BCNF in DBMS
BCNF stands for Boyce–Codd Normal Form.
What is the Normal form?
The normal form is mainly used to reduce the redundancy of the database tables.
Or we can say that the normal form is a step-by-step process and each step is known as the normal form.
In other words, the normal form is a stage at which we organize the database tables.
There is a different types of normal forms:
- 1NF( First normal form)
- 2NF( Second normal form)
- 3NF( Third normal form)
- BCNF( Boyce-Codd normal form)
What is Normalization?
Normalization is a process in the database management system that is used to organize the data with its attributes and it also performs data redundancy to check if the data is stored logically or not.
What is BCNF (Boyce-Codd Normal Form)?
BCNF is known as Boyce-Codd Normal Form, it is a special case of 3NF (normal form) and we can also say that it is also known as 3.5 NF. A relation to BCNF (Boyce-Codd normal form) exists if and only if the determinants are candidate key.
Or we can say that BCNF is a normal form in the normalization of databases and its rules are stricter than the 3 NF. It is based on the functional dependencies that take all the determinants of all candidate keys in a relationship.
As we all know, the third normal form doesn’t remove all the redundancy from the database tables in cases where a functional dependency says that B->C, in this case, B is not a candidate key of the database table. BCNF was introduced to deal with such situations.
A table is said to be in form of BCNF if the table is already in the form of the third normal form (3NF) in the DBMS.
And we also know that for every functional dependency, we should have an attribute in the from of candidate keys, let's say (A->B), A is either the super key or the candidate key. In the simple words, for any case, A cannot be a non-prime attribute.
Some Rules for BCNF in DBMS
Suppose, we want to check if the table satisfies the conditions of BCNF, then we need to follow two conditions as follows:
- First, the table should be in the form of the third normal form (3 NF).
- For any dependency A→B, in this A must be a super key or a candidate key. In other words, for the function dependency A→ B, if B is a prime attribute of the table, then A cannot be a non-prime attribute of the table.
In the state of 3NF, the transitive dependency must not exist. Transitive dependency is known as the Left-hand side (LHS) of the functional dependency.
It must contain a super key or the candidate key or functional dependency right-hand side (RHS) should be a prime attribute.
Example of BCNF in DBMS
Example 1:
Suppose we have a hospital table where the employees work in more than one department.
EMPLOYEE TABLE
| EMP_ID | NATIONALITY | EMP_DEPT | DEPT_TYPE | DEPT_NO |
| E001 | India | Surgery | X12 | 301 |
| E002 | Canada | Dental | X12 | 402 |
| E001 | India | General Medicine | X97 | 212 |
| E004 | Pakistan | Radiology | X97 | 356 |
Functional dependencies of this table:
- EMP_ID → Nationality
- EMP_DEPT → {DEPT_TYPE, DEPT_NO}
Candidate key of this table:
- {EMP_ID, EMP_DEPT}
In this example, the table is not BCNF form as both EMP_ID and EMP_DEPT alone are not keys. If we want to convert this table into BCNF form, we need to decompose the table into three tables based on the functional dependency of the table.
NATIONALITY TABLE
| EMP_ID | NATIONALITY |
| E001 | India |
| E002 | Canada |
| E004 | Pakistan |
DEPT TABLE
| EMP_DEPT | DEPT_TYPE | DEPT_NO |
| Surgery | X12 | 301 |
| Dental | X12 | 402 |
| General Medicine | X97 | 212 |
| Radiology | X97 | 356 |
DEPT Mapping table
| EMP_ID | EMP_DEPT |
| E001 | Surgery |
| E002 | Dental |
| E001 | General Medicine |
| E004 | Radiology |
Functional dependencies
- EMP_ID → Nationality
- EMP_DEPT→ {DEPT_TYPE, DEPT_NO}
Candidate key
- Nationality Table: EMP_ID
- DEPT Table: EMP_DEPT
- DEPT Mapping Table: {EMP_ID, EMP_DEPT}
The relation is now in BCNF form because it satisfies both conditions which are that the table is already in 3NF form and on the LHS of the functional dependency there is a candidate key.
Example: Let's assume we have a company where employees work in more than one department.
EMPLOYEE TABLE
| EMP_ID | EMP_COUNTRY | EMP_DEPT | DEPT_TYPE | EMP_DEPT_NO |
| 250 | India | IT | D395 | 183 |
| 250 | India | Digital Marketing | D395 | 200 |
| 362 | Pakistan | Stores | D284 | 132 |
| 362 | Pakistan | Developing | D284 | 449 |
In the above table, Functional dependencies are as follows:
- EMP_ID → EMP_COUNTRY
- EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}
Candidate key: {EMP-ID, EMP-DEPT}
The table is not in BCNF because neither EMP_DEPT nor EMP_ID alone are keys.
To convert the given table into BCNF, we decompose it into three tables:
EMP_COUNTRY table:
| EMP_ID | EMP_COUNTRY |
| 250 | India |
| 250 | India |
EMP_DEPT table:
| EMP_DEPT | DEPT_TYPE | EMP_DEPT_NO |
| IT | D395 | 183 |
| Digital Marketing | D395 | 200 |
| Stores | D284 | 132 |
| Developing | D284 | 449 |
EMP_DEPT_MAPPING table:
| EMP_ID | EMP_DEPT |
| 250 | IT |
| 250 | Digital Marketing |
| 362 | Stores |
| 362 | Developing |
Functional dependencies:
- EMP_ID → EMP_COUNTRY
- EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}
Candidate keys:
For the first table: EMP_ID
For the second table: EMP_DEPT
For the third table: {EMP_ID, EMP_DEPT}
Now, this is in BCNF because left side part of both the functional dependencies is a key.