IGCSE Computer Science · Area 2 · Algorithms, programming and logic

Databases

A database stores data in a table, and every question on this topic is about the shape of that table. You need to turn a data storage requirement into fields and records, choose a sensible data type for each field, pick a field that can act as the primary key, and then read, complete or predict the output of a structured query. Most marks are lost by choosing the wrong data type, by offering a field that cannot be unique as the primary key, or by forgetting that WHERE decides which records come back while ORDER BY only changes their order.

Not started

Syllabus coverage

  • Define a single-table database from given data storage requirements, including fields, records and validation
  • Suggest suitable basic data types, including text/alphanumeric, character, Boolean, integer, real and date/time
  • Understand the purpose of a primary key and identify a suitable primary key for a given database table
  • Read, understand and complete structured query language (SQL) scripts to query data stored in a single database table
  • SQL is limited to SELECT, FROM, WHERE, ORDER BY DESCENDING, ORDER BY ASCENDING, SUM, COUNT, AND and OR
  • Identify the output given by an SQL statement that will query the given contents of a database table

Part of the IGCSE Computer Science syllabus 0478.

Databases: fields, records and the primary keyA field is a column, a record is a row, and the primary key is the field that identifies a record uniquely.Pick each field's data type from the data that goes in it - text, number, date or Boolean.A single-table database: fields across the top, records down the sideStudentIDFirstNameYearGroupClub1041Amara10Athletics1042Ben10Chess1043Chidi11Athletics1044Dara11DebateThe primary key (StudentID) is unique to one record, so no two rows can be confused.A field is one column; a record is one row. Choose the data type from the data: text, number, date or Boolean.
A single-table database drawn with its fields across the top and its records down the side: a field is one column and a record is one row, the StudentID field is marked as the primary key that identifies each record uniquely, and the fields are annotated with the data type each one should use.

What a single-table database is

A database is a collection of data that is organised so it can be searched, sorted and updated quickly. A single-table database holds all of its data in one table: a grid in which each field is a column and each record is a row. Every record describes one thing - one product, one pupil, one booking - and every record has a value in every field, so the table keeps the same shape all the way down.

Take a stock table for a shop. Its fields might be ProductCode, ProductName, Price, QuantityInStock, InStock and LastOrdered. Each record is one product: P101, Kettle, 24.50, 12, TRUE, 12/03/2026. Nothing is stored twice, and every record follows the same pattern, which is exactly what makes the table searchable and sortable.

A field is one item of data about the thing the record describes, and it is defined once for the whole table: the field Price is a column, and it holds the price of every product. A record is one complete row, made up of one value from each field. The design of the table - the set of fields, with a chosen data type for each - is settled before any data is entered.

TermWhat it meansIn the stock table
FieldOne item of data about the thing a record describes. It is a column of the table and is defined once for every record.ProductCode, ProductName, Price, QuantityInStock
RecordOne complete row of the table, holding one value in every field, describing one thing.P101, Kettle, 24.50, 12
TableThe whole grid of fields and records. A single-table database stores all of its data here and nowhere else.Every product the shop sells
Data storage requirementThe description of what data must be stored, which you turn into a list of fields with a data type for each.'we need each product's code, name, price and how many we have'
A single-table database has no links to any other table, so every field you need must be in this one table. Read the storage requirement and write a field for every item of data it names.

Designing the table from a data storage requirement

A question that asks you to define a single-table database from given data storage requirements gives you a description of what has to be stored and expects a list of fields. Work through the description and write down one field for each separate item of data it mentions. If the requirement says 'the library must record each book's title, author, the date it was borrowed and whether it has been returned', that is four fields: Title, Author, DateBorrowed and Returned.

Each field holds one item of data. Do not put two things in one field: a field called NameAndAddress cannot be sorted by surname and cannot be searched by town. Split it into separate fields - Surname, FirstName, Street, Town, Postcode - and the table becomes far more useful.

Field names are single words with no spaces, written so that anyone can tell what they hold, such as DateBorrowed. The order of the fields makes no difference to the database, but it makes a difference to you: a sensible order makes the table much easier to read when you are working out the output of a query by hand.

Finally, decide for each field whether a value is required. A field that every record must have cannot be left empty, and that is where validation does its work.

  • One field for every separate item of data the storage requirement names.
  • One item of data per field: split a combined field such as NameAndAddress into separate fields.
  • Field names are single words with no spaces, chosen so the meaning is obvious.
  • The set of fields, with a data type for each, is the design of the table - settle it before any data is entered.
Count the items of data in the requirement and count your fields. If the two numbers do not match, you have either combined two pieces of data into one field or missed one out.

Choosing a suitable data type

Every field has a data type, which decides what kind of value it can hold and what can be done with it. The syllabus names six: text/alphanumeric, character, Boolean, integer, real and date/time. Choosing the wrong one is the commonest way to lose marks on this topic, so decide from the data you are actually storing.

Text/alphanumeric holds any combination of letters, digits and symbols and is never used in arithmetic: a product name, an address, a postcode. Character holds exactly one character: a single letter grade, a one-letter discount code, Y or N. Boolean holds one of two values, TRUE or FALSE, and nothing else: whether an item is in stock, whether a book has been returned.

Integer holds whole numbers only: a quantity in stock, a number of pupils, a year. Real holds numbers with a fractional part, so it is used for anything measured or priced: a price of 24.50, a weight of 1.5, a temperature. Date/time holds a date, a time, or both, kept in a fixed format so the database can put records in date order and work out how long ago something happened.

Two choices catch people out. A price is a real, not an integer, because 24.50 has a fractional part and an integer field would lose the 50p. A telephone number is text/alphanumeric, not an integer, because it is a string of digits that is never added up and it may begin with a zero, which an integer would drop.

Data typeWhat it holdsSuitable for
text/alphanumericAny letters, digits and symbols, in any combinationProductName, Address, TelephoneNumber
characterExactly one characterDiscountCode A, Grade B, a Y/N flag
BooleanOne of two values: TRUE or FALSEInStock, Returned, HasPaid
integerA whole number, positive or negativeQuantityInStock, NumberOfPupils, YearOfBirth
realA number with a fractional partPrice, Weight, Temperature
date/timeA date, a time, or bothDateBorrowed, TimeOfSale, LastOrdered
Ask what you will do with the field. If you will add it up or compare it as a number it is integer or real; if you will only read, list or search it, it is text/alphanumeric.

The primary key

Every record in a table has to be findable, and that is the job of the primary key. A primary key is a field that uniquely identifies each record: no two records may hold the same value in that field. In the stock table, ProductCode is the primary key, because every product has its own code and no two products share one.

The primary key must also be present in every record - it can never be left empty - and it must not change, because it is how the database refers to that record. A value that is unique today but might be edited tomorrow makes a poor primary key.

Identifying a suitable primary key means testing a candidate field against those rules. ProductName fails, because two different products can share a name: a shop may sell two products both called Kettle. Price fails. DateBorrowed fails, because two books borrowed on the same day would clash. If the table has no field that is naturally unique, the answer is to add one, such as a PupilID or a booking reference, and use that.

Without a primary key a table can hold duplicate records, and the database has no reliable way to pick out one record from all the others. That is the purpose of the key: uniqueness, and the ability to identify a record.

  • A primary key uniquely identifies each record: no two records may share the same value in that field.
  • It must have a value in every record, and that value must not change.
  • ProductCode or PupilID works because the values are unique; ProductName, Price and DateBorrowed do not, because two records could share them.
  • If no field is naturally unique, add one and make that the primary key.
Test every candidate primary key by asking whether two records could ever share that value. If they could, it is not a primary key.

Validation when the data is entered

Defining a single-table database also means deciding what counts as acceptable data. Validation is the automatic check made as data is entered, to make sure it is sensible before it is stored. It does not make the data correct - it only rejects data that breaks a rule - but it stops obvious errors before they reach the table.

The syllabus names the checks you need. A range check requires the value to lie between limits, such as a quantity between 0 and 500. A length check requires a set number of characters, such as a product code of exactly four. A type check requires the right data type, so no letters in a quantity field. A presence check requires the field not to be left empty. A format check requires the value to follow a pattern, such as a date written as DD/MM/YYYY. A check digit is an extra digit calculated from the others and checked when the value is entered, which catches typing errors in a long number such as a barcode.

Validation is worth marks as an example, not just as a name. 'A range check stops a quantity of 700 being stored' scores; 'a range check' on its own often does not. Say which field the check is applied to and what it would reject.

Validation checkWhat it doesExample for the stock table
Range checkThe value must lie between a lowest and a highest acceptable valueQuantityInStock must be between 0 and 500
Length checkThe value must have a set number of charactersProductCode must be exactly 4 characters
Type checkThe value must be of the right data typeQuantityInStock must be a whole number, not the word twelve
Presence checkThe field must contain somethingProductName cannot be left blank
Format checkThe value must match a patternLastOrdered must be in the form DD/MM/YYYY
Check digitAn extra digit calculated from the others and checked on entryThe final digit of a 13-digit barcode
Validation catches impossible data, not wrong data. A price of 24.50 typed as 24.05 passes every check and is still wrong, because 24.05 is a perfectly valid price.

Reading a structured query: the SQL clauses

A structured query language (SQL) statement asks a question of the data in a table. The syllabus limits you to a single table and to nine keywords, so the statements you meet are short - but you must know exactly what each part does, because questions ask you to read a script, to complete a script, and to say what a script will output.

SELECT names the fields you want in the answer. FROM names the table they come from. WHERE decides which records are returned: only the records for which the condition is true are included, and the rest are left out altogether. ORDER BY ... ASCENDING and ORDER BY ... DESCENDING put the records that survived WHERE into order by one field, smallest or first in the alphabet first, or largest or last first.

AND and OR join two conditions inside a WHERE clause. A record is returned only if both conditions are true when AND is used, and if either condition is true when OR is used. SUM adds up the values in a numeric field and COUNT counts how many records were selected; both return a single number instead of a list of records.

Read a statement outwards from the keywords: FROM tells you the table, WHERE tells you which records, SELECT tells you which fields of those records are shown, and ORDER BY tells you the sequence they appear in. Working through those four steps in writing is how you avoid a wrong answer when the output has several records. Cambridge writes the sort direction in full, as ASCENDING or DESCENDING; some database packages accept the short forms ASC and DESC, but write the full words in the exam.

KeywordWhat it doesExample
SELECTNames the fields to show in the output; * means every fieldSELECT ProductName, Price
FROMNames the table the data comes fromFROM Stock
WHEREKeeps only the records that satisfy the conditionWHERE Price > 20.00
ANDBoth conditions must be true for the record to be returnedWHERE Price > 20.00 AND QuantityInStock > 0
OREither condition may be true for the record to be returnedWHERE QuantityInStock = 0 OR Price < 5.00
ORDER BY ... ASCENDINGSorts the records by a field, smallest or first in the alphabet firstORDER BY Price ASCENDING
ORDER BY ... DESCENDINGSorts the records by a field, largest or last in the alphabet firstORDER BY Price DESCENDING
SUMAdds up the values of a numeric field across the selected recordsSELECT SUM(QuantityInStock)
COUNTCounts how many records were selectedSELECT COUNT(ProductCode)
WHERE changes which records you get; ORDER BY never changes which records you get, only the order they appear in. A question that says 'state the output' is testing both.

Working out the output of a query

Take a table and a statement, and work it out on paper in four steps. The Stock table below holds four records, and the statement is: SELECT ProductName, Price FROM Stock WHERE Price > 20.00 AND QuantityInStock > 0 ORDER BY Price DESCENDING.

Step 1, the table. FROM Stock, so all four records are in play. Step 2, the records. WHERE asks for records whose price is greater than 20.00 and whose quantity is greater than 0. P101 Kettle at 24.50 with 12 in stock passes both tests. P104 Kettle at 31.00 with 4 in stock passes both. P103 Blender at 39.99 has a quantity of 0, so the AND fails and it is out. P102 Toaster at 18.00 fails the price test, so it is out as well.

Step 3, the fields. SELECT ProductName, Price, so each surviving record is shown as its name and its price only - the code and the quantity are not printed. Step 4, the order. ORDER BY Price DESCENDING, so the largest price comes first. The output is two records: Kettle, 31.00 and then Kettle, 24.50.

SUM and COUNT are worked out the same way and then reduced to a single number. SELECT COUNT(ProductCode) FROM Stock WHERE QuantityInStock > 0 counts the records that pass the condition, which is 3 here: P101, P102 and P104. SELECT SUM(QuantityInStock) FROM Stock adds the quantity field across every record: 12 + 6 + 0 + 4 = 22.

ProductCodeProductNamePriceQuantityInStock
P101Kettle24.5012
P102Toaster18.006
P103Blender39.990
P104Kettle31.004
Write the four steps down. Candidates who lose marks on output questions have almost always skipped the WHERE test for one record, or ignored the ORDER BY once they had the right records.

Key terms you must be able to define

Single-table database
A database in which all of the data is held in one table, with no links to any other table.
Field
One item of data about the thing a record describes. A field is a column of the table, and it is defined once for every record.
Record
One complete row of a table, made up of one value from each field, describing one thing such as one product or one pupil.
Data type
The kind of value a field may hold: text/alphanumeric, character, Boolean, integer, real or date/time.
Primary key
A field that uniquely identifies each record in a table. No two records may share the same value, the value must be present in every record, and it must not change.
Validation
An automatic check made as data is entered, to make sure it is sensible before it is stored. It cannot tell whether the data is actually correct.
Range check
A validation check that the value lies between a lowest and a highest acceptable value, for example a quantity between 0 and 500.
Length check
A validation check that the value has a set number of characters, for example a product code of exactly four characters.
Type check
A validation check that the value is of the right data type, for example no letters in a field that must hold a whole number.
Presence check
A validation check that the field has not been left empty.
Format check
A validation check that the value follows a pattern, for example a date written as DD/MM/YYYY.
Check digit
An extra digit calculated from the other digits of a number and checked when the number is entered, used to catch typing errors.
Structured query language (SQL)
The language used to ask questions of the data in a database. On this syllabus the statements are limited to a single table.
SELECT
The SQL keyword that names the fields to be shown in the output of the query.
FROM
The SQL keyword that names the table the data is taken from.
WHERE
The SQL keyword that decides which records are returned: only the records for which the condition is true.
ORDER BY
The SQL keyword that sorts the selected records by a field, either ASCENDING or DESCENDING.
SUM
The SQL function that adds up the values of a numeric field across the selected records and returns one number.
COUNT
The SQL function that counts how many records were selected and returns one number.

Applied to real systems

Cambridge sets 0478 in systems you already use - a phone, a school network, a supermarket till, a website, a bank. Use these as models for how to apply the theory to a context you have not seen before, then write your own.

In a supermarket

The stock table uses ProductCode as its primary key, because every product has its own code and no two products share one. Price is a real field so that 24.50 is stored exactly, QuantityInStock is an integer because it is a whole number of items, and InStock is Boolean because the answer is only ever TRUE or FALSE. A range check on QuantityInStock stops a mistyped 700 being stored as if the shop had seven hundred kettles.

In a school office

A pupil table has PupilID as its primary key, with Surname, FirstName, DateOfBirth, YearGroup and HasPaid. Surname is text/alphanumeric because it is only ever read and searched, never calculated with; DateOfBirth is date/time so the records can be sorted into age order; HasPaid is Boolean. A query such as SELECT COUNT(PupilID) FROM Pupil WHERE YearGroup = 10 AND HasPaid = TRUE answers 'how many Year 10 pupils have paid?' in one line.

At the cinema

One table holds every booking, with BookingReference as the primary key, ShowingTime as date/time, SeatNumber as text/alphanumeric and Paid as Boolean. To find the takings for a showing, the manager queries SELECT SUM(TicketPrice) FROM Booking WHERE ShowingTime = 19:30 - the SUM adds a real field across the selected records and returns a single number. BookingReference has to be unique, because two customers in different seats at the same showing must not share a reference.

In a gym

MemberID is the primary key, DateJoined is date/time, MembershipFee is real and Active is Boolean. ORDER BY DateJoined ASCENDING lists the members who joined first, which is what the gym needs when it is deciding who gets the ten-year discount. A format check on DateJoined rejects a date typed as 3/3/26 when the table expects DD/MM/YYYY, and a presence check stops a record being saved with no surname.

In a hospital

The appointments table holds AppointmentID as the primary key, PatientName and Clinic as text/alphanumeric, AppointmentDate as date/time and Attended as Boolean. SELECT COUNT(AppointmentID) FROM Appointment WHERE Attended = FALSE gives the number of patients who did not turn up, and a length check on AppointmentID rejects any reference that is not the expected number of characters before the record is saved.

Flashcards

Click a card to flip it. Mark honestly — the tracker uses this.

Q1In a database table, what is a field and what is a record?
A field is a column - one item of data, defined once for the whole table. A record is a row - one complete set of values, describing one thing.
Q2What is a single-table database?
A database that holds all of its data in one table, with no links to any other table.
Q3Which data type would you use for a price such as 24.50, and why?
Real, because the value has a fractional part. An integer can only hold whole numbers, so it would lose the 50p.
Q4Which data type holds only TRUE or FALSE?
Boolean. It is used for a field with exactly two possible values, such as whether an item is in stock or whether a book has been returned.
Q5Give the rules a field must obey to be a suitable primary key.
The value must be unique to each record, it must be present in every record, and it must not change.
Q6Why is ProductName a poor primary key for a stock table?
Two different products can share a name, so the field would not identify each record uniquely. A code such as ProductCode would.
Q7What is validation, and what can it not do?
It is an automatic check that data entered into a field is sensible. It cannot tell whether the data is actually correct - a valid but wrong value passes it.
Q8Name four validation checks that could be applied to a stock table.
Range check on QuantityInStock (0 to 500), length check on ProductCode (exactly 4 characters), type check (whole number), presence check (ProductName cannot be blank).
Q9What does WHERE do in an SQL statement?
It decides which records are returned: only the records for which the condition is true. Records that fail the condition are left out of the output.
Q10What is the difference between AND and OR in a WHERE clause?
AND returns a record only if both conditions are true. OR returns a record if either condition is true.
Q11What does ORDER BY Price DESCENDING do?
It puts the selected records in order of price with the largest price first. ASCENDING would put the smallest price first.
Q12What is the difference between SUM and COUNT in SQL?
SUM adds up the values in a numeric field; COUNT counts how many records were selected. Both return a single number.
0 of 12 marked as known

Exam-style questions

7 questions, 23 marks. Write your answer, then reveal the mark scheme and tick each point you actually made. Answering honestly here is what makes the rest of the site useful.

StateAO11 mark
State the name given to a single column of a database table.

Mark scheme — tick each point you actually made

Examiner tip: Learn the pair of terms together: a column is a field and a row is a record. Mixing them up costs easy marks on this topic.
IdentifyAO12 marks
A table called Pupil has the fields PupilID, Surname, FirstName, DateOfBirth, YearGroup and HasPaid. Identify a suitable primary key for this table and give one reason for your choice.

Mark scheme — tick each point you actually made

Examiner tip: Name the field first, then give the reason. 'It is unique' is the reason the examiner is looking for, and it is worth saying why the other fields fail.
SuggestAO23 marks
A garage stores data about each car it sells in a table with the fields Registration, Make, Price, NumberOfDoors and InStock. Suggest a suitable data type for the Price field, for the NumberOfDoors field and for the InStock field.

Mark scheme — tick each point you actually made

Examiner tip: Give the data type and the reason in the same sentence. Each of these three is decided by what the data looks like, not by the name of the field.
GiveAO24 marks
A shop keeps its stock in a single table called Stock, with the fields ProductCode, ProductName, Price and QuantityInStock. Give the SQL statement that returns the name and price of every product with a price greater than 20.00, with the most expensive product first. The statement must use SELECT, FROM, WHERE and ORDER BY.

Mark scheme — tick each point you actually made

Examiner tip: SELECT names the fields, FROM names the table, WHERE gives the condition and ORDER BY gives the sort. DESCENDING is what puts the most expensive product first, and the syllabus writes the keyword in full.
GiveAO25 marks
The table Stock contains these four records, listed as (ProductCode, ProductName, Price, QuantityInStock): (P101, Kettle, 24.50, 12); (P102, Toaster, 18.00, 6); (P103, Blender, 39.99, 0); (P104, Kettle, 31.00, 4). Give the output of this SQL statement, including the order in which the records are returned. SELECT ProductName, Price FROM Stock WHERE Price > 20.00 AND QuantityInStock > 0 ORDER BY Price DESCENDING

Mark scheme — tick each point you actually made

Examiner tip: Test WHERE against every record before you do anything else, and remember that AND needs both conditions to be true. Then apply ORDER BY to the records that survived.
DescribeAO14 marks
A shop is designing the Stock table described above. Describe two validation checks that could be applied to the data as it is entered, and in each case give an example of data that would be rejected.

Mark scheme — tick each point you actually made

Examiner tip: A format check on the date field and a check digit on the product code would also be accepted. Whatever check you choose, name the field it protects and give the value it would reject.
ExplainAO24 marks
Explain why a shop's stock data is better stored in a database than kept on a printed stock sheet.

Mark scheme — tick each point you actually made

Examiner tip: Anchor each reason in something the syllabus names - the primary key, the query, validation - and say what it saves the shop. Vague answers about databases being 'better' score nothing.

Exam technique for this topic

  • Choose the data type from the data itself and say why in the same breath: price -> real, because it has a fractional part; quantity -> integer, because it is a whole number; has the book been returned? -> Boolean, because there are only two possible values.
  • When you are asked to identify a primary key, name the field and then give the reason in one sentence: the value is unique to each record, so no two records can be confused. Never offer a field such as Name, Price or Date, because two records can share those values - if nothing in the table is unique, add a new field such as a code or an ID and use that.
  • For an output question, work through the statement in the order FROM, WHERE, SELECT, ORDER BY, and write down which records survive WHERE before you think about anything else. Then apply the order to the records you have left.
  • Write the SQL the way the syllabus does: ORDER BY Price DESCENDING, with the keyword in full. A correct query with a made-up keyword loses the mark.
  • Validation answers want an example attached: 'a range check on QuantityInStock, so a quantity of 700 is rejected' rather than just 'a range check'.

Why students lose marks here

A student wroteA student wrote: 'ProductName should be the primary key, because every product has a name.'
Why it loses marksA primary key must be unique to each record, and a name does not guarantee that. Two different products can both be called Kettle, and then two records would hold the same key value, which is exactly what a primary key must prevent.
The answer that scoresProductCode is the primary key, because every product has its own code and no two records share one. ProductName cannot be the key: a shop may sell two different products that share a name, so the field would not identify one record uniquely. A primary key must also have a value in every record and must never change, which a product code satisfies.
A student wroteA student wrote: 'Price should be stored as an integer, because a price is a number.'
Why it loses marksAn integer field holds whole numbers only. A price such as 24.50 has a fractional part, so an integer field would lose the 50p and the shop could not add up its takings correctly.
The answer that scoresPrice must be a real, because it has a fractional part: 24.50, 18.00 and 39.99 are all prices. Integer is right for a whole-number field such as QuantityInStock. The same test explains why a telephone number is text/alphanumeric rather than integer - it is never added up, and an integer would drop a leading zero.
A student wroteA student wrote: 'Validation makes sure the data stored in the database is correct.'
Why it loses marksValidation only checks the data against a rule, so it rejects impossible values. It cannot tell whether a sensible value is the right one, which is why it is often said that validation checks the data but does not verify it.
The answer that scoresValidation checks that data is sensible before it is stored: a range check rejects a quantity of 700, a length check rejects a product code of three characters and a presence check rejects an empty name field. It cannot make the data correct, because a price of 24.50 typed as 24.05 passes every check and is still the wrong price.
A student wroteA student wrote: 'SELECT ProductName FROM Stock ORDER BY Price DESCENDING gives the products from cheapest to most expensive.'
Why it loses marksDESCENDING means largest first, so the most expensive product comes at the top of the output. The student has read DESCENDING as 'in order' rather than as 'in descending order'.
The answer that scoresORDER BY Price DESCENDING puts the records in order of price with the largest price first, so the most expensive product is at the top of the output. ORDER BY Price ASCENDING would give the cheapest first. The WHERE clause, not the ORDER BY, is what decides which records appear at all.
A student wroteA student wrote: 'SELECT COUNT(Price) FROM Stock gives the total of all the prices in the table.'
Why it loses marksCOUNT counts records; SUM adds values. The student has used COUNT where the question needed a total, so the answer would be the number of records rather than the sum of the prices.
The answer that scoresSELECT SUM(Price) FROM Stock adds up the price field and gives the total. SELECT COUNT(Price) FROM Stock counts how many records were selected and gives a number of records. Both return a single number rather than a list, which is why they are easy to confuse.