Database normalisation — the process of properly structuring a database to eliminate redundancy and prevent data anomalies. Only examinable in SBA and PAT, but foundational for understanding 12.4.11.
Reasons for normalisation:
Anomalies caused by poor (unnormalised) design:
| Anomaly | Problem |
|---|---|
| Update anomaly | The same data duplicated in multiple places must be updated everywhere consistently — miss one, and the data becomes contradictory |
| Insert anomaly | You can't add certain data without also having other, unrelated data available (e.g. can't add a new subject without a learner already enrolled in it) |
| Delete anomaly | Deleting one record accidentally causes the unintended loss of other, still-needed data |
Key fields:
| Key type | Description |
|---|---|
| Primary key | Uniquely identifies each record in its own table |
| Foreign key | A field in one table that references the primary key of another table, creating a relationship between the two |
| Composite key | A primary key made up of two or more fields combined, used when no single field is unique enough on its own |
Database concepts to identify: duplicate data (the same value repeated unnecessarily), derived data (a value that can be calculated from other stored fields rather than stored itself, e.g. Age from Date of Birth), redundant data (data that provides no additional useful information), atomic fields (a field storing a single, indivisible value) vs non-atomic fields (a field storing multiple values that should really be split, e.g. a single 'Subjects' field listing several subjects).
Normal forms — the standard levels of database normalisation:
| Form | Rule |
|---|---|
| 1NF (First Normal Form) | Eliminate repeating groups — every field must hold only a single (atomic) value, and each record must be unique |
| 2NF (Second Normal Form) | Must already be in 1NF, and every non-key field must depend on the WHOLE primary key (removes partial dependency — relevant when using a composite key) |
| 3NF (Third Normal Form) | Must already be in 2NF, and no non-key field may depend on another non-key field (removes transitive dependency) |
Table relationships:
| Relationship | Meaning | Example |
|---|---|---|
| One-to-one | One record in Table A relates to exactly one record in Table B | One Learner has one Passport record |
| One-to-many | One record in Table A relates to many records in Table B | One Teacher teaches many Classes |
| Many-to-many | Many records in Table A relate to many records in Table B (usually resolved via a linking/junction table) | Many Learners take many Subjects |
The many-to-many relationship between Learners and Subjects, resolved with a LearnerSubject linking table.
Referential integrity ensures that a foreign key value in one table must always correspond to an existing primary key value in the related table — preventing 'orphaned' records that reference something that doesn't exist.
Example
Unnormalised (repeating groups): Learner(Name, Subject1, Subject2, Subject3) Normalised: Learner(LearnerID, Name) + Subject(SubjectID, SubjectName) + LearnerSubject(LearnerID, SubjectID) — a linking table resolving the many-to-many relationship.
💡 Exam Tip
If asked to identify a normalisation violation, look first for repeating groups (1NF), then check whether non-key fields depend on the whole primary key (2NF) and whether any non-key field depends on another non-key field rather than the key (3NF) — work through the forms in order.