TABLE P6.3 ATTRIBUTE SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE NAME INV_NUM PROD NUM 211347 211347 211347 211348 211349 AA-E3422QW QD-300932X RU-995748G AA-E3422QW GH-778345P SALE_DATE 15-Jan-2018 15-Jan-2018 15-Jan-2018 15-Jan-2018 16-Jan-2018 PROD LABEL Rotary sander 0.25-in. drill bit Band saw Rotary sander Power drill 211 211 309 211 157 ToughGo, Inc. VEND CODE VEND NAME NeverFail, Inc. NeverFail, Inc. BeGood, Inc. NeverFail, Inc. QUANT_SOLD 1 2 1 PROD_PRICE $49.95 $3.45 $39.99 549.95 $87.75
Using the INVOICE table structure shown in table P6.3, do the following
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 an invoice number references more than one product. (Hint: This table uses a composite primary key.)
b.Remove all partial dependencies, write the relational schema, and draw the new dependency diagrams. 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_LABEL, PROD_PRICE,VEND_CODE, VEND_NAME
( Hint Your actions should produce three dependency diagrams.)
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.
Trending now
This is a popular solution!
Step by step
Solved in 4 steps with 3 images
