DATABASE NORMALIZATION DBMS


Database Normalization | DBMS Tutorial | Minigranth

Database Normalization : Introduction

  • Till now we are familiar with the concept of functional dependency and closure of attributes that helps in database normalization. What does database normalization means? Let’s discuss.
  • “Database Normalization” is a process or technique to reduce the attribute redundancy and functional dependency within the set of tables present in any database. Redundancy needs to be eliminated because of its undesirable ability to generate multiple issues in the whole database.
  • The database normalization concept is implied with the help of normal forms to remove redundancy from the database.
  • Redundancy can be a major cause of concern while Inserting, Deleting and Updating the data in the tables and these issues are commonly known as “Anomalies” i.e. “Insertion Anomaly, Deletion Anomaly and Updation Anomaly”. “Anomaly” means “Inconsistency” in data. We will try to discuss all of these in detail below.
 

Database Normalization : Why it is Required?

  • Since, databases contains a hell lot of data in the form of tables, it is very difficult or almost impossible to manage data if any of the anomaly occur i.e. either Insertion, Deletion or Updation. Hence, removal of redundant data is very necessary.
  • Consider the example of a company having table “company_details” of accounts department.

This is a sample table which will be used for applyling the database normalization concept.
Table : company_details

  • Here in this table Department, Dept_ID and Manager_Name are the attributes which are same for all the employees. This type of repetition of data is called as redundancy. Let’s see the anomalies now:

1. Insertion Anomaly : If we want to add more employees to the above table, with insertion of every new employee detail attributes such as Department, Dept_ID and Manager_Name will also be inserted, which will ultimately result in higher memory usage and redundancy in data. This is called Insertion Anomaly.

2. Deletion Anomaly : If we want to delete details of an employee with respect to above table, all the other details such as Department, Dept_ID and Manager_Name will also get deleted. Similarly, if we need to delete all the other E_ID, all other details will also be deleted and the table will become blank. This is called as Deletion Anomaly where any change in one attribute causes the change in whole table.

3. Updation Anomaly : If the manager of the department gets promoted and new manager comes in place of him, then we need to update all the entries present in the table. If in case for a particular employees the manager name is not updated, the whole data will become inconsistent. This is called as Updation Anomaly.

 

Anomalies In Database Normalization : Is There Any Solution?

  • Yes, there is a solution to above anomalies we has discussed. Without applying any solution to anomalies, database normalization cannot be achieved. For this, we can split or decompose the whole Company_Details table into Employee_Details(E_ID, Name, Dept_ID) and Department_details(Dept_ID, Dept_Name, Manager_Name).

This image describes the solution to insertion, deletion and updation anomalies.
Database Anomalies : Solution

  • If in case we want to update the name of manager, we will be required to update value of just an attribute only once in the Department_Details table.
  • By splitting the table into two, it will enable us to remove all the anomalies which will further result in implementation of database normalization through normal forms. Head towards the next chapters to know more about normal forms in DBMS.

Previous Next