數(shù)據(jù)庫課程設(shè)計(jì)客房管理系統(tǒng)設(shè)計(jì)

1、目 錄1 引言12 數(shù)據(jù)庫設(shè)計(jì)12.1 需求設(shè)計(jì)12.1.1 總體分析12.12 業(yè)務(wù)流程圖22.1.3 數(shù)據(jù)流圖32.1.4 數(shù)據(jù)字典42.2 概念設(shè)計(jì)72.2.1 分e-r圖72.2.2 總e-r圖82.3 邏輯設(shè)計(jì)82.3.1 e-r 圖向關(guān)系模型轉(zhuǎn)換82.3.2 數(shù)據(jù)模型的優(yōu)化92.3.3 設(shè)計(jì)用戶子模式視圖92.4 物理設(shè)計(jì)102.4.1 建立觸發(fā)器102.4.2 建立存儲過程102.5 數(shù)據(jù)庫實(shí)施階段112.5.1 建立數(shù)據(jù)庫112.5.2 建立數(shù)據(jù)表112.5.3 數(shù)據(jù)庫入庫122.6 調(diào)試與測試142.6.1 觸發(fā)器定義142.6.2 視圖的定義152.6.3 存儲過程定義17

2、2.6.4 數(shù)據(jù)操縱驗(yàn)證183 收獲與體會184 存在的問題與建議184.1 存在問題184.2 建議195.附錄191 引言本次數(shù)據(jù)庫實(shí)習(xí)不要求界面和外部應(yīng)用程序的設(shè)計(jì)，只設(shè)計(jì)數(shù)據(jù)庫部分?？头抗芾硐到y(tǒng)采用sql server 2000作為數(shù)據(jù)庫后臺?？头抗芾硐到y(tǒng)可以實(shí)現(xiàn)對客房的科學(xué)化、規(guī)范化、查詢顧客情況、訂房信息、以及結(jié)算信息等。有助于提高管理的效率，客房管理系統(tǒng)是根據(jù)某賓館對客房的實(shí)際情況進(jìn)行集中的查詢與管理工作，以及提高整個客房管理的工作效率。 2 數(shù)據(jù)庫設(shè)計(jì)2.1 需求設(shè)計(jì)2.1.1 總體分析建立客房管理信息系統(tǒng)的必要性已被廣大的管理人員所接受和認(rèn)可，建立客房管理信息系統(tǒng)是為賓館的管

3、理服務(wù)的，其根本米的是要創(chuàng)造客房的經(jīng)濟(jì)效益。然而客房知道接的經(jīng)濟(jì)效益的提高應(yīng)該包括直接的經(jīng)濟(jì)效益和間接地經(jīng)濟(jì)效益兩方面?？头康闹苯咏?jīng)濟(jì)效益是指由于系統(tǒng)的建立是客房的生產(chǎn)經(jīng)營或客房的管理取得的直接利潤收益。根據(jù)一般賓館的工作流程及環(huán)境，以及顧客的要求設(shè)計(jì)了這個客房管理系統(tǒng)。這個數(shù)據(jù)庫主要完成房間信息查詢，住房信息統(tǒng)計(jì)，和賬務(wù)結(jié)算。其中信息查詢用來給顧客查詢各種房間類型，房間價格等，住房信息統(tǒng)計(jì)用來給客房管理人員查詢是否有空房間，有多少空房，哪個顧客需要結(jié)賬了，等等信息管理，還可以根據(jù)數(shù)據(jù)庫儲存的資料統(tǒng)計(jì)什么樣的人訂房多，什么時段生意好，哪些服務(wù)是大多數(shù)顧客所喜愛的等等?；谶@個數(shù)據(jù)庫，可以根據(jù)不

4、同的需要對數(shù)據(jù)庫進(jìn)不同的查詢。2.12 業(yè)務(wù)流程圖數(shù)據(jù)文件存檔文檔業(yè)務(wù)處理功能根據(jù)了解到的情況，了解到了顧客住房的基本流程，根據(jù)客房管理的基本流程，從業(yè)務(wù)流程圖基本的功能模塊分別用下面圖形表示：圖2-1 業(yè)務(wù)流程圖業(yè)務(wù)處理單位/個人根據(jù)賓館實(shí)際管理過程和各種操作，由了解到的業(yè)務(wù)畫出業(yè)務(wù)流程圖，本系統(tǒng)的業(yè)務(wù)流程圖如下所示：圖 2-2 第一層數(shù)據(jù)流圖2.1.3 數(shù)據(jù)流圖2.1.4 數(shù)據(jù)字典數(shù)據(jù)流圖表達(dá)了數(shù)據(jù)和處理的關(guān)系，數(shù)據(jù)字典則是系統(tǒng)中各類數(shù)據(jù)描述的集合，是進(jìn)行詳細(xì)的數(shù)據(jù)收集和數(shù)據(jù)分析所獲得的主要成果。數(shù)據(jù)字典在數(shù)據(jù)庫設(shè)計(jì)中占有很重要的地位。數(shù)據(jù)字典通常包括數(shù)據(jù)項(xiàng)、數(shù)據(jù)結(jié)構(gòu)、數(shù)據(jù)流、數(shù)據(jù)存儲和處

5、理過程5個部分。其中數(shù)據(jù)項(xiàng)是數(shù)據(jù)的最小組成單位，若干個數(shù)據(jù)項(xiàng)可以組成一個數(shù)據(jù)結(jié)構(gòu)，數(shù)據(jù)字典通過對數(shù)據(jù)項(xiàng)和數(shù)據(jù)結(jié)構(gòu)的定義來描述數(shù)據(jù)流、數(shù)據(jù)存儲的邏輯內(nèi)容。 數(shù)據(jù)項(xiàng)定義數(shù)據(jù)項(xiàng)是不可在分的數(shù)據(jù)單位。下面定義了客房管理系統(tǒng)需要的數(shù)據(jù)項(xiàng)：表2.1 數(shù)據(jù)項(xiàng)定義數(shù)據(jù)項(xiàng)編號數(shù)據(jù)項(xiàng)名稱簡述類型及寬度i1rtype描述房間的不同類型char（10）i2rno表示房間的號碼char(20)i3rstatus房間的狀態(tài)（是否有人?。ヽhar（4）i4rprice描述房間的價格inti5cno顧客編號char(10)i6cname顧客姓名char(10)i7cage顧客年齡inti8csex顧客性別cha

6、r(2)i19idno顧客身份證號碼char(18)i110date入住時間datetimei11time顧客訂房天數(shù)inti12sname服務(wù)名稱char(10)i13samount服務(wù)次數(shù)（個數(shù)）inti14sprice每次（個）服務(wù)單價float 數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)結(jié)構(gòu)反映了數(shù)據(jù)之間的組合關(guān)系，。一個數(shù)據(jù)結(jié)構(gòu)可以由若干個數(shù)據(jù)項(xiàng)組成，也可以由若干個數(shù)據(jù)結(jié)構(gòu)組成，或由若干個數(shù)據(jù)項(xiàng)和數(shù)據(jù)結(jié)構(gòu)混合組成。對數(shù)據(jù)結(jié)構(gòu)的描述通常包括數(shù)據(jù)結(jié)構(gòu)名、含義等。表2-2 數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)結(jié)構(gòu)定義數(shù)據(jù)結(jié)構(gòu)名數(shù)據(jù)結(jié)構(gòu)含義組成ds-1customer入住客人信息cno，cname，csex，cage,idnods

7、-2room房間信息rtype，rno,rprice,rstatusds-3information入住登記表cname,rno,date,time,rtype,idnods-4servies服務(wù)清單rno，sname，samount，spriceds-5menu服務(wù)菜單sname，samount，sprice 數(shù)據(jù)流數(shù)據(jù)流是數(shù)據(jù)結(jié)構(gòu)在系統(tǒng)內(nèi)傳輸?shù)穆窂?。對?shù)據(jù)流的描述通常包括以下內(nèi)容:數(shù)據(jù)流名、說明、數(shù)據(jù)流來源、數(shù)據(jù)流去向、組成等。表2-3 數(shù)據(jù)流數(shù)據(jù)流編號數(shù)據(jù)流名稱簡述數(shù)據(jù)流來源數(shù)據(jù)流去向數(shù)據(jù)流組成f1房間信息表供用戶查詢房間信息，選擇房間類型前臺服務(wù)部顧客rtype，rno，r

8、floor,rprice，rstatusf2發(fā)票 收費(fèi)憑證前臺服務(wù)部顧客房間類型，房間價格，發(fā)票編號，日期f3入住登記表登記訂房客人的信息及房間信息前臺服務(wù)部顧客，后勤服務(wù)部cname,cage,csex,cno,rno,date,timeidno，rtypef4房卡作為訂房、退房憑證，開房門前臺顧客rno，rfloorf5 服務(wù)菜單供用戶選擇相應(yīng)服務(wù)后勤服務(wù)部顧客sname，samount，spricef6服務(wù)清單客人選擇的服務(wù)的信息顧客后勤服務(wù)部rno，sname，samount，spricef7退房信息某顧客退房登記的信息前臺服務(wù)部存盤cname,rno,date,time

9、 數(shù)據(jù)存儲數(shù)據(jù)存儲是數(shù)據(jù)結(jié)構(gòu)停留或保存的地方，也是數(shù)據(jù)流的來源和去向之一。他可以是手工文檔或手工憑單，也可以是計(jì)算機(jī)文檔。表 2-4 數(shù)據(jù)存儲數(shù)據(jù)存儲編號數(shù)據(jù)存儲名稱簡述數(shù)據(jù)存儲組成相關(guān)處理s1入住信息數(shù)據(jù)記錄住宿信息，包括房間信息和客人信息cname,rno,date,timep1，p2，p3，p4s2登記記錄記錄顧客訂房的信息cname,rno,date,timeidno，rtypep 處理過程處理過程的具體處理邏輯一般是用判定表或判定樹來描述。數(shù)據(jù)字典中只需要描述處理過程的說明信息，通常包括處理過程編號、名稱、說明、輸入數(shù)據(jù)流、輸出數(shù)據(jù)流、處理等。表 2-5 處理過程處理

10、過程編號處理過程名稱簡述輸入的數(shù)據(jù)流處理輸出地?cái)?shù)據(jù)流p1更新住房信息有顧客后，要及時更新住房信息入住登記表對住房信息數(shù)據(jù)進(jìn)行更新操作新的住房信息p2選擇房間顧客更具需要選擇房間類型房間信息表選擇需要的房間類型房卡、發(fā)票p3對該顧客進(jìn)行相應(yīng)服務(wù)顧客選擇服務(wù)，對顧客進(jìn)行服務(wù)服務(wù)菜單按照顧客所選服務(wù)提供服務(wù)服務(wù)清單p4退房顧客離開時辦理退房結(jié)算手續(xù)入住登記表辦理退房結(jié)算手續(xù)退房信息2.2 概念設(shè)計(jì)將需求分析得到的用戶需求抽象為信息結(jié)構(gòu)即概念模型的過程就是概念結(jié)構(gòu)設(shè)計(jì)。它是整個數(shù)據(jù)庫設(shè)計(jì)的關(guān)鍵。2.2.1 分e-r圖圖 2-3 各部分分e-r圖面對每一個局部應(yīng)用主義設(shè)計(jì)分e-r圖：2.2.2 總e-r

11、圖各子系統(tǒng)的e-r圖設(shè)計(jì)好以后，下一步就是要將所有的分e-r圖綜合成一個系統(tǒng)的總e-r圖。多個分e-r圖一次集成總e-r圖，如下圖所示：圖 24 總e-r 圖在消除各種沖突和不必要的冗余之后，基本e-r圖就生成了，下一步就是根據(jù)概念結(jié)構(gòu)設(shè)計(jì)結(jié)果來進(jìn)行邏輯結(jié)構(gòu)設(shè)計(jì)了。2.3 邏輯設(shè)計(jì)概念結(jié)構(gòu)是獨(dú)立于任何一種數(shù)據(jù)模型的信息結(jié)構(gòu)。邏輯結(jié)構(gòu)設(shè)計(jì)的任務(wù)就是把概念結(jié)構(gòu)設(shè)計(jì)階段設(shè)計(jì)好的基本e-r圖轉(zhuǎn)換為與所選用的dbms產(chǎn)品所支持的數(shù)據(jù)模型想符合的邏輯結(jié)構(gòu)。2.3.1 e-r 圖向關(guān)系模型轉(zhuǎn)換把總e-r圖轉(zhuǎn)換成為關(guān)系模式，關(guān)系的碼用下劃線標(biāo)出。顧客（顧客編號，顧客姓名，顧客身份證號，顧客性別，顧客年齡）此為

12、顧客實(shí)體所對應(yīng)的關(guān)系模式。房間（房間編號，房間類型，房價狀態(tài)，房間單價）此為房間實(shí)體所對應(yīng)的關(guān)系模式。入住登記表（顧客姓名，房間號碼，顧客身份證號，入住日期，訂房天數(shù)，）此為入住登記表所對應(yīng)的關(guān)系模式，該關(guān)系模式已包含了聯(lián)系“登記”所對應(yīng)的關(guān)系模式。服務(wù)菜單（服務(wù)名稱，服務(wù)次數(shù)，服務(wù)單價）此為服務(wù)菜單實(shí)體對應(yīng)的關(guān)系模式。服務(wù)清單（房間編號，服務(wù)名稱，服務(wù)次數(shù)，服務(wù)單價）此為服務(wù)清單所對應(yīng)的關(guān)系模式，該關(guān)系模式已包含了聯(lián)系“選擇”所對應(yīng)的關(guān)系模式。2.3.2 數(shù)據(jù)模型的優(yōu)化數(shù)據(jù)邏輯設(shè)計(jì)的結(jié)果不是唯一的。為了進(jìn)一步提高數(shù)據(jù)庫應(yīng)用系統(tǒng)的性能，還應(yīng)該分局應(yīng)用系統(tǒng)的需要適當(dāng)?shù)男薷?，調(diào)整數(shù)據(jù)模型的結(jié)構(gòu)，這

13、就是數(shù)據(jù)模型的優(yōu)化。關(guān)系數(shù)據(jù)庫模型的優(yōu)化通常以規(guī)范化理論為指導(dǎo)，方法為：1. 確定數(shù)據(jù)依賴。2. 對于各個關(guān)系模式之間的數(shù)據(jù)依賴進(jìn)行極小化處理，消除冗余的聯(lián)系。3. 按照數(shù)據(jù)依賴的理論對關(guān)系模式逐一進(jìn)行分析，考察是否存在部分函數(shù)依賴、傳遞函數(shù)依賴、多值依賴等，確定個關(guān)系模式分別屬于第幾范式。4. 按照需求分析階段得到的處理要求，分析對于這樣的應(yīng)用環(huán)境這些模式是否合適，確定是否要求對某些模式進(jìn)行合并或分解。5. 對關(guān)系模式進(jìn)行必要的分解，提高數(shù)據(jù)操作的效率和存儲空間利用率。常用的兩種分解發(fā)事水平分解法和垂直分解法。規(guī)范化理論為數(shù)據(jù)庫設(shè)計(jì)人員判斷關(guān)系模式優(yōu)劣提供了理論標(biāo)準(zhǔn)，可以來預(yù)測模式可能出現(xiàn)的

14、問題，使數(shù)據(jù)庫設(shè)計(jì)工作有了嚴(yán)格的理論基礎(chǔ)。2.3.3 設(shè)計(jì)用戶子模式視圖將概念模型轉(zhuǎn)換為全局邏輯模型之后，還應(yīng)該根據(jù)局部應(yīng)用需求，結(jié)合具體dbms的特點(diǎn)，設(shè)計(jì)用戶的外模式。目前關(guān)系數(shù)據(jù)庫管理系統(tǒng)一般都提供了視圖（view）概念，可利用這一功能設(shè)計(jì)更符合局部用戶需要的用戶外模式。對于本系統(tǒng)，綜合顧客和管理者的角度，設(shè)計(jì)了如下視圖：對于顧客，只需要建立狀態(tài)為空的房間信息的視圖：1. 建立空房間的視圖，方便顧客查詢和選擇房間?？辗块g（房間編號，房間類型，房間單價）對于管理者，則要建立如下幾個視圖：1.建立滿房間視圖，方便管理者了解入住客人的信息滿房間（房間編號，房間類型，房間單價）2.建立所有顧客應(yīng)

15、繳納房間費(fèi)用的視圖，方便管理者預(yù)算房間收入房間費(fèi)用（顧客姓名，房間費(fèi)用）3.建立所有顧客應(yīng)繳納服務(wù)費(fèi)用的視圖，方便預(yù)算服務(wù)收入，且可以根據(jù)統(tǒng)計(jì)數(shù)據(jù)來分析那種服務(wù)受歡迎，從而增加和推廣相應(yīng)服務(wù)服務(wù)費(fèi)用（房間編號，服務(wù)綜費(fèi)用）4.建立所有顧客應(yīng)繳納的總費(fèi)用的視圖，方便結(jié)算總費(fèi)用（房間編號，總費(fèi)用）2.4 物理設(shè)計(jì)數(shù)據(jù)庫在物理設(shè)備上的存儲結(jié)構(gòu)與存取方法稱為數(shù)據(jù)庫的物理結(jié)構(gòu)，它依賴于選定的數(shù)據(jù)庫管理系統(tǒng)。為一個給定的邏輯數(shù)據(jù)模型選取一個最適合應(yīng)用要求的物理結(jié)構(gòu)的過程，就是數(shù)據(jù)庫的物理設(shè)計(jì)。2.4.1 建立觸發(fā)器這個系統(tǒng)為數(shù)據(jù)表的更新建立了兩個觸發(fā)器，一個是在有新的客人入住時，插入客人入住信息，觸發(fā)觸發(fā)

16、器，使房間信息得到更新。另外就是在有客人退房結(jié)賬之后，使觸發(fā)器觸發(fā)，更新房間信息表。表 2-6 定義觸發(fā)器觸發(fā)器名觸發(fā)器的作用view_delete在顧客退房之后，觸發(fā)器觸發(fā)，刪除住房登記表中的入住記錄in_status用來在插入了一條顧客訂房紀(jì)錄之后觸發(fā)，使該房間狀態(tài)由空更新為滿out_status用來在插入了一條顧客退房記錄之后觸發(fā)，使該房間狀態(tài)由滿更新為空2.4.2 建立存儲過程存儲過程是存儲在服務(wù)器端由一組編譯的transactsql語句構(gòu)成的sql sever數(shù)據(jù)庫應(yīng)用程序，它能夠檢索系統(tǒng)信息、管理數(shù)據(jù)庫或執(zhí)行其他操作。為了方便操作，為本系統(tǒng)建立了如下存儲過程：表2-7 存儲過程存儲

17、過程編號存儲過程名稱存儲過程作用p1roominf查詢某個房間的信息p2priceinf查詢某個房間的價格p3statusinf查詢某個房間的狀態(tài)p4menuinf查詢有哪些服務(wù)p5updateinf更新房間信息2.5 數(shù)據(jù)庫實(shí)施階段2.5.1 建立數(shù)據(jù)庫用sql語句建立數(shù)據(jù)庫，具體定義如下：create database hotel2.5.2 建立數(shù)據(jù)表用sql語句建立數(shù)據(jù)表，具體定義如下：create table customer(cno char(10), cname char(10), cage int, csex char(2), idno char(18),primary key(

18、cno) )create table room(rno char(10), rtype char(10), rstatus char(4), rprice int, primary key(rno) )create table information(cname char(10), rno char(10), idno char(18), thedate datetime, days int primary key(cname,rno)create table checkout(cno char(10), rno char(10),primary key(cno)create table me

19、nu(sname char(10), sprice int,primary key(sname)create table service(rno char(10), sname char(10), samount int, primary key(rno,sname) )2.5.3 數(shù)據(jù)庫入庫本次數(shù)據(jù)采用excel表導(dǎo)入的方法導(dǎo)入數(shù)據(jù)。數(shù)據(jù)導(dǎo)入后基本表如下所示：1. 基本表customer圖 2-5 表customer2. 基本表 room。圖2-7 基本表 menu圖 2-6 基本表 room3. 基本表 menu。表2-8 基本表information4. 基本表 information。

20、圖2-9 基本表service5. 基本表 service圖2-10 基本表6. 基本表checkout2.6 調(diào)試與測試2.6.1 觸發(fā)器定義定義房間信息在有人入住之后更新的觸發(fā)器create trigger in_statuson informationafter insert,updateasdeclare rno char(10)select rno = rno from insertedupdate roomset rstatus = 滿where room.rno = rno定義房間信息在有人退房之后更新的觸發(fā)器create trigger out_statuson checkou

21、tafter insert,updateasdeclare rno char(10)select rno = rnofrom insertedupdate roomset rstatus = 空where room.rno = rno圖2-11 空房間信息視圖2.6.2 視圖的定義建立空房間信息視圖create view view_eroomas select *from roomwhere rstatus = 空建立滿房間視圖圖2-12 滿房間視圖create view view_froomas select *from roomwhere rstatus = 滿建立客房所有顧客應(yīng)繳納的房間

22、費(fèi)用視圖create view view_roomcost(cname,rcost)as圖2-13 房間費(fèi)用視圖select information.cname,rprice*daysfrom room,informationwhere room.rno in(select rnofrom information) and information.rno=room.rno建立所有顧客應(yīng)繳納服務(wù)費(fèi)用的視圖2-14 服務(wù)費(fèi)用視圖create view view_servicecost(rno,cost)asselect service.rno,sum(samount*sprice) from me

23、nu,servicewhere menu.sname=service.snamegroup by service.rno建立顧客應(yīng)繳納的總費(fèi)用的視圖create view view_allcost(rno,allcost)asselect view_servicecost.rno,cost+rprice*daysfrom view_servicecost,room,information圖2-15 總費(fèi)用視圖where view_servicecost.rno=room.rno and room.rno = information.rno2.6.3 存儲過程定義建立查詢某個房間信息的存儲過程c

24、reate procedure roominfrno char(10)asselect * from roomwhere rno = rnoroominf 101建立查詢某個房間價格的信息的存儲過程create procedure priceinfprno char(10)asselect rpricefrom roomwhere rno = prnopriceinf 101建立某個房間狀態(tài)的存儲過程create procedure statusinfsrno char(10)asselect rstatusfrom roomwhere rno= srno某個客人入住，建立存儲過程更新房間信息create procedure updateinfp_cname char(10),p_rno char(10),p_idno char (18),p_thedate datetime,p_days intasinsert into informationvalues(p_cname,p_rno,p_idno,p_thedate