Table P6.5 Sample ITEM Records

a.     Given that information, write the relational schema and draw the dependency diagram. Make sure that you label the transitive and/or partial dependencies.

 The answers to this problem are shown in Figure P6.5a and the relational schema definition below the figure..

Figure P6.5a The FOUNDIT Co. Initial Dependency Diagram

The dotted transitive dependency lines indicate that these transitive dependencies are subject to interpretation. We will address these dependencies in the discussion that accompanies Problem 5b’s solution.

The relational schema may be written as follows:

ITEM(ITEM_ID, ITEM_DESCRIPTION, BLDG_ROOM, BLDG_CODE, BLDG_NAME, BLDG_MANAGER)

a.     Write the relational schema and create a set of dependency diagrams that meet 3NF requirements. Rename attributes to meet the naming conventions, and create new entities and attributes as necessary.

 The dependency diagrams are shown in Figure P6.5b. We have added a sample relational diagram to illustrate the relationships at this point. The relational schemas are written below Figure 6.5b. The dependency diagrams in Figure P6.5b reflect the notion that one employee manages each building.

Figure P6.5b FOUNDIT Co. 3NF and Its Relational Diagram

The relational schemas are written as follows:

EMPLOYEE(EMP_CODE, EMP_LNAME, EMP_FNAME, EMP_INITIAL)

BUILDING(BLDG_CODE, BLDG_NAME, EMP_CODE)

ITEM(ITEM_ID, ITEM_DESCRIPTION, ITEM_ROOM, BLDG_CODE)

As you discuss the dependency diagrams in Figure P6.5b, remind the students that BLDG_CODE is not a determinant of BLDG_ROOM. A building can have many rooms, so knowing the building code will not tell you what the room in that building is.

If the room is numbered to reflect the building it is in – for example, HE105 indicates room 105 in the Heinz building – one might argue that the BLDG_ROOM value is the determinant of the BLDG_CODE  and  the  BLDG_NAME  values.  You  will  learn  in  Chapter  7,  ―Introduction  to Structured Query Language (SQL),‖ that you can create a query to find a building by looking at room prefixes. However, if you define dependencies in strictly relational algebra terms, you might argue  that  partitioning  the  attribute  value  to  ―create‖  a  dependency  indicates  that  the  partitioned attribute is not (in that strict sense) a determinant. Although we have indicated a transitive dependency from BLDG_ROOM to BLDG_CODE and BLDG_NAME, we have used a dotted line to indicate that there is room for argument in this set of transitive dependencies. In any case, the (arguable) dependency BLDG_ROOM ® BLDG_CODE does not create any problems in a practical sense, so we have not identified it in the Problem 9 solution.

Clearly, BLDG_CODE is a determinant of BLDG_NAME. Therefore, the transitive dependency is marked properly in the Problem 5b solution.

c.      Draw the Crow’s Foot ERD.

 Use Figure P6.5c to show that, in this case, the ER diagram reflects the business rule that one employee can manage many (or at least more than one) building. Because all employees are not required to manage buildings, BUILDING is optional to EMPLOYEE in the manages relationship. Once again, the nature of this relationship is not and cannot be reflected in the dependency diagram.

NOTE

We also assume here that each item has a unique item code and that, therefore, an item can be located in only one place at a time. However, we demonstrate in Appendixes B and C that inventory control requirements usually cover both durable and consumable items. Although durables such as tables, desks, lamps, computers, printers, etc. would be uniquely identified by an assigned inventory code, consumables such as individual reams of paper would clearly not be so identified. Therefore, a given inventory description such as "8.5 inch x 11 inch laser printer paper" could describe reams of paper located in many different buildings and in rooms within those buildings. We demonstrate in Appendixes B and C how such a condition may be properly handled.

Figure P6.5c The FOUNDIT Co. ERD

As you examine Figure P6.5c, note that the BLDG_ROOM is actually an ITEM entity attribute, so it is appropriate to rename it ITEM_ROOM. Also, keep in mind that a room may be related to the building in which it is located. (A BUILDING may contain many ROOMs. Each ROOM is located in a single building.) Therefore, you can expand the design shown in Figure P6.5b to the one shown in Figure P6.5c. This solution assumes that a room is directly traceable to a building. For example, room SC-508 would be located in the Science (SC) Building and room BA-305 would be located in the Business Administration (BA) building. Note that we have made ROOM optional to BUILDING to reflect the likelihood that some buildings – such as storage sheds -- may not contain designated (numbered) rooms. Although optionalities make excellent default conditions, it is always wise to establish the optionality based on a business rule. In any case, the designer must ask about the nature of the room/building relationship.