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

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='等待发送',@日期='2023-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