SQL数据库实训示例.docx
- 文档编号:8945538
- 上传时间:2023-05-16
- 格式:DOCX
- 页数:21
- 大小:346.76KB
SQL数据库实训示例.docx
《SQL数据库实训示例.docx》由会员分享,可在线阅读,更多相关《SQL数据库实训示例.docx(21页珍藏版)》请在冰点文库上搜索。
SQL数据库实训示例
附录:
SQL数据库实训示例--------客房管理系统设计
●本系统要求实现以下主要功能:
1.数据录入功能
在本系统中提供客人信息登记功能。
可以录入客人的姓名、性别、年龄、身份证号码、家庭住址、工作单位、来自地的地名、入住时间、预计入住天数、客房类别、客房号、离店时间以及缴纳押金金额等信息。
在客人退房时,系统根据输入的离店时间及客房单价自动计算客人住宿费金额。
2.数据查询功能
系统需要提供以下查询功能:
(1)查某类客房的入住情况及空房情况,显示所有该类客房空房数目和客房号。
(2)根据客人姓名、来自地的地名、工作单位或家庭住址等信息查询客人信息;根据客房号查询入住客人的信息。
(3)查询某个客人住宿费用情况,显示客人缴纳押金金额、实际入住天数、客房价格、实际住宿费、住宿费差额及余额等信息。
(4)查询所有入住时间达到或超过预计入住天数的客人。
3.数据统计功能
(1)统计一段时间内各类客房的入住情况。
(2)统计全年各月份的客房收入。
(3)统计一段时间内各类客房的入住率。
●系统的实现
1.数据库概念设计
数据库的概念设计就是画出E-R图。
分析前面的系统功能要求,需要一个表来存储和管理客人信息,使系统能够接受客人入住时输入的各项数据,以实现数据录入、查询或统计客人信息等功能。
客人是本系统的第一个实体。
为了能实现查询和统计客房入住情况等功能,必须有一个表来存储和管理所有客房的信息。
客房是本系统的第二个实体。
客房价格是以客房的类型来制定的。
需要建立一个表来记录各种客房类型的信息。
它主要为各种查询和统计功能提供客房价格数据。
客房类型是第3个实体。
据此可以绘出客房管理系统数据库的E-R图如下:
m
1
m11
实体属性列表如下:
实体
属性
客人
序号
姓名
性别
年龄
身份证号码
家庭住址
工作单位
来自地的地名
入住时间
预计入住天数
离店时间
押金金额
住宿费
客房
客房号
客房状态
客房类型编号
客房类型
客房类型编号
客房类型名称
该类型客房价格
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语句建立(建表结构用CREATETABLE语句,输入数据表记录用INSERTINTOTABLE语句),两个表用用企业管理器建立。
(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,也应该定义为惟一的名称。
因为在系统功能需求中,要求按客房类型对数据进行统计,如果在统计结果中只显示客房类型编号,用户就必须记忆那种类型是什么编号,这样很不直观。
所以应按照相关的SQLSener数据库教材中建立惟一约束的方法进行创建。
⑤外键约束
在数据表设计中已经讨论了各个表的外键,这里以tblClient为例说明创建步骤,tblRoom和tblRoomType表可以按照相同的步骤进行操作。
1)打开企业管理器,展开服务器,展开“数据库”,展开“KFGL”数据库,单击“表”。
2)用鼠标右键单击“tblClient”表,选择“设计表”,系统将弹出“设计表”对话窗口。
3)用鼠标右键单击此窗口的上方窗格,单击“关系”,在“主键表”下拉框中选择“tblRoom”,在“外键表”中选择“tblClient”。
4)在“主键表”和“外键表”下方的窗格中部选择列名“rNum”,表明出tblRoom表中的主键“rNum”就是tblClient表中的外键。
5)选择“对INSERT和UPDATE操作强制约束”复选框,表明以后对tblClient表中的rNum所有的添加和更新操作都会检查在tblRoom表中是否存在与此相应的rNum。
单击“关闭”按钮,完成创建外键操作。
⑥规则
为了检查tblClient表中输入的身份证号码CPNUM是有效位数、(我国身份证号码有旧的15位数字和18位数字两种),可以创建一个规则绑定到该列;在数据操作时进行检查。
规则的定义语句为:
(LEN((@CardNum)=15)OR(LEN(@CardNum)=18)
创建和绑定的方法及步骤请参见教材中的相关内容。
⑦标识列
在设计客人信息表tblClient时,我们把客人序号cId定义为标识列,使其在添加记录时自动产生序号,并且每个序号惟一地标识一次客人入住信息。
完成数据完整性设计后,录入模拟数据。
4.关系图的建立
关系是表之间的链接,用一个表中的外健引用另一个表中的主健。
关系线的终结点显示一个主键符号一表示主键到外键的关系,或者显示一个无穷符号以表示一对多关系的外键端。
使用SQLServer7/2000的企业管理器创建关系图,步骤如下:
(1)启动SQLServer企业管理器,并打开“创建数据库关系图向导”窗口。
(2)根据向导页一步步开始关系图的创建。
例为KFGL数据库中的客人信息数据(tbClient)表、客房信息数据(tbltblRoom)表和客房类型数据(tbltblRoomType)表创建关系图。
(1)从“开始”菜单中的SQLServer程序组中启动SQLServer企业管理器,打开“SQLServerEnterpriseManager”窗口。
(2)在左边的目录树结构中选择要创建关系图的数据库文件夹,如“kfgl”文件夹,并在右边的对象窗口中选择并打开其中的“关系图”对象;
图1打开“SQLServerEnterpriseManager”窗口
(3)从“操作”菜单中选择“新建数据库关系图”命令,打开SQLServer的“创建数据库关系图向导”窗口,如图2所示。
图2“创建数据库关系图向导”窗口
(4)单击“下一步”按钮,进入“选择要添加的表”页面,如图3所示。
图3“选择要添加的表”页面
先在左边的“可用的表”列表框中选择要添加的表,如果要系统自动添加选中表的相关表,可以选中列表框下的“自动添加相关的表”复选框,然后单击“添加”按钮。
这时,在右边的“要添加到关系图中的表”列表框中就会出现选中的表。
(5)单击“下一步”按钮,进入“正在完成数据库关系图向导”页面,如图4所示。
图4“正在完成数据库关系图向导”页面
(6)单击“完成”按钮,完成数据库关系图的添加,此时在SQLServer企业管理器中就出现了刚才所创建的关系图,如图5所示。
图5客房管理系统的关系图
(7)点击快捷工具栏中的“
”保存图标,将刚创建好的关系图保存起来。
5.简单查询
简单查询就是SELECT-----FROM------WHERE查询
可以参照教材,对于客房管理系统进行简单查询,完成系统的一些功能。
6.复杂查询
复杂查询包括连接查询、嵌套查询、分组查询、有关组函数的查询等。
可参照教材,对于客房管理系统进行复杂查询查询,完成系统的一些功能。
7.视图、触发器和存储过程设计
需要确定哪些业务处理和数据处理的功能使用MSSQLServer来实现,哪些数据处理的功能由应用程序实现。
下面将讨论系统需要设计并创建的视图、触发器和存储过程。
(1)客人选择客房处理
客人来到饭店入住前要做的第一件事是选择合适的客房,客人将告知饭店服务员自己需要的客房类型,服务员在系统中选择指定的客房类型后系统将显示所有空余的该类型客房,并显示该类型客房价格供客人参考选择。
这个处理过程可以通过一个存储过程来实现。
在这个存储过程中,需要的输入参数是客房类型,输出的结果集是所有这种客房类型的空房记录和价格.定义这个存储过程名为SelRoom,定义输入的参数名为RoomType,定义输出的价格参数名为RoomPrice。
根据存储过程的创建语法,编写SelRoom的实现代码如下:
在上面的代码中包含了两个SELECT语句,第一个SELECT语句从tblRoom表中选择了所有客房类型为输入参数指定类型并且客房状态为“空”(N)的客房号,并将所有记录作为结果集返回;第二个SELECT语句从tblRoomType中取得了指定客房类型的价格,作为输出参数RoomType返回。
(2)客人入住登记处理
客人入住登记操作完成后,入住的客房状态应该及时做相应的改变,并记录客人的序号供以后查询。
这一功能可以使用触发器来自动进行;因为进行客人入住登记操作是在客人信息表tblClient中添加一条新的记录,所以可以为tblClient设计一个Insert触发器,当tblClient执行Insert操作后自动更改出tblRoom相应客房记录的数据。
定义这个触发器名称为client_insert。
其实现代码如下:
(3)客人离店退房处理
客人在离店退房时,服务员输入客人的退房时间,然后要计算出客人的住宿费用,以便给客人结账。
同时,系统应该将客人所退客房的状态更改为“空”,以便于再次接待下一位客人入住。
这一功能也可以通过一个触发器来实现。
为出tblClient表设计一个名为client_update的UPDATE触发器,当系统对tblC1ient的cOutTime(退房时间)进行UPDATE操作后,将会自动触发它。
该触发器将自动取得当前所退客房的单价,并根据客人入住的天数计算出客人的住宿费用,把费用值写入cCost列,最后触发器tblRoom相应客房记录的rStats和cId更新,即将客房状态置为空房,并清除对应于该客人的入住标识信息。
下面是client_update触发器的定义代码;
在上面的代码中首先定义了一个变量@price,然后退过一个右联合直接得到当前客人所住客房的单价。
在第二段语句中,使用SQL函数DATEDIFF求得客人入住时间和退房时间之差,求出入住天数,乘以@price变量就得到了客人最终的住宿费用。
值得注意的是:
UPDATE触发器可以使用inserted表和deleted表,这里必须使用inserted表,它的coumme才是最新更改的退房时间,如果使用deleted表,将得不到正确的结果。
最后,触发器对tblRoom表中cId为当前客人cId的记录的rStats列和cId列进行了更新。
(4)客人信息查询处理
在实际应用中,经常会有这样的要求:
服务员需要通过客人的部分资料查询客人的全部信息以及客人住在哪一个客房。
由于需要通过输入查询条件,才能得到结果集,所以可以设计一个带输入参数的存储过程来实现。
定义一个名为spClientInfo的存储过程,该存储过程以客人姓名(@ClientName)、单位(@ClientWork)、家庭住址(@ClientAdd),及来自地的地名(@ClientFrom)等作为输入参数,存储过程返回查询到的结果集。
spClientInfo的定义代码如下:
在上面的代码中,向spClientInfo传入4个参数,依次是:
客人姓名、客人工作单位、客人家庭地址和客人来自地的地名。
在WHERE子句中使用LIKE,并在参数前、后添加“%”,使得该存储过程具有模糊查询的功能。
比如,@ClientName参数中只输入客人的姓,就可以查询到所有该姓氏的客人信息。
4个参数可以同时给出,也可以只输入一个参数,但是参数的位置顺序不能改变。
(5)查询客人住宿费用的处理
在本系统的功能需求中要求能够查询客人住宿费用的详细情况,包括客人入住时交付的押金金额、客人住宿天数、客房价格、客人住宿费金额、住宿费与押金差额等信息。
实现该功能可以通过一个存储过程,以客人序号为输入参数,输出上面要求的各种信息数值。
设计一个名为spClientCost的存储过程,定义代码如下:
在上面代码中,首先定义了4个变量,分别是:
@Clientprice、@ClientDays、@ClientCost、@ClientBalance,它们分别表示:
客人入住房间的单价、客人住宿的时间、客人的住宿费用和客人所缴纳押金与住宿费用的差额。
第一个SELECT语句起赋值作用,分别把各个表达式的结果赋值给各个变量。
第二个SELECT语句是一个选择,将tblClient表的cDeposit以及前面求得的各个变量值作为存储过程的结果集返回。
(6)查询住宿时间到期的客人
客房管理服务员通常根据客人入住时登记的预住天数收取相应押金,当客人住宿时间达到预住天数时就应该通知客人,以便客人补交押金或退房。
系统为管理服务员提供这样的查询功能,可以显示出所有住宿时间达到预住日期的客人信息。
完成这个功能只需要在表中选择入住天数大于等于预住天数的记录,而不需要输入参数,所以,可以使用一个视图来实现这一查询功能。
定义视图的名称为vClient_Day,定义代码如下:
代码中使用DATEDIFF函数求得cInTime与当前日期之间的天数,即客人实际住宿天数。
所有住宿天数大于或等于预住天数cDay,并且离店时间cOuTime为NULL的客人记录都将成为结果集中的记录。
(7)客房销售统计
作为一个简单的客房管理系统,应该提供给管理者一定的统计数据。
系统中最基本的统计数据就是各种类型客房在一定时何段内的销售收入情况。
设计一个存储过程,以管理人员输入的统计起始日期和终止日期作为输入多数,在这段时期内所有类型客房的销售收入、销售次数(客人入住次数)为结果集。
这个存储过程名为spRoomSale,两个输入参数分别为@StartDate和@EndDate。
设计代码如下:
代码中使用了两个SELECT子查询,分别求得起始日期与终止日期之间、客房类型为当前客房类型的所有住宿费用总和(客房销售收入sale)及客房销售次数salenumo然后这两个值作为新的列与tblRoomType的rType、rName列一起作为结果集。
注意BETWEEN的用法。
(8)统计某年份每月的客房销售数据
饭店管理人员常常需要比较一年中各个月份的客房销售收入,并把它制作成表格。
实现这一功能可以使用一个存储过程,以输入一个年度作为参数,产生该年度12个月份的销售收入统计。
定义该存储过程名为spMonhSum,输入参数为@ThisYear,实现代码如下:
注意上面的代码中如何使用GROUPBY子句来实现统计功能。
代码中还使用了日期函数DATEPART和求和函数SUM。
8.数据库的用户与权限管理
前台服务员可进行客人的录入,查询。
经理可进行统计。
9.数据库的备份
对于KFGL数据库进行完全备份。
10.数据的导入与导出
利用SQLServer数据导入与导出的数据转换服务可以实现不同数据源间的数据传输和数据格式的转换。
实现数据的导入与导出有3种方法:
方法1:
单击企业管理器常用工具栏上的“运行向导”图标;
再单击“所有任务”、“导入数据”或“导出数据”。
方法2:
单击“工具”、“数据转换服务”;
再单击“所有任务”、“导入数据”或“导出数据”。
方法3:
在企业管理器右边显示表的区域上按鼠标右键,
再单击“所有任务”、“导入数据”或“导出数据”。
(1)数据的导出服务
将KFGL数据库中的tblclient表转换成EXCEL工作表tblclient.xls。
1)单击“工具”、“数据转换服务”、“导出数据”,再单击“下一步”按钮,进入如图2—43所示的窗口。
图6选择数据源的导入/导出入口
2)选择数据源,系统默认SQLServer2000数据库,选择SQLServer服务器及数据库。
3)单击“下一步”按钮,出现如图2。
44所示的窗口,分别输入目的数据类型:
MicroSoftExcel97—2000,目的数据的文件名:
tblclient.xls。
图7选择目的地的DTS导入/导出窗口
4)单击“下一步”按钮,再单击“下一步”按钮,出现如图2—45所示的窗口,选择表[kfgl][dbo][tblclient]。
图8选择源表和视图的DTS导入/导出窗口
5)单击“下一步”按钮,再单击“下一步”,及“完成”按钮,即可完成一个SQLServer数据库表向Excel工作表的转换。
6)进入Excell,打开工作表tlb.xls查看该工作表。
.xls
(2)数据的导入服务
按上述类似的步骤将一个Excel工作表导入到KFGL数据库中,实现数据的导入服务。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 数据库 训示