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

圖書管理系統(tǒng)數(shù)據(jù)庫設計MYSQL實現(xiàn)Documentnumber【AA80KGB-AA98YT-AAT8CB-2A6UT-A18GG】圖書管理系統(tǒng)數(shù)據(jù)庫設計一、系統(tǒng)概述1、系統(tǒng)簡介圖書管理是每個圖書館都需要進行的工作。一個設計良好的圖書管理系統(tǒng)數(shù)據(jù)庫能夠給圖書管理帶來很大的便利。2、需求分析圖書管理系統(tǒng)的需求定義為：息。時更新學生個人的借閱信息。學生借閱圖書之前需要將自己的個人信息注冊，登陸時對照學生信息。學生直接歸還圖書，根據(jù)圖書編碼修改借閱信息管理員登陸管理系統(tǒng)后，可以修改圖書信息，增加或者刪除圖書信息管理員可以注銷學生信息。通過需求定義，畫出圖書管理系統(tǒng)的數(shù)據(jù)流圖：數(shù)據(jù)流圖二、系統(tǒng)功能設計系統(tǒng)功能模塊圖：1E-R總體E-R圖：E-RE-RE-R2、設計表給出設計的表名、結構以及表上設計的完整性約束。student：列名數(shù)據(jù)類型是否為空/性質說明stu_idintnotnull/PK標明學生唯一學號stu_namevarcharnotnull學生姓名stu_sexvarcharnotnull學生性別stu_ageintnotnull學生年齡stu_provarcharnotnull學生專業(yè)stu_gradevarcharnotnull學生年級stu_integrityintnot學生誠信級null/default=1book:列名數(shù)據(jù)類型是否為空/性質說明book_idintnotnull/PK唯一書籍序號book_namevarcharnotnull書籍名稱book_authorvarcharnotnull書籍作者book_pubvarcharnotnull書籍出版社book_numintnotnull書籍是否在架上book_sortvarcharnotnull書籍分類book_recorddatatimenull書籍登記日期book_sort:列名數(shù)據(jù)類型是否為空/性質說明sort_idvarcharnotnull/PK類型編號sort_namevarcharnotnull類型名稱borrow:存儲學生的借書信息列名數(shù)據(jù)類型是否為空/性質說明student_idvarcharnotnull/PK學生編號book_idvarcharnotnull/PK書籍編號borrow_datedatatimenull借書時間expect_return_datedatetimenull預期歸還時間return_table:存儲學生的歸還信息列名數(shù)據(jù)類型是否為空/性質說明student_idvarcharnotnull/PK學生編號book_idvarcharnotnull/PK書籍編號borrow_datedatetimenull借書時間return_datedatatimenull實際還書時間ticket:存儲學生的罰單信息列名數(shù)據(jù)類型是否為空/性質說明student_idvarcharnotnull/PK學生編號book_idvarcharnotnull/PK書籍編號over_dateintnull超期天數(shù)ticket_feefloatnull處罰金額manager:列名數(shù)據(jù)類型是否為空/性質說明manager_idvarcharnotnull/PK管理員編號manager_namevarcharnotnull管理員姓名manager_agevarcharnotnull管理員年齡manager_phonevarcharnotnull管理員電話3、設計索引給出在各表上建立的索引以及使用的語句。student：1.為stu_id創(chuàng)建索引，升序排序sql:createindexindex_idonstudent(stu_idasc);2.為stu_name創(chuàng)建索引，并且降序排序sql:altertablestudentaddindexindex_name(stu_name,desc);插入索引操作和結果如下所示：mysql>createindexindex_idonstudent(stu_idasc);QueryOK,0rowsaffectedRecords:0 Duplicates:0 Warnings:0mysql>altertablestudentaddindexindex_name(stu_namedesc);QueryOK,0rowsaffectedRecords:0 Duplicates:0Warnings:0mysql>book:book_idsql:createindexindex_bidonbook(book_id);book_recordsql:createindexindex_brecordonbook(book_record);插入索引的操作和結果如下所示：mysql>createindexindex_bidonbook(book_id);QueryOK,0rowsaffectedRecords:0 Duplicates:0 Warnings:0mysql>createindexindex_brecordonbook(book_record);QueryOK,0rowsaffectedRecords:0 Duplicates:0 Warnings:borrow:1stu_idbook_idsql:createindexindex_sid_bidonborrow(stu_idasc,book_idasc);插入索引的操作和結果如下所示：mysql>createindexindex_sid_bidonborrow(stu_idasc,book_idasc);QueryOK,0rowsaffectedRecords:0 Duplicates:0 Warnings:return_table:1stu_idbook_idsql:createindexindex_sid_bidonreturn_table(stu_idasc,book_idasc);插入索引的操作和結果如下所示：mysql>createindexindex_sid_bid_ronreturn_table(stu_idasc,book_idasc);QueryOK,0rowsaffectedRecords:0 Duplicates:0 Warnings:ticket:1.stu_idbook_idsql:createindexindex_sid_bidonticket(stu_idasc,book_idasc);插入索引的操作和結果如下所示：mysql>createindexindex_sid_bidonticket(stu_idasc,book_idasc);QueryOK,0rowsaffectedRecords:0 Duplicates:0 Warnings:manager:1.為manager_id創(chuàng)建索引：sql:createindexindex_midonmanager(manager_id);插入索引的操作和結果如下所示：mysql>createindexindex_midonmanager(manager_id);QueryOK,0rowsaffectedRecords:0 Duplicates:0 Warnings:4、設計視圖給出在各表上建立的視圖以及使用的語句。student(csstu_cs：sql:createviewstu_csasselect*fromwherepro=‘cs’;操作和結果：mysql> createviewstu_csselect*fromstudentwherestu_pro='cs';QueryOK,0rowsaffectedstudent,borrowbookstu_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,0rowsaffected1cs_book：sql:createviewcs_bookasselect*frombookwherebook.book_sortinfrombook_sortwheresort_id=1);操作和結果顯示：mysql> createviewcs_bookselect*frombookwherebook.book_sortin(selectbook_sort.sort_namefrombook_sortwheresort_id=1);QueryOK,0rowsaffectedstu_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、設計觸發(fā)器給出在各表上建立的觸發(fā)器以及使用的語句。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,0rowsaffectedborrowbook_id=11borrow在borrow中插入這條記錄后，book_id=1的圖書，不在架上，為0：trigger_return,還書成功后，對應的書籍book_numsql:createtriggertrigger_returnafterinsertonreturn_tableforeachrowbeginupdatebooksetbook_num=book_num+1wherebook_id=new.book_id;endreturn_table此時圖書歸還架上：定義定時器（事件）eventJob，每天自動觸發(fā)一次，掃描視圖stu_borrow，若發(fā)現(xiàn)當前有預期歸還時間小于當前時間，則判斷為超期，生成處罰記錄，這proc_gen_ticket：sql:createeventifnotexistseventJobonscheduleevery1DAY /*每天觸發(fā)oncompletionPRESERVEdocallproc_gen_ticket(getdate());/*調用存儲過程*/setglobalevent_scheduler=1;altereventeventJoboncompletionpreserveenable;/*開啟定時器*/操作和結果顯示：11，stu_borrow127127trigger_credit，300，下次不允許借書：sql:createtriggertrigger_creditafterinsertonticketforeachrowbeginif(selectcount(*)fromticketwherestu_id=new.stu_id)>30thenupdatestudentsetstu_integrity=0wherestu_id=new.stu_id;end

endif;ticket3，30測試：學生1超過3次超期歸還圖書后，產(chǎn)生了4條罰單：trigger_credit1四、應用程序設計與編碼實現(xiàn)1要求給出功能描述和代碼。proc_gen_ticket：ticketeventJob中調用：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操作和結果顯示：11，stu_borrow127127sql: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);end設計管理員注冊信息存儲過程：ma_registersql:createprocedurema_register(inma_idint,inma_namevarchar(20),inma_ageint,inma_phoneint)BEGINinsertintomanagervalues(ma_id,ma_name,ma_age,ma_phone);END借書過程的實現(xiàn):設計存儲函數(shù)，func_get_credit，返回學生的誠信級：createfunctionfunc_get_credit(stu_idint)returnsintbeginreturn(selectstu_integrityfromstudentwherestudent.stu_id=stu_id);end設計存儲函數(shù)，func_get_booknum，返回書籍是否在架上：createfunctionfunc_get_booknum(book_idint)returnsbeginreturn(selectbook_numfrombookwherebook.book_id=book_id);endproc_borrowfunc_get_creditborrrowcreateprocedureproc_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紀錄為空：執(zhí)行函數(shù)，學生1借圖書2：callproc_borrow(1,2,now());學生1的誠信級為0：借書失敗：1此時借書成功：proc_return：ticketreturn_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_tablesetborrowdate=(selectborrow_datefromborrowwhereborrow.stu_id=stu_idandborrow.book_id=book_id);book_id;end

insertintoreturn_tablevalues(stu_id,book_id,borrowdate,return_date);deletefromborrowwhereborrow.stu_id=stu_idandborrow.book_id=endif;實驗操作與結果顯示：學生1借了圖書2：超期產(chǎn)生了罰單，沒有交罰單，payoff=1：此時調用還書過程：callproc_return(1,2,now());提示交罰單：交罰單，調用proc_payoff:callproc_payoff(1,2);交罰單成功，payoff=0；此時再次調用還書過程：callproc_return(1,2,now());還書成功，在return_table生成了還書紀錄：交罰單存儲過程：修改罰單中payoff段為0，表明罰單已交：createprocedureproc_payoff(instuidint,inbookidint)beginend

updateticketsetpayoff=0whereticket.stu_id=stuidandticket.book_id=bookid;select‘succeed’;交罰單，調用proc_payoff:callproc_payoff(1,2);交罰單成功，payoff=0；2、功能實現(xiàn)按各功能模塊進行描述。要求：畫出流程圖并給出實現(xiàn)代碼。創(chuàng)建學生統(tǒng)一賬戶,賬戶名：student_account，sql：createuser'student_account'@'localhost';grantinsert,selectonstudentto'student_account'@'localhost';grantselectonbookto'student_account'@'localhost';grantinsert,selectonborrowto'stu