Unformatted text preview: swer:
FA F (reflexivity)
C FA (given) and FA F imply C F (transitivity)
AB C (given), C F and F E (given) imply AB E (transitivity)
Q.9 [20 pts] Consider the relation schema R(A, B, C, D) and the following functional
dependencies that hold on R: A BC, B C
(a) [5 pts] Find the candidate key(s).
(b) [5 pts] Show that R is not in 3NF.
(c) [10 pts] Find a losslessjoin, dependencypreserving decomposition of R into 3NF.
Answer:
(a) The only candidate key is AD.
(b) For the dependency B C it holds that
(i)
it is not trivial;
(ii)
B is not a superkey for R;
(iii)
C is not contained in a candidate key.
This shows that R is not in 3NF. (We could also have used the dependency A
A C.) B, or (c) We first construct a canonical cover of the dependencies: {A B, B C}.
The algorithm in the textbook results in the decomposition {AB, BC, AD} where AD has
to be added since neither AB nor BC contains a candidate key.
(If the original dependencies, rather than the canonical cover, is used, the decomposition
algorithm would have produce {ABC, AD} where ABC is not in 3NF, due to the
dependency B C.)...
View
Full Document
 Spring '11
 ÖzgürUlusoy
 Relational model, Candidate key, Database normalization, BCNF, super key

Click to edit the document details