sql+delphi数据库课程设计报告-酒店管理系统.doc

课程设计课 程： 数据库 题 目： 酒店客房管理系统 专 业： 信息管理与信息系统 班 级： 座 号： 姓 名： 2011年 3 月 5 日一、系统定义酒店客房管理系统是为了在正常运营中需要对客房资源、客人信息、消费信息、结算信息等进行管理，利用酒店客房管理信息系统对客房的各个操作进行管理，能够及时了解各个环节中信息的变更，有利于提高管理的效率。本项目要设计和实现一个能对酒店客房租住信息，客人信息，预订信息，消费信息、结算信息的管理，实现前台操作流程较方便，清晰，高效的信息管理系统二、需求分析现在我国的中小型宾馆客房管理水平还停留在纸介质的基础上，这样的机制已不能适应时代的发展，因为它浪费了许多人力和物力，在信息时代这种传统的管理方法必然被计算机为基础的信息管理所取代。而购买大型通用宾馆客房管理系统，对小型宾馆来说，又要付出昂贵的代价，而且管理项目不一定完全符合小型宾馆客房管理的要求。因此根据企业目前实际的宾馆客房管理情况开发一套宾馆客房管理系统是十分必要的。本系统主要具备以下五大功能: 前台操作、预定管理、财务管理、统计查询、管理设置(1) 前台操作:开房、顾客信息管理、续费、换房、结账退房、客房状态、商品消费、留言板、留言板查看(2) 预定管理: 预订、确认入住、预订信息修改、解除预订(3) 财务管理:所有收支、客户退房清算、交接班、账面金额、(4) 管理设置: 客房信息设置、财务管理、商品信息设置、操作人员信息(5) 统计查询: 客房状态分类查看、入住统计逻辑模型：数据流程图S2前台客房预订系统PF1预定单S1旅客LV( 1)( 2)客房目录D1符合顾客条件的订单P2S1旅客LVF2汇总订单待发出订单D3发送订单P3订单处理P2客房目录D1订单检查P1F3不合格预订单F1预订单( 3)D1客房目录S2前台LVF2汇总订单发放订单P3D3 待发出预订单普通处理P2.4特殊处理P2.3D8 普通订单D7 VIP订单D6 旅客情况订单分类P2.2D5 所剩客房数数目统计P2.1D2 符合条件订单D1 客房目录订单检查P1F3不合格预订单F1预订单S1旅客LV客房预订系统关联图客房预订系统顶层图客房预订系统二层图三、系统设计1、概念结构设计这一设计阶段是在需求分析的基础上，设计出能满足用户需求的各种实体，以及它们之间的关系，为后面的逻辑结构设计打下基础。根据上面的设计规划出的实体有客房实体，客人实体，预订信息、入住信息、账单信息等实体。客房信息 客房信息客房号可住人数客房等级 房间状态价格客房概况楼层 客人信息客人信息顾客编号顾客姓名证件类型证件号联系电话通讯地址性别是否为会员 预定房间客房号顾客编号预定预入住日期预离店日期预存费用1n押金客人入住客房号顾客编号入住是否结账入住时间押 金1n预存费用预离店时间转房 客房号顾客编号转 房1n退房 客房号顾客编号退 房结算费用1n入住日期退房日期2、逻辑结构设计1 客房信息表(客房号，状态标记，客房等级ROclass（豪华、标准、单间），全日房价格，半天房，钟点，楼层，客房概况，可住人数)2顾客信息表Vip表 （积分，会员等级，会员号)顾客基本信息表(顾客编号，表单自动编号,顾客姓名,性别,证件类型,证件号,联系电话,通讯地址,是否为会员，会员号)3操作人员表(员工号,员工姓名,密码,加入时间,部门)4 商品消费设置（商品信息表Goods：商品名、类别、单价，是否参与积分）顾客消费明细单(客房号 , 顾客编号,消费商品数目, 商品名,类别 ,单价,是否参与积分,操作日期,操作人员号)5留言板表(自动编号,留言人呢称,留言标题,留言文本内容,留言时间)6交班表 (操作人员号,操作人员,操作日期)7预订表(自动编号,客房号 , 顾客编号, 顾客姓名,客房类型,预存费用Brplan,押金,预订入住时间,离店时间,预计入住人数,操作日期,员工号)8入住表 (自动编号, 客房号 , 顾客编号, 顾客姓名,客房类型,预存费用,押金 ,是否消费商品,入住时间,预离店时间,入住人数,结账费用结账时交的费用 ,是否结账,操作日期,员工号)9 资金清算表 (客房号 , 顾客编号, 顾客姓名,客人名称,押金 ,应收费用 , 是否消费商品,实收金额,入住时间,结账日期,操作日期,操作人员号)3、系统功能模块图 4、其它设计图形工具Microsoft office visio 2003四、详细设计表格建立： create database newhotel-1 客房信息表create table Room-客房信息表(ROno varchar(8) primary key ,-客房号ROflat char(4) default 空房 ,-状态标记ROclass varchar(20)not null,-客房等级ROclass（豪华、标准、单间）ROdayprice money,-全日房价格ROhalfprice money,-半天房ROhourprice money,-钟点ROfloot char(4) not null,-楼层ROintroduce varchar(100),-客房概况ROnumber char(4)not null,-可住人数)-2 顾客信息表create table VIP(CUVIPno int primary key, VIPrewards int default 0,-积分VIPrewards，VIPclass char(8)-会员等级VIPclass)create table Customs(CUno char(8) , - primary key ,CUnum int IDENTITY(1,1) , -自动编号, -顾客编号,-触发器 回滚 -利用存储过程实现CUname varchar(8),-顾客姓名CUsex char(4),-性别CUtype char(8) not null,-证件类型Cucardno char(20) unique not null ,-证件号CUphone varchar(20)not null,-联系电话CUaddress varchar(40),-通讯地址CUflat bit not null,-是否为会员CUVIPno char(8) default null)-3操作人员表create table Operator(OPno varchar(20) primary key , -员工号OPname char(8), -员工姓名OPpass char(10) , -密码OPdate smalldatetime , -加入时间OPdepartment varchar(30) -部门)-4 商品消费设置（商品信息表Goods：商品名、类别、单价，是否参与积分、检索码）create table Goods(GOtype char(10) , - 类别GOname varchar(20), - 商品名GOprice money, -单价GOjifen bit, -是否参与积分)- 顾客消费明细单create table spend (ROno varchar(8), -客房号 chanzhao CUno char(8) ,SPnumber int, -消费商品数目GOname varchar(20), - 商品名GOtype char(10), -类别 chaozhao GOprice money, -单价GOjifen bit, -是否参与积分SPoper_date datetime, -操作日期CUoper_dateOPno varchar(20),-操作人员号CUopertorforeign key(ROno) references room(ROno),foreign key(CUno ) references Customs(CUno )foreign key(OPno ) references Operator(OPno )-5留言板表create table MessageTP(MessageID int IDENTITY(1,1) , -自动编号Messageowner char(10), -留言人呢称Messagetitle char(20), -留言标题Messagetext char(100), -留言文本内容Messagetime datetime -留言时间)-6交班表create table exchange(OPno1 varchar(20),-操作人员号CUopertorOPno2 varchar(20),-操作人员号CUopertorexoper_date datetime, -操作日期CUoper_dateforeign key(OPno1 ) references Operator(OPno ),foreign key(OPno2 ) references Operator(OPno )-7预订表create table Bookroom (BRplanno int IDENTITY(1,1) ,-自动编号ROno varchar(8), -客房号 CUno char(8) , CUname char(8),Rolevel varchar(20),-客房类型Rolevel （全日房价格ROdayprice、半天房价格BRplan money,-预存费用BRplan、BRdeposit money,-押金BRdeposit BRplanentertime datetime,-预订入住时间BRleavetime datetime, -离店时间BRplanpersons int,-预计入住人数BRoper_date datetime, -操作日期ROoper_dateOPno varchar(20) , -员工号chanzhaoforeign key(ROno) references room(ROno)drop table bookroom-8入住表create table Enterroom(ERenterno int IDENTITY(1,1), -自动编号ROno varchar(8), CUno char(8) , CUname char(8),Rolevel varchar(20),-客房类型Rolevel （全日房价格半天房价格BRplan money,-预存费用BRplan、BRdeposit money,-押金BRdeposit ERgoflat bit , -是否消费商品ERentertime datetime, -入住时间ERleavetime datetime, -预离店时间ERpersons int,-入住人数ERmoney money,-结账费用结账时交的费用 ERpaymoney bit,-是否结账ERoper_date datetime, -操作日期ROoper_dateOPno varchar(20) , -员工号 canzhaoforeign key(ROno) references room(ROno),foreign key(OPno) references Operator(OPno)9所有收支信息表create table Szhistorytable (ROno varchar(8), -客房号 chanzhao CUno char(8) , CUname varchar(8), -顾客姓名chanzhaoSZneed money, -应收费用SZfactfee money, -实收金额BRdeposit money, -押金BRdeposit ERentertime datetime, -入住时间SZoper_date datetime, -操作日期CUoper_dateOPno varchar(20) , -操作人员号CUopertorprimary key(ROno,CUno,ERentertime ),foreign key(ROno) references room(ROno),foreign key(CUno ) references Customs(CUno ),foreign key(OPno ) references Operator(OPno )资金清算表Zjqstablecreate table Zjqstable(ROno varchar(8), -客房号CUno char(8) ,CUname varchar(8), -客人名称BRdeposit money, -押金BRdeposit SZneed money, -应收费用 是否消费商品ZJfactfee money, -实收金额ERentertime datetime, -入住时间CUaccountday datetime, -结账日期ZJoper_date datetime, -操作日期CUoper_dateOPno varchar(20),-操作人员号CUopertorprimary key(ROno,CUno,CUaccountday ),foreign key(ROno) references room(ROno),foreign key(CUno ) references Customs(CUno ),foreign key(OPno ) references Operator(OPno )信息加入-1insert into Room(ROno,ROclass, ROdayprice,ROhalfprice,ROhourprice,ROfloot,ROintroduce ,ROnumber ) values(101,豪华,500,250,50,一楼,空调,1);insert into Room(ROno,ROclass, ROdayprice,ROhalfprice,ROhourprice,ROfloot,ROintroduce ,ROnumber ) values(201,标准,300,150,30,二楼,风扇,2);insert into Room(ROno,ROclass, ROdayprice,ROhalfprice,ROhourprice,ROfloot,ROintroduce ,ROnumber ) values(301,单间,200,100,10,三楼,无,3);-2select * from roominsert into VIP values(1,0,特等);insert into VIP values(2,1,无);insert into VIP values(3,0,中等);select * from Customsinsert into Customs(CUno,CUname,CUsex,CUtype,Cucardno,CUphone,CUaddress,CUflat,CUVIPno) values(1,张三,男,身份证,030510923434福州,1,1);insert into Customs(CUno,CUname,CUsex,CUtype,Cucardno,CUphone,CUaddress,CUflat,CUVIPno) values(2,李四,男,身份证,030510923435福州,0,null);-3insert into Operator values(00001,小红,123456,2000-05-08 ,财务);insert into Operator values(00002,小白,234567,1992-10-01 ,前台);insert into Operator values(00003,小黄,345678,2010-10-01 ,前台);-4insert into Goods values(饮料,可乐,4,1); -5drop table Szhistorytableinsert into Szhistorytable values(101,1,张三,600,200,789,2010-11-11,2010-11-11,00001);-7insert into bookroom(ROno,cuno,CUname,Rolevel,BRplan,BRdeposit, BRplanentertime ,BRleavetime ,BRplanpersons ,BRoper_date,OPno ) values(101,1,张si,500,200,200,2010/10/1,2010/10/10,2,getdate(),00001);-8insert into enterroom(ROno,CUno,CUname,Rolevel,BRplan,BRdeposit,ERgoflat ,ERentertime ,ERleavetime,ERpersons ,ERmoney ,ERpaymoney ,ERoper_date,OPno ) values(101,1,张,500,200,200,1,getdate(),2010/10/10,2,300,0,getdate(),00001);insert into enterroom(ROno,CUno,CUname,Rolevel,BRplan,BRdeposit,ERgoflat ,ERentertime ,ERleavetime,ERpersons ,ERmoney ,ERpaymoney ,ERoper_date,OPno ) values(201,2,dfs,500,200,200,1,getdate(),2010/10/10,2,300,0,getdate(),00001);存储过程-存储过程触发器-预订转入住时候使用 create procedure brproc (ROno varchar(8) -客房号-CUno char(8) ,-CUname varchar(8)as update room set roflat =预订 where rono=ROno create procedure erproc (ROno varchar(8) -客房号-CUno char(8) ,-CUname varchar(8)as update roon set roflat =入住 where rono=ROno -退房create procedure tfproc (ROno varchar(8) -客房号)as update roon set roflat =打扫 where rono=ROno -换房create procedure hfproc (ROno varchar(8) -客房号)as update room set roflat =打扫 where rono=ROno -续费alter procedure 续费xfproc (rono varchar(8),num int )as update enterroom set brplan=brplan+num where ROno= rono create procedure 退房xfproc (rono varchar(8),CUno char(8) )as delete from enterroom where ROno= rono and CUno= cuno create procedure 退房yudingxfproc (rono varchar(8),CUno char(8) )as delete from bookroom where ROno= rono and CUno= cuno - 换房create proc 换房hfproc(rono1 varchar (8),rono2 varchar (8),Rolevel varchar(20)as update enterroom set rono =rono2,rolevel=Rolevel where ROno = rono1 积分增加create procedure cuflatproc (CUno char(8) )as declare num int select num=spnumber from spend where cuno=cuno update vip set VIPrewards= VIPrewards+10*num where CUVIPno = (select CUVIPno from customs where cuno=cuno );用户密码登陆alter proc 密码登陆(opno char(8),oppass char(10),flag int output)as if exists(select * from Operator where opno=opNo) if exists(select * from Operator where oppass=oppass) set flag=1 else set flag=0elseset flag=-1declare flag int exec 密码登陆00001,123456,flag outputselect flag-用户密码修改create table Operator(OPno varchar(20) primary key , -员工号OPname char(8), -员工姓名OPpass char(10) , -密码OPdate smalldatetime , -加入时间OPdepartment varchar(30) -部门 youkeneng wei shangban)alter proc 密码修改(opno char(8),oldpass char(10),newpass char(10),newpass2 char(10),flag int output)as if exists(select * from Operator where opno=opno ) if exists(select * from Operator where oppass=oldpass) begin if newpass=newpass2 begin update operator set oppass=newpass where opno=opno and oppass=oldpass set flag=1 end end else set flag=0 elseset flag=-1-添加进入所有支出create procedure 所有支出(ROno varchar(8) ,-客房号CUno char(8) ,CUname varchar(8),SZneed money, -应收费用SZfactfee money, -实收金额BRdeposit money, ERentertime datetime, -押金BRdeposit SZoper_date datetime, -操作日期CUoper_dateOPno varchar(20) )as insert into Szhistorytablevalues( ROno ,CUno,CUname,SZneed,SZfactfee , BRdeposit ,ERentertime , SZoper_date, OPno ) ;-房屋数目-空房create procedure kfs(num int output)as select num=count(*) from room where roflat = 空房; declare num as int exec kfs num outputprint num -入住create procedure rzs(num int output)as select num=count(*) from room where roflat = 入住; declare num as int exec rzs num outputprint num - 维修create procedure wxs(num int output)as select num=count(*) from room where roflat = 维修; declare num as int exec wxs num outputprint num -打扫create procedure dss(num int output)as select num=count(*) from room where roflat = 打扫; declare num as int exec dss num outputprint num -全部create procedure qbs(num int output)as select num=count(*) from room ; declare num as int exec qbs num outputprint num -查看原有金额create procedure cxbrplan (ROno varchar(8) ,-客房号CUno char(8) ,CUname varchar(8)as declare num moneyselect num=BRplanfrom enterroomwhere ROno= ROno and CUno= CUno and CUname=CUnamereturn num-资金清算alter proc 资金清算( rono varchar(8), CUno char(8) , flag int output )as begin declare total int declare daycount int declare cost int select cost=sum(spnumber*GOprice) from spend where cuno=cuno select daycount =datediff(dd,ERentertime,getdate() from Szhistorytable where rono=rono and cuno=cuno select total =0select total=daycount*(isnull(Rolevel,0) from enterroom where rono=rono and cuno=cuno select total=total+SZneed-SZfactfee from Szhistorytable where rono=rono and cuno=cuno select total= total+cost from Spend where rono=rono and cuno=cuno set flag=total enddeclare flag int exec 资金清算 101 ,1,flag outputselect flag-触发器-预订转入住时候使用 create trigger dftrion bookroomfor insertas begin declare rono varchar(8) select rono=rono from inserted update room set roflat =预订 where rono=ROno end-入住create trigger rztrion enterroomfor insert, updateas begin declare rono varchar(8) select rono=rono from inserted update room set roflat =入住 where rono=ROno end-退房换房create trigger tftrion enterroomfor delete, updateas begin declare rono varchar(8) select rono=rono from deleted update room set roflat =打扫 where rono=ROno endalter proc 确认入住(ROno varchar(8), CUname char(8)as declare CUno char(8),Rolevel varchar(20),BRplan money,BRdeposit money,BRplanentertime datetime,BRleavetime datetime, BRplanpersons int,OPno varchar(20) select CUno=CUno ,Rolevel=Rolevel ,BRplan=BRplan ,BRdeposit=BRdeposit ,BRplanentertime=BRplanentertime ,BRleavetime=BRleavetime , BRplanpersons =BRplanpersons from bookroomwhere ROno =ROno and CUname =CUname insert into enterroom (ROno,CUno,CUname,Rolevel,BRplan,BRdeposit,ERgoflat,ERentertime,ERleavetime,ERpersons,ERmoney,ERpaymoney,ERoper_date, OPno ) values(rono,cuno,CUname,Rolevel,BRplan,BRdeposit,0,getdate(),BRleavetime,BRplanpersons,null,null,getdate(),OPno )delete from bookroom where ROno =ROno and CUname =CUname 五、系统实现与测试1、开发平台和工具选择计算机及操作系统： PC机，WindowsXP1；开发工具：Delphi运行环境: 装有SQL SERVER 2005 的系统语言：T-SQL语言2、系统测试六、课程设计总结我感觉这次的课程设计是我收获最大的一次，从刚开始的只懂部分东西，到后面完成的做完一个小软件，感觉很是开心！在整个设计过程中，对数据库的知识有了更加深入的理解！我觉得数据库的设计最重要的就是需求分析，如果这个没有做好，后面的设计经常会重新再来，或者多次修改！只有把需求分析弄清楚了，才会设计出最优的数据库，而一个数据库最重要的部分则是存储过程的实现部分，在这部分里面需要的知识太多了。与此同时，在做好前期的需求分析后，如何用T-SQL语言实现储存过程显得十分的重要，通过这次实验让我认识到在对于存储过程的应用上还需要进一步提高，同时，触发器也是必不可少的，以它来确保数据的准确度。但是，设计过程中对于软件的效率关注的不多。我们是两人一组，前期的需求分析，系统分析是两个人讨论完成，取长补短，代码实现我负责，前台操作，预订管理，财务管理三个模块。参考文献SQL Server 2005范例代码查询辞典(美) Joseph Sack著中心书库A0940915TP311.138SQ/134精通SQL结构化查询语言详解 .第2版李俊民编著中心书库A1023258TP311.138SQ/135Dephi7程序设计教程附录：外文翻译In Wang Zuoliangs translation practices, he translated many poems, especially the poems written by Robert Burns. His translation of Burns “A Red, Red Rose” brought him fame as a verse translator. At the same time, he published about ten papers on the translation of poems. Some argue that poems cannot be translated. Frost stresses that poetry might get lost in translation. According to Wang, verse translation is possible and necessary, for “The poet-translator brings over some exciting work from another culture and in doing so is also writing his own best work, thereby adding something to his culture. In this transmission and exchange, a richer, more colorful world emerges. ”(Wang, 1991:112). Then how can we translate poems? According to Wangs understanding, the translation of poems is related to three aspects: A poems meaning, poetic art and language. （1）A poems meaning “Socio-cultural differences are formidable enough, but the matter is made much more complex when one realizes that meaning does not consist in the meaning of words only, but also in syntactical structures, speech rhythms, levels of style.” (Wan