数据库应用技术大作业旅馆管理系统数据库设计.docx
《数据库应用技术大作业旅馆管理系统数据库设计.docx》由会员分享，可在线阅读，更多相关《数据库应用技术大作业旅馆管理系统数据库设计.docx（28页珍藏版）》请在冰点文库上搜索。
数据库应用技术大作业旅馆管理系统数据库设计
SQL实践作业：
旅馆管理系统数据库设计与实现
——-—自动化学院自动化专业
小组成员及所完成的工作：
所完成的工作:
数据库整体结构的设计，ER图的绘制和其他工作的审核
系统名称:
旅馆管理系统
一、系统需求
1。
背景:
随着科技和经济的发展，旅游业已经成为一个热门的产业，并且传统的手工已不适应现代酒店管理的需要，及时、准确、全方位的网络化信息管理成为必需。
在酒店的管理及业务日益复杂、要求在不断提高的现状下，利用高科技、现代化的电脑自动化管理系统来处理日益繁重的酒店业务，对于大型的酒店是必须具备的管理方式.
酒店客房管理系统是根据酒店对客房管理的实际情况进行编写的，主要目的是为了方便酒店对客房的实际情况进行集中的查询与管理工作,以提高整个酒店的工作。
酒店客房管理的科学化、系统化、信息化成为各个酒店追求的目标。
因此,而要实现这些功能,就要求各个酒店配备一套客房管理系统，以便在酒店内实施良好的完善的管理且以最快地速度响应客户的需求，及时为他们提供服务,为他们提供一个高效、便捷的居住环境.
2。
旅店需求特点分析:
通过对旅店的调研，对于旅店的需求特点有了基本了解
（1）基本要求：
系统能够进行客户的信息的存储，客户信息的删除，客户信息的更新，客户账单的结算……因此要求系统一定要严密准确，不能
出现插入和删除异常，因此要求数据库系统的设计符合第四范式
（2）高级要求：
旅店面对的经常是一些突发的情况，比如客户的提前退房，客户
要求换房,客户要求其他的附加服务……这些突发事件是我们在我们的系统中需要着重考虑的
3。
旅客需求特点分析：
通过对用户的调研，我们发现用户希望住旅馆的手续能够尽量简单，但是要求旅馆的各种信息准确，出现错误的可能性尽量低
4。
旅店管理系统需求分析：
●旅店的客房特点分析:
（1）通过每个房间唯一的房间号来区别（也就是我们数据库系统中的room_num），房间分成不同的类型（room_type）有‘A'、‘B’、‘C'三种类型，它们对应的价格也会不同，这个由顾客自己选择，体现了我们设计系统的人性化;
（2）房间的状态（room_state）会根据是否有用户入住而不同，如果该房间有人入住的话，那么就将房间的状态标记为busy；
（3）房间同时拥有‘IsBooked’状态,如果房间被预定的话，那么就将这个房间的状态标记为‘Booked’，如果没有预定的话，那么房间的状态为‘nobook’，这样的话方便旅客‘入住’、‘换房'、‘续住'种种操作，避免住进了已经有人预订的房间，同时有些房间即使已经有人预定了，但是在预定旅客之前其他旅客还是可以住进来的，只要他在预定旅客入住之前退房就可以了，样大大提高了客房的利用率。
（4）考虑房间和顾客的关系,房间和旅客关系是一对多的，因为从现实出发，房间分为‘单人房’、‘双人房’等，旅客的收费是按照‘床位’来收费的，但是不同房间的床位的价格是不同的，但是在我们的数据库中默认所有的房间都是单人房，这是我们设计的不足，更理想的是在每种类型中分为‘单人房'、‘双人房’，然后为每个床位设立状态，每位顾客都是按照床位来收房，而不是简单的按照房间来收费，但是在现实生活中旅馆的类型又有很多种，我们所设计的数据库可能只是适合其中的部分类型的旅馆。
●旅客特点分析:
从旅馆的角度来说，最重要的就是准确地记录旅客在住宿期间一切信息，并且要求准确
（1）lodger表用来记录旅客的信息，有lodger_name,id_num,room_num,e
checkin_date,exp_checkout_date，pre_payment
（2）lodger表用来记录当前正在发生‘入住’的旅客的信息，这个表是动态的，
如果旅客退房,与之相关的表示checkout实体集，将用户的信息载入这个表，因为退房的信息是非常关键的,因为旅店的账目的结算是要依靠这个表的,然后他的信息就会从lodger这个表中删除；
旅客可能要求‘换房’，实现这个功能的是change_room联系集
这个联系集联系了lodger和Room两个表，将原来的房间的状态修改为‘Free’,将新入住的房间修改为‘busy’;
旅客可能要求续住，我们当然会满足这个要求，实现这个要求的要求是联系集con_room，如果旅客续住那么在情况允许的情况下，可以继续在原来的房间住宿，并修改exp_checkout_date的信息，如果原来的房间已经有人预定的话，那么建议旅客更改房间,重新登记‘lodger'信息;
●旅店的账目管理特点分析
旅店的账目管理系统是一个比较复杂的系统，要求每天都要进行更新,同时还须具备‘日结’、‘月结’、‘季度结'、‘年结’的功能，与此相关的是checkout，DayAccount，MonthAccount,YearAccount他们最终依赖的表都是checkout表，在我们的系统中checkout表用于记录退房用户的信息，我们并没有设定主键只是设立了外依赖，因为在这个表中不可能有两个完全相同的元组
（lodger_name，room_num，cur_date,（cur_year，cur_month，cur_day）,room_account,back_change）,属性（lodger_name，room_num，cur_
date，）使任意两个元组都不可能相同，因为cur_date是精确到秒的,在实际情况中具有相同lodger_name,在同一时间内从同一个房间退房时不会发生地,之所以不删除这个表的元组，是因为我们想保留这些信息以便日后的查询，比如核对账单,比如发生刑事案件需要旅店配合时，可能需要查询相关的信息……
日结DayAccount的实现也比较复杂，首先我们定制一个作业‘InsetingDayAccount’每天0：
00:
00向DayAccount插入一个用于初始化的元组，然后建立一个checkout与DayAccount之间的触发器,如果向checkout中插入信息，那么就将room_account累加到DayAccount中的day_account中去,这样就能自动统计了每天的盈利状况
MonthAccount和YearAccount的算法和DayAccount类似，同样通过定制作业和建立触发器，实现自动运算，在这就不多介绍了
二、系统概念模型（E—R图）
三、关系模式（逻辑模型）
四、物理设计（表结构）
Table1：
Room（EntitySet）
Attribute:
room_num（房间号），
room_type（房间类型），
room_price（房间价格），
room_state（房间状态）,
IsBooked（预定状态）
PrimaryKey:
room_num
Table2：
Lodger（EntitySet）
Attribute：
lodger_name（客户姓名），
id_num（身份证号）,
room_num（房间号），
checkin_date（入住时间）,
exp_checkout_date（预期退房时间），
pe_payment（客户预付款）
PrimaryKey：
lodger_num，
id_num
ForeignKey:
room_numreferencesRoom
Table3：
Room_Prebook（EntitySet）
Attribute：
reserve_name（预定客户姓名），
room_num（预定房间号）,
exp_checkin_date（预期入住时间），
pre_payment（预付款）
PrimaryKey：
reserve_name
ForeignKey：
room_numreferencesRoom
Table4：
DayAccount（EntitySet）
Attribute：
cur_year（年）,
cur_month（月），
cur_day（日）,
day_account（月结）
PrimaryKey：
cur_year，
cur_month，
cur_day
Table5:
checkout（RelationSet）
Attribute：
lodger_name（客户姓名），
room_num（房间号），
cur_date（日期），
（cur_year，cur_month,cur_day）（年/月/日）,
live_days（住宿天数），
room_account（账单）,
back_change（找零）
ForeignKey：
lodger_namereferencesLodger
room_numreferencesRoom
Table6：
MonthAccount（EntitySet）
Attribute：
cur_year（年）,
cur_month（月），
month_account（日结）
PrimaryKey:
cur_year,
cur_month
Table7:
YearAccount（EntitySet）
Attribute:
cur_year（年），
year_account（年结）
PrimaryKey:
cur_year
Table8：
Remind（EntitySet）
Attribute：
lodger_name（客户姓名）,
room_num（房间号）,
cur_date（日期）
PrimaryKey:
lodger_name，
cur_date
ForeignKey：
lodger_name
五、系统实现
1。
功能模块设计：
（1）客房信息管理集合
a）查询房间的空闲状态
b）查询房间的预定状态
c）查询某种类型房间的价格
d）更新某种类型房间的价格
e）更新房间的空闲状态
f）更新房间的预定状态
（2）旅客信息管理集合
a）查询入住旅客的详细信息
b）查询已预订旅客的信息
c）查询已退房旅客的信息
d）取消预定操作
e）换房操作
f）续住操作
g）退房操作
（3）旅店账户管理集合
a）退房客户结算
b）每日结算
c）每月结算
d）每年结算
（4）服务管理项目集合
a）每日提醒那些旅客已经到退房的时候
b）每日提醒那些预定的客户将在今天住进来
2。
创建数据库的SQL语句
CREATEDATABASEHotel
ON
（
NAME=N’Hotel’,FILENAME=N'E:
\Hotel\Hotel。
mdf’,SIZE=3MB,MAXSIZE=UNLIMITED,
FILEGROWTH=1MB
）
LOGON
（
NAME=N'Hotel_log’，FILENAME=N’E:
\Hotel\Hotel.ldf'，SIZE=1MB，MAXSIZE=2048GB,
FILEGROWTH=10％
）
GO说明：
分别建立了mdf文件和ldf文件,规定mdf文件的初始大小为3MB,增长速率为1MB每次，日志文件初始大小为1MB增长速率为10％.
3．创建表的SQL语句
（1）创建room表,存储客房信息
CREATETableRoom
（
room_numintnotnull,
room_statechar（10）notnull,
room_typechar（10）notnull，
room_pricechar（10）notnull，
primarykey（room_num）
）
（2）创建Lodger表,存储旅客信息
CREATETableLodger
（
lodger_namechar（20）notnull,
id_numbigintnotnull，
room_numintnotnull，
checkin_datedatetimenotnull，
exp_checkout_datedatetimenotnull,
pre_paymentmoney，
primarykey（lodger_name）,
foreignkey（room_num）referencesRoom,
unique（room_num）
）
（3）创建Room_preBook表，用于存储预定客户信息
CREATETABLERoom_Prebook
（
reserve_namechar（10）notnull，
room_numintnotnull,
exp_checkin_datedatetimenotnull,
pre_paymentmoney,
primarykey（reserve_name）,
foreignkey（room_num）referencesRoom
）
（4）创建日结表，存储日结信息
CREATETableDayAccount
（
cur_yearint,
cur_monthint,
cur_dayint，
day_accountmoney，
）
（5）创建退房表,存储退房旅客信息
CREATETableCheckout
（
lodger_namechar（20）notnull，
room_numintnotnull，
cur_datedatetimedefaultGETDATE（）,
cur_yearint，
cur_monthint，
cur_dayint,
live_daysint，
room_accountmoney，
back_changemoney，
foreignkey（room_num）referencesRoom
）
（6）创建MonthAccount表，存储月结信息
createTableMonthAccount
（
cur_yearintnotnull,
cur_monthintnotnull，
Month_accountmoney，
foreignkey（cur_year）referencesDayAccount
）
（7）创建YearAccount表，用于存储年结信息
createTableYearAccount
（
cur_yearintnotnull，
year_accountintnotnull，
foreignkey（cur_year）referencesDayAccount
）
（8）创建Remind表，用于每日提醒
createTableRemind
（
lodger_namechar（20）notnull,
room_numchar（20）notnull,
cur_datedatetimedefaultgetdate（）
foreignkey（lodger_name）referencesLodger）
4。
存储过程展示:
存储过程一:
proc_query_freeroom
代码:
createprocproc_query_freeroom
as
selectroom_num，room_type,room_state
fromroom
whereroom_state=’Free'
功能：
查询当前空闲的房间
存储过程二：
proc_query_bookedroom
代码：
createprocproc_query_bookedroom
as
selectroom_num,room_type，IsBooked
fromroom
whereIsBooked=’booked’
功能:
查询当前已经预定出去的房间
存储过程三:
proc_query_price
代码：
createprocproc_query_price
as
selectdistinctroom_type，room_price
fromroom
功能：
查询不同类型房间的价格
存储过程四：
proc_inc_price
代码：
createprocproc_inc_price
@room_typechar（10），＠new_pricemoney
as
updateroom
setroom_price=@new_price
whereroom_type=＠room_type
功能：
更改某种类型房间的价格
存储过程五：
proc_QueryLodger
代码：
createprocedureproc_QueryLodger
＠checkin_datedatetime
as
selectlodger_name，id_num，exp_checkout_date,pre_payment
fromlodger
wheredatepart（day，checkin_date）=datepart（day,@checkin_date）
功能：
查询指定日期入住的旅客
存储过程六:
proc_querybook
代码：
createprocproc_query_book
as
selectroom_num，reserve_name,exp_checkin_date，pre_payment
fromRoom_prebook
功能：
查询预订的旅客的信息
存储过程七：
proc_bookCancel
代码：
createprocedureproc_bookCancel
@reserve_namechar（20）
as
updateroom
setIsBooked=’nobook'
whereroom_num=（selectroom_num
fromRoom_PreBook
wherereserve_name=＠reserve_name）
deletefromRoom_PreBook
wherereserve_name=@reserve_name
执行:
执行后的room_prebook表
存储过程八:
proc_changeroom
代码：
createprocproc_changeroom
@lodger_namechar（10），
＠source_room_numint，
＠target_room_numint
as
if＠lodger_namein（selectlodger_namefromlodger）
and＠target_room_numin（selectroom_numfromroomwhereroom_state='Free'）
begin
updateroom
setroom_state=’Free’
whereroom_num=@source_room_num
updateroom
setroom_state='Busy’
whereroom_num=＠target_room_num
updatelodger
setroom_num=＠target_room_num
wherelodger_name=@lodger_name
end
存储过程九：
proc_cont_lodge
代码：
createprocproc_cont_lodge
＠lodger_namechar（20），
＠exp_checkout_datedatetime
As
updateLodger
setexp_checkout_date=＠exp_checkout_date
wherelodger_name=@lodger_name
updateroom
setroom_state='Busy'
whereroom_num=（selectroom_num
fromlodger
wherelodger_name=＠lodger_name
存储过程十：
proc_checkout_lodger
代码:
createprocedureproc_checkout_lodger
＠namechar（20）
as
declare@room_numint
insertcheckout（lodger_name,room_num,cur_date，cur_year，cur_month，cur_day,live_days,room_account，back_change）
selectl。
lodger_name，
l。
room_num，
getdate（）,
year（getdate（）），
month（getdate（）），
day（getdate（）），
DATEDIFF（day,checkin_date，getdate（）），
DATEDIFF（day,checkin_date，getdate（））*r.room_price，
l.pre_payment—DATEDIFF（day，checkin_date，getdate（））*r.room_price
fromlodgerasl,roomasr
wherel.lodger_name=＠nameandl。
room_num=r。
room_num
功能：
退房并将旅客信息插进checkout表中
存储过程十一:
proc_dayaccount_checkout
代码：
createprocedureproc_dayaccount_checkout
@cur_yearint,@cur_monthint，＠cur_dayint
as
insertDayAccount（cur_year,cur_month,cur_day，day_account）
selectcur_year，cur_month，cur_day,sum（room_account）
fromcheckout
wherecur_year=＠cur_year
and
cur_month=＠cur_month
and
cur_day=@cur_day
groupbycur_year，cur_month，cur_day
功能：
对checkout表中某一天的所有盈利进行累加并将结果存入dayaccount中
存储过程十二：
proc_dayaccount
代码：
createprocedureproc_QueryDayAccount
＠yearint，＠monthint,＠dayint
as
selectcur_year,cur_month，cur_day，day_account
fromDayAccount
wherecur_year=@yearand
升级会员 