MYSQL课程设计——图书管理系统（二）

四、逻辑结构设计

1.系统功能模块图

画出系统功能模块图，并对子模块功能进行详细说明。图示例如下。

 

2.关系模式的设计

要求所有关系范式至少满足3NF要求，示例如下：

admin表用于存放教师个人信息，该表满足第三范式要求。

admin表

字段名	数据类型	长度	备注	其它
admin_id	VARCHAR	10	管理员编号	主键
admin_name	VARCHAR	30	姓名	非空
admin_pd	VARCHAR	20	密码	非空
admin_sex	VARCHAR	2	性别	取值男或者女
admin_tel	int	11

User表用于存放用户信息，该表满足第三范式要求。

字段名	数据类型	长度	备注	其它
ID	VARCHAR	10	用户编号	主键
name	VARCHAR	30	姓名	非空
pd	VARCHAR	20	密码	非空
sex	VARCHAR	1	性别	取值男或者女
tel	int	11	电话
fee	int	5	欠费情况

book表用于存放图书信息，该表满足第三范式要求。

字段名	数据类型	长度	备注	其它
book_id	CHAR	10	图书编号	主键
book_name	VARCHAR	30	书名	非空
book_author	VARCHAR	10	作者	非空
book_publish	VARCHAR	20	出版社	非空
book_price	int	5	价格	非空
book_number	int	10	库存数量
book_location	char

借阅信息表用于存放图书的借阅信息，该表满足第三范式

字段名	数据类型	长度	备注	其它
ID	CHAR	10	用户编号	主键
ISBN	CHAR	13	ISBN号
book_id	CHAR	10	图书编号	主键
borrowed_time	Date		借书时间
return_time	Date		还书时间

3.视图的设计

（1）reader_book_view来源于book表，

筛选的SQL语句是：

CREATE VIEW reader_book_view(Book_id,ISBN,Book_name,Book_author,Book_publish,Book_price, Book_type,Book_location)

AS

SELECT Book_id,ISBN,Book_name,Book_author,Book_publish,Book_price, Book_type,Book_location

FROM book

WITH LOCAL CHECK OPTION

语义：显示所有图书的信息，包括图书编号、书名、作者、出版社、价格、库存、类型、位置

reader_book_view视图结构

字段名	数据类型	长度	备注
Book_id	char(10)	10	图书编号
ISBN	Char(13)	13	国际标准书号
Book_name	char (20)	20	书名
Book_author	char (10)	10	作者
Book_publish	char (20)	20	出版社
Book_price	Decimal(3,2)	3，2	价格
Book_type	char(5)	5	类型
Book_location	char (20)	20	位置

（2）reader_borrowinfo_vier来源于borrowinfo表，

筛选的SQL语句是：

CREATE VIEW reader_borrowinfo_view(ID,ISBN,Book_id,Borrowed_time,Return_time)

AS

SELECT ID, ISBN,Book_id,Borrowed_time,Return_time

FROM borrowinfo

WITH LOCAL CHECK OPTION

语义：显示所有借阅信息，包括读者编号、图书编号、借阅时间、还书时间

reader_borrowinfo_vier视图结构

字段名	数据类型	长度	备注
ID	char(10)	10	读者编号
Book_id	char(10)	10	图书编号
Borrowed_time	data		借阅时间
Return_time	date		还书时间

（3）reader_user_vier来源于user表，

筛选的SQL语句是：

CREATE VIEW reader_user_view(ID,name,sex,tel,pd,fee)

AS

SELECT ID,name,sex,tel,pd,fee

FROM user

WITH LOCAL CHECK OPTION

语义：显示所有读者信息，包括读者编号、读者姓名、读者性别、联系电话、密码、欠费数

reader_user_vier视图结构

字段名	数据类型	长度	备注
ID	char(10)	10	读者编号
name	char(10)	10	读者姓名
sex	char(1)	1	读者性别
tel	char(11)	11	联系电话
pd	varchar(20)	20	密码
fee	Tinyint(3)	3	欠费数

4.用户设计

根据画出系统功能模块图，说明该系统分为几种用户，用户名和密码是多少，分别拥有哪些权限，能对哪些库、表、视图进行操作。

（1）普通用户

1）用户名

用户名为ID，即读者编号；

2）密码

初始密码为123456，后期可以根据用户需求自行更改20为以内字符类型；

3）权限

用户可以对视图reader_user_vier里的联系电话和密码进行操作；

（2）管理员

1）用户名

用户名为admin_id，即管理员编号；

2）密码

无初始密码，需要录入管理员信息时设置；

3）权限

管理员可以对admin表里的联系电话和密码进行操作、book表、user表进行操作；

5.触发器设计

结合表说明设计了哪些触发器，触发器做什么，设计触发器的必要性和触发器代码。

（1）用于用户密码加密

代码：

CREATE TRIGGER userjiami BEFORE INSERT

ON `user` FOR EACH ROW

SET new.pd=PASSWORD(new.pd);

（2）用于管理员密码加密

代码：

CREATE TRIGGER adminjiami BEFORE INSERT

ON `admin` FOR EACH ROW

SET new.admin_pd=PASSWORD(new.admin_pd);

6.存储过程和函数设计

（1）借书过程存储过程

使用说明：输入读者编号，图书编号，根据图书的需求进行借书，判断借书的条件，有库存，借书最大值，返回成功与否的提示信息；

代码：

相关代码：

delimiter $$

CREATE PROCEDURE borrow_info(IN sid CHAR(10), IN bid CHAR(10), OUT message VARCHAR(20))

BEGIN

DECLARE stock INT DEFAULT 0;

DECLARE number INT DEFAULT 0;

DECLARE money INT DEFAULT 0;

DECLARE IB CHAR(13);

SELECT ISBN INTO IB FROM book WHERE book_id=bid;

SELECT fee INTO money FROM user WHERE ID=sid;

SELECT COUNT(ISBN) INTO number FROM borrowinfo WHERE ISBN=(SELECT ISBN FROM book WHERE Book_id=bid) AND return_time IS NULL;

SELECT COUNT(ISBN) INTO stock FROM borrowinfo WHERE book_id=bid AND return_time IS NULL;

IF money=0 THEN

IF number<15 THEN

IF stock<5 THEN

INSERT INTO borrowinfo(ID,ISBN,book_id,borrowed_time,return_time) VALUES(sid,IB,bid,NOW(),NULL);

SELECT '借阅成功！' INTO message;

ELSE

SELECT '无库存！无法借阅！' INTO message;

END IF;

ELSE

SELECT '超过借书最大值！无法借阅！' INTO message;

END IF;

ELSE

SELECT '欠费！无法借阅！' INTO message;

END IF;

ENDEND $$

delimiter;

（2）还书存储过程

使用说明：输入读者编号、图书编号，返回还书的信息；

代码：

delimiter $$

CREATE PROCEDURE return_info(IN sid CHAR(10),IN bid CHAR(10), OUT message VARCHAR(20))

BEGIN

UPDATE borrowinfo SET return_time= CURRENT_DATE  WHERE ID=sid AND book_id=bid AND return_time IS NULL;

SELECT '还书成功！' INTO message;

END $$;

delimiter;

（3）用户查询存储过程（按照名字查询图书信息）

使用说明：输入图书编号，返回图书的基本信息，若有借阅信息，则再返回以借阅的数量；

代码：

delimiter $$

CREATE PROCEDURE lookup1(IN num CHAR(10))

READS SQL DATA

BEGIN

DECLARE cha INT DEFAULT 0;

SELECT COUNT(ISBN) INTO cha FROM reader_borrowinfo_view WHERE ISBN=(SELECT ISBN FROM book WHERE Book_id=num) AND Return_time IS NULL;

SELECT Book_id,ISBN,Book_name,Book_author,Book_location FROM reader_book_view WHERE Book_id=num;

IF cha>0 THEN

SELECT ISBN,COUNT(ISBN) '已借阅数量' FROM reader_borrowinfo_view WHERE ISBN=(SELECT ISBN FROM book WHERE Book_id=num) AND Return_time IS NULL;

END IF;

END $$

delimiter;

（4）用户查询存储过程（按照名字查询个人信息）

使用说明：输入读者编号，则返回读者的除密码外的个人信息；

代码：

delimiter $$

CREATE PROCEDURE lookupuser(IN uid CHAR(10))

READS SQL DATA

BEGIN

SELECT ID,name,sex,tel,fee FROM reader_user_view WHERE ID=uid;

END $$

delimiter;

（5）用户查询存储过程（按照名字查询个人借阅信息）

使用说明：输入读者编号，则返回读者的个人借阅信息

代码：

delimiter $$

CREATE PROCEDURE lookup(IN uid CHAR(10))

READS SQL DATA

BEGIN

SELECT * FROM reader_borrowinfo_view WHERE ID=uid;

END $$

delimiter;

（6）统计信息存储过程（按类型）

使用说明：输入图书的类型，则返回此类图书的数量；

代码：

delimiter $$

CREATE PROCEDURE lookup(IN uid CHAR(10))

READS SQL DATA

BEGIN

SELECT book_type,COUNT(book_id) '类型的数量' FROM book WHERE book_type=tp;

END $$

delimiter;

（7）计费存储过程

使用说明：使用游标遍历borrowinfo表的ID、borrowed_time、return_time数据，计算是否逾期，并且计算费用；

代码：

delimiter $$

CREATE PROCEDURE upfee()

BEGIN  

DECLARE sid CHAR(10);

DECLARE bt DATE;

DECLARE rt DATE;

DECLARE done INT DEFAULT FALSE;

DECLARE my_cursor CURSOR for (SELECT ID,borrowed_time,return_time FROM borrowinfo);

DECLARE CONTINUE HANDLER FOR NOT found SET done = TRUE;

OPEN my_cursor;

  myloop: LOOP

    FETCH my_cursor INTO sid,bt,rt;

    IF done THEN

      LEAVE myloop;  

    END IF;  

IF (rt IS NULL AND DATEDIFF(NOW(),bt)>15) THEN

UPDATE `user` SET fee=fee+1 WHERE ID=sid;

END IF;

    COMMIT;

  END LOOP myloop;  

CLOSE my_cursor;

END $$

delimiter;

（8）登录函数

使用说明：输入读者编号、密码，判断是用户名和密码是否正确，则返回成功登录与否的信息；

代码：

Delimiter &&

CREATE PROCEDURE login(uid CHAR(10),psd CHAR(20))

RRTURNS VARCHAR(20) READS SQL DATA;

BEGIN

DECLARE lg VARCHAR(20);

DECLARE sid INT DEFAULT 0;

DECLARE spd INT DEFAULT 0;

SELECT COUNT(ID) INTO sid FROM user WHERE ID=uid;

IF sid>0 THEN

    SELECT COUNT(ID) INTO spd FROM user WHERE ID=uid AND pd=PASSWORD(psd);

IF spd=1 THEN

SET lg='登录成功！';

ELSE

SET lg='密码错误！登录失败！';

END IF;

ELSE

SET lg='用户名错误！登录失败！';

END IF;

RETURN lg;

END &&

Delimiter;

（9）修改密码函数

使用说明：输入读者编号、旧密码、新密码，判断用户名和旧密码正确与否，返回修改密码正确与否的信息；

代码：

delimiter &&

CREATE FUNCTION changepd(uid CHAR(10),oldpd CHAR(20),newpd VARCHAR(20))

RETURNS VARCHAR(20) READS SQL DATA

BEGIN

DECLARE cpd VARCHAR(20);

DECLARE sid INT DEFAULT 0;

DECLARE spd INT DEFAULT 0;

SELECT COUNT(ID) INTO sid FROM user WHERE ID=uid;

IF sid>0 THEN

    SELECT COUNT(ID) INTO spd FROM user WHERE ID=uid AND pd=PASSWORD(oldpd);

IF spd=1 THEN

UPDATE user SET pd=PASSWORD(newpd) WHERE ID=uid;

SET cpd='修改成功！';

ELSE

SET cpd='旧密码错误！修改失败！';

END IF;

ELSE

SET cpd='用户名错误！修改失败！';

END IF;

RETURN cpd;

END &&

delimiter;

7.安全性分析

使用了哪些安全策略。如用户、视图、触发器、备份等。

1.对用户的权限进行限制，读者修改的权限仅有对自己密码的修改，以及对部分视图的查阅功能；

2.通过触发器对导入的用户和管理员的明文密码进行哈希加密（其中的PASSWORD函数）；

3.设置了图书管理系统库的自动备份。

五、数据库物理设计

1.选择合适的存储引擎

存储引擎：InnoDB

特点：

（1）支持事务，支持4个事务隔离级别

MySQL从5.5.5版本开始，默认的存储引擎为 InnoDB；

（2）读写阻塞与事务隔离级别相关

能非常高效的缓存索引和数据

表与主键以簇的方式存储

支持分区、表空间，类似oracle数据库

支持外键约束，5.5前不支持全文索引，5.5后支持全文索引；

（3）对硬件资源要求还是比较高的场合

行级锁定，但是全 表扫描仍然会是表级锁定，如

update table set a=1 where user like ‘%lic%’;

2.建立索引

（1）图书索引

原因：通过它能加快借阅关系中图书编号记录的查找速度。

代码：

ALTER TABLE `book`

ADD INDEX `index_book` (`book_id`) USING BTREE COMMENT '图书索引';

（2）用户索引

 原因：通过它能加快借阅关系中用户编号记录的查找速度。

代码：

ALTER TABLE `user`

ADD INDEX `index_user` (`ID`) USING BTREE COMMENT '用户索引';

六、数据库实施

1.创建数据库及数据对象

（1）建立user表

CREATE TABLE `user` (

  `ID` char(10) COLLATE utf8_bin NOT NULL COMMENT '用户编号',

  `name` char(10) COLLATE utf8_bin NOT NULL COMMENT '用户姓名',

  `sex` char(1) COLLATE utf8_bin DEFAULT NULL,

  `tel` char(11) COLLATE utf8_bin DEFAULT NULL,

  `pd` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '密码',

  `fee` tinyint(3) DEFAULT NULL COMMENT '欠费情况',

  PRIMARY KEY (`ID`),

  KEY `index_user` (`ID`) USING BTREE COMMENT '用户索引'

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

（2）建立admin表

CREATE TABLE `admin` (

  `admin_id` char(10) COLLATE utf8_bin NOT NULL COMMENT '管理员编号',

  `admin_name` char(10) COLLATE utf8_bin NOT NULL COMMENT '姓名',

  `admin_sex` char(1) COLLATE utf8_bin DEFAULT NULL COMMENT '性别',

  `admin_tel` char(11) COLLATE utf8_bin DEFAULT NULL COMMENT '管理员电话',

  `admin_pd` varchar(50) COLLATE utf8_bin NOT NULL,

  PRIMARY KEY (`admin_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

（3）建立book表

CREATE TABLE `book` (

  `book_id` char(10) COLLATE utf8_bin NOT NULL COMMENT '图书编号',

  `ISBN` char(13) COLLATE utf8_bin DEFAULT NULL,

  `book_name` char(50) COLLATE utf8_bin DEFAULT NULL COMMENT '书名',

  `book_author` char(20) COLLATE utf8_bin DEFAULT NULL COMMENT '作者',

  `book_publish` char(20) COLLATE utf8_bin DEFAULT NULL COMMENT '出版社',

  `book_price` decimal(5,2) DEFAULT NULL COMMENT '价格',

  `book_type` char(5) COLLATE utf8_bin DEFAULT NULL COMMENT '类型',

  `book_location` char(20) COLLATE utf8_bin DEFAULT NULL COMMENT '位置',

  PRIMARY KEY (`book_id`),

  KEY `index_book` (`book_id`) USING BTREE COMMENT '图书索引'

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

（4）建立borrowinfo表

CREATE TABLE `borrowinfo` (

  `ID` varchar(10) COLLATE utf8_bin NOT NULL COMMENT '用户编号',

  `ISBN` char(13) COLLATE utf8_bin NOT NULL,

  `book_id` varchar(10) COLLATE utf8_bin NOT NULL COMMENT '图书编号',

  `borrowed_time` date DEFAULT NULL COMMENT '借书时间',

  `return_time` date DEFAULT NULL COMMENT '还书时间',

  PRIMARY KEY (`ID`,`book_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

2.数据备份和恢复方案

使用mysql管理工具navicat配置数据库的备份

从备份中恢复数据库

七、总结

图书借阅管理是学校工作中的一项重要内容，学校图书馆，书目繁多，用户的信息冗杂，且信息数据在每天变化，如果采用人工方式进行管理控制，不仅工作量庞大，而且容易出错。浪费了许多的人力和物力，已不能适应时代的发展。在当今信息时代，这种传统的管理方法必然被以计算机为基础的信息管理系统所代替，图书管理系统可以有效的管理图书资源，控制图书增加，删除，修改，学生借阅和返还的流程，缩小开支，提高工作效率与准确率，能够节省时间，既方便了管理人员，又方便了学生，对图书馆的管理有很大的帮助，极大地提高了效率。