What i update ii delete and iii insertion anomalies occur in

What (i) update, (ii) delete and (iii) insertion anomalies occur in the EMP_PROJ and EMP_DEPT relations of Figure 14.3 and 14.4? Explain with examples using this database.

Solution

a)    EMP_DEPT

A tuple in the EMP_DEPT relation though represents a single employee but includes additional information—like, the department name for which the employee works (Dname) and the Social Security number of the department manager (Dmgr_ssn). This results into following anomalies.

Insertion Anomaly:

We cannot INSERT a new employee without a department. The only way to do this is to place NULL values in the attributes for EMPLOYEE. However, it will cause a problem as SSN is the primary key of EMP_DEPT, and each tuple represents an EMPLOYEE entity - not a DEPARTMENT entity.

Insert a Dnumber 4 for which there is no employee.This insertion is not possible without creating a dummy employee record with dummy SSN as SSN is the primary key of the relation.

Similarly, we cannot add a new DEPARTMENT unless at least one EMPLOYEE is assigned to work on it.

Deletion Anomaly:

If we delete a DEPARTMENT having only single EMPLOYEE, we will also lose the information of that DEPARTMENT. For example, if we delete the only employee of DEPARTMENT with Dnumber 1 we will lose the related information for that DEPARTMENT too.

Updation Anomaly:

If we change the value of one of the attributes of a particular DEPARTMENT- say the Dmgr_ssn for Dnumber 4 - we must update the Dmgr_ssn value of all EMPLOYEES who work in that DEPARTMENT. Here we have to change values of 3 tuples.

b)    EMP_PROJ

A tuple in the EMP_PROJ relates an EMPLOYEE to a PROJECT but also includes additional information like the Employee Name (Ename), Project Name (Pname), and Project Location (Plocation). This results into following anomalies.

Insertion Anomaly:

We cannot insert a new EMPLOYEE without a PROJECT assigned to him. The only way to do this is to place NULL values in the attributes for EMPLOYEE. However, it will cause a problem as SSN is a part of the primary key of EMP_PROJ (SSN and Pnumber together constitutes the Primary Key).

Similarly, we cannot add a new PROJECT unless at least one EMPLOYEE is assigned to work on it.

Deletion Anomaly:

If we delete a PROJECT, all the EMPLOYEES who work on that project will also be deleted. It will be problem if the EMPLOYEE is associated with a single PROJECT only.

Alternately, if an EMPLOYEE is the sole employee on a PROJECT, deleting that PROJECT would result in deleting all information of the related EMPLOYEE.

Updation Anomaly:

If we change the value of one of the attributes of a particular EMPLOYEE - say the SSN for Ename, Wong Franklin T. - we must update the SSN value of all the PROJECT records wherever the mentioned Ename occurs. Here we have to change values of 4 tuples.