/*
init script for Example Join queries

*/

-- Drop tables in reverse FK order
Drop table if exists Watches;
Drop table if exists Plays;
Drop table if exists Player0;
Drop table if exists Sport;

-- Create tables
CREATE TABLE `Sport` (
   `name` varchar(50) NOT NULL,
   `numPlayers` int(11),
   PRIMARY KEY (`name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `Player0` (
   `name` varchar(50) NOT NULL,
   `height` int(11),
   PRIMARY KEY (`name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `Plays` (
   `player` varchar(50) NOT NULL,
   `sport` varchar(50) NOT NULL,
   PRIMARY KEY (`player`, `sport`),
   CONSTRAINT `playsPlayerFK` FOREIGN KEY (`player`) 
		REFERENCES `Player0` (`name`)
		ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `playsSportFK` FOREIGN KEY (`sport`) 
		REFERENCES `Sport` (`name`)
		ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `Watches` (
   `name` varchar(50) NOT NULL,
   `sport` varchar(50) NOT NULL,
   PRIMARY KEY (`name`, `sport`),
   CONSTRAINT `watchesNameFK` FOREIGN KEY (`name`) 
		REFERENCES `Player0` (`name`)
		ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `watchesSportFK` FOREIGN KEY (`sport`) 
		REFERENCES `Sport` (`name`)
		ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Insert data
Insert into Player0
	(name, height) values
	('BillieJean', 65),
	('BillyJoeBob', 78),
	('EmmaJean', 72),
	('Jimbob', 60),
	('Wilt', 81);

Insert into Sport
	(name, numPlayers) values
	('baseball', 9),
	('basketball', 5),
	('tennis', 1),
	('volleyball', 6),
	('wrestling', 1);

Insert into Plays
	(player, sport) values
	('EmmaJean', 'basketball'),
	('EmmaJean', 'volleyball'),
	('Jimbob', 'tennis'),
	('Jimbob', 'wrestling'),
	('Wilt', 'basketball');

Insert into Watches
	(name, sport) values
	('Jimbob', 'tennis'),
	('Jimbob', 'volleyball'),
	('Wilt', 'volleyball');
