酒店訂餐系統(tǒng)數(shù)據(jù)庫(kù)設(shè)計(jì)和實(shí)現(xiàn)

1、create database 酒店訂餐系統(tǒng)on(name=W店訂餐,酒店訂餐 .mdf,size=10,maxsize=50, filegrowth=5 ) log on (name =酒店訂餐 _log, 酒店訂餐 .ldf,size=5, maxsize=25, filegrowth=5)create table cookbook( Cooknumber char (9) primary key, name char (20) unique,Member_price float (6), constraint price_chk check)Create table order_list

2、(Lnumber char (9) primary key Chinastyle_food char (20), Westernstyle_food char (20), medical_food char(20), snack char (20)Create table listorder(Lnumber char (9) primary key Delivery_order char (20), Unumber char (20),時(shí)間 datetime,alllist decimal (6,2),Cnumber char (20)Create table 用戶( Unumber char

3、 (9) primary key, Unamechar (20),Usex char (4) default 男 ,/* 菜單編號(hào) */ /* 菜單名 */* 會(huì)員價(jià)格 */(Member_price=0)/* 訂單編號(hào) */* 中餐 */* 西餐 */* 藥膳菜品 */* 小吃 */* 訂單編號(hào) */* 頂單狀態(tài)/* 訂餐者編號(hào) */* 合計(jì) */* 管理員編號(hào)/* 會(huì)員帳號(hào) */* 姓名 */*/*/性別 */Ucode char (9),Telephone char(12),Address char(40)Create table controller(Cnumber char (9)

4、primary key,Cname char (20),Csex char (4),Cage smallint, Cdepartment char (20)Create table food_data (systems char (10),Country char (20),Disease char (20),Name char (20) primary key,Produce char (200),Price float (6)/* 密碼 */* 常用電話 */* 收貨具體地點(diǎn) */* 管理員編號(hào) */* 管理員姓名 */* 管理員性別 */* 管理員年齡 */* 管理員部門 */* 菜品資

5、料 */* 菜系 */* 國(guó)家 */* 不同疾病 */* 菜名 */* 制作 */* 價(jià)格 */Create table business(Cnumber char (9) not null,Lnumber char (9),Delivery_order char(20), 日期 datetime)/* 配送交易 */* 管理員編號(hào) */* 訂單編號(hào) */* 執(zhí)行命令 */* 送貨日期 */* 建立索引 */createuniqueindexfoodon food_data(name)createuniqueindexliston order_list(Lnumber)createunique

6、indexuserson 用戶 (Unumber)Create view Chinastyle_foodAsSelect name, produce, priceFrom food_dataWhere systems= 魯菜Create view Westernstyle_foodAsSelect name, produce, price From food_dataWhere country= 美國(guó) Create view medical_food /* 藥膳菜品 */AsSelect name, produce, priceFrom food_dataWhere disease= 心臟 C

7、reate view snack /* 小吃 */AsSelect produce, priceFrom food_dataWhere name= 炒粉 create procedure insertfood(Systems char (10),Country char (20),Disease char (20)= 無 , Namechar (20),Produce char (200)= 無 , Price float (6)asinsert into food_data values(Systems,/* 菜系 */* 國(guó)家 */* 不同疾病 */* 菜名 */* 制作 */Countr

8、y,Disease,Name,Produce,Price)exec insertfoodSystems=魯菜,Produce= 香辣 ,Price=55Country=中國(guó),Disease=養(yǎng)育,Name=烤鴨delete from food_data where name=水煮牛肉create trigger changedisplay1 on food_datafor insertasselect* from food_data create procedure insertuser/* 會(huì)員帳號(hào) */* 姓名 */* 性別 */* 密碼 */* 常用電話 */(Unumberchar (

9、9) , Unamechar (20), Usexchar (4), Ucodechar (9), Telephone char(12), Address char(40)asinsert into 用戶 values(Unumber,Uname,Usex,Ucode,Telephone,Address)execinsertuserUnumber=09001,Uname=常在 k,Usex=女,Ucode=123457,TelephoneAddress= 勝利路 3 號(hào)deletefrom 用戶where unumber=09001 create trigger ch

10、angedisplay2on 用戶for insertasselect*from inserted create procedure insertorder(Lnumberchar (9),/* 訂單編號(hào) */Chinastyle_food char (20),/* 中餐 */Westernstyle_food char (20),/* 西餐 */medical_food char(20),/* 藥膳菜品 */snack char (20)/* 小吃 */)asinsert into order_list values (Lnumber,Chinastyle_food,Westernstyle

11、_food, medical_food,snack)execinsertorderLnumber=090806,Chinastyle_food= 童子雞,Westernstyle_food= 意大利面 , medical_food= 瓦罐湯 ,snack= 泡粉 deletefrom order_listwhere Lnumber=090801*create trigger changedisplay3on order_listfor insertasselect*from order_listcreate procedure insertbusiness(Cnumberchar (9) ,

12、/* 管理員編號(hào) */Lnumber char(9),/* 訂單編號(hào) */Delivery_orderchar(20),/* 執(zhí)行命令 */日期 datetime)/* 送貨日期 */as insertinto businessvalues(Cnumber,Lnumber ,Delivery_order, 日期 )execinsertbusinessCnumber=080909,Lnumber=090809,Delivery_order= 等待發(fā)送,日期 =2009-08-25deletefrom businesswhere Lnumber=090801 create procedure updatebusiness (Cnumberchar (9), /* 管理員編號(hào) */Lnumber char (9) /* 訂單編號(hào) */ ) as update business set Delivery_order= 發(fā)送