Databases and SQL - Worksheets, Questions and Revision
12 original exam-style questions - 8 pages of questions with a full mark scheme - free printable PDF.
Databases and SQL revision guideMore A Level Computer Science practiceMock exams
This topic is chapter 9 of A Level Computer Science Topic Workbook 1.
1.9 Databases and SQL
Elm Grove Music Academy: a database case study
Original scenario written for Revision Library.
Elm Grove Music Academy offers one-to-one instrumental lessons and is building a relational database to replace its old spreadsheet system. Questions 1, 3, 4, 10 and 11 take the academy's original, unnormalised booking data and normalise it step by step to Third Normal Form (3NF), and model it with an entity relationship (ER) diagram. Questions 2, 5, 6, 7, 8 and 12 use the resulting normalised database (with sample data added) to write and interpret SQL statements. Questions 12 to 14 look at how the database is used and maintained once it is in service: transaction processing, concurrent access, and the trade-off between normalisation and denormalisation.
Student table:
| StudentID | StudentName | StudentPhone |
|---|---|---|
| S01 | Priya Chatterjee | 07911 123456 |
| S02 | Callum Reid | 07922 654321 |
| S03 | Aisha Bello | 07933 789012 |
| S04 | Jack Whitfield | 07944 345678 |
| S05 | Freya Thomson | 07955 901234 |
| S06 | Zainab Hussain | 07966 567890 |
Instrument table:
| InstrumentID | InstrumentName |
|---|---|
| I01 | Piano |
| I02 | Guitar |
| I03 | Violin |
| I04 | Drums |
Instructor table:
| InstructorID | InstructorName |
|---|---|
| T01 | Daniel Osei |
| T02 | Fiona Clarke |
| T03 | Marcus Lindqvist |
Booking table (primary key: StudentID + InstrumentID):
| StudentID | InstrumentID | InstructorID | Day | Time | Duration | Cost |
|---|---|---|---|---|---|---|
| S01 | I01 | T01 | Monday | 16:00 | 30 | 18.00 |
| S01 | I02 | T02 | Wednesday | 17:00 | 45 | 24.00 |
| S02 | I01 | T01 | Tuesday | 15:30 | 30 | 18.00 |
| S03 | I03 | T03 | Monday | 18:00 | 45 | 22.50 |
| S03 | I02 | T02 | Thursday | 10:00 | 30 | 21.00 |
| S04 | I01 | T01 | Monday | 09:00 | 60 | 32.00 |
| S04 | I04 | T02 | Friday | 16:30 | 30 | 20.00 |
| S05 | I02 | T02 | Wednesday | 09:30 | 30 | 21.00 |
(Duration is in minutes; Cost is in GBP (£).)
Figure 1 (unnormalised, UNF):
| StudentID | StudentName | StudentPhone | Lessons (InstrumentID, InstrumentName, InstructorID, InstructorName, Day, Time, Duration, Cost) |
|---|---|---|---|
| S01 | Priya Chatterjee | 07911 123456 | (I01, Piano, T01, Daniel Osei, Monday, 16:00, 30, 18.00), (I02, Guitar, T02, Fiona Clarke, Wednesday, 17:00, 45, 24.00) |
| S02 | Callum Reid | 07922 654321 | (I01, Piano, T01, Daniel Osei, Tuesday, 15:30, 30, 18.00) |
(Duration is in minutes; Cost is in GBP (£).)
Booking1NF(StudentID, StudentName, StudentPhone, InstrumentID, InstrumentName, InstructorID, InstructorName, Day, Time, Duration, Cost)
with primary key (StudentID, InstrumentID).
Booking(StudentID, InstrumentID, InstructorID, InstructorName, Day, Time, Duration, Cost), primary key (StudentID, InstrumentID).
Question 1
- (a) an attribute, or minimal set of attributes, that uniquely identifies each record/row in a table (oe)B1
- (a) it is chosen from the table's candidate keys and must not contain null values (oe)B1
- (a) Answer: A primary key is a field (or minimal combination of fields) that uniquely identifies each record in a table. It is chosen from the table's candidate keys and cannot contain null values.
- (b) an attribute in one table that refers to (matches the values of) the primary key of another, or the same, table (oe)B1
- (b) it creates a link between the two tables and is used to enforce referential integrity between them (oe)B1
- (b) Answer: A foreign key is a field in one table that refers to the primary key of another (or the same) table. It creates a link between the two tables and is used to enforce referential integrity between them.
- (c) an attribute, or minimal combination of attributes, whose values are unique for every record in a table (i.e. could be used to identify records) (oe)B1
- (c) a table may have more than one candidate key; one is chosen to become the primary key and the rest become alternate keys (oe)B1
- (c) Answer: A candidate key is a field, or minimal combination of fields, whose values are unique for every record in a table, so it could be used to identify records. A table can have several candidate keys; one is chosen as the primary key, and the rest become alternate keys.
- (d) a primary/candidate key made up of two or more attributes, where no single one of those attributes alone is sufficiently unique to identify a record (oe)B1
- (d) valid example, e.g. (StudentID, InstrumentID) together uniquely identify a booking, because StudentID alone repeats for a student with several instruments, and InstrumentID alone repeats across different students (oe any valid example)B1
- (d) Answer: A composite key is a primary key made up of two or more attributes, where no single attribute on its own is unique enough to identify a record. For example, in a booking record, StudentID alone is not unique (a student can book several instruments) and InstrumentID alone is not unique (an instrument can be booked by several students), but together (StudentID, InstrumentID) uniquely identify each booking.
Question 2
- (a) SELECT StudentID, InstrumentID, TimeB1
- (a) FROM BookingB1
- (a) WHERE Day = 'Monday'B1
- (a) ORDER BY Time ASC (ASC may be omitted, as it is the default) caoB1
- (a) Answer:
SELECT StudentID, InstrumentID, Time
FROM Booking
WHERE Day = 'Monday'
ORDER BY Time ASC; - (b) (S04, I01, 09:00) firstB1
- (b) then (S01, I01, 16:00), then (S03, I03, 18:00) caoB1
- (b) Answer:S04, I01, 09:00
S01, I01, 16:00
S03, I03, 18:00
Question 3
- (a) it contains a repeating group / a variable number of lesson entries per student record, which is difficult for a DBMS to store and query efficiently (oe)B1
- (a) instrument and instructor details are duplicated across lesson entries (e.g. 'Piano'/'Daniel Osei' repeated for S01 and S02), wasting storage and risking update anomalies/inconsistency if, for example, an instructor's name changes (oe)B1
- (a) Answer: The Lessons column is a repeating group, so each student record can hold a different number of lesson entries, which is difficult for a DBMS to store and search efficiently. It also duplicates data, such as instrument and instructor names, across multiple rows, wasting storage and risking inconsistency if, for example, an instructor's name changes and not every copy is updated.
- (b) one row per (student, instrument) booking, so no nested/repeating group remainsB1
- (b) all attributes present and atomic: StudentID, StudentName, StudentPhone, InstrumentID, InstrumentName, InstructorID, InstructorName, Day, Time, Duration, CostB1
- (b) three rows shown, correctly matching the sample data (two for S01, one for S02)B1
- (b) primary key correctly stated as the composite key (StudentID, InstrumentID)B1
- (b) Answer:Booking1NF(StudentID, StudentName, StudentPhone, InstrumentID, InstrumentName, InstructorID, InstructorName, Day, Time, Duration, Cost)
StudentID StudentName StudentPhone InstrumentID InstrumentName InstructorID InstructorName Day Time Duration Cost S01 Priya Chatterjee 07911 123456 I01 Piano T01 Daniel Osei Monday 16:00 30 18.00 S01 Priya Chatterjee 07911 123456 I02 Guitar T02 Fiona Clarke Wednesday 17:00 45 24.00 S02 Callum Reid 07922 654321 I01 Piano T01 Daniel Osei Tuesday 15:30 30 18.00
Primary key: (StudentID, InstrumentID).
Question 4
- (a) StudentID alone is not unique, because a student can appear in more than one row (once for each instrument they book) (oe)B1
- (a) InstrumentID alone is not unique, because an instrument can be booked by more than one student; only the combination of the two uniquely identifies each row (oe)B1
- (a) Answer: StudentID alone cannot be the key, because a student who books more than one instrument appears in more than one row (e.g. S01 appears twice). InstrumentID alone cannot be the key either, because the same instrument can be booked by more than one student (e.g. I01 appears twice, for S01 and S02). Only the combination (StudentID, InstrumentID) is unique for every row.
- (b) determinant: StudentIDB1
- (b) dependent attribute(s): StudentName and StudentPhone (both depend on StudentID alone, part of the composite key)B1
- (b) determinant: InstrumentIDB1
- (b) dependent attribute: InstrumentName (depends on InstrumentID alone, part of the composite key)B1
- (b) Answer: Partial dependency 1: StudentID -> StudentName, StudentPhone (both depend only on part of the composite key, StudentID). Partial dependency 2: InstrumentID -> InstrumentName (depends only on part of the composite key, InstrumentID).
- (c) Student(StudentID, StudentName, StudentPhone) - primary key StudentIDB1
- (c) Instrument(InstrumentID, InstrumentName) - primary key InstrumentIDB1
- (c) Booking(StudentID, InstrumentID, InstructorID, InstructorName, Day, Time, Duration, Cost) - primary key (StudentID, InstrumentID)B1
- (c) StudentID and InstrumentID within Booking correctly identified as foreign keys, referencing Student and Instrument respectivelyB1
- (c) Answer:Student(StudentID (PK), StudentName, StudentPhone)
Instrument(InstrumentID (PK), InstrumentName)
Booking(StudentID (PK, FK to Student), InstrumentID (PK, FK to Instrument), InstructorID, InstructorName, Day, Time, Duration, Cost)
Question 5
- (a) after normalisation, StudentName is stored only in the Student table, not in Booking, having been removed to eliminate the partial dependency from Question 4 (oe)B1
- (a) an (INNER) JOIN matches each row in Booking to the corresponding row in Student using the shared StudentID field, combining data from both tables in one result (oe)B1
- (a) Answer: StudentName no longer appears in Booking, because it was moved into its own Student table when the partial dependency was removed in Question 4. An INNER JOIN is therefore needed to match each Booking row to the matching Student row (using the shared StudentID field) so that both a booking's details and the student's name can appear in the same result.
- (b) SELECT Student.StudentName, Booking.CostB1
- (b) FROM BookingB1
- (b) INNER JOIN Student ON Booking.StudentID = Student.StudentIDB1
- (b) WHERE Booking.InstructorID = 'T01' (accept an equivalent correct alternative, e.g. using table aliases)B1
- (b) Answer:
SELECT Student.StudentName, Booking.Cost
FROM Booking
INNER JOIN Student ON Booking.StudentID = Student.StudentID
WHERE Booking.InstructorID = 'T01'; - (c) Priya Chatterjee, 18.00 and Callum Reid, 18.00B1
- (c) Jack Whitfield, 32.00 (all three rows correct, any order) caoB1
- (c) Answer:Priya Chatterjee, 18.00
Callum Reid, 18.00
Jack Whitfield, 32.00
Question 6
- (a) INSERT INTO Student (StudentID, StudentName, StudentPhone)B1
- (a) VALUES ('S07', 'Harriet Osborne', '07977 112233'); - correct syntax, with values in matching order caoB1
- (a) Answer:
INSERT INTO Student (StudentID, StudentName, StudentPhone)
VALUES ('S07', 'Harriet Osborne', '07977 112233'); - (b) UPDATE Student SET StudentPhone = '07900 445566'B1
- (b) WHERE StudentID = 'S02'; - WHERE clause present, so only S02's record is affected caoB1
- (b) Answer:
UPDATE Student
SET StudentPhone = '07900 445566'
WHERE StudentID = 'S02'; - (c) DELETE FROM BookingB1
- (c) WHERE StudentID = 'S05' AND InstrumentID = 'I02'; - correct WHERE clause uniquely identifying the one booking, using the composite primary key caoB1
- (c) Answer:
DELETE FROM Booking
WHERE StudentID = 'S05' AND InstrumentID = 'I02';
Question 7
- (a) SELECT StudentID, StudentNameB1
- (a) FROM StudentB1
- (a) WHERE StudentID NOT IN (SELECT StudentID FROM Booking)B1
- (a) whole query correct and would execute without error (accept an equivalent NOT EXISTS or LEFT JOIN ... WHERE Booking.StudentID IS NULL solution) caoB1
- (a) Answer:
SELECT StudentID, StudentName
FROM Student
WHERE StudentID NOT IN (SELECT StudentID FROM Booking); - (b) S06, Zainab Hussain (the only student with no booking record) caoB1
- (b) Answer: S06, Zainab Hussain
Question 8
- (a) S01 (Priya Chatterjee) is referenced as a foreign key value in the Booking table, since she has existing booking records (oe)B1
- (a) deleting the Student row would leave those Booking rows referencing a StudentID that no longer exists - an orphaned/dangling foreign key (oe)B1
- (a) the DBMS enforces referential integrity, so by default it restricts (rejects) any delete that would break this rule (oe)B1
- (a) Answer: S01 (Priya Chatterjee) appears as a foreign key value in the Booking table, since she has two existing bookings. If the Student row for S01 were deleted, those Booking rows would reference a StudentID that no longer exists, breaking referential integrity. To prevent this, the DBMS by default rejects (restricts) the delete.
- (b) the foreign key constraint (StudentID in Booking referencing Student) could be set to use ON DELETE CASCADE (oe)B1
- (b) this automatically deletes every matching row in Booking (i.e. every booking for that student) when the referenced Student row is deleted (oe)B1
- (b) valid risk, e.g. booking/historical data could be lost unintentionally, or a cascading delete can remove more data than the user realises, especially if it chains through further related tables (oe)B1
- (b) Answer: The foreign key from Booking to Student could be defined with ON DELETE CASCADE. This means that when a Student row is deleted, every Booking row referencing that StudentID is automatically deleted too, so S01's bookings would be removed at the same time as her student record. One risk is that this booking data (which might be needed for records or invoicing) could be lost unintentionally, and a cascading delete can remove more data than an administrator realises, especially if other tables also reference Booking.
Question 9
- Level 3 A balanced discussion gives accurate advantages and disadvantages of denormalising, applied to the academy's database, and reaches a justified overall view.5-6
- Level 2 Some accurate advantages and/or disadvantages are given, with limited balance between the two, and/or limited application to the academy's database.3-4
- Level 1 A one-sided or generic comment on normalisation/denormalisation is made, with little reference to the academy's database.1-2
- Level 0 No creditable content.0
- Indicative content:
- Advantage of denormalising: fewer joins are needed to produce a report (e.g. StudentName is read directly from Booking), which can make read-heavy queries faster, especially over large tables.
- Advantage of denormalising: simpler queries may be easier for less experienced staff or report-writers to construct correctly.
- Disadvantage of denormalising: StudentName is duplicated across every booking row for that student, wasting storage and reintroducing the update anomaly identified in Question 3 (if a student's name changes, every duplicated copy must be updated, or the data becomes inconsistent).
- Disadvantage of denormalising: increases the risk of insertion/deletion anomalies and makes it harder to guarantee data integrity, which matters more for a booking system that is updated frequently, as opposed to a rarely-updated reporting/data-warehouse system.
- Valid overall view: for a small, frequently-updated operational database like this one, the integrity benefits of 3NF are likely to outweigh the reporting speed gained from denormalising, unless the reports are run very often on very large volumes of data (any justified conclusion credited).
Question 10
- (a) occurs when a non-key attribute depends on another non-key attribute, rather than depending directly on the (whole) primary key (oe)B1
- (a) i.e. Key -> A -> B, where A and B are both non-key attributes, so B depends on the key only indirectly, via A (oe)B1
- (a) Answer: A transitive dependency occurs when a non-key attribute depends on another non-key attribute, rather than depending directly on the primary key. That is, Key -> A -> B, where A and B are both non-key attributes, so B is only indirectly dependent on the key, via A.
- (b) InstructorID -> InstructorName, i.e. (StudentID, InstrumentID) -> InstructorID -> InstructorNameB1
- (b) InstructorName depends on InstructorID, which is itself a non-key attribute of Booking, not directly on the primary key (oe)B1
- (b) Answer: InstructorName depends on InstructorID (InstructorID -> InstructorName), and InstructorID is itself a non-key attribute of Booking, not part of its primary key. So InstructorName depends on the primary key (StudentID, InstrumentID) only transitively, via InstructorID.
- (c) Student(StudentID, StudentName, StudentPhone) - primary key StudentID (unchanged from 2NF)B1
- (c) Instrument(InstrumentID, InstrumentName) - primary key InstrumentID (unchanged from 2NF)B1
- (c) new relation Instructor(InstructorID, InstructorName) created, primary key InstructorIDB1
- (c) Booking(StudentID, InstrumentID, InstructorID, Day, Time, Duration, Cost) - primary key (StudentID, InstrumentID), with InstructorName removedB1
- (c) StudentID and InstrumentID within Booking correctly labelled FK (to Student and Instrument respectively)B1
- (c) InstructorID within Booking correctly labelled FK (to Instructor)B1
- (c) Answer:Student(StudentID (PK), StudentName, StudentPhone)
Instrument(InstrumentID (PK), InstrumentName)
Instructor(InstructorID (PK), InstructorName)
Booking(StudentID (PK, FK to Student), InstrumentID (PK, FK to Instrument), InstructorID (FK to Instructor), Day, Time, Duration, Cost)
Question 11
- (a) STUDENT, INSTRUMENT, INSTRUCTOR and BOOKING correctly identified as the four entitiesB1
- (a) BOOKING correctly identified as the link/associative entityB1
- (a) Answer: The four entities are STUDENT, INSTRUMENT, INSTRUCTOR and BOOKING. BOOKING is the link (associative) entity, needed to resolve the many-to-many relationship between STUDENT and INSTRUMENT.
- (b) STUDENT box shown correctly, with primary key StudentID underlinedB1
- (b) INSTRUMENT box shown correctly, with primary key InstrumentID underlinedB1
- (b) INSTRUCTOR box shown correctly, with primary key InstructorID underlinedB1
- (b) BOOKING box shown correctly, with composite primary key (StudentID, InstrumentID) underlined and InstructorID labelled as a foreign keyB1
- (b) STUDENT-BOOKING and INSTRUMENT-BOOKING relationships both correctly labelled 1:M (one-to-many), with the 'one' end at STUDENT/INSTRUMENT and the 'many' end at BOOKINGB1
- (b) INSTRUCTOR-BOOKING relationship correctly labelled 1:M (one-to-many), with the 'one' end at INSTRUCTOR and the 'many' end at BOOKINGB1
- (b) Answer: Diagram description (in place of a drawing): four entity boxes. STUDENT(StudentID underlined, StudentName, StudentPhone). INSTRUMENT(InstrumentID underlined, InstrumentName). INSTRUCTOR(InstructorID underlined, InstructorName). BOOKING(StudentID and InstrumentID underlined together as the composite key, InstructorID marked FK, plus Day, Time, Duration, Cost). Lines: STUDENT 1 --- M BOOKING; INSTRUMENT 1 --- M BOOKING; INSTRUCTOR 1 --- M BOOKING.
- (c) the underlying relationship between STUDENT and INSTRUMENT is many-to-many: a student can book several instruments, and an instrument can be booked by several students (oe)B1
- (c) a relational/ER model cannot represent a many-to-many relationship directly, so it must be decomposed into two one-to-many relationships via an intermediate link entity holding foreign keys to both (oe)B1
- (c) Answer: STUDENT and INSTRUMENT have an underlying many-to-many relationship: a student can book several different instruments, and an instrument can be booked by several different students. A relational database cannot store a many-to-many relationship directly, so BOOKING is used as a link entity, holding a foreign key to each of STUDENT and INSTRUMENT, which splits the many-to-many relationship into two one-to-many relationships instead.
Question 12
- (a) SELECT Instructor.InstructorName, COUNT(*) (or an equivalent alias, e.g. AS NumBookings)B1
- (a) FROM BookingB1
- (a) INNER JOIN Instructor ON Booking.InstructorID = Instructor.InstructorIDB1
- (a) GROUP BY Instructor.InstructorName (or InstructorID)B1
- (a) HAVING COUNT(*) > 1B1
- (a) ORDER BY COUNT(*) DESC (or the chosen alias) DESC, and the whole query is syntactically correct overall caoB1
- (a) Answer:
SELECT Instructor.InstructorName, COUNT(*) AS NumBookings
FROM Booking
INNER JOIN Instructor ON Booking.InstructorID = Instructor.InstructorID
GROUP BY Instructor.InstructorName
HAVING COUNT(*) > 1
ORDER BY NumBookings DESC; - (b) Fiona Clarke, 4B1
- (b) Daniel Osei, 3 (in this order; Marcus Lindqvist correctly excluded) caoB1
- (b) Answer:Fiona Clarke, 4
Daniel Osei, 3
Mark your answers
Check answers without an account. Some answers can be checked automatically; use the mark scheme to mark the rest.