Friday, 28 February 2014

3NF and BCNF Compared




Difference between 3NF and BCNF


Assume the following things;
A and B are set of attributes.
A is non-key attribute and B is the primary key.
FD – { A B. }
The above Functional Dependency is about the dependency of primary key on a non-key attribute. This functional dependency is permitted in Third Normal Form (3NF). 3NF tries to identify and eliminate Non-key Non-key dependency.

This Functional Dependency is not permitted in Boyce-Codd Normal Form (BCNF), because BCNF expects the determiner should be a candidate key. In our example, A is not a candidate key. This is why BCNF is termed as strict 3NF.

3NF is always achievable. BCNF is not. BCNF may result in Loss-less Join Decomposition and lead to loss of Dependency Preserving Decompositions.

Properties
3NF
BCNF
Achievability
Always achievable
Not always achievable
Quality of the tables
Less
More
Non-key Determinants
Can have non-key attributes as determinants
Cannot have.
Proposed by
Edgar F. Codd
Raymond F.Boyce and Edgar F.Codd jointly proposed
Decomposition
Loss-less join decomposition can be achieved
Sometimes Loss-less join decomposition cannot be achieved
Table 1 – 3NF – BCNF Comparisons


No comments:

Post a Comment

SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...