圖書管理系統(tǒng)數(shù)據(jù)庫設(shè)計-MYSQL實現(xiàn)圖書管理系統(tǒng)數(shù)據(jù)庫設(shè)計一、系統(tǒng)概述1、系統(tǒng)簡介圖書管理是每個圖書館都需要進行的工作。一個設(shè)計良好的圖書管理系統(tǒng)數(shù)據(jù)庫能夠給圖書管理帶來很大的便利。2、需求分析圖書管理系統(tǒng)的需求定義為：1.學生可以直接通過借閱終端來查閱書籍信息，同時也可以查閱自己的借閱信息。2.當學生需要借閱書籍時，通過賬號密碼登陸借閱系統(tǒng)，借閱系統(tǒng)處理學生的借閱，同時修改圖書館保存的圖書信息，修改被借閱的書籍是否還有剩余，同時更新學生個人的借閱信息。3.學生借閱圖書之前需要將自己的個人信息注冊，登陸時對照學生信息。4.學生直接歸還圖書，根據(jù)圖書編碼修改借閱信息5.管理員登陸管理系統(tǒng)后，可以修改圖書信息，增加或者刪除圖書信息6.管理員可以注銷學生信息。通過需求定義，畫出圖書管理系統(tǒng)的數(shù)據(jù)流圖：數(shù)據(jù)流圖二、系統(tǒng)功能設(shè)計畫出系統(tǒng)功能模塊圖并用文字對各功能模塊進行詳細介紹。系統(tǒng)功能模塊圖：三、數(shù)據(jù)庫設(shè)計方案圖表1、系統(tǒng)E-R模型總體E-R圖：精細化的局部E-R圖：

學生借閱-歸還E-R圖：管理員E-R圖：2、設(shè)計表給出設(shè)計的表名、結(jié)構(gòu)以及表上設(shè)計的完整性約束。student：列名數(shù)據(jù)類型是否為空/性質(zhì)說明stu_idintnotnull/PK標明學生唯一學號stu_namevarcharnotnull學生姓名stu_sexvarcharnotnull學生性別stu_ageintnotnull學生年齡stu_provarcharnotnull學生專業(yè)stu_gradevarcharnotnull學生年級stu_integrityintnotnull/default=1學生誠信級book:列名數(shù)據(jù)類型是否為空/性質(zhì)說明book_idintnotnull/PK唯一書籍序號book_namevarcharnotnull書籍名稱book_authorvarcharnotnull書籍作者book_pubvarcharnotnull書籍出版社book_numintnotnull書籍是否在架上book_sortvarcharnotnull書籍分類book_recorddatatimenull書籍登記日期book_sort:列名數(shù)據(jù)類型是否為空/性質(zhì)說明sort_idvarcharnotnull/PK類型編號sort_namevarcharnotnull類型名稱borrow:存儲學生的借書信息列名數(shù)據(jù)類型是否為空/性質(zhì)說明student_idvarcharnotnull/PK學生編號book_idvarcharnotnull/PK書籍編號borrow_datedatatimenull借書時間expect_return_datedatetimenull預(yù)期歸還時間return_table:存儲學生的歸還信息列名數(shù)據(jù)類型是否為空/性質(zhì)說明student_idvarcharnotnull/PK學生編號book_idvarcharnotnull/PK書籍編號borrow_datedatetimenull借書時間return_datedatatimenull實際還書時間ticket:存儲學生的罰單信息列名數(shù)據(jù)類型是否為空/性質(zhì)說明student_idvarcharnotnull/PK學生編號book_idvarcharnotnull/PK書籍編號over_dateintnull超期天數(shù)ticket_feefloatnull處罰金額manager:列名數(shù)據(jù)類型是否為空/性質(zhì)說明manager_idvarcharnotnull/PK管理員編號manager_namevarcharnotnull管理員姓名manager_agevarcharnotnull管理員年齡manager_phonevarcharnotnull管理員電話3、設(shè)計索引給出在各表上建立的索引以及使用的語句。student：1.為stu_id創(chuàng)建索引，升序排序sql:createindexindex_idonstudent(stu_idasc);2.為stu_name創(chuàng)建索引，并且降序排序sql:altertablestudentaddindexindex_name(stu_name,desc);插入索引操作和結(jié)果如下所示：mysql>createindexindex_idonstudent(stu_idasc);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:0mysql>altertablestudentaddindexindex_name(stu_namedesc);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:0mysql>book:1.為book_id創(chuàng)建索引，升序排列sql:createindexindex_bidonbook(book_id);2.為book_record創(chuàng)建索引，以便方便查詢圖書的登記日期信息，升序：sql:createindexindex_brecordonbook(book_record);插入索引的操作和結(jié)果如下所示：mysql>createindexindex_bidonbook(book_id);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:0mysql>createindexindex_brecordonbook(book_record);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:0borrow:1.為stu_id和book_id創(chuàng)建多列索引：sql:createindexindex_sid_bidonborrow(stu_idasc,book_idasc);插入索引的操作和結(jié)果如下所示：mysql>createindexindex_sid_bidonborrow(stu_idasc,book_idasc);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:0return_table:1.為stu_id和book_id創(chuàng)建多列索引：sql:createindexindex_sid_bidonreturn_table(stu_idasc,book_idasc);插入索引的操作和結(jié)果如下所示：mysql>createindexindex_sid_bid_ronreturn_table(stu_idasc,book_idasc);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:0ticket:1.為stu_id和book_id創(chuàng)建多列索引：sql:createindexindex_sid_bidonticket(stu_idasc,book_idasc);插入索引的操作和結(jié)果如下所示：mysql>createindexindex_sid_bidonticket(stu_idasc,book_idasc);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:0manager:1.為manager_id創(chuàng)建索引：sql:createindexindex_midonmanager(manager_id);插入索引的操作和結(jié)果如下所示：mysql>createindexindex_midonmanager(manager_id);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:04、設(shè)計視圖給出在各表上建立的視圖以及使用的語句。1.在表student上創(chuàng)建計算機專業(yè)(cs)學生的視圖stu_cs：sql:createviewstu_csasselect*fromstudentwherepro=‘cs’;操作和結(jié)果：mysql>createviewstu_csasselect*fromstudentwherestu_pro='cs';QueryOK,0rowsaffected2.在表student,borrow和book上創(chuàng)建借書者的全面信息視圖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;操作和結(jié)果：

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.創(chuàng)建類別1的所有圖書的視圖cs_book：sql:createviewcs_bookasselect*frombookwherebook.book_sortin(selectboofrombook_sortwheresort_id=1);操作和結(jié)果顯示：mysql>createviewcs_bookasselect*frombookwherebook.book_sortin(selectbook_sort.sort_namefrombook_sortwheresort_id=1);QueryOK,0rowsaffected4.創(chuàng)建個人所有借書歸還紀錄視圖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、設(shè)計觸發(fā)器給出在各表上建立的觸發(fā)器以及使用的語句。1.設(shè)計觸發(fā)器borrow,當某學生借書成功后，圖書表相應(yīng)的圖書不在架上，變?yōu)?：sql:createtriggerborrowafterinsertonborrowforeachrowbeginupdatebooksetbook_num=book_num–1wherebook_id=new.book_id;end操作與結(jié)果顯示：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.設(shè)計觸發(fā)器trigger_return,還書成功后，對應(yīng)的書籍book_num變?yōu)?：sql:createtriggertrigger_returnafterinsertonreturn_tableforeachrowbeginupdatebooksetbook_num=book_num+1wherebook_id=new.book_id;end還書時在return_table插入表項：此時圖書歸還架上：3.定義定時器（事件）eventJob，每天自動觸發(fā)一次，掃描視圖stu_borrow，若發(fā)現(xiàn)當前有預(yù)期歸還時間小于當前時間，則判斷為超期，生成處罰記錄，這個定時器將每天定時觸發(fā)存儲過程proc_gen_ticket：sql:createeventifnotexistseventJobonscheduleevery1DAY/*每天觸發(fā)*/oncompletionPRESERVEdocallproc_gen_ticket(getdate());/*調(diào)用存儲過程*/setglobalevent_scheduler=1;altereventeventJoboncompletionpreserveenable;/*開啟定時器*/操作和結(jié)果顯示：1).學生1借了圖書1，生成借書記錄stu_borrow視圖，如下：2).當他在1月27日前還書時，沒有生成罰單：3).當他在1月27日后還書時，生成罰單：4.設(shè)計觸發(fā)器trigger_credit，若處罰記錄超過30條，則將這個學生的誠信級設(shè)置為0，下次不允許借書：sql:createtriggertrigger_creditafterinsertonticketforeachrowbeginif(selectcount(*)fromticketwherestu_id=new.stu_id)>30thenupdatestudentsetstu_integrity=0wherestu_id=new.stu_id;endif;end操作和結(jié)果顯示，測試時選擇插入ticket項大于3，因為30太大了，不容易測試：學生1超過3次超期歸還圖書后，產(chǎn)生了4條罰單：此時觸動觸發(fā)器trigger_credit，將學生1的誠信級設(shè)置為0：

四、應(yīng)用程序設(shè)計與編碼實現(xiàn)1、系統(tǒng)實現(xiàn)中存儲函數(shù)和存儲過程的設(shè)計要求給出功能描述和代碼。1.設(shè)計存儲過程，產(chǎn)生罰單proc_gen_ticket：當日期超過預(yù)定歸還日期時，產(chǎn)生罰單，并將記錄寫入表ticket中，這個存儲過程在定時器eventJob中調(diào)用：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操作和結(jié)果顯示：1).學生1借了圖書1，生成借書記錄stu_borrow視圖，如下：2).當他在1月27日前還書時，沒有生成罰單：3).當他在1月27日后還書時，生成罰單：2.設(shè)計學生注冊信息存儲過程：學生注冊信息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.設(shè)計管理員注冊信息存儲過程：ma_registersql:createprocedurema_register(inma_idint,inma_namevarchar(20),inma_ageint,inma_phoneint)BEGINinsertintomanagervalues(ma_id,ma_name,ma_age,ma_phone);END4.借書過程的實現(xiàn):設(shè)計存儲函數(shù)，func_get_credit，返回學生的誠信級：createfunctionfunc_get_credit(stu_idint)returnsintbeginreturn(selectstu_integrityfromstudentwherestudent.stu_id=stu_id);end設(shè)計存儲函數(shù)，func_get_booknum，返回書籍是否在架上：createfunctionfunc_get_booknum(book_idint)returnsintbeginreturn(selectbook_numfrombookwherebook.book_id=book_id);end設(shè)計存儲過程proc_borrow，調(diào)用func_get_credit和func_get_booknum，判斷這個學生誠信度和書籍是否在架上，若為真，則借書成功，在borrrow表中插入紀錄；否則提示失?。篶reateprocedureproc_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實驗操作與結(jié)果顯示：borrow紀錄為空： 執(zhí)行函數(shù)，學生1借圖書2：callproc_borrow(1,2,now());學生1的誠信級為0： 借書失?。?修改學生1誠信級為1： 此時借書成功： 5.還書存儲過程proc_return：當還書時，查看是否書是否超期，即查詢ticket表項，當發(fā)現(xiàn)超期，提示交罰單后再次還書，如沒有超期，則紀錄歸還項目到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實驗操作與結(jié)果顯示：學生1借了圖書2：超期產(chǎn)生了罰單，沒有交罰單，payoff=1：此時調(diào)用還書過程：callproc_return(1,2,now());提示交罰單：交罰單，調(diào)用proc_payoff:callproc_payoff(1,2);交罰單成功，payoff=0；此時再次調(diào)用還書過程：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交罰單，調(diào)用proc_payoff:callproc_payoff(1,2);交罰單成功，payoff=0；2、功能實現(xiàn)按各功能模塊進行描述。要求：畫出流程圖并給出實現(xiàn)代碼。創(chuàng)建學生統(tǒng)一賬戶,賬戶名：student_account，并且授予權(quán)限：sql：createuser'student_account'@'localhost';grantinsert,selectonstudentto'student_account'@'localhost';grantselectonbookto'student_account'@'localhost';grantinse