简介这份文档面向北京邮电大学数据库课程的学习者聚焦实验四“数据库模式的设计”帮助读者完整走通从需求分析、E-R图构建到逻辑模式、物理模式转换再到建表建视图的全流程。资源包共1个doc文件约1.56MB内容为实验报告形式涵盖实验目的、内容、环境、步骤与结果分析可直接对照在线考试系统案例进行练习。实验以用户管理、试题管理、试卷管理和考试管理四大功能为主线涉及用户、试题库、知识点、试卷、考试管理等实体及其属性与关系并借助Power Designer完成概念模型到物理模型的转换最终导出SQL脚本在IBM DB2 v8.1中执行创建考试信息、在线试卷等视图。目前已有126人学习适合正在完成数据库实验、需要理解E-R图与SQL建表建视图操作的学生参考也可作为复习数据库设计流程的实操材料。1. 北邮数据库实验四从一份 .doc 需求到可落地的数据库模式很多人拿到「北邮数据库实验四-数据库模式的设计.doc」这个标题第一反应是去搜一份现成的 E-R 图或者建表 SQL 交差。但真正做过这套实验的人都知道实验四的核心不是画图而是把一份用自然语言写成的需求文档翻译成一套能跑、能约束、能扩展的关系模式。需求里往往只写了「一个学生可以选多门课」「一个老师可以教多门课」这类业务描述剩下的实体识别、联系判定、范式拆解、主外键设计全得自己补。这篇笔记就按我当年踩坑的顺序把从需求到 Power Designer 建模、再到 SQL 建表验证的完整链路拆开讲。适合正在做数据库实验、课程设计或者第一次接手「给需求文档做模式设计」这类任务的读者。热搜里常出现的数据库模式设计、Power Designer、E-R 图、SQL、DB2 这几个词基本就是这条链路上的关键节点下面逐个落到操作层面。2. 需求文档怎么读实体、属性、联系的识别套路2.1 从需求句子到候选实体需求文档里的句子通常分三类名词性描述、动作性描述、约束性描述。名词性描述往往对应实体或属性动作性描述对应联系约束性描述对应基数。我一般会先把文档通读一遍把所有名词圈出来然后做一次「能不能独立存在」的判断。比如「学生」「课程」「教师」「班级」能独立存在就是实体「学号」「姓名」「学分」依附于实体就是属性。这一步最容易翻车的地方是把「选课」当成实体其实它是一个多对多联系除非需求里明确要求记录选课时间、成绩之外还要记录选课批次、选课操作员那才需要提升为实体。判断实体还有一个实用标准如果某个名词需要被单独管理、有生命周期、会被其他表引用就建实体如果它只是描述另一个对象的特征就做属性。比如「学院」在多数实验需求里是实体因为学生、教师都要归属学院但如果需求只写「学生所在系」那系名可以直接做学生表的属性。这个取舍直接决定后面表数量和范式级别。2.2 联系类型与基数的判定联系类型分 1:1、1:N、M:N 三种判定依据是业务规则里的量词。看到「一个……多个……」就是 1:N看到「多个……多个……」就是 M:N。M:N 联系在关系模式里必须拆成独立的关系表这是实验四最常考的转换点。比如「学生选修课程」是 M:N就要建一张选课表主键是学号加课程号的组合。1:N 联系可以把外键放在 N 端比如「班级包含学生」学生表里放班级号做外键。1:1 联系比较少见一般把外键放在访问频率低或者可选的一侧。基数还要注意「部分参与」和「全部参与」。如果需求写「每个学生必须属于一个班级」那学生表里班级号不能为空如果写「教师可以属于一个教研室也可以暂时没有」那外键就允许为空。这个细节在 Power Designer 里用强制/非强制参与表示建表时对应 NOT NULL 和 NULL。2.3 用 Power Designer 画第一版 E-R 图Power Designer 建概念模型的操作路径是File → New Model → Conceptual Data Model然后从工具面板拖 Entity 和 Relationship。实体里加属性时要勾选主标识符Primary Identifier这对应后面的主键。联系上要设置 Cardinality比如 One-to-Many 或者 Many-to-Many。画完之后用 Check Model 做一次语法检查常见报错是实体没有主标识符、联系两端基数矛盾。操作步骤Power Designer 概念模型 1. 新建 Conceptual Data Model命名如 Exp4_CDM 2. 拖入 Entity双击改名在 Attributes 页加属性 3. 选中主键属性勾选 PPrimary Identifier 4. 拖入 Relationship双击设置两端 Cardinality 5. 菜单 Model → Check Model修复所有 Error 6. 菜单 Tools → Generate Physical Data Model选 DBMS 类型这段步骤里最关键的是第 3 步和第 6 步。主标识符没设生成物理模型时就没有主键DBMS 类型选错后面生成的 SQL 方言会对不上。实验环境如果要求 DB2就在 Generate Physical Data Model 时选 DB2如果只是本地验证选 MySQL 或 SQL Server 都行但要注意自增列、数据类型名称的差异。3. 从 E-R 图到关系模式转换规则与范式检查3.1 实体和联系的转换规则E-R 图转关系模式有一套固定规则每个实体转一张表实体的属性转列主标识符转主键1:1 联系可以把任一端主键放到另一端做外键1:N 联系把 1 端主键放到 N 端做外键M:N 联系单独建表主键是两端主键的组合。这套规则在实验四里必须能默写出来因为答辩或者报告里一定会问「你为什么这么拆」。举个例子需求里有「学生」「课程」「教师」「选课」「授课」五个概念。学生和课程是 M:N转成选课表教师和课程也是 M:N转成授课表学生和班级是 1:N学生表加班级号外键。转换完之后关系模式大致是学生学号姓名班级号、班级班级号班级名、课程课程号课程名学分、教师教师号姓名、选课学号课程号成绩、授课教师号课程号。这套模式已经满足 3NF因为每个非主属性都完全函数依赖于主键没有传递依赖。3.2 范式检查的实操方法范式检查不是背定义而是拿函数依赖去套。第一步找候选键第二步列出所有函数依赖第三步看有没有部分依赖和传递依赖。部分依赖出现在组合主键的表里比如选课表主键是学号课程号如果里面放了课程名课程名只依赖于课程号就是部分依赖要拆出去。传递依赖出现在非主属性决定非主属性比如学生表里有班级号班级号决定班级名如果班级名也放在学生表就是传递依赖要拆出班级表。-- 检查部分依赖的示例选课表里混入了课程名 -- 反例 CREATE TABLE sc_bad ( sno CHAR(10), cno CHAR(10), cname VARCHAR(40), grade DECIMAL(5,1), PRIMARY KEY (sno, cno) ); -- cname 只依赖于 cno属于部分依赖应拆出课程表 -- 正例 CREATE TABLE course ( cno CHAR(10) PRIMARY KEY, cname VARCHAR(40) NOT NULL, credit DECIMAL(3,1) ); CREATE TABLE sc ( sno CHAR(10), cno CHAR(10), grade DECIMAL(5,1), PRIMARY KEY (sno, cno), FOREIGN KEY (sno) REFERENCES student(sno), FOREIGN KEY (cno) REFERENCES course(cno) );上面这段 SQL 里反例表 sc_bad 的问题不是语法错误而是设计错误。它能在数据库里建成功但插入数据后会冗余更新课程名时要改多行删除最后一门选课记录时课程信息也丢了。正例把课程名放回课程表选课表只保留成绩既满足 3NF也避免了更新异常。参数上注意 grade 用 DECIMAL(5,1) 而不是 FLOAT避免浮点误差外键约束要显式写出否则数据库不会帮你检查参照完整性。3.3 主键、外键与约束的落地写法主键选择有两个原则业务无关的代理键优先组合主键在联系表里用。学生表用学号做主键没问题因为学号本身稳定但如果需求里学号会变就加一个自增 id 做代理键。外键要明确 ON DELETE 和 ON UPDATE 行为实验里一般用 RESTRICT 或 NO ACTION防止误删父表数据。唯一约束用在业务上要求不重复的列比如课程名、教师姓名但要注意现实里同名是存在的所以更稳妥的是对「课程名学分」或者直接不加唯一约束靠应用层判断。CREATE TABLE student ( sno CHAR(10) PRIMARY KEY, sname VARCHAR(20) NOT NULL, class_no CHAR(8), FOREIGN KEY (class_no) REFERENCES class(class_no) ON DELETE SET NULL ON UPDATE CASCADE );这段建表语句里ON DELETE SET NULL 表示班级删除后学生记录的班级号置空适合「学生可以暂时没有班级」的场景如果需求要求学生必须有班级就改成 ON DELETE RESTRICT。ON UPDATE CASCADE 表示班级号变更时学生表自动跟着改适合班级号会调整的场景。这两个参数没有绝对对错取决于需求文档里的约束描述。4. 建表与验证把模式设计落到 SQL 并跑通4.1 生成物理模型与导出 SQLPower Designer 生成物理模型后可以直接导出建表脚本。操作路径是 Database → Generate Database选择目录和文件名勾选 Generate CREATE TABLE、Generate FOREIGN KEY 等选项。导出的脚本里会包含表、主键、外键、索引但通常不含测试数据。我一般会先手工检查一遍脚本重点看数据类型是否合理、字符集是否统一、外键顺序是否满足依赖关系。DB2 和 MySQL 在数据类型上有差异比如 DB2 用 VARCHAR 而不是 VARCHAR2自增列 DB2 用 GENERATED ALWAYS AS IDENTITYMySQL 用 AUTO_INCREMENT导出时选对 DBMS 能省很多手工改的时间。# 以 MySQL 为例导入建表脚本 mysql -u root -p exp4 exp4_schema.sql # 查看表结构 mysql -u root -p exp4 -e SHOW TABLES; DESC student; # 查看外键约束 mysql -u root -p exp4 -e SELECT TABLE_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMAexp4 AND REFERENCED_TABLE_NAME IS NOT NULL;这三条命令分别完成导入、看表结构、看外键。导入时如果报「Cannot add foreign key constraint」通常是父表还没建或者列类型不匹配。DESC 看字段类型和是否允许为空和设计文档对照。第三条查 information_schema 能列出所有外键关系用来验证 M:N 联系是否真的拆成了独立表。4.2 插入测试数据验证约束建完表必须插数据验证否则约束写没写对根本不知道。测试数据要覆盖正常插入、外键违反、主键重复、非空违反四种情况。正常插入验证表能用外键违反验证参照完整性生效主键重复验证唯一性非空违反验证 NOT NULL。这四类测试做完模式设计的基本正确性就有保障了。-- 正常插入 INSERT INTO class VALUES (C001, 计算机1班); INSERT INTO student VALUES (2021001, 张三, C001); INSERT INTO course VALUES (CS101, 数据库, 3.0); INSERT INTO sc VALUES (2021001, CS101, 88.5); -- 外键违反应报错 INSERT INTO student VALUES (2021002, 李四, C999); -- 主键重复应报错 INSERT INTO class VALUES (C001, 计算机2班); -- 非空违反应报错 INSERT INTO student VALUES (2021003, NULL, C001);这四条语句里第一条应该成功后三条应该分别报外键、主键、非空错误。如果后三条里有任何一条成功了说明约束没生效要回去检查建表语句。常见原因是外键列类型和父表主键类型不一致比如父表 CHAR(8)子表 VARCHAR(8)MySQL 在某些版本下会静默忽略外键。4.3 用查询验证模式是否满足需求约束验证完还要用查询验证业务需求。需求里写「查询每个学生的选课门数和平均分」就要能写出对应的 GROUP BY 语句写「查询没有选课的学生」就要能用 LEFT JOIN 加 IS NULL。如果某个需求写不出查询或者写出来特别别扭往往说明模式设计有问题比如该拆的表没拆或者该加的联系表没加。-- 每个学生的选课门数和平均分 SELECT s.sno, s.sname, COUNT(sc.cno) AS cnt, AVG(sc.grade) AS avg_grade FROM student s LEFT JOIN sc ON s.sno sc.sno GROUP BY s.sno, s.sname; -- 没有选课的学生 SELECT s.sno, s.sname FROM student s LEFT JOIN sc ON s.sno sc.sno WHERE sc.sno IS NULL;第一条查询用 LEFT JOIN 保证没选课的学生也出现COUNT 统计选课门数AVG 算平均分。第二条用 LEFT JOIN 加 IS NULL 找没选课的学生。如果模式里选课信息散落在学生表里这两条查询就会很难写甚至写不出来。这也是检验模式设计的一个实用标准需求里的查询越容易写模式越合理。5. 避坑与排查实验四最常见的五个翻车点5.1 把 M:N 联系塞进一张表导致数据冗余现象选课表里同时存了学号、课程号、课程名、学分插入多条选课记录后课程名重复出现。原因没有把 M:N 联系拆成独立表或者拆了但把课程属性留在了联系表里。解决课程名、学分放回课程表选课表只保留学号、课程号、成绩主键用组合键。5.2 外键列类型和父表主键不一致现象建表时外键约束报错或者建成功了但插入非法数据不报错。原因父表主键是 CHAR(10)子表外键写成 VARCHAR(10)字符集或排序规则也不同。解决用 SHOW CREATE TABLE 对比两列定义统一成完全相同的类型、字符集、排序规则。5.3 范式过度拆分导致查询性能差现象为了满足 3NF 甚至 BCNF把很多属性拆到独立表结果一个简单查询要 JOIN 五六张表。原因范式是设计参考不是越高越好实验里满足 3NF 即可实际项目还要考虑查询频率。解决对高频查询涉及的少量冗余可以接受但要在报告里说明理由比如「为减少 JOIN 次数在选课表冗余课程名通过触发器保持同步」。5.4 Power Designer 生成 SQL 时 DBMS 选错现象导出的脚本在 DB2 里跑报语法错误比如自增列写法不对、数据类型不存在。原因Generate Physical Data Model 时 DBMS 选了 MySQL但实验环境要求 DB2。解决重新生成物理模型DBMS 选 DB2或者手工改数据类型和自增语法。DB2 的 IDENTITY 列和 MySQL 的 AUTO_INCREMENT 不能混用。5.5 忽略 NULL 和默认值的业务含义现象学生表班级号允许为空但需求写「每个学生必须属于一个班级」导致插入无班级学生成功。原因建表时没加 NOT NULL或者外键列没设强制参与。解决对照需求文档逐条检查约束必须参与的列加 NOT NULL可选参与的不加并在报告里写明依据。6. 进阶技巧用脚本自动检查模式设计是否达标手工检查容易漏我后来习惯写一个 Python 脚本连上数据库后自动查三件事每张表有没有主键、每个外键有没有索引、有没有表没有外键关联。这三项能覆盖实验四大部分评分点。脚本用 information_schema 查元数据不依赖具体 DBMS 的业务表换数据库也能跑。import pymysql conn pymysql.connect(hostlocalhost, userroot, password, databaseexp4) cur conn.cursor() # 检查没有主键的表 cur.execute( SELECT t.TABLE_NAME FROM information_schema.TABLES t LEFT JOIN information_schema.TABLE_CONSTRAINTS c ON t.TABLE_NAME c.TABLE_NAME AND c.CONSTRAINT_TYPE PRIMARY KEY WHERE t.TABLE_SCHEMA exp4 AND c.CONSTRAINT_NAME IS NULL ) print(无主键的表:, cur.fetchall()) # 检查外键列是否有索引 cur.execute( SELECT TABLE_NAME, COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA exp4 AND REFERENCED_TABLE_NAME IS NOT NULL ) for table, col in cur.fetchall(): cur.execute(fSHOW INDEX FROM {table} WHERE Column_name {col}) if not cur.fetchall(): print(f外键无索引: {table}.{col}) cur.close() conn.close()这段脚本里第一条查询找没有主键的表第二条遍历所有外键列并检查是否有索引。参数上把 database 和 TABLE_SCHEMA 换成自己的库名即可。跑完输出为空说明主键和外键索引都齐了。这个脚本我一般放在建表之后、写报告之前跑一次比人工逐张表看快得多。还有一个习惯每改一版模式就把建表脚本、测试数据、验证查询一起存成一个版本目录命名带日期。实验四往往要改好几轮没有版本管理改到最后自己都忘了哪版是对的。这个习惯看起来笨但能省下大量「后悔药」时间。希望帮到你。本文还有配套的精品资源点击获取