Consider the relation DELIVERY(package-id,weight,customer-id,address,driver,day,
ID: 3802246 • Letter: C
Question
Consider the relation DELIVERY(package-id,weight,customer-id,address,driver,day,time,vehicle-id)
You can assume all of the following:
• each customer has a unique address; however, several customers may live in the same address.
• Drivers work alone.
• Drivers use different vehicles on different days.
• Each package has a certain weight.
• all packages for a given customer in a given day are always delivered together (no two deliveris to the same customer on the same day).
But there is no agreement on exactly what constitutes a delivery; hence, you have to give the FDs that hold in this table, the key and normal form assuming each one of the following alternatives in turn:
(a) A delivery is one package delivered to one customer.
(b) A delivery is several packages delivered at the same time to the same customer.
(c) A delivery is one package delivered to one address.
(d) A delivery is all packages to the same address at the same time.
Explanation / Answer
DELIVERY(package-id,weight,customer-id,address,driver,day,time,vehicle-id)
Functional Dependencies :
Normal form - The relation DELIVERY is in first normal form as there are no multivalue dependencies. But it is not in second normal form.
To Normalize DELIVERY, it must be divided into following relations:
underlined are primary keys