Chapter 10: Normalization — 1NF through BCNF

Normalization is the process of organizing data to reduce redundancy and prevent anomalies. It's a set of rules (normal forms) that guide you toward a clean schema.

Why Normalize? The Problem of Redundancy

Consider this denormalized table:

-- BAD: Everything in one table
┌────┬─────────┬──────────────┬─────────────┬────────────┐
│ id │  name   │  dept_name   │ dept_budget │ dept_head  │
├────┼─────────┼──────────────┼─────────────┼────────────┤
│  1 │ Alice   │ Engineering  │  2000000    │ Eve        │
│  2 │ Bob     │ Engineering  │  2000000    │ Eve        │
│  3 │ Charlie │ Engineering  │  2000000    │ Eve        │
│  4 │ Dave    │ Marketing    │   500000    │ Frank      │
└────┴─────────┴──────────────┴─────────────┴────────────┘

Problems (anomalies):

• Update anomaly: If Engineering's budget changes, you must update 3 rows. Miss one → inconsistency.
• Insert anomaly: Can't add a new department until it has at least one employee.
• Delete anomaly: If Dave leaves, you lose all info about Marketing.
• Wasted space: "Engineering" and "2000000" stored 3 times.

First Normal Form (1NF)

Rule: Each cell contains a single atomic value. No arrays, no comma-separated lists, no nested structures.

-- VIOLATES 1NF: multi-valued column
┌────┬─────────┬─────────────────────────┐
│ id │  name   │        skills           │
├────┼─────────┼─────────────────────────┤
│  1 │ Alice   │ C, Python, gRPC         │  ← multiple values!
│  2 │ Bob     │ Java, Kubernetes        │
└────┴─────────┴─────────────────────────┘

-- 1NF SOLUTION: separate table for multi-valued data
employees:          employee_skills:
┌────┬─────────┐   ┌─────────────┬────────────┐
│ id │  name   │   │ employee_id │   skill    │
├────┼─────────┤   ├─────────────┼────────────┤
│  1 │ Alice   │   │      1      │ C          │
│  2 │ Bob     │   │      1      │ Python     │
└────┴─────────┘   │      1      │ gRPC       │
                    │      2      │ Java       │
                    │      2      │ Kubernetes │
                    └─────────────┴────────────┘

Second Normal Form (2NF)

Rule: 1NF + every non-key column depends on the entire primary key (not just part of it). Only relevant for composite keys.

-- VIOLATES 2NF: student_name depends only on student_id, not on course_id
-- Primary key: (student_id, course_id)
┌────────────┬───────────┬──────────────┬───────┐
│ student_id │ course_id │ student_name │ grade │
├────────────┼───────────┼──────────────┼───────┤
│     1      │   CS101   │ Alice        │  A    │
│     1      │   CS201   │ Alice        │  B    │  ← "Alice" repeated!
│     2      │   CS101   │ Bob          │  B    │
└────────────┴───────────┴──────────────┴───────┘

-- 2NF SOLUTION: split into tables based on dependency
students:                    enrollments:
┌────┬───────┐              ┌────────────┬───────────┬───────┐
│ id │ name  │              │ student_id │ course_id │ grade │
├────┼───────┤              ├────────────┼───────────┼───────┤
│  1 │ Alice │              │     1      │   CS101   │  A    │
│  2 │ Bob   │              │     1      │   CS201   │  B    │
└────┴───────┘              │     2      │   CS101   │  B    │
                            └────────────┴───────────┴───────┘

Third Normal Form (3NF)

Rule: 2NF + no non-key column depends on another non-key column (no transitive dependencies).

-- VIOLATES 3NF: dept_budget depends on dept_id, not on employee id
┌────┬─────────┬─────────┬─────────────┐
│ id │  name   │ dept_id │ dept_budget │
├────┼─────────┼─────────┼─────────────┤
│  1 │ Alice   │    1    │  2000000    │  ← dept_budget depends on dept_id
│  2 │ Bob     │    1    │  2000000    │     not on employee id
└────┴─────────┴─────────┴─────────────┘

-- 3NF SOLUTION: move transitive dependency to its own table
employees:                  departments:
┌────┬─────────┬─────────┐ ┌────┬─────────────┐
│ id │  name   │ dept_id │ │ id │   budget    │
├────┼─────────┼─────────┤ ├────┼─────────────┤
│  1 │ Alice   │    1    │ │  1 │  2000000    │
│  2 │ Bob     │    1    │ └────┴─────────────┘
└────┴─────────┴─────────┘

Boyce-Codd Normal Form (BCNF)

Rule: Every determinant (column that determines another) must be a candidate key. Stricter than 3NF but rarely differs in practice.

When to Denormalize

Normalization isn't always the answer. Sometimes you deliberately denormalize for performance:

Normalize When	Denormalize When
Data changes frequently	Data is read-heavy, rarely updated
Consistency is critical	Query performance is critical
Storage is expensive	JOINs are too slow
Multiple apps write the data	You control all access patterns

Mark as Complete