Phase 1 – ERD In this phase of
Phase 1 – ERD
In this phase of the project you will create an ERD based uponthe following requirements and business rules. Limit your ERD toentities and relationships based on the business rules showed here.In other words, do not add realismto yourdesign by expanding or refining the businessrules. However, make sure you include all attributesneeded 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 schoolof business, a school of arts and sciences, a school of education,and a school of applied sciences. Each school is administered by adean who is a professor. Each dean can administer onlyone school. Each school must have a school name.
2.Each school is composed of several departments. Thesmallest number of departments operated by a school is one, and thelargest number of departments is indeterminate. Eachdepartment belongs to only one school. A department may beclassified as research only. Each department must have adepartment name.
3.Each department may have many professors assigned toit. One of those professors chairs thedepartment. Only one professor may chair the departmentto which he or she is assigned. No professor is requiredto accept the chair position. Each professor must have afirst name, last name, rank, specialty, and an email.
4.Each department may offer courses. Each course isoffered by only one department. If a department isresearch only, it will offer no courses. Each course must have acourse title, description, and number of credits.
5.A class will be a section of a course. Acourse may exist in Trinity College’s catalog even when it is notoffered as a class in a current schedule. Each classmust have a class section and class time.
6.Each professor can teach up to four classes. Aprofessor may also be on a research contract and teach no classesat all.
7.A student may enroll in up to six classes, but takes eachclass only once during each enrollment period. Each class may haveup to 35 students. A class can exist even though nostudents are enrolled in it.
8.Each department has several students whose major is offered bythat department, but a student can have only one major and is,therefore, associated with only one department. A student is notrequired to declare a major field of study when firstenrolling.
9.Each student has an advisor in his or her department, eachadvisor counsels several students. An advisor is aprofessor, but not all professors advise students.
10.Each class is taught in a room, and each room is located in abuilding. A building can contain many rooms, but a roomcan only be contained in one building. Some buildings do notcontain class rooms (e.g., maintenance building). A roommust have a room type, a building must have a building name andlocation.
Phase 2. After reviewing the various ERDs, Trinity College hasdecided 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 abovetables. Remember that when creating tables, the tableswith foreign keys have to be created afterthetable that the foreign key points to. When droppingtables, they have to be dropped in the reverse order, so that thetables with foreign keys are dropped beforethetables that the foreign keys point to. You can createall the drop SQL statements first followed by all the createtables. The first time you run them the drop statementswill work as no tables have been created and therefore no dropswill occur. You will need to run them twice to insurethe drops will work.
Please be sure to review the Phase 1 requirements so you candetermine the primary and foreign keys for each table and thendetermine the order of DROP and CREATE STATEMENTS. Please note thatsome fields are foreign keys but not identified as such by theirnames. Also know which fields are required fields and which arenot. Use appropriate data types for each field andmeaningful field names.
Answer:
Phase1:
Phase2:
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 anormal 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 morethan
ProfessorEmail VARCHAR(100)
)
CREATE TABLE SCHOOL(
SchoolID INT IDENTITY PRIMARY KEY, — You can make it a normalcolumn 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 anormal 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])
"Our Prices Start at $11.99. As Our First Client, Use Coupon Code GET15 to claim 15% Discount This Month!!"
