Normalizing the Data › Why Normalize Data?

Why Normalize Data?

Update anomalies

Through normalization, you can develop a database that is protected against update anomalies. Update anomalies are abnormal processing conditions that result from the execution of update functions against the database. Update anomalies sometimes compromise the integrity of the database; therefore, you need to design data entities and relationships that, when implemented as data tables, are fully protected against such anomalies.

Types of anomalies

The following examples illustrate two types of anomalies:

• Deletion anomaly──Suppose you want to delete some information from the following data table:

                 JOB
  
  EMP ID     JOB ID     SALARY GRADE     SALARY
  
  1216       ADM          18              15000
  1041       MGR          30              30000
  1633       INST         23              22000
  1063       ADM          18              18000
  

  In the JOB table, the SALARY GRADE depends only on the JOB ID. If you delete the row for employee 1041 in the JOB table, you therefore lose not only the fact that employee 1041 is a manager, but also the fact that the SALARY GRADE for a manager is 30.

• Insertion anomaly──Suppose you want to add some information to the JOB table. You want to enter the fact that a programmer has a SALARY GRADE of 21. Because of the structure of the JOB table, you cannot enter this information until someone actually has a job as a programmer.

Preventing anomalies

To prevent anomalies from occurring during deletions and insertions of rows in the JOB table, you might create two separate tables:

     POSITION                                JOB

EMP ID     JOB ID     SALARY           JOB ID       SALARY GRADE

1216       ADM         15000           ADM            18
1041       MGR         30000           MGR            30
1633       INST        22000           INST           23
1063       ADM         18000           PGMR           21

Now you can delete the row for employee 1041 in the POSITION table without losing the fact that the SALARY GRADE for a manager is 30. You can also specify that a programmer has a SALARY GRADE of 21 in the JOB table without first specifying a programmer's name.

By breaking down data tables into smaller tables, you prevent update anomalies from occurring.