Question

Phase 1 – ERD

In this phase of the project you will create an ERD based upon the following requirements and business rules. Limit your ERD to entities and relationships based on the business rules showed here. In other words, do not add realismto your design by expanding or refining the business rules.  However, make sure you include all attributes needed that would permit the model to be successfully implemented, including all primary and foreign keys.

1.Trinity College (TC) is divided into several schools: a school of business, a school of arts and sciences, a school of education, and a school of applied sciences. Each school is administered by a dean who is a professor.  Each dean can administer only one school.  Each school must have a school name.

2.Each school is composed of several departments.  The smallest number of departments operated by a school is one, and the largest number of departments is indeterminate.  Each department belongs to only one school. A department may be classified as research only.  Each department must have a department name.

3.Each department may have many professors assigned to it.  One of those professors chairs the department.  Only one professor may chair the department to which he or she is assigned.  No professor is required to accept the chair position.  Each professor must have a first name, last name, rank, specialty, and an email.

4.Each department may offer courses.  Each course is offered by only one department.  If a department is research only, it will offer no courses. Each course must have a course title, description, and number of credits.

5.A class will be a section of a course.   A course may exist in Trinity College’s catalog even when it is not offered as a class in a current schedule.  Each class must have a class section and class time.

6.Each professor can teach up to four classes.  A professor may also be on a research contract and teach no classes at all.

7.A student may enroll in up to six classes, but takes each class only once during each enrollment period. Each class may have up to 35 students.  A class can exist even though no students are enrolled in it.

8.Each department has several students whose major is offered by that department, but a student can have only one major and is, therefore, associated with only one department. A student is not required to declare a major field of study when first enrolling.

9.Each student has an advisor in his or her department, each advisor counsels several students.  An advisor is a professor, but not all professors advise students.

10.Each class is taught in a room, and each room is located in a building.  A building can contain many rooms, but a room can only be contained in one building. Some buildings do not contain class rooms (e.g., maintenance building).  A room must have a room type, a building must have a building name and location.

Phase 2. After reviewing the various ERDs, Trinity College has decided on the following tables and attributes.

PROFESSOR Professor ID Department Professor Specialty Professor Rank Professor Last Name Professor First Name Professor Initial Professor Email	SCHOOL School ID School Name SCHOOLDEAN School Dean	DEPARTMENT Department Code Department Name School DEPARTMENTCHAIR Department Chair
STUDENT Student ID Department Student Last Name Student First Name Student Initial Student Email Advisor	COURSE Course Code Department Course Title Course Description Course Credits	CLASS Class Code Class Section Class Time Course Professor Room	ENROLL Class Student Enrollment Date Enrollment Grade
BUILDIING Building Name Building Location	ROOM Room Code Room Type Building

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 afterthe 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 beforethe 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.

Please be sure to review the Phase 1 requirements so you can determine the primary and foreign keys for each table and then determine the order of DROP and CREATE STATEMENTS. Please note that some fields are foreign keys but not identified as such by their names. Also know which fields are required fields and which are not.  Use appropriate data types for each field and meaningful field names.  

Answer 1

Phase 1:

Phase 2:

Queries:

-- Drop commands

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ENROLL]') AND type in (N'U'))

DROP TABLE [dbo].[ENROLL]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CLASS]') AND type in (N'U'))

DROP TABLE [dbo].[CLASS]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ROOM]') AND type in (N'U'))

DROP TABLE [dbo].[ROOM]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BUILDING]') AND type in (N'U'))

DROP TABLE [dbo].BUILDING

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DEPARTMENTCHAIR]') AND type in (N'U'))

DROP TABLE [dbo].[DEPARTMENTCHAIR]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SCHOOLDEAN]') AND type in (N'U'))

DROP TABLE [dbo].[SCHOOLDEAN]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[COURSE]') AND type in (N'U'))

DROP TABLE [dbo].[COURSE]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PROFESSOR]') AND type in (N'U'))

DROP TABLE [dbo].[PROFESSOR]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STUDENT]') AND type in (N'U'))

DROP TABLE [dbo].[STUDENT]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DEPARTMENT]') AND type in (N'U'))

DROP TABLE [dbo].[DEPARTMENT]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SCHOOL]') AND type in (N'U'))

DROP TABLE [dbo].[SCHOOL]

GO

-- Create table commands

CREATE TABLE PROFESSOR(

ProfessorID INT IDENTITY PRIMARY KEY, -- You can make it a normal column instead of identity as well

Department INT,

ProfessorSpecialty VARCHAR(500),

ProfessorRank TINYINT,

ProfessorLastName VARCHAR(500),

ProfessorFirstName VARCHAR(500),

ProfessorInitial VARCHAR(10), -- Initials cannot be more than

ProfessorEmail VARCHAR(100)

)

CREATE TABLE SCHOOL(

SchoolID INT IDENTITY PRIMARY KEY, -- You can make it a normal column instead of identity as well

SchoolName VARCHAR(500)

)

CREATE TABLE SCHOOLDEAN (

School INT PRIMARY KEY,

Dean VARCHAR(500)

)

CREATE TABLE DEPARTMENT(

DepartmentCode INT IDENTITY PRIMARY KEY, -- You can make it a normal column instead of identity as well

DepartmentName VARCHAR(500),

School INT

)

CREATE TABLE DEPARTMENTCHAIR(

Department INT PRIMARY KEY,

Chair VARCHAR(500)

)

CREATE TABLE STUDENT(

[Student ID] INT IDENTITY PRIMARY KEY

,[Department] INT

,[Student Last Name] VARCHAR(500)

,[Student First Name] VARCHAR(500)

,[Student Initial] VARCHAR(10)

,[Student Email] VARCHAR(100)

,[Advisor] VARCHAR(100)

)

CREATE TABLE COURSE(

CourseCode INT IDENTITY PRIMARY KEY

,Department INT

,CourseTitle VARCHAR(100)

,CourseDescription VARCHAR(500)

,CourseCredits FLOAT

)

CREATE TABLE CLASS (

[Class Code] INT IDENTITY PRIMARY KEY

,[Class Section] VARCHAR(10)

,[Class Time] DATETIME

,[Course] INT

,[Professor] INT

,[Room] INT

)

CREATE TABLE ENROLL (

[Class] INT

,[Student] INT

,[Enrollment Date] DATETIME

,[Enrollment Grade] FLOAT

)

CREATE TABLE BUILDING (

[Building Name] VARCHAR(250) PRIMARY KEY

,[Building Location] VARCHAR(500)

)

CREATE TABLE ROOM (

[Room Code] INT IDENTITY PRIMARY KEY

,[Room Type] VARCHAR(100)

,[Building] VARCHAR(250)

)

GO

-- Add foreign keys

ALTER TABLE PROFESSOR

ADD CONSTRAINT FK_PROFESSOR_Department FOREIGN KEY (Department)

REFERENCES Department (DepartmentCode)

ALTER TABLE DEPARTMENTCHAIR

ADD CONSTRAINT FK_DEPARTMENTCHAIR_Department FOREIGN KEY (Department)

REFERENCES Department (DepartmentCode)

ALTER TABLE STUDENT

ADD CONSTRAINT FK_STUDENT_Department FOREIGN KEY (Department)

REFERENCES Department (DepartmentCode)

ALTER TABLE COURSE

ADD CONSTRAINT FK_COURSE_Department FOREIGN KEY (Department)

REFERENCES Department (DepartmentCode)

ALTER TABLE SCHOOLDEAN

ADD CONSTRAINT FK_SCHOOLDEAN_School FOREIGN KEY (School)

REFERENCES School (SchoolID)

ALTER TABLE Department

ADD CONSTRAINT FK_School_Department FOREIGN KEY (School)

REFERENCES School (SchoolID)

ALTER TABLE CLASS

ADD CONSTRAINT FK_CLASS_Course FOREIGN KEY ([Course])

REFERENCES Course (CourseCode)

ALTER TABLE CLASS

ADD CONSTRAINT FK_CLASS_Room FOREIGN KEY ([Room])

REFERENCES ROOM ([Room Code])

ALTER TABLE CLASS

ADD CONSTRAINT FK_CLASS_Professor FOREIGN KEY ([Professor])

REFERENCES [Professor] (ProfessorID)

ALTER TABLE ENROLL

ADD CONSTRAINT FK_CLASS_ENROLL FOREIGN KEY (Class)

REFERENCES Class ([Class Code])

ALTER TABLE ENROLL

ADD CONSTRAINT FK_ENROLL_Student FOREIGN KEY (Student)

REFERENCES Student ([Student ID])

ALTER TABLE Room

ADD CONSTRAINT FK_Room_Building FOREIGN KEY ([Building])

REFERENCES Building ([Building Name])