-- ------------------------------------------------------
-- Author: Eric Jones <[email protected]>
-- Date: 2013-04-04
-- Purpose: Wrox Professional PHP 6 uses postgreSQL for
-- the examples in the book.  Here I have re-created
-- the SQL from the book for MySQL 5.5
-- 
-- This if for the examples provided in Chapter 5

-- ------------------------------------------------------
-- --- USE AT YOUR OWN RISK ----- I HOLD NO LIABILITY ---
-- ------------------------------------------------------

-- ------------------------------
-- Drop the database
--
-- Uncomment the statement below
-- to complete drop the database
-- and re-create it.
-- -------------------------------

# DROP DATABASE IF EXISTS `proPHP6`;

-- create the database

CREATE DATABASE IF NOT EXISTS `proPHP6` CHARACTER SET utf8 COLLATE utf8_general_ci;

-- switch to using the newly created database

USE `proPHP6`;

-- student table

CREATE TABLE IF NOT EXISTS `student` (
  `studentid` int(11)     unsigned NOT NULL AUTO_INCREMENT,
  `name`      varchar(45),
  PRIMARY KEY (`studentid`)
) ENGINE=InnoDB CHARSET=utf8;

-- course table

CREATE TABLE IF NOT EXISTS `course` (
  `courseid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `coursecode` varchar(10),
  `name` varchar(255),
  PRIMARY KEY (`courseid`)
) ENGINE=InnoDB CHARSET=utf8;

-- studentcourse

CREATE TABLE IF NOT EXISTS `studentcourse` (
  `studentid` int(10) unsigned NOT NULL,
  `courseid` int(10) unsigned NOT NULL,
  UNIQUE KEY `idx_studentcourse_unique` (`studentid`,`courseid`),
  KEY `fk_studentcourse_1_idx` (`studentid`),
  KEY `fk_studentcourse_2_idx` (`courseid`),
  CONSTRAINT `fk_studentcourse_1` 
	FOREIGN KEY (`studentid`) 
	REFERENCES `student` (`studentid`) 
		ON DELETE NO ACTION 
		ON UPDATE NO ACTION,
  CONSTRAINT `fk_studentcourse_2` 
	FOREIGN KEY (`courseid`) 
	REFERENCES `course` (`courseid`) 
		ON DELETE NO ACTION 
		ON UPDATE NO ACTION
) ENGINE=InnoDB CHARSET=utf8;

-- ---------------------------------------------
-- The tables that you add this data into should
-- be empty.  If they are not, the final block
-- of insert statements may not work as expected.
-- ----------------------------------------------

-- ----------------------------------------------
-- The statements below will empty the tables
-- of all data so that the data can be inserted
-- and work as expected.
-- 
-- Uncomment if needed.
-- ----------------------------------------------

# TRUNCATE TABLE `studentcourse`;
# TRUNCATE TABLE `course`;
# TRUNCATE TABLE `student`;

-- ------------------------------
-- student data
-- ------------------------------

-- studentid 1

INSERT INTO `student`(name) VALUES ('Bob Smith');

-- studentid 2

INSERT INTO `student`(name) VALUES ('John Doe');

-- studentid 3

INSERT INTO `student`(name) VALUES ('Jane Baker');

-- ------------------------------
-- course data
-- ------------------------------

-- courseid 1

INSERT INTO `course`(`coursecode`, `name`) VALUES ('CS101', 'Intro to Computer Science');

-- courseid 2

INSERT INTO `course`(`coursecode`, `name`) VALUES ('HIST369', 'British History 1945-1990');

-- courseid 3

INSERT INTO `course`(`coursecode`, `name`) VALUES ('BIO546', 'Advanced Genetics');

-- ------------------------------
-- studentcourse data
-- ------------------------------

-- NOTE: This will work only once the above insert statements have run.

INSERT INTO `studentcourse`(`studentid`,`courseid`) VALUES (1,1);
INSERT INTO `studentcourse`(`studentid`,`courseid`) VALUES (1,2);
INSERT INTO `studentcourse`(`studentid`,`courseid`) VALUES (1,3);
INSERT INTO `studentcourse`(`studentid`,`courseid`) VALUES (2,1);
INSERT INTO `studentcourse`(`studentid`,`courseid`) VALUES (2,3);
INSERT INTO `studentcourse`(`studentid`,`courseid`) VALUES (3,2);

-- End Of SQL