Anomaly database design
- Anomalies are problems that can occur in poorly planned, un-normalized database where all the data stored in one table.
- There are three types of anomalies that can arise in the database because of redundancy are
- Insert anomalies
- Delete anomalies
- Update / Modification anomalies
- Consider a relation emp_dept (E#, Ename, Address, D#, Dname, Dmgr#) with E# as a primary key.
- Let us assume that a new department has been started by the organization but initially there is no employee appointed for that department, then the tuple for this department cannot insert into this table as the E# will have the NULL value, which is not allowed because E# is a primary key.
- Also, This kind of problem in the relationship where some tuple cannot insert known as insert anomaly.
- Now consider there is only one employee in some department and that employee leaves the organization, then the tuple of that employee has to deleted from the table. But in addition to that information about the department also will delete.
- Moreover, This kind of problem in the relation where deletion of some tuples can lead to loss of some other data not intended to removed known as delete anomaly.
Update / Modification Anomaly database design
- Suppose the manager of a department has changed, this requires that the Dmgr# in all the tuples corresponding to that department must change to reflect the new status. If we fail to update all the tuples of given department, then two different records of the employee working in the same department might show different Dmgr# lead to inconsistency in the database.
- This kind of problem known as update or modification anomaly.
How anomalies in database design can solve
- Such type of anomalies in database design can solve by using normalization.