Modification Anomalies

Lets first start with a table for reference. THE KEY WILL BE STUDENT_ID.
COURSES
STUDENT_ID CLASS_ID CLASS_TITLE INSTRUCTOR FEE
562321124 IST-189 BASIC COMPUTER PROCESSING SAMUAL MOORE $245.00
562321124 IST-189 BASIC COMPUTER PROCESSING ADRIAN JONES $245.00
769017832 IST-250 ADVANCED COMPUTER PROCESSING JAMES WILSON $295.00
989832423 OST-223 DATA PROCESSING SAMUEL MOORE $265.00
908769987 IST-289 PROGRAMMING METHODS NORMAN YOUNG $325.00
179077643 IST-220 BASIC PROGRAMMING TAMMY GREEN $245.00


There are Three types of MODIFICATION ANOMALIES

  1. Insertion Anomaly
  2. Deletion Anomaly
  3. Update Anomaly


Insertion Anomaly
Depending on what you read there are variations on each of the three anomalies. I'll start with insertion anomaly. According to a web site from the University of Texas at Austin Graduate school of Library and Information Science, Univ. of Texas

An "Insertion Anomaly" is a failure to place information a new database entry in all the places in the database where information about a new entry needs to be stored. In a properly normalized database, information about a new entry needs to be inserted into only one place in the database; in an inadequately normalized database, information about a new entry may deed to be inserted into more than one place, and, human fallibility being what it is, some of the needed addition insertions may be missed.


With that said, I modeled the above table around the book "Database Processing, Fundamentals, Design & Implementation" By David M. Kroenke. He adds a different facet to the definition. Consider our table above. Now we want to add a new class to our schedule. CPT-173, Visual Database, and the fee will be $287.00. The problem is with the table above, we cannot add the class until someone signs up for it. Because the Primary Key is STUDENT_ID it cannot be null therefore an entry cannot be made.


So now we've seen two different opinions regarding the insertion anomaly. Both are absolutely correct. This is a pitfall to lookout for in our normalization process. Make sure when designing tables, you guard against this type of problem.

Deletion Anomaly as taken from the University of Texas Site
A "deletion anomaly" is a failure to remove information about an existing database entry when it is time to remove that entry. In a properly normalized database, information about an old, to-be-gotten-rid-of entry needs to be deleted from only one place in the database; in an inadequately normalized database, information about that old entry may need to be deleted from more than one place, and, human fallibility being what it is, some of the needed additional deletions may be missed.


In our example If we start deleting Records for example the last record in the table, a lot of data can be lost. We would lose the fact that it costs $245.00 to take the class in Basic Programming. Also the fact that Tammy Green was the instructor would be lost. In addition to the fact that we have lost that data, we've put ourselves in a Insertion Anomaly, Because now we cannot add the data back into the system until someone once again signs up for the class.


Update Anomaly as taken from the University of Texas Site
An update of a database involves modifications that may be additions, deletions, or both. Thus "update anomalies" can be either of the kinds of anomalies discussed above.



These problems with the addition and deletion of data, normal everyday stuff, have to be considered when designing, decomposing relations into new relations, determining primary keys, foreign keys, and the like. As can be seen by these examples disaster can strike, if we fail to properly normalize our data.