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)?