Boyce-Codd Normal Form (BCNF)
A more sophisticated form of the Third Normal Form (3NF) is the Boyce-Codd Normal Form (BCNF). It is stricter in its approach and eliminates certain types of redundancy that 3NF may allow.
What is BCNF?
If X is a table super key for each functional dependency (FD) X → Y, then the relation (table) is in BCNF. In simple terms, the left-hand side (LHS) of every functional dependency must be a super key.
Note: A table must already be in 3NF before checking for BCNF compliance.
Machine Learning Tutorial:–Click Here
Data Science Tutorial:-Click Here
Complete Advance AI topics:- CLICK HERE
Deep Learning Tutorial:- Click Here
Example Scenario
Think of a business where workers can be assigned to different divisions. Here’s an EMPLOYEE table:
| EMP_ID | EMP_COUNTRY | EMP_DEPT | DEPT_TYPE | EMP_DEPT_NO |
|---|---|---|---|---|
| 264 | India | Designing | D394 | 283 |
| 264 | India | Testing | D394 | 300 |
| 364 | UK | Stores | D283 | 232 |
| 364 | UK | Developing | D283 | 549 |
Functional Dependencies
EMP_ID → EMP_COUNTRYEMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}
Candidate Key: {EMP_ID, EMP_DEPT}
In this table, neither EMP_ID nor EMP_DEPT alone is a super key, which violates the BCNF condition. Hence, the table is not in BCNF.
BCNF Decomposition
We break this table down into three distinct tables in order to import it into BCNF:
1. EMP_COUNTRY Table
| EMP_ID | EMP_COUNTRY |
|---|---|
| 264 | India |
| 364 | UK |
2. EMP_DEPT Table
| EMP_DEPT | DEPT_TYPE | EMP_DEPT_NO |
|---|---|---|
| Designing | D394 | 283 |
| Testing | D394 | 300 |
| Stores | D283 | 232 |
| Developing | D283 | 549 |
3. EMP_DEPT_MAPPING Table
| EMP_ID | EMP_DEPT |
|---|---|
| 264 | Designing |
| 264 | Testing |
| 364 | Stores |
| 364 | Developing |
Functional Dependencies After Decomposition
EMP_ID → EMP_COUNTRYEMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}
Candidate Keys:
- EMP_COUNTRY Table:
EMP_ID - EMP_DEPT Table:
EMP_DEPT - EMP_DEPT_MAPPING Table:
{EMP_ID, EMP_DEPT}
Now, all the functional dependencies satisfy the BCNF rule—each LHS is a super key. Therefore, the design is in BCNF.
Complete Python Course with Advance topics:-Click Here
SQL Tutorial :-Click Here
Download New Real Time Projects :-Click here
Conclusion
BCNF helps eliminate redundancy more effectively than 3NF by enforcing stricter rules on functional dependencies. Decomposing relations into BCNF ensures higher data integrity and avoids anomalies during data operations.
For more database concepts, stay connected with Updategadh.
boyce-codd normal form in dbms
boyce-codd normal form example pdf
bcnf example
boyce-codd normal form pronunciation
difference between 3nf and bcnf
explain the boyce-codd normal form (bcnf how is it related to other normal forms)
boyce-codd normal form pdf
bcnf condition
