Quick Contact
DBMS Tutorial
 DBMS Tutorial
 What is Database Management System (DBMS)?
 Components of DBMS
 Applications of DBMS
 Three Schema DBMS Architecture
 Difference between DBMS and RDBMS?
 Difference between File Oriented System and DBMS
 Types of Data Models
 DBMS Schema and Instances
 Data Independence and Data Abstraction
 Database Users and Administrator
 DBMS Languages and Interfaces
DBMS ER Model
DBMS Relational Data Model
DBMS Normalization
Boyce Codd Normal Form (BCNF)
Boyce Codd Normal Form (BCNF) is the advanced version of the third normal form. That’s why it is called as 3.5 Normal Forms.
A table is in BCNF if each functional dependency P→Q,P is the super key of the table.
For Boyce Codd Normal Form, the table should be in the third normal form, and for each functional dependency, L.H.S is super key.
Consider a relation R (A, B, C) where the given FDs are AB→C,C→B. Then, an instance of this relation can be as follows.
A  B  C 

a_{1}  b_{2}  c_{1} 
a_{4}  b_{2}  c_{1} 
a_{1}  b_{3}  c_{2} 
a_{3}  b_{3}  c_{3} 
Check for these redundancies in the relation. As the dependency,C→B holds, we know from the first tuple that when C is c_{1},and B is b_{2}. Therefore, we can say that this information c_1,b_2is repeated in the second tuple. These repetitions had to be made to provide information a_{1},b_{2},c_{1} and a_{4},b_{2},c_{1}.
Check out that relation is in 3NF, and as C is the only candidate key in the relation, there is no chance that a noncandidate key is dependent on another noncandidate key.
Normalization Procedure
If we decompose the table such that no part of the candidate key becomes functionally dependent on a noncandidate key, then the redundancy is removed. Therefore, we decompose the relation as R_{1} (A,C) and R_{2} (B,C). Now, the given instance will become as follows
A  C 

a_{1}  c_{1} 
a_{4}  c_{1} 
a_{1}  c_{2} 
a_{3}  c_{3} 
With AC as the candidate key and
B  C 

b_{2}  c_{1} 
b_{3}  c_{2} 
b_{3}  c_{3} 
With C as the candidate key.
Therefore, the redundancy is no longer present, and we say that the relation is in BCNF.
How to certify that the relation is in BCNF?
For a relation R to be in BCNF, one of the following conditions should hold for all dependencies: A→B in a relation where A and B are a set of attributes within the relation.
 The dependencies A→B is trivial.
 A is a super key of the relation R.
Now, if none of these conditions hold for at least one of the dependencies A→B in R, we say that R is not in BCNF.
When a relation has higher than one candidate key, anomalies can influence the outcome, yet the relation is in third normal form. Anomalies that occur when a table is unable to possess the property that each determinant is a candidate key lead to the BoyceCodd Normal Form (BCNF). The following example cities the failure to maintain this property.
EMPCODE  PID  PLEADER 

001  P01  MG 
002  P02  JDR 
003  P03  SS 
004  P02  JDR 
005  P01  MG 
Where
EMPCODE=Employee Code
PID=Project Identification Number
PLEADER= Project Leader
The only assumption is that a project leader manages every project. This is in 3NF because it has no partial functional dependencies and no transitive dependencies. It does not have the muchneeded property that each determinant be a candidate key. A pair of attributes is determinant, EMPCODE, and PID. Every one distinctive pair of values of EMPCODE and PID establishes a value for the attributes which are unique, PLEADER. Another determinant is the pair EMPCODE and PLEADER, which creates the values of the attributes that are unique, PID. Yet, another determinant is the attribute, PLEADER. Each different value of PLEADER determines a unique value of the attribute, PID. These observations about the relation R correspond to the realworld facts that each employee has a single project leader (PLEADER) for each of his or her project, and just one project leader (PLEADER) manages each project.
It is to be examined whether these three determinants are candidate keys or not. The response is that the pair EMPCODE and PID is a candidate key since each of them identifies a row in R uniquely. Similarly, the pair EMPCODE and PLEADER is a candidate key. Since the value MG appears in two rows of the PLEADER column, the determinant PLEADER is not a candidate key. Therefore, the relation R is unsuccessful in fulfilling the condition that every determinant in it is a candidate key.
Even though R is in 3NF, there are still anomalies in it.
Insertion Anomaly
If we want to add a new project with a project leader, we cannot until we have an employee assigned to that project.
Deletion Anomaly
If we delete the employee“003”, we loss all information that SS manages the project“P03”.
Updation Anomaly
If AC replaces MG as project leader of project“P01”, we have to update multiple rows
The problem occurs because there is a determinant that is not a candidate key.
Now, we formulate the whole thing as follows.
R(EMPCODE,PID,PLEADER)
FDs:EMPCODE,PID→PLEADER
PLEADER→PID.
By decomposition, we get
R1(EMPCODE,PID)
R2(PLEADER,PID)
R1
EMPCODE  PID 

001  P01 
002  P02 
003  P03 
004  P02 
005  P01 
R2
PLEADER  PID 

MG  P01 
JDR  P02 
SS  P03 
Difference between 3NF and BCNF
3NF  BCNF 

A relation is in 3NF if it is in 2NF and no nonprime attribute transitively depends on the primary key. In other words, a relation R is in third normal form if for each functional dependency P→Q in R at least one of the following conditions are met:

A relation R is in BCNF if it is in third normal form and for each functional dependency P→Q in R, P is a key or superkey in R. In other terms, the only difference between 3NF and BCNF is that in BCNF it is not performing the second circumstances of the third normal form. This creates BCNF stricter than the third normal form as any relation that is in BCNF will be in the third normal form, but not certainly every relation that is in the third normal form will be in BCNF. 
Third Normal form can be recover without sacrificing all dependencies  Dependencies cannot be protected in BCNF. 
Lossless decomposition can be managed in 3NF.  Lossless decomposition is tough to manage in BCNF. 
Enroll Yourself in Live Training: DBMS Training