数据库12.24实验

数据库实验

实验十五：T-SQL(13)
一个简化的图书馆信息管理系统，系统需求如下：
1.图书馆有若干管理员librarian，各自有员工号empid、姓名name、身份证号idno等属性。
2.图书馆有若干种图书booktype，每种图书有ISBN、名称title、出版社publisher、作者writers、价格price等属性，每种图书有唯一的ISBN号，同种图书可购入多本。
3.每一本图书book有唯一标记bookid和种类booktype。
4.读者reader在办理借书证后方可借阅，一个读者有唯一的借书证号cardno，还有姓名name、身份证号idno、住址address、注销标记logoff等。每个读者最多可借20本书，读者在注销前，须归还所有已借图书或报失。
5.需处理以下基本业务：
①借书：在某时刻某读者通过某管理员借阅某一本书。
②还书：在某时刻通过某管理员归还某一本书，读者可以在借阅历史表中查阅自己以前所借的书。
③报失：在某时刻某读者向某管理员报失某一本书；报失之后该书不能再借；每一次还书和报失记录都须对应某一次借书记录，且可由不同管理员处理。
用E/R图建立该系统的概念模型如下：

请按要求完成如下工作：
1. 参考以上E-R图，设计关系模式，并确定各关系模式的属性应满足的数据完整性约束，然后定义表的参照完整性约束
2. 根据借还书流程设计相应的触发器.

0建库

create database LIS
on
primary(name=LIS,
   filename='D:\test\test1.mdf',
size=4mb,
maxsize=10mb,
filegrowth=2mb
  )
log on
(name=LISlog,
filename='D:\test\testlog1.ldf',
size=1mb,
maxsize=5mb,
filegrowth=1mb
)
use LIS;

1 librarian表

--1.图书馆有若干管理员librarian，各自有员工号empid、姓名name、身份证号idno等属性。
create table librarian
(
Lempid char(12) not null primary key,
Lname char(15) null,
Lidno char(18) null
)

2 booktype表

--2.图书馆有若干种图书booktype，每种图书有ISBN、名称title、出版社publisher、
--作者writers、价格price等属性，每种图书有唯一的ISBN号，同种图书可购入多本。
create table booktype
(
ISBN char(20) not null primary key,
title char(15),
publisher char(15),
writers char(15),
price int
)

3 book表

--3.每一本图书book有唯一标记bookid和种类booktype。
create table book
(
bookid char(8) not null primary key,
ISBN char(20) foreign key references booktype(ISBN),
)

4 reader表

--4.读者reader在办理借书证后方可借阅，一个读者有唯一的借书证号cardno，
--还有姓名name、身份证号idno、住址address、注销标记logoff等。
--每个读者最多可借20本书，读者在注销前，须归还所有已借图书或报失。
create table reader
(
cardno char(20) not null primary key,
name char(15) null,
idno char(18) null,
address char(18) null,
logoff char(1) not null,
)

5 Record表

--5.需处理以下基本业务：
--①借书：在某时刻某读者通过某管理员借阅某一本书。
--②还书：在某时刻通过某管理员归还某一本书，读者可以在借阅历史表中查阅自己以前所借的书。
--③报失：在某时刻某读者向某管理员报失某一本书；报失之后该书不能再借；
--每一次还书和报失记录都须对应某一次借书记录，且可由不同管理员处理。
--用E/R图建立该系统的概念模型如下：
create table Record
(
recid char(12) not null primary key,
brwLempid char(12) foreign key references librarian(Lempid),
cardno char(20) foreign key references reader(cardno),
bookid char(8)foreign key references book(bookid),
borrowdate DATETIME,
status char(8) null,
enddate DATETIME,
endLempid char(12) foreign key references librarian(Lempid),
)

6 根据借还书流程设计相应的触发器

CREATE trigger borrow_record on book_record after insert
  as begin
    declare @bookid1 char(10)
    declare @empid1 int
    declare @borrow_time char(10)
   select @bookid1 = bookid ,@empid1 = empid ,@borrow_time = borrow_time
    from inserted
     insert into borrow_record values(@bookid1,@empid1,@borrow_time) 
  end
CREATE trigger return_record on book_record after insert
  as begin
    declare @bookid1 char(10)
    declare @empid1 int
    declare @return_time1 char(10)
    declare @return_flag1 char(4)
   select @bookid1 = bookid ,@empid1 = empid ,@return_time1 = return_time,@return_flag1 = return_flag
    from inserted
     insert into return_record values(@bookid1,@empid1,@return_time1,@return_flag1) 
  end
CREATE trigger lost_record on book_record after insert
  as begin
    declare @bookid1 char(10)
    declare @empid1 int
    declare @lost_time1 char(10)
    declare @lost_flag1 char(4)
   select @bookid1 = bookid ,@empid1 = empid ,@lost_time1 = lost_time,@lost_flag1 = lost_flag
    from inserted
     insert into lost_record values(@bookid1,@empid1,@lost_time1,@lost_flag1) 
  end