--
-- Create a very simplified university database 
--
--
-- Drop tables for house-keeping if necessary
--

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

DROP SCHEMA IF EXISTS toyu;
CREATE SCHEMA toyu;
USE toyu;

DROP TABLE IF EXISTS Enroll;
DROP TABLE IF EXISTS Student;
DROP TABLE IF EXISTS Class;
DROP TABLE IF EXISTS Course;
DROP TABLE IF EXISTS Faculty;
DROP TABLE IF EXISTS Department;

--
CREATE TABLE IF NOT EXISTS Department (
    deptCode    VARCHAR(4),
	deptName	VARCHAR(30),
	schoolCode	VARCHAR(3),
	numFaculty	TINYINT,
	CONSTRAINT Department_deptCode_pk PRIMARY KEY (deptCode),
	CONSTRAINT Department_name_ck UNIQUE (deptName)
);

CREATE TABLE IF NOT EXISTS Faculty (
	facId		INT NOT NULL,
	fname	 	VARCHAR(20) NOT NULL,
	lname	 	VARCHAR(20) NOT NULL,
	deptCode	VARCHAR(4) NOT NULL,
	`rank` 		VARCHAR(25),
	CONSTRAINT Faculty_facId_pk PRIMARY KEY (facId),
	CONSTRAINT Faculty_deptCode_fk FOREIGN KEY (deptCode) 
		REFERENCES Department(deptCode));

CREATE TABLE IF NOT EXISTS Course (
	courseId 	INT NOT NULL,
	rubric		char(4) NOT NULL,
	number	 	char(4) NOT NULL,
	name		VARCHAR(80) NOT NULL,
	CONSTRAINT Course_courseId_pk PRIMARY KEY (courseId));
	
CREATE TABLE IF NOT EXISTS Class (
	classId		INT NOT NULL AUTO_INCREMENT,
	courseId 	INT NOT NULL,
	semester	VARCHAR(10) NOT NULL,
	year		DECIMAL(4,0) NOT NULL,
	facId	 	INT NOT NULL,
	room		VARCHAR(6),
	CONSTRAINT Class_classId_pk PRIMARY KEY (classId),
	CONSTRAINT Class_courseId_fk FOREIGN KEY (courseId) 
		REFERENCES Course(courseId) ON DELETE CASCADE,
	CONSTRAINT Class_facId_fk FOREIGN KEY (facId) 
		REFERENCES Faculty (facId) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS Student	(
	stuId		INT NOT NULL,
	fname	 	VARCHAR(20) NOT NULL,
	lname	 	VARCHAR(20) NOT NULL,
	major		VARCHAR(4) NULL,
	minor		VARCHAR(4) NULL,
	credits  	integer(3) DEFAULT 0,
	advisor		INT NULL,
	CONSTRAINT Student_stuId_pk PRIMARY KEY(stuId),
	CONSTRAINT Student_credits_cc CHECK ((credits>=0) AND (credits < 250)),
	CONSTRAINT Student_major_fk FOREIGN KEY (major) 
		REFERENCES Department(deptCode) ON DELETE CASCADE,
	CONSTRAINT Student_minor_fk FOREIGN KEY (minor) 
		REFERENCES Department(deptCode) ON DELETE CASCADE,
	CONSTRAINT Student_advisor_fk FOREIGN KEY (advisor) 
		REFERENCES Faculty(facId)
); 
	

CREATE TABLE IF NOT EXISTS Enroll(
	stuId		INT NOT NULL,
	classId		INT NOT NULL,
	grade		VARCHAR(2),
	CONSTRAINT Enroll_classId_stuId_pk PRIMARY KEY (classId, stuId),
	CONSTRAINT Enroll_classNumber_fk FOREIGN KEY (classId) 
		REFERENCES Class(classId) ON DELETE CASCADE,	
	CONSTRAINT Enroll_stuId_fk FOREIGN KEY (stuId) 
		REFERENCES Student (stuId) ON DELETE CASCADE
);

INSERT INTO DEPARTMENT VALUES 
	('CSCI','Computer Science','CSE',12),
	('CINF','Computer Information Systems','CSE',5),
	('ITEC','Information Technology','CSE',4),
	('ARTS','Arts','HSH',5),
	('ENGL','English','HSH',12),
	('ACCT','Accounting','BUS',10);

INSERT INTO FACULTY VALUES('1011','Paul','Smith','CSCI','Professor'),
	('1012','Mary','Tran','CSCI','Associate Professor'),
	('1013','David','Love','CSCI','Associate Professor'),
	('1014','Sharon','Mannes','CSCI','Assistant Professor'),
	('1015','Daniel','Kim','CINF','Professor'),
	('1016','Andrew','Byre','CINF','Associate Professor'),
	('1017','Deborah','Gump','ITEC','Professor'),
	('1018','Art','Allister','ARTS','Assistant Professor'),
	('1019','Benjamin','Yu','ITEC','Lecturer'),
	('1020','Katrina','Bajaj','ENGL','Lecturer'),
	('1021','Jorginlo','Neymar','ACCT','Assistant Professor');

INSERT INTO COURSE VALUES
	(2000, 'CSCI', '3333', 'Data Structures'),
	(2001, 'CSCI', '4333', 'Design of Database Systems'),
	(2002, 'CSCI', '5333', 'DBMS'),
	(2020, 'CINF', '3321', 'Introduction to Information Systems'),
	(2021, 'CINF', '4320', 'Web Application Development'),
	(2040, 'ITEC', '3335', 'Database Development'),
	(2041, 'ITEC', '3312', 'Introduction to Scripting'),
	(2060, 'ENGL', '1310', 'English I'),
	(2061, 'ENGL', '1311', 'English II'),
	(2080, 'ARTS', '3311', 'Hindu Arts'),
	(2090, 'ACCT', '3311', 'Managerial Accounting');
	

INSERT INTO CLASS VALUES
	(10000,2000,'Fall',2017,1011,'D241'),
	(10001,2001,'Fall',2017,1011,'D242'),
	(10002,2002,'Fall',2017,1012,'D136'),
	(10003,2020,'Fall',2017,1014,'D241'),
	(10004,2021,'Fall',2017,1014,'D241'),
	(10005,2040,'Fall',2017,1015,'D237'),
	(10006,2041,'Fall',2017,1019,'D217'),
	(10007,2060,'Fall',2017,1020,'B101'),
	(10008,2080,'Fall',2017,1018,'D241'),
	(11000,2000,'Spring',2018,1011,'D241'),
	(11001,2001,'Spring',2018,1013,'D242'),
	(11002,2002,'Spring',2018,1013,'D136'),
	(11003,2020,'Spring',2018,1016,'D217'),
	(11004,2061,'Spring',2018,1020,'B101')
;	

INSERT INTO STUDENT VALUES
	(100000,'Tony','Hawk','CSCI','CINF',40,1011),
	(100001,'Mary','Hawk','CSCI','CINF',35,1011),
	(100002,'David','Hawk','CSCI','ITEC',66,1011),
	(100003,'Catherine','Lim','ITEC','CINF',20,1017),
	(100004,'Larry','Johnson','ITEC',null,66,1017),
	(100005,'Linda','Johnson','CINF','ENGL',13,1015),
	(100006,'Lillian','Johnson','CINF','ITEC',18,1015),
	(100007,'Ben','Zico',null,null,16,1014),
	(100008,'Bill','Ching','ARTS','ENGL',90,1018),
	(100009,'Linda','King','ARTS','CSCI',125,1018)
;
	
INSERT INTO ENROLL VALUES
	(100000,10000,'A'),
	(100001,10000,null),
	(100002,10000,'B-'),
	(100000,10001,'A'),
	(100001,10001,'A-'),
	(100000,10002,'B+'),
	(100002,10002,'B+'),
	(100000,10003,'A'),
	(100002,10003,'B-'),
	(100004,10003,'A'),
	(100005,10003,null),
	(100000,10004,'A-'),
	(100004,10004,'B+'),
	(100005,10004,'A-'),
	(100006,10004,'C+'),
	(100005,10005,'A-'),
	(100006,10005,'A'),
	(100005,10006,'B+'),
	(100007,10007,'B+'),
	(100008,10007,'C-'),
	(100007,10008,'A')
;
	
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;