CSE3330/5330 Homework #5
Due on Thursday April 29, before midnight
Worth 3.33% of the final grade


  1. (40 points) Suppose we have a database for an investment firm, consisting of the following attributes: with the following functional dependencies:

    \begin{displaymath}F\; =\; \{\;S\rightarrow D,\;I\rightarrow B,\;IS\rightarrow Q,\;B\rightarrow O\;\}\end{displaymath}

    1. (15 points) Find a key for the relation schema $R=(B,O,S,Q,I,D)$.
    2. (15 points) Give a lossless-join decomposition into BCNF of the schema $R$.
    3. (10 points) Give a lossless-join, dependency preserving decomposition into 3NF of the schema $R$.

  2. (60 points) Consider the relation schema $R=\{A,B,C,D,E\}$ that satisfies the following functional dependencies:

    \begin{displaymath}F\; =\; \{\;A\rightarrow BC,\;CD\rightarrow E,\;B\rightarrow D,\;E\rightarrow A\;\}\end{displaymath}

    1. (10 points) Show that the decomposition of $R$ into $R_1=(A,B,C)$ and $R_2=(A,D,E)$ is a lossless-join decomposition but not a dependency preserving.
    2. (10 points) Show using a counterexample that the decomposition of $R$ into $R_1=(A,B,C)$ and $R_2=(C,D,E)$ is not a lossless-join decomposition.
    3. (20 points) Give a lossless-join decomposition into BCNF of the schema $R$.
    4. (20 points) Give a lossless-join, dependency preserving decomposition into 3NF of the schema $R$.

Submit Homework #5:

Last modified: 04/27/10 by Leonidas Fegaras