圖書管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)MYSQL實(shí)現(xiàn)

1、圖書管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)一、系統(tǒng)概述1、系統(tǒng)簡介圖書管理是每個圖書館都需要進(jìn)行的工作。一個設(shè)計(jì)良好的圖書管理系統(tǒng)數(shù)據(jù)庫能夠給圖書管理帶來很大的便利。2、需求分析圖書管理系統(tǒng)的需求定義為：1 .學(xué)生可以直接通過借閱終端來查閱書籍信息，同時也可以查閱自己的借閱信息。2 .當(dāng)學(xué)生需要借閱書籍時，通過賬號密碼登陸借閱系統(tǒng)，借閱系統(tǒng)處理學(xué)生的借閱，同時修改圖書館保存的圖書信息，修改被借閱的書籍是否還有剩余，同時更新學(xué)生個人的借閱信息。3 .學(xué)生借閱圖書之前需要將自己的個人信息注冊，登陸時對照學(xué)生信息。4 .學(xué)生直接歸還圖書，根據(jù)圖書編碼修改借閱信息5 .管理員登陸管理系統(tǒng)后，可以修改圖書信息，增加或者刪除

2、圖書信息6 .管理員可以注銷學(xué)生信息。通過需求定義，畫出圖書管理系統(tǒng)的數(shù)據(jù)流圖：數(shù)據(jù)流圖二、系統(tǒng)功能設(shè)計(jì)畫出系統(tǒng)功能模塊圖并用文字對各功能模塊進(jìn)行詳細(xì)介紹。系統(tǒng)功能模塊圖：三、數(shù)據(jù)庫設(shè)計(jì)方案圖表1 、系統(tǒng)E-R模型總體E-R圖：精細(xì)化白局部E-R圖:學(xué)生借閱-歸還E-R圖：管理員E-R圖：2、設(shè)計(jì)表給出設(shè)計(jì)的表名、結(jié)構(gòu)以及表上設(shè)計(jì)的完整性約束。student:列名數(shù)據(jù)類型是否為空/性質(zhì)說明stu_idintnotnull/PK標(biāo)明學(xué)生唯一學(xué)號stu_namevarcharnotnull學(xué)生姓名stu_sexvarcharnotnull學(xué)生性別stu_ageintnotnull學(xué)生年齡stu_p

3、rovarcharnotnull學(xué)生專業(yè)stu_gradevarcharnotnull學(xué)生年級stu_integrityintnotnull/default=1學(xué)生誠信級book:列名數(shù)據(jù)類型是否為空/性質(zhì)說明book_idintnotnull/PK唯一書籍序號book_namevarcharnotnull書籍名稱book_authorvarcharnotnull書籍作者book_pubvarcharnotnull書籍出版社book_numintnotnull書籍是否在架上book_sortvarcharnotnull書籍分類book_recorddatatimenull書籍登記日期book_

4、sort:列名數(shù)據(jù)類型是否為空/性質(zhì)說明sort_idvarcharnotnull/PK類型編號sort_namevarcharnotnull類型名稱borrow:存儲學(xué)生的借書信息列名數(shù)據(jù)類型是否為空/性質(zhì)說明student_idvarcharnotnull/PK學(xué)生編號book_idvarcharnotnull/PK書籍編號borrow_datedatatimenull借書時間expect_return_datedatetimenull預(yù)期歸還時間return_table:存儲學(xué)生的歸還信息列名數(shù)據(jù)類型是否為空/性質(zhì)說明student_idvarcharnotnull/PK學(xué)生編號book

5、_idvarcharnotnull/PK書籍編號borrow_datedatetimenull借書時間return_datedatatimenull實(shí)際還書時間ticket:存儲學(xué)生的罰單信息列名數(shù)據(jù)類型是否為空/性質(zhì)說明student_idvarcharnotnull/PK學(xué)生編號book_idvarcharnotnull/PK書籍編號over_dateintnull超期天數(shù)ticket_feefloatnull處罰金額manager:列名數(shù)據(jù)類型是否為空/性質(zhì)說明manager_idvarcharnotnull/PK管理員編號manager_namevarcharnotnull管理員姓名m

6、anager_agevarcharnotnull管理員年齡manager_phonevarcharnotnull管理員電話3、設(shè)計(jì)索引給出在各表上建立的索引以及使用的語句。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);Qu

7、eryOK,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_breco

8、rdonbook(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_bidon

9、borrow(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>createindexind

10、ex_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:0

11、Duplicates:0Warnings:0manager:1.為manager_id創(chuàng)建索引：sql:createindexindex_midonmanager(manager_id);插入索引的操作和結(jié)果如下所示：mysql>createindexindex_midonmanager(manager_id);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:04、設(shè)計(jì)視圖給出在各表上建立的視圖以及使用的語句。1. 在表student上創(chuàng)建計(jì)算機(jī)專業(yè)(cs)學(xué)生的視圖stu_cs：sql:createviewstu_csasselec

12、t*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)exp

13、ect_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

14、=borrow.stu_idandbook.book_id=borrow.book_id;QueryOK,0rowsaffected3. 創(chuàng)建類別1的所有圖書的視圖cs_book：sql:createviewcs_bookasselect*frombookwherebook.book_sortinfrombook_sortwheresort_id=1);操作和結(jié)果顯示：mysql>createviewcs_bookasselect*frombookwherebook.book_sortin(selectbook_sort.sort_namefrombook_sortwheresort_i

15、d=1);QueryOK,0rowsaffected4. 創(chuàng)建個人所有借書歸還紀(jì)錄視圖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_

16、table.book_id;5、設(shè)計(jì)觸發(fā)器給出在各表上建立的觸發(fā)器以及使用的語句。1. 設(shè)計(jì)觸發(fā)器borrow,當(dāng)某學(xué)生借書成功后，圖書表相應(yīng)的圖書不在架上，變?yōu)?：sql:createtriggerborrowafterinsertonborrowforeachrowbeginupdatebooksetbook_num=book_numTwherebook_id=new.book_id;end操作與結(jié)果顯示：mysql>delimiter$mysql>createtriggertrigger_borrow- >afterinsertonborrow- >foreach

17、row- >begin- >updatebooksetbook_num=book_num-1- >wherebook_id=new.book_id;- >end->$QueryOK,0rowsaffected在插入表borrow之前，book_id=1的圖書還在架上，為1：學(xué)生1借了這本書后，在borrow中插入了一條記錄：在borrow中插入這條記錄后，book_id=1的圖書，不在架上，為0：2 .設(shè)計(jì)觸發(fā)器trigger_return,還書成功后，對應(yīng)的書籍book_num變?yōu)?：sql:createtriggertrigger_returnafterins

18、ertonreturn_tableforeachrowbeginupdatebooksetbook_num=book_num+1wherebook_id=new.book_id;end還書時在return_table插入表項(xiàng)：此時圖書歸還架上：3 .定義定時器（事件）eventJob，每天自動觸發(fā)一次，掃描視圖stu_borrow，若發(fā)現(xiàn)當(dāng)前有預(yù)期歸還時間小于當(dāng)前時間，則判斷為超期，生成處罰記錄，這個定時器將每天定時觸發(fā)存儲過程proc_gen_ticket：sql:createeventifnotexistseventJobonscheduleevery1DAY/*每天觸發(fā)*/oncompl

19、etionPRESERVEdocallproc_gen_ticket(getdate();/*調(diào)用存儲過程*/setglobalevent_scheduler=1;altereventeventJoboncompletionpreserveenable;/*開啟定時器*/操作和結(jié)果顯示：1) .學(xué)生1借了圖書1，生成借書記錄stu_borrow視圖，如下：2) .當(dāng)他在1月27日前還書時，沒有生成罰單：3) .當(dāng)他在1月27日后還書時，生成罰單：4.設(shè)計(jì)觸發(fā)器trigger_credit，若處罰記錄超過30條，則將這個學(xué)生的誠信級設(shè)置為0，下次不允許借書：sql:createtriggertr

20、igger_creditafterinsertonticketforeachrowbeginif(selectcount(*)fromticketwherestu_id=new.stu_id)>30thenupdatestudentsetstu_integrity=0wherestu_id=new.stu_id;endif;end操作和結(jié)果顯示，測試時選擇插入ticket項(xiàng)大于3，因?yàn)?0太大了，不容易測試：學(xué)生1超過3次超期歸還圖書后，產(chǎn)生了4條罰單：此時觸動觸發(fā)器trigger_credit，將學(xué)生1的誠信級設(shè)置為0：四、應(yīng)用程序設(shè)計(jì)與編碼實(shí)現(xiàn)1、系統(tǒng)實(shí)現(xiàn)中存儲函數(shù)和存儲過程的設(shè)計(jì)要

21、求給出功能描述和代碼。1. 設(shè)計(jì)存儲過程，產(chǎn)生罰單proc_gen_ticket：當(dāng)日期超過預(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,s

22、tu_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) .學(xué)生1借了圖書1，生成借書記錄stu_borrow視圖，如下：2) .當(dāng)他在1月27日前還書時，沒有生成罰單：3) .當(dāng)他在1月27日后還書時，生成罰單：2. 設(shè)計(jì)學(xué)生注冊信息存儲過程：學(xué)生注冊信息stu_registersql:createprocedurestu_register

23、(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è)計(jì)管理員注冊信息存儲過程：ma_registersql:createprocedurema_regi

24、ster(inma_idint,inma_namevarchar(20),inma_ageint,inma_phoneint)BEGINinsertintomanagervalues(ma_id,ma_name,ma_age,ma_phone);END4. 借書過程的實(shí)現(xiàn):1) 設(shè)計(jì)存儲函數(shù)，func_get_credit，返回學(xué)生的誠信級：createfunctionfunc_get_credit(stu_idint)returnsintbeginreturn(selectstu_integrityfromstudentwherestudent.stu_id=stu_id);end2) 設(shè)計(jì)

25、存儲函數(shù)，func_get_booknum，返回書籍是否在架上：createfunctionfunc_get_booknum(book_idint)returnsintbeginreturn(selectbook_numfrombookwherebook.book_id=book_id);end3) 設(shè)計(jì)存儲過程proc_borrow，調(diào)用func_get_credit和func_get_booknum，判斷這個學(xué)生誠信度和書籍是否在架上，若為真，則借書成功，在borrrow表中插入紀(jì)錄；否則提示失?。篶reateprocedureproc_borrow(instu_idint,inbook_

26、idint,inborrow_datedatetime)beginiffunc_get_credit(stu_id)=1andfunc_get_booknum(book_id)=1theninsertintoborrowvalues(stu_id,book_id,borrow_date);elseselect'failedtoborrow'endif;end實(shí)驗(yàn)操作與結(jié)果顯示：borrow紀(jì)錄為空：執(zhí)行函數(shù)，學(xué)生1借圖書2：callproc_borrow(1,2,now();學(xué)生 1 的誠信級為0：借書失?。盒薷膶W(xué)生1誠信級為1：此時借書成功：5. 還書存儲過程proc_ret

27、urn：當(dāng)還書時，查看是否書是否超期，即查詢ticket表項(xiàng)，當(dāng)發(fā)現(xiàn)超期，提示交罰單后再次還書，如沒有超期，則紀(jì)錄歸還項(xiàng)目到return_table中，并且刪除借書紀(jì)錄(以免還書后定時器仍然掃描這個紀(jì)錄)：sql：createprocedureproc_return(instu_idint,inbook_idint,inreturn_datedatetime)beginDECLAREborrowdatedatetime;if(selectpayofffromticketwhereticket.stu_id=stu_idandticket.book_id=book_id)=1then/*判斷是否

28、交了罰單，1表示沒有交*/select'pleasepayofftheticket'else/*紀(jì)錄歸還項(xiàng)目到return_table中，并且刪除借書紀(jì)錄*/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.

29、book_id=book_id;endif;end實(shí)驗(yàn)操作與結(jié)果顯示：學(xué)生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生成了還書紀(jì)錄：6. 交罰單存儲過程：修改罰單中payoff段為0，表明罰單已交：createprocedureproc_payoff(instuidint,inbo

30、okidint)beginupdateticketsetpayoff=0whereticket.stu_id=stuidandticket.book_id=bookid;selectsucceedend交罰單，調(diào)用proc_payoff:callproc_payoff(1,2);交罰單成功，payoff=0；2、功能實(shí)現(xiàn)按各功能模塊進(jìn)行描述。要求：畫出流程圖并給出實(shí)現(xiàn)代碼。創(chuàng)建學(xué)生統(tǒng)一賬戶,賬戶名：student_account，并且授予權(quán)限：sql：createuser'student_account''localhost'grantinsert,selectonstudentto'student_account''localhost'grantselectonbookto'student_account''localhost'grantinsert,selectonborrowto'student_acco