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

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

2、書(shū)信息6. 管理員可以注銷(xiāo)學(xué)生信息。通過(guò)需求定義，畫(huà)出圖書(shū)管理系統(tǒng)的數(shù)據(jù)流圖：17數(shù)據(jù)流圖管理員信息圖書(shū)信息學(xué)生信息二、系統(tǒng)功能設(shè)計(jì)畫(huà)出系統(tǒng)功能模塊圖并用文字對(duì)各功能模塊進(jìn)行詳細(xì)介紹。系統(tǒng)功能模塊圖：三、數(shù)據(jù)庫(kù)設(shè)計(jì)方案圖表1、系統(tǒng)E-R模型總體E-R圖：精細(xì)化的局部E-R圖：學(xué)生借閱-歸還E-R圖：學(xué)生ID年級(jí)年齡性別誠(chéng)信級(jí)學(xué)生專(zhuān)業(yè)學(xué)生ID歸還時(shí)間圖書(shū)歸還表借閱歸還圖書(shū)借閱表圖書(shū)ID處罰表圖書(shū)ID學(xué)生ID圖書(shū)作者圖書(shū)ID書(shū)名數(shù)量出版社分類(lèi)-匚（處罰金額超期）登記日期管理員E-R圖：2、設(shè)計(jì)表給出設(shè)計(jì)的表名、結(jié)構(gòu)以及表上設(shè)計(jì)的完整性約束。student：列名數(shù)據(jù)類(lèi)型是否為空/性質(zhì)說(shuō)明studin

3、tnotnull/PK標(biāo)明學(xué)生唯一學(xué)號(hào)stunamevarcharnotnull學(xué)生姓名stusexvarcharnotnull學(xué)生性別stuageintnotnull學(xué)生年齡stuprovarcharnotnull學(xué)生專(zhuān)業(yè)stugradevarcharnotnull學(xué)生年級(jí)stuntegrityintnotnull/default=1學(xué)生誠(chéng)信級(jí)book:列名數(shù)據(jù)類(lèi)型是否為空/性質(zhì)說(shuō)明bookidintnotnull/PK唯一書(shū)籍序號(hào)booknamevarcharnotnull書(shū)籍名稱(chēng)bookauthorvarcharnotnull書(shū)籍作者bookpubvarcharnotnull書(shū)籍出版社b

4、ooknumintnotnull書(shū)籍是否在架上booksortvarcharnotnull書(shū)籍分類(lèi)bookrecorddatatimenull書(shū)籍登記日期booksort:列名數(shù)據(jù)類(lèi)型是否為空/性質(zhì)說(shuō)明sortidvarcharnotnull/PK類(lèi)型編號(hào)sortnamevarcharnotnull類(lèi)型名稱(chēng)borrow:存儲(chǔ)學(xué)生的借書(shū)信息列名數(shù)據(jù)類(lèi)型是否為空/性質(zhì)說(shuō)明studentidvarcharnotnull/PK學(xué)生編號(hào)bookidvarcharnotnull/PK書(shū)籍編號(hào)borrowdatedatatimenull借書(shū)時(shí)間expectreturndatedatetimenull預(yù)期歸還

5、時(shí)間returntable:存儲(chǔ)學(xué)生的歸還信息列名數(shù)據(jù)類(lèi)型是否為空/性質(zhì)說(shuō)明studentidvarcharnotnull/PK學(xué)生編號(hào)bookidvarcharnotnull/PK書(shū)籍編號(hào)borrowdatedatetimenull借書(shū)時(shí)間returndatedatatimenull實(shí)際還書(shū)時(shí)間ticket:存儲(chǔ)學(xué)生的罰單信息列名數(shù)據(jù)類(lèi)型是否為空/性質(zhì)說(shuō)明studentidvarcharnotnull/PK學(xué)生編號(hào)bookidvarcharnotnull/PK書(shū)籍編號(hào)overdateintnull超期天數(shù)ticketfeefloatnull處罰金額manager:列名數(shù)據(jù)類(lèi)型是否為空/性質(zhì)說(shuō)

6、明manageridvarcharnotnull/PK管理員編號(hào)managernamevarcharnotnull管理員姓名manageragevarcharnotnull管理員年齡managerphonevarcharnotnull管理員電話(huà)3、設(shè)計(jì)索引給出在各表上建立的索引以及使用的語(yǔ)句。student：1為stu_id創(chuàng)建索引，升序排序sql:createindexindex_idonstudent(stu_idasc);2為stu_name創(chuàng)建索引，并且降序排序sql:altertablestudentaddindexindex_name(stu_name,desc);插入索引操作和結(jié)

7、果如下所示：mysql>createindexindex_idonstudent(stu_idasc);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:0mysql>altertablestudentaddindexindex_name(stu_namedesc);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:0mysql>欄氈旳L翅欄位索弓翹索引方法卜siiu_idNormalE-REEindex_namestu_rameNormalBfREEbook:1. 為bo

8、ok_id創(chuàng)建索引，升序排列sql:createindexindex_bidonbook(book_id);2. 為book_record創(chuàng)建索引，以便方便查詢(xún)圖書(shū)的登記日期信息，升序：sql:createindexindex_brecordonbook(book_record);插入索引的操作和結(jié)果如下所示：mysql>createindexindex_bidonbook(book_id);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:0mysql>createindexindex_brecordonbook(book_re

9、cord);QueryOK,0rowsaffected5若欄位bookidRecords:0Duplicates:0Warnings:0多K籬爰器選項(xiàng)注釋SQLW寶弓卿京引方法NormalBTREEindexbrecordbooknecDrdNormalETREEborrow:1.為stu_id和book_id創(chuàng)建多列索引：sql:createindexindex_sid_bidonborrow(stu_idasc,book_idasc);插入索引的操作和結(jié)果如下所示：mysql>createindexindex_sid_bidonborrow(stu_idasc,book_idasc)

10、;QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:0欄氈SQL預(yù)覽若欄位案引疑實(shí)引方法:ibook_idNormalBIREEindex_sid_bidstujclboolc_idNormalETREEreturn_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_i

11、dasc,book_idasc);QueryOK,0rowsaffectedticket: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:createindex

12、index_midonmanager(manager_id);插入索引的操作和結(jié)果如下所示：mysql>createindexindex_midonmanager(manager_id);QueryOK,0rowsaffectedRecords:0Duplicates:0Warnings:0欄隹盍弓選頂呂垂引摯瘞弓方法manager_idNormalETREE4、設(shè)計(jì)視圖給出在各表上建立的視圖以及使用的語(yǔ)句。1在表student上創(chuàng)建計(jì)算機(jī)專(zhuān)業(yè)(cs)學(xué)生的視圖stu_cs:sql:createviewstu_csasselect*fromstudentwherepro=cs'操

13、作和結(jié)果：mysql>createviewstu_csasselect*fromstudentwherestu_pro='cs'QueryOK,0rowsaffectedstuidstu_nainesftu_sexstu_agestu_prostujgradeMuntegrityMlstuarn20cs20142stu_bm21cs20142stucf20匚£20144Etu_df19c&20145stu_ern20C520142.在表student,borrow和book上創(chuàng)建借書(shū)者的全面信息視圖stu_borrow:sql:createviewstu

14、_borrowasselectstudent.stu_id,book.book_id,student.stu_name,book.book_name,borrow_dateadddate(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_nam

15、e,book.book_name,borrow_dateadddate(borrow_date,30)expect_return_datefromstudent,book,borrowwherestudent.stu_id=borrow.stu_idandbook.book_id=borrow.book_id;QueryOK,0rowsaffecteds.tu_idbookid&tu_nameboo-k_nameborrowdate&xpect_return_date卜1stu_acomputerneiwo2016-1-2817:072017-01-2717:07:183創(chuàng)建類(lèi)

16、別1的所有圖書(shū)的視圖cs_book:sql:createviewcs_bookasselect*frombookwherebook.book_sortin(selectbook_frombook_sortwheresort_id=1);操作和結(jié)果顯示：mysql>createviewcs_bookasselect*frombookwherebook.book_sortin(selectbook_sort.sort_namefrombook_sortwheresort_id=1);QueryOK,0rowsaffectedbookidbook_namebook_

17、authorboo-k_pubboolk_numbook_scmbook_recorclJcomputernehvork3uthor_apub_a1201&-12-2816:55:572computeroperationauthorbpubb1CS201S-12-2S16:5fi；393infcTmationse-curityauthor_cpub_a1cs2016-122815:58:374mftv/areengineeringauthor_dpub_d1C5201&12-2815:59:105c+4plu£1e-aningauthor_epub_31CE2D16-

18、12-2815!59：556cprogrammirgauthorepubs1C52016-12-2S1s36567sqlauthor_dpub_c1C501&-12-2817;00:474創(chuàng)建個(gè)人所有借書(shū)歸還紀(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,retur

19、n_tablewherestudent.stu_id=return_table.stu_idandbook.book_id=return_table.book_id;stuidstu_niamebook_idbook_nameborrow_datereturn_datestu_a1computernetwork2016-10-1617:07:182016-12-2916:56:505、設(shè)計(jì)觸發(fā)器給出在各表上建立的觸發(fā)器以及使用的語(yǔ)句。1.設(shè)計(jì)觸發(fā)器borrow,當(dāng)某學(xué)生借書(shū)成功后，圖書(shū)表相應(yīng)的圖書(shū)不在架上，變?yōu)?：sql:createtriggerborrowafterinsertonborr

20、owforeachrowbeginupdatebooksetbook_num二book_num1wherebook_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

21、之前，book_id二1的圖書(shū)還在架上，為1:book_idbaok_niarTnebaok_authorbook_pulbbook_numbDok_sortboDk_record1computern&tifvorkdu£hor_apUb_i31cs2016-12-2816:55;學(xué)生1借了這本書(shū)后，在borrow中插入了一條記錄：stuidbookidborrowdate11201B-12-2S17:07在borrow中插入這條記錄后，book_id=1的圖書(shū)，不在架上，為0：book_idbock_namebook_authorbook_pubbaok_numboolk_

22、sortbook_record1computernetworkauthor_apub_a0csZ016-12-2616:55:2設(shè)計(jì)觸發(fā)器trigger_return,還書(shū)成功后，對(duì)應(yīng)的書(shū)籍book_num變?yōu)?:sql:createtriggertrigger_returnafterinsertonreturn_tableforeachrowbeginupdatebooksetbook_num=book_num+1wherebook_id=new.book_id;end還書(shū)時(shí)在return_table插入表項(xiàng)：stuidEsoolkidr&iiu門(mén)date1藝讓鑲翳ei"-

23、2-2E飛戸此時(shí)圖書(shū)歸還架上：bo-ok_idbook_nameboolk:_authobookjpubbocknumbook_sonbook_recoird卜computernetworkutho-rapuba1cs2016-12-20116；55；3定義定時(shí)器(事件)eventjob,每天自動(dòng)觸發(fā)一次，掃描視圖stu_borrow，若發(fā)現(xiàn)當(dāng)前有預(yù)期歸還時(shí)間小于當(dāng)前時(shí)間，則判斷為超期，生成處罰記錄，這個(gè)定時(shí)器將每天定時(shí)觸發(fā)存儲(chǔ)過(guò)程proc_gen_ticket:sql:createeventifnotexistseventJobonscheduleevery1DAY/*每天觸發(fā)*/oncom

24、pletionPRESERVEdocallproc_gen_ticket(getdate();/*調(diào)用存儲(chǔ)過(guò)程*/setglobalevent_scheduler=1;altereventeventJoboncompletionpreserveenable;/*開(kāi)啟定時(shí)器*/操作和結(jié)果顯示：1) .學(xué)生1借了圖書(shū)1,生成借書(shū)記錄stu_borrow視圖，如下：stuidbook_icl5tu_namebcrok_nameborrowdate&xpeGt_return_dateMl1atu_acomputernetwo201&-1Z-£817:072017-01-271

25、7:07:182).當(dāng)他在1月27日前還書(shū)時(shí)，沒(méi)有生成罰單：=tuclbook_idrffturn_date-201&-12-30211stujdbookjdover_dateti匚ketjee(hull(Null)CNullMull3).當(dāng)他在1月27日后還書(shū)時(shí)，生成罰單：s-tuidbookidretli門(mén)id旨t亡卜1J2C7-C2-2e2"：3stu_idboc>k_iclove-r_dateticketjee113234設(shè)計(jì)觸發(fā)器trigger_credit,若處罰記錄超過(guò)30條，則將這個(gè)學(xué)生的誠(chéng)信級(jí)設(shè)置為0,下次不允許借書(shū)：sql:createtrigger

26、trigger_creditafterinsertonticketforeachrowbeginif(selectcount(*)fromticketwherestu_id=new.stu_id)>30thenupdatestudentsetstu_integrity=0wherestu_id=new.stu_id;endif;end操作和結(jié)果顯示，測(cè)試時(shí)選擇插入ticket項(xiàng)大于3,因?yàn)?0太大了，不容易測(cè)試：學(xué)生1超過(guò)3次超期歸還圖書(shū)后，產(chǎn)生了4條罰單：&tu_idbook_idover_dateticlcet_fee11沱占123113421531fM.dh此時(shí)觸動(dòng)觸發(fā)器t

27、rigger_credit,將學(xué)生1的誠(chéng)信級(jí)設(shè)置為0:stuidstu_namestu_sexstu_agestu_p-rostujgradestu_inie-grityMlstu_am20cs201402stu_bm21cs20141、應(yīng)用程序設(shè)計(jì)與編碼1、系統(tǒng)實(shí)現(xiàn)中存儲(chǔ)函數(shù)和存儲(chǔ)過(guò)程的設(shè)計(jì)要求給出功能描述和代碼。1.設(shè)計(jì)存儲(chǔ)過(guò)程，產(chǎn)生罰單proc_gen_ticket：當(dāng)日期超過(guò)預(yù)定歸還日期時(shí)，產(chǎn)生罰單，并將記錄寫(xiě)入表ticket中，這個(gè)存儲(chǔ)過(guò)程在定時(shí)器eventJob中調(diào)用：sql：createprocedureproc_gen_ticket(incurrentdatedatetime)

28、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é)果顯示

29、：1).學(xué)生1借了圖書(shū)1,生成借書(shū)記錄stu_borrow視圖，如下：stuidbook_icl5tu_namebcrok_nameborrowdate&xpeGt_return_dateMl1atu_acomputernetwo201&-1Z-£817:072017-01-2717:07:182) .當(dāng)他在1月27日前還書(shū)時(shí)，沒(méi)有生成罰單：bgk_idreturn_date-201&-12-3021:&tu_idbookjdover_dateticlcet_fee(hull(Null)CNullMull3).當(dāng)他在1月27日后還書(shū)時(shí)，生成罰單：s.t

30、u_idbook_idreturndate卜112017-02-2821:5Etu_idboc>k_iclove-r_dateticketjee113232設(shè)計(jì)學(xué)生注冊(cè)信息存儲(chǔ)過(guò)程：學(xué)生注冊(cè)信息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_se

31、x,stu_age,stu_pro,stu_grade)values(stu_id,stu_name,stu_sex,stu_age,stu_pro,stu_grade);end3. 設(shè)計(jì)管理員注冊(cè)信息存儲(chǔ)過(guò)程:ma_registersql:createprocedurema_register(inma_idint,inma_namevarchar(20),inma_ageint,inma_phoneint)BEGINinsertintomanagervalues(ma_id,ma_name,ma_age,ma_phone);END4. 借書(shū)過(guò)程的實(shí)現(xiàn):1)設(shè)計(jì)存儲(chǔ)函數(shù)，func_get_cr

32、edit，返回學(xué)生的誠(chéng)信級(jí)：createfunctionfunc_get_credit(stu_idint)returnsintbeginreturn(selectstu_integrityfromstudentwherestudent.stu_id=stu_id);end2) 設(shè)計(jì)存儲(chǔ)函數(shù)，func_get_booknum，返回書(shū)籍是否在架上：createfunctionfunc_get_booknum(book_idint)returnsintbeginreturn(selectbook_numfrombookwherebook.book_id=book_id);end3) 設(shè)計(jì)存儲(chǔ)過(guò)程p

33、roc_borrow，調(diào)用func_get_credit和func_get_booknum,判斷這個(gè)學(xué)生誠(chéng)信度和書(shū)籍是否在架上,若為真，則借書(shū)成功，在borrrow表中插入紀(jì)錄；否則提示失?。篶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實(shí)驗(yàn)操作與結(jié)果顯示：borrow紀(jì)錄為空：stuidbookidborrowdstte-(hull)(Null)(Null)執(zhí)行函數(shù)，學(xué)生1借圖書(shū)2：callproc_borrow(1,2,now();學(xué)生1的誠(chéng)信級(jí)為0：5.tu_idstu_nameSiu呂EX&tu_age&tUJ3ITDstujgradestu_in