Table 4-8 shows a portion of a shipment table for a large manufacturing company.
ID: 3565981 • Letter: T
Question
Table 4-8 shows a portion of a shipment table for a large manufacturing company. Each shipment (identified by Shipment#) uniquely identifies the shipment Origin, Destination, and Distance. The shipment Origin and Destination pair also uniquely identifies the Distance.
a) Develop a diagram that shows the functional dependencies in the SHIPMENT relation.
b) In what normal form is SHIPMENT? Why?
c) Convert SHIPMENT to third normal form if necessary. Show the resulting table(s) with the sample data presented in SHIPMENT.
Table 4-8 shows a portion of a shipment table for a large manufacturing company. Each shipment (identified by Shipment#) uniquely identifies the shipment Origin, Destination, and Distance. The shipment Origin and Destination pair also uniquely identifies the Distance. a) Develop a diagram that shows the functional dependencies in the SHIPMENT relation. b) In what normal form is SHIPMENT? Why? c) Convert SHIPMENT to third normal form if necessary. Show the resulting table(s) with the sample data presented in SHIPMENT.Explanation / Answer
1) Dependencies are
(Origin,Destination) -> Distance
#Shipment -> (Origin,Destination)
2) Currently table is in 1st normal form because Distance can be identified by (Origin,Destination) which in above table is not the primary key combination hence it is not in 2nd normal form.
3) Converting above table
Origin_Destination_Table(PK = Origin_Distance_ID uniquely defines Origin Destination)
Origin_DistanceID Origin Destination Distance
1 Seatle Denver 1517
2 Chicago Dallas 1058
3 Boston Atlanta 1214
4 Denver Los Angeles 975
Shipment_Table
Shipment# Origin_DistanceID
409 1
618 2
723 3
824 4
629 1
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.