Data normalisation organises data in a database (across multiple tables/entities/relations) to ensure there is no data redundancy and to avoid anomalies (insert, deletion, update). There are three steps, First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF). Data is not fully normalised until it is in 3NF.
Data redundancy occurs when the same data is in the database multiple times, this is undesirable and can result in anomalies and impact efficiency due to database size and errors.
Update anomalies occur when there are multiple records of the same subject. If one record/tuple is updated and not the other the data will become inconsistent. Normalisation will remove the possibility of this anomaly.
Insertion anomalies occur where data cannot be added without the existence of other attributes. Normalisation will remove the possibility of this anomaly.
Deletion anomalies occur when a data is deleted but also removes important information from the database which is not related. Normalisation will remove the possibility of this anomaly.
First Normal Form
No two (or more) records can contain repeating information – each record/tuple must be unique. This is achieved by using a unique key – known as the Primary Key.
Each field/attribute must contain an atomic value within its domain. This means a field with first name and last name should be split into their atomic values FirstName, and LastName, or a full address should be split into Street, City, and Postcode. This can create additional redundancy in the data, however, 2NF resolves this.
There must be no repeating fields with the same type of data – they should become separate records.
Second Normal Form
For data to be in 2NF it must be in 1NF.
There must be no partial dependencies. Attributes must be functionaly dependent on the whole primary key. This means if there is a composite key no attribute can be dependent on only one of the attributes making the composite key. Below the composite primary key to the table is TeacherID and SubjectID.
Third Normal Form
For data to be in 3NF it must be in 2NF (and by definition, also in 1NF).
3NF ensures that all non-key attributes of a table must be fully dependent on the primary key and not dependent on any other non-key attribute. This is also referred to as removing transitive dependencies. This means data redundancy is removed and data integrity is achieved.
Note
There are further levels of Normal Form which we do not cover.