GoalExplain what a flat file is and describe data redundancy, update anomalies and deletion anomalies.
New words
flat file · a database that keeps all its data in one single tabledata redundancy · the same data stored again and again in different placesanomaly · something unexpected that goes wrong in the datarelational database · a database made of two or more tables that are linked to each other
Explanation
A database can be paper, such as cards in a filing cabinet, or electronic. A spreadsheet with one table is a flat file. A flat file works for a short list, but it becomes hard to manage as the data grows. If a fact about one person must appear in every row that mentions them, the same data is stored many times. That is data redundancy. It causes an update anomaly: when a fact changes and only some rows are corrected, the table disagrees with itself. It also causes a deletion anomaly: when the last row about a person is deleted, the facts that only that row held are lost too. A relational database avoids all three problems by keeping each fact once.
Problem
What happens
In the market-stall table
Data redundancy
the same facts are typed again and again
Diego’s phone appears in three rows
Update anomaly
some copies change and some do not
two phone numbers for Diego
Deletion anomaly
deleting a row loses facts that were only there
Yuki’s phone disappears with her order
Worked examples
Order_ID
Item
Qty
Customer
Phone
Area
1
Olives
2
Diego
555-0142
Eastside
2
Bread
3
Aisha
555-0177
Hilltop
3
Honey
1
Diego
555-0142
Eastside
4
Bread
2
Yuki
555-0120
Lakeside
5
Cheese
1
Aisha
555-0177
Hilltop
6
Olives
4
Diego
555-0142
Eastside
A market stall keeps all its home-delivery orders in one flat table. Diego has three orders, so his phone number and area are typed in three times, and Aisha’s are typed twice. Typing the same facts repeatedly takes time, and each repeat is a chance for a typing mistake. This is data redundancy.
Order_ID
Item
Qty
Customer
Phone
Area
1
Olives
2
Diego
555-0199
Eastside
2
Bread
3
Aisha
555-0177
Hilltop
3
Honey
1
Diego
555-0199
Eastside
4
Bread
2
Yuki
555-0120
Lakeside
5
Cheese
1
Aisha
555-0177
Hilltop
6
Olives
4
Diego
555-0142
Eastside
Update anomaly. Diego gets a new phone number, 555-0199. The helper changes orders 1 and 3 but forgets order 6. Now the table holds two different numbers for the same person, and nobody knows which is right.
Order_ID
Item
Qty
Customer
Phone
Area
1
Olives
2
Diego
555-0142
Eastside
2
Bread
3
Aisha
555-0177
Hilltop
3
Honey
1
Diego
555-0142
Eastside
5
Cheese
1
Aisha
555-0177
Hilltop
6
Olives
4
Diego
555-0142
Eastside
Deletion anomaly. Yuki cancels her only order, number 4, and the row is deleted. The stall now has no record of Yuki’s phone or area, although she might order again next week. Facts about the customer were lost because they were stored only inside the order row.
Class activity
Slip database (no computer). Write six orders for an imaginary lemonade stand on slips of paper, each slip with the buyer’s name, favourite flavour and street, as in a flat file. Two of the buyers should have two slips each. Put the slips in a pile and count how many times a street is written. Then choose one buyer, change their street on one slip only, and ask a partner to find the disagreement. Finally remove a buyer’s only slip and write down what facts you can no longer find.
Practice
1
A plant sale keeps one table. How many times is Leo’s phone number stored and how many times is Hana’s? Which problem is this?
Sale_ID
Plant
Buyer
Buyer_phone
1
Mint
Leo
555-0301
2
Basil
Hana
555-0322
3
Thyme
Leo
555-0301
4
Mint
Hana
555-0322
5
Sage
Leo
555-0301
Leo’s number is stored 3 times (sales 1, 3 and 5) and Hana’s 2 times (sales 2 and 4). This is data redundancy.
2
Hana’s phone number has changed. The table shows the result after a helper made the change. Which rows now disagree, and what is this kind of fault called?
Sale_ID
Plant
Buyer
Buyer_phone
1
Mint
Leo
555-0301
2
Basil
Hana
555-0399
3
Thyme
Leo
555-0301
4
Mint
Hana
555-0322
5
Sage
Leo
555-0301
Sale 2 shows 555-0399 but sale 4 still shows 555-0322. Two numbers are stored for Hana. This is an update anomaly.
3
Explain in your own words what a deletion anomaly is and give an example from the plant sale or the market stall.
A deletion anomaly is the loss of useful facts when a row is deleted because those facts were stored nowhere else. Example: if a buyer had only one sale in the plant-sale table and that sale were deleted, the buyer’s phone number would be lost too.
4
Give two reasons why a flat file becomes harder to manage when the amount of data grows.
The same facts are typed many times, which wastes time and causes typing mistakes. Changing a fact means finding and correcting every row, and a missed row leaves the data inconsistent. Deleting a row can also remove facts that are still needed.