Consider a relation Stocks (B, O, I, S, Q, D), whose attributes may be thought o
ID: 3777923 • Letter: C
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. List all the keys for Stocks. Use the 3NF synthesis algorithm to find a lossless-join, dependency-preserving decomposition of Stocks into a set of 3NF relations. Show your work. Are any of the relations in your final decomposition not in BCNF? If yes, decompose them into BCNF.Explanation / Answer
(a)
IS is the only key for the Stocks relation.
(b)
The first step to verify that the given FDs are their own minimal basis is to check
{S+ } = {S }
{I + } = {I}
{IS+ } = {ISDBO}
{B+ } = {B+ }
The second step to verify that the given FDs are their own minimal basis is to check to see if any of the left sides of an FD can have one or more attributes removed without losing the dependencies. However, this is not the case for the one FD that contains two attributes on the left side.
Thus, the given set of FDs has been verified to be the minimal basis.