Implementing a Relational Model in SQL Server

转载翻译自http://www.sqlservercentral.com/articles/Stairway+Series/75775/

在SQL Server中实现关系模型。

 

这一部分不是用来教你关系数据建模或数据库设计的，而是教你如何从一个关系模型创建一个SQL Server数据库。但是，在我为您提供创建SQL Server数据库的代码块之前，我们首先需要探索将要实现的关系数据模型。我的简单模型将包含几个实体(数据表)，其中有主键定义和不同实体之间的一些关系(外键约束)。

我的简单关系模型将是一个简单的酒店预订系统。这个预订系统需要跟踪客户预订信息。图1说明了我将使用T-SQL实现的简单关系模型:

 

图1:一个简单的关系数据库模型，包含6个表。

 

通过回顾这个模型，您可以看到它包含了许多实体(以方框表示)来跟踪预订相关信息。每个实体由许多属性(列)组成，其中一个或多个属性被标识为主键(粗体和下划线的名称)。同样表示的是实体之间的一些关系(以箭头表示)，以显示不同的实体之间是如何相互关联的。我将使用实体、属性、主键和关系的模型，然后开发一个物理SQL Server数据库，该数据库表示此关系模型的设计。

要从这个模型构建一个物理数据库，我们需要在这个模型中定义SQL Server中定义的不同对象。对于图1中的每个实体或框，我将在SQL Server中创建一个表。对于每个实体的每个属性，我将在关联的表中创建一个列。对于每个主键，我将创建一个唯一的聚集索引(注意，也可以使用唯一的非聚集索引创建主键)。有关索引的更多信息，请参见http://www.sqlservercentral.com/stairway/72399/)。最后，对于每个关系，我将创建一个外键约束。

为了开始构建我的数据库，我首先需要创建一个SQL Server数据库来保存我计划创建的所有新数据库对象。我的数据库将被称为RoomReservation。我将使用以下的T-SQL代码创建我的数据库:

CREATE DATABASE RoomReservation;

要从我的模型中开始构建我的房间预订数据库对象，我将创建表对象。要在SQL Server中创建表，我需要使用create table语句。使用CREATE TABLE语句，我将能够定义每个表和每个表中的所有列。下面是创建SQL Server表的简单语法:

     CREATE TABLE <table_name>

 Where:

<table_name> = Name of table
<column_definition> = column_name data_type,[NULL |NOT NULL]

对于CREATE TABLE语句的完整语法，请参阅联机的SQL Server书籍。

我创建的第一个表将是Customer表，它是使用清单1中的代码创建的。

USE RoomReservation;

GO

CREATE TABLE dbo.Customer (

         CustomerIdINT NOT NULL,

         FirstNameNVARCHAR(50) NOT NULL,

         LastNameNVARCHAR(50) NOT NULL,

         Address1NVARCHAR(100) NOT NULL,

         Address2NVARCHAR(100) NULL,

         PhoneNumberNVARCHAR(22) NOT NULL,

         EmailAddressVARCHAR(100) NULL,

         CityVARCHAR(100) NOT NULL,

         StateProvinceNVARCHAR(100) NOT NULL,

         PostalCodeNVARCHAR(100) NOT NULL);

 

清单1:创建Customer表。

 

在此代码中，当我创建Customer表时，我创建了所需的所有列，但我还指定了在插入或更新记录时，该列是否需要一个值。我通过在某些列上指定NOT NULL实现了这一点，而其他列则指定为NULL。

如果一个列被定义为NOT NULL，那意味着您不能创建一个记录，除非您用一个实际值填充这个列。然而，使用NULL规范定义一个列意味着您可以创建一个行，而无需为这个列指定一个值，或者另一种方法是该列允许空值。在上面的CREATE TABLE语句中，我允许列Address2和EmailAddress支持null，而所有其他列都需要在创建行时提供一个值。

这个CREATE TABLE语句并没有完全定义我的Customer表，因为它在上面的关系数据库模型中表示。我仍然需要在列CustomerID上创建一个主键约束。这个主键约束将确保该表中没有两个记录具有相同的CustomerID值。创建主键的代码如清单2所示。

USE RoomReservation;

GO

ALTER TABLE dbo.Customer ADD CONSTRAINT

    PK_Customer PRIMARY KEY CLUSTERED (CustomerId);

 在Transact-SQL语言中，通常有不止一种方法可以做同样的事情。或者，我可以通过运行清单3中的CREATE TABLE语句一次创建我的客户表和主键。
清单3：使用主键创建客户表的另一种方法
在这一点上，我已经向您展示了如何使用定义的主键创建表。留给你看的唯一的东西是如何创建一个外键约束。但是在我能做到这些之前，让我先为您提供脚本，以便在上面的关系数据库模型中创建表格和主键的其余部分。你可以在清单中找到它

USE RoomReservation;

GO

ALTER TABLE dbo.Customer ADD CONSTRAINT

    PK_Customer PRIMARY KEY CLUSTERED (CustomerId);

 

 

 

 

 

 

 

 

USE RoomReservation;

GO

 

CREATE TABLE dbo.Reservation (

         ReservationIdINT NOT NULL,

         ArrivalDateDATETIME NOT NULL,

         DepartureDateDATETIME NOT NULL,

         DailyRateSMALLMONEY NOT NULL,

         ReservationStatusIDINT NOT NULL,

         CustomerIdINT NOT NULL,

         RoomTypeIDINT NOT NULL);

        

ALTER TABLE dbo.Reservation ADD CONSTRAINT

   PK_Reservation PRIMARY KEY CLUSTERED (ReservationId);

        

CREATE TABLE dbo.RoomType (

         RoomTypeIdINT NOT NULL,

         RoomDescNVARCHAR(1000) NOT NULL);

        

ALTER TABLE dbo.RoomType ADD CONSTRAINT

   PK_RoomType PRIMARY KEY CLUSTERED (RoomTypeId);

 

CREATE TABLE dbo.ReservationStatus (

         ReservationStatusIdINT NOT NULL,

         ReservationStatusDescNVARCHAR(50) NOT NULL);

 

ALTER TABLE dbo.ReservationStatus ADDCONSTRAINT

   PK_ReservationStatus PRIMARY KEY CLUSTERED (ReservationStatusId);

        

CREATE TABLE dbo.PaymentType (

         PaymentTypeIdINT NOT NULL,

         PaymentTypeDescNVARCHAR(50) NOT NULL);

 

ALTER TABLE dbo.PaymentType ADD CONSTRAINT

   PK_PaymentType PRIMARY KEY CLUSTERED (PaymentTypeId);

 

CREATE TABLE dbo.CustomerPaymentType (

         PaymentTypeIdINT NOT NULL,

         CustomerIdINT NOT NULL,

         PaymentNotesNVARCHAR(2000) NULL);

 

ALTER TABLE dbo.CustomerPaymentType ADDCONSTRAINT

   PK_CustomerPaymentType PRIMARY KEY CLUSTERED (PaymentTypeId,CustomerId);

清单4：创建附加表和主键约束

外键约束强制两个彼此相关的表之间的参照完整性。外键约束定义的表是“引用表”，并且在表中插入或更新行时，必须在另一个表（称为“引用”表）中具有相关记录。 。在图1中的我的关系模型中，这些外键关系由箭头表示。 外键约束仅在关系中的其中一个表上定义。在我的图中，外键约束将被定义在那些附有箭头尾部（非尖端）的表上。

USE RoomReservation;

GO

ALTER TABLE dbo.Reservation

ADD CONSTRAINTFK_Reservation_CustomerPaymentType FOREIGN KEY (CustomerId)

    REFERENCESdbo.Customer (CustomerID);

要在我的关系模型中定义这些外键约束，我需要修改每个引用表来添加约束。清单5是我可以用来在保留表上创外键约束的T-SQL代码。此约束可确保记录不会在保留表中插入或更新，除非在客户表中找到匹配的记录（基于客户Id）。 

USERoomReservation;

GO

ALTER TABLEdbo.Reservation

ADD CONSTRAINTFK_Reservation_RoomType FOREIGN KEY (RoomTypeId)

    REFERENCES dbo.RoomType (RoomTypeId);

   

ALTER TABLEdbo.Reservation

ADD CONSTRAINTFK_Reservation_ReservationStatus FOREIGN KEY (ReservationStatusId)

    REFERENCES dbo.ReservationStatus(ReservationStatusId);   

   

ALTER TABLEdbo.CustomerPaymentType

ADD CONSTRAINTFK_CustomerPaymentType_PaymentType FOREIGN KEY (PaymentTypeId)

    REFERENCES dbo.PaymentType(PaymentTypeId); 

   

ALTER TABLEdbo.CustomerPaymentType

ADD CONSTRAINTFK_CustomerPaymentType_Customer FOREIGN KEY (CustomerId)

    REFERENCES dbo.Customer (CustomerId);