Normalization solved exercise 3

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

Question:

Consider a relation R(A, B, C, D, E) with FD's AB → C, CD → E, C → A, C → D, D → B.

The possible candidate keys for R are AB, AD, C

(a) List all the functional dependencies that violate 3NF. If any, then decompose R accordingly.

(b) List all the FD's that violate BCNF. If any, then decompose R accordingly.

Solution (a):

To be in 3NF, the following points should not be violated by any of the FDs;

(i) R should be in 2NF;

According to the properties that are to be held for 2NF (No partial functional dependencies), all the non-prime attributes (E is the only non-prime attribute in our case) depend on prime attributes (A, B, C, and D in our case) or keys as a whole. No partial dependencies found. Hence, we would say that R is in 2NF.

(ii) Transitive functional dependencies of non-prime attributes on candidate keys are prohibited;

In the list of functional dependencies, we found no non-key dependencies. That is, no non-prime attribute is functionally dependent on another non-prime attribute.

From points (i) and (ii), it is clear that there are no FDs that violate 3NF. Hence, we would say that R is in 3NF.

Solution (b):

R can be in BCNF if and only if R is in 3NF and every determinant is a candidate key. 

From the solution (a), you would understand that R is in 3NF. But every determinant is not a candidate key. In the given set of FDs, D → B is the only functional dependency that violates this rule. That is, D is not a candidate key on its own. Hence, the relation R is not in BCNF.

To convert R into a BCNF relation, we need to decompose R using the FD D → B. Then we shall get the following relations R1 and R2;

R1 (D, B) and R2 (A, C, D, E)

Go back to Normalization - Solved Exercises page