## CSE3330/5330 Homework #5 Due on Thursday April 29, before midnight Worth 3.33% of the final grade

1. (40 points) Suppose we have a database for an investment firm, consisting of the following attributes:
• (B) Broker
• (O) Office of a broker
• (I) Investor
• (S) Stock
• (Q) Quantity of stock owned by an investor
• (D) Dividend paid by a stock.
with the following functional dependencies:

1. (15 points) Find a key for the relation schema .
2. (15 points) Give a lossless-join decomposition into BCNF of the schema .
3. (10 points) Give a lossless-join, dependency preserving decomposition into 3NF of the schema .

2. (60 points) Consider the relation schema that satisfies the following functional dependencies:

1. (10 points) Show that the decomposition of into and is a lossless-join decomposition but not a dependency preserving.
2. (10 points) Show using a counterexample that the decomposition of into and is not a lossless-join decomposition.
3. (20 points) Give a lossless-join decomposition into BCNF of the schema .
4. (20 points) Give a lossless-join, dependency preserving decomposition into 3NF of the schema .

Submit Homework #5: