2) 存储过程Query_Book_Writer 功能的验证：(按BookWriter模糊查询图书信息)
3） 存储过程Query_Book_Nmae_Publish功能的验证：(按书名和出版社查询图书信息)
4）存储过程Query_Reader_M功能的验证：（以管理员身份查询读者信息） 5）存储过程Reader_Insert 功能的验证：（插入一条读者信息记录）
-32-
6）存储过程Borrow_Insert功能的验证：（插入一条借阅信息记录）
7）存储过程Delete_Reader功能的验证：（按ReaNo删除相应的读者记录）
(注：由于篇幅限制，这里仅给出了其中几个存储过程功能的验证)
3. 触发器功能的验证：（在Borrow表中插入一条记录，触发Book表将图书由“可借”
状态改为“不可借”状态）
-33-
附录3 所有的SQL运行语句
create database Book;
create table ReaderType(
LBID char(5) primary key, LBName char(20) not null, LBnum char(5) not null, LBbqx char(4) not null, LBqx char(3) not null,
)
create table Maneger(
MID char(10) primary key, MName char(10) not null, MSex char(2),
Mpwd char(8) not null, MAuth char(40) not null, MTeleph char(15), MAddre char(30),
check(MSex ='男' or MSex ='女')
)
create table Room(
RoomNo char(5) primary key, RoomMID char(10) not null, Roomnum char(5), RoomAddre char(20),
foreign key(RoomMID) references Maneger(MID),
)
create table Book(
BookID char(9) primary key, BookNo char(20) not null, BookName char(50)not null, BookWriter char(30)not null, BookPublish char(20)not null, BookPrice char(7), BookDate datetime,
-34-
BookClass char(20), BookMain char(200), BookPrim char(30), BookCopy char(5), BookState char(10)not null, BookRNo char(5)not null,
foreign key(BookRNo) references Room(RoomNo),
)
create table Reader(
ReaID char(9) primary key, ReaName char(10) not null, ReaSex char(2) not null, ReaNo char(9) not null, ReaLBID char(5) not null, ReaType char(20), ReaDep char(20), ReaGrade char(5), ReaPref char(20), ReaDate Datetime,
foreign key(ReaLBID) references ReaderType(LBID), check(ReaSex ='男' or ReaSex ='女')
)
create table Borrow(
BookID char(9), ReaID char(9),
Outdate Datetime not null, YHdate Datetime not null, Indate Datetime, Fine char(5), CLState char(8), MID char(10) not null, primary key(BookID,ReaID),
foreign key(MID) references Maneger(MID)
)
create clustered index BookPublish on Book(BookPublish); create clustered index ReaDep on Reader(ReaDep);
-35-
create view Bookview (索书号, 书名, 作者, 出版社, 图书状态) as
select BookNo,BookName,BookWriter,BookPublish,BookState from Book
create view Readerview (编号,读者姓名,类型,学院,专业,办证日期) as
select ReaID,ReaName,ReaType,ReaDep,ReaPref,ReaDate from Reader
create view Borrowview (读者编号,书名,作者,借阅日期,到期日期) as
select ReaID,BookName,BookWriter,Outdate,YHdate from Borrow,Book
where Borrow.BookID=Book.BookID and Borrow.Indate is null
create view Historyview (读者编号,书名,借阅日期,归还日期) as
select ReaID,BookName,Outdate,Indate from Borrow,Book
where Borrow.BookID=Book.BookID and Borrow.Indate is not null
create view Fineview (读者编号,书名,借阅日期,归还日期,罚款,处理状态) as
select ReaID,BookName,Outdate,Indate,Fine,CLState from Borrow,Book
where Borrow.BookID=Book.BookID and Fine is not null
create trigger Reader_delete on Reader for delete as
delete Borrow from deleted
where Borrow.ReaID=deleted.ReaID
-36-
create trigger Borrow_insert1 on Borrow for insert as
declare @BookID char(9) select @BookID=BookID from inserted update Book
set BookState='不可借' where BookID=@BookID
-37-
