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

Models and decisions

Week 20 · Unit 7.4: The power of data · 3 lessons
3

Project: walk for water

Computing 7: Unit 7.4, pp. 112–149
GoalPlan, build on paper, test and evaluate a small data model that uses a database table and a spreadsheet.
New words
test plan · a table that lists tests, the data to use and what should happenexpected outcome · the result that should happen, worked out before the testactual outcome · the result that really happens when the test is donerecommendation · a suggestion for how to improve something
Explanation

This lesson is a small project. A school plans a sponsored walk for a water project: sponsors give 2 coins for each kilometre a pupil walks, and the target is 100 coins. The project has four steps. First plan the data and the tools. Then make the model on paper. Next test it with a test plan whose expected outcomes are worked out before testing. Finally evaluate: say what works, what does not and how to improve it. A test that fails is useful, because it shows you what to fix.

Worked examples
FieldData typeLengthKey
WalkerIDAutonumber–*
NameText20
ClassText5
SponsorFormYes/No–
Step 1: plan. The register of walkers is a database, because it stores records that will be searched. The sponsor money is a spreadsheet, because it needs calculations. The rate per kilometre is stored once in one cell, so it can be changed in one place.
C4fx=$B$1*B4
ABC
1Money per km2
2Target100
3WalkerKmRaised
4Ana612
5Omar1020
6Yuki48
7Lola816
8Ravi1224
9Total4080
Step 2: make. The spreadsheet has the rate in B1 and the target in B2. Each pupil’s money is =$B$1*B4 (and so on down). The totals are in row 9. With a rate of 2 the money raised is 12 + 20 + 8 + 16 + 24 = 80, which is below the target of 100.
TestTest dataExpected outcomeActual outcomePass/fail
1Ana walks 6 km, rate 2C4 shows 1212pass
2rate changed to 3C4 shows 1818pass
3all five walkers, rate 2C9 shows 8056fail
Step 3: test. The expected outcomes were worked out before testing. Test 3 failed. The total only reached 56, because the formula in C9 was =SUM(C4:C7) and left out Ravi’s row. The fix is =SUM(C4:C8). Then the actual outcome is 80 and the test passes.
Class activity

Your own project (no computer). Choose a small situation, such as a recycling bank that pays a small amount for each kilogram of paper brought by each class. Plan: write the fields for a database table, with data types and the primary key, and sketch a spreadsheet with the rate in one cell, a column of kilograms, a column of money, a total and a target. Make: fill in invented figures for five classes. Test: write a test plan with three tests and work out the expected outcomes first; then check each by hand. Evaluate: ask a partner to review it and write two recommendations.

Practice
1
Work out the expected outcome of each test before it is done. (The walkers are Ana 6 km, Omar 10 km, Yuki 4 km, Lola 8 km, Ravi 12 km.)
TestTest dataExpected outcome
1Omar walks 10 km, rate 3?
2five walkers, rate 3total money = ?
3a walker with 0 km, rate 2?
2
In this model the formula for Ana is typed as =6*2 instead of =$B$1*B4. The rate is changed to 3, but Ana’s money stays at 12. Why did the test fail, and how do you fix the model?
TestTest dataExpected outcomeActual outcomePass/fail
4rate changed to 3C4 shows 1812?
3
The sponsor target in B2 is 100 and the total in C9 is 80. Write a conditional formatting rule that shows the total in green when the target is reached. At which rate per kilometre does the rule first switch on, if the walkers cover 40 km in all?
CellsConditionFormat
C9??
4
After testing, a partner writes: ‘The totals are right. The form for the register has no instructions and it asks for home addresses.’ Write two recommendations.