##
**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?**R should be in 2NF, 3NF, and every determinant must be a candidate key.*

__Requirements:__**– 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.**

*Partial key dependency is present*
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

## No comments:

## Post a Comment