Designing a Single-Table Database — Cambridge IGCSE CS (0478)

🎯 Syllabus & Goals 3 min

Cambridge 9.1 · Define a single-table database from given requirements Cambridge 9.1 · Choose a primary key · Read, complete and write SQL scripts Paper 2

By the end of this lesson you can:

Turn a set of data storage requirements into a table design: field names, data types and validation.
Choose, or create, a suitable primary key and justify it.
Read, complete and write SQL scripts that query the table you designed.

Textbook: Chapter 9, §9.1.4 "Practical use of a database" (pp. 348–352)

Recap / Warm-Up 5 min

You can now describe a table, choose data types, pick a primary key and query with SELECT, WHERE, ORDER BY, SUM and COUNT. Today everything comes together.

Quick starter

"How many members have paid their fees?" — SUM or COUNT? And "how many badges have Squad A earned altogether?"

Reveal the answer

Fees paid → COUNT (a number of members/records). Badges altogether → SUM (adds a numeric field).

🧠 Key Concept 14 min

1 · From requirements to a table

In the exam you are given a description of what must be stored. You then define the table. Follow the same five steps every time.

The design method: list, name, type, validate, key. Each step matches a typical exam sub-question.

ScenarioA junior swimming club collects an enrolment form for each swimmer: first name, family name, date of birth and the date they joined. Each swimmer trains in squad A, B or C. The club records whether fees are paid and how many badges each swimmer has earned.

Each box on the form becomes one field. Nothing on the form is guaranteed unique — two swimmers can share a name or a birthday — so an ID field is added as the primary key.

2 · Validate or verify?

Validation can only check that data is reasonable. A name like "Sma" passes every validation rule, so names should also be verified — for example by a visual check against the form.

3 · The finished table

Table MEMBER (8 records, 8 fields)
MemberID	FirstName	FamilyName	DateOfBirth	DateJoined	Squad	FeesPaid	Badges
M001	Sam	Ellis	2012-03-14	2023-09-02	A	TRUE	5
M002	Noor	Hassan	2013-07-21	2024-01-15	B	TRUE	3
M003	Leo	Martin	2011-11-05	2022-05-30	A	FALSE	7
M004	Ava	Kowalski	2014-02-28	2024-03-10	C	TRUE	1
M005	Kai	Ellis	2013-09-09	2024-06-01	B	FALSE	2
M006	Zoe	Adams	2012-12-12	2023-02-18	A	TRUE	6
M007	Omar	Silva	2014-06-17	2024-09-07	C	TRUE	0
M008	Mia	Chen	2011-04-03	2021-10-25	A	TRUE	9

Dates in this unit are written year-month-day, e.g. '2024-03-10'. Written this way, dates sort and compare correctly. Exam papers may show dates as '10/03/2024' — the idea is the same.

Beyond the syllabus (not examined): real systems can build a table with an SQL script too, e.g. CREATE TABLE MEMBER (MemberID CHAR(4) PRIMARY KEY, FirstName VARCHAR(20), …);. At IGCSE you only query a table with SQL — you do not create or change one.

Worked Example 12 min

(a) Define the MEMBER table

List the data items: first name, family name, date of birth, date joined, squad, fees paid, badges.One item of data → one field.
Name each field as one word: FirstName, FamilyName, DateOfBirth, DateJoined, Squad, FeesPaid, Badges.
Choose data types from what each value is — see the table below.Squad is exactly one letter, so character; fees paid has two answers, so Boolean.
Add a validation rule to each field that can be checked.Examiners want the check name and the actual rule, e.g. "range: 0 to 50".
Test each field for uniqueness. None passes, so add MemberID, e.g. M001.Two swimmers could share every other value, but never an ID.

Field	Data type	Validation	Example
`MemberID` 🔑	text	Format: M + 3 digits; presence	M001
`FirstName`	text	Presence; length ≤ 20	Sam
`FamilyName`	text	Presence; length ≤ 30	Ellis
`DateOfBirth`	date/time	Type (real date); range: age 6 to 16	2012-03-14
`DateJoined`	date/time	Type; not after today	2023-09-02
`Squad`	character	Lookup: A, B or C only	A
`FeesPaid`	Boolean	(only TRUE/FALSE possible)	TRUE
`Badges`	integer	Range: 0 to 50	5

(b) Complete a script — "who joined in the first half of 2024?"

Fill each gap, one clause at a time.

SELECT FirstName, ..........
FROM ..........
WHERE DateJoined .......... '2024-01-01' AND '2024-06-30'
ORDER BY ..........;

Gap 1: FamilyNameThe user wants full names, so show both name fields.
Gap 2: MEMBERFROM always names the table.
Gap 3: BETWEEN"AND" with two dates signals a range; the dates go in quotes.
Gap 4: FamilyNameAn alphabetical list is sorted on the family name.
Run it: M002 (15 Jan), M004 (10 Mar) and M005 (1 Jun) joined in range. Sort by family name: Ellis, Hassan, Kowalski.

SELECT FirstName, FamilyName
FROM MEMBER
WHERE DateJoined BETWEEN '2024-01-01' AND '2024-06-30'
ORDER BY FamilyName;

Result set — 3 rows
FirstName	FamilyName
Kai	Ellis
Noor	Hassan
Ava	Kowalski

(c) Read a script — sorting on two fields

SELECT FirstName, FamilyName, Squad
FROM MEMBER
ORDER BY Squad, FamilyName;

No WHERE, so all 8 records appear.
Sort by Squad first: A, A, A, A, B, B, C, C.
Inside each squad, sort by FamilyName: A → Adams, Chen, Ellis, Martin; B → Ellis, Hassan; C → Kowalski, Silva.

Result set — 8 rows
FirstName	FamilyName	Squad
Zoe	Adams	A
Mia	Chen	A
Sam	Ellis	A
Leo	Martin	A
Kai	Ellis	B
Noor	Hassan	B
Ava	Kowalski	C
Omar	Silva	C

Try It Yourself 12 min

🟢 Easy

Goal: Write a query to count the members who have paid their fees. State the value returned.

🟡 Medium

Goal: Write a query to list the MemberID and FirstName of Squad A swimmers who have not paid. Show the output.

🔴 Stretch

Goal: A school organises trips. For each booking it stores the student's name, form group, the trip name, the trip date, the deposit paid (e.g. 12.50) and whether a consent form is returned. Define the table: field names, data types, one validation rule per field, and a primary key.

Hint

The same student can go on several trips, and a trip has many students. Neither the name nor the trip is unique — invent a booking reference.

📝 Exam Practice 10 min

Questions use the MEMBER table shown above.

State[4]

State a suitable data type for each field: DateJoined, Squad, FeesPaid, Badges.

Mark scheme

DateJoined — date/time (1).
Squad — character (accept text) (1).
FeesPaid — Boolean (1).
Badges — integer (1).

Explain[2]

Explain why FamilyName is not a suitable primary key for this table.

Mark scheme

A primary key must be unique for every record (1).
Two members can share a family name — e.g. M001 and M005 are both Ellis (1).

Show[3]

Show the output from this query.

SELECT FirstName, Badges
FROM MEMBER
WHERE Badges > 4
ORDER BY Badges DESC;

Mark scheme

Correct four members only: Mia, Leo, Zoe, Sam (1).
Only FirstName and Badges shown (1).
In descending order of badges: Mia 9, Leo 7, Zoe 6, Sam 5 (1).

Write[3]

Write an SQL query to find the total number of badges earned by swimmers in Squad A.

Mark scheme

SELECT SUM(Badges)
FROM MEMBER
WHERE Squad = 'A';

SELECT SUM(Badges) (1).
FROM MEMBER (1).
WHERE Squad = 'A' (1).

Returns 27 (5 + 7 + 6 + 9)
SUM(Badges)
27

Recap & Key Terms 3 min

To define a single-table database: list the data, name the fields, choose data types, add validation, then choose or add a primary key. Test the design by writing the SQL queries its users will need.

Single-table database: A database that contains only one table.
Primary key: A field that uniquely identifies each record in a table.
Data type: A classification of how data is stored and displayed, and which operations can be performed on it.
Validation: An automatic check that data entered is reasonable before it is accepted.
Verification: A check that data has been copied or entered accurately, e.g. double entry or a visual check.
SQL script: A list of SQL commands that perform a task, often saved so it can be reused.

Homework 1 min

Task (≤ 15 min): Show the output of this query, then write the query that counts how many members are in Squad C.

SELECT FirstName, DateOfBirth
FROM MEMBER
WHERE DateOfBirth < '2013-01-01'
ORDER BY DateOfBirth;

Model answer

Result set — 4 rows
FirstName	DateOfBirth
Mia	2011-04-03
Leo	2011-11-05
Sam	2012-03-14
Zoe	2012-12-12

SELECT COUNT(MemberID)
FROM MEMBER
WHERE Squad = 'C';

This returns 2 (Ava and Omar).

🎯 Syllabus & Goals 3 min

Cambridge 9.1 · Define a single-table database from given requirements Cambridge 9.1 · Choose a primary key · Read, complete and write SQL scripts Paper 2

By the end of this lesson you can:

Turn a set of data storage requirements into a table design: field names, data types and validation.
Choose, or create, a suitable primary key and justify it.
Read, complete and write SQL scripts that query the table you designed.

Textbook: Chapter 9, §9.1.4 "Practical use of a database" (pp. 348–352)

Recap / Warm-Up 5 min

You can now describe a table, choose data types, pick a primary key and query with SELECT, WHERE, ORDER BY, SUM and COUNT. Today everything comes together.

Quick starter

"How many members have paid their fees?" — SUM or COUNT? And "how many badges have Squad A earned altogether?"

Reveal the answer

Fees paid → COUNT (a number of members/records). Badges altogether → SUM (adds a numeric field).

🧠 Key Concept 14 min

1 · From requirements to a table

In the exam you are given a description of what must be stored. You then define the table. Follow the same five steps every time.

The design method: list, name, type, validate, key. Each step matches a typical exam sub-question.

Each box on the form becomes one field. Nothing on the form is guaranteed unique — two swimmers can share a name or a birthday — so an ID field is added as the primary key.

2 · Validate or verify?

Validation can only check that data is reasonable. A name like "Sma" passes every validation rule, so names should also be verified — for example by a visual check against the form.

3 · The finished table

Table MEMBER (8 records, 8 fields)
MemberID	FirstName	FamilyName	DateOfBirth	DateJoined	Squad	FeesPaid	Badges
M001	Sam	Ellis	2012-03-14	2023-09-02	A	TRUE	5
M002	Noor	Hassan	2013-07-21	2024-01-15	B	TRUE	3
M003	Leo	Martin	2011-11-05	2022-05-30	A	FALSE	7
M004	Ava	Kowalski	2014-02-28	2024-03-10	C	TRUE	1
M005	Kai	Ellis	2013-09-09	2024-06-01	B	FALSE	2
M006	Zoe	Adams	2012-12-12	2023-02-18	A	TRUE	6
M007	Omar	Silva	2014-06-17	2024-09-07	C	TRUE	0
M008	Mia	Chen	2011-04-03	2021-10-25	A	TRUE	9

Dates in this unit are written year-month-day, e.g. '2024-03-10'. Written this way, dates sort and compare correctly. Exam papers may show dates as '10/03/2024' — the idea is the same.

Worked Example 12 min

(a) Define the MEMBER table

List the data items: first name, family name, date of birth, date joined, squad, fees paid, badges.One item of data → one field.
Name each field as one word: FirstName, FamilyName, DateOfBirth, DateJoined, Squad, FeesPaid, Badges.
Choose data types from what each value is — see the table below.Squad is exactly one letter, so character; fees paid has two answers, so Boolean.
Add a validation rule to each field that can be checked.Examiners want the check name and the actual rule, e.g. "range: 0 to 50".
Test each field for uniqueness. None passes, so add MemberID, e.g. M001.Two swimmers could share every other value, but never an ID.

Field	Data type	Validation	Example
`MemberID` 🔑	text	Format: M + 3 digits; presence	M001
`FirstName`	text	Presence; length ≤ 20	Sam
`FamilyName`	text	Presence; length ≤ 30	Ellis
`DateOfBirth`	date/time	Type (real date); range: age 6 to 16	2012-03-14
`DateJoined`	date/time	Type; not after today	2023-09-02
`Squad`	character	Lookup: A, B or C only	A
`FeesPaid`	Boolean	(only TRUE/FALSE possible)	TRUE
`Badges`	integer	Range: 0 to 50	5

(b) Complete a script — "who joined in the first half of 2024?"

Fill each gap, one clause at a time.

SELECT FirstName, ..........
FROM ..........
WHERE DateJoined .......... '2024-01-01' AND '2024-06-30'
ORDER BY ..........;

Gap 1: FamilyNameThe user wants full names, so show both name fields.
Gap 2: MEMBERFROM always names the table.
Gap 3: BETWEEN"AND" with two dates signals a range; the dates go in quotes.
Gap 4: FamilyNameAn alphabetical list is sorted on the family name.
Run it: M002 (15 Jan), M004 (10 Mar) and M005 (1 Jun) joined in range. Sort by family name: Ellis, Hassan, Kowalski.

SELECT FirstName, FamilyName
FROM MEMBER
WHERE DateJoined BETWEEN '2024-01-01' AND '2024-06-30'
ORDER BY FamilyName;

Result set — 3 rows
FirstName	FamilyName
Kai	Ellis
Noor	Hassan
Ava	Kowalski

(c) Read a script — sorting on two fields

SELECT FirstName, FamilyName, Squad
FROM MEMBER
ORDER BY Squad, FamilyName;

No WHERE, so all 8 records appear.
Sort by Squad first: A, A, A, A, B, B, C, C.
Inside each squad, sort by FamilyName: A → Adams, Chen, Ellis, Martin; B → Ellis, Hassan; C → Kowalski, Silva.

Result set — 8 rows
FirstName	FamilyName	Squad
Zoe	Adams	A
Mia	Chen	A
Sam	Ellis	A
Leo	Martin	A
Kai	Ellis	B
Noor	Hassan	B
Ava	Kowalski	C
Omar	Silva	C

Try It Yourself 12 min

🟢 Easy

Goal: Write a query to count the members who have paid their fees. State the value returned.

🟡 Medium

Goal: Write a query to list the MemberID and FirstName of Squad A swimmers who have not paid. Show the output.

🔴 Stretch

Hint

The same student can go on several trips, and a trip has many students. Neither the name nor the trip is unique — invent a booking reference.

📝 Exam Practice 10 min

Questions use the MEMBER table shown above.

State[4]

State a suitable data type for each field: DateJoined, Squad, FeesPaid, Badges.

Mark scheme

DateJoined — date/time (1).
Squad — character (accept text) (1).
FeesPaid — Boolean (1).
Badges — integer (1).

Explain[2]

Explain why FamilyName is not a suitable primary key for this table.

Mark scheme

A primary key must be unique for every record (1).
Two members can share a family name — e.g. M001 and M005 are both Ellis (1).

Show[3]

Show the output from this query.

SELECT FirstName, Badges
FROM MEMBER
WHERE Badges > 4
ORDER BY Badges DESC;

Mark scheme

Correct four members only: Mia, Leo, Zoe, Sam (1).
Only FirstName and Badges shown (1).
In descending order of badges: Mia 9, Leo 7, Zoe 6, Sam 5 (1).

Write[3]

Write an SQL query to find the total number of badges earned by swimmers in Squad A.

Mark scheme

SELECT SUM(Badges)
FROM MEMBER
WHERE Squad = 'A';

SELECT SUM(Badges) (1).
FROM MEMBER (1).
WHERE Squad = 'A' (1).

Returns 27 (5 + 7 + 6 + 9)
SUM(Badges)
27

Recap & Key Terms 3 min

To define a single-table database: list the data, name the fields, choose data types, add validation, then choose or add a primary key. Test the design by writing the SQL queries its users will need.

Single-table database: A database that contains only one table.
Primary key: A field that uniquely identifies each record in a table.
Data type: A classification of how data is stored and displayed, and which operations can be performed on it.
Validation: An automatic check that data entered is reasonable before it is accepted.
Verification: A check that data has been copied or entered accurately, e.g. double entry or a visual check.
SQL script: A list of SQL commands that perform a task, often saved so it can be reused.

Homework 1 min

Task (≤ 15 min): Show the output of this query, then write the query that counts how many members are in Squad C.

SELECT FirstName, DateOfBirth
FROM MEMBER
WHERE DateOfBirth < '2013-01-01'
ORDER BY DateOfBirth;

Model answer

Result set — 4 rows
FirstName	DateOfBirth
Mia	2011-04-03
Leo	2011-11-05
Sam	2012-03-14
Zoe	2012-12-12

SELECT COUNT(MemberID)
FROM MEMBER
WHERE Squad = 'C';

This returns 2 (Ava and Omar).