Relational databases: tables, keys & redundancy – GCSE Computer Science exam questions with mark scheme

1Grade 1–33 marks

A gym stores data in a relational database with two tables, Members and Bookings.

Members

MemberID	FirstName	Surname	Town	JoinYear
101	Amira	Khan	Leeds	2021
102	Ben	Clarke	York	2023
103	Chloe	Evans	Leeds	2022
104	Daniel	Okafor	Hull	2020
105	Ella	Morgan	Bradford	2024
106	Femi	Adeyemi	Leeds	2023

Bookings

BookingID	MemberID	ClassName	Day
B01	101	Yoga	Mon
B02	103	Spin	Tue
B03	102	Yoga	Wed
B04	106	Pilates	Mon
B05	103	Yoga	Thu
B06	105	Spin	Fri

(a) Define the term primary key. [1]

(b) State the primary key of the Bookings table. [1]

(a) A field that uniquely identifies each record in a table (allow column/attribute for field, row for record; allow 'a unique identifier for each record'; reject 'a field that is unique' with no reference to identifying records)(1)
(b) BookingID(1)
(c) MemberID (allow Bookings.MemberID)(1)
Answer: (a) field that uniquely identifies each record (b) BookingID (c) MemberID
Examiner tip: A foreign key is the primary key of ANOTHER table – look for a field that appears in both tables.

2Grade 4–54 marks

A gym used to store all its data in a single table, with the member's name and town repeated in every booking record.

(a) Explain what is meant by data redundancy and why it can lead to data inconsistency. [2]

(b) Explain how using two linked tables (a relational database) reduces this problem. [2]

(a) Data redundancy: the same data is stored more than once / unnecessarily duplicated (e.g. a member's town repeated in every booking)(1)
(a) If one copy is updated (e.g. a member moves town) but other copies are not, the copies disagree, causing inconsistency (allow: copies may be entered differently, e.g. typing errors)(1)
(b) Member details (name, town) are stored once only, in a separate Members table (allow: data is not duplicated / each item of data stored in one place)(1)
(b) Any one of: each booking is linked to the member by a foreign key (e.g. MemberID) that matches the primary key of Members; OR so a change (e.g. new town) only needs to be made once, so there are no copies that can disagree(1)
Answer: (a) Redundancy = the same data stored more than once; inconsistency = copies that disagree when one is changed and the others are not. (b) Member details are stored once in a Members table and bookings link to them by a foreign key, so a change is made in one place only.
Examiner tip: Inconsistency is a CONSEQUENCE of redundancy – explain the link, don't just define both words.