What is normalization? What are its merits, demerits and types?


Why normalization is important in Database Management System? What are its advantages and disadvantages? How normalization is categorized while using DBMS? All these questions has been resolved in this article.

What is Normalization


Normalization is the procedure to split the relation into relations with less attributes thereby minimizing the redundancy of the data and minimizing the insertions, deletions and updating. In other words we can say that the normalization is that process in which we renovate an un-normalized relation into relations. There is a sequence of stages or steps on which the normalization works. This sequence is called normal forms. The normal forms are relevant to entity relations. If the form satisfies a certain sets of constraints then a table or a relation comes to the particular normal form. There are above five normal forms. These are 1NF, 2NF, 3NF, 4NF and 5NF. Here NF stands Normal Form. It is very important for relational data model to consider that the first normal form (1NF) is very grave in creating the relations. Rests of all the forms are optional. There are some guidelines which help to create a good database. There is always a need for updating the database, so that there are three anomalies for data modification.

  • Insert–This irregularity refers to the circumstances when one cannot insert a new row(tuple) into a relation because of the shortage of data.

  • Delete-The delete anomaly refers to a state of affairs where the removal of records outcome in accidental loss of several other significant data.

  • Update-The update abnormality is a state wherever an update of a lone data value needs numerous rows of data to be updated.

Advantages and Disadvantages of normalization
Advantages of normalization are as follows-

  • Reduce the redundancy.

  • A large amount added bendable database design.

  • Better overall database association.

  • Data uniformity inside the database.


With these advantages there are some disadvantages of normalization given below-

  • You are not able to begin construction of database prior to know what the customer desires.

  • With the increase in the normal forms its performance gets slower and slower.

  • It is extremely time consuming and complicated method in normalizing relations of advanced degree.

  • Careless disintegration may guide to terrible design of database which may lead to somber troubles.



Types of Normalization


There is a series of normal forms but the most commonly used normal forms are 1NF, 2NF and 3NF given below.

  • First normal form(1NF)-In 1NF the column must have unique value because the repeating group of data cannot exist in arrow that containing the data. Each row of data must have an identifier to identify the data i.e primary key. For example in a student named table the name is repeated again and again of the same id, it makes the table very long and complex. To reduce those tables to the first normal form break the table into two parts where the marks and names by id are given separately in table.

  • Second normal form(2NF)-A table to become normalized to the Second normal form(2NF) should gather all the stuff of first normal form and there have to not be any fractional need of the column on primary key. It means a table that has concatenated primary key, every column in the table that is not part of the primary key must depend upon the whole concatenated key for its maintenance. So if any column depends only on one part of the concatenated key, then the table gets fails.

  • Third Normal form(3NF)-It applies that all non-prime attribute of table must be reliant on primary key. The transitive functional dependency should be detached from the table. The table must be in Second Normal form. The benefits of detaching the transitive dependence are that the quantity of the duplicate data is decreased and the data integrity can be accomplish.


Comments

Guest Author: Yashwath K S22 May 2017

Can we use functions in where clause? Why do we use "AS" in alias name? Can we use alias name in "group by" Clause and "order by" clause?

Author: Mjaay16 Aug 2017 Member Level: Silver   Points : 4

The theory given for the normalization is good.But I would like to give a feedback that the explanation given to the normal forms is too confusing. Some normal forms don't have an example. It could be made more understandable by including functional dependencies. This is the way we could get a better idea.

I would like to correct the explanation given for 1NF- This normal form requires atomicity. Example: If I have a relation.
Name Course
Rahul C/C++
To make this relation in 1NF it would look as follows
Name Course
Rahul C
Rahul C++
This is done in order to overcome anomalies that occur in databases.

2NF - this mainly lays focus that there should not be partial dependency.
eg ab is candidate key
c,d,e,f are non prime attributes
so there can never be a situation where subset of candidate is functional dependent on non prime attribute
e.g
a->d (not in 2 NF)
3 NF- This normal forms basically tells that there should not be transitive dependency that is
a->b and b->c
then a->c (transitive dependency).This should not exist.



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: