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

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

2、加或者删除图书信息6. 管理员可以注销学生信息。通过需求定义，画出图书管理系统的数据流图：1数据流图学生管理员借阅信息21、系统功能设计画出系统功能模块图并用文字对各功能模块进行详细介绍。系统功能模块图:图书管理系统访问模块借阅者模块管理员模块借阅者登陆管理员登陆三、数据库设计方案图表1、系统E-R模型总体E-R图：34学生借阅精细化的局部E-R图:学生借阅-归还E-R图:学生ID诚信级学生ID年级年龄性别学生专业图书归还表归还借阅图书借阅表处罚表学生ID超期作者图书ID数量分类图书ID图书ID图书管理员E-R图:书名图书ID登记日期借阅时间5管理员.联系电话）2、设计表给出设计的表名、结构以

3、及表上设计的完整性约束。student :列名数据类型是否为空/性质说明stu_idintnot null /PK标明学生唯一学号stu_ namevarcharnot null学生姓名stu_sexvarcharnot null学生性别stu_ageintnot null学生年龄stu_provarcharnot null学生专业stu_gradevarcharnot null学生年级stu_i ntegrityintnot n ull/default=1学生诚信级book:列名数据类型是否为空/性质说明book_idintnot null / PK唯一书籍序号book_ namevarch

4、arnot 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:存储学生的借书信息列名数据类型是否为空/性质说明stude nt_idvarcharnot null /

5、 PK学生编号book_idvarcharnot null / PK书籍编号borrow_datedatatimenull借书时间expect_return_datedatetimenull预期归还时间return_table:存储学生的归还信息列名数据类型是否为空/性质说明stude nt_idvarcharnot null / PK学生编号book_idvarcharnot null / PK书籍编号borrow_datedatetimenull借书时间return_datedatatimenull实际还书时间ticket:存储学生的罚单信息列名数据类型是否为空/性质说明stude nt_

6、idvarcharnot null / PK学生编号book_idvarcharnot null / PK书籍编号over_dateintnull超期天数ticket_feefloatnull处罚金额man ager:列名数据类型是否为空/性质说明man ager_idvarcharnot null / PK管理员编号man ager_ namevarcharnot null管理员姓名man ager_agevarcharnot null管理员年龄man ager_ph onevarcharnot null管理员电话3、设计索引给出在各表上建立的索引以及使用的语句。student :1. 为s

7、tud创建索引，升序排序 sql:create in dex in dex_id on stude nt(stu_id asc);2. 为stu_name仓U建索弓I,并且降序排序sql:alter table stude nt add in dex in dex_ name(stu_ name, desc);插入索引操作和结果如下所示：mysql> create in dex in dex_id on stude nt(stu_id asc);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnin gs: 0 mysql>

8、 alter table stude nt add in dex in dex_ name(stu_ name desc);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnin gs: 0mysql>栏拉莖引占闍魅方器SJE注律SOLa案弓删黑引方握jindex idHstujdNormalB1REEstu raneNormalBTREEbook:1. 为book_id创建索引，升序排列sql:create in dex in dex_bid on book(book_id);2. 为book_record创建索引，以便方便查询

9、图书的登记日期信息，升序:sql:create in dex in dex_brecord on book(book_record);插入索引的操作和结果如下所示:mysql> create in dex in dex_bid on book(book_id);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnin gs: 0mysql> create in dex in dex_brecord on book(book_record);Query OK, 0 rows affectedindex bidRecords: 0

10、 Duplicates: 0 Warnin gs: 0index brecordbock recordNormalBTREE选顶曰二案弓方去book idNormalBTREEborrow:1. 为stud和book_id创建多列索引：sql:create in dex in dex_sid_bid on borrow(stu_id asc, book_id asc);插入索引的操作和结果如下所示：mysql> create in dex in dex_sid_bid on borrow(stu_id asc, book_id asc);Query OK, 0 rows affected

11、Records: 0 Duplicates: 0 Warnin gs: 0答塞引魁叢弓方去b-oo k i d.book dNormalETREEincex_5id_tidstujd. bookjdNormalBTREEreturn_table:1.为stud和book_id创建多列索引：sql:create in dex in dex_sid_bid on retur n_table(stu_id asc, book_id asc);插入索引的操作和结果如下所示:mysql> create in dex in dex_sid_bid_r on retur n_table(stu_id

12、asc, book_id asc);Query OK, 0 rows affectedRecords: 0 Duplicates: 0Warnin gs: 0栏位髡1外議牲妥器远项注痒£QL预览栏检塞1方法J index sid bid r幽 1. -式u_id bgkdNormalBTREEticket:1.为stud和book_id创建多列索引：sql:create in dex in dex_sid_bid on ticket(stu_id asc, book_id asc);插入索引的操作和结果如下所示：mysql> create in dex in dex_sid_b

13、id on ticket(stu_id asc, book_id asc);Query OK, 0 rows affected栏检索引补耀越辰器选顷注聲EQL预憶索引方吳Etu id. book idNormalBTREE'ndex sid bidRecords: 0 Duplicates: 0 Warnin gs: 0man ager:1.为manager_id 创建索引：sql:create in dex in dex_mid on man ager(ma nager_id);插入索引的操作和结果如下所示：mysql> create in dex in dex_mid on

14、man ager(ma nager_id);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnin gs: 0栏e塞弓權. - - 卜；.,.nwn. . .I manager id.一： 1°NormalBTREE4、设计视图给出在各表上建立的视图以及使用的语句。1.在表student上创建计算机专业(cs)学生的视图stu_cs : sql: create view stu_cs asselect *from stude ntwhere pro = cs '；操作和结果：mysql> create view

15、stu_cs asselect *from stude ntwhere stu_pro = 'cs'Query OK, 0 rows affectedstu id吁tu naiiriiestui sexstu agestu prostu grade&tu integrityI Etu am20CE201412 stu bm216201413乩LI.匸f20匚5201414 stu_dfIScs201415m20C5201412. 在表student, borrow 和book上创建借书者的全面信息视图stu_borrow :sql: create view stu_bo

16、rrow asselectstude nt.stu_id,book.book_id, stude nt.stu_ name,book.book_ name,borrow_date , adddate(borrow_date,30) expect_return_datefrom stude nt, book, borrowwhere stude nt.stud = borrow.stu_id and book.book_id = borrow.book_id;操作和结果：mysql> create view stu_borrow asselect stude nt.stu_id, book

17、.book_id, stude nt.stu_ name,book.book_ name,borrow_date ， adddate(borrow_date,30) expect_return_datefrom stude nt, book, borrowwhere stude nt.stud = borrow.stu_id and book.book_id = borrow.book_id;Query OK, 0 rows affected5tujd boolcjd &tu_name boolc_name borrow_dateexpectretu rn_d ate1computer

18、 netwo 2016-12-28 17:07 2017-01-27 17r07：133. 创建类别1的所有图书的视图 cs_book :sql: create view cs_book asselect *from bookwhere book.book sort in(select book_sort.sort .n ame from book_sortwhere sort_id = 1);操作和结果显示：mysql> create view cs_book asselect *from bookwhere book.book_sort in(select book_sort.sor

19、t_ name from book_sortwhere sort_id = 1);Query OK, 0 rows affectedbook_：dboo呂 meboofcauthorbootjaubbt>cik_ni.mbco<_5orfbock_rec ” cicom pirter netvgrk：author a1 cs2015-12-2 & 16:55572computer opcrotienp<ub_b1 c231 S 12 28 16:59139airihoi t2D15-12-28 15.58.3720112-2$ 15:59 103 inftjrfTif

20、lii&n鼻仇型川吋4 software efigine&ririg5 c+ 4 plbjs learning6 c prgq rimrniniauthor dauthor bauthor diuthcr dCE201 &-12-2B 15-59:552015 12 28 1&3656ZOIfr-12 28 17;00474. 创建个人所有借书归还纪录视图stu_borrow_return:sql:create view stu_borrow_return asselectstude nt.stud.stude nt.stu_ name,book.book_id

21、,book.book_ name,retur n_table.borrow_date,retur n_table.retur n_datefrom stude nt, book, retur n_tablewhere stude nt.stu_id = retur n_table.stu_id and book.book_id = retur n_table.book_id;stu idstui_namebookidbook nKEEborrowdate5>tu_a1 computer network2016-10-16 17rO7;1020116-12-29 15505、设计触发器给出

22、在各表上建立的触发器以及使用的语句。1.设计触发器borrow,当某学生借书成功后，图书表相应的图书不在架上，变为0:sql:create trigger borrowafter in sert on borrowfor each rowbeginupdate book set book_ num = book_ num-1where book_id = n ew.book_id;end操作与结果显示：mysql> delimiter $mysql> create trigger trigger_borrow-> after in sert on borrow-> fo

23、r each row-> beg in -> update book set book_ num = book_ num - 1 -> where book_id = n ew.book_id;-> end-> $Query OK, 0 rows affected在插入表 borrow 之前，book_id = 1的图书还在架上，为1:book idbco< namebe ok: a uth orbook publb co k numbook sortbook record1 computeauthorapub_i1 C52D1&12-28 16c5

24、5;学生1借了这本书后，在 borrow中插入了一条记录:5tu idbook idborrow_date1y 2016-12-28 17:O7|在borrow中插入这条记录后，book_id =1的图书，不在架上，为 0 :bookj dboc k_nj m#book_au:hoirbsokjpubbcolk_Hijmbook_tertbcok_r#rardl1 cemput*rtvgdk0 Ct2016-12-26 1&55i2.设计触发器trigger_return,还书成功后，对应的书籍book_num变为1 :sql:create trigger trigger_retur

25、nafter in sert on retur n_tablefor each rowbeginupdate book set book_ num = book_ num + 1where book_id = n ew.book_id;end还书时在return_table插入表项:stu idbook idreturn date卜112016-12-28此时图书归还架上:book idboo kam ebooLaiuthorbookjpLilbboclk_iiunnbook_sortbookjecordJconipjiter netarkauthorspubd1 cs2016-1228 1&

26、amp;553. 定义定时器(事件)eventJob ,每天自动触发一次，扫描视图stu_borrow，若发现当前有预期归还时间小于当前时间，则判断为超期，生成处罚记录，这个定时器将每天定时触发存 储过程 proc_gen_ticket :sql:create event if not exists even tJobon schedule every 1 DAY/*每天触发 */on completion PRESERVEdo call proc_gen_ticket(getdate(); /*调用存储过程 */set global evenscheduler = 1;alter event

27、 even tJob on completi on preserve en able; /* 开启定时器*/操作和结果显示：1).学生1借了图书1，生成借书记录 stu_borrow 视图，如下:stu_idbookidstu_namebooknamebo rrowed ateexpect_retLiirn_id ate1stu_acomputer netwo 2016-12-23 17:07 2017-01 -27 17:07:102).当他在1月27日前还书时，没有生成罚单:stu idbe ok idreturn_date1 12016-12-30 21:4或boo k idoverda

28、rtetJcketfee(Nullj(NuJI)Mull)Nuim3).当他在1月27日后还书时，生成罚单:5tudbockreturndate> 1 12017-02-23 21:5stu_idbookticlcet_fee 113230，下4. 设计触发器trigger_credit ,若处罚记录超过30条，则将这个学生的诚信级设置为次不允许借书：sql:create trigger trigger_creditafter in sert on ticketfor each rowbeginif (select coun t(*) from ticket where stu_id=n

29、ew.stud)>30 the nupdate stude nt set stu_ in tegrity = 0 where stu_id = n ew.stu_id; end if;end操作和结果显示，测试时选择插入ticket项大于3，因为30太大了，不容易测试:学生1超过3次超期归还图书后，产生了4条罚单：曰bookjdouer_dateticket_fee1132312311I421531fM. JhZdlrfM.jh此时触动触发器trigger_credit，将学生1的诚信级设置为0:stuj dstu.n am estusexitu_aaestujD rostu_grade

30、stu_integrity| stu am20CS201402 stu bm21cs20141四、应用程序设计与编码实现1、系统实现中存储函数和存储过程的设计要求给出功能描述和代码。1. 设计存储过程，产生罚单proc_gen_ticket :当日期超过预定归还日期时，产生罚单，并将记录写入表ticket中，这个存储过程在定时器eventJob中调用：sql :create procedure proc_ge n_ticket(i n curre ntdate datetime)BEGINdeclare cur_date datetime;set cur_date = curre ntdate

31、;replace into ticket(stu_id, book_id, over_date, ticket_fee)selectstud,book_id,datediff(cur_date,stu_borrow.expect_retur n_date),0.1*datediff(cur_date,stu_borrow.expect_return_date)from stu_borrowwhere cur_date>stu_borrow.expect_retur n_date;end操作和结果显示：1).学生1借了图书1，生成借书记录 stu_borrow 视图，如下:stu_idbo

32、okidstu_namebooknameborrow dateexpect_retLiirn_cl ate1stu_acomputer netwo 2016-12-29 17;07 2017-01-27 17:07:102).当他在1月27日前还书时，没有生成罚单:tu idbook idreturn date1 12016-12-30 21:4stujdbookjdaver darte1ticketjfee(Null)(Null)CMljII)(Mu®3).当他在1月27日后还书时，生成罚单:stu idbock idreturn dateI1y 2017-02-23 21：5|r

33、tu_idbookidoverdateticket_feea 一iT3222. 设计学生注册信息存储过程：学生注册信息 stu_registersql:20create 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

34、_pro, stu_grade) values(stu_id, stu_name, stu_sex, stu_age, stu_pro, stu_grade);end3. 设计管理员注册信息存储过程： 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 managervalues(ma_id, ma_name, ma_age, ma_phone);END4. 借书过程的实现 :1) 设计存

35、储函数 ， func_get_credit ，返回学生的诚信级：create function func_get_credit(stu_id int) returns intbeginreturn(select stu_integrity from student where student.stu_id = stu_id);2) 设计存储函数，func_get_booknum ，返回书籍是否在架上:create fun cti on fun c_get_book nu m(book_id int) retur ns intbeginretur n( select book_ num from

36、 book where book.book_id = book_id);end3) 设计存储过程 proc_borrow，调用 func_get_credit 禾口 func_get_booknum ，判断这个学生诚信度和书籍是否在架上，若为真，则借书成功，在borrrow表中插入纪录；否则提示失败：create procedure proc_borrow(instu_id int, in book_id int, in borrow_datedatetime)beginif fun c_get_credit(stu_id) = 1 and fun c_get_book nu m(book_i

37、d) = 1 the ninsert into borrowvalues(stu_id, book_id, borrow_date);elseselect 'failed to borrow'end if;end实验操作与结果显示:borrow纪录为空：stujdbookborrowNull)(Null)执行函数，学生1借图书2 :call proc_borrow(1,2, no w();学生1的诚信级为0 :5tujd司血astj sexwtu ja g estuprostu_grade20ce20U5tu integrity023#借书失败:信息 结果橇况狀誉failed

38、to borrow failed t& borrow修改学生1诚信级为1 :stu idStu namestu sexstu age-stu prostu gr-adestu integrity1m20CS20141此时借书成功:book idEtu idborrowdate2 2016-12-29 17：315. 还书存储过程proc_return :当还书时，查看是否书是否超期，即查询ticket表项，当发现超期，提示交罚单后再次还书, 如没有超期，则纪录归还项目到return_table中，并且删除借书纪录(以免还书后定时器仍然扫描这个纪录)：sql :create proced

39、ure proc_return(in stud in t, in book_id in t, 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表示没有交*/select 'please pay off the ticket'else /*纪录归还项目到 return_table中，并且删除借书纪录

40、 */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, retur n_date);delete from borrowwhere borrow.stud = stud and borrow.book_id = book_id;end if;end2B22实验操作与结果显示:学生1借了图书2:stu idbook

41、idborrow date2 2016-12-2917:31stu idbookidover_dte-tickeHe-epayoff10超期产生了罚单，没有交罚单，payoff=1 :23此时调用还书过程:call proc_retur n(1,2, no w();提示交罚单：please pay off the ticketplease pay off the ticket交罚单，调用 proc_payoff: call proc_payoff(1,2);交罚单成功，payoff = 0 ;£lu_idover dateticketjtepayoff 144401 2a013222

42、1此时再次调用还书过程:call proc_retur n(1,2, no w();还书成功，在return_table生成了还书纪录:stu idbook idborrow date1 2016*1016 17;072 2016-1229 17：31retu rri date2016-12-29 16:52016-12-29 19:46. 交罚单存储过程：修改罚单中payoff段为0，表明罚单已交：create procedure proc_payoff( in stuid in t, in bookid int) beginupdate ticketset payoff = 0where

43、ticket.stu_id = stuid and ticket.book_id = bookid;select succeed end交罚单，调用 proc_payoff: call proc_payoff(1,2);交罚单成功,payoff =:0 ；stu idbook idover_date-ticketfeepayoff14440i22330i3222 12、功能实现按各功能模块进行描述。要求：画出流程图并给出实现代码。创建学生统一账户，账户名：student_account，并且授予权限：sql:create user 'stude nt_acco un tlocalhos

44、t：grant in sert,select on stude nt to 'stude nt_acco un tlocalhost：grant select on book to 'stude nt_acco un tlocalhost：grant in sert,select on borrow to 'stude nt_acco un tlocalhost：grant in sert,select on retur n_table to 'stude nt_acco un t''localhost'grant select on t

45、icket to 'stude nt_acco un t''localhost'创建管理员统一账户，账户名：man ager_accou nt,并且授予全部权限:sql:create user 'manager_account''localhost' identified by '123'27grant all on library_ma nageme nt to 'ma nager_acco un tlocalhost：查询图书信息按书名查找：select * from book where book_n

46、ame =sqlH-+-十-.十-+-十| bodeid1book naire1tjoo< author1book： pub1boolc nuir;1book 3Qit4+-+-4-4-1471sql1三4autho r_d14putoc1411=4csselect " fram baaJc where lao口k naice =1 dqJ.*:按作者查找:select * from book where book_author = author 'mysql> select * from book where basic author + mthor cr;+-

47、+ + +| bQo)c_ajchar I t)Qok_pih | booh：_mini | b4+一一| t>Qolc_zd | tQok_nazEe4 !一-一-一一一|3 | inforinatioTi secuiity | author_cI pub a借书功能:proc_borrow(in stu_id int, in book_id int, in borrow_date datetime)如果要接的书还在架上，并且学生的诚信级为1，那么可以借书call proc_borrow(1, 1, no w();命令行操作：rtysql> call(lr 5T n.aw();Que工丫 OK, 1 tow a