Consider the relation: SHIPMENT (ShipmentNumber, VendorName, VendorContact, Vend
ID: 3799687 • Letter: C
Question
Consider the relation:
SHIPMENT (ShipmentNumber, VendorName, VendorContact, VendorFax,
DepartureDate, ArrivalDate, CountryOfOrigin, Destination, ShipmentCost,
InsuranceValue, Insurer)
The following are the functional dependencies and multivalued dependencies for the relation of a small import–export business.
ShipmentNumber -> VendorName
ShipmentNumber ->DepartureDate
ShipmentNumber -> ArrivalDate
ShipmentNumber ->CountryOfOrigin
ShipmentNumber -> Destination
ShipmentNumber ->ShipmentCost
ShipmentNumber -> InsuranceValue
ShipmentNumber ->Insurer
VendorName ->-> VendorContact
VendorName ->-> VendorFax
The cost of a shipment between two cities is always the same.
(CountryOfOrigin, Destination) ->ShipmentCost
The insurance value is always the same for a given vendor and country of origin.
(VendorName, CountryOfOrigin) ->InsuranceValue
Using the 12 functional dependencies listed, transform SHIPMENT into a set of tables in BCNF and 4NF. Indicate the primary keys, candidate keys, foreign keys, and referential integrity constraints.
Explanation / Answer
We have to follow the below normalization pattern :
BCNF :
SHIPMENT (ShipmentNumber, VendorName, DepartureDate, ArrivalDate, CountryOfOrigin, Destination,Insurer)
Shipment_Vendor (VendorName, VendorContact, VendorFax)
Shipment_cost (CountryOfOrigin, Destination,ShipmentCost)
Shipment_Insurance(CountryOfOrigin,VendorName,InsuranceValue)
Primary Key :
SHIPMENT : ShipmentNumber
Shipment_Vendor : VendorName
Shipment_cost : CountryOfOrigin, Destination
Shipment_Insurance : CountryOfOrigin,VendorName
Foreign Key :
SHIPMENT : VendorName for Shipment_Vendor
CountryOfOrigin, Destination for Shipment_cost
CountryOfOrigin for Shipment_Insurance
Shipment_Vendor : VendorName for Shipment_Insurance
4NF
SHIPMENT (ShipmentNumber, VendorName, DepartureDate, ArrivalDate, CountryOfOrigin, Destination,Insurer)
Shipment_Vendor (VendorName, VendorContact, VendorFax)
Shipment_cost1 (Destination,ShipmentCost)
Shipment_Insurance1(VendorName,InsuranceValue)
Shipment_Cost_Insurance (CountryOfOrigin, ShipmentCost,InsuranceValue)
Primary Key :
SHIPMENT : ShipmentNumber
Shipment_Vendor : VendorName
Shipment_cost1 : Destination
Shipment_Insurance1 : VendorName
Shipment_Cost_Insurance : CountryOfOrigin