第六章课后简答题

1.借阅事务

*事务操作借书
*/
BEGIN TRANSACTION
DECLARE @errorSum int
DECLARE @RID varchar(50)
DECLARE @BID varchar(50)
SELECT @RID=RID FROM Reader WHERE RName='张无忌'
SELECT @BID=BID FROM Book WHERE BName='深入。Net平台和c#编程'
SET @errorSum=0
INSERT INTO Borrow(RID,BID)VALUES (@RID,@BID)
SET @errorSum=@errorSum+@@error
UPDATE Book SET BCount=BCount-1 WHERE BName=' 深入。Net平台和c#编程'
SET @errorSum=@errorSum+@@error
UPDATE Reader SET LendNum=LendNum+1 WHERE RName='张无忌'
SET @errorSum=@errorSum+@@error
IF @errorSum<>0
  ROLLBACK TRANSACTION
 ELSE
 COMMIT TRANSACTION
GO

2.罚款事务

/*
*事务操作借书
*/
BEGIN TRANSACTION

DECLARE @errorSum int
DECLARE @RID varchar(50)
DECLARE @BID varchar(50)
SELECT @RID=RID FROM Reader WHERE RName='刘冰冰'
SELECT @BID=BID FROM Book WHERE BName='西游记'
SET @errorSum=0
INSERT INTO Penalty(RID,BID,PType,Amount) VALUES(@RID,@BID,'1',5.6)
SET @errorSum=@errorSum+@@error
UPDATE Borrow SET ReturnDate=getdate() WHERE BID=@BID
SET @errorSum=@errorSum+@@error
UPDATE Reader SET LendNum=LendNum-1 WHERE RName='刘冰冰'
SET @errorSum=@errorSum+@@error
UPDATE Book SET BCount=BCount+1 WHERE BID=@BID
SET @errorSum=@errorSum+@@error
IF @errorSum<>0
  ROLLBACK TRANSACTION
 ELSE
 COMMIT TRANSACTION
GO

3.视图
1.--创建索引 
2.IF EXISTS (SELECT * FROM sysindexes  
3.          WHERE name = 'IX_BOOK_BookName') 
4.   DROP INDEX Book.IX_BOOK_BookName  --删除索引 
5./*--笔试列创建非聚集索引：填充因子为30％--*/ 
6.CREATE INDEX IX_BOOK_BookName 
7.   ON BOOK(BName) 
8.       WITH FILLFACTOR = 30 
9.GO 
10. 
11.--创建管理员视图 
12.IF EXISTS (SELECT  * FROM sysobjects  WHERE  NAME='view_borrow') 
13.    Drop VIEW view_borrow 
14.go 
15.CREATE VIEW view_borrow  
16.    AS 
17.        SELECT   图书名称=Book.BNAME,到期时间=Borrow.WillDate,读者姓名=Book.BNAME  
18.        from Borrow,book  
19.        with (index = IX_BOOK_BookName) 
20.        WHERE  Book.BID=Borrow.BID  
21.        --order by Borrow.WillDate 
22.GO 
23. 
24.SELECT * FROM view_borrow 
25. 
26. 
27.--创建读者视图 
28.IF EXISTS(SELECT  *FROM sysobjects  WHERE  NAME='view_book') 
29.    DROP VIEW view_book 
30.    go 
31.CREATE VIEW view_book 
32.    As  
33.        SELECT  图书名称=BNAME,图书总量=BCount,可借阅量=BCount-( 
34.          SELECT COUNT(*) FROM Borrow WHERE returnDate is null AND Borrow.bid = Book.bid 
35.        ) FROM Book  
36.        with (index = IX_BOOK_BookName) 
37.GO 
38. 
39.SELECT * FROM view_book