Skip to the page
Year 10 · Computer Science · Summer 2 · Databases and the Year 10 mock

Tables, fields, records and keys

TheoryPresent ▶

What this lesson covers

9.1 Tables, fields and records

Cambridge IGCSE Computer Science 0478 · Topic 9: Databases · Paper 2

9.2 Data types, validation and primary keys

Cambridge IGCSE Computer Science 0478 · Topic 9: Databases · Paper 2

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.

Success criteria

  • 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.
The syllabus points for these sections
  • Explain what a single-table database is and why we use one.
  • Identify the fields and records in a table.
  • Count fields and records correctly in an exam table.
  • Choose the best data type for a field: text/alphanumeric, character, Boolean, integer, real, date/time.
  • Suggest validation checks for fields in a table.
  • Explain what a primary key is.
  • Choose a suitable primary key and justify it.

Key words

Say each word, then read what it means. Every one is in the glossary too.

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.
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.
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.
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.
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.

The explanation

Read it here, or open the study page below to go one step at a time with things to try.

9.1 Tables, fields and records

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.

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.
?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.

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.

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
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.

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
!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 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`.

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
?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.

Choosing a data type: the tricky cases

Ask two questions. Will I do maths with it? Is it a whole number or does it need decimals?

A phone number is text, not integer. It can start with 0 or +34, and you never add phone numbers together.

A field with only yes/no answers is Boolean. A field that always holds one single symbol, like a grade `A` or a size `M`, is character.

e.g.Example
Field: MobileNumber, value +34 612 345 678 → Text (it has + and spaces, no maths) Field: Minutes, value 55 → Integer (whole number of minutes) Field: Cost, value 5.50 → Real (money has decimals) Field: HelmetHired, value TRUE → Boolean (yes or no)
!Watch out

Do not answer "number" or "string". Use the exact Cambridge names: integer or real, text/alphanumeric. A vague answer like "number" usually scores 0.

Validation in a database

Validation is an automatic check that data is sensible before the database accepts it. It does not prove the data is correct.

You met these checks in topic 7: range, length, type, presence, format and check digit.

In a database, each check is set on a field. The database then refuses bad data when someone types it in.

FieldCheckRule
MinutesRangeBetween 1 and 240
MinutesTypeMust be an integer
HireIDFormatThe letter H followed by two digits
HireIDLengthExactly 3 characters
StationPresenceMust not be left empty
?Why?

A wrong value, like 5500 minutes, would ruin every total and count. Stopping it at the door is easier than finding it later.

The primary key

A primary key is a field that holds a unique value for every record. No two records can have the same value in that field.

It lets the database pick out exactly one record, with no confusion.

To choose one, look for a field where no value can ever repeat, and that is never empty. ID fields are made for this.

e.g.Example
Which field in BikeHire is the best primary key? Station: Sol appears 3 times → not unique ✗ Minutes: values could repeat on another day → not unique ✗ HireDate: 03/03/2026 appears twice → not unique ✗ HireID: H01 to H08, every value different → unique ✓ Answer: HireID, because each hire has its own ID that is never repeated.
!Watch out

In the exam, give the field name and the reason. "HireID" alone may only earn 1 mark. Add "because each value is unique, so it identifies each record".

Study it

Open the study page and go one step at a time.

Computer Science topic 9

Databases

Step by step, with exam questions and a quiz.

Computer Science topic 7

Algorithm design and problem-solving

Step by step, with exam questions and a quiz.

Computer Science topic 8

Programming

Step by step, with exam questions and a quiz.

Topic 18 · Year 7

Data, spreadsheets and AI

Data is raw facts. Information is data with meaning. Spreadsheets calculate, databases store and search, AI learns patterns from examples.

Check yourself

One question at a time. The reason shows after each answer.

Saved on this device.