酒店信息管理組織系統(tǒng)的開(kāi)發(fā)

1、酒店信息管理數(shù)據(jù)庫(kù)設(shè)計(jì)1、需求分析酒店信息管理數(shù)據(jù)庫(kù)是一個(gè)用來(lái)管理酒店樓層、客房、入住,賬單、會(huì)員信息。 針對(duì)耐要求設(shè)計(jì)以下數(shù)據(jù)結(jié)構(gòu)樓層信息，包含數(shù)據(jù)有Floorid. Floorname?？头款愋托畔?，包含數(shù)據(jù)有：TypelD、Typename、Price、CombinPrice、 OverdestineNum、Combination。客房信息表，包含數(shù)據(jù)有：RoomID、FloorlD、TypelD、GuestNumber. BedNumber. RoomDesc、Memo. State入住單信息表，包含有:CheckInIDs VIPID、TypelD、TypelD、TypelD、 Le

2、aveTime. BillState、CheckInNumberx RoomID、CheckinPrice 等。賬單明細(xì)表，包含數(shù)據(jù)有：AccountID、ChecklnlD、ConsumeContent 等。預(yù)定表 包含數(shù)據(jù)有 ChecklnlD. VIPID. TypelD. ArriveTime. LeaveTime. BillState 等。會(huì)員表,包含數(shù)據(jù)有：VIPID、Name、Job、Phone. E-mailx Memoo2、概念結(jié)構(gòu)設(shè)計(jì)根據(jù)設(shè)計(jì)表要求,了解到該數(shù)據(jù)庫(kù)包含客房、賬單、會(huì)員客戶。經(jīng)過(guò)分析,畫出了酒店關(guān) 系的E-R圖,分別如下：MemoRoomID圖3客房信息ER圖

3、圖4入住單E-R圖圖5賬單明細(xì)E-R圖圖6預(yù)定表E-R圖會(huì)員表(VIP)E-mail圖7會(huì)員表E-R圖通過(guò)對(duì)個(gè)實(shí)體之間聯(lián)系的分析,將局部E-T圖綜合成全局E-R圖樓層會(huì)員表賬單明細(xì)客房類型客房信息3、邏輯結(jié)構(gòu)設(shè)計(jì)根據(jù)概念設(shè)計(jì)的結(jié)果，將其轉(zhuǎn)換為關(guān)系模型：樓層(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 );賬單明細(xì)(AccountID. ChecklnlD. ConsumeContent. ConsumeMoney

5、x ConsumeTime. Memo );預(yù)訂單(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 );會(huì)員表(VIPID、Name. Job、Phone. E-mailx Memo);4、物理結(jié)構(gòu)設(shè)計(jì)(1)樓層信息表Floorinfo字段名類型說(shuō)明功能FlooridInt非

6、空，主鍵樓層編號(hào)FloornameChar (3 )(2)客房粥漿 GuestRoornType字段名類型說(shuō)明功能TypelDInt非空，主鍵類型編號(hào)Type nameVarChar (40類型名稱PriceMoney價(jià)格Combi nPriceMon gey拼房?jī)r(jià)格OverdestineNu mInt可超預(yù)定數(shù)Combi nationBit是否口以拼房(3)客房信息表 GuestRoomlnfo字段名類型說(shuō)明功能RoomIDInt非空，主鍵客房編號(hào)FloorlDInt非空,外鍵樓層編號(hào)TypelDInt非空,外鍵類型編號(hào)GuestNumberInt大于0額定人數(shù)BedNumberInt大于0

7、床數(shù)RoomDescVarchar (40 )客房描述MemoVarchar (100備注StateVarchar (4 )默認(rèn)值為"無(wú)人住"狀態(tài)(4) AfSIChecklnBill字段名類型說(shuō)明功能ChecklnlDInt非空，主鍵，標(biāo)識(shí)列入住單編號(hào)VIPIDInt非空，外鍵會(huì)員編號(hào)TypelDInt非空，外鍵客房類型ArriveTimeDatetime到達(dá)時(shí)間LeaveTimeDatetime離開(kāi)時(shí)間BillStateVarchar(20)單據(jù)狀態(tài)CheckI nN umberint入住人數(shù)RoomIDint非空,外鍵客房編號(hào)Checki nPriceMoney入住價(jià)

8、格Disco untFloat折扣DiscountReas onVarchar(20)折扣原因AddBedBit是否加床Ear nestMoney預(yù)收款DestMa nVarchar ( 20 )預(yù)定人DestCompa nyVarchar ( 60 )預(yù)定公司PhoneVarchar ( 15 )聯(lián)系電話OperatorVarchar(lO)操作員MemoVarchar ( 50 )備注Amoun tReceMoney應(yīng)收賬款ConsumeMo neyMoney消費(fèi)全額Accoun tStateBit是否結(jié)賬Acco un tTimeDatetime結(jié)賬日期PayModeVarchar (

9、20 )付款方式(5)賬單明細(xì)表BillDetail字段名類型說(shuō)明功能AccountIDInt非空，主鍵賬單編號(hào)ChecklnlDInt非空，外鍵入住單編號(hào)Con sumeC onten tVarchar ( 40 )消費(fèi)內(nèi)容Con sumeM oneyMoney消費(fèi)全額Con sumeTimeDatetime消費(fèi)時(shí)間MemoVarchar (100備注(6)BookingList字段名類型說(shuō)明功能ChecklnlDInt非空，主鍵，標(biāo)識(shí)列入住單編號(hào)VIPIDInt非空,外鍵會(huì)員編號(hào)TypelDInt非空,外鍵客房類型ArriveTimeDatetime到達(dá)時(shí)間LeaveTimeDatetim

10、e離開(kāi)時(shí)間BillStateVarchar(20)單據(jù)狀態(tài)CheckI nNu mberint入住人數(shù)RoomIDint非空,外鍵客房編號(hào)Checki nPriceMoney入住價(jià)格Disco untFloat折扣Disco un tReas onVarchar(20)折扣原因AddBedBit是否加床Ear nestMoney預(yù)收款DestMa nVarchar ( 20 )預(yù)定人DestCompa nyVarchar ( 60 )預(yù)定公司PhoneVarchar ( 15 )聯(lián)系電話OperatorVarchar(lO)操作員MemoVarchar ( 50 )備注(7)會(huì)員表VIP字段名

11、類型說(shuō)明功能VIPIDInt非空，主鍵會(huì)員編號(hào)NameVarchar ( 10 )會(huì)員姓名JobVarchar ( 40 )單位PhoneVarchar ( 15 )長(zhǎng)度大于等于11聯(lián)系電話E-mailVarchar ( 20 )必須包含符號(hào)E-mailMemoVarchar ( 80 )備注5、數(shù)據(jù)庫(kù)實(shí)施 a.用T-SQL創(chuàng)建數(shù)據(jù)庫(kù)-create database冏店管理系統(tǒng)onprimary(nime="西店管理系貌_(1沆31' 3f ilename=? e: 酒店管理_datal. jndf7,size=5,jriaxsize=unliiTi it ed,f ileg

12、rovth=2)go100 % 歸消息 齢已換就。b、用T-SQL語(yǔ)句創(chuàng)建所有的表及主鍵1 )創(chuàng)建樓層信息表 create table樓層信息夷(Flo or Info int not null primary key, Floorname char (3)go2)創(chuàng)建客房類型表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）創(chuàng)

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）創(chuàng)建入住單信息表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）創(chuàng)建賬單明細(xì)表6061626364656667Ecreate table賬單明細(xì)表(Ac count ID int not null primary key, ConsumeCont ent varchar (40),ConsuineMoney money,ConswneTi

16、jne datetime,Memo varchar (100)go5）創(chuàng)建預(yù)定表bb6970717273747576777879808182S38485868788899091929394959697create table 預(yù)訂單(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）創(chuàng)建會(huì)員表E create table 會(huì)員表(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語(yǔ)句給需要設(shè)置外鍵的表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會(huì)員表_入住單 foreign key(VIPID) REFERENCES 會(huì)員表(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 預(yù)訂單120 add constraint fk_會(huì)員表_預(yù)訂單

20、 foreign key(VIPID) REFERENCES 會(huì)員表(VIPID)121 go122123 Halter table 預(yù)訂單add constraint fk_客房類型表預(yù)訂單 foreign key(TypelD) references 客房類型表(TypelD)125 go126126 Halter table 預(yù)訂單127 add constraint fk_客房信息表預(yù)訂單 foreign key(RoomlD) references 客房信息表(RoomID)128 G0|D、使用insert初始化數(shù)據(jù)庫(kù)1 JU131 Einsert into 樓層信(Floorinfo, F