SQL & Databases — Complete A Level Computer Science Guide (AQA, OCR, Cambridge)

Databases and SQL is a topic that divides A Level Computer Science students. Those who understand the underlying theory — relational models, normalisation, keys — tend to find SQL comes naturally. Those who skip to SQL without the theory often struggle with more complex questions.

This guide covers everything: from entity-relationship diagrams through to complex JOIN queries, and includes the normalisation theory that A Level examiners love to test.

---

Key database concepts

What is a relational database?

A relational database stores data in tables (also called relations). Each table:

• Has a name (e.g., Students, Courses)
• Consists of rows (records) and columns (fields/attributes)
• Has a primary key — a field or combination of fields that uniquely identifies each row

The power of relational databases is the ability to link tables together using foreign keys, eliminating data redundancy.

Key terminology

Term	Definition
Attribute	A column in a table; a property of an entity
Record / Tuple	A row in a table; one instance of an entity
Primary key	A unique identifier for each record; never null
Foreign key	A field in one table that references the primary key of another table
Candidate key	Any field (or combination) that could serve as a primary key
Composite key	A primary key made up of two or more fields
Entity	A “thing” about which data is stored (e.g., Student, Course, Order)

---

Entity-relationship (E-R) diagrams

An E-R diagram shows the relationships between entities before you design the database tables.

Cardinality (relationship types)

One-to-one (1:1) — Each record in Table A corresponds to exactly one record in Table B. Example: Person and Passport

One-to-many (1:M) — One record in Table A can relate to many records in Table B. Example: Teacher and Students (one teacher has many students, each student has one form teacher)

Many-to-many (M:M) — Records in Table A can relate to many records in Table B, and vice versa. Example: Students and Courses (a student takes many courses; a course has many students)

Important: Many-to-many relationships cannot be directly implemented in a relational database. They must be resolved by creating a junction table (also called a link table or bridge table).

Resolving a many-to-many relationship

Before: Students ↔ Courses (M:M — problematic)

After:

• Students (StudentID, Name, …)
• Enrolments (StudentID, CourseID, DateEnrolled, Grade) ← junction table
• Courses (CourseID, CourseName, …)

The junction table Enrolments has a composite primary key of (StudentID, CourseID). Both fields are also foreign keys.

---

Normalisation

Normalisation is the process of organising a database to reduce data redundancy and improve data integrity. There are three forms you need to know for A Level.

Unnormalised form (UNF)

A table with repeating groups — data that isn’t yet structured for a relational database.

Example:

OrderID | CustomerName | CustomerEmail | Items
--------|--------------|---------------|------
001     | Alice Smith  | alice@ex.com  | Book, Pen, Ruler
002     | Bob Jones    | bob@ex.com    | Book

Problems: the Items field contains multiple values; customer data is repeated if the same customer places multiple orders.

First Normal Form (1NF)

Rules:

1. Every cell contains a single atomic value (no lists)
2. There are no repeating groups
3. Each row has a unique identifier

1NF table:

OrderLineID | OrderID | CustomerName | CustomerEmail | Item
------------|---------|--------------|---------------|-----
1           | 001     | Alice Smith  | alice@ex.com  | Book
2           | 001     | Alice Smith  | alice@ex.com  | Pen
3           | 001     | Alice Smith  | alice@ex.com  | Ruler
4           | 002     | Bob Jones    | bob@ex.com    | Book

This solves the repeating groups problem but introduces redundancy (CustomerName/Email repeated).

Second Normal Form (2NF)

Applies only when there is a composite primary key.

Rule: Must be in 1NF, and every non-key attribute must depend on the whole primary key (not just part of it).

If some attribute depends only on part of the composite key, that’s a partial dependency — remove it.

In the example above, if PK is (OrderID, Item), then CustomerName depends only on OrderID (partial dependency). Move customer data to its own table:

Orders:   OrderID, CustomerID, ...
Customers: CustomerID, CustomerName, CustomerEmail
OrderLines: OrderID, Item, Quantity, ...

Third Normal Form (3NF)

Rule: Must be in 2NF, and no non-key attribute should depend on another non-key attribute (no transitive dependencies).

Example of a transitive dependency:

StudentID | CourseName | TeacherID | TeacherName | Room

TeacherName and Room depend on TeacherID, not directly on StudentID. This is a transitive dependency.

3NF solution:

Students: StudentID, CourseName, TeacherID
Teachers: TeacherID, TeacherName, Room

Summary of normal forms

Form	Rule
1NF	Atomic values, no repeating groups, unique rows
2NF	1NF + no partial dependencies (all non-key attributes depend on the whole primary key)
3NF	2NF + no transitive dependencies (non-key attributes don’t depend on other non-key attributes)

Exam tip: In normalisation exam questions, always state why a table is not in a given form and exactly which dependency violates it. “There is a transitive dependency: TeacherName depends on TeacherID, not StudentID” scores marks. “It has redundancy” does not.

---

SQL

SQL (Structured Query Language) is the language used to interact with relational databases. The four key operations are SELECT, INSERT, UPDATE, and DELETE.

SELECT — querying data

-- Select all columns from Students table
SELECT * FROM Students;

-- Select specific columns
SELECT FirstName, LastName, DateOfBirth FROM Students;

-- Filter with WHERE
SELECT * FROM Students WHERE LastName = 'Smith';

-- Multiple conditions
SELECT * FROM Students WHERE LastName = 'Smith' AND YearGroup = 12;

-- Pattern matching
SELECT * FROM Students WHERE LastName LIKE 'Sm%';  -- starts with 'Sm'
SELECT * FROM Students WHERE Email LIKE '%@gmail.com';  -- ends with @gmail.com

-- Sorting
SELECT * FROM Students ORDER BY LastName ASC;     -- ascending
SELECT * FROM Students ORDER BY DateOfBirth DESC;  -- descending

-- Limit results
SELECT * FROM Students ORDER BY LastName LIMIT 10;

-- Aggregation functions
SELECT COUNT(*) FROM Students;                          -- number of rows
SELECT AVG(Score) FROM Results WHERE Subject = 'Maths'; -- average score
SELECT MAX(Score), MIN(Score) FROM Results;             -- max and min
SELECT SUM(Hours) FROM Lessons WHERE TeacherID = 42;    -- total

-- GROUP BY
SELECT Subject, AVG(Score) AS AverageScore
FROM Results
GROUP BY Subject;

-- HAVING (filter on aggregated values)
SELECT Subject, AVG(Score) AS AverageScore
FROM Results
GROUP BY Subject
HAVING AVG(Score) > 70;

JOIN — combining tables

A JOIN combines rows from two or more tables based on a related column.

-- INNER JOIN: rows where there's a match in both tables
SELECT Students.FirstName, Courses.CourseName, Enrolments.Grade
FROM Enrolments
INNER JOIN Students ON Enrolments.StudentID = Students.StudentID
INNER JOIN Courses ON Enrolments.CourseID = Courses.CourseID;

-- LEFT JOIN: all rows from left table, matched rows from right (NULLs if no match)
SELECT Students.FirstName, Enrolments.CourseID
FROM Students
LEFT JOIN Enrolments ON Students.StudentID = Enrolments.StudentID;

Exam tip: In SQL questions, examiners often want an INNER JOIN — make sure you state the ON condition correctly (linking the foreign key to the primary key).

INSERT — adding data

-- Insert a single record
INSERT INTO Students (StudentID, FirstName, LastName, YearGroup)
VALUES (1042, 'Alice', 'Smith', 12);

-- Insert without specifying columns (must include ALL columns in order)
INSERT INTO Students VALUES (1043, 'Bob', 'Jones', 11);

UPDATE — modifying data

-- Update a specific record
UPDATE Students
SET YearGroup = 13
WHERE StudentID = 1042;

-- Update multiple fields
UPDATE Students
SET YearGroup = 13, Email = 'alice.smith@school.edu'
WHERE StudentID = 1042;

Warning: Always include a WHERE clause with UPDATE and DELETE — without it, you modify or delete every row!

DELETE — removing data

-- Delete a specific record
DELETE FROM Students WHERE StudentID = 1042;

-- Delete multiple records
DELETE FROM Results WHERE Grade = 'U';

CREATE TABLE and data types

CREATE TABLE Students (
    StudentID   INTEGER PRIMARY KEY,
    FirstName   VARCHAR(50) NOT NULL,
    LastName    VARCHAR(50) NOT NULL,
    DateOfBirth DATE,
    Email       VARCHAR(100) UNIQUE,
    YearGroup   INTEGER DEFAULT 12,
    FOREIGN KEY (FormTeacherID) REFERENCES Teachers(TeacherID)
);

Common SQL data types:

• INTEGER / INT — whole numbers
• FLOAT / DECIMAL(p,s) — decimal numbers
• VARCHAR(n) — variable-length string up to n characters
• CHAR(n) — fixed-length string of exactly n characters
• DATE — date (YYYY-MM-DD)
• BOOLEAN — TRUE/FALSE
• TEXT — long text (no length limit)

Constraints

Constraint	Purpose
PRIMARY KEY	Uniquely identifies each row; cannot be null
FOREIGN KEY	Links to primary key in another table; enforces referential integrity
UNIQUE	All values in the column must be distinct
NOT NULL	Column must always have a value
DEFAULT	Provides a default value if none is specified
CHECK	Validates data against a condition

---

Database transactions and ACID

At A Level, you also need to understand transactions — a sequence of database operations treated as a single unit.

ACID properties ensure database reliability:

• Atomicity — a transaction either completes fully or not at all (no partial updates)
• Consistency — a transaction brings the database from one valid state to another
• Isolation — concurrent transactions don’t interfere with each other
• Durability — once committed, a transaction is permanent even if the system crashes

---

Common exam questions and how to answer them

“Explain what is meant by a foreign key.”
A foreign key is a field in one table that references the primary key in another table. It creates a link between the two tables and enforces referential integrity — you cannot add a foreign key value that doesn’t exist in the referenced table.

“Write an SQL query to find all students who scored above 80 in Mathematics, showing their name and score, ordered by score descending.”

SELECT s.FirstName, s.LastName, r.Score
FROM Students s
INNER JOIN Results r ON s.StudentID = r.StudentID
WHERE r.Subject = 'Mathematics' AND r.Score > 80
ORDER BY r.Score DESC;

“Explain why a many-to-many relationship needs a junction table.”
In a relational database, a field can only hold one value. If a student takes many courses, you cannot store multiple CourseIDs in one column without violating 1NF. A junction table (e.g., Enrolments) creates one row for each student-course combination, allowing both sides of the relationship to be represented correctly.

---

For SQL practice questions, use the question bank filtered to the Databases topic. For one-to-one help with complex SQL queries or normalisation exam technique, book a tutoring session.