SQL Murder Mystery schema

crime_scene_report

CREATE TABLE crime_scene_report (
  date integer,
  type text,
  description text,
  city text
)

drivers_license

CREATE TABLE drivers_license (
  id integer PRIMARY KEY,
  age integer,
  height integer,
  eye_color text,
  hair_color text,
  gender text,
  plate_number text,
  car_make text,
  car_model text
)

person

CREATE TABLE person (
  id integer PRIMARY KEY,
  name text,
  license_id integer,
  address_number integer,
  address_street_name text,
  ssn char REFERENCES income (ssn),
  FOREIGN KEY (license_id) REFERENCES drivers_license (id)
)

facebook_event_checkin

CREATE TABLE facebook_event_checkin (
  person_id integer,
  event_id integer,
  event_name text,
  date integer,
  FOREIGN KEY (person_id) REFERENCES person(id)
)

interview

CREATE TABLE interview (
  person_id integer,
  transcript text,
  FOREIGN KEY (person_id) REFERENCES person(id)
)

get_fit_now_member

CREATE TABLE get_fit_now_member (
  id text PRIMARY KEY,
  person_id integer,
  name text,
  membership_start_date integer,
  membership_status text,
  FOREIGN KEY (person_id) REFERENCES person(id)
)

get_fit_now_check_in

CREATE TABLE get_fit_now_check_in (
  membership_id text,
  check_in_date
  integer,
  check_in_time integer,
  check_out_time integer,
  FOREIGN KEY (membership_id)
  REFERENCES get_fit_now_member(id)
)

income

CREATE TABLE income (
  ssn CHAR PRIMARY KEY,
  annual_income integer
)