Database DesignNormalizationRelational Databasesjunior level

Database Normalization Through BCNF

the question

What is database normalization? Walk me through 1NF, 2NF, 3NF, and BCNF, and explain what problem each form addresses.

what they're testing: The interviewer is testing whether you can reason about keys and dependencies well enough to prevent data anomalies in a relational schema.

a strong answer

Normalization organizes relational tables so each fact has one appropriate home. You identify candidate keys and functional dependencies, then split relations whose dependencies cause repeated facts. This reduces insert, update, and delete anomalies. It does not guarantee faster reads because decomposition can add joins.

The forms are cumulative:

1NF: each row is unique, each row-column intersection holds one value, and there are no repeating groups.
2NF: the relation is in 1NF, and no non-prime attribute depends on a proper subset of any candidate key. Partial dependencies usually appear with composite keys.
3NF: for every non-trivial functional dependency X → A, either X is a superkey or A is prime, meaning it belongs to a candidate key. This removes transitive dependencies to non-prime attributes while allowing the prime-attribute exception.
BCNF: for every non-trivial functional dependency X → Y, X must be a superkey. BCNF removes the 3NF exception and is therefore stricter.

For example, OrderItem(order_id, product_id, product_name, quantity) has the key (order_id, product_id). If product_id determines product_name, that partial dependency violates 2NF. Moving product details to Product stores the product name once. Denormalize only for a measured workload, with a clear plan for keeping duplicated data consistent.

Where people slip

the tempting wrong answer, and what's actually true

Normalization is mainly a way to save disk space.
Its central purpose is to reduce redundancy and the insert, update, and delete anomalies caused by misplaced dependencies.
A table must have a composite key to be in 2NF.
2NF does not require a composite key. It requires 1NF and forbids a non-prime attribute from depending on a proper subset of any candidate key; composite keys are simply where partial dependencies usually arise.
3NF and BCNF are the same because both remove transitive dependencies.
BCNF is stricter because every determinant in a non-trivial functional dependency must be a superkey, while 3NF permits a prime attribute on the right.
A more normalized schema always makes queries faster.
Normalization can add tables and joins, so read performance may worsen even while data integrity improves.

If they push further

Can a table be in 3NF but not BCNF?

Yes. In `Teaching(student, course, instructor)`, with keys `(student, course)` and `(student, instructor)`, the dependency `instructor → course` is allowed by 3NF because `course` is prime, but it violates BCNF because `instructor` is not a superkey.

When would you denormalize a schema?

Do it for a measured workload, such as an expensive read path, and make the resulting duplication and consistency mechanism explicit.

What are insertion, update, and deletion anomalies?

Explain them as being unable to store one fact independently, having to change the same fact in several rows, or losing an unrelated fact when deleting a row.

Sources

Now answer it yourself.

Reading a strong answer is easy. Producing one under pressure is the skill the interview tests. Gapmap grades your answer against the same bar an interviewer would.

was this useful?

beta

The interviewer part is in the works.

The diagnostic, personal maps, and AI mock interviews are being finished right now. The notes stay free either way. Leave an email and you'll get the first-cohort invite, plus a month of Pro when it opens.