SQL数据库实训示例

附录：

SQL数据库实训示例--------客房管理系统设计

●本系统要求实现以下主要功能：

1．数据录入功能

在本系统中提供客人信息登记功能。可以录入客人的姓名、性别、年龄、身份证号码、家庭住址、工作单位、来自地的地名、入住时间、预计入住天数、客房类别、客房号、离店时间以及缴纳押金金额等信息。在客人退房时，系统根据输入的离店时间及客房单价自动计算客人住宿费金额。

2．数据查询功能

系统需要提供以下查询功能：

(1)查某类客房的入住情况及空房情况，显示所有该类客房空房数目和客房号。

(2)根据客人姓名、来自地的地名、工作单位或家庭住址等信息查询客人信息；根据客房号查询入住客人的信息。

(3)查询某个客人住宿费用情况，显示客人缴纳押金金额、实际入住天数、客房价格、实际住宿费、住宿费差额及余额等信息。

(4)查询所有入住时间达到或超过预计入住天数的客人。

3．数据统计功能

(1)统计一段时间内各类客房的入住情况。

(2)统计全年各月份的客房收入。

(3)统计一段时间内各类客房的入住率。

●系统的实现

1．数据库概念设计

数据库的概念设计就是画出E-R图。

分析前面的系统功能要求，需要一个表来存储和管理客人信息，使系统能够接受客人入住时输入的各项数据，以实现数据录入、查询或统计客人信息等功能。客人是本系统的第一个实体。

为了能实现查询和统计客房入住情况等功能，必须有一个表来存储和管理所有客房的信息。客房是本系统的第二个实体。

客房价格是以客房的类型来制定的。需要建立一个表来记录各种客房类型的信息。它主要为各种查询和统计功能提供客房价格数据。客房类型是第3个实体。

据此可以绘出客房管理系统数据库的E-R图如下：

2.数据库逻辑设计

将数据库的概念模型转换为关系模型：

实体转换成的关系模式有：

客人（序号，姓名，性别，年龄，身份证号码，家庭住址，工作单位，来自地的地名，入住时间，预计入住天数，离店时间，押金金额，住宿费）客房（客房号，客房状态，客房类型编号）

客房类型（客房类型编号，客房类型名称，该类型客房价格）

由联系转换成的关系模式有：

登记（序号，客房号）

因为客房与客人是一比多联系，所以可以取消登记这个实体，在客人实体中添加客房号属性。

各关系模式为：

客人（序号，姓名，性别，年龄，身份证号码，家庭住址，工作单位，来

自地的地名，入住时间，预计入住天数，离店时间，押金金额，住宿费，客房号）客房（客房号，客房状态，客房类型编号,序号）

客房类型（客房类型编号，客房类型名称，该类型客房价格）

带下划线的属性为各关系模式的主码，字体为粗体的属性为各关系模式的外码。

3．数据库与数据表设计

数据库设计是根据系统功能的要求和数据规模规划数据库服务器选型、数据表结构定义、分配数据库服务器端的功能实现以及创建数据库对象。

在SQL数据库中需要建立3个数据表：客人信息数据表、客房信息数据表和客房类型数据表。

（1）数据库与数据表设计

①客人信息数据表定义

根据系统功能要求，客人信息表需要能接受客人登记入住和离店时输入的所有信息，还必须包括客人最终的住宿费金额，因为住宿费金额是统计客房收入的基本数据。

客人信息表的结构定义如表3—1所示。

表1 客人信息表的结构

其中，cId(序号)是表的主键，惟一标识一个入住的客人。设计时定义它为标识列，系统自动地产生连续的永不重复的序号。

rNum(客房号)在客人信息表中是外键，它是客房信息表的主键，惟一标识一个房间。

通过它，系统将引用到客房类型、客房单价等信息。

客人信息表取名为tbClient

②客房信息数据表定义

客房信息表中应该记录每一个客房的信息和状态，系统查询这些信息并决定客人能否入住。每个客房的类型决定了客房的价格，可供客人入住时选择房间及离店时结算住宿费用。

客房数据表的定义如表2所示。

表2客房信息表的结构

其中，rNum(客房号)是该表的主键，惟一标识一个客房房间。它将作为客人信息表的外键，保证客人信息表的参照完整性。

rStats表示客房的状态，设定其值为“N”时，表示客房没有入住客人；值为“F”时，表示客房已经有客人入住；值为“P”时，表示客房被预定。

rType表示客房的类型，它是该表的一个外键，来自客房类型数据表。

cId将记录入住客人的序号，在客房信息表中增加这一列时，虽然增加了数据冗余，但可以在查询房间中入住客人信息时，提高系统的性能。因为客房信息表的记录数相对固定，相比之下，增加这个冗余的列对于整个系统来说是有利的。

客房信息表取名为tblRoom。

③客房类型数据表定义

客房类型主要描述客房的服务标准和收费价格，这些信息如果包含进每个客房的记录中，将会使客房信息表产生较大的数据冗余，当某种类型的客房价格变动时，用户就不得不对客房信息表中的记录逐一进行修改。

客房类型数据表的定义如表3所示。

其中，rType表示客房类型的编号，作为该表的主键，惟一标识某一类客房。它将作为客房信息表的外键，保证客房信息表数据的参照完整性。

客房类型表取名为tblRoomType。

表3 客房类型数据表

(4)根据上面三个数据表的设计，可执行下面的步骤创建数据表

1)打开企业管理器，在本地服务器上创建新的数据库KFGL。

2)创建表tblClient、tblRoom和tblRoomType

注意：一个表用T-SQL语句建立(建表结构用CREATE TABLE语句,输入数据表记录用INSERT INTO TABLE语句),两个表用用企业管理器建立。

(2)数据完整性设计

设计好表的结构后，需要根据实际应用和操作规则为表制定一系列约束和规则，从而达到保证数据完整性原则的目的。

①主键约束、非空值约束

在三个表的设计中已经规定了每个表的主键列、非空列，这些规定都是在实际应用环境中所必需的。比如tblRoom表中定义了房间号rNum为主键，则在表tblRoom中rNum必须是惟一的——一个饭店不可能出现两个房间号码相同的客房；客房类型rType和客房状态rStats不能为空值，因为rType是辨别客人入住客房种类、住宿费用结算的依据，所以不能为空值；rStats是辨别客人能否入住的依据，所以也不能为空值。

②CHECK约束

对于tblClient表，应该建立一个检查约束，即所有客人的离店时间都不可能小于入住时间。

按下列步骤创建这个约束：

1)打开企业管理器，展开服务器，展开“数据库”，展开“KFGL”数据库，单击“表”。

2)用鼠标右键单击“tblClient”表，选择“设计表”，系统将弹出“设计表”对话窗口。

3)用鼠标右键单击此窗口的上方窗格，单击“CHECK约束”，单击“新建”按钮，在“约束表达式”文本框中输入表达式：([cOutTime)＝[cInTime])。

4)选择“对INSERT和UPDATE操作强制约束”复选框，单击“关闭”按钮，完成CHECK约束创建操作。

③使用缺省值

可以将三个数据表中所有货币类型的列都定义为缺省值，特别是指定了“非空”约束的列。tblClient表的预住天数cDay的默认值可定义为1，而客人入住时间cInTime的缺省值应该就是添加客人记录的时间(除非是客房预定，在本系统中暂不考虑客房预定)，所以可以设定缺省值为“(GETDATE())”。

④惟一约束

除了每个表的主键需定义为惟一性外，对于tblRoomType的客房类型名rName，也应该定义为惟一的名称。因为在系统功能需求中，要求按客房类型对数据进行统计，如果在统计结果中只显示客房类型编号，用户就必须记忆那种类型是什么编号，这样很不直观。所以应按照相关的SQL Sener数据库教材中建立惟一约束的方法进行创建。

⑤外键约束

在数据表设计中已经讨论了各个表的外键，这里以tblClient为例说明创建步骤，tblRoom和tblRoomType表可以按照相同的步骤进行操作。

1)打开企业管理器，展开服务器，展开“数据库”，展开“KFGL”数据库，单击“表”。