BdsFFMs0

1SET ANSI_NULLS ON
2GO
3SET QUOTED_IDENTIFIER ON
4GO
5
6-- Create the Department table.
7IF NOT EXISTS (SELECT * FROM sys.objects
8WHERE object_id = OBJECT_ID(N'[dbo].[Department]')
9AND type in (N'U'))
10BEGIN
11CREATE TABLE [dbo].[Department]([DepartmentID] [int] NOT NULL,
12[Name] [nvarchar](50) NOT NULL,
13[Budget] [money] NOT NULL,
14[StartDate] [datetime] NOT NULL,
15[Administrator] [int] NULL,
16CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
17(
18[DepartmentID] ASC
19)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
20END
21GO
22
23-- Create the Person table.
24IF NOT EXISTS (SELECT * FROM sys.objects
25WHERE object_id = OBJECT_ID(N'[dbo].[Person]')
26AND type in (N'U'))
27BEGIN
28CREATE TABLE [dbo].[Person]([PersonID] [int] IDENTITY(1,1) NOT NULL,
29[LastName] [nvarchar](50) NOT NULL,
30[FirstName] [nvarchar](50) NOT NULL,
31[HireDate] [datetime] NULL,
32[EnrollmentDate] [datetime] NULL,
33[Discriminator] [nvarchar](50) NOT NULL,
34CONSTRAINT [PK_School.Student] PRIMARY KEY CLUSTERED
35(
36[PersonID] ASC
37)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
38END
39GO
40
41-- Create the OnsiteCourse table.
42IF NOT EXISTS (SELECT * FROM sys.objects
43WHERE object_id = OBJECT_ID(N'[dbo].[OnsiteCourse]')
44AND type in (N'U'))
45BEGIN
46CREATE TABLE [dbo].[OnsiteCourse]([CourseID] [int] NOT NULL,
47[Location] [nvarchar](50) NOT NULL,
48[Days] [nvarchar](50) NOT NULL,
49[Time] [smalldatetime] NOT NULL,
50CONSTRAINT [PK_OnsiteCourse] PRIMARY KEY CLUSTERED
51(
52[CourseID] ASC
53)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
54END
55GO
56
57-- Create the OnlineCourse table.
58IF NOT EXISTS (SELECT * FROM sys.objects
59WHERE object_id = OBJECT_ID(N'[dbo].[OnlineCourse]')
60AND type in (N'U'))
61BEGIN
62CREATE TABLE [dbo].[OnlineCourse]([CourseID] [int] NOT NULL,
63[URL] [nvarchar](100) NOT NULL,
64CONSTRAINT [PK_OnlineCourse] PRIMARY KEY CLUSTERED
65(
66[CourseID] ASC
67)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
68END
69GO
70
71--Create the StudentGrade table.
72IF NOT EXISTS (SELECT * FROM sys.objects
73WHERE object_id = OBJECT_ID(N'[dbo].[StudentGrade]')
74AND type in (N'U'))
75BEGIN
76CREATE TABLE [dbo].[StudentGrade]([EnrollmentID] [int] IDENTITY(1,1) NOT NULL,
77[CourseID] [int] NOT NULL,
78[StudentID] [int] NOT NULL,
79[Grade] [decimal](3, 2) NULL,
80CONSTRAINT [PK_StudentGrade] PRIMARY KEY CLUSTERED
81(
82[EnrollmentID] ASC
83)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
84END
85GO
86
87-- Create the CourseInstructor table.
88IF NOT EXISTS (SELECT * FROM sys.objects
89WHERE object_id = OBJECT_ID(N'[dbo].[CourseInstructor]')
90AND type in (N'U'))
91BEGIN
92CREATE TABLE [dbo].[CourseInstructor]([CourseID] [int] NOT NULL,
93[PersonID] [int] NOT NULL,
94CONSTRAINT [PK_CourseInstructor] PRIMARY KEY CLUSTERED
95(
96[CourseID] ASC,
97[PersonID] ASC
98)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
99END
100GO
101
102-- Create the Course table.
103IF NOT EXISTS (SELECT * FROM sys.objects
104WHERE object_id = OBJECT_ID(N'[dbo].[Course]')
105AND type in (N'U'))
106BEGIN
107CREATE TABLE [dbo].[Course]([CourseID] [int] NOT NULL,
108[Title] [nvarchar](100) NOT NULL,
109[Credits] [int] NOT NULL,
110[DepartmentID] [int] NOT NULL,
111CONSTRAINT [PK_School.Course] PRIMARY KEY CLUSTERED
112(
113[CourseID] ASC
114)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
115END
116GO
117
118-- Create the OfficeAssignment table.
119IF NOT EXISTS (SELECT * FROM sys.objects
120WHERE object_id = OBJECT_ID(N'[dbo].[OfficeAssignment]')
121AND type in (N'U'))
122BEGIN
123CREATE TABLE [dbo].[OfficeAssignment]([InstructorID] [int] NOT NULL,
124[Location] [nvarchar](50) NOT NULL,
125[Timestamp] [timestamp] NOT NULL,
126CONSTRAINT [PK_OfficeAssignment] PRIMARY KEY CLUSTERED
127(
128[InstructorID] ASC
129)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
130END
131GO
132
133-- Define the relationship between OnsiteCourse and Course.
134IF NOT EXISTS (SELECT * FROM sys.foreign_keys
135WHERE object_id = OBJECT_ID(N'[dbo].[FK_OnsiteCourse_Course]')
136AND parent_object_id = OBJECT_ID(N'[dbo].[OnsiteCourse]'))
137ALTER TABLE [dbo].[OnsiteCourse] WITH CHECK ADD
138CONSTRAINT [FK_OnsiteCourse_Course] FOREIGN KEY([CourseID])
139REFERENCES [dbo].[Course] ([CourseID])
140GO
141ALTER TABLE [dbo].[OnsiteCourse] CHECK
142CONSTRAINT [FK_OnsiteCourse_Course]
143GO
144
145-- Define the relationship between OnlineCourse and Course.
146IF NOT EXISTS (SELECT * FROM sys.foreign_keys
147WHERE object_id = OBJECT_ID(N'[dbo].[FK_OnlineCourse_Course]')
148AND parent_object_id = OBJECT_ID(N'[dbo].[OnlineCourse]'))
149ALTER TABLE [dbo].[OnlineCourse] WITH CHECK ADD
150CONSTRAINT [FK_OnlineCourse_Course] FOREIGN KEY([CourseID])
151REFERENCES [dbo].[Course] ([CourseID])
152GO
153ALTER TABLE [dbo].[OnlineCourse] CHECK
154CONSTRAINT [FK_OnlineCourse_Course]
155GO
156
157-- Define the relationship between StudentGrade and Course.
158IF NOT EXISTS (SELECT * FROM sys.foreign_keys
159WHERE object_id = OBJECT_ID(N'[dbo].[FK_StudentGrade_Course]')
160AND parent_object_id = OBJECT_ID(N'[dbo].[StudentGrade]'))
161ALTER TABLE [dbo].[StudentGrade] WITH CHECK ADD
162CONSTRAINT [FK_StudentGrade_Course] FOREIGN KEY([CourseID])
163REFERENCES [dbo].[Course] ([CourseID])
164GO
165ALTER TABLE [dbo].[StudentGrade] CHECK
166CONSTRAINT [FK_StudentGrade_Course]
167GO
168
169--Define the relationship between StudentGrade and Student.
170IF NOT EXISTS (SELECT * FROM sys.foreign_keys
171WHERE object_id = OBJECT_ID(N'[dbo].[FK_StudentGrade_Student]')
172AND parent_object_id = OBJECT_ID(N'[dbo].[StudentGrade]'))
173ALTER TABLE [dbo].[StudentGrade] WITH CHECK ADD
174CONSTRAINT [FK_StudentGrade_Student] FOREIGN KEY([StudentID])
175REFERENCES [dbo].[Person] ([PersonID])
176GO
177ALTER TABLE [dbo].[StudentGrade] CHECK
178CONSTRAINT [FK_StudentGrade_Student]
179GO
180
181-- Define the relationship between CourseInstructor and Course.
182IF NOT EXISTS (SELECT * FROM sys.foreign_keys
183WHERE object_id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Course]')
184AND parent_object_id = OBJECT_ID(N'[dbo].[CourseInstructor]'))
185ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD
186CONSTRAINT [FK_CourseInstructor_Course] FOREIGN KEY([CourseID])
187REFERENCES [dbo].[Course] ([CourseID])
188GO
189ALTER TABLE [dbo].[CourseInstructor] CHECK
190CONSTRAINT [FK_CourseInstructor_Course]
191GO
192
193-- Define the relationship between CourseInstructor and Person.
194IF NOT EXISTS (SELECT * FROM sys.foreign_keys
195WHERE object_id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Person]')
196AND parent_object_id = OBJECT_ID(N'[dbo].[CourseInstructor]'))
197ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADD
198CONSTRAINT [FK_CourseInstructor_Person] FOREIGN KEY([PersonID])
199REFERENCES [dbo].[Person] ([PersonID])
200GO
201ALTER TABLE [dbo].[CourseInstructor] CHECK
202CONSTRAINT [FK_CourseInstructor_Person]
203GO
204
205-- Define the relationship between Course and Department.
206IF NOT EXISTS (SELECT * FROM sys.foreign_keys
207WHERE object_id = OBJECT_ID(N'[dbo].[FK_Course_Department]')
208AND parent_object_id = OBJECT_ID(N'[dbo].[Course]'))
209ALTER TABLE [dbo].[Course] WITH CHECK ADD
210CONSTRAINT [FK_Course_Department] FOREIGN KEY([DepartmentID])
211REFERENCES [dbo].[Department] ([DepartmentID])
212GO
213ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department]
214GO
215
216--Define the relationship between OfficeAssignment and Person.
217IF NOT EXISTS (SELECT * FROM sys.foreign_keys
218WHERE object_id = OBJECT_ID(N'[dbo].[FK_OfficeAssignment_Person]')
219AND parent_object_id = OBJECT_ID(N'[dbo].[OfficeAssignment]'))
220ALTER TABLE [dbo].[OfficeAssignment] WITH CHECK ADD
221CONSTRAINT [FK_OfficeAssignment_Person] FOREIGN KEY([InstructorID])
222REFERENCES [dbo].[Person] ([PersonID])
223GO
224ALTER TABLE [dbo].[OfficeAssignment] CHECK
225CONSTRAINT [FK_OfficeAssignment_Person]
226GO
227
228-- Create InsertOfficeAssignment stored procedure.
229IF NOT EXISTS (SELECT * FROM sys.objects
230WHERE object_id = OBJECT_ID(N'[dbo].[InsertOfficeAssignment]')
231AND type in (N'P', N'PC'))
232BEGIN
233EXEC dbo.sp_executesql @statement = N'
234CREATE PROCEDURE [dbo].[InsertOfficeAssignment]
235@InstructorID int,
236@Location nvarchar(50)
237AS
238INSERT INTO dbo.OfficeAssignment (InstructorID, Location)
239VALUES (@InstructorID, @Location);
240IF @@ROWCOUNT > 0
241BEGIN
242SELECT [Timestamp] FROM OfficeAssignment
243WHERE InstructorID=@InstructorID;
244END
245'
246END
247GO
248
249--Create the UpdateOfficeAssignment stored procedure.
250IF NOT EXISTS (SELECT * FROM sys.objects
251WHERE object_id = OBJECT_ID(N'[dbo].[UpdateOfficeAssignment]')
252AND type in (N'P', N'PC'))
253BEGIN
254EXEC dbo.sp_executesql @statement = N'
255CREATE PROCEDURE [dbo].[UpdateOfficeAssignment]
256@InstructorID int,
257@Location nvarchar(50),
258@OrigTimestamp timestamp
259AS
260UPDATE OfficeAssignment SET Location=@Location
261WHERE InstructorID=@InstructorID AND [Timestamp]=@OrigTimestamp;
262IF @@ROWCOUNT > 0
263BEGIN
264SELECT [Timestamp] FROM OfficeAssignment
265WHERE InstructorID=@InstructorID;
266END
267'
268END
269GO
270
271-- Create the DeleteOfficeAssignment stored procedure.
272IF NOT EXISTS (SELECT * FROM sys.objects
273WHERE object_id = OBJECT_ID(N'[dbo].[DeleteOfficeAssignment]')
274AND type in (N'P', N'PC'))
275BEGIN
276EXEC dbo.sp_executesql @statement = N'
277CREATE PROCEDURE [dbo].[DeleteOfficeAssignment]
278@InstructorID int
279AS
280DELETE FROM OfficeAssignment
281WHERE InstructorID=@InstructorID;
282'
283END
284GO
285
286-- Create the DeletePerson stored procedure.
287IF NOT EXISTS (SELECT * FROM sys.objects
288WHERE object_id = OBJECT_ID(N'[dbo].[DeletePerson]')
289AND type in (N'P', N'PC'))
290BEGIN
291EXEC dbo.sp_executesql @statement = N'
292CREATE PROCEDURE [dbo].[DeletePerson]
293@PersonID int
294AS
295DELETE FROM Person WHERE PersonID = @PersonID;
296'
297END
298GO
299
300-- Create the UpdatePerson stored procedure.
301IF NOT EXISTS (SELECT * FROM sys.objects
302WHERE object_id = OBJECT_ID(N'[dbo].[UpdatePerson]')
303AND type in (N'P', N'PC'))
304BEGIN
305EXEC dbo.sp_executesql @statement = N'
306CREATE PROCEDURE [dbo].[UpdatePerson]
307@PersonID int,
308@LastName nvarchar(50),
309@FirstName nvarchar(50),
310@HireDate datetime,
311@EnrollmentDate datetime,
312@Discriminator nvarchar(50)
313AS
314UPDATE Person SET LastName=@LastName,
315FirstName=@FirstName,
316HireDate=@HireDate,
317EnrollmentDate=@EnrollmentDate,
318Discriminator=@Discriminator
319WHERE PersonID=@PersonID;
320'
321END
322GO
323
324-- Create the InsertPerson stored procedure.
325IF NOT EXISTS (SELECT * FROM sys.objects
326WHERE object_id = OBJECT_ID(N'[dbo].[InsertPerson]')
327AND type in (N'P', N'PC'))
328BEGIN
329EXEC dbo.sp_executesql @statement = N'
330CREATE PROCEDURE [dbo].[InsertPerson]
331@LastName nvarchar(50),
332@FirstName nvarchar(50),
333@HireDate datetime,
334@EnrollmentDate datetime,
335@Discriminator nvarchar(50)
336AS
337INSERT INTO dbo.Person (LastName,
338FirstName,
339HireDate,
340EnrollmentDate,
341Discriminator)
342VALUES (@LastName,
343@FirstName,
344@HireDate,
345@EnrollmentDate,
346@Discriminator);
347SELECT SCOPE_IDENTITY() as NewPersonID;
348'
349END
350GO
351
352-- Create GetStudentGrades stored procedure.
353IF NOT EXISTS (SELECT * FROM sys.objects
354WHERE object_id = OBJECT_ID(N'[dbo].[GetStudentGrades]')
355AND type in (N'P', N'PC'))
356BEGIN
357EXEC dbo.sp_executesql @statement = N'
358CREATE PROCEDURE [dbo].[GetStudentGrades]
359@StudentID int
360AS
361SELECT EnrollmentID, Grade, CourseID, StudentID FROM dbo.StudentGrade
362WHERE StudentID = @StudentID
363'
364END
365GO
366
367-- Create GetDepartmentName stored procedure.
368IF NOT EXISTS (SELECT * FROM sys.objects
369WHERE object_id = OBJECT_ID(N'[dbo].[GetDepartmentName]')
370AND type in (N'P', N'PC'))
371BEGIN
372EXEC dbo.sp_executesql @statement = N'
373CREATE PROCEDURE [dbo].[GetDepartmentName]
374@ID int,
375@Name nvarchar(50) OUTPUT
376AS
377SELECT @Name = Name FROM Department
378WHERE DepartmentID = @ID
379'
380END
381GO
382
383-- Insert data into the Person table.
384USE School
385GO
386SET IDENTITY_INSERT dbo.Person ON
387GO
388INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
389VALUES (1, 'Abercrombie', 'Kim', '1995-03-11', null, 'Instructor');
390INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
391VALUES (2, 'Barzdukas', 'Gytis', null, '2005-09-01', 'Student');
392INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
393VALUES (3, 'Justice', 'Peggy', null, '2001-09-01', 'Student');
394INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
395VALUES (4, 'Fakhouri', 'Fadi', '2002-08-06', null, 'Instructor');
396INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
397VALUES (5, 'Harui', 'Roger', '1998-07-01', null, 'Instructor');
398INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
399VALUES (6, 'Li', 'Yan', null, '2002-09-01', 'Student');
400INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
401VALUES (7, 'Norman', 'Laura', null, '2003-09-01', 'Student');
402INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
403VALUES (8, 'Olivotto', 'Nino', null, '2005-09-01', 'Student');
404INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
405VALUES (9, 'Tang', 'Wayne', null, '2005-09-01', 'Student');
406INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
407VALUES (10, 'Alonso', 'Meredith', null, '2002-09-01', 'Student');
408INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
409VALUES (11, 'Lopez', 'Sophia', null, '2004-09-01', 'Student');
410INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
411VALUES (12, 'Browning', 'Meredith', null, '2000-09-01', 'Student');
412INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
413VALUES (13, 'Anand', 'Arturo', null, '2003-09-01', 'Student');
414INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
415VALUES (14, 'Walker', 'Alexandra', null, '2000-09-01', 'Student');
416INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
417VALUES (15, 'Powell', 'Carson', null, '2004-09-01', 'Student');
418INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
419VALUES (16, 'Jai', 'Damien', null, '2001-09-01', 'Student');
420INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
421VALUES (17, 'Carlson', 'Robyn', null, '2005-09-01', 'Student');
422INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
423VALUES (18, 'Zheng', 'Roger', '2004-02-12', null, 'Instructor');
424INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
425VALUES (19, 'Bryant', 'Carson', null, '2001-09-01', 'Student');
426INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
427VALUES (20, 'Suarez', 'Robyn', null, '2004-09-01', 'Student');
428INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
429VALUES (21, 'Holt', 'Roger', null, '2004-09-01', 'Student');
430INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
431VALUES (22, 'Alexander', 'Carson', null, '2005-09-01', 'Student');
432INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
433VALUES (23, 'Morgan', 'Isaiah', null, '2001-09-01', 'Student');
434INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
435VALUES (24, 'Martin', 'Randall', null, '2005-09-01', 'Student');
436INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
437VALUES (25, 'Kapoor', 'Candace', '2001-01-15', null, 'Instructor');
438INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
439VALUES (26, 'Rogers', 'Cody', null, '2002-09-01', 'Student');
440INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
441VALUES (27, 'Serrano', 'Stacy', '1999-06-01', null, 'Instructor');
442INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
443VALUES (28, 'White', 'Anthony', null, '2001-09-01', 'Student');
444INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
445VALUES (29, 'Griffin', 'Rachel', null, '2004-09-01', 'Student');
446INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
447VALUES (30, 'Shan', 'Alicia', null, '2003-09-01', 'Student');
448INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
449VALUES (31, 'Stewart', 'Jasmine', '1997-10-12', null, 'Instructor');
450INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
451VALUES (32, 'Xu', 'Kristen', '2001-7-23', null, 'Instructor');
452INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
453VALUES (33, 'Gao', 'Erica', null, '2003-01-30', 'Student');
454INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
455VALUES (34, 'Van Houten', 'Roger', '2000-12-07', null, 'Instructor');
456GO
457SET IDENTITY_INSERT dbo.Person OFF
458GO
459
460-- Insert data into the Department table.
461INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
462VALUES (1, 'Engineering', 350000.00, '2007-09-01', 2);
463INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
464VALUES (2, 'English', 120000.00, '2007-09-01', 6);
465INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
466VALUES (4, 'Economics', 200000.00, '2007-09-01', 4);
467INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
468VALUES (7, 'Mathematics', 250000.00, '2007-09-01', 3);
469GO
470
471
472
473-- Insert data into the Course table.
474INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
475VALUES (1050, 'Chemistry', 4, 1);
476INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
477VALUES (1061, 'Physics', 4, 1);
478INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
479VALUES (1045, 'Calculus', 4, 7);
480INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
481VALUES (2030, 'Poetry', 2, 2);
482INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
483VALUES (2021, 'Composition', 3, 2);
484INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
485VALUES (2042, 'Literature', 4, 2);
486INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
487VALUES (4022, 'Microeconomics', 3, 4);
488INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
489VALUES (4041, 'Macroeconomics', 3, 4);
490INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
491VALUES (4061, 'Quantitative', 2, 4);
492INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
493VALUES (3141, 'Trigonometry', 4, 7);
494GO
495
496-- Insert data into the OnlineCourse table.
497INSERT INTO dbo.OnlineCourse (CourseID, URL)
498VALUES (2030, 'http://www.fineartschool.net/Poetry');
499INSERT INTO dbo.OnlineCourse (CourseID, URL)
500VALUES (2021, 'http://www.fineartschool.net/Composition');
501INSERT INTO dbo.OnlineCourse (CourseID, URL)
502VALUES (4041, 'http://www.fineartschool.net/Macroeconomics');
503INSERT INTO dbo.OnlineCourse (CourseID, URL)
504VALUES (3141, 'http://www.fineartschool.net/Trigonometry');
505
506--Insert data into OnsiteCourse table.
507INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
508VALUES (1050, '123 Smith', 'MTWH', '11:30');
509INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
510VALUES (1061, '234 Smith', 'TWHF', '13:15');
511INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
512VALUES (1045, '121 Smith','MWHF', '15:30');
513INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
514VALUES (4061, '22 Williams', 'TH', '11:15');
515INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
516VALUES (2042, '225 Adams', 'MTWH', '11:00');
517INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
518VALUES (4022, '23 Williams', 'MWF', '9:00');
519
520-- Insert data into the CourseInstructor table.
521INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
522VALUES (1050, 1);
523INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
524VALUES (1061, 31);
525INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
526VALUES (1045, 5);
527INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
528VALUES (2030, 4);
529INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
530VALUES (2021, 27);
531INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
532VALUES (2042, 25);
533INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
534VALUES (4022, 18);
535INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
536VALUES (4041, 32);
537INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
538VALUES (4061, 34);
539GO
540
541--Insert data into the OfficeAssignment table.
542INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
543VALUES (1, '17 Smith');
544INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
545VALUES (4, '29 Adams');
546INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
547VALUES (5, '37 Williams');
548INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
549VALUES (18, '143 Smith');
550INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
551VALUES (25, '57 Adams');
552INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
553VALUES (27, '271 Williams');
554INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
555VALUES (31, '131 Smith');
556INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
557VALUES (32, '203 Williams');
558INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
559VALUES (34, '213 Smith');
560
561-- Insert data into the StudentGrade table.
562INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
563VALUES (2021, 2, 4);
564INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
565VALUES (2030, 2, 3.5);
566INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
567VALUES (2021, 3, 3);
568INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
569VALUES (2030, 3, 4);
570INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
571VALUES (2021, 6, 2.5);
572INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
573VALUES (2042, 6, 3.5);
574INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
575VALUES (2021, 7, 3.5);
576INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
577VALUES (2042, 7, 4);
578INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
579VALUES (2021, 8, 3);
580INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
581VALUES (2042, 8, 3);
582INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
583VALUES (4041, 9, 3.5);
584INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
585VALUES (4041, 10, null);
586INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
587VALUES (4041, 11, 2.5);
588INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
589VALUES (4041, 12, null);
590INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
591VALUES (4061, 12, null);
592INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
593VALUES (4022, 14, 3);
594INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
595VALUES (4022, 13, 4);
596INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
597VALUES (4061, 13, 4);
598INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
599VALUES (4041, 14, 3);
600INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
601VALUES (4022, 15, 2.5);
602INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
603VALUES (4022, 16, 2);
604INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
605VALUES (4022, 17, null);
606INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
607VALUES (4022, 19, 3.5);
608INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
609VALUES (4061, 20, 4);
610INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
611VALUES (4061, 21, 2);
612INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
613VALUES (4022, 22, 3);
614INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
615VALUES (4041, 22, 3.5);
616INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
617VALUES (4061, 22, 2.5);
618INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
619VALUES (4022, 23, 3);
620INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
621VALUES (1045, 23, 1.5);
622INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
623VALUES (1061, 24, 4);
624INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
625VALUES (1061, 25, 3);
626INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
627VALUES (1050, 26, 3.5);
628INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
629VALUES (1061, 26, 3);
630INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
631VALUES (1061, 27, 3);
632INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
633VALUES (1045, 28, 2.5);
634INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
635VALUES (1050, 28, 3.5);
636INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
637VALUES (1061, 29, 4);
638INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
639VALUES (1050, 30, 3.5);
640INSERT INTO dbo.StudentGrade (CourseID, StudentID, Grade)
641VALUES (1061, 30, 4);
642GO