Databases Exam Practice

This synthesis lesson pulls the whole databases unit together by working through full, multi-part exam-style questions of the kind AQA sets — the ones that deliberately span several sub-topics in a single scenario. A typical higher-tariff question gives you a written situation and asks you to model it as an entity-relationship diagram, normalise some sample data, and write SQL against the resulting schema, all in one extended response. Tackling them well is as much about technique — showing each normalisation step, naming cardinality both ways, presenting tidy SQL with the ON clause — as it is about knowledge. Each question below is answered at three bands so you can see exactly what lifts a response from a working answer to a top-band one, with the assessment-objective split made explicit. Read the model answers actively: cover the response, attempt the part yourself, then compare. A consistent online-bookshop / school scenario runs through the questions so the schema is familiar throughout. The single most important lesson across all of them is that database marks reward demonstrated reasoning — shown normalisation steps, justified key choices, explained mechanisms — far more than bare recall, so treat every "explain" and "justify" as an invitation to show your working.

---

Spec Mapping

This lesson is a synthesis of the AQA A-Level Computer Science (7517) Fundamentals of databases content. It revisits, in integrated exam questions: entity-relationship modelling and the resolution of many-to-many relationships; normalisation from unnormalised form through 1NF, 2NF and 3NF; SQL data-definition and data-manipulation (CREATE/INSERT/UPDATE/DELETE), querying (SELECT, WHERE, joins, aggregates, GROUP BY/HAVING); transactions and the ACID properties; and data integrity and security. It is designed to develop the AO2 (apply) and AO3 (analyse/design/evaluate) skills that higher-mark database questions target.

---

How database questions are marked (AO1 / AO2 / AO3)

AO	What it rewards	In databases this looks like
AO1	Knowledge and understanding	Defining a primary key; stating what referential integrity means
AO2	Applying knowledge to a scenario	Writing the correct SQL for this schema; drawing the diagram for this situation
AO3	Analyse, design, evaluate	Justifying a key choice; explaining why M:N must be resolved; reasoning about a missing WHERE

The higher-tariff questions are weighted towards AO2/AO3: marks come from applying and justifying, not from reciting definitions. That is why a top-band answer always explains its reasoning, not just its result.

---

A method for the "big" database question

When faced with an extended scenario question, work in this fixed order — it mirrors how the whole unit fits together and stops you missing marks:

1. Underline the nouns in the scenario → these are your candidate entities.
2. Underline the verbs / linking phrases → these are your relationships; decide each one's degree by reading it both ways.
3. Resolve every many-to-many with a link entity carrying a composite key (and any relationship attributes such as Quantity or Grade).
4. Translate to relations, posting the "one"-side key into the "many" side as a foreign key, and marking primary/foreign keys.
5. If sample data is given, normalise it, showing UNF → 1NF → 2NF → 3NF and naming the dependency removed at each step.
6. Write the SQL against the relations you derived, laying out one clause per line.

Each numbered step is itself a sub-topic of this unit, so a single 12–15 mark question can legitimately test the entire course. The worked questions below follow exactly this method, so watch how the same online-bookshop scenario flows from diagram to relations to SQL.

The shared scenario schema

So the questions interlock, they share one bookshop schema. After modelling and normalisation it settles to:

Relation	Attributes (PK in bold, FK marked *)
Customer	CustomerID, Name, City, Email
Order	OrderID, OrderDate, CustomerID*
Category	CategoryID, CategoryName
Book	BookID, Title, Price, CategoryID*
OrderLine	OrderID*, BookID*, Quantity

OrderLine is the link entity resolving the Order↔Book many-to-many; its composite primary key doubles as two foreign keys. Keep this schema in view as you work the questions.

Reading the command word

Database questions are precise about what they want, signalled by the command word. Matching your response to it is worth easy marks:

Command word	What is expected
State / Give / Identify	A brief fact, no explanation (e.g. "the degree is 1:M")
Describe	An account of what happens, with some detail
Explain	Why or how — reasoning, not just description
Write (SQL)	Actual, syntactically careful SQL — not prose about it
Draw	An actual diagram with symbols, not a paragraph describing one
Justify / Evaluate	A reasoned argument, often weighing alternatives

A frequent self-inflicted error is answering an "explain" question with a "state"-level response — naming a concept without the reasoning the marks require — or answering a "write SQL" question in English. The model answers below are deliberately pitched at the command word: the "write" parts give real SQL, and the "explain" parts give mechanism.

---

Question 1 — From scenario to schema (ER → relations → SQL) [14 marks]

An online bookshop stores data about its customers, the books it sells, and the orders customers place. A customer may place many orders. An order may contain many books, and a book may appear on many orders; for each book on an order the quantity is recorded. Each book is in exactly one category, and a category contains many books.

(a) Draw an entity-relationship diagram for this scenario, resolving any many-to-many relationship and marking cardinality. [5 marks]

(b) Write the relations (in the form Name(PrimaryKey, …), underlining primary keys and marking foreign keys) that this diagram produces. [4 marks]

(c) Write an SQL statement to create the link table that resolves the order–book relationship, with appropriate keys and constraints. [5 marks]

AO breakdown: (a) AO2/AO3 — model the scenario and resolve M:N. (b) AO2 — translate the diagram to relations. (c) AO2/AO3 — apply CREATE TABLE with composite key and foreign keys.

Mid-band response

(a) Entities: Customer, Order, Book, Category. There is an OrderBook table between Order and Book.

(b)

Customer(CustomerID, Name)
Order(OrderID, CustomerID)
Book(BookID, Title, CategoryID)
Category(CategoryID, CategoryName)
OrderBook(OrderID, BookID, Quantity)

(c)

CREATE TABLE OrderBook (
    OrderID INTEGER,
    BookID INTEGER,
    Quantity INTEGER,
    PRIMARY KEY (OrderID, BookID)
);

Stronger response

(a)

erDiagram
    CUSTOMER ||--|{ "ORDER" : "places"
    "ORDER" ||--|{ ORDER_LINE : "contains"
    BOOK ||--|{ ORDER_LINE : "appears on"
    CATEGORY ||--|{ BOOK : "classifies"

(b)

Customer(CustomerID, Name, Email)
Order(OrderID, OrderDate, CustomerID*)
Book(BookID, Title, Price, CategoryID*)
Category(CategoryID, CategoryName)
OrderLine(OrderID*, BookID*, Quantity)   -- composite PK (OrderID, BookID)

(c)

CREATE TABLE OrderLine (
    OrderID  INTEGER NOT NULL,
    BookID   INTEGER NOT NULL,
    Quantity INTEGER NOT NULL,
    PRIMARY KEY (OrderID, BookID),
    FOREIGN KEY (OrderID) REFERENCES "Order"(OrderID),
    FOREIGN KEY (BookID)  REFERENCES Book(BookID)
);

Top-band response

(a)

erDiagram
    CUSTOMER ||--o{ "ORDER" : "places"
    "ORDER" ||--|{ ORDER_LINE : "contains"
    BOOK ||--|{ ORDER_LINE : "appears on"
    CATEGORY ||--|{ BOOK : "classifies"

The Order–Book relationship is many-to-many (an order has many books; a book appears on many orders), so it is resolved by the OrderLine link entity, giving two 1:M relationships. Customer–Order is 1:M (a customer places many orders; each order belongs to one customer) and optional on the customer side (a new customer may have placed no orders yet). Category–Book is 1:M.

(b)

Customer(CustomerID, Name, Email)
Order(OrderID, OrderDate, CustomerID*)              -- * FK to Customer (1:M)
Book(BookID, Title, Price, CategoryID*)             -- * FK to Category (1:M)
Category(CategoryID, CategoryName)
OrderLine(OrderID*, BookID*, Quantity)              -- composite PK; both FKs (M:N)

Each 1:M relationship posts the "one"-side key into the "many" side; the M:N becomes OrderLine, whose composite primary key (OrderID, BookID) is also two foreign keys, and which carries the relationship attribute Quantity.

(c)

CREATE TABLE OrderLine (
    OrderID  INTEGER NOT NULL,
    BookID   INTEGER NOT NULL,
    Quantity INTEGER NOT NULL CHECK (Quantity > 0),
    PRIMARY KEY (OrderID, BookID),
    FOREIGN KEY (OrderID) REFERENCES "Order"(OrderID),
    FOREIGN KEY (BookID)  REFERENCES Book(BookID)
);

The composite PRIMARY KEY (OrderID, BookID) gives entity integrity and means a book appears at most once per order (its quantity captures multiples); the two foreign keys give referential integrity so a line cannot reference a non-existent order or book; the CHECK (Quantity > 0) enforces domain integrity.

Examiner-style commentary: The mid-band answer resolves the M:N (gaining that mark) but its diagram is prose-only with no cardinality symbols, its relations omit foreign-key marking and the Quantity attribute's placement is unexplained, and its CREATE TABLE lacks the foreign keys and NOT NULLs. The stronger answer supplies a correctly-symboled diagram, fully marked relations and complete DDL. The top-band answer additionally reads cardinality both ways, distinguishes optional participation on the Customer side, explains that the composite key doubles as two foreign keys, and justifies each constraint by integrity type — the AO3 reasoning that secures full marks.

---

Question 2 — Normalisation to 3NF [9 marks]

The bookshop currently records order data in a single unnormalised table. Normalise it to Third Normal Form, showing each stage and stating the dependency removed at each step.

OrderID	OrderDate	CustomerID	CustomerName	BookID	Title	Quantity
5001	2026-01-12	C1	Alice Chen	B10, B20	SQL Basics, Data Structures	1, 2
5002	2026-01-13	C2	Bob Patel	B10	SQL Basics	1

AO breakdown: AO2/AO3 throughout — applying the normalisation procedure and justifying each step by the dependency removed.

Mid-band response

1NF: split the repeating books so each row has one book.

2NF/3NF: put customers and books in their own tables.

Customer(CustomerID, CustomerName)
Book(BookID, Title)
Order(OrderID, OrderDate, CustomerID)
OrderLine(OrderID, BookID, Quantity)

Stronger response

UNF → 1NF — remove the repeating group so all values are atomic; composite key (OrderID, BookID):

OrderID	OrderDate	CustomerID	CustomerName	BookID	Title	Quantity
5001	2026-01-12	C1	Alice Chen	B10	SQL Basics	1
5001	2026-01-12	C1	Alice Chen	B20	Data Structures	2
5002	2026-01-13	C2	Bob Patel	B10	SQL Basics	1

1NF → 2NF — remove partial dependencies on part of the composite key. OrderDate and CustomerName/CustomerID depend on OrderID only; Title depends on BookID only:

Order(OrderID, OrderDate, CustomerID, CustomerName)
Book(BookID, Title)
OrderLine(OrderID, BookID, Quantity)

2NF → 3NF — remove the transitive dependency: CustomerName depends on CustomerID, not directly on OrderID:

Order(OrderID, OrderDate, CustomerID)
Customer(CustomerID, CustomerName)
Book(BookID, Title)
OrderLine(OrderID, BookID, Quantity)

Top-band response

UNF → 1NF — A table is in 1NF when it contains no repeating groups and every value is atomic. Splitting the comma-separated books gives one row per (order, book), with composite primary key (OrderID, BookID):

OrderID	OrderDate	CustomerID	CustomerName	BookID	Title	Quantity
5001	2026-01-12	C1	Alice Chen	B10	SQL Basics	1
5001	2026-01-12	C1	Alice Chen	B20	Data Structures	2
5002	2026-01-13	C2	Bob Patel	B10	SQL Basics	1

1NF → 2NF — 2NF additionally requires no partial dependencies (no non-key attribute depending on only part of a composite key). OrderDate, CustomerID and CustomerName depend on OrderID alone; Title depends on BookID alone; only Quantity depends on the whole key. Removing the partial dependencies:

Order(OrderID, OrderDate, CustomerID, CustomerName)
Book(BookID, Title)
OrderLine(OrderID*, BookID*, Quantity)   -- composite PK; both FKs

2NF → 3NF — 3NF additionally requires no transitive dependencies (no non-key attribute depending on another non-key attribute). In Order, CustomerName depends on CustomerID, which is itself a non-key attribute — a transitive dependency OrderID → CustomerID → CustomerName. Removing it:

Customer(CustomerID, CustomerName)
Order(OrderID, OrderDate, CustomerID*)   -- * FK to Customer
Book(BookID, Title)
OrderLine(OrderID*, BookID*, Quantity)   -- composite PK; both FKs

The design now stores each customer, book and order fact exactly once, eliminating the update, insertion and deletion anomalies of the original table.

Examiner-style commentary: The mid-band answer reaches a correct final schema but skips the working — and normalisation questions explicitly award marks for showing each stage and naming the dependency removed, so it loses most of them. The stronger answer shows all three stages with the 1NF table and names partial and transitive dependencies. The top-band answer additionally defines each normal form as it applies it, identifies the specific functional dependency at each step (including writing the transitive chain OrderID → CustomerID → CustomerName), and closes by linking the result back to anomaly elimination. Showing working and naming the exact dependency is the single biggest source of marks here.

---

Question 3 — Writing SQL against the schema [12 marks]

Using Customer(CustomerID, Name, City), Order(OrderID, OrderDate, CustomerID) and OrderLine(OrderID, BookID, Quantity), write SQL for each of the following.

(a) List the names of customers in 'Leeds', ordered by name. [2 marks]

(b) For each customer, show their name and the total number of orders they have placed, including customers with none. [4 marks]

(c) List the cities that have more than 10 customers. [3 marks]

(d) A staff member needs to mark order 5001 as dispatched by setting its OrderDate… then realises orders cannot be deleted carelessly. Write an UPDATE that safely changes only order 5001's date to '2026-02-01', and explain in one sentence what would happen if the WHERE clause were omitted. [3 marks]