【正文】 (TypeNo) references RoomType(TypeNo)on update cascade,)Create TABLE ClientInfo(ClientID char(8) PRIMARY KEY,ClientName varchar(30) not null,ClientSex char(4),ClientCertificateID varchar(18) not null,ClientBirthday datetime,ClientPhone varchar(12) not null,ClientBookOrNot int,ClientAdress varchar(50),ClientConsume money,PaymentTime datetime,ClientRemarks varchar(40),)CREATE TABLE LoginInfo(EmployeeId char(8) PRIMARY KEY,PassWord char(6) not null,Restricte int not null,)create table ReInfo(ClientID char(8) NOT NULL,RoomNo int NOT NULL, LiveDate datetime,OutDate datetime,DayNo AS OutDateLiveDate,primary key (RoomNo,ClientID),foreign key (RoomNo) references RoomInfo(RoomNo),FOREIGN KEY (ClientID) REFERENCES ClientInfo(ClientID)) 創(chuàng)建索引create index sroominfoon ReInfo(ClientID ASC,RoomNo)use MC酒店前臺管理系統(tǒng)GO EXEC sp_helpindex ReInfoGOcreate index sclientinfoon ClientInfo(ClientID ASC,ClientName)use MC酒店前臺管理系統(tǒng)GO EXEC sp_helpindex ClientInfoGO六. 利用TSQL命令增加、刪除、修改數(shù)據(jù)。34222219930123002X39。20110710 09:06:0739。男39。中國北京市朝陽路39。葉華39。1878787444439。C000000339。1982112339。對海鮮過敏39。39。不空閑39。單人房39。A000000739。C000000139。20110704 19:06:0739。20110713 19:06:0739。C000000439。USE MC酒店前臺管理系統(tǒng)GoSELECT ClientName from ClientInfoSELECT RoomNo from RoomInfo where StatusNo=1SELECT ,RoomNo,ClientName,ClientSex,ClientRemarks from ClientInfo,ReInfo where = select ClientSex,Count(ClientSex) as 人數(shù)from ClientInfo group by ClientSexSELECT TypeName,AVG(ClientConsume) AS 平均收入from RoomType,ClientInfo,ReInfo,RoomInfowhere = and = and =Group by TypeName Having AVG(ClientConsume)1000SELECT TypeNo from RoomInfo GROUP BY TypeNo HAVING COUNT(*)2SELECT RoomNo from RoomInfo WHERE TypeNo IN(SELECT TypeNo from RoomInfo GROUP BY TypeNo HAVING COUNT(*)2)SELECT * from RoomType where Price(SELECT AVG(Price) from RoomType)SELECT ClientName from ClientInfo where ClientSex=39。男39。九. 對視圖進(jìn)行插入、刪除、修改數(shù)據(jù)操作。,39。,39。,39。beforeAfterupdate freeroomset TypeName=39。寫出源代碼。*39。 where ClientID=noToUpdate fetch next from UpdateHKUNoCursor into noToUpdate endendexec UpdateHKUNoclose UpdateHKUNoCursor 關(guān)閉deallocate UpdateHKUNoCursor drop procedure UpdateHKUNo 十二. 編寫一個(gè)實(shí)現(xiàn)修改表的觸發(fā)器，實(shí)現(xiàn)完整性控制。創(chuàng)建登錄用戶create login bingjie921123 with password=39。EXEC sp_addsrvrolemember 39。EXEC sp_helpsrvrolememberEXEC sp_dropsrvrolemember 39。EXEC sp_helpsrvrolemember十五. 將部分登錄映射到你的數(shù)據(jù)庫中成為用戶。use MC酒店前臺管理系統(tǒng)gocreate role bjEXEC sp_addlogin 39。EXEC sp_adduser 39。EXEC sp_droprole 39。EXEC sp_adduser 39。CJW39。CJW39。CJW39。EXEC sp_droprole 39。EXEC sp_helprotect 39。CJW39。MC酒店前臺管理系統(tǒng)39。,39。EXEC sp_droprole 39。,39。,39。bj92112339。EXEC sp_dropsrvrolemember 39。EXEC sp_addsrvrolemember 39。create login admin1 with password=39。 update ClientInfoset ClientConsume=2000where ClientID=39。利用游標(biāo)進(jìn)行數(shù)據(jù)修改的存儲過程，將ClientInfo中所有女性的備注改為“女士可免費(fèi)參加舞會”create procedure UpdateHKUNo 存儲過程里面放置游標(biāo)as begindeclare UpdateHKUNoCursor cursor 聲明一個(gè)游標(biāo)，查詢滿足條件的數(shù)據(jù)for select ClientID from ClientInfo where ClientSex=39。create function clientsq(ClientID char(8))returns tableasreturn(select ClientID,ClientName,ClientCertificateID from ClientInfo Where ClientID=ClientID)goSELECT * FROM (39。After刪除數(shù)據(jù)delete from clientmaninfowhere ClientName LIKE 39。beforeAfterupdate clientmaninfoset ClientName=39。,39。,39。,39。select TypeName,SumConsumefrom zxfwhere SumConsume10000select ClientIDfrom clientmaninfowhere ClientName=39。select RoomNo,StatusNo,RoomRemarksfrom RoomInfowhere TypeNo=(select TypeNo from RoomType where TypeName=39。C000000439。20110709 09:06:0739。C000000339。20110710 09:06:0739。B000000139?？偨y(tǒng)套房39。空閑39。39。39。中國北京市朝陽路39。女39。20110719 19:06:0739。34222219880123002239。每日：分前不要打掃其房間39。1982112339。C000000139。592719526039。馬冰潔39。,size=50,maxsize=100,filegrowth=10)log on (name=39。(3)邏輯數(shù)據(jù)層。數(shù)據(jù)庫的基本結(jié)構(gòu)分三個(gè)層次，反映了觀察數(shù)據(jù)庫的三種不同角度。退房登記：需要處理客房及客戶的信息，查詢客戶及客房的相關(guān)信息，并修改客房的各項(xiàng)信息以及客戶的退房時(shí)間及入住天數(shù)。工作人員登錄：需要處理工作人員的信息