In this post we are going to cover normalization, and the three normal forms every interviewer asks about: 1NF, 2NF and 3NF.
Normalization is the process of organizing tables so that each fact is stored only once. When the same value is copied onto many rows, you get anomalies: an update has to change many rows, you cannot insert some facts without inventing others, and deleting one row can lose information you still need.
We will take one small order table from TB Shop, put it in first normal form, then split it step by step into second and third normal form with SQL. At the end we will join everything back together to prove nothing was lost.
Question 10 of the Real DWH Question and Answers series on TechBrothersIT.
- The interview answer
- First normal form: one value per cell
- Let's create some sample data
- Step 1: Move product facts into a products table
- Step 2: Keep only the whole-key column on the order line
- Step 3: Move order facts into an orders table
- Step 4: Remove the transitive dependency (3NF)
- Step 5: Prove nothing was lost
- Follow-up questions interviewers ask
- The full script
The interview answer
Normalization organizes tables to remove redundancy and the update, insert and delete anomalies. 1NF: every cell holds one atomic value, no repeating groups, and each row has a key. 2NF: 1NF, and no column depends on only part of a composite key. 3NF: 2NF, and no column depends on another non-key column. Memory aid: every column depends on the key, the whole key, and nothing but the key.
If you only remember one line, remember the memory aid: every column depends on the key, the whole key, and nothing but the key.
First normal form: one value per cell
Let's start with a table that is not normalized at all. TB Shop keeps each order on one row and squeezes all the products into a single cell.
| order_id | customer | products |
|---|---|---|
| 1001 | Sara Khan | Laptop Stand x1, USB-C Cable x2 |
| 1002 | Omar Ali | Wireless Mouse x1 |
| 1003 | Sara Khan | Desk Lamp x1, USB-C Cable x1 |
This breaks first normal form, because the products cell holds a list, not one value. You cannot easily count how many USB-C Cables were sold, and you cannot join that cell to a products table.
First normal form fixes this with one row per order line and one value per cell. Each row is then identified by a composite key: order_id plus product.
Let's create some sample data
We create the first normal form version of the orders as order_lines_1nf, with the key (order_id, product). The setup also drops the tables we will build in the steps, so the script can be run again.
DROP TABLE IF EXISTS order_lines_1nf;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS order_lines;
DROP TABLE IF EXISTS orders_2nf;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS orders;
-- 1NF: one value per cell, one row per order line, key = (order_id, product)
CREATE TABLE order_lines_1nf (
order_id INT,
product VARCHAR(50),
cust_id VARCHAR(5),
customer VARCHAR(50),
city VARCHAR(50),
category VARCHAR(50),
price DECIMAL(10,2),
qty INT,
PRIMARY KEY (order_id, product)
);
INSERT INTO order_lines_1nf VALUES
(1001, 'Laptop Stand', 'C1', 'Sara Khan', 'Toronto', 'Accessories', 35.00, 1),
(1001, 'USB-C Cable', 'C1', 'Sara Khan', 'Toronto', 'Accessories', 9.00, 2),
(1002, 'Wireless Mouse', 'C2', 'Omar Ali', 'Dallas', 'Electronics', 25.00, 1),
(1003, 'Desk Lamp', 'C1', 'Sara Khan', 'Toronto', 'Home', 30.00, 1),
(1003, 'USB-C Cable', 'C1', 'Sara Khan', 'Toronto', 'Accessories', 9.00, 1);Let's look at the starting table.
SELECT * FROM order_lines_1nf ORDER BY 1;| order_id | product | cust_id | customer | city | category | price | qty |
|---|---|---|---|---|---|---|---|
| 1001 | Laptop Stand | C1 | Sara Khan | Toronto | Accessories | 35.00 | 1 |
| 1001 | USB-C Cable | C1 | Sara Khan | Toronto | Accessories | 9.00 | 2 |
| 1002 | Wireless Mouse | C2 | Omar Ali | Dallas | Electronics | 25.00 | 1 |
| 1003 | Desk Lamp | C1 | Sara Khan | Toronto | Home | 30.00 | 1 |
| 1003 | USB-C Cable | C1 | Sara Khan | Toronto | Accessories | 9.00 | 1 |
Every cell has one value, so this is 1NF. But look at the repetition: Sara Khan and Toronto appear on four rows, and the USB-C Cable price of 9.00 appears twice. First normal form alone does not remove redundancy.
Step 1: Move product facts into a products table
Second normal form is about composite keys. For each column we ask: does it depend on the whole key, or only on part of it? category and price depend on product alone, which is only part of the key (order_id, product). That is a partial dependency, so let's move product facts into their own table.
CREATE TABLE products (
product VARCHAR(50) PRIMARY KEY,
category VARCHAR(50),
price DECIMAL(10,2));
INSERT INTO products
SELECT DISTINCT product, category, price
FROM order_lines_1nf;Now let's check the result.
SELECT * FROM products ORDER BY product;| product | category | price |
|---|---|---|
| Desk Lamp | Home | 30.00 |
| Laptop Stand | Accessories | 35.00 |
| USB-C Cable | Accessories | 9.00 |
| Wireless Mouse | Electronics | 25.00 |
The products table has four rows, one per product. The USB-C Cable price of 9.00 is now stored once instead of twice.
Step 2: Keep only the whole-key column on the order line
Now let's build the new order line table. Quantity depends on the order and the product together, so it is the one column that stays here.
CREATE TABLE order_lines (
order_id INT, product VARCHAR(50), qty INT,
PRIMARY KEY (order_id, product));
INSERT INTO order_lines
SELECT order_id, product, qty
FROM order_lines_1nf;Now let's check the result.
SELECT * FROM order_lines ORDER BY order_id, product;| order_id | product | qty |
|---|---|---|
| 1001 | Laptop Stand | 1 |
| 1001 | USB-C Cable | 2 |
| 1002 | Wireless Mouse | 1 |
| 1003 | Desk Lamp | 1 |
| 1003 | USB-C Cable | 1 |
We still have the same five order lines, but each row now holds only the key and qty. For example, order 1001 has 2 USB-C Cables.
Step 3: Move order facts into an orders table
There is a second partial dependency. cust_id, customer and city depend only on order_id, not on the product. So they move to a table keyed by order_id.
CREATE TABLE orders_2nf (
order_id INT PRIMARY KEY,
cust_id VARCHAR(5), customer VARCHAR(50), city VARCHAR(50));
INSERT INTO orders_2nf
SELECT DISTINCT order_id, cust_id, customer, city
FROM order_lines_1nf;Now let's check the result.
SELECT * FROM orders_2nf ORDER BY order_id;| order_id | cust_id | customer | city |
|---|---|---|---|
| 1001 | C1 | Sara Khan | Toronto |
| 1002 | C2 | Omar Ali | Dallas |
| 1003 | C1 | Sara Khan | Toronto |
Three orders, one row each. At this point the tables are in second normal form: every non-key column depends on the whole key of its table.
Step 4: Remove the transitive dependency (3NF)
Third normal form is about columns that depend on another non-key column. In orders_2nf the key is order_id. The order determines cust_id, and cust_id determines customer and city. That chain, order_id -> cust_id -> customer, city, is a transitive dependency. Name and city are facts about the customer, not about the order.
So let's move customer facts into a customers table and keep only order_id and cust_id in orders.
CREATE TABLE customers (
cust_id VARCHAR(5) PRIMARY KEY,
customer VARCHAR(50), city VARCHAR(50));
INSERT INTO customers
SELECT DISTINCT cust_id, customer, city FROM orders_2nf;
CREATE TABLE orders (
order_id INT PRIMARY KEY, cust_id VARCHAR(5));
INSERT INTO orders
SELECT order_id, cust_id FROM orders_2nf;Now let's check the result.
SELECT * FROM customers ORDER BY cust_id;| cust_id | customer | city |
|---|---|---|
| C1 | Sara Khan | Toronto |
| C2 | Omar Ali | Dallas |
Two customers, two rows. Sara Khan's city is now stored exactly once, so if she moves it is one update on one row. This is third normal form, and it is what most OLTP databases aim for.
Step 5: Prove nothing was lost
A good interview point: how do you know the split did not lose data? Let's join the 3NF tables back together: order lines, orders, customers and products.
SELECT ol.order_id, ol.product, c.customer,
c.city, p.price, ol.qty
FROM order_lines ol
JOIN orders o ON o.order_id = ol.order_id
JOIN customers c ON c.cust_id = o.cust_id
JOIN products p ON p.product = ol.product
ORDER BY ol.order_id, ol.product;| order_id | product | customer | city | price | qty |
|---|---|---|---|---|---|
| 1001 | Laptop Stand | Sara Khan | Toronto | 35.00 | 1 |
| 1001 | USB-C Cable | Sara Khan | Toronto | 9.00 | 2 |
| 1002 | Wireless Mouse | Omar Ali | Dallas | 25.00 | 1 |
| 1003 | Desk Lamp | Sara Khan | Toronto | 30.00 | 1 |
| 1003 | USB-C Cable | Sara Khan | Toronto | 9.00 | 1 |
We get five rows with the same values as the original 1NF table. A correct normalization is lossless.
Notice the trade-off too. Writes are now safe and small, but reads need joins. That is fine for OLTP, and it is exactly why warehouses denormalize again for analysis.
Follow-up questions interviewers ask
What is BCNF?
Boyce-Codd normal form: a stricter 3NF where every determinant is a candidate key.
What are 4NF and 5NF?
They handle multi-valued and join dependencies; rare in practice.
Should a warehouse be in 3NF?
The analytics (presentation) layer usually uses star schemas, which denormalize dimensions on purpose.
The full script
Here is everything from this post in one script. Copy it and run it top to bottom.
-- ========================================================================
-- Data Warehouse Interview Q&A #10 - Normalization: 1NF, 2NF, 3NF
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA dwh_q10; SET search_path TO dwh_q10;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS order_lines_1nf;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS order_lines;
DROP TABLE IF EXISTS orders_2nf;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS orders;
-- 1NF: one value per cell, one row per order line, key = (order_id, product)
CREATE TABLE order_lines_1nf (
order_id INT,
product VARCHAR(50),
cust_id VARCHAR(5),
customer VARCHAR(50),
city VARCHAR(50),
category VARCHAR(50),
price DECIMAL(10,2),
qty INT,
PRIMARY KEY (order_id, product)
);
INSERT INTO order_lines_1nf VALUES
(1001, 'Laptop Stand', 'C1', 'Sara Khan', 'Toronto', 'Accessories', 35.00, 1),
(1001, 'USB-C Cable', 'C1', 'Sara Khan', 'Toronto', 'Accessories', 9.00, 2),
(1002, 'Wireless Mouse', 'C2', 'Omar Ali', 'Dallas', 'Electronics', 25.00, 1),
(1003, 'Desk Lamp', 'C1', 'Sara Khan', 'Toronto', 'Home', 30.00, 1),
(1003, 'USB-C Cable', 'C1', 'Sara Khan', 'Toronto', 'Accessories', 9.00, 1);
-- Step 1: 2NF: product facts depend only on product, so they get their own table
CREATE TABLE products (
product VARCHAR(50) PRIMARY KEY,
category VARCHAR(50),
price DECIMAL(10,2));
INSERT INTO products
SELECT DISTINCT product, category, price
FROM order_lines_1nf;
-- check the result
SELECT * FROM products ORDER BY product;
-- Step 2: 2NF: the order line keeps only what depends on the WHOLE key
CREATE TABLE order_lines (
order_id INT, product VARCHAR(50), qty INT,
PRIMARY KEY (order_id, product));
INSERT INTO order_lines
SELECT order_id, product, qty
FROM order_lines_1nf;
-- check the result
SELECT * FROM order_lines ORDER BY order_id, product;
-- Step 3: 2NF: order facts depend only on order_id
CREATE TABLE orders_2nf (
order_id INT PRIMARY KEY,
cust_id VARCHAR(5), customer VARCHAR(50), city VARCHAR(50));
INSERT INTO orders_2nf
SELECT DISTINCT order_id, cust_id, customer, city
FROM order_lines_1nf;
-- check the result
SELECT * FROM orders_2nf ORDER BY order_id;
-- Step 4: 3NF: remove the transitive dependency order_id -> cust_id -> customer, city
CREATE TABLE customers (
cust_id VARCHAR(5) PRIMARY KEY,
customer VARCHAR(50), city VARCHAR(50));
INSERT INTO customers
SELECT DISTINCT cust_id, customer, city FROM orders_2nf;
CREATE TABLE orders (
order_id INT PRIMARY KEY, cust_id VARCHAR(5));
INSERT INTO orders
SELECT order_id, cust_id FROM orders_2nf;
-- check the result
SELECT * FROM customers ORDER BY cust_id;
-- Step 5: Check: join the 3NF tables back together (nothing was lost)
SELECT ol.order_id, ol.product, c.customer,
c.city, p.price, ol.qty
FROM order_lines ol
JOIN orders o ON o.order_id = ol.order_id
JOIN customers c ON c.cust_id = o.cust_id
JOIN products p ON p.product = ol.product
ORDER BY ol.order_id, ol.product;
Key points
- 1NF: one atomic value per cell, no repeating groups, a key per row
- 2NF: no partial dependency on part of a composite key
- 3NF: no transitive dependency through a non-key column
- "The key, the whole key, and nothing but the key"
- Lossless: joining the tables gives back the original rows
Question 11 — Primary, foreign, natural and composite keys Primary key, foreign key, natural key, composite key: what is the difference?
Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.