酒店客房管理系統(tǒng)參照

1、附錄 1 酒店客房管理系統(tǒng)業(yè)務(wù)流程圖和數(shù)據(jù)流圖1.業(yè)務(wù)流程圖（1）預(yù)訂退訂業(yè)務(wù)流程圖圖 1.1 預(yù)訂退訂業(yè)務(wù)流程圖（2）入住業(yè)務(wù)流程圖圖 1.2 入住業(yè)務(wù)流程圖（3）退房業(yè)務(wù)流程圖圖 1.3 退房業(yè)務(wù)流程圖（4）換房業(yè)務(wù)流程圖2.3.3 酒店客房管理系統(tǒng)數(shù)據(jù)流程圖圖 1.4 換房業(yè)務(wù)流程圖（5）財(cái)務(wù)統(tǒng)計(jì)業(yè)務(wù)流程圖圖 1.5 財(cái)務(wù)統(tǒng)計(jì)業(yè)務(wù)流程圖2.數(shù)據(jù)流圖（1）頂層數(shù)據(jù)流圖圖 1.6 頂層數(shù)據(jù)流圖（2）第一層數(shù)據(jù)流圖圖 1.7 第一層數(shù)據(jù)流圖（3）預(yù)訂退訂數(shù)據(jù)流圖 1.8 預(yù)訂退訂數(shù)據(jù)流圖（4）換房數(shù)據(jù)流圖圖 1.9 換房數(shù)據(jù)流圖（5）入住數(shù)據(jù)流圖圖 1.10 入住數(shù)據(jù)流圖（6）財(cái)務(wù)統(tǒng)計(jì)數(shù)據(jù)流圖

2、圖 1.11 財(cái)務(wù)統(tǒng)計(jì)數(shù)據(jù)流圖（7）退房數(shù)據(jù)流圖圖 1.12 退房數(shù)據(jù)流圖附錄 2 數(shù)據(jù)字典2.1 數(shù)據(jù)項(xiàng)表 21 數(shù)據(jù)項(xiàng)表數(shù)據(jù)相編號(hào)數(shù)據(jù)項(xiàng)名稱數(shù)據(jù)項(xiàng)別名數(shù)據(jù)項(xiàng)類型長(zhǎng)度取之范圍備注Dl-1Gno顧客編號(hào)Char20Dl-2Gname顧客Char20Dl-3Gsex顧客Char2“女”，“男”Dl-4Gid顧客號(hào)Char18Dl-5Gtel顧客Char11Dl-6Gaddress顧客地址Char20Dl-7Ggrade顧客intDl-8Account預(yù)交款floatDl-9grade范圍int大于等于 1Dl-10discount折扣float0.80,0.85,0.90,0.95打折額度Dl-

3、11Goodsno客房物品編號(hào)Char20Dl-12Goodsname客房物品名稱Char20Dl-13Oprice客房物品原價(jià)floatDl-14Dmultiple賠償倍數(shù)floatDl-15Atno消費(fèi)項(xiàng)目編號(hào)Char20Dl-16Atname消費(fèi)項(xiàng)目名稱Char20Dl-17Atprice消費(fèi)項(xiàng)目單價(jià)floatDl-18Rno客房編號(hào)Char20Dl-19Rtype客房類型Char20Dl-20Rprice客房單價(jià)floatDl-21Floor客房樓層IntDl-22Toward客房朝向Char10Dl-24Atime入住時(shí)間datetimeDl-25Rtime預(yù)訂時(shí)間datetimeD

4、l-26Ltime退房時(shí)間datetimeDl-27oprice入住價(jià)格floatDl-28Stime1預(yù)訂房態(tài)修改時(shí)間datetimeDl-29Amoney消費(fèi)項(xiàng)目花費(fèi)float數(shù)據(jù)相編號(hào)數(shù)據(jù)項(xiàng)名稱數(shù)據(jù)項(xiàng)別名數(shù)據(jù)項(xiàng)類型長(zhǎng)度取之范圍備注Dl-30Dmoney物品損壞費(fèi)floatDl-31balance余額floatDl-32Amount消費(fèi)項(xiàng)目個(gè)數(shù)IntDl-33Dnum損壞物品個(gè)數(shù)IntDl-34Stime2退訂修改房態(tài)時(shí)間datetimeDl-35Stime3入住修改房態(tài)時(shí)間datetimeDl-36Stime4換房修改房態(tài)時(shí)間datetimeDl-37Stime5退房修改房態(tài)時(shí)間date

5、time2.2 數(shù)據(jù)結(jié)構(gòu)表 2.2 數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)結(jié)構(gòu)編號(hào)數(shù)據(jù)結(jié)構(gòu)名數(shù)據(jù)結(jié)構(gòu)含義數(shù)據(jù)結(jié)構(gòu)組成DS-1guest顧客信息Gno,Gname,Gsex,Gid,Gtel,Gaddress,Account,balance,grade,di scountDS-2Into入住信息DS-1+DS-8DS-3reserve預(yù)訂信息DS-1+DS-8DS-4return退訂信息DS-1+DS-8DS-5goods客房物品信息Goodsno,Goodsname,Oprice,DmultipleDS-6Amuse價(jià)目表Atno,Atname,AtpriceDS-7RoomType客房信息Rno,Rtype,Rpri

6、ce,Floor,TowardDS-8RoomState房態(tài)信息Rno,Atime,Rtime,RLtime,Ltime,Gno,Intoprice,StimeDS-9consum消費(fèi)Cono,Gno,account,balance,Amoney,Dmoney,CotimeDS-10Amuse顧客消費(fèi)單DS-6+DS-1DS-11GoodsD物品損壞單DS-5+DS-12.3 數(shù)據(jù)處理表 2.3 處理過(guò)程處理邏輯編號(hào)處理邏輯名稱簡(jiǎn)述輸入的數(shù)據(jù)流處理輸出的數(shù)據(jù)流顧客預(yù)訂數(shù)據(jù),房態(tài)信息，客房類型前臺(tái)核對(duì)是否有滿足顧客要求的根據(jù)客房類型和房態(tài)信息,核對(duì)是否有滿足顧客要求的客房并反饋給顧客滿足要求的顧

7、客信息和顧客信息.客房P1.1核對(duì)客戶要求將顧客分配到滿滿足要求的顧客信息和客房信息根據(jù)滿足要求的信息,辦理登記，并修改客房狀態(tài)預(yù)訂單,將預(yù)訂單存檔并反饋給客戶足要求的客房,在前臺(tái)P1.2預(yù)訂登記對(duì)顧客退訂處理顧客的退訂數(shù)據(jù)根據(jù)顧客的退訂信息，客房狀態(tài)房態(tài)信息， 根據(jù)房態(tài)信息更新房態(tài)信息更改后的房態(tài)信息，新的入住P1.3退訂登記前臺(tái)對(duì)已定房顧客進(jìn)行登記顧客給出的預(yù)定信息辦理入住手續(xù)，修改房態(tài)信息P2.1入住登記滿足/不滿足信息， 余額要求查看酒店的空客房是否滿足客戶要求顧客換房要求根據(jù)客戶的要求， 查看是否有滿足客戶要求的空客房P3.1核對(duì)客戶要求余額請(qǐng)求根據(jù)換房顧客的消余額足/不足信息對(duì)顧客

8、的消費(fèi)余額進(jìn)行費(fèi)，余額P3.2余額是否能滿足所換房?jī)r(jià)格對(duì) 換 房 者 進(jìn) 行 登記，并修改房態(tài)信息對(duì)換房者進(jìn)行換余額足夠信息房態(tài)信息房登記P3.3登記換房對(duì)換房顧客填寫(xiě)需款通知足額不足信息填寫(xiě)續(xù)款通知續(xù)款通知單P3.4開(kāi)續(xù)款通知前臺(tái)對(duì)顧客的退顧客退房數(shù)據(jù)根據(jù)顧客的退房信息，更新房態(tài)信息房態(tài)信息， 將新的房態(tài)信息存檔房進(jìn)行確認(rèn)P4.1退房登記根據(jù)財(cái)務(wù)部的顧客消費(fèi)匯總，打印顧客消費(fèi)情況消費(fèi)數(shù)據(jù)， 來(lái)源財(cái)務(wù)部根據(jù)財(cái)務(wù)部的顧客消費(fèi)匯總，打印消消費(fèi)， 反饋給顧客其消費(fèi)情況P4.2打印消費(fèi)費(fèi)客，反饋給顧根據(jù)顧客的消費(fèi)項(xiàng)目和客房部擁有的消費(fèi)項(xiàng)目核對(duì)顧客的消費(fèi)情況對(duì)顧客的各種花費(fèi)進(jìn)行匯總顧客的消費(fèi)項(xiàng)目， 客房

9、部擁有的消費(fèi)項(xiàng)目根據(jù)客房部擁有的消費(fèi)項(xiàng)目統(tǒng)計(jì)顧客的消費(fèi)項(xiàng)目消費(fèi)項(xiàng)目遞給財(cái)務(wù)部， 傳P5.1統(tǒng)計(jì)消費(fèi)項(xiàng)目顧客的消費(fèi)， 損壞物品的賠償， 顧客信息及會(huì)員信息對(duì)顧客的所有經(jīng)費(fèi)進(jìn)行匯總?cè)缡菚?huì)員進(jìn)行一位顧客的所有花費(fèi)匯總顧客消費(fèi)項(xiàng)目P5.2統(tǒng)計(jì)客房物品的損壞情況客房物品信息根據(jù)物品檢查損壞物品賠償信息是否有損壞， 則對(duì)損壞者進(jìn)行索賠P5.3統(tǒng)計(jì)損壞物品處理邏輯編號(hào)處理邏輯名稱簡(jiǎn)述輸入的數(shù)據(jù)流處理輸出的數(shù)據(jù)流匯總酒店的營(yíng)業(yè)額顧客消費(fèi)信息根據(jù)顧客的消費(fèi)情 酒店?duì)I業(yè)額況，對(duì)酒店的營(yíng)業(yè)額進(jìn)行匯總P5.4酒店?duì)I業(yè)額匯總附錄 3 數(shù)據(jù)庫(kù)模式和用戶子模式定義3.1 數(shù)據(jù)庫(kù)模式定義表 31 顧客基本信息列名數(shù)據(jù)類型可否

10、為空說(shuō)明Gno Gname Gsex Gid GtelGaddresschar(20) char(20) char(2) char(18) char(11)char(20)not null not null not nullnot null顧客編號(hào)顧客顧客顧客顧客顧客地址顧客預(yù)付金額顧客顧客的折扣率顧客余額Account Grade discountbalancefloat int floatfloatnot null表 32 房間基本信息列名數(shù)據(jù)類型可否為空說(shuō)明Rno Rtype Rprice RfloorTowardchar(10) char(20) float smallintchar(

11、10)not null not null not null not nullnot null房間編號(hào)房間類型房間價(jià)格房間樓層房間朝向表 33 房間物品信息列名數(shù)據(jù)類型可否為空說(shuō)明Goodsno Goodsname OpriceDmultiplechar(20) char(20) floatfloatnot null not null not nullnot null物品編號(hào)物品名字物品價(jià)格物品索賠倍數(shù)表 34 房態(tài)信息列名數(shù)據(jù)類型可否為空說(shuō)明列名數(shù)據(jù)類型可否為空說(shuō)明Rno Gno Atime Ltime Rtime RltimeIntoPrice Days Stimeflagchar(10)

12、char(20) datetime datetime datetime datetime float int datetimechar(1)not nullnot null房間號(hào)顧客號(hào)實(shí)際入住時(shí)間實(shí)際退房時(shí)間預(yù)訂入住時(shí)間預(yù)訂退房時(shí)間入住價(jià)格入住天數(shù)修改時(shí)間預(yù)訂入住標(biāo)志位表 35項(xiàng)目信息列名數(shù)據(jù)類型可否為空說(shuō)明Atno AtnameAtpricechar(20) char(20)floatnot null not nullnot null項(xiàng)目編號(hào)項(xiàng)目名稱項(xiàng)目單價(jià)（時(shí)間計(jì)費(fèi)或個(gè)數(shù)計(jì)費(fèi)）表 36消費(fèi)信息列名數(shù)據(jù)類型可否為空說(shuō)明Gno Atno AmountWtimechar(20) char(20)

13、 floatdatetimenot nullnot null顧客號(hào) 商品號(hào) 消費(fèi)數(shù)量消費(fèi)開(kāi)始時(shí)間not null表 37 物品賠償信息列名數(shù)據(jù)類型可否為空說(shuō)明Gno RnoGoodsno DnumAmendstimechar(20) char(10) char(20) intdatetimenot nullnot null not null顧客號(hào) 房間號(hào) 物品號(hào) 損壞個(gè)數(shù)損壞時(shí)間Datetime3.2 用戶子模式定義表 38 訂房視圖列名數(shù)據(jù)類型可否為空說(shuō)明Gno Rno RtypeRfloorchar(20) char(10) char(20)smallintnot null not nul

14、l not nullnot null顧客號(hào) 房間號(hào) 房間類型房間樓層Toward IntoPrice Rtime Rltime DaysWtimechar(10) float datetime datetime intdatetimenot null not null not null not null not nullnot null房間朝向入住價(jià)格入住時(shí)間打算退房時(shí)間居住天數(shù)查看時(shí)時(shí)間表 39 入住視圖列名數(shù)據(jù)類型可否為空說(shuō)明Gno Gname Rno Rtype Rfloor TowardIntoPrice Atime Ltime Days AccountWtimechar(20) ch

15、ar(20) char(10) char(20) smallint char(10) float datetime datetime int floatdatetimenot not not not not not not not not not notnotnull null null null null null null null null null nullnull顧客號(hào)顧客 房間號(hào)房間類型房間樓層房間朝向入住價(jià)格入住時(shí)間退房時(shí)間入住天數(shù)預(yù)付金額查看時(shí)時(shí)間表 310 空房視圖列名數(shù)據(jù)類型可否為空說(shuō)明Rno Rtype Rprice RfloorTowardchar(10) char(2

16、0) float intchar(10)not null not null not null not nullnot null房間號(hào) 房間類型入住價(jià)格房間樓層房間朝向附錄 4SQL 語(yǔ)句4.1 建立數(shù)據(jù)表 SQL 語(yǔ)句（1）顧客基本信息表的建立：create table guest( Gno char(20) not null, Gname char(20)not null,Gsex char(20) not null,Gid char(18) unique not null, Gtel char(11),Gaddress char(20), Account float, Grade int,

17、discount float not null, balance float,primary key (Gno),check (Account >= 0.0 and Grade>0)（2）客房基本信息表的建立：create table Roominfo ( Rno char(10),Rtype nvarchar(20)not null, Rprice float not null, Rfloor smallint not null,Toward nvarchar(10)not null, primary key (Rno),check (Rfloor between 1 and 1

18、00),check (Toward in('正北','正南','正西','正東','東北','西南','西北','東南'),check (Rtype in('標(biāo)準(zhǔn) 1','標(biāo)準(zhǔn) 2','豪華 1','豪華 2','高級(jí) 1','高級(jí) 2'),)（3）房態(tài)表的建立：create table RoomState(Rno char(10), Gno char(20), Atime

19、 datetime , Ltime datetime, Rtime datetime, Rltime datetime, IntoPrice float , Days int ,Stime datetime, flag char(1) ,primary key (Rno,Gno),foreign key (Rno)references Roominfo(Rno), foreign key (Gno)references guest(Gno), check (flag in('1','2','3'),)（4）項(xiàng)目基本信息表的建立：create ta

20、ble Atariff( Atno char(20), Atname char(20)not null, Atprice float not null, primary key (Atno), check (Atprice >0.0)（5）顧客消費(fèi)信息表的建立：create table Consumelist ( Gno char(20), Atno char(20), Amount float,Wtime datetime not null, primary key(Gno,Atno),foreign key (Gno)references guest(Gno),foreign key

21、 (Atno)references Atariff(Atno)（6） 客房物品基本信息表的建立：create table RoGoInfo( Goodsno char(20), Goodsname char(20)not null, Oprice float not null, Dmultiple float not null, primary key (Goodsno)（7） 顧客賠償物品信息表的建立：create table GoAmInfo (Gno char(20), Rno char(10), Goodsno char(20), Dnum int ,Amendstime dateti

22、me not null, primary key(Gno,Rno,Goodsno),foreign key (Gno)references guest(Gno), foreign key(Rno)references Roominfo(Rno),foreign key(Goodsno)references RoGoInfo(Goodsno)4.2 建立視圖 SQL 語(yǔ)句（1）用于預(yù)訂房信息的視圖定義如下：create view BookView(Gno,Gname,Rno,Rtype,Rfloor,Toward,IntoPrice,Rtime,Rltime,Days,Stime) assele

23、ct RoomState.Gno,Gname,RoomState.Rno,Rtype,Rfloor,Toward,IntoPrice,Rtime,Rltime,Days,Stime from Roominfo,RoomState,guestwhere flag='1' and Roominfo.Rno=RoomState.Rno and RoomState.Gno=guest.Gno（2）用于已入住房的試圖如下：create view IntoView(Gno,Gname,Rno,Rtype,Rfloor,Toward,IntoPrice,Atime,Ltime,Days,Ac

24、count) asselect RoomState.Gno,Gname,RoomState.Rno,Rtype,Rfloor,Toward,IntoPrice,Atime,Ltime,Days,Account from Roominfo,RoomState,guestwhere flag='2' and Roominfo.Rno=RoomState.Rno and RoomState.Gno=guest.Gno（3）用于空房的視圖定義如下：create view EmRoView(Rno,Rtype,Rprice,Rfloor,Toward) asselect Rno,Rtyp

25、e,Rprice,Rfloor,Toward from Roominfowhere Rno not in (select RnoFrom RoomState)4.3 建立過(guò)程 SQL 語(yǔ)句1.lsqSearchDate 的定義create procedure lsqSearchDate date datetimeasselect Atno,sum(Amount) from Consumelistwhere Wtime=dategroup by Atno2.lsqSearchEmpty 的定義create procedure lsqSearchEmpty floor intasselect Rn

26、o,Rtype,Rprice,Rfloor,Toward from EmRoViewwhere Rfloor=floorlsqSearchEmpty 23. lsqWatchGuest 的定義create procedure lsqWatchGuest asselect Gno,Gname,Gsex,Gid from guest4. lsqSearchGuest 的定義create procedure lsqSearchGuest Gno char(20)as beginselect Gno,Gname,Account,balance from guestwhere Gno=Gnoselect

27、 RoomState.Rno,Rtype,IntoPrice from RoomState,Roominfowhere RoomState.Gno=Gno and RoomState.Rno=Roominfo.Rno select c.Atno,Atname,Amount, Amount*Atprice AmuMoney,Wtime from Consumelist c,Atariff awhere c.Gno=Gno and c.Atno=a.Atno selectg.Rno,r.Goodsname,g.Dnum,r.Oprice,r.Dmultiple,Oprice*g.Dnum*r.Dm

28、ultiple AmendMoney,g.Amendstimefrom GoAmInfo g,RoGoInfo rwhere g.Gno=Gno and g.Goodsno=r.Goodsno end5. lsqConsumeList 的定義CREATE PROCEDURE lsqConsumeListConsumelist_Gno char(20), Consumelist_Atno char(20),Consumelist_Amount float, Consumelist_wtime datetime asinsertinto Consumelistvalues(Consumelist_

29、Gno,Consumelist_Atno , Consumelist_Amount ,Consumelist_wtime ) lsqConsumeList 'G00004','A-KTV-M',2,'2007-1-5'6. lsqAddRoomGoods 的定義create procedure lsqAddRoomGoods GDnumber char(20),GDname char(20), GDprice float, GDmultiple floatas insertinto RoGoInfo(Goodsno,Goodsname,Opric

30、e,Dmultiple) values(GDnumber,GDname,GDprice,GDmultiple)7. lsqAddAmusement 的定義create procedure lsqAddAmusement Atno char(20),Atname char(20), Atprice floatas insertinto Atariff values(Atno,Atname,Atprice)8. lsqAddGuest 的定義create procedure lsqAddGuest Gno char(20),Gname char(20), Gsex char(20), Gid ch

31、ar(20), discount floatas insertinto guest(Gno,Gname,Gsex,Gid,discount) values(Gno,Gname,Gsex,Gid,discount)9. lsqMoney 的定義create procedure lsqMoneyGno char(20),Account float asupdate guestset Account=Account where Gno=Gno10. lsqBook 的定義create procedure lsqBook Rno char(10),Gno char(20), Rtime datetim

32、e, Rltime datetime, Days int, Stime datetime,discount float output, Rprice float output asbeginselect discount=discount from guest where Gno=Gno select Rprice=Rprice from Roominfo where Rno=Rno insertinto RoomState(Rno,Gno,Rtime,Rltime,IntoPrice,Days,Stime,flag) values(Rno,Gno,Rtime,Rltime,discount*

33、Rprice,Days,Stime,'1') end11. lsqInto 的定義create procedure lsqInto Rno char(10),Gno char(20), Atime datetime, Ltime datetime, Days int, Stime datetime, money floatas beginupdate guestset Account=money where Gno=Gno update RoomStateset Atime=Atime,Ltime=Ltime,Days=Days,Stime=Stime,flag='2&

34、#39;where Rno=Rno and Gno=Gnoend 12.lsqInsertAmends 的定義create procedure lsqInsertAmends Gno char(20),Rno char(10), Goodsno char(20), Dnum int, Amendstime datetime asinsertinto GoAmInfo(Gno,Rno,Goodsno,Dnum,Amendstime) values(Gno,Rno,Goodsno,Dnum,Amendstime)13.lsqDeleteRoom 的定義create procedure lsqDel

35、eteRoom Rno char(10),Gno char(20) asdeletefrom RoomStatewhere Rno=Rno and Gno=Gno4.4 建立觸發(fā)器 SQl 語(yǔ)句1.當(dāng)訂房信息（即房態(tài)信息），觸發(fā) guest 表，根據(jù)顧客計(jì)算顧客新的折扣度CREATE TRIGGER tri_discount ON RoomState FOR insertASdeclare Grade int, Gno char(20)select Gno=Gno from insertedselect Grade=Grade from guest where Gno=Gno if (Grad

36、e >= 0 and Grade<300)begin update guestset discount=1.00 where Gno=Gno endelse if(Grade<500 )begin update guestset discount=0.95 where Gno=Gno endelse if(Grade<700) beginupdate guestset discount=0.90 where Gno=Gno endelse if (Grade<1000) beginupdate guestset discount=0.85 where Gno=Gn

37、o endelse beginupdate guestset discount=0.80where Gno in(select Gno from inserted) end2.當(dāng)修改房態(tài)信息（即入住信息）時(shí)，觸發(fā) guest 表，計(jì)算新及余額CREATE TRIGGER tri_grade_balance on RoomState for updateas begin declareIntoPrice float, Days intselect IntoPrice=IntoPrice from RoomState where Rno in(select Rno from inserted )

38、andGno in(select Gno from inserted)select Days=Days from RoomState where Rno in(select Rno from inserted ) andGno in(select Gno from inserted)update guestset balance=Account-IntoPrice*Days,grade=grade+IntoPrice*Days where Gno in(select Gno from inserted)end3.刪除客房信息（即退房）時(shí)，觸發(fā) guest 表，把顧客的預(yù)付款和余額設(shè)為 0CRE

39、ATE TRIGGER tri_delete on RoomState for deleteas beginupdate guestset Account=0,balance=0where Gno in(select Gno from deleted) end4.當(dāng)新的消費(fèi)信息時(shí)，觸發(fā) guest 表，從新計(jì)算顧客和余額CREATE TRIGGER tri_grade1 ON Consumelist FOR insertAs begin declareGno char(20), Atno char(20), Amount int, Atprice floatselect Gno=Gno,Atno=Atno,A