Before talking about the concept of normalization, first let’s understand what problem normalization solves.
Let’s take example of Teacher table. It tells us which teacher takes class of which subject.
You can see there is lot of redundancy in the Teacher table. There is duplication of data. There will be problem in inserting, updating and deleting the data due to redundancy.
To solve this problem, the concept of normalization comes into picture.
Normalization is the process of organizing data in database. Its objective to remove redundancy and data dependence i.e. to place related data in one table. It helps in removing operational anomalies. This concept was first introduced by Edgar Frank Codd.
For example, it is irrelevant to store the Customer information in Employee table.
Let’s take example of Teacher table above; three types of anomalies can occur in this table:
- Insertion anomaly: Suppose a new teacher is hired and not assigned a subject yet, then in this case we have to insert NULL value in the subject which would cause insertion anomaly.
- Updation anomaly: Since teacher name is repeated twice in the table, we will have to update the teacher name at multiple places leading to updation anomaly.
- Deletion anomaly: If we have to delete the subject ‘Maths’ then it will lead to loss of teacher Deepak Kumar’s record thus causing deletion anomaly.
Normalization removes these anomalies by application of various normal forms to decompose the table into multiple tables. We will see discuss various normal forms in another article.