Anomalies

• There are three types of anomalies.
  • Insertion Anomalies
  • Deletion Anomalies
  • Update Anomalies
• Consider:

  +-------+--------+------+--------+----------+--------+--------------------------+--------------+--------------+
  | Order | SKU    | Quan | Price  | ExtPrice | SKU    | SKU_Description          | Department   | Buyer        |
  +-------+--------+------+--------+----------+--------+--------------------------+--------------+--------------+
  |  1000 | 201000 |    1 | 300.00 |   300.00 | 201000 | Half-dome Tent           | Camping      | Cindy Lo     |
  |  1000 | 202000 |    1 | 130.00 |   130.00 | 202000 | Half-dome Tent Vestibule | Camping      | Cindy Lo     |
  |  2000 | 101100 |    4 |  50.00 |   200.00 | 101100 | Dive Mask, Small Clear   | Water Sports | Nancy Meyers |
  |  2000 | 101200 |    2 |  50.00 |   100.00 | 101200 | Dive Mask, Med Clear     | Water Sports | Nancy Meyers |
  |  3000 | 100200 |    1 | 300.00 |   300.00 | 100200 | Std. Scuba Tank, Magenta | Water Sports | Pete Hansen  |
  |  3000 | 101100 |    2 |  50.00 |   100.00 | 101100 | Dive Mask, Small Clear   | Water Sports | Nancy Meyers |
  |  3000 | 101200 |    1 |  50.00 |    50.00 | 101200 | Dive Mask, Med Clear     | Water Sports | Nancy Meyers |
  |  3000 | 201000 |    1 | 300.00 |   300.00 | 201000 | Half-dome Tent           | Camping      | Cindy Lo     |
  +-------+--------+------+--------+----------+--------+--------------------------+--------------+--------------+
       

• An Insertion Anomaly occurs when
  • We wish to add data, such as a new department.
  • We will need an order to go with it.
  • We can't even insert a NULL for OrderNumber as this is the Primary Key
  • We have introduced false data into our system (The fake order number)
  • We wish to add a new buyer.
  • For this, we will need to insert SKU/SKU_Description information
• A Deletion Anomaly
  • An item is deleted from the DB, but other data is lost.
  • If we delete Order 3000, SKU 100200 then Pete Hansen will disappear from our database.
  • This is a Deletion Anomaly.
• A Modification Anomaly
  • Changing the SKY_Description for an item requires modification of MANY different records.
  • Failure to do this will create an inconsistency in the data.