BCNF form in DBMS

BCNF form in DBMS

 

In this article, we will learn about the Boyce Codd Normal Form i.e. BCNF form in DBMS.

  • Normalization is a process where the table is decomposed into further tables  so that  newly formed tables are  free from all problems of insertion, deletion, and updation of data
  • BCNF: Advanced version of 3nf and stricter then 3nf

 

Learn more about Normalization here on this page.

BCNF form in DBMS

Definition of Boyce Codd Normal Form:

The table is said to be in BCNF if it satisfies the following properties

    • It should be in 3nf
    • For every functional dependency, A-> B, A must be a superkey i.e in any functional dependency LHS attribute must be a Superkey.

Problem Table

EMPLOYEE table:

  EID  
EMP_LOCATIONEMP_DEPTDEPT_NOEMP_DEPT_NO
66IndiaHRD1101
66IndiaMarketingD1102
73CANADAExportsD2103
73CANADAFinanceD2104

 

  • Consider a table employee when an employee can work in more than one department, these dependencies  cause  insertion, updation and deletion problems
  • It is in First Normal Form because of no multiple attributes  and It is in second normal form and third Normal Form also because it is free from all kinds of partial and transitive dependencies.

 

 

Procedure to  convert a table into BCNF

In the above table Functional dependencies are as follows:

  • EMP → EMP_COUNTRY
    EMP_DEPT → {DEPT_NO, EMP_DEPT_NO}
  • Candidate key: {EID, EMP_DEPT}
  • From the table, we can see that we must need a candidate key combination {EID, EMP-DEPT} because single employee working in more than two departments hence his ID is duplicated so that only employee ID cannot be served as a primary key
  • Create three tables from the original employee table
  • Create a table called EMP_COUNTRYwhich contains employees country and employee ID and here each record can be uniquely identified from this table.

EMP_COUNTRY table:

   EID   
EMP_LOCATION
66India
73UK

 

  • Create another table  which  will consist of employee department type number and the employee Department number.

 

EMP_DEPT

EMP_DEPTDEPT_NOEMP_DEPT_NO
HRD1101
MarketingD1102
ExportsD2103
FinanceD2104

 

  • Create one more table EMP_DEPT_MAPPING which consists of employee ID and departmentEMP_DEPT_MAPPING table:

 

EMP_IDEMP_DEPT
D1101
D2102
D3103
D4104

 

Observed functional dependencies

    • EID       EMP_COUNTRY 
    • EMP_DEPT      {DEPT_NO, EMP_DEPT_NO} 

Conclusion: The table is in BCNF because the left side of each dependency is a superkey  in all the three tables

One comment on “BCNF form in DBMS”