-- build Hero db
-- modified for MySQL 4 - all views removed

-- badHero
DROP TABLE IF EXISTS badHero;

CREATE TABLE badHero (
  name varchar(50),
  powers varchar(250),
  villain varchar(50),
  plot varchar(50),
  mission varchar(50),
  age int
);

INSERT INTO badHero VALUES(
  'The Plumber',
  'Sewer snake of doom, unclogging, ability to withstand smells',
  'Septic Slime Master',
  'Overcome Chicago with slime',
  'Stop the Septic Slime',
  37
  );

INSERT INTO badHero VALUES (
  'Binary Boy',
  'Hexidecimation beam, obfuscation',
  'Octal',
  'Eliminate the numerals 8 and 9',
  'Make the world safe for binary representation',
  19
);

INSERT INTO badHero VALUES (
  'The Janitor',
  'Mighty Mop',
  'Septic Slim Master',
  'Overcome New York with slime',
  'Stop the Septic Slime',
  41
);
  

SELECT * FROM badHero;  

-- hero - contains only hero data and a foreign key link to mission
DROP TABLE IF EXISTS hero;
CREATE TABLE hero (
  heroID INTEGER PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  birthday DATE,
  missionID INTEGER
);

INSERT INTO hero VALUES (
  null, 'The Plumber', '1976-10-16', 1);
INSERT INTO hero VALUES (
  null, 'Binary Boy', '1988-1-27', 2);
INSERT INTO hero VALUES (
  null, 'The Janitor', '1964-8-30', 1);

SELECT * FROM hero;

-- mission table - contains all data relevant to mission
DROP TABLE IF EXISTS mission;
CREATE TABLE mission (
  missionID INTEGER PRIMARY KEY AUTO_INCREMENT,
  description VARCHAR(50),
  villain VARCHAR(30),
  plot VARCHAR(50)
);

INSERT INTO mission VALUES (
  null, 'Stop the septic slime', 
  'Septic Slime Master', 
  'Overcome city with Slime');
  
INSERT INTO mission VALUES (
  null, 'Make the world safe for Binary representation',
  'Octal', 
  'Eliminate the numerals 8 and 9');

SELECT * FROM mission;

-- standard query for joining hero and mission
SELECT 
  hero.name AS 'hero',
  mission.description AS 'mission'
FROM hero, mission
WHERE
  hero.missionID = mission.missionID;

-- power - has no foreign keys because it's part of many / many join
DROP TABLE IF EXISTS power;
CREATE TABLE power (
  powerID INTEGER PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50)
);

INSERT INTO power VALUES(null, 'Sewer snake of doom');
INSERT INTO power VALUES(null, 'unclogging');
INSERT INTO power VALUES(null, 'ability to withstand smells');
INSERT INTO power VALUES(null, 'Hexidecimation Beam');
INSERT INTO power VALUES(null, 'obfuscation');
INSERT INTO power VALUES(null, 'Mighty Mop');

SELECT * FROM power;

-- hero_power is a link table combining hero and power
-- it contains only foreign keys
DROP TABLE IF EXISTS hero_power;
CREATE TABLE hero_power (
  hero_powerID INTEGER PRIMARY KEY AUTO_INCREMENT,
  heroID INTEGER,
  powerID INTEGER
);

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

SELECT * FROM hero_power;

SELECT
  hero.name AS 'hero',
  power.name AS 'power'
FROM
  hero, power, hero_power
WHERE 
  hero.heroID = hero_power.heroID
AND
  power.powerID = hero_power.powerID;