The Sunday Cipher

BRISTOL, ENGLAND · DIFFICULTY: CAPITAL

THE BRIEFING

Two people were murdered in Bristol six weeks apart. They shared no address, no employer and no known acquaintance. After the second death the Bristol Chronicle received seven letters claiming responsibility. Some demanded money. Some repeated details that had already been printed. Two of them demanded the whole of the front page, and one of those enclosed a numbered cipher which the writer said contained an address. The editor has given the police until Saturday evening. Before anyone spends the night decoding what may well be a joke, work out which of the seven writers actually knows what was found at both crime scenes.

CLUES ON THE TABLE

01.The victims were Helen Price, found on 3 September 1971, and Michael Dunn, found on 15 October.
02.Each crime report holds one detail that was kept out of the press. Compare those details against the letters.
03.The numbered street directory keeps its old editions. Spaces count as characters.
04.An address can change hands. Postal redirections record who registered a forwarding instruction and the date it took effect.
05.A forwarding instruction stays in force until a later one replaces it for the same address.

/ 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_reports4 COL

report_idINT
victim_nameVARCHAR(100)
found_atTIMESTAMP
withheld_detailTEXT
kept out of the newspapers

letters4 COL

letter_idINT
postmarked_atTIMESTAMP
bodyTEXT
→ crime_reports.withheld_detaila truthful letter quotes it
cipherTEXT
→ directory_lines.line_numberpairs of line:character, NULL if none enclosed

directory_lines3 COL

editionINT
line_numberINT
line_textVARCHAR(200)
spaces count as characters

postal_redirects5 COL

redirect_idINT
original_addressVARCHAR(200)
effective_atTIMESTAMP
forwarding_addressVARCHAR(200)
registered_byINT
→ suspects.suspect_id

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 Price of Silence →

"The guests arrived by helicopter and left before the photographs. One line in the organiser's phone was not about the wine."