Table P6.3 Sample INVOICE Records
Attribute Name Sample Value Sample Value Sample Value Sample Value Sample Value
INV_NUM 211347 211347 211347 211348 211349
PROD_NUM AAE3422QW
QD300932X
RU995748G
AAE3422QW
GH778345P
SALE_DATE 15Jan2010
15Jan2010
15Jan2010
15Jan2010
16Jan2010
PROD_LABEL Rotary sander 0.25in.
drill bit Band saw Rotary sander Power drill
VEND_CODE 211 211 309 211 157
VEND_NAME NeverFail, Inc. NeverFail, Inc. BeGood, Inc. NeverFail, Inc. ToughGo, Inc.
QUANT_SOLD 1 8 1 2 1
PROD_PRICE $49.95 $3.45 $39.99 $49.95 $87.75
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.)
b. Remove all partial dependencies, draw the new dependency diagrams, and identify the normal forms for each table structure
you created.
c. Remove all transitive dependencies, and draw the new dependency diagrams. Also identify the normal forms for each table
structure you created.
d. Draw the Crow’s Foot ERD.
5. To keep track of office furniture, computers, printers, and so on, the FOUNDIT company uses the table structure shown in
Table P6.5.
Table P6.5 Sample ITEM Records
Attribute Name Sample Value Sample Value Sample Value
ITEM_ID 231134678
342245225
254668449
ITEM_LABEL HP DeskJet 895Cse HP Toner DT Scanner
ROOM_NUMBER 325 325 123
BLDG_CODE NTC NTC CSF
BLDG_NAME Nottooclear Nottoclear Canseefar
BLDG_MANAGER I. B. Rightonit I. B. Rightonit May B. Next
a. Given that information, write the relational schema and draw the dependency diagram. Make sure that you label the transitive
and/or partial dependencies.