Cover lesson · Computer Science · Year 9 · Intermediate
Structuring a database
DCF: Data and computational thinking → Problem-solving and modelling
Name: Class: Date:
Cover teacher — you need no subject knowledge and no computers
Print: this sheet one per pupil, and the stimulus sheet "The school device loan list" one per pair. The stimulus carries the messy data and the list of data types, so pupils do not need to remember any.
Run: 5 min read the messy sheet · 10 min list the four problems it causes · 20 min design the tables, fields, data types and keys · 10 min draw the relationships and write two sample rows · 5 min answer the three "can your design answer this?" questions.
Collect: this sheet. The worked example sheet gives a complete correct design, including every data type, so you can mark against it directly.
What you are doing
The school keeps every Chromebook loan in one enormous spreadsheet, and it has stopped working. You are going to redesign it as a proper relational database: split the one sheet into separate tables, give every field a sensible name and data type, choose a primary key for each table, and link the tables together so no fact is ever stored twice.
Step by step
- 5 min — Read the messy sheet and the office's three complaints on the stimulus sheet.
- 10 min — List four problems the single-sheet design causes. Quote a row number for each.
- 20 min — Design your tables. Minimum three. Every field needs a name and a data type, and every table needs a primary key.
- 10 min — Draw the relationships between your tables, then write two real rows of data taken from the messy sheet.
- 5 min — Answer the three questions about what your design can and cannot do.
What goes wrong with one big sheet
Four problems, each with a row number from the stimulus sheet as evidence.
| No. | The problem | Evidence (row) |
|---|
| 1 | | |
| 2 | | |
| 3 | | |
| 4 | | |
Design rules you must follow
- 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 table needs a primary key: one field whose value is different in every row and never changes. A pupil's name is not a primary key.
- A fact is stored in exactly one place. If a model's price appears in 400 rows, it is in the wrong table.
- Choose the narrowest data type that fits. Dates go in a DATE field, never in text — you cannot sort or subtract text. Money goes in DECIMAL, not text and not a whole number.
- A table refers to another table with a foreign key: a field holding the other table's primary key.
Your table designs
Table 1 — name: _______________________
| Field name | Data type | Key? (PK / FK / none) and why |
|---|
| | |
| | |
| | |
| | |
| | |
Table 2 — name: _______________________
| Field name | Data type | Key? (PK / FK / none) and why |
|---|
| | |
| | |
| | |
| | |
| | |
Table 3 — name: _______________________
| Field name | Data type | Key? (PK / FK / none) and why |
|---|
| | |
| | |
| | |
| | |
| | |
| | |
| | |
Your relationship diagram
Draw a box for each table, then a line between boxes that are linked. Write 1 at the "one" end and N at the "many" end, and name the relationship in words, for example "one pupil has many loans".
Two real rows
Take rows 1 and 4 from the messy sheet and write them out as they would appear in your tables. Note how much of the text disappears once the repeated facts live somewhere else.
Can your design answer this?
| Question the office will ask | Which tables and fields would you need? |
|---|
| 1. Which devices are overdue right now? | |
| 2. What is the total replacement value of everything currently out on loan? | |
| 3. Seren Bowen has moved from 9C to 9D. How many places in your database have to change? | |
Success criteria
- 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.
If you finish early
- Write the rule that stops the same device being lent to two pupils at once. Which two rows of the messy sheet break it?
- The office wants to record damage. Design a fourth table for repairs, and decide whether it links to the device or to the loan — and say why that choice matters.
- Explain in three sentences why storing a pupil's age would be a design mistake even though the office keeps asking for it.