🎯 Syllabus & Goals 3 min
Cambridge 9.1 · Single-table databases Paper 2 · Algorithms, Programming and Logic
By the end of this lesson you can:
- State what a database, a table, a record and a field are.
- Count the records and fields in a given table, and name fields correctly.
- Suggest suitable validation checks for the fields of a table.
Textbook: Chapter 9, §9.1.1 (pp. 339–342)
Recap / Warm-Up 5 min
In Unit 7 you met validation: automatic checks that data is sensible before it is accepted. Databases use exactly the same checks, so keep them in mind today.
Quick starter
A form asks for your age. Name the validation check that rejects an age of 250.
Reveal the answer
A range check — it only accepts values between a lower and an upper limit (for example 0 to 120).
🧠 Key Concept 14 min
1 · What is a database?
A database is an organised store of data. It is built so people can pull out exactly the information they need. It can hold text, numbers, dates and even pictures.
At IGCSE you only study single-table databases: every item of data sits in one table. Databases with many linked tables (relational databases) come later, at A Level.
2 · Why use a database?
- A change is made once, so the data stays consistent.
- Everyone uses the same copy of the data, so nobody works from an old version.
- Data can be searched and sorted quickly to answer questions.
3 · Tables, records and fields
Data is stored in a table. A table is about one kind of thing, such as pets at a vet's surgery. Each record holds everything about one item — one pet. Each field holds one piece of data about that item — for example, its name.
4 · Naming tables and fields
- Give a table a meaningful name, usually in capitals:
PET,BOOK,APPOINTMENT. - Give each field a meaningful name written as one word with no spaces:
OwnerSurname,DateOfBirth.
Here is the full table you will use in this lesson and the next:
| PetID | PetName | Species | OwnerSurname | DateOfBirth | Sex | WeightKg | Vaccinated |
|---|---|---|---|---|---|---|---|
| P101 | Biscuit | Dog | Taylor | 2019-04-12 | M | 12.5 | TRUE |
| P102 | Luna | Cat | Brown | 2021-09-30 | F | 4.2 | TRUE |
| P103 | Pepper | Rabbit | Taylor | 2022-02-14 | F | 1.8 | FALSE |
| P104 | Max | Dog | Novak | 2016-07-03 | M | 30.1 | TRUE |
| P105 | Luna | Dog | Garcia | 2020-11-22 | F | 8.7 | FALSE |
| P106 | Kiwi | Parrot | Kim | 2018-05-09 | M | 0.4 | TRUE |
5 · Validation inside a database
Validation stops unreasonable data getting into a table. Some checks come free with the database software. Others must be set up by the database developer before anyone uses the table.
Provided automatically
A field set up as a date will reject 31/02/2023 — no such date exists. A number field rejects letters.
Set up by the developer
A range check so WeightKg is above 0 and below 100. A format check so PetID is P then 3 digits.
WeightKg. 250 kg fails the rule, so the record is not saved and the user sees an error.Worked Example 12 min
(a) Reading a table
Use the PET table above. State the number of records, the number of fields, and the data held in field Species of record P104.
- Count the rows below the header row: P101 to P106 → 6 records.The header row holds field names, not data about a pet, so it is not a record.
- Count the columns: PetID, PetName, Species, OwnerSurname, DateOfBirth, Sex, WeightKg, Vaccinated → 8 fields.Every record has the same fields, so counting the headings is enough.
- Find row P104, then move across to the Species column → Dog.One record + one field pins down exactly one item of data.
Answer: 6 records, 8 fields, Dog. Marks: one for each correct value.
(b) Designing fields for a new table
BOOKING, to store each ticket booking: who booked, which film, the date and time of the show, the seat and whether the booking is paid.- List each separate item of data in the scenario: customer name, film, show date, show time, seat, paid?Each separate item of data becomes one field.
- Split items that people search on separately: customer name →
FirstNameandFamilyName.You can then sort or search by family name alone. - Write each name as one word with no spaces:
FilmTitle,ShowDate,ShowTime,SeatNumber,Paid.Field names cannot contain spaces, and a clear name shows what the field holds. - Add a field that is different for every booking:
BookingRef.Two customers can share a name, so we need one field that is never repeated. That is the primary key (next lesson).
(c) Choosing validation checks
| Field (PET) | Check | Rule |
|---|---|---|
PetID | Presence + format | Must be entered; letter P followed by 3 digits |
Sex | Length + lookup | Exactly 1 character; only M or F |
WeightKg | Range | Greater than 0 and less than 100 |
DateOfBirth | Type (automatic) + range | A real date; not later than today |
Try It Yourself 12 min
Goal: In the PET table, state the data held in field OwnerSurname for record P105, and name the field that holds 0.4.
Goal: A doctor's surgery needs a table, APPOINTMENT. Write down six suitable field names. Follow the naming rule.
Goal: For a BOOK table in a school library, choose three fields. For each one, name a different validation check and give its exact rule.
Hint
Think about a field with a fixed length (an ISBN has 13 digits), a field with limits (number of pages) and a field that must never be blank.
📝 Exam Practice 10 min
Define the terms record and field.
Mark scheme
- Record: a collection of fields about one item / person / event // a row in a table (1).
- Field: one item of data about the item // a column in a table (1).
A table, PET, is shown in this lesson. State the number of fields and the number of records in the table.
Mark scheme
- Fields: 8 (1).
- Records: 6 (1).
Give two reasons why a vet's surgery should store its data in one database rather than in separate lists.
Mark scheme
- Changes/additions only need to be made once (1).
- Data is consistent // everyone uses the same data (1).
- Accept: data can be searched/sorted quickly to find information.
Describe two validation checks that could be used on the PetID and WeightKg fields. Give the rule for each.
Mark scheme
- PetID: format check (1) … must be the letter P followed by three digits (1).
- Accept for PetID: length check … exactly 4 characters; presence check … must not be left blank.
- WeightKg: range check (1) … value must be greater than 0 and less than a sensible maximum, e.g. 100 (1).
- Accept for WeightKg: type check … must be a number.
Recap & Key Terms 3 min
A database stores data in a structured way so information can be extracted. At IGCSE it has one table. Each row is a record about one item; each column is a field. Validation keeps unreasonable data out.
- Database
- A persistent, structured collection of data that allows people to extract information in a way that meets their needs.
- Single-table database
- A database that contains only one table.
- Table
- A collection of related records.
- Record
- A collection of fields that describe one item (one row).
- Field
- One item of data stored about every record (one column).
- Validation
- An automatic check that data entered is reasonable before it is stored.
Homework 1 min
Task (≤ 15 min): A sports centre keeps a single table, COURT, of court bookings. Each booking stores the member's name, the court number (1 to 6), the date, the start time and whether a coach is needed. (a) Write suitable field names. (b) Give a validation check, with its rule, for the court number.
Model answer
- (a) e.g.
BookingID,MemberName(orFirstName/FamilyName),CourtNumber,BookingDate,StartTime,CoachNeeded— one word, no spaces. - (b) Range check:
CourtNumbermust be a whole number from 1 to 6 inclusive.