BCNF & Denormalization

Your table passes 3NF. You're pleased.

Then someone updates a row, and a fact you never touched changes meaning.

3NF wasn't quite enough.

Why & what

3NF has a small gap. It allows a column that isn't a key to decide another column, as long as that other column happens to be part of a key. Rare, but real — and when it bites, the repetition comes back.

BCNF closes that gap with a single, stricter sentence.

BCNF (Boyce–Codd Normal Form): every time some column decides another, the deciding column must be a key.

And there's the opposite direction to know as well. Denormalization means deliberately putting repeated data back in, to make reads faster.

How it works

Take a table (student, subject, teacher), where a teacher teaches exactly one subject, and a student can study a subject with any teacher.

1. Check the key. (student, subject) identifies a row. Both are key columns, so there are no non-key columns at all — which means it passes 3NF automatically.
2. Find the odd dependency. teacher → subject. Rao teaches DBMS, so knowing the teacher tells you the subject.
3. Ask the BCNF question. Is teacher a key of this table? No. So a non-key column is deciding something. It fails BCNF, even though it passed 3NF.
4. Fix it by splitting. Teaches(teacher, subject) and StudentTeacher(student, teacher). Now every decider is a key of its own table.
5. Now go the other way on purpose. Your dashboard shows "student name + course title + instructor" and joins four tables every time. It's slow. So you copy course_title back into a reporting table, accepting the repetition to remove the joins. That's denormalization — a knowing trade, not a mistake.

Notice: normalize by default — denormalize only when a slow query proves you must.

Common confusion

"BCNF is just a stricter 3NF" is true but too vague to use. Here's the sharp version:

• 3NF allows a non-key column to decide something, if the thing it decides is part of a key.
• BCNF allows no exceptions at all. Every decider must be a key.

So every BCNF table is in 3NF, but not the reverse — the same nesting shape you saw with keys in 3.2.

Second confusion: people think denormalization means "bad design" or "lazy." It doesn't. The difference is intent and evidence:

• Never normalized in the first place → a mistake.
• Normalized properly, then measured a slow query, then deliberately traded → engineering.

Say it that way in an interview and you sound like someone who's shipped something.

Last one: normalizing is not free. More tables means more joins, and joins cost time. That cost is exactly why 3NF is the usual stopping point rather than pushing to the highest form available.

Interview angle

1. "What's the difference between 3NF and BCNF?" — BCNF requires every decider to be a key, with no exception for key columns. Add that BCNF is stricter, so every BCNF table is in 3NF.
2. "When would you denormalize?" — When a measured read is too slow and the join count is the cause — typically reporting or dashboard tables. Stress that you measure first and accept the repetition knowingly.
3. "What's the cost of over-normalizing?" — More tables, more joins, slower reads, and more complex queries to write and maintain.
4. "Is a table in 3NF always in BCNF?" — No. Give the (student, subject, teacher) case: it passes 3NF but fails BCNF because teacher decides subject without being a key.

Recap

BCNF closes 3NF's last gap by demanding that every deciding column be a key, while denormalization deliberately reverses normalization to buy read speed — measured first, never by accident.