Pre-requisite: DBMS | Relational Model Introduction and Codd Rule, DBMS | Keys in Relational Model (Candidate, Super, Primary, Alternate, and Foreign)
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?
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.