A3 Databases – IB Computer Science exam questions with mark scheme

1Core3 marks

Define the terms primary key and foreign key, and outline one reason for using a foreign key in a relational database. [3]

Primary key: a field (or combination of fields) whose value uniquely identifies each record in a table (must say 'unique' or 'uniquely')(1)
Foreign key: a field in one table that refers to / matches the primary key of another table (accept 'is the primary key of another table')(1)
Reason: it creates a relationship / link between the two tables; accept: enforces referential integrity (a record cannot refer to a record that does not exist); accept: data is stored once and referenced, reducing redundancy(1)
Answer: A primary key uniquely identifies each record; a foreign key is a field that refers to the primary key of another table, used to link the tables (and enforce referential integrity).
Examiner tip: Say 'uniquely' for the primary key and 'primary key of another table' for the foreign key.

2Core3 marks

Use the tables below.

Tutor
TutorID	TutorName
1	Mr Khan
2	Ms Reid
3	Mr Cole

Student
StudentID	Name	Year	TutorID
1	Aisha	12	1
2	Ben	13	2
3	Chloe	12	1
4	Dev	13	3
5	Elin	12	2
6	Femi	12	1

Result
StudentID	Subject	Score
1	CS	78
1	Maths	65
2	CS	55
2	Maths	70
3	CS	90
3	Maths	82
4	CS	48
5	CS	66
5	Maths	71
6	CS	72

Write an SQL query to list the names of all Year 12 students in reverse alphabetical order. [3]

SELECT Name FROM Student (allow Student.Name; do not accept SELECT * as the question asks for names only)(1)
WHERE Year = 12 (allow Year = '12')(1)
ORDER BY Name DESC (reject ORDER BY Name or ORDER BY Name ASC, which sort A to Z)(1)
Answer: SELECT Name FROM Student WHERE Year = 12 ORDER BY Name DESC; output Femi, Elin, Chloe, Aisha
Examiner tip: DESC is needed for reverse order; ORDER BY on its own sorts ascending (A to Z).

3HardHL only4 marks

The table below stores exam results.

Result
StudentID	Subject	Score
1	CS	78
1	Maths	65
2	CS	55
2	Maths	70
3	CS	90
3	Maths	82
4	CS	48
5	CS	66
5	Maths	71
6	CS	72

Write an SQL query that outputs the StudentID and average Score of every student who has at least two results and an average score above 70. [4]

SELECT StudentID, AVG(Score) FROM Result (accept AVERAGE(Score), the IB guide's wording; allow an alias, e.g. AS AvgScore)(1)
GROUP BY StudentID(1)
HAVING COUNT(*) >= 2 (accept COUNT(Score), COUNT(Subject), or > 1)(1)
AND AVG(Score) > 70 in the HAVING clause (reject if either aggregate condition is placed in WHERE)(1)
Answer: SELECT StudentID, AVG(Score) FROM Result GROUP BY StudentID HAVING COUNT(*) >= 2 AND AVG(Score) > 70; output (1, 71.5), (3, 86.0)
Examiner tip: Conditions on aggregates go in HAVING; WHERE cannot use AVG or COUNT.

4Hard5 marks

Use the tables below.

Student
StudentID	Name	Year	TutorID
1	Aisha	12	1
2	Ben	13	2
3	Chloe	12	1
4	Dev	13	3
5	Elin	12	2
6	Femi	12	1

Result
StudentID	Subject	Score
1	CS	78
1	Maths	65
2	CS	55
2	Maths	70
3	CS	90
3	Maths	82
4	CS	48
5	CS	66
5	Maths	71
6	CS	72

Construct an SQL query to output the Name and CS Score of every student in tutor group 1 (TutorID = 1) who scored at least 75 in CS. [5]

SELECT Name, Score (allow Student.Name, Result.Score)(1)
FROM Student JOIN Result (accept INNER JOIN, or FROM Student, Result)(1)
ON Student.StudentID = Result.StudentID (accept the same condition in WHERE; accept USING (StudentID) or NATURAL JOIN; StudentID must be qualified with the table names or with table aliases, e.g. S.StudentID = R.StudentID)(1)
WHERE TutorID = 1(1)
AND Subject = 'CS' AND Score >= 75 (reject Score > 75)(1)
Answer: SELECT Name, Score FROM Student JOIN Result ON Student.StudentID = Result.StudentID WHERE TutorID = 1 AND Subject = 'CS' AND Score >= 75; output Aisha 78, Chloe 90
Examiner tip: Give the join condition; a join with no ON (or WHERE) condition pairs every student with every result.

5Stretch6 marks

An unnormalized table stores orders:

ORDERS(OrderID, OrderDate, CustomerID, CustomerName, CustomerCity, {ProductID, ProductName, UnitPrice, Quantity})

The items in braces repeat for each product on the order. Construct a database in third normal form (3NF) from this table. In your answer, identify the problem removed at each stage (1NF, 2NF, 3NF) and give the final relations with primary keys marked (PK) and foreign keys marked (FK). [6]

1NF: the repeating group (product details) must be removed so every attribute is atomic(1)
1NF relation has a composite key (OrderID, ProductID) (allow 1NF done by moving the repeating group to a separate table, e.g. OrderItem(OrderID, ProductID, ProductName, UnitPrice, Quantity), keyed on (OrderID, ProductID))(1)
2NF: ProductName and UnitPrice depend only on ProductID, and OrderDate, CustomerID, CustomerName, CustomerCity only on OrderID: partial (partial-key) dependencies on the composite key (if the repeating group was split off at 1NF, identifying the ProductID partial dependency alone is enough)(1)
Partial dependencies removed to separate tables (Product, Orders, OrderLine)(1)
3NF: CustomerName and CustomerCity depend on CustomerID, not directly on OrderID (transitive dependency), so move to Customer(1)
Final: Customer(CustomerID PK, CustomerName, CustomerCity); Product(ProductID PK, ProductName, UnitPrice); Orders(OrderID PK, OrderDate, CustomerID FK); OrderLine(OrderID PK/FK, ProductID PK/FK, Quantity). Allow different table names; allow other key notation (e.g. underline, asterisk) if clear; allow a price-at-sale field in OrderLine if justified(1)
Answer: Customer, Product, Orders, OrderLine (see mark scheme)
Examiner tip: Name the type of dependency (partial or transitive) as well as the attribute involved.

6Standard5 marks

A hotel stores its bookings in the table below. BookingDate is stored as text in the form YYYY-MM-DD.

Booking
BookingID	RoomID	BookingDate	Guests
1001	4	2027-03-02	2
1002	7	2027-03-05	1
1003	4	2027-03-09	2
1004	12	2027-03-11	4
1005	7	2027-03-12	2

(a) Construct an SQL statement to add booking 1006 for room 12 on 2027-03-14 for 2 guests. [2]

(b) Booking 1003 now has 3 guests. Construct an SQL statement to make this change. [2]

(a) INSERT INTO Booking (allow with or without the column list (BookingID, RoomID, BookingDate, Guests))(1)
VALUES (1006, 12, '2027-03-14', 2) with values in the correct column order (matching the column list if one is given) and the date in quotes(1)
(b) UPDATE Booking SET Guests = 3 (allow SET Guests = Guests + 1)(1)
WHERE BookingID = 1003 (allow any condition that selects only booking 1003, e.g. RoomID = 4 AND BookingDate = '2027-03-09'; reject if WHERE is missing, as every row would change)(1)
(c) The index must also be updated / re-sorted whenever the column value changes, so each write takes extra time (accept: extra processing on every INSERT/UPDATE/DELETE)(1)
Answer: (a) INSERT INTO Booking (BookingID, RoomID, BookingDate, Guests) VALUES (1006, 12, '2027-03-14', 2); (b) UPDATE Booking SET Guests = 3 WHERE BookingID = 1003; (c) the index has to be updated as well as the table, slowing writes.
Examiner tip: An UPDATE without a WHERE clause changes every row in the table.

7Standard3 marks

Students can join many clubs and each club has many students. Explain how this many-to-many relationship is implemented in a relational database. [3]

A many-to-many relationship cannot be implemented directly: a single foreign key in either table would need repeated values / repeating groups(1)
Create a link table (accept junction / associative / bridge table), e.g. Membership(StudentID, ClubID)(1)
The link table holds two foreign keys, StudentID referencing Student and ClubID referencing Club, turning the many-to-many into two one-to-many relationships (together they usually form a composite primary key; allow a separate surrogate primary key, e.g. MembershipID)(1)
Answer: Link table Membership(StudentID FK, ClubID FK), usually with (StudentID, ClubID) as a composite primary key, giving two one-to-many relationships.
Examiner tip: Say the link table holds two foreign keys; just writing 'a third table' earns only one mark.

Still losing marks on this topic?

Book a free introductory session and we'll find exactly which step is costing you.

Book a free intro