GoalCompare spreadsheets and databases and choose the better tool for a real situation, giving reasons.
New words
data model · a spreadsheet or database that copies a real situation, so that you can study it and test changesspreadsheet · a grid of cells that is best for numbers and calculationsdatabase · a set of tables with fields and records that is best for storing and searching lots of factsscenario · a description of a real situation that is to be modelled
Explanation
Spreadsheets and databases both help people make decisions with data. A spreadsheet stores data in cells and uses formulas to calculate and to show how numbers are linked. A database stores records under field headings, uses primary keys to identify records and uses queries to search. Both can use conditional formatting to highlight important data. To choose, ask: do I need many calculations, and is the data mainly numbers? If so, a spreadsheet is probably better. If the main job is to store and search many records, choose a database. Some jobs need both.
Spreadsheet
Database
data in cells
data in fields and records
labels
field headings
formulas for calculations
queries to search
data formats
data types
conditional formatting
conditional formatting
no key needed
primary key for each record
Worked examples
B5fx=SUM(B2:B4)
A
B
1
Item
Cost
2
Fuel
40
3
Food
55
4
Tickets
30
5
Total
125
A family plans a day trip and wants to know the total cost. This is a calculation job on numbers, so a spreadsheet is best. The formula in B5 is =SUM(B2:B4). Change the cost of one item and the total updates by itself.
Part of the plan
Bike repair stall
Fields
JobNo (key), OwnerName, BikeType, Fault, Ready
Data types
Autonumber, Text, Text, Text, Yes/No
Query
BikeType, Ready where Ready = Yes
Tool
database
A bike repair stall records each bike that is brought in. The stall will have hundreds of records and wants to ask questions such as ‘which bikes are ready to collect?’. This is a storing and searching job, so a database is best. Each bike gets a primary key, and a query uses the criteria Ready = Yes.
Question
If the answer is yes
Many calculations?
spreadsheet
Mainly numbers?
spreadsheet
Many records to store and search?
database
Each record needs a unique identity?
database with a primary key
Some situations need both tools. A school fair stores the names of stall holders in a database and uses a spreadsheet to add up the money. A good planner writes down the data, the calculations and the searches first, and then chooses.
Class activity
Tool chooser cards (no computer). Write ten situations on cards, such as ‘the monthly savings of a family’, ‘a list of all the plants in a school garden with their needs’, ‘points in a quiz competition’ and ‘who has borrowed which book’. Make two piles labelled spreadsheet and database. Sort the cards, and for each one say the reason in one sentence, using the words calculation, record or search. Discuss any card that could go in either pile or in both. Then pick one card and list the field headings or columns you would use.
Practice
1
A class records the weight in kilograms of vegetables picked each day for a month, and wants totals and the average per day. Which tool is better, and why? Name two things you would put in it.
A spreadsheet, because the job is mainly numbers and calculations. Put the days and the weights in cells, and use SUM for the total and AVERAGE for the average per day.
2
Pet sitters keep records of their name, the pets they can look after and their free days. Families want to find a sitter who is free on Saturday. Which tool is better? Give the field headings and one criteria.
A database, because there are many records and families need to search them. Fields: SitterID (key), Name, Pets, FreeDay. Criteria: FreeDay = Saturday.
3
Complete the table: choose spreadsheet or database and give a reason.
Situation
Spreadsheet or database?
Reason
Profit of a lemonade stall
?
?
Register of borrowed sports kit
?
?
Total quiz points of four teams
?
?
Lemonade stall profit: spreadsheet, because it needs calculations. Borrowed sports kit: database, because it stores records that must be searched (for example who has not returned an item). Quiz points: spreadsheet, because it adds numbers.
4
In the trip sheet, the cost of food changes from 55 to 65. What is the new total? Which cell changes, and why?
A
B
1
Item
Cost
2
Fuel
40
3
Food
65
4
Tickets
30
5
Total
?
The total becomes 40 + 65 + 30 = 135. Cell B5 changes, because its formula =SUM(B2:B4) uses the cells above it and recalculates when one of them changes.