I.                    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.

Hence, the overall schema is R = (B, O, I, S, Q, D).

Assume that the following f.d. are required to hold on this d.b.

I -> B,   IS -> Q,    B -> O,    S -> D.

 
1)      List all the candidate keys for R.

2)      Give a lossless-join decomposition of R into BCNF.

3)      Give a lossless-join decomposition of R into 3NF preserving f.d.  Is you answer is in BCNF?

II.                 Consider a relational schema R with attributes A, B, C, D, E and the set of functional dependencies
A -> CD, B -> CE, E -> B.

 
1)      Give a lossless-join decomposition of R into BCNF.

2)      Give a lossless-join decomposition of R into 3NF preserving f.d.  Is you answer is in BCNF?