1、言簡(jiǎn)意賅，遠(yuǎn)見(jiàn)卓識(shí)，望君采納，謝謝！刪除水印可，編輯頁(yè)眉，選中水印，點(diǎn)擊刪除。圖書(shū)管理系統(tǒng)數(shù)據(jù)庫(kù)設(shè)計(jì)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ū)編碼

2、修改借閱信息5 .管理員登陸管理系統(tǒng)后，可以修改圖書(shū)信息，增加或者刪除圖書(shū)信息6 .管理員可以注銷(xiāo)學(xué)生信息。通過(guò)需求定義，畫(huà)出圖書(shū)管理系統(tǒng)的數(shù)據(jù)流圖：23數(shù)據(jù)流圖借閱信息學(xué)生學(xué)生信息系統(tǒng)功能設(shè)計(jì)畫(huà)出系統(tǒng)功能模塊圖并用文字對(duì)各功能模塊進(jìn)行詳細(xì)介紹。三、數(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é)生（學(xué)生ID入/歸還時(shí)間一圖書(shū)歸還表上/（圖書(shū)ID :/借閱（圖捫。力處罰表、/ X, / ,（學(xué)生id）（超期3處罰金額:_ create index index_id on student(stu_id asc);Quer

3、y OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 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欄位indlei_idiridenarnestu_r3rnebook:1 .為book_id創(chuàng)建索引，升序排列sql:c

4、reate index index_bid on book(book_id);2 .為book_record創(chuàng)建索引，以便方便查詢(xún)圖書(shū)的登記日期信息，升序: 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_r

5、ecord);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0欄侵 索引 外特 融意署選項(xiàng) 注降 QL碰慮至弓后去BTREEBTREE當(dāng)欄位需引樊型boo k_idhlcrmlmdejcbrecordbookrecordNornnal 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

6、on borrow(stu_id asc, book_id asc);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0欄構(gòu) 索另 外鍵 融妗器 領(lǐng) 注理弓QL逆真索弓I昊里NormalNormal塞弓后去BTREEBTREE昌欄位oook idindex_sid_bidstu_idt book id return_table:1.為stu_id和book_id創(chuàng)建多列索引： sql:create index index_sid_bid on return_table(stu_id asc, book_id asc); 插

7、入索引的操作和結(jié)果如下所示：mysql 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);插

8、入索引的操作和結(jié)果如下所示：mysql create index index_sid_bid on ticket(stu_id asc, book_id asc);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0欄化 塞引 外凝 倏宜器SIG 注毒 GL儂 manager:奏引美型 Normal室引方法BTREE1.為manager_id 創(chuàng)建索引：sql:create index index_mid on manager(manager_id);插入索引的操作和結(jié)果如下所示:mysql create index ind

9、ex_mid on manager(manager_id);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0欄位 裳弓 外鎮(zhèn) 觸箕善 正當(dāng) 主售 WOL藥監(jiān)managerjd索引法聚弓方市NcrmalBTREE4、設(shè)計(jì)視圖給出在各表上建立的視圖以及使用的語(yǔ)句。1 .在表student上創(chuàng)建計(jì)算機(jī)專(zhuān)業(yè)(cs)學(xué)生的視圖stu_cs ： sql: create view stu_cs asselect *from studentwhere pro = cs；操作和結(jié)果：mysql create view stu_cs ass

10、elect *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)建借書(shū)者的全面信息視圖stu_borrow :sql: create view stu_borrow asselect student.stu_id,

11、 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_name, book.boo

12、k_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_idlttu_namebook_nameborrow_dateexpect_retu rn_d ate1computer netwo 2016-12-28 17:07 2017-OV27 17iO7;103 .

13、創(chuàng)建類(lèi)別1的所有圖書(shū)的視圖 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 sort_id = 1);Query OK, 0 r

14、ows affectedbook汨booknanniebDoikauthorhootjjubbookjiLimbdCKSCrTbock_rer ccmpiJter nefevcrtcsuthQr 3pub.a1 cs2016-12.28 1665:572computer opcrcticnauthor bpub b1 c201B 12-28 1615gl393 mf&rrndtiafi .己outlhr_cpub_fl1 ca2015-12-2ST515&374 software engineer3gauthDr_dpub_d1 cs之川，也之8 15:55:1054一 phs learnin

15、g3utbor_epuba1 C52016-12-2515:59556c prcgrannnningiauthor pub日小7sqlamthgr_dPb_c1 a201悟端17rfXM74 .創(chuàng)建個(gè)人所有借書(shū)歸還紀(jì)錄視圖stu_borrow_return:sql:create view stu_borrow_return asselectstudent.stu_id,student.stu_name,book.book_id,book.book_name,return_table.borrow_date,return_table.return_datefrom student, book,

16、return_tablewhere student.stu_id = return_table.stu_id and book.book_id = return_table.book_id;stujd stu_name Ijookjd lQck_nameborrow_d3tereturndwtEstu_a1 computer network 2016-10-1617rD7:182016-12-2916t56;505、設(shè)計(jì)觸發(fā)器給出在各表上建立的觸發(fā)器以及使用的語(yǔ)句。1 .設(shè)計(jì)觸發(fā)器borrow,當(dāng)某學(xué)生借書(shū)成功后，圖書(shū)表相應(yīng)的圖書(shū)不在架上，變?yōu)?sql:create trigger borro

17、w after insert on borrowfor each rowbeginupdate book set book_num = book_num - 1 where book_id = new.book_id;end操作與結(jié)果顯示：mysql 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 = new.book_id;- end- $Quer

18、y OK, 0 rows affected在插入表 borrow 之前，book_id = 1的圖書(shū)還在架上，為 1：book_id bcok_name book_author bookpub bcok_num boolksort 1 computer network duthor_apub 31 學(xué)生1借了這本書(shū)后，在 borrow中插入了一條記錄：stujd bookid borrow_d3te201 612-2B 17：07在borrow中插入這條記錄后，book_id =1的圖書(shū)，不在架上，為 0:hookjd bock_rrame book_dudior book_pub bcokn

19、urn book_ort 1 computer network aunor_a pub_a0 g0:LciDk_record201&-1220 沅55;tcok_reeord2 .設(shè)計(jì)觸發(fā)器trigger_return,還書(shū)成功后，對(duì)應(yīng)的書(shū)籍 book_num變?yōu)? :sql:create trigger trigger_returnafter insert on returntablefor each rowbeginupdate book set book_num = book_num + 1 where book_id = new.book_id;end還書(shū)時(shí)在return_table插

20、入表項(xiàng):sHiJd bookid returndate陽(yáng) 6-12-28 1&5此時(shí)圖書(shū)歸還架上:book namebook idlcompuiter networkbookauthor author abookjpLibbock rurrbook sortbookecord2016-12-28 1a 553 .定義定時(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

21、eventJobon schedule every 1 DAY /* 每天觸發(fā) */on completion PRESERVEdo call proc_gen_ticket(getdate(); /* 調(diào)用存儲(chǔ)過(guò)程 */set global event_scheduler = 1;alter event eventJob on completion preserve enable; /*開(kāi)啟定時(shí)器*/操作和結(jié)果顯示：1) .學(xué)生1借了圖書(shū)1,生成借書(shū)記錄 stu_borrow視圖，如下：stujd book id 5tu_namebook name borrow date expect_re

22、turn_date1 stu.acompirter netwo 2016-12-28 1707 2017-01 -27 17;O7;182) .當(dāng)他在1月27日前還書(shū)時(shí)，沒(méi)有生成罰單：5tu id bo ok id return _d ate2016-12-30 21stu id boo k id aver darte ticket fee(Null3) .當(dāng)他在1月27日后還書(shū)時(shí)，生成罰單:stu_id bock_id returr_date2D17-O2-2S 21：rtu_idbookidover-dateticket_fee13224 .設(shè)計(jì)觸發(fā)器trigger_credit ,若處罰

23、記錄超過(guò) 30條，則將這個(gè)學(xué)生的誠(chéng)信級(jí)設(shè)置為0,下次不允許借書(shū)：sql:create trigger trigger_credit after insert on ticketfor each rowbeginif (select count(*) from ticket where stu_id=new.stujd)30 thenupdate student set 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超過(guò)3次超期歸還圖書(shū)后，產(chǎn)生了 4

24、條罰單：stujdbook_idover_dateticketjee11323123112421531此時(shí)觸動(dòng)觸發(fā)器trigger_credit ,將學(xué)生1的誠(chéng)信級(jí)設(shè)置為0 :stujdtu_nameitu_agestu_prostu_gradestu_in-tegritynu am20C5201402stu_bm21CS20141四、應(yīng)用程序設(shè)計(jì)與編碼實(shí)現(xiàn)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

25、: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_id,book_id,datediff(cur_date,stu_borrow.expect_return_date),0.1*datediff(cur_date,stu_borrow.expect_ return_date)fr

26、om stu_borrowwhere cur_datestu_borrow.expect_return_date;end操作和結(jié)果顯示：1) .學(xué)生1借了圖書(shū)1,生成借書(shū)記錄 stu_borrow視圖，如下：stujcl book id stu_namebook name borrow date expect_retum_date1 stu_acomputer netwo 201 &-12-28 1707 2017 01 -27 17:07:102) .當(dāng)他在1月27日前還書(shū)時(shí)，沒(méi)有生成罰單：5tu_idbo ok id re-turn_date2016-12-i0 21 11stujdboo

27、 k id over darte ticket fee(Null)(Mull)；3) .當(dāng)他在1月27日后還書(shū)時(shí)，生成罰單：stujd book id returr date2D17-02-23 Z1:stu_idbook_idover-dateticket_fee113232 .設(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 var

28、char(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_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

29、_phone int)BEGINinsert into manager values(ma_id, ma_name, ma_age, ma_phone);END4 .借書(shū)過(guò)程的實(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 student.stu_id = stu_id);end2) 設(shè)計(jì)存儲(chǔ)函數(shù)，func_get_booknum ,返回書(shū)籍是否在架

30、上：create function func_get_booknum(book_id int) returns int beginreturn(select book_num from book where book.book_id = book_id); end3) 設(shè)計(jì)存儲(chǔ)過(guò)程 proc_borrow ,調(diào)用 func_get_credit 和 func_get_booknum ,判斷這個(gè)學(xué)生誠(chéng)信度和書(shū)籍是否在架上，若為真，則借書(shū)成功，在borrrow表中插入紀(jì)錄；否則提示失敗：create procedure proc_borrow(in stu_id int, in book_id i

31、nt, in borrow_date datetime) beginif func_get_credit(stu_id) = 1 and func_get_booknum(book_id) = 1 then insert into borrowvalues(stu_id, book_id, borrow_date); elseselect failed to borrow;end if;end實(shí)驗(yàn)操作與結(jié)果顯示：borrow紀(jì)錄為空：rtu id book id borrow date(Null)執(zhí)行函數(shù)，學(xué)生1借圖書(shū)2:call proc_borrow(1,2,now();學(xué)生1的誠(chéng)信級(jí)為0

32、:stu idstu_narieStLJ E&K 5tu_a g estj_prpstugradestu Jntegnty*KI stu aIYI20 n20140借書(shū)失敗：信息 結(jié)果1 版兄 狀態(tài)failed to borrow failed to borrow修改學(xué)生1誠(chéng)信級(jí)為1 :stujdstu_nsmestu_sexstu_agestuprostu_gradestuj integrity1Stu_BmCS20141此時(shí)借書(shū)成功:stu_id bookidborrowdate2 2016-12-29 17:315 .還書(shū)存儲(chǔ)過(guò)程 proc_return :當(dāng)還書(shū)時(shí)，查看是否書(shū)是否超期，

33、即查詢(xún)ticket表項(xiàng)，當(dāng)發(fā)現(xiàn)超期，提示交罰單后再次還書(shū)， 如沒(méi)有超期，則紀(jì)錄歸還項(xiàng)目到 return_table中，并且刪除借書(shū)紀(jì)錄(以免還書(shū)后定時(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 ticket.stu_id = stu_id and ticket.book_id=book_id)=1 th

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

35、orrow.stu_id = stu_id and borrow.book_id = book_id; end if;end實(shí)驗(yàn)操作與結(jié)果顯示：學(xué)生1借了圖書(shū)2:stujd bookjd borrow_date2 2016-12-2Q 1 市1超期產(chǎn)生了罰單，沒(méi)有交罰單，payoff=1stujdbookjdove-r_dateticket Jeepayoff1444012N1132221此時(shí)調(diào)用還書(shū)過(guò)程：call proc_return(1,2, now();提示交罰單：please pay off the ticlc&tplease pay off the ticket交罰單，調(diào)用 pro

36、c_payoff: call proc_payoff(1,2);交罰單成功，payoff = 0 ;stu idbook idover_dateticketfeepayoffMl14440i22830i32221此時(shí)再次調(diào)用還書(shū)過(guò)程：call proc_return(1,2, now();還書(shū)成功，在return_table生成了還書(shū)紀(jì)錄:stujdboo kJ dborrow_date returndate1 2016*10-16 17:07 2016 12-29 16512 20H-12-2S 17;31 2015-12-29 19;46.交罰單存儲(chǔ)過(guò)程：修改罰單中payoff段為0,表明

37、罰單已交：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: call proc_payoff(1,2);交罰單成功，payoff = 0 ;2、功能實(shí)現(xiàn)按各功能模塊進(jìn)行描述。要求：畫(huà)出流程圖并給出實(shí)現(xiàn)代碼。創(chuàng)建學(xué)生統(tǒng)一賬戶(hù)，賬戶(hù)名：student_account ,并且授予權(quán)限：

38、sql:create user student_accountlocalhost;grant insert,select on student to student_accountlocalhost;grant select on book to student_accountlocalhost;grant insert,select on borrow to student_accountlocalhost;grant insert,select on return_table to student_accountlocalhost;grant select on ticket to stu

39、dent_accountlocalhost;創(chuàng)建管理員統(tǒng)一賬戶(hù)，賬戶(hù)名：manager_account,并且授予全部權(quán)限:sql:create user manager_accountlocalhost identified by 123;grant all on library_management to manager_accountlocalhost;查詢(xún)圖書(shū)信息按書(shū)名查找：select * from book where book_name = sql ；aelecc from book where took_narce = * bql ; H seleat * from boolc where boolc author _ * mthor c; 4-+-d-4+| book_ii | boak_narne| booh：_aiichar I fc)QQk_pkifc | booh：_niini | h44+4-一一|3 | inforiaation security | autlior_c | pub_a. |1 | c+1-+H卜+ 一借書(shū)功能：proc_borrow(in stu_id int, in book_id int, in borrow_date datetime)如