数据库模式设计：ER模型转关系模式，范式检查与SQL建表指南

简介北邮数据库实验四完整报告围绕数据库模式设计展开面向学习数据库课程及需要完成类似实验的在校生。内容以在线考试系统为业务背景详细讲解从需求中抽象出用户、试题库、知识点、试卷、考试管理五个实体的方法并通过 E-R 图转物理模型、生成 SQL 脚本等步骤展示 Power Designer 与 DB2 v8.1 的完整实践流程。报告明确了各实体关键字段如用户ID、知识点代码、题目代码等还分别给出考试信息视图和在线试卷视图的设计便于理解虚拟表在实际考试场景中的应用。资源包内包含 1 个 docx 文档大小约 1.56MB文档中既有实验目的、环境与步骤说明也有属性定义、视图设计思路及可导入验证的 SQL 语句方便对照检查表与视图是否创建成功。该文档已有 145 人学习适合作为实验报告撰写、数据库建模练习或考前复习的参考资料。1. 数据库模式的设计北邮实验四到底在练什么北邮数据库实验四的题目通常是一段业务描述要求据此完成数据库模式的设计。所谓数据库模式就是先于任何SQL语句存在的骨架哪些业务对象要建表、表与表靠什么字段关联、什么样的组合能保证数据不冗余、不产生更新异常。这一步没做好后面写再漂亮的增删改查都是空中楼阁。这篇笔记整理的是一套能直接照做的流程——从需求里抽ER模型、把ER转成关系模式、按范式做检查最后用CREATE TABLE验证。适合正在做数据库课程设计的在校生也适合平时只写业务SQL、没系统做过模式设计的从业者。2. 从需求文本到ER模型实体、属性与联系怎么抽才不返工2.1 实体和属性怎么划分用三个问题过滤需求里的名词ER模型是整个模式设计的源头实体和属性的划分在这里出错后面关系模式、范式的所有工作都会跟着错。实体的定义是需要被独立描述的业务对象它有自己的属性集合、会参与业务联系、在查询里会被单独引用属性则是依附于实体的细节去掉它不影响其他对象的描述方式。判断方法不靠直觉靠三个问题这个名词在需求里有自己的属性集合吗它会和其他对象发生联系吗业务上需要单独按它查询吗三个问题里至少有两个答案是是才保留为实体否则降级为普通属性。以一段典型的学生选课需求为例文本里会出现学生、学号、姓名、班级、课程、课程号、课程名、学分、教师、教师工号、职称、选课时间、成绩。这里姓名课程名没有自己的属性也不参与联系直接降为属性班级则要看题目有没有给额外的字段——如果需求里明确写了班主任、班级人数、教室编号它就具备三个问题里的前两个必须升级为实体如果题目只提了一句学生属于某个班级那作为学生表的一个属性字段也能接受。实际动手时我习惯先在草稿纸上把所有名词列出来做三轮过滤第一轮删掉修饰词和纯状态词第二轮删掉明显是属性的词第三轮对剩下的词逐个跑三个问题。纸质过滤比直接在电脑上画图快因为删改方便。等实体清单稳定下来再打开工具画ER图。2.2 联系类型怎么判断双向问一遍基数再决定外键策略联系是实体之间的业务关系关系模式最终靠联系决定外键放哪、要不要中间表。联系类型判断错了外键落点和中间表设计会全线出错。标准方法是双向问基数从A往B问1个A最多对应几个B从B往A问1个B最多对应几个A把两个答案合成基数对。以学生和课程为例1个学生最多选多门课1门课最多被多个学生选得到(n, m)这是多对多联系必须建中间表。学生和班级1个学生属于1个班级1个班级有多个学生得到(1, n)这是一对多联系外键放在n侧也就是学生表里存class_id。这里有个容易混淆的地方课程和教师的关系要严格按题目描述来。有的题目是一门课由一位教师负责一位教师负责多门课这是(1, n)有的题目是一门课由多位教师合上一位教师上多门课这就是(m, n)。不要凭现实经验猜题目怎么说就怎么建实验报告里判断依据最好写一句方便老师核对你的分析过程。下面这个表格是联系类型与关系模式落地的对应关系可以贴进实验报告联系类型双向基数关系模式落地最容易犯的错1:1(1,1)外键放任一侧加UNIQUE约束把两张表误合并成一张1:n(1,n)外键放在n侧外键放到1侧导致重复值爆炸m:n(n,m)必须建中间表图省事省掉中间表直接复制信息2.3 一个贯穿全文的案例学生选课系统的ER模型为了后面每一步都有参照用一个最常见的实验场景做贯穿案例一个简单教务系统学生选课学生属于班级课程由教师授课。需要记录学生基础信息、班级信息、课程信息、教师信息以及选课记录含成绩和选课时间。先列实体清单。学生学号、姓名、班级编号、入学年份班级班级编号、班级名称、班主任课程课程号、课程名、学分、开课院系教师教师工号、姓名、职称、所属院系。主键分别取学号、班级编号、课程号、教师工号这些字段在业务里天然唯一且稳定。再列联系清单。学生—班级是(1, n)每名学生属于一个班级每个班级有多名学生教师—课程是(1, n)每位教师负责多门课每门课由一位教师负责学生—课程是(m, n)联系属性有成绩和选课时间。ER图建议按这个顺序画先画四个实体框再连三条联系线并标注基数最后挂属性。属性很多时不需要把全部字段都画在图上只要画出主键和联系属性其余字段单独整理成清单否则图会乱到没法提交。ER图在实验四报告里属于设计过程的核心产出画清楚一点后面转换关系模式时自己看着也省力。3. 把ER模型转成关系模式转换规则、外键落点与命名取舍3.1 实体转表、属性转字段主键别选会变的业务字段ER模型到关系模式的转换有一条基本规则每个实体变成一张表每个属性变成一个字段ER模型里定的主键直接继承为表的主键。规则本身一句话就能说完真正需要决策的是主键选型。主键选型的优先级我一般这样排第一业务里天生唯一且稳定的字段比如学号、课程号直接用第二没有这种字段时加一个自增代理键id第三绝对不要用可空的、会变化的、带业务语义的字段当主键比如手机号、邮箱、身份证号。原因不难理解主键是外键的引用目标一旦业务上手机号换了所有引用这张表的子表记录都要跟着级联更新。实验四的数据量小看不出性能问题但老师只要看主键设计就能判断你有没有考虑过数据生命周期。学生表在案例里直接用学号做主键因为教务场景里学号永久不变如果题目给的业务对象没有天然主键就用自增id并在报告里写清楚选代理键是因为无稳定自然键这是基本功的体现。3.2 1:n、m:n、1:1三类联系的外键落点与中间表设计一对多联系的处理是外键放在n侧。学生和班级之间是(1, n)所以在student表里加class_id字段作为外键。这样每名学生只需要存一个班级编号反过来做的话班级表里要存一个学生ID列表关系模型要求字段是原子的直接违反第一范式。多对多联系必须建中间表。学生和课程是(m, n)建一张enrollment表包含student_id、course_id、成绩、选课时间。中间表的主键有两种做法联合主键(student_id, course_id)或者自增代理键id。实验四我推荐联合主键因为它的语义清晰天然保证一个学生对同一门课只能有一条选课记录后面做删除和去重查询也能直接利用这个主键。一对一联系的处理最省事外键放到任意一侧并给外键加UNIQUE约束。实验四的题目里1:1联系出现得不多但如果出现了判断依据是谁访问更频繁就把外键放谁那边。下面是三类联系的转换速查表联系类型外键位置中间表约束要求1:1任一侧不要外键列加UNIQUE1:nn侧不要普通外键m:n无外键要建中间表主键建议联合主键3.3 属性合并与命名规范联系属性和组合属性的处理属性落到关系模式时最常出错的是联系属性。案例里的成绩和选课时间只在某个学生选了某门课这个语境下才有意义所以它们必须放中间表不能放学生表也不能放课程表。判断方法很直白这个属性是由单个实体决定的还是由两个实体的组合决定的成绩由哪个学生选了哪门课共同决定依赖(学号, 课程号)这个复合键放中间表如果放进学生表一个学生选多门课就得扩展多列新开课程还得改表结构这就是典型的把联系属性挂错位置。组合属性怎么处理取决于查询是否需要按子字段筛选。比如地址在需求里如果只是做个展示字段保持一个字符串即可如果题目要求按城市统计学生分布就必须拆成省、市、区三个字段。判断标准不是范式而是查询场景实验四不要为了看起来规范把每个字段都拆到底。命名规范也在这个阶段定下来。表名用单数形式比如student而不是students字段名用蛇形course_name这种外键字段统一用目标表名_主键名命名例如student表里的class_id一看就知道关联的是class表。中间表的命名建议用能表达业务语义的英文词案例里叫enrollment比student_course通用也比a_b这种缩写好读。统一命名吃亏少的是后面第6章的SQL验证join条件基本不用猜。4. 用范式检查模式设计函数依赖、部分依赖与传递依赖的判定4.1 函数依赖集合从题目描述里把规则翻译成X→Y范式检查不是玄学它的分析语言是函数依赖。函数依赖的定义是如果X→Y那么任意两行数据在X上取值相同在Y上也必然相同。口语说就是知道X的值就能唯一确定Y的值。提取函数依赖的方法是从需求描述里找每个……有唯一的……这类句子。比如每个学生有唯一的学号和姓名翻译成学号→姓名、学号→姓名所在的其他属性每门课程有唯一的课程号和学分翻译成课程号→课程名、课程号→学分。把整段需求过一遍得到函数依赖集合F。案例的依赖集合写出来是这样-- 业务规则翻译成函数依赖 学号 → 姓名, 班级编号 课程号 → 课程名, 学分 班级编号 → 班主任 (学号, 课程号) → 成绩, 选课时间在这个集合基础上还能推导出学号→班主任因为学号→班级编号、班级编号→班主任这是一个传递依赖真实存在后面3NF要处理。这里有个边界函数依赖只来自题目明示的规则不要自行脑补。比如一个学生只能选一门课如果题目没说就不能写学号→课程号。这一步做扎实范式的判定和分解才有依据实验报告的数据依赖分析部分也能直接引用。4.2 2NF判定与分解消除对复合主键的部分依赖2NF要求在1NF基础上所有非主属性完全依赖于候选键。说细一点如果表的主键是复合键(X, Y)那么任何非主属性都不能只依赖X或只依赖Y否则就是部分依赖违反2NF。最常见的反例是设计者把选课记录做成一张宽表选课记录学号, 课程号, 姓名, 班级编号, 课程名, 学分, 成绩主键是(学号, 课程号)。这里姓名只依赖学号、课程名只依赖课程号都属于部分依赖这个表不满足2NF。违反2NF的后果用增删改查一测就能暴露。插入异常新开了一门课还没人选因为没有学号课程信息插不进这张表更新异常某门课学分变了必须把所有选了这门课的行的学分都改掉更新语句漏一个条件数据就不一致删除异常学生退课后这门课的课程名和学分也跟着被删了。这些问题不是SQL写错是模式设计错了属于典型的模式设计翻车了SQL再规范也没用。分解的做法是把部分依赖的属性连同它依赖的那部分主键一起拆出去。宽表拆成三张student学号, 姓名, 班级编号course课程号, 课程名, 学分enrollment学号, 课程号, 成绩分解完要验证信息不丢失原来宽表能回答的问题这三张表通过join都能回答只是把重复存储去掉了。拆表不丢信息、只丢冗余这是范式分解的判定标准。4.3 3NF判定与分解顺着传递依赖把表拆到位3NF要求在2NF基础上消除非主属性对候选键的传递依赖。传递依赖长这样X→YY→Z而Y不包含候选键。案例里student表如果这样设计-- 违反3NF的设计班主任经班级编号传递依赖主键 student (学号, 姓名, 班级编号, 班主任)这里学号→班级编号班级编号→班主任班级编号不是候选键存在传递依赖违反3NF。后果在更新时最直观班主任换了这个班级所有学生的记录要一起改漏掉一行就是数据不一致而且一个班级还没有任何学生时班主任信息根本没地方存——插入异常又出现了。分解方法是把中间依赖项和它决定的目标拆成新表student学号, 姓名, 班级编号class班级编号, 班主任enrollment不变拆完后再检查一遍函数依赖集合每个依赖都能在原表里找到归属没有丢失依赖。边界提醒3NF之后不用再往BCNF走。BCNF要求所有决定因素都是候选键在实验四的题量下强行BCNF会产生大量小表查询一次学生列表要join五六张表反而得不偿失。模式设计是冗余与查询代价的权衡实验四做到3NF就够报告里写清楚这一步的取舍反而显得更懂行。5. 避坑模式设计里最常见的5个翻车现场与排查方法5.1 主键选了会变的业务字段一台手机号引发的级联更新现象学生表用手机号当主键某学生换了手机号后所有关联表的程序开始报外键错误数据改不动。 原因选主键时只看了唯一性没看稳定性。手机号天然可变换号这个动作在业务上很常见用它当主键等于把所有引用它的外键都绑在一次级联更新上。 解决ER阶段就圈定主键候选集优先选永久不变的字段。业务字段必须唯一时作为普通字段加UNIQUE约束就可以不承担主键职责。实验报告里写明主键选择理由能直接拉开和敷衍作业的差距。5.2 m:n联系没有中间表课程信息跟着选课记录反复复制现象选课表里把课程名、学分直接复制进来一个学生选一次课就复制一遍。课程学分改了历史和新增数据完全对不上。 原因设计者觉得中间表多余想把实体信息和联系信息塞在一张表里最后得到一堆冗余行。 解决m:n联系必须建中间表。中间表只保留两个外键和联系属性课程名、学分永远存在course表里查询时用join取回来。检查一张表设计是否合理可以问如果删掉一行冗余数据业务信息会不会丢中间表的行删掉后课程信息还在说明边界清晰。5.3 联系属性挂错位置成绩被塞进学生表的典型后果现象学生表设计了成绩1、成绩2、成绩3三列学生多选一门课就要加列选课数量不固定时只能预留空列。 原因把联系属性当成了实体属性。成绩不是学生固有属性也不是课程固有属性而是学生选课这个行为产生的结果由两个实体共同决定。 解决归属判断用一句话这个字段由单个实体决定还是由实体组合决定成绩由(学号, 课程号)决定放中间表。选课时间同理。记住这个判断方式以后遇到订购日期预约时段这类字段都不会再放错地方。5.4 过度追求范式join六张表的优雅查询没人愿意用现象为了消灭所有传递依赖把学生表按属性拆成student_base、student_contact、student_academic查一次学生列表要join三张表业务代码又臭又长。 原因把范式当成了模式设计的目标忘了范式只是消除冗余的手段。实验四的题量做3NF已经足够强行BCNF反而让查询复杂度和维护成本涨上去。 解决3NF止步。若某个冗余字段能让高频查询少一次join保留它并接受可控的一致性风险在报告里说明保留该字段是为了查询性能这个理由比盲目拆表更体现工程判断。要注意的是这种权衡必须写出来不写就会被认为是没发现依赖。5.5 外键不加索引并发插入时的锁等待与死锁现象程序并发运行时偶发阻塞错误日志里出现死锁信息重试几次又能过。 原因MySQL的InnoDB引擎不会自动给外键列建索引。删除父表数据时它需要扫描子表全表来确认有没有引用锁范围被放大两个事务交叉删除不同表的父记录时互相等待就直接死锁。 解决建表时对外键列显式加INDEX删除顺序严格先清理子表引用再删父表记录。这个坑在实验四的单机小数据量下不致命但在并发业务里是典型隐患。实验报告里补一句外键列加索引以避免锁范围过大老师会认为你考虑过工程落地而非只交作业。6. 用SQL把模式落地CREATE TABLE建表与规范化自检6.1 完整的CREATE TABLE把分解出的四张表写出来前面第4章分解出的三张表加上class表就是案例最终的关系模式。建表SQL如下CREATE TABLE class ( class_id INT PRIMARY KEY, class_name VARCHAR(50) NOT NULL, head_teacher VARCHAR(20) ); CREATE TABLE student ( student_id INT PRIMARY KEY, student_name VARCHAR(20) NOT NULL, class_id INT, CONSTRAINT fk_student_class FOREIGN KEY (class_id) REFERENCES class(class_id), INDEX idx_student_class (class_id) ); CREATE TABLE course ( course_id INT PRIMARY KEY, course_name VARCHAR(50) NOT NULL, credit DECIMAL(3,1) ); CREATE TABLE enrollment ( student_id INT, course_id INT, score DECIMAL(5,1), select_time DATETIME, PRIMARY KEY (student_id, course_id), CONSTRAINT fk_enroll_student FOREIGN KEY (student_id) REFERENCES student(student_id), CONSTRAINT fk_enroll_course FOREIGN KEY (course_id) REFERENCES course(course_id), INDEX idx_enroll_course (course_id) );这段SQL的要点enrollment用联合主键体现一个学生同一门课只能选一次的业务规则student表的class_id外键体现1:n联系外键列显式加了索引对应第5章的并发坑约束命名带fk_前缀方便后续排查外键问题。把这张建表脚本和ER图、函数依赖集合放在一起就是完整的实验四报告内容。6.2 用查询SQL做规范化自检冗余还在不在、约束灵不灵建完表不等于设计完成要做两项自检。第一项是冗余检查查询选课记录时学生姓名应该通过join从student表取不能出现在enrollment表里。-- 检查冗余enrollment 表里如果出现 student_name说明设计回退了 SELECT sc.student_id, s.student_name, COUNT(*) AS course_count FROM enrollment sc LEFT JOIN student s ON s.student_id sc.student_id GROUP BY sc.student_id, s.student_name;第二项是约束验证插入一条重复选课记录应该被联合主键拦截删除一个还有学生的班级应该被外键约束阻止。这两条验证通过说明1:n和m:n的约束设计真正生效不是画在纸上好看。我在这个环节还会执行一次第4章的依赖集合核对拿题目原文逐条对照确认没有漏掉业务规则。说实话我当年做类似实验四的作业时也是拿宽表直接交被老师追问课程学分改了你要更新多少行才意识到范式检查不是形式主义。后来每次建表前先把函数依赖写在纸上成了一种习惯——毕竟改动一张草稿上的表比迁移线上数据便宜太多。希望这套从ER到SQL的流程能帮你把实验四一次跑通也让数据库模式的设计从玄学变成能复盘的工程方法。本文还有配套的精品资源点击获取