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?