【正文】 表 3－4 房態(tài)信息列名 數(shù)據(jù)類型 可否為空 說(shuō)明Rno char(10) not null 房間號(hào)Gno char(20) not null 顧客號(hào)Atime datetime 實(shí)際入住時(shí)間Ltime datetime 實(shí)際退房時(shí)間Rtime datetime 預(yù)訂入住時(shí)間Rltime datetime 預(yù)訂退房時(shí)間IntoPrice float 入住價(jià)格Days int 入住天數(shù)Stime datetime 修改記錄時(shí)間flag char(1) 預(yù)訂入住標(biāo)志位表 3－5 娛樂(lè)項(xiàng)目信息列名 數(shù)據(jù)類型 可否為空 說(shuō)明Atno char(20) not null 娛樂(lè)項(xiàng)目編號(hào)Atname char(20) not null 娛樂(lè)項(xiàng)目名稱Atprice float not null 娛樂(lè)項(xiàng)目單價(jià)（時(shí)間計(jì)費(fèi)或個(gè)數(shù)計(jì)費(fèi)）表 3－6 娛樂(lè)消費(fèi)信息列名 數(shù)據(jù)類型 可否為空 說(shuō)明Gno char(20) not null 顧客號(hào)Atno char(20) not null 商品號(hào)Amount float 消費(fèi)數(shù)量Wtime datetime not null 消費(fèi)開(kāi)始時(shí)間表 3－7 物品賠償信息列名 數(shù)據(jù)類型 可否為空 說(shuō)明Gno char(20) not null 顧客號(hào)Rno char(10) not null 房間號(hào)Goodsno char(20) not null 物品號(hào)Dnum int 損壞個(gè)數(shù)Amendstime datetime Datetime 損壞時(shí)間.. . . ..學(xué)習(xí)好幫手 用戶子模式定義表 3－8 訂房視圖列名 數(shù)據(jù)類型 可否為空 說(shuō)明Gno char(20) not null 顧客號(hào)Rno char(10) not null 房間號(hào)Rtype char(20) not null 房間類型Rfloor smallint not null 房間樓層Toward char(10) not null 房間朝向IntoPrice float not null 入住價(jià)格Rtime datetime not null 入住時(shí)間Rltime datetime not null 打算退房時(shí)間Days int not null 居住天數(shù)Wtime datetime not null 查看時(shí)時(shí)間表 3－9 入住視圖列名 數(shù)據(jù)類型 可否為空 說(shuō)明Gno char(20) not null 顧客號(hào)Gname char(20) not null 顧客姓名Rno char(10) not null 房間號(hào)Rtype char(20) not null 房間類型Rfloor smallint not null 房間樓層Toward char(10) not null 房間朝向IntoPrice float not null 入住價(jià)格Atime datetime not null 入住時(shí)間Ltime datetime not null 退房時(shí)間Days int not null 入住天數(shù)Account float not null 預(yù)付金額Wtime datetime not null 查看時(shí)時(shí)間表 3－10 空房視圖列名 數(shù)據(jù)類型 可否為空 說(shuō)明Rno char(10) not null 房間號(hào)Rtype char(20) not null 房間類型.. . . ..學(xué)習(xí)好幫手Rprice float not null 入住價(jià)格Rfloor int not null 房間樓層Toward char(10) not null 房間朝向附錄 4 SQL 語(yǔ)句 建立數(shù)據(jù)表 SQL 語(yǔ)句（1）顧客基本信息表的建立：create table guest( Gno char(20) not null, Gname char(20)not null, Gsex char(20) not null, Gid char(18) unique not null, Gtel char(11), Gaddress char(20), Account float, Grade int, discount float not null, balance float, primary key (Gno), check (Account = and Grade0) )（2）客房基本信息表的建立：create table Roominfo( Rno char(10), Rtype char(20)not null, Rprice float not null, Rfloor smallint not null, Toward char(10)not null, primary key (Rno), check (Rfloor between 1 and 100), check (Toward in(39。正北39。,39。 正南 39。,39。正西39。,39。正東39。,39。東北39。,39。西南39。,39。 西北39。,39。東南39。)), check (Rtype in(39。標(biāo)準(zhǔn) 139。,39。標(biāo)準(zhǔn) 239。,39。豪華 139。,39。豪華 239。,39。高級(jí) 139。,39。高級(jí) 239。)),)（3）房態(tài)表的建立：.. . . ..學(xué)習(xí)好幫手create table RoomState(Rno char(10), Gno char(20), Atime datetime , Ltime datetime, Rtime datetime, Rltime datetime, IntoPrice float , Days int , Stime datetime, flag char(1) ,primary key (Rno,Gno),foreign key (Rno)references Roominfo(Rno),foreign key (Gno)references guest(Gno),check (flag in(39。139。,39。239。,39。339。)),)（4）娛樂(lè)項(xiàng)目基本信息表的建立： create table Atariff( Atno char(20), Atname char(20)not null, Atprice float not null, primary key (Atno), check (Atprice )) （5）顧客娛樂(lè)消費(fèi)信息表的建立： create table Consumelist( Gno char(20), Atno char(20), Amount float, Wtime datetime not null, primary key(Gno,Atno), foreign key (Gno)references guest(Gno), foreign key (Atno)references Atariff(Atno))（6）客房物品基本信息表的建立： create table RoGoInfo ( Goodsno char(20), Goodsname char(20)not null, Oprice float not null, Dmultiple float not null,.. . . ..學(xué)習(xí)好幫手 primary key (Goodsno))（7）顧客賠償物品信息表的建立：create table GoAmInfo(Gno char(20), Rno char(10), Goodsno char(20), Dnum int , Amendstime datetime not null, primary key(Gno,Rno,Goodsno), foreign key (Gno)references guest(Gno), foreign key(Rno)references Roominfo(Rno), foreign key(Goodsno)references RoGoInfo(Goodsno)) 建立視圖 SQL 語(yǔ)句（1）用于查詢預(yù)訂房信息的視圖定義如下：create viewBookView(Gno,Gname,Rno,Rtype,Rfloor,Toward,IntoPrice,Rtime,Rltime,Days,Stime)asselect ,Gname,Rtype,Rfloor,Toward,IntoPrice,Rtime,Rltime,Days,Stimefrom Roominfo,RoomState,guestwhere flag=39。139。 and = and =（2）用于查詢已入住房的試圖如下：create viewIntoView(Gno,Gname,Rno,Rtype,Rfloor,Toward,IntoPrice,Atime,Ltime,Days,Account)asselect ,Gname,Rtype,Rfloor,Toward,IntoPrice,Atime,Ltime,Days,Accountfrom Roominfo,RoomState,guestwhere flag=39。239。 and = and =（3）用于查詢空房的視圖定義如下：create view EmRoView(Rno,Rtype,Rprice,Rfloor,Toward)asselect Rno,Rtype,Rprice,Rfloor,Toward.. . . ..學(xué)習(xí)好幫手from Roominfo where Rno not in (select Rno From RoomState) 建立存儲(chǔ)過(guò)程 SQL 語(yǔ)句 的定義create procedure lsqSearchDate@date datetimeasselect Atno,sum(Amount)from Consumelistwhere Wtime=@dategroup by Atno 的定義create procedure lsqSearchEmpty@floor intasselect Rno,Rtype,Rprice,Rfloor,Towardfrom EmRoViewwhere Rfloor=@floorlsqSearchEmpty 2 的定義create procedure lsqWatchGuestas select Gno,Gname,Gsex,Gidfrom guest 的定義create procedure lsqSearchGuest@Gno char(20)asbeginselect Gno,Gname,Account,balancefrom guestwhere Gno=@Gnoselect ,Rtype,IntoPrice.. . . ..學(xué)習(xí)好幫手from RoomState,Roominfowhere =@Gno and =select ,Atname,Amount, Amount*Atprice AmuMoney,Wtimefrom Consumelist c,Atariff awhere =@Gno and =,,Oprice** AmendMoney,from GoAmInfo g,RoGoInfo rwhere =@Gno and =end 的定義CREATE PROCEDURE lsqConsumeList@Consumelist_Gno char(20),@Consumelist_Atno char(20),@Consumelist_Amount float,@Consumelist_wtime datetimeasinsert into Consumelistvalues(@Consumelist_Gno,@Consumelist_Atno , @Consumelist_Amount ,@Consumelist_wtime )lsqConsumeList 39。G0000439。,39。AKTVM39。,2,39。20221539。