数据库系统概论课程设计：从ER图到事务并发的可运行交付

简介面向数据库系统概论学习者的课程设计实践包取自山东科技大学课程设计任务适合正在学习 SQL、准备完成建表与改表实验的学生对照练习也可作为课程作业的参考样例。压缩包共 5 个文件、约 197KB以 txt 说明与测试数据、cpp 源码、编译生成的 o 中间文件和 exe 可执行程序为主源码与可执行程序配套既能直接运行查看效果也便于阅读实现逻辑。目前已有 683 人学习下载。内容围绕数据表结构定义与变更展开创建表涉及字段名、类型、长度、可空性等属性设定修改表则覆盖字段增删改、顺序调整与属性变更并通过预设测试数据检验操作是否正确随包说明文档对设计目标、步骤与注意事项作了交代可帮助读者把 SQL 语句与程序实现对应起来理解 CREATE TABLE、ALTER TABLE 的实际效果积累调试与验证思路。1. 下载即可使用的数据库系统概论课程设计为什么解压之后往往跑不起来每到期末总有人把“数据库系统概论课程设计”敲进搜索框想要一个下载即可使用的成品解压、导库、改配置、跑起来。需求很实在但流传的压缩包大多只给了半套东西源码在建库脚本丢了脚本在字符集是 latin1数据在外键顺序反了导到一半就中断。我见过 A 同学本地跑得好好的换台机器就报 Table doesnt exist熬到凌晨两点重写建表语句。真正能交的课程设计不是代码量堆得多而是从选题、落表到事务并发这条链路每一环都对得上。这篇就按这条链路往下走适合正在赶课设的人也适合要给新人一套练手项目的开发者。2. 选题与需求边界把“图书借阅”砍成一个能交付的最小闭环2.1 三个常见选题的取舍图书借阅为什么比成绩管理更抗问课程设计最常见的三个选题是学生成绩管理、图书借阅管理、仓库出入库。从“能跑起来”的角度看三个都不难从“经得起问”的角度看差别很大。成绩管理的核心是一张成绩表加一张学生表功能集中在增删改查。答辩时最容易被追问的一句是这跟一张 Excel 表加上筛选有什么区别。这不是刁难是因为它确实没有逼出数据库课程里真正该考的东西——事务、并发、约束、索引。图书借阅不一样它天然带状态流转一本书有总册数和在库册数读者借出时在库册数减一归还时加一逾期还要算罚金。这条链路上你会被迫处理四件事场景逼出来的知识点能不能现场演示两个人同时借最后一本事务、行锁、条件更新能两个会话即可复现还书后册数自动恢复触发器或应用层事务能还一次查一次逾期天数与罚金日期函数、约束、精度选型能改一条借出时间按读者查借阅历史复合索引、执行计划能EXPLAIN 前后对比我一般会建议在图书借阅的基础上再加一层分类树分类下还有子分类这样能自然带出自关联和外键工作量可控又能体现“设计”而不是“堆表”。2.2 需求清单写成可验收条目答辩时少被追问需求写成“实现图书管理功能”这种句子等于没写。能验收的写法是给每条需求一个编号、一个操作路径、一个可观察的结果。下面这份清单是按最小闭环砍过的四张核心表就能撑住。编号功能点验收标准优先级F01图书入库新增一本书后总册数在库册数录入值必做F02读者注册借书证号唯一重复插入报唯一键冲突必做F03借书借出后在库册数减一生成一条借阅记录必做F04还书归还后在库册数加一记录状态变为已还必做F05借阅上限读者已借数量达到上限时拒绝借出必做F06逾期计算超过应还日期按天计算罚金展示天数必做F07续借未逾期且未达续借次数上限时可续借一次选做F08图书检索按书名模糊查、按分类精确查走索引选做F09报表导出某时间段借阅排行前 10选做“必做”只留六条是因为课程设计的周期通常只有两三周真正写不完的从来不是功能而是把功能写稳。F05 和 F06 最能体现设计水平借阅上限应该存在读者类型表里而不是硬编码逾期罚金应该由日期差在查询时算出来而不是存一个会过期的冗余字段——后者就是典型的把状态写死第二天数据就不对了。提示需求边界要主动写进文档的“不做范围”比如不支持在线支付、不做多级审批、不做消息推送。写清楚不做什么比列一堆做不动的功能更显成熟。2.3 环境与技术栈一次定型版本、字符集和服务端参数跑不起来的第一大原因不是代码错是环境不一致。课程设计这种短周期项目环境只定一次之后不再折腾。常见做法是 MySQL 8 加 InnoDB字符集统一 utf8mb4。后端用 Python 配 pymysql 或 Flask 都行甚至可以先只写 SQL 脚本加命令行验证跑通了再接界面。用容器起库是最省事的一条命令锁定版本换机器也不用重装。# 拉起一个固定版本的数据库实例端口映射到本机 3306 docker run -d --name db_course \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDCourse_2024 \ -e MYSQL_DATABASElibrary \ --character-set-serverutf8mb4 \ --collation-serverutf8mb4_0900_ai_ci \ mysql:8.0 # 进入实例确认字符集与存储引擎默认值 docker exec -it db_course mysql -uroot -pCourse_2024 -e SHOW VARIABLES LIKE character_set_server; SHOW VARIABLES LIKE collation_server; SHOW VARIABLES LIKE default_storage_engine; SHOW VARIABLES LIKE sql_mode;这段命令做了三件事用镜像标签锁死小版本避免不同机器装到不同发行版在服务端启动参数里直接指定字符集和排序规则省掉建库时忘记写 charset 的隐患最后用四条 SHOW 确认结果。参数上要特别看sql_mode它决定0000-00-00这类日期是报错还是被悄悄接受很多导入失败和它有关。如果输出里包含STRICT_TRANS_TABLES和NO_ZERO_DATE说明是严格模式建表时日期字段必须有合法默认值或允许 NULL。本机安装的话装完立刻跑一遍同样的四条检查别等到导入数据才发现是 latin1。连接层还有一个容易漏的点客户端连接建立时会协商字符集如果驱动默认的character_set_client和表不一致中文就会变成问号后面细说。3. 从 ER 图到三范式建表schema 怎么定才经得起追问3.1 先画实体再删联系主键、外键与弱实体的取舍画 ER 图不是画得漂亮就行它的真正用途是帮你决定“哪些东西该独立成表”。我通常先列名词再删读者、图书、分类、借阅记录、管理员。名词里“借阅记录”最容易出错——它是一个事件不是实体必须有独立主键和发生时间否则没法追溯历史。主键选型上有个常见分歧用业务码ISBN、借书证号当主键还是用自增 ID。我的做法是自增 BIGINT 做物理主键业务码加唯一索引。理由是业务码会变——读者换证、图书改 ISBN 补录都不该牵动所有引用它的外键而且自增主键在 InnoDB 里是顺序写入聚集索引分裂少数据量一上来差别可见。外键要不要建是课程设计里最值得讲清的一点。很多教程为了“方便导入”直接建议不建外键这是把问题留给了数据质量。我的取舍是核心表之间建外键并显式命名方便出错时定位导入阶段临时关闭检查导完再打开验证。分类树是自关联父分类为 NULL。这里有个坑MySQL 的自关联外键在删除父节点时默认 RESTRICT 会拒绝删除这正是我们要的如果想级联删子分类ON DELETE CASCADE在自关联上也能生效但会连带删掉挂在子分类下的书所以图书表对分类的外键要用 RESTRICT两处语义必须分开写。3.2 建表 SQL 逐段拆约束、默认值、字符集一次写对下面这段是核心四表顺序很关键被引用的表先建。字段类型、约束、注释一次写全后面少改一次就少一次翻车。-- 分类表自关联父分类为空表示顶级 CREATE TABLE category ( category_id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, parent_id INT UNSIGNED NULL, PRIMARY KEY (category_id), UNIQUE KEY uk_category_name_parent (name, parent_id), CONSTRAINT fk_category_parent FOREIGN KEY (parent_id) REFERENCES category (category_id) ON DELETE RESTRICT ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT图书分类; -- 图书表总册数与在库册数分离用 CHECK 兜底 CREATE TABLE book ( book_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, isbn VARCHAR(20) NOT NULL, title VARCHAR(200) NOT NULL, author VARCHAR(100) NOT NULL DEFAULT 佚名, category_id INT UNSIGNED NOT NULL, price DECIMAL(8,2) NOT NULL DEFAULT 0.00, total_copies INT UNSIGNED NOT NULL DEFAULT 1, available_copies INT UNSIGNED NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (book_id), UNIQUE KEY uk_book_isbn (isbn), KEY idx_book_category (category_id), CONSTRAINT fk_book_category FOREIGN KEY (category_id) REFERENCES category (category_id) ON DELETE RESTRICT, CONSTRAINT ck_book_copies CHECK (available_copies total_copies) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT图书; -- 读者类型借阅上限放在这里不写死在代码里 CREATE TABLE reader_type ( type_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, type_name VARCHAR(20) NOT NULL, max_borrow TINYINT UNSIGNED NOT NULL DEFAULT 5, max_days SMALLINT UNSIGNED NOT NULL DEFAULT 30, fine_per_day DECIMAL(5,2) NOT NULL DEFAULT 0.20, PRIMARY KEY (type_id), UNIQUE KEY uk_type_name (type_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT读者类型; -- 读者表card_no 唯一状态用 TINYINT 不用字符串 CREATE TABLE reader ( reader_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, card_no VARCHAR(20) NOT NULL, name VARCHAR(50) NOT NULL, type_id TINYINT UNSIGNED NOT NULL, status TINYINT NOT NULL DEFAULT 1 COMMENT 1正常 0停用, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (reader_id), UNIQUE KEY uk_reader_card (card_no), KEY idx_reader_type (type_id), CONSTRAINT fk_reader_type FOREIGN KEY (type_id) REFERENCES reader_type (type_id) ON DELETE RESTRICT ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT读者; -- 借阅记录事件表只增不改状态流转单独字段 CREATE TABLE borrow_record ( record_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, reader_id BIGINT UNSIGNED NOT NULL, book_id BIGINT UNSIGNED NOT NULL, borrow_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, due_date DATETIME NOT NULL, return_date DATETIME NULL, status TINYINT NOT NULL DEFAULT 0 COMMENT 0借出 1已还, renew_count TINYINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (record_id), KEY idx_borrow_reader_status (reader_id, status), KEY idx_borrow_book (book_id), KEY idx_borrow_due (due_date), CONSTRAINT fk_borrow_reader FOREIGN KEY (reader_id) REFERENCES reader (reader_id) ON DELETE RESTRICT, CONSTRAINT fk_borrow_book FOREIGN KEY (book_id) REFERENCES book (book_id) ON DELETE RESTRICT ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT借阅记录;逐段说明几个关键选择。价格和罚金用DECIMAL不用FLOAT浮点存钱在罚金累加时会算出0.30000000000000004展示层再格式化也掩盖不了对账对不上。status用 TINYINT 而不是字符串是因为它会被放进复合索引定长整型的比较和存储都更省但别单独给它建索引基数太低优化器基本不会走。available_copies是冗余字段第三范式里它不该存在因为它可以由“总册数减未还记录数”推出来。这里要正面回答一个必然会来的追问为什么保留这个冗余。答案是可并发性和可查询性。如果每次借书都去COUNT一遍未还记录高并发下这个聚合会成为热点而且加锁范围难以控制。保留冗余字段用事务和触发器守住一致性是工程上更常见的做法——反范式不是偷懒是明确知道代价并做了补偿。3.3 索引的三个必建与两个别建索引不是越多越好课程设计里尤其容易被扣分的地方是“给每一列都建了索引”然后说不清为什么。必建的三个borrow_record(reader_id, status)支撑“查某读者当前在借”复合索引的顺序不能反把选择性低的status放前面会让索引失效book(isbn)唯一索引支撑入库查重borrow_record(due_date)支撑每天扫描逾期。别建的两个book.available_copies这种频繁更新且基数极低的列建了会让每次借还都多维护一棵索引树reader.status同理。验证方式不必靠背用执行计划看-- 看这条查询是否用上了复合索引 EXPLAIN SELECT record_id, book_id, due_date FROM borrow_record WHERE reader_id 1001 AND status 0; -- 对比把条件顺序写反、或对索引列做函数运算会退化成全表扫描 EXPLAIN SELECT * FROM borrow_record WHERE DATE(borrow_date) 2024-06-01;第二条之所以失效是因为在索引列上套了函数索引没法直接用。正确写法是区间比较borrow_date 2024-06-01 AND borrow_date 2024-06-02。看执行计划重点看type是不是ref或range、key是不是命中你建的那条、rows估算多少。这一步花十分钟答辩时能直接拿出来讲。4. 让数据自己守规矩事务、触发器与并发扣减4.1 借书还书包进一个事务FOR UPDATE 到底锁住了什么借书这个动作至少涉及两条写操作扣减在库册数、插入借阅记录。任何一条失败都该整体回滚否则就会出现“册数减了但没记录”的黑匣子状态事后谁都说不清。-- 借书先锁行再判断避免两个人同时通过检查 START TRANSACTION; SELECT available_copies, total_copies FROM book WHERE book_id 2001 FOR UPDATE; -- 对这本书加排他行锁第二个事务在此等待 -- 应用层拿到结果后判断available_copies 0 且读者未超上限 UPDATE book SET available_copies available_copies - 1 WHERE book_id 2001 AND available_copies 0; INSERT INTO borrow_record (reader_id, book_id, due_date) VALUES (1001, 2001, DATE_ADD(NOW(), INTERVAL 30 DAY)); COMMIT;FOR UPDATE锁的是book_id 2001这一行的聚集索引记录第二个并发事务执行到同一句时会阻塞直到第一个提交。这样“检查余量”和“扣减余量”之间不会插进别人。参数上要改的是due_date的间隔天数别写死 30从reader_type.max_days查出来拼进去这才叫把规则放在数据里。UPDATE里带available_copies 0是第二道防线。即使没走FOR UPDATE条件更新本身也是原子的影响行数为 0 就说明余量已被抢完应用层据此返回“已借完”即可不会扣成负数。两种方式我一般都用锁负责语义清晰条件负责兜底。4.2 用触发器守住“在库数量”这条不变量事务写在应用层有个前提所有入口都得记得写。课程设计里经常出现命令行脚本、测试代码、后端接口三处都能改数据总有一处漏了扣减。触发器解决的是这个把一致性规则下沉到数据库。-- 还书触发器只负责把册数加回去不改其他表 DELIMITER $$ CREATE TRIGGER trg_borrow_return AFTER UPDATE ON borrow_record FOR EACH ROW BEGIN -- 只在状态从“借出”变为“已还”时触发避免重复更新 IF OLD.status 0 AND NEW.status 1 THEN UPDATE book SET available_copies available_copies 1 WHERE book_id NEW.book_id AND available_copies total_copies; -- 防止加超 END IF; END$$ DELIMITER ;这段逻辑的关键在判断条件。触发器体里只能引用OLD和NEWOLD.status 0 AND NEW.status 1保证了幂等性同一条记录重复更新为已还不会反复加册数。available_copies total_copies是第三次兜底防止历史数据错乱导致册数超过总数。必须提醒一个限制MySQL 的触发器不能对本表做增删改也就是定义在borrow_record上的触发器里不能再去UPDATE borrow_record否则直接报 1442 错误。所以借书侧我通常不在触发器里做扣减而是仍放在事务里避免“触发器扣一次、应用层又扣一次”的双重扣减。选一种写进文档别两边都留。4.3 两个会话复现并发超借隔离级别与条件更新的差别并发这块不用讲太多理论开两个会话手动跑一遍就懂了。默认隔离级别是可重复读下面这个顺序能稳定复现问题步骤会话 A会话 B观察结果1START TRANSACTION;START TRANSACTION;两个事务开始2SELECT available_copies FROM book WHERE book_id2001;得到 1—A 看到 13—同样查询得到 1B 也看到 14UPDATE ... SET available_copiesavailable_copies-1 WHERE book_id2001 AND available_copies0;—A 影响 1 行5—执行同样的 UPDATEB 阻塞等 A 提交6COMMIT;阻塞解除B 的影响行数为 0结论很清楚不加条件的UPDATE在 B 恢复执行后会读到 A 提交后的值再减一次就把册数减成 0 甚至负数带上AND available_copies 0之后B 的影响行数是 0应用层据此返回失败。可重复读保证的是同一事务内多次读结果一致不是“别人改不了”这一点没搞清楚就很容易写出玄学 bug。另一个坑是连接池的时区。后端连库如果没设serverTimezone插入的时间可能比实际差 8 小时逾期天数算出来是负数看着像日期函数写错了其实是连接参数。统一在连接串里写死时区比在每个查询里补救靠谱。5. 踩坑排查课程设计包换台机器就跑不起来的 5 个高频原因现象一导入脚本报 1215提示无法添加外键约束。原因基本是建表顺序错了或者被引用列的类型、字符集、引擎不一致。book.book_id是BIGINT UNSIGNEDborrow_record.book_id写成BIGINT就会失败因为符号位不同。解决方式是按依赖顺序建表并在脚本头部临时关闭检查导完再打开验证SET FOREIGN_KEY_CHECKS 0; -- 按任意顺序导入数据 SET FOREIGN_KEY_CHECKS 1; -- 验证是否真的干净 SELECT COUNT(*) FROM borrow_record b LEFT JOIN book k ON b.book_id k.book_id WHERE k.book_id IS NULL;现象二中文全是问号或乱码。原因不是表字符集而是连接层。表是 utf8mb4但客户端连接协商成了 latin1写进去就坏了。查三处SHOW VARIABLES LIKE character_set_%里character_set_client、character_set_connection、character_set_results是否一致。解决方式是在连接串里显式指定charsetutf8mb4或者执行SET NAMES utf8mb4;。已经写坏的数据没法靠改字符集救回来只能重导。现象三插入日期报 1292 或 1067提示无效默认值。严格模式下DATETIME NOT NULL不给默认值就会报错0000-00-00也会被拒。解决是把created_at写成DEFAULT CURRENT_TIMESTAMP或者允许 NULL同时检查脚本里有没有从 Excel 拷出来的2024/6/1这种斜杠格式MySQL 不认要用STR_TO_DATE转。这类问题在批量导入时才炸提前用LIMIT 1试插一条能省很多时间。现象四触发器创建成功但改数据报 1442。原因是触发器里对本表做了写操作。定义在borrow_record上的触发器不能更新borrow_record定义在book上的不能更新book。解决办法是把逻辑挪到另一张表或者干脆移到应用层事务里。这个错很隐蔽因为创建触发器的语句不报错运行时才炸。现象五本地能连换台机器报 Public Key Retrieval is not allowed。新版本驱动默认不允许在非加密连接下取服务端公钥属于连接参数问题不是权限问题。做法是在连接串里显式打开这个开关同时确认用户密码认证方式与驱动版本匹配。很多人这时候去改用户权限越改越乱其实只是连接串少一个参数。6. 交付前用一段脚本把系统从头验一遍放到别人机器上能不能跑靠读代码看不出来只能跑。我习惯写一个一次性验证脚本从建库、灌数据、借书、并发争抢、还书到对账一条链走完任何一步异常就中止并打印上下文。这段脚本本身也是交付物的一部分比写一堆说明文档有用。import pymysql DSN dict(host127.0.0.1, port3306, userroot, passwordCourse_2024, databaselibrary, charsetutf8mb4, autocommitFalse) def check(conn, label, expect, sql, argsNone): with conn.cursor() as cur: cur.execute(sql, args) got cur.fetchone()[0] ok got expect print(f[{OK if ok else FAIL}] {label}: expect{expect} got{got}) return ok conn pymysql.connect(**DSN) # 1. 借书前后在库册数必须严格差 1 check(conn, 初始在库, 3, SELECT available_copies FROM book WHERE book_id2001) # 2. 借出上限与余量断言模拟接口层逻辑 with conn.cursor() as cur: cur.execute(UPDATE book SET available_copiesavailable_copies-1 WHERE book_id2001 AND available_copies0) print(扣减影响行数, cur.rowcount) # 应为 1 cur.execute(INSERT INTO borrow_record(reader_id, book_id, due_date) VALUES(1001, 2001, DATE_ADD(NOW(), INTERVAL 30 DAY))) conn.commit() # 3. 还书改状态后由触发器把册数加回 with conn.cursor() as cur: cur.execute(UPDATE borrow_record SET status1, return_dateNOW() WHERE reader_id1001 AND book_id2001 AND status0) conn.commit() check(conn, 归还后在库, 3, SELECT available_copies FROM book WHERE book_id2001) # 4. 对账在库册数必须等于总数减去未还记录任何不一致说明冗余字段失守 check(conn, 对账差额, 0, SELECT (SELECT total_copies FROM book WHERE book_id2001) - (SELECT COUNT(*) FROM borrow_record WHERE book_id2001 AND status0) - (SELECT available_copies FROM book WHERE book_id2001)) conn.close()最后一条对账查询是这个脚本里最有价值的十行。它把冗余字段和事实数据摆在一起比只要触发器漏写、事务提前提交、或者有人手动改过表这个数就不是 0。我一般在交付前跑三次分别在空库、灌了历史数据、以及导入别人给的旧数据之后三种状态下都过才算真的“下载即可使用”。顺带提两个自查项别漏脚本头部写上建库语句CREATE DATABASE IF NOT EXISTS library DEFAULT CHARSET utf8mb4;别让接手的人自己猜库名初始化数据用固定的几条不要依赖本地已经存在的旧表。我吃过一次亏验证脚本里没写清库名在同事机器上跑到了他另一个同名库里把人家的练习数据覆盖了从那以后所有脚本第一行都是显式建库。希望这些能帮你在截止日期前少熬一个通宵。本文还有配套的精品资源点击获取