Normalisation removes duplicated facts from tables so each fact is stored in one place. The normal forms are steps. Each step removes one kind of redundancy. The quickest way to understand them is to fix one messy table step by step.
The starting point
An orders_sheet with the key (order_id, product_id), copied from a spreadsheet:
order_id | product_id | products | qty | customer_id | customer_city 1001 | 11, 12 | Pen, Notebook | 2,1 | C1 | Pune 1002 | 11 | Pen | 5 | C2 | Delhi
1NF: one value per cell
Lists like "11, 12" in one cell make it impossible to filter or join. Split to one row per (order, product):
order_id | product_id | product_name | qty | customer_id | customer_city 1001 | 11 | Pen | 2 | C1 | Pune 1001 | 12 | Notebook | 1 | C1 | Pune 1002 | 11 | Pen | 5 | C2 | Delhi
2NF: no partial dependency
The key is (order_id, product_id). product_name depends only on product_id, which is part of the key. customer_id depends only on order_id. Pull those out: products(product_id, product_name) and orders(order_id, customer_id, customer_city), with order_items(order_id, product_id, qty).
3NF: no transitive dependency
In orders, customer_city depends on customer_id, which depends on order_id. A non-key column depends on another non-key column. Move it: customers(customer_id, customer_city). Now a customer's city is stored once, and a move changes one row, not 400.
BCNF in one line
BCNF is a stricter 3NF: every column that determines another must be a candidate key. You rarely meet the difference outside textbooks.
Why warehouses denormalise afterwards
Normalised tables are good for writing safely (OLTP): no update anomalies, no contradictions. Analytical queries then need many joins. In a warehouse, you copy attributes back into wide dimensions (star schema) because reads dominate and storage is cheap. You normalise to be correct, then denormalise on purpose to be fast.