S2 ICT | Databases and Table Queries lesson-prep guide (for tutors) | records, fields, primary keys, AND and OR

2026-11-13

Tutor lesson-prep guides · Secondary ICT

Students can open a spreadsheet and type, but ask them to find the students who scored 80 or above in Maths and were absent no more than twice out of 120 names, and most will scan row by row. This lesson turns finding data into three statements they can write down: fields, conditions, primary key.

Objectives — By the end students can: (1) explain the difference between a record and a field, and work out that eight classes of fifteen give 120 records, which with six fields is 720 cells; (2) state that a primary key must be unique, never blank and never changed, and show it with student IDs 2026001 to 2026120; (3) use 8 girls, 6 students at 80 or above and 4 who are both, to get 4 rows for AND (about 26.7%) and 10 rows for OR (about 66.7%), and explain why OR is not 8 plus 6.

01 Prerequisites (3-minute check)

If any of these fails, fix it before the lesson starts.

Skill	Quick check	Pass mark
Sets	In a list of 15 students, 8 are girls. How many are boys?	7, because 15 minus 8
Percentages	What is 4 out of 15 as a percentage?	About 26.7%, written with a per cent sign
Reading tables	What do we call the top row of a table?	The field names, one per column

02 Lesson flow (45 minutes)

Time	Stage	Tutor does	Student does
0-5 min	Estimate first	Write “120 students, find those at 80 or above in Maths with at most 2 absences” and ask how they would do it	Most say “check one by one” and guess several minutes
5-13 min	Records and fields	Write a 15-row sample list and count records, fields and cells together	Can state 15 records, 6 fields, 90 cells
13-22 min	Primary key	Show two students with the same name, then write IDs 2026001 to 2026120	Can state the three rules and reject the name field
22-33 min	Filter conditions	Move from one condition to two, drawing a Venn diagram for AND and OR	Can compute 4 and 10 and name the overlap
33-40 min	Four question types	One question each on records, keys, AND and OR, timed	All four written with steps, not just answers
40-45 min	Exit check	Collect three answers, mark them, name the act to reteach	Hand in three answers

03 Teaching script (read it out)

Scene 1: Act 1: a record is a row, a field is a column

“A table has rows and columns. One row is one record; one column is one field. Eight classes of fifteen students gives eight times fifteen, so 120 records. We have six fields: student ID, name, sex, class, maths score and absences. How many cells? 120 times 6, which is 720. Why count cells? Because any cell can be wrong, and you need to know which cell holds the thing you are looking for before writing a query. Remember three facts: records equal people, fields equal kinds of data, cells equal the product.”

A record is a row, a field is a column, and cells equal records times fields.

Scene 2: Act 2: a primary key is an identity card

“Our list has two students called Chan Ka Yan in the same class and the same year. If you search for that name you get two rows and cannot tell them apart. So every table needs a primary key: a field that never repeats, is never blank and never changes. Our IDs run from 2026001 to 2026120, 120 numbers for 120 students. Can the name be the key? No, because names collide and people change them. Can the class be the key? No, because thirty students share it. The commonest misunderstanding is that a key must be important; it only has to be unique. In practice keys are meaningless numbers, precisely because meaning would make them change.”

A key only has to be unique, never blank and never changing.

Scene 3: Act 3: AND needs both sides true, OR needs one

“In the 15-row sample, 8 are girls and 6 scored 80 or above in Maths. Search for girls AND 80 or above and you get 4 rows, which is 4 out of 15, about 26.7%. Search for girls OR 80 or above and you will want to answer 8 plus 6, which is 14, but the answer is 10. Why? Because 4 girls also scored high, so adding counted them twice. So OR is 8 plus 6 minus 4, which is 10, about 66.7%. Say it as a rule: AND narrows, OR widens, and OR must subtract the overlap.”

AND needs both, OR adds and then subtracts the overlap.

04 Question types and worked steps

Type	Example	Steps	Answer
Records and fields	A table has 40 records and 5 fields. How many cells?	(1) Say a record is a row (2) a field is a column (3) multiply (4) give the unit “cells”	40 times 5 equals 200 cells
Primary key	Which field suits a student table as its primary key: name, class, student ID, sex?	(1) Check uniqueness (2) check blanks (3) check whether it changes (4) eliminate	The student ID; the other three repeat or change
AND query	Of 15 students, 8 are girls, 6 scored 80 or above and 4 are both. How many rows does girls AND 80 or above return?	(1) Draw two circles (2) take the intersection (3) count (4) convert to a percentage	4 rows, 4 out of 15, about 26.7%
OR query	With the same data, how many rows does girls OR 80 or above return?	(1) Add (2) subtract the intersection (3) check it is under the total (4) convert to a percentage	8 plus 6 minus 4 equals 10 rows, about 66.7%

Sanity check (teach this): Sanity checks: records times fields must equal cells, 120 times 6 is 720, so 126 means the two were added; an AND answer can never exceed the smaller of the two conditions (4 is below 6 and 8, which is fine); OR must exceed either condition alone but never the total (10 is under 15, fine); an OR answer of 14 means the overlap of 4 was not subtracted. Percentages must not add up past 100%.

05 Independent practice (3 questions)

Question	Answer	What to watch
A table has 120 records and 6 fields. How many cells?	120 times 6 equals 720 cells	126 means records and fields were added
In a year of 120 students, 33 scored 80 or above with at most two absences. What percentage is that?	33 divided by 120 is 0.275, so 27.5%	Write the per cent sign to one decimal place
Give two reasons a name makes a poor primary key.	Names collide, and they change or may be left blank	Answering “because it is not important” misses the point

06 Common mistakes and what to say

Mistake	Student writes	Say this
Swapping records and fields	“120 fields and 6 records.”	“The other way round. A row is one record, one person; a column is one field, one kind of data. Count the headings: six, so six fields.”
Thinking the key must be important	“Maths score matters most, so it is the key.”	“A key is about being unique, not important. Scores tie, so they cannot be keys; IDs never repeat, so they can.”
Adding the two OR numbers	“8 plus 6 is 14 rows.”	“14 is already close to the total of 15, which should stop you. Subtract the overlap: 8 plus 6 minus 4 is 10.”
Writing vague conditions	“Find the girls who did well in maths.”	“A database does not know what well means. Name the field and the threshold: maths greater than or equal to 80, joined with AND.”

07 Homework (paper, section and question numbers)

Chapter practice: count the records, fields and cells of the 15-row sample — Databases chapter, practice question 1
Chapter practice: give the row counts for the AND and OR queries and explain the subtraction — Databases chapter, practice question 2
Extension: design a six-field table for your own class, name the primary key and justify it — Databases chapter, practice question 3

08 Exit check (last 5 minutes)

Question	Answer	Decision
Which one is horizontal, a record or a field?	A record is a horizontal row, a field is a vertical column	Swapping them means Act 1 again
What must a primary key be?	Unique, never blank, never changing	Missing uniqueness means Act 2 again
Girls 8, maths 80 or above 6, overlap 4: how many rows for OR?	10 rows, 8 plus 6 minus 4	Answering 14 means Act 3 again

09 Tutor FAQ

Should SQL be taught here?

Not in S2. Fields, conditions and primary keys are enough; SQL belongs to senior elective work. Drawing a table and a filter by hand already meets the objective.

Can a spreadsheet filter replace this?

Yes, and it works better. Students click the filter and see 4 rows versus 10 immediately. Ask them to count the remaining rows so the number is not taken on trust.

Is normalisation needed?

No. A primary key is the S2 ceiling; three normal forms would leave students reciting words without seeing the use.

Find a tutor — free Tutor Registration →

A TutorZone lesson-prep guide. Written by the TutorZone team; questions and lesson flow are original and are not taken from any school or publisher material.