CREATE TABLE STUDENT (
	ID INT PRIMARY KEY, 
	NAME VARCHAR(100), 
	DESCRIPTION TEXT) ENGINE=INNODB;



CREATE TABLE COURSE (
	COURSE_ID   CHAR(5) PRIMARY KEY,
	COURSE_NAME VARCHAR(200),
	DESCRITION TEXT	
) ENGINE=INNODB; 


-- This table have a primary key defined on multiple columns
-- and reference the course and student table
CREATE TABLE GRADE (
	COURSE_ID CHAR(5),
	STUDENT_ID INT, 
	TERM CHAR(7),
	GRADE INT, 
	PRIMARY KEY (COURSE_ID, STUDENT_ID, TERM),
	CONSTRAINT FK_GRADE_COURSE  FOREIGN KEY (COURSE_ID)  REFERENCES COURSE(COURSE_ID) ON DELETE RESTRICT,
	CONSTRAINT FK_GRADE_STUDENT FOREIGN KEY (STUDENT_ID) REFERENCES STUDENT(ID) ON DELETE CASCADE ) ENGINE=INNODB;


-- Insertion statements
INSERT INTO STUDENT VALUES('25000','JHON DOE','CS STUDENT');
INSERT INTO STUDENT VALUES('35000','JHON ANDERSON','CS STUDENT');
INSERT INTO STUDENT VALUES('45000','AMANDA DOE','HISTORY STUDENT');


INSERT INTO COURSE VALUES('CS100','INTRO TO PROGRAMING','INTRODUCTION TO COMPUTER SCIENCE, ALGORITHMS AND BASIC PROGRAMMING: DATATYPES, CONTROL FLOW STRUCTURES AND DATA ABSTRACTION.');
INSERT INTO COURSE VALUES('HS100','AMERICAN HISTORY','DEFINTION OF HISTORY. HISTORY OF AMERICA FROM 1492 TO 1700');

INSERT INTO GRADE VALUES('CS100','25000','FALL09',10);
INSERT INTO GRADE VALUES('CS100','35000','FALL09',8);

-- This insertion must fail
INSERT INTO GRADE VALUES('CS100','75000','FALL09',8);

-- A simple select query (this is not executed unless you use the -f flag)
SELECT S.NAME, G.GRADE FROM STUDENT S JOIN GRADE G ON (ID = STUDENT_ID);