Year 10 · Computer Science · Summer 2

Databases and the Year 10 mock · Lesson 1 of 5

Tables, fields, records and keys

Theory9.1 Tables, fields and records9.2 Data types, validation and primary keys

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 1 of 5

You will learn to

  • Identify the fields and records in a single-table database.
  • Choose the best data type for each field: text, character, Boolean, integer, real or date/time.
  • Explain what a primary key is, and choose and justify one.
  • Suggest a validation check for a field.

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 1 of 5

Success criteria

You have got it when you can say:

  • I can count the fields and records in an exam table.
  • I can design a library book table, with a data type for every field.
  • I can explain why a BookID is a better primary key than the book's title.

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 1 of 5

Retrieval starter

Questions from before. Write your answers, then show them.

  1. Last unit

    A compiler…

    A Translates the whole program once into machine codeB Translates one line at a time, every runC Only checks spelling

    A. Translates the whole program once into machine code

  2. Last unit

    An interrupt is…

    A A loop that never endsB A signal that makes the CPU pause and deal with somethingC A type of virus

    B. A signal that makes the CPU pause and deal with something

  3. Earlier this year

    X = (A AND B) OR C with A = 0, B = 1, C = 1 gives…

    A 1B Cannot tellC 0

    A. 1

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 1 of 5

Key words (1 of 5)

Database
An organised, structured store of data that a computer can search and sort.
Single-table database
A database where all the data is kept in one table.
Table
A grid of data made of rows (records) and columns (fields).
Field
One column of a table; it holds one item of data about every record, such as Minutes.

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 1 of 5

Key words (2 of 5)

Record
One row of a table; it holds all the data about one thing, such as one bike hire.
Field name
The heading at the top of a column, such as HireID; it is not a record.
Data type
The kind of data a field can hold, such as integer, real or Boolean.
Text / alphanumeric
A data type for letters, digits and symbols, such as a name or a phone number.

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 1 of 5

Key words (3 of 5)

Character
A data type for exactly one letter, digit or symbol, such as E or M.
Boolean
A data type with only two possible values, TRUE or FALSE.
Integer
A data type for whole numbers, such as 55.
Real
A data type for numbers with a decimal part, such as 5.50.

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 1 of 5

Key words (4 of 5)

Date/time
A data type for a date, a time, or both, such as 05/03/2026.
Primary key
A field whose value is unique for every record, so it identifies each record.
Validation
An automatic check that data is sensible and follows the rules before it is accepted.
Query
A question to the database, written in SQL, that returns matching data.

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 1 of 5

Key words (5 of 5)

ORDER BY
The SQL clause that sorts the output, ascending (ASC) or descending (DESC).
COUNT
An SQL function that counts how many records meet a condition.

9.1 Tables, fields and records · The explanation · step 1 of 7 · part 1 of 3

What a database is

A database is an organised store of data that a computer can search and sort.

At IGCSE you only work with a single-table database. This means all the data sits in one table, like one big grid.

We use a database instead of a list on paper because the computer can find, sort and total thousands of records in a fraction of a second.

9.1 Tables, fields and records · The explanation · step 1 of 7 · part 2 of 3

What a database is (continued)

e.g.Example
A Madrid bike-hire scheme records every hire. Each hire has an ID, a start station, a bike type, the minutes ridden, the cost, whether the rider is a member and the date. With a database, staff can ask: "How much did members pay this week?" and get the answer at once.

9.1 Tables, fields and records · The explanation · step 1 of 7 · part 3 of 3

What a database is (continued)

?Why?

Data kept in one structured table is consistent. Every hire has the same items stored in the same order, so the computer always knows where to look.

9.1 Tables, fields and records · The explanation · step 2 of 7 · part 1 of 3

Our sample table: BikeHire

This is the table we will use for the whole topic. Its name is `BikeHire`.

Each column is a field. A field holds one item of data, such as `Minutes`.

Each row is a record. A record holds all the data about one thing, here one bike hire.

`BikeType` is `E` for an electric bike and `M` for a mechanical (pedal) bike.

9.1 Tables, fields and records · The explanation · step 2 of 7 · part 2 of 3

Our sample table: BikeHire (continued)

HireIDStationBikeTypeMinutesCostMemberHireDate
H01SolE252.50TRUE03/03/2026
H02RetiroM402.00FALSE03/03/2026
H03AtochaE121.20TRUE04/03/2026
H04SolM180.90TRUE04/03/2026
H05MoncloaE555.50FALSE05/03/2026
H06RetiroE303.00TRUE05/03/2026
H07AtochaM80.40FALSE06/03/2026
H08SolE424.20FALSE06/03/2026

9.1 Tables, fields and records · The explanation · step 2 of 7 · part 3 of 3

Our sample table: BikeHire (continued)

e.g.Example
Cost in euros: electric bikes cost 0.10 per minute, mechanical bikes 0.05 per minute. H01: 25 × 0.10 = 2.50 H02: 40 × 0.05 = 2.00
?Why?

Keeping one table all topic long lets you check every query output against data you already know.

9.1 Tables, fields and records · The explanation · step 3 of 7 · part 1 of 2

Counting fields and records

Exam questions often ask: "State the number of fields" or "State the number of records".

Count the columns for fields. Count the data rows for records.

The top row holds the field names. It is not a record, so do not count it.

e.g.Example
BikeHire fields: HireID, Station, BikeType, Minutes, Cost, Member, HireDate Number of fields = 7 BikeHire records: H01 to H08 Number of records = 8

9.1 Tables, fields and records · The explanation · step 3 of 7 · part 2 of 2

Counting fields and records (continued)

!Watch out

Students often count the heading row as a record and answer 9. The heading row only names the fields.

9.2 Data types, validation and primary keys · The explanation · step 4 of 7 · part 1 of 3

The six data types

Every field has a data type. The data type tells the database what kind of data the field may hold.

Cambridge uses six data types for databases. Learn them with an example from `BikeHire`.

9.2 Data types, validation and primary keys · The explanation · step 4 of 7 · part 2 of 3

The six data types (continued)

Data typeWhat it holdsBikeHire fieldExample
Text / alphanumericLetters, digits and symbolsStation, HireIDRetiro, H05
CharacterExactly one letter, digit or symbolBikeTypeE
BooleanOnly two values: TRUE or FALSEMemberTRUE
IntegerA whole numberMinutes55
RealA number with a decimal partCost5.50
Date/timeA date, a time, or bothHireDate05/03/2026

9.2 Data types, validation and primary keys · The explanation · step 4 of 7 · part 3 of 3

The six data types (continued)

?Why?

The right data type lets the database do the right things. It can add up real numbers, sort dates in time order and reject a letter typed into an integer field.

The explanation · 3 more steps

More on the lesson page

The lesson page has the rest of the explanation, step by step:

  • Choosing a data type: the tricky cases
  • Validation in a database
  • The primary key

Open the lesson page

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 1 of 5

Quick quiz · 1 of 3

A record is…

  1. A One column
  2. B The whole table
  3. C One row: everything about one thing

C. One row: everything about one thing

A field is a column; a record is a row.

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 1 of 5

Quick quiz · 2 of 3

A good primary key is…

  1. A A unique ID number
  2. B A surname
  3. C A date of birth

A. A unique ID number

Two pupils can share a surname or a birthday. An ID is unique.

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 1 of 5

Quick quiz · 3 of 3

SELECT Name FROM Pupils WHERE Year = 8 ORDER BY Name; shows…

  1. A All pupils
  2. B Year 8 names in alphabetical order
  3. C Only the first pupil

B. Year 8 names in alphabetical order

WHERE filters the rows; ORDER BY sorts them.

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 1 of 5

Exit ticket

Answer on paper before you go. Then check.

  1. The data type for 'has paid' (yes or no) is…

    A TextB RealC Boolean

    C. Boolean Boolean holds true or false.

  2. SELECT COUNT(*) FROM Pupils WHERE House = 'Red'; gives…

    A The total scoreB Their namesC How many pupils are in Red

    C. How many pupils are in Red COUNT gives one number: how many records match.