数据库课程设计图书馆管理系统：ER图、建表与SQL事务实战

简介这是一份面向计算机、信息管理等专业学生的《数据库系统原理》课程设计参考文档完整呈现图书馆管理系统的设计流程。文档从课程设计目的与项目背景切入结合高校图书馆信息化管理趋势依次完成可行性研究、需求分析、概要设计等环节并重点介绍系统管理、读者管理、图书与期刊管理、流通管理、统计分析等功能模块帮助读者理解数据库原理、SQL语句应用与数据关系设计。文档还将图书馆信息、读者档案、图书期刊、流通参数等数据对象整理为具体功能模块涉及字段设置、借阅规则、挂失恢复、征订验收、盘点注销等细节可支撑课程设计报告撰写、数据库建表与答辩准备。压缩包内仅含1个doc文档大小约117KB内容集中便于逐章阅读。该文档已有3405人学习适合正在完成数据库课程设计或需要参考完整设计流程的同学使用。1. 数据库课程设计图书馆管理系统先弄清楚老师要收的到底是什么看到题目里那个“.doc”就该明白这门课设真正的交付物不是 demo 网页而是一份能自圆其说的数据库设计报告。图书馆管理系统是数据库课程设计里出现频率最高的题目之一读者、图书、借阅记录三个实体恰好把关系数据库的建表、约束、事务并发、统计查询全部装进去适合正在赶课设的学生也适合带课设的老师拿来做评分口径。大多数人的第一反应是找一份现成代码这条路在答辩时最容易翻车。老师顺着关系模式问一句“借阅记录为什么这样拆、索引为什么这么加”答不上来就得返工。与其背源码不如把重心压在需求分析、ER 图、建表、业务 SQL、验证这条链路上每一层都在报告里写清楚依据。下文按这条链路讲数据库默认 MySQL 8.4 LTSSQL Server 和国产数据库的语法略有差异规范化思路完全通用。2. 需求分析和 ER 图报告第一章能不能过就看设计线是否完整2.1 需求分析先讲边界借阅、归还、续借、逾期、统计五个主流程需求分析的作用是把“图书馆管理系统”这个宽泛题目收敛成可实现的业务边界。常见做法是把功能拆成五个主流程借书、还书、续借、逾期查询、统计报表。每个流程都对应后文里的表结构和 SQL报告里只要有这个映射答辩时就不会被“你做了哪些功能”这个问题卡住。借书流程的关键是验证读者和图书的状态读者是否存在、当前在借数量是否超限、图书有没有可借副本。还书流程的关键是登记实际归还时间并按应还时间算出是否逾期、罚金多少。续借流程要限制次数避免一本图书被无限期占用。逾期查询给管理员看本质是基于状态和时间的一类条件查询。统计报表一般取每月借还量、图书热度排行这类聚合数据方便把报告里的“应用效果”写成数字。写需求分析时最容易犯的毛病是“什么功能都想加”。这是数据库课设不是软件工程项目评分重点在数据模型的规范性和 SQL 的正确性不在功能数量。我一般会单独写一段“隐式需求”放进报告数据要能审计到“谁在什么时间借了哪一册书”读者删除要有业务限制图书副本要有独立标识。这一段别省被提问的概率很高。2.2 ER 图实体与联系读者、图书、借阅记录之间的表结构雏形ER 图阶段要确定实体、属性和联系。这个题目里三个实体属主读者、图书、借阅记录。读者实体的属性至少包括读者 ID、姓名、读者类型、联系电话、注册日期。图书实体的属性除了书名、作者、出版社、ISBN 之外一定要有“总藏书量/可借数量”这对副本计数否则后面借还书无法扣减库存。借阅记录实体则包括借出时间、应还时间、实际归还时间、续借次数和罚金。三个实体之间是多对多联系一位读者可以借多本图书一册图书可以被多位读者先后借阅。关系数据库里多对多必须用中间表拆这个中间表就是借阅记录。实体属性先定成这样实体主键关键非主属性readerreader_idreader_name, reader_type, phone, register_datebookbook_idisbn, book_name, author, publisher, category_id, total_copies, available_copies, shelf_timeborrow_recordborrow_idreader_id, book_id, borrow_time, due_time, return_time, renewal_count, fine_amount, status这里有一个报告的隐藏加分项如果你的借阅记录只到“某本书”而不是“某一册”老师追问“同一套书有两本你借的到底是哪一本”就会卡住。简单处理是 book 表一册一行每一册有自己的 book_id复杂一点是拆成 book_info 书目表和 book_copy 副本表。课程设计用前者就够但要在 ER 图里明确标注“book 表示一册可借的实体副本”而不是一种书。2.3 关系模式与 3NF 检查第三范式怎么拆才能让老师没话说ER 图转关系模式有两条规则实体直接转表多对多联系单独转中间表。于是得到 reader、book、borrow_record外加一个可选的 category 分类表。范式检查要落在“非主属性只依赖于主键不能依赖其他非主属性”这个判断上。最容易出问题的是书目分类信息。如果 book 表里同时存 category_id 和 category_name那 category_name 就是一个传递依赖改一次分类名要改几十行图书数据。把分类独立成 category 表book 表只存 category_id 外键传递依赖就消除了。现在这个关系模式里逐项检查所有非主属性每一项都只依赖于各自的主键满足 3NF。另一个容易被忽略的规范化点是借阅记录里不该存书名和读者名。书名和读者名都属于被依赖方的属性一旦读者改名或图书信息变更借阅历史也会被连带“改掉”。借阅记录只存外键 ID显示信息用 JOIN 去取这是范式问题也是数据审计的底线。报告里如果能写出这条设计依据答辩分通常比只贴 ER 图高出一截。3. 建库建表落地字符集、存储引擎、约束和索引一次定清楚3.1 环境准备MySQL 8.4 LTS 的下载解压、初始化与连接串常见做法是下载 MySQL 8.4 LTS 的 zip 包本地解压之后初始化数据目录。课设阶段用 mysqld --initialize-insecure 能免密进入省去一大堆配置文件折腾但这只适合本机开发不要在生产服务器这么干。# 解压后进入 MySQL 的 bin 目录先初始化数据目录 mysqld --initialize-insecure --userroot --basedirD:/mysql --datadirD:/mysql/data # 把 MySQL 注册成 Windows 服务并启动 mysqld --install mysql8 net start mysql8 # 用 root 免密登录显式指定 utf8mb4避免客户端乱码 mysql --default-character-setutf8mb4 -u root -p参数说明--basedir 和 --datadir 要改成你自己的实际路径--initialize-insecure 表示初始 root 密码为空登录后建议立刻执行 ALTER USER rootlocalhost IDENTIFIED BY 你自己密码。登录时加 --default-character-setutf8mb4 是一道保险Windows 控制台默认字符集经常是 GBK不加这一句后续建表里写中文注释会直接乱掉。3.2 建表 SQLreader、book、borrow_record 的字段、主外键与检查约束建表顺序必须是 category、reader、book最后建 borrow_record因为子表引用的父表要先存在。下面这套脚本直接可以跑通CREATE DATABASE library_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci; USE library_db; CREATE TABLE category ( category_id INT AUTO_INCREMENT PRIMARY KEY, category_name VARCHAR(50) NOT NULL UNIQUE ) ENGINEInnoDB; CREATE TABLE reader ( reader_id INT AUTO_INCREMENT PRIMARY KEY, reader_name VARCHAR(50) NOT NULL, reader_type ENUM(STUDENT,TEACHER) NOT NULL DEFAULT STUDENT, phone VARCHAR(20), register_date DATE NOT NULL, UNIQUE KEY uk_reader_phone (phone) ) ENGINEInnoDB; CREATE TABLE book ( book_id INT AUTO_INCREMENT PRIMARY KEY, isbn VARCHAR(20) NOT NULL, book_name VARCHAR(200) NOT NULL, author VARCHAR(100), category_id INT, total_copies INT NOT NULL DEFAULT 1, available_copies INT NOT NULL DEFAULT 1, shelf_time DATE, UNIQUE KEY uk_book_isbn (isbn), CONSTRAINT fk_book_category FOREIGN KEY (category_id) REFERENCES category(category_id) ) ENGINEInnoDB; CREATE TABLE borrow_record ( borrow_id INT AUTO_INCREMENT PRIMARY KEY, reader_id INT NOT NULL, book_id INT NOT NULL, borrow_time DATETIME NOT NULL, due_time DATETIME NOT NULL, return_time DATETIME NULL, renewal_count TINYINT NOT NULL DEFAULT 0, status ENUM(BORROWED,RETURNED) NOT NULL DEFAULT BORROWED, fine_amount DECIMAL(6,2) NOT NULL DEFAULT 0.00, KEY idx_borrow_reader_time (reader_id, borrow_time), KEY idx_borrow_book_time (book_id, borrow_time), KEY idx_borrow_status (status), CONSTRAINT fk_borrow_reader FOREIGN KEY (reader_id) REFERENCES reader(reader_id), CONSTRAINT fk_borrow_book FOREIGN KEY (book_id) REFERENCES book(book_id), CONSTRAINT chk_renewal CHECK (renewal_count 2) ) ENGINEInnoDB;几个参数容易踩坑register_date 是 DATE不要在代码里传带时分秒的字符串due_time 和 borrow_time 用 DATETIME因为计算逾期罚金要精确到日但记录时刻必须留到秒。status 只设 BORROWED 和 RETURNED 两个枚举值逾期不落库用查询实时算避免“状态漂移”——如果你把 OVERDUE 也存进去还书时忘了更新就会留下错误状态。ENGINE 统一写 InnoDBMyISAM 不支持外键和事务借书流程根本没法保证一致性。MySQL 低于 8.0.13 时DATE 字段不支持 DEFAULT (CURRENT_DATE) 这种表达式默认值所以上面 register_date 我没写默认值让程序插入时显式传当前日期。这是兼容性最好的写法课设里不会因为这个被老师挑毛病。3.3 归档表与统计索引报告里多一个“性能优化”的小节表建完之后还需要在设计报告里给索引和归档留一个章节。borrow_record 的索引不是随便加的idx_borrow_reader_time 服务于“查询某读者所有借阅记录”和“某读者当前在借列表”idx_borrow_book_time 服务于“某本书的借阅历史”和“图书热度排行”idx_borrow_status 服务于管理员按状态筛选。性能优化这块可以写一个务实的归档策略当 borrow_record 超过一定量级把状态为 RETURNED 且 return_time 距今超过一年的记录迁移到 borrow_record_archive 表结构完全相同。课设数据量小物理归档不必真做但报告里写清楚“为什么要归档、什么时候归档、归档后查询如何路由”这比空谈索引优化更让老师信服。3.4 造数据脚本用 Python 一次生成 50 个读者、100 册书和半年借阅记录建好的表是空的演示时不好看SQL 优化也讲不出效果。我一般用一段 Python 脚本造数据先装依赖 pymysqlimport pymysql import random from datetime import datetime, timedelta random.seed(42) conn pymysql.connect( host127.0.0.1, userroot, password123456, databaselibrary_db, charsetutf8mb4 ) cursor conn.cursor() # 造 50 个读者 for i in range(50): name fstudent_{i:02d} reader_type random.choice([STUDENT, TEACHER]) phone f138{random.randint(10000000, 99999999)} reg_date (datetime.now() - timedelta(daysrandom.randint(30, 500))).date() cursor.execute( INSERT INTO reader(reader_name, reader_type, phone, register_date) VALUES(%s, %s, %s, %s), (name, reader_type, phone, reg_date) ) # 造 10 个分类和 100 册书 for c in range(10): cursor.execute(INSERT INTO category(category_name) VALUES(%s), (fcat_{c},)) for i in range(100): cursor.execute( INSERT INTO book(isbn, book_name, author, category_id, total_copies, available_copies, shelf_time) VALUES(%s, %s, %s, %s, 5, 5, %s), (f978-{random.randint(1000, 9999)}-{i:04d}, fbook_{i:03d}, fauthor_{i%20:02d}, random.randint(1, 10), (datetime.now() - timedelta(daysrandom.randint(30, 800))).date()) ) conn.commit() cursor.close() conn.close() print(done)参数说明random.seed(42) 固定随机种子每次生成的数据一致方便你和老师复现同一个演示结果。password 换成你自己的密码。register_date 用 Python 端的 date 对象避免字符串格式问题。total_copies 先全部给 5后面借书时 available_copies 会逐步扣演示更自然。这个脚本只做了初始数据借阅记录的造数在第 4 章业务 SQL 跑通之后再生成会更合理。4. 把业务做成增删改查 SQL借书、还书、续借、逾期罚款怎么算4.1 借书流程先锁库存再 INSERT 记录事务里两步不能颠倒借书是一个典型“读改写”流程直接写成两条独立 SQL 一定出事必须放在同一个事务里。先锁住图书行确认可借数量再插入借阅记录并扣减库存START TRANSACTION; SELECT available_copies FROM book WHERE book_id 1 FOR UPDATE; UPDATE book SET available_copies available_copies - 1 WHERE book_id 1 AND available_copies 0; INSERT INTO borrow_record (reader_id, book_id, borrow_time, due_time, renewal_count, status) VALUES (1001, 1, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY), 0, BORROWED); COMMIT;参数说明FOR UPDATE 是行级锁事务提交前其他会话对 book_id1 这一行的 UPDATE 会阻塞等待避免并发借最后一本书时超卖。借期默认为 30 天DATE_ADD(NOW(), INTERVAL 30 DAY) 生成应还时间。INSERT 之后的 UPDATE 顺序不能反如果先扣库存再插入记录插入失败回滚时库存也会回滚表面没区别但排查问题时会多绕弯路。应用层需要检查 UPDATE 影响行数如果为 0 说明可借数量已经不足直接 ROLLBACK。4.2 还书与逾期罚款自然日差和 24 小时的差异要先定口径还书逻辑要先查出该借阅记录的 book_id 和逾期天数再更新记录并归还库存。MySQL 不允许在同一句 UPDATE 里既更新 borrow_record 又从该表子查询取数据所以拆成两步-- 第一步在应用层拿到 book_id 和 overdue_days SELECT book_id, DATEDIFF(NOW(), due_time) AS overdue_days FROM borrow_record WHERE borrow_id 1 AND status BORROWED; -- 第二步overdue_days 作为变量传入 UPDATE borrow_record SET return_time NOW(), status RETURNED, fine_amount IF(overdue_days 0, overdue_days * 0.10, 0.00) WHERE borrow_id 1; UPDATE book SET available_copies available_copies 1 WHERE book_id 1;参数说明DATEDIFF 返回的是自然日差今天应还、明天还就是 1 天应还当天哪怕过了 23 小时也算 0 天。课程设计用自然日口径最合适好解释也好验收。如果老师要求精确到小时可以用 TIMESTAMPDIFF(MINUTE, due_time, NOW()) / 1440 折算但报告里要把口径写一致别一处按日一处按小时。罚金 0.10 元/天这个数值在报告里要单独说明通常写在需求分析的业务规则里。4.3 续借、热门图书和读者排行聚合查询让报告有数据可讲续借是典型的“有条件更新”必须同时判断状态和次数UPDATE borrow_record SET due_time DATE_ADD(due_time, INTERVAL 30 DAY), renewal_count renewal_count 1 WHERE borrow_id 1 AND status BORROWED AND renewal_count 2;热门图书排行用 GROUP BY 加 JOIN统计近 6 个月的借阅次数SELECT b.book_name, COUNT(*) AS borrow_times FROM borrow_record br JOIN book b ON br.book_id b.book_id WHERE br.borrow_time DATE_SUB(CURDATE(), INTERVAL 6 MONTH) GROUP BY b.book_id, b.book_name ORDER BY borrow_times DESC LIMIT 10;参数说明续借上限 renewal_count 2 表示最多续借 2 次即借期最长 90 天。排行榜 SQL 里注意 GROUP BY 要带上 b.book_name因为 book_name 不是聚合函数列只 GROUP BY b.book_id 在 ONLY_FULL_GROUP_BY 模式下会直接报错。这也是课设现场最常见的 SQL 报错之一。4.4 事务与并发控制连接池、锁等待和 ALTER TABLE 的取舍网页程序连接数据库一般用连接池事务边界必须放在 service 层不能让一个事务跨越多次 HTTP 请求。事务开始时从连接池借连接提交后才还回连接池这是既提高性能又避免连接泄漏的通用做法。借书这种长事务里不要夹杂查询统计、发通知之类的耗时操作锁等待超时默认是 50 秒事务持锁太久会让其他会话堆积。并发更新还容易触发死锁和锁等待。死锁的常见触发姿势是两个事务以不同顺序更新同一组行。解决办法是让所有业务 SQL 里的更新顺序一致比如先更新 book 再更新 borrow_record。EXPLAIN 也是排查利器在慢查询前面加上 EXPLAIN能直接看到走没走索引。还有一个课设阶段的劝告表结构不要边跑边改。MySQL 8.0.12 之后部分 ADD COLUMN 是 INSTANT 算法秒级完成但修改 ENUM 枚举值或者 VARCHAR 长度这类操作会触发表重建数据量大时会锁住读写。课设表里就几十行数据无所谓但报告里写一句“上线前完成结构评审避免频繁 DDL”比事后解释强得多。5. 避坑与排查图书馆课设最容易翻车的 5 个现场5.1 外键删除受限、清库后关联数据变成孤儿现象图书表里还有借出记录时执行 DELETE FROM book WHERE book_id1 报错 Cannot delete or update a parent row外键约束失败。想省事把 FOREIGN_KEY_CHECKS0 关了再删倒是删掉了后面一对 ID 全是脏数据。原因borrow_record 外键默认 ON DELETE RESTRICT有子记录引用时父行不允许删除。关闭外键检查等于放弃完整性保护删除后没有任何手段找回关联关系。解决课程设计不提供“物理删除”按钮正确做法是给 reader 和 book 加一个 is_active 标志位下架图书改成 UPDATE book SET is_active0。如果要清理测试数据正常顺序是先清 borrow_record再清 book 和 reader脚本里用不上 FOREIGN_KEY_CHECKS。我自己的习惯是宁可多写一层逻辑也不碰这个全局开关。5.2 中文全部变成问号、应还日期差一天现象插入中文书名后命令行和网页里读出来全是“”或者明明借了 30 天应还日期却少了一天。原因乱码是字符集链路不一致。建库用了 utf8mb4但客户端连接串没指定字符集连接层按 gbk 或 latin1 处理中文进库就变成了乱码。日期差一天通常是代码里把 datetime 转成 date 或字符串时丢了时分秒借书当天 23:59:59 插入读出来只剩日期应还日期计算时起点就错了。解决从建库、建表到连接串全部统一 utf8mb4。pymysql 连接参数里写 charsetutf8mb4JDBC 连接串加 characterEncodingutf8命令行登录加 --default-character-setutf8mb4。日期字段统一用 DATETIME 存储代码里不要手动截断时间。还款当天不逾期这条规则用自然日差 DATEDIFF 判断比转字符串后比较稳定得多。5.3 并发借书超卖、同表子查询更新报错现象两个窗口同时借同一本书可借数量明明只剩 1 本两次操作都成功了available_copies 变成 -1。另一个现象是 UPDATE borrow_record 里的子查询再查 borrow_record报错 You cant specify target table for update in FROM clause。原因第一类是并发导致程序里先 SELECT 判断数量再 UPDATE两条语句之间其他事务插了队读改写不是原子操作。第二类是 MySQL 的一个限制同一语句更新某张表时子查询不能再查同一张表。解决借书时用 SELECT FOR UPDATE 先锁行或在 UPDATE 的 WHERE 里直接加 available_copies 0然后判断影响行数为 0 就回滚。同表更新拆成两步先在应用层查出 book_id再执行两条独立 UPDATE。这个限制不算 bug理解之后就不会再踩。数据库并发锁和死锁的演示用两个 MySQL 命令行窗口开两个事务就能复现答辩时这是很能说明问题的素材。6. 答辩前怎么验证走一遍 EXPLAIN再做一个逾期视图提交报告前把核心 SQL 全部放到真实数据上跑一遍 EXPLAIN比口头说“我优化过”有用得多。借书和还书各挑一条最常用的查询比如读者当前在借列表EXPLAIN SELECT * FROM borrow_record WHERE reader_id 1001 AND status BORROWED;如果 type 是 ref 或者 range说明 idx_borrow_reader_time 生效了如果 type 是 ALLrows 还特别大就是全表扫描把这条 SQL 和索引设计小节的矛盾修正后再交。报告里放一张 EXPLAIN 结果截图老师基本不会再追问性能问题。再做一个逾期未还视图答辩时一句“管理员只看视图”就能把业务逻辑讲得很干净CREATE VIEW v_overdue AS SELECT r.reader_name, b.book_name, br.due_time, DATEDIFF(NOW(), br.due_time) AS overdue_days FROM borrow_record br JOIN reader r ON br.reader_id r.reader_id JOIN book b ON br.book_id b.book_id WHERE br.status BORROWED AND br.due_time NOW();视图的查询条件里用了 status 和 due_time正好对应 idx_borrow_status 和借出时间索引。验证清单可以按“建库建表、造数、借书、还书、续借、逾期、统计、索引”八项逐个打勾每一项写一句预期结果和实际表现。说一个我自己的习惯数据库课设交之前把建表脚本从头到尾重新执行一遍然后用刚才的验证清单把所有 SQL 跑一遍确认每一步都有数据变化和返回结果。当年我自己做课设时就是靠这个习惯发现还书 SQL 里同表子查询的报错当场改掉答辩才没翻车。这个“先验证再写报告”的顺序后来也一直沿用。希望帮到你。本文还有配套的精品资源点击获取