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.
plan→make on paper→test with a plan→evaluate
Worked examples
Field
Data type
Length
Key
WalkerID
Autonumber
–
*
Name
Text
20
Class
Text
5
SponsorForm
Yes/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
A
B
C
1
Money per km
2
2
Target
100
3
Walker
Km
Raised
4
Ana
6
12
5
Omar
10
20
6
Yuki
4
8
7
Lola
8
16
8
Ravi
12
24
9
Total
40
80
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.
Test
Test data
Expected outcome
Actual outcome
Pass/fail
1
Ana walks 6 km, rate 2
C4 shows 12
12
pass
2
rate changed to 3
C4 shows 18
18
pass
3
all five walkers, rate 2
C9 shows 80
56
fail
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.)
Test
Test data
Expected outcome
1
Omar walks 10 km, rate 3
?
2
five walkers, rate 3
total money = ?
3
a walker with 0 km, rate 2
?
Test 1: 10 × 3 = 30. Test 2: the total kilometres are 40, so at 3 coins per kilometre 40 × 3 = 120. Test 3: 0 km gives 0 coins.
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?
Test
Test data
Expected outcome
Actual outcome
Pass/fail
4
rate changed to 3
C4 shows 18
12
?
The numbers 6 and 2 are typed inside the formula, so the cells B4 and B1 are not used and changing the rate has no effect. Use =$B$1*B4, so the formula reads the rate and the kilometres from the cells.
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?
Cells
Condition
Format
C9
?
?
Cell C9, condition greater than or equal to B2 (100), format green fill. At a rate of 2 the total is 80 (not green). At 3 it is 120 (green). The rate of 2.5 gives exactly 100, so the rule switches on at 2.5.
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.
Add clear instructions to the register form. Remove the home address question, because the project does not need it and personal data should be collected only when it is needed.