酒店订餐系统数据库设计和实现_精品文档.doc
《酒店订餐系统数据库设计和实现_精品文档.doc》由会员分享,可在线阅读,更多相关《酒店订餐系统数据库设计和实现_精品文档.doc(6页珍藏版)》请在冰豆网上搜索。
createdatabase酒店订餐系统
on
(
name=酒店订餐,
filename='D:
\酒店订餐.mdf',
size=10,
maxsize=50,
filegrowth=5
)
logon
(
name=酒店订餐_log,
filename='D:
\酒店订餐.ldf',
size=5,
maxsize=25,
filegrowth=5
)
createtablecookbook
(Cooknumberchar(9)primarykey,/*菜单编号*/
namechar(20)unique,/*菜单名*/
Member_pricefloat(6),/*会员价格*/
constraintprice_chkcheck(Member_price>=0)
)
Createtableorder_list
(Lnumberchar(9)primarykey,/*订单编号*/
Chinastyle_foodchar(20),/*中餐*/
Westernstyle_foodchar(20),/*西餐*/
medical_foodchar(20),/*药膳菜品*/
snackchar(20)/*小吃*/
)
Createtablelistorder
(Lnumberchar(9)primarykey,/*订单编号*/
Delivery_orderchar(20),/*顶单状态*/
Unumberchar(20),/*订餐者编号*/
时间datetime,
alllistdecimal(6,2),/*合计*/
Cnumberchar(20)/*管理员编号*/
)
Createtable用户
(Unumberchar(9)primarykey,/*会员帐号*/
Unamechar(20),/*姓名*/
Usexchar(4)default'男',/*性别*/
Ucodechar(9),/*密码*/
Telephonechar(12),/*常用电话*/
Addresschar(40)/*收货具体地点*/
)
Createtablecontroller
(Cnumberchar(9)primarykey,/*管理员编号*/
Cnamechar(20),/*管理员姓名*/
Csexchar(4),/*管理员性别*/
Cagesmallint,/*管理员年龄*/
Cdepartmentchar(20)/*管理员部门*/
)
Createtablefood_data/*菜品资料*/
(systemschar(10),/*菜系*/
Countrychar(20),/*国家*/
Diseasechar(20),/*不同疾病*/
Namechar(20)primarykey,/*菜名*/
Producechar(200),/*制作*/
Pricefloat(6)/*价格*/
)
Createtablebusiness/*配送交易*/
(
Cnumberchar(9)notnull,/*管理员编号*/
Lnumberchar(9),/*订单编号*/
Delivery_orderchar(20),/*执行命令*/
日期datetime/*送货日期*/
)
/*建立索引*/
createuniqueindexfoodonfood_data(name)
createuniqueindexlistonorder_list(Lnumber)
createuniqueindexuserson用户(Unumber)
CreateviewChinastyle_food
As
Selectname,produce,price
Fromfood_data
Wheresystems='鲁菜'
CreateviewWesternstyle_food
As
Selectname,produce,price
Fromfood_data
Wherecountry='美国'
Createviewmedical_food/*药膳菜品*/
As
Selectname,produce,price
Fromfood_data
Wheredisease='心脏'
Createviewsnack/*小吃*/
As
Selectproduce,price
Fromfood_data
Wherename='炒粉'
createprocedureinsertfood
(
@Systemschar(10),/*菜系*/
@Countrychar(20),/*国家*/
@Diseasechar(20)='无',/*不同疾病*/
@Namechar(20),/*菜名*/
@Producechar(200)='无',/*制作*/
@Pricefloat(6)
)
as
insertintofood_datavalues(@Systems,@Country,@Disease,@Name,@Produce,@Price)
execinsertfood@Systems='鲁菜',@Country='中国',@Disease='养育',@Name='烤鸭',@Produce='香辣',@Price=55
delete
fromfood_data
wherename='水煮牛肉'
createtriggerchangedisplay1
onfood_data
forinsert
as
select*fromfood_data
createprocedureinsertuser
(
@Unumberchar(9),/*会员帐号*/
@Unamechar(20),/*姓名*/
@Usexchar(4),/*性别*/
@Ucodechar(9),/*密码*/
@Telephonechar(12),/*常用电话*/
@Addresschar(40)
)
as
insertinto用户values(@Unumber,@Uname,@Usex,@Ucode,@Telephone,@Address)
execinsertuser@Unumber='09001',@Uname='常在k',@Usex='女',@Ucode='123457',@Telephone='15100002222',@Address='胜利路3号'
delete
from用户
whereunumber='09001'
createtriggerchangedisplay2
on用户
forinsert
as
select*frominserte