open with acrobat adobe 

 

https://acrobat.adobe.com/link/review?uri=urn:aaid:scds:US:eb43f20a-f79f-374c-8674-c01e8d2bc568

 

In order to have a consistent database you will need to use the attached SQL file to create the tables. You may have to manually drop the tables in MySQL in order to run this. Once all tables are created then you will need to insert the data that is in the second document. All insert statements should be in one .sql file and submitted in the drop box.

 

TABLE TO BE USED

 

USE MSIS230;

 

DROP TABLE IF EXISTS ENROLL;

DROP TABLE IF EXISTS CLASS;

DROP TABLE IF EXISTS COURSEDEPT;

DROP TABLE IF EXISTS ADVISOR;

DROP TABLE IF EXISTS STUDENTDEPT;

DROP TABLE IF EXISTS DEPTCHAIR;

DROP TABLE IF EXISTS STUDENT;

DROP TABLE IF EXISTS COURSE;

DROP TABLE IF EXISTS DEPARTMENT;

DROP TABLE IF EXISTS PROFESSOR;

 

CREATE TABLE IF NOT EXISTS PROFESSOR

(

PROFID VARCHAR(45) PRIMARY KEY,

PROFSPECIALTY VARCHAR(45),

PROFRANK VARCHAR(45),

PROFLNAME VARCHAR(45) NOT NULL,

PROFFNAME VARCHAR(45) NOT NULL,

PROFINITIAL CHAR(2),

PROFEMAIL VARCHAR(45)

);

 

CREATE TABLE IF NOT EXISTS DEPARTMENT

(

DEPTID VARCHAR(45) PRIMARY KEY,

DEPTNAME VARCHAR(45) NOT NULL

);

 

CREATE TABLE IF NOT EXISTS COURSE

(

COURSEID VARCHAR(10) PRIMARY KEY,

COURSETITLE VARCHAR(45) NOT NULL,

COURSEDESCRIPTION VARCHAR(100),

COURSECREDITS INT

  );

 

CREATE TABLE IF NOT EXISTS STUDENT

(

STUDENTID VARCHAR(10) PRIMARY KEY,

STUDENTLNAME VARCHAR(45) NOT NULL,

STUDENTFNAME VARCHAR(45) NOT NULL,

STUDENTINITIAL CHAR(2),

STUDENTEMAIL VARCHAR(45)

);

 

##### TABLES W/ FOREIGN KEYS #####

 

CREATE TABLE IF NOT EXISTS DEPTCHAIR

(

PROFESSORID VARCHAR(45),

DEPTID VARCHAR(45),

CONSTRAINT PK_DEPTCHAIR PRIMARY KEY (PROFESSORID, DEPTID),

CONSTRAINT FK_DEPT FOREIGN KEY (PROFESSORID) REFERENCES PROFESSOR (PROFID),

CONSTRAINT FK_CHAIR FOREIGN KEY (DEPTID) REFERENCES DEPARTMENT (DEPTID)

);

 

CREATE TABLE IF NOT EXISTS STUDENTDEPT

(

STUDENTID VARCHAR(10),

DEPTID VARCHAR(45),

CONSTRAINT PK_STUDENTDEPT PRIMARY KEY (STUDENTID, DEPTID),

CONSTRAINT FK_STUDENT FOREIGN KEY (STUDENTID) REFERENCES STUDENT (STUDENTID),

CONSTRAINT FK_DEPARTMENT FOREIGN KEY (DEPTID) REFERENCES DEPARTMENT (DEPTID)

);

 

CREATE TABLE IF NOT EXISTS ADVISOR

(

PROFESSORID VARCHAR(45),

STUDENTID VARCHAR(10),

CONSTRAINT PK_ADVISOR PRIMARY KEY (PROFESSORID, STUDENTID),

CONSTRAINT FK_PROF FOREIGN KEY (PROFESSORID) REFERENCES PROFESSOR (PROFID),

CONSTRAINT FK2_STUDENT FOREIGN KEY (STUDENTID) REFERENCES STUDENT (STUDENTID)

);

 

CREATE TABLE IF NOT EXISTS COURSEDEPT

(

COURSEID VARCHAR(10),

DEPTID VARCHAR(45),

CONSTRAINT PK_COURSEDEPT PRIMARY KEY (COURSEID, DEPTID),

CONSTRAINT FK2_COURSE FOREIGN KEY (COURSEID) REFERENCES COURSE (COURSEID),

CONSTRAINT FK2_DEPT FOREIGN KEY (DEPTID) REFERENCES DEPARTMENT (DEPTID) 

);

 

CREATE TABLE IF NOT EXISTS CLASS

(

CLASSID VARCHAR(10),

COURSEID VARCHAR(10),

PROFID VARCHAR(45),

CLASSSECTION CHAR(2),

CLASSDAYS VARCHAR (25),

CLASSTIME VARCHAR (25),

CONSTRAINT PK_CLASS PRIMARY KEY (CLASSID, COURSEID),

CONSTRAINT FK3_COURSE FOREIGN KEY (COURSEID) REFERENCES COURSE (COURSEID)

);

 

CREATE TABLE IF NOT EXISTS ENROLL

(

STUDENTID VARCHAR(10),

COURSEID VARCHAR(10),

CLASSID VARCHAR(10),

ENROLL_DATE DATETIME NOT NULL,

GRADE CHAR(2) NOT NULL,

CONSTRAINT PK_ENROLL PRIMARY KEY (STUDENTID, COURSEID, CLASSID),

CONSTRAINT FK3_STUDENT FOREIGN KEY (STUDENTID) REFERENCES STUDENT (STUDENTID),

CONSTRAINT FK4_COURSE FOREIGN KEY (COURSEID) REFERENCES COURSE (COURSEID),

CONSTRAINT FK_CLASS FOREIGN KEY (CLASSID) REFERENCES CLASS (CLASSID)

);