Lesson 13 of 30
Normalization
Normalization organizes tables so each fact is stored once. A table that repeats facts in many rows allows contradictory data and forces the same change in many places. The rules are built on functional dependencies.
A functional dependency A -> B means each value of A determines exactly one value of B. For example, customer_id -> customer_city.
| Key term | Meaning |
|---|---|
| Super key | any set of columns that uniquely identifies a row |
| Candidate key | a minimal super key (removing any column breaks uniqueness) |
| Primary key | the candidate key chosen as the identifier |
| Composite key | a key made of more than one column |
| Prime attribute | a column that belongs to some candidate key |
| Normal form | Rule |
|---|---|
| 1NF | every column holds one atomic value; no repeating groups; rows are identifiable |
| 2NF | 1NF, and no non-key column depends on only part of a composite key |
| 3NF | 2NF, and no non-key column depends on another non-key column (no transitive dependency) |
| BCNF | for every dependency A -> B, A is a super key |
The demos use demo_ tables, dropped at the end.
Example 1: A wide table with repeated facts
One row per order line, with customer and product details copied into each row.
CREATE TABLE demo_wide (
order_id int,
product_id int,
qty int,
customer_id int,
customer_name text,
customer_city text,
product_name text,
product_price numeric(8,2),
PRIMARY KEY (order_id, product_id)
);
INSERT INTO demo_wide VALUES
(1, 10, 2, 100, 'Asha', 'Pune', 'Pen', 1.50),
(1, 20, 1, 100, 'Asha', 'Pune', 'Notebook', 4.00),
(2, 10, 5, 200, 'Bala', 'Mumbai', 'Pen', 1.50),
(3, 30, 1, 100, 'Asha', 'Pune', 'Stapler', 8.00);
SELECT * FROM demo_wide ORDER BY order_id, product_id; order_id | product_id | qty | customer_id | customer_name | customer_city | product_name | product_price
----------+------------+-----+-------------+---------------+---------------+--------------+---------------
1 | 10 | 2 | 100 | Asha | Pune | Pen | 1.50
1 | 20 | 1 | 100 | Asha | Pune | Notebook | 4.00
2 | 10 | 5 | 200 | Bala | Mumbai | Pen | 1.50
3 | 30 | 1 | 100 | Asha | Pune | Stapler | 8.00
(4 rows)The key is (order_id, product_id). Dependencies in this table:
order_id -> customer_id(partial: depends on part of the key)product_id -> product_name, product_price(partial)customer_id -> customer_name, customer_city(transitive: a non-key column determines other non-key columns)
Example 2: Update anomaly
Asha moves to Delhi. Updating only one row leaves the data contradicting itself.
UPDATE demo_wide SET customer_city = 'Delhi' WHERE order_id = 1 AND product_id = 10;
SELECT DISTINCT customer_id, customer_name, customer_city
FROM demo_wide
WHERE customer_id = 100
ORDER BY customer_city; customer_id | customer_name | customer_city
-------------+---------------+---------------
100 | Asha | Delhi
100 | Asha | Pune
(2 rows)Customer 100 now has two cities. The correct update touches every row of the customer, and the number of rows grows with each order.
UPDATE demo_wide SET customer_city = 'Delhi' WHERE customer_id = 100;Example 3: Insert anomaly
A new product cannot be stored until an order contains it, because order_id is part of the primary key and cannot be NULL.
INSERT INTO demo_wide (product_id, product_name, product_price) VALUES (40, 'Ruler', 0.75);ERROR: null value in column "order_id" of relation "demo_wide" violates not-null constraint
DETAIL: Failing row contains (null, 40, null, null, null, null, Ruler, 0.75).Example 4: Delete anomaly
Deleting order 2 removes the only row that mentions customer Bala.
DELETE FROM demo_wide WHERE order_id = 2;
SELECT count(*) AS rows_for_bala FROM demo_wide WHERE customer_name = 'Bala'; rows_for_bala
---------------
0
(1 row)The customer record is lost along with the order.
Example 5: Checking a functional dependency
A -> B holds in the data when no value of A maps to more than one value of B. This query lists the violations, and an empty result means the dependency holds (for the current rows).
SELECT customer_id, count(DISTINCT customer_city) AS cities
FROM demo_wide
GROUP BY customer_id
HAVING count(DISTINCT customer_city) > 1; customer_id | cities
-------------+--------
(0 rows)Data can only disprove a dependency. A dependency is a rule of the domain, so it is decided from the business rules and then enforced with keys.
Example 6: 1NF, atomic values
A column holding a list breaks 1NF. Searching, joining, and constraining the items becomes hard.
CREATE TABLE demo_orders_bad (order_id int PRIMARY KEY, products text);
INSERT INTO demo_orders_bad VALUES (1, 'Pen,Notebook'), (2, 'Pen');
SELECT order_id
FROM demo_orders_bad
WHERE products LIKE '%Pen%'; order_id
----------
1
2
(2 rows)LIKE '%Pen%' would also match Pencil. The 1NF form uses one row per item.
CREATE TABLE demo_order_items (
order_id int,
product text,
PRIMARY KEY (order_id, product)
);
INSERT INTO demo_order_items VALUES (1, 'Pen'), (1, 'Notebook'), (2, 'Pen');
SELECT order_id FROM demo_order_items WHERE product = 'Pen' ORDER BY order_id; order_id
----------
1
2
(2 rows)Example 7: 2NF, 3NF, and BCNF
2NF removes partial dependencies. In demo_wide, product_name depends only on product_id, so product facts move to a products table.
3NF removes transitive dependencies. customer_city depends on customer_id, which depends on order_id, so customer facts move to a customers table. A common summary: every non-key column depends on the key, the whole key, and nothing but the key.
BCNF is stricter than 3NF: every determinant must be a super key. A table breaks BCNF but not 3NF only when a non-key column determines a column that belongs to a candidate key. Example: a table (student, course, teacher) where each teacher teaches one course (teacher -> course) and a student has one teacher per course, so (student, course) is a key. teacher is not a key, so the table violates BCNF. The fix splits it into (teacher, course) and (student, teacher).
CREATE TABLE demo_enroll (
student text,
course text,
teacher text,
PRIMARY KEY (student, course)
);
INSERT INTO demo_enroll VALUES
('Isha', 'SQL', 'Rao'), ('Jay', 'SQL', 'Rao'), ('Isha', 'Python', 'Sen');
CREATE TABLE demo_teacher_course (teacher text PRIMARY KEY, course text NOT NULL);
CREATE TABLE demo_student_teacher (
student text,
teacher text REFERENCES demo_teacher_course(teacher),
PRIMARY KEY (student, teacher)
);
INSERT INTO demo_teacher_course SELECT DISTINCT teacher, course FROM demo_enroll;
INSERT INTO demo_student_teacher SELECT student, teacher FROM demo_enroll;
SELECT st.student, tc.course, tc.teacher
FROM demo_student_teacher st
JOIN demo_teacher_course tc ON tc.teacher = st.teacher
ORDER BY st.student, tc.course; student | course | teacher
---------+--------+---------
Isha | Python | Sen
Isha | SQL | Rao
Jay | SQL | Rao
(3 rows)The join reproduces the original three rows, and the rule teacher -> course is now enforced by the primary key of demo_teacher_course. BCNF decomposition can lose the ability to enforce some dependencies with keys alone, which is why 3NF is the usual practical target.
Example 8: Decompose the wide table and join it back
Rebuild demo_wide from its original data, split it into four normalized tables, and join to confirm no information is lost.
TRUNCATE demo_wide;
INSERT INTO demo_wide VALUES
(1, 10, 2, 100, 'Asha', 'Pune', 'Pen', 1.50),
(1, 20, 1, 100, 'Asha', 'Pune', 'Notebook', 4.00),
(2, 10, 5, 200, 'Bala', 'Mumbai', 'Pen', 1.50),
(3, 30, 1, 100, 'Asha', 'Pune', 'Stapler', 8.00);
CREATE TABLE demo_customers (id int PRIMARY KEY, name text NOT NULL, city text NOT NULL);
CREATE TABLE demo_products (id int PRIMARY KEY, name text NOT NULL, price numeric(8,2) NOT NULL);
CREATE TABLE demo_orders (id int PRIMARY KEY, customer_id int NOT NULL REFERENCES demo_customers(id));
CREATE TABLE demo_lines (
order_id int REFERENCES demo_orders(id),
product_id int REFERENCES demo_products(id),
qty int NOT NULL,
PRIMARY KEY (order_id, product_id)
);
INSERT INTO demo_customers SELECT DISTINCT customer_id, customer_name, customer_city FROM demo_wide;
INSERT INTO demo_products SELECT DISTINCT product_id, product_name, product_price FROM demo_wide;
INSERT INTO demo_orders SELECT DISTINCT order_id, customer_id FROM demo_wide;
INSERT INTO demo_lines SELECT order_id, product_id, qty FROM demo_wide;
SELECT l.order_id, c.name AS customer, c.city, p.name AS product, l.qty, p.price
FROM demo_lines l
JOIN demo_orders o ON o.id = l.order_id
JOIN demo_customers c ON c.id = o.customer_id
JOIN demo_products p ON p.id = l.product_id
ORDER BY l.order_id, l.product_id; order_id | customer | city | product | qty | price
----------+----------+--------+----------+-----+-------
1 | Asha | Pune | Pen | 2 | 1.50
1 | Asha | Pune | Notebook | 1 | 4.00
2 | Bala | Mumbai | Pen | 5 | 1.50
3 | Asha | Pune | Stapler | 1 | 8.00
(4 rows)A lossless check compares the join against the original table. Zero rows in both directions means identical contents.
SELECT count(*) AS differences FROM (
(SELECT l.order_id, l.product_id, l.qty, o.customer_id, c.name, c.city, p.name, p.price
FROM demo_lines l
JOIN demo_orders o ON o.id = l.order_id
JOIN demo_customers c ON c.id = o.customer_id
JOIN demo_products p ON p.id = l.product_id
EXCEPT SELECT * FROM demo_wide)
UNION ALL
(SELECT * FROM demo_wide
EXCEPT
SELECT l.order_id, l.product_id, l.qty, o.customer_id, c.name, c.city, p.name, p.price
FROM demo_lines l
JOIN demo_orders o ON o.id = l.order_id
JOIN demo_customers c ON c.id = o.customer_id
JOIN demo_products p ON p.id = l.product_id)
) d; differences
-------------
0
(1 row)The anomalies are gone. A city change is one row, and a product can exist without an order (product 40 has no lines).
UPDATE demo_customers SET city = 'Delhi' WHERE id = 100;
INSERT INTO demo_products VALUES (40, 'Ruler', 0.75);
SELECT id, name, city FROM demo_customers ORDER BY id; id | name | city
-----+------+--------
100 | Asha | Delhi
200 | Bala | Mumbai
(2 rows)Example 9: Denormalizing for reads
Denormalization deliberately stores redundant data to avoid joins or aggregation at read time. It fits read-heavy workloads where the cost of a join or a COUNT on every request is too high. The cost is extra writes and the risk of the copies drifting apart.
Typical forms: a summary column (orders.total), a precomputed count (customers.order_count), or a copied value that is expensive to look up.
ALTER TABLE demo_customers ADD COLUMN order_count int NOT NULL DEFAULT 0;
UPDATE demo_customers c
SET order_count = (SELECT count(*) FROM demo_orders o WHERE o.customer_id = c.id);
SELECT id, name, order_count FROM demo_customers ORDER BY id; id | name | order_count
-----+------+-------------
100 | Asha | 2
200 | Bala | 1
(2 rows)Something must keep order_count correct on every insert and delete: application code, a trigger (see Views and Routines), or a periodic rebuild. Normalize first, then denormalize measured hot spots.
Example 10: Star schema versus normalized OLTP
| Normalized OLTP | Star schema (analytics) | |
|---|---|---|
| Purpose | many small reads and writes | large scans and aggregates |
| Shape | many narrow tables, 3NF | one central fact table plus wide dimension tables |
| Redundancy | minimal | dimensions repeat attributes (city, category) |
| Joins | many, deep | few, one hop from fact to each dimension |
| Writes | cheap, one place per fact | batch loads |
The fact table holds measurements (quantity, amount) and foreign keys to dimensions (date, product, customer).
CREATE TABLE demo_dim_product (product_key int PRIMARY KEY, name text, category text);
CREATE TABLE demo_dim_customer (customer_key int PRIMARY KEY, name text, city text, region text);
CREATE TABLE demo_fact_sales (
sale_date date,
product_key int REFERENCES demo_dim_product,
customer_key int REFERENCES demo_dim_customer,
quantity int,
amount numeric(10,2)
);
INSERT INTO demo_dim_product VALUES (1, 'Pen', 'Stationery'), (2, 'Stapler', 'Office');
INSERT INTO demo_dim_customer VALUES (100, 'Asha', 'Pune', 'West'), (200, 'Bala', 'Mumbai', 'West');
INSERT INTO demo_fact_sales VALUES
('2026-01-05', 1, 100, 2, 3.00),
('2026-01-06', 2, 100, 1, 8.00),
('2026-02-01', 1, 200, 5, 7.50);
SELECT p.category, c.region, sum(f.amount) AS revenue
FROM demo_fact_sales f
JOIN demo_dim_product p ON p.product_key = f.product_key
JOIN demo_dim_customer c ON c.customer_key = f.customer_key
GROUP BY p.category, c.region
ORDER BY p.category; category | region | revenue
------------+--------+---------
Office | West | 8.00
Stationery | West | 10.50
(2 rows)Cleanup:
DROP TABLE demo_fact_sales, demo_dim_product, demo_dim_customer,
demo_lines, demo_orders, demo_products, demo_customers, demo_wide,
demo_orders_bad, demo_order_items,
demo_student_teacher, demo_teacher_course, demo_enroll;Interview points
- Define normalization: remove redundancy by placing each fact in one table, guided by functional dependencies.
- The three anomalies: update (inconsistent copies), insert (cannot record a fact without an unrelated one), delete (losing one fact deletes another).
- 1NF: atomic values. 2NF: no partial dependency on a composite key. 3NF: no transitive dependency. BCNF: every determinant is a super key.
- A table with a single-column primary key and 1NF is automatically in 2NF.
- 3NF and BCNF differ only when a table has overlapping composite candidate keys.
- Candidate key versus primary key: several candidate keys can exist; one is chosen as primary.
- Denormalize for read performance after measuring; the price is write cost and consistency risk.
- OLTP favors normalization; data warehouses favor star schemas.
- A lossless decomposition must join back to exactly the original rows.
Summary
- Normalization stores each fact once, based on functional dependencies, to prevent update, insert, and delete anomalies.
- 1NF requires atomic values, 2NF removes partial key dependencies, 3NF removes transitive ones, and BCNF requires every determinant to be a super key.
- Decomposition is correct when joining the parts returns the original rows.
- Denormalize selectively (summary columns, precomputed counts) and keep the copies in sync.
- Star schemas trade redundancy for fast analytic scans; normalized schemas suit transactional workloads.