Normalization: From 1NF to BCNF

Normalization is the process of structuring tables to eliminate redundancy and the update, insert, and delete anomalies that redundancy causes. Interviewers use it to test whether you understand why schemas look the way they do, not just how to memorize a list of normal forms. The whole subject reduces to one idea: every fact should be stored once, in the place determined by its keys.

Why redundancy is dangerous

Consider a single table that mixes students, the courses they take, and each instructor's office:

| student_id | student_name | course | instructor | instructor_office | |-----------|--------------|--------|-----------|-------------------| | 1 | Asha | DBMS | Rao | B-204 | | 1 | Asha | OS | Khan | C-110 | | 2 | Vik | DBMS | Rao | B-204 |

Three anomalies live here:

• Update anomaly: if Rao moves office, you must update every DBMS row or the data becomes inconsistent.
• Insertion anomaly: you cannot record a new instructor's office until some student enrolls in their course.
• Deletion anomaly: if the last student drops OS, you lose the fact that Khan is in C-110.

Normalization systematically removes these by splitting the table according to its functional dependencies.

Functional dependencies: the foundation

A functional dependency X → Y means: for any two rows with the same X, the value of Y must be identical. X determines Y. In the table above, instructor → instructor_office (an instructor has one office) and student_id → student_name. Finding the candidate keys means finding the minimal set of attributes whose closure determines every other attribute. Every normalization decision is a statement about which dependencies are allowed to remain.

The following widget walks the same messy table from unnormalized form through each normal form, highlighting exactly which anomaly is fixed at each step.

First Normal Form (1NF): atomic values

A table is in 1NF when every cell holds a single, atomic value and there are no repeating groups. A phone_numbers column containing "555-1234, 555-9999" violates 1NF. The fix is to break the multivalued attribute into separate rows or a separate table. 1NF is the price of admission to the relational model.

Second Normal Form (2NF): no partial dependencies

2NF applies when the primary key is composite. A table is in 2NF if it is in 1NF and every non-key attribute depends on the whole key, not just part of it. Suppose the key is (student_id, course) and we store student_name. Since student_id → student_name alone, student_name depends on only part of the key — a partial dependency. Split students into their own table. If your primary key is a single column, the table is automatically in 2NF.

Third Normal Form (3NF): no transitive dependencies

3NF is in 2NF and has no transitive dependency: a non-key attribute must not determine another non-key attribute. In an orders table with order_id → customer_id → customer_city, the city depends on the customer, which depends on the order — transitively. Storing customer_city here repeats it for every order. Move customer attributes to a customers table. Formally, for every dependency X → Y, either X is a superkey or Y is a prime attribute.

Boyce-Codd Normal Form (BCNF): the strict version

BCNF tightens 3NF: for every non-trivial dependency X → Y, X must be a superkey — no exceptions for prime attributes. BCNF only differs from 3NF when a table has multiple overlapping candidate keys. The canonical example: (student, subject) → teacher and also teacher → subject (each teacher teaches one subject). Here teacher determines subject but is not a superkey, so the table is in 3NF but not BCNF. Decompose to eliminate it — though note that BCNF decomposition can sometimes sacrifice dependency preservation, a genuine trade-off.

The normal form ladder

| Form | Requirement | Removes | |------|-------------|---------| | 1NF | Atomic values, no repeating groups | Multivalued cells | | 2NF | 1NF + no partial dependency on a composite key | Partial dependencies | | 3NF | 2NF + no transitive dependency | Transitive dependencies | | BCNF | Every determinant is a superkey | Anomalies from overlapping keys |

Denormalization: breaking the rules on purpose

Normalization optimizes for write consistency but multiplies joins on reads. In read-heavy systems, engineers deliberately denormalize — storing customer_city back on the order, or keeping a precomputed order_count — to avoid expensive joins. The cost is that you now own the redundancy: every write must update all copies, often via triggers, application logic, or periodic jobs. The interview-worthy statement is: "Normalize until it hurts, then denormalize until it works." Data warehouses (star schemas) are intentionally denormalized because they are append-mostly and read-dominated.

What interviewers actually probe

1. Name the three anomalies and give a concrete example of each — this proves you understand the purpose, not just the definitions.
2. Distinguish 3NF from BCNF with the overlapping-candidate-key example. Most candidates stop at 3NF; knowing when BCNF matters signals depth.
3. Justify denormalization with a real trade-off: read latency versus write complexity and consistency risk.
4. Explain partial vs transitive dependency precisely — partial is about a piece of a composite key; transitive is one non-key attribute determining another.

Normalization is ultimately about placing each fact where its key says it belongs. If you can trace the functional dependencies, the correct decomposition falls out mechanically.