酒店信息管理组织系统的开发

1、酒店信息管理数据库设计1、需求分析酒店信息管理数据库是一个用来管理酒店楼层、客房、入住,账单、会员信息。 针对耐要求设计以下数据结构楼层信息，包含数据有Floorid. Floorname。客房类型信息，包含数据有：TypelD、Typename、Price、CombinPrice、 OverdestineNum、Combination。客房信息表，包含数据有：RoomID、FloorlD、TypelD、GuestNumber. BedNumber. RoomDesc、Memo. State入住单信息表，包含有:CheckInIDs VIPID、TypelD、TypelD、TypelD、 Le

2、aveTime. BillState、CheckInNumberx RoomID、CheckinPrice 等。账单明细表，包含数据有：AccountID、ChecklnlD、ConsumeContent 等。预定表 包含数据有 ChecklnlD. VIPID. TypelD. ArriveTime. LeaveTime. BillState 等。会员表,包含数据有：VIPID、Name、Job、Phone. E-mailx Memoo2、概念结构设计根据设计表要求,了解到该数据库包含客房、账单、会员客户。经过分析,画出了酒店关 系的E-R图,分别如下：MemoRoomID图3客房信息ER图

3、图4入住单E-R图图5账单明细E-R图图6预定表E-R图会员表(VIP)E-mail图7会员表E-R图通过对个实体之间联系的分析,将局部E-T图综合成全局E-R图楼层会员表账单明细客房类型客房信息3、逻辑结构设计根据概念设计的结果，将其转换为关系模型：楼层(Flooid、Floorname );客房类型(TypelD、Type name、Price、CombinPrice. OverdestineNum. Combi nation );客房信息(RoomID、FloorlD、TypelDx GuestNumber. BedNumber. RoomDesc. Memo. State );入住单(

4、ChecklnlD. VIPID、TypelD、ArriveTimex LeaveTime. BillState、 CheckI nN umber. RoomID、Checki nPrice、Disco unt、DiscountReas on、AddBed、 Ear nest、DestMa n、DestCompa ny、Phone、Operator. Memo. AmountRecex ConsumeMoneyx AccountState、AccountTimes PayMode );账单明细(AccountID. ChecklnlD. ConsumeContent. ConsumeMoney

5、x ConsumeTime. Memo );预订单(CheckInIDx VIPIDX TypelD. ArriveTimex LeaveTime. BillState、 Checki nN umber. RoomID、Checki nPrice、Disco unt、DiscountReas on、AddBed. Ear nest. DestMa n、DestCompa ny、Phon 巳 Operator. Memo );会员表(VIPID、Name. Job、Phone. E-mailx Memo);4、物理结构设计(1)楼层信息表Floorinfo字段名类型说明功能FlooridInt非

6、空，主键楼层编号FloornameChar (3 )(2)客房粥漿 GuestRoornType字段名类型说明功能TypelDInt非空，主键类型编号Type nameVarChar (40类型名称PriceMoney价格Combi nPriceMon gey拼房价格OverdestineNu mInt可超预定数Combi nationBit是否口以拼房(3)客房信息表 GuestRoomlnfo字段名类型说明功能RoomIDInt非空，主键客房编号FloorlDInt非空,外键楼层编号TypelDInt非空,外键类型编号GuestNumberInt大于0额定人数BedNumberInt大于0

7、床数RoomDescVarchar (40 )客房描述MemoVarchar (100备注StateVarchar (4 )默认值为"无人住"状态(4) AfSIChecklnBill字段名类型说明功能ChecklnlDInt非空，主键，标识列入住单编号VIPIDInt非空，外键会员编号TypelDInt非空，外键客房类型ArriveTimeDatetime到达时间LeaveTimeDatetime离开时间BillStateVarchar(20)单据状态CheckI nN umberint入住人数RoomIDint非空,外键客房编号Checki nPriceMoney入住价

8、格Disco untFloat折扣DiscountReas onVarchar(20)折扣原因AddBedBit是否加床Ear nestMoney预收款DestMa nVarchar ( 20 )预定人DestCompa nyVarchar ( 60 )预定公司PhoneVarchar ( 15 )联系电话OperatorVarchar(lO)操作员MemoVarchar ( 50 )备注Amoun tReceMoney应收账款ConsumeMo neyMoney消费全额Accoun tStateBit是否结账Acco un tTimeDatetime结账日期PayModeVarchar (

9、20 )付款方式(5)账单明细表BillDetail字段名类型说明功能AccountIDInt非空，主键账单编号ChecklnlDInt非空，外键入住单编号Con sumeC onten tVarchar ( 40 )消费内容Con sumeM oneyMoney消费全额Con sumeTimeDatetime消费时间MemoVarchar (100备注(6)BookingList字段名类型说明功能ChecklnlDInt非空，主键，标识列入住单编号VIPIDInt非空,外键会员编号TypelDInt非空,外键客房类型ArriveTimeDatetime到达时间LeaveTimeDatetim

10、e离开时间BillStateVarchar(20)单据状态CheckI nNu mberint入住人数RoomIDint非空,外键客房编号Checki nPriceMoney入住价格Disco untFloat折扣Disco un tReas onVarchar(20)折扣原因AddBedBit是否加床Ear nestMoney预收款DestMa nVarchar ( 20 )预定人DestCompa nyVarchar ( 60 )预定公司PhoneVarchar ( 15 )联系电话OperatorVarchar(lO)操作员MemoVarchar ( 50 )备注(7)会员表VIP字段名

11、类型说明功能VIPIDInt非空，主键会员编号NameVarchar ( 10 )会员姓名JobVarchar ( 40 )单位PhoneVarchar ( 15 )长度大于等于11联系电话E-mailVarchar ( 20 )必须包含符号E-mailMemoVarchar ( 80 )备注5、数据库实施 a.用T-SQL创建数据库-create database冏店管理系统onprimary(nime="西店管理系貌_(1沆31' 3f ilename=? e: 酒店管理_datal. jndf7,size=5,jriaxsize=unliiTi it ed,f ileg

12、rovth=2)go100 % 歸消息 齢已换就。b、用T-SQL语句创建所有的表及主键1 )创建楼层信息表 create table楼层信息夷(Flo or Info int not null primary key, Floorname char (3)go2)创建客房类型表I- create tble客房类型克 (IypelD int nc-t null primary key, Typenaine varchar (40), Price Money, CombinPrice money7 Over des tine Num ini., | Combination, bit) go3）创

13、建客房信息表24252627282930313233Ecreate table客雳信息衷 (RoomlD int not null primary key.Floor ID int nc-t null ,Type ID int not null?EGuestNumber int,Bedl'Imnber int,I-KoomDesc varchar (40) ?BMemo varchar(100),State varchar (4)、3）创建入住单信息表3435 363738394041424344454647484950515253545556575859create table 入住

14、单.ChecklnlD int not null primary key, VIPID int not null,TypelD int,ArriveTijne datetline.Leaverime datetine,BillState varchar(20),CheckInllumber int,RoomlD int not null,CheckinPrice money,Discount float,DiscountReason. varchar (20),AddBed bit,Earnest money?DestMan varchar(20),DestCompany varchar(60

15、j ,Phone varchar(20)?Operator varchar(20)7Mem.0 varchar (50),AmountRece m.on.ey,ConsumeMoney nioney,AccountState bit.Account Time datetine.PayMode varchar(20)go4）创建账单明细表6061626364656667Ecreate table账单明细表(Ac count ID int not null primary key, ConsumeCont ent varchar (40),ConsuineMoney money,ConswneTi

16、jne datetime,Memo varchar (100)go5）创建预定表bb6970717273747576777879808182S38485868788899091929394959697create table 预订单(ChecklnlD int not null primary key, VIPID int not null,TypelD int ncrt null?Ar rive Time datetiine,LeaveTime datetime,BillSt2te varchar(20) 7 ChecklnNujnber int, RoomlD int not null,C

17、heckinPrice Jioney,Discount float,DiscountReason varchar (20) ?AddBed bit.Earnest money, DestMan varchar (20) ?DestConLpany varchar (60),Phone varchar(15), Operator varchar(20)? Memo varchar(50),)6）创建会员表E create table 会员表(VIP ID int not null primary key,Mame varchar (10),Job varchar (40),Phone varch

18、ar (15) check (Phone>=11),varchar (20) check (E_mail like '廻炉)?Kemo varchar (80)golC、用sql语句给需要设置外键的表Halter table 客房fl息表add constraint怨层信息表咨房信息表 foreign key(FloorlD) references 桜层信息(Floorinfogoer table客房类型表constraint fk_客房信息表_客房类型表 foreign key(TypelD) REFERENCES 客房类型表(TypelD)E alter table 入住单a

19、dd constraint会员表_入住单 foreign key(VIPID) REFERENCES 会员表(VIPID)goE alter table 入住单add constraint fk_客房类型表入住单 foreign l<ey(TypelD) references 客房类型(TypelD) goE： alt addreferences 客房信息表(RoojrID)er table入住单constraint fk_容房信息表-入住单 foreign key(RoomlD)GO118119 Halter table 预订单120 add constraint fk_会员表_预订单

20、 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初始化数据库1 JU131 Einsert into 楼层信(Floorinfo, F