Boyce-codd normal formal(BCNF) | normalization and its type

BCNF( BOYCE-CODD NORMAL FORM)

The BCNF was introduced as a stronger definition of 3NF because a relation that is in 3NF may still face some redundancy problems. It was not satisfactory for relations that had, Multiple candidate keys, Where multiple candidates keys are composite,Multiple candidate keys overlapped i.e. Two or more of the candidate keys share a common attribute. The above three conditions tell us when a relation in 3NF is not in BCNF.

A relation is said to be in BCNF if and only if every determinant is a candidate key. A determinant is an attribute on which some of the attributes are fully functionally dependent. In other words, for a relation to be in BCNF , a relation must only have candidate keys as determinants.

EXAMPLE- Let us consider a relation GRADE having attributes SName ,Stu_Id,Course and Grade.

Stu_Id	SName	Course	Grade
5705	Rajesh	Computer	A
5901	Kapil	Computer	C
5658	Sunil	Punjabi	A
5705	Rajesh	French	A
5816	Ankit	Dance	B
5658	Sunil	Yoga	B

Let us assume that each student has a unique Stu_Id  and unique SName. The relation GRADE consists of two candidates keys (SName,Course) and (Stu_Id ,Course).The functional dependencies which hold on GRADE relation are

The GRADE relation is in 3NF since there is no transitive dependencies But not in BCNF because

(a) It consists of multiple candidate keys (SName,Course) and (Stu_Id ,Course).

(b) The candidate keys in the relation are composite keys i.e. it consists of more than one attribute like

(SName,Course) and (Stu_Id ,Course).

(c) Both candidate keys share a common attribute 'Course'.

However, this relation has a disadvantage in the form of repetition of data which may result in insertion,updation and deletion anomalies.It is due to the overlapping candidate keys. 

To remove the above problems,we decompose the GRADE relation into two relations in two possible ways.

GRADE_ST and GRADE_OOU

Relation GRADE_ST

Stu_Id	SName
5705	Rajesh
5901	Kapil
5658	Sunil
5816	Ankit

Relation GRADE_OOU

Stu_Id	Course	Grade
5705	Computer	A
5901	Computer	C
5658	Punjabi	A
5705	French	A
5816	Dance	B
5658	Yoga	B

On decomposing the GRADE relation ,the anomalies discussed earlier are removed.

Now it is in BCNF.

From the definition of BCNF , it is clear that a relation in BCNF is also in 3NF but the converse is not necessarily true.