Question

Consider the following schema and functional dependencies:

SHIPPING (ShipName, ShipType, VoyageID, Cargo, Port, ArrivalDate) Key: ShipName, ArrivalDate FD1: ShipName > ShipType FD2: VoyageID > ShipName, Cargo FD3: ShipName, ArrivalDate > VoyageId, Port

1.Please list the final set of 3NF schema including all its keys.

2.Do any of the finalized 3NF schema have determinates that are not candidate keys? If yes, explain - which schema(s)? Why?

Answer 1

Answer:------------
Given,
Relation, SHIPPING (ShipName, ShipType, VoyageID, Cargo, Port, ArrivalDate)
Key is ShipName and ArrivalDate
FD1: ShipName > ShipType
FD2: VoyageID > ShipName, Cargo
FD3: ShipName, ArrivalDate > VoyageId, Port

Firstly we need to normalize it in 2NF.
Starting with the initial relation:
Shipping (ShipName, ShipType, VoyageID, Cargo, Port, ArrivalDate)
We have a partial key dependency as ShipName alone can determine ShipType. So we normalize to:
SHIPS( ShipName, ShipType)
ShipName -> ShipType

VOYAGES (ShipName, VoyageID, Cargo, Port, ArrivalDate)
ShipName, ArrivalDate -> VoyageID, Port
VoyageID -> ShipName, Cargo

Now normalize in 3NF:--------

As in 2NF, VOYAGES has a transitive dependency: ShipName, ArrivalDate -> VoyageID -> Cargo, So normalize VOYAGES:
SHIPPORTS (ShipName, VoyageID, Port, ArrivalDate)
ShipName, ArrivalDate -> VoyageID, Port
VoyageID -> ShipName

CARGO (VoyageID, Cargo)
VoyageId -> Cargo

SHIPS( ShipName, ShipType)
ShipName -> ShipType