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?