Normalization solved exercise 5

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 / Normalization to BCNF.

Question:

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

Determine all the keys of relation R. Is the relation R in BCNF?

Solution:

We can derive the values of C and D uniquely from the FDs AB → C, and B → D. And the closure of AB, i.e., (AB)⁺ = ABCD. Hence, AB is one of the keys.

We can derive the values of B and D uniquely from the FDs AC → B, and B → D (Transitive FDs). (AC)⁺ = ABCD. Hence, AC is one of the keys.

We can derive the values of A and D uniquely from the FDs BC → A, and B → D. and the closure (BC)⁺ = ABCD. Hence, BC is one of the keys.

The keys are AB, AC, and BC.

Is R in BCNF?

Requirements: R should be in 2NF, 3NF, and every determinant must be a candidate key.

Partial key dependency is present – In the functional dependency B → D, the determinant B is not a key. But it is a part of the candidate keys AB and BC. If part of any candidate keys can uniquely identify another (or set of) non-key attribute, we call that as partial key dependency.

Hence, R is not in 2NF. We need to decompose R into the following relations so that we can make 2NF relations out of R;

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

R1 and R2 do not have partial key dependencies, and transitive dependencies. Hence, both are in 2NF and 3NF.

The determinants are the keys in both the relations. Hence, R1 and R2 are in BCNF.

Go back to Normalization - solved exercises page