SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS STUDENT;

DROP TABLE  IF EXISTS CAMPUS;

DROP TABLE  IF EXISTS ROOM;

DROP TABLE  IF EXISTS COURSE;

DROP TABLE  IF EXISTS INSTRUCTOR;

DROP TABLE  IF EXISTS APPROVED_INSTRUCTOR;

DROP TABLE  IF EXISTS CLASS;

DROP TABLE  IF EXISTS STUDENT_GRADE;

SET FOREIGN_KEY_CHECKS=1;

 

CREATE TABLE STUDENT(

Student_ID   INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,

Last_NameVARCHAR(30) NOT NULL,

First_NameVARCHAR(30) NOT NULL,

Address      VARCHAR(40),

Zip          INTEGER,

Major        VARCHAR(20),

Class        INTEGER,

Status       VARCHAR(20),

CONSTRAINT Status CHECK(Status > 0 AND Status <= 4)

);

 

INSERT INTO STUDENT (Last_Name, First_Name, Address, Zip, Major, Class, Status)

                VALUES('Mills','Rana','P.O. Box 619, 5808 Semper St.',32641,'MATH',2,'FAILING');

INSERT INTO STUDENT (Last_Name, First_Name, Address, Zip, Major, Class, Status)

                VALUES('Snow','Holmes','313-6948 Nunc, Ave',36702,'SPANISH',3,'SATISFACTORY');

INSERT INTO STUDENT (Last_Name, First_Name, Address, Zip, Major, Class, Status)       

                VALUES('Woodward','Dustin','Ap #593-8291 Id St.',26151,'BUSINESS',1,'SATISFACTORY');

 

 

CREATE TABLE CAMPUS

(

Campus_Name  VARCHAR(40)  NOT NULL,

Address      VARCHAR(40),

Zip          INTEGER,

Phone_NoVARCHAR(20),

UNIQUE(Campus_Name),

PRIMARY KEY(Campus_Name));

 

INSERT INTO CAMPUS (Campus_Name, Address, Zip, Phone_No)

                VALUES('Newport Campus','405-6298 In Street',62840,'1-639-674-3520');

INSERT INTO CAMPUS (Campus_Name, Address, Zip, Phone_No)

                VALUES('Colli a Volturno Campus','820-7808 Risus, Rd.',51852,'1-824-430-8362');

INSERT INTO CAMPUS (Campus_Name, Address, Zip, Phone_No)

                VALUES('Lens-Saint-Remy Campus','505-182 Luctus, Ave',21345,'1-460-439-2331');

 

CREATE TABLE ROOM

(

Room_NoINTEGER  NOT NULL,

Campus_Name  VARCHAR(40) NOT NULL,

Facility     VARCHAR(15),

Capacity     INTEGER,

PRIMARY KEY(Room_No, Campus_Name),

CONSTRAINT CampusFK FOREIGN KEY(Campus_Name) REFERENCES CAMPUS(Campus_Name)

);

 

INSERT INTO ROOM(Room_No,Campus_Name,Facility,Capacity)             

                VALUES(6,'Newport Campus','Lloydminster',30);

INSERT INTO ROOM(Room_No,Campus_Name,Facility,Capacity)             

                VALUES(19,'Colli a Volturno Campus','Erode',32);

INSERT INTO ROOM(Room_No,Campus_Name,Facility,Capacity)             

                VALUES(40,'Lens-Saint-Remy Campus','Cantalupo in Sabina',38);

 

CREATE TABLE COURSE

(

Course_NoINTEGER  NOT NULL PRIMARY KEY,

Course_Name  Varchar(25)  NOT NULL,

CreditHours  INTEGER,

UNIQUE(Course_Name),

CONSTRAINT CreditHrsCHECK(CreditHours> 0 AND CreditHours<= 4)

);

 

INSERT INTO COURSE(Course_No,Course_Name,CreditHours) 

                VALUES(1043,'TECH121',1);

INSERT INTO COURSE(Course_No,Course_Name,CreditHours) 

                VALUES(1006,'Psy101',3);

INSERT INTO COURSE(Course_No,Course_Name,CreditHours) 

                VALUES(1696,'Psy300',2);

 

CREATE TABLE INSTRUCTOR

(

Instructor_ID INTEGER       NOT NULL PRIMARY KEY,

Last_NameVARCHAR(30)   NOT NULL,

First_NameVARCHAR(20)   NOT NULL,

Address       VARCHAR(40),

Zip           INTEGER,

Office_No     INTEGER

);

 

INSERT INTO INSTRUCTOR(Instructor_ID,Last_Name,First_Name,Address,Zip,Office_No)

                VALUES(400,'Thane','Puckett','P.O. Box 895, 191 Erat Rd.',53841,30);

INSERT INTO INSTRUCTOR(Instructor_ID,Last_Name,First_Name,Address,Zip,Office_No)

                VALUES(737,'Jared','Tate','Ap #177-1334 Faucibus St.',88675,21);

INSERT INTO INSTRUCTOR(Instructor_ID,Last_Name,First_Name,Address,Zip,Office_No)

                VALUES(640,'Yuli','Hurst','Ap #799-5088 Scelerisque St.',26162,12);

 

CREATE TABLE APPROVED_INSTRUCTOR

(

Instructor_ID INTEGER NOT NULL,

Course_No     INTEGER NOT NULL,

PRIMARY KEY(Instructor_ID, Course_No),

CONSTRAINT InstructorFK           FOREIGN KEY(Instructor_ID) REFERENCES INSTRUCTOR(Instructor_ID),

CONSTRAINT CourseFK FOREIGN KEY(Course_No) REFERENCES COURSE(Course_No)

);

 

INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)

                VALUES(400,1043);

INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)

                VALUES(737,1006);

INSERT INTO APPROVED_INSTRUCTOR(Instructor_ID, Course_No)

                VALUES(640,1696);

 

Create TABLE CLASS

(

Class_ID               INTEGER NOT NULL PRIMARY KEY,

Room_No           INTEGER NOT NULL,

Course_No         INTEGER NOT NULL,

Section_No        INTEGER,

Semester_Name             Varchar(15),

Years                     INTEGER,

Instructor_ID     INTEGER NOT NULL,

Campus_Name Varchar(15),

Start_Date          Datetime,

Start_Time          Datetime,

CONSTRAINT InstructoridFK        FOREIGN KEY(Instructor_ID) REFERENCES APPROVED_INSTRUCTOR(Instructor_ID),

CONSTRAINT RoomNoFK                             FOREIGN KEY(Room_No) REFERENCES ROOM(Room_No)

);

 

INSERT INTO CLASS(Class_ID,Room_No,Course_No,Section_No,Semester_Name,Years,Instructor_ID,Campus_Name,Start_Date,Start_Time)

                VALUES(12408,6,1043,10,'Fall',2014,'400','Newport Campus','2014-10-08','8:00');

INSERT INTO CLASS(Class_ID,Room_No,Course_No,Section_No,Semester_Name,Years,Instructor_ID,Campus_Name,Start_Date,Start_Time)

                VALUES(15716,19,1006,10,'Other',2014,'737','Newport Campus','2014-10-08','8:00');

INSERT INTO CLASS(Class_ID,Room_No,Course_No,Section_No,Semester_Name,Years,Instructor_ID,Campus_Name,Start_Date,Start_Time)

                VALUES(12525,40,1696,10,'Other',2014,'640','Newport Campus','2014-10-08','8:00');

 

CREATE TABLE STUDENT_GRADE

(

Student_ID         INTEGER NOT NULL,

Class_ID               INTEGER NOT NULL,

Student_Grade                ENUM('A','B','C','D','F','I','W'),

PRIMARY KEY(Student_ID, Class_ID),

CONSTRAINT StudentIDFK           FOREIGN KEY(Student_ID) REFERENCES STUDENT(Student_ID),

CONSTRAINT ClassIDFK FOREIGN KEY(Class_ID) REFERENCES CLASS(Class_ID)

);

 

INSERT INTO STUDENT_GRADE(Student_ID,Class_ID,Student_Grade)

                VALUES(1,12408,'A');

INSERT INTO STUDENT_GRADE(Student_ID,Class_ID,Student_Grade)

                VALUES(2,12408,'B');

INSERT INTO STUDENT_GRADE(Student_ID,Class_ID,Student_Grade)

                VALUES(1,15716,'C');

 

Commit;

 

SHOW TABLES;

 

SELECT * FROM STUDENT;

 

SELECT * FROM CAMPUS;

 

SELECT * FROM ROOM;

 

SELECT * FROM COURSE;

 

SELECT * FROM INSTRUCTOR;

 

SELECT * FROM APPROVED_INSTRUCTOR;

 

SELECT * FROM CLASS;

 

SELECT * FROM STUDENT_GRADE;