Question

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), and D (dividend paid by a stock), with the following functional dependencies: SD, I B.IS a Find a key for the relation scheme R(B,O,S,Q,I,D). b. Find a decomposition of R into third normal form, having a lossless join and preserving dependencies c.Find a lossless join decomposition of R into Boyce-Codd normal form.

Answer 1

Answer:

Given, FDs are:

S -> D

I -> B

IS -> Q

B -> O

a)

"I" and "S" must be there in any candidate key because they do not appear on the right side of any functional dependency.

The only candidate key is: IS

IS -> ISBDQO

b)

Decomposition of R into 3NF: (I, B), (S, D), (B, O), (I, S, Q)

c)

Decomposition of R into BCNF:

Decompose R by I → B into R1 = (I, B) and R2 = (I, O, S, Q, D).

R1 is in BCNF

Decompose R2 by S → D into R21 = (S, D) and R22 = (O, I, S, Q).

R21is in BCNF

Decompose R22 by I → O into R221 = (I, O) and R222 = (I, S, Q).

R221 is in BCNF.

R222 is in BCNF.

The decomposition is: (I, B), (S, D), (I, O), (I, S, Q)

We can also write it as: (I, B), (S, D), (B, O), (I, S, Q)

Explanation:

The answer above is rendered in a very explanatory way.