--
--
-- 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;
DROP TABLE IF EXISTS school;
DROP TABLE IF EXISTS Grade;


--

CREATE TABLE IF NOT EXISTS Grade (
    grade VARCHAR(2),
    gradePoint DECIMAL(5,4),
    CONSTRAINT Grade_grade_pk PRIMARY KEY (grade)
);

CREATE TABLE IF NOT EXISTS School (
    schoolCode VARCHAR(3),
    schoolName VARCHAR(30),
    CONSTRAINT School_schoolCode_pk PRIMARY KEY (schoolCode), 
    CONSTRAINT School_name_ck UNIQUE (schoolName)
);

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),
    CONSTRAINT Department_schoolCode_fk FOREIGN KEY (schoolCode) 
        REFERENCES School(schoolCode)
);

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,
    credits     TINYINT,
    CONSTRAINT Course_courseId_pk PRIMARY KEY (courseId),
    CONSTRAINT Course_deptCode_fk FOREIGN KEY (rubric) 
        REFERENCES Department(deptCode));
    
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),
    n_alerts   INT,
    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,
    CONSTRAINT Enroll_grade_fk FOREIGN KEY (grade) 
        REFERENCES Grade (grade) ON DELETE CASCADE
);

INSERT INTO Grade VALUES 
    ('A',4),('A-',3.6667),('B+',3.3333),('B',3),('B-',2.6667),
    ('C+',2.3333),('C',2),('C-',1.6667),
    ('D+',1.3333),('D',1),('D-',0.6667),('F',0),
    ('WX', NULL);
    
INSERT INTO School VALUES 
    ('BUS','Business'),('EDU','Education'),
    ('HSH','Human Sciences and Humanities'),
    ('CSE','Science and Engineering');

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

INSERT INTO Faculty VALUES
    (1011,'Paul','Smith','CSCI','Professor'),
    (1012,'Mary','Tran','CSCI','Associate Professor'),
    (1013,'David','Love','CSCI',NULL),
    (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',3),
    (2001,'CSCI',4333,'Design of Database Systems',3),
    (2002,'CSCI',5333,'DBMS',3),
    (2020,'CINF',3321,'Introduction to Information Systems',3),
    (2021,'CINF',4320,'Web Application Development',3),
    (2040,'ITEC',3335,'Database Development',3),
    (2041,'ITEC',3312,'Introduction to Scripting',3),
    (2060,'ENGL',1410,'English I',4),
    (2061,'ENGL',1311,'English II',3),
    (2080,'ARTS',3311,'Hindu Arts',3),
    (2090,'ACCT',3333,'Managerial Accounting',3);

INSERT INTO Class VALUES
    (10000,2000,'Fall',2019,1011,'D241'),
    (10001,2001,'Fall',2019,1011,'D242'),
    (10002,2002,'Fall',2019,1012,'D136'),
    (10003,2020,'Fall',2019,1014,'D241'),
    (10004,2021,'Fall',2019,1014,'D241'),
    (10005,2040,'Fall',2019,1015,'D237'),
    (10006,2041,'Fall',2019,1019,'D217'),
    (10007,2060,'Fall',2019,1020,'B101'),
    (10008,2080,'Fall',2019,1018,'D241'),
    (11000,2000,'Spring',2020,1011,'D241'),
    (11001,2001,'Spring',2020,1013,'D242'),
    (11002,2002,'Spring',2020,1013,'D136'),
    (11003,2020,'Spring',2020,1016,'D217'),
    (11004,2061,'Spring',2020,1018,'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,NULL),
    (100008,'Bill','Ching','ARTS','ENGL',90,1018),
    (100009,'Linda','King','ARTS','CSCI',125,1018);
    
INSERT INTO Enroll VALUES
    (100000,10000,'A',0),
    (100001,10000,NULL,NULL),
    (100002,10000,'B-',3),
    (100000,10001,'A',2),
    (100001,10001,'A-',0),
    (100000,10002,'B+',1),
    (100002,10002,'B+',2),
    (100000,10003,'C',0),
    (100002,10003,'D',4),
    (100004,10003,'A',0),
    (100005,10003,NULL,NULL),
    (100000,10004,'A-',1),
    (100004,10004,'B+',NULL),
    (100005,10004,'A-',0),
    (100006,10004,'C+',NULL),
    (100005,10005,'A-',0),
    (100006,10005,'A',NULL),
    (100005,10006,'B+',NULL),
    (100007,10007,'F',4),
    (100008,10007,'C-',0),
    (100007,10008,'A-',0),
    (100000,11001,'D',4);
    
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;