Data modelling, relational databases and normalisation – 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.

2Hard5 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.

3Standard3 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.

Stuck on this topic?

Book a free 30-minute session on WhatsApp and we'll find exactly which step is costing you the marks. Or build a timed paper on it.

Free 30-minute session