Normalization: 1NF, 2NF, 3NF
You'll learn to
- -Explain 1NF, 2NF and 3NF each as the fix for one concrete bug, not as theory
- -Name the three anomalies (update, insert, delete) and show each one on a small table
- -Run the "key, the whole key, and nothing but the key" test on a table
Normalization is a set of rules for structuring tables so each fact is stored once. Storing a fact in several places is called redundancy, and it causes anomalies, meaning bugs that come from the structure itself. An update anomaly is when you change a fact in one place and forget the others. An insert anomaly is when you cannot record one fact without inventing another. A delete anomaly is when deleting one thing destroys a different fact. Each normal form removes a specific, nameable problem, which turns "database theory" into a practical tool.
An Analogy: Moving House
When you pack boxes for a move, you do not write the new address inside every box on a hundred separate sticky notes. You keep it in one address book and refer to it. If the address changes, you correct one page. Normalization is that habit applied to tables: keep each fact in one place and point at it.
First Normal Form (1NF): No Repeating Groups
1NF requires every column to hold a single, atomic value. Atomic means it cannot be usefully split further. A comma-separated list in one column, or repeated columns for the same kind of value, breaks it.
-- Violates 1NF: a single column holding a variable-length list
CREATE TABLE cargo_holds (
id INTEGER PRIMARY KEY,
hold_name TEXT,
items TEXT -- "crates, barrels, luggage, mail sacks" - not atomic!
);
-- 1NF-compliant: one row per (hold, item) pair
CREATE TABLE cargo_items (
id INTEGER PRIMARY KEY,
hold_id INTEGER NOT NULL,
item_type TEXT NOT NULL,
FOREIGN KEY (hold_id) REFERENCES cargo_holds(id)
);The concrete bug: to find which holds contain barrels in a comma-separated column you need fragile text matching with LIKE '%barrels%', which would also match a hold that lists "no barrels". Adding or removing one item means rewriting the whole string. With one row per item, both are ordinary indexed queries.
Second Normal Form (2NF): No Partial Dependencies
2NF matters for tables whose primary key is made of more than one column, called a composite key. It requires every non-key column to depend on the whole key, not just part of it. In the example below the key is (hold_id, item_type), but mail_room_name depends only on hold_id.
-- Violates 2NF: composite key (hold_id, item_type), but mail_room_name
-- only actually depends on hold_id - a partial dependency
CREATE TABLE mail_sorting (
hold_id INTEGER,
item_type TEXT,
quantity INTEGER,
mail_room_name TEXT, -- depends only on hold_id, not on item_type
PRIMARY KEY (hold_id, item_type)
);
-- 2NF-compliant: split out the partially-dependent column
CREATE TABLE mail_holds (
hold_id INTEGER PRIMARY KEY,
mail_room_name TEXT
);
CREATE TABLE mail_sorting (
hold_id INTEGER,
item_type TEXT,
quantity INTEGER,
PRIMARY KEY (hold_id, item_type),
FOREIGN KEY (hold_id) REFERENCES mail_holds(hold_id)
);The concrete bug: suppose hold 4 stores three item types, so three rows. The mail room's name is repeated in all three. If the mail room is renamed, you must update three rows, and missing one leaves the table contradicting itself. After the split, the name is stored once.
Third Normal Form (3NF): No Transitive Dependencies
3NF requires that non-key columns depend only on the primary key, and not on some other non-key column. A transitive dependency is one where a column depends on the key only through another column. Below, lifeboat_capacity depends on lifeboat_id, a non-key column, not on allocation_id.
-- Violates 3NF: allocation_id is the key, but lifeboat_capacity
-- depends on lifeboat_id (a non-key column), not on allocation_id directly
CREATE TABLE lifeboat_allocations (
allocation_id INTEGER PRIMARY KEY,
passenger_id INTEGER NOT NULL,
lifeboat_id INTEGER NOT NULL,
lifeboat_capacity INTEGER -- transitively dependent on lifeboat_id, not allocation_id
);
-- 3NF-compliant: capacity belongs on the lifeboats table itself
CREATE TABLE lifeboats (
id INTEGER PRIMARY KEY,
capacity INTEGER NOT NULL
);
CREATE TABLE lifeboat_allocations (
allocation_id INTEGER PRIMARY KEY,
passenger_id INTEGER NOT NULL,
lifeboat_id INTEGER NOT NULL,
FOREIGN KEY (lifeboat_id) REFERENCES lifeboats(id)
);Count the damage with numbers. Lifeboat 4 holds 40 people and appears in 25 allocation rows, so its capacity is stored 25 times. If the capacity becomes 50, you must change 25 rows (an update anomaly), and missing one leaves two answers. If lifeboat 9 has no passengers yet, there is no row to hold its capacity at all (an insert anomaly). And deleting the last allocation for lifeboat 4 deletes the only copy of its capacity (a delete anomaly). After the split the capacity lives once in the lifeboats table, and all three problems disappear.
The lifeboat scenario returns in the next chapter's Lab level, this time for a different lesson: enforcing capacity limits with CHECK constraints. The schema shape, a separate lifeboats table, is the one 3NF just derived.
A fast test for 3NF: for every non-key column, ask "does this depend on the key, the whole key, and nothing but the key?" That one sentence captures all three normal forms.
Interview Signal is part of Pro
See a real weak answer next to a real strong one for this exact topic.
Quiz is part of Pro
Test what you just read with a short quiz, and bank the XP.
Normalize Cargo Hold Cleanup and Mail Room Sorting in the LLD Lab's Titanic act.
Discussion0
Join the Discussion
Sign in to leave comments, reply to others, or like insights.
No comments yet. Be the first to start the thread!