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

create database 酒店订餐系统

on

(

name=酒店订餐,

'D:\酒店订餐.mdf',

size=10,

maxsize=50,

)

log on

(

name =酒店订餐_log,

'D:\酒店订餐.ldf',

size=5,

maxsize=25,

)

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)

)

as

insert into food_data values(@Systems, @Country,@Disease,@Name,@Produce,@Price)

exec insertfood @Systems='鲁菜', @Country='中国',@Disease='养育',@Name='烤鸭',@Produce='香辣',@Price=55

delete

from food_data

where name='水煮牛肉'

create trigger changedisplay1

on food_data

for insert

as

select* from food_data

create procedure insertuser

(

@Unumber char (9) , /*会员帐号*/

@Uname char (20), /*姓名*/

@Usex char (4), /*性别*/ @Ucode char (9), /*密码*/

@Telephone char(12), /*常用电话*/

@Address char(40)

)

as

insert into 用户 values(@Unumber,@Uname,@Usex,@Ucode,@Telephone,@Address)

exec insertuser @Unumber='09001',@Uname='常在k',@Usex='女

',@Ucode='123457',@Telephone='',@Address='胜利路3号'

delete

from 用户

where unumber='09001'

create trigger changedisplay2

on 用户

for insert

as

select*from inserted

create procedure insertorder

(

@Lnumber char (9), /*订单编号*/

@Chinastyle_food char (20), /*中餐*/

@Westernstyle_food char (20), /*西餐*/

@medical_food char(20), /*药膳菜品*/

@snack char (20) /*小吃*/

)

as

insert into order_list values (@Lnumber,@Chinastyle_food,@Westernstyle_food,

@medical_food,@snack)

exec insertorder @Lnumber='090806',@Chinastyle_food='童子鸡',@Westernstyle_food='

意大利面', @medical_food='瓦罐汤',@snack='泡粉'

delete

from order_list

where Lnumber='090801'

*****************************************

create trigger changedisplay3

on order_list

for insert

as

select*from order_list

create procedure insertbusiness

(

@Cnumber char (9) , /*管理员编号*/

@Lnumber char (9), /*订单编号*/

@Delivery_order char(20), /*执行命令*/

@日期 datetime ) /*送货日期*/

as

insert into business values(@Cnumber,@Lnumber , @Delivery_order,@日期)

exec insertbusiness @Cnumber='080909',@Lnumber='090809',@Delivery_order='等待发送',@日期='2009-08-25'

delete

from business

where Lnumber='090801'

create procedure updatebusiness

(

@Cnumber char (9), /*管理员编号*/

@Lnumber char (9) /*订单编号*/

)

as

update business

set Delivery_order='发送'

where Delivery_order='等待发送'

and lnumber=@Lnumber and cnumber=@Cnumber

exec updatebusiness @Lnumber='090809 ', @Cnumber='080909'

create trigger changedisplay4 on business

for update

as

select * from business