Normalisation is organising tables so each fact is stored once. That removes update anomalies (changing a value in one row but not another), insert anomalies and delete anomalies. Walk one table through the steps.
Starting point
OrderData
order_id customer city products
101 Asha Pune Pen:2, Notebook:1
102 Ravi Mumbai Pen:5
1NF: atomic values, no repeating groups
Each cell holds one value and each row is unique.
order_id product qty customer city
101 Pen 2 Asha Pune
101 Notebook 1 Asha Pune
102 Pen 5 Ravi Mumbai
Key: (order_id, product)
2NF: no partial dependency
Every non-key column must depend on the whole key. Here customer and city depend only on order_id, part of the key. Split them out.
Orders(order_id PK, customer, city)
OrderItems(order_id, product, qty) PK (order_id, product)
3NF: no transitive dependency
Non-key columns must depend only on the key, not on another non-key column. city depends on the customer, not on the order. Split again.
Customers(customer_id PK, name, city)
Orders(order_id PK, customer_id FK)
OrderItems(order_id FK, product_id FK, qty)
Products(product_id PK, name, price)
Now a customer moving city is one update in one row.
BCNF in one line
Every determinant must be a candidate key. It is a stricter 3NF; most 3NF tables already satisfy it.
When to denormalise
Reporting and read-heavy screens sometimes copy data (for example storing order_total on the order, or a reporting table) to avoid many joins. Do it on purpose, keep the copy in sync, and say so in the interview: it shows you know the trade-off.
Follow-ups
- What are insert, update and delete anomalies?
- What is a candidate key versus a primary key?
- Is a highly normalised schema always faster?
This answer was written by Debugger’s Space as practice material. It is asked in most Indian IT services interviews (TCS, Infosys, Wipro, HCLTech, Tech Mahindra, LTIMindtree, Accenture, Cognizant, Capgemini, IBM). See each company’s hiring steps and practice questions on Debugger’s Space.