/**
 * initializes the Student, Course, and Schedule tables and adds
 * a trigger and stored procedure
 * @version 080305
 * @author Pete Nordquist
 */

-- drop tables
DROP TABLE IF EXISTS Schedule;
DROP TABLE IF EXISTS Student;
DROP TABLE IF EXISTS Course;

-- create tables
CREATE TABLE Student (
	ID INT PRIMARY KEY,
	NAME VARCHAR(50)
	) engine=innodb;
CREATE TABLE Course (
	CRN INT PRIMARY KEY,
	NAME VARCHAR(50)
	) engine=innodb;
CREATE TABLE Schedule (
	ID INT,
	CRN INT, 
	constraint SchedulePK primary key(ID,CRN),
	constraint StudentFK foreign key(ID) references Student(ID)
		on delete cascade on update cascade,
	constraint CourseFK foreign key(CRN) references Course(CRN)
		on delete cascade on update cascade
	) engine=innodb;


-- Create a few Courses
INSERT INTO Course VALUES (1, 'cs257');
INSERT INTO Course VALUES (2, 'cs360');
INSERT INTO Course VALUES (3, 'cs310');
INSERT INTO Course VALUES (4, 'cs450');
INSERT INTO Course VALUES (5, 'pe180');


