What are Anomalies in DBMS

What are Anomalies in DBMS

Friends, do you want to know, what are the Anomalies in the database, what are its types and how to remove Anomalies from the database.

So let us know in detail about 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.

• What are the anomalies in DBMS?
• How we remove anomalies.
• What are insertion anomalies in DBMS?
• What do you mean by anomalies in DBMS?

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 independently 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_id	e_name	e_address	e_dept
101	Rick	Delhi	D001
101	Rick	Delhi	D002
123	Maggie	Agra	D890
166	Glenn	Chennai	D900
166	Glenn	Chennai	D004

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

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 in 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 articles click on the link