酒店信息管理系统的开发

酒店信息管理数据库设计1、需求分析酒店信息管理数据库就是一个用来管理酒店楼层、客房、入住,账单、会员信息。 针对设计要求设计以下数据结构楼层信息，包含数据有Floorid. Floorname。客房类型信息”包含数据有:TypelD、Typename、Price 、binPrice OverdestineNum bination。客房信息表，包含数据有:RoomID、FloorlD、TypelD、GuestNumber、BedNumber、 RoomDesc、Memo State入住单信息表，包含数据有:ChecklnlD、VIPID、TypelD、TypelD、TypelD、LeaveTime、 BillState ChecklnNumber、RoomID、CheckinPrice 等。账单明细表，包含数据有:AccountID、ChecklnlDx ConsumeContent 等。预定表包含数据有:ChecklnlD VIPID TypelD ArriveTime LeaveTime BillState 等。会员表，包含数据有:VIPID、Name、Job、Phone EmaiL Memo。楼层F ooridFloor namee图bin Price客房类型OverdestineNumbination各厉来生tR NMemoRoomDescBedNumberChecklnNumb入住单BillStateV P DTypelD2、概念结构设计根据设计表要求,了解到该数据库包含客房.账单、会员客户。经过分析，画出了酒店关系得 ER图，分别如下：TypelDTypenamePriceStateRoomIDFloorlDTypelDnIDAccountIDChecklnlD客丿芳后思LeaveTimeCon sumeM oneyCon sumeTim客房类型(TypelD、Typename、Price binPrice OverdestineNum bination);客房信息(RoomID、FloorlD、TypelD、GuestNumber BedNumber RoomDesc Memo、State);入住单(ChecklnlD、VIPID、TypelD、ArriveTime、LeaveTime、Billstate、ChecklnNumbe、 RoomID、CheckinPrice、Disco unt、DiscountReas on、AddBed Ear nest、DestMan Destpany、Phone、Operator、Memo AmountRece ConsumeMoney Accountstate、 AccountTime PayMode);账单明细(AccountlD、ChecklnlD、ConsumeContent、Consumel/loney、ConsumeTime、 Memo);预订单(ChecklnlDVIPIDTypelDArriveTimeLeaveTime Billstate ChecklnNumber RoomID、CheckinPrice、Disco unt、DiscountReas on、AddBed Ear nest、DestMan Destpany、Phone OperatorMemo);会员表(VIPID、Name Job、Phone Email Memo);4、物理结构设计（1）楼层信息表Floorinfo字段名类型说明功能FlooiidInt非空，主键楼层编号FlooinameChar (3)(2) 客房类型表 GiiestRoomType字段名类型说明功能TypelDhit非空，主键类型编号TypenaineVailiai(40)类型名称PriceMoney价格buiPiiceMongev拼房价格OveidestineNumInt可超预定数bmationBit就是否可以拼房(3) 客房信息表 GiiestRoonilnfo字段名类型说明功能RoomIDhit非空，主键客房编号FlooiLDhit非空，外键楼层编号TypelDhit非空，外键类型编号GuestNumberhit大于0额定人数BedNumberhit大于0床数RoomDescVarchar (40)客房描述MemoVarchar(lOO)备注StateVaichar (4)默认值为“无人住”状态(4) 入住单 ChecklnBill字段名类型说明功能CheckliilDhit非空，主键，标识列入住单编号VIPIDhit非空，外键会员编号TypelDhit非空，外键客房类型AiiiveTimeDatetime到达时间LeaveTmieDatetime离开时间BillStateVarchar(20)单据状态CheckliiNumbefint入住人数RoomIDint非空，外键客房编号CheckinPiiceMoney入住价格DiscountFloat折扣DiscountReasonVarchar(20)折扣原因AddBedBit就是否加床EarnestMoney预收款DestManVarchar (20)预定人DestpanvVaichar (60)预定公司PhoneVarchar (15)联系电话OpeiatoiVaichar(lO)操作员MemoVarchar (50)备注AinountReceMoney应收账款ConsumeMoneyMoney消费金额AccountStateBit就是否结账AccountTimeDatetime结账日期PayModeVarchar (20)付款方式(5) 账单明细表BillDetail字段名类型说明功能AccountIDhit非空，主键账单编号CheckhilDLit非空，外键入住单编号ConsunieContentVarchar (40)消费内容ConsumeMoneyMoney消费金额ConsunieTuneDatetime消费时间MemoVaichai(100)备注(6 )预定单 BookingList字段名类型说明功能CheckhilDLit非空，主键，标识列入住单编号VIPIDLit非空，外键会员编号TvpelDLit非空，外键客房类型AinveTimeDatetime到达时间LeaveTmieDatetime离开时间BillStateVaichar(20)单据状态CheckhiNumbermt入住人数RoomIDmt非空，外键客房编号CheckuiPiiceMoney入住价格DiscountFloat折扣DiscountReasonVaichar(20)折扣原因AddBedBit就是否加床EarnestMoney预收款DestManVaichai (20)预定人DestpanvVarchar (60)预定公司PhoneVaichai(15)联系电话OpeiatoiVarchai(lO)操作员MemoVaichai (50)备注(7)会员表VIP字段名类型说明功能VIPIDLit非空，主键会员编号NameVaichai(lO)会员姓名JobVarchar (40)单位PhoneVaichai(15)长度大于等于11联系电话EmailVaichai (20)必须包含符号EmailMemoVaichai(SO)备注5、数据库实施a、用TSQL创建数据库-create database冏店管理系统onprimary（name= 画店管理系_datal，,f ilenain8=, e: 酒止管理系统_datal. jndf?, size=5,max si ze=unlijnite d,f ilegrovth=2）go100 % -歸消息 齢已换就。b、用TSQL语句创建所有得表及主键1）创建楼层信息表2）创建客房类型表3）创建客房信息表3）创建入住单信息表3435363738394041424344454647484950515253545556575859bb69707172737475767778798081S2838485868788= create table 入性单(CheckInlD int not null primary key, VIPID int not null,TypelD irrt,AtrriveTijne datetime,LeaveTime datetime, EillState varchar(20)? ChecklnliuiTiber int, RoomlD int not null, CheckinPrice Roney, Discount float,D is count Re as on. varchar (20),Ad-dBed bit,Earnest money,DestHan varchar(20),DestConipany varchar (60;,Phone varchar (20),Operator varchar(20)?MeiTLO varchar (50),AmountRece in.on.ey, ConsumeMoney Roney, AccountState bit, Account Time datetine.PayMode varchar(20)go4）创建账单明细表 5）创建预定表-create table 桢订单(IChecklnlD int not null primary key,VIPID int not null,TypelD int not null,ArriveTinLe datetimsLeaveTime datetime,BillState varchar(20)ChecklnlJujnber int?RoomlD int not null,CheckinPrice jnoney,Discount float,DiscountReason varchar (20) ?AddBed bit,Earnest money,DestMan varchar (20) 3DestCompany varchar (60)Phone varchar(15),Operator varchar(20),Memo varchar(50),)6）创建会员表8990 B create table 会员表(91VIPID int not null prijnary key.921Iain.e varchar (10),93Job varchar(40)?94Phone varchar C15) check (Phone=11)951 varchar (20) check (E_m.ail like = %鹼)?96Memo varchar(80)97O|C、用sql语句给需要设置外键得表Ealter table客房信息表add constraint搂层信息表窖房信息表 foreign key(FloorlD) references 桜层信息(Floorinfogoer table客房类型表constraint fk_客房信息表_客房类型表 foreign key(TypelD) REFERENCES 客房类型表(TypelD)Balter table 入住单add constraint会员表_入住单 foreign key(VIPID) REFERENCES 会员表(VIPID)goE alter table 入住单add constraint fk_客房类型表入住单 foreign ley(TypelD) references 客房类型(TypelD) goE： alt addreferences 客房信息表(RoojrID)er table入住单constraint fk_容房信息表-入住单 foreign key(RoomlD)GO118119 Halter table 预订单120 add constraint fk_会员表_预订单 foreign key(VIPID) REFERENCES 会员表(VIPID)121 go122123 Halter table 预订单add constraint fk_客房类型表预订单 foreign key(TypelD) references 客房类型表(TypelD)125 go126126 Halter table 预订单127 add constraint fk_客房信息表预订单 foreign key(RoomlD) references 客房信息表(RoomID)128 G0|D、使用insert初始化数据库131 Binsert into 楼层信(Floorinfo, Floornaike132 valuesC 39 / 309 )i0 %- 4fl消息(1行受影哨)I:-inser t into 客房 类型表(TypeID? Typename, Price, CombinPrice, Ove r de st ineNum, Combination;values( 1，总统套房，9999， 8989， 4， 1)6 oi1 EJinsert irrto VIPID? Maine, Job? Phone? E_jnail? Nemo.i8 values T / tac-er? 7 reporterf J 184697973 , 1341549901/ ,好看又可爱)E、查询数据库139140 select *from 会员表141142select froin楼层信息表% 4结果盟消息VIPIDNonePhoneE_mailMem o；1 1i:toer；reporter1846979731341549901 好看又可爱)%彳召结果餵消息Floorinfoz；Floornjrie301143 I144 select * from客房类型表 )0% - 结果爭消息1TypelDTypensme 总统套房PriceCorribiiLFri os Over des tiCdiribiiisiti on9999.008989.004116、数据库运行与维护完成数据库简单得设计后，实现增删改查功能。并对数据库进行维护、调整。