Database Anomalies

---

This slide explains database anomalies and shows why a good database design is needed.

1. Below are unnormalized data items for employee information:
   
   

   emp_id   emp_name   emp_phone dept_name   dept_phone   dept_mgrname skill_id   skill_name   skill_date   skill_lvl (could be many for an employee)



2. An inappropriate database design results in the following schema:
   
   

   emp_info
   emp_id	...	skill_id-1	skill_id-2	...	skill_id-n
   ...	...	...	...	...	...



3. A single table causes anomalies:
   
   

   emp_info
   emp_id	emp_name	...	skill_id	skill_name	skill_date	skill_lvl
   09112	Jones	...	44	librarian	03-15-20	12
   09112	Jones	...	26	typing	06-30-18	10
   09112	Jones	...	89	word-proc	01-15-16	12
   12231	Smith	...	26	typing	04-15-19	5
   12231	Smith	...	39	filing	07-30-21	7
   ...	...	...	...	...	...	...

There are several database anomalies:

• Update anomaly: Changing a single attribute value may require that several rows be updated.


• Delete anomaly: Deleting some rows of the table can cause losing information that is not supposed to lose.


• Insert anomaly: Entities or instances can not be represented without including information about some other information that does not exist.