图书管理系统数据库设计-MYSQL实现

..-.可修编.图书数据库设计一、系统概述1、系统简介图书管理是每个图书馆都需要进展的工作。一个设计良好的图书管理系统数据库能够给图书管理带来很大的便利。2、需求分析图书管理系统的需求定义为：1.学生可以直接通过借阅终端来查阅书籍信息，同时也可以查阅自己的借阅信息。2.当学生需要借阅书籍时，通过账号密码登陆借阅系统，借阅系统处理学生的借阅，同时修改图书馆保存的图书信息，修改被借阅的书籍是否还有剩余，同时更新学生个人的借阅信息。3.学生借阅图书之前需要将自己的个人信息注册，登陆时对照学生信息。4.学生直接归还图书，根据图书编码修改借阅信息5.管理员登陆管理系统后，可以修改图书信息，增加或者删除图书信息6.管理员可以注销学生信息。通过需求定义，画出图书管理系统的数据流图：数据流图二、系统功能设计画出系统功能模块图并用文字对各功能模块进展详细介绍。系统功能模块图：三、数据库设计案图表1、系统E-R模型总体E-R图：精细化的局部E-R图：学生借阅-归还E-R图：管理员E-R图：2、设计表给出设计的表名、构造以及表上设计的完整性约束。student：列名数据类型是否为空/性质说明stu_idintnotnull/PK标明学生唯一学号stu_namevarcharnotnull学生XXstu_sexvarcharnotnull学生性别stu_ageintnotnull学生年龄stu_provarcharnotnull学生专业stu_gradevarcharnotnull学生年级stu_integrityintnotnull/default=1学生诚信级book:列名数据类型是否为空/性质说明book_idintnotnull/PK唯一书籍序号book_namevarcharnotnull书籍名称book_authorvarcharnotnull书籍作者book_pubvarcharnotnull书籍book_numintnotnull书籍是否在架上book_sortvarcharnotnull书籍分类book_recorddatatimenull书籍登记日期book_sort:列名数据类型是否为空/性质说明sort_idvarcharnotnull/PK类型编号sort_namevarcharnotnull类型名称borrow:存储学生的借书信息列名数据类型是否为空/性质说明student_idvarcharnotnull/PK学生编号book_idvarcharnotnull/PK书籍编号borrow_datedatatimenull借书时间expect_return_datedatetimenull预期归还时间return_table:存储学生的归还信息列名数据类型是否为空/性质说明student_idvarcharnotnull/PK学生编号book_idvarcharnotnull/PK书籍编号borrow_datedatetimenull借书时间return_datedatatimenull实际还书时间ticket:存储学生的罚单信息列名数据类型是否为空/性质说明student_idvarcharnotnull/PK学生编号book_idvarcharnotnull/PK书籍编号over_dateintnull超期天数ticket_feefloatnull处分金额manager:列名数据类型是否为空/性质说明manager_idvarcharnotnull/PK管理员编号manager_namevarcharnotnull管理员XXmanager_agevarcharnotnull管理员年龄manager_phonevarcharnotnull管理员3、设计索引给出在各表上建立的索引以及使用的语句。student：1.为stu_id创立索引，升序排序sql:createindexindex_idonstudent(stu_idasc);2.为stu_name创立索引，并且降序排序sql:altertablestudentaddindexindex_name(stu_name,desc);插入索引操作和结果如下所示：mysql>createindexindex_idonstudent(stu_idasc);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:0mysql>altertablestudentaddindexindex_name(stu_namedesc);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:0mysql>book:1.为book_id创立索引，升序排列sql:createindexindex_bidonbook(book_id);2.为book_record创立索引，以便便查询图书的登记日期信息，升序：sql:createindexindex_brecordonbook(book_record);插入索引的操作和结果如下所示：mysql>createindexindex_bidonbook(book_id);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:0mysql>createindexindex_brecordonbook(book_record);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:0borrow:1.为stu_id和book_id创立多列索引：sql:createindexindex_sid_bidonborrow(stu_idasc,book_idasc);插入索引的操作和结果如下所示：mysql>createindexindex_sid_bidonborrow(stu_idasc,book_idasc);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:0return_table:1.为stu_id和book_id创立多列索引：sql:createindexindex_sid_bidonreturn_table(stu_idasc,book_idasc);插入索引的操作和结果如下所示：mysql>createindexindex_sid_bid_ronreturn_table(stu_idasc,book_idasc);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:0ticket:1.为stu_id和book_id创立多列索引：sql:createindexindex_sid_bidonticket(stu_idasc,book_idasc);插入索引的操作和结果如下所示：mysql>createindexindex_sid_bidonticket(stu_idasc,book_idasc);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:0manager:1.为manager_id创立索引：sql:createindexindex_midonmanager(manager_id);插入索引的操作和结果如下所示：mysql>createindexindex_midonmanager(manager_id);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:04、设计视图给出在各表上建立的视图以及使用的语句。1.在表student上创立计算机专业(cs)学生的视图stu_cs：sql:createviewstu_csasselect*fromstudentwherepro=‘cs’;操作和结果：mysql>createviewstu_csasselect*fromstudentwherestu_pro='cs';QueryOK,0rowsaffected2.在表student,borrow和book上创立借书者的全面信息视图stu_borrow：sql:createviewstu_borrowasselectstudent.stu_id,book.book_id,student.stu_name,book.book_name,borrow_date，adddate(borrow_date,30)expect_return_datefromstudent,book,borrowwherestudent.stu_id=borrow.stu_idandbook.book_id=borrow.book_id;操作和结果：mysql>createviewstu_borrowasselectstudent.stu_id,book.book_id,student.stu_name,book.book_name,borrow_date，adddate(borrow_date,30)expect_return_datefromstudent,book,borrowwherestudent.stu_id=borrow.stu_idandbook.book_id=borrow.book_id;QueryOK,0rowsaffected3.创立类别1的所有图书的视图cs_book：sql:createviewcs_bookasselect*frombookwherebook.book_sortin(selectbook_sort.sort.namefrombook_sortwheresort_id=1);操作和结果显示：mysql>createviewcs_bookasselect*frombookwherebook.book_sortin(selectbook_sort.sort_namefrombook_sortwheresort_id=1);QueryOK,0rowsaffected4.创立个人所有借书归还纪录视图stu_borrow_return:sql:createviewstu_borrow_returnasselectstudent.stu_id,student.stu_name,book.book_id,book.book_name,return_table.borrow_date,return_table.return_datefromstudent,book,return_tablewherestudent.stu_id=return_table.stu_idandbook.book_id=return_table.book_id;5、设计触发器给出在各表上建立的触发器以及使用的语句。1.设计触发器borrow,当某学生借书成功后，图书表相应的图书不在架上，变为0：sql:createtriggerborrowafterinsertonborrowforeachrowbeginupdatebooksetbook_num=book_num–1wherebook_id=new.book_id;end操作与结果显示：mysql>delimiter$$mysql>createtriggertrigger_borrow->afterinsertonborrow->foreachrow->begin->updatebooksetbook_num=book_num-1->wherebook_id=new.book_id;->end->$$QueryOK,0rowsaffected在插入表borrow之前，book_id=1的图书还在架上，为1：学生1借了这本书后，在borrow中插入了一条记录：在borrow中插入这条记录后，book_id=1的图书，不在架上，为0：2.设计触发器trigger_return,还书成功后，对应的书籍book_num变为1：sql:createtriggertrigger_returnafterinsertonreturn_tableforeachrowbeginupdatebooksetbook_num=book_num+1wherebook_id=new.book_id;end还书时在return_table插入表项：此时图书归还架上：3.定义定时器〔事件〕eventJob，每天自动触发一次，扫描视图stu_borrow，假设发现当前有预期归还时间小于当前时间，那么判断为超期，生成处分记录，这个定时器将每天定时触发存储过程proc_gen_ticket：sql:createeventifnotexistseventJobonscheduleevery1DAY/*每天触发*/onpletionPRESERVEdocallproc_gen_ticket(getdate());/*调用存储过程*/setglobalevent_scheduler=1;altereventeventJobonpletionpreserveenable;/*开启定时器*/操作和结果显示：1).学生1借了图书1，生成借书记录stu_borrow视图，如下：2).当他在1月27日前还书时，没有生成罚单：3).当他在1月27日后还书时，生成罚单：4.设计触发器trigger_credit，假设处分记录超过30条，那么将这个学生的诚信级设置为0，下次不允借书：sql:createtriggertrigger_creditafterinsertonticketforeachrowbeginif(selectcount(*)fromticketwherestu_id=new.stu_id)>30thenupdatestudentsetstu_integrity=0wherestu_id=new.stu_id;endif;end操作和结果显示，测试时选择插入ticket项大于3，因为30太大了，不容易测试：学生1超过3次超期归还图书后，产生了4条罚单：此时触动触发器trigger_credit，将学生1的诚信级设置为0：四、应用程序设计与编码实现1、系统实现中存储函数和存储过程的设计要求给出功能描述和代码。1.设计存储过程，产生罚单proc_gen_ticket：当日期超过预定归还日期时，产生罚单，并将记录写入表ticket中，这个存储过程在定时器eventJob中调用：sql：createprocedureproc_gen_ticket(incurrentdatedatetime)BEGINdeclarecur_datedatetime;setcur_date=currentdate;replaceintoticket(stu_id,book_id,over_date,ticket_fee)selectstu_id,book_id,datediff(cur_date,stu_borrow.expect_return_date),0.1*datediff(cur_date,stu_borrow.expect_return_date)fromstu_borrowwherecur_date>stu_borrow.expect_return_date;end操作和结果显示：1).学生1借了图书1，生成借书记录stu_borrow视图，如下：2).当他在1月27日前还书时，没有生成罚单：3).当他在1月27日后还书时，生成罚单：2.设计学生注册信息存储过程：学生注册信息stu_registersql:createprocedurestu_register(instu_idint,instu_namevarchar(20),instu_sexvarchar(20),instu_ageint,instu_provarchar(20),instu_gradevarchar(20))begininsertintostudent(stu_id,stu_name,stu_sex,stu_age,stu_pro,stu_grade)values(stu_id,stu_name,stu_sex,stu_age,stu_pro,stu_grade);end3.设计管理员注册信息存储过程：ma_registersql:createprocedurema_register(inma_idint,inma_namevarchar(20),inma_ageint,inma_phoneint)BEGINinsertintomanagervalues(ma_id,ma_name,ma_age,ma_phone);END4.借书过程的实现:设计存储函数，func_get_credit，返回学生的诚信级：createfunctionfunc_get_credit(stu_idint)returnsintbeginreturn(selectstu_integrityfromstudentwherestudent.stu_id=stu_id);end设计存储函数，func_get_booknum，返回书籍是否在架上：createfunctionfunc_get_booknum(book_idint)returnsintbeginreturn(selectbook_numfrombookwherebook.book_id=book_id);end设计存储过程proc_borrow，调用func_get_credit和func_get_booknum，判断这个学生诚信度和书籍是否在架上，假设为真，那么借书成功，在borrrow表中插入纪录；否那么提示失败：createprocedureproc_borrow(instu_idint,inbook_idint,inborrow_datedatetime)beginiffunc_get_credit(stu_id)=1andfunc_get_booknum(book_id)=1theninsertintoborrowvalues(stu_id,book_id,borrow_date);elseselect'failedtoborrow';endif;end实验操作与结果显示：borrow纪录为空：执行函数，学生1借图书2：callproc_borrow(1,2,now());学生1的诚信级为0：借书失败：修改学生1诚信级为1：此时借书成功：5.还书存储过程proc_return：当还书时，查看是否书是否超期，即查询ticket表项，当发现超期，提示交罚单后再次还书，如没有超期，那么纪录归还到return_table中，并且删除借书纪录〔以免还书后定时器仍然扫描这个纪录〕：sql：createprocedureproc_return(instu_idint,inbook_idint,inreturn_datedatetime)beginDECLAREborrowdatedatetime;if(selectpayofffromticketwhereticket.stu_id=stu_idandticket.book_id=book_id)=1then/*判断是否交了罚单，1表示没有交*/select'pleasepayofftheticket';else/*纪录归还工程到return_table中，并且删除借书纪录*/setborrowdate=(selectborrow_datefromborrowwhereborrow.stu_id=stu_idandborrow.book_id=book_id);insertintoreturn_tablevalues(stu_id,book_id,borrowdate,return_date);deletefromborrowwhereborrow.stu_id=stu_idandborrow.book_id=book_id;endif;end实验操作与结果显示：学生1借了图书2：超期产生了罚单，没有交罚单，payoff=1：此时调用还书过程：callproc_return(1,2,now());提示交罚单：交罚单，调用proc_payoff:callproc_payoff(1,2);交罚单成功，payoff=0；此时再次调用还书过程：callproc_return(1,2,now());还书成功，在return_table生成了还书纪录：6.交罚单存储过程：修改罚单中payoff段为0，说明罚单已交：createprocedureproc_payoff(instuidint,inbookidint)beginupdateticketsetpayoff=0whereticket.stu_id=stuidandticket.book_id=bookid;select‘succeed’;end交罚单，调用proc_payoff:callproc_payoff(1,2);交罚单成功，payoff=0；2、功能实现按各功能模块进展描述。要求：画出图并给出实现代码。创立学生统一账户,账户名：student_account，并且授予权限：sql：createuser'student_account''localhost';grantinsert,selectonstudentto'student_account''localhost';grantselectonbookto'student_account''localhost';grantinsert,selectonborrowto'student_account''localhost';grantinsert,selectonreturn_tableto'student_account''localhost';grantselectonticketto'student_account''localhost';创立管理员统一账户，账户名：manager_account,并且授予全部权限：sql：createuser'manager_account''localhost'identifiedby'123';grantallonlibrary_managementto'manager_account''localhost';查询图书信息按书名查找：select*frombookwherebook_name=‘sql’;按作者查找：select*frombookwherebook_author=‘author’;借书功能:proc_borrow(instu_idint,inbook_idint,inborrow_datedatetime)如果要接的书还在架上，并且学生的诚信级为1，那么可以借书callproc_borrow(1,1,now());命令行操作：表borrow：视图stu_borrow:表book：还书功能:proc_return(instu_idint,inbook_idint,inreturn_datedatetime)callproc_return(1,1,now());命令行操作：表return_table:表borrow：表book：交罚单功能:proc_payoff(instuidint,inbookidint)callproc_payoff(1,1);实验操作和结果见上节：“6.交罚单存储过程“管理员添加图书：insertintobookvalues(…);操作与结果：管理员删除图书：deletefrombookwhere(condition);管理员注销学生信息：deletefromstudentwhere(condition);管理员恢复学生的诚信级：updatestudentsetstu_integrity=1where(condition);学生借书-归还流程图：管理员管理流程图：数据库设计结果：五、实习体会自己写教育之通病是教用脑的人不用手，不教用手的人用脑，所以一无所能。教育革命的对策是手脑联盟，结果是手与脑的力量都可以大到不可思议。