Databases and SQL - Worksheets, Questions and Revision

14 original exam-style questions - 6 pages of questions with a full mark scheme - free printable PDF.

Download PDF Jump to mark scheme (page 7)Read the revision guide

Next in this strand

Networking and the Internet

Revision Library

revisionlibrary.co.uk

A-Level · AQA

A2.7 Databases and SQL

AQA 7517 · Calculators not allowed · about 130 minutes

Total Marks

Name: _______________________________ Date: ____ / ____ / ______

Answer ALL questions. Show all your working.

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 to 5 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 6 to 11 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.

Before building its database, Elm Grove Music Academy's systems analyst reviews some key relational database terms.

(a)Define the term 'primary key'.(2)

(b)Define the term 'foreign key'.(2)

(c)Define the term 'candidate key'.(2)

(d)Using an example from Elm Grove Music Academy's booking data, explain what is meant by a 'composite key'.(2)

(Total for Question 1 is 8 marks)

Elm Grove Music Academy's original spreadsheet stores each student's lesson bookings as a repeating group, as shown below (Figure 1). Assume the business rule: a student may book lessons for several different instruments, but can only have one active booking per instrument (so a student cannot have two separate bookings for, say, Piano at the same time).

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 pounds sterling.)

(a)State one problem that would occur if this unnormalised (UNF) data was stored and processed in this form.(2)

(b)Convert the data in Figure 1 into First Normal Form (1NF) by removing the repeating group. Write out the resulting flat table, including every attribute, and state a suitable primary key.(4)

(Total for Question 2 is 6 marks)

Question 2(b)'s 1NF relation is:

Booking1NF(StudentID, StudentName, StudentPhone, InstrumentID, InstrumentName, InstructorID, InstructorName, Day, Time, Duration, Cost)

with primary key (StudentID, InstrumentID).

(a)Explain why (StudentID, InstrumentID) is a suitable primary key for this relation, rather than StudentID alone or InstrumentID alone.(2)

(b)Identify TWO partial dependencies in this 1NF relation. For each, state the determinant and the dependent attribute(s).(4)

(c)Hence convert the relation into Second Normal Form (2NF). Write out each resulting relation, underlining the primary key of each.(4)

(Total for Question 3 is 10 marks)

The 2NF Booking relation from Question 3(c) is:

Booking(StudentID, InstrumentID, InstructorID, InstructorName, Day, Time, Duration, Cost), primary key (StudentID, InstrumentID).

(a)State what is meant by a transitive dependency between non-key attributes.(2)

(b)Identify the transitive dependency present in the 2NF Booking relation above.(2)

(c)Convert the relations into Third Normal Form (3NF). Write out the complete, final set of relations for Elm Grove Music Academy's database, underlining each primary key and labelling each foreign key (FK).(6)

(Total for Question 4 is 10 marks)

Using the final 3NF relations from Question 4(c), an entity relationship (ER) diagram is to be produced for Elm Grove Music Academy's database.

(a)Identify the four entities required, and state which one is needed only to resolve a many-to-many relationship (a 'link' or associative entity).(2)

(b)Draw an entity relationship diagram for this database. Show each entity, its primary key (underlined) and any foreign keys, and label the cardinality of every relationship.(6)

(c)Explain why a link entity (BOOKING) is needed to model the relationship between STUDENT and INSTRUMENT, rather than connecting them with a single direct relationship.(2)

(Total for Question 5 is 10 marks)

Figure 2: Elm Grove Music Academy's database (following normalisation in Questions 2 to 4), with sample data added. Questions 6 to 11 all refer to this database.

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 pounds sterling.)

(a)Write an SQL query to display the StudentID, InstrumentID and Time of every booking that takes place on a Monday, ordered by Time, earliest first.(4)

(b)State the StudentID, InstrumentID and Time values that this query would return, in the order they would appear.(2)

(Total for Question 6 is 6 marks)

Questions 6 to 11 refer to Figure 2 (Question 6).

(a)Explain why an INNER JOIN between the Booking and Student tables is needed to display a student's name alongside their booking details, following the normalisation carried out in Questions 3 and 4.(2)

(b)Write an SQL query, using an INNER JOIN, to display the StudentName and Cost for every booking taught by instructor T01 (Daniel Osei).(4)

(c)State the result this query would return.(2)

(Total for Question 7 is 8 marks)

Figure 2 (Question 6) is used again for this question.

(a)Write an SQL query to display, for each instructor, their InstructorName and the total number of bookings they teach. Only include instructors who teach more than one booking, and order the output with the highest number of bookings first.(6)

(b)State the final output of this query.(2)

(Total for Question 8 is 8 marks)

Elm Grove Music Academy needs to make the following changes to its database. Assume each part below is applied independently to the original data in Figure 2 (Question 6).

(a)A new student, Harriet Osborne, joins the academy. Her StudentID is S07 and her phone number is 07977 112233. Write an SQL statement to add her record to the Student table.(2)

(b)Callum Reid (S02) changes his phone number to 07900 445566. Write an SQL statement to update his record.(2)

(c)Freya Thomson (S05) cancels her Wednesday Guitar lesson with instructor T02. Write an SQL statement to delete only this booking record (do not delete her Student record).(2)

(Total for Question 9 is 6 marks)

Using the original Student and Booking data in Figure 2 (Question 6) - that is, not including any of the changes made in Question 9.

(a)Write an SQL query, using a subquery, to display the StudentID and StudentName of every student who has not booked any lessons.(4)

(b)State the result this query would return.(1)

(Total for Question 10 is 5 marks)

Figure 2 (Question 6) is used again for this question.

(a)A database administrator attempts to run the statement: DELETE FROM Student WHERE StudentID = 'S01'; Explain why the DBMS might reject this statement.(3)

(b)Describe how the database could instead be configured to automatically remove Priya Chatterjee's booking records at the same time as her student record, and give one risk of using this approach.(3)

(Total for Question 11 is 6 marks)

A separate part of Elm Grove Music Academy's system processes online payments. When a parent pays a GBP 50 deposit, the system must (1) subtract GBP 50 from the parent's stored account balance and (2) add GBP 50 to the academy's income record, using two SQL UPDATE statements grouped together.

(a)Define the term 'transaction' in the context of a database system.(2)

(b)State what is meant by COMMIT and ROLLBACK.(2)

(c)Discuss how each of the four ACID properties (Atomicity, Consistency, Isolation, Durability) contributes to the reliable processing of the deposit payment described above, particularly if the system were to fail partway through.(6)

(Total for Question 12 is 10 marks)

Elm Grove Music Academy's booking system is used by several members of staff at once.

(a)Explain what is meant by record locking in a multi-user database system.(2)

(b)Two members of staff both try to change the Time of the same booking record (S04, I04) at the same moment. Explain, using pessimistic record locking, how the DBMS would prevent this causing a lost update.(3)

(Total for Question 13 is 5 marks)

Elm Grove Music Academy's manager suggests that, to make monthly reports run faster, the database should be deliberately denormalised, for example by storing StudentName directly in the Booking table again (as it was before Question 3), instead of looking it up via a join each time. Discuss the advantages and disadvantages of denormalising the database in this way, compared with keeping it fully normalised to 3NF.

(Total for Question 14 is 6 marks)

Mark scheme · A2.7 Databases and SQL

Question 1

(a) B1 an attribute, or minimal set of attributes, that uniquely identifies each record/row in a table (oe)
(a) B1 it is chosen from the table's candidate keys and must not contain null values (oe)
(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) B1 an attribute in one table that refers to (matches the values of) the primary key of another, or the same, table (oe)
(b) B1 it creates a link between the two tables and is used to enforce referential integrity between them (oe)
(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) B1 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)
(c) B1 a table may have more than one candidate key; one is chosen to become the primary key and the rest become alternate keys (oe)
(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) B1 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)
(d) B1 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)
(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) B1 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)
(a) B1 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)
(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) B1 one row per (student, instrument) booking, so no nested/repeating group remains
(b) B1 all attributes present and atomic: StudentID, StudentName, StudentPhone, InstrumentID, InstrumentName, InstructorID, InstructorName, Day, Time, Duration, Cost
(b) B1 three rows shown, correctly matching the sample data (two for S01, one for S02)
(b) B1 primary key correctly stated as the composite key (StudentID, InstrumentID)

(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 3

(a) B1 StudentID alone is not unique, because a student can appear in more than one row (once for each instrument they book) (oe)
(a) B1 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)
(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) B1 determinant: StudentID
(b) B1 dependent attribute(s): StudentName and StudentPhone (both depend on StudentID alone, part of the composite key)
(b) B1 determinant: InstrumentID
(b) B1 dependent attribute: InstrumentName (depends on InstrumentID alone, part of the composite key)
(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) B1 Student(StudentID, StudentName, StudentPhone) - primary key StudentID
(c) B1 Instrument(InstrumentID, InstrumentName) - primary key InstrumentID
(c) B1 Booking(StudentID, InstrumentID, InstructorID, InstructorName, Day, Time, Duration, Cost) - primary key (StudentID, InstrumentID)
(c) B1 StudentID and InstrumentID within Booking correctly identified as foreign keys, referencing Student and Instrument respectively
(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 4

(a) B1 occurs when a non-key attribute depends on another non-key attribute, rather than depending directly on the (whole) primary key (oe)
(a) B1 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)
(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) B1 InstructorID -> InstructorName, i.e. (StudentID, InstrumentID) -> InstructorID -> InstructorName
(b) B1 InstructorName depends on InstructorID, which is itself a non-key attribute of Booking, not directly on the primary key (oe)
(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) B1 Student(StudentID, StudentName, StudentPhone) - primary key StudentID (unchanged from 2NF)
(c) B1 Instrument(InstrumentID, InstrumentName) - primary key InstrumentID (unchanged from 2NF)
(c) B1 new relation Instructor(InstructorID, InstructorName) created, primary key InstructorID
(c) B1 Booking(StudentID, InstrumentID, InstructorID, Day, Time, Duration, Cost) - primary key (StudentID, InstrumentID), with InstructorName removed
(c) B1 StudentID and InstrumentID within Booking correctly labelled FK (to Student and Instrument respectively)
(c) B1 InstructorID within Booking correctly labelled FK (to Instructor)
(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 5

(a) B1 STUDENT, INSTRUMENT, INSTRUCTOR and BOOKING correctly identified as the four entities
(a) B1 BOOKING correctly identified as the link/associative entity
(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) B1 STUDENT box shown correctly, with primary key StudentID underlined
(b) B1 INSTRUMENT box shown correctly, with primary key InstrumentID underlined
(b) B1 INSTRUCTOR box shown correctly, with primary key InstructorID underlined
(b) B1 BOOKING box shown correctly, with composite primary key (StudentID, InstrumentID) underlined and InstructorID labelled as a foreign key
(b) B1 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 BOOKING
(b) B1 INSTRUCTOR-BOOKING relationship correctly labelled 1:M (one-to-many), with the 'one' end at INSTRUCTOR and the 'many' end at BOOKING
(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) B1 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)
(c) B1 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)
(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 6

(a) B1 SELECT StudentID, InstrumentID, Time
(a) B1 FROM Booking
(a) B1 WHERE Day = 'Monday'
(a) B1 ORDER BY Time ASC (ASC may be omitted, as it is the default) cao
(a) Answer:
```
SELECT StudentID, InstrumentID, Time
```
FROM Booking
WHERE Day = 'Monday'
ORDER BY Time ASC;
(b) B1 (S04, I01, 09:00) first
(b) B1 then (S01, I01, 16:00), then (S03, I03, 18:00) cao
(b) Answer:S04, I01, 09:00
S01, I01, 16:00
S03, I03, 18:00

Question 7

(a) B1 after normalisation, StudentName is stored only in the Student table, not in Booking, having been removed to eliminate the partial dependency from Question 3 (oe)
(a) B1 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)
(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 3. 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) B1 SELECT Student.StudentName, Booking.Cost
(b) B1 FROM Booking
(b) B1 INNER JOIN Student ON Booking.StudentID = Student.StudentID
(b) B1 WHERE Booking.InstructorID = 'T01' (accept an equivalent correct alternative, e.g. using table aliases)
(b) Answer:
```
SELECT Student.StudentName, Booking.Cost
```
FROM Booking
INNER JOIN Student ON Booking.StudentID = Student.StudentID
WHERE Booking.InstructorID = 'T01';
(c) B1 Priya Chatterjee, 18.00 and Callum Reid, 18.00
(c) B1 Jack Whitfield, 32.00 (all three rows correct, any order) cao
(c) Answer:Priya Chatterjee, 18.00
Callum Reid, 18.00
Jack Whitfield, 32.00

Question 8

(a) B1 SELECT Instructor.InstructorName, COUNT(*) (or an equivalent alias, e.g. AS NumBookings)
(a) B1 FROM Booking
(a) B1 INNER JOIN Instructor ON Booking.InstructorID = Instructor.InstructorID
(a) B1 GROUP BY Instructor.InstructorName (or InstructorID)
(a) B1 HAVING COUNT(*) > 1
(a) B1 ORDER BY COUNT(*) DESC (or the chosen alias) DESC, and the whole query is syntactically correct overall cao
(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) B1 Fiona Clarke, 4
(b) B1 Daniel Osei, 3 (in this order; Marcus Lindqvist correctly excluded) cao
(b) Answer:Fiona Clarke, 4
Daniel Osei, 3

Question 9

(a) B1 INSERT INTO Student (StudentID, StudentName, StudentPhone)
(a) B1 VALUES ('S07', 'Harriet Osborne', '07977 112233'); - correct syntax, with values in matching order cao
(a) Answer:
```
INSERT INTO Student (StudentID, StudentName, StudentPhone)
```
VALUES ('S07', 'Harriet Osborne', '07977 112233');
(b) B1 UPDATE Student SET StudentPhone = '07900 445566'
(b) B1 WHERE StudentID = 'S02'; - WHERE clause present, so only S02's record is affected cao
(b) Answer:
```
UPDATE Student
```
SET StudentPhone = '07900 445566'
WHERE StudentID = 'S02';
(c) B1 DELETE FROM Booking
(c) B1 WHERE StudentID = 'S05' AND InstrumentID = 'I02'; - correct WHERE clause uniquely identifying the one booking, using the composite primary key cao
(c) Answer:
```
DELETE FROM Booking
```
WHERE StudentID = 'S05' AND InstrumentID = 'I02';

Question 10

(a) B1 SELECT StudentID, StudentName
(a) B1 FROM Student
(a) B1 WHERE StudentID NOT IN (SELECT StudentID FROM Booking)
(a) B1 whole query correct and would execute without error (accept an equivalent NOT EXISTS or LEFT JOIN ... WHERE Booking.StudentID IS NULL solution) cao
(a) Answer:
```
SELECT StudentID, StudentName
```
FROM Student
WHERE StudentID NOT IN (SELECT StudentID FROM Booking);
(b) B1 S06, Zainab Hussain (the only student with no booking record) cao
(b) Answer: S06, Zainab Hussain

Question 11

(a) B1 S01 (Priya Chatterjee) is referenced as a foreign key value in the Booking table, since she has existing booking records (oe)
(a) B1 deleting the Student row would leave those Booking rows referencing a StudentID that no longer exists - an orphaned/dangling foreign key (oe)
(a) B1 the DBMS enforces referential integrity, so by default it restricts (rejects) any delete that would break this rule (oe)
(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) B1 the foreign key constraint (StudentID in Booking referencing Student) could be set to use ON DELETE CASCADE (oe)
(b) B1 this automatically deletes every matching row in Booking (i.e. every booking for that student) when the referenced Student row is deleted (oe)
(b) B1 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)
(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 12

(a) B1 a transaction is a logical unit of work made up of one or more database operations, e.g. SQL statements (oe)
(a) B1 it is treated as a single, indivisible action: either every operation within it completes successfully, or none of them are applied (oe)
(a) Answer: A transaction is a logical unit of work made up of one or more database operations (such as SQL statements) that is treated as a single, indivisible action: either every operation in it completes successfully, or none of them are applied to the database.
(b) B1 COMMIT: permanently saves/applies all the changes made during the transaction to the database (oe)
(b) B1 ROLLBACK: cancels/undoes all the changes made during the transaction, returning the database to the state it was in before the transaction began (oe)
(b) Answer: COMMIT permanently saves all the changes made during a transaction to the database. ROLLBACK cancels/undoes all the changes made during a transaction, returning the database to the state it was in before the transaction started.
(c) L3 (5-6): Clear, accurate explanation of all four ACID properties, each explicitly applied to the deposit scenario (e.g. what happens if the system fails between the two UPDATE statements).
(c) L2 (3-4): Sound explanation of two or three ACID properties, with some application to the deposit scenario.
(c) L1 (1-2): Basic, undeveloped reference to one or two ACID properties, with little or no link to the deposit scenario.
(c) L0 (0): No creditable content.
(c) Answer: Extended, levels-marked response: award marks holistically against the levels of response grid, using the indicative content as a guide to creditable points; there is no single correct wording.

Question 13

(a) B1 when a user/process is updating a record, the DBMS locks that record so no other user/process can access (or at least cannot update) it at the same time (oe)
(a) B1 the lock is released once the update/transaction is complete, allowing other users to then access the record (oe)
(a) Answer: Record locking is when the DBMS prevents more than one user or process from updating the same record at the same time. When a user begins updating a record, the DBMS locks it; the lock is released once their transaction is complete, after which other users can access the record.
(b) B1 the DBMS allows the first member of staff's request to lock the (S04, I04) record as soon as they begin editing it (oe)
(b) B1 the second member of staff's request is blocked/queued (or given an error) and cannot open the same record for editing until the first lock is released (oe)
(b) B1 once the first update is committed and the lock released, the second staff member's request can proceed, now working from the up to date record, so neither change is silently overwritten/lost (oe)
(b) Answer: The DBMS lets whichever staff member accesses the (S04, I04) record first place a lock on it. The second staff member's request to edit the same record is blocked (or shown an error) until that lock is released. Once the first update is committed and the lock released, the second request can then proceed, working from the now up to date record, so neither person's change silently overwrites the other's.