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

实验题目：设计并实现一个简单的学校图书馆数据库系统实验报告：一、需求分析1、E-R图2、建立基本表1）图书（书号，书名，作者，出版社，定价，类别，借阅次数，总借阅时间，总册数，剩余册书）2）学生读者（学号，姓名，所在系，性别，可借书量，是否有书逾期）3）学生借阅（书号，学号，姓名，书名，借书日期，应还日期，实还日期）4）教师读者（教师号，姓名，单位，性别，可借书量，是否有书逾期）5）教师借阅（书号，教师号，姓名，书名，借书日期，应还日期，实还日期，是否续借）2、关系图二、查询功能的实现1、判断某位读者是否有逾期的图书（以学生读者为例）CREATEPROCEDURE[判断某位学生读者是否有逾期的图书](@学号_1char(5))ASIF((SELECTCOUNT(书号)FROM学生借阅WHERE((DATEDIFF(day,(SELECT应还日期FROM学生借阅WHERE学号=@学号_1),getdate()))0)AND(学号=@学号_1))=0)/*计算是否逾期*/BEGINprint'该读者无逾期的书'UPDATE学生读者SET是否有书逾期=1WHERE学号=@学号_1ENDELSEprint'该读者有逾期的书'GO功能：该存储过程通过对输入的读者号所对应的预期借阅信息进行统计，若结果集非空则通知逾期，同时将‘是否有书逾期’置为‘1’，否则通知逾期。（教师读者查询同理，此处不再赘述）2、图书按借阅量排序CREATEPROCEDURE[图书按借阅量排序]ASSELECT图书.书号,图书.书名,(图书.总册数-图书.剩余册数)as借出量FROM图书ORDERBY图书.借出量ASC/*按借出量升序排序*/GO功能：该存储过程通过图书属性中总册数与剩余册数作差作为借出量，并在结果集中按升序排列。3、查询已全部借出的书CREATEPROCEDURE[查询已全部借出的书]ASSELECT图书.书号,图书.书名FROM图书WHERE图书.剩余册数=0GO功能：该存储过程通过对剩余册书是否为零的判断实现对是否全部借出的判断。4、查询未借出的书CREATEPROCEDURE[查询未借出的书]ASSELECT图书.书号,图书.书名,图书.剩余册数FROM图书WHERE图书.总册数=图书.剩余册数GO功能：该存储过程通过对总册数与剩余册数的比较实现对未借出的图书的判断。5图书按类统计CREATEPROCEDURE[图书按类统计]ASSELECT类别,sum(剩余册数)as馆藏册数,(sum(总册数)-sum(剩余册数))as借出册数,(sum(总借阅时间))as总借阅时间FROM图书GROUPBY类别GO功能：该存储过程通过分组统计函数实现对基本属性的运算，并在结果集中以新属性名列出。三、存储过程和触发器1、存储过程1）新增图书信息CREATEPROCEDURE[新增图书信息](@书号_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])ASINSERTINTO[PB08210172宫永超].[dbo].[图书]([书号],[书名],[作者],[出版社],[定价],[类别],[借阅次数],[总借阅时间],[总册数],[剩余册数])VALUES(@书号_1,@书名_2,@作者_3,@出版社_4,@定价_5,@类别_6,@借阅次数_7,@总借阅时间_8,@总册数_9,@剩余册数_10)GO说明：该存储过程实现新图书信息向图书表中的添加，每次只能添加一个元组。2）新增学生读者信息CREATEPROCEDURE[新增学生读者信息](@学号_1[char](10),@姓名_2[varchar](10),@所在系_3[char](3),@性别_4[char](2),@可借书量_5[int],@是否有书逾期_6[bit])ASINSERTINTO学生读者([学号],[姓名],[所在系],[性别],[可借书量],[是否有书逾期])VALUES(@学号_1,@姓名_2,@所在系_3,@性别_4,@可借书量_5,@是否有书逾期_6)GO说明：该存储过程实现新学生读者信息向学生读者表中的添加，每次只能添加一个元组。3）新增教师读者信息CREATEPROCEDURE[新增教师读者信息](@教师号_1[char](10),@姓名_2[varchar](50),@单位_3[varchar](50),@性别_4[char](2),@可借书量_5[int],@是否有书逾期_6[bit])ASINSERTINTO[PB08210172宫永超].[dbo].[教师读者]([教师号],[姓名],[单位],[性别],[可借书量],[是否有书逾期])VALUES(@教师号_1,@姓名_2,@单位_3,@性别_4,@可借书量_5,@是否有书逾期_6)GO说明：该存储过程实现新教师读者信息向教师读者表中的添加，每次只能添加一个元组。4）插入学生借阅信息CREATEPROCEDURE[插入学生借阅信息](@书号_1[char](10),@学号_2[char](5),@姓名_3[varchar](10),@书名_4[varchar](10),@借书日期_5[datetime])ASINSERTINTO[学生借阅]([书号],[学号],[姓名],[书名],[借书日期])VALUES(@书号_1,@学号_2,@姓名_3,@书名_4,@借书日期_5)GO说明：该存储过程将输入信息在教师借阅表中产生一个新的元组，即插入新的借阅信息，从而实现借书功能。5）插入教师借阅信息CREATEPROCEDURE[插入教师借阅信息](@书号_1[char](10),@教师号_2[char](4),@姓名_3[varchar](10),@书名_4[varchar](10),@借书日期_5[datetime])ASINSERTINTO[PB08210172宫永超].[dbo].[教师借阅]([书号],[教师号],[姓名],[书名],[借书日期])VALUES(@书号_1,@教师号_2,@姓名_3,@书名_4,@借书日期_5)GO说明：该存储过程将输入信息在教师借阅表中产生一个新的元组，即插入新的借阅信息，从而实现借书功能。6）教师借阅数据更新CREATEPROCEDURE[教师借阅数据更新](@书号_1[char],@教师号_2[char],@是否续借_6[int])ASUPDATE教师借阅SET[是否续借]=@是否续借_6/*“是否续借”置为“1”*/WHERE([书号]=@书号_1AND[教师号]=@教师号_2)IF(@是否续借_6=1)UPDATE教师借阅SET应还日期=DATEADD(day,30,教师借阅.应还日期)/*将应还日期推迟一个月*/FROM教师借阅GO说明：该存储过程通过输入的教师号和书号找到对应的借阅信息后对其进行更新，将标记信息“是否续借”置为“1”，并将应还日期推迟一个月，从而实现续借功能。7）学生还书CREATEPROCEDURE[学生还书]@学号_1char(5),@书号_1char(5),@实还日期_1datetime,@是否丢失intASDECLARE@罚金floatDECLARE@借期_1intDECLARE@超期_2intBEGINSET@借期_1=DATEDIFF(day,/*计算借期并用变量@借期_1表示*/(SELECT借书日期FROM学生借阅WHERE学号=@学号_1AND书号=@书号_1),@实还日期_1)SET@超期_2=DATEDIFF(day,/*计算超期并用变量@超期_2表示*/(SELECT应还日期FROM学生借阅WHERE学号=@学号_1AND书号=@书号_1),@实还日期_1)IF(@超期_20)/*计算超期罚金并打印结果*/SET@罚金=@超期_2*0.05PRINT'超期罚款'+CAST(@罚金ASchar(5))IF(@是否丢失=1)/*计算丢书罚金并打印结果*/SET@罚金=2*(SELECT定价FROM图书WHERE@书号_1=书号)PRINT'丢书罚款'+CAST(@罚金ASchar(5))UPDATE图书SET/*对对应图书信息进行更新*/剩余册数=剩余册数+1，总借阅时间=总借阅时间+@借期_1WHERE书号=@书号_1UPDATE学生借阅SET/*对对应学生借阅信息进行更新*/实还日期=@实还日期_1WHERE学号=@学号_1UPDATE学生读者SET/*对对应学生读者信息进行更新*/可借书量=可借书量+1WHERE学号=@学号_1DELETE/*删除该借阅信息，实现还书功能*/FROM学生借阅WHERE学号=@学号_1AND书号=@书号_1ENDGO说明：该存储过程主要根据输入信息实现还书、计算并打印罚款（包括超期和丢书）的功能，同时对其他表中相关信息作出更新，最后删除该借阅信息。在实验过程中本想用触发器实现，但遇到了一系列问题，最后选择用存储过程实现，并成功地实现了以上各功能。8）教师还书（注释、说明与上一存储过程类似，不再赘述）CREATEPROCEDURE[dbo].[教师还书]@教师号_1char(5),@书号_1char(5),@实还日期_1datetime,@是否丢失intASDECLARE@罚金floatDECLARE@借期_1intDECLARE@超期_2intBEGINSET@借期_1=DATEDIFF(day,(SELECT借书日期FROM教师借阅WHERE教师号=@教师号_1AND书号=@书号_1),@实还日期_1)SET@超期_2=DATEDIFF(day,(SELECT应还日期FROM教师借阅WHERE教师号=@教师号_1AND书号=@书号_1),@实还日期_1)IF(@超期_20)SET@罚金=@超期_2*0.05PRINT'超期罚款'+CAST(@罚金ASchar(5))IF(@是否丢失=1)SET@罚金=2*(SELECT定价FROM图书WHERE@书号_1=书号)PRINT'丢书罚款'+CAST(@罚金ASchar(5))UPDATE图书SET剩余册数=剩余册数+1，图书.总借阅时间=图书.总借阅时间+@借期_1WHERE图书.书号=@书号_1UPDATE教师借阅SET实还日期=@实还日期_1WHERE教师号=@教师号_1UPDATE教师读者SET可借书量=可借书量+1WHERE教师号=@教师号_1DELETEFROM教师借阅WHERE教师号=@教师号_1AND书号=@书号_1ENDGO2、触发器1）Tri_学生借阅CREATETRIGGER[Tri_学生借阅]ON学生借阅FORINSERTASIF/*借书要求，有逾期、借书量满或借同一本书不可借，回滚INSERT操作*/((((SELECT学生读者.是否有书逾期FROM学生读者,InsertedWHERE学生读者.学号=Inserted.学号)=1)/*有逾期*/OR(SELECT学生读者.可借书量FROM学生读者,InsertedWHERE学生读者.学号=Inserted.学号)=0)/*借书量满*/OR((SELECT学生借阅.书号FROM学生借阅,InsertedWHERE学生借阅.书号=Inserted.书号AND学生借阅.学号=Inserted.学号)!=NULL))/*借同一本书*/ROLLBACKTRANSACTIONBEGIN/*符合借书要求，执行以下操作，对相关表的相关属性值进行更新*/UPDATE图书SET图书.借阅次数=图书.借阅次数+1,图书.剩余册数=图书.剩余册数-1FROM图书,InsertedWHERE图书.书号=Inserted.书号UPDATE学生读者SET学生读者.可借书量=学生读者.可借书量-1FROM学生读者,InsertedWHER