SQL Detective

Learn sql by solving a mystery

Intro🔗

One time, I was browsing the IBM developerworks website and I found a kind of tutorial to get people familiar with SQL. It was marketed as 'Informix' but it is just focusing on plain SQL.

From the website:

The Informix Detective game:

Here's a fun way to learn about IBM Informix! Learn or teach the basics of Informix and relational databases with an interactive game called the Informix Detective Game (the game's theme is a crime investigation). The game teaches relational database concepts and shows how technology can be applied to solving real-life problems. The Informix Detective Game is based on the DB2 Detective Game created by Joanna Kubasta and Joanne Moore.

I guess you can still play the official version, but sometime ago, I exported the data so that you can use it also for postgresql, mysql, mongo, as well as the origional informix. As it is rather simple to change the import I also added a mongo and a _nix 'import'. Feel free to make a pull request for your favorite database.

Game🔗

The game is simple:

Spoiler alert: (sneaky product placement as IBM had not sold it's laptop branch to Lenovo by that time)

You are an intern working in a security department for a large enterprise. As part of your training, you have been asked to participate in a crime investigation simulation to help locate a missing thinkpad laptop.

Datamodel🔗

The of the Detective Game database consists of four tables: employee, hardware, lobby, and security. It also shows you the type of information stored in each of these tables:

The employee table stores information about each employee such as their employee number, name, manager, office number, phone number, and badge number.

  • The security table stores information as to when each employee or visitor badged in and out of the building (date and time).
  • The lobby table stores the badge number assigned to each visitor by name.
  • The hardware table stores the serial number and type number for every computer along with its location and the ID of the employee who is its primary user.

In SQL DDL it could look like this:

file: create_table.sql

CREATE TABLE security(
    badge_number CHAR( 5 ) NOT NULL PRIMARY KEY,
    sign_out_date DATE,
    sign_out_time TIME without time zone,
    sign_in_time TIME without time zone
);

CREATE TABLE employee(
    employee_id CHAR( 6 ) NOT NULL PRIMARY KEY,
    first_name VARCHAR( 2 0),
    last_name VARCHAR( 20 ),
    location CHAR( 6 ),
    manager_id CHAR( 6 ),
    extension CHAR( 4 ),
    gender CHAR( 1 ),
    hair_colour CHAR( 6 ),
    badge_number CHAR( 5 ) REFERENCES security( badge_number ),
    restricted_access CHAR( 1 )
);

CREATE TABLE lobby(
    first_name VARCHAR( 20 ),
    last_name VARCHAR( 20 ),
    badge_number CHAR( 5 ) NOT NULL REFERENCES security( badge_number ),
    PRIMARY KEY( first_name, last_name )
);

CREATE TABLE hardware (
    employee_id CHAR( 6 ) PRIMARY KEY,
    location CHAR( 6 ),
    hardware_tn CHAR( 8 ),
    hardware_sn CHAR( 8 )  NOT NULL,
    FOREIGN KEY( employee_id ) REFERENCES employee( employee_id )
);

Clues🔗

There are some clues to point you in the right direction.

Extra homework assignment!🔗

Now the game was nice, as a extra bonus you also made the needed corrections to the records. But the real kicker that this game should teach, but neglects to do, is:

What constraints, triggers, etc could have prevented this problem in the first place.

Make the migration plan to modernize this database.

See https://github.com/verpeteren/SqlDetective for details