Normal Forms and Normalisation
What is a normalised database?
A normalised database is one that has little or no redundant data. Typically, redundant relational databases have tables with too many attributes.
A rule of thumb
All table data should either be key or semantically depend on the key.
If you can spot data that does not directly depend on the key (recall the GP’s age field from Lecture 1), that part of the table should be split off into a separate table.
This procedure is then repeated on the new tables until closure: a fixed-point is reached where no further decompositions are needed.
Decomposition as division
“Splitting off” is essentially a division transform: an information-preserving rewrite that can be reversed using a join, which behaves like a multiplication.
A decomposition is lossless if the original table can be perfectly reconstructed by joining the decomposed tables. Not all decompositions are lossless; a good normalisation produces only lossless decompositions.
Automated vs. manual normalisation
Automated procedures have been mooted to convert databases into normal forms:
| Normal form | Key property |
|---|---|
| First Normal Form (1NF) | All attributes are atomic (no repeating groups) |
| Second Normal Form (2NF) | No partial dependencies on a composite key |
| Third Normal Form (3NF) | No transitive dependencies |
| Boyce-Codd Normal Form (BCNF) | Every determinant is a candidate key |
But computers cannot really understand what “semantically depends” means. Doing a good job of Entity-Relationship modelling in the first place, or manual decomposition, is generally preferable.
Syllabus scope
For this course, you only need to know that data should ideally be functionally or semantically dependent on the primary key. The subtleties of 3NF vs. BCNF are off the syllabus.
Closure
Closure: an iteration is repeated until there are no further changes. A fixed-point is found.
Normal form conversion is a closure iteration:
- Identify an attribute that does not depend on the key.
- Split it into a new table along with the key it does depend on.
- Repeat from step 1 on all resulting tables.
- Stop when every attribute in every table depends on the key of that table.
Why normalise?
| Benefit | Explanation |
|---|---|
| Reduced redundancy | Each fact stored once |
| Higher update throughput | Fewer locks needed per update |
| Easier consistency | No redundant copies to keep in sync |
| Simpler schema evolution | Changes affect fewer places |
Summary
- A normalised database minimises redundant data.
- The rule of thumb: every attribute should depend on the key.
- Normalisation proceeds by repeated decomposition until closure.
- Decomposition is a lossless division; JOIN is the inverse.
- Manual modelling is generally preferable to automated normalisation.