简介面向北邮研一数据库课程大作业的完整设计文档适合正在准备课程设计、希望参考标准建模范式的高年级本科生与研究生。内容围绕学生成绩管理系统展开从教务场景梳理成绩录入、学生查询、不及格名单统计、无教学任务教师查询等功能需求并给出 Course、Student、Sc、Teacher 四张表的字段定义、数据类型与主外键约束配套局部与全局 ER 图、关系模式与第三范式判定、函数依赖集以及建库建表的 SQL 语句可按章节对照复现。整包共 1 个 docx 文件约 503KB以图文与代码混排的课程报告形式组织章节结构贴合大作业提交规范。上线以来已有 607 人学习可作为数据库需求分析、概念设计与逻辑设计全流程的撰写与实现参照。1. 一份数据库大作业最容易翻车的地方不是 SQL 写不出来学生成绩管理系统几乎是每门数据库课的标准题目四张表、十几个查询看上去像把课堂例子抄一遍就能交的东西。可真正动手的人会发现卡住你的从来不是SELECT的语法而是「学生删了、成绩还在」「CHECK 约束写了、120 分照样入库」这类问题——它们不报错只在演示现场安静地把你留在黑板上。一份数据库大作业背后其实是一条完整的设计链需求问句 → ER 联系 → 关系模式 → 函数依赖与范式 → DDL 约束 → 视图、存储过程、触发器。链上任何一环偷懒后面都要靠血泪经验去补。下面就从这套学生成绩管理系统出发把每一环的底层机制拆开为什么成绩必须单独成表为什么视图有的能插入有的不能为什么 MySQL 5.5 里那条 CHECK 约束形同虚设。2. 四张表是怎么长出来的一对多与多对多的拆解逻辑2.1 需求里的几个问句决定了表要长什么样需求文档里写了四条功能授课教师录入成绩、学生自查成绩、教务处查各科不及格名单、教务处查没有教学任务的老师名单。很多人读到这儿就直接跳到建表这是设计链上第一个偷懒点。功能问句的真正价值是逼你回答一个建模问题数据以谁为主体、以什么粒度存在。逐条翻译 「教师录入成绩」说明成绩必须绑定到“某个学生 某门课”这个具体的组合上而不是绑定到学生或课程任意一方「学生自查成绩」说明学生维度必须能独立检索因此学生属性不能塞进成绩行里「查不及格名单」说明成绩字段必须是可比较的数值类型并且要能按课程聚合「查没有教学任务的老师」说明教师和课程之间存在一条可空的关系——一个老师可以一课都不带。把这四条摊开实体轮廓就出来了学生、教师、课程、成绩。前三个是名词成绩是动词“选修/考试”留下的结果。ER 图里那个被反复画错的点就在这儿——成绩不是一个实体而是一条联系的属性。原文的全局 E-R 图把“选修”这条联系单独画出来又给它挂上成绩属性这个动作本身就已经在暗示它还需要一张表来承载。需求分析里还有一句容易被忽略的话——系统由授课教师完成成绩录入。这实际上给 course 表增加了一个约束语义一门课在某一学期由一位教师负责因此教师号和课程号之间存在一个业务上的函数依赖。这个依赖后面会直接决定 course 表能不能满足第三范式第 3 章再展开。2.2 多对多关系不能靠加列解决SC 表是必然产物新手最常犯的建模错误是试图在学生表里加一列course_list把「高等数学, 科学导论, 数据库原理」用逗号拼起来存。这个做法在插入时很爽在查询时就会全面崩溃查“选修高等数学的学生”只能靠LIKE %高等数学%索引彻底失效查“不及格名单”必须先把字符串拆开再比较SQL 里根本没有原生手段一门课改名全表字符串都要更新。关系模型给出的答案是把多对多关系降维成两个一对多学生表一头、课程表一头中间插一张联系表。原文里 sc 表就是这个产物-- 成绩表把“学生—课程”的多对多联系拆成两条一对多 CREATE TABLE sc ( sno CHAR(10) NOT NULL, -- 学生编号同时是组合主键的一半 cno CHAR(5) NOT NULL, -- 课程编号组合主键的另一半 degree DECIMAL(4,1), -- 成绩允许 NULL 表示“选了但没考” CONSTRAINT A1 PRIMARY KEY (sno, cno), -- 组合主键一个学生对一门课只能有一条记录 CONSTRAINT A2 CHECK (degree 0 AND degree 100) );sno和cno一起做组合主键这一句就顺手解决了两件事第一它杜绝了同一个学生对同一门课录入两条成绩的脏数据因为重复插入会直接触发主键冲突第二它天然构成了“学生 × 课程”这个笛卡尔积上的唯一标识后面做连接查询时可以直接当索引用。degree允许为 NULL 也是设计到位的地方。NULL 在这里不是“忘了填”而是有明确语义的“已选课未出分”和 0 分是两码事。平均分统计时AVG()会自动跳过 NULL如果把未出分记成 0 分一门课的班级均分会被硬生生拉下来——这是很多人在结果核对时才发现对不上的玄学问题。2.3 主键、外键与「文档里写了、DDL 里没有」的约束原始作业里有个非常典型的现象需求分析章节白纸黑字写着「sc 表设置 sno 和 cno 为组合主键同时作为外键引用 student 表和 course 表」但翻到建表语句CREATE TABLE sc里只有 PRIMARY KEY一条 FOREIGN KEY 都没有。这不是笔误而是绝大多数课程作业的常态——外键只存在于论文的文字里。后果很直接DELETE FROM student WHERE sname示例姓名执行完那条学生记录没了sc 表里他的所有成绩仍然活得好好的教务处的统计报表里会凭空多出一批查不到人的孤儿成绩。数据库不会拦你因为它压根不知道这层关系。真正把外键写进 DDL 应该是这样-- 先建被引用的父表再建子表否则外键创建会失败 CREATE TABLE student ( sno CHAR(9) PRIMARY KEY, -- 注意这里 9 位和 sc 表的 10 位不一致 sname CHAR(8), ssex CHAR(2), smajor CHAR(20), sclass CHAR(10) ); ALTER TABLE sc ADD CONSTRAINT fk_sc_student FOREIGN KEY (sno) REFERENCES student (sno) ON DELETE CASCADE -- 学生被删成绩跟着走 ON UPDATE CASCADE; -- 学号变更成绩跟着改ON DELETE CASCADE和ON DELETE RESTRICT是两种完全不同的业务态度。CASCADE 适合“成绩依附于学生存在”的场景删学生就该删成绩RESTRICT 适合“有成绩就不许删学生”的场景逼你先处理数据。教务系统真实场景里通常选后者因为删学生是有审批流程的成绩不该被顺手带走。这个取舍没有对错只有业务上认不认。提示建外键时 InnoDB 要求子表和父表的列类型严格匹配。原文 sc 表用CHAR(10)、student 表用CHAR(9)这种长度差异会让ALTER TABLE ... ADD FOREIGN KEY直接报错 1215——外键加不上多半不是语法问题而是两边类型没对齐。3. 第三范式与函数依赖拆表到底拆到哪一步停手3.1 从函数依赖推候选码候选码不是拍脑袋选的范式判断之所以让人头疼是因为教科书的定义绕而作业里的属性少。真正好用的做法是先写出函数依赖集再从依赖里找候选码——候选码就是那些“能唯一决定其余所有属性”的最小属性组。以 student 表(sno, sname, ssex, smajor, sclass)为例。学号决定姓名、性别、专业、班级这四条依赖的左边都是 sno右边是其余属性。sno 本身就能决定全部属性且不能再缩小所以 sno 是候选码也是主键。整个表里不存在“非主属性依赖于候选码的一部分”的情况因为候选码只有一个属性谈不上“一部分”。因此 student 表自动满足 2NF。sc 表(sno, cno, degree)稍微复杂一点。候选码是(sno, cno)这个二元组。degree 依赖于整个二元组吗是的——单独给一个学号推不出成绩单独给一个课程号也推不出成绩必须两者同时确定。没有部分依赖也没有传递依赖sc 表同样满足 3NF。course 表(cno, cname, tno)的依赖关系是cno → cname、cno → tno。候选码是 cno两个非主属性都完全依赖于它中间没有“cno → tno → cname”这种传递链所以也满足 3NF。原文里写「课程号和教师号为该关系的候选码」这句话本身有问题如果 tno 也是候选码意味着一个教师号能唯一确定一门课那就变成“一个老师只能教一门课”了与业务明显矛盾。这段表述上的含糊恰恰说明作者当时是靠感觉判断范式而不是靠依赖集推导。养成先把依赖写出来的习惯范式判断会从玄学变成算术。3.2 3NF 的边界course 表里的 tno 算不算冗余按上面的推导course 表带一个 tno 是合规的 3NF没有任何问题。但换个业务前提结论立刻翻转。如果一门课可以由多位教师开设——比如“数据库原理”同时由三位老师分别带班——那么(cno, tno)就成了真正的组合候选码一门课对应多条记录cno 不再能唯一决定 cnamecname 只依赖 cno 这一半构成了对候选码的部分依赖表就退化成 2NF。这时候正确的拆法是三张表course(cno, cname)、teacher(tno, tname, tdept)、teach(cno, tno)把“谁教哪门课”这条多对多联系单独拎出来。所以 3NF 的边界不取决于属性名而取决于业务基数。同一个字段在前一种业务里是合理冗余在后一种业务里就是范式违规。判断的时候不要背“非主属性不传递依赖于主键”这句话改问自己这个属性到底由谁唯一决定如果答案不是主键的全部那就要警惕了。这种拆分带来的代价也很真实查询“某学生所有课程及任课教师”从两表连接变成三表连接SQL 变长执行计划里多一次 join。原文的复杂查询里已经有student INNER JOIN sc INNER JOIN course这种三表写法再叠一层 teacher 就是四表。教育系统的数据量通常不大多一次 join 换来的是数据一致性这笔账是划算的但如果放到千万级日志场景反范式化把 teacher 名字冗余进成绩表反而是常规操作。规范不是教条是要拿业务规模去折算成本的。3.3 字段类型是设计的一部分CHAR 与 DECIMAL 的取舍建表语句里最容易被跳过的部分就是类型和长度但真正让系统出问题的地方常常就在这儿。先看degree DECIMAL(4,1)。这个声明表示总共 4 位有效数字、其中小数点后 1 位取值范围是 -999.9 到 999.9。成绩 0 到 100 完全放得下还留了余量。如果写成DECIMAL(3,1)最大值就变成 99.9一个 100 分直接报 out of range——这种错误在期末录分时才会暴露属于典型的延迟爆炸。反过来如果用FLOAT存成绩86.1 可能在内部变成 86.09999999两个浮点数做比较会失败AVG()的汇总结果也会带一串小数尾巴。分数这种需要精确比较和求和的量用定点数几乎总是优于浮点数。再看CHAR(9)和CHAR(10)。CHAR 是定长类型不足长度会用空格补齐。student 表声明 sno 是 9 位sc 表声明 10 位这个不一致在纯查询阶段不会立刻出问题因为 MySQL 比较 CHAR 时会忽略尾随空格120210332 和120210332被认为是相等的。但它埋了两颗雷一是前面说的外键加不上二是当有人用INSERT INTO sc VALUES(120210332 , ...)带一个尾随空格写入时SELECT * FROM sc WHERE sno 120210332能查到而按长度做校验的逻辑会判它不合格。定长字段的长度约定是系统契约的一部分四处不一致就是四处裂缝。注意学号这类只含数字但语义上不是数值的标识一律用字符类型。用 INT 存学号前导零会被吃掉超出 INT 范围会溢出而且它本来就不参与算术运算。4. 视图、存储过程与触发器把逻辑下沉到数据库里会发生什么4.1 视图的可更新边界v_student 只读view_sc 可写原文建了两个视图一个能查不能写另一个能直接往里插数据。这个差异不是巧合是 MySQL 可更新视图判定规则在起作用。-- 三表连接视图理论上只读 CREATE VIEW v_student AS SELECT A.sno, A.sname, C.degree FROM student A, course B, sc C WHERE A.sno C.sno AND B.cno C.cno AND B.cname 科学导论; -- 单表投影视图含主键 sno、cno可更新 CREATE VIEW view_sc AS SELECT sno, degree, cno FROM sc;MySQL 判断一个视图能否被 INSERT/UPDATE/DELETE核心条件是视图里的每一行必须能一对一地映射回基表的一行。具体到实现上视图定义里不能出现聚合函数、DISTINCT、GROUP BY、HAVING、UNION、子查询出现在 select 列表里也不能是多表连接——只要出现其中任意一条视图就退化为只读。v_student违反了多表连接这一条三张表的数据合到一起一行视图记录可能对应 sc 表的一行加上 student、course 各自的一行反过来往视图里插一条(sno, sname, degree)数据库无法判断这条记录应该拆到哪里去sname 该写 student 表degree 该写 sc 表而 cno 根本没在视图里出现。所以它只能读。view_sc是单表投影字段来自 sc 表的三列其中 sno 和 cno 一起构成基表主键——主键被完整包含在视图里这是可更新视图的关键条件。数据库拿到INSERT INTO view_sc VALUES(140210321,88,C01)之后只要把三个值按列对应关系映射回 sc 表就能确定唯一一行插入得以成立。如果视图里漏掉了主键列数据库就无法定位哪一行更新同样会被拒绝。视图的价值不只是省事。它是一个解耦层应用层只依赖view_sc的列顺序基表后面加字段、改字段名都不会影响应用。但这份便利要靠可更新性的代价换一旦业务需要跨表聚合视图就只能退化成查询封装写入逻辑得回到应用层或者存储过程里。4.2 DELIMITER 不是 SQL 语句存储过程的客户端侧真相存储过程那段代码里最让人困惑的往往是开头那个DELIMITER //。它长得像 SQL其实服务端根本不认识这条指令——它是 mysql 命令行客户端自己解析的。原因在协议层客户端靠分号判断一条语句结束把整段文本发给服务端。存储过程体内本身就有多条以分号结尾的语句客户端一遇到第一个分号就把半截CREATE PROCEDURE发出去了服务端自然报语法错误。DELIMITER //的作用是把客户端的语句结束符临时改成//让整段过程体作为一个整体发送结束后再用DELIMITER ;改回来。DELIMITER // -- 客户端侧把结束符临时改成 // CREATE PROCEDURE num_sc( IN tmp_sno CHAR(9), -- 入参要统计的学号 OUT count_num INT -- 出参该学生的选课门数 ) READS SQL DATA -- 声明只读不写供优化器参考 BEGIN SELECT COUNT(*) INTO count_num -- INTO 把聚合结果写进出参变量 FROM sc WHERE sno tmp_sno; END // DELIMITER ; -- 改回分号否则后续语句全乱 CALL num_sc(120210332, n); -- 用用户变量接收出参 SELECT n;IN 和 OUT 的区别是这套机制的关键。IN 参数是值传递调用时把实参复制进去过程内修改不影响外部OUT 参数是引用传递过程结束时把变量值写回调用方的变量里。所以调用时必须传一个用户变量n直接CALL num_sc(120210332, 0)拿不到结果。这种“有出参就得有承接变量”的模式正是存储过程比普通 SQL 难调试的地方——返回值不在结果集里藏在一个变量中用SELECT n才能看见。顺带提一句在 Navicat 这类图形客户端里粘贴上述代码时DELIMITER的处理方式各不相同有的会自己识别过程体边界并忽略这行指令。命令行能跑通、图形工具报错八成就是这行在做怪——这是黑匣子第一次露头。4.3 触发器与 OLD 伪记录删除留痕的实现路径触发器解决的是“某个动作发生时必须顺带做另一件事”的问题。原文的需求是实现删除学生时留痕-- 建一张结构相同的空表接收被删除的学生记录 CREATE TABLE delstudent AS SELECT sno, sname FROM student WHERE 1 0; CREATE TRIGGER trig_student AFTER DELETE ON student FOR EACH ROW INSERT INTO delstudent(sno, sname) VALUES(OLD.sno, OLD.sname);AFTER DELETE表示触发时机在被删行已经从 student 表消失之后。FOR EACH ROW是行级触发器的声明表示每删除一行就执行一次触发器主体——如果不写这句MySQL 会直接拒绝创建。OLD是触发器里的伪记录代表被删除行在删除前的值。DELETE 场景下只能用 OLD因为新值不存在对应地INSERT 场景只能用 NEWUPDATE 场景两者都能用。这个设计是合理的删掉的东西只能回忆插入的东西只能展望。真正值得琢磨的是时机——AFTER而不是BEFORE。如果写成 BEFORE DELETE 再往 delstudent 插数据一旦后面某一步失败回滚留痕表里的记录还在就会出现“人没删掉痕迹却留下了”的不一致。AFTER 触发器在事务内执行与主操作同生共死这个语义才符合“留痕”的本意。CREATE TABLE ... SELECT ... WHERE 10这个建空表的手法很常见但代价不小第 5 章会专门讲。4.4 CHECK 约束在 MySQL 5.5 中的沉睡原文环境写的是 MySQL 5.5而 sc 表里那条CONSTRAINT A2 CHECK (degree 0 AND degree 100)在 5.5 里是一句被解析、被记录、然后被彻底忽略的声明。MySQL 在相当长的版本周期里不支持 CHECK 约束。语法解析器能识别它建表不会报错SHOW CREATE TABLE也能看到它但插入 999 分照样成功。原因在存储引擎层CHECK 需要每次写入时求值而早期设计把这类校验留给了应用层服务端只在解析阶段做语法确认。真正的强制校验要到 8.0.16 才落地。这个细节的意义超出作业本身。它说明“约束”这个词在不同层次上含义完全不同写在文档里是设计意图写在 DDL 里是语法声明只有被引擎真正执行的才是有效的防线。三者之间可以差着好几个版本。当年那条 CHECK 看起来固若金汤实际上一条 120 分的记录能畅通无阻地进库然后安静地躺在不及格名单的判定逻辑外面。在 5.5 环境下要真正卡住范围只有两条路用触发器 BEFORE INSERT / BEFORE UPDATE 里显式判断并 SIGNAL 报错或者用 ENUM/外键之类的替代手段更常见的做法是把校验放到应用层。这也是为什么很多老系统里会看到一堆触发器在做本该由 CHECK 完成的事。5. 避坑与排查五处不会报错却会让演示翻车的细节5.1 外键只写在文档里删掉学生成绩成了孤儿现象删除一条 student 记录后SELECT * FROM sc WHERE sno120210332仍能查出该学生的全部成绩统计不及格名单时出现查不到姓名的空行。原因需求章节声明了外键关系但CREATE TABLE sc里只有 PRIMARY KEY没有任何 FOREIGN KEY 子句。数据库从未被告知这两张表有关联自然不会做任何级联或拒绝动作。解决按 2.3 里的写法补上外键并明确ON DELETE行为。补之前先跑一遍孤儿数据清理否则加约束时会被存量脏数据挡住-- 先找出没有对应学生的成绩记录 SELECT s.sno, s.cno FROM sc s LEFT JOIN student t ON s.sno t.sno WHERE t.sno IS NULL; -- 清理后加外键加了 RESTRICT 之后删学生会被显式拒绝 ALTER TABLE sc ADD CONSTRAINT fk_sc_student FOREIGN KEY (sno) REFERENCES student(sno) ON DELETE RESTRICT;5.2 CHECK 写了但没生效120 分照样入库现象表定义里明明白白有CHECK (degree 0 AND degree 100)手工插入INSERT INTO sc VALUES(120210332, 120, C01)却成功。原因MySQL 5.5 只解析 CHECK 子句不执行校验存储引擎层没有对应的求值逻辑。解决用 BEFORE INSERT / BEFORE UPDATE 触发器兜底在写库前判断并主动报错。DELIMITER // CREATE TRIGGER trg_degree_check BEFORE INSERT ON sc FOR EACH ROW BEGIN IF NEW.degree IS NOT NULL AND (NEW.degree 0 OR NEW.degree 100) THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 成绩必须在 0 到 100 之间; END IF; END // DELIMITER ;SIGNAL SQLSTATE 45000是用户自定义错误的通用写法会让插入直接失败并抛出指定消息。注意 UPDATE 场景要另建一个 BEFORE UPDATE 触发器两个时机不能共用。5.3 sno 长度不一致CHAR(9) 与 CHAR(10) 的隐性冲突现象ALTER TABLE sc ADD FOREIGN KEY (sno) REFERENCES student(sno)报错 1215语法检查一遍也找不出问题。原因student.sno 是 CHAR(9)sc.sno 是 CHAR(10)。InnoDB 要求外键列与被引用列的类型完全一致长度不同直接拒绝。这条规则不看你实际存了几个字符只看声明。解决统一成同一个长度并考虑是否要加字符集与排序规则一致性。改列时用 MODIFY注意已有数据超出新长度的会被截断-- 改成一致长度先确认没有超长数据 SELECT MAX(CHAR_LENGTH(sno)) FROM sc; ALTER TABLE sc MODIFY sno CHAR(9) NOT NULL;5.4 用 CTAS 建留痕表主键索引一起丢了现象CREATE TABLE delstudent AS SELECT sno, sname FROM student WHERE 10建出来的表能插数据但后面查得慢、还能插重复记录。原因CREATE TABLE ... AS SELECT只复制列定义和数据不复制主键、索引、自增属性、默认值和字符集配置。建出来的 delstudent 是一张既无约束也无索引的裸表。解决显式写完整建表语句把需要的约束补上。CREATE TABLE delstudent ( sno CHAR(9) PRIMARY KEY, sname CHAR(8), del_time DATETIME DEFAULT CURRENT_TIMESTAMP );顺手加一个del_time字段是血泪经验——留痕表只记“谁被删了”用处有限带上删除时间事后排查“哪个操作删的、什么时候删的”才有意义。5.5 插入数字学号不加引号隐式转换悄悄吞掉索引现象INSERT INTO student (sno, sname, ssex) VALUES (120210455, 示例姓名, 男)执行成功但后续按学号查询时偶尔查不到或者执行计划显示全表扫描。原因sno 定义成 CHAR插入时给的是数字字面量MySQL 会做隐式类型转换把数字转成字符串再存。更麻烦的是反向场景——WHERE sno 120210455时优化器可能把列转成数字比较导致 sno 上的索引失效。解决字符类型的列值一律加引号。这是最便宜也最容易忘的一条INSERT INTO student (sno, sname, ssex) VALUES (120210455, 示例姓名, 男); -- 引号保命排查手段是用EXPLAIN看type列出现ALL就是全表扫描。另外注意约束名的坑原文 course 表和 teacher 表都写了CONSTRAINT C1 PRIMARY KEY(...)在 MySQL 里主键的约束符号是被忽略的主键永远叫 PRIMARY重名不报错也不生效。真正会冲突的是外键和唯一索引的约束名全局唯一。6. 约束该放在哪一层从课程作业到工程直觉写完这套表再回头看最值得琢磨的不是哪个 SQL 写得漂亮而是一个反复出现的选择题约束到底该放在哪里。数据库层能提供的约束有主键、唯一索引、外键、非空、默认值还有真正生效时的 CHECK。这些约束的特点是“无法绕过”——不管从哪个应用、哪条 SQL 进来都会被拦下。代价是刚性加外键要清存量数据改类型要锁表线上大表加约束基本是一次小型手术。应用层约束反过来灵活、易改、能给出友好提示但只要有一个入口绕过去数据就烂了。我的判断标准是这样能由数据本身定义的不变量尽量下沉到数据库。学号唯一、成绩在 0 到 100、一个学生对一门课只有一条成绩这三条无论业务怎么演进都不会变就该让数据库守住。而“删除学生需要教务审批”“成绩修改要留操作人”这类带流程语义的规则属于应用层数据库管不了也不该管。原文里 CHECK 与触发器的取舍就是这条标准的现成案例——5.5 的 CHECK 不生效触发器成了唯一能落地的数据库层防线虽然它比 CHECK 重得多。还有一层容易被忽略范围校验最该防的其实不是恶意输入而是自己和同事的手滑。期末录分时把 86 打成 860一条 CHECK 或者一个触发器能立刻拦下一条都没有的话这个错误会跟着统计报表一路走到教务处。这种后悔药装的时候嫌麻烦出事的时候救命。验证这套设计是否真的立住了有个很朴素的办法写一组故意违规的 SQL逐条跑一遍看数据库拦不拦。删一个有成绩的学生、插一个 120 分、插两条同学生同课程的成绩、插一个不存在的课程号。四条里只要有任意一条通过了就说明那一层约束是空的。这组用例比任何文档都诚实。从那以后我每次做完表结构都会先跑一遍这四条“故意找茬”的语句确认每一条都被正确拒绝才敢往下写业务查询。约束这东西只有在你试着违反它并被拦住的时候才能确认它真的存在而不是只存在于设计文档的某一段文字里。希望帮到你。本文还有配套的精品资源点击获取