Normal Forms

• Normal forms are designed to eliminate Modification Anomalies
  • Anomalies occur when you want to change/insert/delete information about one theme and you must enter/delete data about a second theme.
  • Anomalies are due to poorly designed tables.
  • Anomalies can be
    • Insertion
    • Deletion
    • Update.
• Consider the following table:

  ItemNumber	EquipmentType	AcquisitionCost	RepairNumber	RepairDate	RepairCost
  100	Drill Press	3500	2000	2013-05-05	375.00
  200	Lathe	4750	2100	2013-05-07	255.00
  100	Drill Press	3500	2200	2013-06-19	178.00
  400	Mill	27300	2300	2013-06-19	1875.00
  100	Drill Press	3500	2400	2013-07-05	0.00
  100	Drill Press	3500	2500	2013-08-17	275.00

• Is the table a relation?
  • What would the dependencies be?
  • What would the primary key be?
• This table presents some problems.
  • What if we delete the second row?
    • All information about the Lathe is lost.
    • Or all information about Repair 2100 is lost.
    • These two items are related, but they are clearly not the same data.
    • When the deletion of one row forces the removal of two different things, it is called a deletion anomaly.
  • What if we acquire a new piece of equipment?
    • We could insert it, but we would need to make up a bogus repair record.
    • The structure of a table which forces the entry of facts about two entities when adding an entity is a insertion anomaly
  • What about modification
    • Insert (100, "Drill Press", 5500, ...)
    • Insert (100, "Drill Prass", ...
    • Update anomalies occur when updating information in one row the same information is allowed to remain unchanged in another.
  • Why does this table lead to these anomalies?
    • It contains information about two different things
    • Equipment
    • Repairs.
• Read 132, 133 - History of Normal Forms.