Boyce-Codd Normal Form

Every normal form is a rule about functional dependencies. Column A determines column B, written A -> B, when 2 rows that agree on A always agree on B. A candidate key is a set of columns that determines every other column, and a table can have more than 1 candidate key. 3NF requires that every column outside the keys depends on a whole candidate key and on nothing else. The rule says nothing about columns that sit inside a key, and that silence is the gap Boyce-Codd Normal Form closes.

A table that passes 3NF and still breaks

A tutoring marketplace records which tutor a student works with in each subject in sessions(student_id, subject, tutor_id). Two business rules hold: a student has 1 tutor per subject, and a tutor teaches exactly 1 subject.

The rules give 2 dependencies. {student_id, subject} -> tutor_id, because a student has 1 tutor per subject. tutor_id -> subject, because a tutor teaches 1 subject. That second dependency makes {student_id, tutor_id} a second key of the table: knowing the tutor tells you the subject. subject belongs to the key {student_id, subject}, so it counts as a key column, and 3NF exempts any dependency whose right side is a key column. That exemption lets tutor_id -> subject slip through, and the table passes.

Now watch it fail. Tutor T7 moves from Algebra to Geometry: every row carrying T7 must change, 2 rows in this sample and thousands in production, and a job that misses 1 leaves T7 teaching 2 subjects at once. Student S3 drops Algebra: the only row recording that T9 teaches Algebra disappears with the student. A new tutor T11 who teaches Chemistry can't be stored at all until a student signs up, because student_id is part of every key.

tutor_id isn't a candidate key of sessions, because it doesn't determine student_id. So tutor_id -> subject breaks BCNF. The fix moves that dependency into a table where its left side is the key.

The decomposition

tutor_subjects has 1 determinant, tutor_id, and it's the key. student_tutors has a 2-column key and no other columns, so nothing can depend on part of it. T7 moving to Geometry is now 1 row. S3 leaving deletes 1 row in student_tutors while T9 keeps teaching Algebra. T11 gets a row before any student arrives. The original 3-column view comes back with a join.

SELECT
    st.student_id,
    ts.subject,
    st.tutor_id
FROM student_tutors st
JOIN tutor_subjects ts
    ON st.tutor_id = ts.tutor_id

The price is that the rule {student_id, subject} -> tutor_id now spans 2 tables. A row pairing S1 with T7 and a second row pairing S1 with T9 would give S1 2 Algebra tutors, and neither table can reject the second row on its own. The check becomes a join that counts tutors per student per subject.

SELECT
    st.student_id,
    ts.subject,
    COUNT(*) AS tutors
FROM student_tutors st
JOIN tutor_subjects ts
    ON st.tutor_id = ts.tutor_id
GROUP BY st.student_id, ts.subject
HAVING COUNT(*) > 1

BCNF always removes the redundancy. It sometimes gives up the ability to enforce a dependency inside 1 table, and the engineer decides which costs more. For a tutor who changes subject once a year while thousands of session rows carry that subject, the join check wins.

A retail chain stores shifts(employee_id, store_id, manager_id). Each manager runs exactly 1 store, and each employee has exactly 1 manager at a given store. Which change brings the table into BCNF?

• Leave the table alone. Both store_id and manager_id are key columns, so no dependency between them can violate a normal form.
• Split into managers(manager_id, store_id) and shifts(employee_id, manager_id), and derive the store through a join when a query needs it.
• Split into employees(employee_id, store_id) and stores(store_id, manager_id), with 1 row per store in the second table.
• Add a shift_id column as a new single-column key so that every other column depends on that key alone.

About this lesson

This section is part of the Beyond 3NF lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.