Set of solved exercises in Normalization / Normalization Solved Examples / How to find candidate keys, and primary keys in database? / Sets of examples to find the keys of a tables / Process of Key finding in a database - Examples
1.
Consider the following relation schema R and set of Functional Dependencies F:
R(A,B,C,D,E),
F
= {AC → E, C → D, D → A}
One
of the FDs contains an extraneous attribute that can be removed without
changing the dependencies implied by the above set. Explain which one.
Answer: Since the functional dependencies C → D and D → A imply C → A (transitive dependency), the A in AC → E is extraneous. C alone can determine the
other attributes.
2.
For the following relation schema R and set of Functional Dependencies F:
R(A,B,C,D,E),
F = {AC → E, B → D, E → A}
List
all candidate keys.
Answer: From the given set F of functional
dependencies, it is very evident that B and C must be in the candidate key as
they are not present in the Right Hand Side (RHS) of the given set of FDs. Hence,
at first we can check for BC as the candidate key as follows;
If you know B, then you know B and D through FD B → D. Along with this, if you know C, then you know BCD. That is, BC → BCD. B and C together cannot determine A and E, so BC cannot be a candidate key.
Then
we can try with the attributes that are present in the LHS like B and C. First
let us take A. Then we have,
ABC → ABCDE. So, ABC is a candidate key.
Now
we shall try with the other LHS attribute E. Then we have,
BCE → ABCDE. So, BCE is another candidate key.
Checking
BCA and BCE, we see that both of them
are candidate keys.
3.
The relation schema given in question number 2 above is not in BCNF due to the
reason that it has two candidate keys. List one functional dependency that
violates the rules for BCNF.
Answer: E → A violates the rules. If we don’t have a functional
dependency like this, we have only one candidate key, i.e, ABC.