Consider this table: CustomerPurchases(cid, cname, caddress, czip, cstate, pid,
ID: 3690059 • Letter: C
Question
Consider this table:
CustomerPurchases(cid, cname, caddress, czip, cstate, pid, pname, pdescript, ptype, pcountry, mid, mname, mcountry, pprice, ptax, pship, pquantity)
Table has information about a customer (cid, name, address information), a product the customer bought (pid, name, description, type, and country where it was made), manufacturer, quantity of the product bought, price, tax, shipping cost. Unique ids for each customer, product, and manufacturer.
Assume: (i) if customers living in the same state pay the same price, they should also pay the same tax, (ii) when customers live in the same zip code, they should pay the same shipping costs, and (iii) a product could be made by more than one manufacturer.
a) Explain why the above is not a good relational design. Name several reasons!
b) Derive all candidate keys for this table.
c) Identify the set F of non-trivial functional dependencies for this schema. (It is enough to identify a subset E such that the closures of E and F are the same.)
d) Derive a canonical cover of the functional dependencies in F.
e) Is the above schema in BCNF? Prove or disprove. If it is not in BCNF, convert it into BCNF.
f) Is the BCNF schema from e) dependency-preserving? Prove or disprove. If not, convert it into 3NF.
g) Suppose we add an additional constraint that all products of the same type made by the same manufacturer must be made in the same country (that is, a manufacturer may produce products of type ``shoes’’ in Mexico and of type ``shirts’’ in Canada, but a manufacturer cannot make shoes in more than one country). How would this change your answers to parts (b) to (f)?
Explanation / Answer
a) It is not in a good relational design because the table CustomerPurchases is not in s 3NF, it has got functional dependencies present in it, like the details of product like pname,pdescript...
b) cid and pid are the possible candidate key of CustomerPurchases table
c) pid -> {pname, pdescript, ptype, pcountry, mid, mname, mcountry, pprice, ptax, pship, pquantity}
d) Customer(cid, cname, caddress, czip, cstate)
Purchases(pid, pname, pdescript, ptype, pcountry, mid, mname, mcountry, pprice, ptax, pship, pquantity)