我的GitHub ：GitHub - FunnyGuy525/LibraryManageSystem)

一、数据库结构设计

1.E-R图

2. 数据库创建语句

create database if not exists tsjy;

use tsjy;

3. 表结构设计说明

借阅人表是一个实体表，它存储了借阅人的基本信息，如证件号、姓名、类别、已借数目和电话。证件号是借阅人表的主键，它可以唯一标识每个借阅人。类别字段有一个检查约束，它限制了类别只能是教师或学生。已借数目字段有一个大于等于0的约束表示借书数量不会为负数，以及一个默认值0，它表示每个借阅人初始时没有借阅任何图书。

图书表是另一个实体表，它存储了图书的基本信息，如图书编号、书名、类别和是否借出。图书编号是图书表的主键，它可以唯一标识每本图书。是否借出字段是一个布尔类型，它表示图书是否已经被借出。类别字段有一个检查约束，它限制了每本书只能是已有的某一种类别。是否借出字段有一个默认值，它表示每本图书初始时都没有被借出。

借阅信息表是一个关系表，它存储了借阅人和图书之间的多对多关系，以及相关的借阅信息，如借出日期、应归还日期和实际归还日期。借阅信息表有两个外键：证件号和图书编号，它们分别引用了借阅人表和图书表的主键。这样就可以确保每条借阅信息都对应一个有效的借阅人和一个有效的图书。借出日期字段是一个日期/时间类型，它表示图书被借出的日期。应归还日期字段也是一个日期/时间类型，它表示图书应该归还的日期。应归还日期字段是一个计算字段，它根据借出日期加上30天来计算。实际归还日期字段也是一个日期/时间类型，它表示图书实际归还的日期。

4. 数据表创建语句

create table borrowers(

     id char(6) not null,

    name char(10) not null,

    type enum('teacher', 'student') NOT NULL,

    amount int not null default 0 check(amount >= 0),

    phone char(11) not null,

     primary key(id)

);

create table books(

     bookid char(20) not null,

    bookname char(20) not null,

    category enum('文学', '理学', '法学', '工学', '其他') not null,

    in_out boolean not null default false,

    primary key(bookid)

);

create table borrow_info(

     borrow_id int not null auto_increment,

    id char(6) not null,

    bookid char(20) not null,

    borrow_date date not null,

    due_date date generated always as (date_add(borrow_date, interval 30 day)),

    return_date date ,

    primary key(borrow_id),

    foreign key(id)references borrowers(id),

    foreign key(bookid)references books(bookid),

    check(return_date >= borrow_date or return_date is null)

);

二、功能设置

1、数据操作测试。

# 1.创建视图显示所有逾期未归还的借阅信息（包括借阅人姓名，借阅人类别，书名，借出日期，应归还日期，逾期时长）

create view overdue_info(借阅人姓名,借阅人类别,书名,借出日期,应归还日期,逾期时长) as

select name,type,bookname,borrow_date,due_date,datediff(curdate(),due_date)

     from borrow_info bi join borrowers b on bi.id = b.id

    join books bk on bi.bookid = bk.bookid

    where bi.return_date is null and curdate() > bi.due_date;

# 2.存储过程首先应当判断书籍未被借出去时借阅才能成功，

#数据才可以正确插入，否则借阅失败，同时抛出一个自定义的错误信息，提示这本书已经被借出。

#借阅人表中的借阅数量的修改可以通过触发器实现

delimiter $$

create procedure borrow_book(in p_id char(6),in p_bookid char(20))

begin

  declare v_in_out boolean;

  select in_out into v_in_out from books where bookid = p_bookid;

  if v_in_out = false then

    # update borrowers set amount = amount + 1 where id = p_id;

    update books set in_out = true where bookid = p_bookid;

    insert into borrow_info(id, bookid, borrow_date) values(p_id, p_bookid, curdate());

  else

    signal sqlstate '45000' set message_text = '书籍已被借出';

  end if;

end$$

delimiter ;

#3. 还书将书籍在库信息修改为true，表示归还入库，同时将今天的日期填入借阅信息表中的归还日期。

# 借阅人表中的借阅数量的修改可以通过触发器实现

delimiter $$

create procedure return_book(in p_id char(6),in p_bookid char(20))

begin

     # update borrowers set amount = amount - 1 where id = p_id;

    update books set in_out = false where bookid = p_id;

    update borrow_info set return_date = curdate() where id = p_id and bookid = p_bookid;

end$$

delimiter ;

#4.查询借阅人姓名

delimiter $$

create function get_borrower_name(p_bookid char(20)) returns char(10)

begin

     declare v_name char(10);

    select name into v_name from borrowers b

    join borrow_info bi on b.id = bi.id

    join books bk on bk.bookid = bi.bookid

    where bi.bookid = p_bookid;

    return v_name;

end$$

delimiter ;

#5.计算某借阅人还能借阅的图书数目，学生限额5本，教师限额10本

delimiter $$

create function get_available_books(p_id char(6)) returns int

begin

     declare v_type enum('teacher', 'student');