Database Normalization

Step 1: Why Normalize?

Imagine you're managing a school database. You create one big table to track everything — students, their courses, and instructor details. It seems convenient at first, but look at the problems:

orders (denormalized)

order_id	customer	cust_email	cust_city	items	total

Problems in this table:

Redundancy: Alice's name, email, and city are repeated in every row she appears in. If she has 100 orders, her info is stored 100 times.

Update Anomaly: If Alice moves to a new city, you must update every single row. Miss one and the data is inconsistent.

Delete Anomaly: If you delete Bob's only order (#103), you lose all of Bob's customer information entirely.

Insert Anomaly: You can't add a new customer without creating a fake order first, because order_id is required.

Multi-valued cells: The "items" column contains comma-separated values ("Laptop, Mouse"). This violates the principle of atomic values.

Normalization systematically eliminates these problems by organizing data into well-structured tables. Let's see how it works through three progressive normal forms.

Step 2: First Normal Form (1NF)

1NF Rules:

Each column must contain atomic (single, indivisible) values
No repeating groups or arrays in a single cell
Each row must be uniquely identifiable

Look at the "items" column below — it contains multiple values crammed into single cells. This violates 1NF.

Before (violates 1NF)

order_id	customer	items

After (1NF applied)

order_id	customer	item

Each multi-valued cell has been split into its own row. Now every cell contains exactly one atomic value. The composite key is now (order_id, item) to uniquely identify each row.

Step 3: Second Normal Form (2NF)

2NF Rules:

Must already be in 1NF
Every non-key column must depend on the entire primary key, not just part of it

After 1NF, our table has a composite key (order_id, item). But notice that customer, cust_email, and order_date depend only on order_id — they don't care which item it is. That's a partial dependency.

Table after 1NF (has partial dependencies)

order_id (PK)	item (PK)	customer	cust_email	order_date	qty

Dependency Diagram:

{order_id, item} → qty (full dependency, OK)

{order_id} → customer, cust_email, order_date (partial dependency, BAD)

Columns that depended on only order_id (not the full composite key) have been moved to a separate orders table. The order_items table keeps only columns that depend on the full composite key (order_id, item). A foreign key links them together.

Step 4: Third Normal Form (3NF)

3NF Rules:

Must already be in 2NF
No transitive dependencies — non-key columns must not depend on other non-key columns

Our orders table from Step 3 has a hidden problem: cust_email depends on customer, which depends on order_id. That's a transitive dependency chain:

orders table (has transitive dependency)

order_id (PK)	customer_id	customer_name	cust_email	order_date

Transitive Dependency Chain:

order_id → customer_id → customer_name, cust_email

customer_name and cust_email depend on customer_id, NOT directly on order_id

The transitive dependency is eliminated. customer_name and cust_email now live in a dedicated customers table, keyed by customer_id. The orders table references customers via a foreign key. No non-key column depends on another non-key column.

Step 5: Full Transformation

Watch the entire normalization journey from the original messy table to a clean, well-structured schema. Apply each normal form in sequence:

Original (Unnormalized)

Step 6: Knowledge Check

1. Which normal form requires that every column contains only atomic (single, indivisible) values?

Second Normal Form (2NF) First Normal Form (1NF) Third Normal Form (3NF)

2. A column depends on only part of a composite primary key. Which normal form does this violate?

First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF)

3. In 3NF, what type of dependency must be eliminated?

Partial dependencies Functional dependencies Transitive dependencies

Database Normalization

Step 1: Why Normalize?

orders (denormalized)

Problems in this table:

Step 2: First Normal Form (1NF)

Before (violates 1NF)

After (1NF applied)

Step 3: Second Normal Form (2NF)

Table after 1NF (has partial dependencies)

Dependency Diagram:

After 2NF: Split into two tables

orders

order_items

Step 4: Third Normal Form (3NF)

orders table (has transitive dependency)

Transitive Dependency Chain:

After 3NF: Extract customer table

orders

customers

Step 5: Full Transformation

Final Schema Diagram

Step 6: Knowledge Check

About This Lab

How It Works

More Labs in Databases

SQL Joins

SQL SELECT Basics