An anomaly is a deviation from the norm, a glitch or an error that doesn’t fit in with the rest of the pattern of the database. Normalization takes care of these anomalies. Normalization ensures that all three challenges (update, insert, and delete anomalies), as well as any others that may arise, are addressed during the design process.
In this article, we will dive deeper into Anomalies in DBMS according to the GATE Syllabus for (Computer Science Engineering) CSE. Keep reading ahead to learn more.
Table of Contents
- What are the Anomalies in DBMS?
- How are Anomalies Caused in DBMS?
- Type of Anomalies in DBMS
- Practice Problems on Anomalies in DBMS
What are the Anomalies in DBMS?
Normalization is required to organise data in a database. If it is not done, the overall data integrity in the database will deteriorate over time. This is related to data abnormalities in particular. These DBMS anomalies are common, and they result in data that doesn’t match with what the real-world database claims to reflect.
When there is too much redundancy in the information present in the database, anomalies occur. Also, when all the tables that make up a database are poorly constructed, anomalies are bound to occur.
How are Anomalies Caused in DBMS?
What exactly does “bad construction” imply? When the DB (database) designer constructs the database, he should identify the entities that rely on one other for existence, such as hotel rooms and the hotel, and then reduce the probability that one might ever exist independently of the other.
A database anomaly is a fault in a database that usually emerges as a result of shoddy planning and storing everything in a flat database. In most cases, this is removed through the normalization procedure, which involves the joining and splitting of tables. The purpose of the normalization process is to minimise the negative impacts of generating tables that would generate anomalies in the DB.
Example
Consider a manufacturing firm that keeps worker information in a table called employee, which has four columns: w_id for the employee’s id, w_name for the employee’s name, w_address for the employee’s address, and w_dept for the employee’s department. The table will look like this at some point:
| w_id | w_name | w_address | w_dept |
| 201 | David | Delhi | F001 |
| 201 | David | Delhi | F002 |
| 223 | Mike | Agra | F890 |
| 266 | Berry | Chennai | F900 |
| 266 | Berry | Chennai | F004 |
The table above has not been normalized. We’ll look at the issues that arise when the table isn’t normalized.
Type of Anomalies in DBMS
Various types of anomalies can occur in a DB. For instance, redundancy anomalies are a very significant issue for tests if you’re a student, and for job interviews if you’re searching for a job. But these can be easily identified and fixed. The following are actually the ones about which we should be worried:
1. Update
2. Insert
3. Delete
Anomalies in databases can be, thus, divided into three major categories:
Update Anomaly
Employee David has two rows in the table given above since he works in two different departments. If we want to change David’s address, we must do so in two rows, else the data would become inconsistent.
If the proper address is updated in one of the departments but not in another, David will have two different addresses in the database, which is incorrect and leads to inconsistent data.
Insert Anomaly
If a new worker joins the firm and is currently unassigned to any department, we will be unable to put the data into the table because the w_dept field does not allow nulls.
Delete Anomaly
If the corporation closes the department F890 at some point in the future, deleting the rows with w_dept as F890 will also erase the information of employee Mike, who is solely assigned to this department.
Practice Problems on Anomalies in DBMS
1. Due to _______, the database design precludes some data from being stored.
a. Insertion Anomalies
b. Deletion Anomalies
c. Selection Anomalies
d. Update Anomalies
Answer – (a) Insertion Anomalies
2. The results of a table that shows data redundancy would yield the __________ anomalies.
a. Update
b. Insertion
c. Deletion
d. All of these
Answer – (d) All of these
3. What are the different types of relationships produced by the technique in order to prevent modification anomalies?
a. Referential integrity constraints
b. Functional dependencies
c. Normal forms
d. None of the above
Answer – (c) Normal forms
Keep learning and stay tuned to get the latest updates on the GATE Exam along with Eligibility Criteria, GATE Syllabus for CSE (Computer Science Engineering), GATE CSE Notes, GATE CSE Question Paper, and more.
Also Explore,
Comments