Database Anomalies? – Data Integrity and Normalization

What are the database anomalies? Briefly discuss insertion, deletion and modification anomalies.

Database Anomalies

Database anomalies are the problems in relations that occur due to redundancy in the relations. These anomalies affect the process of inserting, deleting and modifying data in the relations. Some important data may be lost if a relation  is updated that contains database anomalies. It is important to remove these anomalies in order t perform different processing on the relations without any problem.

Types of Anomalies

Different types of database anomalies are as follows:

• Insertion Anomaly

The insertion anomaly occurs when new record is inserted in relation. In this anomaly user cannot insert a fact about an entity until he has an additional fact about another entity.

• Deletion Anomaly

The deletion anomaly occurs when a record is deleted from the relation. In this anomaly, the deletion of facts about an entity automatically deletes the fact of another entity.

• Modification Anomaly

The modification anomaly occurs when the record is updated in the relation. In this anomaly, the modification in the value of specific attribute requires modification in all records in which that value occurs.

What is partial dependency? How does partial dependency affect a relation?

Partial dependency

A type of dependency in which one or more non-key attributes are functionally dependent on a part of primary key is called partial dependency.

Example

STUDENT (STUD_ID, NAME, DEPT, MONFEE, CRSNO, CDTE)

The relation contains a composite primary key that consists of STUD_ID and CRSNO.

Table 4.2: The STUDENT relation

 The functional dependencies in this relation are as follows:

STUD_ID à NAME, DEPT, MONFEE
STUD_ID, CRSNO à CRSDTE

The non-key attributes NAME, DEPT and MONFEE are functionally dependent on a part of primary key STUD_ID. They are not functionally dependent on complete key. It is called partial dependency. The partial dependency in the above relation creates redundancy. It results in certain database anomalies when the relation is updated.

• Insertion anomaly: Suppose a new course WEBDEV is to be inserted in the relation. The new CRSNO cannot be inserted in relation without inserting STUD_ID the primary key consists of STUD_ID and CRSNO.
• Deletion anomaly: Suppose the record of STUD_ID 140 is to be deleted. The information of TAXACCT will also be deleted along with that student.
• Modification Anomaly: Suppose the name of STUD_ID 100 is to be updated to “Ahmad Ali”. It requires to update all records in which STUD_ID 100 appears.

All the partially dependent attributes are removed and placed in another relation when a relation is converted from 1NF to 2NF. The decomposition must satisfy one of the above-mentioned conditions.

STUDENT relation can be split in two relations STUDENT1 and COURSE as follows:

STUDENT1 (STUD_ID, NAME, DEPT, MONFEE)\

Table 4.4: The STUDENT1 Relation

COURSE (STUD_ID, CRSNO, CDTE)

Table 4.5: The COURSE relation

The primary key in STUDENT1 relation consists of only one attribute. It satisfies the first condition. The attribute CDTE in COURSE relation fully functionally depends on whole composite key. It satisfies the third condition. It means that both relations are in second normal forms.

• Further Reading
• Data integrity and normalization