The shape of the lesson
By the end of the lesson, learners can
- Tables, fields and records: In the lab, click a column heading to highlight a field and click a row to highlight a record.
- Choosing data types: Pick the data type that matches what the field will hold and what you will do with it.
- Defining a table and validation: To define a single-table database from given data, list each field with a sensible name, a data type and, where useful, a validation check.
- Field: a column, one item of data (e.g. Weight). Record: a row, all the data about one item.
- Counting: do not count the row of field names as a record.
- Data types: text/alphanumeric, character, Boolean, integer, real, date/time. Never write "number".
- Justify: whole number = integer; decimal = real; yes/no = Boolean; leading zero or symbols = text.
One picture every idea returns to
A database is a structured collection of data that allows people to extract information. In a single-table database all the data is in one table. Each field (column) holds one item of data, such as Name or Weight. Each record (row) holds all the data about one person or thing. Every field is given a data type: text/alphanumeric, character, Boolean, integer, real or date/time. Validation rules check that data entered into a field is sensible.
Forty-five minutes, phase by phase
| Time | Phase | What happens in the room | Grouping |
|---|---|---|---|
| 0 to 5 min | Hook: predict | Open with the question in the title, Data in rows and columns.. A vet surgery, a school library and a games shop all keep the same kind of data again and again: one set of details for each pet, book or game. Put the figure on the board: it is the one picture every idea in the lesson returns to. | Whole class, then pairs |
| 5 to 13 min | Teach and check: Tables, fields and records | In the lab, click a column heading to highlight a field and click a row to highlight a record. Project tab 1 of the lab (Table, fields and records) and let a student drive it while the class predicts. | Whole class, then pairs |
| 13 to 21 min | Teach and check: Choosing data types | Pick the data type that matches what the field will hold and what you will do with it. Model the worked example Phone numbers on the board, one line at a time. Your turn: Suggest data types for: (a) a pet's weight in kg, (b) whether a pet is microchipped, (c) a pet's ID such as P01. | Pairs |
| 21 to 30 min | Teach and check: Defining a table and validation | To define a single-table database from given data, list each field with a sensible name, a data type and, where useful, a validation check. | Connect-Extend-Challenge |
| 30 to 36 min | Check | The four Stage 1 questions from the student lesson, answered on mini whiteboards or by show of fingers. Re-teach the one most get wrong. | Individual, then whole class |
| 36 to 43 min | Plenary | The exam-style questions from Stage 2, one at a time: thirty seconds alone, then a random call. The feedback lines in the key give the examiner's wording. | Individual, random call |
| 43 to 45 min | Exit and homework | Set the worksheet below (6 questions, 16 marks). Learners self-assess against the objectives before they leave. | Individual |
Protect the parts that carry the learning
The sequence carries about fifty minutes of material, so the lab is driven by one student while the class predicts rather than explored freely. Times are a guide.
Protected: the check at 30 minutes. It is the only individual read on whether the key idea landed, and it is not cut.
Cut first if the build overruns: the last check in Defining a table and validation, never the check or the plenary.
In a 60 minute block: run worksheet questions 1 to 3 in class as a Rally Coach set and model both the hardest worked example and its check live.
Connect-Extend-Challenge
After the new idea, each student writes three lines: how it connects to what they knew, how it extends their thinking, and what still challenges them. Pairs compare, then the challenges become the plenary.
The full structure, with its variations and the other twelve, is on the cooperative-learning page.
What to head off, and how
| Trap learners fall into | Teaching move that pre-empts it |
|---|---|
| Do not mix the two words up. | A field is a column (one kind of data); a record is a row (one item). Swapping them loses the mark. |
| "Number" and "string" are not accepted as answers. | Give the exact type: integer or real, and text/alphanumeric. |
| A range check on Weight would still accept 21.5 when the dog really weighs 12.5. | Validation checks that data is sensible, not that it is accurate. |
Single-table databases: fields, records and data types: worksheet
6 questions, 16 marks. Written in the style of the papers; no past paper question is reproduced.
State what is meant by a field and by a record in a database table.
A cinema stores data in a table FILMS.
| FilmID | Title | Rating | Minutes | Is3D |
|---|---|---|---|---|
| F101 | Ocean Deep | U | 96 | TRUE |
| F102 | Night Train | 15 | 118 | FALSE |
| F103 | Robo Pals | PG | 102 | TRUE |
State the number of fields and the number of records in the table.
For the FILMS table above, suggest the most suitable data type for each field: Title, Rating, Minutes, Is3D.
A student suggests storing a postcode such as B12 0QJ as an integer. Explain why this is not suitable.
A shop sells T-shirts in sizes S, M or L, priced from 5.00 to 30.00. Define two fields, Size and Price, for its database table: give a data type and a suitable validation check for each.
The FILMS table has a field Minutes with a range check of 60 to 240. Explain why the value 96 could still be wrong after passing the check.
Answer key and marking notes
- Field: a column in the table that stores one item of data (one attribute) for every record.
- Record: a row in the table that stores all the data about one item, person or thing.
- 5 fields (FilmID, Title, Rating, Minutes, Is3D).
- 3 records (one for each film; the row of field names is not a record).
- Title: text/alphanumeric.
- Rating: text/alphanumeric (values such as PG and 15 can be two characters).
- Minutes: integer (a whole number of minutes).
- Is3D: Boolean (TRUE or FALSE).
- A postcode contains letters and a space as well as digits.
- So it must be stored as text/alphanumeric; it is never used in calculations.
- Size: character (one letter).
- Size validation: lookup / format check that the value is S, M or L.
- Price: real.
- Price validation: range check that it is between 5.00 and 30.00 inclusive.
- Validation only checks that the data is reasonable (inside the allowed range).
- The real film might be 69 minutes and 96 was typed by mistake; validation cannot check accuracy against the real value.
6 multiple-choice questions on this lesson
From the topic question bank. Assign them in the app for a marked score in your gradebook, or run the topic as a class game.
In a database table, what is a field?
- Aa column that stores one item of data for every record
- Ba row that stores all the data about one item
- Cone value stored where a row meets a column
- Dthe name given to the whole collection of data
The table FILMS stores details of films that can be rented. How many fields and how many records does the table have?
- A6 fields and 7 records
- B7 fields and 6 records
- C6 fields and 8 records
- D7 fields and 7 records
The table FILMS stores details of films that can be rented. The Minutes field stores each film's running time in whole minutes. Which data type is most suitable for Minutes?
- Ainteger
- Breal
- Ctext/alphanumeric
- Ddate/time
Every FilmID in the FILMS table must be the letter F followed by exactly two digits, such as F07. Which validation check is most suitable for this field?
- Aformat check
- Brange check
- Ctype check
- Dlookup check
A shop designs a single-table database for its products. Which choice of data type is not suitable?
- ABarcode, with values such as 0045123, stored as integer
- BPrice, with values such as 12.99, stored as real
- CInStock, with values TRUE or FALSE, stored as Boolean
- DDateAdded, with values such as 03/05/2026, stored as date/time
The Minutes field has a range check that accepts values from 60 to 240. A film actually lasts 121 minutes, but the value 112 is typed in by mistake. What happens?
- A112 is accepted, because validation only checks that data is sensible, not that it is correct
- B112 is rejected, because the range check compares it with the real running time
- C112 is rejected, because a range check needs the digits in ascending order
- D112 is accepted, but only after the range check asks for it to be typed twice
Quiz key
A field is a column, such as Title or Rental, holding one item of data for every record. A row holding all the data about one item is a record, not a field. A single value where a row meets a column is just one data item in that field. The name of the whole collection is the table name (for example FILMS).
There are 6 columns (FilmID, Title, Genre, Minutes, Rental, Subtitled), so 6 fields, and 7 rows of data, one per film, so 7 records. Swapping the numbers mixes up fields and records. Counting 8 records wrongly includes the row of field names, which is not a record.
Running times such as 95 and 118 are whole numbers, so integer is the right type. Real is for numbers with a fractional part, such as the Rental values. Text would stop the values being compared as numbers in a query such as Minutes > 100. Date/time is for calendar dates and clock times, not a length of time counted in minutes.
A format check tests that data matches a pattern, here one letter F then two digits. A range check tests that a number lies between limits, which does not apply to a code such as F07. A type check would only test that the data is text, so it would accept G7X. A lookup check compares data with a fixed list of allowed values, but the pattern allows many IDs that are not yet in any list.
A barcode such as 0045123 begins with zeros, which an integer would drop (storing 45123), and no arithmetic is ever done on a barcode, so it should be text/alphanumeric. Price has a fractional part, so real is correct. InStock has only two possible values, so Boolean is correct. DateAdded is a date, so date/time is correct.
112 lies between 60 and 240, so the range check accepts it: validation checks that data is reasonable, but cannot know the true value. A range check does not compare data with the real running time; it only tests the limits. The order of the digits plays no part in a range check. Typing the data twice is a double entry check, which is verification, not part of a range check.
What to project
The student lesson embeds these tabs. Open them full screen on the projector; every slider has a drag handle on the stage.
Slides for this lesson
A projectable deck for every pack is on the way. Until then, present the student lesson full screen: its sections, figure and lab tabs follow the same order as the plan above.