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

1、圖書管理系統(tǒng)數(shù)據(jù)庫(kù)設(shè)計(jì) M Y SQ L 實(shí)現(xiàn)標(biāo)準(zhǔn)化管理部編碼-99968T-6889628-J68568-1689N圖書管理系統(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ù)能夠給圖書管理帶來(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í)

2、對(duì)照學(xué)生信息。4. 學(xué)生直接歸還圖書，根據(jù)圖書編碼修改借閱信息5. 管理員登陸管理系統(tǒng)后，可以修改圖書信息，增加或者刪除圖書信息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é)生借閱-歸還 E-R 圖： 管理員 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

3、_namevarcharnot null學(xué)生姓名stu_sexvarcharnot null學(xué)生性別stu_ageintnot null學(xué)生年齡stu_provarcharnot null學(xué)生專業(yè)stu_gradevarcharnot null學(xué)生年級(jí)stu_integrityintnot學(xué)生誠(chéng)信級(jí)null/default=1book:列名數(shù)據(jù)類型是否為空/性質(zhì)說(shuō)明book_idintnot null / PK唯一書籍序號(hào)book_namevarcharnot null書籍名稱book_authorvarcharnot null書籍作者book_pubvarcharnot null書籍出版社b

4、ook_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ù)類型是否為空/性質(zhì)說(shuō)明student_idvarcharnot null / PK學(xué)生編號(hào)book_idvarcharnot null / PK書籍編號(hào)borrow_datedatatimenull借書時(shí)間expect_retu

5、rn_datedatetimenull預(yù)期歸還時(shí)間return_table:存儲(chǔ)學(xué)生的歸還信息列名數(shù)據(jù)類型是否為空/性質(zhì)說(shuō)明student_idvarcharnot null / PK學(xué)生編號(hào)book_idvarcharnot null / PK書籍編號(hào)borrow_datedatetimenull借書時(shí)間return_datedatatimenull實(shí)際還書時(shí)間ticket:存儲(chǔ)學(xué)生的罰單信息列名數(shù)據(jù)類型是否為空/性質(zhì)說(shuō)明student_idvarcharnot null / PK學(xué)生編號(hào)book_idvarcharnot null / PK書籍編號(hào)over_dateintnull超期天數(shù)t

6、icket_feefloatnull處罰金額manager:列名數(shù)據(jù)類型是否為空/性質(zhì)說(shuō)明manager_idvarcharnot null / PK管理員編號(hào)manager_namevarcharnot null管理員姓名manager_agevarcharnot null管理員年齡manager_phonevarcharnot null管理員電話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)建索引，并且降

7、序排序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: 0Duplicates: 0Warnings: 0mysql> alter table studentadd indexindex_name(stu_name desc);Query OK, 0 rows affectedRecords: 0Duplica

8、tes: 0Warnings:0mysql>book:1. 為 book_id 創(chuàng)建索引，升序排列sql:create index index_bid on book(book_id);2. 為 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: 0Duplicate

9、s: 0Warnings: 0mysql> create index index_brecord on book(book_record); Query OK, 0 rows affectedRecords: 0Duplicates: 0Warnings: 0 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(st

10、u_id asc, book_id asc); Query OK, 0 rows affectedRecords: 0Duplicates: 0Warnings: 0 return_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 on return_table(stu_id asc, book_id asc);Query O

11、K, 0 rows affectedRecords: 0Duplicates: 0Warnings: 0 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 index_sid_bid on ticket(stu_id asc, book_id asc); Query OK, 0 rows affectedRecords: 0Duplicates: 0Warnings

12、: 0 manager:1.為 manager_id 創(chuàng)建索引：sql:create index index_mid on manager(manager_id); 插入索引的操作和結(jié)果如下所示：mysql> create index index_mid on manager(manager_id); Query OK, 0 rows affectedRecords: 0Duplicates: 0Warnings: 04、設(shè)計(jì)視圖給出在各表上建立的視圖以及使用的語(yǔ)句。1. 在表 student 上創(chuàng)建計(jì)算機(jī)專業(yè)(cs)學(xué)生的視圖 stu_cs： sql: create view stu_

13、cs asselect * from studentwhere pro = cs; 操作和結(jié)果：mysql>create view stu_cs as select *from studentwhere stu_pro = 'cs' Query OK, 0 rows affected2. 在表 student, borrow 和 book 上創(chuàng)建借書者的全面信息視圖 stu_borrow： sql: create view stu_borrow asselect student.stu_id, book.book_id, student.stu_name, book.bo

14、ok_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.book_name, borrow_date，adddate(borrow_d

15、ate,30) expect_return_datefrom student, book, borrowwhere student.stu_id = borrow.stu_id and book.book_id = borrow.book_id;Query OK, 0 rows affected3. 創(chuàng)建類別 1 的所有圖書的視圖 cs_book： sql: create view cs_book asselect * from bookwhere book.book_sort in from book_sortwhere sort_id = 1); 操作和結(jié)果顯示：mysql>crea

16、te view cs_book as select *from bookwhere book.book_sort in (select book_sort.sort_name from book_sortwhere sort_id = 1); Query OK, 0 rows affected4. 創(chuàng)建個(gè)人所有借書歸還紀(jì)錄視圖 stu_borrow_return: sql:create view stu_borrow_return asselect student.stu_id, student.stu_name, book.book_id, book.book_name,return_tab

17、le.borrow_date,return_table.return_date from student, book, return_tablewhere student.stu_id = return_table.stu_id and book.book_id = return_table.book_id;5、設(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 borrow for each rowbeginupdate

18、 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-> $Query OK, 0 rows

19、 affected在插入表 borrow 之前，book_id = 1 的圖書還在架上，為 1： 學(xué)生 1 借了這本書后，在 borrow 中插入了一條記錄：在 borrow 中插入這條記錄后，book_id =1 的圖書，不在架上，為 0：2. 設(shè)計(jì)觸發(fā)器 trigger_return, 還書成功后，對(duì)應(yīng)的書籍book_num 變?yōu)?1： sql:create trigger trigger_return after insert on return_table for each rowbeginupdate book set book_num = book_num + 1 where bo

20、ok_id = new.book_id;end還書時(shí)在 return_table 插入表項(xiàng)： 此時(shí)圖書歸還架上：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:create event if not exists eventJobon schedule every 1 DAY/*每天觸發(fā)*/ on completion PRESERVEdo call proc_gen_ticket(getdate(); /*調(diào)用存

21、儲(chǔ)過(guò)程*/ set global event_scheduler = 1;alter event eventJob on completion preserve enable; /*開啟定時(shí)器*/操作和結(jié)果顯示：1). 學(xué)生 1 借了圖書 1，生成借書記錄 stu_borrow 視圖，如下：2). 當(dāng)他在 1 月 27 日前還書時(shí)，沒(méi)有生成罰單：3). 當(dāng)他在 1 月 27 日后還書時(shí)，生成罰單：4. 設(shè)計(jì)觸發(fā)器 trigger_credit，若處罰記錄超過(guò) 30 條，則將這個(gè)學(xué)生的誠(chéng)信級(jí)設(shè)置為 0，下次不允許借書：sql:create trigger trigger_credit after

22、 insert on ticketfor each row beginthen new.stu_id;endif (select count(*) from ticket where stu_id=new.stu_id)>30 update student set stu_integrity = 0 where stu_id =end if;操作和結(jié)果顯示，測(cè)試時(shí)選擇插入 ticket 項(xiàng)大于 3，因?yàn)?30 太大了，不容易測(cè)試： 學(xué)生 1 超過(guò) 3 次超期歸還圖書后，產(chǎn)生了 4 條罰單：此時(shí)觸動(dòng)觸發(fā)器 trigger_credit，將學(xué)生 1 的誠(chéng)信級(jí)設(shè)置為 0：四、應(yīng)用程序設(shè)計(jì)與編碼

23、實(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)生罰單，并將記錄寫入表 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_f

24、ee) select stu_id, book_id,datediff(cur_date,stu_borrow.expect_return_date),0.1*datediff(cur_date,s tu_borrow.expect_return_date)from stu_borrowwhere cur_date>stu_borrow.expect_return_date;end操作和結(jié)果顯示：1). 學(xué)生 1 借了圖書 1，生成借書記錄 stu_borrow 視圖，如下：2). 當(dāng)他在 1 月 27 日前還書時(shí)，沒(méi)有生成罰單：3). 當(dāng)他在 1 月 27 日后還書時(shí)，生成罰單：2.

25、設(shè)計(jì)學(xué)生注冊(cè)信息存儲(chǔ)過(guò)程：學(xué)生注冊(cè)信息 stu_register sql: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,

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

27、n 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，返回書籍是否在架上：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ǔ)

28、過(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 thenelseinsert into borrowvalues(stu_id, b

29、ook_id, borrow_date);select 'failed to borrow'end if;end實(shí)驗(yàn)操作與結(jié)果顯示： borrow 紀(jì)錄為空：執(zhí)行函數(shù)，學(xué)生 1 借圖書 2： call proc_borrow(1,2,now(); 學(xué)生 1 的誠(chéng)信級(jí)為 0：借書失?。盒薷膶W(xué)生 1 誠(chéng)信級(jí)為 1： 此時(shí)借書成功：5. 還書存儲(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ì)錄）： sq

30、l：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 then /*判斷是否交了罰單，1 表示沒(méi)有交*/select 'please pay off the ticket'else /*紀(jì)錄歸還項(xiàng)目到 return_t

31、able 中，并且刪除借書紀(jì)錄*/ set borrowdate = (select borrow_date from borrow whereborrow.stu_id = stu_id and borrow.book_id = 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;endend if;實(shí)驗(yàn)操作與結(jié)果顯示： 學(xué)生 1

32、借了圖書 2：超期產(chǎn)生了罰單，沒(méi)有交罰單，payoff=1： 此時(shí)調(diào)用還書過(guò)程：call proc_return(1, 2, now(); 提示交罰單：交罰單，調(diào)用 proc_payoff:call proc_payoff(1, 2); 交罰單成功，payoff = 0； 此時(shí)再次調(diào)用還書過(guò)程：call proc_return(1, 2, now();還書成功，在 return_table 生成了還書紀(jì)錄：6. 交罰單存儲(chǔ)過(guò)程：修改罰單中 payoff 段為 0，表明罰單已交：create procedure proc_payoff(in stuid int, in bookid int) b

33、eginendupdate ticket set payoff = 0where ticket.stu_id = stuid and ticket.book_id = bookid; select succeed;交罰單，調(diào)用 proc_payoff:call proc_payoff(1, 2); 交罰單成功，payoff = 0；2、功能實(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''localhost' grant select on book to 'student_account''localhost'grant insert,select on borrow to 'student_accoun