Topic 25 of 52 · Advanced
Normalization & Schema Design
1NF, 2NF, 3NF, BCNF and when to denormalise.
Concept
Normalization organises tables to remove redundancy and update anomalies. 1NF: atomic values, no repeating groups.
2NF: no partial dependency on part of a composite key. 3NF: no transitive dependency (non-key → non-key). BCNF: every determinant is a candidate key.
Analytics systems often denormalise (star schema: fact table + dimension tables) to make reads faster.
Cheat sheet
| Form | Rule |
|---|---|
| 1NF | Atomic values, unique rows |
| 2NF | 1NF + no partial dependency |
| 3NF | 2NF + no transitive dependency |
| BCNF | Every determinant is a candidate key |
Code (SQL · MySQL 8)
-- Bad: order(order_id, customer_name, customer_city, product_list)
-- Good (3NF):
CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10,2));
CREATE TABLE order_items (
order_id INT, product_id INT, qty INT,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);Remember
- Many-to-many needs a junction table.
- OLTP → normalise; OLAP → star schema.
- Anomalies: insert, update, delete.
Interview questions
Explain 3NF with an example.
In employee(emp_id, dept_id, dept_name), dept_name depends on dept_id, not emp_id. Move dept_name to a departments table.
When would you denormalise?
For read-heavy reporting where joins are expensive, accepting extra storage and update complexity.
Lesson test
Take an 8-question test on “Normalization & Schema Design” — concepts, output prediction, bug spotting and interview questions.