All About Database Normalization

Ø Database Normalization

Database normalization is actually a schema design technique which helps to eliminate redundant data and reduce dependencies among the attributes of a schema. It’s a systematic approach of splitting or decomposing tables to avoid undesirable characteristics like insertion, deletion and updating anomalies in the database. Database normalization is a multi-step process to divide larger tables into smaller views to eliminate repetitive data and store data more logically and effectively. Therefore, the motive intention of database normalization is to organize data into the database more logically so that it can be extracted, inserted and updated with minimal intervention.  Since current world is driven by data and so efficiency as well as effectiveness of a real world application mostly depends on how data can be accessed, inserted, deleted and updated.

Ø Why do we need normalization?

This topic can be quite confusing for the beginners and sometimes for the experienced programmers as well. However, overall gist indication on why database normalization is essential can be summarized as follows:

⇒For eliminating duplicate data and data anomalies.

⇒For avoiding unwanted data connections and dependencies.

⇒For optimization of database storage as well as application efficiency.

⇒For reducing the delay and complexity of accessing database.

⇒For making database more natural and logical.

⇒For better facilitate data interpretation and database access to the applications as well as to the end users.

For better visualization and understa, let’s think of a real world example. Suppose, we have a table which describes employees of a well-known software company.

EmployeeId	Name	Department	Team Lead	Phone No (Team Lead)
1231	Arafat Ullah	Software	Tajul Islam	32998656
1232	Mike Hike	Software	Tajul Islam	32998656
1233	Golam Sarowar	Software	Tajul Islam	32998656
1234	Mehedi Hasan	Software	Tajul Islam	32998656

In the above table, we have data of employees of a software company, their id, name, team lead and phone no of team lead. This table is not normalized so what we can see that duplicate value is present there which is referred as redundant data. This redundant data not only consumes extra memory space in the database but also make it difficult to update, delete and insert new entries. Moreover, this insertion, deletion and update anomalies are doubled when the database is not normalized enough. Let’s dive in depth to understand those anomalies for not normalizing data.

 😢   Insertion Anomaly

Now suppose this month 20 more employees have been recruited in software engineering department. So what you have to do now is to insert those new engineers in the above table which leads repetitive insertion of department name, team lead name and team lead’s phone no. This is insertion anomaly which increases data redundancy in one sense.

       😢  Deletion anomaly

From the above mentioned table it is also noticeable that two different information are mentioned there. First one is about employee and another information is about department name and team lead name along with team lead’s phone number. Now, suppose you want to delete only employee information. Oh god!! What will happen? Department information along with department head will be deleted from the table and we will lost those information. This is referred as deletion anomaly.

         😢 Update Anomaly

Now, suppose Mr. Tajul islam who was head of the software engineering department leaves the department for some unavoidable reasons which makes some people smile and of course some employee cry. Now, a new team lead has been recruited and you have to update the name of the team lead and his phone number as well. There, just imagine, you have to update the same information again and again in each and every rows. This is devastating right?  This scenario is regarded as update anomaly.

So, I think above discussion will surely make you realize why database normalization is essential for most of the database system to operate efficiently and effectively.

 

To Be Continued – stay tuned