# Note: The "if exists" which only works with MySQL 3.22 or later

DROP database IF EXISTS system;
CREATE database system;
USE system;

CREATE TABLE states
(
  id int(5) AUTO_INCREMENT,
  name varchar(25) NOT NULL,
  population int(15) DEFAULT 0,
  capitol varchar(25) NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO states (name, population, capitol) VALUES ("Idaho", 1429000, "Boise");
INSERT INTO states (name, population, capitol) VALUES ("Montana", 936000, "Helena");
INSERT INTO states (name, population, capitol) VALUES ("North Dakota", 637000, "Bismarck");
INSERT INTO states (name, population, capitol) VALUES ("South Dakota", 776000, "Pierre");
INSERT INTO states (name, population, capitol) VALUES ("Washington", 6288000, "Olympia");
INSERT INTO states (name, population, capitol) VALUES ("Wyoming", 449000, "Cheyenne");

CREATE TABLE cities
(
  id int(5) AUTO_INCREMENT,
  name varchar(25) NOT NULL,
  population int(15) DEFAULT 0,
  state_id int(5) DEFAULT 2,
  PRIMARY KEY (id)
);

INSERT INTO cities (name, population) VALUES ("Belgrade", 7000);
INSERT INTO cities (name, population) VALUES ("Billings", 97000);
INSERT INTO cities (name, population) VALUES ("Bozeman", 32000);
INSERT INTO cities (name, population) VALUES ("Great Falls", 57000);
INSERT INTO cities (name, population) VALUES ("Helena", 27000);
INSERT INTO cities (name, population) VALUES ("Livingston", 7000);
INSERT INTO cities (name, population) VALUES ("Manhattan", 1500);
INSERT INTO cities (name, population) VALUES ("Missoula", 62000);

CREATE TABLE access
(
  id int(5) AUTO_INCREMENT,
  name varchar(20) NOT NULL,
  append int(1) DEFAULT 0,
  browse int(1) DEFAULT 0,
  modify int(1) DEFAULT 0,
  PRIMARY KEY (id)
);

INSERT INTO access (name, append, browse, modify) VALUES ("add", 1, 1, 0);
INSERT INTO access (name, append, browse, modify) VALUES ("admin", 1, 1, 1);
INSERT INTO access (name, append, browse, modify) VALUES ("browse", 0, 1, 0);

CREATE TABLE users
(
  id int(5) AUTO_INCREMENT,
  first_name varchar(30) NOT NULL,
  last_name varchar(30) NOT NULL,
  city_id int(5) NOT NULL,
  state_id int(5) NOT NULL,
  access_id int(5) NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO users (first_name, last_name, city_id, state_id, access_id) 
       VALUES ("Joey", "Dennehy", 8, 2, 1);
INSERT INTO users (first_name, last_name, city_id, state_id, access_id) 
       VALUES ("Colt", "Frederickson", 7, 2, 2);
INSERT INTO users (first_name, last_name, city_id, state_id, access_id) 
       VALUES ("Steve", "Honig", 6, 2, 3);
INSERT INTO users (first_name, last_name, city_id, state_id, access_id) 
       VALUES ("Kevin", "Joyce", 5, 2, 1);
INSERT INTO users (first_name, last_name, city_id, state_id, access_id) 
       VALUES ("Bonnie", "Kirkpatrick", 4, 2, 2);
INSERT INTO users (first_name, last_name, city_id, state_id, access_id) 
       VALUES ("Ian", "Lake", 3, 2, 3);
INSERT INTO users (first_name, last_name, city_id, state_id, access_id) 
       VALUES ("Brad", "Leary", 2, 2, 1);
INSERT INTO users (first_name, last_name, city_id, state_id, access_id) 
       VALUES ("Greg", "Lehmann", 1, 2, 2);
INSERT INTO users (first_name, last_name, city_id, state_id, access_id) 
       VALUES ("Reggie", "Mead", 1, 2, 3);
INSERT INTO users (first_name, last_name, city_id, state_id, access_id) 
       VALUES ("Will", "Merryman", 3, 2, 1);
INSERT INTO users (first_name, last_name, city_id, state_id, access_id) 
       VALUES ("Peter", "Nix", 3, 2, 2);
INSERT INTO users (first_name, last_name, city_id, state_id, access_id) 
       VALUES ("Todd", "Trotter", 8, 2, 3);
INSERT INTO users (first_name, last_name, city_id, state_id, access_id) 
       VALUES ("Ernie", "Turner", 3, 2, 1);
INSERT INTO users (first_name, last_name, city_id, state_id, access_id) 
       VALUES ("Valerie", "Wagner", 7, 2, 2);
INSERT INTO users (first_name, last_name, city_id, state_id, access_id) 
       VALUES ("Shen", "Wan", 1, 2, 3);
