Normalization — Animate | AI Wisdom

← Back to topic
🎞 ANIMATE · Mode 3 of 5 · +100 XP— Database Normalization
📋1NF
⚙️2NF
🔄3NF
✅Result
🎯 Next: Test →
Step 1 / 4
Speed
First Normal Form
1NF: atomic values — split "Pen, Notebook" into separate order_items rows💡 Analogy
Like separating "groceries: milk, eggs, bread" — one item per line, not comma-separated.
1NF rule: each cell holds one value (atomic). "Pen, Notebook" in one cell violates 1NF. Split into a separate table with one row per item.
Console Output
> Before: orders.products = "Pen, Notebook" ❌
> After: order_items rows:
> (order_id=1, product_id=10, qty=1)
> (order_id=1, product_id=11, qty=2)
●normalization.sql
First Normal FormL1–12
1-- Unnormalized (1NF violation): multiple values in one column
2-- orders: id | customer_name | customer_email | products
3-- 1     | Alice         | a@co.com      | "Pen, Notebook"
4-- Problem: products column has multiple values — hard to query/update
5 
6-- 1NF: Atomic values, eliminate repeating groups
7CREATE TABLE order_items (
8    order_id  INT REFERENCES orders(id),
9    product_id INT REFERENCES products(id),
10    quantity  INT NOT NULL,
11    PRIMARY KEY (order_id, product_id)
12);
13 
14-- 2NF: Remove partial dependencies (all non-key cols depend on whole PK)
15-- Bad: order_items has product_name — depends only on product_id, not full PK
16-- Fix: move product_name to products table
17CREATE TABLE products (
18    id    SERIAL PRIMARY KEY,
19    name  VARCHAR(255) NOT NULL,
20    price DECIMAL(10,2) NOT NULL
21);
22 
23-- 3NF: Remove transitive dependencies (non-key → non-key)
24-- Bad: orders has customer_city, customer_country — depend on customer_id not order_id
25-- Fix: move to customers table
26CREATE TABLE customers (
27    id      SERIAL PRIMARY KEY,
28    name    VARCHAR(255) NOT NULL,
29    email   VARCHAR(255) UNIQUE NOT NULL,
30    city    VARCHAR(100),
31    country CHAR(2)
32);
33 
34-- Final normalized schema: orders just links customer + date
35CREATE TABLE orders (
36    id          SERIAL PRIMARY KEY,
37    customer_id INT REFERENCES customers(id),
38    created_at  TIMESTAMPTZ DEFAULT NOW()
39);
39 lines
Timeline — 4 Steps
First Normal Form
1NF: atomic values — split "Pen, Notebook" into separate order_items rows💡 Analogy
Like separating "groceries: milk, eggs, bread" — one item per line, not comma-separated.
1NF rule: each cell holds one value (atomic). "Pen, Notebook" in one cell violates 1NF. Split into a separate table with one row per item.
Console Output
> Before: orders.products = "Pen, Notebook" ❌
> After: order_items rows:
> (order_id=1, product_id=10, qty=1)
> (order_id=1, product_id=11, qty=2)

Was this page helpful?

Discussion

1NF: atomic values — split "Pen, Notebook" into separate order_items rows

💡 Analogy

Like separating "groceries: milk, eggs, bread" — one item per line, not comma-separated.

1NF rule: each cell holds one value (atomic). "Pen, Notebook" in one cell violates 1NF. Split into a separate table with one row per item.

Console Output

> Before: orders.products = "Pen, Notebook" ❌
> After: order_items rows:
> (order_id=1, product_id=10, qty=1)
> (order_id=1, product_id=11, qty=2)

1NF: atomic values — split "Pen, Notebook" into separate order_items rows

💡 Analogy

Like separating "groceries: milk, eggs, bread" — one item per line, not comma-separated.

1NF rule: each cell holds one value (atomic). "Pen, Notebook" in one cell violates 1NF. Split into a separate table with one row per item.

Console Output

> Before: orders.products = "Pen, Notebook" ❌
> After: order_items rows:
> (order_id=1, product_id=10, qty=1)
> (order_id=1, product_id=11, qty=2)