Anomalies in dbms can be defined as the flaws or problems in the relational database that can be occurred by the operations such as some instances of insertion leading to insertion anomalies in dbms, deleting rows that can lead to deletion anomalies in dbms.
What are Anomalies in DBMS?
Database Management Systems are an important tool for the storage, retrieval and manipulation of data in today’s entirely data-dependent world. But with the increase number of tuples or rows in the data, the anomalies tend to increase.
Thus, Anomalies are the inconsistencies occurring in the table on performing any of the three operations, update, insert or delete as types of anomalies in dbms illustrated in the above-mentioned table.
Insertion Anomaly
Discussing anomalies in dbms, you must be aware of anomalies as the inconsistencies in the relation. Now, Insertion Anomalies in DBMS are defined with relevant real-world example in this section.
Insertion Anomalies in DBMS are encountered when inserting data into the table. A few of the common examples of such anomalies are related to faulty data management or missing attributes such as taking the example of studying in an engineering college in their respective departments, but if a student joins the college without generation of enrollment number or even department, then adding the student without an enrollment number will cause an error unless the database administrator has allowed students with NULL value as enrollment number.
Example of Insertion Anomalies in DBMS
Suppose you have a database table named "Engineers" with columns for "S. No", "Name", "Department", and "Enrollment No” with the table mentioned as follows:-
Table Name: Engineers
| S. No | Name | Department | Enrollment No |
|---|---|---|---|
| 1 | Vijay | CSE | 1234567 |
| 2 | Manish | IT | 1234568 |
| 3 | Sid | ECE | 1234569 |
In this table, suppose you want to insert a new student named John into the table, but you don’t yet know their Department thus even if you try to insert a new record without specifying the Department, you will encounter an insertion anomaly because the database won’t allow you to leave the "Department" field blank. To avoid the anomaly, you would need to add a new department to the "Departments" table before inserting the new student into the "Engineers" table.
Updation Anomaly
Updation anomalies in dbms are another form of anomalies that arise when any form of changes are made to the data in a table but the changes do not reflect in another table. This is because data is interdependent across tables being referenced between tables.
One of the examples of updation anomaly is when we have two tables, table one to store customer information and the other to store the order details in an e-commerce store, to reference the customer information in the order details table, a foreign key is used but if the customer name is updated in the customer table with the previous name existing in the table with order details, then then it would lead to inconsistencies what is known as updation anomalies in dbms.
Example of Updation Anomalies in DBMS
Suppose you have a database table named "Customers" with columns for "ID" "Name" and “Email”,
Table Name: Customers
| ID | Name | |
|---|---|---|
| 1 | Alice | alice1@ok.com |
| 2 | Bob | bob2@ok.com |
| 3 | Charles | charles3@ok.com |
And a table named “Orders” with columns for “ID”, “Customer ID”, “Order Date” and “Order Total” with the tables mentioned as follows:-
Table Name: Orders
| ID | Customer ID | Order Date | Order Total |
|---|---|---|---|
| 1 | 1 | 15-02-2023 | 1000 |
| 2 | 1 | 16-02-2023 | 500 |
| 3 | 2 | 11-01-2023 | 600 |
| 4 | 3 | 12-01-2023 | 150 |
Suppose there is a change of Name in Customers table then Orders table still references the old name of the customer, then through Customer ID you can refer to the name of the customer despite the name being updated. Thus, in order to prevent updation anomaly, it becomes necessarily important to design a database in a manner that tables are normalized and foreign keys are taken care of to maintain referential integrity in the database.
Deletion Anomaly
Deletion anomalies in DBMS are the third type of anomaly that occurs on deleting data from the table with significant data loss in the process. It has a high chance that the data is not normalized and spread across tables.
One example of this can be a table, again a customer table of an e-commerce company that uses a single table for all the orders placed, but on cancelling the order, the order information must be removed and since there is no other table to store customer information, the customer information is bound to be deleted as well. Thus, we can split the table into two, one that has requested orders and the other holding the customer details to increase customer retention.
Example of Deletion Anomalies in DBMS
Suppose you have a database table named "Products" with columns for "Product ID," "Name," "Description," "Quantity," and "Price." The table contains the following data:
Table Name: Products
| Product ID | Name | Description | Quantity | Price |
|---|---|---|---|---|
| 1 | Tablet | 128 GB | 2 | 20000 |
| 2 | Smartphone | 64 GB | 1 | 8000 |
| 3 | Notebook | 512 GB | 3 | 50000 |
Suppose you want to delete the "Notebook" product from the table for some reason. In that case, you will encounter a deletion anomaly because the table will lose descriptions about “Notebook” as well as its quantity, and price. To avoid the anomaly, you would need to move this information to a separate table to preserve the data for future uses.
Conclusion
In this article, we started defining what are anomalies in dbms and progressed to discussing the types of anomalies in dbms. The three types discussed were insertion anomalies in dbms, updation anomalies in dbms and deletion anomalies in dbms. We hope you found this article helpful and expect to see you soon with another informative article.
Frequently Asked Questions
1. What are the anomalies in DBMS?
Anomaly in DBMS refers to unexpected behaviour or inconsistency in data that can occur when performing operations like inserting, updating or deleting on a database table. There are three types of anomalies in dbms: insertion, deletion, and update anomalies.
2. How can I prevent anomalies in my database?
To prevent anomalies in a database, you need to ensure that it is properly designed and normalized. Normalization is the process of organizing data in a database to minimize data redundancy and to ensure data dependencies make sense. Proper normalization can help eliminate the potential for anomalies and help maintain data integrity.
3. What are the risks of not handling anomalies in a DBMS?
Not handling anomalies in a DBMS can result in data inconsistencies and errors. It can also lead to duplicate data, inaccurate data, and difficulties in maintaining and querying the database. These issues can result in incorrect data analysis, decision-making, and other business processes that rely on accurate and consistent data. Ultimately, it can lead to data quality issues that can impact the success of an organization.
4. What is referential integrity?
Referential integrity is a concept in DBMS that ensures that the relationships between tables are maintained and that data in one table thaences data in another table is accurate and consistent.