Question

SHIPPING MANIFEST Shipment ID 00-0001 Origin: Destination: Brazil Shipment Date: 01 /10/2010 Expected Arrival: 01/14/2010 Captain: 002-15 Henry Moore Boston Weight Quantity Total WEIGHT Type Description Item Number 100 2,000 3223 3297 Concrete Form 500 50,000 174,000 224,000 87 BM Steel Beam Shipment Total: Figure 2: (ref: Modern Database Management, Hoffer et al) Based on figure 2 above: a) Draw a relational schema and show the dependencies on it. (5 marks) b) In what normal form is this relation? Explain your answer (5 marks) c) Decompose SHIPPING MANIFEST into a set of 3NF relations. Show all your work (5 marks)

Answer 1

The following figure shows the relational schema for the above Shipping Manifest table provided in the question. Shipment ID Shipment Date Origin Expected Arrival Destination Ship Number Captain Item Number Type Description Weight Quantity Total Weight

Considering relational schema of Shipping Manifest, the functional dependencies that exist in the Shipping Manifest are Shipment ID Shipping Date, Origin, Expected Arrival, Destination, Ship Number, Captain Item Number Type, Description, Weight . Shipment ID, Item Number Quantity, Total Weight

The following diagram represents the functional dependency diagram for the above set of functional dependencies

Partial Dependency Partial Dependency ull Dependency Transitive dependency

b. The following condition must be true for a relation to be in First normal form. There are no repeating groups in the relation (thus, there is a single fact at the intersection of each row and column of the table A primary key has been defined, which uniquely identifies each row in the relation When a relation is not in First normal form then this relation will not be in Second, Third or any other normal form. The given Shipping Manifest relation is not in any Normal form because it does not satisfy the condition of 1NF

I have answered only (a) and (b)