Worked example · Computer Science · Year 9
DCF: Data and computational thinking → Problem-solving and modelling
A complete, correct redesign of the device loan sheet: four tables, every field typed, keys chosen and relationships named. Show it after pupils have drafted their own tables. A pupil design with three tables (no separate models table) is still a strong answer — the fourth table is what fixes the office's third complaint.
| No. | The problem | Evidence |
|---|---|---|
| 1 | The same fact is stored many times. "Acer Chromebook 314" and "£215.00" appear in five of the ten rows. Change the price and you have to find every copy. | Rows 1, 2, 4, 5 and 10. The office's third complaint. |
| 2 | Copies disagree with each other. The same pupil is 9C in one row and 9D in another, so the sheet no longer has a single answer to "what form is she in?" | Seren Bowen is 9C in row 4 and 9D in row 7. |
| 3 | Typing the same thing twice invites typing errors. A misspelled model name will not appear in a search, so the office undercounts its own kit. | Row 5 says "Acer Chromebok 314". Row 3 has the same pupil as row 1 typed as "elin prys". |
| 4 | Nothing identifies a row, so nothing can be checked. There is no way to state the rule "a device can only be on one open loan at a time", so two overlapping loans of the same device sit there unnoticed. | Rows 4 and 5 both have CB-0042 out from 02/10 to 16/10, to two different pupils. Also the dates are in three different formats (rows 1, 3 and 4), so they cannot be sorted. |
Each problem is named and pinned to a row, which is what turns "the sheet is messy" into a design brief.
| Field name | Data type | Key and why |
|---|---|---|
| pupil_id | INTEGER | PK — a number the school assigns. Unique, and it never changes when a pupil changes name or form. |
| first_name | VARCHAR(30) | none |
| surname | VARCHAR(40) | none — long enough for a double-barrelled surname such as Morgan-Hughes. |
| form_group | VARCHAR(4) | none — stored once, so "9C or 9D?" can only have one answer. Four characters covers 10A and 11B. |
| date_of_birth | DATE | none — a real date, so the office can sort by it. Age is not stored: it would be wrong within a year. |
| Field name | Data type | Key and why |
|---|---|---|
| model_id | INTEGER | PK |
| model_name | VARCHAR(40) | none — typed once ever, so "Chromebok" cannot happen again. |
| replacement_cost | DECIMAL(7,2) | none — exact money to the penny. Up to £99,999.99, which is ample. Never a float, because 189.99 must stay 189.99. |
| Field name | Data type | Key and why |
|---|---|---|
| device_id | INTEGER | PK |
| asset_tag | CHAR(7) | unique — always exactly seven characters, as in "CB-0042", so CHAR is right rather than VARCHAR. |
| model_id | INTEGER | FK → models.model_id. This is the one line that removes every repeated model name and price. |
| purchase_date | DATE | none — lets the office work out how old a device is by subtraction, which text could not do. |
| Field name | Data type | Key and why |
|---|---|---|
| loan_id | INTEGER | PK — every loan now has an identity, which the spreadsheet row number never really was. |
| pupil_id | INTEGER | FK → pupils.pupil_id |
| device_id | INTEGER | FK → devices.device_id |
| issued_on | DATE | none |
| due_back | DATE | none — DATE, so "is it overdue?" is a comparison rather than a guess. |
| returned_on | DATE, may be NULL | none — NULL means "still out". A blank cell in a text column meant nothing at all. |
| charger_included | BOOLEAN | none — replaces "yes", "y", "Y" and "no" with two possible values. |
One table per kind of thing, a primary key that never changes in each, and the narrowest type for every field — first, second and third success criteria. Note DATE for all five dates and DECIMAL for the money.
In words: one pupil has many loans (Elin Prys appears twice in the messy sheet, which is now two rows in loans pointing at one row in pupils). One device has many loans over its life. One model has many devices — the school owns several Acer 314s but stores the name and the price once.
Pupils and devices are really in a many-to-many relationship: a pupil borrows many devices and a device is borrowed by many pupils. You cannot store that in either table, which is exactly why the loans table exists in the middle.
Both ends of every relationship are labelled and named in words, and the many-to-many is resolved properly — fourth success criterion.
The two loan rows are now 7 short fields each instead of 11 long ones. "Acer Chromebook 314" and "215.00" appear once in the whole database rather than once per loan, and "CB-0042" appears once instead of in every row that mentions it.
Real data from the stimulus, transferred correctly, with the dates rewritten in a single sortable format.
1. Which devices are overdue right now? Yes. Take every row of loans where returned_on is NULL and due_back is earlier than today, then join to devices for the asset tag and to pupils for the name. In the ten sample rows that is rows 3, 7 and 9 — CB-0108, CB-0017 and CB-0091. This only works because due_back is a DATE; with "15th Oct" in a text field the comparison is impossible.
2. Total replacement value of everything out on loan? Yes. Same set of unreturned loans, joined through devices to models, then add up replacement_cost. For those three: £189.99 (HP Chromebook 11 G9) + £215.00 (Acer Chromebook 314) + £232.50 (Lenovo 300e Chromebook) = £637.49. Because the price lives in one place, this total is right even if a price was updated yesterday.
3. Seren Bowen moves from 9C to 9D. One change: the form_group field in her single row of pupils. In the old sheet it was every row she appeared in, and the office's complaint was exactly that — which is how the sheet ended up saying 9C and 9D at the same time.
The design is tested against the questions it exists to answer, with the actual figures worked out — fifth success criterion.
The rule is: a device may have at most one loan where returned_on is NULL. Rows 4 and 5 of the messy sheet break it — CB-0042 is issued to two different pupils on 02/10/2025 — and so do rows 1 and 5 if you look at the dates, because row 1's loan of CB-0042 was not returned until 26/09/2025, which is fine, but nothing in a spreadsheet was checking. A database can refuse the second row at the moment somebody types it, which is the real advantage: the structure prevents the mistake instead of recording it.
A repairs table should link to the device, not the loan. A crack in a screen belongs to the device for the rest of its life, and you want the repair history even when the damage was spotted in the cupboard rather than during a loan. You could store the loan_id as well, as an optional extra field, to record who had it when the damage appeared.
Storing a pupil's age would be a mistake because it is derived data: it can be calculated from date_of_birth and today's date whenever it is needed. Stored, it is correct for a year and wrong from then on, and it would have to be updated for every pupil every birthday. Store the fact that does not change, and calculate the one that does.
Goes beyond the structure to the constraints, and gives a reason for the design choice rather than a preference.