AdvertisementYour brand could be hereYour brand hereOn every Darslab page · students, parents and teachersinfo@darslab.comAsk for pricesPricesinfo@darslab.com
☰ Year plan · Computing

Link the tables

Week 18 · 2.3 Creating databases · 3 lessons
1

One big table, many problems

Computing 9: Topic 2.3, pp. 217–256
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.

ProblemWhat happensIn the market-stall table
Data redundancythe same facts are typed again and againDiego’s phone appears in three rows
Update anomalysome copies change and some do nottwo phone numbers for Diego
Deletion anomalydeleting a row loses facts that were only thereYuki’s phone disappears with her order
Worked examples
Order_IDItemQtyCustomerPhoneArea
1Olives2Diego555-0142Eastside
2Bread3Aisha555-0177Hilltop
3Honey1Diego555-0142Eastside
4Bread2Yuki555-0120Lakeside
5Cheese1Aisha555-0177Hilltop
6Olives4Diego555-0142Eastside
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_IDItemQtyCustomerPhoneArea
1Olives2Diego555-0199Eastside
2Bread3Aisha555-0177Hilltop
3Honey1Diego555-0199Eastside
4Bread2Yuki555-0120Lakeside
5Cheese1Aisha555-0177Hilltop
6Olives4Diego555-0142Eastside
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_IDItemQtyCustomerPhoneArea
1Olives2Diego555-0142Eastside
2Bread3Aisha555-0177Hilltop
3Honey1Diego555-0142Eastside
5Cheese1Aisha555-0177Hilltop
6Olives4Diego555-0142Eastside
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_IDPlantBuyerBuyer_phone
1MintLeo555-0301
2BasilHana555-0322
3ThymeLeo555-0301
4MintHana555-0322
5SageLeo555-0301
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_IDPlantBuyerBuyer_phone
1MintLeo555-0301
2BasilHana555-0399
3ThymeLeo555-0301
4MintHana555-0322
5SageLeo555-0301
3
Explain in your own words what a deletion anomaly is and give an example from the plant sale or the market stall.
4
Give two reasons why a flat file becomes harder to manage when the amount of data grows.