Normalization in SQL: A Practical 1NF-to-3NF Tutorial with Runnable Examples

A table can accept valid SQL inserts and still be badly designed. When customer, product, order, and salesperson facts repeat in every order-line row, one missed UPDATE can create contradictions. Functional dependencies separate those facts into five constrained 3NF tables; a lossless join rebuilds the original four rows.

1. Start with one legal but fragile order table

If keys, rows, and joins are new, start with RDBMS Concepts and SQL Basics. Normalization asks a narrower question: which determinant owns each fact? Each non-key fact must depend on the whole key and nothing else. SQL executes the schema; dependencies test whether it is sound. Explore the wider subject in CS Fundamentals.

CREATE TABLE order_line_raw (
  order_id INT,
  order_date DATE,
  customer_id INT,
  customer_name VARCHAR(60),
  customer_city VARCHAR(40),
  sales_rep_id INT,
  sales_rep_name VARCHAR(60),
  product_id VARCHAR(10),
  product_name VARCHAR(60),
  product_category VARCHAR(40),
  quantity INT,
  line_status VARCHAR(20),
  PRIMARY KEY (order_id, product_id)
);

INSERT INTO order_line_raw VALUES
  (5001, DATE '2026-07-01', 101, 'Asha', 'Pune', 7, 'Meera', 'P10', 'Keyboard', 'Peripherals', 1, 'Packed'),
  (5001, DATE '2026-07-01', 101, 'Asha', 'Pune', 7, 'Meera', 'P20', 'Mouse', 'Peripherals', 2, 'Packed'),
  (5002, DATE '2026-07-02', 102, 'Ravi', 'Jaipur', 8, 'Kabir', 'P10', 'Keyboard', 'Peripherals', 1, 'Shipped'),
  (5003, DATE '2026-07-02', 101, 'Asha', 'Pune', 7, 'Meera', 'P30', 'USB Hub', 'Accessories', 1, 'Pending');

The composite key allows each product once per order. If repeated lines are valid, add line_no.

2. Derive the dependencies before splitting anything

The candidate key is (order_id, product_id). The relevant dependencies are:

• order_id -> order_date, customer_id, sales_rep_id

• customer_id -> customer_name, customer_city

• sales_rep_id -> sales_rep_name

• product_id -> product_name, product_category

• (order_id, product_id) -> quantity, line_status

product_category describes a product. line_status describes fulfilment of one order line. They therefore have different determinants even when several rows happen to say Packed.

Every sample cell is atomic, so the table is in 1NF. It fails 2NF because order facts depend only on order_id, while product facts depend only on product_id. Each determinant is only part of the composite key. Customer and salesperson names add transitive dependencies that remain after removing the partial ones.

Renaming P10 to Mechanical Keyboard requires changing the 5001 and 5002 rows; missing either gives P10 two names. P40, Webcam, Video, cannot be recorded before its first order. Deleting order 5003 also removes the only description of P30, USB Hub, Accessories.

3. Move from 1NF to 2NF, then finish the 3NF design

First remove partial dependencies. Put product_id, product_name, product_category in products, the line relationship in order_items(order_id, product_id, quantity, line_status), and the order facts in intermediate table orders_2nf(order_id, order_date, customer_id, customer_name, customer_city, sales_rep_id, sales_rep_name).

That reaches 2NF, but not 3NF. In orders_2nf, customer_id -> customer_name, customer_city and sales_rep_id -> sales_rep_name still make non-key attributes depend on other non-key attributes.

The 3NF design has five relations:

• customers(customer_id, customer_name, customer_city)

• sales_reps(sales_rep_id, sales_rep_name)

• products(product_id, product_name, product_category)

• orders(order_id, order_date, customer_id, sales_rep_id)

• order_items(order_id, product_id, quantity, line_status)

orders references customers and sales reps; both key columns in order_items reference parents. Asha and Pune now occur once under customer 101, and Keyboard and Peripherals once under P10, while each line keeps its status. For higher normal forms, read Normalization in DBMS from 1NF to BCNF.

4. Create the normalized SQL schema with constraints

Create parent tables before tables that reference them:

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(60) NOT NULL,
  customer_city VARCHAR(40) NOT NULL
);

CREATE TABLE sales_reps (
  sales_rep_id INT PRIMARY KEY,
  sales_rep_name VARCHAR(60) NOT NULL
);

CREATE TABLE products (
  product_id VARCHAR(10) PRIMARY KEY,
  product_name VARCHAR(60) NOT NULL,
  product_category VARCHAR(40) NOT NULL
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  order_date DATE NOT NULL,
  customer_id INT NOT NULL REFERENCES customers(customer_id),
  sales_rep_id INT NOT NULL REFERENCES sales_reps(sales_rep_id)
);

CREATE TABLE order_items (
  order_id INT NOT NULL REFERENCES orders(order_id),
  product_id VARCHAR(10) NOT NULL REFERENCES products(product_id),
  quantity INT NOT NULL CHECK (quantity > 0),
  line_status VARCHAR(20) NOT NULL
    CHECK (line_status IN ('Pending', 'Packed', 'Shipped')),
  PRIMARY KEY (order_id, product_id)
);

Define cascades explicitly. Deleting an order may remove its lines, while deleting a product should not erase historical sales.

Decomposition gives each fact a proper home. Primary keys, foreign keys, NOT NULL, and CHECK constraints stop invalid rows from entering those homes. 3NF alone does not enforce positive quantities, preserve history, or choose deletion behaviour.

5. Migrate the four rows without hiding contradictions

Before using DISTINCT, find conflicting descriptions for the same identifier:

SELECT customer_id FROM order_line_raw GROUP BY customer_id
HAVING COUNT(DISTINCT customer_name) > 1
    OR COUNT(DISTINCT customer_city) > 1;

SELECT sales_rep_id FROM order_line_raw GROUP BY sales_rep_id
HAVING COUNT(DISTINCT sales_rep_name) > 1;

SELECT product_id FROM order_line_raw GROUP BY product_id
HAVING COUNT(DISTINCT product_name) > 1
    OR COUNT(DISTINCT product_category) > 1;

All three checks return zero rows for this sample. If P10 appears as both Keyboard and Mechanical Keyboard, DISTINCT must not conceal the conflict. Decide which value is correct first.

Where the database supports transactional DDL and DML, the data-copy sequence is:

BEGIN;

INSERT INTO customers
SELECT DISTINCT customer_id, customer_name, customer_city FROM order_line_raw;
INSERT INTO sales_reps
SELECT DISTINCT sales_rep_id, sales_rep_name FROM order_line_raw;
INSERT INTO products
SELECT DISTINCT product_id, product_name, product_category FROM order_line_raw;
INSERT INTO orders
SELECT DISTINCT order_id, order_date, customer_id, sales_rep_id FROM order_line_raw;
INSERT INTO order_items
SELECT order_id, product_id, quantity, line_status FROM order_line_raw;

COMMIT;

Expect 2 customers, 2 sales reps, 3 products, 3 orders, and 4 items. Validate a copy, switch writes, retain a backup, and remove the raw table only after review. Adapt transactions to your engine.

6. Prove the new schema still answers the old questions

The normalized tables still answer the original business question:

SELECT o.order_id, c.customer_name, SUM(oi.quantity) AS total_units
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, c.customer_name
ORDER BY o.order_id;

The output is (5001, Asha, 3), (5002, Ravi, 1), and (5003, Asha, 1). For 5001, 1 Keyboard unit + 2 Mouse units = 3 units.

Start validation with counts:

SELECT COUNT(*) FROM order_line_raw; -- 4
SELECT COUNT(*) FROM order_items;    -- 4

Then compare all twelve reconstructed columns in both directions:

WITH rebuilt AS (
  SELECT o.order_id, o.order_date, c.customer_id, c.customer_name,
         c.customer_city, s.sales_rep_id, s.sales_rep_name,
         p.product_id, p.product_name, p.product_category,
         oi.quantity, oi.line_status
  FROM orders o
  JOIN customers c ON c.customer_id = o.customer_id
  JOIN sales_reps s ON s.sales_rep_id = o.sales_rep_id
  JOIN order_items oi ON oi.order_id = o.order_id
  JOIN products p ON p.product_id = oi.product_id
)
SELECT 'raw_minus_rebuilt' AS check_name, COUNT(*) AS differences
FROM (SELECT * FROM order_line_raw EXCEPT SELECT * FROM rebuilt) a
UNION ALL
SELECT 'rebuilt_minus_raw', COUNT(*)
FROM (SELECT * FROM rebuilt EXCEPT SELECT * FROM order_line_raw) b;

Both difference counts must be zero; equal row counts alone cannot prove equality. The reconstruction is lossless for this dataset and dependency design. Review the mechanics in SQL Queries and Joins in DBMS.

7. Common normalization traps and how exams test them

Use dependencies, not visual repetition, to diagnose each mistake:

• Split by appearance: line_status can lose its order-line context. Keep it under (order_id, product_id).

• Keep customer_name in orders: the transitive dependency remains. Store it in customers.

• Move line_status to products: P10 would be both Packed and Shipped. Keep it in order_items.

• Key items by product_id alone: P10 cannot appear in orders 5001 and 5002. Retain the composite key.

• Omit foreign keys: orphan order lines become possible. Reference both parents.

Five checks now have direct answers. The raw candidate key is (order_id, product_id). product_id -> product_name, product_category is partial relative to that key. order_id -> customer_id -> customer_name is transitive. The design has five relations. Asha has 3 units in order 5001 plus 1 in order 5003, so 3 + 1 = 4 units.

If one product may appear twice in an order, add line_no and use a key such as (order_id, line_no).

8. The short version and the next step

• Atomic cells established 1NF; partial dependencies separated order, product, and line facts.

• Transitive dependencies moved customers and sales reps into their own tables.

• Constraints protected five tables; joins rebuilt four lines and returned Asha's 4 units.

Add order 5004 dated 2026-07-03 for customer 102 and sales rep 8, then add two P30 units with status Packed. Only orders and order_items change; the totals query gains (5004, Ravi, 2). Until the raw table includes order 5004, reconciliation reports one extra rebuilt row. Continue with the Zero to Hero Complete CS Course and write the dependency list before the next CREATE TABLE.