Fit for Release

OXFORD, ENGLAND · DIFFICULTY: FELONY

THE BRIEFING

Dr Martin Calder disappeared on his way home. Thirty-six hours later police found him inside an abandoned ambulance outside the psychiatric hospital he used to direct. Several precise cuts had been made after he was already dead, and each one had been carefully stitched closed again. His clothes were folded beside him. A cassette was lying on the dashboard. On the recording Calder is reading out a statement, and whenever he hesitates somebody makes him start again. The police suspect a former patient. Calder assessed hundreds of them. Start with the examination report. Whoever did this left more of themselves in the stitches than in the recording.

RECOVERED RECORDING

CALDER:  "The subject understands the consequences—"

UNKNOWN:  "Your consequences."

CALDER:  "I understand the consequences of my actions."

UNKNOWN:  "Keep going."

CALDER:  "I accept responsibility."

UNKNOWN:  "You left that part out last time."

CLUES ON THE TABLE

01.Calder was found on 12 November 1991. Begin with his entry in crime_scenes.
02.The examination separates the injuries that killed him from the work done afterwards. Two marks stand out: the order the stitches were worked in, and a statement read back under correction.
03.Archived criminal cases use institutional file references. The names are held in a separate register.
04.Plenty of old cases carry one of the two marks. Only one file carries both, and only across cases that were proven.
05.A file can hold several assessments. Take the last one before the murder that let the patient out, then read the interview it cites against what the assessment claims happened.

/ 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.

crime_scenes4 COL

scene_idINT
victim_nameVARCHAR(100)
found_atTIMESTAMP
forensic_notesTEXT

criminal_history5 COL

case_idINT
crime_typeVARCHAR(100)
outcomeVARCHAR(30)
Proven, Not proven or Withdrawn
institutional_refVARCHAR(30)
→ institutional_records.file_reference
case_notesTEXT

institutional_records6 COL

record_idINT
file_referenceVARCHAR(30)
reviewed_atTIMESTAMP
decisionVARCHAR(50)
interview_referenceVARCHAR(30)
→ interviews.interview_reference
assessment_notesTEXT

interviews4 COL

interview_referenceVARCHAR(30)
recorded_atTIMESTAMP
subject_idINT
→ suspects.suspect_id
transcriptTEXT

suspects3 COL

suspect_idINT
nameVARCHAR(100)
occupationVARCHAR(100)

JOINS TO ANOTHER TABLEJOINED FROM ANOTHER TABLE

/ THE QUERY TERMINAL

Run your queries

Use SQL to explore the data. Each table holds part of the answer.

query.sql · 0 queries

or Ctrl+Enter

/ NOTES

Your notepad

Write down anything useful as you go.

NOTES

/ THE ACCUSATION

Name your suspect

Type the suspect's full name and submit.

NEXT CASE

The Quiet Ward →

"Admissions held steady. Deaths did not. Every one of them was reviewed on its own, and every one of them was signed off."