Boyce Codd normal form (BCNF)
It is an advance version of 3NF that’s why it is also referred as 3.5NF. BCNF is stricter than 3NF. A table complies with BCNF if it is in 3NF and for every functional dependency X->Y, X should be the super key of the table.Example: Suppose there is a company wherein employees work in more than one department. They store the data like this:
emp_id | emp_nationality | emp_dept | dept_type | dept_no_of_emp |
1001 | Austrian | Production and planning | D001 | 200 |
1001 | Austrian | stores | D001 | 250 |
1002 | American | design and technical support | D134 | 100 |
1002 | American | Purchasing department | D134 | 600 |
emp_id -> emp_nationality
emp_dept -> {dept_type, dept_no_of_emp}
Candidate key: {emp_id, emp_dept}
The table is not in BCNF as neither emp_id nor emp_dept alone are keys.
To make the table comply with BCNF we can break the table in three tables like this:
emp_nationality table:
emp_id | emp_nationality |
1001 | Austrian |
1002 | American |
emp_dept | dept_type | dept_no_of_emp |
Production and planning | D001 | 200 |
stores | D001 | 250 |
design and technical support | D134 | 100 |
Purchasing department | D134 | 600 |
emp_id | emp_dept |
1001 | Production and planning |
1001 | stores |
1002 | design and technical support |
1002 | Purchasing department |
emp_id -> emp_nationality
emp_dept -> {dept_type, dept_no_of_emp}
Candidate keys:
For first table: emp_id
For second table: emp_dept
For third table: {emp_id, emp_dept}
This is now in BCNF as in both the functional dependencies left side part is a key.
Hope you got the concept.