简介这是一份面向北邮数据库课程实验四的完整实验报告文档内容围绕在线考试系统的数据库模式设计展开。文档从需求分析入手梳理用户、试题、知识点、试卷、考试等核心实体及其属性关系逐步完成E-R图构建、概念模型转换、Power Designer物理建模并生成可在IBM DB2中执行的SQL脚本最终落地为表与视图。全文以实验报告体例呈现既包含目标与实验环境也给出实体属性定义、模型示意图和脚本验证分析适合正在完成同类实验的本科生结合Power Designer实操参考。资源为1份doc文档压缩包大小1.56MB内容覆盖E-R图、概念模型、物理模型、SQL脚本生成与视图创建等关键环节。目前已有126人学习下载可用作数据库设计流程与工具使用的对照资料。1. 数据库模式设计实验为什么总是卡在范式上北邮数据库实验四这门课的作业名字叫“数据库模式的设计”实际上绝大部分分数都压在两张表上一张是E-R图一张是规范化的关系模式表。很多同学把前三个实验的增删改查写得顺风顺水到了这一轮却反复被退回原因不在SQL语法而在函数依赖没理清。模式设计是整个数据库课程里最接近“理论决定工程”的环节范式判定错了后面的建表、索引、应用代码全是白做。这门实验适合两类人一类是正在被课程作业卡住、需要把范式判定写成可验证步骤的学生另一类是工作后回头看觉得“当初范式背了但没真懂”的开发者。模式设计不要求你写多复杂的SQL核心考察是你能不能从需求描述里提炼出属性、主键、外键和依赖关系并把它们转化为符合第三范式3NF或BCNF的关系模式。这个能力在真实业务建模里同样管用毕竟任何一张业务表的字段取舍本质都是模式设计。2. 从函数依赖出发模式设计的第一步是找对依赖关系2.1 函数依赖的形式化定义与实验中的常见误区函数依赖是关系模式设计的起点形式化定义是在关系R中属性集X的每个取值都唯一对应属性集Y的一个取值记作X→Y。实验报告里最常见的错误是把“业务上的对应关系”直接当成函数依赖比如“一个学生属于一个班级”写成学号→班级这本身没问题但漏掉了“一个班级有多个学生”这种多对一关系对依赖方向的限制。判断函数依赖一定要回到语义上问一句给定X的值Y的值是否唯一确定。实验题目给的原始需求往往是自然语言描述比如“每个学生可选多门课程每门课程可被多个学生选修选修记录包含成绩”。这 段描述里成绩的依赖是学号课程号→成绩而不是学号→成绩因为单个学号对应了多门课程的成绩。这个区分是整套模式设计的分水岭后面所有范式判定都建立在这条依赖链的准确性上。完整定义看设关系模式R(U)U为属性集合X、Y为U的子集如果对于R的任意两个元组t1、t2当t1[X]t2[X]时必有t1[Y]t2[Y]则称X函数确定Y即X→Y。从函数依赖集F出发还可以通过Armstrong公理推导出额外的依赖比如自反律、增广律、传递律。实验报告里不需要写推导过程但判定传递依赖时必须用传递律这就是后面3NF判定的理论基础。2.2 部分依赖与传递依赖两个最容易被扣分的点部分依赖的定义是若X→Y为完全函数依赖当且仅当X的任何真子集都不能决定Y若X的真子集X满足X→Y则称Y部分依赖于X。在“学生选课”表学号课程号成绩系别中系别依赖于学号而学号是复合主键学号课程号的真子集所以系别对主键是部分函数依赖。这种情况只出现在复合主键的表里单属性主键的表天然不会产生部分依赖这个特征可以用来快速排查。传递依赖的定义是若X→YY→Z且Y不包含XZ不属于Y则Z传递依赖于X。经典的例子是“学生表”学号系别系主任学号→系别系别→系主任系主任不直接依赖学号这就是传递依赖。实验里最隐蔽的传递依赖出现 在冗余属性上比如在订单表里同时保存了客户编号和客户名称如果客户编号→客户名称那么客户名称就是传递依赖。实操时建议把所有依赖写成一张清单格式为“字段A→字段B理由…”。这步不用代码但务必在报告的“函数依赖分析”一节里完整列出老师批改时首先看的就是这个清单的完备性。依赖找不全范式判定必然出错而且出错后很难回查。2.3 用SQL逆向验证函数依赖实验报告中容易被忽略的实操环节纯理论分析容易漏判好在可以写几段SQL去数据库里实测。假设实验数据已经导入要验证“课程号→课程名”是否成立可以跑-- 验证课程号到课程名是否为函数依赖 SELECT course_id, COUNT(DISTINCT course_name) AS name_count FROM course GROUP BY course_id HAVING COUNT(DISTINCT course_name) 1;这段SQL把课程号分组统计每组内课程名的去重数量。如果查询结果为空说明每个课程号只对应一个课程名函数依赖成立如果查出数据说明一个课程号对应多个课程名这条依赖不成立。判定函数依赖的本质就是“同一X值是否一定对应同一Y值”在数据层面用分组计数即可验证。验证部分依赖时需要把复合主键分组。例如要验证“学号→系别”是否为部分依赖可以构造student_id, course_id的复合键检查同一student_id下是否出现多个系别-- 验证学号对系别的决定关系用于排查部分依赖 SELECT student_id, COUNT(DISTINCT dept_name) AS dept_count FROM student_course GROUP BY student_id HAVING COUNT(DISTINCT dept_name) 1;如果这个查询无结果说明学号能决定系别而学号是复合主键学号课程号的真子集即部分函数依赖存在。注意这个验证的成立前提是student_course表已经存在且包含系别字段实际实验中系别通常冗余在选课表里正好用它做判断。3. 范式判定的可执行流程从1NF到BCNF的逐级检查3.1 范式判定的顺序与实验报告的标准写法范式判定是一套逐级检查流程先保证1NF再消除部分依赖达到2NF再消除传递依赖达到3NF最后处理所有依赖的决定因素包含候选键达到BCNF。实验报告里常常只写“该模式达到第三范式”缺乏推导过程这是被扣分的主要原因。标准写法应该分三步第一步列出候选键第二步列出每个非主属性对候选键的依赖类型第三步给出范式级别结论。以“学生选课”模式为例候选键是学号课程号。非主属性有成绩、系别。成绩对候选键是完全依赖学号课程号→成绩且单个学号或课程号不能决定成绩。系别对候选键是部分依赖学号→系别。因为存在部分依赖该模式不是2NF。分解办法是拆成“学生表”学号系别和“选课表”学号课程号成绩两个模式。判定结论应该写成“存在非主属性系别对候选键的部分函数依赖不满足2NF需分解”。传递依赖的判定逻辑类似。比如“学生表”学号系别系主任候选键是学号。非主属性系别对学号完全依赖系主任对学号完全依赖但系主任同时依赖于系别即学号→系别→系主任构成传递依赖不满足3NF。分解方案是拆成“学生表”学号系别和“系别表”系别系主任。3.2 候选键的求法属性闭包计算的可操作步骤范式判定绕不开候选键的确定。候选键的定义是能唯一决定所有属性的最小属性集。实验中最常用的求法是属性闭包法给定属性集X反复用函数依赖集F中的依赖扩展X直到X不再变化得到的集合就是X的闭包X⁺。如果X⁺等于关系模式的全部属性UX就是超键去掉冗余属性后得到候选键。举一个实验常用的例子。设关系模式R(A, B, C, D, E)函数依赖集F{A→B, B→C, CD→E, E→A}。求A的闭包A→B得ABB→C得ABC此时没有依赖的左侧包含ABC以外的属性A⁺{A,B,C}不等于UA不是超键。求CD的闭包CD→E得CDEE→A得ACDEA→B得ABCDECD⁺UCD是超键。进一步检查C→D→均无法推出U故CD是候选键。这个计算过程建议在报告的“候选键分析”一节写清楚格式为“设属性集X…根据F推导X⁺…因X⁺U故X为超键去除任一属性后闭包不再等于U故为候选键”。不要只写结论关键是展示推导路径这也给老师一个按步骤给分的抓手。对于更复杂的依赖集可以借助无损分解与依赖保持的概念验证分解正确性。无损分解的判定用Chase算法依赖保持则检查分解后的函数依赖并集是否蕴含原依赖集。实验报告不必写Chase过程但要在分解后检查“原依赖是否都能由分解后的模式推出”。3.3 范式判定辅助表把依赖关系转成可检查的矩阵在实验报告里画一个“属性依赖分析表”能大幅提高可读性日常自己做判定也可以用这张表来检查遗漏。候选键非主属性依赖类型是否满足2NF是否满足3NF分解去向(学号, 课程号)成绩完全依赖是是选课表(学号, 课程号, 成绩)(学号, 课程号)系别部分依赖否否学生表(学号, 系别)学号系主任传递依赖是否系别表(系别, 系主任)这张表的要点有三条第一依赖类型列必须写明“完全依赖/部分依赖/传递依赖/直接依赖”不能只写“依赖”第二2NF列只对复合主键的模式有意义单主键模式默认满足2NF但要在报告里注明原因第三分解去向列的方案必须保证分解后满足“无损连接”和“依赖保持”两个性质否则还会继续出现问题。无损连接的直接验证办法是看分解后的模式是否有公共属性且公共属性是某个分解模式的超键。“学生表”和“选课表”的公共属性是学号学号是学生表的键满足无损连接。依赖保持则检查原依赖集F中的每条依赖是否都能在分解后的某个模式中被函数依赖集推导出来学号→系别在学生表中可直接推出学号课程号→成绩在选课表中可直接推出满足依赖保持。这两个性质是下一步分解实现的前提很多同学分解后没检查导致后续插入异常或数据冗余。4. 从E-R图到关系模式的转换实验四真正的动手环节4.1 实体与联系的转换规则及属性处理E-R图转关系模式有固定的规则每个实体转换为一个关系模式实体的属性成为关系的属性实体的码成为关系的码。联系则根据类型不同处理。1:1联系可以并入任一端实体的关系模式加上另一端的码作为外键1:n联系将1端的码加入n端实体对应的关系模式作为外键m:n联系产生独立的关系模式属性为两端实体的码加上联系自身的属性。实验题目里通常会给几个实体和联系比如学生、课程、教师、系。转换时容易出现一个矛盾实体属性与联系属性互相重叠。比较合理的处理顺序是先把各实体转成关系模式再逐个处理联系最后合并重叠字段。合并规则是同一字段名出现在多个关系模式中时要保证类型、语义一致避免同名字段不同含义。4.2 多对多联系的属性归属选课表的成绩字段该放哪m:n联系是实验四里最容易出问题的地方。以学生和课程为例二者是m:n联系联系属性为成绩。正确转换是生成独立的关系模式“选课表”学号课程号成绩主键为学号课程号学号和课程号同时作为外键引用学生表和课程表。有的同学想把成绩放到学生表里做成学号姓名课程号成绩这样会导致一个学生有多条记录学生基本信息被大量复制产生冗余和更新异常。正确的建表SQL如下-- 学生表 CREATE TABLE student ( student_id VARCHAR(20) PRIMARY KEY, student_name VARCHAR(50) NOT NULL, dept_name VARCHAR(50) ); -- 课程表 CREATE TABLE course ( course_id VARCHAR(20) PRIMARY KEY, course_name VARCHAR(100) NOT NULL, credit NUMERIC(3,1) ); -- 选课表多对多联系的独立关系模式 CREATE TABLE sc ( student_id VARCHAR(20) NOT NULL, course_id VARCHAR(20) NOT NULL, score NUMERIC(5,2), PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES student(student_id), FOREIGN KEY (course_id) REFERENCES course(course_id) );选课表的关键设计点有三处PRIMARY KEY (student_id, course_id)体现了m:n联系的联合主键FOREIGN KEY两个外键保证引用完整性score作为联系自身的属性落在这个独立表上。实验报告里要写出这条SQL并注释说明“该表由m:n联系转换而来主键为两端实体码的组合”。如果联系是1:n比如“系”对“学生”则不需要独立建表直接在“学生表”里加dept_name字段并建立外键指向“系表”。这个区分写进报告的“联系转换说明”一节比只贴SQL得分更高。4.3 分步转换的代码实现先建实体表再建联系表实际建库时建议按“实体表→联系表”的顺序执行。先建student表和course表再建sc表避免外键引用不存在的表导致建表失败。分为三步的操作步骤是第一步定义实体表。只包含实体自身属性和主键外键留到联系转换时再补充。这一步的目的在于让每个实体独立成型避免后续思考联系时干扰主键设定。第二步定义联系表。对于m:n联系新建一张表两个外键字段联合作为主键附加联系属性。对于1:n联系在n端实体表里增加外键列。第三步用ALTER TABLE为1:n联系补充外键。比如学生表里有dept_name字段用如下SQL补充外键约束ALTER TABLE student ADD CONSTRAINT fk_student_dept FOREIGN KEY (dept_name) REFERENCES department(dept_name);这里需要注意一个细节department表的dept_name必须是主键或唯一约束否则外键无法建立。有些同学在department表里把dept_name设为普通字段就直接当作被引用列结果报了“there is no unique constraint matching given keys for referenced table”的错误。这是实验环境里高频出现的报错之一遇到时先检查被引用列的约束状态。5. 模式分解与规范化BCNF实战与异常处理5.1 一个完整的BCNF分解案例满足3NF的模式不一定满足BCNF。BCNF要求在关系模式R中对于F中每个非平凡函数依赖X→YX都包含候选键。一个经典的反例是关系模式学生课程教师语义约定每个教师只教一门课每门课由多个教师教一个学生选修某门课就对应一个固定教师。函数依赖集为{教师→课程, (学生, 课程)→教师}。候选键分析(学生, 课程)能决定教师(学生, 教师)也能决定课程。所以候选键有两个(学生, 课程)和(学生, 教师)。非主属性为空所以不存在非主属性对候选键的部分或传递依赖满足3NF。但看函数依赖教师→课程教师的集合不包含任何候选键因此不满足BCNF。分解过程将教师→课程抽出来将依赖左侧教师加上右侧课程作为新关系模式R1(教师, 课程)原模式中保留候选键和教师属性得到R2(学生, 教师)。NATURAL JOIN后能还原原数据证明无损。这个案例拆解成SQL建表为-- 拆分后的教师授课表 CREATE TABLE teacher_course ( teacher_id VARCHAR(20) PRIMARY KEY, course_id VARCHAR(20) NOT NULL ); -- 拆分后的选课教师表 CREATE TABLE student_teacher ( student_id VARCHAR(20) NOT NULL, teacher_id VARCHAR(20) NOT NULL, PRIMARY KEY (student_id, teacher_id), FOREIGN KEY (teacher_id) REFERENCES teacher_course(teacher_id) );这个案例说明了一个实际判断方法检查每个函数依赖的左侧是否为超键。把所有非平凡函数依赖列出来逐一检查左侧是否包含候选键只要有任意一个不满足就需要继续分解。5.2 3NF合成算法与BCNF分解的选择时机做完分解后应该用3NF合成算法验证一下结果是否已经是3NF。合成算法步骤是对函数依赖集F先求最小覆盖再将同一依赖左侧的分组每组生成一个关系模式最后检查候选键是否出现在某个模式中若没有则单独加一个候选键作为模式。实验报告不需要完整跑一遍合成算法但要有这个意识否则可能自己都不知道分解出来的模式到底处于哪个范式。实际选型上3NF和BCNF的选择是有成本的。BCNF完全消除了冗余但可能破坏依赖保持。比如上面那个例子分解后原依赖(学生, 课程)→教师需要连接两个表才能验证不再被单表直接保持。如果业务写多读少且查询路径固定选BCNF合理如果表经常联合查询3NF通常够用且查询更直接。实验四的评分标准一般只要求到3NF能到BCNF并说明代价可以拿加分。5.3 规范化后的异常验证用SQL检查插入与更新问题规范化之前未分解的选课表会出现三类问题插入异常一个新学生还没选课就无法插入因为主键学号课程号不能为NULL删除异常删除某学生的所有选课记录会把学生基本信息连带删掉更新异常修改学生系别时需要更新该生的所有选课记录漏更新就会出现数据不一致。用SQL去验证异常是比较有说服力的实验过程。假设在未规范化的sc_info表学号姓名系别课程号成绩中执行-- 验证插入异常新学生无选课记录系别等基础信息无法写入 INSERT INTO sc_info (student_id, student_name, dept_name, course_id, score) VALUES (20240001, 张三, 计算机学院, NULL, NULL);这条SQL在未规范化的表里会直接失败因为course_id是主键的一部分不允许为NULL。这个失败本身就是插入异常的实证。规范化拆分后可以先把张三插入student表再在选课时插入sc表两个操作互不阻塞。把这个对照实验写进报告能很好地说明规范化的实际收益。6. 模式设计验证的加分技巧外键检查与查询路径分析6.1 用一条SQL验证所有外键约束的有效性模式设计完建表后第一件事是验证外键逻辑是否闭合。一个实用技巧是通过系统表查询外键关系SELECT tc.table_name AS child_table, kcu.column_name AS child_column, ccu.table_name AS parent_table, ccu.column_name AS parent_column FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name kcu.constraint_name JOIN information_schema.constraint_column_usage ccu ON tc.constraint_name ccu.constraint_name WHERE tc.constraint_type FOREIGN KEY ORDER BY tc.table_name;这段SQL会列出数据库中所有外键约束以及它们关联的子表列与父表列。打开结果后人工检查两点一是每一个外键的父表列必须是主键或唯一键二是外键的引用方向不能成环否则删除父表记录时会被约束阻挡。这个查询在实验报告里写上会是个加分项它也验证了模式是否闭合。6.2 查询路径分析验证分解后的模式能否支撑典型查询规范化分解之后要重新验证原先在未规范表上能跑的查询在分解后的多张表上是否依然高效。比如查询“每个学生的姓名和平均成绩”分解后需要一个连接查询SELECT s.student_name, ROUND(AVG(sc.score), 2) AS avg_score FROM student s JOIN sc ON s.student_id sc.student_id GROUP BY s.student_name;分析这条查询的路径student_id作为连接字段在student表是主键在sc表是联合主键的第一列两个表都走索引连接成本可控。如果查询经常按系别筛选取课情况建议在sc表的student_id上单独加一个索引避免每次join都做全表扫描。让查询路径更清晰的做法是建表后立即执行EXPLAIN检查执行计划EXPLAIN SELECT s.student_name, sc.course_id, sc.score FROM student s JOIN sc ON s.student_id sc.student_id WHERE s.dept_name 计算机学院;观察输出中是否出现“Seq Scan”或“Full Table Scan”。如果student表因为筛选dept_name走了全表扫描就为dept_name加一个普通索引。这一步体现的是模式设计对物理设计的约束作用实验报告里写清楚“加索引的理由是查询条件涉及非主键列”比单纯说“为了性能”更有说服力。6.3 实验报告里最容易被忽略的一个检查点最后提供一条容易被忽略的自检技巧对每个非主属性逐一询问“它是否只依赖于整个主键且不依赖于主键之外的其他属性”。如果答案全是“是”模式就是3NF如果某个非主属性依赖于另一个非主属性则是传递依赖。把每个字段这一问的答案写进报告的附录表导师可以用最快速度判断你的规范化深度是否达标。模式设计实验的评分往往落在这一层。本文还有配套的精品资源点击获取