[翻译]ADO.NET Entity Framework Beta2(六)/快速入门(实体框架)(1)/创建学校演示数据库

简介:

In this task, you will create the schema for the School database and load data into the database. To do this, you use SQL Server Management Studio or SQL Server Management Studio Express to execute a Transact-SQL script. The resulting School database is used as the relational data store in the rest of the tasks in this quickstart.

在本任务中,你将创建学校数据库架构并填充数据。你要使用SQL Server 管理中心或 SQL Server Express管理中心来执行一个TSQL脚本。生成的数据库将在剩下的快速入门任务中作为关系型数据库被使用。

To create the School database and schema/创建数据库及架构

  1. On the File menu, point to New, and then click Database Engine Query. 在文件菜单,点击新建,然后单击数据库引擎查询

  2. In the Connect to Database Engine dialog box, type either localhost or the name of the local SQL Server instance, and then click Connect.在连接到数据库引擎对话框,输入Loccalhost或者本地SQL Server的实例名,然后单击连接

  3. Paste the following Transact-SQL script in the query window and then click Execute.粘贴以下TSQL脚本到查询窗口并单击工具栏上的执行按钮。



    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

    USE School;
    GO

    -- Create the Course table.
    IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
    OBJECT_ID(N'[dbo].[Course]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
    CREATE TABLE [dbo].[Course](
    [CourseID] [int] NOT NULL,
    [Title] [nvarchar](100) NOT NULL,
    [StartDate] [datetime] NULL,
    [EndDate] [datetime] NULL,
    [Credits] [int] NULL,
    CONSTRAINT [PK_School.Course] PRIMARY KEY CLUSTERED
    (
    [CourseID] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    END
    GO

    -- Create the Person table.
    IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
    OBJECT_ID(N'[dbo].[Person]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    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
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    END
    GO

    -- Create the Enrollment table.
    IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
    OBJECT_ID(N'[dbo].[Enrollment]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
    CREATE TABLE [dbo].[Enrollment](
    [EnrollmentID] [int] IDENTITY(1,1) NOT NULL,
    [CourseID] [int] NOT NULL,
    [StudentPersonID] [int] NOT NULL,
    [Grade] [decimal](3, 2) NULL,
    CONSTRAINT [PK_Enrollment] PRIMARY KEY CLUSTERED
    (
    [EnrollmentID] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    END
    GO

    -- Create the CourseInstructor table.
    IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[CourseInstructor]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
    CREATE TABLE [dbo].[CourseInstructor](
    [CourseInstructorID] [int] IDENTITY(1,1) NOT NULL,
    [CourseID] [int] NOT NULL,
    [PersonID] [int] NOT NULL,
    CONSTRAINT [PK_CourseInstructor] PRIMARY KEY CLUSTERED
    (
    [CourseInstructorID] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    END
    GO

    -- Define the relationship between Enrollment and Course.
    IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Enrollment_Course]') AND type = 'F')
    ALTER TABLE [dbo].[Enrollment] WITH CHECK ADD CONSTRAINT [FK_Enrollment_Course] FOREIGN KEY([CourseID])
    REFERENCES [dbo].[Course] ([CourseID])
    GO

    ALTER TABLE [dbo].[Enrollment] CHECK CONSTRAINT [FK_Enrollment_Course]
    GO

    -- Define the relationship between Enrollment and Person.
    IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_Enrollment_Student]') AND type = 'F')
    ALTER TABLE [dbo].[Enrollment] WITH CHECK ADD CONSTRAINT [FK_Enrollment_Student] FOREIGN KEY([StudentPersonID])
    REFERENCES [dbo].[Person] ([PersonID])
    GO

    ALTER TABLE [dbo].[Enrollment] CHECK CONSTRAINT [FK_Enrollment_Student]
    GO

    -- Define the relationship between CourseInstructor and Course.
    IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Course]') AND type = 'F')
    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 dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_CourseInstructor_Person]') AND type = 'F')
    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

    -- 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', '2000-02-12', null);
    GO
    SET IDENTITY_INSERT dbo.Person OFF
    GO

    -- Insert data into the Course table.
    INSERT INTO dbo.Course (CourseID, Title, StartDate, EndDate, Credits)
    VALUES (4050, 'Math', '2007-09-01', '2007-09-01', 4);
    INSERT INTO dbo.Course (CourseID, Title, StartDate, EndDate, Credits)
    VALUES (4051, 'Science', '2007-09-01', '2007-09-01', 4);
    INSERT INTO dbo.Course (CourseID, Title, StartDate, EndDate, Credits)
    VALUES (3030, 'Art', '2007-09-01', '2007-09-01', 3);
    INSERT INTO dbo.Course (CourseID, Title, StartDate, EndDate, Credits)
    VALUES (2021, 'Woodshop', '2007-09-01', '2007-09-01', 2);
    INSERT INTO dbo.Course (CourseID, Title, StartDate, EndDate, Credits)
    VALUES (2022, 'Cooking', '2007-09-01', '2007-09-01', 2);
    INSERT INTO dbo.Course (CourseID, Title, StartDate, EndDate, Credits)
    VALUES (4041, 'History', '2007-09-01', '2007-09-01', 4);
    INSERT INTO dbo.Course (CourseID, Title, StartDate, EndDate, Credits)
    VALUES (4042, 'Literature', '2007-09-01', '2007-09-01', 4);
    GO

    -- Insert data into the CourseInstructor table.
    SET IDENTITY_INSERT dbo.CourseInstructor ON
    GO
    INSERT INTO dbo.CourseInstructor(CourseInstructorID, CourseID, PersonID)
    VALUES (1, 4051, 1);
    INSERT INTO dbo.CourseInstructor(CourseInstructorID, CourseID, PersonID)
    VALUES (2, 2021, 31);
    INSERT INTO dbo.CourseInstructor(CourseInstructorID, CourseID, PersonID)
    VALUES (3, 2022, 5);
    INSERT INTO dbo.CourseInstructor(CourseInstructorID, CourseID, PersonID)
    VALUES (4, 3030, 4);
    INSERT INTO dbo.CourseInstructor(CourseInstructorID, CourseID, PersonID)
    VALUES (5, 4041, 27);
    INSERT INTO dbo.CourseInstructor(CourseInstructorID, CourseID, PersonID)
    VALUES (6, 4042, 25);
    INSERT INTO dbo.CourseInstructor(CourseInstructorID, CourseID, PersonID)
    VALUES (7, 4050, 18);
    GO
    SET IDENTITY_INSERT dbo.CourseInstructor OFF
    GO

    -- Insert data into the Enrollment table.
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (2021, 2, 4);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (2021, 3, 3);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (2022, 6, 2.5);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (2022, 7, 3.5);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (2022, 8, 3);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (4041, 9, 3.5);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (4041, 10, null);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (4041, 11, 2.5);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (4041, 12, null);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (4041, 13, 4);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (4042, 14, 3);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (4050, 15, 2.5);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (4050, 16, 2);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (4050, 17, null);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (4050, 19, 3.5);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (4050, 20, 4);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (4050, 21, 2);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (4050, 22, 3);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (4041, 22, 3.5);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (2022, 22, 2.5);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (4051, 22, 3);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (4051, 23, 1.5);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (4051, 24, 4);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (4051, 26, 3.5);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (4051, 28, 2.5);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (4051, 29, 3);
    INSERT INTO dbo.Enrollment (CourseID, StudentPersonID, Grade)
    VALUES (3030, 30, 3.5);
    GO

Next Steps/下一H

You have successfully created the School database. Next, you will create the class scheduling application:

你已经成功创建了学校数据库,下面你将创建ClassScheduling用程序。

Creating the Class Schedule Application/创建ClassSchedule应用程序.

See Also/请参考

作者: 徐少侠
出处: http://www.cnblogs.com/Chinese-xu/

本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接。
如有问题,可以通过 Chinese_Xu@126.com 联系我,非常感谢。

分享家:Addthis中文版

本文转自徐少侠博客园博客,原文链接:http://www.cnblogs.com/Chinese-xu/archive/2007/09/21/901232.html,如需转载请自行联系原作者
目录
相关文章
|
C# Android开发 iOS开发
2025年全面的.NET跨平台应用框架推荐
2025年全面的.NET跨平台应用框架推荐
701 23
|
消息中间件 开发框架 监控
NET任务调度框架Hangfire使用指南
Hangfire 是一个用于 .NET 应用程序的开源任务调度框架,支持长时间运行任务、定时任务等。通过简单的安装配置,即可将任务从主线程分离,提升应用性能。支持多种数据库,提供丰富的任务类型如立即执行、延迟执行和周期性任务,并有可视化管理界面 Hangfire Dashboard。还支持安全性配置及扩展插件,如 Hangfire.HttpJob,适合各种复杂场景下的任务调度需求。
1505 1
NET任务调度框架Hangfire使用指南
|
监控 前端开发 API
一款基于 .NET MVC 框架开发、功能全面的MES系统
一款基于 .NET MVC 框架开发、功能全面的MES系统
567 5
|
NoSQL 关系型数据库 分布式数据库
PolarDB图数据库快速入门
图数据库(Graph Database)专门存储图数据,适合处理社交网络、知识图谱等复杂关系。它使用图查询语言(如Cypher、Gremlin)进行操作。PolarDB兼容OpenCypher语法,支持创建、查询、更新和删除图数据,包括模式匹配、过滤、MERGE避免重复、可视化工具等功能,简化了图数据的管理和应用。
|
数据库连接 数据库 C#
Windows下C# 通过ADO.NET方式连接南大通用GBase 8s数据库(上)
Windows下C# 通过ADO.NET方式连接南大通用GBase 8s数据库(上)
|
6月前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能(下)
使用MYSQL Report分析数据库性能
480 158
|
6月前
|
关系型数据库 MySQL 数据库
自建数据库如何迁移至RDS MySQL实例
数据库迁移是一项复杂且耗时的工程,需考虑数据安全、完整性及业务中断影响。使用阿里云数据传输服务DTS,可快速、平滑完成迁移任务,将应用停机时间降至分钟级。您还可通过全量备份自建数据库并恢复至RDS MySQL实例,实现间接迁移上云。
|
6月前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。
1149 152
|
6月前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎,提供高性价比、稳定安全的云数据库服务,适用于多种行业与业务场景。
881 156