Boyce-Codd Normal Form (BCNF) in DBMS – Definition, Examples, and Conversion

• Boyce-Codd Normal Form (BCNF) in DBMS – Definition, Examples, and Conversion
  • 🧠 What is BCNF?
  • 🧪 Example: Student_Course_Instructor Table
  • ⚠️ Functional Dependencies
  • 🔧 Converting to BCNF
  • ✅ Result After Applying BCNF
  • 💡 Why BCNF Matters
  • 📌 Summary

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 determinant X is 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

⚠️ 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 is now the primary key and a superkey.

✅ Step 2: Create the Student_Course Table

• 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

📌 Summary