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

1、精選優(yōu)質(zhì)文檔-傾情為你奉上圖書管理系統(tǒng)數(shù)據(jù)庫設(shè)計一、系統(tǒng)概述1、系統(tǒng)簡介圖書管理是每個圖書館都需要進行的工作。一個設(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ù)流圖：數(shù)據(jù)流圖二、系統(tǒng)功能設(shè)計 畫出系統(tǒng)功能模塊圖并用文字對各功能模塊進行詳細(xì)介紹。系統(tǒng)功能模塊圖：三、數(shù)據(jù)庫設(shè)計方案圖表 1、系統(tǒng)E-R模型總體E-R圖：精細(xì)化的局部E-R圖：學(xué)生借閱-歸還E-R圖：管理員E-R圖： 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_ageintno

3、t null學(xué)生年齡stu_provarcharnot null學(xué)生專業(yè)stu_gradevarcharnot null學(xué)生年級stu_integrityintnot null/default=1學(xué)生誠信級book:列名數(shù)據(jù)類型是否為空/性質(zhì)說明book_idintnot null / PK唯一書籍序號book_namevarcharnot null書籍名稱book_authorvarcharnot null書籍作者book_pubvarcharnot null書籍出版社book_numint not null 書籍是否在架上book_sortvarcharnot null書籍分類book_r

4、ecorddatatimenull書籍登記日期book_sort:列名數(shù)據(jù)類型是否為空/性質(zhì)說明sort_idvarcharnot null / PK類型編號sort_namevarcharnot null類型名稱borrow:存儲學(xué)生的借書信息列名數(shù)據(jù)類型是否為空/性質(zhì)說明student_idvarcharnot null / PK學(xué)生編號book_idvarchar not null / PK書籍編號borrow_datedatatimenull借書時間expect_return_datedatetimenull預(yù)期歸還時間return_table:存儲學(xué)生的歸還信息列名數(shù)據(jù)類型是否為空/

5、性質(zhì)說明student_idvarcharnot null / PK學(xué)生編號book_idvarchar not null / PK書籍編號borrow_datedatetimenull借書時間return_datedatatimenull實際還書時間ticket:存儲學(xué)生的罰單信息列名數(shù)據(jù)類型是否為空/性質(zhì)說明student_idvarcharnot null / PK學(xué)生編號book_idvarchar not null / PK書籍編號over_dateintnull超期天數(shù)ticket_feefloatnull處罰金額manager:列名數(shù)據(jù)類型是否為空/性質(zhì)說明manager_idv

6、archarnot null / PK管理員編號manager_namevarchar not null 管理員姓名manager_agevarcharnot null管理員年齡manager_phonevarcharnot null管理員電話 3、設(shè)計索引給出在各表上建立的索引以及使用的語句。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,

7、desc);插入索引操作和結(jié)果如下所示：mysql> create index index_id on student(stu_id asc);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0mysql> alter table student add index index_name(stu_name desc);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0mysql>book:1.為book_id創(chuàng)建索引，升序排列sql:

8、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: 0 Duplicates: 0 Warnings: 0mysql> create index index_brecord on book

9、(book_record);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0borrow: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 rows affectedRecords: 0 Dupli

10、cates: 0 Warnings: 0return_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 OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0ticket:1. 為stu_

11、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: 0 Duplicates: 0 Warnings: 0manager:1.為manager_id創(chuàng)建索引：sql:create index index_mid on manager(manag

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

13、elect *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.book_name, borrow_date，adddate(borrow_date,30) expect_return_datefrom student, book, borr

14、owwhere 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_date,30) expect_return_datefrom student, book, borrowwhere student.stu_id = borrow.stu_i

15、d 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(select book_from book_sortwhere sort_id = 1);操作和結(jié)果顯示：mysql> create view cs_book asselect *from bookwhere book.book_sort in(select b

16、ook_sort.sort_namefrom book_sortwhere sort_id = 1);Query OK, 0 rows affected4.創(chuàng)建個人所有借書歸還紀(jì)錄視圖stu_borrow_return:sql:create view stu_borrow_return asselect student.stu_id, student.stu_name, book.book_id, book.book_name,return_table.borrow_date,return_table.return_datefrom student, book, return_tablewhe

17、re student.stu_id = return_table.stu_id and book.book_id = return_table.book_id; 5、設(shè)計觸發(fā)器 給出在各表上建立的觸發(fā)器以及使用的語句。1.設(shè)計觸發(fā)器borrow, 當(dāng)某學(xué)生借書成功后，圖書表相應(yīng)的圖書不在架上，變?yōu)?：sql:create trigger borrowafter insert on borrow for each rowbeginupdate book set book_num = book_num 1 where book_id = new.book_id;end操作與結(jié)果顯示：mysql&g

18、t; 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 affected在插入表borrow之前，book_id = 1 的圖書還在架上，為1：學(xué)生1借了這本書后，在borrow中插入了一條記錄：在

19、borrow中插入這條記錄后，book_id =1的圖書，不在架上，為0：2.設(shè)計觸發(fā)器trigger_return, 還書成功后，對應(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 = new.book_id;end還書時在return_table插入表項：此時圖書歸還架上：3.定義定時器（事件）eventJob，每天自動觸發(fā)一次，掃描視圖stu_

20、borrow，若發(fā)現(xiàn)當(dāng)前有預(yù)期歸還時間小于當(dāng)前時間，則判斷為超期，生成處罰記錄，這個定時器將每天定時觸發(fā)存儲過程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)用存儲過程*/set global event_scheduler = 1;alter event eventJob on completion preserve enable; /*開啟定時器

21、*/操作和結(jié)果顯示：1). 學(xué)生1借了圖書1，生成借書記錄stu_borrow視圖，如下：2). 當(dāng)他在1月27日前還書時，沒有生成罰單：3). 當(dāng)他在1月27日后還書時，生成罰單：4.設(shè)計觸發(fā)器trigger_credit，若處罰記錄超過30條，則將這個學(xué)生的誠信級設(shè)置為0，下次不允許借書：sql:create trigger trigger_creditafter insert on ticketfor each row beginif (select count(*) from ticket where stu_id=new.stu_id)>30 thenupdate studen

22、t set stu_integrity = 0 where stu_id = new.stu_id;end if;end操作和結(jié)果顯示，測試時選擇插入ticket項大于3，因為30太大了，不容易測試：學(xué)生1超過3次超期歸還圖書后，產(chǎn)生了4條罰單：此時觸動觸發(fā)器trigger_credit，將學(xué)生1的誠信級設(shè)置為0：四、應(yīng)用程序設(shè)計與編碼實現(xiàn)1、系統(tǒng)實現(xiàn)中存儲函數(shù)和存儲過程的設(shè)計 要求給出功能描述和代碼。1. 設(shè)計存儲過程，產(chǎn)生罰單proc_gen_ticket：當(dāng)日期超過預(yù)定歸還日期時，產(chǎn)生罰單，并將記錄寫入表ticket中，這個存儲過程在定時器eventJob中調(diào)用：sql：create p

23、rocedure 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(cur_date,stu_borrow.expect_return_date),0.1*datediff(cur_date,stu_borrow.expect_return_date)from stu

24、_borrowwhere cur_date>stu_borrow.expect_return_date;end操作和結(jié)果顯示：1). 學(xué)生1借了圖書1，生成借書記錄stu_borrow視圖，如下：2). 當(dāng)他在1月27日前還書時，沒有生成罰單：3). 當(dāng)他在1月27日后還書時，生成罰單：2.設(shè)計學(xué)生注冊信息存儲過程：學(xué)生注冊信息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

25、_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_grade);end3. 設(shè)計管理員注冊信息存儲過程：ma_registersql:create procedure ma_register(in ma_id int, in ma_name varchar(20), in ma_age int

26、, in ma_phone int)BEGINinsert into managervalues(ma_id, ma_name, ma_age, ma_phone);END4. 借書過程的實現(xiàn):1) 設(shè)計存儲函數(shù)，func_get_credit，返回學(xué)生的誠信級：create function func_get_credit(stu_id int) returns intbeginreturn(select stu_integrity from student where student.stu_id = stu_id);end2) 設(shè)計存儲函數(shù)，func_get_booknum，返回書籍是否

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

28、 borrow_date datetime)beginif func_get_credit(stu_id) = 1 and func_get_booknum(book_id) = 1 theninsert into borrow values(stu_id, book_id, borrow_date);else select 'failed to borrow'end if;end實驗操作與結(jié)果顯示：borrow紀(jì)錄為空：執(zhí)行函數(shù)，學(xué)生1借圖書2：call proc_borrow(1,2,now();學(xué)生1的誠信級為0：借書失敗：修改學(xué)生1誠信級為1：此時借書成功：5. 還書存

29、儲過程proc_return：當(dāng)還書時，查看是否書是否超期，即查詢ticket表項，當(dāng)發(fā)現(xiàn)超期，提示交罰單后再次還書，如沒有超期，則紀(jì)錄歸還項目到return_table中，并且刪除借書紀(jì)錄（以免還書后定時器仍然掃描這個紀(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 ti

30、cket.book_id=book_id) = 1 then /*判斷是否交了罰單，1表示沒有交*/select 'please pay off the ticket'else /*紀(jì)錄歸還項目到return_table中，并且刪除借書紀(jì)錄*/set borrowdate = (select borrow_date from borrow where borrow.stu_id = stu_id and borrow.book_id = book_id);insert into return_table values(stu_id, book_id, borrowdate, r

31、eturn_date);delete from borrowwhere borrow.stu_id = stu_id and borrow.book_id = book_id;end if;end實驗操作與結(jié)果顯示：學(xué)生1借了圖書2：超期產(chǎn)生了罰單，沒有交罰單，payoff=1：此時調(diào)用還書過程：call proc_return(1, 2, now();提示交罰單：交罰單，調(diào)用proc_payoff:call proc_payoff(1, 2);交罰單成功，payoff = 0；此時再次調(diào)用還書過程：call proc_return(1, 2, now();還書成功，在return_table

32、生成了還書紀(jì)錄：6. 交罰單存儲過程：修改罰單中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:call proc_payoff(1, 2);交罰單成功，payoff = 0；2、功能實現(xiàn) 按各功能模塊進行描述。要求：畫出流程圖并給出實現(xiàn)代碼。l 創(chuàng)建

33、學(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_account''localhost'grant i