Relational databases and structured query: OxfordAQA IGCSE Computer Science Complete Notes

A skill shared across every education system

Whichever country's school system you compare OxfordAQA against, relational databases and SQL tend to appear on the syllabus in a broadly similar form, because they describe a genuinely universal way of organising data that any organisation, anywhere, ends up needing. A school's student records, a hospital's patient files, a shop's stock list: all of them are, underneath, relational databases. This oxfordaqa igcse computer science relational databases and structured query guide covers the concept of a database, how to design one properly, and how to query it using SQL.

Relational databases

A database is an organised collection of data. A relational database organises that data specifically into tables, with relationships between those tables, and this structure is what gives the topic its name. Five core concepts anchor this section, and you should be able to define each precisely.

The specification also notes that terms such as entity, attribute and entity identifier are more commonly used when discussing an abstract model of a database, while table, record, field and primary key are used for both the abstract model and its actual implementation; on this course, use the second set of terms consistently in both contexts.

A relational database's main advantage over storing everything in one large, unstructured table is that it eliminates data inconsistency and data redundancy. If a student's name were repeated in every single subject record rather than stored once and referenced by a foreign key, correcting a spelling mistake would mean updating dozens of separate records, and missing even one of them would leave the database internally inconsistent. Splitting data across related tables, connected by keys, avoids that problem structurally.

Database design

You should be able to choose appropriate data types, and where relevant appropriate lengths, for each field in a table; select a suitable primary key for a table; and produce a full design for a relational database from a written description of a scenario. Exam scenarios will not exceed three tables, which keeps the design task manageable, but you should still be comfortable identifying which fields in each table need to become foreign keys to connect the tables correctly.

Table designs are written in a standard notation: TableName, followed by its fields in brackets, with the primary key underlined. Comparing this to how the same relationships might be drawn as an entity-relationship diagram in other systems, the underlined-field notation used here is simply a more compact, text-based way of recording the same design decisions.

Worked example: designing a simple two-table database

Consider a scenario: a small library needs to record its books and the members who borrow them, including which member currently has which book.

Member (MemberID, FirstName, LastName, Email)
Book (ISBN, Title, Author, BorrowedByMemberID)

Here, MemberID is the primary key of the Member table, and ISBN is the primary key of the Book table. BorrowedByMemberID in the Book table is a foreign key, referring back to MemberID in the Member table, and it is this foreign key that establishes the relationship between the two tables, letting you work out which member currently holds any given book.

Structured query language (SQL)

SQL is how you actually retrieve, insert, update and delete data stored in a relational database, and four command types matter on this specification.

Retrieving data with SELECT, FROM and WHERE

SELECT Title, Author
FROM Book
WHERE BorrowedByMemberID = 1042

SELECT names the fields you want returned, FROM names the table those fields come from, and WHERE filters the records down to only those matching a given condition. Exam questions will require data extracted from no more than two tables for any single query, so practise queries that join exactly two tables together using a shared key before attempting anything more elaborate.

Adding data with INSERT INTO

INSERT INTO Member (MemberID, FirstName, LastName, Email)
VALUES (1043, Maria, Costa, maria.costa@example.com)

Only the form of INSERT INTO that specifies every value in a new record is required for this specification, so make sure your practice always lists a complete record, in the same field order as the table was defined, rather than a partial one.

Editing data with UPDATE, and removing it with DELETE FROM

UPDATE Member
SET Email = maria.costa2@example.com
WHERE MemberID = 1043

DELETE FROM Member
WHERE MemberID = 1043

Notice that both UPDATE and DELETE FROM rely on a WHERE clause to target the correct record precisely; leaving that clause out, even by accident, would apply the change to every single record in the table, so treat the WHERE clause as mandatory in practice even in scenarios where the syntax alone would technically allow you to omit it.

Worked example: extracting data across two tables

Exam questions frequently ask for a query that draws on two related tables at once, using a foreign key to connect them, which is a slightly more demanding but genuinely common task.

SELECT Title
FROM Book
WHERE BorrowedByMemberID = 1042

This single-table version tells you which books a specific member currently holds, but it only shows the member's ID number, not their name. To get a readable answer that names the member directly rather than just their identifying number, a query would need to combine information held in both the Book table and the Member table, matching records where Book.BorrowedByMemberID equals Member.MemberID. This kind of two-table comparison, using a shared key to connect otherwise separate rows of data, is precisely why the foreign key relationship established at the design stage matters so much: without it, there would be no reliable way to connect a book back to the member who borrowed it at all.

Common mistakes to avoid

• Choosing a primary key that is not actually guaranteed to be unique, such as a person's name, rather than a dedicated identifier field.
• Forgetting to include the foreign key that connects two tables when designing a database from a scenario.
• Writing an UPDATE or DELETE FROM statement without a WHERE clause, which would affect every record rather than the intended one.
• Attempting to extract data from more than two tables in a single SQL query, beyond what this specification actually requires.
• Confusing a field, a column of data shared across every record, with a record, a single row representing one specific item.

Self-check questions

1. Define the terms table, record, field, primary key and foreign key in your own words.
2. Design a two-table database for a scenario in which a school records classes and the teacher assigned to each class.
3. Write a SELECT statement that retrieves the names of all members who borrowed a book with ISBN 9780134092669.
4. Write an INSERT INTO statement that adds a new book to the Book table above.
5. Write an UPDATE statement that corrects a misspelled author name for a specific ISBN.

Design and querying are best practised together rather than in isolation, because a poorly designed table makes even a simple SQL query awkward, while a well designed one makes almost every query straightforward. These oxfordaqa igcse computer science revision notes on relational databases and structured query are built around that connection deliberately, and working through fresh oxfordaqa igcse computer science practice questions that combine both design and SQL in a single scenario is the most realistic way to prepare.

Reading a design before you query it

A useful exam habit, before writing a single line of SQL, is to read the table design notation carefully and sketch out, in your own words, what each table represents and how the tables connect. Given Member (MemberID, FirstName, LastName, Email) and Book (ISBN, Title, Author, BorrowedByMemberID), a quick read tells you this models a one-to-many relationship: one member can borrow many books over time, since many rows in the Book table could, in principle, share the same BorrowedByMemberID value, while each book, identified by its own unique ISBN, is only ever linked back to a single member at any given moment. Spotting that shape before you attempt a query saves time, because it tells you immediately which table needs to be filtered and which table needs to be looked up to answer a given question about the data.

Where this fits internationally

Because SQL itself is an internationally standardised language used far beyond any one school system, the skills in this relational databases and structured query oxfordaqa igcse guide transfer directly into further study or employment regardless of which country you eventually work in. Keep this page among your oxfordaqa igcse computer science notes for the igcse 9210 relational databases and structured query content, and treat every command and design rule above as fully oxfordaqa igcse computer science explained, ready to be applied confidently to a scenario you have never seen before, which is exactly what the exam will ask you to do.