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

1、專業(yè).專注圖書管理系統(tǒng)數(shù)據(jù)庫設(shè)計一、系統(tǒng)概述1、系統(tǒng)簡介圖書管理是每個圖書館都需要進(jìn)行的工作 。一個設(shè)計良好的圖書管理系統(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ù)流圖 ：專業(yè).專注數(shù)據(jù)流圖學(xué)生專業(yè).專注1、系統(tǒng)功能設(shè)計畫出系統(tǒng)功能模塊圖并用文字對各功能模塊進(jìn)行詳細(xì)介紹系統(tǒng)功能模塊圖：三、數(shù)據(jù)庫設(shè)計方案圖表1、系統(tǒng) E-R 模型借閱者登陸管理員登陸圖書管理系統(tǒng)訪問模塊借閱者模塊管理員模塊專業(yè).專注總體E-R圖：專業(yè).專注圖書管理管理員精細(xì)化的局部E-R圖:學(xué)生借閱-歸還E-R圖:管理員E-R圖:年級誠信級學(xué)生圖書 IDID圖書 IDID圖書圖書 IDID學(xué)生 IDID學(xué)生 IDID性別年齡專業(yè)圖書歸還表歸還圖書借閱表處罰表分類歸還時間借閱專業(yè).專注類別編號類別名稱姓名年齡I

3、D號所屬單位管理管理員管理學(xué)生屬于圖書類別專業(yè).專注2、設(shè)計表給出設(shè)計的表名、結(jié)構(gòu)以及表上設(shè)計的完整性約束student:列名數(shù)據(jù)類型是否為空/性質(zhì)說明stu_idintnot null /PK標(biāo)明學(xué)生唯一學(xué)號stu_ namevarcharnot null學(xué)生姓名stu_sexvarcharnot null學(xué)生性別stu_ageintnot null學(xué)生年齡stu_provarcharnot null學(xué)生專業(yè)stu_gradevarcharnot null學(xué)生年級專業(yè).專注stu_i ntegrityintnot n ull/default=1學(xué)生誠信級book:列名數(shù)據(jù)類型是否為空/性質(zhì)說

4、明book_idintnot null / PK唯一書籍序號book_ namevarcharnot null書籍名稱book_authorvarcharnot null書籍作者book_pubvarcharnot null書籍出版社book_ numintnot null書籍是否在架上book_sortvarcharnot null書籍分類book_recorddatatimenull書籍登記日期book_sort:列名數(shù)據(jù)類型是否為空/性質(zhì)說明sort_idvarcharnot null / PK類型編號sort_ namevarcharnot null類型名稱borrow:存儲學(xué)生的借書

5、信息列名數(shù)據(jù)類型是否為空/性質(zhì)說明stude nt_idvarcharnot null / PK學(xué)生編號book_idvarcharnot null / PK書籍編號borrow_datedatatimenull借書時間expect_return_datedatetimenull預(yù)期歸還時間return_table:存儲學(xué)生的歸還信息列名數(shù)據(jù)類型是否為空/性質(zhì)說明專業(yè).專注stude nt_idvarcharnot null / PK學(xué)生編號book_idvarcharnot null / PK書籍編號borrow_datedatetimenull借書時間return_datedatatime

6、null實(shí)際還書時間ticket:存儲學(xué)生的罰單信息列名數(shù)據(jù)類型是否為空/性質(zhì)說明stude nt_idvarcharnot null / PK學(xué)生編號book_idvarcharnot null / PK書籍編號over_dateintnull超期天數(shù)ticket_feefloatnull處罰金額man ager:列名數(shù)據(jù)類型是否為空/性質(zhì)說明man ager_idvarcharnot null / PK管理員編號man ager_ namevarcharnot null管理員姓名man ager_agevarcharnot null管理員年齡man ager_ph onevarcharno

7、t null管理員電話3、設(shè)計索引給出在各表上建立的索引以及使用的語句student:1.為stud創(chuàng)建索引，升序排序sql:create in dex in dex_id on stude nt(stu_id asc);專業(yè).專注2.為stu_name創(chuàng)建索引，并且降序排序sql:alter table stude nt add in dex in dex_ name(stu_ name, desc);插入索引操作和結(jié)果如下所示：mysql create in dex in dex_id on stude nt(stu_id asc);Query OK, 0 rows affectedRec

8、ords: 0 Duplicates: 0 Warnin gs: 0mysql alter table stude nt add in dex in dex_ name(stu_ name desc);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnin gs: 0mysql墨1夕卜謎盍弓類型翼引廉idHsu idNormalBTREEirideinamestu_rarneNormalBTREEbook:1.為book_id創(chuàng)建索引，升序排列sql:create in dex in dex_bid on book(book_id);2

9、.為book_record創(chuàng)建索引，以便方便查詢圖書的登記日期信息，升序：sql:create in dex in dex_brecord on book(book_record);插入索引的操作和結(jié)果如下所示:mysql create in dex in dex_bid on book(book_id);Query OK, 0 rows affected專業(yè).專注Records: 0 Duplicates: 0Warnin gs: 0mysql create in dex in dex_brecord on book(book_record);Query OK, 0 rows affecte

10、dborrow:1.為stud和book_id創(chuàng)建多列索引：sql:create in dex in dex_sid_bid on borrow(stu_id asc, book_id asc);插入索引的操作和結(jié)果如下所示:mysql create in dex in dex_sid_bid on borrow(stu_id asc, book_id asc);Query OK, 0 rows affectedRecords: 0 Duplicates: 0Warnin gs: 0瑩引外洽遠(yuǎn)頂注澤池範(fàn)案引浬叢弓方去J book ndH bookjdNormalETREEindex_sid_t

11、idstu id. book idNormalBTREEreturn_table:1.為stud和book_id創(chuàng)建多列索引：sql:create in dex in dex_sid_bid on retur n_table(stu_id asc, book_id asc);專業(yè).專注插入索引的操作和結(jié)果如下所示mysql create in dex in dex_sid_bid_r on retur n_table(stu_id asc, book_id asc);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnin gs: 0 t

12、icket:1.為stud和book_id創(chuàng)建多列索引：sql:create in dex in dex_sid_bid on ticket(stu_id asc, book_id asc);插入索引的操作和結(jié)果如下所示:mysql create in dex in dex_sid_bid on ticket(stu_id asc, book_id asc);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnin gs: 0社僅 霆引 外堪 輕盤器Sts注釋3L砒呂W 位I &tu id, book id- . . .Norma

13、lBTR旺man ager:1.為manager_id創(chuàng)建索引：sql:create in dex in dex_mid on man ager(ma nager_id);插入索引的操作和結(jié)果如下所示:mysql create in dex in dex_mid on man ager(ma nager_id);Query OK, 0 rows affected夕K牲發(fā)器NcrrmalBTREESQL預(yù)彥欄檢欄位stu_id. bgkd專業(yè).專注Records: 0 Duplicates: 0Warnin gs: 0觀遷噴生澤沁趣呂養(yǎng)引趣冨弓方跡卜j.* 一. . . . . - .1mana

14、ger i dlNcrmalBTREE4、設(shè)計視圖給出在各表上建立的視圖以及使用的語句。1.在表student上創(chuàng)建計算機(jī)專業(yè)（cs）學(xué)生的視圖stu_cs：sql: create view stu_cs asselect *from stude nt操作和結(jié)果：mysql create view stu_cs as select *from stude ntwhere stu_pro = cs;Query OK, 0 rows affectedstu_id或u name5tu_agestu_pro5tu_grade5tvntegrityI tu_am20cs201412 stu bm21匚2

15、01412弓tu cf20匚52D1414 stu dfigcs201415 stu ernEQ匚旨201412.在表student, borrow和book上創(chuàng)建借書者的全面信息視圖stu_borrowsql: create view stu_borrow asselect stude nt.stu_id, book.book_id,stude nt.stu_ name,book.book_ name,borrow_date,adddate(borrow_date,30) expect_return_datewhere pro =cs專業(yè).專注from stude nt, book, bor

16、rowwhere stude nt.stud = borrow.stu_id and book.book_id = borrow.book_id;操作和結(jié)果：mysql create view stu_borrow asselectstude nt.stu_id,book.book_id,stude nt.stu_ name,book.book_ name,borrow_date,adddate(borrow_date,30) expect_return_datefrom stude nt, book, borrowwhere stude nt.stud = borrow.stu_id and

17、 book.book_id = borrow.book_id;Query OK, 0 rows affectedstujdbookbook_nameborrow_d3teexpect_retu rn_d ate1computer netwo 2016-12-28 17:07 2017-01-27 17r07：183.創(chuàng)建類別1的所有圖書的視圖cs_book:sql: create view cs_book asselect *from bookwhere book.book_sort in(select book_sort.sort .n amefrom book sort專業(yè).專注where

18、 sort_id = 1);操作和結(jié)果顯示：mysql create view cs_book as select *from bookwhere book.book_sort in(select book_sort.sort_ name from book_sort where sort_id = 1);Query OK, 0 rows affectedbQok.authorbookbook_sortbQQlk_recQfd1computer nebvoricAiiitharj!p( (ih a1 C52010-12-2B 1&55J372eampjJter Dpe-ratiomau

19、thor_bpub b1 Ct2016-)2 283inicirmiati&n securitj1author cpuba1 cs2016-1228 15：58：i74ecHware ongineerirgoutlhcr_dpub_dl1 H2016 12 28 15( (S9:1OSte+ plus leainirga-utbcrepuba1 M201 5 1215t5H56c proq-ammngiautror_Epub_a1 CS2016-12-2S 1&3&567詢authoredP*jb_c1 a2D1 delimiter $mysql create trig

20、ger trigger_borrow- after in sert on borrow- for each row- beg in- update book set book_ num = book_ num - 1- where book_id = n ew.book_id;- end- $Query OK, 0 rows affected專業(yè).專注在插入表borrow之前，book_id = 1的圖書還在架上，為1:book idbco m#bookauthorbaokjpubbaotc_numbook_tortbcokrecordl1 cemput*r0 Ct2016-12-26 1&a

21、mp;55i2.設(shè)計觸發(fā)器trigger_return,還書成功后，對應(yīng)的書籍book_num變?yōu)?:sql:create trigger trigger_retur nafter in sert on retur n_tablefor each rowbeginupdate book set book_ num = book_ num + 1 where book_id = n ew.book_id;end還書時在return_table插入表項(xiàng)：stujdbook_idreturn_date112016-12-28 18f!I此時圖書歸還架上book idboo kji am ebooL_

22、aiuthorbookjpijbboclkruimbook_sortbookj ecordJcampjter n曰iwcirkauthorpubdi1 2316-122816L553.定義定時器(事件)eventJob,每天自動觸發(fā)一次，掃描視圖stu_borrow,若發(fā)現(xiàn)當(dāng)前 有預(yù)期歸還時間小于當(dāng)前時間 ，則判斷為超期，生成處罰記錄，這個定時器將每天定時觸發(fā)存儲過程proc_gen_ticket:專業(yè).專注sql:create event if not exists even tJobon schedule every 1 DAY/*每天觸發(fā)*/on completion PRESERVEd

23、o call proc_gen_ticket(getdate(); /*調(diào)用存儲過程*/set global evenscheduler = 1;alter event even tJob on completi on preserve en able; /*開啟定時器*/操作和結(jié)果顯示：1).學(xué)生1借了圖書1，生成借書記錄stu_borrow視圖，如下：stu_idbook_rdstu_namebooknameborrow dateexpect_retuirn_cl ate11stu_acomputer netwo 2016-12-28 17:07 2017-01-27 17:O7:1S2)

24、.當(dāng)他在1月27日前還書時，沒有生成罰單tu idbook idreturn date112016-12-30 21:4stujdbookjdaver dartetticketjfee(Null)(Null)(Nu ID3).當(dāng)他在1月27日后還書時，生成罰單：stu idbock idreturn cUtw112017-02-20 31:5專業(yè).專注rtu_idbook idticketT3234.設(shè)計觸發(fā)器trigger_credit,若處罰記錄超過30條， 則將這個學(xué)生的誠信級設(shè)置為0,下次不允許借書sql:create trigger trigger_creditafter in se

25、rt on ticketfor each rowbeginif (select coun t(*) from ticket where stu_id=new.stud)30 the nupdate stude nt set stu_ in tegrity = 0 where stu_id = n ew.stu_id;end if;end操作和結(jié)果顯示，測試時選擇插入ticket項(xiàng)大于3，因?yàn)?0太大了，不容易測試：學(xué)生1超過3次超期歸還圖書后，產(chǎn)生了4條罰單:曰bookjdouer_dateticket_fee1132312311J421531fM. dhfM.jh此時觸動觸發(fā)器trigger_credit，將學(xué)生1的誠信級設(shè)置為0:stu_i dstu.n am estusextu_aaestujD rostu_g