Using the INVOICE table structure shown in Table P6.3, do the following

Table P6.3 Sample INVOICE Records

a. Write the relational schema, draw its dependency diagram and identify all dependencies, including all partial and transitive dependencies. You can assume that the table does not contain repeating groups and that any invoice number may reference more than one product. (Hint: This table uses a composite primary key.)

 The solutions to both problems (3a and 3b) are shown in Figure P6.3a.

NOTE

We have combined the solutions to Problems 3a and 3b to let you illustrate the start of the normalization process within a single PowerPoint slide. Students generally seem to have an easier time understanding the normalization process if they can compare the normal forms directly. We will continue to use this technique for several of the initial normalization decompositions … if the available PowerPoint slide space permits it.

b. Remove all partial dependencies, write the relational schema, and draw the new dependency Identify the normal forms for each table structure you created.

NOTE

You can assume that any given product is supplied by a single vendor but a vendor can supply many products. Therefore, it is proper to conclude that the following dependency exists:

PROD_NUM → PROD_DESCRIPTION, PROD_PRICE, VEND_CODE, VEND_NAME

(Hint: Your actions should produce three dependency diagrams.)

Figure P6.3a The Dependency Diagrams for Problems 3a and 3b

c. Remove all transitive dependencies, write the relational schema, and draw the new dependency diagrams. Also identify the normal forms for each table structure you created.

 To illustrate the effect of Problem 3's complete decomposition, we have shown Problem 3a's dependency diagram again in Figure P6.3c.

Figure P6.3c The Dependency Diagram for Problem 3c

d. Draw the Crow’s Foot ERD.

NOTE

Emphasize that, because the dependency diagrams cannot show the nature (1:1, 1:M, M:N) of the relationships, the ER Diagrams remain crucial to the design effort. Complex design is impossible to produce successfully without some form of modeling, be it ER, Semantic Object Modeling, or some other modeling methodology. Yet, as the preceding decompositions demonstrate, the dependency diagrams are a valuable addition to the designer's toolbox.

(Normalization is likely to suggest the existence of entities that may not have been considered during the modeling process.) And, if information or transaction management issues require the existence of attributes that create other than 3NF or BCNF conditions, the proper dependency diagrams will at least force awareness of these conditions.

The invoicing ERD, accompanied by its relational diagram, is shown in Figure P6.3d. (The relational diagram only includes the critical PK and FK components, plus a few sample attributes, for space considerations.)

Figure P6.3d The Invoicing ERD and Its (Partial) Relational Diagram

     Crow’s Foot Invoicing ERD

 Invoicing Relational Diagram, Sample Attributes