Here is the school database script screenshoot SET ANSI_NULLS ON GO SET QUOTED_I
ID: 3711510 • Letter: H
Question
Here is the school database script screenshootSET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
USE [master]; GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'School') DROP DATABASE School; GO
-- Create the School database. CREATE DATABASE School; GO
-- Specify a simple recovery model -- to keep the log growth to a minimum. ALTER DATABASE School SET RECOVERY SIMPLE; GO
USE School; GO
-- Create the Department table. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Department]( [DepartmentID] [int] NOT NULL, [Name] [nvarchar](50) NOT NULL, [Budget] [money] NOT NULL, [StartDate] [datetime] NOT NULL, [Administrator] [int] NULL, CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ( [DepartmentID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO
-- Create the Person table. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Person]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Person]( [PersonID] [int] IDENTITY(1,1) NOT NULL, [LastName] [nvarchar](50) NOT NULL, [FirstName] [nvarchar](50) NOT NULL, [HireDate] [datetime] NULL, [EnrollmentDate] [datetime] NULL, CONSTRAINT [PK_School.Student] PRIMARY KEY CLUSTERED ( [PersonID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO
-- Create the OnsiteCourse table. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OnsiteCourse]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[OnsiteCourse]( [CourseID] [int] NOT NULL, [Location] [nvarchar](50) NOT NULL, [Days] [nvarchar](50) NOT NULL, [Time] [smalldatetime] NOT NULL, CONSTRAINT [PK_OnsiteCourse] PRIMARY KEY CLUSTERED ( [CourseID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO
-- Create the OnlineCourse table. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OnlineCourse]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[OnlineCourse]( [CourseID] [int] NOT NULL, [URL] [nvarchar](100) NOT NULL, CONSTRAINT [PK_OnlineCourse] PRIMARY KEY CLUSTERED ( [CourseID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO
--Create the StudentGrade table. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StudentGrade]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[StudentGrade]( [EnrollmentID] [int] IDENTITY(1,1) NOT NULL, [CourseID] [int] NOT NULL, [StudentID] [int] NOT NULL, [Grade] [decimal](3, 2) NULL, CONSTRAINT [PK_StudentGrade] PRIMARY KEY CLUSTERED ( [EnrollmentID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO
-- Create the CourseInstructor table. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CourseInstructor]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[CourseInstructor]( [CourseID] [int] NOT NULL, [PersonID] [int] NOT NULL, CONSTRAINT [PK_CourseInstructor] PRIMARY KEY CLUSTERED ( [CourseID] ASC, [PersonID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO
-- Create the Course table. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Course]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Course]( [CourseID] [int] NOT NULL, [Title] [nvarchar](100) NOT NULL, [Credits] [int] NOT NULL, [DepartmentID] [int] NOT NULL, CONSTRAINT [PK_School.Course] PRIMARY KEY CLUSTERED ( [CourseID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO
-- Create the OfficeAssignment table. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OfficeAssignment]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[OfficeAssignment]( [InstructorID] [int] NOT NULL, [Location] [nvarchar](50) NOT NULL, [Timestamp] [timestamp] NOT NULL, CONSTRAINT [PK_OfficeAssignment] PRIMARY KEY CLUSTERED ( [InstructorID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO
-- Define the relationship between OnsiteCourse and Course. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_OnsiteCourse_Course]') AND parent_object_id = OBJECT_ID(N'[dbo].[OnsiteCourse]')) ALTER TABLE [dbo].[OnsiteCourse] WITH CHECK ADD CONSTRAINT [FK_OnsiteCourse_Course] FOREIGN KEY([CourseID]) REFERENCES [dbo].[Course] ([CourseID]) GO ALTER TABLE [dbo].[OnsiteCourse] CHECK CONSTRAINT [FK_OnsiteCourse_Course] GO
-- Define the relationship between OnlineCourse and Course. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_OnlineCourse_Course]') AND parent_object_id = OBJECT_ID(N'[dbo].[OnlineCourse]')) ALTER TABLE [dbo].[OnlineCourse] WITH CHECK ADD CONSTRAINT [FK_OnlineCourse_Course] FOREIGN KEY([CourseID]) REFERENCES [dbo].[Course] ([CourseID]) GO ALTER TABLE [dbo].[OnlineCourse] CHECK CONSTRAINT [FK_OnlineCourse_Course] GO
-- Define the relationship between StudentGrade and Course. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_StudentGrade_Course]') AND parent_object_id = OBJECT_ID(N'[dbo].[StudentGrade]')) ALTER TABLE [dbo].[StudentGrade] WITH CHECK ADD CONSTRAINT [FK_StudentGrade_Course] FOREIGN KEY([CourseID]) REFERENCES [dbo].[Course] ([CourseID]) GO ALTER TABLE [dbo].[StudentGrade] CHECK CONSTRAINT [FK_StudentGrade_Course] GO
--Define the relationship between StudentGrade and Student. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_StudentGrade_Student]') AND parent_object_id = OBJECT_ID(N'[dbo].[StudentGrade]')) ALTER TABLE [dbo].[StudentGrade] WITH CHECK ADD CONSTRAINT [FK_StudentGrade_Student] FOREIGN KEY([StudentID]) REFERENCES [dbo].[Person] ([PersonID]) GO ALTER TABLE [dbo].[StudentGrade] CHECK CONSTRAINT [FK_StudentGrade_Student] GO
-- Define the relationship between CourseInstructor and Course. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Course]') AND parent_object_id = OBJECT_ID(N'[dbo].[CourseInstructor]')) ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD CONSTRAINT [FK_CourseInstructor_Course] FOREIGN KEY([CourseID]) REFERENCES [dbo].[Course] ([CourseID]) GO ALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Course] GO
-- Define the relationship between CourseInstructor and Person. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Person]') AND parent_object_id = OBJECT_ID(N'[dbo].[CourseInstructor]')) ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD CONSTRAINT [FK_CourseInstructor_Person] FOREIGN KEY([PersonID]) REFERENCES [dbo].[Person] ([PersonID]) GO ALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Person] GO
-- Define the relationship between Course and Department. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Course_Department]') AND parent_object_id = OBJECT_ID(N'[dbo].[Course]')) ALTER TABLE [dbo].[Course] WITH CHECK ADD CONSTRAINT [FK_Course_Department] FOREIGN KEY([DepartmentID]) REFERENCES [dbo].[Department] ([DepartmentID]) GO ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department] GO
--Define the relationship between OfficeAssignment and Person. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_OfficeAssignment_Person]') AND parent_object_id = OBJECT_ID(N'[dbo].[OfficeAssignment]')) ALTER TABLE [dbo].[OfficeAssignment] WITH CHECK ADD CONSTRAINT [FK_OfficeAssignment_Person] FOREIGN KEY([InstructorID]) REFERENCES [dbo].[Person] ([PersonID]) GO ALTER TABLE [dbo].[OfficeAssignment] CHECK CONSTRAINT [FK_OfficeAssignment_Person] GO
-- Create InsertOfficeAssignment stored procedure. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertOfficeAssignment]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[InsertOfficeAssignment] @InstructorID int, @Location nvarchar(50) AS INSERT INTO dbo.OfficeAssignment (InstructorID, Location) VALUES (@InstructorID, @Location); IF @@ROWCOUNT > 0 BEGIN SELECT [Timestamp] FROM OfficeAssignment WHERE InstructorID=@InstructorID; END ' END GO
--Create the UpdateOfficeAssignment stored procedure. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateOfficeAssignment]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[UpdateOfficeAssignment] @InstructorID int, @Location nvarchar(50), @OrigTimestamp timestamp AS UPDATE OfficeAssignment SET Location=@Location WHERE InstructorID=@InstructorID AND [Timestamp]=@OrigTimestamp; IF @@ROWCOUNT > 0 BEGIN SELECT [Timestamp] FROM OfficeAssignment WHERE InstructorID=@InstructorID; END ' END GO
-- Create the DeleteOfficeAssignment stored procedure. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteOfficeAssignment]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[DeleteOfficeAssignment] @InstructorID int AS DELETE FROM OfficeAssignment WHERE InstructorID=@InstructorID; ' END GO
-- Create the DeletePerson stored procedure. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeletePerson]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[DeletePerson] @PersonID int AS DELETE FROM Person WHERE PersonID = @PersonID; ' END GO
-- Create the UpdatePerson stored procedure. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdatePerson]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[UpdatePerson] @PersonID int, @LastName nvarchar(50), @FirstName nvarchar(50), @HireDate datetime, @EnrollmentDate datetime AS UPDATE Person SET LastName=@LastName, FirstName=@FirstName, HireDate=@HireDate, EnrollmentDate=@EnrollmentDate WHERE PersonID=@PersonID; ' END GO
-- Create the InsertPerson stored procedure. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertPerson]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[InsertPerson] @LastName nvarchar(50), @FirstName nvarchar(50), @HireDate datetime, @EnrollmentDate datetime AS INSERT INTO dbo.Person (LastName, FirstName, HireDate, EnrollmentDate) VALUES (@LastName, @FirstName, @HireDate, @EnrollmentDate); SELECT SCOPE_IDENTITY() as NewPersonID; ' END GO
-- Create GetStudentGrades stored procedure. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStudentGrades]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[GetStudentGrades] @StudentID int AS SELECT EnrollmentID, Grade, CourseID, StudentID FROM dbo.StudentGrade WHERE StudentID = @StudentID ' END GO
-- Create GetDepartmentName stored procedure. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetDepartmentName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[GetDepartmentName] @ID int, @Name nvarchar(50) OUTPUT AS SELECT @Name = Name FROM Department WHERE DepartmentID = @ID ' END GO
-- Insert data into the Person table. USE School GO SET IDENTITY_INSERT dbo.Person ON GO INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (1, 'Abercrombie', 'Kim', '1995-03-11', null); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (2, 'Barzdukas', 'Gytis', null, '2005-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (3, 'Justice', 'Peggy', null, '2001-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (4, 'Fakhouri', 'Fadi', '2002-08-06', null); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (5, 'Harui', 'Roger', '1998-07-01', null); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (6, 'Li', 'Yan', null, '2002-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (7, 'Norman', 'Laura', null, '2003-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (8, 'Olivotto', 'Nino', null, '2005-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (9, 'Tang', 'Wayne', null, '2005-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (10, 'Alonso', 'Meredith', null, '2002-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (11, 'Lopez', 'Sophia', null, '2004-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (12, 'Browning', 'Meredith', null, '2000-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (13, 'Anand', 'Arturo', null, '2003-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (14, 'Walker', 'Alexandra', null, '2000-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (15, 'Powell', 'Carson', null, '2004-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (16, 'Jai', 'Damien', null, '2001-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (17, 'Carlson', 'Robyn', null, '2005-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (18, 'Zheng', 'Roger', '2004-02-12', null); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (19, 'Bryant', 'Carson', null, '2001-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (20, 'Suarez', 'Robyn', null, '2004-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (21, 'Holt', 'Roger', null, '2004-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (22, 'Alexander', 'Carson', null, '2005-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (23, 'Morgan', 'Isaiah', null, '2001-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (24, 'Martin', 'Randall', null, '2005-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (25, 'Kapoor', 'Candace', '2001-01-15', null); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (26, 'Rogers', 'Cody', null, '2002-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (27, 'Serrano', 'Stacy', '1999-06-01', null); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (28, 'White', 'Anthony', null, '2001-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (29, 'Griffin', 'Rachel', null, '2004-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (30, 'Shan', 'Alicia', null, '2003-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (31, 'Stewart', 'Jasmine', '1997-10-12', null); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (32, 'Xu', 'Kristen', '2001-7-23', null); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (33, 'Gao', 'Erica', null, '2003-01-30'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (34, 'Van Houten', 'Roger', '2000-12-07', null); GO SET IDENTITY_INSERT dbo.Person OFF GO
-- Insert data into the Department table. INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator) VALUES (1, 'Engineering', 350000.00, '2007-09-01', 2); INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator) VALUES (2, 'English', 120000.00, '2007-09-01', 6); INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator) VALUES (4, 'Economics', 200000.00, '2007-09-01', 4); INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator) VALUES (7, 'Mathematics', 250000.00, '2007-09-01', 3); GO
-- Insert data into the Course table. INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) VALUES (1050, 'Chemistry', 4, 1); INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) VALUES (1061, 'Physics', 4, 1); INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) VALUES (1045, 'Calculus', 4, 7); INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) VALUES (2030, 'Poetry', 2, 2); INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) VALUES (2021, 'Composition', 3, 2); INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) VALUES (2042, 'Literature', 4, 2); INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) VALUES (4022, 'Microeconomics', 3, 4); INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) VALUES (4041, 'Macroeconomics', 3, 4); INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) VALUES (4061, 'Quantitative', 2, 4); INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) VALUES (3141, 'Trigonometry', 4, 7); GO
-- Insert data into the OnlineCourse table. INSERT INTO dbo.OnlineCourse (CourseID, URL) VALUES (2030, 'http://www.fineartschool.net/Poetry'); INSERT INTO dbo.OnlineCourse (CourseID, URL) VALUES (2021, 'http://www.fineartschool.net/Composition'); INSERT INTO dbo.OnlineCourse (CourseID, URL) VALUES (4041, 'http://www.fineartschool.net/Macroeconomics'); INSERT INTO dbo.OnlineCourse (CourseID, URL) VALUES (3141, 'http://www.fineartschool.net/Trigonometry');
--Insert data into OnsiteCourse table. INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time]) VALUES (1050, '123 Smith', 'MTWH', '11:30'); INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time]) VALUES (1061, '234 Smith', 'TWHF', '13:15'); INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time]) VALUES (1045, '121 Smith','MWHF', '15:30'); INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time]) VALUES (4061, '22 Williams', 'TH', '11:15'); INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time]) VALUES (2042, '225 Adams', 'MTWH', '11:00'); INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time]) VALUES (4022, '23 Williams', 'MWF', '9:00');
-- Insert data into the CourseInstructor table. INSERT INTO dbo.CourseInstructor(CourseID, PersonID) VALUES (1050, 1); INSERT INTO dbo.CourseInstructor(CourseID, PersonID) VALUES (1061, 31); INSERT INTO dbo.CourseInstructor(CourseID, PersonID) VALUES (1045, 5); INSERT INTO dbo.CourseInstructor(CourseID, PersonID) VALUES (2030, 4); INSERT INTO dbo.CourseInstructor(CourseID, PersonID) VALUES (2021, 27); INSERT INTO dbo.CourseInstructor(CourseID, PersonID) VALUES (2042, 25); INSERT INTO dbo.CourseInstructor(CourseID, PersonID) VALUES (4022, 18); INSERT INTO dbo.CourseInstructor(CourseID, PersonID) VALUES (4041, 32); INSERT INTO dbo.CourseInstructor(CourseID, PersonID) VALUES (4061, 34); GO
--Insert data into the OfficeAssignment table. INSERT INTO dbo.OfficeAssignment(InstructorID, Location) VALUES (1, '17 Smith'); INSERT INTO dbo.OfficeAssignment(InstructorID, Location) VALUES (4, '29 Adams'); INSERT INTO dbo.OfficeAssignment(InstructorID, Location) VALUES (5, '37 Williams'); INSERT INTO dbo.OfficeAssignment(InstructorID, Location) VALUES (18, '143 Smith'); INSERT INTO dbo.OfficeAssignment(InstructorID, Location) VALUES (25, '57 Adams'); INSERT INTO dbo.OfficeAssignment(InstructorID, Location) VALUES (27, '271 Williams'); INSERT INTO dbo.OfficeAssignment(InstructorID, Location) VALUES (31, '131 Smith'); INSERT INTO dbo.OfficeAssignment(InstructorID, Location) VALUES (32, '203 Williams'); INSERT INTO dbo.OfficeAssignment(InstructorID, Location) VALUES (34, '213 Smith');
-- Insert data into the StudentGrade table. INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (2021, 2, 4); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (2030, 2, 3.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (2021, 3, 3); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (2030, 3, 4); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (2021, 6, 2.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (2042, 6, 3.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (2021, 7, 3.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (2042, 7, 4); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (2021, 8, 3); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (2042, 8, 3); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4041, 9, 3.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4041, 10, null); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4041, 11, 2.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4041, 12, null); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4061, 12, null); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4022, 14, 3); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4022, 13, 4); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4061, 13, 4); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4041, 14, 3); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4022, 15, 2.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4022, 16, 2); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4022, 17, null); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4022, 19, 3.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4061, 20, 4); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4061, 21, 2); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4022, 22, 3); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4041, 22, 3.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4061, 22, 2.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4022, 23, 3); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1045, 23, 1.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1061, 24, 4); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1061, 25, 3); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1050, 26, 3.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1061, 26, 3); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1061, 27, 3); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1045, 28, 2.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1050, 28, 3.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1061, 29, 4); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1050, 30, 3.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1061, 30, 4); GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
USE [master]; GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'School') DROP DATABASE School; GO
-- Create the School database. CREATE DATABASE School; GO
-- Specify a simple recovery model -- to keep the log growth to a minimum. ALTER DATABASE School SET RECOVERY SIMPLE; GO
USE School; GO
-- Create the Department table. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Department]( [DepartmentID] [int] NOT NULL, [Name] [nvarchar](50) NOT NULL, [Budget] [money] NOT NULL, [StartDate] [datetime] NOT NULL, [Administrator] [int] NULL, CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ( [DepartmentID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO
-- Create the Person table. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Person]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Person]( [PersonID] [int] IDENTITY(1,1) NOT NULL, [LastName] [nvarchar](50) NOT NULL, [FirstName] [nvarchar](50) NOT NULL, [HireDate] [datetime] NULL, [EnrollmentDate] [datetime] NULL, CONSTRAINT [PK_School.Student] PRIMARY KEY CLUSTERED ( [PersonID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO
-- Create the OnsiteCourse table. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OnsiteCourse]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[OnsiteCourse]( [CourseID] [int] NOT NULL, [Location] [nvarchar](50) NOT NULL, [Days] [nvarchar](50) NOT NULL, [Time] [smalldatetime] NOT NULL, CONSTRAINT [PK_OnsiteCourse] PRIMARY KEY CLUSTERED ( [CourseID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO
-- Create the OnlineCourse table. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OnlineCourse]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[OnlineCourse]( [CourseID] [int] NOT NULL, [URL] [nvarchar](100) NOT NULL, CONSTRAINT [PK_OnlineCourse] PRIMARY KEY CLUSTERED ( [CourseID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO
--Create the StudentGrade table. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StudentGrade]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[StudentGrade]( [EnrollmentID] [int] IDENTITY(1,1) NOT NULL, [CourseID] [int] NOT NULL, [StudentID] [int] NOT NULL, [Grade] [decimal](3, 2) NULL, CONSTRAINT [PK_StudentGrade] PRIMARY KEY CLUSTERED ( [EnrollmentID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO
-- Create the CourseInstructor table. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CourseInstructor]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[CourseInstructor]( [CourseID] [int] NOT NULL, [PersonID] [int] NOT NULL, CONSTRAINT [PK_CourseInstructor] PRIMARY KEY CLUSTERED ( [CourseID] ASC, [PersonID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO
-- Create the Course table. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Course]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Course]( [CourseID] [int] NOT NULL, [Title] [nvarchar](100) NOT NULL, [Credits] [int] NOT NULL, [DepartmentID] [int] NOT NULL, CONSTRAINT [PK_School.Course] PRIMARY KEY CLUSTERED ( [CourseID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO
-- Create the OfficeAssignment table. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OfficeAssignment]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[OfficeAssignment]( [InstructorID] [int] NOT NULL, [Location] [nvarchar](50) NOT NULL, [Timestamp] [timestamp] NOT NULL, CONSTRAINT [PK_OfficeAssignment] PRIMARY KEY CLUSTERED ( [InstructorID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO
-- Define the relationship between OnsiteCourse and Course. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_OnsiteCourse_Course]') AND parent_object_id = OBJECT_ID(N'[dbo].[OnsiteCourse]')) ALTER TABLE [dbo].[OnsiteCourse] WITH CHECK ADD CONSTRAINT [FK_OnsiteCourse_Course] FOREIGN KEY([CourseID]) REFERENCES [dbo].[Course] ([CourseID]) GO ALTER TABLE [dbo].[OnsiteCourse] CHECK CONSTRAINT [FK_OnsiteCourse_Course] GO
-- Define the relationship between OnlineCourse and Course. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_OnlineCourse_Course]') AND parent_object_id = OBJECT_ID(N'[dbo].[OnlineCourse]')) ALTER TABLE [dbo].[OnlineCourse] WITH CHECK ADD CONSTRAINT [FK_OnlineCourse_Course] FOREIGN KEY([CourseID]) REFERENCES [dbo].[Course] ([CourseID]) GO ALTER TABLE [dbo].[OnlineCourse] CHECK CONSTRAINT [FK_OnlineCourse_Course] GO
-- Define the relationship between StudentGrade and Course. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_StudentGrade_Course]') AND parent_object_id = OBJECT_ID(N'[dbo].[StudentGrade]')) ALTER TABLE [dbo].[StudentGrade] WITH CHECK ADD CONSTRAINT [FK_StudentGrade_Course] FOREIGN KEY([CourseID]) REFERENCES [dbo].[Course] ([CourseID]) GO ALTER TABLE [dbo].[StudentGrade] CHECK CONSTRAINT [FK_StudentGrade_Course] GO
--Define the relationship between StudentGrade and Student. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_StudentGrade_Student]') AND parent_object_id = OBJECT_ID(N'[dbo].[StudentGrade]')) ALTER TABLE [dbo].[StudentGrade] WITH CHECK ADD CONSTRAINT [FK_StudentGrade_Student] FOREIGN KEY([StudentID]) REFERENCES [dbo].[Person] ([PersonID]) GO ALTER TABLE [dbo].[StudentGrade] CHECK CONSTRAINT [FK_StudentGrade_Student] GO
-- Define the relationship between CourseInstructor and Course. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Course]') AND parent_object_id = OBJECT_ID(N'[dbo].[CourseInstructor]')) ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD CONSTRAINT [FK_CourseInstructor_Course] FOREIGN KEY([CourseID]) REFERENCES [dbo].[Course] ([CourseID]) GO ALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Course] GO
-- Define the relationship between CourseInstructor and Person. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Person]') AND parent_object_id = OBJECT_ID(N'[dbo].[CourseInstructor]')) ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD CONSTRAINT [FK_CourseInstructor_Person] FOREIGN KEY([PersonID]) REFERENCES [dbo].[Person] ([PersonID]) GO ALTER TABLE [dbo].[CourseInstructor] CHECK CONSTRAINT [FK_CourseInstructor_Person] GO
-- Define the relationship between Course and Department. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Course_Department]') AND parent_object_id = OBJECT_ID(N'[dbo].[Course]')) ALTER TABLE [dbo].[Course] WITH CHECK ADD CONSTRAINT [FK_Course_Department] FOREIGN KEY([DepartmentID]) REFERENCES [dbo].[Department] ([DepartmentID]) GO ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department] GO
--Define the relationship between OfficeAssignment and Person. IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_OfficeAssignment_Person]') AND parent_object_id = OBJECT_ID(N'[dbo].[OfficeAssignment]')) ALTER TABLE [dbo].[OfficeAssignment] WITH CHECK ADD CONSTRAINT [FK_OfficeAssignment_Person] FOREIGN KEY([InstructorID]) REFERENCES [dbo].[Person] ([PersonID]) GO ALTER TABLE [dbo].[OfficeAssignment] CHECK CONSTRAINT [FK_OfficeAssignment_Person] GO
-- Create InsertOfficeAssignment stored procedure. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertOfficeAssignment]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[InsertOfficeAssignment] @InstructorID int, @Location nvarchar(50) AS INSERT INTO dbo.OfficeAssignment (InstructorID, Location) VALUES (@InstructorID, @Location); IF @@ROWCOUNT > 0 BEGIN SELECT [Timestamp] FROM OfficeAssignment WHERE InstructorID=@InstructorID; END ' END GO
--Create the UpdateOfficeAssignment stored procedure. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateOfficeAssignment]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[UpdateOfficeAssignment] @InstructorID int, @Location nvarchar(50), @OrigTimestamp timestamp AS UPDATE OfficeAssignment SET Location=@Location WHERE InstructorID=@InstructorID AND [Timestamp]=@OrigTimestamp; IF @@ROWCOUNT > 0 BEGIN SELECT [Timestamp] FROM OfficeAssignment WHERE InstructorID=@InstructorID; END ' END GO
-- Create the DeleteOfficeAssignment stored procedure. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteOfficeAssignment]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[DeleteOfficeAssignment] @InstructorID int AS DELETE FROM OfficeAssignment WHERE InstructorID=@InstructorID; ' END GO
-- Create the DeletePerson stored procedure. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeletePerson]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[DeletePerson] @PersonID int AS DELETE FROM Person WHERE PersonID = @PersonID; ' END GO
-- Create the UpdatePerson stored procedure. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdatePerson]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[UpdatePerson] @PersonID int, @LastName nvarchar(50), @FirstName nvarchar(50), @HireDate datetime, @EnrollmentDate datetime AS UPDATE Person SET LastName=@LastName, FirstName=@FirstName, HireDate=@HireDate, EnrollmentDate=@EnrollmentDate WHERE PersonID=@PersonID; ' END GO
-- Create the InsertPerson stored procedure. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertPerson]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[InsertPerson] @LastName nvarchar(50), @FirstName nvarchar(50), @HireDate datetime, @EnrollmentDate datetime AS INSERT INTO dbo.Person (LastName, FirstName, HireDate, EnrollmentDate) VALUES (@LastName, @FirstName, @HireDate, @EnrollmentDate); SELECT SCOPE_IDENTITY() as NewPersonID; ' END GO
-- Create GetStudentGrades stored procedure. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetStudentGrades]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[GetStudentGrades] @StudentID int AS SELECT EnrollmentID, Grade, CourseID, StudentID FROM dbo.StudentGrade WHERE StudentID = @StudentID ' END GO
-- Create GetDepartmentName stored procedure. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetDepartmentName]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[GetDepartmentName] @ID int, @Name nvarchar(50) OUTPUT AS SELECT @Name = Name FROM Department WHERE DepartmentID = @ID ' END GO
-- Insert data into the Person table. USE School GO SET IDENTITY_INSERT dbo.Person ON GO INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (1, 'Abercrombie', 'Kim', '1995-03-11', null); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (2, 'Barzdukas', 'Gytis', null, '2005-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (3, 'Justice', 'Peggy', null, '2001-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (4, 'Fakhouri', 'Fadi', '2002-08-06', null); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (5, 'Harui', 'Roger', '1998-07-01', null); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (6, 'Li', 'Yan', null, '2002-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (7, 'Norman', 'Laura', null, '2003-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (8, 'Olivotto', 'Nino', null, '2005-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (9, 'Tang', 'Wayne', null, '2005-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (10, 'Alonso', 'Meredith', null, '2002-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (11, 'Lopez', 'Sophia', null, '2004-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (12, 'Browning', 'Meredith', null, '2000-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (13, 'Anand', 'Arturo', null, '2003-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (14, 'Walker', 'Alexandra', null, '2000-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (15, 'Powell', 'Carson', null, '2004-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (16, 'Jai', 'Damien', null, '2001-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (17, 'Carlson', 'Robyn', null, '2005-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (18, 'Zheng', 'Roger', '2004-02-12', null); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (19, 'Bryant', 'Carson', null, '2001-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (20, 'Suarez', 'Robyn', null, '2004-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (21, 'Holt', 'Roger', null, '2004-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (22, 'Alexander', 'Carson', null, '2005-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (23, 'Morgan', 'Isaiah', null, '2001-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (24, 'Martin', 'Randall', null, '2005-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (25, 'Kapoor', 'Candace', '2001-01-15', null); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (26, 'Rogers', 'Cody', null, '2002-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (27, 'Serrano', 'Stacy', '1999-06-01', null); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (28, 'White', 'Anthony', null, '2001-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (29, 'Griffin', 'Rachel', null, '2004-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (30, 'Shan', 'Alicia', null, '2003-09-01'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (31, 'Stewart', 'Jasmine', '1997-10-12', null); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (32, 'Xu', 'Kristen', '2001-7-23', null); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (33, 'Gao', 'Erica', null, '2003-01-30'); INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate) VALUES (34, 'Van Houten', 'Roger', '2000-12-07', null); GO SET IDENTITY_INSERT dbo.Person OFF GO
-- Insert data into the Department table. INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator) VALUES (1, 'Engineering', 350000.00, '2007-09-01', 2); INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator) VALUES (2, 'English', 120000.00, '2007-09-01', 6); INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator) VALUES (4, 'Economics', 200000.00, '2007-09-01', 4); INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator) VALUES (7, 'Mathematics', 250000.00, '2007-09-01', 3); GO
-- Insert data into the Course table. INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) VALUES (1050, 'Chemistry', 4, 1); INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) VALUES (1061, 'Physics', 4, 1); INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) VALUES (1045, 'Calculus', 4, 7); INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) VALUES (2030, 'Poetry', 2, 2); INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) VALUES (2021, 'Composition', 3, 2); INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) VALUES (2042, 'Literature', 4, 2); INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) VALUES (4022, 'Microeconomics', 3, 4); INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) VALUES (4041, 'Macroeconomics', 3, 4); INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) VALUES (4061, 'Quantitative', 2, 4); INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID) VALUES (3141, 'Trigonometry', 4, 7); GO
-- Insert data into the OnlineCourse table. INSERT INTO dbo.OnlineCourse (CourseID, URL) VALUES (2030, 'http://www.fineartschool.net/Poetry'); INSERT INTO dbo.OnlineCourse (CourseID, URL) VALUES (2021, 'http://www.fineartschool.net/Composition'); INSERT INTO dbo.OnlineCourse (CourseID, URL) VALUES (4041, 'http://www.fineartschool.net/Macroeconomics'); INSERT INTO dbo.OnlineCourse (CourseID, URL) VALUES (3141, 'http://www.fineartschool.net/Trigonometry');
--Insert data into OnsiteCourse table. INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time]) VALUES (1050, '123 Smith', 'MTWH', '11:30'); INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time]) VALUES (1061, '234 Smith', 'TWHF', '13:15'); INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time]) VALUES (1045, '121 Smith','MWHF', '15:30'); INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time]) VALUES (4061, '22 Williams', 'TH', '11:15'); INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time]) VALUES (2042, '225 Adams', 'MTWH', '11:00'); INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time]) VALUES (4022, '23 Williams', 'MWF', '9:00');
-- Insert data into the CourseInstructor table. INSERT INTO dbo.CourseInstructor(CourseID, PersonID) VALUES (1050, 1); INSERT INTO dbo.CourseInstructor(CourseID, PersonID) VALUES (1061, 31); INSERT INTO dbo.CourseInstructor(CourseID, PersonID) VALUES (1045, 5); INSERT INTO dbo.CourseInstructor(CourseID, PersonID) VALUES (2030, 4); INSERT INTO dbo.CourseInstructor(CourseID, PersonID) VALUES (2021, 27); INSERT INTO dbo.CourseInstructor(CourseID, PersonID) VALUES (2042, 25); INSERT INTO dbo.CourseInstructor(CourseID, PersonID) VALUES (4022, 18); INSERT INTO dbo.CourseInstructor(CourseID, PersonID) VALUES (4041, 32); INSERT INTO dbo.CourseInstructor(CourseID, PersonID) VALUES (4061, 34); GO
--Insert data into the OfficeAssignment table. INSERT INTO dbo.OfficeAssignment(InstructorID, Location) VALUES (1, '17 Smith'); INSERT INTO dbo.OfficeAssignment(InstructorID, Location) VALUES (4, '29 Adams'); INSERT INTO dbo.OfficeAssignment(InstructorID, Location) VALUES (5, '37 Williams'); INSERT INTO dbo.OfficeAssignment(InstructorID, Location) VALUES (18, '143 Smith'); INSERT INTO dbo.OfficeAssignment(InstructorID, Location) VALUES (25, '57 Adams'); INSERT INTO dbo.OfficeAssignment(InstructorID, Location) VALUES (27, '271 Williams'); INSERT INTO dbo.OfficeAssignment(InstructorID, Location) VALUES (31, '131 Smith'); INSERT INTO dbo.OfficeAssignment(InstructorID, Location) VALUES (32, '203 Williams'); INSERT INTO dbo.OfficeAssignment(InstructorID, Location) VALUES (34, '213 Smith');
-- Insert data into the StudentGrade table. INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (2021, 2, 4); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (2030, 2, 3.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (2021, 3, 3); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (2030, 3, 4); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (2021, 6, 2.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (2042, 6, 3.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (2021, 7, 3.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (2042, 7, 4); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (2021, 8, 3); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (2042, 8, 3); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4041, 9, 3.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4041, 10, null); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4041, 11, 2.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4041, 12, null); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4061, 12, null); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4022, 14, 3); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4022, 13, 4); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4061, 13, 4); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4041, 14, 3); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4022, 15, 2.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4022, 16, 2); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4022, 17, null); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4022, 19, 3.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4061, 20, 4); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4061, 21, 2); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4022, 22, 3); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4041, 22, 3.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4061, 22, 2.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (4022, 23, 3); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1045, 23, 1.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1061, 24, 4); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1061, 25, 3); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1050, 26, 3.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1061, 26, 3); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1061, 27, 3); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1045, 28, 2.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1050, 28, 3.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1061, 29, 4); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1050, 30, 3.5); INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade) VALUES (1061, 30, 4); GO
Explanation / Answer
Note- Given assignment is big enough to be completed in given time. Tried to solve the maximum part . It seems insert statement you already have, so not focussing on that. If you want other parts to be answered, please post remaining part as a seperate question.
Create sstatement is used to create all the given table. Primary key and foreign key constraint are mentioned where necessary.
1)
CREATE DATABASE University;
2)
CREATE TABLE Department
(
DepartmentID INT,
Name VARCHAR(50) NOT NULL,
Budget DECIMAL(6,2),
StartDate DATE,
Administrator VARCHAR(25),
PRIMARY KEY (DepartmentID)
);
3)
CREATE TABLE Person
(
PersonID INT,
LastName VARCHAR(50),
FirstName VARCHAR(50),
HireDate DATE,
EnrollmentDate DATE,
PRIMARY KEY (PersonID)
);
4)
CREATE TABLE OnlineCourse
(
CourseID INT,
URL VARCHAR(255),
PRIMARY KEY (CourseID)
);
5)
CREATE TABLE OnsiteCourse
(
CourseID INT,
Location VARCHAR(100),
Days VARCHAR(15),
Time DATE,
PRIARY KEY (CourseID)
);
6)
CREATE TABLE StudentGrade
(
EnrollmentID INT,
CourseID INT,
StudentID INT,
Grade CHAR(6),
PRIMARY KEY (EnrollmentID),
FOREIGN KEY (CourseID) REFERENCES Course (CourseID),
FOREIGN KEY (StudentID) REFERENCES Person (PersonID)
);
7)
CREATE TABLE CourseInstructor
(
CourseID INT,
PersonID INT,
PRIMARY KEY (CourseID, PersonID),
FOREIGN KEY (CourseID) REFERENCES Course (CourseID),
FOREIGN KEY (PersonID) REFERENCES Person (PersonID)
);
8)
CREATE TABLE Course
(
CourseID INT,
Title VARCHAR(50),
Credits INT,
DepartmentID INT,
PRIMARY KEY (CourseID),
FOREIGN KEY (DepartmentID) REFERENCES Department (DepartmentID)
);
9)
CREATE TABLE OfficeAssignment
(
InstructorID INT,
Location VARCHAR(50),
Timestamp DATE,
PRIMARY KEY (InstructorID),
FOREIGN KEY (InstructorID) REFERENCES Person (PersonID)
);
10)
ALTER TABLE Course
ADD CONSTRAINT primCourse
PRIMARY KEY (CourseID)
11)
ALTER TABLE OnsiteCourse
ADD CONSTRAINT forOnsiteCourse
FOREIGN KEY (CourseID)
REFERENCES Course (CourseID)
12)
ALTER TABLE OnlineCourse
ADD CONSTRAINT forCourse
FOREIGN KEY (CourseID)
REFERENCES Course (CourseID)
13) Foriegn key constraint has already been added to the studentGrade table while creating StudentGrade table. I order to execute the below statement remove the constraint first.
ALTER TABLE StudentGrade
ADD CONSTRAINT stCourse
FOREIGN KEY (CourseID)
REFERENCES Course (CourseID)