Databases: IGCSE Computer Science Complete Notes

A Global Language for Organised Data

Every country on earth keeps records. Tax authorities in Stockholm, hospital registries in Nairobi, shipping manifests in Singapore - all of them rely on structured collections of data that can be searched, sorted, and updated reliably. The tool that makes this possible is the database, and the language used to query most of the world's databases is SQL. Whether you are studying in London, Lagos, or Lima, the Cambridge IGCSE Computer Science (0478) syllabus expects you to understand how databases are designed, how data types are chosen, what makes a good primary key, and how to write queries that retrieve exactly the information you need.

This topic bridges the gap between abstract data storage and the real systems that power online banking, airline booking, school administration, and e-commerce across every continent. The concepts here are not theoretical curiosities. They are the practical foundation of how modern organisations manage information.

Database Fundamentals

A database is an organised collection of structured data, stored electronically so that it can be easily accessed, managed, and updated. At the IGCSE level, you will work primarily with single-table flat-file databases, though you should understand that real-world systems often use multiple linked tables (relational databases).

Key Terminology

Three terms form the vocabulary of every database discussion:

• Table: The structure that holds all the data. Think of it as a grid - rows and columns - where each column represents a category of information, and each row represents one complete entry.
• Record: A single row in the table. Each record holds all the data about one entity (one student, one product, one transaction).
• Field: A single column in the table. Each field holds one category of data across all records (for example, "Surname" or "DateOfBirth").

Consider a school database tracking student enrolment. The table might look like this:

Here, each row is a record (one student), and each column is a field (StudentID, FirstName, Surname, DateOfBirth, Year Group). The whole structure is the table.

Designing a Single-Table Database

When an exam question asks you to design a database table from a written description, follow these steps:

1. Identify the entities being stored (books, patients, orders).
2. List the fields needed for each entity - what information must be recorded?
3. Choose an appropriate data type for each field.
4. Select a primary key - a field (or combination) that uniquely identifies each record.

Data Types

Every field in a database must be assigned a data type that determines what kind of values it can store and what operations can be performed on it. Choosing the correct data type is a skill that Cambridge examiners test directly. A telephone number stored as an INTEGER would lose its leading zero. A price stored as TEXT could not be used in a SUM calculation. Precision here matters.

Primary Keys

A primary key is a field (or combination of fields) that uniquely identifies each record in a table. No two records can share the same primary key value, and the primary key field cannot be left empty (null).

Why Primary Keys Matter

Without a primary key, the database has no reliable way to distinguish one record from another. Imagine a school table where two students share the name "Maria Garcia." If the table has no unique identifier, searching for Maria Garcia would return both records with no way to tell which is which. The primary key solves this by guaranteeing that every record has a distinct identity.

Primary keys also enable fast retrieval. The database engine builds an index on the primary key, allowing it to locate a specific record without scanning every row in the table.

Natural vs. Artificial Keys

Sometimes a field in the data is naturally unique. An ISBN uniquely identifies a book. A national insurance number uniquely identifies a person in the UK. A passport number uniquely identifies a passport. These are natural keys.

Often, though, no single field in the data is guaranteed to be unique. Names repeat. Dates repeat. Addresses can be shared. In these cases, you create an artificial key - a field whose sole purpose is to provide a unique identifier. StudentID, ProductCode, OrderNumber: these are invented specifically to serve as primary keys.

SQL: Structured Query Language

SQL is the standard language for interacting with databases. From small classroom exercises to vast commercial systems used by companies in Tokyo, Berlin, and Sao Paulo, SQL is the shared vocabulary. The IGCSE syllabus focuses on retrieval queries - statements that extract data from existing tables.

The SELECT Statement

The basic structure of an SQL query is:

SELECT field1, field2 FROM TableName WHERE condition ORDER BY field ASC;

Each clause plays a specific role:

• SELECT - specifies which fields to display. Use * to select all fields.
• FROM - identifies the table to query.
• WHERE - filters the records returned based on one or more conditions. Optional.
• ORDER BY - sorts the results by a specified field, either ASC (ascending, the default) or DESC (descending). Optional.

Comparison and Logical Operators

Aggregate Functions

SQL provides functions that perform calculations across multiple records:

• COUNT(*) - returns the number of records matching the query.
• SUM(field) - returns the total of all values in a numeric field.
• AVG(field) - returns the arithmetic mean of all values in a numeric field.

These are often combined with WHERE to narrow the calculation. For instance, SELECT AVG(Score) FROM Results WHERE Subject = 'Physics' returns the average physics score.

Worked SQL Examples

The examples below use this sample table, Products:

Example 1: Show the names and prices of all accessories, sorted by price from lowest to highest.

SELECT ProductName, Price FROM Products WHERE Category = 'Accessories' ORDER BY Price ASC;

Example 2: Count the number of products that are currently in stock.

SELECT COUNT(*) FROM Products WHERE InStock = TRUE;

Result: 4

Example 3: Show all products costing more than 15.00 that are in stock, sorted by price from highest to lowest.

SELECT * FROM Products WHERE Price > 15.00 AND InStock = TRUE ORDER BY Price DESC;

Example 4: Calculate the average price of all products.

SELECT AVG(Price) FROM Products;

Result: 23.646

Example 5: Find all products whose name starts with "W".

SELECT * FROM Products WHERE ProductName LIKE 'W%';

Worked Exam-Style Questions

Model answer (a):

Note that AnimalID is an artificial primary key created because none of the given fields is guaranteed to be unique. OwnerPhone is stored as TEXT to preserve any leading zeros and because no arithmetic is performed on it.

Model answer (b): The animal's name would not be a suitable primary key because multiple animals could have the same name (two dogs both called "Max"), so it cannot uniquely identify each record.

Model answer (a):

SELECT StudentName, Score FROM Results WHERE Subject = 'Mathematics' AND Score >= 70 ORDER BY Score DESC;

Model answer (b):

SELECT COUNT(*) FROM Results WHERE Grade = 'A';

Model answer (c):

SELECT * FROM Results WHERE Score < 40 AND (Subject = 'English' OR Subject = 'Science');

Common Mistakes

1. Choosing INTEGER for phone numbers or postcodes. These values contain leading zeros or letters that INTEGER cannot preserve. Always use TEXT for identifiers that are not used in arithmetic.
2. Using a non-unique field as a primary key. A surname, a date of birth, or a city name can repeat across records. If no naturally unique field exists, create an artificial key.
3. Forgetting ORDER BY defaults to ASC. If a question asks for results "in ascending order," ASC is technically optional but good practice to state. If the question says "highest first" or "largest to smallest," you must write DESC.
4. Confusing COUNT with SUM. COUNT returns how many records match; SUM adds up the values in a numeric field. "How many students scored above 80" needs COUNT. "What is the total of all scores" needs SUM.
5. Omitting quotes around text values in SQL. String comparisons require single quotes: WHERE Subject = 'Mathematics'. Numeric comparisons do not: WHERE Score >= 70. Mixing these up causes syntax errors.
6. Ignoring brackets with AND/OR. SQL evaluates AND before OR. The query WHERE Score < 40 AND Subject = 'English' OR Subject = 'Science' does not do what most students intend. Use brackets to group the OR: WHERE Score < 40 AND (Subject = 'English' OR Subject = 'Science').

Self-Check Questions

1. A bookshop stores data about its books. Each record includes the ISBN, title, author, price, and number of copies in stock. Which field should be the primary key? Justify your answer.
2. A field stores whether a library book is currently on loan. What data type should this field be, and why?
3. Write an SQL query to display the titles and authors of all books from the table Books where the price is less than 10.00, sorted alphabetically by title.
4. Explain the difference between SELECT COUNT(*) FROM Orders WHERE Status = 'Shipped' and SELECT SUM(Quantity) FROM Orders WHERE Status = 'Shipped'.
5. A database stores employee records. The fields are: EmployeeID, Name, Department, Salary, StartDate. Write an SQL query to find the average salary of employees in the "Engineering" department.