Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 79 ยท Database Design Theory

Redundancy and Update Anomalies

See why storing everything in one table causes redundancy and insertion, deletion and update anomalies, with a live bad schema and the decomposition that fixes it.

You can build a database that holds all the right facts and still design it badly. The classic way to fail is to put everything into one wide table: easy to start, painful to maintain. Facts get repeated, and repeated facts drift apart. This lesson shows the symptoms, called update anomalies, on a live example. The next lessons (functional dependencies and the normal forms) give you the theory that explains and prevents them.

The goal of this chapter is a design where each fact is stored in exactly one place. That sounds obvious, but a table that mixes several kinds of facts makes it impossible, and the cure is decomposition: splitting the table into smaller ones that can be joined back together.

One wide table, three problems

Insertion Cannot record a fact without an unrelated one.
Deletion Deleting a row loses an unrelated fact.
Update One fact changed in many rows, or inconsistently.

All three come from redundancy.

1 / 2

A bad schema

A training company stores every class booking in one table: which employee attended which course, who teaches that course and what the instructor's office is.

emp_id emp_name course instructor office
1 Ana SQL Novak B12
2 Boris SQL Novak B12
3 Chen Python Reyes C03
1 Ana Python Reyes C03

The key of this table is the pair (emp_id, course), because an employee can attend several courses and a course has several attendees. But look at what else is true: the instructor and office depend only on the course, and the employee name depends only on emp_id. Those are facts about different things, glued into one row. The result is redundancy: Novak's name and office appear once per attendee, and Ana's name appears once per course.

The three anomalies

SQL Update, deletion and insertion anomalies

Press Run (or Ctrl + Enter) to see the result here.

The first query returns two rows for the SQL course, one with office B12 and one with B20: the database now holds a contradiction, and nothing tells you which row is right. After the two deletes the Python query returns nothing, so the instructor and office for Python have vanished along with the bookings. The last INSERT is the insertion anomaly. In SQLite, a composite primary key that contains a NULL is (for historical reasons) accepted in ordinary tables, so the statement may even succeed, and leave a meaningless row with no employee. In other engines it fails. Either result is bad: there is no clean way to store a course that nobody has booked yet.

  • Update anomaly: changing one fact needs changes in many rows. Miss one and the data is inconsistent.
  • Deletion anomaly: deleting the last row about one thing erases facts about another thing.
  • Insertion anomaly: you cannot store a fact about one thing until a fact about another thing exists.

Where the problem comes from

Redundancy by itself is not harmful if the database controls it. The problem is uncontrolled redundancy, where the same fact exists in several places and the schema cannot keep them equal. Here the root cause is that some columns depend on only part of the key. office is determined by course alone, not by (emp_id, course). Chapter 6 formalises this as a functional dependency and uses it to decide when a table is well designed.

The cure: decomposition

Split the wide table so that each table describes one kind of thing, and each column depends on the key of its own table:

  • employee(emp_id, emp_name)
  • course(course, instructor, office)
  • booking(emp_id, course) referencing both

Now a fact is stored once. The office changes in one row. Deleting every booking does not delete a course. A course with no bookings is just a row in course. The wide view is still available through a join.

SQL The decomposed schema fixes all three

Press Run (or Ctrl + Enter) to see the result here.

Only two bookings remain, both for SQL, and both show office B20. The course table still lists Python and Rust, so no fact was lost. Decomposition must also be lossless: joining the pieces must give back exactly the original table, with no extra or missing rows. Not every split has this property, and BCNF and Lossless Decomposition shows how to test it.

Is redundancy always bad?

Not always. Data warehouses and some read-heavy systems accept redundancy on purpose, trading write complexity for read speed (see Multivalued Dependencies, 4NF and Denormalization). The rule is to decide where to repeat data and to have a mechanism, such as triggers or a rebuild job, that keeps copies consistent. Accidental redundancy from a lazy design is what normalisation removes.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. An instructor's office is stored on every booking row. The office changes, and only some rows are updated. Which anomaly is this?

    One fact is stored in many places, so it can be changed in some places and not others, leaving the data inconsistent.
  2. 2. Deleting the last booking for a course also removes the only record of who teaches it. This is a

    Deleting one kind of fact (a booking) unintentionally erased another (the course details).
  3. 3. What is the usual cure for update anomalies?

    Decomposition places each kind of fact in its own table and links the tables with foreign keys.
  4. 4. A good decomposition must also be

    If the join of the parts gives extra or missing rows, the split lost information.

Summary

  • Putting several kinds of facts in one table causes redundancy: the same fact repeated in many rows.
  • Redundancy leads to update, deletion and insertion anomalies.
  • The root cause is columns that depend on only part of the key (or on non-key columns), which later lessons formalise as functional dependencies.
  • Decomposition gives one table per kind of fact, linked by foreign keys, and a join rebuilds the wide view.
  • A decomposition must be lossless; deliberate redundancy is a performance choice, not an accident.

Next: Functional Dependencies.