简介这份资源是北邮研一数据库课程大作业的完整详解文档面向正在学习关系数据库、需要完成课程设计的高校学生。内容围绕学生成绩管理系统展开涵盖需求分析、数据库设计、ER图绘制、逻辑结构设计与建表程序等环节并给出Course、Student、Sc、Teacher四张表的字段定义、主外键约束与函数依赖分析还涉及不及格学生名单统计、无教学任务教师查询等具体功能实现。资源包共1个docx文件约503KB以文字与表格形式系统整理了从概念设计到SQL建表的全过程便于对照理解数据库规范化与完整性约束的落地方式。目前已有606人学习下载适合作为课程设计参考、实验报告撰写模板或期末复习的案例材料。1. 学生成绩管理系统为什么四张表就能撑起一个教务系统很多人第一次看到学生成绩管理系统的表结构第一反应是“这也太简单了”——四张表字段加起来不到二十个建表语句几十行就能写完。但真正跑起来你会发现不及格名单统计、无教学任务教师查询、按课程名反查选课学生这些需求全都能从这四张表里推出来而且推得干净利落。这背后不是“表少所以简单”而是关系模型把业务语义压缩进了主键和外键的约束里。换句话说表结构本身就是业务规则的一种编码形式。搞懂这套编码逻辑比背会十条 SQL 语句有用得多。这份数据库大作业的价值也在这里它用一个足够小的场景把 ER 建模、范式推导、约束设计和查询分解串成了一条完整的链路让你看到“设计”和“实现”之间到底发生了什么。2. 从 ER 图到四张表实体和联系是怎么被拆开的2.1 为什么学生和课程之间必须有一张 sc 表先看实体。学生Student、课程Course、教师Teacher是三个独立存在的对象各自有主键sno、cno、tno。但“学生选修课程”这件事既不属于学生也不属于课程它是一个联系。联系在关系模型里不能凭空存在必须落成一张表于是有了 sc。sc 的主键是 (sno, cno) 组合主键这个选择不是随便定的。如果只用 sno 做主键一个学生就只能选一门课只用 cno 做主键一门课就只能被一个学生选。组合主键的本质是选课记录的唯一标识是“谁选了哪门课”这个二元组。degree 字段放在 sc 里而不是 student 或 course 里也是同样的道理——成绩是选课这个动作的结果不是学生或课程的固有属性。常见的实现思路是把多对多联系拆成两张一对多Student 到 sc 是一对多Course 到 sc 是一对多。这样任何一张表都不存储冗余的关联信息查询时通过 JOIN 把语义重新组装回来。2.2 函数依赖怎么决定字段该放哪张表逻辑结构设计里提到每张表都满足第三范式这不是一句口号。以 student 表为例函数依赖是 sno → sname, ssex, smajor, sclass。学号决定姓名、性别、专业、班级反过来不成立。如果把 sclass 拆出去单独建一张班级表当然也可以但在这个规模下会增加 JOIN 成本收益不明显。course 表的依赖是 cno → cname, tno。注意 tno 在这里是“任课教师号”它依赖于课程号而不是独立的主键。这意味着同一门课由一位教师负责但一位教师可以教多门课。如果业务变成“一门课由多位教师合上”course 表就得再拆出一张授课关系表。范式分析的价值就在于它让你在需求变化时知道该动哪张表而不是到处打补丁。sc 表的依赖是 (sno, cno) → degree。degree 完全依赖于组合主键不存在部分依赖或传递依赖所以它待在 sc 里是干净的。如果 degree 依赖于 sno 而不依赖于 cno那就说明成绩是学生属性而非选课属性应该挪回 student 表。2.3 主键、外键和 CHECK 约束各自守什么门建表语句里几个约束值得逐条看CREATE TABLE sc ( sno char(10) NOT NULL, degree decimal(4,1), cno char(5) NOT NULL, CONSTRAINT A1 PRIMARY KEY(sno, cno), CONSTRAINT A2 CHECK(degree 0 and degree 100) );PRIMARY KEY(sno, cno) 保证同一个学生不能对同一门课重复选课这是业务上的硬规则。CHECK 约束把成绩锁定在 0 到 100 之间防止录入时手滑打出 860 这种值。NOT NULL 加在 sno 和 cno 上是因为组合主键的成员不允许为空——否则“谁选了课”这个语义就断了。外键约束在原文中没有显式写出但从 ER 图的依赖关系可以推断sc.sno 引用 student.snosc.cno 引用 course.cnocourse.tno 引用 teacher.tno。外键的作用不只是“连表查询方便”它还在删除和更新时保护引用完整性。比如删除一个还有选课记录的学生数据库会直接拒绝除非你先处理 sc 里的关联行。这就是为什么后面触发器要用 AFTER DELETE 来记录被删学生——因为正常情况下你根本删不掉。注意MySQL 中 CHECK 约束在 8.0.16 之前是被解析但忽略的如果运行环境是 MySQL 5.5这条约束实际上不会生效需要在应用层或触发器里补校验。3. 视图、存储过程和触发器把业务逻辑下沉到数据库3.1 视图不是“简化查询”而是权限和抽象的边界原文里创建了一个 v_student 视图把 student、course、sc 三张表 JOIN 在一起对外只暴露学号、姓名、成绩三个字段。这个做法的意义不只是“少写 JOIN”。视图在数据库里扮演的是抽象层的角色底层表结构可以变只要视图定义跟着调整上层查询不用动。CREATE VIEW v_student AS SELECT A.sno, sname, degree FROM student A, course B, sc C WHERE A.sno C.sno AND B.cno C.cno AND cname 科学导论;这条视图把“选修科学导论的学生成绩”这个业务问题固化成了一个命名对象。教务处的人不需要知道三张表怎么连只需要 SELECT * FROM v_student。另一个 view_sc 视图更直接只做了字段投影但它的用途是配合 INSERT——通过视图插入数据时数据库会自动把值映射回基表 sc。视图的可更新性是有条件的如果视图定义里包含聚合函数、GROUP BY、DISTINCT 或跨多表的复杂 JOININSERT/UPDATE 通常会被拒绝。view_sc 之所以能插入成功是因为它只做了简单的字段选择没有破坏基表到视图的映射关系。3.2 存储过程把“统计”变成可复用的数据库对象两个存储过程分别解决两类问题。proc_stud 是无参查询按班级模糊匹配返回学生列表DELIMITER // CREATE PROCEDURE proc_stud() READS SQL DATA BEGIN SELECT sno, sname, smajor FROM student WHERE sclass LIKE %4412% ORDER BY sno; END // DELIMITER ;这里 DELIMITER 的作用是把语句结束符从分号临时改成 //否则 MySQL 会在第一个分号处截断存储过程体。READS SQL DATA 是告诉数据库这个存储过程只读不写优化器可以据此做一些权限和缓存上的处理。num_sc 是带 IN 和 OUT 参数的存储过程统计某个学生的选课数量CREATE PROCEDURE num_sc(IN tmp_sno char(9), OUT count_num INT) READS SQL DATA BEGIN SELECT COUNT(*) INTO count_num FROM sc WHERE sno tmp_sno; END //IN 参数由调用方传入OUT 参数由存储过程写回。调用时用 CALL num_sc(120210332, cnt)然后 SELECT cnt 取结果。这种模式适合把统计逻辑封装在数据库端避免每次都在应用层拼 SQL。3.3 触发器删除学生时自动留痕触发器 trig_student 的定义是 AFTER DELETE ON student意思是当 student 表有行被删除后自动把被删行的 sno 和 sname 插入 delstudent 表CREATE TRIGGER trig_student AFTER DELETE ON student FOR EACH ROW INSERT INTO delstudent(sno, sname) VALUES(old.sno, old.sname);old 是触发器里的特殊别名代表被删除的那一行。FOR EACH ROW 表示每删一行就触发一次而不是整条 DELETE 语句只触发一次。这个设计解决的是“删除后无法追溯”的问题——学生记录没了但操作痕迹还在。注意触发器里的操作和主语句在同一个事务中如果触发器插入失败删除操作也会回滚。这既是保护也是风险delstudent 表如果被锁住删除学生会直接失败。3.4 复杂查询的分解思路原文列了几条典型查询值得看的是它们怎么把业务问题翻译成 JOIN 条件。比如“查询选修高等数学的学生学号和姓名”SELECT A.sno, sname FROM student A INNER JOIN sc B ON A.sno B.sno INNER JOIN course C ON B.cno C.cno WHERE C.cname 高等数学;这条查询的路径是从课程名找到课程号从课程号找到选课记录从选课记录找到学生。每一步都是一次 JOIN条件写在 ON 里而不是 WHERE 里语义更清晰。不及格名单查询同理只是多了一个 degree 60 的过滤条件。“没有教学任务的老师”这个需求原文没有给出 SQL但思路是反连接先找出所有出现在 course.tno 里的教师号再从 teacher 表里排除掉。SELECT tno, tname FROM teacher WHERE tno NOT IN (SELECT DISTINCT tno FROM course WHERE tno IS NOT NULL);NOT IN 在这里要小心 NULL 值如果子查询返回任何 NULL整个 NOT IN 会变成 UNKNOWN结果为空。所以子查询里加了 tno IS NOT NULL。更稳妥的写法是用 NOT EXISTS 或 LEFT JOIN ... WHERE ... IS NULL。4. 避坑与排查建表和查询里最容易翻车的几个点4.1 字符集不一致导致中文乱码现象插入“科学导论”后查询显示为问号或乱码。原因数据库、表、连接三层的字符集不统一常见的是数据库用 latin1连接用 utf8。解决建库时指定 CHARACTER SET utf8mb4建表时继承库的字符集连接串里也显式声明 charsetutf8mb4。MySQL 5.5 默认字符集不是 utf8mb4需要手动改。4.2 组合主键顺序影响索引效率现象sc 表按 cno 查询很慢按 sno 查询很快。原因组合主键 (sno, cno) 的索引先按 sno 排序再按 cno 排序。单独用 cno 做条件时无法利用索引的最左前缀。解决如果按课程查成绩是高频操作需要额外建一个 cno 上的索引或者把组合主键顺序调整为 (cno, sno)。但后者会影响“按学生查成绩”的效率需要根据实际查询分布权衡。4.3 触发器里误用 NEW 和 OLD现象DELETE 触发器里写 NEW.sno 报错。原因DELETE 操作没有“新行”只有“旧行”所以只能用 OLD。INSERT 触发器只能用 NEWUPDATE 触发器两者都能用。解决记住口诀——删旧插新改两全。写触发器前先确认操作类型。4.4 视图插入数据时基表 NOT NULL 字段被忽略现象通过 view_sc 插入 (sno, degree, cno) 成功但基表 sc 里其他 NOT NULL 字段没有值。原因视图只暴露了部分字段插入时未暴露的字段如果允许 NULL 就填 NULL不允许 NULL 就报错。解决确保视图包含基表所有 NOT NULL 且无默认值的字段或者在视图定义里用常量补齐。4.5 存储过程 DELIMITER 忘记恢复现象创建存储过程后后续所有 SQL 语句都报语法错误。原因DELIMITER // 把结束符改成了 //但没有改回分号导致后续语句被当成存储过程体的一部分。解决存储过程定义结束后立即执行 DELIMITER ; 恢复默认结束符。在 Navicat 里有时不需要手动改 DELIMITER但命令行客户端必须注意。5. 设计取舍小系统里的大问题这套四张表的设计放在教学场景里是够用的但它刻意回避了几个真实系统里绕不开的问题。第一个是成绩的时效性degree 直接存在 sc 表里没有版本概念。如果学生查完成绩后教师修改了分数学生看到的会直接变没有任何“修改记录”可查。真实教务系统通常会在 sc 之外再加一张成绩变更日志表记录每次修改的前后值和操作人。第二个是教师和课程的绑定关系。course.tno 把任课教师写死在课程表里意味着同一门课在不同学期由不同教师上时必须新建一条 course 记录。更合理的做法是把“课程”和“开课实例”分开课程表只存 cno 和 cname开课表存开课号、cno、tno、学期。这样同一门课可以有多位教师、多个学期历史数据也不会混在一起。第三个是权限模型。原文的需求里提到“授课教师完成成绩录入学生自行查询”但表结构里没有任何字段能区分“谁有权录哪门课的成绩”。真实系统需要在教师和课程之间建立明确的授权关系而不是靠应用层硬编码。我一般会这么理解教学作业的价值不在于它覆盖了多少功能而在于它逼你把 ER 图上的每一条线都翻译成主键、外键和约束。当你发现“没有教学任务的老师”这个查询需要反连接、NOT IN 要处理 NULL、组合主键顺序影响索引时你已经在触碰真实系统里那些不会写在需求文档里的细节了。从那以后我每次拿到一个新表结构都会先问三个问题主键是什么、外键指向谁、哪些字段允许为空。这三个问题答完大部分查询的写法基本就定了。希望帮到你。本文还有配套的精品资源点击获取