The anomalies of a redundant table
The three anomalies of a redundant table (insertion, update, deletion), the redundancy that causes them and the decomposition that removes them, on a flat table of enrolments and orders.
- 22 cards
- 5 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- Scenarios
- Understand
A few cards from the deck
Redundancy: what is it?
Show the answer
The same piece of information stored several times in the database, so that one copy can differ from another.
It wastes space, but the real cost is consistency: every extra copy is a copy to keep up to date.
Léa moves and the query below is run. What does the table then hold for Léa?
```sql
-- Inscription(etudiant, adresse, cours, enseignant)
-- Léa: 2 rows (Réseaux, Bases), address '3 rue Haute'
UPDATE inscription
SET adresse = '12 rue Basse'
WHERE etudiant = 'Léa' AND cours = 'Bases';`- Two different addresses, depending on the row read
- The new address on both rows, SQL propagates the change
- A foreign key error, because the address is referenced elsewhere
- No change at all, since the WHERE clause targets two columns
Show the answer
Two different addresses, depending on the row read
Only one record is changed: the Réseaux row keeps
3 rue Haute. Nothing in the schema says the two addresses must be equal.An … anomaly occurs when the same information is copied on several rows and only some of those rows are updated.
Show the answer
An update anomaly occurs when the same information is copied on several rows and only some of those rows are updated.
A too narrow
UPDATE ... WHEREcauses it without any error: SQL does not know the copies had to stay identical.Update anomaly: what is it?
Show the answer
The inconsistency that appears when a piece of information copied on several rows is changed on only some of them.
After a customer moves, their old orders show one address and the new ones another: the database no longer has a single answer.
Two invoices of the same customer show two different addresses, because the address is copied into every order. What is the right fix?
- An ORDER BY on the date, to read the order's most recent address first
- An adresse2 column to keep the old address next to the new one, on every order
- A longer VARCHAR, so the address fits in full
- A Client table holding the address, referenced by a foreign key
Show the answer
A Client table holding the address, referenced by a foreign key
Sorting or adding a column leaves the copy in place. Only decomposition gives a single source. (If an invoice's address must stay the one on its issue date, that is a deliberate denormalisation choice, not an anomaly.)