酒店管理系统数据库代码Word下载.docx
《酒店管理系统数据库代码Word下载.docx》由会员分享,可在线阅读,更多相关《酒店管理系统数据库代码Word下载.docx(10页珍藏版)》请在冰豆网上搜索。
--1顾客表
createtableCustomer
(CustomerIDintprimarykey,
CustomerNamenvarchar(40)notnull,
CustomerInfonvarchar(18)notnull,
Csexnvarchar
(1),
CPhonenvarchar(11)notnull,
Notesntext
--droptableCustomer
--2员工表
createtableEmployee
(EmployeeIDintprimarykey,
UserNamenvarchar(40)notnull,
Passwordnvarchar(40)notnull,
EmployeeNamenvarchar(40)notnull,
Esexnvarchar
(1),
EPhonenvarchar(11)notnull,
--3客房表(有外键)
createtableRoom
(RoomIDintprimarykey,
RoomTypeIDintnotnull,
RoomStatenvarchar
(1)notnull,
Notesntext,
FOREIGNKEY(RoomTypeID)REFERENCESRoomType(RoomTypeID),
--droptableRoom
--4客房类型表(有外键)
createtableRoomType
(RoomTypeIDintprimarykey,
RoomTypeNamenchar(20)notnull,
Costfloat,
Totalint,
Surplusint,
--droptableRoomType
--5订房表
select*fromOrderInfo
createtableOrderInfo
(OrderIDintnotnullprimarykey,
RoomIDintnotnull,
CustomerIDint,
EmployeeIDint,
Entertimedatetimenotnull,
Depositfloat,
ORstaticnvarchar(10)notnull,
FOREIGNKEY(CustomerID)REFERENCESCustomer(CustomerID),
FOREIGNKEY(RoomID)REFERENCESRoom(RoomID),
FOREIGNKEY(EmployeeID)REFERENCESEmployee(EmployeeID),
--altertableaddconstraintOI_DORstaticdefault'
use'
--droptableOrderInfo
--6退房表check-out
createtableCheckout
(CheckoutIDintprimarykey,
RoomIDintnotnull,
Entertimedatetimenotnull,
Endtimedatetimenotnull,
Total_consumptionfloat,
--droptableCheckout
sp_helpCheckout
----------------------------------------表插入信息----------------------------------------------------------------------
--Employee表
insertintoEmployeevalues('
zhoutonglu'
123456,'
董洁'
'
f'
,null)
liminghao'
李明浩'
m'
yuxian'
余香'
---RoomType表
insertintoRoomTypevalues(1,'
单间'
200,20,19,null)
insertintoRoomTypevalues(2,'
标准间'
260,20,19,null)
insertintoRoomTypevalues(3,'
豪华单间'
580,20,19,null)
insertintoRoomTypevalues(4,'
行政套房'
880,20,19,null)
----Room表
insertintoRoomvalues('
1011'
1,'
Y'
null)
1012'
N'
1021'
2,'
1022'
1031'
3,'
1032'
1041'
4,'
1042'
1013'
1014'
1023'
1024'
1033'
1034'
1051'
1052'
---Customer表
insertintoCustomervalues('
刘德华'
,'
张更硕'
周辉'
刘美美'
范冰冰'
佟大为'
范玮琪'
陈小春'
kenim'
--OrderInfo表
select*fromOrderInfo
insertintoOrderInfovalues(9001,'
1,1,'
2013-09-039:
00PM'
null)
insertintoOrderInfovalues(9002,'
2,2,'
2013-09-057:
insertintoOrderInfovalues(9003,'
3,2,'
2013-09-048:
insertintoOrderInfovalues(9004,'
4,2,'
2013-09-122:
insertintoOrderInfovalues(9005,'
9,2,'
2013-09-047:
insertintoOrderInfovalues(9006,'
10,2,'
--insertintoOrderInfovalues(9007,'
11,2,'
2013-09-42:
execproc_find_stu1041---库存-1
--insertintoOrderInfovalues(9005,'
--deleteOrderInfowhereOrderIDin(9005)
---Checkout表
insertintoCheckoutvalues(13001,'
1,2,'
2013-09-04'
200,NULL)
insertintoCheckoutvalues(13002,'
2013-09-033:
insertintoCheckoutvalues(13003,'
2013-09-0310:
--insertintoCheckoutvalues(13004,'
2013-09-038:
880,NULL)
deleteCheckoutwhereCheckoutIDin(13001,13002)
execproc_find_stu21011---库存+1
--insertintoCheckoutvalues('
O2001'
R003'
1002,2,'
2013-09-06'
570,NULL)
O2002'
R001'
1003,2,'
----------------------------------------创建触发器----------------------------------------------------------------------
--1\创建客房使用状态触发器(插入)
createtriggerRoomState_1
onOrderInfo
forinsert
as
updateRoom
setRoomState='
whereRoomID=any(selectRoomIDfromINSERTED)
--droptriggerRoomState_1
--2-创建客房使用状态触发器(删除)
createtriggerRoomState_2
onCheckout
whereRoomIDin(selectRoomIDfromINSERTED)
--droptriggerRoomState_2
---3--创建修改订单状态触发器
createtriggerORstatic_1
updateOrderInfo
setORstatic='
NO'
--droptriggerORstatic_1
---------------------------------------存储过程-----------------------------------------
-----------------------------------------------------------------------------------------
--1--创建修改客房库存触发器(减少)
createprocproc_find_stu(@startIdint)
updateRoomTypesetSurplus=(Surplus-1)
whereRoomTypeIDin(selectRoomTypeIDfromRoomwhereRoomID=@startId)
go
execproc_find_stu1011
--2--创建修改客房库存触发器(增长)
createprocproc_find_stu2(@startIdint)
updateRoomTypesetSurplus=(Surplus+1)
execproc_find_stu21011
---系统功能流程
--()顾客入住员工查询闲置房间
select*fromRoomwhereRoomState='
selectRoomID,RoomTypeName,RoomState,Cost,Total,SurplusfromRoom,RoomTypewhere=andRoomState='
---A。
顾客要求住'
selectRoomID,RoomTypeName,RoomState,Cost,Total,SurplusfromRoom,RoomType
where=andRoomState='
andRoomTypeName='
---B。
---C。
或是'
andRoomTypeNamein('
-------------------------------------------------------------------------------------------------------------------------
--()登记住房订单信息----当客户入住房间,房间状态变为使用状态(RoomState='
,订单表'
ORstatic'
=USE表示顾客入住登记成功
--例如:
某客人(林大帅)要入住“标准客房”
--1.员工查询是否还有闲置“标准客房”(还剩下个房间)
--2.登记受理,先登记入住顾客信息
select*fromCustomer
insertintoCustomervalues('
林大帅'
---3.登记订单信息
insertintoOrderInfovalues(9007,'
12,2,'
select*fromRoom--可以看到该房间状态为‘Y’(触发器起作用)
select*fromOrderInfo--登记成功
----------调用存储过程把该类房间剩余量减一
select*fromRoomType--可以看到该房间类型还有个
execproc_find_stu1022---库存-1
--()顾客退房登记信息(订单表'
=NO表示顾客已经退房,同时房间表中该房间编号'
RoomState'
=N表示该房间空闲)
房间号为的顾客退房
select*fromCheckout
select*fromOrderInfo
select*fromRoom
insertintoCheckoutvalues(13003,'
select*fromCheckout--确定办理成功
select*fromOrderInfo
---同时调用存储过程把该类房间数量+1
execproc_find_stu21021---库存+1
----当客户退房时,登记成功,房间状态变为空闲状态(RoomState='
select*fromRoom--可以看到该房间状态为‘N’(触发器起作用)
select*fromOrderInfo--可以看到该ORstatic为‘NO’(触发器起作用)
--------------------------------------------------------------------------------------------------
--()查询当前酒店入住的客户数量
selectcount(*)as'
当前入住顾客人数'
fromOrderInfowhere(ORstatic='
---(5)查询-09-04的营业额
selectSUM(Total_consumption)fromCheckoutWHEREEndtime='
---(6)查询现在入住的客户中性别为男的客户信息
selectCustomerName,Csex,RoomID,Entertime,DepositfromCustomer,OrderInfo
where=andCsex='
---(7)查询现在入住的客人姓范的有那些
where=andCustomerNamelike'
范%'
---(7)查询当前入住'
'
的客人信息
selectCustomerName,,Enterti