Project Overview
Welcome, time traveler! The year is 1824, and the Heritage Haven museum in London has reported the disappearance of several priceless artifacts. As agents of the Time Investigators Guild, you have been sent back in time to solve this mystery and ensure history remains unaltered. The fate of these artifacts could have significant implications for future generations!
Objective
- Master SQL Fundamentals:
- Practice essential SQL skills, such as SELECT statements, filtering, grouping, and ordering, while tackling real-world data challenges.
- Develop Analytical Thinking:
- Learn how to interpret data across multiple tables, draw connections, and uncover hidden patterns.
- Understand Data Relationships:
- Gain hands-on experience working with joins, correlations, and cross-references between tables to derive meaningful insights.
- Build a Story from Data:
- Piece together a timeline and narrative from raw data to solve a complex mystery.
- Learn to Think Like a Detective:
- Develop investigative skills by identifying key data points, analyzing suspicious patterns, and making data-driven conclusions.
- Find the Missing Artifact:
- Identify which artifacts are missing, when they disappeared, and how they were taken.
- Identify the Culprit:
- Use the provided museum datasets to analyze staff, visitor, and security logs to determine who is responsible.
Watch your training video below:
Schema Overview
Below is a detailed schema of the database you will be working with in the exercises. The schema provides the structure for the tables, including their column names, data types, and a description of the purpose of each column. Familiarize yourself with this schema before starting the tasks, as it will help you write accurate SQL queries and understand the relationships between the tables.
Each table schema is collapsible for better navigation. Click on a table name to view its columns and details.
Schema Information
| Column Name | Data Type | Description |
|---|---|---|
| artifact_id | INTEGER | Unique identifier for each artifact |
| artifact_name | TEXT | Name of the artifact |
| artifact_value | INTEGER | Estimated value of the artifact |
| current_location | TEXT | Current location of the artifact |
| Column Name | Data Type | Description |
|---|---|---|
| log_id | INTEGER | Unique identifier for each log entry |
| artifact_id | INTEGER | Identifier of the artifact |
| action | TEXT | Action performed (e.g., ‘checked_out’, ‘returned’) |
| timestamp | DATETIME | Date and time of the action |
| staff_id | INTEGER | Staff involved in the action |
| Column Name | Data Type | Description |
|---|---|---|
| log_id | INTEGER | Unique identifier for each security log |
| person_id | INTEGER | Identifier of the person (staff/visitor) |
| person_type | TEXT | Type of person (‘staff’ or ‘visitor’) |
| location | TEXT | Location accessed |
| timestamp | DATETIME | Time of access |
| Column Name | Data Type | Description |
|---|---|---|
| movement_id | INTEGER | Unique identifier for each movement log |
| artifact_id | INTEGER | Identifier of the artifact |
| from_location | TEXT | Location artifact was moved from |
| to_location | TEXT | Location artifact was moved to |
| timestamp | DATETIME | Date and time of the movement |
| Column Name | Data Type | Description |
|---|---|---|
| interaction_id | INTEGER | Unique identifier for each interaction |
| visitor_id | INTEGER | Identifier of the visitor |
| staff_id | INTEGER | Identifier of the staff |
| location | TEXT | Location of the interaction |
| timestamp | DATETIME | Time of the interaction |
