sqlqueries.txt

CREATE TABLE STUDENT_INFO (
    Student_ID varchar(9) NOT NULL,
   Student_FName varchar(50) NOT NULL,
    Student_LName varchar(50) NOT NULL,
    Student_Email varchar(17) NOT NULL,
    Student_Password varchar(35) NOT NULL,
    PRIMARY KEY (Student_ID)
);
 
CREATE TABLE PROFESSOR_INFO (
    Professor_ID varchar(20) NOT NULL,
    Professor_Name varchar(100) NOT NULL,
    Professor_Email varchar(29) NOT NULL,
    Warr_office varchar(8),
    MIC_office varchar(8),
    PRIMARY KEY (Professor_ID)
);
Prof info insert
INSERT INTO professor_info VALUES ("aksoy", "Ahmet Aksoy", "aksoy@ucmo.edu", "WCM115", NULL);
INSERT INTO professor_info VALUES ("hchen", "Hang Chen", "hchen@ucmo.edu", "WCM205C", NULL);
INSERT INTO professor_info VALUES ("copus", "Belinda Copus", "copus@ucmo.edu", "WCM206C", NULL);
INSERT INTO professor_info VALUES ("grebe", "Mark Grebe", "grebe@ucmo.edu", "WCM213", NULL);
INSERT INTO professor_info VALUES ("dding", "Dabin Ding", "dding@ucmo.edu", "WCM211", NULL);
INSERT INTO professor_info VALUES ("haque", "Taiabul Haque", "haque@ucmo.edu", "WCM116X", NULL);
INSERT INTO professor_info VALUES ("thorn", "Trang Horn", "thorn@ucmo.edu", "WCM205D", NULL);
INSERT INTO professor_info VALUES ("tkang", "Taeghyun Kang", "tkang@ucmo.edu", "WCM219", NULL);
INSERT INTO professor_info VALUES ("lui", "Yui Man Lui", "lui@ucmo.edu", "WCM119", NULL);
INSERT INTO professor_info VALUES ("park", "Hyungbae Park", "park@ucmo.edu", "WCM223", NULL);
INSERT INTO professor_info VALUES ("ipark", "Ikhyun Park", "ipark@ucmo.edu", "WCM221", NULL);
INSERT INTO professor_info VALUES ("arawashdeh", "Ahmad Rawashdeh", "arawashdeh@ucmo.edu", NULL, "MICD162");
INSERT INTO professor_info VALUES ("rawashdeh", "Mohammad Rawashdeh", "rawashdeh@ucmo.edu", NULL, "MICD158");
INSERT INTO professor_info VALUES ("reza", "Farhadur Reza", "reza@ucmo.edu", NULL, "MICD160");
INSERT INTO professor_info VALUES ("sshin", "Sunae Shin", "sshin@ucmo.edu", NULL, "MICD159");
INSERT INTO professor_info VALUES ("tian", "Songlin Tian", "tian@ucmo.edu", "WCM215", NULL);
INSERT INTO professor_info VALUES ("jwang", "Jun Wang", "jwang@ucmo.edu", "WCM116L", NULL);
INSERT INTO professor_info VALUES ("yousef", "Mahmoud Yousef", "yousef@ucmo.edu", "WCM217", NULL);
INSERT INTO professor_info VALUES ("yue", "Xiaodong Yue", "yue@ucmo.edu", "WCM222C", NULL);
INSERT INTO professor_info VALUES ("zzhou", "Zhiguo Zhou", "zzhou@ucmo.edu", "WCM116F", NULL);

CREATE TABLE COURSE_LIST(
    course_name varchar(255) NOT NULL,
    course_code varchar(8) NOT NULL,
    professor_ID varchar(20) NOT NULL,
    FOREIGN KEY (professor_ID) 
REFERENCES professor_info(professor_ID)
ON DELETE CASCADE
);



CREATE TABLE Available_times(
    available_id int auto_increment,
    location varchar(8) NOT NULL,
    day varchar(9) NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    date DATE NOT NULL,
    available BOOLEAN NOT NULL,
    professor_ID varchar(20) NOT NULL,
    PRIMARY KEY(appointment_id),
    FOREIGN KEY (professor_ID) REFERENCES professor_info(professor_ID)ON DELETE CASCADE,

CONSTRAINT mustStartOnTHIRTYMinuteBoundary CHECK (
        EXTRACT(MINUTE FROM start_time) % 30 = 0
        AND EXTRACT(SECOND FROM start_time) = 0
    ),
    CONSTRAINT mustEndOnTHIRTYMinuteBoundary CHECK (
        EXTRACT(MINUTE FROM end_time) % 30 = 0
        AND EXTRACT(SECOND FROM end_time) = 0
    ),
    CONSTRAINT cannotStartBefore0800 CHECK (
        EXTRACT(HOUR FROM start_time) >= 8
    ),
    CONSTRAINT cannotEndAfter1700 CHECK (
        EXTRACT(HOUR FROM (start_time - INTERVAL 1 SECOND)) < 17
    ),
    CONSTRAINT mustEndAfterStart CHECK (
        end_time > start_time
    )

);

INSERT INTO AVAILTIMES TABLE FOR PROFS
INSERT INTO Available_times VALUES (“THE LOCATION”, WEEKDAY('2020-02-07'),”09:30”, “10:00”, '2020-02-07', “true/false”, “prof id”);
Make switch() statement on JS end to display the days based on the WEEKDAY return value:
WEEKDAY(): 0 - Monday, 1-Tuesday, 2-Wednesday, …. 6-Sunday

CREATE TABLE APPOINTMENT (
    Professor_Email varchar(20) NOT NULL,
    Student_Email varchar(17) NOT NULL,
    course_code varchar(6) NOT NULL,
    purpose varchar(25) NOT NULL,
    office varchar(8) NOT NULL,
    start_time time NOT NULL,
    end_time time NOT NULL,
    appointment_description varchar(255),
    student_fname varchar(50) NOT NULL,
    student_lname varchar(50) NOT NULL,
    sent bool NOT NULL,
    appointment_date date NOT NULL,
    appointment_id int auto_increment,
    PRIMARY KEY(appointment_id)
);