Database normalization organizes relational tables so data is stored with less redundancy and fewer update, insert, and delete anomalies. This cheat sheet covers the major normal forms used in database design, from 1NF through 4NF. College students need it to quickly identify dependencies, candidate keys, and valid decompositions when designing or analyzing schemas.
The core idea is that functional dependencies determine which attributes belong together in a relation. A table moves into higher normal forms by removing repeating groups, partial dependencies, transitive dependencies, and certain multivalued dependencies. The most important checks are whether determinants are keys, whether non-key attributes depend on whole keys, and whether decompositions are lossless and dependency preserving.
Key Facts
- A functional dependency X -> Y means that any two rows with the same values for attributes X must also have the same values for attributes Y.
- A superkey is any attribute set K such that K -> all attributes in the relation.
- A candidate key is a minimal superkey, meaning no attribute can be removed while still determining all attributes.
- 1NF requires every attribute value to be atomic, with no repeating groups, arrays, or nested tables inside a cell.
- 2NF requires 1NF and no partial dependency, so every non-prime attribute must depend on the entire candidate key, not part of a composite key.
- 3NF requires 2NF and no transitive dependency of a non-prime attribute on a key, often stated as for every X -> A, X is a superkey or A is prime.
- BCNF requires that for every nontrivial functional dependency X -> Y, X must be a superkey.
- A binary decomposition of R into R1 and R2 is lossless if (R1 intersect R2) -> R1 or (R1 intersect R2) -> R2 holds under the dependencies.
Vocabulary
- Functional dependency
- A rule X -> Y showing that values of attributes X uniquely determine values of attributes Y within a relation.
- Candidate key
- A smallest set of attributes that can uniquely identify every tuple in a relation.
- Prime attribute
- An attribute that belongs to at least one candidate key.
- Partial dependency
- A dependency where a non-prime attribute depends on only part of a composite candidate key.
- Transitive dependency
- A dependency where a key determines an intermediate attribute, and that intermediate attribute determines a non-key attribute.
- Lossless decomposition
- A table split that can be joined back together without creating extra rows or losing original rows.
Common Mistakes to Avoid
- Treating every unique-looking column as a key is wrong because a candidate key must be guaranteed by the rules of the data, not by a small sample of rows.
- Ignoring composite keys is wrong because 2NF specifically checks whether non-prime attributes depend on only part of a multi-attribute key.
- Confusing 3NF with BCNF is wrong because 3NF allows X -> A when A is prime, while BCNF requires X to be a superkey for every nontrivial dependency.
- Assuming every decomposition is safe is wrong because a split can be lossy if the shared attributes do not functionally determine one of the decomposed tables.
- Removing dependencies without checking preservation is wrong because a normalized design may still make important constraints difficult to enforce without joining tables.
Practice Questions
- 1 Relation R(A, B, C) has functional dependencies A -> B and B -> C. What are the candidate keys, and is R in 3NF?
- 2 Relation Enroll(StudentID, CourseID, StudentName, CourseTitle, Grade) has key (StudentID, CourseID) and dependencies StudentID -> StudentName, CourseID -> CourseTitle, and (StudentID, CourseID) -> Grade. Decompose it into 2NF relations.
- 3 Relation R(A, B, C, D) has dependencies AB -> C, C -> D, and D -> A. Find one candidate key and identify whether C -> D violates BCNF.
- 4 Why can a schema that satisfies 3NF still fail BCNF, and what tradeoff might make a designer keep the 3NF version?
Understanding Database Normalization Forms Reference
Normalization is a design process, not a rule that every table must reach the highest possible form. Start by listing the facts the system needs to remember. Then identify the real-world rule behind each dependency.
For example, a student ID determines a student name because one ID belongs to one student. A course code does not determine an instructor if different sections of the course have different instructors.
Dependencies come from business rules, not from a small sample of rows. Sample data can hide errors when it happens to contain only one instructor per course.
Keys need careful testing because many normalization mistakes begin with a wrong key. Find a set of attributes that identifies one row, then remove attributes one at a time. If the remaining set still identifies a row, the original set was not minimal.
A table can have more than one candidate key. One candidate key is usually chosen as the primary key, but the other candidate keys still matter when checking normal forms. In an enrollment table, student ID plus section ID may be a key.
A student name is not part of that key just because it appears in the table. If student name depends only on student ID, storing it in every enrollment row creates repeated data and inconsistent updates.
Decomposition means splitting one relation into smaller relations. The shared attributes must let the original information be reconstructed by joining the smaller tables. This is why lossless decomposition matters.
Suppose an employee table stores employee ID, department ID, and department phone. Splitting it into an employee department table and a department phone table is safe when department ID determines department phone. Joining on department ID restores each employee's department phone without inventing false employee department pairs.
A bad split can create extra rows after a join, called spurious tuples. It can make a database report relationships that never existed.
BCNF is stricter than 3NF, so a relation can satisfy 3NF yet fail BCNF. This often happens when a dependency has a determinant that is not a key, while the attribute on the right belongs to some candidate key. In practice, designers may accept 3NF when it preserves important dependencies that would otherwise require repeated joins to enforce.
Fourth normal form addresses a different pattern. It handles independent many-to-many facts, such as a teacher having several subjects and several languages, where subjects do not control languages.
Keep separate facts in separate tables when their combinations are not meaningful. When studying, write each dependency in words, identify every candidate key, test one rule at a time, then verify that each split keeps the needed facts enforceable.