四巨头第五周作业翻译
系列
这篇文章是阶梯系列的一部分:通往T-SQL DML的阶梯。通过使用SQL Server的Transact-SQL (T-SQL)语言，这个阶梯将为您提供如何使用SQL Server表数据的基本解释。DML是数据处理语言，是处理数据方面的语言。它包括语句选择、插入、更新和删除。这个阶梯将提供一些SQL语言的历史和一些关于集合理论的一般概念。每个级别都将建立在之前的级别上，所以当您完成时，您将很好地理解如何从SQL Server中选择和修改数据。
在这个阶梯的前一级，我向您提供了关于基本选择语句和SQL历史的信息。这些级别为您提供了理解如何检索数据以及SQL环境如何随着技术和技术解决方案发生变化而变化的基础。在这个层次上，我将探讨如何实现基于关系模型的简单SQL Server数据库。在开始创建数据库之前，首先让我介绍一下关系模型的创建者的一些历史。
关系数据建模之父
关系数据库设计的概念最初是由Edgar F. Codd在1970年提出的，论文标题为“大型共享数据银行的数据关系模型”。Codd在IBM工作时开发了这个建模理论。IBM在Codd的数据建模概念上的速度不够快，因此并不是第一个提供关系数据库引擎的供应商，它利用了Codd的新关系数据建模理论。Codd的关系建模概念现在是用于在SQL Server和其他关系数据库引擎中创建关系数据库的框架。
Codd出生在英国的波特兰岛，在加入皇家空军成为二战飞行员之前，他学习了数学和化学。1948年他搬到纽约，开始在IBM工作，在那里他是一名数学程序员。他漂流了好几年，最终搬到加利福尼亚，在IBM圣何塞研究实验室工作。Codd继续致力于完善和证明关系数据模型，直到上世纪90年代他的健康问题迫使他退休。埃德加·f·科德于2003年4月18日去世，享年79岁。
在SQL Server中实现关系模型
这个阶梯不是用来教你关系数据建模或数据库设计的，而是教你如何从一个关系模型创建一个SQL Server数据库。但是，在我为您提供创建SQL Server数据库的代码块之前，我们首先需要探索将要实现的关系数据模型。我的简单模型将包含几个实体(数据表)，其中有主键定义和不同实体之间的一些关系(外键约束)。
我的简单关系模型将是一个简单的酒店预订系统。这个预订系统需要跟踪客户预订信息。图1说明了我将使用T-SQL实现的简单关系模型:
通过回顾这个模型，您可以看到它包含了许多实体(以方框表示)来跟踪预订相关信息。每个实体由许多属性(列)组成，其中一个或多个属性被标识为主键(粗体和下划线的名称)。同样表示的是实体之间的一些关系(以箭头表示)，以显示不同的实体之间是如何相互关联的。我将使用实体、属性、主键和关系的模型，然后开发一个物理SQL Server数据库，该数据库表示此关系模型的设计。
要从这个模型构建一个物理数据库，我们需要在这个模型中定义SQL Server中定义的不同对象。对于图1中的每个实体或框，我将在SQL Server中创建一个表。对于每个实体的每个属性，我将在关联的表中创建一个列。对于每个主键，我将创建一个惟一的聚集索引(注意，也可以使用唯一的非聚集索引创建主键)。有关索引的更多信息，请参见http://www.sqlservercentral.com/stairway/72399/)。最后，对于每个关系，我将创建一个外键约束。
为了开始构建我的数据库，我首先需要创建一个SQL Server数据库来保存我计划创建的所有新数据库对象。我的数据库将被称为RoomReservation。我将使用以下的T-SQL代码创建我的数据库:
为了开始从我的模型构建 RoomReservation数据库对象，我将创建表对象。要在SQL Server中创建表，我需要使用CREATE TABLE语句。使用CREATE TABLE语句，我将能够定义每个表和每个表中的所有列。下面是创建SQLServer表的简单语法
CREATE TABLE <table_name> (
<column_definition> [,…N]);
Where:
<table_name> = Name of table
<column_definition> = column_name
data_type,[NULL | NOT NULL]
有关CREATE TABLE语句的完整语法，请参阅SQLServerBooks Online。
我创建的第一个表将是Customer表，它使用清单1中的代码创建。
USE RoomReservation;
GO
CREATE TABLE dbo.Customer (
CustomerId INT NOT NULL,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
Address1 NVARCHAR(100) NOT NULL,
Address2 NVARCHAR(100) NULL,
PhoneNumber NVARCHAR(22) NOT NULL,
EmailAddress VARCHAR(100) NULL,
City VARCHAR(100) NOT NULL,
StateProvince NVARCHAR(100) NOT NULL,
PostalCode NVARCHAR(100) NOT NULL);
Listing 1：创建Customer表
在这段代码中，当我创建Customer表时，我创建了所需的所有列，但我也指定了在向该表中插入或更新记录时，该列是否需要一个值。我在某些列上指定NOT NULL，而另一些列指定NULL来实现这一点。
如果列被定义为NOT NULL，这意味着除非用实际值填充该列，否则无法创建记录。尽管使用NULL规范定义列意味着你可以创建一行，而无需为该列指定值，或者说，另一种方法是该列允许空值。在上面的CREATE TABLE语句中，我允许列Address 2和EmailAddress支持NULL，而所有其余的列都需要在创建行时提供一个值。
这个CREATE TABLE语句没有完全定义我的Customer表，因为它在上面的关系数据库模型中表示。我仍然需要在列CustomerID上创建一个主键约束。此主键约束将确保本表中没有两个记录具有相同的CustomerID值。创建主键的代码如清单2所示。
USE RoomReservation;
GO
ALTER TABLE dbo.Customer ADD CONSTRAINT
PK_Customer PRIMARY KEY CLUSTERED (CustomerId);
Listing 2：向Customer表添加主键约束
此ALTERTABLE语句向我的Customer表添加了一个主键约束。该主键将以名为pk_Customer的聚集索引的形式创建。
在Transact-SQL语言中，通常有不止一种方法来做同样的事情。或者，通过运行清单3中的CREATE TABLE语句，可以一次性创建Customer表和主键。
USE RoomReservation;
GO
CREATE TABLE dbo.Customer (
CustomerId INT NOT NULL CONSTRAINT PK_Customer PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
Address1 NVARCHAR(100) NOT NULL,
Address2 NVARCHAR(100) NULL,
PhoneNumber NVARCHAR(22) NOT NULL,
EmailAddress NVARCHAR(100) NULL,
City VARCHAR(100) NOT NULL,
StateProvince NVARCHAR(100) NOT NULL,
PostalCode NVARCHAR(100) NOT NULL);
Listing 3：用主键创建客户表的另一种方法
至此，我已经向你展示了如何创建一个具有定义的主键的表。剩下的唯一要向你展示的是如何创建外键约束。但在我这样做之前，首先让我提供脚本，以便在上面的关系数据库模型中创建其余的表和主键。你可以在清单4中找到它。
USE RoomReservation;
GO
CREATE TABLE dbo.Reservation (
ReservationId INT NOT NULL,
ArrivalDate DATETIME NOT NULL,
DepartureDate DATETIME NOT NULL,
DailyRate SMALLMONEY NOT NULL,
ReservationStatusID INT NOT NULL,
CustomerId INT NOT NULL,
RoomTypeID INT NOT NULL);
ALTER TABLE dbo.Reservation ADD CONSTRAINT
PK_Reservation PRIMARY KEY CLUSTERED (ReservationId);
CREATE TABLE dbo.RoomType (
RoomTypeId INT NOT NULL,
RoomDesc NVARCHAR(1000) NOT NULL);
ALTER TABLE dbo.RoomType ADD CONSTRAINT
PK_RoomType PRIMARY KEY CLUSTERED (RoomTypeId);
CREATE TABLE dbo.ReservationStatus (
ReservationStatusId INT NOT NULL,
ReservationStatusDesc NVARCHAR(50) NOT NULL);
ALTER TABLE dbo.ReservationStatus ADD CONSTRAINT
PK_ReservationStatus PRIMARY KEY CLUSTERED (ReservationStatusId);
CREATE TABLE dbo.PaymentType (
PaymentTypeId INT NOT NULL,
PaymentTypeDesc NVARCHAR(50) NOT NULL);
ALTER TABLE dbo.PaymentType ADD CONSTRAINT
PK_PaymentType PRIMARY KEY CLUSTERED (PaymentTypeId);
CREATE TABLE dbo.CustomerPaymentType (
PaymentTypeId INT NOT NULL,
CustomerId INT NOT NULL,
PaymentNotes NVARCHAR(2000) NULL);
ALTER TABLE dbo.CustomerPaymentType ADD CONSTRAINT
PK_CustomerPaymentType PRIMARY KEY CLUSTERED (PaymentTypeId, CustomerId);
Listing 4：创建附加表和主键约束
外键约束强制两个相互关联的表之间的引用完整性。外键约束定义的表是“引用表”，并要求在表中插入或更新行时，在另一个表中有一个相关记录，称为“Reference”表。在图1中的关系模型中，这些外键关系由箭头表示。外键约束仅在关系中的一个表上定义。在我的图表中，外键约束将定义在那些带有箭头尾(非尖头末端)的表上。
要在关系模型中定义这些外键约束，我需要修改每个引用表以添加约束。清单5是我可以用来在订票表上创建外键约束的T-SQL代码。此约束确保记录不会被插入或更新到预订表中，除非在CustomerId的Customer表中找到匹配的记录。
USE RoomReservation;
GO
ALTER TABLE dbo.Reservation
ADD CONSTRAINT FK_Reservation_CustomerPaymentType FOREIGN KEY (CustomerId)
REFERENCES dbo.Customer (CustomerID);
Listing 5：在引用客户表的预订表上创建外键约束
为了完成我的设计，我需要实现图1中我的模型中标识的所有其他外键约束。清单6包含ALTER TABLE语句，用于在我的数据模型中创建额外的外键约束。
USE RoomReservation;
GO
ALTER TABLE dbo.Reservation
ADD CONSTRAINT FK_Reservation_RoomType FOREIGN KEY (RoomTypeId)
REFERENCES dbo.RoomType (RoomTypeId);
ALTER TABLE dbo.Reservation
ADD CONSTRAINT FK_Reservation_ReservationStatus FOREIGN KEY (ReservationStatusId)
REFERENCES dbo.ReservationStatus (ReservationStatusId);
ALTER TABLE dbo.CustomerPaymentType
ADD CONSTRAINT FK_CustomerPaymentType_PaymentType FOREIGN KEY (PaymentTypeId)
REFERENCES dbo.PaymentType (PaymentTypeId);
ALTER TABLE dbo.CustomerPaymentType
ADD CONSTRAINT FK_CustomerPaymentType_Customer FOREIGN KEY (CustomerId)
REFERENCES dbo.Customer (CustomerId);
清单6:创建额外的外键约束
验证数据库设计
一旦我完成了从数据模型构建数据库的工作，我会验证实现的设计以确保它是正确的。这个验证过程是确保我在物理数据库中建立的所有数据完整性规则都是正确的。在我的设计中，需要验证这是我的规则。
l 插入或更新的所有行必须为定义为NOT NULL的任一列定义特定的值。
l 主键在的列的值不允许重复。
l 具有外键限制器的栏不允许在引用表中没有匹配记录的数据
在我验证数据完整性规则之前，我首先需要用一些有效的数据填充引用的表。我将使用清单7中的代码用一些有效数据填充这些表：
USE RoomReservation;
GO
SET NOCOUNT ON;
-- Create PaymentType records
INSERT INTO PaymentType VALUES(1,'Visa');
INSERT INTO PaymentType VALUES(2,'MasterCard');
INSERT INTO PaymentType VALUES(3,'American Express');
-- Create Customer
INSERT INTO Customer VALUES
(1,'Greg','Larsen','123 Some Place'
,NULL,'123-456-7890',Null,'MyCity','MA','12345');
-- Create Reservation Status
INSERT INTO ReservationStatus VALUES (1,'Booked');
INSERT INTO ReservationStatus VALUES (2,'Cancelled');
-- Create Room Type
INSERT INTO RoomType VALUES (1,'Kingsize');
INSERT INTO RoomType VALUES (2,'Queen');
INSERT INTO RoomType VALUES (3,'Double');
清单7:插入初始数据。
为了验证我在数据库中构建的数据完整性规则，我将运行清单8中的插入语句。
USE RoomReservation;
GO
-- Violates NOT NULL constraint
INSERT INTO Reservation VALUES(1,'2011-8-1 5:00 PM'
,'2011-8-2 9:00 AM'
, 150.99,NULL,1,1);
-- Violates Primary Key Constraint
INSERT INTO RoomType VALUES (3,'Suite');
-- Violates Foreign Key Constraint
INSERT INTO CustomerPaymentType VALUES (1,2,'Will need an internet connection');
清单8:用插入语句测试各种约束。
这些插入语句中的每一个都应该失败，因为它们违反了在RoomReservation数据库中构建的数据完整性规则。第一个插入语句违反ReservationStatusID列的非空验证检查。
第二个插入语句违背了放在RoomType表上的主键约束。这个插入语句试图将3的值插入到RoomTypeID列中。问题是在RoomType表中已经有了一个记录，房间类型的值为3。
最后一个插入语句违反了CustomerPaymentType表的外键约束。在该特定插入语句中，客户表中没有客户id，值为2。
要正确插入这些记录，需要整理插入的数据值。一旦数据被整理清楚，我就可以将这些新数据插入到适当的表中。清单9包含清理过的插入语句，它将检查所有传递数据的完整性，并成功地插入到RoomReservation数据库中的适当表的中：
USE RoomReservation;
GO
-- Violates NOT NULL constraint
INSERT INTO Reservation VALUES(1,'2011-8-1 5:00 PM'
,'2011-8-2 9:00 AM'
, 150.99,1,1,1);
-- Violates Primary Key Constraint
INSERT INTO RoomType VALUES (4,'Suite');
-- Violates Foreign Key Constraint
INSERT INTO CustomerPaymentType VALUES (1,1,'Will need an internet connection');
清单9:附加的约束测试。
关系数据库设计
我的预示演示了如何使用关系模型并使用它来实现一个SQL Server数据库。通过使用非空、主键和外键控制，我将数据完整性规则构建到数据库设计中。这允许我在底层数据库定义中执行这些规则，而不是在业务处理层中编写代码来验证这些数据规则。通过这样做，我允许SQL Server数据库引擎为我执行这些数据完整性检查。
通过理解并创建有关关系数据库模型的数据库设计，您将构建一个强大的、高效的数据库实现方式，您可以在其中建立数据完整性检查权。
