Answer
Database Normalization
Normalization is the process of structuring a database to reduce redundancy and improve data integrity by following a set of normal forms (NF).
Unnormalized Table (Problems)
SQL
-- Single table with all data mixed in
| order_id | customer | phone | product1 | product2 | city | state |
|----------|----------|-----------|-----------|-----------|----------|-------|
| 1 | Alice | 9876543210 | Laptop | Mouse | Bangalore| KA |
| 2 | Bob | 9123456789 | Keyboard | NULL | Mumbai | MH |
-- Problems:
-- 1. Multiple products in one row (repeating groups)
-- 2. Update anomaly: if Alice moves, update multiple rows
-- 3. Delete anomaly: delete order 1 → lose Alice's info
-- 4. Insert anomaly: can''t add customer without an order
1NF — First Normal Form
Rule: Each column must hold atomic (single) values. No repeating groups. Each row must be unique.
SQL
-- ❌ Violates 1NF: multiple products in one row
| order_id | customer | products |
|----------|----------|----------------|
| 1 | Alice | Laptop, Mouse |
-- ✅ 1NF: one product per row
| order_id | customer | product |
|----------|----------|---------|
| 1 | Alice | Laptop |
| 1 | Alice | Mouse |
| 2 | Bob | Keyboard|
2NF — Second Normal Form
Rule: Must be in 1NF + no partial dependency (non-key column depends on only part of a composite primary key).
SQL
-- Table with composite PK: (order_id, product)
-- ❌ Violates 2NF: customer_name depends only on order_id, not on (order_id + product)
| order_id | product | customer_name | qty |
|----------|----------|--------------|-----|
| 1 | Laptop | Alice | 1 |
| 1 | Mouse | Alice | 2 | ← Alice repeated!
-- ✅ 2NF: Split into two tables
-- orders table (order_id → customer_name)
| order_id | customer_name |
|----------|--------------|
| 1 | Alice |
| 2 | Bob |
-- order_items table (order_id + product → qty)
| order_id | product | qty |
|----------|---------|-----|
| 1 | Laptop | 1 |
| 1 | Mouse | 2 |
| 2 | Keyboard| 1 |
3NF — Third Normal Form
Rule: Must be in 2NF + no transitive dependency (non-key column depends on another non-key column).
SQL
-- ❌ Violates 3NF: zip_code → city and state (transitive dependency!)
-- zip_code is not the PK, but city and state depend on it
| order_id | customer | zip_code | city | state |
|----------|----------|---------|-----------|-------|
| 1 | Alice | 560001 | Bangalore | KA |
| 2 | Bob | 400001 | Mumbai | MH |
-- ✅ 3NF: Move zip_code → city/state to its own table
-- orders table
| order_id | customer | zip_code |
|----------|----------|---------|
| 1 | Alice | 560001 |
| 2 | Bob | 400001 |
-- zip_codes table
| zip_code | city | state |
|---------|-----------|-------|
| 560001 | Bangalore | KA |
| 400001 | Mumbai | MH |
Summary
| Normal Form | Eliminates |
|---|---|
| 1NF | Repeating groups, multi-valued cells |
| 2NF | Partial dependencies on composite PK |
| 3NF | Transitive dependencies between non-key columns |
Denormalization — When to Break the Rules
SQL
-- Sometimes purposely denormalize for read performance
-- E.g., in a reporting table or data warehouse:
CREATE TABLE order_summary AS
SELECT o.order_id, c.customer_name, c.email,
p.product_name, oi.quantity, oi.price
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id;
-- Redundant but fast for reports — no JOINs needed