食堂管理系統(tǒng)-數(shù)據(jù)庫設(shè)計

1、 2012年 3 月 14 日課題：數(shù)據(jù)庫設(shè)計（食堂管理系統(tǒng)）目的和要求：1. 掌握數(shù)據(jù)庫需求分析2. 掌握數(shù)據(jù)庫概念結(jié)構(gòu)設(shè)計與邏輯結(jié)構(gòu)設(shè)計方法3. 掌握創(chuàng)建數(shù)據(jù)庫、表、約束、視圖、存儲過程、觸發(fā)器的方法4. 掌握權(quán)限的設(shè)置方法5. 掌握備份與恢復(fù)的方法操作內(nèi)容：設(shè)計數(shù)據(jù)庫課題：食堂管理系統(tǒng)一、數(shù)據(jù)需求分析：在搜集資料和分析用戶應(yīng)用要求的基礎(chǔ)上，形成該數(shù)據(jù)庫系統(tǒng)的應(yīng)用需求說明；二、涉及到的表：通過需求分析確定表結(jié)構(gòu)。1、分幾個表2、表中需哪些列，列名、數(shù)據(jù)類型、長度；3、可否為空值；(表示未知、不可用或?qū)⒃谝院筇砑訑?shù)據(jù))4、需使用的約束：主鍵，外鍵，默認(rèn)值，check，唯一值約束。三、創(chuàng)建視圖

2、四、存儲過程五、觸發(fā)器六、權(quán)限設(shè)置七、數(shù)據(jù)備份與恢復(fù)設(shè)計一、 需求分析1. 系統(tǒng)分析隨著時代的進(jìn)步， 如今各個服務(wù)行業(yè)也都逐漸發(fā)展壯大起來，尤其是食堂服務(wù)業(yè)，其在服務(wù)范圍、服務(wù)數(shù)量和服務(wù)內(nèi)容上都有著非常大的膨脹幅度，因此如何對如此復(fù)雜而頻繁的服務(wù)活動進(jìn)行管理就屬于“食堂管理”的內(nèi)容。其主要包括：職員資料管理、物品管理、消費內(nèi)容管理、席位管理、客戶評價管理，工資管理等，它是現(xiàn)代食堂管理中的一個重要組成部分。 2. 功能需求分析“食堂管理” 包括很多項目，以前食堂管理人員要記錄大量的用戶消費內(nèi)容，然后通過計算器進(jìn)行一系列的加減乘除運(yùn)算， 最后得出一位顧客的“應(yīng)付金額”，這樣做的效率和準(zhǔn)確度可想而知

3、。 如果使用計算機(jī)來實現(xiàn)對食堂服務(wù)業(yè)的智能管理，從選擇菜、酒水、主食，到計算“應(yīng)付金額”，最后到打印消費內(nèi)容，計算機(jī)都可以很準(zhǔn)確、很快捷地進(jìn)行處理，這些都是“食堂管理系統(tǒng)”的功能。一個完善的“食堂管理系統(tǒng)”可以很好地管理食堂服務(wù)業(yè)的各項內(nèi)容，這樣不僅能更好地服務(wù)顧客，而且可以為經(jīng)營者創(chuàng)造更大的利潤。針對每部分的具體功能我們又做了如下的詳細(xì)分析： 職員管理管理物品管理席位管理營業(yè)管理食堂管理系統(tǒng)評價管理工資管理二、 涉及的表職員資料屬性類型大小是否為空約束職員編號char6否主鍵。6位數(shù)姓名varchar20否職位varchar20否性別Char2否，默認(rèn)“男”男或女民族varchar10否，默

4、認(rèn)“漢族”籍貫varchar10否，默認(rèn)“廣東”出生日期datetime否身份證號char18否婚姻狀況char4否，默認(rèn)“未婚”未婚或已婚家庭地址varchar30否聯(lián)系電話varchar11否備注varchar30是物品表屬性類型大小是否為空約束物品編號char6否主鍵。6位數(shù)物品名字varchar20否所屬類型char4否，默認(rèn)“主食”主食、酒水或其他價格/元int否是否售馨char2否，默認(rèn)“否”是或否品牌varchar30是備注varchar30是席位表屬性類型大小是否為空約束席位號char6否主鍵，6位數(shù)負(fù)責(zé)人編號外鍵人數(shù)int否，默認(rèn)“8”1至20狀態(tài)char4否，默認(rèn)“空閑”正

5、在使用，預(yù)定或空閑日期datetime否備注varchar30是銷售記錄屬性類型大小是否為空約束類型外鍵數(shù)量int否方式varchar10否，默認(rèn)“現(xiàn)場”現(xiàn)場、預(yù)定或外賣日期datetime否金額/元int否備注varchar30是評價情況屬性類型大小是否為空約束職員編號外鍵評價級別char4否，默認(rèn)“優(yōu)”優(yōu)、良、一般、差日期datetime否，默認(rèn)當(dāng)天備注varchar30是工資表屬性類型大小是否為空約束職員編號外鍵工資/元int否日期datetime否備注varchar30是SQL 命令創(chuàng)建數(shù)據(jù)庫create database 食堂管理系統(tǒng)on primary (name= stglxt_

6、data,filename='e:stglxt_data.mdf')log on (name=stglxt_log1,filename='e:stglxt _log.ldf')創(chuàng)建表create table 職員資料(職員編號char(6) not null primary keycheck(職員編號like'0-90-90-90-90-90-9'),姓名varchar(20) not null,職位varchar(20) not null,性別char(2) not null check(性別='男' or 性別='女&

7、#39;) default '男',民族varchar(8) null default '漢族',出生日期datetime not null,身份證號碼char(18) not null unique,婚姻狀況char(4) not null check(婚姻狀況='已婚' or 婚姻狀況='未婚') default '未婚',聯(lián)系電話varchar(11) not null unique,備注varchar(30)create table 物品表(物品編號 char(6) not null primary key

8、,物品名字 varchar(20) not null,所屬類型 char(4) not null check(所屬類型='主食'or 所屬類型='酒水' or 所屬類型='其他') default '主食',價格 money not null,是否售馨 char(2) not null check(是否售馨='是' or 是否售馨='否') default '否',品牌 varchar(30),備注 varchar(30)create table 席位表(席位號char(6) not

9、 null primary key,負(fù)責(zé)人編號char(6) not null foreign key references 職員資料(職員編號) on update cascade on delete cascade,人數(shù)int not null,狀態(tài)char(4) not null check(狀態(tài)='使用' or 狀態(tài)='預(yù)定' or 狀態(tài)='空閑') default '空閑',日期datetime not null,備注varchar(30)create table 銷售記錄(類型char(4) not null chec

10、k(類型='主食'or 類型='酒水' or 類型='其他') default '主食',數(shù)量int not null,方式char(4) not null check(方式='現(xiàn)場' or 方式='預(yù)定' or 方式='外賣') default '現(xiàn)場',金額money not null,日期datetime not null,備注varchar(30)create table 評價情況(職員編號 char(6) not null foreign key refer

11、ences 職員資料(職員編號) on update cascade on delete cascade,評價級別char(4) not null check(評價級別in ('優(yōu)','良','一般','差') default '優(yōu)',日期datetime not null default getdate(),備注varchar(30)create table 工資表(職員編號char(6) not null foreign key references 職員資料(職員編號) on update cascade o

12、n delete cascade,工資money not null,日期datetime not null,備注varchar(30)所有表的截圖如下：三、 視圖設(shè)計1. 為了方便查看職員的評價與對應(yīng)的職員，所以要建一個“職員信息與評價表”，以顯示職員的評價的同時，還可以看到該職員的一些個人信息。create view 職員信息與評價表asselect 姓名,性別,職位,聯(lián)系電話,評價級別,日期from 職員資料 a, 評價情況bwhere a.職員編號=b. 職員編號2. 為了方便查看物品的銷售記錄以及物品的信息，所以建立一個“物品信息與銷售情況表”。create view 物品信息與銷售情

13、況表asselect 物品名字,所屬類型,價格,品牌,方式,日期,數(shù)量from 銷售記錄 a, 物品表 bwhere a.類型=b. 所屬類型3. 為了在職員信息中，可以方便看到該職員的工資，所以建立一個“職員信息工資表”create view 職員信息工資表asselect 姓名,性別,職位,聯(lián)系電話,工資(元),日期from 職員資料 a, 工資表 bwhere a.職員編號=b. 職員編號四、 存儲過程1. 創(chuàng)建一個存儲過程，通過給定職位可查看該職位所有職員的個人信息。create proc 查詢所指定職位的職員基本信息職位 varchar(20)asselect 職位,姓名,性別,民族

14、,籍貫,出生日期,身份證號,婚姻狀況,家庭住址,聯(lián)系電話,備注from 職員資料where職位=職位2. 創(chuàng)建一個存儲過程，通過給定職員編號可刪除該職員create proc 刪除所指定的職員職員編號 char(6)asdelete 職員資料where 職員編號=職員編號五、 觸發(fā)器1. 在評價情況表中添加記錄后，統(tǒng)計某職員的優(yōu)評數(shù)量，在本月1號到到添加日期這段時間的好評達(dá)到10次將在職員資料表中為該職員的備注欄添加上“工作中表現(xiàn)優(yōu)秀！”create trigger 優(yōu)評備注 on 評價情況after insertasdeclare a varchar,b intbeginselect a=職

15、員編號 form inserted where 評價級別=優(yōu)select b=count（*）form 評價情況 where 評價級別=優(yōu)and 職員編號=a and dateadd（day，-1，getdate()）<30 and dateadd() group by 評價級別，職員編號if b>10beginupdate 職員資料set 備注=” 工作中表現(xiàn)優(yōu)秀！”where 職員編號=aendelseend2. 在評價情況表中添加記錄后，統(tǒng)計某職員的優(yōu)評數(shù)量，在本月1號到到添加日期這段時間的差評達(dá)到10次將在職員資料表中為該職員的備注欄添加上“工作中表現(xiàn)糟糕！”。create

16、 trigger 優(yōu)評備注 on 評價情況after insertasdeclare c varchar,d intbeginselect c=職員編號 form inserted where 評價級別=差select d=count（*）form 評價情況 where 評價級別=差and 職員編號=c and dateadd（day，-1，getdate()）<30 and dateadd() group by 評價級別，職員編號if d>10beginupdate 職員資料set 備注=” 工作中表現(xiàn)糟糕！”where 職員編號=aendelseend六、 權(quán)限設(shè)置我們所設(shè)計的

17、教師檔案管理系統(tǒng)根據(jù)實際情況會用到四類用戶，有食堂所有者、管理者、普通員工，還有客戶。食堂所有者在這管理系統(tǒng)種具有全部權(quán)限，食堂的管理人員具有職員管理，物品管理，營業(yè)管理，評價管理的添加刪改權(quán)限。 食堂普通職員具有席位管理的添加刪改權(quán)限，方便對日常工作的管理?？蛻艟哂性谠u價情況表上添加對職員的評價情況的權(quán)限，但是不能修改和刪除。1、新建了三個用戶分別代表四類用戶，所有者、管理者、普通員工，客戶，分別設(shè)有密碼。命令如下：exec sp_addlogin '所有者','111'exec sp_addlogin '管理者','222'e

18、xec sp_addlogin '普通員工','333'exec sp_addlogin '客戶','444'2、授予所有者所有權(quán)限給 '所有者' 授予食堂管理系統(tǒng)數(shù)據(jù)庫訪問權(quán)，并給其分配為該數(shù)據(jù)庫的擁有者：db_owner，在該數(shù)據(jù)庫中擁有所有權(quán)限；命令如下：use 食堂管理系統(tǒng)exec sp_grantdbaccess '所有者'goexec sp_addrolemember 'db_owner','所有者'2、通過所有者用戶給普通員工授予權(quán)限，命令如下：use

19、食堂管理系統(tǒng)exec sp_grantdbaccess '普通員工'gogrant select on 席位表 to 普通員工3、通過所有者用戶給管理者授予權(quán)限，命令如下：use 食堂管理系統(tǒng)exec sp_grantdbaccess '管理者'gogrant select,insert,update on 職員資料 to 管理者grant select,insert,update on 物品表 to 管理者grant select,insert,update on 銷售記錄 to 管理者grant select,insert,update on 評價情況 to

20、 管理者七、 數(shù)據(jù)備份與恢復(fù)設(shè)計為了防止數(shù)據(jù)可能在運(yùn)行過程中出現(xiàn)某些意外的錯誤而導(dǎo)致數(shù)據(jù)丟失等嚴(yán)重問題，數(shù)據(jù)庫的備份與恢復(fù)在實際的數(shù)據(jù)庫系統(tǒng)開發(fā)中有著十分重要的意義。1. 針對系統(tǒng)的數(shù)據(jù)庫分析：為了防止突發(fā)事件對現(xiàn)存數(shù)據(jù)庫的破壞，應(yīng)該隨時將數(shù)據(jù)庫信息進(jìn)行備份，考慮到三種備份空間與時間上的優(yōu)化，我們采取備份策略：1) 有規(guī)律的進(jìn)行完整數(shù)據(jù)庫備份，可以隔一段時間，在操作數(shù)據(jù)庫不頻繁的時間段進(jìn)行；2) 在較小的時間間隔內(nèi)進(jìn)行差異備份，比如一天；3) 在相鄰的兩次差異備份之間進(jìn)行事務(wù)日志備份，可以每半個小時一次，以補(bǔ)充備份在完整數(shù)據(jù)庫備份與差異備份進(jìn)行之間之間進(jìn)行的事務(wù)。2. 考慮食堂管理系統(tǒng)實際情況

21、，我們采取恢復(fù)策略：先恢復(fù)最近一次的數(shù)據(jù)庫備份，接著進(jìn)行差異備份恢復(fù)，最后進(jìn)行事務(wù)日志備份的恢復(fù)。因為我們同樣關(guān)心數(shù)據(jù)庫狀態(tài)之間的變化，所以采用完整數(shù)據(jù)庫恢復(fù)策略。3. 由于食堂管理系統(tǒng)的數(shù)據(jù)量會隨時間不斷增多，可能出現(xiàn)很多的人員的調(diào)動和調(diào)整，占據(jù)存儲空間的同時又降低數(shù)據(jù)庫訪問的效率，針對這方面我們采?。好?年對教師教師基本信息進(jìn)行數(shù)據(jù)備份，備份數(shù)據(jù)另保存，同時將這些已備份的數(shù)據(jù)從食堂管理系統(tǒng)中刪除。數(shù)據(jù)庫備份備份：數(shù)據(jù)庫備份就是制作數(shù)據(jù)庫結(jié)構(gòu)和數(shù)據(jù)的拷貝，以便在數(shù)據(jù)庫遭到破壞的時候能夠恢復(fù)數(shù)據(jù)庫。備份數(shù)據(jù)庫語法backup database 要備份的數(shù)據(jù)庫名to 用來備份的備份設(shè)備名/備份文

22、件名 with format/init/noinit/restart/differentialformat：完全刪除備份設(shè)備原內(nèi)容后備份init：完全刪除備份設(shè)備除標(biāo)題外的原內(nèi)容noinit：備份內(nèi)容添加到備份設(shè)備原有內(nèi)容后restart：在中斷點重新備份differential：進(jìn)行差異性備份事務(wù)日志備份的語法：backup log 要備份的數(shù)據(jù)庫名to 用來備份的備份設(shè)備名/備份文件名恢復(fù)數(shù)據(jù)庫1、數(shù)據(jù)庫恢復(fù)：數(shù)據(jù)庫恢復(fù)就是把數(shù)據(jù)庫備份加載到系統(tǒng)中。數(shù)據(jù)庫備份后，一旦系統(tǒng)發(fā)生崩潰或者執(zhí)行了錯誤的數(shù)據(jù)庫操作，就可以從備份文件中恢復(fù)數(shù)據(jù)庫。系統(tǒng)在恢復(fù)數(shù)據(jù)庫的過程中，自動執(zhí)行安全性檢查、重建數(shù)據(jù)

23、庫結(jié)構(gòu)以及完整數(shù)據(jù)庫內(nèi)容。 2、下面三個情況會導(dǎo)致恢復(fù)失敗：服務(wù)器上的數(shù)據(jù)庫文件集和備份集中的數(shù)據(jù)文件集不一致。如果在restore語句中指定的數(shù)據(jù)庫已經(jīng)存在，并且該數(shù)據(jù)庫與在備份文件中記錄的數(shù)據(jù)庫不同。不能提供用于恢復(fù)數(shù)據(jù)庫的全部文件或文件組。3、恢復(fù)數(shù)據(jù)庫時應(yīng)注意的兩個問題：在數(shù)據(jù)庫恢復(fù)前，應(yīng)該首先刪除故障數(shù)據(jù)庫，以便刪除對故障硬件的任何引用。在數(shù)據(jù)庫恢復(fù)前，必須限制用戶對數(shù)據(jù)庫的訪問恢復(fù)數(shù)據(jù)庫語法restore database 數(shù)據(jù)庫名from 備份設(shè)備名with file=備份文件序號Norecovery *還原還沒有最后完成，還能接收新的日志備份文件，用來恢復(fù) Recovery *

24、還原已經(jīng)結(jié)束，數(shù)據(jù)庫可以恢復(fù)使用恢復(fù)事務(wù)日志restore log 數(shù)據(jù)庫名from 備份設(shè)備邏輯名with file=備份文件序號,norecovery/recovery創(chuàng)建備份設(shè)備exec sp_addumpdevice disk ,邏輯名稱 ,物理名稱對數(shù)據(jù)庫進(jìn)行如下操作：創(chuàng)建好食堂管理系統(tǒng)數(shù)據(jù)庫，然后對數(shù)據(jù)庫進(jìn)行完全備份；對數(shù)據(jù)庫進(jìn)行相對應(yīng)的操作后，進(jìn)行差異備份；對數(shù)據(jù)庫再次進(jìn)行相關(guān)操作后，進(jìn)行日志備份；破壞數(shù)據(jù)庫，然后進(jìn)行還原（選擇最佳還原方案）。還原后，再次進(jìn)行相關(guān)操作，然后進(jìn)行一次差異備份。破壞數(shù)據(jù)庫，然后進(jìn)行還原（選擇最佳還原方案）。還原后，進(jìn)行一次完全備份。再次破壞數(shù)據(jù)庫，最

25、后一次還原（選擇最佳還原方案）。命令如下： exec sp_addumpdevice 'disk','bbb','e:bbb.bak'backup database 食堂管理系統(tǒng) to bbb2、backup database 食堂管理系統(tǒng)to bbb with differential3、backup log 食堂管理系統(tǒng) to bbb4、drop database 食堂管理系統(tǒng)gorestore database 食堂管理系統(tǒng) from bbb with file=1,norecoveryrestore database 食堂管理系統(tǒng) fro

26、m bbb with file=2,norecoveryrestore log 食堂管理系統(tǒng) from bbb with file=35、backup database 食堂管理系統(tǒng) to bbb with differential6、drop database 食堂管理系統(tǒng)gorestore database食堂管理系統(tǒng) from bbb with file=1,norecoveryrestore database食堂管理系統(tǒng) from bbb with file=47、backup database食堂管理系統(tǒng) to bbb8、drop database 食堂管理系統(tǒng)gorestore database 食堂管理系統(tǒng) from bbb with file=5總結(jié)通過這次的數(shù)據(jù)庫課程設(shè)計，獨立完成了食堂管理系統(tǒng)的設(shè)計，從這次的系統(tǒng)設(shè)計中我領(lǐng)略到了：1. 開放一個數(shù)據(jù)庫應(yīng)用系統(tǒng)，通常需