The Relational Model and Normalization - Database Processing: Fundamentals, Design, and Implementation - page 159

6. PURCHASE ( PurchaseItem , PurchasePrice, PurchaseDate ,Vendor)
and:
VENDOR ( Vendor , Phone)
7. PURCHASE ( PurchaseItem , PurchasePrice, PurchaseDate , Vendor )
and:
VENDOR ( Vendor , Phone)
F. Modify what you consider to be the best design in part E to include surrogate ID
columns called PurchaseID and VendorID. How does this improve the design?
G. The relations in your design from part D and part F are not connected. Modify the
database design so that sales data and purchase data are related.
James Morgan keeps a table of data about the stores from which he purchases. The
stores are located in different countries and have different specialties. Consider the
following relation:
STORE (StoreName, City, Country, OwnerName, Specialty)
A. Explain the conditions under which each of the following is true:
1. StoreName S City
2. City S StoreName
3. City S Country
4. (StoreName, Country) S (City, OwnerName)
5. (City, Specialty) S StoreName
6. OwnerName S S StoreName
7. StoreName S S Specialty
B. With regard to the relation in part A:
1. Specify which of the dependencies in part A seem most appropriate for a small
import-export business.
2. Given your assumptions in B.1, transform the STORE table into a set of tables that
are in both 4NF and BCNF. Indicate the primary keys, candidate keys, foreign keys,
and referential integrity constraints.
C. Consider the relation:
SHIPMENT (ShipmentNumber, ShipperName, ShipperContact, ShipperFax,
DepartureDate, ArrivalDate, CountryOfOrigin, Destination, ShipmentCost,
InsuranceValue, Insurer)
1. Write a functional dependency that expresses the fact that the cost of a shipment
between two cities is always the same.
2. Write a functional dependency that expresses the fact that the insurance value is
always the same for a given shipper.
3. Write a functional dependency that expresses the fact that the insurance value is
always the same for a given shipper and country of origin.
4. Describe two possible multivalued dependencies in SHIPMENT.
5. State what you believe are reasonable functional dependencies for the SHIPMENT
relation for a small import-export business.
6. State what you believe are reasonable multivalued dependencies for the SHIPMENT
relation.
7. Using your assumptions in 5 and 6, transform SHIPMENT into a set of tables in
BCNF and 4NF. Indicate the primary keys, candidate keys, foreign keys, and referen-
tial integrity constraints.