Revision Notes for IGCSE

Single-Table Database Design

Watch this lesson Video 9.1 · 7:12 · Fields, records, the primary key, and choosing the right data type

1. What a Database Is

A database is a structured, organised collection of data stored so that it can be searched, sorted and updated easily. Storing the same data in a plain text file or a pile of paper forms makes it slow to search and easy to corrupt.

Scope for this syllabus: you are required to work with a single-table database only. You will not be asked to design a database with two or more linked tables, and foreign keys and relationships are not examined at IGCSE. If you have seen relational database design elsewhere, set it aside for this paper — everything below concerns one table.

2. Essential Terminology

Three terms describe the structure of a single table, and they are examined directly.

Term	Meaning	In a table
Field	A single item of data, of one specific data type	A column
Record	A complete set of fields about one person, object or event	A row
Primary key	A field that uniquely identifies each record	One designated column

A helpful way to keep these straight: a field is one fact, a record is all the facts about one thing, and the table is all the records together.

3. The Primary Key

The purpose of a primary key is to identify each record uniquely, so that no two records can be confused and any single record can be found reliably.

A suitable primary key must:

be different for every record — no duplicates are possible
never be left empty
not change over time

Choosing a primary key

Consider a table of students. Which field would work?

Field	Suitable?	Why
FirstName	No	Two students can share a first name
Surname	No	Duplicates are very likely
DateOfBirth	No	Several students may share a birthday
ClassGroup	No	Many students are in the same class
StudentID	Yes	Deliberately issued so that it is unique and never reused

Justify your choice by uniqueness. The mark is for explaining that the field is unique to each record. Answers such as "because it is a number" or "because it is the first column" score nothing. Equally, saying a name "is probably unique" is not enough — a primary key must be guaranteed unique.

4. Basic Data Types

Each field is given a data type. The syllabus limits these to six.

Data type	Stores	Example
Text / alphanumeric	Letters, digits and symbols together	14 Green Lane
Character	A single character only	M
Boolean	One of two values only	TRUE / FALSE
Integer	A whole number	17
Real	A number with a decimal part	72.5
Date/time	A calendar date and/or a time	14/07/2009

A telephone number is text, not an integer. It may begin with a zero, may contain spaces or a +, and is never used in arithmetic. The same reasoning applies to postcodes and student ID codes containing letters. This is a very frequently examined point.

Choose Boolean whenever a field answers a yes/no question, such as LibraryFinePaid. Using text for this wastes space and allows inconsistent entries such as "yes", "Y" and "true" in the same column.

5. Validation in a Database

A field definition can also carry validation so that impossible data is refused as it is entered. The same checks used in programming apply here.

Field	Suitable validation
Age	Range check — between 11 and 19
StudentID	Length check — exactly 6 characters; presence check
Surname	Presence check — must not be blank
DateOfBirth	Format check — dd/mm/yyyy

6. Designing a Single Table

Given a set of storage requirements, a database definition states each field name, its data type, and which field is the primary key.

Worked example

A school library needs to store, for each book: a unique reference, the title, the author, the year of publication, the price paid, and whether it is currently on loan.

Field name	Data type	Notes
BookRef	Text/alphanumeric	Primary key — unique for every book
Title	Text/alphanumeric
Author	Text/alphanumeric
YearPublished	Integer	A whole number, used in comparisons
Price	Real	Needs a decimal part
OnLoan	Boolean	Only two possible values

With sample data the table looks like this:

BookRef	Title	Author	YearPublished	Price	OnLoan
LIB001	Hard Times	Dickens	1854	7.99	TRUE
LIB002	Silas Marner	Eliot	1861	6.50	FALSE
LIB003	Kim	Kipling	1901	8.25	FALSE

Each column is a field. Each row is a record. BookRef is the primary key, so no two rows can ever be mistaken for one another.

7. Exam Focus

Give a data type from the list, not a programming type. Write "text/alphanumeric", not "string" or "varchar". Write "real", not "float" or "decimal".

Do not invent extra tables. Questions on this topic supply requirements for a single table. Splitting the data across two tables with a foreign key is outside the syllabus and does not answer the question asked.

Use the exact field names given in the question. When a scenario names the data items, reuse those names in your design. Renaming them makes your answer harder to mark and can lose the mark.

Quick self-check

Define field, record and primary key.
State the purpose of a primary key in one sentence.
Explain why a mobile phone number should be stored as text, not integer.
A club stores, for each member: a membership number, name, date joined, fees paid in the year, and whether they are a committee member. Give a suitable data type for each and identify the primary key.
Suggest a suitable validation check for a field storing a percentage mark.

9 Databases