Question

After reviewing the various ERDs, Trinity University has decided on the following tables and columns. Primary key fields are highlighted in red.

PLEASE USE MYSQL WORBENCH AND SCREEN SHOT

Base Tables:

PROFESSOR ProfID ProfSpecialty ProfRank ProfLName (NN) ProfFName (NN) ProfInitial ProfEmail

DEPARTMENT DeptID DeptName (NN)

COURSE CourseID CourseTitle (NN) CourseDescription CourseCredits

STUDENT StudentID StudentLName (NN) StudentFName (NN) StudentInitial StudentEmail

Tables with Foreign Keys:

DEPTCHAIR ProfessorID DeptID	STUDENTDEPT StudentID DeptID	ADVISOR ProfessorID StudentID	COURSEDEPT CourseID DeptID
CLASS ClassID CourseID ProfID ClassSection ClassDays ClassTime	ENROLL Student ID CourseID ClassID EnrollmentDate (NN) Grade (NN)

Write the SQL code to drop and then create the above tables. Remember that when creating tables, the tables with foreign keys have to be created after the table that the foreign key points to. When dropping tables, they have to be dropped in the reverse order, so that the tables with foreign keys are dropped before the tables that the foreign keys point to. You can create all the drop SQL statements first followed by all the create tables. The first time you run them the drop statements will work as no tables have been created and therefore no drops will occur. You will need to run them twice to insure the drops will work.

All primary key fields and fields marked as (NN) are required fields, all others are optional. Use appropriate data types for each column.

I am trying to do but it doesnt work

DROP TABLE IF EXISTS PROFESSOR;
DROP TABLE IF EXISTS DEPARTMENT;
DROP TABLE IF EXISTS COURSE;
DROP TABLE IF EXISTS STUDENT;
DROP TABLE IF EXISTS DEPTCHAIR;
DROP TABLE IF EXISTS STUDENTDEPT;
DROP TABLE IF EXISTS ADVISOR;
DROP TABLE IF EXISTS COURSEDEPT;
DROP TABLE IF EXISTS CLASS;
DROP TABLE IF EXISTS ENROLL;

CREATE TABLE IF NOT EXISTS PROFESSOR
(
Prof_ID int PRIMARY KEY,
Prof_Speciality varchar(20),
Prof_Rank int,
Prof_LName varchar(20),
Prof_FName varchar(20),
Prof_Initial varchar(20),
Prof_Emai varchar
);

Answer 1

Answer:

CREATE TABLE PROFESSOR(
ProfID int,
ProfSpecialty varchar(15),
ProfRank varchar(5),
ProfLName varchar(15) NOT NULL,
ProfFName varchar(15) NOT NULL,
ProfInitial varchar(10),
ProfEmail varchar(20),
PRIMARY KEY(ProfID));

CREATE TABLE DEPARTMENT(
DeptID int,
DeptName varchar(15) NOT NULL,
PRIMARY KEY(DeptID));

CREATE TABLE COURSE(
CourseID int,
CourseTitle varchar(10) NOT NULL,
CourseDescription varchar(20),
CourseCredits decimal(4,2),
PRIMARY KEY(CourseID));

CREATE TABLE STUDENT(
StudentID int,
StudentLName varchar(10) NOT NULL,
StudentFName varchar(10) NOT NULL,
StudentInitial varchar(10),
StudentEmail varchar(20),
PRIMARY KEY(StudentID));

CREATE TABLE DEPTCHAIR(
ProfessorID int,
DeptID int,
PRIMARY KEY(ProfessorID, DeptID),
FOREIGN KEY (ProfessorID) REFERENCES PROFESSOR(ProfID),
FOREIGN KEY (DeptID) REFERENCES DEPARTMENT(DeptID));

CREATE TABLE CLASS(
ClassID int,
CourseID int,
ProfID int,
ClassSection varchar(10),
ClassDays int,
ClassTime varchar(10),
PRIMARY KEY(ClassID, CourseID,ProfID),
FOREIGN KEY (CourseID) REFERENCES COURSE(CourseID),
FOREIGN KEY (ProfID) REFERENCES PROFESSOR(ProfID));

CREATE TABLE STUDENTDEPT(
StudentID int,
DeptID int,
PRIMARY KEY(StudentID, DeptID),
FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID),
FOREIGN KEY (DeptID) REFERENCES DEPARTMENT(DeptID));

CREATE TABLE ENROLL(
StudentID int,
CourseID int,
ClassID int,
EnrollmentDate date NOT NULL,
Grade varchar(4) NOT NULL,
PRIMARY KEY(StudentID,CourseID, ClassID),
FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID),
FOREIGN KEY (CourseID) REFERENCES COURSE(CourseID),
FOREIGN KEY (ClassID) REFERENCES CLASS(ClassID));

CREATE TABLE ADVISOR(
ProfessorID int,
StudentID int,
PRIMARY KEY(ProfessorID,StudentID),
FOREIGN KEY (ProfessorID) REFERENCES PROFESSOR(ProfID),
FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID));

CREATE TABLE COURSEDEPT(
CourseID int,
DeptID int,
PRIMARY KEY(CourseID,DeptID),
FOREIGN KEY (CourseID) REFERENCES COURSE(CourseID),
FOREIGN KEY (DeptID) REFERENCES DEPARTMENT(DeptID));

DROP TABLE IF EXISTS DEPTCHAIR;
DROP TABLE IF EXISTS STUDENTDEPT;
DROP TABLE IF EXISTS ADVISOR;
DROP TABLE IF EXISTS COURSEDEPT;
DROP TABLE IF EXISTS ENROLL;
DROP TABLE IF EXISTS CLASS;
DROP TABLE IF EXISTS PROFESSOR;
DROP TABLE IF EXISTS DEPARTMENT;
DROP TABLE IF EXISTS COURSE;
DROP TABLE IF EXISTS STUDENT;

Screenshot: