AQA A-Level Computer Science Paper 2, June 2025: Question 6
12 marks · Medium difficulty · Short Answer
Answer questions on a relational database design for school merits, including key constraints, entity-relationship diagrams, SQL INSERT and SELECT queries, and concurrency issues.
Practise this questionQuestion
Question text
06 A school uses a relational database to store information about merits that are given to
students. A merit is an award made for outstanding work or conduct.
At the end of each term, prizes are awarded to the students who achieve the most
merits.
The database contains the four relations shown in Figure 2.
Figure 2
Student(StudentID, FirstName, LastName, YearGroup, House)
Teacher(TeacherID, FirstName, LastName)
Merit(MeritID, StudentID, TeacherID, DateAwarded, Reason)
Term(TermName, TermYear, StartDate, EndDate)
The Student relation stores information about the students who attend the school.
An example of the data stored about a student is:
StudentID FirstName LastName YearGroup House
20965 Sophie Latham 12 Kitchener
The Teacher relation stores information about the teachers who work at the school
and who award merits to students.
The Merit relation stores information about the merits that teachers have awarded
to students, when they were awarded and for what reason.
The Term relation stores information about the dates of each term. An example of
the data stored about a term is:
TermName TermYear StartDate EndDate
Autumn 2024 04/09/2024 20/12/2024
The StudentID, TeacherID, MeritID, YearGroup and TermYear attributes are all13
numeric values.
06.1 When the Merit relation was first designed, it did not contain the MeritID field.
Instead, the values in the StudentID, TeacherID and DateAwarded attributes were
used together as a composite entity identifier.
Describe the limitation that would have occurred if the entity identifier for the
Merit relation consisted of the StudentID, TeacherID and DateAwarded attributes.
[1 mark]
06.2 The diagram below shows three of the entities from the school merit database.
Draw the relationships that exist between the three entities shown on the diagram.
You must clearly show the degree of each relationship.
[1 mark]
Figure 2 is repeated below to help you answer Questions 06.3 and 06.4 without
having to turn back in the question paper.
Figure 2 (repeated)
Student(StudentID, FirstName, LastName, YearGroup, House)
Teacher(TeacherID, FirstName, LastName)
Merit(MeritID, StudentID, TeacherID, DateAwarded, Reason)
Term(TermName, TermYear, StartDate, EndDate)
06.3 A new student has joined the school. The student is called Ethan Smith and
their year group is 7. They are placed in Hulme house and given the
StudentID number 17423.
Write SQL code to add the student’s details to the Student table.
[2 marks]
06.4 At the end of the Spring term of 2025, the school needed to identify the students
who achieved merits during the term, so that prizes could be awarded to them.
The dates of the term are stored in the Term table.
Write a query that will list all the merits that were awarded during the term.
For each merit on the list, the first name and last name of the student awarded the
merit should be included, together with the reason the merit was awarded.
The merits should appear on the list in order of the StudentID of the students that
they were awarded to, with merits for the student with the lowest StudentID at the
start of the list.
[6 marks]
06.5 The merits database can be accessed on any computer within the school.
It is possible that two users might try to edit the same record simultaneously.
Describe how two users trying to edit the same record could cause a problem if
the database system did not implement a method to manage concurrent access.
[2 marks]
Mark scheme
Show the mark scheme
Total
Qu Pt Marking guidance
marks
06 1 Mark is AO2 (analyse) 1
It would not be possible for the same teacher to award the same student
(A. a student) more than one (A. two) merit(s) on the same day;
A. if a teacher / student left the school referential integrity issues may arise
Total
Qu Pt Marking guidance
marks
06 2 Mark is AO2 (analyse) 1
1 mark: Student entity joined to Merit entity using 1-many relationship AND
Teacher entity joined to Merit entity using 1-many relationship
I. many-to-many relationship between Student and Teacher
– A-LEVEL COMPUTER SCIENCE – –
Award 0 marks if any incorrect relationships drawn.
Total
Qu Pt Marking guidance
marks
06 3 Marks are AO3 (programming) 2
1 mark: INSERT INTO Student // INSERT INTO Student
(StudentID, FirstName, LastName, YearGroup, House)
If field list given in INSERT INTO clause then allow fields in any order, but must
include all five fields.
1 mark: VALUES (17423, "Ethan", "Smith", 7, "Hulme")
If field list given in INSERT INTO clause then values must match order in that
clause. If field list not given then values must be in order shown, otherwise this
mark cannot be awarded.
A. use of ' instead of "
16 R. quotation marks around 17423 and 7
Max 1 if VALUES clause before INSERT INTO clause.
Max 1 if code would not work.
Total
Qu Pt Marking guidance
marks
06 4 4 marks for AO2 (analyse) and 2 marks for AO3 (programming) 6
AO2 (analyse) – 4 marks:
1 mark for correctly analysing the data model and identifying the tables that data
needs to be extracted from (Student, Merit, Term) and the fields that need to
be extracted (FirstName, LastName, Reason), and including these and no
other tables or fields in the query.
A. inclusion of unnecessary table Teacher as long as it is correctly linked to the
Merit table
1 mark for correctly identifying the condition to select the correct term:
TermName = "Spring" and correctly identifying the condition to select the
correct date: TermYear = 2025
1 mark for correctly identifying the condition to link the Student and Merit
tables: Student.StudentID = Merit.StudentID
1 mark for correctly identifying the conditions to link the Term and Merit tables:
DateAwarded >= StartDate and DateAwarded <= EndDate
Note: Award a maximum of 2 of the 3 available marks for the correct conditions
if they are not joined by the correct logical operator AND.
Note: The AO2 marks for analysing the data model should be awarded regardless
of whether correct SQL syntax is used or not as they are for data modelling, not–A-LEVELCOMPUTER SCIENCE––
syntactically correct SQL programming.
A. mark(s) can be awarded for the correct logical conditions even if the required 17
tables are not identified as being used by the query
AO3 (programming) – 2 marks:
1 mark for fully correct SQL in two or three of the four clauses (SELECT, FROM,
WHERE, ORDER BY)
OR
2 marks for fully correct SQL in all four clauses (SELECT, FROM, WHERE,
ORDER BY)
Note:
• For the SELECT clause to count as correct SQL it must include at least two
correct fields.
• For the FROM clause to count as correct SQL it must include at least two
correct tables.
• For the WHERE clause to count as correct it must include at least one correct
condition, but does not have to include them all (ignore missing conditions or
irrelevant conditions), however the whole WHERE clause must have correct
syntax.
• For the ORDER BY clause to count as correct it must be fully correct, but the
response does not need to include the table name.
A. table names before fieldnames separated by a full stop
A. use of alias / command eg FROM Term AS T then use of T as the table
name and note that command is not required eg FROM Term T
A. INNER JOIN written as one word ie INNERJOIN or just JOIN
A. use of BETWEEN instead of >= and <= ie DateAwarded BETWEEN
StartDate AND EndDate
A. ASC at end of ORDER BY clause
A. use of ' instead of "
I. insertion of spaces into fieldnames
I. minor spelling mistakes in fieldnames etc
I. unnecessary brackets as long as they would not stop the query working
R. DESC at end of ORDER BY clause
DPT. unnecessary punctuation – allow one semicolon at the very end of the
statement, but not at the end of each clause (applies to lines of correct SQL code
not marks)
DPT. fieldname before table name (applies to lines of correct SQL code not
marks)
Overall Max 5 if solution does not work fully
– A-LEVEL COMPUTER SCIENCE – –
18 Example Solutions
Example 1 – All conditions in WHERE clause
SELECT FirstName, LastName, Reason
FROM Student, Merit, Term
WHERE TermName = "Spring"
AND TermYear = 2025
AND Student.StudentID = Merit.StudentID
AND DateAwarded >= StartDate
AND DateAwarded <= EndDate
ORDER BY Student.StudentID
Example 2 – Use of INNER JOIN
SELECT FirstName, LastName, Reason
FROM ( Student INNER JOIN Merit ON
Student.StudentID = Merit.StudentID )
INNER JOIN Term ON
( Merit.DateAwarded >= Term.StartDate
AND Merit.DateAwarded <= Term.EndDate )
WHERE TermName = "Spring" AND TermYear = 2025
ORDER BY Student.StudentID
Example 3 – A nested solution
SELECT FirstName, LastName, Reason
FROM ( SELECT StudentID, Reason
FROM ( SELECT StartDate, EndDate
FROM Term
WHERE TermName = "Spring"
AND TermYear = 2025
) DT INNER JOIN Merit
ON Merit.DateAwarded <= DT.EndDate
AND Merit.DateAwarded >= DT.StartDate
) SR INNER JOIN Student
ON SR.StudentID = Student.StudentID
ORDER BY Student.StudentID–A-LEVEL COMPUTER SCIENCE – –
Refer nested solutions to team leaders for marking.
Total
Qu Pt Marking guidance
marks
06 5 Marks are AO1 (understanding) 2
2 marks: The update made by the user who saved (the record) first will be lost //
only the update made by the user who saved (the record) second will be kept //
the update made by the user who saved second will overwrite the update of the
user who saved first
OR
1 mark: One user’s update is lost // only one user’s update is kept 19
A. the ‘lost update problem’ occurs
NE. data is lost
NE. only one update is unsuccessful
Ignore additional statements of other vague things that would not occur eg
data inconsistency but talked out by incorrect specific statements eg some
of each user’s changes would be made.
How to answer it
Relational Databases & SQL Mastery Guide
This multi-part question assesses your theoretical understanding and practical implementation of relational database management systems (RDBMS), specifically:
- Entity Integrity & Composite Keys: Identifying real-world data limitations caused by composite primary keys.
- Entity-Relationship (ER) Modelling: Correctly resolving degrees of relationships (one-to-many) between entities.
- Data Manipulation Language (DML) - INSERT: Syntactically correct record creation with strict datatype awareness (numeric vs text literals).
- Complex Queries (SQL SELECT with multiple joins): Constructing multi-table queries incorporating non-equi joins (date ranges), filtering, and ordering.
- Database Concurrency & Transaction Integrity: Explaining concurrent access anomalies, specifically the "Lost Update Problem".
Limitations of Composite Entity Identifiers
Evaluating the composite key (StudentID, TeacherID, DateAwarded)
✅ Correct Answer
A teacher would not be able to award the same student more than one merit on the same day.
(Alternative acceptable: If a teacher or student left the school, referential integrity issues may occur if historical records are modified.)
💡 Key Knowledge
A primary key (or composite identifier) enforces entity integrity: every record must have a unique combination of key values.
If the key is (StudentID, TeacherID, DateAwarded) , duplicate combinations are rejected. Inserting a second merit from the same teacher to the same student on the same calendar day causes a primary key collision.
🧠 Exam Technique
Always state the real-world consequence clearly. Don't just say "it can't have duplicates" — contextualise it: "the same teacher cannot give the same student two merits on one day."
❌ Common Errors
- Vague claims like "it wouldn't allow multiple merits" without specifying by the same teacher on the same day.
- Confusing primary keys with foreign keys.
Entity-Relationship (ER) Diagram
Visualising 1-to-Many Relationships
✅ Correct Diagram Description
- Line connecting Student to Merit with One at the Student end and Many (crow's foot) at the Merit end.
- Line connecting Teacher to Merit with One at the Teacher end and Many (crow's foot) at the Merit end.
- NO direct relationship line drawn between Student and Teacher.
💡 Key Knowledge
In relational database design, Merit acts as a junction (link) entity containing foreign keys:
- One Student receives zero, one, or many Merits (1 : M).
- One Teacher awards zero, one, or many Merits (1 : M).
❌ Common Errors
- Reversing cardinality: Putting the "Many" end at Student or Teacher. Remember: the foreign key is inside Merit , so Merit is on the "Many" side.
- Drawing a direct line between Teacher and Student (awards 0 marks).
Writing an SQL INSERT Statement
Adding a new record with exact datatypes
✅ Standard SQL Solutions
Option 1 (Explicit field list - Best Practice):
Option 2 (Implicit fields matching schema):
🧠 Exam Technique & Data Types
Pay close attention to attribute types given in the preamble:
- StudentID and YearGroup are numeric → NO quotes ( 17423 , 7 ).
- FirstName , LastName , House are text → Must be enclosed in quotes ( 'Ethan' or "Ethan" ).
❌ Common Errors & Mark Penalties
- Quotes around numbers: Putting '17423' or '7' will lose Mark 2.
- Reversing order of clauses: Putting VALUES before INSERT INTO caps marks at max 1.
- Misordering values when the explicit column list is omitted.
• Mark 1: Correct INSERT INTO Student clause.
• Mark 2: Correct VALUES (...) clause with proper string literals and unquoted numbers.
Multi-Table SQL Query with Range Filtering & Ordering
Data extraction across Student, Merit, and Term
✅ Model Solution 1: Implicit Join (WHERE Clause)
✅ Model Solution 2: Explicit ANSI INNER JOIN
📐 Step-by-Step Mark Breakdown (6 Marks Total)
AO2: Data Modelling Analysis (4 Marks)
- Mark 1: Selecting only FirstName, LastName, Reason from Student, Merit, Term .
- Mark 2: Identifying term conditions: TermName = "Spring" AND TermYear = 2025 .
- Mark 3: Linking tables: Student.StudentID = Merit.StudentID .
- Mark 4: Non-equi join on date range: DateAwarded >= StartDate AND DateAwarded <= EndDate (or BETWEEN ).
AO3: SQL Programming (2 Marks)
- 2 marks: Fully correct SQL across all 4 clauses ( SELECT, FROM, WHERE, ORDER BY ).
- 1 mark: Fully correct SQL in 2 or 3 clauses.
❌ Common Traps
- Unnecessary Tables: Adding Teacher to the query is not required (though accepted if correctly joined).
- Confusing the Join Condition: Trying to join Merit and Term using an equality operator (=). There is no TermID in Merit ! The join must be range-based ( BETWEEN StartDate AND EndDate ).
- DESC instead of ASC: The question requires "lowest StudentID first" → ascending order (default or ASC ). Writing DESC loses the clause mark.
- Semicolons: Putting semicolons at the end of each line instead of only at the very end of the statement.
Concurrent Database Access & Record Locking
The "Lost Update" Problem
✅ Correct Answer (2 Marks)
The update made by the user who saves first will be overwritten/lost; only the update made by the user who saves second will be retained.
💡 Key Knowledge: How the Lost Update Occurs
- User A reads record R into local memory.
- User B reads the same record R simultaneously.
- User A modifies a field and saves (writes back to disk).
- User B modifies a different field and saves.
- Outcome: User B's write overwrites User A's changes without incorporating them. User A's update is permanently lost.
🧠 Exam Technique
- To get 1 mark: Mentioning that "one user's update is lost" or naming "the lost update problem".
- To get the full 2 marks: Clearly distinguish between the first saver and the second saver (the second saver overwrites the first).
❌ Common Errors
- Vague statements like "the data gets corrupted" or "the computer crashes" (0 marks).
- Stating "neither update will save" (incorrect: the last write succeeds, the earlier write is lost).
Topics
4.10 Fundamentals of databases · 4.10.1 Conceptual data models and entity relationship modelling · 4.10.2 Relational databases · 4.10.4 Structured Query Language (SQL) · 4.10.5 Client server databases
Question and mark scheme from the AQA A-Level Computer Science examination, Paper 2, June 2025. QuestionVault is an independent revision resource; questions remain the copyright of the awarding body.