數(shù)據(jù)庫應(yīng)用技術(shù)大作業(yè)-旅館管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)

1、SQL 實(shí)踐作業(yè)：旅館管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)與實(shí)現(xiàn)-自動(dòng)化學(xué)院自化專業(yè)所完成工作 :數(shù)據(jù)庫整體結(jié)構(gòu)的設(shè)計(jì)，ER 圖的繪制和其他工作的審核系統(tǒng)名稱旅館管理系統(tǒng)一、系統(tǒng)需求1。背景:隨著科技和經(jīng)濟(jì)的發(fā)展，旅游業(yè)已經(jīng)成為一個(gè)熱門的產(chǎn)業(yè)，并且傳統(tǒng)的手 工已不適應(yīng)現(xiàn)代酒店管理的需要及時(shí)準(zhǔn)確全方位的網(wǎng)絡(luò)化信息管理成為必 需。在酒店的管理及業(yè)務(wù)日益復(fù)雜、要求在不斷提高的現(xiàn)狀下，利用高科技、現(xiàn) 代化的電腦自動(dòng)化管理系統(tǒng)來處理日益繁重的酒店業(yè)務(wù)于大型的酒店是必須 具備的管理方式.酒店客房管理系統(tǒng)是根據(jù)酒店對客房管理的實(shí)際情況進(jìn)行編寫的要目的 是為了方便酒店對客房的實(shí)際情況進(jìn)行集中的查詢與管理工 ,以提高整個(gè)酒店 的

2、工作。酒店客房管理的科學(xué)化、系統(tǒng)化、信息化成為各個(gè)酒店追求的目標(biāo)。因 此,而要實(shí)現(xiàn)這些功能,就要求各個(gè)酒店配備一套客房管理系統(tǒng)便在酒店內(nèi)實(shí) 施良好的完善的管理且以最快地速度響應(yīng)客戶的需求，及時(shí)為他們提供服 ,為 他們提供一個(gè)高效、便捷的居住環(huán)境.2。旅店需求特點(diǎn)分析:通過對旅店的調(diào)研，對于旅店的需求特點(diǎn)有了基本了解（1）基本要求：系統(tǒng)能夠進(jìn)行客戶的信息的存儲，客戶信息的刪除，客戶信息 的 更新戶賬單的結(jié)算因此要求系統(tǒng)一定要嚴(yán)密 準(zhǔn)確，不能出現(xiàn)插入和刪除異常，因此要求數(shù)據(jù)庫系統(tǒng)的設(shè)計(jì)符合第四范式 （2）高級要求：旅店面對的經(jīng)常是一些突發(fā)的情況，比如客戶的提前退房，客 戶要求換 , 客戶要求其他的

3、加服務(wù)這些突發(fā)事件是我們在 我們的系統(tǒng)中需要著重考慮的3。旅客需求特點(diǎn)分析：通過對用戶的調(diào)研，我們發(fā)現(xiàn)用戶希望住旅館的手續(xù)能夠盡量簡單，但是要求旅館的各種信息準(zhǔn)確，出現(xiàn)錯(cuò)誤的可能性盡量低4。旅店管理系統(tǒng)需求分析： 旅店的客房特點(diǎn)分析 過每個(gè)房間唯一的房間號來區(qū)別（也就是我們數(shù)據(jù)庫系統(tǒng)中的 room_num間分成不同的類(room_type)有A 類型它們對應(yīng)的價(jià)格也會不同這個(gè)由顧客自己選擇體現(xiàn)了我們 設(shè)計(jì)系統(tǒng)的人性化; 房間的狀態(tài)room_state)會根據(jù)是否有用戶入住而不同果該房間 有人入住的話，那么就將房間的狀態(tài)標(biāo)記為 ； 房間同時(shí)擁有狀態(tài),如果房間被預(yù)定的話，那么就將這 個(gè)房間的狀態(tài)標(biāo)

4、記為 Booked果沒有預(yù)定的話，那么房間的狀 態(tài)為nobook樣的話方便旅客入住種種操 作避免住進(jìn)了已經(jīng)有人預(yù)訂的房間時(shí)有些房間即使已經(jīng)有人預(yù) 定了但是在預(yù)定旅客之前其他旅客還是可以住進(jìn)來的要他在預(yù) 定旅客入住之前退房就可以了，樣大大提高了客房的利用率。 考慮房間和顧客的關(guān)系 ,房間和旅客關(guān)系是一對多的，因?yàn)閺默F(xiàn)實(shí)出 發(fā)，房間分為單人房房等，旅客的收費(fèi)是按照床位 來收費(fèi)的但是不同房間的床位的價(jià)格是不同的是在我們的數(shù)據(jù) 庫中默認(rèn)所有的房間都是單人房這是我們設(shè)計(jì)的不足更理想的是 在每種類型中分單人房人房為每個(gè)床位設(shè)立狀態(tài)， 每位顧客都是按照床位來收房而不是簡單的按照房間來收費(fèi)但是 在現(xiàn)實(shí)生活中旅

5、館的類型又有很多種們所設(shè)計(jì)的數(shù)據(jù)庫可能只是 適合其中的部分類型的旅館。旅客特點(diǎn)分析:從旅館的角度來說，最重要的就是準(zhǔn)確地記錄旅客在住宿期間一切 信息，并且要求準(zhǔn)確(1) 表用來記錄旅客的信息 lodger_name,id_num,room_num,e checkin_date,exp_checkout_date，pre_payment(2) lodger 用來記錄當(dāng)前正在發(fā)生入住的旅客的信息，這個(gè)表是 動(dòng)態(tài)的，如果旅客退房,與之相關(guān)的表示 checkout 實(shí)體集將用戶的信息 載入這個(gè)表，因?yàn)橥朔康男畔⑹欠浅ｊP(guān)鍵的 因旅店的賬目的 結(jié)算是要依靠這個(gè)表的,然后他的信息就會 這個(gè)表中刪 除；旅客可

6、能要求換房實(shí)現(xiàn)這個(gè)功能的是 聯(lián)系集 這個(gè)聯(lián)系集聯(lián)系了 個(gè)表，將原來的房間的狀態(tài) 修改為Free,將新入住的房間修改為;旅客可能要求續(xù)住，我們當(dāng)然會滿足這個(gè)要求，實(shí)現(xiàn)這個(gè)要求的 要求是聯(lián)系 con_room果旅客續(xù)住那么在情況允許的情況下， 可以繼續(xù)在原來的房間住宿 exp_checkout_date 的信息，如果原來的房間已經(jīng)有人預(yù)定的話么建議旅客更改房間重新 登記lodger信息;旅店的賬目管理特點(diǎn)分析旅店的賬目管理系統(tǒng)是一個(gè)比較復(fù)雜的系統(tǒng)求每天都要進(jìn)行更新 同時(shí)還須具備日結(jié)結(jié)的功能，與此相 關(guān)的是 ，DayAccount，MonthAccount,YearAccount 他們最 終依賴的表

7、都是 表我們的系統(tǒng)中 checkout 用于記錄 退房用戶的信息我們并沒有設(shè)定主鍵只是設(shè)立了外依賴因?yàn)樵谶@ 個(gè)表中不可能有兩個(gè)完全相同的元組(lodger_name room_num cur_year ， cur_day ） , （ lodger_name ， room_numdate兩個(gè)元組都不可能相同為 cur_date 是精確到秒的, 在實(shí)際情況中具有相同 lodger_name,同一時(shí)間內(nèi)從同一個(gè)房間退 房時(shí)不會發(fā)生地 ,之所以不刪除這個(gè)表的元組，是因?yàn)槲覀兿氡Ａ暨@ 些信息以便日后的查詢，比如核對賬 ,比如發(fā)生刑事案件需要旅店 配合時(shí)，可能需要查詢相關(guān)的信息日結(jié) DayAccount

8、實(shí)現(xiàn)也 比較 復(fù)雜， 先我們 定 一個(gè)作業(yè) Inseting DayAccount 每 0：00:00 向 DayAccount 插入一個(gè)用 于初始化的元組后建立一個(gè) 與 DayAccount 之間的觸發(fā) 器,如果向 checkout 中插入信息，那么就將 累加到 DayAccount 中的 day_account 中去這樣就能自動(dòng)統(tǒng)計(jì)了每天的盈利 狀況MonthAccount 和 YearAccount 算法和 DayAccount 似，同樣通 過定制作業(yè)和建立觸發(fā)器，實(shí)現(xiàn)自動(dòng)運(yùn)算，在這就不多介紹了二、系統(tǒng)概念型（ 圖三、關(guān)系模式邏輯模 四、物理設(shè)計(jì)表結(jié)構(gòu)) Table1 ：Room Set

9、）room_num (房間號 room_type (房間類型 room_price（房間價(jià)格)， room_state（房間狀態(tài)）, IsBooked （預(yù)定狀態(tài)）room_numTable2 ：Lodger (Entity Set）Attribute lodger_name （客戶姓名 id_num （身份證號）, room_num （房間號)， checkin_date (入住時(shí)間）, exp_checkout_date (預(yù)期退房時(shí)間 pe_payment （客戶預(yù)付款)PrimaryKey ：lodger_num，id_numroom_num references RoomTable

10、3 ：Room_Prebook （Entity Set)Attribute reserve_name （預(yù)定客戶姓名 room_num （預(yù)定房間號）, exp_checkin_date（ 預(yù)期入住時(shí)間 pre_payment （預(yù)付款）PrimaryKey ：reserve_nameForeignKey ：room_num references RoomTable4 ：DayAccount (Entity ）Attribute cur_year （年）,cur_month （月cur_day (日),day_account（月結(jié)）PrimaryKey ：cur_year，cur_month，

11、cur_day Set）Attribute lodger_name （客戶姓名)，room_num （房間號)，cur_date （日期（cur_year，cur_month,cur_day)(/月/日）, live_days （住宿天數(shù)room_account (賬單）,back_change (找零）ForeignKey ：lodger_name references Lodgerroom_num references RoomTable6 ： MonthAccount(Entity ）Attribute cur_year (年）,cur_month (月)，month_account （

12、日結(jié)）cur_year,cur_month （Entity Set)cur_year （年year_account （年結(jié)）cur_yearTable8 ：Remind(EntitySet)Attribute lodger_name （客戶姓名）,room_num (房間號）,cur_date （日期）lodger_name，cur_dateForeignKey ：lodger_name五、系統(tǒng)實(shí)現(xiàn)1功能模塊設(shè)：(1) 客房信息管理集合a) 查詢房間的空閑狀態(tài)b) 查詢房間的預(yù)定狀態(tài)c) 查詢某種類型房間的價(jià)格d) 更新某種類型房間的價(jià)格e) 更新房間的空閑狀態(tài)f) 更新房間的預(yù)定狀態(tài)(2)

13、旅客信息管理集合a) 查詢?nèi)胱÷每偷脑敿?xì)信息b) 查詢已預(yù)訂旅客的信息c) 查詢已退房旅客的信息d) 取消預(yù)定操作e) 換房操作f) 續(xù)住操作g) 退房操作(3) 旅店賬戶管理集合a) 退房客戶結(jié)算b) 每日結(jié)算c) 每月結(jié)算d) 每年結(jié)算(4) 服務(wù)管理項(xiàng)目集合a) 每日提醒那些旅客已經(jīng)到退房的時(shí)候b) 每日提醒那些預(yù)定的客戶將在今天住進(jìn)來2。創(chuàng)建據(jù)庫的 語句CREATE DATABASE HotelON（NAME = NHotel,FILENAME = 。mdf,SIZE =23MB,MAXSIZE = UNLIMITED,FILEGROWTH = 1MB）LOG ON（NAME = NH

14、otel_log，F(xiàn)ILENAME = N ，SIZE 1MB MAXSIZE =2048GB,FILEGROWTH = 10 )GO 說明：分別建立了 文件和 ldf 件,規(guī)定 文件的初始大小為 3MB,增 長速率為 1MB 每次，日志文件初始小為 1MB 增長速率為 10.3創(chuàng)建表的 句（1)建 表存客房信息 （ ） 10） (） ， ）（2）創(chuàng)建 Lodger 表,存儲旅客信息 (（ ） references , )）（3)創(chuàng)Room_preBook表，用于存儲預(yù)定客戶信息 (10) ， ）（4） （ ，)(5創(chuàng)退表儲房客息 （ ， (） ， ， , ， （ ) ） 4（6)創(chuàng)Month

15、Account表，存儲月結(jié)信息 ( ， money ( ） ）建表，用于 ( ， int （ ) )建emind表 （ （） 20) ( 4存過展存儲過一 : 代碼: ， = 功能：查詢當(dāng)前空閑的房間存儲過二 ：proc_query_bookedroom 代碼： , ，5 功能:查詢當(dāng)前已經(jīng)預(yù)定出去的房間存儲過三 :proc_query_price代碼：create proc proc_query_priceasselect distinct room_type，room_pricefrom room功能：查詢不同類型房間的價(jià)格存儲過四： proc_inc_price代碼：create pro

16、c proc_inc_priceroom_type char(10)，new_price moneyasupdate roomset room_price = new_pricewhere room_type = room_type功能：更改某種類型房間的價(jià)格存儲過五 ：proc_QueryLodger代碼：create procedure proc_QueryLodgercheckin_date datetimeasselect lodger_name，id_num，exp_checkout_date,pre_paymentfrom lodgerwhere datepart（day，chec

17、kin_date）=datepart（day,checkin_date 功能：查詢指定日期入住的旅客存儲過六 :proc_querybook代碼：create proc proc_query_bookasselect room_num，reserve_name ,exp_checkin_date，pre_payment from Room_prebook功能：查詢預(yù)訂的旅客的信息6存儲過七 ：proc_bookCancel代碼：create procedure proc_bookCancelreserve_name char(20)asupdate roomset IsBooked = nob

18、ookwhere room_num = （select room_numfrom Room_PreBookwhere reserve_name = reserve_name）delete from Room_PreBookwhere reserve_name = reserve_name執(zhí)行:執(zhí)行后的 room_prebook 表存儲過八 :proc_changeroom代碼： 10）， ， （select ） select = ） = = = = 7存儲過九 ：proc_cont_lodge 代碼： （， = = 存儲過十 ：proc_checkout_lodger代碼: （） ( , ，

19、） l。 ， ， （ ）（）， ）， ， ） day ， ） . ， ， ， ）r. l,room r = l。 = 。功能：退房并將旅客信息插進(jìn) checkout 表中 存儲過十一 :proc_dayaccount_checkout 代碼：create procedure proc_dayaccount_checkout cur_year int,cur_month int ，cur_day int as8insert DayAccount（cur_year,cur_month,cur_day，day_account)select cur_year，cur_month，cur_day,sum

20、(room_accountfrom checkoutwhere cur_year = cur_yearandcur_month = cur_monthandcur_day = cur_daygroup by cur_year，cur_month，cur_day功能：對 checkout 表中某一天的所有盈利進(jìn)行累加并將結(jié)果存入 dayaccount 中存儲過十二 ：proc_dayaccount代碼： ， , , ， ， = 存儲過十三 :proc_query_monthaccount 代碼: , ， ， 功能:查詢月賬單存儲過十四：代碼： ， 功能:查詢年盈利情況 存儲過十五： proc_r

21、emind 代碼：9 lodger_name )， ， ) （getdate(）功能:將住宿在當(dāng)天要到期的旅客的姓名和其他相關(guān)信息放入每日提醒中以便對 旅客進(jìn)行提醒存儲過十六： proc_room_remind代碼： （ day，（）)功能:將到期旅客所住房間的狀態(tài)標(biāo)記free這間房在當(dāng)天的下午就可 以被租出去了,這樣的功能提高了房間的利用率執(zhí)行前：存儲過程十 :proc_discount代碼： (） = = 功能：為某些符合條件的旅客進(jìn)行打折 代碼：create proc InitRoomasupdate roomset room_state freewhere room_num in （s

22、elect room_numfrom lodger ）10update roomset room_state busywhere room_num （select room_numfrom lodger)update roomset room_type = where room_type 1update roomset room_type = where room_type 2update roomset room_type = where room_type 3功能：規(guī)范化 room存儲過程十 : ， （ ） = 功能：統(tǒng)計(jì)目前空房數(shù),照房間類型來區(qū)分存過二： 代碼： ，, = 功能:計(jì)某月

23、客戶的退房情況觸發(fā)器實(shí)踐1。Tigger1:trigger1_room_lodger11代碼 = = 功能:當(dāng)向 lodger 中插入新的數(shù)據(jù)（即有新旅客住進(jìn)來么將相應(yīng)的房間的狀態(tài) 修改為 busy。說明在向 lodger 中插入后，在觸發(fā)器的作用下， 的狀態(tài)發(fā)生了改變。2。Trigger2:代碼: = = 功能說明：如果向 room_prebook 中插入訂房旅客的信息，那么相關(guān)的房間的 IsBooked 狀態(tài)將會被標(biāo)記為 booked3。Trigger3 trigger3_checkout on checkout ）-修改狀態(tài) = i c。 12 -修改 狀態(tài) = 。 c。 as ， i i.= 。 = = 功能：如果 checkout 表有數(shù)據(jù)插入，觸發(fā)下列兩項(xiàng)動(dòng)作(1) 將旅客所住房間在 room 中的 room_state 記為Free(2) 將該旅客信息從 lodger 中刪除4.Trigger4 ：tigger4_dayaccount_checkout代碼： = = + ( ） （ ）= （ ）功能：這是旅店的日結(jié)系統(tǒng)，首先通過定制作業(yè)每個(gè)一天向 中插入 一個(gè)元組 day_account 為 即對 dayaccount 每天的結(jié)算元組進(jìn)行初始化， 如果有向 checkout 中插入元組,那么將這筆 ro