Anomalies in Relational Model

Anomalies in the relational model typically fall under the purview of Referential Constraints and Entity Constraints. These constraints play a crucial role in preventing and addressing anomalies in a relational database.

1. Referential Constraints and Anomalies:

   • Referential constraints ensure the integrity and consistency of data across related tables by maintaining referential integrity.

   • Anomalies, such as insertion, update, and deletion anomalies, can occur when referential integrity is not enforced.

   • Insertion Anomaly: This occurs when you can't insert a record into the referencing table because the required value in the referenced table doesn't exist. Referential constraints prevent this by requiring that values in the referencing table's foreign key must correspond to values in the referenced table's primary key.

   • Update Anomaly: This arises when updating data in the referenced table leads to inconsistencies in the referencing table. Referential constraints help maintain consistency by updating or cascading changes as needed.

   • Deletion Anomaly: This occurs when deleting a record from the referenced table results in the loss of related data in the referencing table. Referential constraints can help maintain data integrity by specifying actions to take when referenced records are deleted, such as cascading deletes or setting foreign keys to NULL.

2. Entity Constraints and Anomalies:

   • Entity constraints, specifically the requirement for a primary key in every relation, address anomalies related to entity integrity.

   • Entity integrity anomalies are prevented by ensuring that each record in a table has a unique identifier (primary key) that is not NULL.

   • Without a primary key, you could encounter anomalies like duplicate records, data inconsistency, and difficulties in uniquely identifying and managing records.

These anomalies can be categorized into three types:

1. Insertion Anomalies

2. Deletion Anomalies

3. Update Anomalies.

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.

Removal of Anomalies

These anomalies can be avoided or minimized by designing databases that adhere to the principles of 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 removing 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.

FAQs

1. What is Normalization?

Normalization is the process of splitting the tables into smaller ones so as to remove anomalies in the database. It helps in reducing redundancy in the database.

2. What are Anomalies in the Relational Model?

An anomaly is a fault that is present in the database which occurs because of the poor maintenance and poor storing of the data in the flat database. Normalization is the process of removing anomalies from the database.

3. How Anomalies can be removed?

Anomalies can be removed with the process of Normalization. Normalization involves organizing data into tables and applying rules to ensure data is stored in a consistent and efficient manner.

Advantages:

Data Integrity: Relational databases enforce data integrity through various constraints such as primary keys, foreign keys, and referential integrity rules, ensuring that the data is accurate and consistent. Scalability: Relational databases are highly scalable and can handle large amounts of data without sacrificing performance. Flexibility: The relational model allows for flexible querying of data, making it easier to retrieve specific information and generate reports. Security: Relational databases provide robust security features to protect data from unauthorized access.

Disadvantages:

Redundancy: The relational model can result in data redundancy, where the same data is stored in multiple places, leading to inefficiency and potential data inconsistencies. Complexity: Creating and maintaining a relational database can be complex and time-consuming, requiring specialized knowledge and skills. Performance: As the size of the database grows, performance can suffer due to the need to join multiple tables to retrieve information. Inability to handle unstructured data: The relational model is not well-suited for handling unstructured or semi-structured data, such as images, videos, and text documents.