数据库-图书借阅系统

数据库课程设计课设名称图书借阅系统学生姓名李XX学号XXXX班级信管1163指导教师刘金华成绩图书管理系统目录一、需求分析 P31.背景2.系统目标3.用户需求4.功能划分5.功能描述二、概念结构设计 P51.管理员实体E-R图2.图书信息实体E-R图3.读者信息实体E-R图4.整体E-R图三、逻辑结构设计 P51.关系 2.关系模式 3.数据库表四、数据库实施 P61.建表 P62.输入数据 P83.建立视图 P114.创建角色并授权 P125.创建存储过程 P136.创建触发器 P14五、心得体会 P16图书借阅系统一、需求分析1.背景在图书馆的活动中，需要通过管理图书信息、图书管理员信息以及借阅者的信息，以达到完善图书借阅系统的目的。传统的手工操作方式，存在许多缺点和弊端，比如易发生数据丢失，统计错误，劳动强度高，且速度慢。使用计算机可以高速，快捷地完成以上工作。在计算机联网后，数据在网上传递，可以实现数据共享，避免重复劳动，规范图书借阅管理行为，从而提高了管理效率和水平。2.系统目标图书借阅系统以计算机为工具，通过对图书馆借阅书籍所需的信息管理，把管理人员从繁琐的数据计算处理中解脱出来，使其有更多的精力从事图书借阅管理政策的研究实施，工作计划的制定执行和工作质量的监督检查，从而全面提高工作质量。实现一个计算机综合图书借阅系统，完成1）管理员信息管理、2）图书信息管理、3）读者信息管理等功能。3.用户需求此系统专门为图书馆相关管理所设计。主要描述用户需求的使用范围，功能要求信息采集与各人员的使用权限。4.功能划分 系统为用户提供三项选择：管理员信息管理、图书信息管理、读者信息管理等功能。5.功能描述（1）、管理员信息管理：给出所有管理员的列表信息。可以执行添加、修改、删除、查找等操作。（2）、图书信息管理：给出所有图书信息的列表，用户可以实现添加，修改，删除，查找图书信息等操作。（3）、读者信息管理：给出所有借阅者信息的列表，用户可以实现添加，修改，删除，查找读者信息等操作。6.数据流图二、概念结构设计1.管理员实体E-R图 2.图书信息实体E-R图库存数量分类号图书信息出版社编号作者书名姓名职工号管理员联系方式性别3.读者信息实体E-R图姓名借书证号读者性别联系方式4.整体E-R图书名作者编号出版社职工号姓名管理图书信息管理员性别联系方式库存数量分类号借阅注册借书日期还书日期借书数量借书证号姓名读者联系方式性别三、逻辑结构设计1.关系（1）、管理关系：管理员和图书的关系是1：n，每个管理员可以管理多本图书。（2）、注册关系：管理员和读者的关系是1：n，每个管理员可以为多名读者注册。（3）、借阅关系：读者和图书的关系是m:n，每个读者可以借阅多本图书，每本图书可被不同读者借阅。2.关系模式管理员（职工号，姓名，性别，联系方式）图书信息（编号，书名，作者，出版社，分类号，库存数量）读者（借书证号，姓名，性别，联系方式）借阅（借书证号，编号，借书日期，还书日期，借书数量）3.数据库表（1）管理员基本信息表名称数据类型长度键类型允许空职工号Char20主键No姓名Varchar20No性别Varchar10No联系方式Char20No（2）图书信息基本信息表名称数据类型长度键类型允许空编号Char20主键No书名Varchar20No作者Varchar20No出版社Varchar20No分类号Varchar20No库存数量Varchar10No（3）读者基本信息表名称数据类型长度键类型允许空借书证号Char20主键No姓名Varchar20No性别Varchar10No联系方式Char20No（4）借阅基本信息表名称数据类型长度键类型允许空借书证号Char20外码No编号Char20外码No借书日期Char10No还书日期Char10借书数量Char10No四、数据库实施1.建表表的定义：在关系型数据库当中一个表就是一个关系，一个关系数据库可以包含多个表。（1）、管理员表/* Table: 管理员 */drop table if exists 管理员;create table 管理员( 职工号 char(20) not null, 姓名 varchar(20) not null, 性别 varchar(10) not null, 联系方式 char(20) not null, primary key (职工号);（2）、图书信息表/* Table: 图书信息*/drop table if exists 图书信息;create table 图书信息( 编号 char(20) not null, 书名 varchar(20) not null, 作者 varchar(20) not null, 出版社 varchar(20) not null, 分类号 varchar(20) not null, 库存数量 char(10) not null, primary key (编号);（3）、读者表/* Table: 读者*/drop table if exists 读者;create table 读者( 借书证号 char(20) not null, 姓名 varchar(20) not null, 性别 varchar(10) not null, 联系方式 char(20) not null, primary key (借书证号) );（4）、借阅表/* Table: 借阅*/drop table if exists 借阅;create table 借阅( 借书证号 char(20) not null, 编号 char(20) not null, 借书日期 char(10) not null, 还书日期 char(10) , 借书数量 int not null, primary key (编号, 借书证号); alter table 借阅 add constraint FK_借阅 foreign key (编号) references 图书信息 (编号) on delete restrict on update restrict;alter table 借阅 add constraint FK_借阅2 foreign key (借书证号) references 读者 (借书证号) on delete restrict on update restrict;2.输入数据/*图书信息表插入数据*/INSERT INTO 图书信息 VALUES (,马克思主义,王大伟,清华大学出版社,思想哲学类,20);INSERT INTO 图书信息 VALUES (,绿野仙踪,弗兰克,人民文学出版社,文学艺术类,20);INSERT INTO 图书信息 VALUES (,十四分之一,宁一航,中国农业出版社,恐怖灵异类,20);INSERT INTO 图书信息 VALUES (,物理结构模型,张俊,清华大学出版社,社会科学类,20);INSERT INTO 图书信息 VALUES (,海洋知识史,王珊,中华书局出版社,社会科学类,20);INSERT INTO 图书信息 VALUES (,做个好妈妈,张小蒙,北京生活出版社,生活智慧类,20);INSERT INTO 图书信息 VALUES (,中国近代史,王大伟,北大文学出版社,历史发展类,20);/*管理员表插入数据*/INSERT INTO 管理员 VALUES (,李红,女,);INSERT INTO 管理员 VALUES (,王巧,女,);INSERT INTO 管理员 VALUES (,廖大强,男,);INSERT INTO 管理员 VALUES (,陆峰,男,);INSERT INTO 管理员 VALUES (,张小兰,女,);INSERT INTO 管理员 VALUES (,黄佩蓝,女,);INSERT INTO 管理员 VALUES (,飘零大叔,男,);/*读者表插入数据*/INSERT INTO 读者 VALUES (1311,廖浩艇,男,);INSERT INTO 读者 VALUES (1312,廖飞艇,男,);INSERT INTO 读者 VALUES (1313,武艺,男,);INSERT INTO 读者 VALUES (1314,刘艳娜,女,);INSERT INTO 读者 VALUES (1315,南天,男,);INSERT INTO 读者 VALUES (1316,黛玉,女,);INSERT INTO 读者 VALUES (1317,白云,女,);/*借阅表插入数据*/INSERT INTO 借阅 VALUES (1311,2);INSERT INTO 借阅 VALUES (1312,3);INSERT INTO 借阅 VALUES (1313,1);INSERT INTO 借阅 VALUES (1314,1);INSERT INTO 借阅 VALUES (1315,2);INSERT INTO 借阅 VALUES (1316,2);INSERT INTO 借阅 VALUES (1317,1);3.建立视图定义：视图是从一个或多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。和表一样，视图也是包括几个被定义的数据列和多个数据行，但就本质而言这些数据列和数据行来源于其所引用的表。（1）、/*视图查询图书信息表中的信息*/DROP VIEW IF EXISTS book;CREATE VIEW bookASSELECT 编号,分类号,库存数量,书名FROM 图书信息;/*查询数据*/*查询视图1*/SELECT *FROM BOOK;/*查询视图2*/SELECT 书名,分类号FROM BOOK;（2）、/*视图查询管理员、读者表中的信息*/DROP VIEW IF EXISTS Y01;CREATE VIEW Y01(num,work_phone,reader_phone)ASSELECT 管理员.职工号,管理员.联系方式,读者.联系方式FROM 管理员,读者;/*查询数据*/SELECT DISTINCT num,work_phoneFROM Y01;4.创建用户并授权/*创建用户USER1 */Create USER USER1localhost IDENTIFIED BY 0668;/*在cmd中操作*/cd C:Program FilesMySQLMySQL Server 5.7binmysql -u user1 -p/*用户USER1拥有对各表的所有权限*/（1）将图书信息表的所有权限授予用户USER1GRANT ALL PRIVILEGESON stud1.图书信息TO USER1localhost;（2）将管理员表的所有权限授予用户USER1GRANT ALL PRIVILEGESON stud1.管理员TO USER1localhost;（3）将读者表的所有权限授予用户USER1GRANT ALL PRIVILEGESON stud1.读者TO USER1localhost;（4）将图书信息表的所有权限授予用户USER1GRANT ALL PRIVILEGESON stud1.借阅TO USER1localhost;/*查询用户USER1的权限*/SHOW GRANTSFOR USER1localhost;5.创建存储过程定义：存储过程是由过程化SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，因此称它为存储过程，使用时只要调用即可。（1）、/*定义一个存储过程，更改管理员表的所有联系方式：把5换成6*/DELIMITER /DROP PROCEDURE IF EXISTS Sno;CREATE PROCEDURE Sno( )BEGINUPDATE 管理员SET 联系方式=REPLACE(联系方式,5,6)WHERE 联系方式 LIKE%5%;END/delimiter ;/*先查看信息*/select 联系方式from 管理员;/*再调用存储过程*/call Sno ( );/*最后查看是否成功调用存储过程*/select *from 管理员;（2）、/*定义一个存储过程，更新图书信息表的所有库存数量：库存数量+100*/DELIMITER /DROP PROCEDURE IF EXISTS booksum;CREATE PROCEDURE booksum( )begin UPDATE 图书信息SET 库存数量=库存数量+100;END/DELIMITER ;/*先查看信息*/SELECT 书名,库存数量FROM 图书信息;/*再调用存储过程*/call booksum( );/*最后查看是否成功调用存储过程*/SELECT 书名,作者,库存数量FROM 图书信息;6.创建触发器定义：触发器是用户定义在关系表上的一类由事件驱动的特殊过程。它是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。1./*创建触发器，当图书信息表的库存数量属性进行修改时，若库存数量增加10%，将此次操作记录到book_record表上*/DELIMITER $DROP TRIGGER IF EXISTS BOOK_RECORD;CREATE TRIGGER BOOK_RECORDAFTER UPDATE ON 图书信息FOR EACH ROWBEGINIF (new.库存数量=1.1*old.库存数量)THEN INSERT INTO book_record(编号,书名,原库存数量,现库存数量) VALUES(old.编号,old.书名,old.库存数量,new.库存数量);END IF;END;$DELIMITER ;/*先建好表book_record*/DROP TABLE IF EXISTS book_record ;CREATE TABLE book_record( 编号 char(20) not null, 书名 varchar(20) not null, 原库存数量 char(10) not null, 现库存数量 char(10) not null);/*原库存数量*/SELECT 书名,库存数量FROM 图书信息;/*修改某一图书的库存数量*/UPDATE 图书信息SET 库存数量=600 WHERE 书名=中国近代史;/*检查触发器是否可以使用*/SELECT * FROM book_record;2. /*定义一个AFTER行级触发器，当借阅表的借书数量属性进行修改时，若借书数量增加5%，将此次操作记录到book_lent表上*/DELIMITER $DROP TRIGGER IF EXISTS book_lent;CREATE TRIGGER book_lentAFTER UPDATEON 借阅FOR EACH ROWBEGINIF (new.借书数量1.05*old.借书数量)THEN INSERT INTO book_lent(借书证号,原借书数量,现借书数量) VALUES(old.借书证号,old.借书数量,new.借书数量);END IF;E