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.

How Are Anomalies Caused in DBMS?

Anomalies in DBMS are caused by poor management of storing everything in the flat database, lack of normalization, data redundancy, and improper use of primary or foreign keys. These issues result in inconsistencies during insert, update, or delete operations, leading to data integrity problems.

The three primary types of anomalies are:

• 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.

These anomalies can be removed with the process of Normalization, which generally splits the database which results in reducing the anomalies in the database.

STUDENT Table

STUDENT_COURSE

1. 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.

OR

An insertion anomaly occurs when adding a new row to a table leads to inconsistencies.

Example: If we try to insert a record into the STUDENT_COURSE table with STUD_NO = 7, it will not be allowed because there is no corresponding STUD_NO = 7 in the STUDENT table.  

2. 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 the STUDENT table with STUD_NO = 1, it will not be allowed because there are corresponding records in the STUDENT_COURSE table referencing STUD_NO = 1. Deleting the record would violate the foreign key constraint, which ensures data consistency between the two tables.

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.

Removal of Anomalies

Anomalies in DBMS can be removed by applying normalization. Normalization involves organizing data into tables and applying rules to ensure data is stored in a consistent and efficient manner. By reducing data redundancy and ensuring data integrity, normalization helps to eliminate anomalies and improve the overall quality of the database

According to E. F. Codd, who is the inventor of the Relational Database, the goals of Normalization include:

• It helps in vacating all the repeated data from the database.
• It helps in removing undesirable deletion, insertion, and update anomalies.
• It helps in making a proper and useful relationship between tables.  

Key steps include:

1. First Normal Form (1NF): Ensures each column contains atomic values and removes repeating groups.
2. Second Normal Form (2NF): Eliminates partial dependencies by ensuring all non-key attributes are fully dependent on the primary key.
3. Third Normal Form (3NF): Removes transitive dependencies by ensuring non-key attributes depend only on the primary key.

By implementing these normalization steps, the database becomes more structured, reducing the likelihood of insertion, update, and deletion anomalies.

Read more about Normal Forms in DBMS.