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:

• 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:

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

EMP_DEPT

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