图书管理系统数据库设计
1. 创建如下几个数据表:
(1)创建名为Book(图书信息)的表,表中的各列要求如下:
(2)创建名为Reader(读者信息)的表,表中的各列要求如下:
(3)创建名为Borrow_info的各列要求如下:
(4)Book 表和Reader表至少输入两条记录,Borrow_info表至少输入四条记录。
2. 创建视图用来查询所有读者的姓名、所借的图书名称、借阅日期。
3. 创建带参数的存储过程,实现向Book表中插入数据的功能。
4. 在Reader表上创建一个删除的替代触发器,当删除该表中的数据时,替换成先删除Borrow_info表上的相关记录,再删除Reader表上的记录。
5. 创建带参数的函数,根据给定的读者编号,查询并返回该读者的借书次数。
答案:
1.
create table Book
(B_no varchar(10) primary key,B_name varchar(20) not null,Author varchar(20),ISBN varchar(20),Publisher varchar(30),Price float check(Price>0))
create table Reader
(R_no int primary key,R_name varchar(10),R_class varchar(10) not null)
create table Borrow_info
(B_no varchar(10)foreign key (B_no)references Book(B_no),R_no int foreign key (R_no)references Reader(R_no),Borrow_date datetime,primary key(B_no,R_no,Borrow_date))
insert into Book values('0001','西游记','吴承恩',null,null,78)
insert into Book values('0002','红楼梦','曹雪芹',null,null,75)
insert into Reader values('0001','王红','net2')
insert into Reader values('0002','张三','net2')
insert into Borrow_info values('0001','0001','2012-04-04')
insert into Borrow_info values('0001','0002','2012-05-05')
insert into Borrow_info values('0002','0001','2012-05-06')
insert into Borrow_info values('0002','0002','2012-05-07')
2.
create view v1
as
select R.R_name,B.B_name,info.Borrow_date from Borrow_info info inner join Reader R on info.R_no=R.R_no inner join Book B on info.B_no=B.B_no
3.
create procedure insert_Book
@id varchar(10),@name varchar(20),@author varchar(20),@isbn varchar(20),@publisher varchar(30),@price float
as
begin
insert into Book values(@id,@name,@author,@isbn,@publisher,@price)
end
4.
create trigger t1
on Reader
instead of delete
as
begin
declare @id int
select @id=R_no from deleted
delete from Borrow_info where R_no=@id
delete from Reader where R_no=@id
end
5.
create function info(@id int)
returns int
as
begin
return(select count(Borrow_date)from Borrow_info where R_no=@id group by R_no)
end
本文来源:https://www.2haoxitong.net/k/doc/0ceda83431126edb6f1a10ee.html
文档为doc格式