Consider a relation Stocks(B, O, I, S, Q, D), whose attributes may be thought of

Question

Consider a relation Stocks(B, O, I, S, Q, D), whose attributes may be thought of informally as broker, office (of the broker), investor, stock, quantity (of the stock owned by the investor), and dividend (of the stock). Let the set of FDs for Stocks be S rightarrow D, I rightarrow B, IS rightarrow Q, and B rightarrow O. (a) List all the keys for Stocks. (b) Use the 3NF synthesis algorithm to find a lossless-join, dependency-preserving decomposition of Stocks into a set of 3NF relations. Show your work. (c) Are any of the relations in your final decomposition not in BCNF? If yes, decompose them into BCNF.

Explanation / Answer

(a)

Make a table based on functional dependency
Left: Stocks placed on left side of FD
Middle: Stocks placed on both left and right side of FD
Right: Stocks placed on right side of FD

LEFT MIDDLE RIGHT
S,I   B   D,Q,O

Let S be a key, then
S FD is SDQ, as this doesnot complete all the stocks in the relation , hence not a key.

now take,
SI FD is SIDBQO, it contains all the elements of the relation, hence it is a key.

Now take,
I FD is IBO. Not a key.

Now Take
IB FD is IBO, Not a key.

Right side doesnot considered to be a key.

Therefore SI is the key

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.