简介面向北邮数据库课程学习者的实验四完整报告围绕在线考试系统的数据库模式设计展开覆盖需求分析、E-R图构建、逻辑模式与物理模式转换以及使用Power Designer完成概念模型到物理模型的映射和SQL脚本生成。报告定义了用户、试题库、知识点、试卷、考试管理五个核心实体及其属性关系并给出在IBM DB2环境中执行脚本创建表与视图的具体步骤包含考试信息、在线试卷两个视图的设计参考。资源为单一doc文档大小约1.56MB内含实验目的、实验环境、实体定义、模型截图及结果分析等完整章节结构清晰便于对照查阅适合正在完成数据库实验、复习E-R图与模式转换知识的同学直接参考。已有126人学习下载可作为同类实验报告撰写或数据库设计入门的对照资料。1. 数据库模式设计北邮实验四到底在考什么北邮数据库实验四-数据库模式的设计说穿了就是让一个人从需求描述开始把现实业务抽象成一张张带约束的表。很多同学把它当成“用SQL建几张表交差”但实验评分真正盯的是ER图是否完整、关系模式是否消除了明显冗余、主外键和约束是不是经得起插入和查询的考验。这篇笔记按常见实验要求走一遍从画ER图到写SQL再到查坑顺便回答“模式设计到底要设计到什么程度”。适合正在做实验的学生也适合刚入职需要独立设计数据库表的开发。照着做你能交出一份能跑、能讲清楚、老师问不倒的作业。2. 从需求到ER图实验四的建模第一步2.1 先别急着建表把需求文本里的实体、属性和联系找出来通常实验四给一段像“学生选修课程每门课程有一位授课教师学生选课后获得成绩”这样的自然语言需求。第一步不是打开数据库客户端而是拿一支笔把句子里的名词圈出来学生、课程、教师、成绩、授课。名词往往是实体名词前的定语往往是属性动词往往是联系。用这个规则不会漏掉核心要素。我一般会先建一张对照表左边放需求原文里的短语右边放模型元素。比如“学生学号、姓名、专业”判断为实体学生属性有学号、姓名、专业“课程课程号、课程名、学分”判断为实体课程“教师工号、姓名、职称”判断为实体教师“一门课程由一位教师教授”判断为联系授课“学生选修课程后取得成绩”判断为联系选课成绩是选课这个联系的属性不是学生或课程的属性。这样一划模式设计的骨架就出来了。这里有一个新手很容易忽略的问题属性到底该挂在实体上还是挂在联系上。上面例子里的“成绩”就必须挂在选课联系上因为一门课程可能被多个学生选修同一个学生也可能选多门课程成绩不是单独某个学生或单独某门课能确定的。如果把成绩挂到课程上就会出现“每个学生的成绩都要往课程里塞”的尴尬挂到学生上一门课只能存一个成绩。判断标准是看这个属性的取值是否需要“实体对”同时成立。需要就放联系。还有一类属性要特别小心就是派生属性。比如“学生所选课程的总学分”它由选课记录和课程学分算出来不是基础属性。实验报告里如果把派生属性也建成列插入时要手动同步更新时又容易漏改属于典型的“自己给自己挖坑”。所以建表前先把需求句子里能通过其他数据计算出来的字段单独列成“派生属性”清单设计模式时一律不建列最多在视图里计算。2.2 用ER图定候选键与基数多对多联系是拆表的起点实体、属性找齐后下一步是确定实体之间的基数。基数决定了联系在关系模式中到底是变成一张表还是变成一个外键。这里用选课场景演示。学生和课程之间是多对多关系一个学生选多门课程一门课程可以被多个学生选。这个联系上挂着“成绩”所以选课不能只靠某一端的主键表达必须独立成一张表。课程和教师之间是一对多关系一位教师教多门课程每门课程有一位教师。这种联系不需要建“授课表”直接在课程表里加一列教师号即可。这时需要把候选键也定下来。学生表用学号做主键课程表用课程号教师表用工号。这些是需求里明确给出的唯一标识。选课联系的主键通常由学生号和课程号组合而成。这里有一个很不容易在第一次就发现的坑如果同一名学生一学期选了同一门课两次比如重修那么学生号课程号就不唯一了。如果你的实验需求里有“补考”“重修”这类词组合主键就不够用需要引入一个选课流水号或者把“学期”并进主键。如果需求没提直接用组合键提了一定要提前加选课id否则第二次插入会撞主键那时候再来后悔改写的东西比先想到多得多。基数判断还带来一个大家默认但很少明说的好处多对多联系实体化之后就能把“成绩”这类联系属性安全地挂上去。如果强行把多对多简化成一对多比如在课程表里加一列“学生号”一门课程有多个学生时只能拆成多列或者存逗号分隔的字符串这两种都是反模式。前者让表结构随数据量变化后者让查询没法走索引。所以看到“一个X对应多个Y”不要急着在Y表加外键先确认反过来是不是也成立。2.3 把ER图转成关系模式三条规则加上一条检查拿到ER图转关系模式有常见的三条规则。规则一每个实体转成一张表实体的属性转成表的列实体的主键转成表的主键。规则二一对多联系转成“一”端主键并入“多”端作为外键。这里的“授课”联系就并入课程表课程表加一列授课教师号。规则三多对多联系必须独立成表表中至少包含两端实体的主键作为外键组合成主键如果联系本身有属性一起放进表里。选课联系就按这个规则转成选课表表中包含学生号、课程号、成绩。三条规则之外我还会加一条检查能不能在不出错的情况下重建原来的多条多对多关系。比如从选课表出发查询“某个学生选了什么课”通过student_id过滤就能拿到课程号查询“某门课有哪些学生”通过course_id过滤也能拿到学生号。这说明选课表完整承载了多对多联系。反过来如果某张表里存着一个逗号分隔的ID串无论怎么设计索引都说明转换漏了一步。转换过程中还有两个容易出错的细节。第一联系属性放进联系表后不要忘记这个属性的出现次数跟着联系走。比如“成绩”是每次选课一个值不是每个学生一个值。第二一对多端的外键列要允许NULL吗如果一门课程允许暂未安排教师外键列就要设为NULL并把外键约束写成ON DELETE SET NULL。如果一门课程必须有教师外键列要加NOT NULL删除教师时就要阻止删除对应ON DELETE RESTRICT。这个取舍直接影响后面SQL的约束写法所以现在定下来后面少改一次。真正的关系模式到这里应该是四张独立的表student(student_id, name, major)、teacher(teacher_id, name, title)、course(course_id, name, credit, teacher_id)、enrollment(student_id, course_id, score)。到了这一步概念层和逻辑层的设计已经完成。你会发现这四张表并不是一开始就凭空想出来的而是从ER图一步一步推过来的。接下来要做的是用范式规则验证这四张表有没有漏掉的冗余。3. 关系模式规范化避免把实验四写成“一张大表”3.1 第一范式到第三范式什么时候拆表拆到什么粒度规范化听上去玄学其实用一条主线就能串起来检查非主属性对主键的依赖关系。下面用一张反例表展开这张表是很多第一次做模式设计的人常交出来的东西成绩汇总表(学号, 姓名, 课程号, 课程名, 成绩, 教师号, 教师职称)主键是(学号, 课程号)。先看第一范式每一列必须是不可再分的原子值。这里“成绩”列如果存成“优秀/90”这种带两个信息的字符串就违反1NF。这个好修拆成成绩数值用CHECK约束限定范围。再看第二范式非主属性必须完全依赖于主键不能只依赖主键的一部分。姓名只依赖学号课程名只依赖课程号教师职称只依赖教师号它们都构成对(学号, 课程号)的部分依赖所以2NF不满足。拆法是把依赖学号的属性拆到学生表把依赖课程号的属性拆到课程表把依赖教师号的属性拆到教师表。第三范式非主属性对主键不能有传递依赖。接着上面教师职称依赖教师号而教师号又依赖课程号。如果课程号确定后能推出教师号教师职称就和主键隔了一层形成传递依赖。拆法是把教师信息单独放到教师表。拆完之后得到的正是上一章的student、course、teacher、enrollment四张表。所以范式不是新东西它只是验证你ER建模有没有做错的数学化表达。实验里常见的打分点其实就是这三步要求你写出“该表是否满足3NF如果不满足请分解到3NF”。所以报告里最好每一步都留痕给出函数依赖集合、判断候选键、说明部分依赖和传递依赖在哪再给出分解结果。不要只写最终建表语句过程分很重要。很多同学栽在没有中间推导老师一眼看出是背的表结构。3.2 函数依赖分析与候选键判定用手算代替碰运气函数依赖是规范化的数学依据。给定一张表先写清楚依赖关系候选键就能手算出来。以选课场景为例student_id - name, majorcourse_id - name, credit, teacher_idteacher_id - title现在有一张新的成绩表(学号, 课程号, 成绩, 教师号, 教师职称)。观察闭包从(学号, 课程号)出发通过课程号推出教师号再通过教师号推出教师职称所以(学号, 课程号)能推出所有属性是候选键。同时教师职称是非主属性但它的依赖路径是“主键 - 教师号 - 教师职称”属于传递依赖所以该表没到3NF。逐项检查后把教师职称挪到教师表里就消除了传递依赖。候选键判定还有一个常见误区并非所有表都有单列主键。选课表的主键是两列的组合这是合理的。但组合键不能由其中一列单独推出全部列否则当前表不满足2NF。比如如果(课程号)能推出教师号和教师职称那么教师职称就部分依赖于组合键(学号, 课程号)同样要拆出去。判断时不要只看“看起来是否重复”要依赖关系说出来。如果你觉得手算闭包麻烦可以用一个小技巧验证把每一行记录看成一个事实主键的每一列都参与唯一决定一行。凡是“只靠主键的一部分就能确定”的列都是拆表的线索。经验上一张表如果同时出现“学生信息”和“课程信息”大概率存在部分依赖这时直接拆就好不需要把FD集合写满一整页。这个手算能力在实验四里属于基本功到了后面的数据库应用开发你会发现它能直接帮你发现接口返回字段的冗余。3.3 规范化的边界不是拆得越细越好很多同学知道BCNF之后恨不得把所有表都拆到BCNF。比如把“课程号 - 课程名”也拆成课程基础表把“教师号 - 教师名”再拆成个人信息表最后一条查询要join五张表。实验四不是论文不是范式级别越高越好。教学实验的惯例是满足3NF即可除非题目明确要求考BCNF。过度拆分的直接代价是查询语句变复杂如果做系统开发的阶段需要高频查询反而要回头合并。更实际的做法是模式层保持3NF查询层用视图。比如选课成绩查询需要学生姓名、课程名、教师名可以在模式不变的前提下创建视图把join逻辑写进视图里。这样既保住范式得分又给后续开发留了方便。有些资料提“反范式”用来做报表但那是另一个话题实验报告里提一句“为查询性能做适度冗余”可以别在主设计里乱来。规范化的边界要把握一个度如果一张表即使不满足3NF但更新频率极低、读多写少且冗余字段不会因为更新而失控那么保留冗余也说得通。但在课程实验中老师希望看到的是你具备“发现冗余并处理”的能力。所以默认全部走到3NF再在文档里讨论哪里可能有性能问题这样拿分最稳。4. 用SQL把模式落地建表语句、约束与索引4.1 从模式到建表语句先写MySQL版再对比PostgreSQL逻辑模式确定以后落地到SQL是水到渠成的事。下面这个完整的建表脚本可以在MySQL 8.0或MariaDB 10.5以上直接跑覆盖前面四张表CREATE TABLE student ( student_id CHAR(10) PRIMARY KEY, name VARCHAR(50) NOT NULL, major VARCHAR(50) DEFAULT 未定 ); CREATE TABLE teacher ( teacher_id CHAR(8) PRIMARY KEY, name VARCHAR(50) NOT NULL, title VARCHAR(20) ); CREATE TABLE course ( course_id CHAR(6) PRIMARY KEY, name VARCHAR(80) NOT NULL, credit DECIMAL(2,1) CHECK (credit 0), teacher_id CHAR(8), CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id) ON DELETE SET NULL ); CREATE TABLE enrollment ( student_id CHAR(10), course_id CHAR(6), score DECIMAL(5,1), PRIMARY KEY (student_id, course_id), CONSTRAINT fk_enroll_student FOREIGN KEY (student_id) REFERENCES student(student_id) ON DELETE CASCADE, CONSTRAINT fk_enroll_course FOREIGN KEY (course_id) REFERENCES course(course_id) ON DELETE CASCADE, CONSTRAINT chk_enroll_score CHECK (score BETWEEN 0 AND 100) );这段脚本有几个参数要说明CHAR长度是对学号、工号这类定长业务编码的选择不要给学号用VARCHAR再碰运气定长编码用CHAR更省空间。DECIMAL(5,1)表示成绩最多三位整数加一位小数足够覆盖百分制。外键的ON DELETE策略特意选了不同方式删除学生时他所有选课记录一起删除因为能查到学生但查不到选课没有意义删除课程时同样级联删除选课但删除教师时课程保留教师号置空这样历史课程信息不会丢。这组策略是实验里最常被追问的“为什么”照抄也要能解释。PostgreSQL版本大体相同差异只在数据类型和自增写法。如果实验环境是PostgreSQL建议用NUMERIC(2,1)代替DECIMAL用CHAR(10)不变。另外如果主键要用自增整数MySQL写INT AUTO_INCREMENTPostgreSQL写SERIAL或IDENTITY。其余外键和CHECK语法完全一致。两个版本文档里都写一下能体现你理解跨数据库移植。如果用的是DM数据库这类国产库建表语法基本兼容Oracle风格但“模式”命名要留意第五章会专门讲这个坑。4.2 主键、外键、唯一约束与CHECK约束不是一个摆设建表语句写对了模式设计才算真正落地。主键和唯一约束要分清主键一定是非空且唯一但唯一约束可以为空。比如一个学生如果允许没有学号不现实所以学号直接设主键。但“邮箱”这类业务字段可以作为唯一约束允许暂无邮箱的人插入多个空值。在实验模式里通常不需要把唯一约束用得很花哨但至少要保证选课表里同一学生同一课程不能重记录这就是组合主键的功劳。CHECK约束是最容易被忽略的一环。成绩列要限定0到100MySQL 8.0会强制MariaDB某些版本可能忽略但写进去能让实验报告显得专业。CHECK里还有一个容易踩的坑不要写带查询的CHECK子查询标准SQL不支持子查询即使某些数据库允许也会导致表锁定和性能问题。判断一门课是否存在是外键约束该干的事不是CHECK该干的事。外键约束的删除策略也需要按语义选。如果实验要求“删除一门课程时保留学生选课记录但课程信息不可查”那用ON DELETE SET NULL但course_id是联合主键的一部分置空会违反主键非空所以这种情况不能用SET NULL。正确做法是设计一个“课程快照”表但这已经超出普通实验范围。如果遇到这种需求最稳妥的方案是级联删除并在文档里说明为了保持历史成绩完整业务上应做软删除而不是物理删除。这是数据库设计里很现实的取舍。4.3 索引设计外键列和组合键的右边列要单独索引模式设计不仅包含建表还要考虑查询效率。实验四不一定强制建索引但加一两个能体现你想到“物理设计”。学生表和教师表按主键建索引就够了。课程表的teacher_id是外键InnoDB会自动为外键列建索引不用手动加。真正容易被忽略的是enrollment表主键是(student_id, course_id)这意味着以student_id为条件的查询能用到主键索引但如果你经常要查“某门课有哪些人选的”course_id在最左匹配原则下用不上主键索引查询会变成全表扫描。解决方法是单独给course_id建一个索引CREATE INDEX idx_enroll_course ON enrollment(course_id);同理如果业务中高频查询“某课程的平均分”这个索引就够了。如果还要按成绩排序可以再建一个联合索引(course_id, score)但不要建太多。索引会在写入时增加开销实验数据量小看不出优势但在报告里写“为课程维度的查询补充二级索引避免对组合主键右列做全表扫描”这句话就是采分点。还有一点容易被忽略CHAR和VARCHAR的编码排序。默认排序规则下字符串比较区分大小写和不区分大小写会影响“相等”判断。如果学生号中既有数字又有字母建议统一设置成utf8mb4_general_ci避免同一个学号因大小写差异产生两条记录。建表时指定ENGINEInnoDB DEFAULT CHARSETutf8mb4这是MySQL 8.0的默认值但写出来会让脚本更稳。5. 模式设计的五个翻车现场避坑与排查5.1 主键用业务字段学号一改全库跟着遭殃现象用学号作主键某学生转学后学号更换按学号关联的成绩、选课全部要改。如果更新漏改外键直接报错。原因学号是业务编码不是稳定的人工标识它会被行政流程修改。解决如果实验允许给student表加一个自增id做主键学号用UNIQUE约束。如果老师要求必须用学号做主键那至少要在文档里说明“业务键变更时需要级联更新可在应用层做变更日志”。这种“看到问题并提出方案”的写法比闷头照做更容易拿分。5.2 插入顺序引发外键死锁先插子表后插父表现象执行INSERT INTO enrollment(student_id, course_id, score)报错“a foreign key constraint fails”但数据看起来没问题。原因外键检查时被参照的记录在父表中还不存在。解决按依赖顺序插入先student、teacher再course最后enrollment。如果使用批量导入工具或脚本临时关闭外键检查不是好习惯因为会导致脏数据入库后无法开启检查。真正确保顺序的方法是让导入脚本读取依赖关系或者按外键层级分轮提交。实验中遇到这个错先把插入语句一条条按顺序跑能过就是语句顺序问题不是模式问题。5.3 一个字段存一串ID把多对多塞进单列现象课程表里有一列“选课学生”存着“20240001,20240002,20240003”查询某门课选课名单要用LIKE %20240002%且无法引用外键。原因建模阶段把多对多联系误当成一对多省略了关联表。解决这是一张典型的关联表缺失必须拆出enrollment表。如果已经建表用ALTER TABLE加关联表并迁移数据。这个坑在实验报告自检时最容易发现凡是要解析逗号分隔的字段都是反模式。看到自己写了逗号分隔趁早重设计别等报告写完了再后悔。5.4 范式高到没边查询一句join四张表成绩还查不出来现象模式完全满足BCNF每个实体一张表连学生姓名都单独一张表最终查询课程成绩要关联五张表。原因把规范化的目标理解成“拆得越多越好”忘了模式设计要服务查询。解决实验做到3NF就停查询需求用视图封装。这里给出的建议是CREATE VIEW v_course_score AS SELECT e.student_id, s.name AS student_name, c.course_id, c.name AS course_name, t.name AS teacher_name, e.score FROM enrollment e JOIN student s ON e.student_id s.student_id JOIN course c ON e.course_id c.course_id JOIN teacher t ON c.teacher_id t.teacher_id;视图不影响表的范式但在应用层能把复杂join隐藏起来。如果实验报告里写了“由于过度拆分导致查询效率低”老师会认为你理解边界比无脑拆表强。5.5 建表报“模式错误”保留字和Schema命名惹的祸现象在MySQL执行CREATE TABLE order (...)直接报语法错误在达梦数据库里用“mode”做表名也提示“模式错误”。原因order、mode都是保留字或内置关键字表名与数据库模式名Schema名冲突。解决改名是最省事的比如course_order。如果一定要用保留字MySQL用反引号包裹CREATE TABLEorder(...)达梦里需要显式指定模式名比如CREATE TABLE S1.order ...但“模式错误”也可能是当前用户的Schema不存在需要先执行CREATE SCHEMA或切换默认Schema。这个坑还牵出一个概念区分实验四说的“数据库模式设计”指的是关系模式整体设计包括表、视图、约束、索引这些结构的集合而数据库管理工具里报“模式错误”的“模式”是schema一个数据库下有多个schema。两者不是一回事。报告里如果能把“模式”这个词的两个层次说清楚会显得你不是在背概念。5.6 用元数据反向检查提交之前的后悔药除了上面五个坑我还有一个实验前必查的动作用数据库自带的 INFORMATION_SCHEMA 反向检查建表结果。比如想确认外键是否生成SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA test AND REFERENCED_TABLE_NAME IS NOT NULL;如果查出外键没有出现在预期位置说明建表顺序或约束名错了。这个查询只读不改是排错最好的后悔药。还有检查约束SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA test;用这两步能把建表脚本里犯的错提前暴露不用等插入数据时再碰运气。我自己刚做实验四的时候就是靠这条查出了两个漏建的外键那张报告才没在答辩时翻车。6. 模式设计落地后用三组数据和一段视图验证你的设计模式设计值不值得做完最终要看实验结果能不能回答几个问题。我自己的收尾习惯是不急着点“运行”而是先在sql脚本里造三组业务场景数据。第一组正常插入验证主外键链路第二组故意插入超范围成绩验证CHECK约束弹出错误第三组删除一个学生验证级联删除是否连带清掉选课记录。这三组跑完模式的基本行为就锁住了。接着用视图和查询验证设计目标查“每个学生的平均分”时join是否不超过三张表结果有没有重复行查“某门课选课名单”时是否用了course_id索引而不是全表扫描。这些验证结果直接作为实验报告的“测试章节”比把建表语句贴一遍更有说服力。一个我踩过的坑是只在空表上验证忽略了选课表主键冲突的情况。所以现在每次提交前一定会再插入一条主键完全相同的学生和课程记录确认它收到Duplicate entry错误。如果实验允许我还会用EXPLAIN运行一条联表查询看执行计划里有没有出现全表扫描。数据量小的库容易出现这种扫表但看到了就知道哪个索引没加。最后把设计文档整理成“需求 - ER图 - 关系模式 - SQL - 验证”五段式。每一段都留下修改痕迹尤其是ER图阶段出现过的多对多联系一定要保留“为什么拆表”的注释。这样答辩时老师问任何一个“为什么建这张表”你都能从函数依赖推演一遍。这就是我做完北邮数据库实验四后凝成的习惯希望帮到你。本文还有配套的精品资源点击获取 数据库模式设计实战：从ER图到SQL建表与范式避坑
简介面向北邮数据库课程学习者的实验四完整报告围绕在线考试系统的数据库模式设计展开覆盖需求分析、E-R图构建、逻辑模式与物理模式转换以及使用Power Designer完成概念模型到物理模型的映射和SQL脚本生成。报告定义了用户、试题库、知识点、试卷、考试管理五个核心实体及其属性关系并给出在IBM DB2环境中执行脚本创建表与视图的具体步骤包含考试信息、在线试卷两个视图的设计参考。资源为单一doc文档大小约1.56MB内含实验目的、实验环境、实体定义、模型截图及结果分析等完整章节结构清晰便于对照查阅适合正在完成数据库实验、复习E-R图与模式转换知识的同学直接参考。已有126人学习下载可作为同类实验报告撰写或数据库设计入门的对照资料。1. 数据库模式设计北邮实验四到底在考什么北邮数据库实验四-数据库模式的设计说穿了就是让一个人从需求描述开始把现实业务抽象成一张张带约束的表。很多同学把它当成“用SQL建几张表交差”但实验评分真正盯的是ER图是否完整、关系模式是否消除了明显冗余、主外键和约束是不是经得起插入和查询的考验。这篇笔记按常见实验要求走一遍从画ER图到写SQL再到查坑顺便回答“模式设计到底要设计到什么程度”。适合正在做实验的学生也适合刚入职需要独立设计数据库表的开发。照着做你能交出一份能跑、能讲清楚、老师问不倒的作业。2. 从需求到ER图实验四的建模第一步2.1 先别急着建表把需求文本里的实体、属性和联系找出来通常实验四给一段像“学生选修课程每门课程有一位授课教师学生选课后获得成绩”这样的自然语言需求。第一步不是打开数据库客户端而是拿一支笔把句子里的名词圈出来学生、课程、教师、成绩、授课。名词往往是实体名词前的定语往往是属性动词往往是联系。用这个规则不会漏掉核心要素。我一般会先建一张对照表左边放需求原文里的短语右边放模型元素。比如“学生学号、姓名、专业”判断为实体学生属性有学号、姓名、专业“课程课程号、课程名、学分”判断为实体课程“教师工号、姓名、职称”判断为实体教师“一门课程由一位教师教授”判断为联系授课“学生选修课程后取得成绩”判断为联系选课成绩是选课这个联系的属性不是学生或课程的属性。这样一划模式设计的骨架就出来了。这里有一个新手很容易忽略的问题属性到底该挂在实体上还是挂在联系上。上面例子里的“成绩”就必须挂在选课联系上因为一门课程可能被多个学生选修同一个学生也可能选多门课程成绩不是单独某个学生或单独某门课能确定的。如果把成绩挂到课程上就会出现“每个学生的成绩都要往课程里塞”的尴尬挂到学生上一门课只能存一个成绩。判断标准是看这个属性的取值是否需要“实体对”同时成立。需要就放联系。还有一类属性要特别小心就是派生属性。比如“学生所选课程的总学分”它由选课记录和课程学分算出来不是基础属性。实验报告里如果把派生属性也建成列插入时要手动同步更新时又容易漏改属于典型的“自己给自己挖坑”。所以建表前先把需求句子里能通过其他数据计算出来的字段单独列成“派生属性”清单设计模式时一律不建列最多在视图里计算。2.2 用ER图定候选键与基数多对多联系是拆表的起点实体、属性找齐后下一步是确定实体之间的基数。基数决定了联系在关系模式中到底是变成一张表还是变成一个外键。这里用选课场景演示。学生和课程之间是多对多关系一个学生选多门课程一门课程可以被多个学生选。这个联系上挂着“成绩”所以选课不能只靠某一端的主键表达必须独立成一张表。课程和教师之间是一对多关系一位教师教多门课程每门课程有一位教师。这种联系不需要建“授课表”直接在课程表里加一列教师号即可。这时需要把候选键也定下来。学生表用学号做主键课程表用课程号教师表用工号。这些是需求里明确给出的唯一标识。选课联系的主键通常由学生号和课程号组合而成。这里有一个很不容易在第一次就发现的坑如果同一名学生一学期选了同一门课两次比如重修那么学生号课程号就不唯一了。如果你的实验需求里有“补考”“重修”这类词组合主键就不够用需要引入一个选课流水号或者把“学期”并进主键。如果需求没提直接用组合键提了一定要提前加选课id否则第二次插入会撞主键那时候再来后悔改写的东西比先想到多得多。基数判断还带来一个大家默认但很少明说的好处多对多联系实体化之后就能把“成绩”这类联系属性安全地挂上去。如果强行把多对多简化成一对多比如在课程表里加一列“学生号”一门课程有多个学生时只能拆成多列或者存逗号分隔的字符串这两种都是反模式。前者让表结构随数据量变化后者让查询没法走索引。所以看到“一个X对应多个Y”不要急着在Y表加外键先确认反过来是不是也成立。2.3 把ER图转成关系模式三条规则加上一条检查拿到ER图转关系模式有常见的三条规则。规则一每个实体转成一张表实体的属性转成表的列实体的主键转成表的主键。规则二一对多联系转成“一”端主键并入“多”端作为外键。这里的“授课”联系就并入课程表课程表加一列授课教师号。规则三多对多联系必须独立成表表中至少包含两端实体的主键作为外键组合成主键如果联系本身有属性一起放进表里。选课联系就按这个规则转成选课表表中包含学生号、课程号、成绩。三条规则之外我还会加一条检查能不能在不出错的情况下重建原来的多条多对多关系。比如从选课表出发查询“某个学生选了什么课”通过student_id过滤就能拿到课程号查询“某门课有哪些学生”通过course_id过滤也能拿到学生号。这说明选课表完整承载了多对多联系。反过来如果某张表里存着一个逗号分隔的ID串无论怎么设计索引都说明转换漏了一步。转换过程中还有两个容易出错的细节。第一联系属性放进联系表后不要忘记这个属性的出现次数跟着联系走。比如“成绩”是每次选课一个值不是每个学生一个值。第二一对多端的外键列要允许NULL吗如果一门课程允许暂未安排教师外键列就要设为NULL并把外键约束写成ON DELETE SET NULL。如果一门课程必须有教师外键列要加NOT NULL删除教师时就要阻止删除对应ON DELETE RESTRICT。这个取舍直接影响后面SQL的约束写法所以现在定下来后面少改一次。真正的关系模式到这里应该是四张独立的表student(student_id, name, major)、teacher(teacher_id, name, title)、course(course_id, name, credit, teacher_id)、enrollment(student_id, course_id, score)。到了这一步概念层和逻辑层的设计已经完成。你会发现这四张表并不是一开始就凭空想出来的而是从ER图一步一步推过来的。接下来要做的是用范式规则验证这四张表有没有漏掉的冗余。3. 关系模式规范化避免把实验四写成“一张大表”3.1 第一范式到第三范式什么时候拆表拆到什么粒度规范化听上去玄学其实用一条主线就能串起来检查非主属性对主键的依赖关系。下面用一张反例表展开这张表是很多第一次做模式设计的人常交出来的东西成绩汇总表(学号, 姓名, 课程号, 课程名, 成绩, 教师号, 教师职称)主键是(学号, 课程号)。先看第一范式每一列必须是不可再分的原子值。这里“成绩”列如果存成“优秀/90”这种带两个信息的字符串就违反1NF。这个好修拆成成绩数值用CHECK约束限定范围。再看第二范式非主属性必须完全依赖于主键不能只依赖主键的一部分。姓名只依赖学号课程名只依赖课程号教师职称只依赖教师号它们都构成对(学号, 课程号)的部分依赖所以2NF不满足。拆法是把依赖学号的属性拆到学生表把依赖课程号的属性拆到课程表把依赖教师号的属性拆到教师表。第三范式非主属性对主键不能有传递依赖。接着上面教师职称依赖教师号而教师号又依赖课程号。如果课程号确定后能推出教师号教师职称就和主键隔了一层形成传递依赖。拆法是把教师信息单独放到教师表。拆完之后得到的正是上一章的student、course、teacher、enrollment四张表。所以范式不是新东西它只是验证你ER建模有没有做错的数学化表达。实验里常见的打分点其实就是这三步要求你写出“该表是否满足3NF如果不满足请分解到3NF”。所以报告里最好每一步都留痕给出函数依赖集合、判断候选键、说明部分依赖和传递依赖在哪再给出分解结果。不要只写最终建表语句过程分很重要。很多同学栽在没有中间推导老师一眼看出是背的表结构。3.2 函数依赖分析与候选键判定用手算代替碰运气函数依赖是规范化的数学依据。给定一张表先写清楚依赖关系候选键就能手算出来。以选课场景为例student_id - name, majorcourse_id - name, credit, teacher_idteacher_id - title现在有一张新的成绩表(学号, 课程号, 成绩, 教师号, 教师职称)。观察闭包从(学号, 课程号)出发通过课程号推出教师号再通过教师号推出教师职称所以(学号, 课程号)能推出所有属性是候选键。同时教师职称是非主属性但它的依赖路径是“主键 - 教师号 - 教师职称”属于传递依赖所以该表没到3NF。逐项检查后把教师职称挪到教师表里就消除了传递依赖。候选键判定还有一个常见误区并非所有表都有单列主键。选课表的主键是两列的组合这是合理的。但组合键不能由其中一列单独推出全部列否则当前表不满足2NF。比如如果(课程号)能推出教师号和教师职称那么教师职称就部分依赖于组合键(学号, 课程号)同样要拆出去。判断时不要只看“看起来是否重复”要依赖关系说出来。如果你觉得手算闭包麻烦可以用一个小技巧验证把每一行记录看成一个事实主键的每一列都参与唯一决定一行。凡是“只靠主键的一部分就能确定”的列都是拆表的线索。经验上一张表如果同时出现“学生信息”和“课程信息”大概率存在部分依赖这时直接拆就好不需要把FD集合写满一整页。这个手算能力在实验四里属于基本功到了后面的数据库应用开发你会发现它能直接帮你发现接口返回字段的冗余。3.3 规范化的边界不是拆得越细越好很多同学知道BCNF之后恨不得把所有表都拆到BCNF。比如把“课程号 - 课程名”也拆成课程基础表把“教师号 - 教师名”再拆成个人信息表最后一条查询要join五张表。实验四不是论文不是范式级别越高越好。教学实验的惯例是满足3NF即可除非题目明确要求考BCNF。过度拆分的直接代价是查询语句变复杂如果做系统开发的阶段需要高频查询反而要回头合并。更实际的做法是模式层保持3NF查询层用视图。比如选课成绩查询需要学生姓名、课程名、教师名可以在模式不变的前提下创建视图把join逻辑写进视图里。这样既保住范式得分又给后续开发留了方便。有些资料提“反范式”用来做报表但那是另一个话题实验报告里提一句“为查询性能做适度冗余”可以别在主设计里乱来。规范化的边界要把握一个度如果一张表即使不满足3NF但更新频率极低、读多写少且冗余字段不会因为更新而失控那么保留冗余也说得通。但在课程实验中老师希望看到的是你具备“发现冗余并处理”的能力。所以默认全部走到3NF再在文档里讨论哪里可能有性能问题这样拿分最稳。4. 用SQL把模式落地建表语句、约束与索引4.1 从模式到建表语句先写MySQL版再对比PostgreSQL逻辑模式确定以后落地到SQL是水到渠成的事。下面这个完整的建表脚本可以在MySQL 8.0或MariaDB 10.5以上直接跑覆盖前面四张表CREATE TABLE student ( student_id CHAR(10) PRIMARY KEY, name VARCHAR(50) NOT NULL, major VARCHAR(50) DEFAULT 未定 ); CREATE TABLE teacher ( teacher_id CHAR(8) PRIMARY KEY, name VARCHAR(50) NOT NULL, title VARCHAR(20) ); CREATE TABLE course ( course_id CHAR(6) PRIMARY KEY, name VARCHAR(80) NOT NULL, credit DECIMAL(2,1) CHECK (credit 0), teacher_id CHAR(8), CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id) ON DELETE SET NULL ); CREATE TABLE enrollment ( student_id CHAR(10), course_id CHAR(6), score DECIMAL(5,1), PRIMARY KEY (student_id, course_id), CONSTRAINT fk_enroll_student FOREIGN KEY (student_id) REFERENCES student(student_id) ON DELETE CASCADE, CONSTRAINT fk_enroll_course FOREIGN KEY (course_id) REFERENCES course(course_id) ON DELETE CASCADE, CONSTRAINT chk_enroll_score CHECK (score BETWEEN 0 AND 100) );这段脚本有几个参数要说明CHAR长度是对学号、工号这类定长业务编码的选择不要给学号用VARCHAR再碰运气定长编码用CHAR更省空间。DECIMAL(5,1)表示成绩最多三位整数加一位小数足够覆盖百分制。外键的ON DELETE策略特意选了不同方式删除学生时他所有选课记录一起删除因为能查到学生但查不到选课没有意义删除课程时同样级联删除选课但删除教师时课程保留教师号置空这样历史课程信息不会丢。这组策略是实验里最常被追问的“为什么”照抄也要能解释。PostgreSQL版本大体相同差异只在数据类型和自增写法。如果实验环境是PostgreSQL建议用NUMERIC(2,1)代替DECIMAL用CHAR(10)不变。另外如果主键要用自增整数MySQL写INT AUTO_INCREMENTPostgreSQL写SERIAL或IDENTITY。其余外键和CHECK语法完全一致。两个版本文档里都写一下能体现你理解跨数据库移植。如果用的是DM数据库这类国产库建表语法基本兼容Oracle风格但“模式”命名要留意第五章会专门讲这个坑。4.2 主键、外键、唯一约束与CHECK约束不是一个摆设建表语句写对了模式设计才算真正落地。主键和唯一约束要分清主键一定是非空且唯一但唯一约束可以为空。比如一个学生如果允许没有学号不现实所以学号直接设主键。但“邮箱”这类业务字段可以作为唯一约束允许暂无邮箱的人插入多个空值。在实验模式里通常不需要把唯一约束用得很花哨但至少要保证选课表里同一学生同一课程不能重记录这就是组合主键的功劳。CHECK约束是最容易被忽略的一环。成绩列要限定0到100MySQL 8.0会强制MariaDB某些版本可能忽略但写进去能让实验报告显得专业。CHECK里还有一个容易踩的坑不要写带查询的CHECK子查询标准SQL不支持子查询即使某些数据库允许也会导致表锁定和性能问题。判断一门课是否存在是外键约束该干的事不是CHECK该干的事。外键约束的删除策略也需要按语义选。如果实验要求“删除一门课程时保留学生选课记录但课程信息不可查”那用ON DELETE SET NULL但course_id是联合主键的一部分置空会违反主键非空所以这种情况不能用SET NULL。正确做法是设计一个“课程快照”表但这已经超出普通实验范围。如果遇到这种需求最稳妥的方案是级联删除并在文档里说明为了保持历史成绩完整业务上应做软删除而不是物理删除。这是数据库设计里很现实的取舍。4.3 索引设计外键列和组合键的右边列要单独索引模式设计不仅包含建表还要考虑查询效率。实验四不一定强制建索引但加一两个能体现你想到“物理设计”。学生表和教师表按主键建索引就够了。课程表的teacher_id是外键InnoDB会自动为外键列建索引不用手动加。真正容易被忽略的是enrollment表主键是(student_id, course_id)这意味着以student_id为条件的查询能用到主键索引但如果你经常要查“某门课有哪些人选的”course_id在最左匹配原则下用不上主键索引查询会变成全表扫描。解决方法是单独给course_id建一个索引CREATE INDEX idx_enroll_course ON enrollment(course_id);同理如果业务中高频查询“某课程的平均分”这个索引就够了。如果还要按成绩排序可以再建一个联合索引(course_id, score)但不要建太多。索引会在写入时增加开销实验数据量小看不出优势但在报告里写“为课程维度的查询补充二级索引避免对组合主键右列做全表扫描”这句话就是采分点。还有一点容易被忽略CHAR和VARCHAR的编码排序。默认排序规则下字符串比较区分大小写和不区分大小写会影响“相等”判断。如果学生号中既有数字又有字母建议统一设置成utf8mb4_general_ci避免同一个学号因大小写差异产生两条记录。建表时指定ENGINEInnoDB DEFAULT CHARSETutf8mb4这是MySQL 8.0的默认值但写出来会让脚本更稳。5. 模式设计的五个翻车现场避坑与排查5.1 主键用业务字段学号一改全库跟着遭殃现象用学号作主键某学生转学后学号更换按学号关联的成绩、选课全部要改。如果更新漏改外键直接报错。原因学号是业务编码不是稳定的人工标识它会被行政流程修改。解决如果实验允许给student表加一个自增id做主键学号用UNIQUE约束。如果老师要求必须用学号做主键那至少要在文档里说明“业务键变更时需要级联更新可在应用层做变更日志”。这种“看到问题并提出方案”的写法比闷头照做更容易拿分。5.2 插入顺序引发外键死锁先插子表后插父表现象执行INSERT INTO enrollment(student_id, course_id, score)报错“a foreign key constraint fails”但数据看起来没问题。原因外键检查时被参照的记录在父表中还不存在。解决按依赖顺序插入先student、teacher再course最后enrollment。如果使用批量导入工具或脚本临时关闭外键检查不是好习惯因为会导致脏数据入库后无法开启检查。真正确保顺序的方法是让导入脚本读取依赖关系或者按外键层级分轮提交。实验中遇到这个错先把插入语句一条条按顺序跑能过就是语句顺序问题不是模式问题。5.3 一个字段存一串ID把多对多塞进单列现象课程表里有一列“选课学生”存着“20240001,20240002,20240003”查询某门课选课名单要用LIKE %20240002%且无法引用外键。原因建模阶段把多对多联系误当成一对多省略了关联表。解决这是一张典型的关联表缺失必须拆出enrollment表。如果已经建表用ALTER TABLE加关联表并迁移数据。这个坑在实验报告自检时最容易发现凡是要解析逗号分隔的字段都是反模式。看到自己写了逗号分隔趁早重设计别等报告写完了再后悔。5.4 范式高到没边查询一句join四张表成绩还查不出来现象模式完全满足BCNF每个实体一张表连学生姓名都单独一张表最终查询课程成绩要关联五张表。原因把规范化的目标理解成“拆得越多越好”忘了模式设计要服务查询。解决实验做到3NF就停查询需求用视图封装。这里给出的建议是CREATE VIEW v_course_score AS SELECT e.student_id, s.name AS student_name, c.course_id, c.name AS course_name, t.name AS teacher_name, e.score FROM enrollment e JOIN student s ON e.student_id s.student_id JOIN course c ON e.course_id c.course_id JOIN teacher t ON c.teacher_id t.teacher_id;视图不影响表的范式但在应用层能把复杂join隐藏起来。如果实验报告里写了“由于过度拆分导致查询效率低”老师会认为你理解边界比无脑拆表强。5.5 建表报“模式错误”保留字和Schema命名惹的祸现象在MySQL执行CREATE TABLE order (...)直接报语法错误在达梦数据库里用“mode”做表名也提示“模式错误”。原因order、mode都是保留字或内置关键字表名与数据库模式名Schema名冲突。解决改名是最省事的比如course_order。如果一定要用保留字MySQL用反引号包裹CREATE TABLEorder(...)达梦里需要显式指定模式名比如CREATE TABLE S1.order ...但“模式错误”也可能是当前用户的Schema不存在需要先执行CREATE SCHEMA或切换默认Schema。这个坑还牵出一个概念区分实验四说的“数据库模式设计”指的是关系模式整体设计包括表、视图、约束、索引这些结构的集合而数据库管理工具里报“模式错误”的“模式”是schema一个数据库下有多个schema。两者不是一回事。报告里如果能把“模式”这个词的两个层次说清楚会显得你不是在背概念。5.6 用元数据反向检查提交之前的后悔药除了上面五个坑我还有一个实验前必查的动作用数据库自带的 INFORMATION_SCHEMA 反向检查建表结果。比如想确认外键是否生成SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA test AND REFERENCED_TABLE_NAME IS NOT NULL;如果查出外键没有出现在预期位置说明建表顺序或约束名错了。这个查询只读不改是排错最好的后悔药。还有检查约束SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA test;用这两步能把建表脚本里犯的错提前暴露不用等插入数据时再碰运气。我自己刚做实验四的时候就是靠这条查出了两个漏建的外键那张报告才没在答辩时翻车。6. 模式设计落地后用三组数据和一段视图验证你的设计模式设计值不值得做完最终要看实验结果能不能回答几个问题。我自己的收尾习惯是不急着点“运行”而是先在sql脚本里造三组业务场景数据。第一组正常插入验证主外键链路第二组故意插入超范围成绩验证CHECK约束弹出错误第三组删除一个学生验证级联删除是否连带清掉选课记录。这三组跑完模式的基本行为就锁住了。接着用视图和查询验证设计目标查“每个学生的平均分”时join是否不超过三张表结果有没有重复行查“某门课选课名单”时是否用了course_id索引而不是全表扫描。这些验证结果直接作为实验报告的“测试章节”比把建表语句贴一遍更有说服力。一个我踩过的坑是只在空表上验证忽略了选课表主键冲突的情况。所以现在每次提交前一定会再插入一条主键完全相同的学生和课程记录确认它收到Duplicate entry错误。如果实验允许我还会用EXPLAIN运行一条联表查询看执行计划里有没有出现全表扫描。数据量小的库容易出现这种扫表但看到了就知道哪个索引没加。最后把设计文档整理成“需求 - ER图 - 关系模式 - SQL - 验证”五段式。每一段都留下修改痕迹尤其是ER图阶段出现过的多对多联系一定要保留“为什么拆表”的注释。这样答辩时老师问任何一个“为什么建这张表”你都能从函数依赖推演一遍。这就是我做完北邮数据库实验四后凝成的习惯希望帮到你。本文还有配套的精品资源点击获取