第六章课后简单题

来源：互联网发布：华为光猫更改ssh端口编辑：程序博客网时间：2024/06/17 14:04

第一题：

事务的四个特性：
    原子性
    一致性
    隔离性
    持久性

第二题：

1.数据库的表就是你所创建的真实的表。
2.视图是来源于真实的表，为了不完全将表的数据展现可以创建此表的视图，视图中的数据来源与表，对视图的更改不会影响原表的数据。

第三题：

/**事务操作借书*/BEGIN TRANSACTIONDECLARE @errorSum intDECLARE @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=0INSERT INTO Borrow(RID,BID)VALUES (@RID,@BID)SET @errorSum=@errorSum+@@errorUPDATE Book SET BCount=BCount-1 WHERE BName=' 深入。Net平台和c#编程'SET @errorSum=@errorSum+@@errorUPDATE Reader SET LendNum=LendNum+1 WHERE RName='张无忌'SET @errorSum=@errorSum+@@errorIF @errorSum<>0ROLLBACK TRANSACTIONELSE COMMIT TRANSACTIONGO

第四题;

/**事务操作借书*/BEGIN TRANSACTIONDECLARE @errorSum intDECLARE @RID varchar(50)DECLARE @BID varchar(50)SELECT @RID=RID FROM Reader WHERE RName='刘冰冰'SELECT @BID=BID FROM Book WHERE BName='西游记'SET @errorSum=0INSERT INTO Penalty(RID,BID,PType,Amount) VALUES(@RID,@BID,'1',5.6)SET @errorSum=@errorSum+@@errorUPDATE Borrow SET ReturnDate=getdate() WHERE BID=@BIDSET @errorSum=@errorSum+@@errorUPDATE Reader SET LendNum=LendNum-1 WHERE RName='刘冰冰'SET @errorSum=@errorSum+@@errorUPDATE Book SET BCount=BCount+1 WHERE BID=@BIDSET @errorSum=@errorSum+@@errorIF @errorSum<>0ROLLBACK TRANSACTIONELSE COMMIT TRANSACTIONGO

第五题：

USE LibraryGO--创建索引IF EXISTS (SELECT * FROM sysindexes           WHERE name = 'IX_BOOK_BookName')   DROP INDEX Book.IX_BOOK_BookName  --删除索引/*--笔试列创建非聚集索引：填充因子为30％--*/CREATE INDEX IX_BOOK_BookName   ON BOOK(BName)   WITH FILLFACTOR = 30GO--创建管理员视图IF EXISTS (SELECT  * FROM sysobjects  WHERE  NAME='view_borrow')Drop VIEW view_borrowgoCREATE VIEW view_borrow ASSELECT   图书名称=Book.BNAME,到期时间=Borrow.WillDate,读者姓名=Book.BNAME from Borrow,book with (index = IX_BOOK_BookName)WHERE  Book.BID=Borrow.BID --order by Borrow.WillDateGOSELECT * FROM view_borrow--创建读者视图IF EXISTS(SELECT  *FROM sysobjects  WHERE  NAME='view_book')DROP VIEW view_bookgoCREATE VIEW view_bookAs SELECT  图书名称=BNAME,图书总量=BCount,可借阅量=BCount-(  SELECT COUNT(*) FROM Borrow WHERE returnDate is null AND Borrow.bid = Book.bid) FROM Book with (index = IX_BOOK_BookName)GOSELECT * FROM view_book

0 0