Databases and SQL: Computer Science 9618 (Cambridge International AS & A Level)
Syllabus 8.1, 8.2, 8.3 · Strand 5 Databases
- Questions
- 10
- Total marks
- 62
- Tier mix
- 10 Core
0 of 10 questions completed
Syllabus coverage
- 8.1 5 questions completed
- 8.2 1 question completed
- 8.3 4 questions completed
A single flat file quickly runs into duplicated and inconsistent data, which is the problem a relational database is designed to solve, and this topic (syllabus ref 8.1–8.3) covers both its design and its query language. An entity-relationship diagram documents tables (entities), their primary/candidate/foreign keys, and one-to-one, one-to-many or many-to-many relationships between them. Normalisation (working a design through 1NF, 2NF and 3NF) systematically removes repeating groups and partial or transitive dependencies, and being able to justify why a given table already is, or is not, in 3NF is a core exam skill.
A DBMS provides the software layer that manages this structure: a data dictionary, security and access rights, and a query processor. Its two languages split by job, DDL defines the structure (CREATE TABLE, ALTER TABLE, with PRIMARY KEY/FOREIGN KEY clauses), while DML queries and maintains the data:
SELECT student.name, SUM(score.marks)
FROM student INNER JOIN score ON student.id = score.student_id
WHERE score.subject = 'Computer Science'
GROUP BY student.name
ORDER BY SUM(score.marks) DESC;The worked examples below are original, covering design, normalisation and SQL scripts in full.
Question 1
A small dental clinic stores every appointment in a single flat-file table. Each row holds the appointment date, the dentist's name, the dentist's phone extension, the patient's name and the patient's home address. Because the clinic has no separate table for patients, a patient's name and address are repeated on every row for every appointment they have ever had.
Which of the following is a genuine limitation of this single flat-file approach that splitting the data into separate, related tables (a relational database) is designed to solve?
Question 2
A community sports centre keeps records of its coaches and the sessions they run using two tables.
COACH table
| Field | Data type | Key |
|---|---|---|
| CoachID | INTEGER | Primary key |
| CoachName | VARCHAR(30) | |
| VARCHAR(50) | Unique for every coach | |
| Specialism | VARCHAR(20) |
SESSIONS table
| Field | Data type | Key |
|---|---|---|
| SessionID | INTEGER | Primary key |
| SessionDate | DATE | |
| SportName | VARCHAR(20) | |
| CoachID | INTEGER | Refers to CoachID in COACH |
| Fee | REAL |
Each coach can run many sessions, but each session is run by only one coach.
(a) Email in COACH is unique for every coach, but CoachID was chosen as the primary key
instead. State what is meant by a candidate key, and explain why Email fits this
description. [2]
(b) State the type of relationship (one-to-one, one-to-many or many-to-many) that exists
between COACH and SESSIONS, and explain how the two tables above show this. [2]
(c) Explain what is meant by referential integrity, and describe one rule the database
should enforce using the CoachID field to maintain it between these two tables. [2]
(d) A receptionist often searches SESSIONS by SportName, even though it is not a key field.
State the term used for a non-unique field used in this way, and explain why SportName could
not instead be used as the primary key of SESSIONS. [2]
Question 3
GreenTrail Outdoors records its customer orders in a single un-normalised table, ORDER_FORM.
Each order can list more than one product, and all of that order's products are currently held
together in the same record, as shown below.
ORDER_FORM (un-normalised)
| OrderID | OrderDate | CustomerID | CustomerName | ProductID | ProductName | UnitPrice | Quantity |
|---|---|---|---|---|---|---|---|
| 5001 | 2026-09-01 | C12 | J Alvarez | P01, P04 | Trail Mix, Granola Bar | 3.50, 2.20 | 2, 5 |
| 5002 | 2026-09-03 | C07 | R Novak | P04 | Granola Bar | 2.20 | 3 |
(a) Explain why ORDER_FORM is not in First Normal Form (1NF). Then show how the data for
OrderID 5001 would be split into two separate rows once the table is in 1NF, and state the
primary key needed for this resulting table. [3]
(b) The 1NF table from (a) contains a partial dependency, so it is not in Second Normal Form (2NF). Identify this partial dependency and describe how the table would be restructured (giving the resulting tables and their primary keys) to remove it and reach 2NF. [3]
(c) After the restructuring in (b), one of the resulting tables still contains a transitive dependency, so it is not yet in Third Normal Form (3NF). Identify this transitive dependency and describe the further restructuring needed to reach 3NF, giving the final set of tables and their keys. [3]
Question 4
The community sports centre from earlier questions no longer accesses its COACH and
SESSIONS tables as raw files. Instead, all access now goes through a Database Management
System (DBMS), which manages the data on behalf of every member of staff and every program
that needs it.
(a) State what is meant by a data dictionary, and describe one piece of information about
the CoachID field that it would store. [2]
(b) Describe the purpose of the DBMS's query processor when a receptionist runs an SQL
SELECT statement to list all of today's sessions. [2]
(c) State the purpose of the DBMS's developer interface, and give one example of a task a developer might use it for when maintaining this database. [2]
(d) Other than the use of passwords, state one feature the DBMS provides to protect the centre's data, and explain what it protects against. [2]
Question 5
The community sports centre stores its data in two tables. COACH already exists in the
database, populated as shown below.
COACH table structure: CoachID (INTEGER, primary key), CoachName (VARCHAR(30)), Specialism
(VARCHAR(20)).
COACH data
| CoachID | CoachName | Specialism |
|---|---|---|
| 1 | Priya Nair | Swimming |
| 2 | Tom Blake | Athletics |
| 3 | Sana Malik | Swimming |
SESSIONS table structure (does not yet exist): SessionID (INTEGER, primary key), SessionDate
(DATE), SportName (VARCHAR(20)), CoachID (INTEGER, foreign key referencing COACH), Fee
(REAL).
SESSIONS data, once created and populated
| SessionID | SessionDate | SportName | CoachID | Fee |
|---|---|---|---|---|
| 101 | 2026-09-01 | Swimming | 1 | 12.50 |
| 102 | 2026-09-01 | Athletics | 2 | 8.00 |
| 103 | 2026-09-02 | Swimming | 3 | 11.00 |
| 104 | 2026-09-03 | Swimming | 1 | 15.00 |
| 105 | 2026-09-03 | Athletics | 2 | 8.00 |
| 108 | 2026-09-04 | Athletics | 2 | 14.00 |
| 109 | 2026-09-05 | Swimming | 3 | 9.50 |
Each part below is independent and refers back to this original table data, regardless of any earlier part.
(a) Write an SQL statement to create the SESSIONS table, including appropriate data types,
its primary key, and a foreign key referencing COACH. [2]
(b) Write an SQL statement to add a new field, IsCancelled, of type BOOLEAN, to the
SESSIONS table. [1]
(c) Write an SQL statement to display the SportName, SessionDate and Fee of every session
with a Fee greater than 10.00, ordered by Fee in descending order. [2]
(d) Write an SQL statement, joining SESSIONS to COACH, to display each coach's CoachName
together with the number of sessions they run, the total Fee income from those sessions, and
the average Fee per session, grouped by coach. [3]
(e) Write an SQL statement to insert a new session: SessionID 106, SessionDate '2026-09-04', SportName 'Athletics', CoachID 2, Fee 8.00. [1]
(f) Write an SQL statement to increase the Fee of every Swimming session by 1.50. [2]
(g) Write an SQL statement to delete any session run by CoachID 2 with a Fee of less than
10.00. [1]
Question 6
Riverside Secondary School records student club membership. Each student may join several different clubs, and each club typically has many student members - for example, Amara is a member of both the Chess Club and the Debate Club, while the Chess Club itself has many other student members besides Amara.
Which of the following correctly identifies the relationship between STUDENT and CLUB, and
how a relational database would properly implement it?
Question 7
VetCare Clinic keeps its records in two related tables, already designed as shown below.
OWNER table
| Field | Data type | Key |
|---|---|---|
| OwnerID | INTEGER | Primary key |
| OwnerName | VARCHAR(30) | |
| OwnerPhone | VARCHAR(15) |
PET table
| Field | Data type | Key |
|---|---|---|
| PetID | INTEGER | Primary key |
| PetName | VARCHAR(20) | |
| Species | VARCHAR(20) | |
| OwnerID | INTEGER | Refers to OwnerID in OWNER |
(a) State what is meant by First Normal Form (1NF), and explain why the PET table above
already satisfies it. [2]
(b) PET's primary key, PetID, is a single field rather than a composite key. Explain why any
table with a single-field primary key, such as PET, is automatically in Second Normal Form
(2NF) whenever it is already in 1NF. [2]
(c) Suppose OwnerName and OwnerPhone were instead added as two extra fields directly inside
PET, alongside OwnerID, rather than being kept in a separate OWNER table. Explain, in terms
of Third Normal Form (3NF), why this change would break 3NF. [2]
Question 8
A database developer runs four different SQL statements while maintaining and using a STOCK
database:
A. UPDATE STOCK SET Price = Price * 1.10 WHERE Category = 'Electronics';
B. ALTER TABLE STOCK ADD Category VARCHAR(20);
C. SELECT ProductName, Price FROM STOCK WHERE Price > 50.00;
D. DELETE FROM STOCK WHERE Quantity = 0;
Which one of these four statements is a Data Definition Language (DDL) statement, rather than a Data Manipulation Language (DML) statement?
Question 9
Fernbank Books stores its stock in a single table, BOOK.
BOOK table structure: ISBN (VARCHAR(13), primary key), Title (VARCHAR(40)), Genre
(VARCHAR(15)), Author (VARCHAR(30)), Price (REAL), Stock (INTEGER).
BOOK data
| ISBN | Title | Genre | Author | Price | Stock |
|---|---|---|---|---|---|
| 9780001 | The Silent Orbit | Sci-Fi | J Ramirez | 8.99 | 4 |
| 9780002 | Garden of Ash | Fiction | J Ramirez | 6.50 | 0 |
| 9780003 | Coding for Beginners | Non-Fiction | A Osei | 12.99 | 10 |
| 9780004 | The Silent Orbit II | Sci-Fi | J Ramirez | 9.50 | 2 |
| 9780005 | Whispering Hills | Fiction | K Novak | 7.25 | 5 |
| 9780006 | Data Structures Explained | Non-Fiction | A Osei | 14.00 | 0 |
| 9780007 | The Last Harvest | Fiction | K Novak | 6.99 | 3 |
Each part below refers back to this original table data, regardless of any earlier part.
(a) Write an SQL statement to display every distinct Genre value held in BOOK. [2]
(b) Write an SQL statement to display the Title and Author of every book whose Author
starts with the letter 'J'. [2]
(c) Write an SQL statement to display the Title and Price of every book with a Price
between 7.00 and 12.00 inclusive. [2]
(d) Write an SQL statement to count how many books in BOOK are currently out of stock (that
is, Stock equal to 0). [2]
Question 10
BrightCode after-school coding club records which students register for which workshops. The
STUDENT and WORKSHOP tables already exist and are populated as shown below.
STUDENT table
| StudentID | StudentName |
|---|---|
| 1 | Liam Chen |
| 2 | Freya Adams |
| 3 | Omar Siddiqui |
WORKSHOP table
| WorkshopID | WorkshopTitle | WorkshopDate |
|---|---|---|
| 401 | Intro to Python | 2026-10-05 |
| 402 | Web Basics | 2026-10-12 |
| 403 | Game Design | 2026-10-19 |
A student can register for many workshops, and a workshop can have many students registered for
it, so a third table, REGISTRATION, is needed to link them. It does not yet exist, but once
created it will hold: StudentID (INTEGER, foreign key referencing STUDENT), WorkshopID
(INTEGER, foreign key referencing WORKSHOP), Attended (BOOLEAN).
REGISTRATION data, once created and populated
| StudentID | WorkshopID | Attended |
|---|---|---|
| 1 | 401 | TRUE |
| 1 | 402 | FALSE |
| 2 | 401 | FALSE |
| 2 | 402 | TRUE |
| 3 | 401 | TRUE |
| 3 | 402 | TRUE |
Each part below is independent and refers back to this original table data, regardless of any earlier part.
(a) Neither StudentID alone nor WorkshopID alone is unique in REGISTRATION. Explain why
REGISTRATION needs a composite primary key made up of both StudentID and WorkshopID
together. [2]
(b) Write an SQL statement to create the REGISTRATION table, including its composite primary
key and its two foreign keys referencing STUDENT and WORKSHOP. [3]
(c) Student Freya Adams (StudentID 2) has now also registered for the Game Design workshop (WorkshopID 403), and has not yet attended it. Write an SQL statement to insert this new registration. [1]
(d) Write an SQL statement to delete every registration for WorkshopID 401 where Attended is
FALSE. [2]