图书管理系统–MySQL数据库设计

目录

建表：

 约束-constraints： 

视图： 

 函数：

 触发器：

存储过程：

数据库备份：

图书管理系统结构：

图书管理系统E-R图 ：

建表：

管理员：Administrator（角色名，密码，备注）

读者：TReader（借书证号，密码，姓名，性别，出生时间，专业，借书量，照片，备注，联系方式）

图书：TBook（ISBN，书名，作译者，出版社，出版年月，价格，复本量，库存量，分类号，内容提要，封面照片）

借阅：TLend（借书证号，ISBN，图书ID，借书时间，应还时间）

归还：HLend（编号、借书证号、ISBN、图书ID、借书时间、还书时间）

借书情况：TBLend（图书ID，ISBN，是否借出）

（注：红色是主键，蓝色是外键）

图书管理数据库名称定义为MBOOK，所有者为系统管理员。 为了保存图书管理系统中需要的信息，MBOOK数据库需要包含一个数据文件和一个日志文件。数据文件用于存放数据库的数据，日志文件用于存放日志信息。 存放数据库的数据可以使用多个数据文件

MBOOK数据库数据文件名称定义为MBOOK.MYD，索引文件mbook.MYI，数据库表结构文件mbook.frm

日志文件名称定义为MBOOK_log.ldf   ??

并且在表上增加约束，

读者表Treader:借书量>=0 ,:借书量<5;

Tbook表图书库存量
<副本量;< p="">

Tlend表：增加两个外键 TBook（ISBN）和 Treader（借书证号）；

增加一个视图用来查询：

TLend.借书证号,TReader.姓名,TReader.借书量,TLend.ISBN,TBook.书名,TBook.出版社,

TBook.价格,TLend.图书ID,TLend.借书时间；

条件：Treader内连接TLend（Treader.借书证号=TLend.借书证号），TLend内连接TBook（TLend.ISBN=TBook.ISBN）；

功能实现：

“生成图书借出情况”功能实现  PROCEDURE 

此功能使用存储过程来实现，存储过程名为BookID_Generate，参数：ISBN(图书的ISBN)，count(复本量)，firstID（起始的图书ID）。使用循环向TBLend表中插入count条记录，图书ID的起始值为加1firstID，之后每增加一条借出记录图书ID值。

“图书借阅”功能实现   create procedure Book_Borrow

编写思路：

（1）此功能使用存储过程来实现，存储过程名称为Book_Borrow。参数：借书证号（in_ReaderID）、ISBN（in_ISBN）、图书ID（in_BookID）、执行信息（out_str）。根据存储过程的前3个参数，实现读者图书“借阅”。第四个参数为输出参数，将存储过程的执行情况以字符串形式赋予此参数。

（2）根据“借书证号”查询读者信息表（TReader）是否存在该读者，如果不存在，则将输出参数out_str赋值为“该读者不存在”并返回0，存储过程结束，表示不能借书。

（3）根据“ISBN”查询图书信息表（TBook），查询是否存在该图书，如果不存在，则将输出参数赋值为“该图书不存在”并返回0，存储过程结束，表示不能借书。

（4）根据“借书证号”查询读者信息表（TReader），查询该读者的借书量。如果借书量=5，则将输出参数赋值为“读者借书量不能大于5”并返回0，存储过程结束，表示不能借书。

（5）根据“ISBN”查询图书信息表（TBook）中该图书的库存量。如果库存量=0，则将输出参数赋值为“图书库存量为0”并返回0，存储过程结束，表示不能借书。

（6）查询借阅表（TLend），查看该读者是否已经借阅该图书，如果已经借过，则将输出参数赋值为“读者已经借过该书”并返回0，存储过程结束，表示不能借书。

（7）查询借阅表（TLend），查看该图书ID是否已经存在，如果存在则将输出参数赋值为“该图书ID已存在”并返回0，存储过程结束，表示不能添加借书记录。

（8）使借阅表（TLend）增加一条该读者借书记录；

读者信息表（TReader）中该读者的借书量加1； 

图书信息表（TBook）该图书（对应ISBN）记录的库存量减1；

将是否借出表（TBLend）中该图书（对应的图书ID）记录的“是否借出”信息修改为1。

存储过程结束，将输出参数赋值为“借书成功”并返回1，表示借书成功。

（9）如果存储过程执行过程中遇到错误则回滚之前进行的操作，并将输出参数赋为“执行过程中遇到错误”并返回0，表示存储过程执行中遇到错误，回滚到执行存储过程前的状态。

“同步删除图书借出情况”功能实现   create trigger TBook_delete ON TBook

实现功能：在TBook表中删除一条图书记录时，同时删除TBLend表中与该书相关的记录，以及在TLend表中删除相应记录。

编写思路：该功能使用在TBook表中定义删除触发器的方法实现。触发器名称为Book_delete，触发器类型为DML触发器，在对TBook表进行了DELETE操作后激活。

“图书归还”功能实现 create trigger TLend_after_delete on TLend after delete

实现功能：

当读者“归还”图书时，即删除借阅表（TLend）中的一条借书记录时，

读者信息表（TReader）该读者的借书量减1；

图书信息表（TBook）该图书记录的库存量加1；

图书是否借出表（TBLend）的是否借出信息改为0  ××    TBook中库存量=副本量 ；

还书记录表（HTLend）添加一条该读者的还书记录。

“统计借书次数”功能实现 CREATE FUNCTION L_count( @ReaderID char(6) ) 

实现功能：图书管理员输入读者的借书证号，可以统计读者总共借过多少次书，包括借过归还和在借的次数。

编写思路：本功能使用用户自定义标量函数实现，函数名为L_count。使用借书证号作为参数，参数名为ReaderID，通过查询借阅表TLend和还书记录表HLend中该读者的借书记录，将查得的次数相加得到该读者总的借书次数并返回

use mbook; CREATE TABLE `TBook` ( `ISBN` char(18) NOT NULL PRIMARY KEY, `书名` varchar(100) NOT NULL, `作译者` varchar(100) NOT NULL, `出版社` varchar(100) NOT NULL, `出版年月` char(10) NULL, `价格` float NULL, `复本量` int NOT NULL, `库存量` int NOT NULL DEFAULT '1', `分类号` char(18) NULL, `内容提要` varchar(150) NULL, `封面照片` varbinary(200) NULL ); 

use mbook; CREATE TABLE `TReader` ( `借书证号` char(6) NOT NULL PRIMARY KEY, `密码` varchar(20) NOT NULL, `姓名` char(8) NOT NULL, `性别` bit NOT NULL, `出生时间` date NOT NULL, `专业` char(12) NOT NULL, `借书量` int NOT NULL DEFAULT '0', `照片` varbinary(200) NULL, `备注` varchar(200) NULL ); 

create table `HLend` ( `编号` int not null primary key auto_increment, `借书证号` char(6) not null, `ISBN` char(18) not null, `图书ID` char(10) not null, `借书时间` datetime not null, `还书时间` datetime not null );

create table `TLend` ( 借书证号 char(6) not null, ISBN char(18) not null, 图书ID char(10) primary key, 借书时间 datetime not null, 应还时间 datetime not null ); -- 应还时间 as 借书时间+5

create table `TBLend` ( 图书ID char(10) primary key, ISBN char(18) not null, 是否借出 bit not null );

CREATE TABLE Administrator ( `角色名` char(20) NOT NULL PRIMARY KEY, `密码` VARCHAR(20) NOT NULL, `备注` VARCHAR(100) NULL );

 约束-constraints： 

use mbook; alter table TReader add check(借书量>=0 and 借书量<=5); alter table TBook add constraint CK_TBook check(库存量<=复本量); alter table tlend add constraint foreign key (readerId) references treader(readerId) on delete cascade; alter table tlend add constraint foreign key (isbn) references tbook(isbn) on delete cascade; -- alter table TLend add foreign key(借书证号) references TReader(借书证号); -- alter table TLend add foreign key(ISBN) references TBook(ISBN);

在执行一条sql的时候的时候 突然报错1452 

其实1452的错误主要原因就是：有外键的子表对应的主表中没有数据。

比方说：

class表：

2    普通班

student表：

视图： 

use MBOOK; create view RBL as select TLend.借书证号,TReader.姓名,TReader.借书量,TLend.ISBN, TBook.书名,TBook.出版社,TBook.价格,TLend.图书ID,TLend.借书时间 from TReader inner join TLend on TReader.借书证号=TLend.借书证号 inner join TBook on TLend.ISBN=TBook.ISBN

 函数：

set global log_bin_trust_function_creators=TRUE; DROP FUNCTION IF EXISTS `L_count`; DELIMITER ;; CREATE DEFINER=`root`@`localhost` FUNCTION `L_count`(ReaderID char(6)) RETURNS int(11) BEGIN declare count1 integer; declare count2 integer; select count(readerId) into count1 from tlend where readerId=ReaderID; select count(readerId) into count2 from hlend where readerId=ReaderID; return count1+count2; END ;; DELIMITER ;

 触发器：

delimiter $$ CREATE TRIGGER `tlend_AFTER_DELETE` AFTER DELETE ON `tlend` FOR EACH ROW BEGIN declare _ReaderID char(6); declare _ISBN char(18); declare _Book_ID char(10); declare _LTime datetime; declare flag int; declare cur_return cursor for select 借书证号,ISBN,图书ID,借书时间 from deleted; set flag=0; open cur_return; fetch cur_return into _ReaderID,_ISBN,_Book_ID,_LTime; loop_label:Loop update TReader set 借书量=借书量-1 where 借书证号=ReaderID; update TBook set 库存量=库存量+1 where ISBN=ISBN; update TBLend set 是否借出=0 where 图书ID=Book_ID; insert into HLend(借书证号,ISBN,图书ID,借书时间,还书时间) values(_ReaderID,_ISBN,_Book_ID,_LTime,NOW()); fetch cur_return into _ReaderID,_ISBN,_Book_ID,_LTime; if(flag=1) then leave loop_label; end if; end Loop; close cur_return; END $$ delimiter ;

DROP TRIGGER IF EXISTS `TBook_delete`; DELIMITER ;; CREATE TRIGGER `TBook_delete` AFTER DELETE ON `tbook` FOR EACH ROW begin delete from `TBLend` where ISBN=old.ISBN; -- delete from `TLend` where ISBN=old.ISBN; end ;; DELIMITER ;

存储过程：

use mbook; -- drop procedure if exists call `BookID_Generate`; delimiter $ create procedure `BookID_Generate` (in_ISBN char(18),_count int,firstID char(10)) begin declare cnt int ; set cnt=_count; while cnt>0 do insert into `TBLend` values(firstID,in_ISBN,0); set firstID=firstID+1; set cnt=cnt-1; end while; end $ delimiter ;

use mbook; delimiter $$ create procedure Book_Borrow (in_ReaderID char(6),in_ISBN char(18),in_BookID char(10),out_str char(30) ) begin if not exists(select * from TReader where 借书证号=in_ReaderID) then set out_str='该读者不存在'; end if; if not exists(select * from TBook where ISBN=in_ISBN) then set out_str='该图书不存在'; end if; if (select 借书量 from TReader where 借书证号=in_ReaderID)=5 then set out_str='读者借书量不能大于5'; end if; if (select 库存量 from TBook where ISBN=in_ISBN)=0 then set out_str='图书库存量为0'; end if; if (in_ISBN in (select ISBN from TLend where 借书证号=in_ReaderID) ) then set out_str='读者已经借过该书'; end if; if exists (select * from TLend where 图书ID=in_BookID) then set out_str='该图书已经被借出'; end if; set autocommit=0; start transaction; insert into TLend(借书证号,ISBN,图书ID,借书时间)values(in_ReaderID,in_ISBN,in_BookID,GETDATE()); update TReader set 借书量=借书量+1 where 借书证号=in_ReaderID; update TBook set 库存量=库存量-1 where ISBNin_ISBN; update TBLend set 是否借出=1 where 图书ID=in_BookID; commit; end $$ delimiter ; 

数据库备份：

use mbook; delimiter $ create procedure DB_backup (path varchar(100)) begin backup database MBOOK to disk=path with init; end $ delimiter ;

       这篇文章主要是记录数据库的设计，好多人在评论区要代码，前端展示的代码网上很多呀，你们自己整合一下嘛，不要老想着抄作业... ...