Normalisation is the process to breaking down large tables or relations into smaller tables to reduce data redundancy, and increase
Definition
Normalisation is used in databases to:
- Reduce duplicates
- Removes anomalies:
- Insertion anomaly: Cannot insert a new record until every single attribute is full (assuming attributes cannot be null)
- Deletion anomaly: If a record is deleted because of certain attributes, additional data from other attributes will be lost
- Update anomaly: If the attributes are ‘linked’, multiple updates need to be made
However, it does come a cost, which is increased query speed.
Process
- Remove duplicates by splitting table into two and then using foreign keys
- Remove derived attributes (can be calculated)
- In most cases, a table is ‘broken’ into other, more specialised tables, and foreign keys are used to link tables.
Functional Dependency
Normal Forms
There are multiple normal forms for a relation, which are an indicator to how normalised it is. The higher the form, the more normalised a table is.
| Description | Unnormalised | First | Second | Third |
|---|---|---|---|---|
| Ensure scalar data. Relations are one-dimensional (cannot contain other relations) | ✕ | ✕ | ✕ | |
| Remove any partial functional dependencies. Make any non-key attributes determined by the entire primary key, not a subset. | ✕ | ✕ | ||
| Remove any transitive functional dependencies | ✕ |