Fordham, New York City's Jesuit University
back Back to course home page

FORDHAM UNIVERSITY, Fordham College Lincoln Center
CSEU 3500 -- Data Base Systems
Dept. of Computer & Info. Sciences, Spring, 2004

Homework for Chapter 7
Due date: Wednesday, May 5

Note: Questions marked with an asterisk are significantly different from the corresponding questions in the text. Relatively minor clarifications and additions to the other questions are in brackets.

[10 pts.] List all the functional dependencies satisfied by the relation of Figure 7.21. [Do not include trivial dependencies or those with extraneous variables. Justify your answer.]
$ A$ $ B$ $ C$
$ a_1$ $ b_1$ $ c_1$
$ a_1$ $ b_1$ $ c_2$
$ a_2$ $ b_1$ $ c_1$
$ a_2$ $ b_1$ $ c_3$

Figure 7.21 Relation of Exercise 7.4

[10 pts.] Explain how functional dependencies can be used to indicate the following [mapping cardinality constraints. That is, state what functional dependencies would, if satisfied by a database, guarantee that the cardinality constraints are obeyed.] [Assume the primary key of account is account-number and the primary key of customer is customer-name.]
[5 pts.] Consider the following proposed rule for functional dependencies: If $ \alpha \rightarrow \beta$ and $ \gamma \rightarrow \beta$ then $ \alpha \rightarrow \gamma$. Prove that this rule is not sound by showing a relation $ r$ which satisfies $ \alpha \rightarrow \beta$ and $ \gamma \rightarrow \beta$ but does not satisfy $ \alpha \rightarrow \gamma$.

[25 pts.] [Draw the functional-dependency diagram (as described in class)] for the following set $ F$ of functional dependencies for the relation scheme $ R=(A,B,C,X,Y,Z)$. Compute the closure of $ F$. [Omit trivial dependencies or those with extraneous variables.]
  [1] $ A \rightarrow X$
  [2] $ XY \rightarrow Z$
  [3] $ B \rightarrow X$
  [4] $ B \rightarrow C$
  [5] $ C \rightarrow Y$
Set of functional dependencies.
List the candidate key(s) for $ R$.
[10 pts.] Using the functional dependencies of Exercise 7.11, compute $ B^+$.
[10 pts.] Given the schema and functional dependencies of Exercise 7.11, show that the following decomposition of $ R$ is a lossless-join decomposition:
$ (A,B,C)$
$ (A,B,X,Y,Z)$

[10 pts.] Show that the following decomposition of the schema $ R$ of Exercise 7.11 is not a lossless-join decomposition:
$ (A,B,C)$
$ (C,X,Y,Z)$
Hint: Give an example of a relation $ r$ on $ R$ [satisfying the set $ F$ of functional dependencies] such that

$\displaystyle \Pi _{A,B,C}(r) \bowtie \Pi _{C,X,Y,Z}(r) \ne r.$

Robert Moniot 2004-04-26