What is normalisation? Explain 1NF, 2NF and 3NF.

Database normalisation explained with one table taken from unnormalised to 1NF, 2NF and 3NF, plus BCNF and when to denormalise.

0 0
Read Time:1 Minute, 44 Second

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.

Previous post What is the difference between INNER JOIN, LEFT JOIN and FULL JOIN?
Next post What do the ACID properties mean?
Subscribe
Notify of
guest

0 Comments
Oldest
Newest Most Voted
Close
0
Would love your thoughts, please comment.x
()
x