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

Term 2 round-up

Week 21 · Review: Term 2 · 3 lessons
3

Review: sheets and databases

Computing 7: Units 7.3–7.4, pp. 82–149
GoalUse formulae, absolute references and queries, and choose the best tool for a data job.
New words
absolute reference · a cell reference with $ signs, such as $B$1, that does not move when a formula is copiedprimary key · a field whose value is different in every record, so that each record can be foundquery · a question that asks a database to show the records that match the criteriawhat-if analysis · changing a value in a model to see what happens to the results
Explanation

A spreadsheet is good at calculations. A formula starts with =, uses cell references and keeps its answer up to date when a value changes. SUM and AVERAGE work on a range such as B2:B6. A reference such as B1 moves when a formula is copied down, but $B$1 is locked and always points to the same cell. A conditional formatting rule colours the cells that meet a condition. A database stores many records in a table of fields. Each record needs a primary key, each field has a data type, and a query uses criteria such as Minutes > 30 to select records. A data model uses factors and rules to predict results, and a simulation imitates a real situation, so people can try things safely.

SpreadsheetDatabase
cells and formulaefields, records and queries
SUM, AVERAGEcriteria such as Minutes > 30
$B$1 locks a cellprimary key makes each record unique
conditional formatting rulehighlight rule on a form or report
what-if: change a cellreport: group and count records
Worked examples
C3fx=$B$1*B3
ABC
1Price per bib3
2TeamBibsCost
3Reds824
4Blues1236
5Greens1030
6Golds618
7Total36108
A sports club buys team bibs at the price in B1. The formula =$B$1*B3 is copied down, so the price stays locked and only the number of bibs changes. The total is 36 bibs and a cost of 108. A conditional formatting rule ‘Cost greater than 25’ shades the two cells that hold 36 and 30.
RouteNo (key)StartMinutesNight service
12Harbour25No
14Market40Yes
21Harbour35No
25Station50Yes
33Market20No
41Station45Yes
A bus company keeps a database of its routes. Start repeats (every start appears twice) and Night service repeats, and two routes could one day take the same number of minutes, so RouteNo is the right primary key. The query ‘Minutes > 30’ shows RouteNo and Minutes for routes 14, 21, 25, 41. The query ‘Night service = Yes’ selects routes 14, 25, 41.
SituationBest tool or methodWhy
add up the rainfall of a school garden and find the averagespreadsheetformulae such as SUM and AVERAGE do the calculations
800 library books that readers search by title or authordatabasea query can search many records quickly
try different ticket prices to see the incomespreadsheet (what-if)change one cell and every formula updates
learner pilots practise landing in a moving-picture programsimulationmistakes are safe and the real plane is not needed
Choose the tool by looking at the job. Calculations on numbers and ‘what if?’ questions suit a spreadsheet. Many records that people search suit a database. A simulation is used when real practice would be dangerous, expensive or impossible.
Class activity

Tool chooser (no computer): write eight jobs on cards, such as ‘the total cost of a school trip’, ‘a list of all the pupils in the choir and the days they can come’ and ‘a moving-picture program for practising a fire drill’. Make three boxes: spreadsheet, database and simulation. Put each card in a box and write one reason on the back. Then pick one of the spreadsheet cards and draw a grid on paper with a formula that uses a locked cell reference.

Practice
1
Write the formula for each empty cell and work out what it shows: the total and the average number of pages read.
AB
1ClassPages read
25A120
35B95
45C150
55D135
6Total?
7Average?
2
Write the criteria for each query and list the titles that are selected. (a) the titles of films shorter than 100 minutes (b) the titles of films that have not been seen
FilmID (key)TitleMinutesSeen
1Desert Kite85Yes
2The Silver Fox102No
3Moon Garden96Yes
4Tiny Giants118No
5River Song90No
3
The cost in D2 shows #VALUE! instead of 24. Explain the mistake and write the correct formula.
D2fx=A2*C2
ABCD
1ItemPriceNumberCost
2Cap64#VALUE!
4
The price of a plant pot rises from 4 to 5. Work out the new cost of each class and the new total. Which costs are shaded by the rule ‘Cost greater than 50’?
ABC
1Price per pot5
2ClassPotsCost
3Year 7A9?
4Year 7B14?
5Year 7C11?
6Year 7D6?
7Total40?