Normalization interview questions, with answers

The questions, with answers

1. 1.What is normalization, and what problems does it solve?

   Normalization is the process of organising tables so that each fact is stored once, by splitting a table with redundant data into smaller tables linked by keys. It removes three anomalies. Update anomaly: the same fact stored in many rows must be changed everywhere, and usually isn't. Insertion anomaly: you can't record one fact without inventing another — no way to store a new department until it has an employee. Deletion anomaly: deleting the last row about something deletes the something — remove the only employee of a department and the department vanishes.

2. 2.What does First Normal Form require?

   Every column holds atomic (indivisible) values, and there are no repeating groups — no column like phone_numbers containing "98765, 91234", and no phone1, phone2, phone3 columns. Each row is uniquely identifiable. A table with a comma-separated list of skills in one column is the standard example of a 1NF violation; the fix is a separate Skills table with one row per (employee, skill). "Atomic" is relative to how the data is used: a full name in one column is fine until you need to sort by surname.

3. 3.What does Second Normal Form require, and what is a partial dependency?

   2NF is 1NF plus: every non-key column depends on the whole primary key, not on part of it. A partial dependency can only exist when the key is composite. Example: OrderItem(order_id, product_id, quantity, product_name) with key (order_id, product_id). product_name depends on product_id alone — it's the same for that product in every order — so it's a partial dependency. Fix: move product_name to Product(product_id, product_name). If a table's key is a single column it is automatically in 2NF once it's in 1NF, which is a useful thing to say.

4. 4.What does Third Normal Form require, and what is a transitive dependency?

   3NF is 2NF plus: no non-key column depends on another non-key column. That indirect chain — key → A → B — is a transitive dependency. Example: Employee(emp_id, name, dept_id, dept_name). dept_name depends on dept_id, which depends on emp_id; so dept_name depends on the key only transitively. Change a department's name and you must update every employee in it. Fix: Department(dept_id, dept_name) and keep only dept_id in Employee. The one-line test: every non-key column must depend on the key, the whole key, and nothing but the key.

5. 5.What is BCNF, and how is it different from 3NF?

   Boyce–Codd Normal Form: for every non-trivial functional dependency X → Y, X must be a superkey. 3NF makes an exception — it also allows X → Y when Y is part of some candidate key (a prime attribute). BCNF removes that exception, so BCNF is strictly stronger.

   The classic example: Enrolment(student, subject, tutor), where each tutor teaches exactly one subject and each student has one tutor per subject. Candidate key: (student, subject). But tutor → subject holds, and tutor is not a superkey. Because subject is a prime attribute, the table is in 3NF; because tutor is not a superkey, it is not in BCNF. Decompose into (tutor, subject) and (student, tutor).

6. 6.What is a functional dependency, and how do you find candidate keys from a set of them?

   X → Y means: whenever two rows agree on X, they agree on Y — X determines Y. A superkey is any set of columns that determines every column; a candidate key is a minimal superkey; a prime attribute is one that belongs to some candidate key. To find candidate keys, take an attribute set and compute its closure — everything it determines, applying dependencies repeatedly. If the closure is all attributes, it's a superkey; strip attributes while it stays a superkey to get a candidate key. Attributes that never appear on the right side of any dependency must be in every candidate key, which is the fastest place to start.

7. 7.What is denormalization, and when is it justified?

   Denormalization deliberately reintroduces redundancy — storing dept_name in Employee again, or a precomputed order_total — to avoid joins and aggregation on hot read paths. It is justified when reads vastly outnumber writes and the join cost is measured, not assumed: reporting tables, dashboards, search indexes, caches. The price is that the application must now keep the copies consistent, typically with triggers or in the write path. The answer interviewers want: normalize the source of truth, denormalize derived read models, and know which is which.

8. 8.Is a higher normal form always better?

   No. Each decomposition trades redundancy for joins, and joins cost time at read. 3NF or BCNF is the practical target for transactional schemas; most well-designed tables reach it naturally. 4NF (removing multi-valued dependencies — two independent one-to-many facts in one table) and 5NF exist and are worth naming, but designing past BCNF is rare in practice. Two things to check on any decomposition: it must be lossless (joining the pieces reconstructs exactly the original rows) and ideally dependency-preserving (every original dependency can still be enforced within one table) — BCNF sometimes has to give up the second, which is the real reason 3NF is still taught.

How the diagnostic asks it

One question from the DBMS bank, exactly as a sitting would show it. The bank has 4 on normalization and 30 across DBMS.