酒店订餐系统数据库设计和实现

create database 酒店订餐系统on(name=酒店订餐,filename=D:酒店订餐.mdf,size=10,maxsize=50,filegrowth=5)log on(name =酒店订餐_log,filename=D:酒店订餐.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 (Member_price=0)Create table order_list (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), /*订餐者编号*/ 时间 datetime, alllist decimal (6,2), /*合计*/ Cnumber char (20) /*管理员编号*/ )Create table 用户 ( Unumber char (9) primary key, /*会员帐号*/ Uname char (20), /*姓名*/ Usex char (4) default男, /*性别*/ Ucode char (9), /*密码*/ Telephone char(12), /*常用电话*/ Address char(40) /*收货具体地点*/ )Create table controller (Cnumber char (9) 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) /*价格*/ ) Create table business /*配送交易*/ ( Cnumber char (9) not null, /*管理员编号*/ Lnumber char (9), /*订单编号*/ Delivery_order char(20), /*执行命令*/ 日期 datetime /*送货日期*/ )/*建立索引*/create unique index food on food_data(name)create unique index list on order_list(Lnumber)create unique index users on 用户(Unumber) Create view Chinastyle_food As Select name, produce, price From food_data Where systems=鲁菜 Create view Westernstyle_food As Select name, produce, price From food_data Where country=美国 Create view medical_food /*药膳菜品*/ As Select name, produce, price From food_data Where disease=心脏Create view snack /*小吃*/ As Select produce, price From food_data Where name=炒粉create procedure insertfood( Systems char (10), /*菜系*/ Country char (20), /*国家*/ Disease char (20)=无, /*不同疾病*/ Name char (20), /*菜名*/ Produce char (200)=无, /*制作*/ Price float (6) )asinsert into food_data values(Systems, Country,Disease,Name,Produce,Price)exec insertfood Systems=鲁菜, Country=中国,Disease=养育,Name=烤鸭,Produce=香辣,Price=55deletefrom food_datawhere name=水煮牛肉 create trigger changedisplay1on food_datafor insertasselect* from food_datacreate procedure insertuser( Unumber char (9) , /*会员帐号*/ Uname char (20), /*姓名*/ Usex char (4), /*性别*/ Ucode char (9), /*密码*/ Telephone char(12), /*常用电话*/ Address char(40) ) asinsert into 用户 values(Unumber,Uname,Usex,Ucode,Telephone,Address)exec insertuser Unumber=09001,Uname=常在k,Usex=女,Ucode=123457,TelephoneAddress=胜利路3号deletefrom 用户where unumber=09001create trigger changedisplay2on 用户for insert asselect*from inserted create procedure insertorder( Lnumber char (9), /*订单编号*/ Chinastyle_food char (20), /*中餐*/ Westernstyle_food char (20), /*西餐*/ medical_food char(20), /*药膳菜品*/ snack char (20) /*小吃*/)asinsert into order_list values (Lnumber,Chinastyle_food,Westernstyle_food, medical_food,snack)exec insertorder Lnumber=090806,Chinastyle_food=童子鸡,Westernstyle_food=意大利面, medical_food=瓦罐汤,snack=泡粉deletefrom order_listwhere Lnumber=090801 *create trigger changedisplay3on order_listfor insert asselect*from order_listcreate procedure insertbusiness( Cnumber char (9) , /*管理员编号*/ Lnumber char (9), /*订单编号*/ Delivery_order char(20), /*执行命令*/ 日期 datetime ) /*送货日期*/asinsert into business values(Cnumber,Lnumber , Delivery_order,日期)exec insertbusiness Cnumber=080909,Lnumber=090809,Delivery_order=等待发送,日期=2009-08-25deletefrom businesswhere Lnumber=090801 create procedure updatebusiness( Cnumber char (9), /*管理员编号*/ Lnumber char (9) /*订单编号*/)asupdate businessset Delivery