Anomalies in DBMS

By Priyanshu Vaish|Updated : October 6th, 2022

The Anomalies in DBMS are typically caused by poor planning and the use of flat databases. A discrepancy between two parts of a database is referred to as an anomaly. Normalizing the table by splitting or joining them usually removes anomalies in DBMS. Normalization results in structured data.

In this article, we will look in depth at data anomalies in DBMS using an example.  We will also look at how anomalies in DBMS are caused and the different types of anomalies in DBMS, with an example such as insert, delete and update anomalies in DBMS.

Table of Content

What are the Anomalies in DBMS?

A database anomaly is a data inconsistency caused by an operation such as an insertion, deletion, or update. When a record is held in multiple locations, and not all copies are updated, inconsistencies can occur.

Anomalies in DBMS Definition

Anomalies in DBMS occur when the data in the database contains too much redundancy and the tables that comprise the database are poorly constructed.

What Causes Anomalies in DBMS?

Poorly constructed tables in a database are commonly the main cause behind anomalies. Poor construction means when a designer creates a database, he fails to identify interdependent entities, such as rooms in a hostel and the hostel and then minimizes the chances of one entity being independent of another.

Anomalies in DBMS PDF

Database anomalies in DBMS are a weakness within a database that can result from poor planning or when all data is stored in a flat database. Typically, a normalization procedure that combines and splits tables is sufficient to remove this. We reduce the likelihood of creating tables that generate anomalies by normalizing the database.

Types of Anomalies in DBMS

If a database design isn't perfect, it may contain anomalies, a database administrator's major headache. It is nearly impossible to manage a database that contains anomalies. There are various types of anomalies in DBMS that are as follows:

  • Insert Anomalies in DBMS
  • Update Anomalies in DBMS
  • Delete Anomalies in DBMS

Insert Anomalies in DBMS

When certain attributes cannot be inserted into the database without the presence of other attributes, this is referred to as an Insert Anomalies in DBMS. For example, suppose there is a student database with four columns: Rollno for the student's id, Name for the student's name, Address for the student's address, and Club for the student's club. Eventually, the table will appear as follows:

Rollno

Name

Address

Club

220

Annamalai

Kerala

yoga

220

Muthu

Kerala

Music

231

Mukesh

Mumbai

Crypto

232

Muni

Karnataka

Public Speaking

232

Muni

Karnataka

Arts

If a new student named Nanda joins the college and has no department affiliation because the club only accepts students in their second year, he will appear in the above table. Then we can't insert Nanda's data into the table because the Club field doesn't accept null values.

Update Anomalies in DBMS

An Update Anomalies in DBMS occur when one or more instances of duplicated data are updated but not all. For example, suppose there is a database of rooms that contains four columns: Courseno for the course type, Name for the tutor's name, Rollno for the room allocated, and Room_size for the room space. Eventually, the table will appear as follows:

Courseno

Name

Roomno

Room_size

220

Annamalai

10

50

220

Muthu

11

150

231

Mukesh

15

250

232

Muni

15

250

Roomno 15 has been improved; it is now Room_size = 500. For updating a single entity, we have to update all other columns where Roomno 15.

Delete Anomalies in DBMS

When certain attributes are lost due to the deletion of other attributes, this is referred to as Delete Anomalies in DBMS. For example, suppose there is a database of rooms that contains four columns: Courseno for the course type, Name for the tutor's name, Rollno for the room allocated, and Room_size for the room space. Eventually, the table will appear as follows:

Courseno

Name

Roomno

Room_size

220

Annamalai

10

50

220

Muthu

11

150

231

Mukesh

15

250

232

Muni

15

250

If we remove the entity courseno: 231 from the above table, the details of roomno 15 get deleted. This implies the corresponding course will also get deleted.

How to Remove Anomalies in DBMS?

To avoid anomalies in DBMS, normalize the database by efficiently organizing data in a database.

The following are the goals of normalization, according to Edgar F Codd, the inventor of relational databases:

  • removing all duplicate (or redundant) data from the database
  • removing unnecessary insertions, updates, and deletions anomalies in DBMS
  • reducing the need to restructure the entire database whenever new fields are added
  • making table relationships more useful and understandable

Important GATE Topics

Types Of Op AmpTellegen's Theorem
What Is GearDegree Of Static Indeterminacy
Difference Between Linker And LoaderMillman's Theorem
OP AmpSimple Op Amp Circuits
Maxwells Reciprocal TheoremDifference Between Abstract Class And Interface

Comments

write a comment

FAQs on Anomalies in DBMS

  • A database anomaly is a flaw in a database that usually results from poor planning and storing everything in a flat database. This is usually removed during the normalization procedure, which involves joining and splitting tables.

  • The different types of Anomalies in DBMS are as follows:

    • Insert Anomalies in DBMS
    • Delete Anomalies in DBMS
    • Update Anomalies in DBMS
  • The anomalies in DBMS is a fault in a database that usually results from poor planning and storing everything in a flat database. This is usually removed during the normalization procedure, which involves joining and splitting tables. The goal of the normalization process is to reduce the negative effects of creating tables that cause anomalies in DBMS.

  • The following are the goals of normalization, according to Edgar F Codd, the inventor of relational databases:

    • removing all duplicate (or redundant) data from the database
    • removing unnecessary insertions, updates, and deletions anomalies in DBMS
    • reducing the need to restructure the entire database whenever new fields are added
    • making table relationships more useful and understandable
  • Employee David has two rows in the preceding table because he works in two different departments. If we change David's address, we must do so in two rows or the data will become inconsistent. If the correct address is updated in one department but not in another, David will have two addresses in the database, which is incorrect and results in inconsistent data.

Follow us for latest updates