Normalisation - 1NF, 2NF & 3NF
Normalisation organises a database to reduce redundancy and avoid anomalies. This lesson explains why we normalise, the rules for first, second and third normal form, and a memory aid for them, then applies them to an exam-style question.
Key terms
- Normalisation
- Organising tables to remove redundancy and avoid anomalies.
- Atomic value
- A single, indivisible value in a field, not a list.
- First normal form (1NF)
- No repeating groups, every field holds an atomic value, and each record can be uniquely identified.
- Second normal form (2NF)
- In 1NF, and every non-key field depends on the whole primary key (no partial dependencies).
- Third normal form (3NF)
- In 2NF, and no non-key field depends on another non-key field (no transitive dependencies).
- Partial dependency
- A non-key field that depends on only part of a composite primary key.
- Transitive dependency
- A non-key field that depends on another non-key field rather than directly on the primary key.
- Insertion anomaly
- Being unable to add new data without also adding unrelated data.
- Deletion anomaly
- Deleting a record accidentally removes other data that should have been kept.
- Modification anomaly
- The same data stored in several places must be changed everywhere, or it becomes inconsistent.
- The key, the whole key and nothing but the key
- A memory aid: 1NF needs a key, 2NF depends on the whole key, 3NF depends on nothing but the key.
Part of Databases.