Database Normalization Cheat Sheet
Normal forms from 1NF through 5NF explained with functional dependency rules and before/after examples for eliminating redundancy.
2 PagesIntermediateFeb 25, 2026
Normal Forms
The progression of normalization levels.
- 1NF- every column holds atomic values, no repeating groups; rows are unique
- 2NF- 1NF plus every non-key attribute fully depends on the whole primary key
- 3NF- 2NF plus no transitive dependencies between non-key attributes
- BCNF- a stricter 3NF where every determinant must be a candidate key
- 4NF- eliminates multi-valued dependencies
- 5NF- eliminates join dependencies not implied by the candidate keys
1NF & 2NF Example
Removing repeating groups and partial dependencies.
sql
-- Unnormalized: repeating groups-- Orders(OrderId, CustomerName, Product1, Product2)-- 1NF: atomic values, one row per productCREATE TABLE OrderItems ( OrderId INT, Product VARCHAR(100), PRIMARY KEY (OrderId, Product));-- 2NF: split out data that only depends on part of the composite keyCREATE TABLE Orders (OrderId INT PRIMARY KEY, CustomerName VARCHAR(100));CREATE TABLE OrderItems ( OrderId INT, Product VARCHAR(100), Qty INT, PRIMARY KEY (OrderId, Product));
3NF Example
Removing a transitive dependency.
sql
-- Before 3NF: transitive dependency (ZipCode -> City)-- Employees(EmpId, ZipCode, City)-- After 3NF: City depends on ZipCode, not directly on EmpIdCREATE TABLE Employees (EmpId INT PRIMARY KEY, ZipCode VARCHAR(10));CREATE TABLE ZipCodes (ZipCode VARCHAR(10) PRIMARY KEY, City VARCHAR(100));
Functional Dependency Terms
Vocabulary used when reasoning about normal forms.
- Functional dependency- A -> B means A's value determines B's value
- Candidate key- a minimal set of columns that uniquely identifies a row
- Determinant- the left-hand side of a functional dependency
- Partial dependency- a non-key attribute depends on only part of a composite key
- Transitive dependency- A -> B -> C, where C depends on B which depends on A
- Denormalization- deliberately reintroducing redundancy to speed up reads
BCNF Example
Decomposing a relation where a non-key attribute determines part of the key.
sql
-- Before BCNF: Enrollment(StudentId, CourseId, Instructor)-- FD: Instructor -> CourseId (each instructor teaches exactly one course)-- This violates BCNF: Instructor is a determinant but not a candidate key-- After BCNF: split so every determinant is a candidate keyCREATE TABLE CourseInstructors ( Instructor VARCHAR(100) PRIMARY KEY, CourseId INT NOT NULL);CREATE TABLE Enrollment ( StudentId INT, Instructor VARCHAR(100), PRIMARY KEY (StudentId, Instructor), FOREIGN KEY (Instructor) REFERENCES CourseInstructors(Instructor));
4NF Example
Removing an independent multi-valued dependency.
sql
-- Before 4NF: Employee(EmpId, Skill, Language)-- Skill and Language are independent multi-valued facts about EmpId,-- so combining them creates spurious rows (the cross product of both sets)-- After 4NF: split into two independent relationsCREATE TABLE EmployeeSkills (EmpId INT, Skill VARCHAR(100), PRIMARY KEY (EmpId, Skill));CREATE TABLE EmployeeLanguages (EmpId INT, Language VARCHAR(100), PRIMARY KEY (EmpId, Language));
Detecting FD Violations with SQL
A query that surfaces candidate transitive/partial dependencies by finding duplicate determinant values mapping to different dependents.
sql
-- Find ZipCode values that map to more than one City (would break 3NF-- if City were stored directly on a table keyed by something other than ZipCode)SELECT ZipCode, COUNT(DISTINCT City) AS city_variantsFROM EmployeesGROUP BY ZipCodeHAVING COUNT(DISTINCT City) > 1;
Controlled Denormalization Patterns
Two common, deliberate ways to trade write complexity for read speed after normalizing.
sql
-- 1. Materialized summary column, kept correct with a triggerALTER TABLE orders ADD COLUMN item_count INT NOT NULL DEFAULT 0;CREATE OR REPLACE FUNCTION sync_item_count() RETURNS TRIGGER AS $$BEGIN UPDATE orders SET item_count = ( SELECT COUNT(*) FROM order_items WHERE order_id = NEW.order_id ) WHERE id = NEW.order_id; RETURN NEW;END;$$ LANGUAGE plpgsql;CREATE TRIGGER trg_item_countAFTER INSERT OR DELETE ON order_itemsFOR EACH ROW EXECUTE FUNCTION sync_item_count();-- 2. Materialized view refreshed on a schedule instead of per-writeCREATE MATERIALIZED VIEW customer_order_totals ASSELECT customer_id, SUM(total) AS lifetime_valueFROM orders GROUP BY customer_id;REFRESH MATERIALIZED VIEW CONCURRENTLY customer_order_totals;
Anomalies Normalization Prevents
The three classic problems each normal form is fighting.
- Insertion anomaly- you can't record a fact (e.g. a new course) until an unrelated fact (a student) also exists
- Update anomaly- the same fact is duplicated across rows, so an update must touch every copy or data goes inconsistent
- Deletion anomaly- deleting one fact accidentally erases an unrelated fact stored redundantly in the same row
- Trivial FD- A -> B where B is a subset of A; always holds and is ignored during normalization analysis
- Armstrong's axioms- reflexivity, augmentation, transitivity — the inference rules used to derive the full closure of FDs
- Lossless-join decomposition- splitting a table such that joining the parts back always reproduces the original rows exactly
- Dependency-preserving decomposition- a decomposition where every original FD can still be checked without a join
Pro Tip
Normalize for correctness first, usually to 3NF, then selectively denormalize specific hot read paths once real query performance data justifies it — premature denormalization just recreates update anomalies without a proven benefit.
Was this cheat sheet helpful?
Explore Topics
#DatabaseNormalization#DatabaseNormalizationCheatSheet#Database#Intermediate#NormalForms#1NF2NFExample#3NFExample#FunctionalDependencyTerms#Databases#CheatSheet#SkillVeris
