Normalization

Normalization interview questions

What problem does normalization actually solve?

Update anomalies caused by redundancy. If a fact is stored in many rows, an update must touch all of them; miss one and the database now holds two contradictory answers. Normalization ensures each fact lives in exactly one place.

Why they ask: Answering with the anomalies rather than 'it removes redundancy' shows you know why redundancy is bad.

What is first normal form?

Every column holds a single atomic value — no lists, no repeating groups like phone1/phone2/phone3, and every row is uniquely identifiable.

What is second normal form?

1NF, plus no partial dependencies: no non-key attribute depends on only part of a composite primary key. It can only be violated when the key is composite, so a single-column key means 2NF is automatic.

Why they ask: The 'only matters with composite keys' point is the part most people miss.

What is third normal form?

2NF, plus no transitive dependencies: no non-key attribute determines another non-key attribute. If employee → department and department → department_head, then department_head belongs in a separate departments table.

What is BCNF and how is it stricter than 3NF?

Boyce-Codd normal form requires that for every non-trivial dependency X → Y, X is a candidate key. 3NF allows an exception where Y is part of some candidate key, so a table can satisfy 3NF and still violate BCNF — usually with overlapping composite candidate keys.

Is BCNF always achievable?

No — and this is the nuance worth knowing. A decomposition into BCNF is always lossless, but it is not always dependency-preserving. Some schemas force a choice between BCNF and keeping every functional dependency enforceable within a single table.

Why they ask: Almost nobody volunteers this, and it's the difference between reciting the forms and understanding them.

What makes a decomposition lossless?

Joining the resulting tables reproduces exactly the original rows — nothing invented, nothing lost. It's guaranteed when the shared attributes of the two tables form a superkey of at least one of them.

What does fourth normal form address?

Multivalued dependencies: one key independently determining two unrelated multivalued facts, such as a person's skills and their languages. Storing both in one table forces a meaningless cross product, so they belong in separate tables.

When would you deliberately denormalize?

When reads dominate and joins are the bottleneck — analytics tables, caches, and precomputed aggregates. You accept redundancy and the write-time cost of keeping copies consistent in exchange for fewer joins.

Name the three anomalies normalization prevents.

Update — changing a fact requires touching many rows. Insert — you can't record one fact without inventing another, like being unable to add a department with no employees. Delete — removing the last row destroys unrelated information.

In practice, how far do people normalize?

Usually 3NF or BCNF. Beyond that the decompositions rarely pay for the extra joins in an operational system, so 4NF and 5NF tend to be exam material rather than daily practice.