Question

 Develop a diagram that shows the functional dependencies in the SHIPMENT relation.

2. In what normal form is “SHIPMENT”? Why?

3.Convert SHIPMENT to BCNF if necessary. Show the resulting table(s) with the sample data presented in SHIPMENT.

Answer 1

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 the 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