- Published on
Boyce-Codd Normal Form (BCNF) in DBMS – Definition, Examples, and Conversion
Table of Contents
Boyce-Codd Normal Form (BCNF) in DBMS – Definition, Examples, and Conversion
Boyce-Codd Normal Form (BCNF) is a stricter version of the Third Normal Form (3NF) used in relational database design. It addresses certain anomalies that 3NF cannot eliminate by requiring that every determinant in a table be a superkey.
🧠 What is BCNF?
A table is in BCNF if:
- It is already in 3NF
- For every functional dependency
X → Y, the determinantXis a superkey
This ensures that all non-trivial dependencies are based only on unique identifiers, eliminating more subtle forms of redundancy.
🧪 Example: Student_Course_Instructor Table
| Student_ID | Course_ID | Instructor |
|---|---|---|
| 101 | C101 | Dr. Brown |
| 102 | C101 | Dr. Brown |
| 103 | C102 | Dr. Smith |
| 104 | C103 | Dr. Lee |
⚠️ Functional Dependencies
Student_ID, Course_ID → Instructor(composite key uniquely identifies the instructor for a student-course pairing)Course_ID → Instructor(each course is taught by only one instructor)
The problem lies in Course_ID → Instructor:
👉 Course_ID is not a superkey in this table, which violates BCNF.
🔧 Converting to BCNF
We must decompose the table into two smaller tables where every determinant is a superkey.
✅ Step 1: Create the Course_Instructor Table
| Course_ID | Instructor |
|---|---|
| C101 | Dr. Brown |
| C102 | Dr. Smith |
| C103 | Dr. Lee |
Course_IDis now the primary key and a superkey.
✅ Step 2: Create the Student_Course Table
| Student_ID | Course_ID |
|---|---|
| 101 | C101 |
| 102 | C101 |
| 103 | C102 |
| 104 | C103 |
- Composite key
{Student_ID, Course_ID}is a superkey in this table.
✅ Result After Applying BCNF
Both resulting tables now satisfy BCNF:
- Every determinant is a superkey
- Functional dependencies are cleanly separated
- Redundancy is removed
- Data integrity is improved
💡 Why BCNF Matters
| Benefit | Description |
|---|---|
| 📦 Redundancy-Free | Removes repeated values that 3NF might miss |
| 🔐 Better Integrity | Prevents anomalies caused by improper dependencies |
| ⚙️ Efficient Design | Makes schema cleaner, faster to query, and easier to maintain |
📌 Summary
| Feature | Description |
|---|---|
| Normal Form | Boyce-Codd Normal Form (BCNF) |
| Requirements | Table in 3NF, and every determinant must be a superkey |
| Fix | Decompose into tables where each dependency uses a superkey |
| Purpose | Handle edge cases not covered by 3NF and prevent advanced anomalies |