Consider the universal relation schema INVENTORY (Store#, Item, Vendor, Date, Cost, Units, Manager, Price, Sale, Size, Color, Location) and the constraint set F {fd1, fd2, fd3, fd4, fd5, fd6, fd7} introduced originally in Chapter 7, Exercise 13 where:
fd1: {Item, Vendor} → Cost fd2: {Store#, Date} → {Manager, Sale}
fd3: {Store#, Item, Date} → Units fd4: Manager → Store#
fd5: Cost → Price fd6: Item → {Size, Color} fd7: Vendor → Location
a. Confirm that F is a minimal cover for the set of functional dependencies given above.
b. List the candidate key(s) of INVENTORY.
c. For each candidate key, indicate the immediate normal form violated in INVENTORY by each of the functional dependencies given above.
d. If INVENTORY is not in BCNF, design a relational schema that:
• is in BCNF so that all anomalies due to functional dependencies are eradicated, and
• yields all lossless-join decompositions
e. List the functional dependencies in F that are not preserved in this design.
f. Show the final design. The design should be parsimonious (i.e., minimal set in BCNF). Also, clearly indicate entity integrity and referential integrity constraints.
g. Revise the above design so that all dependencies are preserved in a lossless-join decomposition with the least sacrifice in the achieved level of normal form.