简介北邮数据库实验四——数据库模式的设计是一份面向北京邮电大学数据库课程本科生的实验报告文档聚焦数据库模式设计与建模全流程。文档内容覆盖在线考试系统的需求分析、E-R图设计、Power Designer概念模型与物理模型转换、SQL脚本生成及DB2中表和视图的实现与验证尤其详细记录了用户、试题库、知识点、试卷、考试管理五个实体的属性定义以及考试信息视图和在线试卷视图的创建语句适合正在完成同类实验或复习数据库建模的同学参考。资源包共1个docx文件大小1.56MB实际为完整实验报告包含实验目的、环境、步骤、结果截图与SQL导出内容可帮助读者对照检查自己的设计思路。该资源已有145人学习作为北邮数据库实验的配套文档能提供具象化的操作示例与排错参考便于快速掌握E-R图到物理模型的转换方法。1. 数据库模式设计考试系统的E-R图为什么不能省很多做过在线考试系统的人第一反应是直接建 user、exam、paper、item 四张表等联调时才发现“试卷由哪些题组成”“一个学生的一次考试成绩放在哪里”都说不清。这类问题的根子在数据库模式设计阶段没把实体与关系理清。北邮数据库实验四正好把它拆成一条可验证的链路先根据需求抽 E-R 图再用 PowerDesigner 转成物理模型最后导出 SQL 脚本到 DB2 v8.1 执行并核对视图。这个过程的受众很明确一类是被课程设计卡在表结构上的学生另一类是实际项目里需要把“需求描述”转成可评审表结构的工程师。下面按这条链路把实体取舍、PowerDesigner 参数和 DB2 执行细节讲透。2. E-R图整理五个实体与关系之间如何取舍在线考试系统的需求描述里藏着三个关键约束每个用户只有一种角色一道题只能考察一个知识点相同知识点的题在同一份试卷里不能出现第二次。前两条直接决定实体边界第三条决定试卷和试题之间不是简单的一对多而是要按知识点去重。整理 E-R 图时我一般先把动词找出来“输入试题”“生成试卷”“参加考试”“提交答案”“查看历史成绩”每个动词两边各是一个实体动词本身决定关系类型。2.1 先按职责划分实体边界需求已经写明用户、知识点、题库、试卷、考试五件事但建表前还要把每个实体的属性检查一遍避免把“显示字段”和“业务字段”混在一起。实验给出的属性清单可以直接转成下面的关系模式骨架实体主键核心属性外键用户UserIDUserName, Role, Password无知识点PointIDPcontent, Psubject无试题库ItemIDIcontent, Iscore, Ioption, IanswerPointID → 知识点试卷PaperIDPaperNameItemID → 试题库考试管理EIDEname, Etime, Egrade(可空)UserID → 用户注意考试成绩 Egrade 在 E-R 图阶段被定义为可空这个字段不能用 0 填充。学生还没提交答案时成绩不存在如果填 0后面统计平均分、及格率时会把未考试的学生一起算进去属于典型的“用业务值代替空值”错误。PowerDesigner 的概念模型里把这个属性勾成 Mandatory 之外的 Optional对应的物理表列才允许 NULL。2.1.1 用户和角色拆两张表还是一张表常见的错误是把老师、学生拆成 Teacher 和 Student 两张实体表然后用“用户类型”字段去判断。一旦需求允许“教师也能参加考试”或“学生也能录入题目”这种设计就得同时改表结构和查询语句。需求原文规定“一个用户有且只有一种角色”这正是类别属性的典型场景保留一个 Role 字段即可查询时用WHERE Role S或WHERE Role T过滤。PowerDesigner 中不需要为角色单独建实体只给 User 实体加一个值域约束物理模型里生成 CHECK 约束就够了。2.2 关系怎么挂外键放左边还是右边五个实体本身好建难的是实体之间的关系。把关系模式写出来最有说服力用户(UserID, UserName, Role, Password) 知识点(PointID, Pcontent, Psubject) 试题库(ItemID, Icontent, Iscore, Ioption, Ianswer, PointID) 试卷(PaperID, PaperName) -- 课程模型里再挂 ItemID见下方分析 考试管理(EID, Ename, Etime, Egrade, UserID, PaperID)知识点和试题的关系是一道题只能考一个知识点所以知识点是“一”方试题是“多”方PointID 挂在试题库里作为外键不需要再建“试题-知识点”中间表。用户和考试的关系同理一个用户可以有多次考试记录UserID 挂在考试管理表上就能表达“这个成绩属于哪个学生”。试卷和试题的关系则值得多说几句。需求要求“相同知识点的试题只能在一张试卷中出现一次”按规范化设计应该让试卷和试题形成多对多关系拆出 PaperItem(PaperID, ItemID) 中间表。但实验的实体清单把 ItemID 直接放在试卷表里等价于“一份试卷用多行表示每行存一道题”。这样做不是不能用只是 PaperID 会重复不能单独当主键。我实际建表时会改用复合主键(PaperID, ItemID)既保留课程实体清单的所有内容又能避免同一道题被重复加入。这一点一定要在 E-R 图阶段想清楚等生成了 SQL 再改就很麻烦。考试管理和试卷之间也存在类似问题。需求说“教师指定某次考试使用的试卷”考试管理表里实际需要一个 PaperID 外键否则无法知道这场考试用的是哪张卷。课程实体清单没有写这个字段但概念模型里考试与试卷的关系是必然存在的否则 Realpaper 视图根本连不出来。2.3 相同知识点去重用数据库约束保证应用层可以在生成试卷时用双重循环检查“同知识点题目是否重复”但数据库模式设计里更可靠的是加唯一约束。如果采用(PaperID, ItemID)作为试卷表的复合主键重复插入直接被主键挡住如果保留实验里的单主键写法则额外建立唯一索引CREATE UNIQUE INDEX UNIQ_PAPER_ITEM ON Paper (PaperID, ItemID);这个索引的字段顺序很重要。查询通常以 PaperID 为入口所以把 PaperID 放在索引最左侧ItemID 放在第二列用于唯一判断。索引只占两个 INTEGER共 8 字节左右对千万级数据量也是可以接受的。绝大多数课程设计只会用应用层去重一旦两个老师同时录入同一道题成绩统计就会出错加上这道唯一索引是性价比最高的兜底。3. PowerDesigner概念模型转物理模型DB2字段类型和标识列PowerDesigner 的价值不是画图好看而是让 CDM 和 PDM 保持同步。CDM 里的实体、属性和关系经过 Check Model 检查后可以生成指定数据库的物理模型再产出 DDL 脚本。这个过程如果少了字段类型映射的人工确认导出的表很可能带着 DOUBLE、LONG VARCHAR 这类不适合 DB2 v8.1 的列类型。3.1 从 CDM 到 PDMDBMS 别选错在 PowerDesigner 中先新建 Conceptual Data Model创建 User、KnowledgePoint、ItemBank、Paper、ExamManagement 五个实体然后用 Relationship 连接它们。关系基数这样设置KnowledgePoint 到 ItemBank 是 1..n因为一个知识点对应多道题ItemBank 到 Paper 是多对多但实验简化成 Paper 端挂 ItemIDUser 到 ExamManagement 是 1..n因为一个用户有多条考试记录。画完关系后先执行Check Model修复实体名冲突和关系未命名的问题再执行Tools → Generate Physical Data Model。DBMS 下拉框要选IBM DB2 Universal Database 8.x而不是 7.x 或 DB2 UDB for iSeries。选错版本会影响 TIMESTAMP、IDENTITY 和约束脚本的语法生成后不容易看出问题只有在 DB2 控制台执行时才会暴露。3.2 字段类型映射把分数定义成 DECIMAL 而不是 DOUBLECDM 里的逻辑数据类型不会自动对应到最优的 DB2 物理类型转成 PDM 后必须逐列检查。前面五个实验实体对应的列类型我一般按下面这张表处理CDM 逻辑类型DB2 物理类型适用字段说明IntegerINTEGERUserID, PointID, ItemID, PaperID, EID主键统一用 INTEGERVariable charactersVARCHAR(n)UserName, Ename, Pcontent, Icontentn 按实际长度留余量CharactersCHAR(1)Role教师 T、学生 S固定一个字符Date/TimeTIMESTAMPEtime考试时间包含日期和时刻不能用 DATEFloat/DoubleDECIMAL(5,2)Iscore分数精确到两位不能再用浮点Variable charactersVARCHAR(1024)Ioption四个选项用分隔符拼成一个字段分数用 DECIMAL(5,2) 是 DB2 下最容易忽略的细节。浮点类型在计算平均分时会出现 89.5 变成 89.499999 的情况DECIMAL 则不会。选项字段 Ioption 推荐存成A.xxx#B.xxx#C.xxx#D.xxx这种分隔符文本取题时再按#拆开比额外建四张选项子表更贴近课程实验的体量。转换完成后给主键设置自增。DB2 里叫 IDENTITY在 PowerDesigner 物理模型的 Column Properties 中勾选 Identity然后打开 Identity 属性Start with 填 1Increment 填 1。物理模型导出的建表语句形如CREATE TABLE T_User ( UserID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL, UserName VARCHAR(50) NOT NULL, Role CHAR(1) NOT NULL, Password VARCHAR(128) NOT NULL, CONSTRAINT PK_T_User PRIMARY KEY (UserID) );这里把表名命名为 T_User 而不是 User是因为USER在 DB2 里是系统函数名不加双引号执行时会异常。PowerDesigner 默认会把用户表命名为 User所以建议在 CDM 阶段就改名。GENERATED ALWAYS AS IDENTITY表示插入数据时不传 UserID由数据库自动生成如果将来需要手工指定 ID主键列的类型定义要改成GENERATED BY DEFAULT否则 INSERT 语句必须写OVERRIDING SYSTEM VALUE才能绕过。3.3 外键约束和索引在物理模型里一起生成PowerDesigner 转成 PDM 后外键默认以 Reference 对象存在。生成脚本时会自动构造 ALTER TABLE 语句ALTER TABLE T_ItemBank ADD CONSTRAINT FK_ITEM_POINT FOREIGN KEY (PointID) REFERENCES T_KnowledgePoint(PointID); ALTER TABLE T_Paper ADD CONSTRAINT FK_PAPER_ITEM FOREIGN KEY (ItemID) REFERENCES T_ItemBank(ItemID);执行顺序上先建父表再建子表不会报错反过来删表时必须先把 T_ExamManagement、T_Paper 这类子表 DROP 掉才能 DROP T_User 和 T_KnowledgePoint。PowerDesigner 生成脚本时默认会在外键列上建索引这个选项不要关。DB2 删除父表记录时如果外键列没有索引会触发全表扫描来保证参照完整性数据量一大就会锁表。外键索引建议命名成IDX_ITEM_POINT这样的格式方便后续在 TS 脚本里辨认。3.4 生成 SQL 脚本前的四个检查清单在点击Database → Generate Database之前我会按顺序确认四件事。第一Check Model必须通过尤其是每个实体至少有一个标识符。第二外键列的数据类型必须和主键完全一致比如 PointID 在父表是 INTEGER子表就不能用 SMALLINT。第三PDM 中所有主键都勾选了 Identity否则插入时主键冲突。第四在 Generate Database 对话框里同时勾选 Table、View、Reference 和 Index并把脚本输出路径设为独立目录比如$PROJECT/sql/db2_schema.sql。这样导出的文件可直接交给 DB2 CLP 执行也能在版本管理里逐行 diff。4. 把SQL脚本落进DB2表与视图创建的完整过程PowerDesigner 生成的 SQL 脚本只是“半成品”它默认不包含 CREATE DATABASE也不保证能直接兼容当前 DB2 控制台的编码设置。所以我一般手动建库、分步执行脚本而不是双击脚本一把梭。4.1 创建数据库并执行建表脚本先在 DB2 CLP 里建立课程实验数据库db2 create database examdb using codeset UTF-8 territory CN db2 connect to examdb user db2admin using your_password db2 -tvf db2_schema.sql db2 connect reset-t表示按分号作为语句结束符-v会在屏幕回显每条执行的 SQL-f指定脚本文件。第一次执行时建议先只执行db2 -tvf db2_schema.sql不要连着建视图因为视图脚本依赖的基础表结构可能还没创建完成。PowerDesigner 导出文件如果带中文在 Windows 控制台下常常乱码。可以在执行前执行db2set DB2CODEPAGE1208并重启 DB2 实例或者把脚本通过文本编辑器转成 UTF-8 无 BOM 编码再执行。实验环境是 DB2 v8.1对 UTF-8 的支持本来就有限出现中文异常时优先怀疑编码而不是 SQL 语法。4.2 创建两个视图考试信息和在线试卷表建好后创建实验要求的两个视图。视图在 PowerDesigner 中可以作为独立对象画出来但我更建议直接在 DB2 里手动执行 CREATE VIEW因为视图的 SELECT 语句后续还要调整写成文本文件进版本管理更方便。两个视图的字段来源关系如下视图名称服务对象数据来源ExamInformation学生查看历年考试时间、科目、成绩ExamManagement × User × Paper × ItemBank × KnowledgePointRealpaper学生在线考试按考试加载试卷题目ExamManagement × Paper × ItemBank第一个视图重点是“科目”。科目没有单独实体它由课程知识点里的 Psubject 表达所以视图必须从 ExamManagement 一路 JOIN 到 KnowledgePointCREATE VIEW ExamInformation (UserName, Ename, Etime, Psubject, Egrade) AS SELECT u.UserName, e.Ename, e.Etime, kp.Psubject, e.Egrade FROM T_ExamManagement e JOIN T_User u ON u.UserID e.UserID LEFT JOIN T_Paper p ON p.PaperID e.PaperID LEFT JOIN T_ItemBank ib ON ib.ItemID p.ItemID LEFT JOIN T_KnowledgePoint kp ON kp.PointID ib.PointID;这里使用 LEFT JOIN 是因为 Egrade 可空且某个考试记录可能还没生成卷面不能因为关联不到题就整行丢失。如果 T_Paper 使用(PaperID, ItemID)复合主键同一个考试会因为多道题产生多行展示学生历史记录时要用 DISTINCT 或 GROUP BY 去重。这个细节在实验报告里写出来比只贴建表语句有说服力得多。第二个视图是 Realpaper它把试卷和题目拼在一起供学生进入在线考场时逐条渲染CREATE VIEW Realpaper (EID, PaperName, ItemID, Icontent, Ioption, Iscore) AS SELECT e.EID, p.PaperName, ib.ItemID, ib.Icontent, ib.Ioption, ib.Iscore FROM T_ExamManagement e JOIN T_Paper p ON p.PaperID e.PaperID JOIN T_ItemBank ib ON ib.ItemID p.ItemID;视图里不写WHERE EID ?因为“在线试卷”不是只给某一场考试用。真正查询时再过滤SELECT EID, PaperName, ItemID, Icontent, Ioption, Iscore FROM Realpaper WHERE EID 1001;视图不是物化表每次查询都会重新执行 JOIN所以在视图里写死考试编号会让这个视图失去复用价值。执行视图脚本仍然用 DB2 CLPdb2 connect to examdb user db2admin using your_password db2 -tvf create_views.sql db2 connect reset4.3 查看表和视图是否真的创建成功建完后不要急着截图先用系统目录表确认对象存在db2 SELECT TABNAME, TYPE FROM SYSCAT.TABLES WHERE TABSCHEMA CURRENT SCHEMA ORDER BY TABNAME db2 DESCRIBE TABLE ExamInformation db2 SELECT COUNT(*) FROM Realpaper第一条语句的 TYPE 列T 表示表V 表示视图。如果 ExamInformation 没有出现在结果里说明 CREATE VIEW 执行失败常见原因是 T_Paper 表名不对或者 T_KnowledgePoint 在物理模型里叫了别的名字。第二条语句 DESCRIBE 能显示出视图的列名、类型和长度和 PowerDesigner 物理模型对比一下就能确认字段映射是否正确。第三条语句返回 0 也是正常的视图能执行 COUNT 就说明语法和权限都没问题数据反而可以后补。5. 视图建好后的一步在 DB2 里查依赖和隔离级别表和视图创建成功只是开始。PowerDesigner 生成的视图和手工写的视图在 DB2 的依赖关系上完全一样一旦基表结构变化视图可能失效而且不会在创建时报错。5.1 用 SYSCAT.VIEWS 检查视图是否失效DB2 把视图的定义文本存放在SYSCAT.VIEWS中同时有个VALID字段直接标记视图是否可执行。出现以下情况时看清这个字段某天往 T_Paper 表加了新列PowerDesigner 又重新生成了脚本并覆盖执行Realpaper 视图依赖的列可能还在但类型被改掉这时视图不会被自动更新。执行下面的语句快速体检SELECT VIEWNAME, VALID, TEXT FROM SYSCAT.VIEWS WHERE VIEWNAME IN (EXAMINFORMATION, REALPAPER);VALID 为 N 时先读 TEXT 字段找回原始 SELECT再对照当前表结构判断是列名不一致还是类型不兼容。DB2 支持ALTER VIEW ExamInformation REGENERATE时可以用这个语句重新编译v8.1 如果提示语法不支持就直接 DROP VIEW 再重建。5.2 历史成绩去重和在线试卷读取优化ExamInformation 视图因为 Paper 复合主键会产生重复行学生查询历史成绩时建议用 DISTINCTSELECT DISTINCT UserName, Ename, Etime, Psubject, Egrade FROM ExamInformation WHERE UserName student01 ORDER BY Etime DESC;这条语句只适合展示不适合直接在统计接口里套 SUM。如果你要计算某个学生的平均成绩应该回到基表按 EID 分组而不是在重复视图结果上聚合。在线试卷读取可以用 DB2 的隔离级别控制锁等待。考试场景中试卷内容在考中通常不会再被编辑查询 Realpaper 时加上 WITH URSELECT EID, PaperName, ItemID, Icontent, Ioption, Iscore FROM Realpaper WHERE EID 1001 WITH UR;UR 表示未提交读不会请求行锁避免学生提交答案的高峰期和教师端改试卷互相阻塞。前提是考试开始后教师确实不改题如果考中允许教师修正试题就删掉 WITH UR用默认的 CS 隔离级别读到已提交数据。一个视图在不同业务场景下使用不同隔离级别比在视图定义里写死锁选项要灵活得多。最后再检查一次 EXAMINFORMATION 的 VALID如果已经是 N优先看 T_PAPER 的复合主键是否被 PowerDesigner 重新生成过这是这个实验最常见的视图失效原因。本文还有配套的精品资源点击获取