Normalization: 1NF, 2NF, 3NF
You'll learn to
- -Normalize a table through 1NF, 2NF, and 3NF, and explain what anomaly each step actually eliminates
- -Recognize repeating groups and partial/transitive dependencies as the concrete signals that trigger each normal form
Normalization is a sequence of rules for structuring tables to eliminate redundancy and the update anomalies redundancy causes. Each normal form fixes a specific, nameable problem - which makes the difference between "database theory" and a genuinely practical design tool once you tie each step to the concrete bug it prevents.
First Normal Form (1NF): No Repeating Groups
1NF requires that every column hold a single, atomic value - no comma-separated lists or repeated groups of columns crammed into one field.
-- 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 this prevents: searching for "which holds contain barrels" against a comma-separated items column requires fragile string matching (LIKE '%barrels%'), and adding or removing a single item means rewriting the entire string. One row per item makes both operations a normal, indexed query.
Second Normal Form (2NF): No Partial Dependencies
2NF applies to tables with a composite primary key (more than one column), and requires that every non-key column depend on the whole key, not just part of it. A "partial dependency" is a column that only actually depends on one piece of a composite key.
-- 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: without the split, mail_room_name is duplicated across every item_type row for the same hold_id, and updating the mail room's name means updating every one of those duplicated rows - miss one, and the table now contradicts itself about which mail room a hold belongs to.
Third Normal Form (3NF): No Transitive Dependencies
3NF requires that non-key columns depend only on the primary key - not on another non-key column. A transitive dependency is a non-key column that actually depends on a different non-key column, not directly on the key.
-- 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)
);The lifeboats scenario above returns in the next chapter's Lab level (Lifeboat Allocation), this time for a different lesson - enforcing capacity limits and priority boarding rules with CHECK constraints rather than normal forms. The schema shape (a separate lifeboats table) is the same one 3NF just derived here.
A fast mental test for 3NF: for every non-key column, ask "does this depend on the key, the whole key, and nothing but the key?" That single sentence (a well-known mnemonic) captures all three normal forms at once.
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.