Databases (normalisation, SQL) – A-level Computer Science exam questions with mark scheme

1Core4 marks

A school database has two tables:

Student(StudentID, Name, FormID)
Form(FormID, Tutor)

(a) State the foreign key in the Student table. [1]

(b) Explain the purpose of a foreign key. [1]

(d) Explain why Tutor is stored in Form rather than in Student. [1]

(a) FormID(1)
(b) It links a record in one table to a record in another table, because it is the primary key of the other table (allow: it creates the relationship between Student and Form)(1)
(c) One-to-many (one form has many students) (allow 1:M; reject many-to-one unless the direction Student to Form is made clear)(1)
(d) Tutor depends on FormID, not StudentID, so storing it in Student would repeat it for every student in the form (redundancy), risking inconsistent data when it is updated (allow: it would be a transitive dependency / not 3NF)(1)
Answer: (a) FormID (b) it links a Student record to the matching Form record (it is the primary key of Form) (c) one-to-many (d) avoids storing the tutor repeatedly (redundancy/inconsistency); Tutor depends on FormID, not StudentID
Examiner tip: A foreign key is a primary key in a different table; just "a key" earns nothing.

2Standard4 marks

A table Book(BookID, Title, Genre, Price, Stock) stores books in a shop.

Write an SQL query to display the Title and Price of all Fantasy books costing less than 10, in descending order of price. [4]

SELECT Title, Price FROM Book(1)
WHERE Genre = 'Fantasy' (allow double quotes; allow the two conditions in either order)(1)
AND Price < 10 (reject <= 10; reject OR)(1)
ORDER BY Price DESC (reject ORDER BY without DESC)(1)
Answer: SELECT Title, Price FROM Book WHERE Genre = 'Fantasy' AND Price < 10 ORDER BY Price DESC;
Examiner tip: Put ORDER BY last, and DESC is needed since the default is ascending.

3Hard5 marks

A table in first normal form is: Orders(OrderID, ProductID, OrderDate, CustomerID, CustomerName, CustomerCity, ProductName, UnitPrice, Quantity).

The primary key is (OrderID, ProductID). The dependencies are:
OrderID → OrderDate, CustomerID
CustomerID → CustomerName, CustomerCity
ProductID → ProductName, UnitPrice
(OrderID, ProductID) → Quantity

(a) Explain why the table is not in second normal form. [1]

(b) Give a set of tables in third normal form. Write each table as TableName(attribute, attribute, …) and write (PK) after every attribute that is part of that table’s primary key. [4]

(a) It has partial dependencies: some non-key attributes depend on only part of the composite key (e.g. OrderDate depends on OrderID alone / ProductName on ProductID alone)(1)
(b) CustomerOrder(OrderID (PK), OrderDate, CustomerID) (any sensible table name)(1)
(b) Customer(CustomerID (PK), CustomerName, CustomerCity)(1)
(b) Product(ProductID (PK), ProductName, UnitPrice)(1)
(b) OrderLine(OrderID (PK), ProductID (PK), Quantity) – both attributes marked as the composite primary key. Tables in any order; foreign keys need not be marked; a table containing extra attributes from another table does not get its mark(1)
Answer: (a) Partial dependencies: e.g. OrderDate depends on OrderID only. (b) CustomerOrder(OrderID (PK), OrderDate, CustomerID); Customer(CustomerID (PK), CustomerName, CustomerCity); Product(ProductID (PK), ProductName, UnitPrice); OrderLine(OrderID (PK), ProductID (PK), Quantity)
Examiner tip: Moving CustomerName out of the order table is a 3NF step (transitive dependency), not just 2NF.

4Stretch6 marks

Tables: Student(StudentID, Name, FormID) and Result(ResultID, StudentID, Subject, Mark).

Write an SQL query to display each Subject and the average Mark of students in form '12B' only, showing only subjects whose average is greater than 60. [6]

SELECT Subject, AVG(Mark) (allow AVG(Result.Mark); allow an alias, e.g. AS AverageMark)(1)
FROM Student INNER JOIN Result (allow JOIN; allow the two tables in either order; allow FROM Student, Result with the join condition in the WHERE clause; allow FROM Result with a subquery instead of a join, e.g. WHERE StudentID IN (SELECT StudentID FROM Student WHERE FormID = '12B'), for this mark and the next)(1)
ON Student.StudentID = Result.StudentID (allow in WHERE … AND …; StudentID must be qualified by table name or a table alias in a join, e.g. S.StudentID = R.StudentID; allow StudentID IN (SELECT StudentID FROM Student …) as the linking condition in a subquery answer)(1)
WHERE FormID = '12B' (allow Student.FormID; allow double quotes; allow inside a subquery; allow as an extra join condition, e.g. ON … AND FormID = '12B')(1)
GROUP BY Subject(1)
HAVING AVG(Mark) > 60 (reject AVG(Mark) > 60 in the WHERE clause)(1)
Answer: SELECT Subject, AVG(Mark) FROM Student INNER JOIN Result ON Student.StudentID = Result.StudentID WHERE FormID = '12B' GROUP BY Subject HAVING AVG(Mark) > 60;
Examiner tip: Use WHERE to filter rows before grouping and HAVING to filter the groups; AVG cannot go in WHERE.

5Standard4 marks

Using the table Book(BookID, Title, Genre, Price, Stock):

(a) Write an SQL statement to add the book with BookID 6, title 'Sky Fort', genre 'Fantasy', price 7.50 and stock 12. [2]

(b) Write an SQL statement to increase the price of every Fantasy book by 10%. [2]

(a) INSERT INTO Book VALUES … (allow a column list (BookID, Title, Genre, Price, Stock) before VALUES)(1)
(a) (6, 'Sky Fort', 'Fantasy', 7.50, 12) in the column order, with the text values in quotes (allow 7.5)(1)
(b) UPDATE Book SET Price = Price * 1.1 (allow Price + Price * 0.1, Price * 110 / 100)(1)
(b) WHERE Genre = 'Fantasy'(1)
Answer: (a) INSERT INTO Book VALUES (6,'Sky Fort','Fantasy',7.50,12); (b) UPDATE Book SET Price = Price * 1.1 WHERE Genre = 'Fantasy';
Examiner tip: Forgetting WHERE in an UPDATE changes every row in the table.

6Hard5 marks

(a) In a school database, Student.FormID is a foreign key that refers to Form.FormID. Explain what is meant by referential integrity and give an example of an action that would break it in this database. [2]

(b) A bank uses a client–server database. Two clerks open the same account record at the same time and each makes a change to the balance. Explain how record locking prevents one of the updates from being lost. [2]

(c) A transaction that transfers money between two accounts must lock both account records. Explain how this use of record locks could lead to deadlock. [1]

(a) Every foreign key value must match an existing primary key value in the referenced table / a foreign key cannot refer to a record that does not exist(1)
(a) Example: adding a student with a FormID (e.g. '12Z') that is not in the Form table, or deleting/changing a Form record while students still refer to it(1)
(b) The first clerk's transaction locks the record, so the second clerk cannot access/update it until the lock is released(1)
(b) The second change is then made to the already-updated balance, so the first update is not overwritten (lost update problem avoided)(1)
(c) Transaction 1 locks account X and waits for account Y, while transaction 2 has locked Y and waits for X, so neither can continue (each holds a lock the other needs)(1)
Answer: (a) Every foreign key value must match an existing primary key value; e.g. adding a student whose FormID is not in Form. (b) The first transaction locks the record so the second must wait, then works on the updated value, so no update is lost. (c) Each transaction holds one lock and waits for the other's lock, so neither can proceed.
Examiner tip: Deadlock needs two records: each transaction holds one lock and waits for the other – one user waiting for one lock is not deadlock.

7Standard3 marks

The entity-relationship diagram shows part of a shop's database. A crow's foot at the end of a line shows the ‘many’ side of a relationship.

(a) Identify the relationship that is many-to-many. [1]

(b) Explain how this relationship is implemented in a relational database. [2]

(a) Order and Product (an order contains many products and a product appears in many orders)(1)
(b) A link (junction) entity/table, e.g. OrderLine, is created between them (allow: the relationship is split into two one-to-many relationships)(1)
(b) Its primary key is a composite of the primary keys of Order and Product, each also a foreign key (giving Order 1–M OrderLine and Product 1–M OrderLine) (allow: the link table contains the primary keys of Order and Product / OrderID and ProductID as foreign keys)(1)
Answer: (a) Order–Product (b) Create a link table (e.g. OrderLine) whose composite primary key is made of the primary keys of Order and Product, each a foreign key, replacing the many-to-many with two one-to-many relationships.
Examiner tip: A many-to-many relationship cannot be stored directly; say a link table is needed.

Still losing marks on this topic?

Book a free introductory session and we'll find exactly which step is costing you.

Book a free intro