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.

7.4

[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

7.6

[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.]

• A one-to-one relationship set exists between entity sets account and customer.
• A many-to-one relationship set exists between entity sets account and customer.

[Assume the primary key of account is account-number and the primary key of customer is customer-name.]

7.7

[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$.

7.11*

[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$.

7.12*

[10 pts.] Using the functional dependencies of Exercise 7.11, compute $B^+$.

7.13*

[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)$

7.16*

[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

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