设计并实现一个简单的学校图书馆数据库系统
实验题目：设计并实现一个简单的学校图书馆数据库系统
实验报告：
一、需求分析
1、E-R图
2、建立基本表
1）图书（书号，书名，作者，出版社，定价，类别，借阅次数，总借阅时间，总册数，剩余册书）
2）学生读者（学号，姓名，所在系，性别，可借书量，是否有书逾期）
3）学生借阅（书号，学号，姓名，书名，借书日期，应还日期，实还日期）
4）教师读者（教师号，姓名，单位，性别，可借书量，是否有书逾期）
5）教师借阅（书号，教师号，姓名，书名，借书日期，应还日期，实还日期，是否续借）
2、关系图
二、查询功能的实现
1、判断某位读者是否有逾期的图书（以学生读者为例）
CREA TE PROCEDURE [判断某位学生读者是否有逾期的图书]
(@学号_1 char(5))
AS
IF((SELECT COUNT (书号)
FROM 学生借阅
WHERE ((DA TEDIFF(day,(SELECT 应还日期FROM 学生借阅WHERE 学号=@学号_1),getdate()))>0) AND (学号=@学号_1))=0) /*计算是否逾期*/ BEGIN
print'该读者无逾期的书'
UPDA TE 学生读者
SET是否有书逾期=1
WHERE 学号=@学号_1
END
ELSE
print'该读者有逾期的书'
GO
功能：该存储过程通过对输入的读者号所对应的预期借阅信息进行统计，若结果集非空则通知逾期，同时将‘是否有书逾期’置为‘1’，否则通知逾期。（教师读者查询同理，此处不再赘述）
2、图书按借阅量排序
CREA TE PROCEDURE [图书按借阅量排序] AS
SELECT 图书.书号,图书.书名,(图书.总册数-图书.剩余册数) as 借出量
FROM 图书
ORDER BY图书.借出量ASC /*按借出量升序排序*/ GO
功能：该存储过程通过图书属性中总册数与剩余册数作差作为借出量，并在结果集中按升序排列。
3、查询已全部借出的书
CREA TE PROCEDURE [查询已全部借出的书] AS
SELECT 图书.书号,图书.书名
FROM 图书
WHERE 图书.剩余册数=0
GO
功能：该存储过程通过对剩余册书是否为零的判断实现对是否全部借出的判断。
4、查询未借出的书
CREA TE PROCEDURE [查询未借出的书] AS
SELECT 图书.书号,图书.书名,图书.剩余册数
FROM 图书
WHERE 图书.总册数=图书.剩余册数
GO
功能：该存储过程通过对总册数与剩余册数的比较实现对未借出的图书的判断。
5图书按类统计
CREA TE PROCEDURE [图书按类统计]
AS
SELECT 类别,sum(剩余册数) as 馆藏册数,(sum(总册数)-sum(剩余册数)) as 借出册数,(sum(总借阅时间)) as 总借阅时间
FROM 图书
GROUP BY类别
GO
功能：该存储过程通过分组统计函数实现对基本属性的运算，并在结果集中以新属性名列出。
三、存储过程和触发器
1、存储过程
1）新增图书信息
CREA TE PROCEDURE [新增图书信息]
(@书号_1 [char](10),
@书名_2 [varchar](50),
@作者_3 [varchar](50),
@出版社_4 [varchar](50),
@定价_5 [float],
@类别_6 [varchar](50),
@借阅次数_7 [int],
@总借阅时间_8 [int],
@总册数_9 [int],
@剩余册数_10 [int])
AS INSERT INTO [PB08210172 宫永超].[dbo].[图书]
( [书号],
[书名],
[作者],
[出版社],
[定价],
[类别],
[借阅次数],
[总借阅时间],
[总册数],
[剩余册数])
V ALUES
( @书号_1,
@书名_2,
@作者_3,
@出版社_4,
@定价_5,
@类别_6,
@借阅次数_7,
@总借阅时间_8,
@总册数_9,
@剩余册数_10)
GO
说明：该存储过程实现新图书信息向图书表中的添加，每次只能添加一个元组。
2）新增学生读者信息
CREA TE PROCEDURE [新增学生读者信息]
(@学号_1 [char](10),
@姓名_2 [varchar](10),
@所在系_3 [char](3),
@性别_4 [char](2),
@可借书量_5 [int],
@是否有书逾期_6 [bit])
AS INSERT INTO 学生读者
( [学号],
[姓名],
[所在系],
[性别],
[可借书量],
[是否有书逾期])
V ALUES
( @学号_1,
@姓名_2,
@所在系_3,
@性别_4,
@可借书量_5,
@是否有书逾期_6)
GO
说明：该存储过程实现新学生读者信息向学生读者表中的添加，每次只能添加一个元组。3）新增教师读者信息
CREA TE PROCEDURE [新增教师读者信息]
(@教师号_1 [char](10),
@姓名_2 [varchar](50),
@单位_3 [varchar](50),
@性别_4 [char](2),
@可借书量_5 [int],
@是否有书逾期_6 [bit])
AS INSERT INTO [PB08210172 宫永超].[dbo].[教师读者]
( [教师号],
[姓名],
[单位],
[性别],
[可借书量],
[是否有书逾期])
V ALUES
( @教师号_1,
@姓名_2,
@单位_3,
@性别_4,
@可借书量_5,
@是否有书逾期_6)
GO
说明：该存储过程实现新教师读者信息向教师读者表中的添加，每次只能添加一个元组。4）插入学生借阅信息
CREA TE PROCEDURE [插入学生借阅信息]
(@书号_1 [char](10),
@学号_2 [char](5),
@姓名_3 [varchar](10),
@书名_4 [varchar](10),
@借书日期_5 [datetime])
AS INSERT INTO [学生借阅]
( [书号],
[学号],
[姓名],
[书名],
[借书日期])
V ALUES
( @书号_1,
@学号_2,
@姓名_3,
@书名_4,
@借书日期_5)
GO
说明：该存储过程将输入信息在教师借阅表中产生一个新的元组，即插入新的借阅信息，从
而实现借书功能。
5）插入教师借阅信息
CREA TE PROCEDURE [插入教师借阅信息]
(@书号_1 [char](10),
@教师号_2 [char](4),
@姓名_3 [varchar](10),
@书名_4 [varchar](10),
@借书日期_5 [datetime])
AS INSERT INTO [PB08210172 宫永超].[dbo].[教师借阅]
( [书号],
[教师号],
[姓名],
[书名],
[借书日期])
V ALUES
( @书号_1,
@教师号_2,
@姓名_3,
@书名_4,
@借书日期_5)
GO
说明：该存储过程将输入信息在教师借阅表中产生一个新的元组，即插入新的借阅信息，从而实现借书功能。
6）教师借阅数据更新
CREA TE PROCEDURE [教师借阅数据更新]
(@书号_1 [char],
@教师号_2 [char],
@是否续借_6 [int])
AS
UPDA TE 教师借阅
SET [是否续借] = @是否续借_6 /*“是否续借”置为“1”*/ WHERE ( [书号] = @书号_1 AND [教师号] = @教师号_2)
IF( @是否续借_6=1)
UPDA TE 教师借阅SET
应还日期=DA TEADD(day,30,教师借阅.应还日期) /*将应还日期推迟一个月*/ FROM 教师借阅
GO
说明：该存储过程通过输入的教师号和书号找到对应的借阅信息后对其进行更新，将标记信息“是否续借”置为“1”，并将应还日期推迟一个月，从而实现续借功能。
7）学生还书
CREA TE PROCEDURE [学生还书]
@学号_1 char(5),
@书号_1 char(5),
@实还日期_1 datetime,
@是否丢失int
AS
DECLARE @罚金float
DECLARE @借期_1 int
DECLARE @超期_2 int
BEGIN
SET @借期_1=DA TEDIFF(day, /*计算借期并用变量@借期_1表示*/ (SELECT 借书日期FROM 学生借阅WHERE 学号=@学号_1 AND 书号=@书号_1),
@实还日期_1)
SET @超期_2=DA TEDIFF(day, /*计算超期并用变量@超期_2表示*/ (SELECT 应还日期FROM 学生借阅WHERE 学号=@学号_1 AND 书号=@书号_1),
@实还日期_1)
IF (@超期_2>0) /*计算超期罚金并打印结果*/ SET @罚金=@超期_2*0.05
PRINT '超期罚款'+CAST(@罚金AS char(5))
IF (@是否丢失=1) /*计算丢书罚金并打印结果*/ SET @罚金= 2*(SELECT 定价FROM 图书WHERE @书号_1=书号)
PRINT '丢书罚款'+CAST(@罚金AS char(5))
UPDA TE 图书SET /*对对应图书信息进行更新*/ 剩余册数=剩余册数+1，
总借阅时间=总借阅时间+@借期_1
WHERE 书号=@书号_1
UPDA TE 学生借阅SET /*对对应学生借阅信息进行更新*/ 实还日期=@实还日期_1
WHERE 学号=@学号_1
UPDA TE 学生读者SET /*对对应学生读者信息进行更新*/ 可借书量=可借书量+1
WHERE 学号=@学号_1
DELETE /*删除该借阅信息，实现还书功能*/ FROM 学生借阅
WHERE 学号=@学号_1 AND 书号=@书号_1
END
GO
说明：该存储过程主要根据输入信息实现还书、计算并打印罚款（包括超期和丢书）的功能，同时对其他表中相关信息作出更新，最后删除该借阅信息。在实验过程中本想用触发器实现，但遇到了一系列问题，最后选择用存储过程实现，并成功地实现了以上各功能。
8）教师还书（注释、说明与上一存储过程类似，不再赘述）
CREA TE PROCEDURE [dbo].[教师还书]
@教师号_1 char(5),
@书号_1 char(5),
@实还日期_1 datetime,
@是否丢失int
AS
DECLARE @罚金float
DECLARE @借期_1 int
DECLARE @超期_2 int
BEGIN
SET @借期_1=DA TEDIFF(day,
(SELECT 借书日期FROM 教师借阅WHERE 教师号=@教师号_1 AND 书号=@书号_1),
@实还日期_1)
SET @超期_2=DA TEDIFF(day,
(SELECT 应还日期FROM 教师借阅WHERE 教师号=@教师号_1 AND 书号=@书号_1),
@实还日期_1)
IF (@超期_2>0)
SET @罚金=@超期_2*0.05
PRINT '超期罚款'+CAST(@罚金AS char(5))
IF (@是否丢失=1)
SET @罚金= 2*(SELECT 定价FROM 图书WHERE @书号_1=书号)
PRINT '丢书罚款'+CAST(@罚金AS char(5))
UPDA TE 图书SET
剩余册数=剩余册数+1，
图书.总借阅时间=图书.总借阅时间+@借期_1
WHERE 图书.书号=@书号_1
UPDA TE 教师借阅SET
实还日期=@实还日期_1
WHERE 教师号=@教师号_1
UPDA TE 教师读者SET
可借书量=可借书量+1
WHERE 教师号=@教师号_1
DELETE
FROM 教师借阅
WHERE 教师号=@教师号_1 AND 书号=@书号_1
END
GO
2、触发器
1）Tri_学生借阅
CREA TE TRIGGER [Tri_学生借阅] ON 学生借阅
FOR INSERT
AS
IF /*借书要求，有逾期、借书量满或借同一本书不可借，回滚INSERT操作*/ ((((SELECT 学生读者.是否有书逾期FROM 学生读者,Inserted WHERE 学生读者.学号=Inserted.学号)=1) /*有逾期*/
OR(SELECT 学生读者.可借书量FROM 学生读者,Inserted WHERE 学生读者.学号=Inserted.学号)=0) /*借书量满*/ OR((SELECT 学生借阅.书号FROM 学生借阅,Inserted WHERE 学生借阅.书号=Inserted.书号AND 学生借阅. 学号=Inserted.学号)!=NULL)) /*借同一本书*/
ROLLBACK TRANSACTION
BEGIN /*符合借书要求，执行以下操作，对相关表的相关属性值进行更新*/ UPDA TE 图书SET
图书.借阅次数=图书.借阅次数+1,
图书.剩余册数=图书.剩余册数-1
FROM 图书,Inserted
WHERE 图书.书号=Inserted.书号
UPDA TE 学生读者SET
学生读者.可借书量=学生读者.可借书量-1
FROM 学生读者,Inserted
WHERE 学生读者.学号=Inserted.学号
UPDA TE 学生借阅SET
学生借阅.应还日期=DA TEADD(day,60,学生借阅.借书日期)
FROM 学生借阅,Inserted
WHERE 学生借阅.书号=Inserted.书号
END
说明：该触发器建立在INSERT操作之上，即在插入新的借阅信息时触发器动作，先对读者的借阅条件进行检查，若不符合要求回滚INSERT操作，即借阅失败，否则即实现插入新的借阅信息，借阅成功。之后，触发器对相关联的表中的某些属性值进行更新，比如图书的“剩余册数”、读者的“可借书量”等等实现自动修改，使其符合实际要求。
2）Tri_教师借阅（注释、说明与上一触发器类似，不再赘述）
CREA TE TRIGGER [Tri_教师借阅] ON 教师借阅
FOR INSERT
AS
IF
((((SELECT 教师读者.是否有书逾期FROM 教师读者,Inserted WHERE 教师读者.教师号=Inserted.教师号)=1)
OR(SELECT 教师读者.可借书量FROM 教师读者,Inserted WHERE 教师读者.教师号=Inserted.教师号)=0)
OR((SELECT 教师借阅.书号FROM 教师借阅,Inserted WHERE 教师借阅.书号=Inserted.书号AND 教师借阅. 教师号=Inserted.教师号)!=NULL))
ROLLBACK TRANSACTION
BEGIN
UPDA TE 图书SET
图书.借阅次数=图书.借阅次数+1,
图书.剩余册数=图书.剩余册数-1
FROM 图书,Inserted
WHERE 图书.书号=Inserted.书号
UPDA TE 教师读者SET
教师读者.可借书量=教师读者.可借书量-1
FROM 教师读者,Inserted
WHERE 教师读者.教师号=Inserted.教师号
UPDA TE 教师借阅SET
教师借阅.应还日期=DA TEADD(day,90,教师借阅.借书日期)
FROM 教师借阅,Inserted
WHERE 教师借阅.书号=Inserted.书号
END
四、实验分析与总结
管理规则的实现
1）只有本校师生才能借书
利用学号、教师号定义时的CHECK约束（学号LIKE“PB[0-9][0-9][0-9]”，教师号LIKE “T[0-9][0-9][0-9]”），对其进行限制，防止其他结构的读者证号插入数据库中。
2）每人同一本书只能借一本书
在触发器Tri_学生借阅、Tri_教师借阅中查询是否已有该读者对该本书的借阅信息，若以有则回滚插入操作，从而实现该约束。
3）借期、续借及超期罚金
通过（实还日期-借书日期）来标记“是否逾期”，从而判断逾期情况，对逾期读者计算超期罚金并打印，另外利用单独的存储过程进行教师读者的续借处理。
4）限制逾期读者借书
在触发器Tri_学生借阅、Tri_教师借阅中查询该读者的借阅信息中是否有逾期标记，若以有则回滚插入操作，从而实现该约束。
5）丢书罚金
在“学生还书”、“教师还书”的存储过程中，判断输入“是否丢失”的值，若为“1”，即丢失，则计算并打印出丢书罚金，否则不操作。
6）统计每本图书的借出次数及借阅时间
利用存储过程进行统计：
CREA TE PROCEDURE [统计次数时间]
AS
SELECT 书名,借阅次数,总借阅时间
FROM 图书
GO
由于基本表中已有所要求的属性值，所以只需进行最简单的SELECT查询即可。
7）统计各类图书的馆藏册数、借出册数和平均借阅时间
利用存储过程进行统计：
CREA TE PROCEDURE [图书按类统计]
AS
SELECT 类别,sum(剩余册数) as 馆藏册数,(sum(总册数)-sum(剩余册数)) as 借出册数,(sum(总借阅时间)/sum(借阅次数)) as 平均借阅时间
FROM 图书
GROUP BY类别
GO
该存储过程中利用了按类别分组以及统计函数，并将统计结果以新列名在结果集中列出。