Ø 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
