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:

  1. 1.      Insertion Anomaly

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

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

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

STUD_ID NAME DEPT MONFEE CRSNO CDTE
100 Ahmad Marketing 1000 SPSS 19/06/96
100 Ahmad Marketing 1000 SURVEYS 10/07/96
140 Nazir Accounting 1200 TAXACCT 12/08/97
110 Hamid Info. System 1100 SPSS 14/07/96
110 Hamid Info. System 1100 COBOL 22/07/96
190 Rashid Finance 1200 INVESTMENT 20/06/97
150 Hussain Marketing 1000 SPSS 19/06/97
150 Hussain Marketing 1000 SYSANAL 20/07/97

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 parimary 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)\

STUD_ID NAME DEPT MONFEE
100 Ahmad Marketing 1000
140 Nazir Accounting 1200
110 Hamid Info. System 1100
190 Rashid Finance 1200
150 Hussain Marketing 1000

Table 4.4: The STUDENT1 Relation

COURSE (STUD_ID, CRSNO, CDTE)

STUD_ID CRSNO CDTE
100 SPSS 19/06/96
100 SURVEYS 10/07/96
140 TAXACCT 12/08/97
110 SPSS 14/07/96
110 COBOL 22/07/96
190 INVESTMENT 20/06/97
150 SPSS 19/06/97
150 SYSANAL 20/07/97

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 compostie key. It satisfies the third condition. It means that both relations are in second normal forms.