-- BuildDemo
-- for SQLite, eSQL
-- Andy Harris, 4/13/05
-- Demos for all database labs


DROP TABLE hero;
DROP TABLE phonelist;

create table phonelist(
  id INTEGER PRIMARY KEY,
  firstname VARCHAR(15),
  lastname VARCHAR(15),
  email VARCHAR(20),
  phone VARCHAR(15)
);

  INSERT INTO phonelist   VALUES(0,'Andy','Harris','aharris@cs.iupui.edu','123-4567');
  INSERT INTO phonelist   VALUES(1,'Joe','Slow','jslow@noplace.net','987-6543');

CREATE TABLE hero (
  id INTEGER PRIMARY KEY,
  name varchar(30),
  power varchar(30),
  weapon varchar(30),
  transportation varchar(30)
);

INSERT INTO hero VALUES (0, 'Professor One', 'Earthquake generation', 'Laser Pointer', 'Binary Cow');
INSERT INTO hero VALUES (1, 'Bat Worm', 'Super Speed', 'Worm belt', 'Bat taxi');
INSERT INTO hero VALUES (2, 'Millennium Panther', 'Death Breath', 'Panther Bullets', 'Panther Submarine');
INSERT INTO hero VALUES (3, 'Lightning Guardian', 'Electric Toe', 'Guardian Missles', 'Guardian Moped');
INSERT INTO hero VALUES (4, 'Yak-Bot', 'Super Yurt', 'Yakbutter flamethrower', 'Yak Dirigible');


---------------------------------------------------------------
-- buildSpy.sql
-- builds and populates all databases for spy examples
-- uses SQLite - should adapt easily to other rdbms
-- by Andy Harris for PHP/MySQL for Abs. Beg
---------------------------------------------------------------


---------------------------------------------------------------
-- conventions
---------------------------------------------------------------

-- primary key = table name . ID
-- primary key always first fields
-- all primary keys autonumbered
-- all field names camel-cased
-- only link tables use underscore
-- foreign keys indicated although mySQL does not enforce
-- every table used as foriegn reference has a name field
---------------------------------------------------------------

---------------------------------------------------------------
--housekeeping
---------------------------------------------------------------


DROP TABLE agent;
DROP TABLE agent_specialty;
DROP TABLE badspy;
DROP TABLE operation;
DROP TABLE specialty;

CREATE TABLE agent (
  agentID INTEGER PRIMARY KEY,
  name varchar(50),
  operationID int
); 

INSERT INTO agent VALUES (null, 'Bond', 1);
INSERT INTO agent VALUES (null, 'Falcon', 1);
INSERT INTO agent VALUES (null, 'Cardinal', 2);
INSERT INTO agent VALUES (null, 'Blackford', 2);
INSERT INTO agent VALUES (null, 'Rahab', 3);
INSERT INTO agent VALUES (null, 'James Bland', 2);

CREATE TABLE operation (
  operationID INTEGER PRIMARY KEY,
  name varchar(50),
  description varchar(50),
  location varchar(50)
);

INSERT INTO operation VALUES (null, 'Dancing Elephant', 'Infiltrate suspicious zoo', 'London');
INSERT INTO operation VALUES (null, 'Enduring Angst', 'Make bad guys feel really guilty', 'Lower Volta');
INSERT INTO operation VALUES (null, 'Furious Dandelion', 'Plant crabgrass in enemy lawns', 'East Java');

CREATE TABLE specialty (
  specialtyID INTEGER PRIMARY KEY,
  name varchar(50) 
);

INSERT INTO specialty VALUES (null, 'Electronics');
INSERT INTO specialty VALUES (null, 'Counterintelligence');
INSERT INTO specialty VALUES (null, 'Sabotage');
INSERT INTO specialty VALUES (null, 'Doily Design');
INSERT INTO specialty VALUES (null, 'Explosives');
INSERT INTO specialty VALUES (null, 'Flower Arranging');

CREATE TABLE agent_specialty (
  agent_specialtyID INTEGER PRIMARY KEY,
  agentID int,
  specialtyID int
);

INSERT INTO agent_specialty VALUES (null, 1, 2);
INSERT INTO agent_specialty VALUES (null, 1, 3);
INSERT INTO agent_specialty VALUES (null, 2, 1);
INSERT INTO agent_specialty VALUES (null, 2, 6);
INSERT INTO agent_specialty VALUES (null, 3, 2);
INSERT INTO agent_specialty VALUES (null, 4, 4);
INSERT INTO agent_specialty VALUES (null, 4, 5);

CREATE TABLE badspy (
  agentID INTEGER PRIMARY KEY,
  name varchar(30),
  specialty varchar(40),
  assignment varchar(40),
  description varchar(40),
  location varchar(20)
);

INSERT INTO badspy VALUES (null, 'Rahab', 'Electronics, Counterintelligence', 'Raging Dandelion', 'Plant Crabgrass', 'Sudan');
INSERT INTO badspy VALUES (null, 'Gold Elbow', 'Sabatoge, Doily design', 'Dancing Elephant', 'Infiltrate suspicious zoo', 'London');
INSERT INTO badspy VALUES (null, 'Falcon', 'Counterintelligence', 'Dancing Elephant', 'Infiltrate suspicious circus', 'London');
INSERT INTO badspy VALUES (null, 'Cardinal', 'Sabatoge', 'Enduring Angst', 'Make bad guys feel really guilty', 'Lower Volta');
INSERT INTO badspy VALUES (null, 'Blackford', 'Explosives, Flower arranging', 'Enduring Angst', 'Make bad guys feel really guilty', 'Lower Votla');