酒店管理系统数据库代码

酒店管理系统数据库代码酒店管理系统数据库代码PAGEPAGE1/81/8酒店管理系统数据库代码useHotel_Management1select*fromCustomerselect*fromEmployeeselect*fromRoomTypeselect*fromRoomselect*fromOrderInfoselect*fromCheckoutdropdatabaseHotel_Management1 创 建 数 据 库Hotel_ManagementcreatedatabaseHotel_MDBonprimary(name=Hotel_Management1,='F:\Hotel_Management\Hotel_Management.MDF',size=10MB,=20%)logon(name=Hotel_Management1,='F:\Hotel_Management\Hotel_Management1.LDF',size=10MB,=2MB)--使用数据库USEHotel_Management1 创建表 --1顾客表createtableCustomer(CustomerIDintprimaryCustomerNamenvarchar(40)notnull,CustomerInfonvarchar(18)notnull,Csexnvarchar(1),CPhonenvarchar(11)notnull,Notesntext)--droptableCustomer--2员工表酒店管理系统数据库代码酒店管理系统数据库代码PAGEPAGE2/82/8createtableEmployee(EmployeeIDintprimarykey,UserNamenvarchar(40)notnull,Passwordnvarchar(40)notnull,EmployeeNamenvarchar(40)notnull,Esexnvarchar(1),EPhonenvarchar(11)notnull,Notesntext)--3客房表()createtableRoom(RoomIDintprimaryRoomTypeIDintnotnull,RoomStatenvarchar(1)notnull,Notesntext,FOREIGNKEY(RoomTypeID)REFERENCESRoomType(RoomTypeID),)--droptableRoom--4客房类型表)createtableRoomType(RoomTypeIDintprimaryRoomTypeNamenchar(20)notnull,Costfloat,Totalint,Surplusint,Notesntext,)--droptableRoomType--5订房表select*fromOrderInfocreatetableOrderInfo(OrderIDintnotnullprimarykey,RoomIDintnotnull,CustomerIDint,EmployeeIDint,Entertimedatetimenotnull,Depositfloat,ORstaticnvarchar(10)notnull Notesntext,FOREIGNKEY(CustomerID)REFERENCESCustomer(CustomerID),FOREIGNKEY(RoomID)REFERENCESRoom(RoomID),酒店管理系统数据库代码酒店管理系统数据库代码PAGEPAGE3/83/8FOREIGNKEY(EmployeeID)REFERENCESEmployee(EmployeeID),)--altertable addconstraintOI_DORstaticdefault'use'--droptableOrderInfo--6退房表check-outcreatetableCheckout(CheckoutIDintprimarykey,RoomIDintnotnull,CustomerIDint,EmployeeIDint,Entertimedatetimenotnull,Endtimedatetimenotnull,Total_consumptionfloat,Notesntext,FOREIGNKEY(EmployeeID)REFERENCESEmployee(EmployeeID),FOREIGNKEY(CustomerID)REFERENCESCustomer(CustomerID),)--droptableCheckoutsp_helpCheckout 表 插 入 信 息--Employee表insertintoEmployeevalues('zhoutonglu',123456,'董洁','f',,null)insertintoEmployeevalues('liminghao',123456,'李明浩','m',,null)insertintoEmployeevalues('yuxian',123456,'余香','f',,null)select*fromEmployeeRoomType表select*fromRoomTypeinsertintoRoomTypevalues(1,'单间',200,20,19,null)insertintoRoomTypevalues(2,'标准间',260,20,19,null)insertintoRoomTypevalues(3,'豪华单间',580,20,19,null)insertintoRoomTypevalues(4,'行政套房',880,20,19,null)Room表select*fromRoominsertintoRoomvalues('1011',1,'Y',null)insertintoRoomvalues('1012',1,'N',null)insertintoRoomvalues('1021',2,'Y',null)酒店管理系统数据库代码酒店管理系统数据库代码PAGEPAGE4/84/8insertintoRoomvalues('1022',2,'N',null)insertintoRoomvalues('1031',3,'Y',null)insertintoRoomvalues('1032',3,'N',null)insertintoRoomvalues('1041',4,'Y',null)insertintoRoomvalues('1042',4,'N',null)insertintoRoomvalues('1013',1,'Y',null)insertintoRoomvalues('1014',1,'N',null)insertintoRoomvalues('1023',2,'Y',null)insertintoRoomvalues('1024',2,'N',null)insertintoRoomvalues('1033',3,'Y',null)insertintoRoomvalues('1034',3,'N',null)insertintoRoomvalues('1051',4,'Y',null)insertintoRoomvalues('1052',4,'N',null)Customer表select*fromCustomerselect*fromRoominsertintoCustomervalues('刘德华',,'m',,null)insertintoCustomervalues('张更硕',,'m',,null)insertintoCustomervalues('周辉',,'m',16376156666,null)insertintoCustomervalues('刘美美',,'f',,null)insertintoCustomervalues('范冰冰',,'f',,null)insertintoCustomervalues('佟大为',,'m',,null)insertintoCustomervalues('范玮琪',,'f',16376156666,null)insertintoCustomervalues('陈小春',,'m',,null)insertintoCustomervalues('kenim',,'m',,null)--OrderInfo表select*fromOrderInfoinsertintoOrderInfovalues(9001,'1011',1,1,'2013-09-039:00PM',250.00,'use',null)insertintoOrderInfovalues(9002,'1021',2,2,'2013-09-057:00PM',300.00,'use',null)insertintoOrderInfovalues(9003,'1031',3,2,'2013-09-048:00PM',600.00,'use',null)insertintoOrderInfovalues(9004,'1041',4,2,'2013-09-122:00PM',1000.00,'use',null)insertintoOrderInfovalues(9005,'1021',9,2,'2013-09-047:00PM',300.00,'use',null)insertintoOrderInfovalues(9006,'1031',10,2,'2013-09-048:00PM',600.00,'use',null)--insertintoOrderInfovalues(9007,'1041',11,2,'2013-09-42:00PM',1000.00,'use',null)execproc_find_stu1041库存-1--insertintoOrderInfovalues(9005,'1012',1,1,'2013-09-039:00PM',250.00,'use',null)--deleteOrderInfowhereOrderIDin(9005)--droptableOrderInfoCheckout表insertintoCheckoutvalues(13001,'1011',1,2,'2013-09-039:00PM','2013-09-04',200,NULL)insertintoCheckoutvalues(13002,'1021',2,2,'2013-09-033:00PM','2013-09-04',200,NULL)酒店管理系统数据库代码酒店管理系统数据库代码PAGEPAGE5/85/8insertintoCheckoutvalues(13003,'1031',3,2,'2013-09-0310:00PM','2013-09-04',200,NULL)--insertintoCheckoutvalues(13004,'1041',4,2,'2013-09-038:00PM','2013-09-04',200,NULLinsertintoCheckoutvalues(13003,'1021',9,2,'2013-09-0310:00PM','2013-09-04',880,NULL)deleteCheckoutwhereCheckoutIDin(13001,13002)--droptableCheckoutselect*fromCheckoutselect*fromOrderInfoselect*fromRoomTypeselect*fromRoomexecproc_find_stu1041库存-1execproc_find_stu21011库存+1--insertintoCheckoutvalues('O2001','R003',1002,2,'2013-09-06',570,NULL)--insertintoCheckoutvalues('O2002','R001',1003,2,'2013-09-04',570,NULL) 创 建 触 发 器--1\创建客房使用状态触发器(插入)createtriggerRoomState_1onOrderInfoforinsertasupdateRoomsetRoomState='Y'whereRoomID=any(selectRoomIDfromINSERTED)--droptriggerRoomState_1--2-创建客房使用状态触发器（删除）createtriggerRoomState_2onCheckoutforinsertasupdateRoomsetRoomState='N'whereRoomIDin(selectRoomIDfromINSERTED)--droptriggerRoomState_23--创建修改订单状态触发器createtriggerORstatic_1onCheckout酒店管理系统数据库代码酒店管理系统数据库代码PAGEPAGE6/86/8forinsertasupdateOrderInfosetwhereRoomID=any(selectRoomIDfromINSERTED)--droptriggerORstatic_1 储过程 --1--创建修改客房库存触发器（减少）createprocproc_find_stu(@startIdint)asupdateRoomTypesetSurplus=(Surplus-1)whereRoomTypeIDin(selectRoomTypeIDfromRoomwhereRoomID=@startId)goexecproc_find_stu1011--2--创建修改客房库存触发器（增长createprocproc_find_stu2(@startIdint)asupdateRoomTypesetSurplus=(Surplus+1)whereRoomTypeIDin(selectRoomTypeIDfromRoomwhereRoomID=@startId)goexecproc_find_stu21011系统功能流程useHotel_Management1select*fromCustomerselect*fromEmployeeselect*fromRoomTypeselect*fromRoomselect*fromOrderInfoselect*fromCheckout--（）顾客入住员工查询闲置房间select*fromRoomwhereRoomState='N'select RoomID,RoomTypeName,RoomState,Cost,Total,Surplus from whereandRoomState='N'A。顾客要求住'标准间'select RoomID,RoomTypeName,RoomState,Cost,Total,Surplus fromRoom,RoomTypewhere and RoomState='N' 酒店管理系统数据库代码酒店管理系统数据库代码PAGEPAGE7/87/8RoomTypeName='标准间'B。顾客要求住'豪华单间'select RoomID,RoomTypeName,RoomState,Cost,Total,Surplus fromRoom,RoomTypewhere and RoomState='N' RoomTypeName='豪华单间'C。顾客要求住'行政套房'或是'豪华单间'select RoomID,RoomTypeName,RoomState,Cost,Total,Surplus fromwhere and RoomState='N' andRoomTypeNamein('豪华单间','行政套房')登记住房订单信当客户入住房房间状态变为使用状(RoomState='Y'，订单表'ORstatic'=USE表示顾客入住登记成功--例如：某客人（林大帅）要入住“标准客房”--1.员工查询是否还有闲置“标准客房”（还剩下个房间）select RoomID,RoomTypeName,RoomState,Cost,Total,Surplus fromwhere and RoomState='N' andRoomTypeName='标准间'select*fromCustomerinsertintoCustomervalues('林大帅',,'m',,null)select*fromCustomer3.登记订单信息insertintoOrderInfovalues(9007,'1022',12,2,'2013-09-42:00PM',400.00,'use',null)select*fromRoom--可以看到该房间状态为‘Y’（触发器起作用）select*fromOrderInfo--登记成功调用存储过程把该类房间剩余量减一select*fromexecproc_find_stu库存-1--（）顾客退房登记信息(订单表'ORstatic'=NO号'RoomState'=N)--例如：房间号为的顾客退房select*fromCheckoutselect*fromOrderInfoselect*fromRoominsertintoCheckoutvalues(13003,'1021',9,2,'2013-09-0310:00PM','2013-09-04',880,NULL)select*fromCheckou