酒店信息管理数据库设计1.需求分析酒店信息管理数据库是一个用来管理酒店楼层、客房、入住，账单、会员信息。 针对设计要求设计以下数据结构楼层信息，包含数据有Floorid. Floor name 客房类型信息，包含数据有：TypelD x Typename . Price 、Comb inPrice OverdestineNum、 Combination 。客房信息表，包含数据有:Room ID FloorlD. TypelD. GuestNumber. BedNumber. RoomDesc. Memo、 State入住单信息表，包含数据有：ChecklnlD. TPID、TypelD、TypelD、TypelD、 LeaveTime BillState CheckInNumber RoomID. CheckinPrice 等。账单明细表，包含数据有：AccountID. ChecklnlD, ConsumeContent 等。 预定表，包含数据有:ChecklnlD、VIPID、TypelD. ArriveTime, LeaveTime、 BillState 等。会员表，包含数据有：VIPID. Name、Job、Phone、E-mai 1. Memo。2、概念结构设计根据设计表要求，了解到该数据库包含客房、账单.会员客户。经过分析，画出了酒店关 系的E-R图，分别如I、:客房信息GuestNumber图3客房信息E-R图图4入住单E-R图客房信息3、逻辑结构设计根据概念设计的结果将其转换为关系模型：楼层(Floorid、Floorname)；客 房类型(TypelD、Type name . Price、Comb in Price、OverdestineNum、 Combi nati on)；客房信息(RoomlD、FloorlD. TypelD. GuestNumberBedXumber. RoomDesc、 Memo. State)；入住单(ChecklnlD、VIPID、TypelD, ArriveTime, LeaveTime. BillState、 ChecklnNumberRoomlD、CheckinPrice、Discount、DiscountReason、AddBed、 Earnest、DestMan、DestCompany、Phone.Operator、Memo、AmountRece、ConsumeMoney、 AccountStateN AccountTime. PayMode)；账单明细(Account ID ChecklnlD. ConsumeContent. ConsumeMoney. ConsumeTime. Memo)；预订单(ChecklnlD、VIPID、TypelD、ArriveTimeLeaveTime. BillState、 ChecklnNumber. RoomID. CheckinPrice、Discount. DiscountReason. AddBed、 Earnest、 DestManDestCompanyPhone. OperatorMemo)；会员表(VIPID. Name. Job、Phone、E-mailMemo)；4、物理结构设计(1)楼层信息表Floorinfo字段名类型说明功能FlooridInt非空，主键楼层编号FloornameChar (3)(2)客房类型表 GuestRoomType字段名类型说明功能TypelDInt非空，主键类型编号TypenameVaiChar (40)类型名称PriceMoney价格CombinPriceMongey拼房价格OverdestineNumInt可超预定数CombinationBit是否可以拼房(3)客房信息表 GuestRoomlnfo字段名类型说明功能RoomIDInt非空，主键客房编号FloorlDInt非空，外键楼层编号TypeIDInt非空，外键类型编号GuestNumberInt大于0额定人数BedNumberInt大于0床数RoomDescVarchar (40)客房描述MemoVarchar (100)备注StateVarchar (4)默认值为“无人住”状态(4)入住单 ChecklnBill字段名类型说明功能ChecklnlDInt非空，主键，标识列入住单编号VIPIDInt非空，外键会员编号TypelDInt非空，外键客房类型ArriveTimeDatetime到达时间LeaveTimeDatetime离开时间BillStateVarchar(20)单据状态ChecklnNumberint入住人数RoomlDint非空，外键客房编号CheckinPriceMoney入住价格DiscountFloat折扣DiscountRcasonVarchar(20)折扣原因AddBedBit是否加床EarnestMoney预收款DestManVarchar (20)预定人DestCompanyVarchar (60)预定公司PhoneVarchar (15)联系OperatorVarchar(10)操作员MemoVarchar (50)备注AmountReceMoney应收账款ConsumeMoneyMoney消费金额AccountStateBit是否结账AccountTimeDatetime结账日期PayModeVarchar (20)付款方式(5)账单明细表BillDetail字段名类型说明功能AccountIDInt非空，主键账单编号ChecklnlDInt非空，外键入住单编号ConsumeContentVarchar (40)消费容ConsumeMoneyMoney消费金额ConsumeTimeDatetime消费时间MemoVarchar (100)备注(6)预定单 BookingList字段名类型说明功能ChecklnlDInt非空，主键，标识列入住单编号VIPIDInt非空，外键会员编号TypelDInt非空，夕卜键客房类型ArriveTimeDatetime到达时间LeaveTimeDatetime离开时间BillStateVarchar(20)单据状态CheckInNumberint入住人数RoomIDint非空，外键客房编号CheckinPriceMoney入住价格DiscountFloat折扣Disco tin tReaso nVarchar (20)折扣原因AddBedBit是否加床EarnestMoney预收款DestManVarchar (20)预定人DestCompanyVarchar (60)预定公司PhoneVarchar (15)联系OperatorVarchar(10)操作员MemoVarchar (50)备注(7)会员表VIP字段名类型说明功能VIPIDInt非空，主键会员编号NameVarchar (10)会员JobVarchar (40)单位PhoneVarchar (15)长度大于等于11联系E-mailVarchar (20)必须包含符号E-mailMemoVarchar (80)备注5、数据库实施a、用T-SQL创建数据库Hcreate database酒店管理亲统npriniary(najae=,曆店管理系统_datal5,f ilen.ajne=：，e:店管理系 _datal. mdf ,size=5,maxsiz e=unliiTi it ed,filegrovth=2)go100% 眾消息命令已换完成。b、用T-SQL语句创建所有的表及主键1) 创建楼层信息表-create tab*楼层信息表(FloorInfo int “ot tluII primary k&y,FLoorname char(3j Jgo2) 创建客房类型表Ecreate table客房类型克(TypelD in.t not null priniary key,Typenane varchar (40),Price Money,CojnbinPrice money, OverdestineNuin int? | CoiTibination bit) go.3) 创建客房信息表24-create table客房信息表25(RoomlD int not null primary key.26FloorlD int not null ,27TypelD int not null,28EGuestNumber int,29BedlIujriber int.30ERooiriDesc varchar (40),31Memo varchar(100),32State varchar(4)333）创建入住单信息表60616263646566673435 Ecreate table 入住单36(Ch.ecklnlD in.t not null primary key37VIP ID int not null,38TypeID int,39ArriveTime datetime.40LeaveTime datetime.41BillState varchar(20),42Che cilhNiunber int.43RoomID int not null,44CheciiriPrice money.45Discount float,46DiscountReason varchar(20L47AddBed bit,48Earnest mon&y.49DestMan varchar(20),50DestCoiripany varchar (60),51Phone varchar(20A52Operator varchar(20),