Friday 27 June 2014

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.                                          

0 comments:

Post a Comment