Question

Provide five specific examples of data anomalies (including at least one insert, update, and delete anomaly each) that might happen if your database were not in at least third normal form. Provide sample non-normalized data and tables, the action taken, and the resulting anomalous data. Explain why your design avoids each possible problem.

Answer 1

Consider the table Given Below

emp_id	emp_name	emp_zip	emp_state	emp_city	emp_district
1001	John	282005	UP	Agra	Dayal Bagh
1002	Ajeet	222008	TN	Chennai	M-City
1006	Lora	282007	TN	Chennai	Urrapakkam
1101	Lilly	222008	TN	Chennai	M-City

Here the table is not in 3normal form and so the anamlies are

1. Insert anamaloy - Suppose a new employee needs to be added but whose address is not yet known in such acse teh data won't be added
2. Insert anamoly - Suppose a new eployee joins and we know the zip code but not the state then again data won't be added
3. update anamoly - suppose we want to update the state of a zip code , above 222008 zip code has Chennai as state , now if we want to update Chennai to somethign else we will have to do it for eveyr row ehere the zip code id 222008.
4. update anamoly - suppsoe we want to update city of a zip code again we will have to update each and every row where the ip code is waht we want to update
5. delete anamoly - Suppose we want to remove the zip code 222008 then it will remove all the employee also belongiing to that zip code

The above table is not in 3Normal form because the  emp_state, emp_city & emp_district dependent on emp_zip. And, emp_zip is dependent on emp_id and so to normlaize it we can make two sepaaret tables , on will store the zip code , city and state and other will map employeees to it.

So the normalized table will be

Table 1

emp_id	emp_name	emp_zip
1001	John	282005
1002	Ajeet	222008
1006	Lora	282007

Table 2

emp_zip	emp_state	emp_city	emp_district
282005	UP	Agra	Dayal Bagh
222008	TN	Chennai	M-City