《酒店订餐系统数据库设计和实现(共6页).doc》由会员分享，可在线阅读，更多相关《酒店订餐系统数据库设计和实现(共6页).doc（6页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、精选优质文档-倾情为你奉上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), /*会员价格*
2、/ 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 , /*订单编号*/
3、 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), /*常用电话*/
4、 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 ch
5、ar (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_d
6、ata(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
7、_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 cha
8、r (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_datacr
9、eate 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=,Telepho
10、ne=,Address=胜利路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) /*小吃*/)a
11、sinsert into order_list values (Lnumber,Chinastyle_food,Westernstyle_food, medical_food,snack)exec insertorder Lnumber=,Chinastyle_food=童子鸡,Westernstyle_food=意大利面, medical_food=瓦罐汤,snack=泡粉deletefrom order_listwhere Lnumber= *create trigger changedisplay3on order_listfor insert asselect*from order_l
12、istcreate procedure insertbusiness( Cnumber char (9) , /*管理员编号*/ Lnumber char (9), /*订单编号*/ Delivery_order char(20), /*执行命令*/ 日期 datetime ) /*送货日期*/asinsert into business values(Cnumber,Lnumber , Delivery_order,日期)exec insertbusiness Cnumber=,Lnumber=,Delivery_order=等待发送,日期=2009-08-25deletefrom busi
13、nesswhere Lnumber= create procedure updatebusiness( Cnumber char (9), /*管理员编号*/ Lnumber char (9) /*订单编号*/)asupdate businessset Delivery_order=发送where Delivery_order=等待发送and lnumber=Lnumber and cnumber=Cnumberexec updatebusiness Lnumber= , Cnumber=create trigger changedisplay4on businessfor updateasselect * from business专心-专注-专业