數(shù)據(jù)庫設(shè)計報告-小型餐飲管理系統(tǒng).doc

第一章 設(shè)計任務(wù)與要求目的及意義：此小型餐飲業(yè)管理系統(tǒng)的設(shè)計主要是為了方便管理，對于各個進(jìn)出賬目，支出和收入的管理便于系統(tǒng)化，在每月匯總計算中分析運(yùn)營趨勢和餐館的發(fā)展方向進(jìn)行一定的規(guī)劃。通過各個月的盈利進(jìn)行對比，分析那種方式更適合餐館的有利運(yùn)營和更好地服務(wù)顧客。實現(xiàn)對餐館內(nèi)部各種管理的電子化、自動化，提高各個模塊之間的辦公效率，為高質(zhì)量餐館服務(wù)提供保證。任務(wù)：1.能夠?qū)崿F(xiàn)對該系統(tǒng)進(jìn)行管理的人員的權(quán)限限制；2.使餐館能夠及時并靈活的對菜品品種其價位等進(jìn)行管理；3.為餐館提供從客戶點餐到結(jié)算等一系列操作的服務(wù)，使之能簡單易行、方便、 清楚地進(jìn)行管理第二章 系統(tǒng)功能分析一功能需求：餐飲管理系統(tǒng)中主要包括對以下幾種管理：賬單管理，財務(wù)管理，訂餐管理，菜品管理，系統(tǒng)管理。（1）系統(tǒng)管理：系統(tǒng)管理包括用戶名和密碼，主要用于用戶登陸界面登陸和查詢。（2）賬單管理：賬單管理包括賬單號和餐臺，每一個餐臺對應(yīng)一個一個賬單號，通過餐臺號的記錄來對對應(yīng)的餐臺進(jìn)行記賬管理，即就是記錄每一個餐臺的消費(fèi)金額。賬單管理是財務(wù)管理的一個小分支，是服務(wù)于財務(wù)管理的。（3）財務(wù)管理：包括賬單號，時間和賬目。賬目用于記錄賬單號的消費(fèi)金額及時間，以便用于結(jié)算和匯總。每日的結(jié)算要通過對每個餐臺號的消費(fèi)金額匯總來記錄，然后由每日的結(jié)算匯總得出每月的結(jié)算。帳務(wù)系統(tǒng)功能的完整性。一旦該系統(tǒng)正式運(yùn)行，餐廳每日營業(yè)帳和全部往來客戶帳務(wù)的操作結(jié)算都將依靠計算機(jī)，該系統(tǒng)面對當(dāng)前餐飲業(yè)各種復(fù)雜的結(jié)算要求應(yīng)具有很強(qiáng)的應(yīng)變能力。（4）訂餐管理：訂餐管理僅包括單價。訂餐管理是便于顧客訂餐和賬單管理時對各個菜品消費(fèi)金額的記錄，這樣便于賬單管理和財務(wù)匯總。 （5）菜品管理：菜品管理包括菜名，菜品類別和菜品品種。菜品管理便于顧客點菜和記錄各個餐臺的消費(fèi)記賬，菜品品種也便于餐館的食材采購。二. 數(shù)據(jù)需求：賬單管理中賬單號是主鍵，每一個餐臺對應(yīng)一個賬單號。財務(wù)管理中賬單號是主鍵，每一個賬單號都有對應(yīng)的時間和賬目記錄。訂餐管理中單價就是主鍵。菜品管理中菜名是主鍵。系統(tǒng)管理中用戶名是主鍵。三. 性能需求：該餐飲系統(tǒng)操作簡單方便可適應(yīng)各類中小型餐館。第三章 系統(tǒng)功能模塊設(shè)計一. 在該系統(tǒng)功能分析的基礎(chǔ)上，考慮powerbuilder程序編制的特點，得到如下功能模塊圖。小型餐飲業(yè)管理系統(tǒng)系統(tǒng)管理賬單管理訂餐管理財務(wù)管理菜品管理用戶管理密碼管理賬單號管理餐臺管理訂單查詢餐臺點餐管理每日賬目管理賬目時間管理菜名管理菜品管理菜價管理類別管理圖1 系統(tǒng)功能模塊圖二. 數(shù)據(jù)庫設(shè)計1. 概念結(jié)構(gòu)設(shè)計2. 邏輯結(jié)構(gòu)設(shè)計3. 數(shù)據(jù)庫實施 1) 數(shù)據(jù)庫及表結(jié)構(gòu)的創(chuàng)建 設(shè)本系統(tǒng)使用的數(shù)據(jù)庫名為小型餐飲業(yè)管理系統(tǒng),根據(jù)已設(shè)計的關(guān)系模式及各模式的完整性要求，現(xiàn)在就可以在sql server 6.x數(shù)據(jù)庫系統(tǒng)中實現(xiàn)這些邏輯結(jié)構(gòu)。下面是創(chuàng)建基本表的sql語句：/* = */* table: caipingl */* = */create table caipingl( cpgl_name char(20) not null, cpgl_danjia integer not null, cpgl_leibie char(8) null , cpgl_pingzhong char(8) null , cpgl_diancai integer null , constraint pk_caipingl primary key (cpgl_name, cpgl_danjia)go/* = */* table: xtgl */* = */create table xtgl( username char(20) not null, password char(8) not null, constraint pk_xtgl primary key (username, password)go/* = */* table: zhangdagl */* = */create table zhangdagl( zhangdan_number numeric not null, zhangdan_cantai numeric null , cai_zhangdan_number numeric null , constraint pk_zhangdagl primary key (zhangdan_number)go/* = */* index: relation_383_fk */* = */create index relation_383_fk on zhangdagl (cai_zhangdan_number)go/* = */* table: caiwugl */* = */create table caiwugl( zhangdan_number numeric not null, caiwu_time timestamp null , caiwu_amount char(10) null , constraint pk_caiwugl primary key (zhangdan_number)go/* = */* table: dingcangl */* = */create table dingcangl( zhangdan_number numeric not null, dcgl_name char(20) not null, dcgl_danjia int not null, constraint pk_dingcangl primary key (zhangdan_number, dcgl_name, dcgl_danjia)go/* = */* index: relation_451_fk */* = */create index relation_451_fk on dingcangl (zhangdan_number)go建立相關(guān)觸發(fā)器，執(zhí)行級聯(lián)刪除* = */* database name: model_1 */* dbms name: microsoft sql server 6.x */* created on: 2010-1-21 18:15 */* = */* insert trigger ti_caiwugl for table caiwugl */create trigger ti_caiwugl on caiwugl for insert asbegin declare numrows int, numnull int, errno int, errmsg varchar(255) select numrows = rowcount if numrows = 0 return /* parent zhangdagl must exist when inserting a child in caiwugl */ if update(zhangdan_number) begin if (select count(*) from zhangdagl t1, inserted t2 where t1.zhangdan_number = t2.zhangdan_number) != numrows begin select errno = 30002, errmsg = parent does not exist in zhangdagl. cannot create child in caiwugl. goto error end end return/* errors handling */error: raiserror errno errmsg rollback transactionendgo/* update trigger tu_caiwugl for table caiwugl */create trigger tu_caiwugl on caiwugl for update asbegin declare numrows int, numnull int, errno int, errmsg varchar(255) select numrows = rowcount if numrows = 0 return /* parent zhangdagl must exist when updating a child in caiwugl */ if update(zhangdan_number) begin if (select count(*) from zhangdagl t1, inserted t2 where t1.zhangdan_number = t2.zhangdan_number) != numrows begin select errno = 30003, errmsg = zhangdagl does not exist. cannot modify child in caiwugl. goto error end end /* modify parent code of caiwugl for all children in zhangdagl */ if update(zhangdan_number) begin update zhangdagl set cai_zhangdan_number = i1.zhangdan_number from zhangdagl t2, inserted i1, deleted d1 where t2.cai_zhangdan_number = d1.zhangdan_number and (i1.zhangdan_number != d1.zhangdan_number) end return/* errors handling */error: raiserror errno errmsg rollback transactionendgo/* delete trigger td_caiwugl for table caiwugl */create trigger td_caiwugl on caiwugl for delete asbegin declare numrows int, errno int, errmsg varchar(255) select numrows = rowcount if numrows = 0 return /* delete all children in zhangdagl */ delete zhangdagl from zhangdagl t2, deleted t1 where t2.cai_zhangdan_number = t1.zhangdan_number return/* errors handling */error: raiserror errno errmsg rollback transactionendgo/* insert trigger ti_dingcangl for table dingcangl */create trigger ti_dingcangl on dingcangl for insert asbegin declare numrows int, numnull int, errno int, errmsg varchar(255) select numrows = rowcount if numrows = 0 return /* parent zhangdagl must exist when inserting a child in dingcangl */ if update(zhangdan_number) begin if (select count(*) from zhangdagl t1, inserted t2 where t1.zhangdan_number = t2.zhangdan_number) != numrows begin select errno = 30002, errmsg = parent does not exist in zhangdagl. cannot create child in dingcangl. goto error end end return/* errors handling */error: raiserror errno errmsg rollback transactionendgo/* update trigger tu_dingcangl for table dingcangl */create trigger tu_dingcangl on dingcangl for update asbegin declare numrows int, numnull int, errno int, errmsg varchar(255) select numrows = rowcount if numrows = 0 return /* parent zhangdagl must exist when updating a child in dingcangl */ if update(zhangdan_number) begin if (select count(*) from zhangdagl t1, inserted t2 where t1.zhangdan_number = t2.zhangdan_number) != numrows begin select errno = 30003, errmsg = zhangdagl does not exist. cannot modify child in dingcangl. goto error end end return/* errors handling */error: raiserror errno errmsg rollback transactionendgo/* insert trigger ti_zhangdagl for table zhangdagl */create trigger ti_zhangdagl on zhangdagl for insert asbegin declare numrows int, numnull int, errno int, errmsg varchar(255) select numrows = rowcount if numrows = 0 return /* parent caiwugl must exist when inserting a child in zhangdagl */ if update(cai_zhangdan_number) begin select numnull = (select count(*) from inserted where cai_zhangdan_number is null) if numnull != numrows if (select count(*) from caiwugl t1, inserted t2 where t1.zhangdan_number = t2.cai_zhangdan_number) != numrows - numnull begin select errno = 30002, errmsg = parent does not exist in caiwugl. cannot create child in zhangdagl. goto error end end return/* errors handling */error: raiserror errno errmsg rollback transactionendgo/* update trigger tu_zhangdagl for table zhangdagl */create trigger tu_zhangdagl on zhangdagl for update asbegin declare numrows int, numnull int, errno int, errmsg varchar(255) select numrows = rowcount if numrows = 0 return /* parent caiwugl must exist when updating a child in zhangdagl */ if update(cai_zhangdan_number) begin select numnull = (select count(*) from inserted where cai_zhangdan_number is null) if numnull != numrows if (select count(*) from caiwugl t1, inserted t2 where t1.zhangdan_number = t2.cai_zhangdan_number) != numrows - numnull begin select errno = 30003, errmsg = caiwugl does not exist. cannot modify child in zhangdagl. goto error end end /* modify parent code of zhangdagl for all children in caiwugl */ if update(zhangdan_number) begin update caiwugl set zhangdan_number = i1.zhangdan_number from caiwugl t2, inserted i1, deleted d1 where t2.zhangdan_number = d1.zhangdan_number and (i1.zhangdan_number != d1.zhangdan_number) end /* modify parent code of zhangdagl for all children in dingcangl */ if update(zhangdan_number) begin update dingcangl set zhangdan_number = i1.zhangdan_number from dingcangl t2, inserted i1, deleted d1 where t2.zhangdan_number = d1.zhangdan_number and (i1.zhangdan_number != d1.zhangdan_number) end return/* errors handling */error: raiserror errno errmsg rollback transactionendgo/* delete trigger td_zhangdagl for table zhangdagl */create trigger td_zhangdagl on zhangdagl for delete asbegin declare numrows int, errno int, errmsg varchar(255) select numrows = rowcount if numrows = 0 return /* delete all children in caiwugl */ delete caiwugl from caiwugl t2, deleted t1 where t2.zhangdan_number = t1.zhangdan_number /* delete