酒店订餐系统数据库设计和实现(共6页).doc
精选优质文档-倾情为你奉上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='',Telephone='',Address='胜利路3号'deletefrom 用户where unumber='09001'create 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='',Chinastyle_food='童子鸡',Westernstyle_food='意大利面', medical_food='瓦罐汤',snack='泡粉'deletefrom order_listwhere Lnumber='' *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='',Lnumber='',Delivery_order='等待发送',日期='2009-08-25'deletefrom businesswhere 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专心-专注-专业
