Anomalies in DBMS

What is Anomalies in DBMS

Normalization is necessary if you do not do it then the overall integrity of the data stored in the database will eventually degrade.  Specifically, this is due to data anomalies.  These anomalies in DBMS naturally occur and result in data that does not match the real-world database purports to represent.

Anomalies in DBMS are caused when there is too much redundancy in the database’s information. Anomalies can often be caused when the tables that make up the database suffer from poor construction. So, what does “poor construction” mean? Poor table design will become evident if, when the designer creates the database, he doesn’t identify the entities that depend on each other for existence, like the rooms of a hotel and the hotel, and then minimize the chance that one would ever exist independent of the other.

The normalization process was created largely in order to reduce the negative effects of creating tables that will introduce anomalies into the database.

Example:

Suppose a manufacturing company stores the employee details in a table named employee that has four attributes: emp_id for storing employee’s id, e_name for storing employee’s name, e_address for storing employee’s address, and e_dept for storing the department details in which the employee works. At some point in time the table looks like this:
e_id e_name e_address e_dept

e_ide_namee_addresse_dept
101RickDelhiD001
101RickDelhiD002
123MaggieAgraD890
166GlennChennaiD900
166GlennChennaiD004
Anomalies in DBMS


The above table is not normalized. We will see the problems that we face when the table is not normalized.

Type of Anomalies in DBMS

Anomalies in dbms
Anomalies in dbms

Update anomaly:

In the above table, we have two rows for employee Rick as he belongs to two departments of the company. If we want to update the address of Rick then we have to update the same in two rows or the data will become inconsistent.

If somehow, the correct address gets updated in one department but not in other then as per the database, Rick would be having two different addresses, which is not correct and would lead to inconsistent data.

Insert anomaly:

Suppose a new employee joins the company, who is under training and currently not assigned to any department then we would not be able to insert the data into the table if the e_dept field doesn’t allow nulls.

Delete anomaly:

Suppose, if at a point of time the company closes the department D890 then deleting the rows that are having e_dept as D890 would also delete the information of employee Maggie since she is assigned only to this department.


To overcome these anomalies in DBMS, we need to normalize the data.

So by the normalization concept, you can learn how we remove these anomalies from the table.

you can also read the article on RDBMS

for more technical article click on the link

Leave a Reply

Your email address will not be published. Required fields are marked *