Boyce-Codd Normal Form (BCNF) vs. 3NF

Medium

Interview Question: "What is Boyce-Codd Normal Form (BCNF), and how does it differ from 3NF? Give a concrete example of a schema that is in 3NF but violates BCNF, and explain the fundamental trade-off between BCNF and Dependency Preservation."

3NF has a loophole. BCNF closes it. That's really the whole difference — but the loophole causes real bugs, so it's worth seeing exactly where it bites.

---

The one-line rule difference

For every non-trivial functional dependency $X \to Y$ (where $Y \not\subseteq X$), here's what each normal form demands of $X$:

Normal Form	What $X$ has to be
3NF	A superkey — OR $Y$ is part of some candidate key (a "prime attribute")
BCNF	A superkey. No exceptions.

3NF has an escape hatch: if $Y$ happens to be part of a candidate key, the rule looks the other way even when $X$ can't uniquely identify a row. BCNF removes that escape hatch entirely.

---

A table that's valid 3NF but is still broken: Student, Subject, Advisor

Here are the rules of a university advising system:

1. A student can take many subjects.
2. For each subject a student takes, they get exactly one advisor.
3. Each advisor only specializes in one subject.
4. Many advisors can teach the same subject.

That gives one table: StudentAdvising(StudentID, Subject, Advisor).

Dependencies:

• {StudentID, Subject} → Advisor (a student-subject pair has one advisor)
• Advisor → Subject (each advisor only teaches one subject)

Candidate keys: {StudentID, Subject}, and also {StudentID, Advisor} — because knowing the advisor already tells you the subject. These two keys overlap on StudentID, and because of that overlap, every column in the table ends up being part of some candidate key ("prime").

Does it pass 3NF? Check Advisor → Subject: is Advisor a superkey? No — one advisor has many students. Is Subject prime? Yes, it's part of {StudentID, Subject}. The escape hatch applies, so this table is valid 3NF.

Does it pass BCNF? Same dependency, but BCNF doesn't care whether Subject is prime — it only asks "is Advisor a superkey?" No. So this table violates BCNF, even though it's perfectly valid 3NF.

StudentAdvising (valid 3NF, but still broken):
+-----------+---------+------------+
| StudentID | Subject | Advisor    |
+-----------+---------+------------+
| S101      | Physics | Dr. Robert |
| S102      | Physics | Dr. Robert |  <-- "Dr. Robert teaches Physics" is repeated
| S103      | Math    | Dr. Gauss  |
+-----------+---------+------------+

And that repetition causes real anomalies:

1. Update anomaly: Dr. Robert switches to teaching Quantum Mechanics — you have to find and update every row with his name, or the data goes inconsistent.
2. Insertion anomaly: You can't add a new Chemistry advisor until a student actually signs up with them, because StudentID is part of the primary key and can't be NULL.
3. Deletion anomaly: If S103 drops out, deleting their row also erases the only record that Dr. Gauss advises Math.

---

Fixing it: split the table

Decompose along the dependency that was causing trouble, Advisor → Subject:

3NF (one table)

📋StudentAdvising(StudentID, Subject, Advisor)

'Dr. Robert teaches Physics' repeated per student

split on Advisor → Subject

BCNF (two tables)

🗂️AdvisorSubject(Advisor PK, Subject)

one row per advisor

🗂️StudentAdvisor(StudentID, Advisor)

one row per enrollment

Now Advisor is the primary key of its own table, so it's a superkey — BCNF satisfied, and all three anomalies above are gone: you can add Dr. Curie before any student signs up, and deleting a student never erases an advisor's subject.

---

The catch: you can lose the ability to enforce a rule

3NF guarantees two things at once: a lossless decomposition, and every original dependency can still be checked with a simple key constraint. BCNF only guarantees the first.

Here's why: the original rule was {StudentID, Subject} → Advisor — a student can't have two different advisors for the same subject. After splitting into AdvisorSubject and StudentAdvisor, StudentID and Subject now live in different tables. There's no primary-key or unique constraint left that can enforce "this student doesn't have two advisors for the same subject" — you'd need an expensive JOIN across both tables on every write just to check it.

Because of that, real-world schemas often deliberately stay in 3NF and enforce the extra rule with triggers or application logic, rather than decomposing all the way to BCNF and losing the ability to check it with a constraint.

---

Say this in the interview

"3NF has a loophole: a dependency X → Y is fine if Y is part of a candidate key, even if X isn't a superkey. BCNF removes that loophole — X always has to be a superkey. The classic case where this bites is overlapping candidate keys, like a Student-Subject-Advisor table: it passes 3NF but still has update, insertion, and deletion anomalies. Decomposing it into BCNF fixes the anomalies but can break dependency preservation — a rule that used to be a simple key constraint might now need an expensive cross-table join to enforce."

---

Code: watching the anomalies happen, then disappear

The following Python script models the Student-Subject-Advisor schema in SQLite, demonstrating the insertion and deletion anomalies in 3NF and verifying the decomposed BCNF schema.

import sqlite3

def run_bcnf_demo():
    conn = sqlite3.connect(":memory:")
    cursor = conn.cursor()

    print("--- 1. Demonstrating 3NF Anomalies ---")
    cursor.execute("""
        CREATE TABLE StudentAdvising_3NF (
            student_id TEXT,
            subject TEXT,
            advisor TEXT,
            PRIMARY KEY (student_id, subject)
        );
    """)

    # Populate 3NF table
    cursor.execute("INSERT INTO StudentAdvising_3NF VALUES ('S101', 'Physics', 'Dr. Robert');")
    cursor.execute("INSERT INTO StudentAdvising_3NF VALUES ('S102', 'Physics', 'Dr. Robert');")
    cursor.execute("INSERT INTO StudentAdvising_3NF VALUES ('S103', 'Math', 'Dr. Gauss');")

    # Insertion Anomaly: Cannot insert an advisor without a student
    try:
        cursor.execute("INSERT INTO StudentAdvising_3NF VALUES (NULL, 'Chemistry', 'Dr. Curie');")
    except sqlite3.IntegrityError as e:
        print("[Insertion Anomaly Confirmed] Cannot hire Dr. Curie without a student: " + str(e))

    # Deletion Anomaly: Removing student S103 deletes all record of Dr. Gauss advising Math
    cursor.execute("DELETE FROM StudentAdvising_3NF WHERE student_id = 'S103';")
    cursor.execute("SELECT * FROM StudentAdvising_3NF WHERE advisor = 'Dr. Gauss';")
    if not cursor.fetchall():
        print("[Deletion Anomaly Confirmed] S103 deleted; record of Dr. Gauss advising Math was lost!")

    print("\n--- 2. Decomposing into BCNF ---")
    cursor.execute("""
        CREATE TABLE AdvisorSubject_BCNF (
            advisor TEXT PRIMARY KEY,
            subject TEXT NOT NULL
        );
    """)
    cursor.execute("""
        CREATE TABLE StudentAdvisor_BCNF (
            student_id TEXT NOT NULL,
            advisor TEXT NOT NULL,
            PRIMARY KEY (student_id, advisor),
            FOREIGN KEY (advisor) REFERENCES AdvisorSubject_BCNF(advisor)
        );
    """)

    # In BCNF: Hiring Dr. Curie without students is completely valid!
    cursor.execute("INSERT INTO AdvisorSubject_BCNF VALUES ('Dr. Curie', 'Chemistry');")
    cursor.execute("INSERT INTO AdvisorSubject_BCNF VALUES ('Dr. Gauss', 'Math');")
    cursor.execute("INSERT INTO StudentAdvisor_BCNF VALUES ('S103', 'Dr. Gauss');")

    # Deleting student S103 leaves advisor record intact!
    cursor.execute("DELETE FROM StudentAdvisor_BCNF WHERE student_id = 'S103';")
    cursor.execute("SELECT * FROM AdvisorSubject_BCNF WHERE advisor = 'Dr. Gauss';")
    res = cursor.fetchall()
    print("[BCNF Success] Dr. Gauss still exists in AdvisorSubject: " + str(res))

    conn.close()

if __name__ == "__main__":
    run_bcnf_demo()