客房标准管理系统标准设计.doc

客房管理系统设计
在当今经济和商务往来交往日益频繁情况下，宾馆服务行业正面临客流量骤增压力。越来越多宾馆饭店全部认识到传统管理方法已经不能适合当今社会需要，必需借助优异计算机信息技术对宾馆服务进行管理。“客房管理系统”能够说是整个宾馆计算机信息管理中心子系统，因为宾馆最关键功效就是为旅客提供客房。设计客房管理这么一个系统，能够包含到大多数SQL Server数据库关键数据库对象、关键功效和特征，比如：视图、触发器和存放过程等。由此，经过这个课程设计能够加深对这些SQL Server数据库知识学习、了解，积累在实际工程应用中利用多种数据库对象经验，使学生掌握使用应用软件开发工具开发数据库管理系统基础方法。
本课程设计关键目标是学习和练习SQL Server数据库实际应用。

1．系统功效设计
1).数据录入功效
在本系统中提供客人信息登记功效。能够录入客人姓名、性别、年纪、身份证号码、入住时间、估计入住天数、客房类别、客房号、离店时间和缴纳押金金额等信息。在客人退房时，系统依据输入离店时间及客房单价自动计算客人住宿费金额。
2).数据查询功效
系统需要提供以下查询功效：
① 查某类客房入住情况及空房情况，显示全部该类客房空房数目和客房号。
② 依据客人姓名信息查询客人信息；依据客房号查询入住客人信息。
③ 查询某个客人住宿费用情况，显示客人缴纳押金金额，实际入住天数、客房价格、实际住宿费、住宿费差额及余额等信息。
④ 查询全部入住时间达成或超出估计入住天数客人。
3).数据统计功效
① 统计一段时间内多种客房入住情况。
② 统计整年各月份客房收入。
③ 统计一段时间内各类客房入住率。
2．数据库设计
1). 数据库和数据表设计
2). 数据完整性设计
① 主键约束、非空值约束
② CHECK约束 离店时间>入住时间
③ 使用缺省值 入住时间、估计入住天数
④ 惟一约束 客房类别
⑤ 外键约束
⑥ 规则 身份证长度 15 OR 18
3). 视图、触发器和存放过程设计
1、客人选择客房处理
客人来到饭店入住前要做第一件事是选择适宜客房,客人将通知饭店服务员自己需要客房类型,服务员在系统中选择指定客房类型后系统将现实全部空余该类型客房,并显示该类型客房价格供客人参考选择。这个处理过程能够经过一个存放过程来实现。
在这个存放过程中，需要输入参数是客房类型,输出结果集是全部这种客房类型空房统计和价格。定义这个存放过程名为SelRoom，定义输入参数名为RoomType,定义输出价格参数名为RoomPrice。依据存放过程创建语法，编写SelRoom实现代码以下：

create procedure selroom
@roomtype char(2),
@roomPrice smallmoney output
as
select 客房号 from 客房信息
where (客房类型编号=@roomtype) and (客房状态='N')
select @roomprice=价格 from 客房类型
where 客房类型编号=@roomtype

在上面代码中包含了两个Select语句，第一个SELECT语句从客房信息表中选择了全部客房类型为输入参数指定类型而且客房状态为“空”（N）客房号，并将全部统计作为结果集返回；第二个SELECT语句从客房类型中取得了制订客房类型价格，作为输出参数Roomprice返回。

2、客人入住登记处理
客人入住登记操作完成后，入住客房状态应该立即做对应改变，并统计客人序号供以后查询。这一功效能够使用触发器来自动进行：因为进行客人入住登记操作是在客人信息表中添加一条新统计，所以能够为客人信息表设计一个INSERT触发器，当客人信息实施INSERT操作后自动更改客房信息对应客房统计数据。
定义这个触发器名称为client_insert 。其实现代码以下：

create trigger client_insert on dbo.客人信息
for insert
as
update 客房信息
set 客房状态='F',客房信息.入住客人序号=B.序号
from 客房信息 AS A,inserted as B
WHERE A.客房号=b.客房号

3、客人离店退房处理
客人在离店退房时，服务员输入客人退房时间，然后计算出客人住宿费用，方便于客人结帐。同时，系统应该将客人所退房状态更改为“空”，方便于再次接待下一位客人入住。这一功效也能够经过一个触发器来实现。为客人信息表设计一个名为client_updateUPDATE触发器，当系统对客人信息离店时间进行UPDATE操作后，将会自动触发它。该触发器将自动取得目前所退客房单价，并依据客人入住天数计算出客人住宿费用，将费用值写入住宿费列，最终触发器将客房信息对应客房统计客房状态和入住序号更新，立即客房状态置为空房，并清除对于该客人入住标识信息。
下面是client_update触发器定义代码：

create trigger client_update on dbo.客人信息
for update
as
if update(离店时间) /*只有对离店时间列进行更改时才处理*/
begin /*求出客人所住客房单价*/
declare @price smallmoney
set @price=(select 价格 from 客房类型 right join 客房信息 on
客房类型.客房类型编号=客房信息.客房类型编号 right join 客人信息 on 客房信息.客房号=客人信息.客房号
where 客人信息.序号 in(select 序号 from inserted))
/*求客人住宿费并更改住宿费列*/
update 客人信息
set 住宿费=@price*datediff(day,b.入住时间,b.离店时间)
from 客人信息 as a,inserted as b
where a.序号=b.序号
/*更新客房信息对应客房状态*/
update 客房信息
set 客房状态='N',入住客人序号=''
from 客房信息 ,inserted
where 客房信息.入住客人序号=inserted.序号
end

在上面代码中，首先定义了一个变量@price，然后经过一个右联合直接得到目前客人所住客房单价。在第二段语句中，使用SQL函数DATEDIFF求得客人入住时间和退房时间之差，求出入住天数，乘以@price变量就得到了客人最终住宿费用。值得注意是：UPDATE触发器能够使用inserted表和deleted表，这里必需使用inserted表，它离店时间才识最新更改离店时间，假如使用deleted表，将得不到正确结果。
最终，触发器对客房信息表中入住序号为目前客人入住序号统计客房状态列和入住序号列进行了更新。

4、客人信息查询处理
在实际应用中，常常会有这么要求：服务员需要经过客人部分资料查询客人全部信息和客人住在哪一个客房。因为需要经过输入查询条，才能得到结果集，所以能够设计一个带输入参数存放过程来实现。定义一个名为spClientInfo存放过程，该存放过程以客人姓名（@ClientName）作为输入参数，存放过程返回到查询到结果集。
spClientInfo定义代码以下：

create procedure spClientInfo
@ClientName varchar(8)='%'
as
select * from 客人信息
where 姓名 like @clientname+'%'

在上面代码中，想spClientInfo传入客人姓名参数。在WHERE子句中使用LIKE，并在参数前、后添加“%”，使得该存放过程含有模糊查询功效。比如，@ClientName参数中只输入客人姓，就能够查询到全部该姓氏客人信息。

5、查询客人住宿费用处理
在本系统功效需求中要求能够查询客人住宿费用具体情况，包含客人入住时交付押金金额、客人住宿天数、客房价格、客人住宿费金额、住宿费和押金差额等信息。实现该功效能够经过一个存放过程，以客人序号为输入参数，输出上面要求多种信息数值。设计一个名为spClientCost存放过程，定义代码以下：

create procedure spClientCost
@ClientId char(9)
as
declare @clientPrice smallmoney
declare @clientDays smallint
declare @clientCost smallmoney
declare @clientBalance smallmoney
select
@clientPrice=(
select 价格 from 客房类型 right join 客房信息
on 客房信息.客房类型编号=客房类型.客房类型编号
right join 客人信息
on 客人信息.客房号=客房信息.客房号
where 客人信息.序号=@clientId),
@clientDays=(datediff(day,入住时间,getdate())),
@clientCost=(@clientPrice*@clientDays),
@clientBalance=(押金金额-@clientCost)
from 客人信息 where 序号=@clientId

select 押金金额,@clientPrice as 价格,
@clientDays as 天数,@clientCost as 住宿费,@clientBalance as 余额
from 客人信息
where 序号=@clientId

在上面代码中，首先定义了4个变量，分别是：@clientPrice，@clientDays，@clientCost，@clientBalance，它们分别表示：客人入住房间单价客人住宿时间，客人住宿费用和客人所缴纳押金和住宿费用差额。第一个SELECT语句起赋值作用，分别把各个表示式结果赋值给各个变量。第二个SELECT语句是一个选择，将客人信息表押金金额和前面求得各个变量值作为存放过程结果集返回。

6、查询住宿时间到期客人
客房管理服务员通常依据客人入住时登记预住天数收取对应押金，当客人住宿时间达成预住天数时就应该通知客人，方便客人补交押金或退房。系统为管理服务员提供这么查询功效，能够显示出全部住宿时间达成预住日期客人信息。完成这个功效只需要在表中选择入住天数大于等于预住天数统计，而不需要输入参数，所以，能够使用一个视图来实现这一查询功效。
定义视图名称为vClient_Day，定义代码以下：

create view dbo.vClient_Day
as
select 序号,姓名,客房号,估计入住天数,入住时间,datediff(day,入住时间,getdate()) as 入住天数
from dbo.客人信息
where (离店时间 is null) and (datediff(day,入住时间,getdate())>=估计入住天数)

代码中使用DATEDIFF函数求得入住日期和目前日期之间天数，即客人实际住宿天数。全部住宿天数大于或等于预住天数，而且离店时间为NULL 客人统计全部将成为结果集中统计。

7、客房销售统计
作为一个简单客房管理系统，应该提供给管理者一定统计数据。系统中最基础统计数据就是多种类型客房在一定时间段内销售收入情况。设计一个存放过程，以管理人员输入统计起始日期和终止日期作为输入参数，在这段时期内全部类型客房销售收入、销售次数（客人入住次数）为结果集。这个存放过程名为spRoomSale，两个输入参数分别为@StartDate，@EndDate。设计代码以下：

create procedure spRoomSale
@StartDate smalldatetime,
@EndDate smalldatetime
as
select 客房类型编号,客房类型,(select sum(住宿费) from 客人信息 left join 客房信息
ON 客人信息.客房号=客房信息.客房号 where (客人信息.入住时间 between @startdate and @enddate)
and (客房类型.客房类型编号=客房信息.客房类型编号)) as 销售额,
(select count(客人信息.序号) from 客人信息
left join 客房信息 on 客人信息.客房号=客房信息.客房号
where (客人信息.入住时间 between @startdate and @enddate)
and (客房类型.客房类型编号=客房信息.客房类型编号)) as 消费次数
from 客房类型

代码中使用了两个SELECT子查询，分别求得起始日期和终止日期之间、客房类型为目前客房类型全部住宿费用总和（客房销售收入 sale）及客房销售次数 salenum。然后这两个值作为新列和客房类型客房类型编号、客房类型名称列一起作为结果集。注意BETWEEN使用方法。

8、统计某年份每个月客房销售数据
饭店管理人员常常需要比较十二个月中各个月份客房销售收入，并把它制作成表格。实现这一功效能够使用一个存放过程以输入一个年度作为参数，产生该年度12个月份销售收入统计。定义该存放过程名为spMonthSum，输入参数为@thisYear，实现代码以下：

create procedure spMonthSum
@thisyear char(4)
as
select datepart(mm,入住时间) as 月份,Sum(住宿费) as 营业额
from 客人信息
where datepart(yyyy,入住时间)=@thisyear
group by datepart(mm,入住时间)

注意上面代码中怎样使用GROUP BY子句来实现统计功效。代码中还使用了日期函数DATEPART和求和函数SUM。

⑨ 统计一段时间内各类客房入住率。