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

《设计并实现一个简单的学校图书馆数据库系统.pdf》由会员分享，可在线阅读，更多相关《设计并实现一个简单的学校图书馆数据库系统.pdf（11页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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

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

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

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

5、50),作者_3varchar(50),出版社_4 varchar(50),定价_5float,类别_6varchar(50),借阅次数_7int,总借阅时间_8 int,总册数_9 int,剩余册数_10int)AS INSERT INTO PB08210172 宫永超.dbo.图书(书号,书名,作者,出版社,定价,类别,借阅次数,总借阅时间,总册数,剩余册数)VALUES(书号_1,书名_2,作者_3,出版社_4,定价_5,类别_6,借阅次数_7,总借阅时间_8,总册数_9,剩余册数_10)GO说明：该存储过程实现新图书信息向图书表中的添加，每次只能添加一个元组。2新增学生读者信息CREA

6、TE PROCEDURE 新增学生读者信息(学号_1char(10),_2varchar(10),所在系_3 char(3),性别_4char(2),可借书量_5int,是否有书逾期_6bit)AS INSERT INTO 学生读者(学号,所在系,性别,可借书量,是否有书逾期)VALUES(学号_1,_2,所在系_3,性别_4,可借书量_5,是否有书逾期_6)GO说明：该存储过程实现新学生读者信息向学生读者表中的添加，每次只能添加一个元组。3新增教师读者信息CREATE PROCEDURE 新增教师读者信息(教师号_1 char(10),_2varchar(50),单位_3varchar(50

7、),性别_4char(2),可借书量_5int,是否有书逾期_6bit)AS INSERT INTO PB08210172 宫永超.dbo.教师读者(教师号,单位,性别,可借书量,是否有书逾期)VALUES(教师号_1,_2,单位_3,性别_4,可借书量_5,是否有书逾期_6)GO说明：该存储过程实现新教师读者信息向教师读者表中的添加，每次只能添加一个元组。4插入学生借阅信息CREATE PROCEDURE 插入学生借阅信息(书号_1char(10),学号_2char(5),_3varchar(10),书名_4varchar(10),借书日期_5datetime)AS INSERT INTO

8、学生借阅(书号,学号,书名,借书日期)VALUES(书号_1,学号_2,_3,书名_4,借书日期_5)GO说明：该存储过程将输入信息在教师借阅表中产生一个新的元组，即插入新的借阅信息，从而实现借书功能。5插入教师借阅信息CREATE PROCEDURE 插入教师借阅信息(书号_1char(10),教师号_2 char(4),_3varchar(10),书名_4varchar(10),借书日期_5datetime)AS INSERT INTO PB08210172 宫永超.dbo.教师借阅(书号,教师号,书名,借书日期)VALUES(书号_1,教师号_2,_3,书名_4,借书日期_5)GO说明：

9、该存储过程将输入信息在教师借阅表中产生一个新的元组，即插入新的借阅信息，从而实现借书功能。6）教师借阅数据更新CREATE PROCEDURE 教师借阅数据更新(书号_1char,教师号_2 char,是否续借_6int)ASUPDATE教师借阅SET 是否续借=是否续借_6/*“是否续借”置为“1”*/WHERE(书号=书号_1 AND 教师号=教师号_2)IF(是否续借_6=1)UPDATE 教师借阅 SET应还日期=DATEADD(day,30,教师借阅.应还日期)/*将应还日期推迟一个月*/FROM 教师借阅GO说明：该存储过程通过输入的教师号和书号找到对应的借阅信息后对其进行更新，将

10、标记信息“是否续借”置为“1”，并将应还日期推迟一个月，从而实现续借功能。7学生还书CREATE PROCEDURE 学生还书学号_1char(5),书号_1char(5),实还日期_1 datetime,是否丧失 intASDECLARE 罚金 floatDECLARE 借期_1intDECLARE 超期_2intBEGINSET 借期_1=DATEDIFF(day,/*计算借期并用变量借期_1 表示*/(SELECT借书日期 FROM学生借阅 WHERE 学号=学号_1 AND 书号=书号_1),实还日期_1)SET 超期_2=DATEDIFF(day,/*计算超期并用变量超期_2 表示*

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

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

13、DURE dbo.教师还书教师号_1char(5),书号_1char(5),实还日期_1 datetime,是否丧失 intASDECLARE 罚金 floatDECLARE 借期_1intDECLARE 超期_2intBEGINSET 借期_1=DATEDIFF(day,(SELECT借书日期 FROM教师借阅 WHERE 教师号=教师号_1 AND 书号=书号_1),实还日期_1)SET 超期_2=DATEDIFF(day,(SELECT应还日期 FROM教师借阅 WHERE 教师号=教师号_1 AND 书号=书号_1),实还日期_1)IF(超期_20)PRINT 超期罚款+CAST(罚金

14、 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 教师号=教师号_1DELETEFROM 教师借阅WHERE 教师号=教师号_1 AND 书号=书号_1ENDGO2、触发器1Tri_学生借阅CREAT

15、E TRIGGER Tri_ 学生借阅 ON 学生借阅FOR INSERTASIF/*借书要求，有逾期、借书量满或借同一本书不可借，回滚INSERT操作*/(SELECT 学生读者.是否有书逾期FROM 学生读者,Inserted WHERE 学生读者.学号=Inserted.学号)=1)/*有逾期*/OR(SELECT 学生读者.可借书量 FROM 学生读者,Inserted WHERE 学生读者.学号=Inserted.学号)=0)/*借书量满*/OR(SELECT 学生借阅.书号 FROM学生借阅,Inserted WHERE 学生借阅.书号=Inserted.书号 AND学生借阅.学号

16、=Inserted.学号)!=NULL)/*借同一本书*/ROLLBACK TRANSACTIONBEGIN/*符合借书要求，执行以下操作，对相关表的相关属性值进行更新*/UPDATE 图书 SET图书.借阅次数=图书.借阅次数+1,FROM 图书,InsertedUPDATE 学生读者 SETFROM 学生读者,InsertedUPDATE 学生借阅 SET学生借阅.应还日期=DATEADD(day,60,学生借阅.借书日期)FROM 学生借阅,InsertedEND说明：该触发器建立在 INSERT操作之上，即在插入新的借阅信息时触发器动作，先对读者的借阅条件进行检查，假设不符合要求回滚

17、INSERT操作，即借阅失败，否则即实现插入新的借阅信息，借阅成功。之后，触发器对相关联的表中的某些属性值进行更新，比方图书的“剩余册数”、读者的“可借书量”等等实现自动修改，使其符合实际要求。2Tri_教师借阅注释、说明与上一触发器类似，不再赘述CREATE TRIGGER Tri_ 教师借阅 ON 教师借阅FOR INSERTASIF(SELECT 教师读者.是否有书逾期 FROM 教师读者,Inserted WHERE 教师读者.教师号=Inserted.教师号)=1)OR(SELECT 教师读者.可借书量FROM 教师读者,Inserted WHERE 教师读者.教师号=Inserte

18、d.教师号)=0)OR(SELECT 教师借阅.书号 FROM 教师借阅,Inserted WHERE 教师借阅.书号=Inserted.书号 AND教师借阅.教师号=Inserted.教师号)!=NULL)ROLLBACK TRANSACTIONBEGINUPDATE 图书 SET图书.借阅次数=图书.借阅次数+1,FROM 图书,InsertedUPDATE 教师读者 SETFROM 教师读者,InsertedUPDATE 教师借阅 SET教师借阅.应还日期=DATEADD(day,90,教师借阅.借书日期)FROM 教师借阅,InsertedEND四、实验分析与总结四、实验分析与总结管理

19、规则的实现1）只有本校师生才能借书利用学号、教师号定义时的 CHECK 约束 学号 LIKE“PB0-90-90-9”，教师号 LIKE“T0-90-90-9”，对其进行限制，防止其他结构的读者证号插入数据库中。2）每人同一本书只能借一本书在触发器 Tri_学生借阅、Tri_教师借阅中查询是否已有该读者对该本书的借阅信息，假设以有则回滚插入操作，从而实现该约束。3）借期、续借及超期罚金通过实还日期-借书日期来标记“是否逾期”，从而判断逾期情况，对逾期读者计算超期罚金并打印，另外利用单独的存储过程进行教师读者的续借处理。4）限制逾期读者借书在触发器 Tri_学生借阅、Tri_教师借阅中查询该读者

20、的借阅信息中是否有逾期标记，假设以有则回滚插入操作，从而实现该约束。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该存储过程中利用了按类别分组以及统计函数，并将统计结果以新列名在结果集中列出。