How Are Anomalies Caused in DBMS?
Database anomalies are the faults in the database caused due to poor management of storing everything in the flat database. It can be removed with the process of Normalization, which generally splits the database which results in reducing the anomalies in the database.
STUDENT Table
STUD_NO | STUD_NAME | STUD_PHONE | STUD_STATE | STUD-COUNTRY | STUD_AGE |
---|---|---|---|---|---|
1 | RAM | 9716271721 | Haryana | India | 20 |
2 | RAM | 9898291281 | Punjab | India | 19 |
3 | SUJIT | 7898291981 | Rajasthan | India | 18 |
4 | SURESH | Punjab | India | 21 |
Table 1
STUDENT_COURSE
STUD_NO | COURSE_NO | COURSE_NAME |
---|---|---|
1 | C1 | DBMS |
2 | C2 | Computer Networks |
1 | C2 | Computer Networks |
Table 2
Insertion Anomaly: If a tuple is inserted in referencing relation and referencing attribute value is not present in referenced attribute, it will not allow insertion in referencing relation.
Example: If we try to insert a record in STUDENT_COURSE with STUD_NO =7, it will not allow it.
Deletion and Updation Anomaly: If a tuple is deleted or updated from referenced relation and the referenced attribute value is used by referencing attribute in referencing relation, it will not allow deleting the tuple from referenced relation.
Example: If we want to update a record from STUDENT_COURSE with STUD_NO =1, We have to update it in both rows of the table. If we try to delete a record from STUDENT with STUD_NO =1, it will not allow it.
To avoid this, the following can be used in query:
- ON DELETE/UPDATE SET NULL: If a tuple is deleted or updated from referenced relation and the referenced attribute value is used by referencing attribute in referencing relation, it will delete/update the tuple from referenced relation and set the value of referencing attribute to NULL.
- ON DELETE/UPDATE CASCADE: If a tuple is deleted or updated from referenced relation and the referenced attribute value is used by referencing attribute in referencing relation, it will delete/update the tuple from referenced relation and referencing relation as well.
How These Anomalies Occur?
- Insertion Anomalies: These anomalies occur when it is not possible to insert data into a database because the required fields are missing or because the data is incomplete. For example, if a database requires that every record has a primary key, but no value is provided for a particular record, it cannot be inserted into the database.
- Deletion anomalies: These anomalies occur when deleting a record from a database and can result in the unintentional loss of data. For example, if a database contains information about customers and orders, deleting a customer record may also delete all the orders associated with that customer.
- Update anomalies: These anomalies occur when modifying data in a database and can result in inconsistencies or errors. For example, if a database contains information about employees and their salaries, updating an employee’s salary in one record but not in all related records could lead to incorrect calculations and reporting.
Anomalies in Relational Model
Anomalies in the relational model refer to inconsistencies or errors that can arise when working with relational databases, specifically in the context of data insertion, deletion, and modification. There are different types of anomalies that can occur in referencing and referenced relations which can be discussed as:
These anomalies can be categorized into three types:
- Insertion Anomalies
- Deletion Anomalies
- Update Anomalies.