酒店订餐系统数据库设计和实现
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
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='水煮牛肉'