拓冰网站定制 
简介一份面向北京邮电大学数据库课程的实验报告围绕在线考试系统需求完整呈现数据库模式设计全过程。内容涵盖需求分析与实体提取用户、试题库、知识点、试卷、考试管理5个实体、E-R图构建、Power Designer概念模型转换以及将物理模型导出SQL脚本并在IBM DB2中生成表和视图的实操步骤可供数据库设计初学者或完成同类实验的本科生对照参考。文档按实验目的、实验环境、实验步骤、结果与分析的结构组织并包含概念模型到物理模型转换的截图说明便于追溯关键设计环节资源为1个doc文档压缩包共1个文件整体约1.56MB可直接打开阅读。文档同时整理E-R图、逻辑模式与物理模式、视图作用等核心知识点有助于理解数据完整性约束与数据库实现流程。已有126人学习适合用于快速把握实验重点和验证设计思路。1. 数据库模式设计不只是画图这个实验到底在练什么数据库模式设计这件事我在实验室见过太多翻车现场E-R 图画得挺完整PowerDesigner 模型也生成出来了结果脚本一放进 DB2 就报错不是外键顺序问题就是视图字段对不上。这个实验的核心是围绕在线考试系统做数据库模式设计教师录入试题、按知识点组卷、学生参加考试、提交后立刻出成绩你需要从需求描述里抽出用户、试题库、知识点、试卷、考试管理五个实体再走一遍“E-R 图 → 概念模型 → 物理模型 → SQL 脚本 → DB2 建表建视图”的完整链路。适合正在做数据库课程设计、需要交实验报告的同学也适合刚接触 Power Designer 或 DB2 的开发者按这个思路复现一遍。按这个思路走一遍能帮你少踩外键顺序和视图过滤的坑把模式设计变成可验证、可执行的脚本。2. 需求分析到 E-R 图五个实体和它们的约束怎么定2.1 按实验需求拆实体和属性为什么试题、知识点要分开拿到需求描述第一件事不是开 PowerDesigner 画图而是先把句子拆成对象和动作。这段需求里反复出现的名词就是实体候选教师、学生、用户、试题、知识点、试卷、考试、成绩。但教师和学生都可以归到“用户”实体里因为需求只强调“一个用户有且只有一种角色”并不需要教师和学生各自单独建表。我一般会先把角色当成用户表的属性而不是独立实体原因很直接角色只有教师、学生两种属于同一类用户对象的分类标签不是单独的行为主体。如果建了一个“教师表”和一个“学生表”后续登录、权限、成绩关联都要做两套逻辑在实验这个体量里反而画蛇添足。所以用户表只放四个字段用户 ID、用户名、角色、密码。接下来是试题库和知识点。很多同学会把“知识点”直接做成试题表里的一个文本字段这不符合需求里的两个条件一是课程知识点确定且可以扩展二是一道试题只能考察一个知识点。如果把知识点做成文本字段将来想统计“某个知识点的出题量和正确率”就只能靠 LIKE 模糊匹配没法做稳定关联。把知识点抽成独立实体后试题表只存一个知识点代码外键知识点内容单独维护才能支持后续扩展。实验文档里给的需求说试题采用单项选择形式包含知识点、内容、分值、备选答案和唯一正确答案。那么试题库实体最少要有这几个属性题目代码、题目内容、分数、选项、正确答案、知识点代码外键。这里有一个容易漏掉的细节分数和正确答案不能为空但原始文档没有明确标注做模式设计时必须自己在需求里推导出来。分值在考试逻辑里要参与成绩计算正确答案要用来判分所以都应该加上 NOT NULL 约束。五个实体的属性拆完后我按实验报告的口径整理了一张对照表实体关键属性主键外键用户UserID, UserName, Role, PasswordUserID无知识点PointID, Pcontent, PsubjectPointID无试题库ItemID, Icontent, Iscore, Ioption, Ianswer, PointIDItemIDPointID → 知识点试卷PaperID, PaperNamePaperID无考试管理EID, Ename, Etime, Egrade, UserIDEIDUserID → 用户这里要注意原始实验文档在“试卷”实体里直接放了一个“题目代码 ItemID”作为外键这其实是一个很大的简化。按需求描述试卷由“一定数量即知识点的数量的试题组成”一份试卷里面至少会有多道题直接在试卷表里放一个 ItemID 只能表示一份试卷挂一道题明显和“组成”这个词冲突。后面做物理模型时我会把试卷和试题的关系拆成一张关联表否则生成的数据库根本支撑不了组卷逻辑。2.2 关系和基数约束试题与试卷的“同知识点只出现一次”怎么在 E-R 图表达实体拆完不等于 E-R 图完成真正决定数据库能不能跑起来的是关系基数。这个实验里最容易理解错的关系有三个知识点与试题、试卷与试题、考试与试卷。先看知识点与试题。需求明确“一道试题只能考察一个知识点”所以这是典型的一对多关系一个知识点对应多道试题一道试题只属于一个知识点。外键一定要放在多的一端也就是试题库表里放 PointID。反过来在知识点表里放题目字段是错的那样一个知识点只能保存一道题完全跑偏。再看试卷与试题。前面已经说过试卷要包含多道试题而同一道试题在题库里只有一份所以从逻辑上讲是典型的多对多关系一份试卷包含多道题一个题目也可能被多份试卷选用。PowerDesigner 在概念模型里可以直接画“多对多关系”生成物理模型时它会自动换算出一张关联表关联表里放试卷代码和题目代码两个外键。如果实验里老师要求只建五个实体不加第六张表那么你至少要在试卷表里用“题目代码”做外键来应付报告但从模式设计的完整性出发我更建议保留关联表。“同一知识点的试题只能在一份试卷中出现一次”这个约束属于业务规则不是简单的外键能卡死的。数据库层面只能做到试卷试题关联表里每条记录的试卷代码题目代码唯一不能自动限制“两张题目不能属于同一知识点”。要在数据库里真正实现这个约束需要结合题目表带出知识点再写唯一索引或者触发器。我在实验报告里通常会把它写成一段文字说明E-R 图阶段用关系注释表达这个限制物理模型阶段暂不实现只保留在系统设计文档里。这个处理方式不是偷懒而是让实验聚焦在模式设计流程本身。考试与试卷的关系也很关键。需求说“教师指定某次考试使用的试卷学生参加考试使用统一的试卷”那么一次考试只能用一份试卷一份试卷可以被多次考试使用这是多对一关系多场考试对同一份试卷。所以考试管理表里要加一个 PaperID 外键指向试卷表。但原始实验文档列考试管理实体时只写了用户 ID 外键没有写试卷外键这就是一个明显的遗漏。如果你完全照抄文档生成物理模型考试记录就无法关联到具体试卷“拿到试卷”这个功能根本走不通。考试管理表里的 UserID 我理解为“参加考试的学生”。需求里允许学生提交答案后立刻得到成绩成绩还可能为空说明考试管理表其实承担了“考试字面信息”和“学生答卷成绩记录”的双重职责。严格建模应该把考试和成绩拆成两个实体但实验文档把两者合并在一个表里我们就按它的设计来EID 是考试代码Ename 是考试标题Etime 是考试时间Egrade 是学生成绩且允许为空UserID 是学生外键。这样一张表能查询出某次考试有哪些学生参加、谁还没出成绩虽然有点糙但满足实验步骤。最后是用户和角色的处理。需求有一个硬性条件“一个用户有且只有一种角色”所以用户表里的 Role 字段要加 CHECK 约束只允许 TEACHER 和 STUDENT 两种值。很多同学会用默认值或者干脆不约束这在实验评分时容易被扣分因为需求里白纸黑字写了“唯一角色”说明设计者要有意识地用约束去实现它。E-R 图阶段可以在 Role 属性下标注“单一取值”生成物理模型后转成 CHECK 即可。3. 用 Power Designer 把 E-R 图变成物理模型从概念模型到 DB2 脚本3.1 概念模型CDM的建模顺序与命名规范用 PowerDesigner 做实验开箱后先建 Conceptual Data Model。我习惯的操作顺序是先把五个实体画出来属性全部填完再拉关系线。如果先拉线后补属性后面返回去改实体时关系上的外键映射经常不跟着刷新又要重新生成物理模型。实体命名尽量采用英文避免中文表名直接落到 DB2 里。DB2 v8.1 在 Windows 上支持中文标识符但 SQL 脚本文件如果编码不对很容易在导入时乱码。保守做法是全用英文大写或 PascalCase。属性名也一样UserID 就用 UserIDEname 就用 Ename。PowerDesigner 的默认显示不区分大小写但生成脚本时可以统一转大写。每个实体都要先在 CDM 里把主标识符Primary Identifier定好。用户实体定 UserID知识点定 PointID试题库定 ItemID试卷定 PaperID考试管理定 EID。主键列在“Identifiers”选项卡里勾选不要在属性面板里随手设个“Primary”完事。后续生成物理模型时主标识符会直接变成主键约束如果这一步漏了生成的 PDM 里所有表都没有主键DB2 的 UPDATE 和 DELETE 会变得非常难写。在设计属性时最好先定义几个 Domain 统一下数据类型。PowerDesigner 的 Domain 相当于一套自定义类型ID 固定用 Integer名称固定用 VARCHAR(50)题目内容这种长文本固定用 VARCHAR(500)。这样后续新建实体时直接复用 Domain生成的物理模型也会保持一致不会出现用户表的 UserID 是 Integer、考试表的 UserID 变成 BigInt 这种低级问题。实验文档不要求 Domain但按这个习惯走一遍能省掉后面改外键列类型的麻烦。3.2 生成物理模型PDM与 DB2 v8.1 的适配CDM 画完后在菜单栏选 Tools → Generate Physical Data Model新版 PowerDesigner 会弹出一个选择框DBMS 列表里要选 IBM DB2 UDB for Linux, Unix, and Windows 8.x。这一步选错 DBMS后面生成的 SQL 语法会和实验环境不匹配比如把数据类型生成成 SQL Server 的 NVARCHARDB2 导入直接报语法错误。选择 DB2 8.x 后PowerDesigner 会自动做一层类型映射。常见映射有以下这些CDM 里的数据类型生成的 DB2 v8.1 类型说明IntegerINTEGER主键、外键都推荐用这个Variable characters (n)VARCHAR(n)名称、内容、密码等文本Decimal(p,s)DECIMAL(p,s)成绩、分值等小数Date TimeTIMESTAMP考试时间BooleanSMALLINT如果需要布尔字段DB2 没有原生 BOOLEAN生成 PDM 时还要注意外键的命名。PowerDesigner 默认会生成类似“FK_TABLE_REFERENCE_TABLE”的长名字这在 DB2 里没有太大问题但后续反向工程时名字太长不好认。我一般会在 PDM 里手工把外键约束重命名为“FK_子表名_父表名”例如 FK_EXAM_USER、FK_EXAM_PAPER。这样执行 SQL 后用系统目录查约束时一眼就能看出谁引用谁。多对多关系在 PDM 里会体现成一张关联表。如果在 CDM 中试卷与试题画的是多对多生成 PDM 后会自动出现 PAPER_ITEM 表字段就是 PaperID 和 ItemID 两个外键组成的联合主键。这个自动生成过程不需要你手写代码但你要能认出来。很多同学看到模型里凭空多了一张表就以为是误操作给删了删完之后试卷的题量就变成只能挂一条这是整个实验里最容易踩的坑之一。如果你不希望它自动生成也可以手动把关系改为两个一对多但我不建议这么改因为自动生成的行为才是更标准的多对多建模方式。3.3 导出 SQL 脚本和两个关键视图的定义PDM 整理好之后选 Database → Generate Database目标 DBMS 同样选 DB2 UDB 8.x。这一步会生成一个 SQL 脚本里面通常包含建表、主键、外键、索引和部分约束。生成前要在 Options 里把“生成外键”勾上否则脚本里只有建表语句没有 ALTER TABLE ADD FOREIGN KEY表之间的引用关系就丢了。PowerDesigner 里视图不是从 CDM 直接转换过来的CDM 阶段一般不画视图视图要在 PDM 里手工创建。你在 PDM 工作区的模型树里找到 View 分组右键新建视图填入视图名称后再打开它的 Query 选项卡编写 SELECT 语句。这个 SELECT 可以手动写也可以点击打开 Query Builder 拖表生成。实验要求建两个视图考试信息视图和在线试卷视图。考试信息视图我通常命名为 EXAM_INFORMATION目的是给学生提供历年考试时间、科目、成绩记录。在线试卷视图命名为 REAL_PAPER目的是给正在参加考试的学生展示试卷内容包括题目、选项、所属知识点。这里有一个设计细节在线试卷视图不应该把正确答案包含进去否则学生在答题前就能从接口里查到答案安全性上完全说不通。原始文档里题目有一个正确答案属性但视图生成时我会在 SQL 查询里把 Ianswer 字段排除掉只保留题号的题目内容、选项和知识点信息。导出 SQL 后建议先别急着在 DB2 里执行把脚本用文本编辑器打开重点看三处表名是否和 PDM 一致外键是否有命名CREATE VIEW 语句是否出现在建表之后。我见过不少同学导出的视图语句跑到了建表语句前面结果 DB2 执行时报“未找到表或视图”原因就是脚本里对象顺序和依赖关系不对。4. 生成表和视图的 SQL 落地脚本怎么改才能在 DB2 里跑通4.1 五张表的 DDL 结构与外键关系PowerDesigner 生成的脚本往往比较啰嗦有 DROP TABLE、CREATE TABLE、ALTER TABLE 外键约束等一大段。为了讲清楚结构我会把核心建表语句整理成下面这版精简 DDL。你自己在实验里可以不完全照抄但对照它能看出模式和表之间的关系。CREATE TABLE USER_INFO ( UserID INTEGER NOT NULL, UserName VARCHAR(50) NOT NULL, Role CHAR(10) NOT NULL, Password VARCHAR(64) NOT NULL, CONSTRAINT PK_USER_INFO PRIMARY KEY (UserID), CONSTRAINT CK_USER_ROLE CHECK (Role IN (TEACHER, STUDENT)) );这里把表名从 USER 改成 USER_INFO是因为 USER 在 DB2 里是系统保留字直接执行 CREATE TABLE USER 大概率会报语法错误。Role 字段用 CHAR(10) 而不是 VARCHAR因为教师和学生两种角色长度固定CHAR 更紧凑同时加上 CHECK 约束来实现“一个用户只有一种角色”。CREATE TABLE KNOWLEDGE_POINT ( PointID INTEGER NOT NULL, Pcontent VARCHAR(200) NOT NULL, Psubject VARCHAR(100), CONSTRAINT PK_KNOWLEDGE_POINT PRIMARY KEY (PointID) );知识点表本身没有外键Psubject 代表学科在实验里可以理解为课程名称。Pcontent 是知识点内容比如“数据库事务 ACID”。CREATE TABLE ITEM_BANK ( ItemID INTEGER NOT NULL, Icontent VARCHAR(500) NOT NULL, Iscore DECIMAL(5,2) NOT NULL, Ioption VARCHAR(500) NOT NULL, Ianswer VARCHAR(10) NOT NULL, PointID INTEGER NOT NULL, CONSTRAINT PK_ITEM_BANK PRIMARY KEY (ItemID), CONSTRAINT FK_ITEM_POINT FOREIGN KEY (PointID) REFERENCES KNOWLEDGE_POINT (PointID) );试题库表就是题库的核心。Iscore 用 DECIMAL(5,2) 而不是 INTEGER因为分值可能是 2.5 分这种带小数的设计。Ianswer 只存一个正确答案标识比如选项 A。这里外键 PointID 引用知识点表保证每道题都必须归属到一个真实存在的知识点否则组卷时按知识点分组就无从谈起。Ioption 字段用来放整个单选题选项的拼接文本可以用分隔符从 A 到 D 一次存完实验粒度不需要单独做一张选项表。CREATE TABLE PAPER ( PaperID INTEGER NOT NULL, PaperName VARCHAR(100) NOT NULL, CONSTRAINT PK_PAPER PRIMARY KEY (PaperID) ); CREATE TABLE PAPER_ITEM ( PaperID INTEGER NOT NULL, ItemID INTEGER NOT NULL, CONSTRAINT PK_PAPER_ITEM PRIMARY KEY (PaperID, ItemID), CONSTRAINT FK_PAPER_ITEM_PAPER FOREIGN KEY (PaperID) REFERENCES PAPER (PaperID), CONSTRAINT FK_PAPER_ITEM_ITEM FOREIGN KEY (ItemID) REFERENCES ITEM_BANK (ItemID) );这里我用 PAPER_ITEM 关联表实现试卷与试题的多对多关系。联合主键是PaperID, ItemID能保证同一道题在试卷里不会重复录入但注意它不能保证“同一知识点的不同题目不重复出现”这两件事是完全不同的约束实验里不要混淆。CREATE TABLE EXAM_MANAGEMENT ( EID INTEGER NOT NULL, Ename VARCHAR(100) NOT NULL, Etime TIMESTAMP, Egrade DECIMAL(5,2), UserID INTEGER NOT NULL, PaperID INTEGER NOT NULL, CONSTRAINT PK_EXAM_MANAGEMENT PRIMARY KEY (EID), CONSTRAINT FK_EXAM_USER FOREIGN KEY (UserID) REFERENCES USER_INFO (UserID), CONSTRAINT FK_EXAM_PAPER FOREIGN KEY (PaperID) REFERENCES PAPER (PaperID) );考试管理表里 Etime 用 TIMESTAMP记录的是学生参加考试的时间点。Egrade 允许为空对应“学生还没交卷或还没出成绩”的状态这样设计才能区分未考试和考了零分两种不同情况。UserID 指向学生PaperID 指向本次考试使用的试卷这两个外键合起来才能表达“某学生参加了某份试卷对应的考试”。4.2 两个视图的定义和用途考试信息视图的定位是学生历史考试成绩查询。我不想把它写成复杂的多表连接因为原始文档里的考试管理表本身已经包含了学生 ID、考试名称、时间和成绩直接过滤掉 NULL 成绩就能得到有效历史记录。CREATE VIEW EXAM_INFORMATION ( ExamCode, SubjectName, ExamTime, Grade, UserID ) AS SELECT EID, Ename, Etime, Egrade, UserID FROM EXAM_MANAGEMENT WHERE Egrade IS NOT NULL;这个视图里的 Grade 就是 EgradeSubjectName 其实不严谨地借用了 Ename 字段。真正严格的系统应该有科目表但实验里课程只有一门所以这种做法可以接受。过滤条件 WHERE Egrade IS NOT NULL 是最关键的一点它把还没出成绩的记录排除掉免得学生在历史记录里看到一堆空成绩。在线试卷视图要同时关联考试管理、试卷、试卷试题关联表和试题库因为一份在线试卷必须能告诉学生本次考试试卷标题、包含哪些题、每个题的选项是什么。CREATE VIEW REAL_PAPER ( EID, PaperID, PaperName, ItemID, Icontent, Ioption, PointID ) AS SELECT em.EID, em.PaperID, p.PaperName, pi.ItemID, ib.Icontent, ib.Ioption, ib.PointID FROM EXAM_MANAGEMENT em JOIN PAPER p ON em.PaperID p.PaperID JOIN PAPER_ITEM pi ON p.PaperID pi.PaperID JOIN ITEM_BANK ib ON pi.ItemID ib.ItemID;视图列里没有 Ianswer这是特意去掉的。如果教师需要阅卷评分可以另外建一个带正确答案的视图或者直接查 ITEM_BANK 表。把正确答案暴露给学生的在线试卷视图是设计事故哪怕只是一个实验也必须养成这种安全意识。这里的四个 JOIN 每一步都有明确作用第一个 JOIN 把考试关联到试卷第二个 JOIN 从试卷取题目关系第三个 JOIN 从关系中拿到题目的实际内容缺一环试卷就拼不完整。4.3 执行脚本后的验证用系统目录查表结构在 DB2 v8.1 里执行脚本我推荐在命令行里用db2 -tvf schema.sql-t表示以分号作为语句终止符-v表示回显每条正在执行的 SQL-f指定脚本文件。这样哪条语句报错能立刻定位到对应行号。把前面的建表和视图语句按顺序存到一个文件里执行完成后不要急着通过命令行客户端乱翻直接查系统目录表来验证。SELECT TABNAME, TYPE FROM SYSCAT.TABLES WHERE TABSCHEMA CURRENT SCHEMA ORDER BY TABNAME;这条查询返回当前模式下的所有表和视图。SYSCAT.TABLES 里 TYPE 为 T 表示表V 表示视图。正常情况你至少能看到 USER_INFO、KNOWLEDGE_POINT、ITEM_BANK、PAPER、PAPER_ITEM、EXAM_MANAGEMENT 六张 T 记录以及 EXAM_INFORMATION、REAL_PAPER 两条 V 记录。如果少了 PAPER_ITEM说明生成 PDM 时多对多关系没有成功转换需要回去检查 CDM。SELECT COLNAME, TYPENAME, LENGTH, NULLS FROM SYSCAT.COLUMNS WHERE TABNAME EXAM_MANAGEMENT ORDER BY COLNO;这条查询用于核对列定义。重点看 Egrade 的 NULLS 是否为 Y如果变成 N说明你给成绩字段加了 NOT NULL会导致没参加考试的学生无法写入记录。还要看外键列 UserID 和 PaperID 的数据类型是否与其他表的主键类型一致。DB2 对外键列类型不匹配常常不报错但后续 JOIN 时会出现效率问题甚至类型转换异常这种坑非常隐蔽。SELECT CONSTNAME, TABNAME, REFTABNAME FROM SYSCAT.REFERENCES WHERE TABSCHEMA CURRENT SCHEMA ORDER BY CONSTNAME;最后验证外键。SYSCAT.REFERENCES 里保存了每个外键约束的源表和引用表。核对一下 FK_EXAM_USER 是不是从 EXAM_MANAGEMENT 指向 USER_INFOFK_EXAM_PAPER 是不是从 EXAM_MANAGEMENT 指向 PAPER。视图底层引用了哪些表也能通过这条已知关系去推导。如果某张表的外键消失了最常见的原因是生成脚本时勾掉了外键选项或者在建表前先删掉了约束。5. 数据库模式设计避坑五种最常见的实验翻车现场5.1 外键顺序导致建表失败SQLCODE -204现象执行 PowerDesigner 导出的脚本时前几条 CREATE TABLE 都成功到某一条突然报 SQLCODE -204提示“表或视图不存在”。我见过最多的是在创建 ITEM_BANK 表时引用 KNOWLEDGE_POINT但脚本里还没创建知识点表。原因PowerDesigner 生成脚本时表顺序来自 PDM 里对象的创建顺序而不是依赖关系顺序。如果你先画了试题库再画知识点脚本导出的顺序就会把 ITEM_BANK 排在前面。DB2 建表带外键引用时被引用表还没有建立自然报表不存在。解决不要靠手拖脚本顺序最稳妥的办法是把所有表先建完再统一加外键约束。也就是把 CREATE TABLE 放前面把 ALTER TABLE ADD FOREIGN KEY 放脚本最后。这个习惯我后来一直在用不管是什么建模工具导出的脚本都先做这一步整理能绕开大部分建表顺序坑。5.2 PowerDesigner 生成类型和保留字冲突SQLCODE -104现象用户表建不出来只要 SQL 里出现 USER 这个表名就报 SQLCODE -104 或 -199错误信息指向表名附近。原因USER 是 DB2 内置函数和特殊寄存器名称在没有加双引号的情况下会被当成关键字解析。PowerDesigner 从 CDM 生成 PDM 时不会自动帮你判断目标数据库保留字它只会按照实体名称原样生成。解决表名改成 USER_INFO、APP_USER 或 T_USER 之类字段也要避开 TIME、DATE、ROLE 等容易撞车的词。另外一个相关点是数据类型如果 CDM 里字段没给长度DB2 的 VARCHAR 会因为没有长度参数而报语法错误。对策是在 CDM 阶段每一个文本属性都显式设置长度不要依赖默认值否则生成脚本后在 DB2 里改起来更费劲。5.3 多对多关系没生成关联表试卷变成单题试卷现象执行完脚本往 PAPER 表里插入数据时发现只有一列 ItemID一张试卷只能关联一道题。组卷要求一个知识点出一道题结果一份卷子只有一题。原因在 CDM 里把试卷与试题画成了一对一或一对多关系导致 PowerDesigner 认为多的一段只在 PAPER 表放外键即可。这种错误通常发生在没有理解题意的时候以为“试卷包含题目”就是试卷表里加一个题目字段。解决回到 CDM把试卷和试题的关系改成多对多重新生成物理模型PowerDesigner 会自动产出 PAPER_ITEM 关联表。如果你已经把 CDM 删了或者来不及改也可以手动建 PAPER_ITEM 表并补两条外键。这里我要强调一点自动生成的关联表名可能很乱比如 PAPER_ITEM_BANK 之类最好手动在 PDM 里重命名为 PAPER_ITEM不然实验报告的表格不美观后面写 SQL 也很别扭。5.4 视图里出现大量 NULL 成绩历史记录全是空行现象EXAM_INFORMATION 视图创建成功后用学生账号查询发现每个考试标题都有记录但成绩全是空的根本分不清哪些考过、哪些没考过。原因直接在 EXAM_MANAGEMENT 表上建视图时没有过滤 Egrade而 Egrade 允许为空未交卷的考生记录也会被视图查出来。这属于查询条件没有匹配业务语义。解决在视图定义里过滤掉成绩为空的行或者更好的做法是把“考试信息”和“成绩记录”分开建模。如果只是交实验把 WHERE Egrade IS NOT NULL 加上就够交差了。但如果将来做真实系统我会把 EXAM_MANAGEMENT 拆成 EXAM 和 EXAM_SCORE 两张表考试表存考试标题、考试时间、试卷、任课教师成绩表存学生 ID、考试 ID、分数、交卷状态。这样视图查询才不用靠 NULL 去猜状态。5.5 换达梦数据库时的模式错误schema 与用户名不匹配现象把同一套脚本拿到达梦数据库里执行建表语句报“模式错误”或“模式 [XXX] 不存在”但同样脚本在 DB2 里跑得好好的。原因和 DB2 的 CURRENT SCHEMA 机制类似达梦默认把用户名作为模式名如果当前连接用户是 SYSDBA 或没有同名模式脚本里又没有 SET SCHEMA 指向实际业务模式CREATE TABLE 就会失败。PowerDesigner 生成的脚本里如果带上了创建者的 AUTHORIZATION 信息到另一个数据库实例里也容易出现模式错位。解决在脚本开头显式声明模式比如SET SCHEMA DBUSER;或者把脚本里的AUTHORIZATION子句去掉。网络上关于“达梦数据库 模式错误”的提问大半都是这一类原因。遇到这个报错第一反应不应该是改表结构而是检查当前连接用户、目标模式和脚本里的模式名是否三者一致。这个经验放到国产数据库迁移场景里同样适用模式名是数据库对象的第一层命名空间错一个字符都找不到对象。6. 验证模式设计是否合格用系统查询和一个小技巧6.1 建立一套可重复跑的验收清单模式设计到底做没做对不能只靠眼睛看 E-R 图要落到可查询的数据库对象上。我会用一条 SQL 一组结果的方式做验收逻辑是这样的先确认对象齐全再确认列定义正确最后确认约束生效。SELECT TABNAME, TYPE FROM SYSCAT.TABLES WHERE TABSCHEMA CURRENT SCHEMA ORDER BY TABNAME;备选的一组“预期”应当是六张 T 表和两张 V 表。这一步达成后做一次插入测试插入一个教师用户、一个知识点、两道题、一份试卷、一次考试然后查询 REAL_PAPER 视图看看能不能把对应题目带出来。如果题目数量对不上多半是 PAPER_ITEM 没有成功插入或者视图 JOIN 字段写错。数据能读出来之后再验证约束是否真的工作。试着往 USER_INFO 里插入 Role 为 ADMIN 的记录正确结果是被 CK_USER_ROLE 拦住试着插入不存在的 UserID 到 EXAM_MANAGEMENT正确结果是被 FK_EXAM_USER 拦住。如果数据库没有拦住说明外键没有生效后续业务层就要承担大量本来数据库该管的事情。SELECT CONSTNAME, TABNAME, REFTABNAME FROM SYSCAT.REFERENCES WHERE TABSCHEMA CURRENT SCHEMA;另外一个实验里值得做的验证是视图权限。DB2 v8.1 里用户要能查视图至少需要视图所属模式的 EXECUTE 或原表的 SELECT 权限。很多学生在自己账号下建的表自己查没问题换一个账号登录就提示没有权限然后以为视图坏了其实只是授权没做。实验报告里把权限语句写上比如给课程账号授权查询两个视图能多拿不少印象分。6.2 用反向工程把数据库拉回 PDM 做一致性核验最后分享一个我后来一直保留的习惯做完整套实验后把数据库里的实际结构反向工程回 PowerDesigner和原来设计的 PDM 对比一遍。具体操作是在 PDM 界面选择 Database → Reverse Engineer Database数据库类型选 IBM DB2 UDB 8.x然后配置 ODBC 数据源连接到你的实验数据库。连上之后PowerDesigner 会读取当前模式下的表、列、主键、外键、视图反向生成一个基于数据库实际状态的物理模型。把这个反向生成的模型和当初正向设计的模型放在一起逐表比较字段、约束和关系。这个习惯的价值在于能发现两类正向设计里看不见的问题一类是执行脚本时人为修改了表名或列名导致模型和真实库不一致另一类是漏掉了外键视图 JOIN 却引用到了这个列数据能跑通但关系不完整。反向工程出来的模型才是数据库真正的模式原来的 PDM 只是愿景两者一旦对不上说明设计过程中有一处“模式漂移”没有被发现。从那以后我每次做完数据库模式设计不管交不交实验报告都会强制走一遍反向工程把“设计模型”和“物理库模型”晒在一起对比半小时。外键名称对不上、类型不一致、视图少过滤条件这些问题都能在对比时原形毕露。用这套方法重做这个在线考试系统实验你的模式和脚本才真正经得起数据库本身的检验希望帮到你。本文还有配套的精品资源点击获取 数据库模式设计实战：在线考试系统建模与DB2实现
简介一份面向北京邮电大学数据库课程的实验报告围绕在线考试系统需求完整呈现数据库模式设计全过程。内容涵盖需求分析与实体提取用户、试题库、知识点、试卷、考试管理5个实体、E-R图构建、Power Designer概念模型转换以及将物理模型导出SQL脚本并在IBM DB2中生成表和视图的实操步骤可供数据库设计初学者或完成同类实验的本科生对照参考。文档按实验目的、实验环境、实验步骤、结果与分析的结构组织并包含概念模型到物理模型转换的截图说明便于追溯关键设计环节资源为1个doc文档压缩包共1个文件整体约1.56MB可直接打开阅读。文档同时整理E-R图、逻辑模式与物理模式、视图作用等核心知识点有助于理解数据完整性约束与数据库实现流程。已有126人学习适合用于快速把握实验重点和验证设计思路。1. 数据库模式设计不只是画图这个实验到底在练什么数据库模式设计这件事我在实验室见过太多翻车现场E-R 图画得挺完整PowerDesigner 模型也生成出来了结果脚本一放进 DB2 就报错不是外键顺序问题就是视图字段对不上。这个实验的核心是围绕在线考试系统做数据库模式设计教师录入试题、按知识点组卷、学生参加考试、提交后立刻出成绩你需要从需求描述里抽出用户、试题库、知识点、试卷、考试管理五个实体再走一遍“E-R 图 → 概念模型 → 物理模型 → SQL 脚本 → DB2 建表建视图”的完整链路。适合正在做数据库课程设计、需要交实验报告的同学也适合刚接触 Power Designer 或 DB2 的开发者按这个思路复现一遍。按这个思路走一遍能帮你少踩外键顺序和视图过滤的坑把模式设计变成可验证、可执行的脚本。2. 需求分析到 E-R 图五个实体和它们的约束怎么定2.1 按实验需求拆实体和属性为什么试题、知识点要分开拿到需求描述第一件事不是开 PowerDesigner 画图而是先把句子拆成对象和动作。这段需求里反复出现的名词就是实体候选教师、学生、用户、试题、知识点、试卷、考试、成绩。但教师和学生都可以归到“用户”实体里因为需求只强调“一个用户有且只有一种角色”并不需要教师和学生各自单独建表。我一般会先把角色当成用户表的属性而不是独立实体原因很直接角色只有教师、学生两种属于同一类用户对象的分类标签不是单独的行为主体。如果建了一个“教师表”和一个“学生表”后续登录、权限、成绩关联都要做两套逻辑在实验这个体量里反而画蛇添足。所以用户表只放四个字段用户 ID、用户名、角色、密码。接下来是试题库和知识点。很多同学会把“知识点”直接做成试题表里的一个文本字段这不符合需求里的两个条件一是课程知识点确定且可以扩展二是一道试题只能考察一个知识点。如果把知识点做成文本字段将来想统计“某个知识点的出题量和正确率”就只能靠 LIKE 模糊匹配没法做稳定关联。把知识点抽成独立实体后试题表只存一个知识点代码外键知识点内容单独维护才能支持后续扩展。实验文档里给的需求说试题采用单项选择形式包含知识点、内容、分值、备选答案和唯一正确答案。那么试题库实体最少要有这几个属性题目代码、题目内容、分数、选项、正确答案、知识点代码外键。这里有一个容易漏掉的细节分数和正确答案不能为空但原始文档没有明确标注做模式设计时必须自己在需求里推导出来。分值在考试逻辑里要参与成绩计算正确答案要用来判分所以都应该加上 NOT NULL 约束。五个实体的属性拆完后我按实验报告的口径整理了一张对照表实体关键属性主键外键用户UserID, UserName, Role, PasswordUserID无知识点PointID, Pcontent, PsubjectPointID无试题库ItemID, Icontent, Iscore, Ioption, Ianswer, PointIDItemIDPointID → 知识点试卷PaperID, PaperNamePaperID无考试管理EID, Ename, Etime, Egrade, UserIDEIDUserID → 用户这里要注意原始实验文档在“试卷”实体里直接放了一个“题目代码 ItemID”作为外键这其实是一个很大的简化。按需求描述试卷由“一定数量即知识点的数量的试题组成”一份试卷里面至少会有多道题直接在试卷表里放一个 ItemID 只能表示一份试卷挂一道题明显和“组成”这个词冲突。后面做物理模型时我会把试卷和试题的关系拆成一张关联表否则生成的数据库根本支撑不了组卷逻辑。2.2 关系和基数约束试题与试卷的“同知识点只出现一次”怎么在 E-R 图表达实体拆完不等于 E-R 图完成真正决定数据库能不能跑起来的是关系基数。这个实验里最容易理解错的关系有三个知识点与试题、试卷与试题、考试与试卷。先看知识点与试题。需求明确“一道试题只能考察一个知识点”所以这是典型的一对多关系一个知识点对应多道试题一道试题只属于一个知识点。外键一定要放在多的一端也就是试题库表里放 PointID。反过来在知识点表里放题目字段是错的那样一个知识点只能保存一道题完全跑偏。再看试卷与试题。前面已经说过试卷要包含多道试题而同一道试题在题库里只有一份所以从逻辑上讲是典型的多对多关系一份试卷包含多道题一个题目也可能被多份试卷选用。PowerDesigner 在概念模型里可以直接画“多对多关系”生成物理模型时它会自动换算出一张关联表关联表里放试卷代码和题目代码两个外键。如果实验里老师要求只建五个实体不加第六张表那么你至少要在试卷表里用“题目代码”做外键来应付报告但从模式设计的完整性出发我更建议保留关联表。“同一知识点的试题只能在一份试卷中出现一次”这个约束属于业务规则不是简单的外键能卡死的。数据库层面只能做到试卷试题关联表里每条记录的试卷代码题目代码唯一不能自动限制“两张题目不能属于同一知识点”。要在数据库里真正实现这个约束需要结合题目表带出知识点再写唯一索引或者触发器。我在实验报告里通常会把它写成一段文字说明E-R 图阶段用关系注释表达这个限制物理模型阶段暂不实现只保留在系统设计文档里。这个处理方式不是偷懒而是让实验聚焦在模式设计流程本身。考试与试卷的关系也很关键。需求说“教师指定某次考试使用的试卷学生参加考试使用统一的试卷”那么一次考试只能用一份试卷一份试卷可以被多次考试使用这是多对一关系多场考试对同一份试卷。所以考试管理表里要加一个 PaperID 外键指向试卷表。但原始实验文档列考试管理实体时只写了用户 ID 外键没有写试卷外键这就是一个明显的遗漏。如果你完全照抄文档生成物理模型考试记录就无法关联到具体试卷“拿到试卷”这个功能根本走不通。考试管理表里的 UserID 我理解为“参加考试的学生”。需求里允许学生提交答案后立刻得到成绩成绩还可能为空说明考试管理表其实承担了“考试字面信息”和“学生答卷成绩记录”的双重职责。严格建模应该把考试和成绩拆成两个实体但实验文档把两者合并在一个表里我们就按它的设计来EID 是考试代码Ename 是考试标题Etime 是考试时间Egrade 是学生成绩且允许为空UserID 是学生外键。这样一张表能查询出某次考试有哪些学生参加、谁还没出成绩虽然有点糙但满足实验步骤。最后是用户和角色的处理。需求有一个硬性条件“一个用户有且只有一种角色”所以用户表里的 Role 字段要加 CHECK 约束只允许 TEACHER 和 STUDENT 两种值。很多同学会用默认值或者干脆不约束这在实验评分时容易被扣分因为需求里白纸黑字写了“唯一角色”说明设计者要有意识地用约束去实现它。E-R 图阶段可以在 Role 属性下标注“单一取值”生成物理模型后转成 CHECK 即可。3. 用 Power Designer 把 E-R 图变成物理模型从概念模型到 DB2 脚本3.1 概念模型CDM的建模顺序与命名规范用 PowerDesigner 做实验开箱后先建 Conceptual Data Model。我习惯的操作顺序是先把五个实体画出来属性全部填完再拉关系线。如果先拉线后补属性后面返回去改实体时关系上的外键映射经常不跟着刷新又要重新生成物理模型。实体命名尽量采用英文避免中文表名直接落到 DB2 里。DB2 v8.1 在 Windows 上支持中文标识符但 SQL 脚本文件如果编码不对很容易在导入时乱码。保守做法是全用英文大写或 PascalCase。属性名也一样UserID 就用 UserIDEname 就用 Ename。PowerDesigner 的默认显示不区分大小写但生成脚本时可以统一转大写。每个实体都要先在 CDM 里把主标识符Primary Identifier定好。用户实体定 UserID知识点定 PointID试题库定 ItemID试卷定 PaperID考试管理定 EID。主键列在“Identifiers”选项卡里勾选不要在属性面板里随手设个“Primary”完事。后续生成物理模型时主标识符会直接变成主键约束如果这一步漏了生成的 PDM 里所有表都没有主键DB2 的 UPDATE 和 DELETE 会变得非常难写。在设计属性时最好先定义几个 Domain 统一下数据类型。PowerDesigner 的 Domain 相当于一套自定义类型ID 固定用 Integer名称固定用 VARCHAR(50)题目内容这种长文本固定用 VARCHAR(500)。这样后续新建实体时直接复用 Domain生成的物理模型也会保持一致不会出现用户表的 UserID 是 Integer、考试表的 UserID 变成 BigInt 这种低级问题。实验文档不要求 Domain但按这个习惯走一遍能省掉后面改外键列类型的麻烦。3.2 生成物理模型PDM与 DB2 v8.1 的适配CDM 画完后在菜单栏选 Tools → Generate Physical Data Model新版 PowerDesigner 会弹出一个选择框DBMS 列表里要选 IBM DB2 UDB for Linux, Unix, and Windows 8.x。这一步选错 DBMS后面生成的 SQL 语法会和实验环境不匹配比如把数据类型生成成 SQL Server 的 NVARCHARDB2 导入直接报语法错误。选择 DB2 8.x 后PowerDesigner 会自动做一层类型映射。常见映射有以下这些CDM 里的数据类型生成的 DB2 v8.1 类型说明IntegerINTEGER主键、外键都推荐用这个Variable characters (n)VARCHAR(n)名称、内容、密码等文本Decimal(p,s)DECIMAL(p,s)成绩、分值等小数Date TimeTIMESTAMP考试时间BooleanSMALLINT如果需要布尔字段DB2 没有原生 BOOLEAN生成 PDM 时还要注意外键的命名。PowerDesigner 默认会生成类似“FK_TABLE_REFERENCE_TABLE”的长名字这在 DB2 里没有太大问题但后续反向工程时名字太长不好认。我一般会在 PDM 里手工把外键约束重命名为“FK_子表名_父表名”例如 FK_EXAM_USER、FK_EXAM_PAPER。这样执行 SQL 后用系统目录查约束时一眼就能看出谁引用谁。多对多关系在 PDM 里会体现成一张关联表。如果在 CDM 中试卷与试题画的是多对多生成 PDM 后会自动出现 PAPER_ITEM 表字段就是 PaperID 和 ItemID 两个外键组成的联合主键。这个自动生成过程不需要你手写代码但你要能认出来。很多同学看到模型里凭空多了一张表就以为是误操作给删了删完之后试卷的题量就变成只能挂一条这是整个实验里最容易踩的坑之一。如果你不希望它自动生成也可以手动把关系改为两个一对多但我不建议这么改因为自动生成的行为才是更标准的多对多建模方式。3.3 导出 SQL 脚本和两个关键视图的定义PDM 整理好之后选 Database → Generate Database目标 DBMS 同样选 DB2 UDB 8.x。这一步会生成一个 SQL 脚本里面通常包含建表、主键、外键、索引和部分约束。生成前要在 Options 里把“生成外键”勾上否则脚本里只有建表语句没有 ALTER TABLE ADD FOREIGN KEY表之间的引用关系就丢了。PowerDesigner 里视图不是从 CDM 直接转换过来的CDM 阶段一般不画视图视图要在 PDM 里手工创建。你在 PDM 工作区的模型树里找到 View 分组右键新建视图填入视图名称后再打开它的 Query 选项卡编写 SELECT 语句。这个 SELECT 可以手动写也可以点击打开 Query Builder 拖表生成。实验要求建两个视图考试信息视图和在线试卷视图。考试信息视图我通常命名为 EXAM_INFORMATION目的是给学生提供历年考试时间、科目、成绩记录。在线试卷视图命名为 REAL_PAPER目的是给正在参加考试的学生展示试卷内容包括题目、选项、所属知识点。这里有一个设计细节在线试卷视图不应该把正确答案包含进去否则学生在答题前就能从接口里查到答案安全性上完全说不通。原始文档里题目有一个正确答案属性但视图生成时我会在 SQL 查询里把 Ianswer 字段排除掉只保留题号的题目内容、选项和知识点信息。导出 SQL 后建议先别急着在 DB2 里执行把脚本用文本编辑器打开重点看三处表名是否和 PDM 一致外键是否有命名CREATE VIEW 语句是否出现在建表之后。我见过不少同学导出的视图语句跑到了建表语句前面结果 DB2 执行时报“未找到表或视图”原因就是脚本里对象顺序和依赖关系不对。4. 生成表和视图的 SQL 落地脚本怎么改才能在 DB2 里跑通4.1 五张表的 DDL 结构与外键关系PowerDesigner 生成的脚本往往比较啰嗦有 DROP TABLE、CREATE TABLE、ALTER TABLE 外键约束等一大段。为了讲清楚结构我会把核心建表语句整理成下面这版精简 DDL。你自己在实验里可以不完全照抄但对照它能看出模式和表之间的关系。CREATE TABLE USER_INFO ( UserID INTEGER NOT NULL, UserName VARCHAR(50) NOT NULL, Role CHAR(10) NOT NULL, Password VARCHAR(64) NOT NULL, CONSTRAINT PK_USER_INFO PRIMARY KEY (UserID), CONSTRAINT CK_USER_ROLE CHECK (Role IN (TEACHER, STUDENT)) );这里把表名从 USER 改成 USER_INFO是因为 USER 在 DB2 里是系统保留字直接执行 CREATE TABLE USER 大概率会报语法错误。Role 字段用 CHAR(10) 而不是 VARCHAR因为教师和学生两种角色长度固定CHAR 更紧凑同时加上 CHECK 约束来实现“一个用户只有一种角色”。CREATE TABLE KNOWLEDGE_POINT ( PointID INTEGER NOT NULL, Pcontent VARCHAR(200) NOT NULL, Psubject VARCHAR(100), CONSTRAINT PK_KNOWLEDGE_POINT PRIMARY KEY (PointID) );知识点表本身没有外键Psubject 代表学科在实验里可以理解为课程名称。Pcontent 是知识点内容比如“数据库事务 ACID”。CREATE TABLE ITEM_BANK ( ItemID INTEGER NOT NULL, Icontent VARCHAR(500) NOT NULL, Iscore DECIMAL(5,2) NOT NULL, Ioption VARCHAR(500) NOT NULL, Ianswer VARCHAR(10) NOT NULL, PointID INTEGER NOT NULL, CONSTRAINT PK_ITEM_BANK PRIMARY KEY (ItemID), CONSTRAINT FK_ITEM_POINT FOREIGN KEY (PointID) REFERENCES KNOWLEDGE_POINT (PointID) );试题库表就是题库的核心。Iscore 用 DECIMAL(5,2) 而不是 INTEGER因为分值可能是 2.5 分这种带小数的设计。Ianswer 只存一个正确答案标识比如选项 A。这里外键 PointID 引用知识点表保证每道题都必须归属到一个真实存在的知识点否则组卷时按知识点分组就无从谈起。Ioption 字段用来放整个单选题选项的拼接文本可以用分隔符从 A 到 D 一次存完实验粒度不需要单独做一张选项表。CREATE TABLE PAPER ( PaperID INTEGER NOT NULL, PaperName VARCHAR(100) NOT NULL, CONSTRAINT PK_PAPER PRIMARY KEY (PaperID) ); CREATE TABLE PAPER_ITEM ( PaperID INTEGER NOT NULL, ItemID INTEGER NOT NULL, CONSTRAINT PK_PAPER_ITEM PRIMARY KEY (PaperID, ItemID), CONSTRAINT FK_PAPER_ITEM_PAPER FOREIGN KEY (PaperID) REFERENCES PAPER (PaperID), CONSTRAINT FK_PAPER_ITEM_ITEM FOREIGN KEY (ItemID) REFERENCES ITEM_BANK (ItemID) );这里我用 PAPER_ITEM 关联表实现试卷与试题的多对多关系。联合主键是PaperID, ItemID能保证同一道题在试卷里不会重复录入但注意它不能保证“同一知识点的不同题目不重复出现”这两件事是完全不同的约束实验里不要混淆。CREATE TABLE EXAM_MANAGEMENT ( EID INTEGER NOT NULL, Ename VARCHAR(100) NOT NULL, Etime TIMESTAMP, Egrade DECIMAL(5,2), UserID INTEGER NOT NULL, PaperID INTEGER NOT NULL, CONSTRAINT PK_EXAM_MANAGEMENT PRIMARY KEY (EID), CONSTRAINT FK_EXAM_USER FOREIGN KEY (UserID) REFERENCES USER_INFO (UserID), CONSTRAINT FK_EXAM_PAPER FOREIGN KEY (PaperID) REFERENCES PAPER (PaperID) );考试管理表里 Etime 用 TIMESTAMP记录的是学生参加考试的时间点。Egrade 允许为空对应“学生还没交卷或还没出成绩”的状态这样设计才能区分未考试和考了零分两种不同情况。UserID 指向学生PaperID 指向本次考试使用的试卷这两个外键合起来才能表达“某学生参加了某份试卷对应的考试”。4.2 两个视图的定义和用途考试信息视图的定位是学生历史考试成绩查询。我不想把它写成复杂的多表连接因为原始文档里的考试管理表本身已经包含了学生 ID、考试名称、时间和成绩直接过滤掉 NULL 成绩就能得到有效历史记录。CREATE VIEW EXAM_INFORMATION ( ExamCode, SubjectName, ExamTime, Grade, UserID ) AS SELECT EID, Ename, Etime, Egrade, UserID FROM EXAM_MANAGEMENT WHERE Egrade IS NOT NULL;这个视图里的 Grade 就是 EgradeSubjectName 其实不严谨地借用了 Ename 字段。真正严格的系统应该有科目表但实验里课程只有一门所以这种做法可以接受。过滤条件 WHERE Egrade IS NOT NULL 是最关键的一点它把还没出成绩的记录排除掉免得学生在历史记录里看到一堆空成绩。在线试卷视图要同时关联考试管理、试卷、试卷试题关联表和试题库因为一份在线试卷必须能告诉学生本次考试试卷标题、包含哪些题、每个题的选项是什么。CREATE VIEW REAL_PAPER ( EID, PaperID, PaperName, ItemID, Icontent, Ioption, PointID ) AS SELECT em.EID, em.PaperID, p.PaperName, pi.ItemID, ib.Icontent, ib.Ioption, ib.PointID FROM EXAM_MANAGEMENT em JOIN PAPER p ON em.PaperID p.PaperID JOIN PAPER_ITEM pi ON p.PaperID pi.PaperID JOIN ITEM_BANK ib ON pi.ItemID ib.ItemID;视图列里没有 Ianswer这是特意去掉的。如果教师需要阅卷评分可以另外建一个带正确答案的视图或者直接查 ITEM_BANK 表。把正确答案暴露给学生的在线试卷视图是设计事故哪怕只是一个实验也必须养成这种安全意识。这里的四个 JOIN 每一步都有明确作用第一个 JOIN 把考试关联到试卷第二个 JOIN 从试卷取题目关系第三个 JOIN 从关系中拿到题目的实际内容缺一环试卷就拼不完整。4.3 执行脚本后的验证用系统目录查表结构在 DB2 v8.1 里执行脚本我推荐在命令行里用db2 -tvf schema.sql-t表示以分号作为语句终止符-v表示回显每条正在执行的 SQL-f指定脚本文件。这样哪条语句报错能立刻定位到对应行号。把前面的建表和视图语句按顺序存到一个文件里执行完成后不要急着通过命令行客户端乱翻直接查系统目录表来验证。SELECT TABNAME, TYPE FROM SYSCAT.TABLES WHERE TABSCHEMA CURRENT SCHEMA ORDER BY TABNAME;这条查询返回当前模式下的所有表和视图。SYSCAT.TABLES 里 TYPE 为 T 表示表V 表示视图。正常情况你至少能看到 USER_INFO、KNOWLEDGE_POINT、ITEM_BANK、PAPER、PAPER_ITEM、EXAM_MANAGEMENT 六张 T 记录以及 EXAM_INFORMATION、REAL_PAPER 两条 V 记录。如果少了 PAPER_ITEM说明生成 PDM 时多对多关系没有成功转换需要回去检查 CDM。SELECT COLNAME, TYPENAME, LENGTH, NULLS FROM SYSCAT.COLUMNS WHERE TABNAME EXAM_MANAGEMENT ORDER BY COLNO;这条查询用于核对列定义。重点看 Egrade 的 NULLS 是否为 Y如果变成 N说明你给成绩字段加了 NOT NULL会导致没参加考试的学生无法写入记录。还要看外键列 UserID 和 PaperID 的数据类型是否与其他表的主键类型一致。DB2 对外键列类型不匹配常常不报错但后续 JOIN 时会出现效率问题甚至类型转换异常这种坑非常隐蔽。SELECT CONSTNAME, TABNAME, REFTABNAME FROM SYSCAT.REFERENCES WHERE TABSCHEMA CURRENT SCHEMA ORDER BY CONSTNAME;最后验证外键。SYSCAT.REFERENCES 里保存了每个外键约束的源表和引用表。核对一下 FK_EXAM_USER 是不是从 EXAM_MANAGEMENT 指向 USER_INFOFK_EXAM_PAPER 是不是从 EXAM_MANAGEMENT 指向 PAPER。视图底层引用了哪些表也能通过这条已知关系去推导。如果某张表的外键消失了最常见的原因是生成脚本时勾掉了外键选项或者在建表前先删掉了约束。5. 数据库模式设计避坑五种最常见的实验翻车现场5.1 外键顺序导致建表失败SQLCODE -204现象执行 PowerDesigner 导出的脚本时前几条 CREATE TABLE 都成功到某一条突然报 SQLCODE -204提示“表或视图不存在”。我见过最多的是在创建 ITEM_BANK 表时引用 KNOWLEDGE_POINT但脚本里还没创建知识点表。原因PowerDesigner 生成脚本时表顺序来自 PDM 里对象的创建顺序而不是依赖关系顺序。如果你先画了试题库再画知识点脚本导出的顺序就会把 ITEM_BANK 排在前面。DB2 建表带外键引用时被引用表还没有建立自然报表不存在。解决不要靠手拖脚本顺序最稳妥的办法是把所有表先建完再统一加外键约束。也就是把 CREATE TABLE 放前面把 ALTER TABLE ADD FOREIGN KEY 放脚本最后。这个习惯我后来一直在用不管是什么建模工具导出的脚本都先做这一步整理能绕开大部分建表顺序坑。5.2 PowerDesigner 生成类型和保留字冲突SQLCODE -104现象用户表建不出来只要 SQL 里出现 USER 这个表名就报 SQLCODE -104 或 -199错误信息指向表名附近。原因USER 是 DB2 内置函数和特殊寄存器名称在没有加双引号的情况下会被当成关键字解析。PowerDesigner 从 CDM 生成 PDM 时不会自动帮你判断目标数据库保留字它只会按照实体名称原样生成。解决表名改成 USER_INFO、APP_USER 或 T_USER 之类字段也要避开 TIME、DATE、ROLE 等容易撞车的词。另外一个相关点是数据类型如果 CDM 里字段没给长度DB2 的 VARCHAR 会因为没有长度参数而报语法错误。对策是在 CDM 阶段每一个文本属性都显式设置长度不要依赖默认值否则生成脚本后在 DB2 里改起来更费劲。5.3 多对多关系没生成关联表试卷变成单题试卷现象执行完脚本往 PAPER 表里插入数据时发现只有一列 ItemID一张试卷只能关联一道题。组卷要求一个知识点出一道题结果一份卷子只有一题。原因在 CDM 里把试卷与试题画成了一对一或一对多关系导致 PowerDesigner 认为多的一段只在 PAPER 表放外键即可。这种错误通常发生在没有理解题意的时候以为“试卷包含题目”就是试卷表里加一个题目字段。解决回到 CDM把试卷和试题的关系改成多对多重新生成物理模型PowerDesigner 会自动产出 PAPER_ITEM 关联表。如果你已经把 CDM 删了或者来不及改也可以手动建 PAPER_ITEM 表并补两条外键。这里我要强调一点自动生成的关联表名可能很乱比如 PAPER_ITEM_BANK 之类最好手动在 PDM 里重命名为 PAPER_ITEM不然实验报告的表格不美观后面写 SQL 也很别扭。5.4 视图里出现大量 NULL 成绩历史记录全是空行现象EXAM_INFORMATION 视图创建成功后用学生账号查询发现每个考试标题都有记录但成绩全是空的根本分不清哪些考过、哪些没考过。原因直接在 EXAM_MANAGEMENT 表上建视图时没有过滤 Egrade而 Egrade 允许为空未交卷的考生记录也会被视图查出来。这属于查询条件没有匹配业务语义。解决在视图定义里过滤掉成绩为空的行或者更好的做法是把“考试信息”和“成绩记录”分开建模。如果只是交实验把 WHERE Egrade IS NOT NULL 加上就够交差了。但如果将来做真实系统我会把 EXAM_MANAGEMENT 拆成 EXAM 和 EXAM_SCORE 两张表考试表存考试标题、考试时间、试卷、任课教师成绩表存学生 ID、考试 ID、分数、交卷状态。这样视图查询才不用靠 NULL 去猜状态。5.5 换达梦数据库时的模式错误schema 与用户名不匹配现象把同一套脚本拿到达梦数据库里执行建表语句报“模式错误”或“模式 [XXX] 不存在”但同样脚本在 DB2 里跑得好好的。原因和 DB2 的 CURRENT SCHEMA 机制类似达梦默认把用户名作为模式名如果当前连接用户是 SYSDBA 或没有同名模式脚本里又没有 SET SCHEMA 指向实际业务模式CREATE TABLE 就会失败。PowerDesigner 生成的脚本里如果带上了创建者的 AUTHORIZATION 信息到另一个数据库实例里也容易出现模式错位。解决在脚本开头显式声明模式比如SET SCHEMA DBUSER;或者把脚本里的AUTHORIZATION子句去掉。网络上关于“达梦数据库 模式错误”的提问大半都是这一类原因。遇到这个报错第一反应不应该是改表结构而是检查当前连接用户、目标模式和脚本里的模式名是否三者一致。这个经验放到国产数据库迁移场景里同样适用模式名是数据库对象的第一层命名空间错一个字符都找不到对象。6. 验证模式设计是否合格用系统查询和一个小技巧6.1 建立一套可重复跑的验收清单模式设计到底做没做对不能只靠眼睛看 E-R 图要落到可查询的数据库对象上。我会用一条 SQL 一组结果的方式做验收逻辑是这样的先确认对象齐全再确认列定义正确最后确认约束生效。SELECT TABNAME, TYPE FROM SYSCAT.TABLES WHERE TABSCHEMA CURRENT SCHEMA ORDER BY TABNAME;备选的一组“预期”应当是六张 T 表和两张 V 表。这一步达成后做一次插入测试插入一个教师用户、一个知识点、两道题、一份试卷、一次考试然后查询 REAL_PAPER 视图看看能不能把对应题目带出来。如果题目数量对不上多半是 PAPER_ITEM 没有成功插入或者视图 JOIN 字段写错。数据能读出来之后再验证约束是否真的工作。试着往 USER_INFO 里插入 Role 为 ADMIN 的记录正确结果是被 CK_USER_ROLE 拦住试着插入不存在的 UserID 到 EXAM_MANAGEMENT正确结果是被 FK_EXAM_USER 拦住。如果数据库没有拦住说明外键没有生效后续业务层就要承担大量本来数据库该管的事情。SELECT CONSTNAME, TABNAME, REFTABNAME FROM SYSCAT.REFERENCES WHERE TABSCHEMA CURRENT SCHEMA;另外一个实验里值得做的验证是视图权限。DB2 v8.1 里用户要能查视图至少需要视图所属模式的 EXECUTE 或原表的 SELECT 权限。很多学生在自己账号下建的表自己查没问题换一个账号登录就提示没有权限然后以为视图坏了其实只是授权没做。实验报告里把权限语句写上比如给课程账号授权查询两个视图能多拿不少印象分。6.2 用反向工程把数据库拉回 PDM 做一致性核验最后分享一个我后来一直保留的习惯做完整套实验后把数据库里的实际结构反向工程回 PowerDesigner和原来设计的 PDM 对比一遍。具体操作是在 PDM 界面选择 Database → Reverse Engineer Database数据库类型选 IBM DB2 UDB 8.x然后配置 ODBC 数据源连接到你的实验数据库。连上之后PowerDesigner 会读取当前模式下的表、列、主键、外键、视图反向生成一个基于数据库实际状态的物理模型。把这个反向生成的模型和当初正向设计的模型放在一起逐表比较字段、约束和关系。这个习惯的价值在于能发现两类正向设计里看不见的问题一类是执行脚本时人为修改了表名或列名导致模型和真实库不一致另一类是漏掉了外键视图 JOIN 却引用到了这个列数据能跑通但关系不完整。反向工程出来的模型才是数据库真正的模式原来的 PDM 只是愿景两者一旦对不上说明设计过程中有一处“模式漂移”没有被发现。从那以后我每次做完数据库模式设计不管交不交实验报告都会强制走一遍反向工程把“设计模型”和“物理库模型”晒在一起对比半小时。外键名称对不上、类型不一致、视图少过滤条件这些问题都能在对比时原形毕露。用这套方法重做这个在线考试系统实验你的模式和脚本才真正经得起数据库本身的检验希望帮到你。本文还有配套的精品资源点击获取