AQA A-Level Computer Science: Databases & SQL
6 exam-style questions with full mark schemes and model answers. Write your own answer and the AI examiner marks it against the mark scheme.
The following scenario and data were written for this exercise.
Pinehurst Community Library records its book loans in a single un-normalised table. Each row records a member, the membership scheme they belong to, and the books they currently have on loan. A sample of the raw data is shown below.
| MemberID | MemberName | MembershipType | MonthlyFee | Loans (BookID, Title, AuthorID, AuthorName, DueDate) |
|---|---|---|---|---|
| M14 | Aisha Khan | Standard | 3.50 | (B201, Coastlines, A07, R. Mensah, 2025-03-18), (B205, Tidewater, A07, R. Mensah, 2025-03-25) |
| M15 | Tom Reilly | Premium | 6.00 | (B201, Coastlines, A07, R. Mensah, 2025-03-20) |
| M16 | Sara Bell | Standard | 3.50 | (B210, Foundations, A11, L. Okoro, 2025-03-22) |
A member may have several books on loan at once; the same book may be on loan to different members over time. The monthly fee is fixed by the membership type (every Standard member pays the same fee, every Premium member pays the same fee), and each book has exactly one author.
(a) Using an example from the data, explain one problem (update, insertion or deletion anomaly) that this un-normalised design would cause. [3 marks]
(b) Normalise the data to Third Normal Form (3NF), showing the relations at each stage (1NF, 2NF, 3NF) and stating, at each stage, which dependency you remove. Use the notation Relation(PrimaryKey, …), marking foreign keys clearly. [6 marks]
(c) Identify one transitive dependency that is present after 2NF and explain why it must be removed to reach 3NF. [3 marks]
A development team is designing the database for a high-traffic online store. The lead developer proposes normalising every table to Third Normal Form (3NF). A colleague objects that "fully normalising will make the busiest pages too slow, and we should leave some tables de-normalised".
Discuss the benefits and drawbacks of normalising the store's database to Third Normal Form, and conclude with a justified recommendation. [9 marks]
The following schema was written for this exercise.
A library database uses these three relations (primary keys underlined, foreign keys marked *):
Member(MemberID, MemberName, JoinDate)Book(BookID, Title, Genre)Loan(LoanID, MemberID*, BookID*, LoanDate, Returned)
In Loan, the column Returned is a BOOLEAN that is TRUE once a book has been given back and FALSE while it is still out on loan.
Write a single SQL query that lists, for every member who currently has more than 2 books still on loan, the member's name and the number of books they have on loan. The result should show the busiest borrowers first (largest number of current loans at the top). [6 marks]
The following schema was written for this exercise.
A library database already contains the tables Member(MemberID, MemberName, JoinDate) and Book(BookID, Title, Genre). A new Loan table is needed to record which member has borrowed which book and when. It must have:
LoanID— an integer that is the primary key;MemberID— an integer that must match an existing member;BookID— an integer that must match an existing book;LoanDate— a date that must always be supplied.
Write the SQL CREATE TABLE statement for the Loan table, choosing appropriate data types and including the constraints that enforce the primary key and referential integrity for both foreign keys. [5 marks]
A banking database processes a transfer of £100 from one account to another as a single transaction made up of two updates: subtracting £100 from the first account and adding £100 to the second.
State the four ACID properties of a database transaction, and for each property briefly explain what it guarantees. [4 marks]
The following relation was written for this exercise.
A vehicle-hire firm stores its cars in the relation below (primary key underlined). All values are atomic and there are no repeating groups. The firm guarantees that every car of a given fuel type is in the same tax band (for example, every Petrol car is in band C).
Car(Registration, Make, Model, FuelType, FuelTypeTaxBand)
State the highest normal form this relation currently satisfies, and justify your answer by referring to a specific dependency. [3 marks]