Identify the anomalies that are present in the given table

Finding anomalies that are present in a given relation table, Solved exercise for finding the anomalies like insertion, deletion and modifcation, Fix the given table by eliminating the anomalies

Question:

Consider the relation Treatment with the schema Treatment (doctorID, doctorName, patientID, diagnosis) and functional dependencies;

doctorID → doctorName and

(doctorID, patientID) → diagnosis.

Describe different types of anomaly that can arise for this table with example records.

Answer:

Example:

doctorID	doctorName	patientID	diagnosis
D001	Mohan	PAT123	Fever
D002	Vijay	PAT110	Alergy
D003	Jenifer	PAT112	Fever
D002	Vijay	PAT121	Cold

TREATMENT has two FDs. From the FDs, we can derive that the combination (doctorID, patientID) is the primary key for TREATMENT.

Anomalies:

Insertion anomaly: The inability to store certain attributes’ values without the other attributes. If we are not able to insert a record into a relational table because of the absence of values for other attributes is called insertion anomaly.

For Treatment, we cannot insert the doctor information like doctorID and doctorName without any patient. That is, we need at least one patient to include the doctor information into the table. For example, if one more doctor Dr.Neeraj is appointed, we need to allocate at least one patient to insert Dr.Neeraj’s information. This inability is insertion anomaly.

Deletion anomaly: Deletion of values of certain attributes from any records (rows) will lead to lose of other attributes’ values of the same records. That means, we lose the complete information about an entity if we delete few values. This is called as deletion anomaly.

Deleting patients’ diagnosis could delete the name of their doctor. For example, Dr.Mohan has only one patient PAT123 registered for him. If we delete the patient PAT123, we need to delete Dr.Mohan’s details as well. This is the deletion anomaly present in the TREATMENT table.

Modification anomaly: Modification of certain values in a table may lead to change the values in more than one record if that particular value has duplicates. If we miss any one occurrence of that data, that leads to inconsistency of the table. This is called as modification anomaly.

A doctor may have more than one patient, so an update anomaly may result if a doctor’s name is changed for a given doctorID for only one patient. For example, in our table TREATMENT Dr.Vijay has two patients. Suppose that we need to change the doctor name from Vijay to Vijay Kumar. This change has to be done on two records. What if we change with second record and not changing with fourth record? If we do such thing, we are not able to find the exact name of the doctor of doctor ID D002. This stage is called as modification anomaly.

*******************

Go back to Normalization – solved exercises page.