设计并实现一个简单的学校图书馆数据库系统

1、精选文档试验题目：设计并实现一个简洁的学校图书馆数据库系统试验报告：一、需求分析1、 E-R图2、 建立基本表1）图书（书号，书名，作者，出版社，定价，类别，借阅次数，总借阅时间，总册数，剩余册书）2）同学读者（学号，姓名，所在系，性别，可借书量，是否有书逾期）3）同学借阅（书号，学号，姓名，书名，借书日期，应还日期，实还日期）4）老师读者（老师号，姓名，单位，性别，可借书量，是否有书逾期）5）老师借阅（书号，老师号，姓名，书名，借书日期，应还日期，实还日期，是否续借）2、关系图二、查询功能的实现1、推断某位读者是否有逾期的图书（以同学读者为例）CREATE PROCEDURE 推断某位同学读

2、者是否有逾期的图书 (学号_1 char(5)ASIF(SELECT COUNT (书号)FROM 同学借阅WHERE (DATEDIFF(day,(SELECT 应还日期 FROM 同学借阅 WHERE 学号=学号_1),getdate()0) AND (学号=学号_1)=0) /*计算是否逾期*/BEGINprint该读者无逾期的书UPDATE 同学读者 SET是否有书逾期=1WHERE 学号=学号_1ENDELSE print该读者有逾期的书GO功能：该存储过程通过对输入的读者号所对应的预期借阅信息进行统计，若结果集非空则通知逾期，同时将是否有书逾期置为1，否则通知逾期。（老师读者查询同

3、理，此处不再赘述）2、图书按借阅量排序CREATE PROCEDURE 图书按借阅量排序 ASSELECT 图书.书号,图书.书名,(图书.总册数-图书.剩余册数) as 借出量FROM 图书ORDER BY 图书.借出量 ASC /*按借出量升序排序*/GO功能：该存储过程通过图书属性中总册数与剩余册数作差作为借出量，并在结果集中按升序排列。3、查询已全部借出的书CREATE PROCEDURE 查询已全部借出的书 ASSELECT 图书.书号,图书.书名FROM 图书WHERE 图书.剩余册数=0GO功能：该存储过程通过对剩余册书是否为零的推断实现对是否全部借出的推断。4、查询未借出的书C

4、REATE PROCEDURE 查询未借出的书 ASSELECT 图书.书号,图书.书名,图书.剩余册数FROM 图书WHERE 图书.总册数=图书.剩余册数GO功能：该存储过程通过对总册数与剩余册数的比较实现对未借出的图书的推断。5图书按类统计CREATE PROCEDURE 图书按类统计 ASSELECT 类别,sum(剩余册数) as 馆藏册数,(sum(总册数)-sum(剩余册数) as 借出册数,(sum(总借阅时间) as 总借阅时间FROM 图书GROUP BY 类别GO功能：该存储过程通过分组统计函数实现对基本属性的运算，并在结果集中以新属性名列出。三、存储过程和触发器1、 存

5、储过程1）新增图书信息CREATE 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.图书 ( 书号, 书名, 作者, 出版社, 定价, 类别, 借阅次数, 总借阅时间, 总册数, 剩余册数) VALUES ( 书号_1, 书名_2, 作者_3, 出版

6、社_4, 定价_5, 类别_6, 借阅次数_7, 总借阅时间_8, 总册数_9, 剩余册数_10)GO说明：该存储过程实现新图书信息向图书表中的添加，每次只能添加一个元组。2）新增同学读者信息CREATE PROCEDURE 新增同学读者信息(学号_1 char(10), 姓名_2 varchar(10), 所在系_3 char(3), 性别_4 char(2), 可借书量_5 int, 是否有书逾期_6 bit)AS INSERT INTO 同学读者 ( 学号, 姓名, 所在系, 性别, 可借书量, 是否有书逾期) VALUES ( 学号_1, 姓名_2, 所在系_3, 性别_4, 可借书量

7、_5, 是否有书逾期_6)GO说明：该存储过程实现新同学读者信息向同学读者表中的添加，每次只能添加一个元组。3）新增老师读者信息CREATE PROCEDURE 新增老师读者信息(老师号_1 char(10), 姓名_2 varchar(50), 单位_3 varchar(50), 性别_4 char(2), 可借书量_5 int, 是否有书逾期_6 bit)AS INSERT INTO PB08210172 宫永超.dbo.老师读者 ( 老师号, 姓名, 单位, 性别, 可借书量, 是否有书逾期) VALUES ( 老师号_1, 姓名_2, 单位_3, 性别_4, 可借书量_5, 是否有书逾

8、期_6)GO说明：该存储过程实现新老师读者信息向老师读者表中的添加，每次只能添加一个元组。4）插入同学借阅信息CREATE PROCEDURE 插入同学借阅信息(书号_1 char(10), 学号_2 char(5), 姓名_3 varchar(10), 书名_4 varchar(10), 借书日期_5 datetime)AS INSERT INTO 同学借阅 ( 书号, 学号, 姓名, 书名, 借书日期) VALUES ( 书号_1, 学号_2, 姓名_3, 书名_4, 借书日期_5)GO说明：该存储过程将输入信息在老师借阅表中产生一个新的元组，即插入新的借阅信息，从而实现借书功能。5）插入

9、老师借阅信息CREATE PROCEDURE 插入老师借阅信息(书号_1 char(10), 老师号_2 char(4), 姓名_3 varchar(10), 书名_4 varchar(10), 借书日期_5 datetime)AS INSERT INTO PB08210172 宫永超.dbo.老师借阅 ( 书号, 老师号, 姓名, 书名, 借书日期) VALUES ( 书号_1, 老师号_2, 姓名_3, 书名_4, 借书日期_5)GO说明：该存储过程将输入信息在老师借阅表中产生一个新的元组，即插入新的借阅信息，从而实现借书功能。6） 老师借阅数据更新CREATE PROCEDURE 老师借

10、阅数据更新(书号_1 char, 老师号_2 char, 是否续借_6 int)AS UPDATE 老师借阅SET 是否续借 = 是否续借_6 /*“是否续借”置为“1”*/WHERE ( 书号 = 书号_1 AND 老师号 = 老师号_2)IF( 是否续借_6=1) UPDATE 老师借阅 SET应还日期=DATEADD(day,30,老师借阅.应还日期) /*将应还日期推迟一个月*/FROM 老师借阅GO说明：该存储过程通过输入的老师号和书号找到对应的借阅信息后对其进行更新，将标记信息“是否续借”置为“1”，并将应还日期推迟一个月，从而实现续借功能。7）同学还书CREATE PROCEDU

11、RE 同学还书 学号_1 char(5), 书号_1 char(5), 实还日期_1 datetime, 是否丢失 int ASDECLARE 罚金 floatDECLARE 借期_1 intDECLARE 超期_2 intBEGINSET 借期_1=DATEDIFF(day, /*计算借期并用变量借期_1表示*/(SELECT 借书日期 FROM 同学借阅 WHERE 学号=学号_1 AND 书号=书号_1),实还日期_1)SET 超期_2=DATEDIFF(day, /*计算超期并用变量超期_2表示*/(SELECT 应还日期 FROM 同学借阅 WHERE 学号=学号_1 AND 书号=

12、书号_1),实还日期_1)IF (超期_20) /*计算超期罚金并打印结果*/SET 罚金=超期_2*0.05PRINT 超期罚款+CAST(罚金 AS char(5)IF (是否丢失=1) /*计算丢书罚金并打印结果*/SET 罚金= 2*(SELECT 定价 FROM 图书 WHERE 书号_1=书号 )PRINT 丢书罚款+CAST(罚金 AS char(5)UPDATE 图书 SET /*对对应图书信息进行更新*/剩余册数=剩余册数+1， 总借阅时间=总借阅时间+借期_1WHERE 书号=书号_1UPDATE 同学借阅 SET /*对对应同学借阅信息进行更新*/实还日期=实还日期_1W

13、HERE 学号=学号_1UPDATE 同学读者 SET /*对对应同学读者信息进行更新*/可借书量=可借书量+1WHERE 学号=学号_1DELETE /*删除该借阅信息，实现还书功能*/FROM 同学借阅 WHERE 学号=学号_1 AND 书号=书号_1ENDGO说明：该存储过程主要依据输入信息实现还书、计算并打印罚款（包括超期和丢书）的功能，同时对其他表中相关信息作出更新，最终删除该借阅信息。在试验过程中本想用触发器实现，但遇到了一系列问题，最终选择用存储过程实现，并成功地实现了以上各功能。8）老师还书（注释、说明与上一存储过程类似，不再赘述）CREATE PROCEDURE dbo.老

14、师还书 老师号_1 char(5), 书号_1 char(5), 实还日期_1 datetime, 是否丢失 int ASDECLARE 罚金 floatDECLARE 借期_1 intDECLARE 超期_2 intBEGINSET 借期_1=DATEDIFF(day,(SELECT 借书日期 FROM 老师借阅 WHERE 老师号=老师号_1 AND 书号=书号_1),实还日期_1)SET 超期_2=DATEDIFF(day,(SELECT 应还日期 FROM 老师借阅 WHERE 老师号=老师号_1 AND 书号=书号_1),实还日期_1)IF (超期_20)SET 罚金=超期_2*0.

15、05PRINT 超期罚款+CAST(罚金 AS char(5)IF (是否丢失=1)SET 罚金= 2*(SELECT 定价 FROM 图书 WHERE 书号_1=书号 )PRINT 丢书罚款+CAST(罚金 AS char(5)UPDATE 图书 SET 剩余册数=剩余册数+1，图书.总借阅时间=图书.总借阅时间+借期_1WHERE 图书.书号=书号_1UPDATE 老师借阅 SET实还日期=实还日期_1WHERE 老师号=老师号_1UPDATE 老师读者 SET可借书量=可借书量+1WHERE 老师号=老师号_1DELETE FROM 老师借阅 WHERE 老师号=老师号_1 AND 书号

16、=书号_1ENDGO2、 触发器1）Tri_同学借阅CREATE TRIGGER Tri_同学借阅 ON 同学借阅 FOR INSERT ASIF /*借书要求，有逾期、借书量满或借同一本书不行借，回滚INSERT操作*/(SELECT 同学读者.是否有书逾期 FROM 同学读者,Inserted WHERE 同学读者.学号=Inserted.学号)=1) /*有逾期*/OR(SELECT 同学读者.可借书量 FROM 同学读者,Inserted WHERE 同学读者.学号=Inserted.学号)=0) /*借书量满*/OR(SELECT 同学借阅.书号FROM 同学借阅,Inserted

17、WHERE 同学借阅.书号=Inserted.书号 AND 同学借阅. 学号=Inserted.学号)!=NULL) /*借同一本书*/ROLLBACK TRANSACTIONBEGIN /*符合借书要求，执行以下操作，对相关表的相关属性值进行更新*/UPDATE 图书 SET图书.借阅次数=图书.借阅次数+1,图书.剩余册数=图书.剩余册数-1FROM 图书,InsertedWHERE 图书.书号=Inserted.书号UPDATE 同学读者 SET同学读者.可借书量=同学读者.可借书量-1FROM 同学读者,InsertedWHERE 同学读者.学号=Inserted.学号UPDATE 同

18、学借阅 SET同学借阅.应还日期=DATEADD(day,60,同学借阅.借书日期)FROM 同学借阅,InsertedWHERE 同学借阅.书号=Inserted.书号END说明：该触发器建立在INSERT操作之上，即在插入新的借阅信息时触发器动作，先对读者的借阅条件进行检查，若不符合要求回滚INSERT操作，即借阅失败，否则即实现插入新的借阅信息，借阅成功。之后，触发器对相关联的表中的某些属性值进行更新，比如图书的“剩余册数”、读者的“可借书量”等等实现自动修改，使其符合实际要求。2）Tri_老师借阅（注释、说明与上一触发器类似，不再赘述）CREATE TRIGGER Tri_老师借阅 O

19、N 老师借阅 FOR INSERT ASIF(SELECT 老师读者.是否有书逾期 FROM 老师读者,Inserted WHERE 老师读者.老师号=Inserted.老师号)=1)OR(SELECT 老师读者.可借书量 FROM 老师读者,Inserted WHERE 老师读者.老师号=Inserted.老师号)=0)OR(SELECT 老师借阅.书号 FROM 老师借阅,Inserted WHERE 老师借阅.书号=Inserted.书号 AND 老师借阅. 老师号=Inserted.老师号)!=NULL)ROLLBACK TRANSACTIONBEGINUPDATE 图书 SET图书.

20、借阅次数=图书.借阅次数+1,图书.剩余册数=图书.剩余册数-1FROM 图书,InsertedWHERE 图书.书号=Inserted.书号UPDATE 老师读者 SET老师读者.可借书量=老师读者.可借书量-1FROM 老师读者,InsertedWHERE 老师读者.老师号=Inserted.老师号UPDATE 老师借阅 SET老师借阅.应还日期=DATEADD(day,90,老师借阅.借书日期)FROM 老师借阅,InsertedWHERE 老师借阅.书号=Inserted.书号END四、试验分析与总结管理规章的实现1） 只有本校师生才能借书利用学号、老师号定义时的CHECK约束（学号LIKE“PB0-90-90-9”，老师号LI