Detailed explanation
Normalization is the process of organising relational tables using keys and functional dependencies to reduce unnecessary duplication and prevent insertion, update, and deletion anomalies. A functional dependency X → Y means that each value of X determines exactly one value of Y. For example, storing a customer's current city in every order requires several updates when that city changes; missing one creates inconsistent data. Normalization separates facts according to what determines them, rather than simply splitting large tables into smaller ones.
First Normal Form (1NF) requires each column value to be a single value in its intended domain, with no repeating groups or lists of separate values in one field. For example, student(id, phones = '9876,9123') stores multiple phone numbers in one field. Separate this into student(id, ...) and student_phone(student_id, phone), with one row per phone number. The pair (student_id, phone) can identify each phone association. Atomicity depends on intended use: a full name can remain one value if the application treats it as such.
Second Normal Form (2NF) requires 1NF and no partial dependency of a non-prime attribute on any candidate key. A non-prime attribute belongs to no candidate key. Consider enrollment(student_id, course_id, student_name, grade), whose candidate key is (student_id, course_id). The grade depends on the complete key, but student_name depends only on student_id. Move the name into student(student_id, student_name), leaving enrollment(student_id, course_id, grade). If every candidate key has one column, a 1NF table automatically satisfies 2NF.
Third Normal Form (3NF) additionally eliminates problematic transitive dependencies. In orders(id, cust_id, cust_city), assume id identifies an order and cust_id determines the customer's current city. Then id → cust_id → cust_city; keep orders(id, cust_id) and customers(cust_id, cust_city). Formally, for every non-trivial dependency X → A, either X must be a superkey or A must be prime. Interviewers expect you to identify dependencies, explain the anomaly, and propose a lossless decomposition, ideally preserving dependencies. Historical shipping city is a different fact: if it belongs to the order, retaining it is not this violation.
Key points to remember
- Normalization reduces redundant facts and insertion, update, and deletion anomalies.
- 1NF: store single values in the intended domain, without repeating groups.
- 2NF: satisfy 1NF and remove partial dependencies of non-prime attributes on candidate keys.
- 3NF: satisfy 2NF and remove transitive dependencies that violate the superkey-or-prime-attribute rule.
- Decompose tables so joins reconstruct the original facts without spurious rows; use foreign keys to maintain references.
Common mistakes candidates make
- Checking only the chosen primary key for 2NF instead of considering all candidate keys.
- Splitting tables without stating the functional dependencies and business assumptions.
- Assuming normalization always improves query speed; additional joins can increase read costs.
Likely follow-up questions
How is 3NF different from BCNF?
BCNF requires the determinant of every non-trivial functional dependency to be a superkey. 3NF also permits a dependency when its dependent attribute is prime, so some 3NF tables are not in BCNF.
What is a lossless decomposition?
A decomposition is lossless when joining the decomposed relations reconstructs exactly the original relation, without extra or missing rows. For a binary decomposition under functional dependencies, the common attributes must determine all attributes of at least one component.
When would you denormalize a database?
Denormalization can help when measured read-performance requirements justify storing duplicate or precomputed data. It introduces consistency work, so explain how updates will keep the duplicated facts synchronised.
Interview tip: Explain each normal form through a dependency, the anomaly it causes, and the tables that remove it.