Normalization in DBMS

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.

Insert Anomaly

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

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.

Delete 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.

Types of normalization
  1. First Normal Form(1NF)
  2. Second Normal Form(2NF)
  3. Third Normal Form(3NF)
  4. Boyce and Codd Normal Form(BCNF)
  5. Fourth Normal Form(4NF)

We will discuss about these types of normal forms in our next tutorials.

Also Read: –> Views in SQL –>DBMS Integrity Constraints

Reference: Database Management Systems

FAQ?

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.

Leave a comment