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

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

2、刪除圖書信息6 .管理員可以注銷學(xué)生信息。通過需求定義，畫出圖書管理系統(tǒng)的數(shù)據(jù)流圖：數(shù)據(jù)流圖借閱信息:、系統(tǒng)功能設(shè)計(jì)畫出系統(tǒng)功能模塊圖并用文字對(duì)各功能模塊進(jìn)行詳細(xì)介紹。系統(tǒng)功能模塊圖:圖書管理系統(tǒng)管理員模塊借閱者模塊三、數(shù)據(jù)庫(kù)設(shè)計(jì)方案圖表1、系統(tǒng)E-R模型總體E-R圖：學(xué)生1 1借閱隔a圖書管理管理員精細(xì)化白局部E-R圖：學(xué)生借閱-歸還E-R圖：（學(xué)生ID .（年級(jí)年齡,誠(chéng)信級(jí)）j工學(xué)生（學(xué)生1叭/',1歸還時(shí)間圖書歸還表（圖書ID ：j歸還 借閱-廣；上處罰表/（圖 PDX :（學(xué)生ID）（超期:處罰金額圖書_C（圖書 ID書名（出版社）（分類）（，性別）Y專業(yè)）.（學(xué)生ID ） &

3、#39;（圖書借閱表圖書ID） 一乂登記日期，（借閱時(shí)間）數(shù)量）管理員E-R圖:2、設(shè)計(jì)表給出設(shè)計(jì)的表名、結(jié)構(gòu)以及表上設(shè)計(jì)的完整性約束。student :列名數(shù)據(jù)類型是否為空/性質(zhì)說明stu_idintnot null /PK標(biāo)明學(xué)生唯一學(xué)號(hào)stu_namevarcharnot null學(xué)生姓名stu_sexvarcharnot null學(xué)生性別stu_ageintnot null學(xué)生年齡stu_provarcharnot null學(xué)生專業(yè)stu_gradevarcharnot null學(xué)生年級(jí)stu_integrityintnot null/default=1學(xué)生誠(chéng)信級(jí)book:列名數(shù)據(jù)類型

4、是否為空/性質(zhì)說明book_idintnot null / PK唯一書籍序號(hào)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類型編號(hào)sort_namevarcharnot null類型名稱borrow:存儲(chǔ)

5、學(xué)生的借書信息列名數(shù)據(jù)類型是否為空/性質(zhì)說明student_idvarcharnot null / PK學(xué)生編號(hào)book_idvarcharnot null / PK書籍編號(hào)borrow_datedatatimenull借書時(shí)間expect_return_datedatetimenull預(yù)期歸還時(shí)間return_table:存儲(chǔ)學(xué)生的歸還信息列名數(shù)據(jù)類型是否為空/性質(zhì)說明student_idvarcharnot null / PK學(xué)生編號(hào)book_idvarcharnot null / PK書籍編號(hào)borrow_datedatetimenull借書時(shí)間return_datedatatimenu

6、ll實(shí)際還書時(shí)間ticket:存儲(chǔ)學(xué)生的罰單信息列名數(shù)據(jù)類型是否為空/性質(zhì)說明student_idvarcharnot null / PK學(xué)生編號(hào)book_idvarcharnot null / PK書籍編號(hào)over_dateintnull超期天數(shù)ticket_feefloatnull處罰金額manager:列名數(shù)據(jù)類型是否為空/性質(zhì)說明manager_idvarcharnot null / PK管理員編號(hào)manager_namevarcharnot null管理員姓名manager_agevarcharnot null管理員年齡manager_phonevarcharnot null管理員電

7、話3、設(shè)計(jì)索引給出在各表上建立的索引以及使用的語(yǔ)句。student :1 .為stu_id創(chuàng)建索引，升序排序sql:create index index_id on student(stu_id asc);2 .為stu_name創(chuàng)建索引，并且降序排序sql:alter table student add index index_name(stu_name, desc);插入索引操作和結(jié)果如下所示：mysql> create index index_id on student(stu_id asc);Query OK, 0 rows affectedRecords: 0 Duplicat

8、es: 0 Warnings: 0mysql> alter table student add index index_name(stu_name desc);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0 book:欄位索引QL領(lǐng)點(diǎn)a欄位犯美暨Ijindex idNormalStu rgrneNormalmysql>累弓方法 b REL BTREE1 .為book_id創(chuàng)建索引，升序排列sql:create index index_bid on book(book_id);2 .為book_record創(chuàng)

9、建索引，以便方便查詢圖書的登記日期信息，升序:sql:create index index_brecord on book(book_record);插入索引的操作和結(jié)果如下所示:mysql> create index index_bid on book(book_id);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0mysql> create index index_brecord on book(book_record);Query OK, 0 rows affectedRecords: 0 Dupli

10、cates: 0 Warnings: 0名欄位圭引樊型案引方法IJ index bidn ： book idNormalBTREEindejcbrecordbookrecordMormalBREE主卓客QL凌空borrow:1.為stu_id和book_id創(chuàng)建多列索引：sql:create index index_sid_bid on borrow(stu_id asc, book_id asc);插入索引的操作和結(jié)果如下所示：mysql> create index index_sid_bid on borrow(stu_id asc, book_id asc);Query OK, 0

11、 rows affectedRecords: 0 Duplicates: 0 Warnings: 0欄色 圭罵 外糧 岫發(fā)翳法質(zhì) 注癢 13L竭名欄位素引蚪卜incex_5id_tid.bo ok_ dstujd. bookjdNormalNormalETREEBTREEreturn_table: 1.為stu_id和book_id創(chuàng)建多列索引:sql:create index index_sid_bid on return_table(stu_id asc, book_id asc);插入索引的操作和結(jié)果如下所示：mysql> create index index_sid_bid_r

12、on return_table(stu_id asc, book_id asc);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0欄位熹I g國(guó)魅精演 謝 SQL預(yù)覽案看方法BTREE芻 桂位素引類型index ±id bid r . .ticket:1.為stu_id和book_id創(chuàng)建多列索引：sql:create index index_sid_bid on ticket(stu_id asc, book_id asc);插入索引的操作和結(jié)果如下所示：mysql> create index inde

13、x_sid_bid on ticket(stu_id asc, book_id asc);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0本位 索引 卡償 觸好器選面 注拜 SQL前弟'ndex_sid_bidstujd, book idNormalBTREEmanager:1.為manager_id 創(chuàng)建索引：sql:create index index_mid on manager(manager_id);插入索引的操作和結(jié)果如下所示：mysql> create index index_mid on m

14、anager(manager_id);Query OK, 0 rows affectedRecords: 0Duplicates: 0Warnings:0欄位 爨馬 卦鋌 觸友然五項(xiàng) 主擇 萄受其近幻索引越聚弓方法manaceridNormalBTREE4、設(shè)計(jì)視圖給出在各表上建立的視圖以及使用的語(yǔ)句。1 .在表student上創(chuàng)建計(jì)算機(jī)專業(yè)(cs)學(xué)生的視圖stu_cs :sql: create view stu_cs asselect *from studentwhere pro =' cs'；操作和結(jié)果：mysql> create view stu_cs assel

15、ect *from studentwhere stu_pro = 'cs'Query OK, 0 rows affectedstu idnamest Li sexstu agestu prostu gradestu integrityI Etu am20CE201412 stu bm21匚5201413 stu)f20US201414 std_dfIScs201415m20C5201412 .在表student, borrow 和book上創(chuàng)建借書者的全面信息視圖stu_borrow :sql: create view stu_borrow asselect student.s

16、tu_id, book.book_id, student.stu_name, book.book_name, borrow_date , adddate(borrow_date,30) expect_return_datefrom student, book, borrowwhere student.stu_id = borrow.stu_id and book.book_id = borrow.book_id;操作和結(jié)果：mysql> create view stu_borrow asselect student.stu_id, book.book_id, student.stu_na

17、me, book.book_name, borrow_date , adddate(borrow_date,30) expect_return_datefrom student, book, borrowwhere student.stu_id = borrow.stu_id and book.book_id = borrow.book_id;Query OK, 0 rows affectedstu_idbook_idl號(hào)tu_ riarnsbook_nameborrow_d3te&xpect_retu rn_d atecomputer netwo 2016-12-28 17:07 2

18、017-01-27 17:07:103 .創(chuàng)建類別1的所有圖書的視圖cs_book :sql: create view cs_book asselect *from bookwhere book.book_sort in(select book_from book_sortwhere sort_id = 1);操作和結(jié)果顯示：mysql> create view cs_book asselect *from bookwhere book.book_sort in(select book_sort.sort_namefrom book_sortwhere sor

19、t_id = 1);Query OK, 0 rows affected0 5丹昨boolc_3uthorbootjaubbookjiumbC0<5ClTbock_rec “dcom pirter ngtwc，#author apub.w1 CE2015-12-2& 1&55572computer opcrotienblither bpub_b1231 S 12 28 16tS&393 mf&rfTifliioriouthsjup<ub_d1 Cl2D16-12-2fi 115:58374engineeirigauthor_dpiub_d1 £

20、;52D1<k12-2A 15:59： 105c+ 4 p|j& learning3utbor_epub_31 CE201612-28 15:59556C prgq rannnntniauthor gpub日1 C2016-12 2& 16：36561sqlauthcjd1 a2016-1226 1700.474 .創(chuàng)建個(gè)人所有借書歸還紀(jì)錄視圖stu_borrow_return:sql:create view stu_borrow_return asselectstudent.stu_id,student.stu_name,book.book_name,return_ta

21、ble.borrow_date,return_table.return_datefrom student, book, return_tablebook.book_id,stu a1 camputer network2016-10-16 17:07:132016-12-2916t56i50where student.stu_id = return_table.stu_id and book.book_id = return_table.book_id;5tLic1stu_namebook_idbooknameborrow_datereturr_date5、設(shè)計(jì)觸發(fā)器給出在各表上建立的觸發(fā)器以及

22、使用的語(yǔ)句。1 .設(shè)計(jì)觸發(fā)器borrow,當(dāng)某學(xué)生借書成功后，圖書表相應(yīng)的圖書不在架上，變?yōu)?0：sql:create trigger borrowafter insert on borrowfor each rowbeginupdate book set book_num = book_num Twhere book_id = new.book_id;end操作與結(jié)果顯示：mysql> delimiter $mysql> create trigger trigger_borrow- > after insert on borrow-> for each row- &g

23、t; begin- > update book set book_num = book_num - 1- > where book_id = new.book_id;- > end-> $Query OK, 0 rows affected在插入表 borrow 之前，book_id = 1的圖書還在架上，為 1 ：book idbco< namebe ok: a uth orbook publb co k. n umbock sortbook record1 computer ne-torkauthor_apub_i1 C52D1&12-28學(xué)生1借了這本

24、書后，在 borrow中插入了一條記錄:5tu idbo ok idborrow_date2016-12-20 17:07在borrow中插入這條記錄后，book_id =1的圖書，不在架上，為 0 :b«k_idbook_au:hoirbsokpublbcok_nijmbook_ortbcok_r*rardl1 cemput#r0 ct2016-12-26 1655i2 .設(shè)計(jì)觸發(fā)器trigger_return,還書成功后，對(duì)應(yīng)的書籍 book_num變?yōu)? ： sql:create trigger trigger_returnafter insert on return_tabl

25、efor each rowbeginupdate book set book_num = book_num + 1where book_id = new.book_id;end還書時(shí)在return_table插入表項(xiàng)：shjid bookid returndate2016-12-18c5卜 11此時(shí)圖書歸還架上:book idbookjiamebooLaiuthorbookjpiibbock.runnbook_sortbookjecord卜Jcanipjiter nWwcirkauthorspubd12016-1228 1a553 .定義定時(shí)器(事件)eventJob ,每天自動(dòng)觸發(fā)一次，掃描

26、視圖stu_borrow ,若發(fā)現(xiàn)當(dāng)前有預(yù)期歸還時(shí)間小于當(dāng)前時(shí)間，則判斷為超期，生成處罰記錄，這個(gè)定時(shí)器將每天定時(shí)觸發(fā)存儲(chǔ)過程 proc_gen_ticket :sql:create event if not exists eventJobon schedule every 1 DAY/*每天觸發(fā) */ on completion PRESERVEdo call proc_gen_ticket(getdate(); /*調(diào)用存儲(chǔ)過程 */開啟定時(shí)器*/set global event_scheduler = 1;alter event eventJob on completion preser

27、ve enable; /*操作和結(jié)果顯示:1) .學(xué)生1借了圖書1,生成借書記錄 stu_borrow 視圖，如下:stu_idbookidstu_namebooknamebo rrowed ateexpect_retLiirn_id ate1stu_acompLiter netwo 2016-12-23 17:07 2017-01 -27 17:07:102) .當(dāng)他在1月27日前還書時(shí)，沒有生成罰單:stu id book id return date2016-12-30 21:stuidboo k idverdateticket Jee(Null)(Null)(Nuim3).當(dāng)他在1月2

28、7日后還書時(shí)，生成罰單:stujd boclc_id retumdate2017-02-23 2"stu_idbookjdovr-dateticket_fee13234.設(shè)計(jì)觸發(fā)器trigger_credit ,若處罰記錄超過30條，則將這個(gè)學(xué)生的誠(chéng)信級(jí)設(shè)置為0,下次不允許借書：sql:create trigger trigger_creditafter insert on ticketfor each rowbeginif (select count(*) from ticket where stu_id=new.stu_id)>30 thenupdate student s

29、et stu_integrity = 0 where stu_id = new.stu_id;end if;end操作和結(jié)果顯示，測(cè)試時(shí)選擇插入ticket項(xiàng)大于3,因?yàn)?0太大了，不容易測(cè)試：學(xué)生1超過3次超期歸還圖書后，產(chǎn)生了 4條罰單：stuidbookjdouer_dateticketjee1132312311I421531此時(shí)觸動(dòng)觸發(fā)器trigger_credit ,將學(xué)生1的誠(chéng)信級(jí)設(shè)置為0:stujdtu_namestusexitu_agestujJTOstu_gradestu_integritym20C5201402stu_bm21cs20141四、應(yīng)用程序設(shè)計(jì)與編碼實(shí)現(xiàn)1、系統(tǒng)

30、實(shí)現(xiàn)中存儲(chǔ)函數(shù)和存儲(chǔ)過程的設(shè)計(jì)要求給出功能描述和代碼。1.設(shè)計(jì)存儲(chǔ)過程，產(chǎn)生罰單proc_gen_ticket :當(dāng)日期超過預(yù)定歸還日期時(shí)，產(chǎn)生罰單，并將記錄寫入表ticket中，這個(gè)存儲(chǔ)過程在定時(shí)器eventJob中調(diào)用：sql :create procedure proc_gen_ticket(in currentdate datetime)BEGINdeclare cur_date datetime;set cur_date = currentdate;replace into ticket(stu_id, book_id, over_date, ticket_fee)selectstu

31、_id,book_id,datediff(cur_date,stu_borrow.expect_return_date),0.1*datediff(cur_date,stu_borrow.expect_return_date)from stu_borrowwhere cur_date>stu_borrow.expect_return_date;end操作和結(jié)果顯示：1) .學(xué)生1借了圖書1,生成借書記錄 stu_borrow 視圖，如下：stu_idbookidstu_namebook_nameborrow dateexpect_retLnrn_cl ate1stu_acompirter

32、 netwo 2016門"23 17:07 2017-01-27 17:07:102) .當(dāng)他在1月27日前還書時(shí)，沒有生成罰單:5tu_idbo ok idreturn date112016-12-0 21:4|stujdbookjdover darteticket fee(MM(Null)CNljII)(Uuin3) .當(dāng)他在1月27日后還書時(shí)，生成罰單:stu_id bockid retu m_dat?2017-02-20 21Ertu_idbookidover-dateticket_fee:一113222 .設(shè)計(jì)學(xué)生注冊(cè)信息存儲(chǔ)過程：學(xué)生注冊(cè)信息stu_registersql

33、:create procedure stu_register(in stu_id int, in stu_name varchar(20), in stu_sex varchar(20), in stu_age int, in stu_pro varchar(20), in stu_grade varchar(20)begininsert into student(stu_id, stu_name, stu_sex, stu_age, stu_pro, stu_grade) values(stu_id, stu_name, stu_sex, stu_age, stu_pro, stu_grad

34、e);end3 .設(shè)計(jì)管理員注冊(cè)信息存儲(chǔ)過程：ma_registersql:create procedure ma_register(in ma_id int, in ma_name varchar(20), in ma_age int, inma_phone int)BEGINinsert into managervalues(ma_id, ma_name, ma_age, ma_phone);END4 .借書過程的實(shí)現(xiàn)：1) 設(shè)計(jì)存儲(chǔ)函數(shù)，func_get_credit ,返回學(xué)生的誠(chéng)信級(jí)：create function func_get_credit(stu_id int) return

35、s intbeginreturn(select stu_integrity from student where student.stu_id = stu_id);end2) 設(shè)計(jì)存儲(chǔ)函數(shù)，func_get_booknum ,返回書籍是否在架上:create function func_get_booknum(book_id int) returns intbeginreturn(select book_num from book where book.book_id = book_id); end3) 設(shè)計(jì)存儲(chǔ)過程 proc_borrow ,調(diào)用 func_get_credit 和 func

36、_get_booknum ,判斷這個(gè)學(xué)生誠(chéng)信度和書籍是否在架上，若為真，則借書成功，在borrrow表中插入紀(jì)錄；否則提示失?。篶reate procedure proc_borrow(in stu_id int, in book_id int, in borrow_date datetime)beginif func_get_credit(stu_id) = 1 and func_get_booknum(book_id) = 1 theninsert into borrowvalues(stu_id, book_id, borrow_date);elseselect 'failed

37、to borrow'end if;end實(shí)驗(yàn)操作與結(jié)果顯示:borrow紀(jì)錄為空:stu Jd book id borrow date(Null)執(zhí)行函數(shù)，學(xué)生1借圖書2:call proc_borrow(1,2,now();學(xué)生1的誠(chéng)信級(jí)為0 :stu id4li sew stu.agcstj_prc)stu_grade_integrityB歡u a20 n20140借書失?。盒畔⒔Y(jié)果1幅兄 狀套faded to borrow failed to borrow修改學(xué)生1誠(chéng)信級(jí)為1 :stujdstu_nsmestLi_sexstu_agestuprostu_grddestuj int

38、egrity1m20CS20141此時(shí)借書成功:book idstu idborrowdate2 2016-12-29 17：315 .還書存儲(chǔ)過程proc_return :當(dāng)還書時(shí)，查看是否書是否超期，即查詢ticket表項(xiàng)，當(dāng)發(fā)現(xiàn)超期，提示交罰單后再次還書, 如沒有超期，則紀(jì)錄歸還項(xiàng)目到 return_table中，并且刪除借書紀(jì)錄(以免還書后定時(shí)器仍然掃描這個(gè)紀(jì)錄)：sql :create procedure proc_return(in stu_id int, in book_id int, in return_date datetime)beginDECLARE borrowdate

39、 datetime;andid =if (select payoff from ticket where ticket.stu_id = stu_id ticket.book_id=book_id) = 1 then /*判斷是否交了罰單，1表示沒有交*/select 'please pay off the ticket'else /*紀(jì)錄歸還項(xiàng)目到 return_table中，并且刪除借書紀(jì)錄 */set borrowdate = (select borrow_date from borrow where borrow.stu stu_id and borrow.book_i

40、d = book_id);insert into return_tablevalues(stu_id, book_id, borrowdate, return_date);delete from borrowwhere borrow.stu_id = stu_id and borrow.book_id = book_id;end if;end實(shí)驗(yàn)操作與結(jié)果顯示:學(xué)生1借了圖書2:stujdbookjdborrow_d ate2 2016-12-2917:31超期產(chǎn)生了罰單，沒有交罰單，payoff=1stujd bookjd over_dte ticketfe-e payoff14440122

41、031132221此時(shí)調(diào)用還書過程:call proc_return(1,2, now();提示交罰單：please pay off the ticketplease pay off the ticket交罰單，調(diào)用 proc_payoff: call proc_payoff(1,2);交罰單成功，payoff = 0 ;5tLi idbook idover_d*itefeepjyvffD1444Qi22Saoi32221此時(shí)再次調(diào)用還書過程:call proc_return(1,2, now();還書成功，在return_table生成了還書紀(jì)錄:book idborrow dateretu

42、 rri datestu id1 2016-1016 17:072016-12-29 16:52 2016-12-2917:11 2016-12-39 19:46.交罰單存儲(chǔ)過程：修改罰單中payoff段為0,表明罰單已交：create procedure proc_payoff(in stuid int, in bookid int)beginupdate ticketset payoff = 0where ticket.stu_id = stuid and ticket.book_id = bookid;select ' succeed end交罰單，調(diào)用 proc_payoff:

43、 call proc_payoff(1,2);交罰單成功，payoff = 0 ;stu idbook idover_dateticketfeepayoff*Ml14440i22a30i322212、功能實(shí)現(xiàn)按各功能模塊進(jìn)行描述。要求：畫出流程圖并給出實(shí)現(xiàn)代碼。創(chuàng)建學(xué)生統(tǒng)一賬戶，賬戶名：student_account ,并且授予權(quán)限：sql:create user 'student_account''localhost'grant insert,select on student to 'student_account''localhos

44、t'grant select on book to 'student_account''localhost'grant insert,select on borrow to 'student_account''localhost'grant insert,select on return_table to 'student_account''localhost'grant select on ticket to 'student_account''localhost&

45、#39;創(chuàng)建管理員統(tǒng)一賬戶，賬戶名：manager_account,并且授予全部權(quán)限:sql:create user 'manager_account''localhost' identified by '123'grant all on library_management to 'manager_account''localhost'查詢圖書信息sql按書名查找：select * from book where book_name =select « fram baaJc where book_nai

46、ce = 1 dql,;HTF11h| boolcid|七口口k naxre|七口okmxrthnr|口口0匕 pub|boofc_num|bock sort+-+44|7 | sql| authord | putoc |1 | as-4 4- - 4- - - i - -4- -按作者查找:select * from book where book_author = author 'tiQok:_id | tQok_nazue| b口口忙一己口占匕口!？ | t)口口*_口汕 | book_nnni | b一 -T 一 - TNk- 一一3 | inforinatioTi secuiity | author_c | puti_a. |1 | c借書功能：proc_borrow(in stu_id int, in book_id int, in borrow_date datetime)如果要接的書還在架上，并且學(xué)生的誠(chéng)信級(jí)為1,那么可以借書 call proc_borrow(1, 1, now();命令行操作：rtysql> call(lr 5T n.aw();Q