《图书管理系统数据库设计MYSQL实现.docx》由会员分享，可在线阅读，更多相关《图书管理系统数据库设计MYSQL实现.docx（22页珍藏版）》请在三一文库上搜索。
1、图书管理系统数据库设计1、系统简介图书管理是每个图书馆都需要进行的工作。一个设计良好的图书管理系统数据库能够给图书管理带来很大的便利。2、需求分析图书管理系统的需求定义为：1 .学生可以直接通过借阅终端来查阅书籍信息，同时也可以查阅自己的借阅信息。2 .当学生需要借阅书籍时， 通过账号密码登陆借阅系统，借阅系统处理学生的借阅，同时修改图书馆保存的图书信息， 修改被借阅的书籍是否还有剩余， 同时更新学生个人的借阅信息。3 .学生借阅图书之前需要将自己的个人信息注册，登陆时对照学生信息。4 .学生直接归还图书，根据图书编码修改借阅信息5 .管理员登陆管理系统后，可以修改图书信息，增加或者删除图书信
2、息6 .管理员可以注销学生信息。通过需求定义，画出图书管理系统的数据流图：数据流图学生信息图书信息学生信息借阅信息借阅信息学生学生信息:、系统功能设计画出系统功能模块图并用文字对各功能模块进行详细介绍。 系统功能模块图：三、数据库设计方案图表1、系统E-R模型总体E-R图:图书V管理 管理员精细化白局部E-R图：学生借阅-归还E-R图： （学生ID）（年级（年d诚信级学生乙（学生1 /归还时间-图书归还表上/（图书ID”归还借阅A（图书旧广处罚表 /人 7（学生ID）（超期）处罚金图书（,图书 id/ Cr一,（出版社，（分类管理员E-R图：姓名）（ 年龄ID#所属单位）管理员一（联系电话）/
3、xX（类朋学生图书一属于:一（性别）一C专业）人里吵图书借阅表1图书 ID/X登记日期） 卜借阅时间作者尸）扁号）（类别名称）图书类别2、设计表给出设计的表名、结构以与表上设计的完整性约束。student列名数据类型是否为空/性质说明stu_idintnot null /PK标明学生唯一学号stu_namevarcharnot null学生stu_sexvarcharnot null学生性别stu_ageintnot null学生年龄stu_provarcharnot null学生专业stu_gradevarcharnot null学生年级stu_integrityintnot null/de
4、fault=1学生诚信级book:列名数据类型是否为空/性质说明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_namevarcharn
5、ot null类型名称borrow:存储学生的借书信息列名数据类型是否为空/性质说明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借书时间r
6、eturn_datedatatimenull实际还书时间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_phonev
7、archarnot null管理员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
8、: 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栏冠藜l外建 触发给选项 注释SQL烫S栏位案弓英型零引方法indlex_id.stuji dNormalBTREEirde_rame5tu_rarneNomialBFREEbook:1 .为book_id创建索引，升序排列sql:create index index_bid on b
9、ook(book_id);2 .为book_record创建索引，以便方便查询图书的登记日期信息，升序: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 aff
10、ectedRecords: 0 Duplicates: 0 Warnings: 0料度器透直 渊SQL频窟吝招砂索引方法indexbrecordB bookjd book_recordNormalNormalBTREEBTREEborrow: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
11、 OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0档位 未引 扑室 融向黑 田5注疫 SQL -5索引吴星塞三方去NcrmlBTREENormalETREES栏位book idindex_5id_bidstujd, bookidreturn_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_b
12、id_r on return_table(stu_id asc, book_id asc);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0栏位索引外嶷.脸墨选项 生薜应位素引理素引方法Qindex sid bid rstu idbookidNormalBTREEticket: 1.为stu_id 和book_id创建多列索引：sql:create index index_sid_bid on ticket(stu_id asc, book_id asc);插入索引的操作和结果如下所示：mysql create inde
13、x index_sid_bid on ticket(stu_id asc, book_id asc);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0rtu_id, book_id栏位 塞引 ; 触殳器还西 法舞 GL独母案引表型容引方法NormalBTREEmanager:1.为manager_id创建索引：sql:create index index_mid on manager(manager_id);插入索引的操作和结果如下所示:mysql create index index_mid on manager(ma
14、nager_id);Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0栏拉 宾匚 讣妲 般爱号选项 翊 如海managerjd索引拉蒙弓方正NormalBTREE4、设计视图给出在各表上建立的视图以与使用的语句。1 .在表student上创建计算机专业(cs)学生的视图stu_cs : sql: create view stu_cs asselect *from studentwhere pro = cs；操作和结果：mysql create view stu_cs asselect *from studentwhere
15、stu_pro = cs;Query OK, 0 rows affectedstu idstunamestusexstu agestujarortu gradestu j integrityMlEtv a m20G5201412stubm21C5201413stucf20匚霍201414f15CS201415stuem20CS201412 .在表student, borrow 和book上创建借书者的全面信息视图stu_borrow :sql: create view stu_borrow asselect student.stu_id, book.book_id, student.stu_n
16、ame, 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_name, book.book_name, borrow_date , addd
17、ate(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 affectedbook idstu_n 占mwstu_ 3boolc_n3me borrow date expect return date computer netwc 2016-12-23 17:07 2017-01-27 177:183 .创建类别1的所有图书的视图cs_book :sq
18、l: create view cs_book asselect *from bookwhere book.book_sort in(select book_sort.sort.namefrom book_sortwhere sort_id = 1);操作和结果显示：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 rows affectedbaok_iuhQrboo
19、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 ：book.idbcoknamebcokauthorbook_pubIbcokjwmboo k.sortboak_record1cor
20、nputer networkauthor_flpuba1c2O16-12-2S 16? 55;学生1借了这本书后，在 borrow中插入了一条记录:StLIjdboo kjdborraw_d3te1J201&-12-29 17：O7|在borrow中插入这条记录后，book_id =1的图书，不在架上，为 0:bookjdbocknamebook_audiciirbo&k_pubbcoknuniboolc_iortbook_record1 computer reworkauthorialpjb_a0 cs2015-12-26 16:55:2.设计触发器trigger_return, 还书成功后
21、，对应的书籍 book_num变为1 ： sql:create trigger trigger_returnafter insert on return_tablefor each rowbeginupdate book set book_num = book_num + 1where book_id = new.book_id; end还书时在return_table插入表项:sHiidbook_idreturndate1口阳 412-28 18:51此时图书归还架上:bookidlbooknamebook_anjthorbookjpLibbock=rumbcck.sortboo kT ec
22、ordcompjter networkpub_912316-1228 1&55;3.定义定时器(事件)eventJob ,每天自动触发一次，扫描视图 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
23、global event_scheduler = 1;alter event eventJob on completion preserve enable; /*开启定时器*/操作和结果显示：1).学生1借了图书1,生成借书记录 stu_borrow 视图，如下：stupid book_id 5tunamebook name borrow dateexpectrreturn_date1 stii_acomputer netwo 2016-12-28 17:07 2017-0117:07:182) .当他在1月27日前还书时，没有生成罚单:stujd bo ok id rsturn_datt20
24、16 12-3D 21stuid boo kid over_dateticket Jee(Null)3) .当他在1月27日后还书时，生成罚单:stujd bookid returr_date2017-02-26 21：5rtu idbookidover_dateticket_fe13234.设计触发器trigger_credit ，若处罚记录超过 30条，则将这个学生的诚信级设置为0,下次不允许借书：sql:create trigger trigger_creditafter insert on ticket for each rowbeginif (select count(*) from
25、 ticket where stu_id=new.stu_id)30 thenupdate student set stu_integrity = 0 where stu_id = new.stu_id;end if;end操作和结果显示，测试时选择插入ticket项大于3,因为30太大了，不容易测试:学生1超过3次超期归还图书后，产生了 4条罚单：stujdbookjdovar_date11323123112421531zIH1. , I KjT KII. , 1H此时触动触发器trigger_credit ,将学生1的诚信级设置为0:stujdstu_namestu_sexstu_ages
26、tu_p rostu_gradestujntegritystu am20CS201402Etubm21cs20141四、应用程序设计与编码实现1、系统实现中存储函数和存储过程的设计要求给出功能描述和代码。1 .设计存储过程，产生罚单proc_gen_ticket :当日期超过预定归还日期时，产生罚单，并将记录写入表 ticket中，这个存储过程在定时器eventJob中调用：sql :create procedure proc_gen_ticket(in currentdate datetime)BEGINdeclare cur_date datetime;set cur_date = cur
27、rentdate;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)from stu_borrowwhere cur_datestu_borrow.expect_return_date;end操作和结果显示：1) .学生1借了图书1,生成借书记录 stu_borrow 视图，如下：tu_
28、id bookjd stunamebookname borrowdate expect_return_d ate1 tu_acomputer netwo 2016-12-29 1707 2017-01 -27 17:07:182) .当他在1月27日前还书时，没有生成罚单：5tu_id bo ok id rebturn_date2016-12-30 21stujd bookjd aver_darte ticketjeeMl 111f NullNullj3) .当他在1月27日后还书时，生成罚单:stu idbaok_ldreturridate17-0278 21 &rtu idbaak_idQ
29、Ytrdate13232 .设计学生注册信息存储过程：学生注册信息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 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
30、, 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) 设计存储函数，func_get_credit ,返回学生的诚信级：c
31、reate function func_get_credit(stu_id int) returns int beginreturn(select stu_integrity from student where student.stu_idstu_id);end2) 设计存储函数，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); end
32、3) 设计存储过程 proc_borrow ,调用 func_get_credit 和 func_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 then insert into borrowvalues(stu_
33、id, book_id, borrow_date);elseselect failed to borrow;end if;end实验操作与结果显示：borrow纪录为空：stu_id bookjd borrow date(Null)执行函数，学生1借图书2:call proc_borrow(1,2,now();学生1的诚信级为0:stui id5tu_name,5tljl_sex5tu_a g estu_prost upgrade5tu_i ntegritystu am20cs20140借书失败：信息 结果1 柢况 状至failed to borrow failed to borrow修改学生
34、1诚信级为1:5tU_idsturarnestusexstuagestuprostugradestujntegrity1arr2。OS20141此时借书成功:stu idbook idborrow_date2 2016-12-255 .还书存储过程 proc_return :当还书时，查看是否书是否超期，即查询 ticket表项，当发现超期，提示交罚单后再次还书，如没有超期，则纪录归还项目到return_table 中，并且删除借书纪录(以免还书后定时器仍然扫描这个纪录)：sql :create procedure proc_return(in stu_id int, in book_id i
35、nt, in return_datedatetime)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 中，并且删除借书纪录 */set borrowdate = (select borrow_date from borrow
36、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 borrow.stu_id = stu_id and borrow.book_id = book_id;end if;end实验操作与结果显示：学生1借了图书2:stujd bookjd borrow_date 2 2016-12-29 17:31超期产生了罚单，没有交罚单，payoff
37、=1 :stuid bookjd oebr_date ticketjee payoff 14440122831132221此时调用还书过程：call proc_return(1,2, now();提示交罚单：please pay off the ticketplease pay off the ticket交罚单，调用 proc_payoff: call proc_payoff(1,2); 交罚单成功，payoff=0 ;stu_idbook idover_date-titketfeepayoff114440J1228301i2221此时再次调用还书过程：call proc_return(1,
38、2, now();还书成功，在return_table 生成了还书纪录:stu id be ok id borrowdate relurndate1 2016-10-1C 17:07 2016-12-25 16:512 2016-12*23 17:31 2016 12-29 19:46 .交罚单存储过程：修改罚单中payoff段为0,表明罚单已交：create procedure proc_payoff(in stuid int, in bookid int)beginupdate ticketset payoff = 0where ticket.stu_id = stuid and tick
39、et.book_id = bookid;select succeed；end交罚单，调用 proc_payoff:call proc_payoff(1,2);交罚单成功，payoff = 0 ;stu idbook id over_datt tkket fee payoff12Z83。1322212、功能实现按各功能模块进行描述。要求：画出流程图并给出实现代码。创建学生统一账户，账户名：student_account ,并且授予权限：sql :create user student_accountlocalhost;grant insert,select on student to student_accountlocalhost;grant select