IT230 Phase 2 Requirements After reviewing the various ERDs, Trinity College has
ID: 3714953 • Letter: I
Question
IT230
Phase 2 Requirements
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 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.
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.
The following is an example of the drop table statement that I covered in one of the lectures:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tablename]') AND type in (N'U'))
DROP TABLE [dbo].[tablename]
All of the SQL statements must be in one file. Do not use Word as your submission file as it won’t work in the SQL Management Studio. Use the following steps to create the file and submit it:
Open Management Studio and select the database you created
Click on New Query
Write the first drop and create statement and execute them
Click File à Save As (it will show a generic name of the query)
Name the file appropriately and save it somewhere you can find it. Leave it as a .sql file
Add the next drop and create statements and execute again.
If it works, save the file. If not, make the corrections, re-execute until it works.
Continue until all of the drop and create statements are executing and tables are created.
Submit the .sql file to the drop box.
You can do this in stages if you want. If you have saved the file, the next time you open Management Studio go to FileàOpenàFile and then select the file that you have been saving. It will automatically open in the query window and you can continue working on it.
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
Explanation / Answer
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])
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.