【数据库】图书管理系统

分析学校的图书借阅系统，尝试模拟该借阅系统的数据库结构自行设计一个数据库。数据库所支持的功能和存储的信息需至少包含如下几个方面。

读者信息，可参考下图，只需设计其中主要属性信息即可。需要注意，每个读者可能有着不同的借阅权限，比如：本科生能借阅50本，教师能借阅100本，而且借阅时限也不同，所以，为了便于管理，将读者分为不同等级，每个等级有着不同的借阅权限。因此需要设计借阅等级信息的结构，其中应包含最大借阅量，最长借阅时限等信息。

图书信息，可参考下图，只需设计其中主要属性信息即可。与图书内容相关的学科主题词信息（主题词经常要临时增加，所以要单独保存）。为了方便图书馆采购，还需要存储出版社联系信息（地址、通信方式等）。(注意，一部书只能由一个出版社出版)

注意，同一部书一般提供了多本供借阅，多本书有着不同的条码号，如下图所示，同一个书籍信息下提供了多本图书，而且存放在不同位置。因此，需要考虑每部图书信息和具体的每本图书信息之间的关系。

读者可以针对图书进行评论，如下图所示，包括评论内容，评论时间，评论者，评分等信息。注意评论针对的是一部图书，而并非某一本书（因为有多本）。

----------要求-----------

分析设计以上基本问题的概念模型（E-R图）。

根据概念模型设计相应的关系模式。

设定一些约束：例如，读者姓名不能为空，读者等级默认为0，图书条码为7位数字构成。

向每个数据表中输入若干测试数据。

创建如下视图：

视图1：显示所有当前仍未归还的图书条码、借阅者姓名、编号、借阅时间和应还时间；

视图2：显示某图书条码和历史总借阅量；

视图3：显示与某主题词相关的所有图书条码和历史总借阅量；

视图4：显示某读者的编号（如20181001）和其当前所有未归还图书的条码、图书名称和应还日期；

视图5：显示某读者的编号和其对应的历史借阅信息，包括图书条码、图书名称、实际借阅日期和实际归还日期；

视图6：显示某读者编号和其发表的书评内容，包括评论的图书名称、评论时间和评论内容；

视图7：显示某出版社名称及其出版的所有图书名称。

ER图：

代码：

create database db
use db

create table 借阅等级信息(
    借阅等级 int primary key,
    最长借阅时间 int,
    最大借阅数量 int,
)

insert into 借阅等级信息 values
(0, 30, 10),
(1, 60, 20),
(2, 90, 30),
(3, 120, 40)

create table 读者信息(
    姓名 char(15) not null,
    证件号 char(12) primary key,
    借阅等级 int default 0,
    累计借书 int default 0,
    foreign key(借阅等级) references 借阅等级信息(借阅等级)
)

insert into 读者信息(姓名, 证件号, 借阅等级) values
('张三', '541607120165', 1), 
('李四', '541707010185', 3),
('王五', '541707120165', 1),
('赵六', '541505980268', 2),
('孙七', '541407010169', 0),
('周八', '541307010489', 1)

create table 出版社信息(
    出版社 varchar(20) primary key,
    地址 varchar(25),
    联系电话 char(7)
)

insert into 出版社信息 values
('清华大学出版社', '北京', '4979421'),
('晟威出版社', '天津', '5564130'),
('南海出版公司', '海南', '4984910'),
('上海文艺出版社', '上海', '6640239')

create table 图书信息(
    索书号 char(15) primary key,
    作者 char(15),
    书名 char(15),
    出版社 varchar(20),
    出版时间 date,
    foreign key(出版社)references 出版社信息(出版社)
)
insert into 图书信息 values
('b12987', '严蔚敏', '数据结构', '清华大学出版社', '2012-02-06'),
('b97894', '东野圭吾', '幻夜', '南海出版公司', '2004-08-02'),
('b16546', '吴玉华', '物理实验教程', '清华大学出版社', '2013-05-15'),
('b89490', '张雪峰', '考研指点', '晟威出版社', '2016-12-12'),
('b56400', '郏宗培', '纸上寻仙记', '上海文艺出版社', '2011-02-05')

create table 单本图书信息(
    条码号 char(7) primary key check(len(条码号) = 7),
    索书号 char(15),
    馆藏地 varchar(40),
    书刊状态 varchar(6) check(书刊状态 in ('可借', '借出', '非可借')),
    历史借阅量 int default 0,
    foreign key(索书号)references 图书信息(索书号)
)

insert into 单本图书信息(条码号, 索书号, 馆藏地, 书刊状态) values
('t987628', 'b97894', '三楼A8', '借出'),
('t594805', 'b97894', '二楼C7', '可借'),
('t984910', 'b89490', '五楼A2', '借出'),
('t940566', 'b12987', '负一楼D3', '借出')

create table 借阅信息(
    借阅号 char(6) primary key,
    借阅时间 date,
    归还时间 date,
    图书条码号 char(7),
    借阅人证件号 char(12),
    foreign key(图书条码号) references 单本图书信息(条码号),
    foreign key(借阅人证件号) references 读者信息(证件号)
)

create table 评论信息(
    评论号 char(8) primary key,
    评分 numeric(2, 1),
    内容 varchar(200),
    评论时间 date,
    评论者id char(12),
    索书号 char(15),
    foreign key(索书号)references 图书信息(索书号),
    foreign key(评论者id)references 读者信息(证件号)
)

insert into 评论信息 values
('p12391', 8.9, '很有趣', '2015-06-24', '541707010185', 'b97894'), 
('p98523', 7.8, '受益颇多', '2016-05-22', '541307010489', 'b89490'),
('p94606', 6.8, '完全看不懂orz', '2017-05-02', '541607120165', 'b12987')

create table 主题词信息(
    ID char(8) primary key,
    类别 char(15),
    索书号 char(15),
    foreign key(索书号)references 图书信息(索书号)
)

insert into 主题词信息 values
('z64400', '计算机', 'b12987'),
('z95410', '物理', 'b16546'),
('z98500', '考研', 'b89490'),
('z64165', '推理悬疑', 'b97894'),
('z69850', '仙侠', 'b56400')

--触发器1：读者借书后引发的一系列操作
use db
go
create trigger trigger1
on 借阅信息
after insert
as
begin    
update 读者信息                                                 --更改读者累计借书量
set 累计借书 = 累计借书 + 1                                     
from 读者信息, inserted
where 读者信息.证件号 = inserted.借阅人证件号

update 单本图书信息                                             --更改图书状态
set 单本图书信息.书刊状态 = '借出'
from inserted, 单本图书信息
where inserted.图书条码号 = 单本图书信息.条码号

update 单本图书信息
set 历史借阅量 = 历史借阅量 + 1                                 --更改图书历史借阅量
from inserted, 单本图书信息
where inserted.图书条码号 = 单本图书信息.条码号
end

insert into 借阅信息 values
('j13488', '2018-05-01', '2018-08-01', 't987628', '541407010169')
insert into 借阅信息 values
('j14910', '2016-12-11', '2017-01-25', 't984910', '541607120165')
insert into 借阅信息 values
('j97890', '2018-05-14', '2018-06-14', 't940566', '541607120165')

--试图1：显示所有当前仍未归还的图书条码、借阅者姓名、编号、借阅时间和应还时间
go
create view view_1(图书条码, 借阅者姓名, 编号, 借阅时间, 应还时间)
as
select 图书条码号, 姓名, 证件号, 借阅时间, 归还时间
from 借阅信息, 读者信息
where 借阅信息.借阅人证件号 = 读者信息.证件号 and 归还时间 > getdate()


--试图2：显示某图书条码和历史总借阅量
go
create view view_2(图书条码, 历史总借阅量)
as
select 条码号, 历史借阅量
from 单本图书信息, 图书信息
where 单本图书信息.索书号 = 图书信息.索书号 and 书名 = '幻夜'


--视图3：显示与某主题词相关的所有图书条码和历史总借阅量
go
create view view_3(类别, 图书条码, 历史总借阅量)
as
select 类别, 条码号, 历史借阅量
from 单本图书信息, 图书信息, 主题词信息
where 主题词信息.索书号 = 图书信息.索书号 and 图书信息.索书号 = 单本图书信息.索书号 and 类别 = '考研'


--试图4：显示某读者的编号（如20181001）和其当前所有未归还图书的条码、图书名称和应还日期
go
create view view_4(读者编号, 条码, 图书名称, 应还日期)
as
select 借阅人证件号, 图书条码号, 书名, 归还时间
from 单本图书信息, 图书信息, 借阅信息
where 借阅信息.图书条码号 = 单本图书信息.条码号 and 单本图书信息.索书号 = 图书信息.索书号
and 归还时间 > getdate() and 借阅人证件号 = '541607120165'


--试图5：显示某读者的编号和其对应的历史借阅信息，包括图书条码、图书名称、实际借阅日期和实际归还日期
go
create view view_5(读者编号, 图书条码, 图书名称, 借阅日期, 归还日期)
as
select 借阅人证件号, 图书条码号, 书名, 借阅时间, 归还时间
from 单本图书信息, 图书信息, 借阅信息
where 借阅信息.图书条码号 = 单本图书信息.条码号 and 单本图书信息.索书号 = 图书信息.索书号
and 借阅人证件号 = '541607120165'



--试图6：显示某读者编号和其发表的书评内容，包括评论的图书名称、评论时间和评论内容
go
create view view_6(读者编号, 图书名称, 评论时间, 评论内容)
as
select 评论者id, 书名, 评论时间, 内容
from 借阅信息, 单本图书信息, 评论信息, 图书信息
where 借阅信息.图书条码号 = 单本图书信息.条码号 and 单本图书信息.索书号 = 评论信息.索书号
and 评论信息.索书号 = 图书信息.索书号 and 评论者id = '541607120165'


--视图7：显示某出版社名称及其出版的所有图书名称，并按出版时间排序
go
create view view_7(出版社名称, 图书名称, 出版时间)
as
select top 100 percent 出版社信息.出版社, 书名, 出版时间
from 出版社信息, 图书信息
where 出版社信息.出版社 = 图书信息.出版社
order by 出版时间 asc



--执行
select * from view_1

select * from view_2

select * from view_3

select * from view_4

select * from view_5

select * from view_6

select * from view_7 order by 出版时间 asc