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

1、圖書管理系統(tǒng)數(shù)據(jù)庫(kù)設(shè)計(jì)1、系統(tǒng)簡(jiǎn)介圖書管理是每個(gè)圖書館都需要進(jìn)行的工作。 一個(gè)設(shè)計(jì)良好的圖書管理系統(tǒng)數(shù)據(jù)庫(kù)能夠給圖書管理帶來(lái)很大的便利。2、需求分析圖書管理系統(tǒng)的需求定義為：1 .學(xué)生可以直接通過(guò)借閱終端來(lái)查閱書籍信息，同時(shí)也可以查閱自己的借閱信息。2 .當(dāng)學(xué)生需要借閱書籍時(shí)，通過(guò)賬號(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é)生信息。通過(guò)需求定義，畫出圖書管理系統(tǒng)的數(shù)據(jù)流圖：數(shù)據(jù)流圖:、系統(tǒng)功能設(shè)計(jì)畫出系統(tǒng)功能模塊圖并用文字對(duì)各功能模塊進(jìn)行詳細(xì)介紹。 系統(tǒng)功能模塊圖：三、數(shù)據(jù)庫(kù)設(shè)計(jì)方案圖表1、系統(tǒng)E-R模型總體E-R圖：精細(xì)化白局部E-R圖:（學(xué)生ID）（年級(jí) '，（年d誠(chéng)信級(jí)）jN學(xué)生乙（學(xué)生1叭 /'歸還時(shí)間-圖書歸還表/（圖書ID”）歸還借閱A（學(xué)生id）（超期3處罰金額圖書 1Kz（出版社）分類管理員E-R圖：姓名）（年齡（D id#/2“一（所屬單位）管理員聯(lián)系電話）7 y（類那學(xué)生圖書，屬于（性別）Y專業(yè),人學(xué)生吟圖書借閱表Y圖書ID）/_/登記日期）卜借閱時(shí)間

3、作者）匕數(shù)量）扁號(hào)）（類別名稱圖書類別學(xué)生借閱-歸還E-R圖:2、設(shè)計(jì)表給出設(shè)計(jì)的表名、結(jié)構(gòu)以及表上設(shè)計(jì)的完整性約束。student :列名數(shù)據(jù)類型是否為空/性質(zhì)說(shuō)明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ù)類型是否為空/性質(zhì)說(shuō)

4、明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ì)說(shuō)明sort_idvarcharnot null / PK編號(hào)sort_namevarcharnot null名稱borrow:存儲(chǔ)學(xué)生的借書信息列名數(shù)據(jù)類

5、型是否為空/性質(zhì)一說(shuō)明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ì)說(shuō)明student_idvarcharnot null / PKr學(xué)生編號(hào)book_idvarcharnot null / PK書籍編號(hào)borrow_datedatetimenullr借書時(shí)間return_datedatatimenull實(shí)際還書時(shí)間t

6、icket:存儲(chǔ)學(xué)生的罰單信息列名數(shù)據(jù)類型是否為空/性質(zhì)說(shuō)明student_idvarcharnot null / PK學(xué)生編號(hào)book_idvarcharnot null / PK書籍編號(hào)over_dateintnull超期天數(shù)ticket_feefloatnull處罰金額manager:列名數(shù)據(jù)類型是否為空/性質(zhì)說(shuō)明manager_idvarcharnot null / PK管理員編號(hào)manager_namevarcharnot null管理員姓名manager_agevarcharnot null管理員年齡manager_phonevarcharnot null管理員電話3、設(shè)計(jì)索引給出

7、在各表上建立的索引以及使用的語(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 affected Records: 0 Duplicates: 0 Wa

8、rnings: 0mysql> alter table student add index index_name(stu_name desc);Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0mysql>欄穆 蠡l 外短 觸發(fā)器選項(xiàng) 注釋 SQL燙S米弓也NormalNomnal索弓方去BTREEBTREES欄位indlex_idirideTiarnestu_r3rnebook:1 .為book_id創(chuàng)建索引，升序排列sql:create index index_bid on book(book_id);2

9、 .為book_record創(chuàng)建索引，以便方便查詢圖書的登記日期信息，升序: 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 affected

10、Records: 0 Duplicates: 0 Warnings: 0欄侵 索引 外窘 料垢會(huì)選趣 注蟀 £QL碰慮至弓后去BTREEBTREES欄位需引樊型boo k_idhlormlmdejcbrecordbookrecordNornnalborrow: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, b

11、ook_id asc);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0欄構(gòu) 索另 外鋰 融妗器知 注集弓QL逆層索弓I昊里NormalNormal塞弓后去BTREEBTREE昌欄位oook idindex_sid_bidstu_i& book idreturn_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>

12、; create index index_sid_bid_r on return_table(stu_id asc, book_id asc);Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0欄位 素 夕段觸發(fā)言談 注薜 £QL預(yù)覽stu id. book idNormal案W方汰BTREEticket:1.為stu_id和book_id創(chuàng)建多列索引：sql:create index index_sid_bid on ticket(stu_id asc, book_id asc);插入索引的操作和結(jié)果如下所示

13、：mysql> create index index_sid_bid on ticket(stu_id asc, book_id asc);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0欄化 塞引 外凝 倏宜器砌 注程 GL獨(dú)賢.如 id. book idNormal室引方至BTREEmanager:1.為manager_id 創(chuàng)建索引：sql:create index index_mid on manager(manager_id);插入索引的操作和結(jié)果如下所示:mysql> create index i

14、ndex_mid on manager(manager_id);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0欄拉 賓匚酸三舒.三廣 胡 £UL蠻強(qiáng)manacerjd索引法蒙弓方正NormalBTREE4、設(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

15、 view stu_cs asselect *from studentwhere stu_pro = 'cs'Query OK, 0 rows affectedstu_id5tuname5tu_agestu_pro5tu_gradesttijntegrityI 5tu_am20cs201412 stu bm21匚5201413 占tu cf20cs201414 stu dfigCS201415 5tU crn2。G201412 .在表student, borrow 和book上創(chuàng)建借書者的全面信息視圖stu_borrowsql: create view stu_borrow a

16、sselect , , , , borrow_date , adddate(borrow_date,30) expect_return_date from student, book, borrowwhere = and =;操作和結(jié)果：mysql> create view stu_borrow asselect , , , , borrow_date , adddate(borrow_date,30) expect_return_date from student, book, borrowwhere = and =;Query OK, 0 rows affectedstujdbook

17、idbook_nameborrow_d3teexpect_retu rn_d ate11computer netwo2016-12-28 17:07 2017-01-27 17:07:103 .創(chuàng)建類別1的所有圖書的視圖cs_book :sql: create view cs_book asselect *from bookwhere in(select from book_sortwhere sort_id = 1);操作和結(jié)果顯示：mysql> create view cs_book asselect *from bookwhere in(selectfrom book_sortwh

18、ere sort_id = 1);Query OK, 0 rows affectedbook.autharboo<pubbgkjiumbook.sortbocVecord1EEpuTET nenvoric制Hharp(ih a15?01M2-2fl 1fi:55572亡cimput型operatimautbi3r_bpubb1cc2016-12 28 lti:5S:59二intcmiiation securitj1author cpub a1C£2016-12-28 15：5B：374software onghwring041thor_dpub.d1C52316 12 28 1

19、5i59i1O5七十十 plus leanirga-utbcrepub日1201 5 121515Hsec proq-flmmngiautbore?p<jb_a1cs201 后 12 團(tuán) 103&567sqla uthor.dwb工1CS20112 26 17;帥 47bgk_d4 .創(chuàng)建個(gè)人所有借書歸還紀(jì)錄視圖stu_borrow_return:sql:create view stu_borrow_return asselect ,from student, book, return_tablewhere = and =;stu_id stu_name bookjd bock_

20、nameborrow_datereturr_dste>stu_a1 computer network 2016-10-1617rD7:182016-12-2916t56:505、設(shè)計(jì)觸發(fā)器給出在各表上建立的觸發(fā)器以及使用的語(yǔ)句。1.設(shè)計(jì)觸發(fā)器borrow,當(dāng)某學(xué)生借書成功后，圖書表相應(yīng)的圖書不在架上，變?yōu)?0： sql:create trigger borrow after insert on borrowfor each rowbeginupdate book set book_num = book_num - 1 where book_id =;end操作與結(jié)果顯示：mysql>

21、; delimiter $mysql> create trigger trigger_borrow- > after insert on borrow- > for each row- > begin- > update book set book_num = book_num - 1- > where book_id =;- > end- > $Query OK, 0 rows affected在插入表 borrow 之前，book_id = 1的圖書還在架上，為 1 ：book.idbco< n-amebcokauthorbook_pu

22、bIbcok. numbooksortbook record1 computer n&t-vorkauthor_apub_i1 C52D1&-12-281&55:學(xué)生1借了這本書后，在 borrow中插入了一條記錄:stujd bookid borrow_date2016-13-28 177；在borrow中插入這條記錄后，book_id =1的圖書，不在架上，為 0:bookjdboclcnamebook_audiorbo&k_pubbcok.niirnboolc_iortt>cok_record1 con puier networkaunor_apu”

23、0£015-12-26 1655：2 .設(shè)計(jì)觸發(fā)器trigger_return, 還書成功后，對(duì)應(yīng)的書籍 book_num變?yōu)? : sql:create trigger trigger_returnafter insert on return_table for each rowbeginupdate book set book_num = book_num + 1 where book_id =;end還書時(shí)在return table插入表項(xiàng)：&tu_idbook_idreturn_date1此時(shí)圖書歸還架上:book idboo kji am ebooL_aiuthor

24、bookjpLibbook_sortbookj ecordJcomputef networkauthorpub占1 口2016-1228 1a553 .定義定時(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:create event if not exists eventJobon schedule every 1 DAY/* 每天觸發(fā) */on completion PRESERVEdo call proc_gen

25、_ticket(getdate(); /*調(diào)用存儲(chǔ)過(guò)程 */set global event_scheduler = 1;alter event eventJob on completion preserve enable; /*開啟定時(shí)器*/操作和結(jié)果顯示：1).學(xué)生1借了圖書1,生成借書記錄 stu_borrow視圖，如下：stujd book id stu_namebook name borrow dateexpect_return_date1 stu_acomputer netwo 2016-12-23177 2017-01-27 17:07:1183).當(dāng)他在1月27日后還書時(shí)，生成

26、罰單:stujd book id return date2017-02-23 21：rtu id book id over date ticket fee4.設(shè)計(jì)觸發(fā)器trigger_credit ,若處罰記錄超過(guò) 30條，則將這個(gè)學(xué)生的誠(chéng)信級(jí)設(shè)置為0,下次不允許借書：sql:create trigger trigger_creditafter insert on ticket for each rowbeginif (select count(*) from ticket where stu_id=>30 thenupdate student set stu_integrity = 0

27、 where stu_id =;end if;end操作和結(jié)果顯示，測(cè)試時(shí)選擇插入ticket項(xiàng)大于3,因?yàn)?0太大了，不容易測(cè)試:學(xué)生1超過(guò)3次超期歸還圖書后，產(chǎn)生了 4條罰單：stujdbookjdover_dateticket_fee1132312311J42151此時(shí)觸動(dòng)觸發(fā)器trigger_credit ,將學(xué)生1的誠(chéng)信級(jí)設(shè)置為0 :stu_idstu_namestu_sexitu_aqestu_p rostu_qradestu_integritym20CS201402 qtu_bm21cs20141四、應(yīng)用程序設(shè)計(jì)與編碼實(shí)現(xiàn)1、系統(tǒng)實(shí)現(xiàn)中存儲(chǔ)函數(shù)和存儲(chǔ)過(guò)程的設(shè)計(jì)要求給出功能描述和代

28、碼。1 .設(shè)計(jì)存儲(chǔ)過(guò)程，產(chǎn)生罰單proc_gen_ticket :當(dāng)日期超過(guò)預(yù)定歸還日期時(shí)，產(chǎn)生罰單，并將記錄寫入表ticket中，這個(gè)存儲(chǔ)過(guò)程在定時(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) select stu_id, book_id, datediff(

29、cur_date,*datediff(cur_date, from stu_borrowwhere cur_date>end操作和結(jié)果顯示：1) .學(xué)生1借了圖書1,生成借書記錄 stu_borrow視圖，如下：5tujd bock id 5tu_namebook nameborrow date expect_retum_date1 stu_acomputer netwo 2016-12-28 1707 2017 01 -27 17;O7門82) .當(dāng)他在1月27日前還書時(shí)，沒(méi)有生成罰單:Null)3) .當(dāng)他在1月27日后還書時(shí)，生成罰單:stujd bock id returrdat

30、e2017-02-23 21:5rtu_idback idqy年r_d n收ticket3i13232 .設(shè)計(jì)學(xué)生注冊(cè)信息存儲(chǔ)過(guò)程：學(xué)生注冊(cè)信息stu_registersql: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, st

31、u_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:create procedure ma_register(in ma_id int, in ma_name varchar(20), in ma_age int, in ma_phone int)BEGINinsert into manager values(ma_id, ma_name, ma_age, ma_phone); END4 .借書過(guò)程

32、的實(shí)現(xiàn)：1) 設(shè)計(jì)存儲(chǔ)函數(shù)，func_get_credit ,返回學(xué)生的誠(chéng)信級(jí)：create function func_get_credit(stu_id int) returns int beginreturn(select stu_integrity from student where = stu_id); end2) 設(shè)計(jì)存儲(chǔ)函數(shù)，func_get_booknum ,返回書籍是否在架上：create function func_get_booknum(book_id int) returns int beginreturn(select book_num from book wher

33、e = book_id); end3) 設(shè)計(jì)存儲(chǔ)過(guò)程 proc_borrow ,調(diào)用 func_get_credit 和 func_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

34、borrowvalues(stu_id, book_id, borrow_date); elseselect 'failed to borrow'end if;end實(shí)驗(yàn)操作與結(jié)果顯示:borrow紀(jì)錄為空：stujdbookidborrow dateF(Null)(Null)執(zhí)行函數(shù)，學(xué)生1借圖書2:call proc_borrow(1,2,now();學(xué)生1的誠(chéng)信級(jí)為0:stuid5tLi_namestu sex5tu_age-5tu_prcstugrddestujntegrity1m2。C520141此時(shí)借書成功：stu_id bookjd borrowdate卜2 20

35、16-12-29 17：315 .還書存儲(chǔ)過(guò)程 proc_return :當(dāng)還書時(shí)，查看是否書是否超期，即查詢ticket表項(xiàng)，當(dāng)發(fā)現(xiàn)超期，提示交罰單后再次還書, 如沒(méi)有超期，則紀(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 datetime;if (select payoff from ticket where = st

36、u_id and =book_id) = 1 then /*判斷是否交了罰單，1表示沒(méi)有交*/select 'please pay off the ticket'else /*紀(jì)錄歸還項(xiàng)目到return_table中，并且刪除借書紀(jì)錄 */set borrowdate = (select borrow_date from borrow where= stu_id andbook_id);insert into return_tablevalues(stu_id, book_id, borrowdate, return_date);delete from borrowwhere

37、= stu_id and = book_id;end if;end實(shí)驗(yàn)操作與結(jié)果顯示：學(xué)生1借了圖書2:stujd bookjdborrowed ate2 2016-12-29 17:31超期產(chǎn)生了罰單，沒(méi)有交罰單，payoff=1 :stu idbookjd口 fer_d。也ticket Jeepayoff14440122831132221此時(shí)調(diào)用還書過(guò)程：call proc_return(1,2, now();提示交罰單：please pay off the ticketplease pay off the ticket交罰單，調(diào)用 proc_payoff: call proc_payof

38、f(1,2);交罰單成功，payoff = 0 ;此時(shí)再次調(diào)用還書過(guò)程：call proc_return(1,2, now();還書成功，在return_table生成了還書紀(jì)錄:stujd btok_ld borrow_date returndate 1 2016-10-16 17:07 2016 12-29 16:512 20HJ2-2S 17;31 ZQ11Z-Z9 1946 .交罰單存儲(chǔ)過(guò)程：修改罰單中payoff段為0,表明罰單已交：create procedure proc_payoff(in stuid int, in bookid int)beginupdate tickets

39、et payoff = 0where = stuid and = bookid;select 'succeed '；end交罰單，調(diào)用 proc_payoff:call proc_payoff(1,2);交罰單成功，payoff = 0 ;5tLi id bookjd aver_ddte li<ketjee payoff2、功能實(shí)現(xiàn)按各功能模塊進(jìn)行描述。要求：畫出流程圖并給出實(shí)現(xiàn)代碼。創(chuàng)建學(xué)生統(tǒng)一賬戶，賬戶名：student_account ,并且授予權(quán)限：sql:create user 'student_account''localhost

40、9;grant insert,select on student to 'student_account''localhost'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''localh

41、ost'grant select on ticket to 'student_account''localhost'創(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'查詢圖書信息按書名查找：sele

42、ct * from book where book_name ='sql'；laysqiy aelact " frait hoc )c wh&re book name = ' s(jl1;-I11J1-1I tjoolc_id| t?ook_nan;e|Lookantlior|t?OQk_puti| baolc_nuiii|booX_sort+-+-|7 | sql| author d | puti_c |1 | cs-4 +=" -+-4- 卜-+-一一按作者查找：select * from book where book_author =

43、 author '；mysql> select * from 匕口口比 where boolc author " * author cf;|hciok:_id | boolc_narue| booautharI t)cjok_pkih|boalc_nuiii I h4-4 +- +-|3 | inforinatioTisecurity | autiior_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();命令行操作：Ttysql> call(lr E, n.aw();Query OK, 1 tow affecced表 borrow :5tujd bookjd borrowdate視圖 stu_borrow:表 book :k5 2016-12-29 2049bookname borrow_dateexp ectretu rn_d ate0 e2O16-12-2G 15-59;C + + plus l&arnin 2016 12-29 20:39 2017-01-28 20:39235