← IGCSE Computer Science Paper 2

Defining a single-table database from storage requirements

The plan for one table: list each field the scenario needs, give it a data type and any validation, and choose a primary key so the table can hold every record.

Part of: IGCSE Computer Science 0478 — 9 Databases — Module 18, ACARA Digital Technologies Years 7–8 — Structured Data and Queries — Module 22, QCE Digital Solutions Unit 2 — Data-Driven Problems and Solution Requirements — Module 57, WA Computer Science ATAR Year 11 — Data Management — Module 41

Understanding Defining a single-table database from storage requirements in IGCSE Computer Science Paper 2

What it does: A yes/no value is stored as the Boolean data type, so the field Vaccinated is given the type Boolean. A number with a decimal part is stored as Real (floating point). Integer would lose the decimal places. A primary key must be unique for each record. Surnames can be shared by many students. Dates should use the date/time type so they can be checked and sorted correctly. A range check tests that a value lies between a lowest and highest limit. A presence check only tests that data was entered.

Common mistakes: The table lists only three fields: PetID, Name and Born. The header should say (3 fields) so it matches the field lines. A Boolean can only hold two values (True/False), so it cannot give each book a unique ID. BookID should be Integer (or Text).

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Defining a single-table database from storage requirements to a class →

This module's key terms are also in a printable crossword (Modules 500–522) and word search (Modules 500–538).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
A library wants to store each book's title, author and whether it is on loan. Which field list is the best start for the table?
BookID, Title, Author, OnLoanBooks, Library, Shelf, Reading
Matches
PetName | Integer | Primary key
↓
A pet's name is made of letters, so it should be Text, not Integer. It is also a poor primary key because two pets can share the same name. A field such as PetID would be better.
Applying
Meanings
Term
table structure
Meaning
The plan for one table: list each field the scenario needs, give it a data type and any validation, and choose a primary key so the table can hold every record.
Practice
Table: Book
Testing
Gaps
PetID | | Primary key
Check Answer
Spots
1Table: Pet (4 fields)
2PetID | Integer | Primary key

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Flamingo' badge — Balanced Coder, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. A library wants to store each book's title, author and whether it is on loan. Which field list is the best start for the table?
A. BookID, Title, Author, OnLoan ✓
B. Books, Library, Shelf, Reading
Read the scenario and turn each piece of data to be stored into one field. BookID is added as a unique key.
2. A pet shop table has the field Price (e.g. 12.50). Which data type fits best?
A. Boolean
B. Real (floating point) ✓
Prices have decimal places, so a real (floating point) number is suitable. Boolean only holds True or False.
3. A school table stores whether a student has paid for a trip (yes or no). Which data type should the field Paid use?
A. Boolean ✓
B. Integer
A yes/no value is Boolean. Integer would allow any whole number, which is not needed.
4. Which field is the best primary key for a table of school students?
A. StudentID, because every student has a different one ✓
B. LastName, because every student has one
A primary key must be unique for each record. Surnames can be shared by many students.
5. A pet shop table has the field DateOfBirth. Which data type should it have?
A. Text
B. Date/time ✓
Dates should use the date/time type so they can be checked and sorted correctly.

Match the Pairs

BookID | Text | Primary key → A field called BookID holds text and is the primary key, so every book in the library has its own unique ID
DateDue | Date/Time | Presence check → A field called DateDue stores a date and a presence check makes sure it is never left empty
A pet shop table needs a field to store whether each animal has been vaccinated, which only has two possible values → Vaccinated | Boolean
A school table stores each student's height in metres, such as 1.62, so the field needs a data type that allows decimal places → Height | Real
PetName | Integer | Primary key → A pet's name is made of letters, so it should be Text, not Integer. It is also a poor primary key because two pets can share the same name. A field such as PetID would be better.
Fields: Title, Author (no primary key) → The table structure has no primary key. A field such as BookID with a unique value for each record should be added and marked as the primary key.

Key Term

table structure
The plan for one table: list each field the scenario needs, give it a data type and any validation, and choose a primary key so the table can hold every record.

Fill in the Blanks

1. Table: Pet PetID | Integer | Primary key Name | Text | Presence check
Hint: Type the data type for a whole-number ID code.
2. Table: Book Title | Text | Presence check Price | Real | Range check 0 to 100
Hint: Type the data type for a price that can have decimal places.
3. Table: Student StudentID | Text | Primary key Name | Text | Presence check
Hint: Type the two-word label for the field that uniquely identifies each record.
4. Table: Pet Vaccinated | Boolean | Yes or No PetID | Integer | Primary key
Hint: Type the data type for a field that only holds true/false or yes/no.
5. Table: School YearGroup | Integer | Range check 7 to 13 StudentID | Text | Primary key
Hint: Type the validation check that keeps a number between a lowest and highest value.

Spot the Error

Question 1
A Table: Pet (4 fields)
B PetID | Integer | Primary key
C Name | Text
D Born | Date
Line A — The table lists only three fields: PetID, Name and Born. The header should say (3 fields) so it matches the field lines.
Question 2
A Table: Book (3 fields)
B BookID | Boolean | Primary key
C Title | Text
D OnLoan | Boolean
Line B — A Boolean can only hold two values (True/False), so it cannot give each book a unique ID. BookID should be Integer (or Text).
Question 3
A Table: Student (4 fields)
B ID | Integer | Primary key
C Name | Text
D Year | Text | Range 7-13
E Paid | Boolean
Line D — A range check compares numbers, so the Year field must be Integer, not Text. The line should be Year | Integer | Range 7-13.
Question 4
A Table: Stock (4 fields)
B ItemCode | Text | Primary key
C Price | Integer | Range 0-500
D Quantity | Integer
E Name | Text
Line C — Prices have pence (for example 4.99), so they need a Real (floating point) type, not Integer. It should be Price | Real | Range 0-500.
Question 5
A Need: ID, name, age
B ID | Integer | Primary key
C Name | Text
D Joined | Date
Line A — The scenario line should list the same three fields the table stores: ID, name and the date joined. The Joined field would otherwise have no requirement behind it and the table would be wrong.

Student with a class code? Join your class →