Examples of Anomalies

Examples of Anomalies
(Updated Oct 2004)

Emp_ID	L_Name	F_Name	Dept_Name	Salary	Course_Title	Date_Completed
100	Simpson	Margaret	Marketing	48000	SPSS	6/11/2004
100	Simpson	Margaret	Marketing	48000	Surveys	10/7/2003
140	Morton	Alan	Accounting	52000	Tax Acct	12/8/2003
150	Martin	Susan	Marketing	42000	SPSS	6/11/2004
150	Martin	Susan	Marketing	42000	Java	8/12/2003
190	Davis	Larry	Finance	55000

In the above table, the primary key is a composite key (by design): Emp_ID + Course_Title. Three problems would result from the design of the table:

1. Insertion anomaly: to insert a new employee, the user must supply the values for both of the key fields Emp_ID and Course_Title – however, in a normal business situation, a new employee (such as #190 above) should be allowed to be added w/o the need of a course.

2. Deletion anomaly: what would happen to the course "Tax Acct" if Emp # 140 is to be deleted?

3. Modification anomaly: suppose Emp # 150 receives a salary raise, then how many rows need to be updated – and what if one of them is missed?
à à à à à à
To solve the above problems, normalization is employed.