Data redundancy
Why a flat table that repeats the same information contradicts itself, loses facts or refuses to record them, and how to split it into two tables so that each fact is written only once.
- 28 cards
- 5 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- The numbers
- Scenarios
A few cards from the deck
Data redundancy: what is it?
Show the answer
The same piece of information stored in several places in the database, for example a customer's city copied onto each of their orders.
Every copy has to stay identical to the others: the more there are, the more likely one gets missed when something changes.
Durand has three orders (101, 102, 103) in the flat table, city Lyon. This query is run. What state is the table in afterwards?
```sql
UPDATE commande_plat SET ville_client = 'Villeurbanne' WHERE num = 102;`- Durand lives in Lyon on two rows and Villeurbanne on one: the database contradicts itself
- All three of Durand's rows become Villeurbanne, as they share the same id_client
- The database rejects the query, because the city is repeated on other rows of the customer
- Only row 102 changes and the table stays consistent, since the query succeeded
Show the answer
Durand lives in Lyon on two rows and Villeurbanne on one: the database contradicts itself
WHERE num = 102touches one row, and SQL does not know the other two talk about the same customer. You would needWHERE id_client = 7, and nothing stops you forgetting it: that is the update anomaly.When the same fact is written on several rows of a table, we speak of …; it is what makes update anomalies possible.
Show the answer
When the same fact is written on several rows of a table, we speak of redundancy; it is what makes update anomalies possible.
A piece of information written only once cannot contradict itself: removing the redundancy removes the source of the anomalies.
Update anomaly: what is it?
Show the answer
The inconsistency that appears when a piece of information copied in several places is changed in some of them but not all.
If Durand has three orders in a flat table, his city is written three times: fixing only one leaves the database saying two things at once.