9.1–9.2 Foundations Lab · Databases

📚 Topic overview

Every organisation you interact with runs on a database. Your school stores your attendance in one. Your streaming service stores what you've watched in one. When you log into a website and it "remembers" you, that's a database being queried in the background.

Topic 9 splits into two skills: first, how to design a database so it stores data cleanly (Lab A), and second, how to question a database so you can get information back out (Labs B and C). This first lab tackles the foundations — the structure that makes everything else possible, and the language you'll use to talk to it.

Prerequisites: none required. If you've met data types in Chapter 8 (Programming), you'll notice the database types are slightly different — that's one of the things this lab locks in.

🎯 Learning objectives

9.1 Database structure

9.1.a Understand the structure and components of a single-table database.

9.1.b Identify the fields necessary for a single-table database.

9.1.c Understand what type of data each of the basic data types represents.

9.1.d Identify appropriate data types for specific data and fields.

9.1.e Describe the purpose and/or need for a primary key in a table.

9.1.f Identify an appropriate primary key for a table.

9.2 SQL introduction

9.2.a Understand what SQL stands for and what it is used for.

9.2.b Understand the purpose of SQL scripts.

9.2.c Recognise that this syllabus uses SQL to search for data and perform calculations — not to define or modify tables.

📖 Key terminology

Term	Meaning
Database	An organised collection of data. In this syllabus, always a single-table database.
Table	A set of data about one type of object (e.g. students, books, storage units). Made up of fields and records.
Field	An individual piece of data being stored — one column in the table (e.g. `LastName`, `Price`).
Record	All the fields about one object — one row in the table (e.g. everything stored about Aarna Singh).
Data type	The characteristic of a piece of data that tells the database what it can hold.
Text	A data type storing letters, symbols, or mixed characters (e.g. `"Sparkle"`). Called "text" in databases, not "string".
Character	A data type storing exactly one letter, digit, or symbol (e.g. `'A'`).
Integer	A data type storing a whole number, no decimal point (e.g. `23`).
Real	A data type storing a number with a decimal point (e.g. `3.99`).
Boolean	A data type storing one of two values, usually True/False or Yes/No. Written without quotation marks.
Date/Time	A data type storing a date and/or a time value.
Primary key	A unique field in a database used to identify one specific record. No two records can share the same primary key value.
SQL	Structured Query Language — a standard language used across most databases to interact with data.
SQL script	A series of SQL statements (commands) that are executed together and return one or more values.
Query	A request made to the database to return specific data.

Boolean values (TRUE, FALSE) never take quotation marks in SQL. All other text values do — this is the most-cited SQL exam mistake.

🧠 Core theory

1. Anatomy of a single-table database

A database table is a grid. Every column is a field (one category of data). Every row is a record (one entry). That's it — that's the whole shape.

BookID	BookName	Author
B01	The Hobbit	Tolkien
B02	Matilda	Dahl
B03	Coraline	Gaiman
B04	Wonder	Palacio

Columns = fields Rows = records

Sanity check: this table has 3 fields and 4 records. Read down for fields, across for records.

Recurring exam trap: examiners cite students reversing fields and records year after year. See the Misconceptions block below.

2. The six database data types

Cambridge tests exactly six data types. Learn how each is written in a table cell — and, critically, whether it needs quotes when it appears in an SQL query.

Data type	What it stores	Example cell	Quotes in SQL?
Text	Words, sentences, mixed characters	`Amsterdam`	Yes: `"Amsterdam"`
Character	Exactly one character	`A`	Yes: `'A'`
Integer	Whole number, no decimal	`1566999`	No
Real	Number with a decimal point	`3.99`	No
Boolean	True/False or Yes/No	`TRUE`	No — this is the trap
Date/Time	A date and/or time	`2025-06-14`	Yes (treated like text)

The quotes column previews 9.4 WHERE-clause behaviour, but earns its place here because 2024 s24 and 2025 w25 both flagged missing quotes as a recurring mark loss.

3. Primary keys — what makes a field "unique"

Part A — what a primary key IS. A primary key is a field whose value never repeats across records. It uniquely identifies exactly one record. Examples you'll meet in past papers: StorageID, Code, ContractNumber, BookID, StudentID.

Part B — how you CHOOSE a primary key. Ask of every field: "Could two records ever share this value?"

If yes → not a primary key. Names repeat. Colours repeat. Prices repeat.
If no field is naturally unique, the database creates one — usually an ID field like ItemID or Code.
The primary key is a column in the table, not a row and not the table's heading.

🗝️ Mark-scheme phrase to memorise

"…because it is a unique identifier." That phrase — or a paraphrase that includes both unique and identify — is the standard mark-earning answer for "Explain why X is the primary key." Verbatim from the 2024 s24 mark scheme.

4. What SQL is, and what this syllabus asks of it

SQL = Structured Query Language. It's the standard language across most database systems — learn it once, use it anywhere.

SQL can do many things: define tables, change tables, add data, search for data, calculate on data. This syllabus tests only the last two: searching (queries) and calculating (SUM, COUNT).

An SQL script is a series of statements that get executed together and return one or more values.

📋 What Cambridge will ask you to do

You won't be asked to write SQL that creates or modifies a table in this exam. You will be asked to read scripts, complete scripts, and predict what scripts return. All Lab B and Lab C content.

✏️ Worked examples

Example 1 — Reading the anatomy of a table

The table (MajorCity, adapted from 2025 s25):

Code    City       Capital   Country          Population
ASY1    Beijing    TRUE      China            21,766,214
EUY3    Amsterdam  TRUE      Netherlands       1,174,025
EUN1    Frankfurt  FALSE     Germany             796,437

Q: How many fields? How many records?

A: 5 fields (Code, City, Capital, Country, Population). 3 records (each row = one city).

Why it matters: Cambridge asks this exact question at least once a year and always flags students who swap the two numbers.

Example 2 — Choosing a primary key

Same table. Which field could be the primary key?

City? Only if no two cities ever share a name. Global databases have this problem — think "Springfield". Weak candidate.
Country? No — multiple cities per country in this table's shape. Instant fail.
Code? Every code is different (ASY1, EUY3, EUN1). Yes — this is the unique identifier.

Winning answer format

"Code is the primary key because it is a unique identifier / no two records share the same code."

Example 3 — Picking data types

Field	Stores	Data type
ItemName	"Sparkle"	Text
Colour	"red"	Text
Weight	2	Integer
Price	3.99	Real
InStock	Yes/No	Boolean
DateAdded	2026-01-15	Date/Time

Trap flag: students commonly write string instead of Text. Cambridge database data types differ slightly from programming data types — always write Text in a database question.

Example 4 — Recognising SQL vs. non-SQL

Which of these three snippets is SQL?

Snippet A:  DECLARE x : INTEGER
Snippet B:  SELECT City FROM MajorCity
Snippet C:  IF Price > 5 THEN OUTPUT "Expensive"

A: Only B is SQL. A is Cambridge pseudocode (a variable declaration). C is Cambridge pseudocode (a selection statement).

Why it matters: SQL is a separate language from the pseudocode you already know. Mixing keywords is a common Q10-territory mistake.

⚠️ Common misconceptions

Trap 1 — Swapping fields and records

❌ Saying the table has 23 fields and 6 records (when it has 6 fields and 23 records).

Fields are the columns (categories of data). Records are the rows (each individual entry). Number of records = number of things you're storing. Number of fields = number of details about each thing.

Cited: 2025 s25 Q8(a), 2024 s24, 2024 w24 — recurring across every sitting.

Trap 2 — Writing "string" instead of "Text"

❌ Giving string as a data type in a database question.

In programming (Chapter 8) you learned about STRING. In databases the correct term is Text. They store the same kind of data, but Cambridge marks them separately — write "Text" every time in a database question.

Cited: 2023 examiner report — "A common error seen was to give string instead of text."

Trap 3 — Picking a non-unique field as the primary key

❌ Saying Type or Name or Country is the primary key because it's the "most important" field.

The primary key isn't the most important field — it's the unique one. If any two records could share the same value in that field, it cannot be a primary key. Ask yourself: "Could two records ever have the same value here?" If yes, it's out.

Cited: 2024 w24 — "Most candidates understood that the data in the Type field was not unique for all records, so could not be the primary key."

Trap 4 — Confusing the table structure names

❌ Identifying "Field" as a primary key when "Field" is the column heading of the table.

Some past-paper tables include a helper column literally labelled "Field" that lists the field names. Students then circle "Field" as the primary key. The primary key is one of the actual data fields — not the label above them.

Cited: 2024 s24 Q9 — "Some candidates incorrectly identified 'Field' as a primary key, which was the heading of the table."

Trap 5 — Forgetting the "unique identifier" phrase

❌ Saying the primary key was chosen "because it's important" or "because it comes first".

There is one mark-earning phrase Cambridge accepts for why a field is the primary key: it is a unique identifier. Say unique and say it identifies a record. Anything else risks losing the mark.

Cited: 2024 s24 Q9(a)(ii) — mark scheme awards "unique identifier" phrasing.

Trap 6 — Treating SQL as pseudocode

❌ Mixing pseudocode keywords into an SQL statement (e.g. writing OUTPUT instead of SELECT, or using IF inside a query).

SQL is a separate language from Cambridge pseudocode. SQL keywords are SELECT, FROM, WHERE, ORDER BY, SUM, COUNT. Pseudocode keywords like OUTPUT, DECLARE, IF never appear in SQL. Never mix them.

Cited: 2023 w23 examiner report on SQL punctuation and keyword errors.

🎯 Cambridge exam focus

The exact phrasings that score credit. Memorise them.

"Unique identifier." Full mark-earning phrase for "Why is X the primary key?" — "Because it is a unique identifier (of a record)." Say unique and say identify.

Fields = columns, records = rows. If asked to count either, count carefully and sanity-check: "How many categories? How many entries?"

Write "Text", not "String". In every database question, the data type for words is Text. Never String.

Boolean values are unquoted. Capital = TRUE, never Capital = "TRUE". The single most-lost SQL mark on Booleans.

Text values in WHERE are quoted. Country = "China" (double quotes standard). Missing these = zero on that line.

Primary key is a field, not a heading. The primary key is a column inside the table's data — not the label at the top of a helper column called "Field".

SQL is a separate language. Never mix pseudocode (OUTPUT, DECLARE, IF) into an SQL statement.

A "script" is multiple statements. If a question says "complete the SQL script", you may need SELECT, FROM, WHERE, and ORDER BY — not just one line.

🧪 Quick knowledge check

Click each question to reveal the answer. 0 / 6 revealed

1. What's the difference between a field and a record?

A field is a column — one type of data being stored (e.g. LastName). A record is a row — all the fields belonging to one entity (e.g. everything stored about one person).

2. A database has 6 columns and 11 rows. How many fields and how many records?

6 fields and 11 records. Fields = columns, records = rows.

3. Give the correct database data type for storing a person's name.

Text. Not String — that's the programming term.

4. Which data type stores a value like TRUE or FALSE, and does it need quotation marks in SQL?

Boolean. It does not take quotation marks — Boolean values are written unquoted.

5. Explain what a primary key is and why databases need one.

A primary key is a unique field that identifies one specific record. Databases need one because it lets the system find, update, or reference a single record with certainty — no two records share the same primary key value.

6. What does SQL stand for and what are the two things this syllabus uses SQL for?

SQL stands for Structured Query Language. This syllabus uses SQL for (1) searching data (queries) and (2) calculating on data (SUM, COUNT).

✅ Ready for the next lab

You've locked in the structural language of databases — fields, records, data types, primary keys — and you know what SQL is for. That's the whole foundation.

Next lab: 9.3–9.5 Querying Lab — where you finally get to write SQL to pull specific data out of a table. See you in Lab B.

📍 How this lesson works

📚 Book Notes → 🎓 Learn → 🎮 Activities → ✎ Practice → 📋 Exam → 🔄 Review → 🏆 Mastery

Read Book Notes first, then work through each tab in order. Your progress is saved automatically. ✓ marks appear on tabs you've visited.

💾 Save key facts

Persist the six most exam-critical facts from this lab to your Vault.

💡 Cambridge tip

The "unique identifier" phrase is worth memorising word-for-word. Every time Cambridge asks "Why is X a suitable primary key?" the mark scheme accepts a variant of "because it is a unique identifier".

Say unique and say it identifies. Vague answers like "because it's important" or "because it's first" score nothing.

📊 Session progress

0Practice attempts

0Exam attempts

0Skills mastered

🎓 Learn — walking-around understanding

Book Notes is the reference. Learn is the walk-through. If you're new to databases entirely, start here and go slow. If Book Notes already clicked, skim.

Concept 1 · A table is a grid, not a shape you have to memorise

Every single database table you'll ever see in this course looks the same: a header row of field names, then a stack of data rows. That's it. If you can read a spreadsheet, you can read a database table.

The two questions Cambridge cares about are: which piece is which?

A field is a category of information. "Everyone's last name" is a field. "Everyone's date of birth" is another field. Fields run top to bottom as columns.
A record is one specific entry. Everything stored about Aarna Singh is one record. Everything stored about the book Matilda is one record. Records run left to right as rows.

Trick for the counting question

Cambridge often gives you a table and asks "how many fields and how many records?" Point at the top row and count categories → that's fields. Then count everything below the header → that's records. Slow down. Every year, students who rush swap the numbers.

Concept 2 · Data types tell the database what kind of thing lives in each field

When you design a table, you have to decide what kind of data each field holds. Cambridge tests six types. There's a specific vocabulary — and it doesn't quite match what you learned in programming.

🔤

Text — anything with letters or a mix (city names, addresses, colours).
Character — exactly one symbol (like a grade "A").

🔢

Integer — whole numbers (population, count in stock).
Real — numbers with decimals (price, weight, percentages).

✅

Boolean — a yes/no or true/false switch (in stock? capital city? paid?).

📅

Date/Time — for dates, times, or both. Cambridge accepts "Date" alone if the field only stores a date.

Watch: in programming you say String. In databases you say Text. Same idea, different word. Cambridge marks them separately.

Concept 3 · Primary key = one-of-a-kind field

Imagine your school has two students both called Alex Chen. If the register just says "Alex Chen was absent," which one? You need something unique — a Student ID. That's a primary key.

Every database table needs one field where no two records share the same value. That field is the primary key. It's how the database points at exactly one row when it needs to find, update, or delete something.

Sometimes a natural field works: ContractNumber, ISBN, StorageID. When nothing natural is unique, the database creates one: ItemID, Code.

The 6-mark habit

Whenever Cambridge asks "Why is X the primary key?", write: "Because it is a unique identifier — no two records share the same X." Say unique. Say identify. Every time.

Concept 4 · SQL is a different language on top of the table

Once you have a table, you need a way to talk to it. That language is SQL — Structured Query Language.

SQL is not pseudocode. It's not Python. It's its own thing — a small set of keywords used to ask a database questions. In this syllabus you'll only use SQL to search (find rows that match a rule) and calculate (add them up, count them).

Cambridge won't ask you to create a table with SQL, add rows with SQL, or delete anything. Your job across Labs B and C is to read, complete, and predict SQL scripts.

Vocabulary lock: "SQL script" = a series of SQL statements executed together. It doesn't mean one line — a full SELECT ... FROM ... WHERE ... ORDER BY is one script made of multiple statements. Cambridge uses "script" carefully — you should too.

You've got Learn — head to Activities

Time to build a table yourself with the Table Architect. That's where fields, data types, and primary keys stop being definitions and become decisions.

🎮 Table Architect

Pick a scenario, add the fields you think it needs, choose a data type for each, and nominate a primary key. The Architect will check your work against the way Cambridge expects a well-formed table to look — and flag the traps if you fall into one.

📚 Library book

🎓 Student

📦 Storage unit

🌍 City

🛠️ Design your table

Pick a primary key

👀 Live preview — 3 example records

Add some fields on the left. The preview will fill in as you go.

What the Architect is testing

Did you include the essential fields? Each scenario has fields Cambridge would expect. Miss too many and you'll get a "table is too thin" hint.
Are your data types right? A field storing a price is Real, not Integer. A field storing yes/no is Boolean, not Text.
Is your primary key actually unique? Names repeat. Types repeat. IDs and codes don't. The Architect will call it out if you pick a repeatable field.
Did you write "Text" — not "String"? The dropdown enforces this, so the mistake can't slip through — one less mark to lose in the exam.

⚡ 60-second sprint

Ten rapid-fire questions on foundations vocabulary. Sixty seconds on the clock. Beat your best score.

✎ Practice

Multiple-choice checks. Start on Easy if you're new to the topic; move up as you go. Every attempt is logged in Progress. Adaptive: weak skills come up more often.

All

Easy

Medium

Exam-style

🧪 Exam Mode

📋 Exam-style questions

Real past-paper style. Type your answer, then self-mark against the mark scheme. Skills feed into Mastery.

🔄 Review

The mark-losing traps Cambridge examiners cite year after year on Topic 9.1–9.2 — plus memory triggers to lock the exam-winning phrases in your head.

⚠️ The 10 traps Cambridge cites most

Every trap here is drawn from an actual Cambridge examiner report. Read them before every mock exam.

Trap 1 · Swapping fields and records

Fields are columns (categories of data). Records are rows (individual entries). Students who rush swap the numbers under exam pressure.

Cited: 2024 w24 P2 Q10 examiner report — recurring across 2023–2025.

Trap 2 · Writing "String" instead of "Text"

In database questions the data type for words is Text, not String. String is a programming term (Chapter 8). Cambridge marks the two separately in database contexts.

Cited: recurring across 2022–2025 mark schemes — model answers always use "Text".

Trap 3 · Missing quotes around Text values in WHERE

Text values in WHERE clauses must be in quotation marks (single or double, Cambridge accepts either). Missing quotes = zero marks on that line.

Cited: 2023 P2 Q9 — "A common error seen was not to include quotation marks round the required author's name." Reinforced 2024 s24 Q9 SoftDrinks + 2025 w25 (missing quotes around "Asia").

Trap 4 · Adding quotes around Booleans or numbers

Booleans and numbers are never quoted in SQL. Only text-like values (Text, Character, Date/Time) take quotes. Available = "TRUE" loses the mark.

Cited: 2025 w25 examiner report — mark scheme model SQL uses Capital = TRUE unquoted.

Trap 5 · Vague primary key answers

The mark-earning phrase for "why is X the primary key?" is "because it is a unique identifier". Answers like "because it's important" or "because it's first" score nothing.

Cited: 2025 s25 P2 Q8 MajorCity examiner report — model answer specifies "unique identifier".

Trap 6 · Choosing a repeatable field as the primary key

Names repeat. Prices repeat. Yes/No fields have only two values across the whole table. A primary key must be unique — usually an ID or Code field.

Cited: 2024 s24 P2 Q9 StorageUnit — StorageID is the only correct answer.

Trap 7 · Confusing primary key with the column header row

The primary key is a field — a column of data inside the table. It is NOT the label row at the top of a helper column called "Field". Read the question carefully.

Cited: 2024 w24 P2 examiner report on database structure questions.

Trap 8 · Extra punctuation in SQL queries

Trailing commas after the last field (SELECT City, Country,), full stops instead of commas, or stray semicolons mid-query are all syntax errors. Cambridge flags "extra punctuation" as a recurring mark loss.

Cited: 2023 P2 examiner report — "A common error seen was to include extra punctuation."

Trap 9 · Extra punctuation in SQL output

When asked to "write the output of this SQL statement", students often add commas or full stops. Cambridge outputs are plain values, one record per line, separated only by spaces or tabs.

Cited: 2024 w24 P2 — "Punctuation marks should not be in the output from an SQL statement." Reinforced 2025 m25 verbatim.

Trap 10 · Mixing pseudocode into SQL

SQL keywords are SELECT, FROM, WHERE, ORDER BY, SUM, COUNT. Pseudocode keywords like OUTPUT, DECLARE, IF never appear inside an SQL script. They're separate languages.

Cited: 2023 w23 P2 examiner report on SQL punctuation and keyword errors.

🧠 Memory triggers

Short mnemonics for the exam-critical phrases you'll be tested on.

🗝️ "FROC" — Fields are Rows Or Columns?

Fields = Columns.
Records = Rows. (Think: "F comes before R, C comes before Ro" — pairs go together.) When you see "how many fields?", count across the header. "How many records?", count down the rows.

🗝️ "TIU" — the primary key phrase

Three words score the mark: "because it is a unique identifier". Say unique. Say identify. Say them together. That's the whole answer.

🗝️ "TICRB-D" — the six data types

Text · Integer · Character · Real · Boolean · Date/Time. (Think: "Ticker-B-D".) That's every type Cambridge tests. No "String". No "Number". No "Letter".

🗝️ "Text-like, Quote it"

The one-line quoting rule: "If the value is text-like, quote it. If it's number-like or True/False, don't." Text, Character, Date/Time = quoted. Integer, Real, Boolean = unquoted. Worth more marks than any other rule in Topic 9.

🗝️ "SQL is not Pseudocode"

If a keyword appears in Chapter 8 (Programming) but not in Chapter 9 (Databases), it doesn't belong in an SQL script. OUTPUT, DECLARE, IF, DIV, MOD → pseudocode only. SELECT, FROM, WHERE, ORDER BY → SQL only.

🗝️ "S-F-W-O" — clause order for later labs

SELECT → FROM → WHERE → ORDER BY. Say it aloud once a day for a week. When you get to Lab B, this order is worth every full-mark query. Fixed, never reversed.

Your logged mistakes

No mistakes logged yet. Come back after Practice and Exam.

Recent Vault entries

Nothing in the Vault yet. Save key facts from Book Notes or from question feedback.

🏆 Mastery

Every skill you attempt (in Table Architect, Practice, or Exam) is scored. Get 3+ attempts with 70%+ accuracy on a skill and it goes green — mastered.

Tap a skill badge to override its status (e.g. mark it Mastered by hand once you're confident, or set it back to In Progress to force more practice).

No attempts yet.

📝 Revision checklist

Tick each item as you cover it. Save your progress by leaving this browser open.

Achievements

Complete Practice and Exam to unlock achievements.