Normalization - solved exercises set 1

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.