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

实验题目：设计并实现一个简单的学校图书馆数据库系统实验报告：一、需求分析1、 E-R图2、 建立基本表1）图书（书号，书名，作者，出版社，定价，类别，借阅次数，总借阅时间，总册数，剩余册书）2）学生读者（学号，姓名，所在系，性别，可借书量，是否有书逾期）3）学生借阅（书号，学号，姓名，书名，借书日期，应还日期，实还日期）4）教师读者（教师号，姓名，单位，性别，可借书量，是否有书逾期）5）教师借阅（书号，教师号，姓名，书名，借书日期，应还日期，实还日期，是否续借）2、关系图二、查询功能的实现1、判断某位读者是否有逾期的图书（以学生读者为例）CREATE PROCEDURE 判断某位学生读者是否有逾期的图书 (学号_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，否则通知逾期。（教师读者查询同理，此处不再赘述）2、图书按借阅量排序CREATE PROCEDURE 图书按借阅量排序 ASSELECT 图书.书号,图书.书名,(图书.总册数-图书.剩余册数) as 借出量FROM 图书ORDER BY 图书.借出量 ASC /*按借出量升序排序*/GO功能：该存储过程通过图书属性中总册数与剩余册数作差作为借出量，并在结果集中按升序排列。3、查询已全部借出的书CREATE PROCEDURE 查询已全部借出的书 ASSELECT 图书.书号,图书.书名FROM 图书WHERE 图书.剩余册数=0GO功能：该存储过程通过对剩余册书是否为零的判断实现对是否全部借出的判断。4、查询未借出的书CREATE PROCEDURE 查询未借出的书 ASSELECT 图书.书号,图书.书名,图书.剩余册数FROM 图书WHERE 图书.总册数=图书.剩余册数GO功能：该存储过程通过对总册数与剩余册数的比较实现对未借出的图书的判断。5图书按类统计CREATE PROCEDURE 图书按类统计 ASSELECT 类别,sum(剩余册数) as 馆藏册数,(sum(总册数)-sum(剩余册数) as 借出册数,(sum(总借阅时间) as 总借阅时间FROM 图书GROUP BY 类别GO功能：该存储过程通过分组统计函数实现对基本属性的运算，并在结果集中以新属性名列出。三、存储过程和触发器1、 存储过程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, 出版社_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, 可借书量_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, 是否有书逾期_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）插入教师借阅信息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 教师借阅数据更新(书号_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 PROCEDURE 学生还书 学号_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 书号=书号_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 /*对对应学生借阅信息进行更新*/实还日期=实还日期_1WHERE 学号=学号_1UPDATE 学生读者 SET /*对对应学生读者信息进行更新*/可借书量=可借书量+1WHERE 学号=学号_1DELETE /*删除该借阅信息，实现还书功能*/FROM 学生借阅 WHERE 学号=学号_1 AND 书号=书号_1ENDGO说明：该存储过程主要根据输入信息实现还书、计算并打印罚款（包括超期和丢书）的功能，同时对其他表中相关信息作出更新，最后删除该借阅信息。在实验过程中本想用触发器实现，但遇到了一系列问题，最后选择用存储过程实现，并成功地实现了以上各功能。8）教师还书（注释、说明与上一存储过程类似，不再赘述）CREATE PROCEDURE dbo.教师还书 教师号_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.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 书号=书号_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 WHERE 学生借阅.书号=Inserted.书号 AND 学生借阅. 学号=Inserted.学号)!=NULL) /*借同一本书*/ROLLBACK TRANSACTIONBEGIN /*符合借书要求，执行以下操作，对相关表的相关属性值进行更新*/UPDATE 图书 SET图书.借阅次数=图书.借阅次数+1,图书.剩余册数=图书.剩余册数-1FROM 图书,InsertedWHERE 图书.书号=Inserted.书号UPDATE 学生读者 SET学生读者.可借书量=学生读者.可借书量-1FROM 学生读者,InsertedWHERE 学生读者.学号=Inserted.学号UPDATE 学生借阅 SET学生借阅.应还日期=DATEADD(day,60,学生借阅.借书日期)FROM 学生借阅,InsertedWHERE 学生借阅.书号=Inserted.书号END说明：该触发器建立在INSERT操作之上，即在插入新的借阅信息时触发器动作，先对读者的借阅条件进行检查，若不符合要求回滚INSERT操作，即借阅失败，否则即实现插入新的借阅信息，借阅成功。之后，触发器对相关联的表中的某些属性值进行更新，比如图书的“剩余册数”、读者的“可借书量”等等实现自动修改，使其符合实际要求。2）Tri_教师借阅（注释、说明与上一触发器类似，不再赘述）CREATE TRIGGER Tri_教师借阅 ON 教师借阅 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图书.借阅次数=图书.借阅次数+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”，教师号LIKE“T0-90-90-9”），对其进行限制，防止其他结构的读者证号插入数据库中。2） 每人同一本书只能借一本书在触发器Tri_学生借阅、Tri_教师借阅中查询是否已有该读者对该本书的借阅信息，若以有则回滚插入操作，从而实现该约束。3） 借期、续借及超期罚金通过（实还日期-借书日期）来标记“是否逾期”，从而判断逾期情况，对逾期读者计算超期罚金并打印，另外利用单独的存储过程进行教师读者的续借处理。4） 限制逾期读者借书在触发器Tri_学生借阅、Tri_教师借阅中查询该读者的借阅信息中是否有逾期标记，若以有则回滚插入操作，从而实现该约束。5）丢书罚金在“学生还书”、“教师还书”的存储过程中，判断输入“是否丢失”的值，若为“1”，即丢失，则计算并打印出丢书罚金，否则不操作。6）统计每本图书的借出次数及借阅时间利用存储过程进行统计：CREATE PROCEDURE 统计次数时间 ASSELECT 书名,借阅次数,总借阅时间FROM 图书GO由于基本表中已有所要求的属性值，所以只需进行最简单的SELECT查询即可。7） 统计各类图书的馆藏册数、借出册数和平均借阅时间利用存储过程进行统计：CREATE PROCEDURE 图书按类统计ASSELECT 类别,sum(剩余册数) as 馆藏册数,(sum(总册数)-sum(剩余册数) as 借出册数,(sum(总借阅时间)/sum(借阅次数) as 平均借阅时间FROM 图书GROUP BY 类别GO该存储过程中利用了按类别分组以及统计函数，并将统计结果以新列名在结果集中列出。