Databases, SQL and Normalisation - Worksheets, Questions and Revision
10 original exam-style questions - 6 pages of questions with a full mark scheme - free printable PDF.
Databases, SQL and Normalisation revision guideMore A Level Computer Science practiceMock exams
This topic is chapter 8 of A Level Computer Science Topic Workbook 1.
1.8 Databases, SQL and Normalisation
- The centre employs several fitness instructors. Each instructor has one main speciality (e.g. yoga, spin, pilates) and runs one or more different fitness classes during the week.
- Each fitness class is run by exactly one instructor, on a fixed day and start time, and has a maximum number of places.
- Members join the centre and can book a place on any class. A member may book onto many different classes, and a class may have many members booked onto it, each on their own booking date, with a record kept of whether they actually attended.
- The centre needs to store each member's name, date of birth and the date they joined, and each instructor's name, speciality and contact number.
Member(MemberID [PK], FirstName, LastName, DateOfBirth, JoinDate, MembershipType)
Instructor(InstructorID [PK], FirstName, LastName, Speciality, ContactNumber)
FitnessClass(ClassID [PK], ClassName, InstructorID [FK], DayOfWeek, StartTime, DurationMins, MaxCapacity)
Booking(BookingID [PK], MemberID [FK], ClassID [FK], BookingDate, Attended)
FitnessClass.InstructorID references Instructor.InstructorID. Booking.MemberID references Member.MemberID and Booking.ClassID references FitnessClass.ClassID. Attended holds the text 'Yes' or 'No'. Refer to this schema for every remaining question in this pack unless stated otherwise.
Write an SQL statement for each of the following.
ClassRegister:
| ClassID | ClassName | InstructorID | InstructorName | InstructorSpeciality | MemberID | MemberName | MemberPhone | BookingDate | Attended |
|---|---|---|---|---|---|---|---|---|---|
| 5 | Spin | 12 | Grace Whitfield | Cycling | 88 | Callum Ashworth | 07700 900123 | 03/02/2026 | Yes |
| 5 | Spin | 12 | Grace Whitfield | Cycling | 91 | Freya Doku | 07700 900456 | 03/02/2026 | No |
| 7 | Pilates | 12 | Grace Whitfield | Cycling | 88 | Callum Ashworth | 07700 900123 | 04/02/2026 | Yes |
Using this table, describe an example of each type of anomaly named below.
Question 1
- (a) A field (or minimal combination of fields) whose value uniquely identifies each record/row in a table (oe)B1
- (a) Answer: A field, or minimal set of fields, that uniquely identifies each row in a table.
- (b) A field in one table that references (matches the value of) the primary key of another table, used to link the two tables (oe)B1
- (b) Answer: A field in one table that holds the value of the primary key of another table, creating a link between the two tables.
- (c) Composite key (accept compound key)B1
- (c) Explains that neither ScreeningID alone (repeats for every seat sold at that screening) nor SeatNumber alone (repeats across different screenings) is unique by itself, so both are needed together to guarantee uniqueness (oe)B1
- (c) Answer: Composite key (ScreeningID + SeatNumber). Neither column is unique alone: the same ScreeningID appears once per seat sold, and the same SeatNumber appears once per screening, so only the combination is guaranteed unique.
- (d) Candidate keys (accept alternate keys)B1
- (d) Valid reason, e.g. StudentID can be a simple, short, system-generated number under the college's control and is never missing, whereas National Insurance Number is sensitive personal data and may not yet be issued for a young student (oe)B1
- (d) Answer: NationalInsuranceNumber and Email are candidate keys. StudentID may be preferred because it is a simple system-generated number under the college's control, avoiding the need to store sensitive personal data as the key and avoiding problems where a student does not yet have a National Insurance number.
Question 2
- (a) Member entity identified (oe name, e.g. Customer)B1
- (a) Instructor entity identifiedB1
- (a) Both FitnessClass and Booking entities identified (both required for this mark, since one links members to classes)B1
- (a) Answer: Member, Instructor, FitnessClass, Booking (or equivalent names).
- (b) One-to-manyB1
- (b) Correct justification: one instructor can run several classes, but each class has exactly one instructor (oe)B1
- (b) Answer: One-to-many (one Instructor to many FitnessClass records), because each class has only one instructor but an instructor can run several classes.
- (c) States that the relationship between Member and FitnessClass is many-to-many (a member books many classes; a class has many members) (oe)B1
- (c) Explains that a relational database cannot represent a many-to-many relationship directly, so a linking entity is used to break it into two one-to-many relationships (oe)B1
- (c) Answer: Because Member and FitnessClass have a many-to-many relationship, and a relational database can only directly represent one-to-many relationships; the Booking entity resolves this by forming a one-to-many relationship with each of Member and FitnessClass.
- (d) Member: MemberID and Instructor: InstructorID both correctB1
- (d) FitnessClass: ClassID correctB1
- (d) Booking: a new unique reference such as BookingID, not simply MemberID + ClassID alone if repeat bookings are possible - accept MemberID + ClassID + BookingDate as a valid composite alternativeB1
- (d) Answer: MemberID, InstructorID, ClassID, BookingID (each a unique system-generated reference for its entity).
Question 3
- (a) Correct SELECT ... FROM Member structure naming the two correct columnsB1
- (a) Correct WHERE clause: WHERE MembershipType = 'Premium'B1
- (a) Answer: SELECT FirstName, LastName FROM Member WHERE MembershipType = 'Premium';
- (b) Correct SELECT ClassName, DayOfWeek FROM FitnessClassB1
- (b) Correct ORDER BY ClassName (ascending is the default, so no ASC is required, though ASC is also accepted)B1
- (b) Answer: SELECT ClassName, DayOfWeek FROM FitnessClass ORDER BY ClassName;
- (c) Correct use of COUNT(*) or COUNT(BookingID), FROM BookingB1
- (c) Correct WHERE ClassID = 5B1
- (c) Answer: SELECT COUNT(*) FROM Booking WHERE ClassID = 5;
- (d) Correct use of the DISTINCT keywordB1
- (d) Correct FROM Member with no unnecessary WHERE clause or extra columnsB1
- (d) Answer: SELECT DISTINCT MembershipType FROM Member;
- (e) Correct SELECT LastName FROM InstructorB1
- (e) Correct use of LIKE with wildcards on both sides: WHERE Speciality LIKE '%Yoga%'B1
- (e) Answer: SELECT LastName FROM Instructor WHERE Speciality LIKE '%Yoga%';
Question 4
- (a) Correct INSERT INTO Member (...) structure naming all six columns in a sensible orderB1
- (a) Correct VALUES list with all six values in the same order as the named columns, text/date values single-quotedB1
- (a) Values match those given exactly: 214, 'Priya', 'Chandran', 14/03/2001, 01/07/2026, 'Standard'B1
- (a) Answer: INSERT INTO Member (MemberID, FirstName, LastName, DateOfBirth, JoinDate, MembershipType) VALUES (214, 'Priya', 'Chandran', '2001-03-14', '2026-07-01', 'Standard');
- (b) Correct UPDATE FitnessClass SET MaxCapacity = MaxCapacity + 5 structureB1
- (b) Correct WHERE ClassID = 5B1
- (b) Answer: UPDATE FitnessClass SET MaxCapacity = MaxCapacity + 5 WHERE ClassID = 5;
- (c) Correct DELETE FROM Booking structureB1
- (c) Correct WHERE Attended = 'No' conditionB1
- (c) Correct AND condition on BookingDate before the given date, e.g. AND BookingDate < '2026-01-01', combined with AND (not OR)B1
- (c) Answer: DELETE FROM Booking WHERE Attended = 'No' AND BookingDate < '2026-01-01';
Question 5
- (a) CREATE TABLE Payment syntax with all five columns listedB1
- (a) Appropriate data type chosen for each column (e.g. INT/INTEGER for PaymentID and MemberID, DECIMAL for Amount, DATE for PaymentDate, VARCHAR/CHAR/TEXT for Method)B1
- (a) PaymentID correctly declared PRIMARY KEYB1
- (a) MemberID declared as a foreign key using FOREIGN KEY (MemberID) REFERENCES Member(MemberID), or an equivalent inline REFERENCES clauseB1
- (a) Statement is syntactically well-formed overall (matching brackets, commas between column definitions, terminating semicolon)B1
- (a) No contradictory or incorrect constraint added (e.g. does not also make MemberID a second primary key)B1
- (a) Answer:CREATE TABLE Payment (
PaymentID INT PRIMARY KEY,
MemberID INT,
Amount DECIMAL(6,2),
PaymentDate DATE,
Method VARCHAR(10),
FOREIGN KEY (MemberID) REFERENCES Member(MemberID)
); - (b) Enforces referential integrity: prevents a payment being inserted for a MemberID that does not exist in the Member table (oe)B1
- (b) Second valid point, e.g. it also restricts deletion/update of a referenced Member row unless a cascade rule is set, keeping the two tables consistent with each other (oe)B1
- (b) Answer: It enforces referential integrity: the DBMS will reject any Payment row whose MemberID does not already exist in Member, and it controls what happens to Payment rows if the referenced Member row is later updated or deleted, keeping the two tables consistent.
Question 6
- (a) Rule(s) ensuring that a foreign key value in one table must either match an existing primary key value in the referenced table, or be null (oe)B1
- (a) Second point, e.g. this prevents 'orphaned' rows that reference a record which does not exist (oe)B1
- (a) Answer: Referential integrity means every foreign key value must match an existing primary key value in the table it references (or be null), so no row can reference a record that does not exist.
- (b) The DELETE is rejected/fails with an error, because deleting would leave three FitnessClass rows referencing an Instructor that no longer exists (oe)B1
- (b) This is the default 'restrict'/'no action' behaviour of the foreign key constraintB1
- (b) Explains why this is useful: it protects data integrity by stopping the accidental creation of orphaned FitnessClass rows whose InstructorID would no longer correspond to any real instructor (oe)B1
- (b) Answer: The DBMS rejects/blocks the deletion of InstructorID 3, because three FitnessClass rows still reference it as a foreign key. This restrict/no-action default protects the database from ending up with FitnessClass rows pointing at an instructor that no longer exists.
- (c) States that the DELETE would now be allowed to succeed rather than being rejectedB1
- (c) Explains that all three FitnessClass rows referencing InstructorID 3 would automatically also be deleted (oe)B1
- (c) Notes the consequence/risk: those three classes would be removed from the system entirely rather than being kept and perhaps reassigned to a different instructor, which may not be the desired real-world outcome (oe)B1
- (c) Answer: With ON DELETE CASCADE, deleting InstructorID 3 would succeed, and the DBMS would automatically delete all three FitnessClass rows that referenced InstructorID 3 as well, rather than blocking the deletion. This removes those classes from the system entirely, which may be undesirable if the centre only wanted to reassign them to a new instructor.
Question 7
- (a) Correctly identifies that a new instructor with no class assigned yet cannot be added to the database at all, because there is no class/booking row for their details to attach to (oe)B1
- (a) Links this clearly to the flat table structure: instructor data only exists as part of a class-booking row, so it cannot exist independently (oe)B1
- (a) Answer: A newly hired instructor who has not yet been assigned a class cannot be entered into ClassRegister at all, because instructor details only ever appear attached to a ClassID/MemberID booking row; there is no row to insert their details into.
- (b) Correctly identifies that deleting a member's only booking row would also delete that member's own details, e.g. deleting Freya Doku's row (her only booking) loses all record of her (MemberID, name, phone) even though she is still a member (oe)B1
- (b) Explains why this happens: member/instructor data is not stored independently of booking rows, so deleting a booking row can unintentionally delete other data (oe)B1
- (b) Answer: If Freya Doku's row (her only booking) is deleted because the booking was cancelled, all record of Freya Doku's details (MemberID, name, phone) is lost from the database too, even though she is still a member of the centre.
- (c) Correctly identifies that Grace Whitfield's details (InstructorName/InstructorSpeciality) are repeated in multiple rows (rows 1, 2 and 3), so changing her speciality requires updating every one of those rows (oe)B1
- (c) Explains the risk: if only some of the repeated rows are updated, the table becomes inconsistent, showing two different specialities for the same instructor (oe)B1
- (c) Answer: Grace Whitfield's InstructorSpeciality ('Cycling') appears in all three sample rows. If she retrains and her speciality changes, every row containing her details must be updated; if even one row is missed, the table then holds two different, contradictory specialities for the same instructor.
Question 8
- (a) Correct SELECT InstructorID, COUNT(*) (or COUNT(ClassID)) FROM FitnessClassM1
- (a) Correct GROUP BY InstructorIDM1
- (a) Correct HAVING COUNT(*) > 1 (must use HAVING, not WHERE, since it filters an aggregated value)A1
- (a) Statement fully well-formed with clauses in the correct order (SELECT ... FROM ... GROUP BY ... HAVING ...)A1
- (a) Answer: SELECT InstructorID, COUNT(*) FROM FitnessClass GROUP BY InstructorID HAVING COUNT(*) > 1;
- (b) Correct inner subquery: (SELECT AVG(MaxCapacity) FROM FitnessClass)M1
- (b) Correct outer query structure: SELECT ClassName FROM FitnessClass WHERE MaxCapacity > (...)M1
- (b) Subquery correctly nested inside the WHERE comparison, with matching brackets and no comma/table-alias errorsA1
- (b) Statement fully well-formed and would execute correctly as writtenA1
- (b) Answer: SELECT ClassName FROM FitnessClass WHERE MaxCapacity > (SELECT AVG(MaxCapacity) FROM FitnessClass);
Question 9
- (a) Yes, it is in 1NF, because every cell holds a single, atomic value and there are no repeating groups of columns within a single row (oe)B1
- (a) Answer: Yes, ClassRegister is in 1NF: every column holds one atomic value per row, and there is no repeating group of columns (e.g. no Member1Name, Member2Name pattern).
- (b) Correct composite key identified: ClassID + MemberID (accept + BookingDate if the reasoning allows for a member booking the same class twice)B1
- (b) Correctly identifies that ClassName, InstructorID, InstructorName and InstructorSpeciality all depend only on ClassID, not on the whole key (oe)B1
- (b) Correctly identifies that MemberName and MemberPhone depend only on MemberID, not on the whole key, as a second example of partial dependency (oe)B1
- (b) Answer: Composite primary key: ClassID + MemberID. Partial dependencies: ClassName, InstructorID, InstructorName and InstructorSpeciality depend only on ClassID (not on MemberID too); MemberName and MemberPhone depend only on MemberID (not on ClassID too).
- (c) A Class table extracted, with ClassID as its key, holding ClassName, InstructorID, InstructorName, InstructorSpecialityB1
- (c) A Member table extracted, with MemberID as its key, holding MemberName, MemberPhoneB1
- (c) A Booking table retained, with the composite key ClassID + MemberID (plus BookingDate as needed), holding only BookingDate and AttendedB1
- (c) All original data is still represented across the three tables with no column lost, and the tables correctly link back to each other via ClassID/MemberID (oe)B1
- (c) Answer: Class(ClassID [PK], ClassName, InstructorID, InstructorName, InstructorSpeciality). Member(MemberID [PK], MemberName, MemberPhone). Booking(ClassID [FK], MemberID [FK], BookingDate, Attended), with ClassID + MemberID (+ BookingDate) as its key.
- (d) Correctly identifies that InstructorName and InstructorSpeciality depend on InstructorID, not directly on ClassID (i.e. ClassID -> InstructorID -> InstructorName/InstructorSpeciality is a transitive dependency)B1
- (d) Extracts a new Instructor table with InstructorID as its key, holding InstructorName and InstructorSpecialityB1
- (d) Reduces Class to ClassID [PK], ClassName, InstructorID [FK], with InstructorID now only a foreign key referencing InstructorB1
- (d) Member and Booking tables from part (c) are correctly carried forward unchanged, since neither contains a transitive dependencyB1
- (d) Answer: Transitive dependency: ClassID -> InstructorID -> InstructorName/InstructorSpeciality. 3NF tables: Instructor(InstructorID [PK], InstructorName, InstructorSpeciality); Class(ClassID [PK], ClassName, InstructorID [FK]); Member(MemberID [PK], MemberName, MemberPhone); Booking(ClassID [FK], MemberID [FK], BookingDate, Attended).
- (e) Valid advantage identified, e.g. eliminates the update anomaly from Question 7(c), since each instructor's speciality is now stored once, in one row of Instructor (oe)B1
- (e) Explanation linking the advantage to reduced redundancy, e.g. less data is duplicated overall, saving storage and avoiding the risk of the same fact being recorded inconsistently (oe)B1
- (e) Answer: Each instructor's details are now stored exactly once, in a single row of the Instructor table, rather than being repeated in every ClassRegister row for that instructor; this removes the update anomaly from Question 7(c) and reduces overall data redundancy/storage.
Question 10
- (a) Correct INNER JOIN linking Member to Booking on Member.MemberID = Booking.MemberIDM1
- (a) Correct second INNER JOIN linking Booking to FitnessClass on Booking.ClassID = FitnessClass.ClassIDM1
- (a) Correct SELECT list: Member.FirstName, Member.LastName, FitnessClass.ClassName (or unambiguous unqualified equivalents)A1
- (a) Statement is fully well-formed overall, joining all three tables correctly with no missing/incorrect join conditionA1
- (a) Answer: SELECT Member.FirstName, Member.LastName, FitnessClass.ClassName FROM Member INNER JOIN Booking ON Member.MemberID = Booking.MemberID INNER JOIN FitnessClass ON Booking.ClassID = FitnessClass.ClassID;
- (b) Correct LEFT JOIN FROM Member ... Booking ON Member.MemberID = Booking.MemberID (Member must be the left/first table so unmatched members are kept)M1
- (b) Correct WHERE Booking.BookingID IS NULL (or another Booking column IS NULL) to isolate members with no matching booking rowM1
- (b) Correct SELECT FirstName, LastName from MemberA1
- (b) Statement fully well-formed, correctly combining the LEFT JOIN and the IS NULL filterA1
- (b) Answer: SELECT Member.FirstName, Member.LastName FROM Member LEFT JOIN Booking ON Member.MemberID = Booking.MemberID WHERE Booking.BookingID IS NULL;
- (c) States that an INNER JOIN only returns rows where a match exists in both tables (oe)B1
- (c) Explains that members with no booking, by definition, have no matching Booking row, so an INNER JOIN would exclude them entirely from the result (oe)B1
- (c) Answer: An INNER JOIN only keeps rows where both tables have a matching row. A member with no booking has no matching Booking row at all, so an INNER JOIN would exclude that member from the results completely, meaning the query could never list members with zero bookings.
Mark your answers
Check answers without an account. Some answers can be checked automatically; use the mark scheme to mark the rest.