SQL Data Forensics: The Murder Mystery Case Study

Project info and links:

Original Database Source

SQL queries I used to complete this project

Language Used: SQLite 3

Project Objective:

This is a digital forensic investigation using SQL to identify a murderer from a city police database. With only the following information-

"The crime was a ​murder​ that occurred sometime on ​Jan.15, 2018​ and that it took place in ​SQL City"

-I need to use SQL to find clues to help me identify the culprit of the crime​

Entity Relational Diagram of the Database:

1. The Lead:

I filtered on the crime_scene_report table for a murder on Jan 15, 2018 in SQL City. I found two witnesses gathered from the description column of the crime_scene_report: one on Northwestern Dr and one named Annabel on Franklin Ave.

-- STEP 1: Find the crime scene report of the murder, utilizing the city, type of crime, and date in the crime_scene_report table to filter the correct report

SELECT * FROM crime_scene_report 
WHERE date = 20180115 AND type = 'murder' AND city = 'SQL City';

2. Witness Testimony:

Having gained info from the description column of the crime_scene_report table for the murder on Jan 15, 2018 in SQL City, I query that info onto the person table to filter out and identify the two witnesses:

/* STEP 2: Identify the two witnesses mentioned in the report by referencing their personal information gathered from the crime scene report through querying the person's table. 
 Witness 1: Lives at the last house on Northwestern Dr */ 
           
SELECT id,name FROM person 
WHERE address_street_name = 'Northwestern Dr' 
ORDER BY address_number DESC LIMIT 1;

-- Witness 2: Named Annabel, lives on Franklin Ave 

SELECT id,name FROM person 
WHERE name LIKE 'Annabel%' AND address_street_name = 'Franklin Ave';

3. Retrieving Interview Transcripts:

After acquiring the id of the two witnesses, I use that identifying information to locate the transcripts relative to their person_id on the interview table.

-- STEP 3: I retrieve interview transcripts using subqueries to dynamically pull person_id based on witness leads, ensuring the script remains functional if IDs in the person table change

SELECT * FROM interview 
WHERE person_id IN ((SELECT id  FROM person 
WHERE address_street_name = 'Northwestern Dr' 
ORDER BY address_number DESC LIMIT 1),
(SELECT id FROM person 
WHERE name LIKE 'Annabel%' AND address_street_name = 'Franklin Ave'));

4. The Final Join

Finally I executed a 4-way join to isolate the suspect matching all criteria gathered from the transcripts, using the relative information from the person, driver's license, get_fit_now_member, and get_fit_now_check_in tables

/* STEP 4: Join tables to cross reference relative Primary Keys and Foreign keys from the person, driver's license, get_fit_now_member, and get_fit_now_check_in tables to use all the evidence gathered to
identify the culprit */

SELECT p.name,d.eye_color,d.hair_color, p.id, d.plate_number, f.membership_status
FROM person p
JOIN drivers_license d ON p.license_id = d.id
JOIN get_fit_now_member f ON p.id = f.person_id
JOIN get_fit_now_check_in c ON f.id = c.membership_id
WHERE c.check_in_date LIKE '____0109' 
  AND f.membership_status = 'gold'
  AND d.plate_number LIKE '%H42W%';

---

Developed and formatted by Anthony Tran