CASE FILE · No. 004 · 1953
The Quiet Ward
ST ALDEN'S HOSPITAL, LEEDS · DIFFICULTY: FELONY
THE BRIEFING
St Alden's Hospital has reported a sharp rise in patient deaths over the past three months. Admissions have stayed broadly level and the deaths are spread across several wards, so nothing stood out on any single ward. Each death was reviewed on its own and each was signed off. Nobody has looked at them together. The director has asked for an independent audit of the records from September through November 1953. You have the patient stays, the staff roster, the prescriptions and the medication records. Work out when the pattern starts, what the deaths have in common, and whether any member of staff needs to be looked at properly.
CLUES ON THE TABLE
- 01.Work through the records from 1 September to 30 November 1953. Start with patient_stays.
- 02.For a stay that has finished, ended_at is the time of discharge or of death. Stays still running have no end time.
- 03.Staff can be rostered to different wards on different shifts, and shifts are not all the same length.
- 04.One nurse turns up beside more deaths than anybody else in the building. Check how many shifts she worked before drawing a conclusion.
- 05.An administration cites the prescription it was given under. The prescription names a patient, and so does the administration.
/ THE EVIDENCE
Database Schema
These are the tables you can query. The lines are the keys that join them. Hover a table to see what it connects to.
- stay_idINT
- wardVARCHAR(50)
- admitted_atTIMESTAMP
- ended_atTIMESTAMPNULL while the stay is ongoing
- outcomeVARCHAR(20)discharged, deceased or ongoing
- shift_idINT
- staff_idINT→ staff.staff_id
- wardVARCHAR(50)→ patient_stays.wardsame ward names
- starts_atTIMESTAMP
- ends_atTIMESTAMP
- staff_idINT
- nameVARCHAR(100)
- roleVARCHAR(100)
- order_idINT
- stay_idINT→ patient_stays.stay_idwho it was prescribed for
- medicationVARCHAR(100)
- prescribed_atTIMESTAMP
- administration_idINT
- stay_idINT→ patient_stays.stay_idwho received it
- staff_idINT→ staff.staff_id
- order_idINT→ medication_orders.order_idthe prescription cited
- administered_atTIMESTAMP
/ THE QUERY TERMINAL
Run your queries
Use SQL to explore the data. Each table holds part of the answer.
/ NOTES
Your notepad
Write down anything useful as you go.
/ THE ACCUSATION
Name your suspect
Type the suspect's full name and submit.
NEXT CASE
The Sunday Cipher →"Seven people wrote in to confess. Six of them only know what the newspaper printed."