前言

      本系列整理自 。

数据库准备

       该系列用到的数据库为 school，使用SQL Server。如果没有安装，可以使用VS附带的SQL Express。步骤省略，不清楚的可以使用“靠谱的”搜索引擎查找。

       SQL脚本：

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- 创建 Department 表。IF NOT EXISTS (SELECT * FROM sys.objectsWHERE object_id = OBJECT_ID(N'[dbo].[Department]')AND type in (N'U'))BEGINCREATE 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]ENDGO-- 创建 Person 表。IF NOT EXISTS (SELECT * FROM sys.objectsWHERE object_id = OBJECT_ID(N'[dbo].[Person]')AND type in (N'U'))BEGINCREATE 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,[Discriminator] [nvarchar](50) NOT NULL,CONSTRAINT [PK_School.Student] PRIMARY KEY CLUSTERED([PersonID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]ENDGO-- 创建 OnsiteCourse 表。IF NOT EXISTS (SELECT * FROM sys.objectsWHERE object_id = OBJECT_ID(N'[dbo].[OnsiteCourse]')AND type in (N'U'))BEGINCREATE 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]ENDGO-- 创建 OnlineCourse 表。IF NOT EXISTS (SELECT * FROM sys.objectsWHERE object_id = OBJECT_ID(N'[dbo].[OnlineCourse]')AND type in (N'U'))BEGINCREATE 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]ENDGO-- 创建 StudentGrade 表。IF NOT EXISTS (SELECT * FROM sys.objectsWHERE object_id = OBJECT_ID(N'[dbo].[StudentGrade]')AND type in (N'U'))BEGINCREATE 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]ENDGO-- 创建 CourseInstructor 表。IF NOT EXISTS (SELECT * FROM sys.objectsWHERE object_id = OBJECT_ID(N'[dbo].[CourseInstructor]')AND type in (N'U'))BEGINCREATE 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]ENDGO-- 创建 Course 表。IF NOT EXISTS (SELECT * FROM sys.objectsWHERE object_id = OBJECT_ID(N'[dbo].[Course]')AND type in (N'U'))BEGINCREATE 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]ENDGO-- 创建 OfficeAssignment 表。IF NOT EXISTS (SELECT * FROM sys.objectsWHERE object_id = OBJECT_ID(N'[dbo].[OfficeAssignment]')AND type in (N'U'))BEGINCREATE 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]ENDGO-- 定义 OnsiteCourse 与 Course 之间的关系。IF NOT EXISTS (SELECT * FROM sys.foreign_keysWHERE object_id = OBJECT_ID(N'[dbo].[FK_OnsiteCourse_Course]')AND parent_object_id = OBJECT_ID(N'[dbo].[OnsiteCourse]'))ALTER TABLE [dbo].[OnsiteCourse] WITH CHECK ADDCONSTRAINT [FK_OnsiteCourse_Course] FOREIGN KEY([CourseID])REFERENCES [dbo].[Course] ([CourseID])GOALTER TABLE [dbo].[OnsiteCourse] CHECKCONSTRAINT [FK_OnsiteCourse_Course]GO-- 定义 OnlineCourse 与 Course 之间的关系。IF NOT EXISTS (SELECT * FROM sys.foreign_keysWHERE object_id = OBJECT_ID(N'[dbo].[FK_OnlineCourse_Course]')AND parent_object_id = OBJECT_ID(N'[dbo].[OnlineCourse]'))ALTER TABLE [dbo].[OnlineCourse] WITH CHECK ADDCONSTRAINT [FK_OnlineCourse_Course] FOREIGN KEY([CourseID])REFERENCES [dbo].[Course] ([CourseID])GOALTER TABLE [dbo].[OnlineCourse] CHECKCONSTRAINT [FK_OnlineCourse_Course]GO-- 定义 StudentGrade 与 Course 之间的关系。IF NOT EXISTS (SELECT * FROM sys.foreign_keysWHERE object_id = OBJECT_ID(N'[dbo].[FK_StudentGrade_Course]')AND parent_object_id = OBJECT_ID(N'[dbo].[StudentGrade]'))ALTER TABLE [dbo].[StudentGrade] WITH CHECK ADDCONSTRAINT [FK_StudentGrade_Course] FOREIGN KEY([CourseID])REFERENCES [dbo].[Course] ([CourseID])GOALTER TABLE [dbo].[StudentGrade] CHECKCONSTRAINT [FK_StudentGrade_Course]GO-- 定义 StudentGrade 与 Student 之间的关系。IF NOT EXISTS (SELECT * FROM sys.foreign_keysWHERE object_id = OBJECT_ID(N'[dbo].[FK_StudentGrade_Student]')AND parent_object_id = OBJECT_ID(N'[dbo].[StudentGrade]'))ALTER TABLE [dbo].[StudentGrade] WITH CHECK ADDCONSTRAINT [FK_StudentGrade_Student] FOREIGN KEY([StudentID])REFERENCES [dbo].[Person] ([PersonID])GOALTER TABLE [dbo].[StudentGrade] CHECKCONSTRAINT [FK_StudentGrade_Student]GO-- 定义 CourseInstructor 与 Course 之间的关系。IF NOT EXISTS (SELECT * FROM sys.foreign_keysWHERE object_id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Course]')AND parent_object_id = OBJECT_ID(N'[dbo].[CourseInstructor]'))ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADDCONSTRAINT [FK_CourseInstructor_Course] FOREIGN KEY([CourseID])REFERENCES [dbo].[Course] ([CourseID])GOALTER TABLE [dbo].[CourseInstructor] CHECKCONSTRAINT [FK_CourseInstructor_Course]GO-- 定义 CourseInstructor 与 Person 之间的关系。IF NOT EXISTS (SELECT * FROM sys.foreign_keysWHERE object_id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Person]')AND parent_object_id = OBJECT_ID(N'[dbo].[CourseInstructor]'))ALTER TABLE [dbo].[CourseInstructor] WITH CHECK ADDCONSTRAINT [FK_CourseInstructor_Person] FOREIGN KEY([PersonID])REFERENCES [dbo].[Person] ([PersonID])GOALTER TABLE [dbo].[CourseInstructor] CHECKCONSTRAINT [FK_CourseInstructor_Person]GO-- 定义 Course 与 Department 之间的关系。IF NOT EXISTS (SELECT * FROM sys.foreign_keysWHERE object_id = OBJECT_ID(N'[dbo].[FK_Course_Department]')AND parent_object_id = OBJECT_ID(N'[dbo].[Course]'))ALTER TABLE [dbo].[Course] WITH CHECK ADDCONSTRAINT [FK_Course_Department] FOREIGN KEY([DepartmentID])REFERENCES [dbo].[Department] ([DepartmentID])GOALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department]GO-- 定义 OfficeAssignment 与 Person 之间的关系。IF NOT EXISTS (SELECT * FROM sys.foreign_keysWHERE object_id = OBJECT_ID(N'[dbo].[FK_OfficeAssignment_Person]')AND parent_object_id = OBJECT_ID(N'[dbo].[OfficeAssignment]'))ALTER TABLE [dbo].[OfficeAssignment] WITH CHECK ADDCONSTRAINT [FK_OfficeAssignment_Person] FOREIGN KEY([InstructorID])REFERENCES [dbo].[Person] ([PersonID])GOALTER TABLE [dbo].[OfficeAssignment] CHECKCONSTRAINT [FK_OfficeAssignment_Person]GO-- 创建 InsertOfficeAssignment 存储过程。IF NOT EXISTS (SELECT * FROM sys.objectsWHERE object_id = OBJECT_ID(N'[dbo].[InsertOfficeAssignment]')AND type in (N'P', N'PC'))BEGINEXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[InsertOfficeAssignment]@InstructorID int,@Location nvarchar(50)ASINSERT INTO dbo.OfficeAssignment (InstructorID, Location)VALUES (@InstructorID, @Location);IF @@ROWCOUNT > 0BEGINSELECT [Timestamp] FROM OfficeAssignmentWHERE InstructorID=@InstructorID;END'ENDGO-- 创建 UpdateOfficeAssignment 存储过程。IF NOT EXISTS (SELECT * FROM sys.objectsWHERE object_id = OBJECT_ID(N'[dbo].[UpdateOfficeAssignment]')AND type in (N'P', N'PC'))BEGINEXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[UpdateOfficeAssignment]@InstructorID int,@Location nvarchar(50),@OrigTimestamp timestampASUPDATE OfficeAssignment SET Location=@LocationWHERE InstructorID=@InstructorID AND [Timestamp]=@OrigTimestamp;IF @@ROWCOUNT > 0BEGINSELECT [Timestamp] FROM OfficeAssignmentWHERE InstructorID=@InstructorID;END'ENDGO-- 创建 DeleteOfficeAssignment 存储过程。IF NOT EXISTS (SELECT * FROM sys.objectsWHERE object_id = OBJECT_ID(N'[dbo].[DeleteOfficeAssignment]')AND type in (N'P', N'PC'))BEGINEXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[DeleteOfficeAssignment]@InstructorID intASDELETE FROM OfficeAssignmentWHERE InstructorID=@InstructorID;'ENDGO-- 创建 DeletePerson 存储过程。IF NOT EXISTS (SELECT * FROM sys.objectsWHERE object_id = OBJECT_ID(N'[dbo].[DeletePerson]')AND type in (N'P', N'PC'))BEGINEXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[DeletePerson]@PersonID intASDELETE FROM Person WHERE PersonID = @PersonID;'ENDGO-- 创建 UpdatePerson 存储过程。IF NOT EXISTS (SELECT * FROM sys.objectsWHERE object_id = OBJECT_ID(N'[dbo].[UpdatePerson]')AND type in (N'P', N'PC'))BEGINEXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[UpdatePerson]@PersonID int,@LastName nvarchar(50),@FirstName nvarchar(50),@HireDate datetime,@EnrollmentDate datetime,@Discriminator nvarchar(50)ASUPDATE Person SET LastName=@LastName,FirstName=@FirstName,HireDate=@HireDate,EnrollmentDate=@EnrollmentDate,Discriminator=@DiscriminatorWHERE PersonID=@PersonID;'ENDGO-- 创建 InsertPerson 存储过程。IF NOT EXISTS (SELECT * FROM sys.objectsWHERE object_id = OBJECT_ID(N'[dbo].[InsertPerson]')AND type in (N'P', N'PC'))BEGINEXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[InsertPerson]@LastName nvarchar(50),@FirstName nvarchar(50),@HireDate datetime,@EnrollmentDate datetime,@Discriminator nvarchar(50)ASINSERT INTO dbo.Person (LastName,FirstName,HireDate,EnrollmentDate,Discriminator)VALUES (@LastName,@FirstName,@HireDate,@EnrollmentDate,@Discriminator);SELECT SCOPE_IDENTITY() as NewPersonID;'ENDGO-- 创建 GetStudentGrades 存储过程。IF NOT EXISTS (SELECT * FROM sys.objectsWHERE object_id = OBJECT_ID(N'[dbo].[GetStudentGrades]')AND type in (N'P', N'PC'))BEGINEXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GetStudentGrades]@StudentID intASSELECT EnrollmentID, Grade, CourseID, StudentID FROM dbo.StudentGradeWHERE StudentID = @StudentID'ENDGO-- 创建 GetDepartmentName 存储过程。IF NOT EXISTS (SELECT * FROM sys.objectsWHERE object_id = OBJECT_ID(N'[dbo].[GetDepartmentName]')AND type in (N'P', N'PC'))BEGINEXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[GetDepartmentName]@ID int,@Name nvarchar(50) OUTPUTASSELECT @Name = Name FROM DepartmentWHERE DepartmentID = @ID'ENDGO-- 将数据插到 Person 表中。USE SchoolGOSET IDENTITY_INSERT dbo.Person ONGOINSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (1, 'Abercrombie', 'Kim', '1995-03-11', null, 'Instructor');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (2, 'Barzdukas', 'Gytis', null, '2005-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (3, 'Justice', 'Peggy', null, '2001-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (4, 'Fakhouri', 'Fadi', '2002-08-06', null, 'Instructor');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (5, 'Harui', 'Roger', '1998-07-01', null, 'Instructor');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (6, 'Li', 'Yan', null, '2002-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (7, 'Norman', 'Laura', null, '2003-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (8, 'Olivotto', 'Nino', null, '2005-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (9, 'Tang', 'Wayne', null, '2005-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (10, 'Alonso', 'Meredith', null, '2002-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (11, 'Lopez', 'Sophia', null, '2004-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (12, 'Browning', 'Meredith', null, '2000-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (13, 'Anand', 'Arturo', null, '2003-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (14, 'Walker', 'Alexandra', null, '2000-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (15, 'Powell', 'Carson', null, '2004-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (16, 'Jai', 'Damien', null, '2001-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (17, 'Carlson', 'Robyn', null, '2005-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (18, 'Zheng', 'Roger', '2004-02-12', null, 'Instructor');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (19, 'Bryant', 'Carson', null, '2001-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (20, 'Suarez', 'Robyn', null, '2004-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (21, 'Holt', 'Roger', null, '2004-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (22, 'Alexander', 'Carson', null, '2005-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (23, 'Morgan', 'Isaiah', null, '2001-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (24, 'Martin', 'Randall', null, '2005-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (25, 'Kapoor', 'Candace', '2001-01-15', null, 'Instructor');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (26, 'Rogers', 'Cody', null, '2002-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (27, 'Serrano', 'Stacy', '1999-06-01', null, 'Instructor');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (28, 'White', 'Anthony', null, '2001-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (29, 'Griffin', 'Rachel', null, '2004-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (30, 'Shan', 'Alicia', null, '2003-09-01', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (31, 'Stewart', 'Jasmine', '1997-10-12', null, 'Instructor');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (32, 'Xu', 'Kristen', '2001-7-23', null, 'Instructor');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (33, 'Gao', 'Erica', null, '2003-01-30', 'Student');INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)VALUES (34, 'Van Houten', 'Roger', '2000-12-07', null, 'Instructor');GOSET IDENTITY_INSERT dbo.Person OFFGO-- 将数据插到 Department 表中。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 -- 将数据插到 Course 表中。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-- 将数据插到 OnlineCourse 表中。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');-- 将数据插到 OnsiteCourse 表中。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');-- 将数据插到 CourseInstructor 表中。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-- 将数据插到 OfficeAssignment 表中。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');-- 将数据插到 StudentGrade 表中。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

  实体框架词汇表