图书管理系统数据库设计-MYSQL实现

1、图书管理系统数据库设计一、系统概述1、系统简介图书管理是每个图书馆都需要进行的工作。一个设计良好的图书管理系统数据库能够给图书管理带来很大的便利。2、需求分析图书管理系统的需求定义为：1 .学生可以直接通过借阅终端来查阅书籍信息，同时也可以查阅自己的借阅信息。2 .当学生需要借阅书籍时，通过账号密码登陆借阅系统，借阅系统处理学生的借阅，同时修改图书馆保存的图书信息， 修改被借阅的书籍是否还有剩余， 同时更新学生个人的借阅信息。3 .学生借阅图书之前需要将自己的个人信息注册，登陆时对照学生信息。4 .学生直接归还图书，根据图书编码修改借阅信息5 .管理员登陆管理系统后，可以修改图书信息，增加或者

2、删除图书信息6 .管理员可以注销学生信息。通过需求定义，画出图书管理系统的数据流图：数据流图借阅信息:、系统功能设计画出系统功能模块图并用文字对各功能模块进行详细介绍。系统功能模块图:图书管理系统管理员模块借阅者模块三、数据库设计方案图表1、系统E-R模型总体E-R图：学生1 1借阅隔a图书管理管理员精细化白局部E-R图：学生借阅-归还E-R图：（学生ID .（年级年龄,诚信级）j工学生（学生1叭/',1归还时间图书归还表（图书ID ：j归还 借阅-广；上处罚表/（图 PDX :（学生ID）（超期:处罚金额图书_C（图书 ID书名（出版社）（分类）（，性别）Y专业）.（学生ID ） &

3、#39;（图书借阅表图书ID） 一乂登记日期，（借阅时间）数量）管理员E-R图:2、设计表给出设计的表名、结构以及表上设计的完整性约束。student :列名数据类型是否为空/性质说明stu_idintnot null /PK标明学生唯一学号stu_namevarcharnot null学生姓名stu_sexvarcharnot null学生性别stu_ageintnot null学生年龄stu_provarcharnot null学生专业stu_gradevarcharnot null学生年级stu_integrityintnot null/default=1学生诚信级book:列名数据类型

4、是否为空/性质说明book_idintnot null / PK唯一书籍序号book_namevarcharnot null书籍名称book_authorvarcharnot null书籍作者book_pubvarcharnot null书籍出版社book_numintnot null书籍是否在架上book_sortvarcharnot null书籍分类book_recorddatatimenull书籍登记日期book_sort:列名数据类型是否为空/性质说明sort_idvarcharnot null / PK类型编号sort_namevarcharnot null类型名称borrow:存储

5、学生的借书信息列名数据类型是否为空/性质说明student_idvarcharnot null / PK学生编号book_idvarcharnot null / PK书籍编号borrow_datedatatimenull借书时间expect_return_datedatetimenull预期归还时间return_table:存储学生的归还信息列名数据类型是否为空/性质说明student_idvarcharnot null / PK学生编号book_idvarcharnot null / PK书籍编号borrow_datedatetimenull借书时间return_datedatatimenu

6、ll实际还书时间ticket:存储学生的罚单信息列名数据类型是否为空/性质说明student_idvarcharnot null / PK学生编号book_idvarcharnot null / PK书籍编号over_dateintnull超期天数ticket_feefloatnull处罚金额manager:列名数据类型是否为空/性质说明manager_idvarcharnot null / PK管理员编号manager_namevarcharnot null管理员姓名manager_agevarcharnot null管理员年龄manager_phonevarcharnot null管理员电

7、话3、设计索引给出在各表上建立的索引以及使用的语句。student :1 .为stu_id创建索引，升序排序sql:create index index_id on student(stu_id asc);2 .为stu_name创建索引，并且降序排序sql:alter table student add index index_name(stu_name, desc);插入索引操作和结果如下所示：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领点a栏位犯美暨Ijindex idNormalStu rgrneNormalmysql>累弓方法 b REL BTREE1 .为book_id创建索引，升序排列sql:create index index_bid on book(book_id);2 .为book_record创

9、建索引，以便方便查询图书的登记日期信息，升序:sql:create index index_brecord on book(book_record);插入索引的操作和结果如下所示: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创建多列索引：sql:create index index_sid_bid on borrow(stu_id asc, book_id asc);插入索引的操作和结果如下所示：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栏色 圭骂 外粮 岫发翳法质 注痒 13L竭名栏位素引蚪卜incex_5id_tid.bo ok_ dstujd. bookjdNormalNormalETREEBTREEreturn_table: 1.为stu_id和book_id创建多列索引:sql:create index index_sid_bid on return_table(stu_id asc, book_id asc);插入索引的操作和结果如下所示：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国魅精演 谢 SQL预览案看方法BTREE刍 桂位素引类型index ±id bid r . .ticket:1.为stu_id和book_id创建多列索引：sql:create index index_sid_bid on ticket(stu_id asc, book_id asc);插入索引的操作和结果如下所示：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 创建索引：sql:create index index_mid on manager(manager_id);插入索引的操作和结果如下所示：mysql> create index index_mid on m

14、anager(manager_id);Query OK, 0 rows affectedRecords: 0Duplicates: 0Warnings:0栏位 爨马 卦铤 触友然五项 主择 萄受其近幻索引越聚弓方法manaceridNormalBTREE4、设计视图给出在各表上建立的视图以及使用的语句。1 .在表student上创建计算机专业(cs)学生的视图stu_cs :sql: create view stu_cs asselect *from studentwhere pro =' cs'；操作和结果：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上创建借书者的全面信息视图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;操作和结果：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号tu_ riarnsbook_nameborrow_d3te&xpect_retu rn_d atecomputer netwo 2016-12-28 17:07 2

18、017-01-27 17:07:103 .创建类别1的所有图书的视图cs_book :sql: create view cs_book asselect *from bookwhere book.book_sort in(select book_from book_sortwhere sort_id = 1);操作和结果显示：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 .创建个人所有借书归还纪录视图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、设计触发器给出在各表上建立的触发器以及

22、使用的语句。1 .设计触发器borrow,当某学生借书成功后，图书表相应的图书不在架上，变为 0：sql:create trigger borrowafter insert on borrowfor each rowbeginupdate book set book_num = book_num Twhere book_id = new.book_id;end操作与结果显示：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学生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 .设计触发器trigger_return,还书成功后，对应的书籍 book_num变为1 ： 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还书时在return_table插入表项：shjid bookid returndate2016-12-18c5卜 11此时图书归还架上:book idbookjiamebooLaiuthorbookjpiibbock.runnbook_sortbookjecord卜Jcanipjiter nWwcirkauthorspubd12016-1228 1a553 .定义定时器(事件)eventJob ,每天自动触发一次，扫描

26、视图stu_borrow ,若发现当前有预期归还时间小于当前时间，则判断为超期，生成处罚记录，这个定时器将每天定时触发存储过程 proc_gen_ticket :sql:create event if not exists eventJobon schedule every 1 DAY/*每天触发 */ on completion PRESERVEdo call proc_gen_ticket(getdate(); /*调用存储过程 */开启定时器*/set global event_scheduler = 1;alter event eventJob on completion preser

27、ve enable; /*操作和结果显示:1) .学生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) .当他在1月27日前还书时，没有生成罚单:stu id book id return date2016-12-30 21:stuidboo k idverdateticket Jee(Null)(Null)(Nuim3).当他在1月2

28、7日后还书时，生成罚单:stujd boclc_id retumdate2017-02-23 2"stu_idbookjdovr-dateticket_fee13234.设计触发器trigger_credit ,若处罚记录超过30条，则将这个学生的诚信级设置为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操作和结果显示，测试时选择插入ticket项大于3,因为30太大了，不容易测试：学生1超过3次超期归还图书后，产生了 4条罚单：stuidbookjdouer_dateticketjee1132312311I421531此时触动触发器trigger_credit ,将学生1的诚信级设置为0:stujdtu_namestusexitu_agestujJTOstu_gradestu_integritym20C5201402stu_bm21cs20141四、应用程序设计与编码实现1、系统

30、实现中存储函数和存储过程的设计要求给出功能描述和代码。1.设计存储过程，产生罚单proc_gen_ticket :当日期超过预定归还日期时，产生罚单，并将记录写入表ticket中，这个存储过程在定时器eventJob中调用：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操作和结果显示：1) .学生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) .当他在1月27日前还书时，没有生成罚单:5tu_idbo ok idreturn date112016-12-0 21:4|stujdbookjdover darteticket fee(MM(Null)CNljII)(Uuin3) .当他在1月27日后还书时，生成罚单:stu_id bockid retu m_dat?2017-02-20 21Ertu_idbookidover-dateticket_fee:一113222 .设计学生注册信息存储过程：学生注册信息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 .设计管理员注册信息存储过程：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 .借书过程的实现：1) 设计存储函数，func_get_credit ,返回学生的诚信级：create function func_get_credit(stu_id int) return

35、s intbeginreturn(select stu_integrity from student where student.stu_id = stu_id);end2) 设计存储函数，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) 设计存储过程 proc_borrow ,调用 func_get_credit 和 func

36、_get_booknum ,判断这个学生诚信度和书籍是否在架上，若为真，则借书成功，在borrrow表中插入纪录；否则提示失败：create 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实验操作与结果显示:borrow纪录为空:stu Jd book id borrow date(Null)执行函数，学生1借图书2:call proc_borrow(1,2,now();学生1的诚信级为0 :stu id4li sew stu.agcstj_prc)stu_grade_integrityB欢u a20 n20140借书失败：信息结果1幅兄 状套faded to borrow failed to borrow修改学生1诚信级为1 :stujdstu_nsmestLi_sexstu_agestuprostu_grddestuj int

38、egrity1m20CS20141此时借书成功:book idstu idborrowdate2 2016-12-29 17：315 .还书存储过程proc_return :当还书时，查看是否书是否超期，即查询ticket表项，当发现超期，提示交罚单后再次还书, 如没有超期，则纪录归还项目到 return_table中，并且删除借书纪录(以免还书后定时器仍然扫描这个纪录)：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 /*纪录归还项目到 return_table中，并且删除借书纪录 */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实验操作与结果显示:学生1借了图书2:stujdbookjdborrow_d ate2 2016-12-2917:31超期产生了罚单，没有交罚单，payoff=1stujd bookjd over_dte ticketfe-e payoff14440122

41、031132221此时调用还书过程:call proc_return(1,2, now();提示交罚单：please pay off the ticketplease pay off the ticket交罚单，调用 proc_payoff: call proc_payoff(1,2);交罚单成功，payoff = 0 ;5tLi idbook idover_d*itefeepjyvffD1444Qi22Saoi32221此时再次调用还书过程:call proc_return(1,2, now();还书成功，在return_table生成了还书纪录:book idborrow dateretu

42、 rri datestu id1 2016-1016 17:072016-12-29 16:52 2016-12-2917:11 2016-12-39 19:46.交罚单存储过程：修改罚单中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交罚单，调用 proc_payoff:

43、 call proc_payoff(1,2);交罚单成功，payoff = 0 ;stu idbook idover_dateticketfeepayoff*Ml14440i22a30i322212、功能实现按各功能模块进行描述。要求：画出流程图并给出实现代码。创建学生统一账户，账户名：student_account ,并且授予权限：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;创建管理员统一账户，账户名：manager_account,并且授予全部权限: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)如果要接的书还在架上，并且学生的诚信级为1,那么可以借书 call proc_borrow(1, 1, now();命令行操作：rtysql> call(lr 5T n.aw();Q