Presentation: Structuring a Database

Slide 1 of 7 · Settling starter · Computer Science · Year 9

5 minutes

Structuring a database

The school keeps every Chromebook loan in one enormous spreadsheet, device_loans_2025.xlsx, and it has stopped working. It is up to 900 rows. Today you redesign it as a proper relational database.

While the register is called: write your name, class and date on your worksheet, then read the messy sheet and the office’s 3 complaints on the stimulus sheet.

Starter question: Seren Bowen appears in 2 rows. Which form group is she in?

DCF: Data and computational thinking → Problem-solving and modelling No computers needed · you design the structure, you do not fix the data

Supply teacher script (no subject knowledge needed)

Hand out: one worksheet per pupil and one stimulus sheet (The school device loan list) per pair. The stimulus carries the messy data and the list of data types, so pupils do not need to remember any. The pupils, asset tags and prices are invented.

Say aloud: “Write your name, class and date at the top of your worksheet. While I take the register, read the 10 rows of the messy spreadsheet on the stimulus sheet and the 3 complaints from the office underneath. Answer the starter question in rough.”

Starter answer: Nobody knows. She is 9C in row 4 and 9D in row 7. That is the office’s first complaint, and it happens because her form group is typed again on every row instead of being stored once.

Slide 2 of 7 · Today’s brief and key words · Computer Science · Year 9

2 minutes

Your brief: split 1 sheet into linked tables

You will list 4 problems the single sheet causes, split it into at least 3 tables, give every field a name and a data type, choose a primary key for each table, link the tables with foreign keys, write 2 real rows, and test your design against 3 questions the office asks.

Primary key (PK)

1 field whose value is different in every row and never changes. A pupil’s name is not a primary key.

Foreign key (FK)

A field holding another table’s primary key. It is how 1 table refers to another.

Data type

The narrowest type that fits: INTEGER, VARCHAR(n), CHAR(n), DATE, DECIMAL(p,s) or BOOLEAN. Dates in DATE, money in DECIMAL.

DCF: Data and computational thinking → Problem-solving and modelling A fact is stored in exactly 1 place

Slide 3 of 7 · Step 2: what goes wrong with 1 big sheet · Computer Science · Year 9

9 minutes

Step 2: list 4 problems, each with a row number as evidence

Use the 10 rows and the 3 complaints on the stimulus sheet. Each problem needs a row number from the messy sheet as evidence.

No.	The problem	Evidence (row)
1	The same fact is stored many times. “Acer Chromebook 314” and “£215.00” appear in 5 of the 10 rows. Change the price and you have to find every copy.	Rows 1, 2, 4, 5 and 10. The office’s third complaint.

Step 2 on your worksheet · 9 minutes Quote a row number for every problem

Supply teacher script (no subject knowledge needed)

Say aloud: “The first problem is on the board as a model. Find 3 more. Look for rows that disagree with each other, spelling mistakes, dates written in different ways, and the same device out with 2 people at once. Write the row number next to each one.”

Quick check: 2. Copies disagree with each other: Seren Bowen is 9C in row 4 and 9D in row 7. 3. Typing the same thing twice invites typing errors: row 5 says “Acer Chromebok 314”, so it will not appear in a search; row 3 has the same pupil as row 1 typed as “elin prys”; Rhys Morgan-Hughes loses his hyphen in row 5. 4. Nothing identifies a row, so nothing can be checked: rows 4 and 5 both have CB-0042 out from 02/10 to 16/10 to 2 different pupils. Also the dates are in 3 different formats (rows 1, 3 and 4), so they cannot be sorted, and “Charger?” has yes, y, Y and no. Any 4 of these with a row number is a full answer.

Slide 4 of 7 · Step 3: design your tables · Computer Science · Year 9

18 minutes

Step 3: design your tables, minimum 3

One table per kind of thing: a pupil is a kind of thing, a loan is a kind of thing, they are not the same thing. Every field needs a name and a data type. Every table needs a primary key. A table refers to another with a foreign key.

Table 1: pupils	Data type	Key? (PK / FK / none) and why
pupil_id	INTEGER	PK: a number the school assigns. Unique, and it never changes when a pupil changes name or form.
surname	VARCHAR(40)	none: long enough for a double-barrelled surname such as Morgan-Hughes.
date_of_birth	DATE	none: a real date, so the office can sort by it.

Step 3 on your worksheet · 18 minutes If a price appears in 400 rows, it is in the wrong table

Supply teacher script (no subject knowledge needed)

Say aloud: “Three rows of a pupils table are on the board as a model. Decide what kinds of thing are in the sheet, give each one a table, and fill in the field name, data type and key column for every field. Use only the 6 data types on the stimulus sheet. Dates go in DATE, never text. Money goes in DECIMAL. Write why each key is a key.”

Walk the room: a strong 3-table answer is pupils (pupil_id PK, first_name VARCHAR(30), surname VARCHAR(40), form_group VARCHAR(4), date_of_birth DATE), devices (device_id INTEGER PK, asset_tag CHAR(7), model name, replacement cost DECIMAL(7,2), purchase_date DATE) and loans (loan_id INTEGER PK, pupil_id FK, device_id FK, issued_on DATE, due_back DATE, returned_on DATE which may be NULL meaning still out, charger_included BOOLEAN). The worked example adds a 4th table, models (model_id PK, model_name VARCHAR(40), replacement_cost DECIMAL(7,2)), with devices holding model_id as a FK; that is what fixes the office’s third complaint. Age should not be stored: it would be wrong within a year.

Common errors: a name or form group used as a primary key; dates or money typed as VARCHAR; the model name and price copied into the loans table.

Slide 5 of 7 · Steps 4 and 5: relationships, 2 real rows, 3 questions · Computer Science · Year 9

12 minutes

Step 4: draw the relationships and write 2 rows. Step 5: can your design answer this?

Step 4 (8 min): draw a box for each table and a line between linked boxes. Write 1 at the “one” end and N at the “many” end, and name it in words, for example “one pupil has many loans”. Then write rows 1 and 4 of the messy sheet as they would appear in your tables.

Step 5 (4 min): for each of the 3 office questions, say which tables and fields you would need.

Steps 4 and 5 on your worksheet · 12 minutes Notice how much text disappears once repeated facts live elsewhere

Supply teacher script (no subject knowledge needed)

Say aloud: “8 minutes for the diagram and the 2 rows, then 4 minutes for the 3 questions. Row 1 is on the board as a model: the loan row is now 7 short fields instead of 11 long ones, and the dates are all written the same way. Do row 4, Seren Bowen, the same way. Her loan points at the same device row, CB-0042, because it is the same Chromebook.”

Quick check, questions: 1. Overdue now: loans where returned_on is NULL and due_back is earlier than today, joined to devices for the asset tag and pupils for the name; in the sample that is rows 3, 7 and 9, CB-0108, CB-0017 and CB-0091. Only works because due_back is a DATE. 2. Total value out: the same unreturned loans joined to the replacement cost, added up: £189.99 + £215.00 + £232.50 = £637.49. 3. Seren moves to 9D: 1 change, the form_group field in her single row of pupils.

Slide 6 of 7 · Self and partner check · Computer Science · Year 9

2 minutes

Check your design against the rules

Tick each success criterion on your sheet that your design meets. Then correct 1 thing: find any date or money field you typed as text and change its data type.

I split the data by the kind of thing it describes, so no fact about a pupil, a device or a model is repeated.
Every table has a primary key that is unique and will never need to change.
Every field has the narrowest sensible data type, with dates as DATE and money as DECIMAL.
My tables are joined by foreign keys, and my diagram names each relationship and marks its “one” and “many” ends.
I tested my design against real questions the office asks, and said which fields would answer each one.

2 minutes · self and partner check Finished early? Write the rule that stops a device being lent twice at once

Supply teacher script (no subject knowledge needed)

Say aloud: “Tick the success criteria you have met. Swap with your partner and check each other’s data types: every date must be DATE and the replacement cost must be DECIMAL. Correct 1 thing in a different colour.”

Extension answers: The rule is that a device may have at most 1 loan where returned_on is NULL; rows 4 and 5 of the messy sheet break it, CB-0042 issued to 2 different pupils on 02/10/2025. A repairs table should link to the device, not the loan, because a cracked screen belongs to the device for the rest of its life and damage may be spotted in the cupboard; loan_id could be an optional extra field. Storing age is a mistake because it is derived data: it can be calculated from date_of_birth whenever needed, is wrong within a year, and would have to be updated for every pupil every birthday.

Slide 7 of 7 · Plenary review · Computer Science · Year 9

2 minutes

Quick-check answers before handing in

Check your main answers before your worksheets are collected.

Question	Correct answer
Which 2 rows show the same device out with 2 pupils at once?	Rows 4 and 5: CB-0042, 02/10/2025 to 16/10/2025, Seren Bowen and Rhys Morgan Hughes.
What data type should the due date and the replacement cost have?	DATE and DECIMAL(7,2). Text cannot be sorted, subtracted or added up.
Seren Bowen moves from 9C to 9D. How many places change in a good design?	1: the form_group field in her single row of the pupils table.

Check your name and class are on your worksheet Leave stimulus sheets neat on the desk