OCR A-Level Computer Science: Databases, Ethics & Law

The following scenario and data were written for this exercise.

Riverside Tennis Club records its court bookings in a single un-normalised table. Each row records a member, the membership grade they hold, and the court sessions they have booked. A sample of the raw data is shown below.

MemberID	MemberName	Grade	GradeHourlyRate	Bookings (CourtID, CourtSurface, SessionDate, CoachID, CoachName)
M21	Priya Shah	Full	4.00	(C1, Clay, 2026-04-02, K3, D. Owens), (C4, Grass, 2026-04-09, K3, D. Owens)
M22	Leo Adeyemi	Off-Peak	2.50	(C1, Clay, 2026-04-03, K7, S. Patel)
M23	Mara Voss	Full	4.00	(C2, Hard, 2026-04-05, K7, S. Patel)

A member may book several sessions; the same court may be booked by different members on different dates. The hourly rate is fixed by the membership grade (every Full member pays the same rate, every Off-Peak member pays the same rate), each court has exactly one surface, and each booking is supervised by exactly one coach.

(a) 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]

(b) On your normalised schema, write SQL for the following two tasks, giving each statement as a separate SQL query. (i) List the name of every member who has a booking on court C1, together with the session date, using an INNER JOIN. [3 marks] (ii) Insert a new booking: member M22 books court C2 on 2026-04-11 supervised by coach K3. [3 marks]

A city council plans to install live facial-recognition (LFR) cameras across its high street. The system continuously scans the faces of every passer-by, converts each face to a biometric template, and checks it in real time against a council-held watchlist of people previously involved in shoplifting and antisocial behaviour. Faces that do not match are, the council says, deleted within seconds; matches trigger an alert to a control room. The council has not asked passers-by for consent and argues the deployment is justified because it reduces crime.

Discuss the legal and ethical issues raised by the council's plan, referring to relevant legislation, and reach a justified conclusion. [9 marks]

The following schema was written for this exercise.

A music venue uses these relations (primary keys underlined, foreign keys marked *):

• Artist(ArtistID, ArtistName, Genre)
• Gig(GigID, ArtistID*, GigDate, TicketsSold)

Each row of Gig records one performance by one artist, with TicketsSold holding how many tickets were sold for that gig.

Write a single SQL query that lists, for each artist who has played more than one gig, the artist's name and the total number of tickets they have sold across all their gigs. The best-selling artists should appear first (largest ticket total at the top). [6 marks]

A theatre's online box office lets many customers buy seats for the same show at the same time. To stop two customers being sold the same seat, the database uses record locking.

(a) Explain what record locking is and how it prevents two customers from buying the same seat. [3 marks]

(b) Record locking can cause a deadlock. Briefly describe how a deadlock can arise, and state one technique a DBMS can use to resolve or avoid it. [2 marks]

The Computer Misuse Act 1990 defines three principal criminal offences.

State the three principal offences under the Computer Misuse Act 1990, and for each give a one-line example of behaviour that would break it. [4 marks]

The following schema was written for this exercise.

A veterinary practice stores its data in two relations (primary key underlined, foreign key marked *):

• Owner(OwnerID, OwnerName, Phone)
• Pet(PetID, PetName, Species, OwnerID*)

Explain what referential integrity guarantees about the OwnerID foreign key in Pet, and give one example of an action the DBMS would reject in order to maintain it. [3 marks]