28 min read
Redundant Data and Anomalies
The same phone copied onto every loan causes three kinds of trouble when you update, insert, or delete.
You have already seen duplicated data: Sophie's phone on a card, in a notebook, and in a spreadsheet. There is a second version of the same mistake inside one list.
George Clarke keeps a single sheet at Riverside Public Library in York. Each line is a loan, and to "save time" he also types the member's phone and the book's author on that same line.
Amelia Hart counts. Sophie's phone appears twice already. The River Path's author appears twice. The sheet looks complete. It is already dangerous.
If every loan row also stores the member's phone and the book's author, then the same fact is written again for every borrow. That extra copy is redundant data: a fact stored more than once, even though one official copy would have been enough.
Redundant data is not merely untidy. It creates three kinds of failure that database designers call anomalies. An anomaly is a problem that appears when you add, change, or remove a row, because the same fact was stored in the wrong place.
| LoansAndEverything | ||||
|---|---|---|---|---|
The update problem
Sophie changes her phone to 07700 200 1188. In a well-designed database you change it once, on her member row.
On George's sheet you must find every loan line that still holds the old number. He updates January. He misses May. The library now has two phones for one person, inside the same list.
That is an update anomaly: changing one fact means hunting down every copy, and missing one copy leaves the data disagreeing with itself.
The insert problem
A new title arrives: a fourth book, not yet borrowed by anyone. On George's sheet there is nowhere honest to put it. Every row is a loan. A book with no loan has no row.
Oliver Bennett either invents a fake loan, or the title stays in a notebook — which is a second store again.
That is an insert anomaly: you cannot record a fact you need (this book exists) until some other fact exists (someone borrowed it).
The delete problem
Emily Brooks returns Bristol Harbour. It was her only loan on the sheet. George deletes the row.
The loan is gone, which is fair. The title and author are also gone, because they only lived on that loan line. The library has just "forgotten" a book it still owns.
That is a delete anomaly: removing a row that you no longer need also destroys a fact you still needed, because that fact had no home of its own.
One mixed list, three failures
Practice
Swipe or use the arrows to move between questions.
1 / 3
More lessons in Database Fundamentals · Next: Normalisation · Previous: Validation