宿舍管理系统数据库课设：从E-R建表到触发器事务与对账验证

简介：面向数据库课程设计场景的学生宿舍管理系统完整资料，适合正在做课程设计、需要参考双角色权限与业务闭环实现的高校学生。系统围绕寝室长与宿管员两类用户展开，登录后按身份进入不同界面：寝室长可查看寝室人员、提交报修、修改密码；宿管员可检索与维护住宿信息、处理报修状态、管理学生账号。数据库设计覆盖寝室长、宿管员、报修及学生信息四类表单，字段关系清晰，便于按需求增删改。压缩包共3个文件，包含rar源代码包、sql建库脚本和docx文档模板，整体约2.96MB，可配合Word模板整理设计报告。目前已有5057人学习，适合需要快速搭建可运行原型、对照表结构与功能模块查漏补缺的读者。

1. 宿舍管理系统：数据库课程设计里性价比最高的那块练兵场

挑数据库课程设计题目时，学生宿舍管理系统几乎年年被抢。原因不玄学：实体数量刚好卡在能画满一张 E-R 图、又不至于做不完的区间，业务规则又天然带事务、触发器、统计查询，评分点全都能踩到。这份资源给的是可直接跑的 SQL 文件、对应源代码和一份 Word 模板，适合三类人：第一次做课设、需要一份能改的底稿的人；想把建表和业务规则对齐、答辩时不被追问倒下的人；以及手里已经有代码、只缺一套规范文档结构的人。下面按“实体怎么定、表怎么建、规则怎么压进库、哪里最容易翻车、怎么自证设计没问题”的顺序拆。

2. 需求到 E-R：先把实体和关系钉死，再谈建表

2.1 从业务动作反推实体清单，比盯着名词列字段靠谱

多数人一上来就打开建表界面，想到一个字段加一个字段，做到一半发现“调宿记录放哪”答不上来。我的习惯是先列业务动作，再让实体自己浮出来。宿舍管理真正会发生的动作就七类：新生入住、调宿、退宿、报修、水电费结算、访客登记、按楼栋和班级查询统计。每个动作追问三个问题：谁发起、改了哪些数据、事后要能查到什么。

按这个套路走一遍，实体清单基本长这样：

这张表不是给你抄的，是给你对照的：如果你画完 E-R 图发现只有五六张表，多半是把“住宿记录”和“报修”合并进了学生表，那后面的历史查询和调宿功能一定写不出来。

2.2 关系基数的三个易错点：学生与宿舍不是简单的一对多

第一个坑，学生和宿舍。直觉上“一个宿舍住多个学生”就是 1:N，于是很多人在 student 表里加一个 room_id 字段了事。这样做的直接后果是：学生一调宿，旧记录被覆盖，退宿统计、住宿历史、按学期核算全废。正确做法是引入住宿记录表 accommodation，让学生和宿舍都退化成它的 1:N 关系，一个学生对应多条记录，一条记录对应一个学生；一个房间对应多条记录，一条记录对应一个房间。学生表里可以保留一个 current_room_id 作为查询冗余，但它是派生字段，必须和住宿记录同步，不能当成唯一事实来源。

第二个坑，报修单的归属。报修既可能由学生发起，也可能由宿管巡查发起，所以 repair 表里 student_id 应该允许为空，room_id 必须非空。很多人把 student_id 设成 NOT NULL，结果宿管代报修的场景测试时直接插不进数据。

第三个坑，访客和房间。访客登记的业务粒度是“某个访客在某段时间进入了某个房间”，visitor 表里只放 room_id 和姓名、进入时间、离开时间就够了，不要再建一张“访客-学生”的多对多表，除非需求里明确要求记录被访学生。多建一张表就多一组外键和一组答辩时要解释的关系，得不偿失。

2.3 E-R 图转关系模式的转换规则与字段落位

画完图到落表之间还有一步转换，规则其实只有三条，但每年都有人在这里丢分。1:1 关系把主键并到任意一侧并加唯一约束；1:N 关系把“一”端的主键放到“多”端当外键；M:N 关系必须单独建关联表，主键用两个外键的组合或者代理键。住宿记录表就是 M:N 的产物，只是它带了床位号、入住日期这些属性，所以它同时是一个实体。

字段落位讲的是属性该挂在哪张表上，判断标准只有一条：这个属性描述的是“谁”。床位号描述的是“某个学生住进某个房间这件事”，所以它在 accommodation 上；房间容量描述的是房间本身，所以它在 room 上；学生姓名描述的是人，所以它在 student 上。常见错误是把班级名称直接写进学生表，正确做法是学生表存 class_id，班级名称查 class 表，否则改一次班名要更新几十行，还容易不一致。

到这一步，逻辑设计阶段的产出应该是：E-R 图一张、关系模式清单一份、每张表的字段说明。这份清单直接决定后面建表脚本长什么样，也是 Word 模板里“逻辑结构设计”那一章的正文，先把这里写顺，文档不用返工。

3. 建表 SQL 落地：类型选型、约束顺序和索引策略

3.1 字段类型选型：几个每年都吵的点

字符集统一用 utf8mb4，不要用 utf8，中文姓名、楼栋名、备注都能覆盖，避免“建表时是 utf8、连接是 utf8mb4”导致的乱码。排序规则在 MySQL 8.x 上可以直接用 utf8mb4_0900_ai_ci，如果教学环境是 5.7，就统一写 utf8mb4_general_ci，不要在同一个库里混用两种规则，混用之后随便一个关联查询都会报 Illegal mix of collations。

学号建议 VARCHAR(20) 而不是 INT，因为很多学校的学号带年份前缀，甚至带字母，用整型会丢前导零。姓名 VARCHAR(30)，性别用 CHAR(1) 存 'M'/'F'，比 TINYINT 更可读，也比 ENUM 更安全——ENUM 看起来省事，但改枚举值要 ALTER TABLE，而且插入非法值时在非严格模式下会静默变成空字符串，属于事后很难查的玄学问题。

金额一律 DECIMAL(8,2)，不要用 FLOAT，水电费对账差一分钱都能被追问半天。日期用 DATE，时间戳用 DATETIME 并显式设置默认值，别指望 TIMESTAMP 自动时区转换，教学环境里那玩意儿经常和你想的不一样。状态字段统一用 VARCHAR(10) 存 'IN'/'OUT'、'PENDING'/'DONE' 这类短码，比数字状态码可读，答辩演示时一眼能看懂。

3.2 建表脚本：按依赖顺序写，外键最后加

建表顺序必须服从外键依赖，先父后子：class → building → room → student → accommodation → repair → utility_bill → visitor。下面这段是核心骨架，可以直接拿去改字段：

-- 统一下库级字符集，避免后面每张表单独指定 CREATE DATABASE IF NOT EXISTS dorm_mgmt DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci; USE dorm_mgmt; CREATE TABLE class ( class_id INT AUTO_INCREMENT PRIMARY KEY, class_name VARCHAR(50) NOT NULL, major VARCHAR(50) NOT NULL, grade SMALLINT NOT NULL, counselor VARCHAR(30) DEFAULT NULL, UNIQUE KEY uk_class_name (class_name) ) ENGINE=InnoDB; CREATE TABLE building ( building_id INT AUTO_INCREMENT PRIMARY KEY, building_no VARCHAR(10) NOT NULL, gender_type CHAR(1) NOT NULL, -- M 男生楼 / F 女生楼 floors TINYINT NOT NULL DEFAULT 6, UNIQUE KEY uk_building_no (building_no), CONSTRAINT ck_building_gender CHECK (gender_type IN ('M','F')) ) ENGINE=InnoDB; CREATE TABLE room ( room_id INT AUTO_INCREMENT PRIMARY KEY, building_id INT NOT NULL, room_no VARCHAR(10) NOT NULL, capacity TINYINT NOT NULL DEFAULT 4, occupied TINYINT NOT NULL DEFAULT 0, -- 冗余计数，和住宿记录对账 status VARCHAR(10) NOT NULL DEFAULT 'AVAILABLE', -- AVAILABLE / FULL / REPAIR UNIQUE KEY uk_room (building_id, room_no), KEY idx_room_status (status), CONSTRAINT fk_room_building FOREIGN KEY (building_id) REFERENCES building (building_id) ON DELETE RESTRICT, CONSTRAINT ck_room_capacity CHECK (capacity BETWEEN 1 AND 8), CONSTRAINT ck_room_occupied CHECK (occupied >= 0 AND occupied <= capacity) ) ENGINE=InnoDB; CREATE TABLE student ( student_id INT AUTO_INCREMENT PRIMARY KEY, student_no VARCHAR(20) NOT NULL, name VARCHAR(30) NOT NULL, gender CHAR(1) NOT NULL, class_id INT NOT NULL, phone VARCHAR(20) DEFAULT NULL, status VARCHAR(10) NOT NULL DEFAULT 'ACTIVE', -- ACTIVE / GRADUATED / SUSPENDED UNIQUE KEY uk_student_no (student_no), KEY idx_student_class (class_id), CONSTRAINT fk_student_class FOREIGN KEY (class_id) REFERENCES class (class_id) ON DELETE RESTRICT, CONSTRAINT ck_student_gender CHECK (gender IN ('M','F')) ) ENGINE=InnoDB; CREATE TABLE accommodation ( acc_id BIGINT AUTO_INCREMENT PRIMARY KEY, student_id INT NOT NULL, room_id INT NOT NULL, bed_no TINYINT NOT NULL, check_in_date DATE NOT NULL, check_out_date DATE DEFAULT NULL, status VARCHAR(10) NOT NULL DEFAULT 'IN', -- IN / OUT UNIQUE KEY uk_bed_active (room_id, bed_no, status), -- 防一床两人 KEY idx_acc_student (student_id, status), KEY idx_acc_room (room_id, status), CONSTRAINT fk_acc_student FOREIGN KEY (student_id) REFERENCES student (student_id) ON DELETE RESTRICT, CONSTRAINT fk_acc_room FOREIGN KEY (room_id) REFERENCES room (room_id) ON DELETE RESTRICT ) ENGINE=InnoDB;

这段脚本里有几个刻意的设计。uk_bed_active 用 (room_id, bed_no, status) 做唯一键，是为了让同一个床位不能出现两条 status='IN' 的记录，退宿后 status 变成 'OUT'，历史记录能留下，新同学又能入住同一床位——这是不加触发器也能挡住重复分配的低成本手段，代价是同一床位只能留一条 'OUT' 历史，如果要求保留完整历史，就改成部分索引或把唯一键挪到应用层校验。room.occupied 是冗余计数，好处是列表页不用每次 COUNT，坏处是必须靠事务或触发器维护，这一点在第 4 章展开。CHECK 约束在 MySQL 8.0.16 之后才真正生效，5.7 上写了也只是注释，别以为它拦得住脏数据。

报修表和账单表照着同样的方式写：repair 关联 room 和 student（student_id 允许 NULL），utility_bill 用 (room_id, bill_month) 做唯一键防重复出账，金额字段 DECIMAL(8,2)。所有外键统一 ON DELETE RESTRICT，不要在课程设计里用 CASCADE，一旦演示时删了一栋楼，住宿记录连带清空，现场很难解释。

3.3 索引怎么加才不白加

课程设计里加索引最容易走向两个极端：一个不加，或者每个字段都加。判断依据只有一条——看你的查询条件和高频页面的 WHERE、JOIN、ORDER BY。宿舍管理系统里真正高频的就那么几条：

• 按学号查学生：student 上的 uk_student_no 已经覆盖，不用额外加。
• 查某房间当前住户：accommodation 的 (room_id, status)，上面脚本已建。
• 查某学生的住宿历史：accommodation 的 (student_id, status)。
• 报修列表按状态和时间倒序：repair 上建 (status, report_time)。
• 账单按月份和缴费状态筛：utility_bill 上建 (bill_month, pay_status)。

联合索引要注意最左前缀，比如建了 (status, report_time)，只按 report_time 查就用不上。另外别给 gender 这种只有两个值的字段单独建索引，选择性太低，优化器大概率直接全表扫，白占空间。索引不是越多越好，每个索引都会拖慢插入，初始化几万条测试数据时差别很明显。

3.4 初始化数据与字符集检查

建完表先灌数据再谈功能。我一般用一段 Python 脚本批量造学生和住宿记录，比手写 INSERT 快得多，也方便造出“满员房间”“空房间”这些边界场景：

import random from faker import Faker # 教学环境没有就换成固定姓名列表 fake = Faker("zh_CN") rows = [] for i in range(1, 301): student_no = f"2023{i:04d}" name = fake.name() gender = random.choice(["M", "F"]) class_id = random.randint(1, 10) rows.append((student_no, name, gender, class_id)) # 拼成批量插入，一次性提交，比逐条 executemany 快一个量级 values = ",".join(["(%s,%s,%s,%s)"] * len(rows)) sql = f"INSERT INTO student(student_no,name,gender,class_id) VALUES {values}"

这里用 executemany 或拼批量 INSERT 都行，关键是别在循环里一条一条 commit。造完数据后跑一句SHOW VARIABLES LIKE 'character_set_%'，确认库、表、连接三处的字符集一致；再插一条带生僻字和 emoji 的备注，能正常存取就说明 utf8mb4 生效了。这一步花两分钟，能省掉后面文档里中文乱码的一堆麻烦。

4. 把业务规则压进数据库：触发器、事务与视图

4.1 容量与性别校验：BEFORE 触发器做校验，AFTER 触发器做计数

“房间住满了还能再插人”“男生被分到女生楼”是答辩最常被现场演示的两个漏洞。这类规则放在应用层也能写，但课程设计的评分点往往就看数据库端有没有体现约束能力，所以用触发器实现更划算。要点是把校验和计数拆成两个触发器：BEFORE INSERT 只做判断，不修改数据；AFTER INSERT 才去更新 room.occupied。

DELIMITER $$ CREATE TRIGGER trg_acc_before_insert BEFORE INSERT ON accommodation FOR EACH ROW BEGIN DECLARE v_capacity TINYINT; DECLARE v_occupied TINYINT; DECLARE v_room_gender CHAR(1); DECLARE v_room_status VARCHAR(10); DECLARE v_stu_gender CHAR(1); SELECT capacity, occupied, gender_type, status INTO v_capacity, v_occupied, v_room_gender, v_room_status FROM room r JOIN building b ON b.building_id = r.building_id WHERE r.room_id = NEW.room_id; IF v_room_status = 'REPAIR' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '房间维修中，不可安排入住'; END IF; IF v_occupied >= v_capacity THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '房间已满员'; END IF; SELECT gender INTO v_stu_gender FROM student WHERE student_id = NEW.student_id; IF v_stu_gender <> v_room_gender THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '学生性别与楼栋类型不匹配'; END IF; END$$ CREATE TRIGGER trg_acc_after_insert AFTER INSERT ON accommodation FOR EACH ROW BEGIN IF NEW.status = 'IN' THEN UPDATE room SET occupied = occupied + 1, status = IF(occupied + 1 >= capacity, 'FULL', 'AVAILABLE') WHERE room_id = NEW.room_id; END IF; END$$ DELIMITER ;

逻辑上分两段是有原因的：BEFORE 触发器里如果去 UPDATE room，读到的 occupied 还是语句执行前的旧值，连续批量插入时会全部通过校验，等于白写；而且 MySQL 不允许在触发器里修改正在被当前语句使用的表，很容易触发 1442 错误。所以校验放 BEFORE 只读，计数放 AFTER 只写，职责分开，后面排查也清楚。SIGNAL SQLSTATE '45000' 是自定义业务错误的通用写法，应用层捕获后直接把 MESSAGE_TEXT 弹给用户就行。

退宿同理，写一个 AFTER UPDATE 触发器，当 OLD.status='IN' 且 NEW.status='OUT' 时把 occupied 减一，并把房间状态从 FULL 改回 AVAILABLE。注意触发器里要判断状态是否真的发生变化，否则任何一次 UPDATE 都会触发计数，数据很快就对不上。

4.2 调宿事务：两条 UPDATE 之间的原子性不能省

调宿是这个系统里唯一必须上事务的操作：要把一条住宿记录置为 OUT、插入一条新记录、两个房间的计数各改一次。中间任何一步失败，数据就处于“人走了但新房间没进去”的状态。更麻烦的是并发——两个管理员同时把最后一个床位分给不同学生，如果不加锁，两边都能通过容量校验。

START TRANSACTION; -- 按 room_id 排序再加锁，避免两个事务交叉锁导致死锁 SELECT room_id, capacity, occupied FROM room WHERE room_id IN (3, 7) ORDER BY room_id FOR UPDATE; UPDATE accommodation SET status = 'OUT', check_out_date = CURDATE() WHERE acc_id = 1024 AND status = 'IN'; INSERT INTO accommodation(student_id, room_id, bed_no, check_in_date, status) VALUES (215, 7, 2, CURDATE(), 'IN'); COMMIT;

FOR UPDATE 锁住的是 room 表的这两行，容量校验和后续的计数更新就都在锁的保护下。排序加锁是血泪经验：两个事务分别锁房间 3 和 7、再互相请求对方持有的锁，就会死锁，按固定顺序请求能规避掉大部分。事务里要检查 UPDATE 影响行数，如果返回 0，说明这条记录已经被别人退宿了，应该 ROLLBACK 而不是继续插新记录。另外整个事务要尽量短，别把校验提示、日志写入这些放在事务中间。

4.3 视图：报表页不该重写一遍多表 JOIN

宿舍管理的查询页翻来覆去就那几个需求：当前住宿明细、空床位列表、某楼栋入住率、欠费房间。把这些固化成视图，既能让后端代码变短，也能在文档里体现“外模式”的设计层次。

CREATE VIEW v_current_residence AS SELECT s.student_no, s.name, s.gender, c.class_name, b.building_no, r.room_no, a.bed_no, a.check_in_date FROM accommodation a JOIN student s ON s.student_id = a.student_id JOIN class c ON c.class_id = s.class_id JOIN room r ON r.room_id = a.room_id JOIN building b ON b.building_id = r.building_id WHERE a.status = 'IN'; CREATE VIEW v_empty_bed AS SELECT r.room_id, b.building_no, r.room_no, r.capacity, r.occupied, r.capacity - r.occupied AS free_beds FROM room r JOIN building b ON b.building_id = r.building_id WHERE r.status <> 'REPAIR' AND r.occupied < r.capacity;

视图的好处是改口径只改一处，比如以后要排除休学学生，只在 v_current_residence 里加一个条件，所有调用方自动生效。代价是视图不存数据、每次查询都展开执行，所以别在视图里套视图，也别指望它加速。空床位视图用的是 room.occupied 这个冗余计数，如果计数维护错了，视图结果就跟着错，这也是第 6 章要做对账的原因。

5. 避坑与排查：五类真会卡住人的现场

5.1 建表报 1215、删数据报 1451

现象：执行建表脚本时提示 Cannot add foreign key constraint，或者删学生时提示 Cannot delete or update a parent row。

原因基本只有三种：父表还没建、外键字段和主键字段的类型或字符集不一致、被引用列没有索引。删不掉则是子表还有引用数据，外键的 RESTRICT 在起作用。

解决：严格按 class → building → room → student → accommodation 的顺序建表；外键列的类型写成和被引用列完全一致，INT 对 INT，别一边 INT 一边 BIGINT；实在要调整顺序，先建表不加外键，最后统一 ALTER TABLE ADD CONSTRAINT。删数据时按子表到父表的顺序清理，或者临时SET FOREIGN_KEY_CHECKS = 0之后务必记得改回 1，忘了这一步后面所有约束都形同虚设。

5.2 关联查询报 Illegal mix of collations

现象：两个表单独查都正常，一 JOIN 就报字符集排序规则不一致。

原因：建库时没指定字符集，某张表用了 utf8mb4_general_ci，另一张用了 utf8mb4_0900_ai_ci，或者其他表是 utf8。字符集相同、排序规则不同也会报错。

解决：先查SELECT table_name, table_collation FROM information_schema.tables WHERE table_schema='dorm_mgmt'，把不一致的表统一成同一个规则；之后建表脚本里显式写 COLLATE，别依赖服务器默认值。连接串上也加上 charset=utf8mb4，三处对齐才不会反复。

5.3 触发器报 1442，或者计数越跑越偏

现象：插入住宿记录时提示 Can't update table 'room' in stored function/trigger because it is already used by statement。

原因：在 BEFORE INSERT 触发器里 UPDATE 了 room 表，或者触发器里又更新了自己所在的表，形成递归。

解决：校验类逻辑放 BEFORE 只读，计数类逻辑放 AFTER 只写，两者不要混在一个触发器里；如果确实需要在同一张表上做级联更新，改用存储过程由应用层调用。计数跑偏的另一个常见原因是退宿触发器没判断 OLD.status 和 NEW.status 是否真的变了，导致重复扣减，加一层状态变化判断就能解决。

5.4 文档里的字段和 SQL 对不上

现象：Word 模板里字段说明写的是 12 个字段，实际建表只有 10 个，或者字段名大小写不一致。

原因：文档和脚本分开维护，改了一边忘了另一边。

解决：以 DDL 为唯一事实来源，先定稿建表脚本，再照着脚本反向填字段说明表；改字段时先改 SQL 再同步文档。导出 E-R 图也用同一份脚本，别手工另画一份，两边必然对不上。这是答辩时最容易被翻出来的一条，属于纯送分也纯送命的类型。

5.5 演示数据太少，功能看不出效果，计数也对不上

现象：本地只插了十几个学生，索引有没有生效看不出来，入住率统计全是 0% 和 100%，occupied 字段和实际人数偶尔差一两个。

原因：数据量太小加上冗余计数靠手改。测试时图省事直接 UPDATE room SET occupied = 3，忘了和 accommodation 表同步。

解决：至少造三千条学生、一千条住宿记录，几十个房间分布在三到五栋楼，边界场景（满员房、空房、维修房、刚退宿的房）各留几个。occupied 不允许手工改，一律走触发器或事务。演示前跑一遍对账 SQL，确认计数和明细一致再上台。

6. 用验证脚本自证设计：对账、执行计划与文档一致性

设计做完不等于没问题，得能自己证明。我常用的第一条是计数对账，一条 SQL 就能把 room.occupied 和住宿明细的差异全找出来：

SELECT r.room_id, r.capacity, r.occupied, COUNT(a.acc_id) AS actual_in, r.occupied - COUNT(a.acc_id) AS diff FROM room r LEFT JOIN accommodation a ON a.room_id = r.room_id AND a.status = 'IN' GROUP BY r.room_id, r.capacity, r.occupied HAVING diff <> 0;

返回空集才算通过。第二条是床位冲突检查，找出同一房间同一床位存在两条 'IN' 记录的情况，正常情况下也应该为空。第三条是孤儿数据检查，用 LEFT JOIN 找 accommodation 里指向不存在学生或房间的记录，这类数据一旦出现，通常是外键被临时关掉过。

索引是否生效不能靠感觉，用 EXPLAIN 看。EXPLAIN SELECT ... WHERE a.status='IN' AND r.building_id=3跑一遍，重点看 type 是不是 ref 或 range，key 是不是命中了预期的索引，rows 估算值和实际数据量比例是否合理。如果 type 是 ALL，说明在走全表扫，检查一下查询条件有没有破坏最左前缀。这一套跑完，文档里的“测试与性能分析”章节直接有素材，不用编。

Word 模板别只当排版工具用，按它的骨架填内容反而能反过来查设计漏洞：

填这张表的时候你会被迫回答“冗余字段凭什么留”，这恰恰是答辩最容易卡住的地方，能说清楚“为了列表页免 COUNT，靠触发器保证一致”，这个点就变成加分项而不是漏洞。

从那以后我每次交这类设计，都会在打包前把对账 SQL 和 EXPLAIN 各跑一遍，空集和命中索引截图留档，文档里字段说明和 DDL 逐条对一遍才收工。带 SQL 文件、源代码和 Word 模板的这套底稿，拿来改改表名和字段就能开工，省下的时间够你把触发器和事务这两块真正吃透。希望帮到你。

本文还有配套的精品资源，点击获取