Why Normalisation Exists: Anomalies and Redundancy

Concept

Normalisation is the process of organising attributes into relations so that redundancy is reduced and update anomalies are removed. It is not a style preference — each normal form removes one specific, demonstrable failure.

The badly designed relation

  STUDENT_COURSE

  roll | name  | dept | dept_head | course | title     | marks
  -----+-------+------+-----------+--------+-----------+------
   21  | Meera | CS   | Dr Rao    | CS201  | Databases |  87
   21  | Meera | CS   | Dr Rao    | CS202  | Networks  |  74
   22  | Ravi  | CS   | Dr Rao    | CS201  | Databases |  91
   23  | Anitha| EC   | Dr Iyer   | EC101  | Circuits  |  65

  Look at what repeats:
    Meera and CS and Dr Rao   -> twice
    Dr Rao                    -> three times
    Databases                 -> twice

  Every repetition is an opportunity for two copies to
  disagree. That is the whole problem in one sentence.

The three anomalies

1. Insertion anomaly

A fact cannot be recorded because an unrelated fact is missing.

  The college creates a new department, Mathematics, headed
  by Dr Nair. No students have joined yet.

  Try to insert:
    roll = ?, name = ?, dept = MA, dept_head = Dr Nair,
    course = ?, title = ?, marks = ?

  roll is part of the key, so it cannot be null.
  You CANNOT record the department until a student exists.

  Similarly, a new course with no enrolments cannot be
  recorded either.

2. Update anomaly

One change to one real world fact requires many rows to change, and any missed row makes the database contradict itself.

  Dr Rao is replaced as head of Computing by Dr Menon.

  Dr Rao appears in THREE rows. All three must change,
  atomically. Update two and miss one:

   21 | Meera | CS | Dr Menon | ...
   21 | Meera | CS | Dr Menon | ...
   22 | Ravi  | CS | Dr Rao   | ...     <- contradiction

  The database now asserts that Computing has two heads.
  Nothing in it can tell which row is correct.

3. Deletion anomaly

Removing one fact destroys another that you wanted to keep.

  Anitha withdraws from EC101, so her row is deleted.

  Deleted with it, and NOT deliberately:
    that the EC department exists
    that Dr Iyer heads it
    that a course called Circuits exists

  Those facts had no other row to live in. One student
  leaving erased three unrelated pieces of information.

Why the anomalies happen

Each one traces to a dependency whose determinant is not a key.

The general principle behind every normal form: a fact should be stored once, in the relation whose key determines it. Everything from 1NF to BCNF is a particular way of enforcing that sentence.

The fix, previewed

  DEPARTMENT ( dept, dept_head )
     CS  Dr Rao          <- head stored ONCE
     EC  Dr Iyer
     MA  Dr Nair         <- can exist with no students

  STUDENT ( roll, name, dept )
     21  Meera  CS
     22  Ravi   CS
     23  Anitha EC

  COURSE ( course, title )
     CS201 Databases     <- can exist with no enrolments
     CS202 Networks
     EC101 Circuits

  ENROLMENT ( roll, course, marks )
     21 CS201 87
     21 CS202 74
     22 CS201 91
     23 EC101 65

  Now:
    insert a department with no students     -> fine
    change the head                          -> ONE row
    delete an enrolment                      -> loses nothing else

The cost of normalisation

Normalisation is not free, and saying so is what separates understanding from recitation.

The usual engineering answer is to normalise to 3NF or BCNF for anything that is written to, and denormalise deliberately and locally where a read pattern proves it necessary. Note 6169 covers that decision.

The normal forms at a glance

Common mistakes

• Describing normalisation as removing redundancy only. The point is the anomalies; redundancy is their cause.
• Believing it always improves performance. It improves write correctness and often costs read speed.
• Normalising without knowing the dependencies. Every normal form is defined in terms of them.
• Treating anomalies as rare. Any repeated value will eventually disagree with its copy.

Exam and interview questions

1. Define insertion, update and deletion anomalies, with an example of each from one table.
2. What causes anomalies, expressed in terms of dependencies and keys?
3. State the one sentence principle behind all normal forms.
4. Give two genuine costs of normalisation.
5. Name each normal form and the single problem it removes.

Practice

1. Design a badly normalised EMPLOYEE_PROJECT table and demonstrate all three anomalies on it.
2. For the STUDENT_COURSE table above, list every fact stored more than once and count the copies.
3. Explain in three sentences why the deletion anomaly is the most dangerous of the three.

Conclusion

Redundancy causes insertion, update and deletion anomalies, and redundancy arises when a fact is stored in a relation whose key does not determine it. Every normal form is a rule for putting each fact in the relation that owns it.