学校图书管理系统
某大学图书馆希望建立一个数据库系统来管理图书借阅情况。图书馆用户有学生和教师两大类。每个用户都可以借阅多本图书，每本图书可供借阅的用户数由图书馆藏量决定。学生用户的借阅数量不能超过5本，教师用户按不同职称借阅量有所不同，副教授以上可以借阅10本，讲师可以借阅8本，助教可以借阅6本。图书借阅有一定期限，最长为三个月，超期需要缴纳罚款。图书需要分类管理，方便用户借阅。
以下为mysql上运行的sql语句
-- 图书馆借阅管理系统数据库设计
-- 创建数据库
CREATE DATABASE IF NOT EXISTS library_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE library_system;
-- 1. 用户类型表
CREATE TABLE user_types (
type_id INT PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL UNIQUE COMMENT '用户类型名称',
max_borrow_count INT NOT NULL COMMENT '最大借阅数量',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) COMMENT '用户类型表';
-- 2. 图书分类表
CREATE TABLE book_categories (
category_id INT PRIMARY KEY AUTO_INCREMENT,
category_name VARCHAR(50) NOT NULL UNIQUE COMMENT '分类名称',
category_code VARCHAR(10) NOT NULL UNIQUE COMMENT '分类编码',
parent_id INT DEFAULT NULL COMMENT '父分类ID',
description TEXT COMMENT '分类描述',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (parent_id) REFERENCES book_categories(category_id)
) COMMENT '图书分类表';
-- 3. 用户表
CREATE TABLE users (
user_id INT PRIMARY KEY AUTO_INCREMENT,
user_code VARCHAR(20) NOT NULL UNIQUE COMMENT '用户编号',
name VARCHAR(50) NOT NULL COMMENT '姓名',
user_type_id INT NOT NULL COMMENT '用户类型ID',
title VARCHAR(20) DEFAULT NULL COMMENT '职称(教师用)',
phone VARCHAR(15) COMMENT '联系电话',
email VARCHAR(100) COMMENT '邮箱',
department VARCHAR(100) COMMENT '院系/部门',
status ENUM('active', 'inactive', 'suspended') DEFAULT 'active' COMMENT '用户状态',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_type_id) REFERENCES user_types(type_id),
INDEX idx_user_code (user_code),
INDEX idx_name (name)
) COMMENT '用户表';
-- 4. 图书信息表
CREATE TABLE books (
book_id INT PRIMARY KEY AUTO_INCREMENT,
isbn VARCHAR(20) UNIQUE COMMENT 'ISBN编号',
title VARCHAR(200) NOT NULL COMMENT '书名',
author VARCHAR(100) COMMENT '作者',
publisher VARCHAR(100) COMMENT '出版社',
publish_date DATE COMMENT '出版日期',
category_id INT NOT NULL COMMENT '分类ID',
total_copies INT NOT NULL DEFAULT 1 COMMENT '总册数',
available_copies INT NOT NULL DEFAULT 1 COMMENT '可借册数',
location VARCHAR(50) COMMENT '存放位置',
price DECIMAL(10,2) COMMENT '价格',
description TEXT COMMENT '图书描述',
status ENUM('available', 'unavailable', 'damaged') DEFAULT 'available' COMMENT '图书状态',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES book_categories(category_id),
INDEX idx_isbn (isbn),
INDEX idx_title (title),
INDEX idx_author (author),
INDEX idx_category (category_id)
) COMMENT '图书信息表';
-- 5. 借阅记录表
CREATE TABLE borrow_records (
record_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL COMMENT '用户ID',
book_id INT NOT NULL COMMENT '图书ID',
borrow_date DATE NOT NULL COMMENT '借阅日期',
due_date DATE NOT NULL COMMENT '应还日期',
return_date DATE DEFAULT NULL COMMENT '实际归还日期',
renewal_count INT DEFAULT 0 COMMENT '续借次数',
status ENUM('borrowed', 'returned', 'overdue', 'lost') DEFAULT 'borrowed' COMMENT '借阅状态',
fine_amount DECIMAL(10,2) DEFAULT 0.00 COMMENT '罚款金额',
fine_paid BOOLEAN DEFAULT FALSE COMMENT '罚款是否已付',
notes TEXT COMMENT '备注',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (book_id) REFERENCES books(book_id),
INDEX idx_user_id (user_id),
INDEX idx_book_id (book_id),
INDEX idx_borrow_date (borrow_date),
INDEX idx_due_date (due_date),
INDEX idx_status (status)
) COMMENT '借阅记录表';
-- 6. 罚款记录表
CREATE TABLE fine_records (
fine_id INT PRIMARY KEY AUTO_INCREMENT,
record_id INT NOT NULL COMMENT '借阅记录ID',
user_id INT NOT NULL COMMENT '用户ID',
fine_type ENUM('overdue', 'damage', 'lost') NOT NULL COMMENT '罚款类型',
fine_amount DECIMAL(10,2) NOT NULL COMMENT '罚款金额',
overdue_days INT DEFAULT 0 COMMENT '超期天数',
status ENUM('unpaid', 'paid', 'waived') DEFAULT 'unpaid' COMMENT '支付状态',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
paid_at TIMESTAMP NULL DEFAULT NULL COMMENT '支付时间',
FOREIGN KEY (record_id) REFERENCES borrow_records(record_id),
FOREIGN KEY (user_id) REFERENCES users(user_id),
INDEX idx_user_id (user_id),
INDEX idx_status (status)
) COMMENT '罚款记录表';
-- 删除可能存在的旧触发器
DROP TRIGGER IF EXISTS update_available_copies_after_borrow;
DROP TRIGGER IF EXISTS update_available_copies_after_return;
-- 创建触发器：借书时更新可借册数
DELIMITER $
CREATE TRIGGER update_available_copies_after_borrow
AFTER INSERT ON borrow_records
FOR EACH ROW
BEGIN
IF NEW.status = 'borrowed' THEN
UPDATE books
SET available_copies = available_copies - 1
WHERE book_id = NEW.book_id AND available_copies > 0;
END IF;
END$
-- 创建触发器：还书时更新可借册数
CREATE TRIGGER update_available_copies_after_return
AFTER UPDATE ON borrow_records
FOR EACH ROW
BEGIN
IF OLD.status = 'borrowed' AND NEW.status = 'returned' THEN
UPDATE books
SET available_copies = available_copies + 1
WHERE book_id = NEW.book_id;
END IF;
END$
DELIMITER ;
-- 插入示例数据
-- 1. 用户类型数据
INSERT INTO user_types (type_name, max_borrow_count) VALUES
('学生', 5),
('助教', 6),
('讲师', 8),
('副教授', 10),
('教授', 10);
-- 2. 图书分类数据
INSERT INTO book_categories (category_name, category_code, description) VALUES
('计算机科学', 'CS', '计算机科学相关图书'),
('文学', 'LIT', '文学作品和文学理论'),
('历史', 'HIST', '历史类图书'),
('数学', 'MATH', '数学相关图书'),
('物理学', 'PHY', '物理学相关图书'),
('经济学', 'ECON', '经济学相关图书'),
('哲学', 'PHIL', '哲学相关图书'),
('艺术', 'ART', '艺术相关图书');
-- 子分类
INSERT INTO book_categories (category_name, category_code, parent_id, description) VALUES
('程序设计', 'CS01', 1, '编程和软件开发'),
('数据库', 'CS02', 1, '数据库系统和管理'),
('人工智能', 'CS03', 1, '人工智能和机器学习'),
('中国文学', 'LIT01', 2, '中国古典和现代文学'),
('外国文学', 'LIT02', 2, '世界各国文学作品'),
('中国历史', 'HIST01', 3, '中国历史'),
('世界历史', 'HIST02', 3, '世界历史');
-- 3. 用户数据
INSERT INTO users (user_code, name, user_type_id, title, phone, email, department) VALUES
-- 学生
('S2024001', '张三', 1, NULL, '13800138001', 'zhangsan@university.edu', '计算机学院'),
('S2024002', '李四', 1, NULL, '13800138002', 'lisi@university.edu', '文学院'),
('S2024003', '王五', 1, NULL, '13800138003', 'wangwu@university.edu', '数学学院'),
('S2024004', '赵六', 1, NULL, '13800138004', 'zhaoliu@university.edu', '物理学院'),
('S2024005', '孙七', 1, NULL, '13800138005', 'sunqi@university.edu', '经济学院'),
-- 教师
('T2024001', '陈教授', 5, '教授', '13900139001', 'chen@university.edu', '计算机学院'),
('T2024002', '刘副教授', 4, '副教授', '13900139002', 'liu@university.edu', '文学院'),
('T2024003', '杨讲师', 3, '讲师', '13900139003', 'yang@university.edu', '数学学院'),
('T2024004', '周助教', 2, '助教', '13900139004', 'zhou@university.edu', '物理学院'),
('T2024005', '吴教授', 5, '教授', '13900139005', 'wu@university.edu', '经济学院');
-- 4. 图书数据
INSERT INTO books (isbn, title, author, publisher, publish_date, category_id, total_copies, available_copies, location, price) VALUES
-- 计算机类
('978-7-115-12345-1', 'Java编程思想', 'Bruce Eckel', '人民邮电出版社', '2023-01-15', 9, 5, 5, 'A1-001', 89.00),
('978-7-115-12345-2', 'MySQL必知必会', 'Ben Forta', '人民邮电出版社', '2023-02-20', 10, 3, 3, 'A1-002', 59.00),
('978-7-115-12345-3', '机器学习实战', 'Peter Harrington', '人民邮电出版社', '2023-03-10', 11, 4, 4, 'A1-003', 79.00),
('978-7-115-12345-4', '算法导论', 'Thomas H. Cormen', '机械工业出版社', '2023-04-05', 1, 6, 6, 'A1-004', 128.00),
('978-7-115-12345-5', 'Python编程从入门到实践', 'Eric Matthes', '人民邮电出版社', '2023-05-12', 9, 8, 8, 'A1-005', 69.00),
-- 文学类
('978-7-020-12345-1', '红楼梦', '曹雪芹', '人民文学出版社', '2023-01-01', 12, 10, 10, 'B1-001', 45.00),
('978-7-020-12345-2', '百年孤独', '加西亚·马尔克斯', '南海出版社', '2023-02-01', 13, 5, 5, 'B1-002', 39.00),
('978-7-020-12345-3', '三体', '刘慈欣', '重庆出版社', '2023-03-01', 12, 7, 7, 'B1-003', 75.00),
('978-7-020-12345-4', '围城', '钱钟书', '人民文学出版社', '2023-04-01', 12, 6, 6, 'B1-004', 35.00),
-- 历史类
('978-7-101-12345-1', '史记', '司马迁', '中华书局', '2023-01-01', 14, 4, 4, 'C1-001', 120.00),
('978-7-101-12345-2', '全球通史', '斯塔夫里阿诺斯', '北京大学出版社', '2023-02-01', 15, 3, 3, 'C1-002', 88.00),
-- 数学类
('978-7-040-12345-1', '高等数学', '同济大学数学系', '高等教育出版社', '2023-01-01', 4, 12, 12, 'D1-001', 56.00),
('978-7-040-12345-2', '线性代数', '同济大学数学系', '高等教育出版社', '2023-02-01', 4, 8, 8, 'D1-002', 42.00);
-- 5. 借阅记录数据
INSERT INTO borrow_records (user_id, book_id, borrow_date, due_date, status) VALUES
-- 当前借阅中的记录
(1, 1, '2024-05-01', '2024-08-01', 'borrowed'),
(1, 2, '2024-05-15', '2024-08-15', 'borrowed'),
(2, 3, '2024-06-01', '2024-09-01', 'borrowed'),
(3, 4, '2024-06-10', '2024-09-10', 'borrowed'),
(4, 5, '2024-06-15', '2024-09-15', 'borrowed'),
-- 已归还的记录
(1, 6, '2024-03-01', '2024-06-01', 'returned'),
(2, 7, '2024-04-01', '2024-07-01', 'returned'),
(3, 8, '2024-04-15', '2024-07-15', 'returned'),
-- 超期记录
(5, 9, '2024-02-01', '2024-05-01', 'overdue'),
-- 教师借阅记录
(6, 10, '2024-05-20', '2024-08-20', 'borrowed'),
(7, 11, '2024-06-01', '2024-09-01', 'borrowed'),
(8, 12, '2024-06-05', '2024-09-05', 'borrowed');
-- 更新归还日期
UPDATE borrow_records SET return_date = '2024-06-01' WHERE record_id IN (6, 7, 8);
-- 6. 罚款记录数据
INSERT INTO fine_records (record_id, user_id, fine_type, fine_amount, overdue_days, status) VALUES
(9, 5, 'overdue', 15.00, 30, 'unpaid');
-- 创建一些实用的视图
-- 用户当前借阅情况视图
CREATE VIEW user_current_borrows AS
SELECT
u.user_id,
u.user_code,
u.name,
ut.type_name,
ut.max_borrow_count,
COUNT(br.record_id) as current_borrow_count,
(ut.max_borrow_count - COUNT(br.record_id)) as remaining_quota
FROM users u
LEFT JOIN user_types ut ON u.user_type_id = ut.type_id
LEFT JOIN borrow_records br ON u.user_id = br.user_id AND br.status = 'borrowed'
GROUP BY u.user_id;
-- 图书库存状态视图
CREATE VIEW book_inventory AS
SELECT
b.book_id,
b.isbn,
b.title,
b.author,
bc.category_name,
b.total_copies,
b.available_copies,
(b.total_copies - b.available_copies) as borrowed_copies,
b.location
FROM books b
LEFT JOIN book_categories bc ON b.category_id = bc.category_id;
-- 超期借阅视图
CREATE VIEW overdue_borrows AS
SELECT
br.record_id,
u.user_code,
u.name,
b.title,
br.borrow_date,
br.due_date,
DATEDIFF(CURDATE(), br.due_date) as overdue_days,
(DATEDIFF(CURDATE(), br.due_date) * 1.0) as suggested_fine
FROM borrow_records br
JOIN users u ON br.user_id = u.user_id
JOIN books b ON br.book_id = b.book_id
WHERE br.status = 'borrowed' AND br.due_date < CURDATE();
后端在idea上建立springboot项目，如附件。
扫一扫
9512
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
