CREATE OWN LIBRARY

Boyce Codd Normal Form (BCNF):

Before seeing this chapter follow previous chapters: 

2NF, 3NF, Find Super and Candidate Key from Attribute Closer.

Definition:

So, there is any Functional Dependency X → Y, then X must be Super Key then it is in BCNF.

After 3NF

So, all is in BCNF.

So, at BCNF there will be no redundancy which is created by Functional Dependencies. 

The redundancy present at BCNF is created by Multi-Valued Dependency (X ↠ Y) which can be solved by 4NF and 5NF.

Due to Functional Dependencies, we can get redundant data because every time when left-hand side attribute is repeated in the relation, then related all attributes will be repeated.

For more details please follow: Functional Dependencies Concept

But in the case of  BCNF X → Y, where X is Super Key, so here left side contain key attribute which is never repeated, so all related attributes will not be repeated.

In the above example 3NF is already in BCNF but not every time it is true.

Let take an example:

Consider a relation R(ABCD) and FDs (AB → CD, D → A). Assume that it does not contains any multi-valued attributes. Find the highest normal form.

Solution:

The above relation R (ABCD) is already in 1NF because it not contains any multi-valued attributes.

Given functional dependencies {AB → CD, D → A}

Now we first find the Super key and Candidate key to check other 2NF, 3NF, and BCNF.

So, AB is Super Key and Candidate Key both. 

For details please follow: Find Super and Candidate Key from Attribute Closer

So, Candidate Key (CK) = {AB}, Prime Attribute (PA) = {A, B}, Non-Prime Attribute (NPA) = {C, D}.

Check for 2NF (For more details please follow: 2NF)

AB → CD

Here AB is not a proper subset of Candidate Key even it is itself a candidate key. AB → AB, so, it is not Partial Dependency. It is in 2NF

D → A

Here, D is not a proper subset of Candidate Key (AB) and A is the prime attribute.

So, it is not Partial Dependency. It is in 2NF.

So, R(ABCD) is in 2NF.

Check for 3NF: (For more details please follow: 3NF)

AB → CD

Here AB is Super Key, so, it is in 3NF, it is not Transitive Dependency.

D → A

Here D is not Super Key but A is Prime Attribute. So, it is not Transitive Dependency. It is also in 3NF.

So, R (ABCD) is in 3NF.

Check for BCNF:

AB → CD 

Here AB is Super Key, so, it is in BCNF.

D → A

Here D is not Super Key, so, it is not in BCNF

So, redundancy may exist due to D → A.

So, the above relation R(ABCD) is not in BCNF. 

Now, we can try to decompose it:

Now after decomposition we check for lossless decomposition and dependency preserving to check it is valid decomposition or not.

 If we check lossless decomposition, here D → A, in relation R2(DA) where D is Super Key. So, it satisfied lossless join decomposition. 

For more details please follow: Concept of Lossless and Lossy Join Decomposition

But the problem is it is not dependency preserving because we can’t derive Functional Dependency AB → CD from the above two relations R1(BCD) and R2(DA).

So, it is not a valid decomposition, we cannot decompose this given relation R(ABCD) though it is not in BCNF.

Important note:

1NF, 2NF, 3NF, and BCNF are always lossless decompositions.

1NF, 2NF, and 3NF are every time must be dependency preserving but only BCNF is not every time dependency preserving. 

So, every 3NF we can’t convert to BCNF. Maybe it is possible or maybe not possible. So, it is not mandatory that every 3NF must be converted to BCNF.

Note:  That’s why we can say the most adequate form is 3NF.