Data Anomalies

Data Anomalies are about any kind of unusual behavior which can degrade the quality of data. Please note, data quality is not only about dirty data but also about not being able to insert, update and delete required data.

Type of Data Anomalies

• Insertion: If one is unable to enter quality data in database.
• Update: If one is unable to update relevant data or able to update irrelevant data.
• Delete: If one is unable to delete relevant data or able to delete irrelevant data.
• Redundancy: If there are duplicate records in a table.

Data Normalization and database constraints play a vibrant role to make sure data anomalies doesn’t occur. To start with, let’s say there is a table with employee ID, name, department, and salary details. First wrong approach we can noticed i.e., this table is in de-normalized form, by right there should be separate tables for department and salary data using normalization process. Normalization and Denormalization are covered, in detail, in data modelling topic.

Refer to de-normalized employee table.

• If we define a rule i.e., department name cannot be NULL. When a new employee is hired and till the time, he/ she has not been assigned a department, his/ her record cannot be inserted in the employee table. Now, if management asks for a report for all employees, the report will be wrong which is a data anomaly.
• Another example can be, there is only one employee in HR department and if one wants to remove that employee data as new employee will be joining in few days. Deleting that employee record from de-normalized table means deleting HR department details as well. Now, if someone analyzes the data, at this point-in-time, there would be no HR department in the company which is a data anomaly.
• As you can see in the above table, employee name: Adam has 2 departments assigned to him, means, he has worked in multiple departments since he has joined this company. Now if there is a need to change the department name: IT for Adam and one is not aware of multiple departments tagged with Adam, an update command to update IT to e.g., Tech Org. will also update Procurement to Tech Org. which would create data quality issue in the database and is a data anomaly.
• Doing normalization by splitting employee table further into department and salary in separate tables will eliminate duplication/ redundancy of records and will save a lot of storage. Plus, above mentioned insert, update, and delete anomalies can also be addressed.