數(shù)據(jù)庫課程設計餐飲下單管理系統(tǒng)

目錄TOC\o"1-3"\h\u1.課題名稱及目標 12.需求分析 12.1數(shù)據(jù)需求 12.2事務需求 22.3數(shù)據(jù)流 32.4數(shù)據(jù)字典 53.數(shù)據(jù)庫結(jié)構(gòu)設計 83.1概念設計 8 8 93.2邏輯設計 103.2.1E-R圖向關(guān)系模型的轉(zhuǎn)換 10 113.2.3用戶子模式建立 124.數(shù)據(jù)庫物理設計 124.1存儲過程 134.2觸發(fā)器 135.數(shù)據(jù)庫的實施 135.1在SQL中數(shù)據(jù)庫實施 13 13 155.2數(shù)據(jù)庫測試 15 156.總結(jié) 297.附錄 301.課題名稱及目標本次實訓要設計的數(shù)據(jù)庫課題名稱是餐廳管理系統(tǒng)。餐廳管理系統(tǒng)的功能主要包括顧客點菜、結(jié)賬、對員工資料進行管理、對餐桌信息進行統(tǒng)一設置、對消費情況進行查詢等功能?；竟δ苋缦拢海?）菜譜信息管理：對菜譜進行增、查、改、刪操作。即可查詢當前餐廳所推出的菜品，增加菜品，修改菜品的價格，刪除菜品。（2）餐桌信息管理：能查詢當前餐廳中所有餐桌的座位數(shù)以及使用情況，客人可以對位實現(xiàn)的餐桌進行使用申請。（3）顧客信息管理：可以查詢顧客的相關(guān)信息，如性別，年齡，聯(lián)系方式等。當有新顧客時，可添加新顧客的信息。（4）顧客下訂單功能：實現(xiàn)下訂單，具有唯一的訂單號，可用于結(jié)賬?？筛鶕?jù)顧客的人數(shù)選擇相應的餐桌號，并且能夠指定服務員。（6）顧客點菜：實現(xiàn)顧客點菜功能。顧客可以對所下的訂單進行修改，即可刪除所點的菜品或是修改數(shù)量。同時可以查詢某位顧客的訂單。（7）顧客結(jié)賬：根據(jù)訂單號進行結(jié)賬（防止有相同姓名的顧客，以致結(jié)錯帳）。能夠根據(jù)消費金額進行對應的折扣，并把消費信息存檔到消費記錄中去。2.需求分析通過了解餐飲行業(yè)的特點和實際情況，從分析飯店的基本情況入手，結(jié)合要實現(xiàn)的功能，對系統(tǒng)的可行性進行分析，為提高其可行性，故做了以下數(shù)據(jù)分析。2.1數(shù)據(jù)需求餐桌數(shù)據(jù)包括餐桌號、座位數(shù)、狀態(tài)（空/有人）餐廳存儲有顧客信息，員工信息。菜品信息有菜品編號、名稱、類別、價格等具體數(shù)據(jù)需求如下：◎下訂單階段需要的數(shù)據(jù)顧客信息：包括顧客編號。訂單信息：包括訂單編號、消費時間。餐桌信息：包括編號、可容人數(shù)、使用狀態(tài)等員工信息：包括員工工號?！螯c菜階段需要的數(shù)據(jù)訂單信息：訂單編號菜品信息：包括菜品編號、菜品名稱、菜品數(shù)量等?！蚪Y(jié)賬階段需要的數(shù)據(jù)：訂單信息：包括訂單編號、顧客編號菜品信息：包括菜品編號、菜品名稱、菜品數(shù)量等。員工信息：包括員工工號折扣信息：包括消費金額、折扣數(shù)◎員工管理需要的數(shù)據(jù)：員工檔案：包括工號、姓名、性別、工資◎顧客管理需要的數(shù)據(jù)：顧客檔案：包括編號、姓名、性別等?！蛳M記錄管理需要的數(shù)據(jù)有：消費信息：訂單編號、消費金額、折扣后金額、結(jié)賬時間2.2事務需求數(shù)據(jù)錄入錄入顧客信息錄入餐桌信息錄入員工信息錄入菜品信息數(shù)據(jù)更新/刪除更新/刪除餐桌信息更新/刪除菜譜菜品信息更新/刪除員工信息更新/刪除顧客信息更新/刪除訂單菜品信息數(shù)據(jù)查詢查詢可用餐桌信息查詢在售菜品信息查詢訂單信息查詢顧客點菜信息查詢員工信息查詢顧客消費信息2.3數(shù)據(jù)流（1）頂層數(shù)據(jù)流P1訂單處理E2P1訂單處理E2服務員E1顧客負責F1F1變更信息第一層數(shù)據(jù)流P2.2點菜處理F2.1折扣P2.2點菜處理F2.1折扣P2.1P2.1直接下單E2.1顧客E2.3服務員P2.3結(jié)賬E2.2訂單E2.1顧客E2.3服務員P2.3結(jié)賬E2.2訂單(3)第二層數(shù)據(jù)流點菜業(yè)務申請變更P3.1.2點菜處理反饋變更P3.1.2點菜處理查看菜譜P3.1.5判斷變更方式查看菜譜P3.1.5判斷變更方式P3.1.4變更處理加菜、減菜P3.1.4變更處理P3.1.1P3.1.1直接下單F3.1.2存儲賬單E3.1.2訂單E3.1.1顧客F3.1.2存儲賬單E3.1.2訂單E3.1.1顧客P3.1.3后臺處理P3.1.3后臺處理F3.1.1存儲訂單點菜業(yè)務2.4數(shù)據(jù)字典數(shù)據(jù)項Foodtable(餐桌)數(shù)據(jù)項屬性存儲代碼類型長度餐桌號Foodtablenovarchar10座位數(shù)seatingnoint使用狀態(tài)Foodtablestatevarchar10Menus（菜譜）數(shù)據(jù)項屬性存儲代碼類型長度編號Dishnovarchar10名稱Dishnamevarchar10類別Dishclassvarchar10價格DishpricefloatWorker（員工）數(shù)據(jù)項屬性存儲代碼類型長度工號Workernovarchar10姓名Workernamevarchar10性別Workersexvarchar4年齡Workerageint工資WorkersalaryintCustomer（顧客）數(shù)據(jù)項屬性存儲代碼類型長度編號Customernovarchar10姓名Customernamevarchar10性別Customersexvarchar4聯(lián)系方式Customerphonenovarchar20Oder（訂單）數(shù)據(jù)項屬性存儲代碼類型長度訂單編號Odernovarchar10顧客編號Customernovarchar10消費時間consumetimedatetime餐桌編號Foodtablenovarchar10服務員編號Workernovarchar10Menus_Oder（點菜）數(shù)據(jù)項屬性存儲代碼類型長度訂單編號Odernovarchar10菜品編號Dishnovarchar10菜品名稱Disnnamevarchar10菜品數(shù)量Dish_amountintDiscount_rules（折扣規(guī)則）數(shù)據(jù)項屬性存儲代碼類型長度消費金額Consumptionfloat折扣DiscountfloatSales_bill（消費賬單）數(shù)據(jù)項屬性存儲代碼類型長度訂單編號Odernovarchar10消費金額Consumptionfloat折扣后金額after_discountfloat賬單時間Billtimeint數(shù)據(jù)結(jié)構(gòu)編號數(shù)據(jù)結(jié)構(gòu)名屬性1餐桌信息餐桌號、座位數(shù)、使用狀態(tài)2菜譜編號、名稱、類別、價格3員工信息工號、姓名、性別、年齡、工資4顧客信息編號、姓名、性別、聯(lián)系方式5訂單信息訂單編號、顧客編號、消費時間、餐桌編號、服務員編號6點菜訂單編號、菜品編號、菜品名稱、菜品數(shù)量7折扣規(guī)則消費金額、折扣8消費賬單訂單編號、消費金額、折扣后金額、賬單時間數(shù)據(jù)流編號數(shù)據(jù)流名輸入輸出1菜品信息修改菜品菜譜2空閑餐桌選擇餐桌訂單3點菜信息加菜退菜點菜4記入賬單訂單結(jié)賬5結(jié)賬折扣方式結(jié)賬數(shù)據(jù)存儲編號數(shù)據(jù)存儲名輸入輸出1顧客信息更新顧客新信息顧客信息2菜譜更新新增菜品菜譜3訂單儲存訂單訂單信息4餐桌更新新增餐桌餐桌5賬單存儲消費信息賬單記錄處理過程編號處理過程名輸入數(shù)據(jù)流輸出數(shù)據(jù)流1顧客點菜菜譜訂單2結(jié)賬訂單賬單記錄3.數(shù)據(jù)庫結(jié)構(gòu)設計3.1概念設計訂單形成過程1n111nnn1n負責包含菜品選擇菜譜顧客查看生成訂單1n111nnn1n負責包含菜品選擇菜譜顧客查看生成訂單服務員

服務員

（2）結(jié)賬形成過程消費金額包含支付顧客訂單對應

消費金額包含支付顧客訂單對應折扣規(guī)則對應

折扣規(guī)則對應

111對應1111111對應1111111nnnnn工資對應員工折扣銷售記錄賬單支付保存位置餐桌負責訂單下單包含菜品選擇查看顧客菜譜3.2邏輯設計111對應1111111對應1111111nnnnn工資對應員工折扣銷售記錄賬單支付保存位置餐桌負責訂單下單包含菜品選擇查看顧客菜譜3.2.1E-R圖向關(guān)系模型的轉(zhuǎn)換轉(zhuǎn)換規(guī)則一個實體型轉(zhuǎn)換成一個關(guān)系模型。實體的屬性就是關(guān)系的屬性，實體的碼就是關(guān)系的碼。實體型間的聯(lián)系常有如下不同的情況：一個1:1聯(lián)系可以轉(zhuǎn)換為一個獨立的關(guān)系模式，也可以任意一端對應的關(guān)系模式合并。一個1：n聯(lián)系可以轉(zhuǎn)換成一個獨立的關(guān)系模式，也可以與n端對應的關(guān)系模式合并。一個m:n聯(lián)系轉(zhuǎn)換成一個關(guān)系模式。3或3個以上實體間的一個多聯(lián)系可以轉(zhuǎn)換成一個關(guān)系模式。具有相同碼的關(guān)系模式可合并。B.根據(jù)以上規(guī)則得到如下關(guān)系模型（下劃線的屬性為主碼）菜譜（菜品編號，菜品名稱，菜品類別，菜品價格）顧客（顧客編號，姓名，性別，年齡，聯(lián)系方式）餐桌（餐桌號，座位數(shù)，使用狀態(tài)）員工（工號，姓名，性別，年齡，工資）訂單（訂單編號，顧客編號，消費時間，餐桌編號，服務員編號）菜譜_訂單（訂單編號，菜品編號，菜品名稱，菜品數(shù)量）消費記錄（訂單號，消費金額，折扣后金額，賬單時間）折扣規(guī)則（消費金額，折扣）確定數(shù)據(jù)依賴。對各個關(guān)系模式間的數(shù)據(jù)依賴進行極小化分析，減小冗余。按照數(shù)據(jù)依賴的理論對關(guān)系模式進行分析，看是否存在部分函數(shù)依賴或函數(shù)傳遞或多值依賴等，確保各關(guān)系模式滿足第三范式。按照需求分析階段得到的處理要求，分析對于這樣的應用環(huán)境這些模式是否合適，確定是否要對某些模式進行合并或分解。對關(guān)系模式進行必要的分解，分析對于這樣的應用環(huán)境這些模式是否合適，確定是否要對某些模式進行合并或分解。3.2.3用戶子模式建立名稱描述插入新顧客信息若是新顧客，則插入顧客的信息，姓名，性別等下單就位顧客來餐，下訂單，分配餐桌點菜存儲顧客所點的菜品刪除菜品刪除顧客所點的某菜品修改菜品數(shù)量修改顧客所點的某菜品的數(shù)量查詢顧客點菜信息查詢顧客所點的菜品消費總額計算顧客本次消費的總額。顧客支付完賬單時，把消費信息存檔到消費記錄中結(jié)賬后更改餐桌狀態(tài)當顧客結(jié)賬后，把餐桌狀態(tài)改為“空”刪除菜譜中菜品刪除菜譜中存在的某菜品修改菜品價格修改菜譜中某菜品的價格向菜譜添加菜品向菜譜添加新菜品trig_discount觸發(fā)器，顧客進行結(jié)賬時，消費金額在不同的區(qū)間會有不同的折扣。trig_Sales_bill觸發(fā)器，當消費金額達到一定額數(shù)時，會送相應優(yōu)惠券trig_Menus觸發(fā)器，當向菜譜添加新菜品，若該菜品名稱含有“紅”字，會發(fā)出“此后生意必定紅紅火火”的祝福語。4.數(shù)據(jù)庫物理設計數(shù)據(jù)庫在物理設備上的存儲結(jié)構(gòu)與存取方法就是數(shù)據(jù)庫的物理結(jié)構(gòu)，它依賴于選定的數(shù)據(jù)庫管理系統(tǒng)，為一個給定的邏輯數(shù)據(jù)模型選取一個最合適的應用環(huán)境的物理結(jié)構(gòu)過程，就是數(shù)據(jù)庫的物理設計。這一階段主要任務時確定數(shù)據(jù)庫的物理結(jié)構(gòu)，并不斷的進行優(yōu)化處理，主要建立索引，觸發(fā)器，存儲過程。4.1存儲過程存儲過程可以實現(xiàn)數(shù)據(jù)庫操作的增刪改功能，在執(zhí)行時需要調(diào)用。御用像函數(shù)一樣，也可以有參數(shù)的參與。定義存儲過程提供了過程封裝的效果，執(zhí)行只需要調(diào)用，在更大程度上簡化了數(shù)據(jù)處理過程。4.2觸發(fā)器5.數(shù)據(jù)庫的實施5.1在SQL中數(shù)據(jù)庫實施createdatabaseRestaurant_Management建表createtableMenus--菜譜(Dishnovarchar(10)primarykey,Dishnamevarchar(10),Dishclassvarchar(10),Dishpricefloat,)createtableCustomer--顧客(Customernovarchar(10)primarykey,Customernamevarchar(10)notnull,Customersexvarchar(4)check(Customersex='男'orCustomersex='女')notnull,Customerphonenovarchar(20)uniquenotnull,)createtableFoodtable--餐桌(Foodtablenovarchar(10)primarykey,seatingnointnotnull,Foodtablestatevarchar(6)check(Foodtablestate='空'orFoodtablestate='有人'),)droptableOdercreatetableOder--訂單(Odernovarchar(10)primarykey,Customernovarchar(10)notnull,consumetimedatetimenotnull,Foodtablenovarchar(10)notnull,Workernovarchar(10)notnull,foreignkey(Customerno)referencesCustomer(Customerno),foreignkey(Foodtableno)referencesFoodtable(Foodtableno),foreignkey(Workerno)referencesWorker(Workerno),)createtableWorker--員工(Workernovarchar(10)primarykey,Workernamevarchar(10),Workersexvarchar(4)check(Workersex='男'orWorkersex='女'),Workerageint,Workersalaryint,)droptableMenus_OdercreatetableMenus_Oder--點菜(Odernovarchar(10),Dishnovarchar(10),Disnnamevarchar(10),Dish_amountintnotnull,Primarykey(Dishno,Oderno),foreignkey(Dishno)referencesMenus(Dishno),foreignkey(Oderno)referencesOder(Oderno),)droptableSales_billcreatetableSales_bill--銷售賬單(Odernovarchar(10)primarykey,Consumptionfloat,after_discountfloat,Billtimedatetime,foreignkey(Oderno)referencesOder(Oderno))droptableDiscount_rulescreatetableDiscount_rules--折扣規(guī)則(Consumptionfloatprimarykey,Discountfloat,)本系統(tǒng)采用將數(shù)據(jù)逐條錄入的方法。5.2數(shù)據(jù)庫測試（1）存儲過程插入新顧客信息dropprocedure插入新顧客信息createprocedure插入新顧客信息(@Customernovarchar(10),@Customernamevarchar(10),@Customersexvarchar(4),@Customerphonenovarchar(20))asinsertintoCustomervalues(@Customerno,@Customername,@Customersex,@Customerphoneno)select*fromCustomer驗證execute插入新顧客信息'112699','李陽','男',下單就位dropprocedure下單就位createprocedure下單就位(@Odernovarchar(10),@Customernovarchar(10),@consumetimedatetime,@Foodtablenovarchar(10),@Wokernovarchar(10))asbegintransaction--開始事務insertintoOdervalues(@Oderno,@Customerno,@consumetime,@Foodtableno,@Wokerno)if((selectFoodtablestatefromFoodtablewhereFoodtableno=@Foodtableno)<>'空')beginprint'該餐桌有人，請重選餐桌。'RollbacktranreturnendelsebeginupdateFoodtablesetFoodtablestate='有人'whereFoodtableno=@Foodtablenoprint'下單成功，請就位開始點菜'endcommittran驗證execute下單就位'O_001','112699','2013-12-26','TB_005','W_002'execute下單就位'O_002','112698','2013-12-26','TB_004','W_003'當餐桌有人時，則顯示成功下單時，則顯示查看下單情況select*fromOderselect*fromFoodtable菜譜的查詢select*fromMenus刪除菜譜中菜品createprocedure刪除菜譜中菜品@Dishnovarchar(10)asdeletefromMenusWhereDishno=@Dishnoselect*fromMenus驗證execute刪除菜譜中菜品'D_112'修改菜品價格createprocedure修改菜品價格(@Dishnovarchar(10),@Dishpricefloat)asupdateMenussetDishprice=@DishpriceWhereDishno=@Dishnoselect*fromMenus驗證execute修改菜品價格'D_113','30'向菜譜增加菜品createprocedure向菜譜增加菜品(@Dishnovarchar(10),@Dishnamevarchar(10),@Dishclassvarchar(10),@Dishpricefloat)asinsertintoMenusvalues(@Dishno,@Dishname,@Dishclass,@Dishprice)select*fromMenus驗證execute向菜譜增加菜品'D_112','時蔬','小炒類','20'點菜的增加、查詢、刪除、修改點菜createprocedure點菜(@Odernovarchar(10),@Dishnovarchar(10),@Disnnamevarchar(10),@Dish_amountint)asinsertintoMenus_Odervalues(@Oderno,@Dishno,@Disnname,@Dish_amount)select*fromMenus_OderwhereOderno=@Oderno驗證execute點菜'O_001','D_111','小炒河蝦','2'execute點菜'O_001','D_114','雞蛋炒芹菜','1'execute點菜'O_001','D_115','三鮮湯','3'查詢客戶點菜信息createprocedure查詢顧客點菜信息@Customernovarchar(10)asselectCustomerno=@Customerno,Menus.Dishno,Dishname,Dish_amountfromMenus,Menus_Oder,OderwhereOder.Customerno=@CustomernoandMenus.Dishno=Menus_Oder.DishnoandOder.Oderno=Menus_Oder.Oderno驗證execute查詢顧客點菜信息'112699'刪除菜品createprocedure刪除菜品(@Odernovarchar(10),@Dishnovarchar(10))asdeletefromMenus_OderWhereOderno=@OdernoandDishno=@Dishnoselect*fromMenus_OderwhereOderno=@Oderno驗證execute刪除菜品'O_001','D_111'修改菜品數(shù)量createprocedure修改菜品數(shù)量(@Odernovarchar(10),@Dishnovarchar(10),@Dish_amountint)asupdateMenus_OdersetDish_amount=@Dish_amountWhereOderno=@OdernoandDishno=@Dishnoselect*fromMenus_OderwhereOderno=@Oderno驗證execute修改菜品數(shù)量'O_001','D_111','3',計算總消費額DROPprocedure消費總額createprocedure消費總額(@Odernovarchar(10),@Consumptionfloatoutput,@discountfloatoutput)asselect@Consumption=SUM(Dishprice*Dish_amount)fromMenus_Oder,Menus,OderwhereMenus.Dishno=Menus_Oder.DishnoandOder.Oderno=Menus_Oder.OdernoandOder.Oderno=@Odernobegintran啟用事務ifexists(selectConsumptionfromDiscount_ruleswhereConsumption=@Consumption)--判斷Discount_rules中是否有對應折扣beginselect@discount=DiscountfromDiscount_ruleswhereConsumption=@ConsumptionendELSEbeginINSERTintoDiscount_rules(Consumption)VALUES(@Consumption)--將@Consumption插入到Discount_rulesselect@discount=DiscountfromDiscount_ruleswhereConsumption=@Consumption--獲取對應折扣endifexists(selectOdernofromSales_billwhereOderno=@Oderno)--判斷消費賬單是否已有該消費記錄beginprint'消費記錄已插入消費賬單,別重復插入'endelsebegininsertintoSales_bill(Oderno,Consumption,after_discount,Billtime)values(@Oderno,@Consumption,@Consumption*@discount,GETDATE())print'成功將消費記錄插入到消費賬單'endcommittran--提交事務驗證declare@Consumptionfloat,@discountfloat,@Odernovarchar(10)='O_001'execute消費總額'O_001',@Consumptionoutput,@discountoutputselectOderno,Customername,@Consumption總消費,@discount折扣fromOder,CustomerwhereCustomer.Customerno=Oder.CustomernoandOderno=@Oderno并把消費信息插入到消費記錄中若已經(jīng)結(jié)賬，則提示結(jié)賬后餐桌狀態(tài)createprocedure結(jié)賬后更改信息@Odernovarchar(10)asupdateFoodtable修改餐桌狀態(tài)setFoodtablestate='空'whereFoodtableno=(selectFoodtablenofromOderwhereOderno=@Oderno)驗證execute結(jié)賬后更改信息'O_001'對菜譜的操作查詢菜譜select*fromMenus刪除菜譜中菜品createprocedure刪除菜譜中菜品@Dishnovarchar(10)asdeletefromMenusWhereDishno=@Dishnoselect*fromMenus驗證execute刪除菜譜中菜品'D_112'執(zhí)行前：執(zhí)行后：修改菜品價格createprocedure修改菜品價格(@Dishnovarchar(10),@Dishpricefloat)asupdateMenussetDishprice=@DishpriceWhereDishno=@Dishnoselect*fromMenus驗證execute修改菜品價格'D_113','40'執(zhí)行后：向菜譜增加菜品createprocedure向菜譜增加菜品(@Dishnovarchar(10),@Dishnamevarchar(10),@Dishclassvarchar(10),@Dishpricefloat)asinsertintoMenusvalues(@Dishno,@Dishname,@Dishclass,@Dishprice)select*fromMenus驗證execute向菜譜增加菜品'D_112','時蔬','小炒類','20'執(zhí)行后：（2）觸發(fā)器折扣DROPtriggertrig_discountcreatetriggertrig_discountonDiscount_rulesforinsertasbegindeclare@expandfloatselect@expand=ConsumptionFrominsertedif(@expand<50)beginupdateDiscount_rulessetDiscount=1whereConsumption=@expandendelseif(@expand>=50and@expand<100)beginupdateDiscount_rulessetdiscount=0.95whereConsumption=@expandendelseif(@expand>=100and@expand<150)beginupdateDiscount_rulessetdiscount=0.90whereConsumption=@expandendelseif(@expand>=150and@expand<200)beginupdateDiscount_rulesset