编辑代码

CREATE DATABASE IF NOT EXISTS LibraryDB;
USE LibraryDB;

-- 1. 图书表
CREATE TABLE Book (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(50) NOT NULL,
    publisher VARCHAR(50),
    publish_date DATE,
    total_copies INT NOT NULL DEFAULT 0,
    available_copies INT NOT NULL DEFAULT 0,
    CONSTRAINT chk_copies CHECK (available_copies <= total_copies AND available_copies >= 0)
);

-- 2. 读者表
CREATE TABLE Reader (
    reader_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender ENUM('男','女','其他') DEFAULT '其他',
    type ENUM('学生','教师','社会人士') NOT NULL,
    phone CHAR(11) UNIQUE,
    reg_date DATE NOT NULL,
    max_borrow_limit INT NOT NULL
);

-- 3. 借阅记录表
CREATE TABLE BorrowRecord (
    record_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT NOT NULL,
    reader_id INT NOT NULL,
    borrow_date DATE NOT NULL,
    due_date DATE NOT NULL,
    return_date DATE,
    status ENUM('已还','未还') DEFAULT '未还',
    CONSTRAINT fk_book FOREIGN KEY (book_id) REFERENCES Book(book_id),
    CONSTRAINT fk_reader FOREIGN KEY (reader_id) REFERENCES Reader(reader_id)
);

-- 4. 罚款记录表
CREATE TABLE FineRecord (
    fine_id INT AUTO_INCREMENT PRIMARY KEY,
    record_id INT NOT NULL,
    book_id INT NOT NULL,
    amount DECIMAL(8,2) NOT NULL DEFAULT 0,
    is_paid BOOLEAN DEFAULT FALSE,
    CONSTRAINT fk_borrow_record FOREIGN KEY (record_id) REFERENCES BorrowRecord(record_id),
    CONSTRAINT fk_fine_book FOREIGN KEY (book_id) REFERENCES Book(book_id)
);


-- ===================== 测试数据 ===================== --
-- 插入图书数据
INSERT INTO Book (title, author, publisher, publish_date, total_copies, available_copies) VALUES
('数据库系统概念', '王珊', '机械工业出版社', '2012-05-01', 10, 10),
('三体', '刘慈欣', '重庆出版社', '2008-01-01', 5, 5),
('百年孤独', '马尔克斯', '南海出版公司', '2011-06-01', 8, 8),
('人类简史', '尤瓦尔·赫拉利', '中信出版社', '2014-11-01', 6, 6),
('小王子', '圣埃克苏佩里', '人民文学出版社', '2000-08-01', 15, 15);

-- 插入读者数据
INSERT INTO Reader (name, gender, type, phone, reg_date, max_borrow_limit) VALUES
('张三', '男', '教师', '13800138000', '2022-01-15', 10),
('李四', '女', '学生', '13900139000', '2023-03-20', 5),
('王五', '男', '社会人士', '13700137000', '2021-11-05', 3),
('赵六', '女', '学生', '13600136000', '2023-09-10', 5),
('钱七', '男', '教师', '13500135000', '2020-05-12', 10);