Normalization is a way of organizing the data in a database. Normalization of a database is a systematic approach of decomposing the tables to eliminate data redundancy(repetition) and undesirable characteristics like Insertion, Update and Deletion Anomalies. It is a multi-step process that puts the data into a tabular form, removing duplicated data from relation tables. Normalization in DBMS divides the larger table into the smaller tables and links them using relationships.
Normal forms are used to reduce the data redundancy in a database table.
Problems Without Normalization
If a table in a database is not properly normalized and if it has redundancy then that table will not only consume extra memory in your database but also it will be difficult to handle and update the database. Insert, Update and delete are the most frequent anomalies if a database is not properly normalized.
To gain good knowledge over the topic let’s consider an example of a student table.
An Insert Anomaly occurs when certain attributes cannot be inserted into the database without the presence of other attributes.
If there is a new admission in the institution, until the student opts a branch, data of that student can’t be inserted into the table or the branch information has to be set as NULL. If there are 200 students in a branch then the branch information is repeated 200 times which causes insert anomaly.
Update anomaly is data inconsistency that results from data redundancy and a partial update.
For example, if Mr. David leaves the institution or he is no longer HOD of Computer department. Then records of all the students have to be updated by mistake if any record is missed it leads to inconsistency of data which is update anomaly.
A delete anomaly occurs when you delete a record that contains an attribute that should not be deleted.
In the above example, two different data are kept in a single table i.e. student information and branch information. At the end of year, if student record is deleted then we will also lose the branch information which leads to delete anomaly.
Types of Normal Forms
There are five different types of normal forms.
- First Normal Form(1NF)
- Second Normal Form(2NF)
- Third Normal Form(3NF)
- Boyce and Codd Normal Form(BCNF)
- Fourth Normal Form(4NF)
We will discuss about these types of normal forms in our next tutorials.
Reference: Database Management Systems
Why is normalization important?
normalization is an important part of relational database design, because it helps with the speed, accuracy, and efficiency of the database.
What are most commonly used normal forms?
Most commonly used normal forms are:
First Normal Form(1NF)
Second Normal Form(2NF)
Third Normal Form(3NF)
Boyce and Codd Normal Form(BCNF)
What are the problems without normalization?
Insert anomaly, Update anomaly, Delete anomaly are the different problems without normalizing a database.