北邮数据库实验：在线考试系统模式设计与Power Designer实战

简介这份文档是北邮数据库实验四「数据库模式的设计」的完整实验报告面向正在学习数据库课程、需要完成模式设计实验的高校学生。内容围绕在线考试系统展开覆盖需求分析、E-R图构建、逻辑模式与物理模式转换以及在DB2中创建表和视图的全过程适合作为课程实验参考与设计思路梳理。资源包共1个doc文件约1.56MB为Word格式的实验报告文档便于直接查阅与对照。目前已有126人学习下载。报告详细列出了用户、试题库、知识点、试卷、考试管理等实体的属性与关系并给出概念模型、物理模型及考试信息、在线试卷两个视图的创建思路还涉及Power Designer建模与SQL脚本生成等关键环节能帮助读者理解数据库设计的完整流程掌握E-R图到物理模型的转换方法并熟悉视图与数据完整性的实际应用。1. 从一份北邮数据库实验报告说起在线考试系统的模式设计到底在练什么如果你手头正躺着一份《北邮数据库实验四-数据库模式的设计.doc》大概率你面对的不是“看不懂理论”而是“知道要画 E-R 图、要建表建视图但真打开 Power Designer 就不知道第一步点哪里”。这份实验报告的核心是围绕一个在线考试系统把需求分析、E-R 图、概念模型、物理模型、SQL 脚本、DB2 建表建视图串成一条完整链路。它适合正在做数据库课程设计的学生也适合想借一个真实业务场景把 Power Designer 和 SQL 建表重新走一遍的从业者。说白了它练的不是背概念而是把“教师出题、学生考试、系统判分”这套业务翻译成数据库能落地的表和视图。2. 在线考试系统的实体与关系先把需求拆成五张表2.1 从业务描述里抠出实体和属性实验给的需求信息很长但真正要落库的实体其实就五个用户、试题库、知识点、试卷、考试管理。很多同学一上来就急着开 Power Designer结果画到一半发现字段对不上又回头改需求来回折腾。我的习惯是先在纸上把每个实体的属性列清楚再进工具。用户实体承担登录和角色区分。一个用户有且只有一种角色所以角色字段放在用户表里就够了不需要单独拆角色表。字段包括用户 ID、用户名、角色、密码。这里有个容易忽略的点用户 ID 是主键角色只区分教师和学生两种值用字符或小整数都行但别为了“规范”硬拆成角色表实验阶段没必要。试题库实体是在线考试系统的核心。题目代码是主键题目内容、分数、选项、正确答案、知识点代码都是必备属性。因为试题全是单项选择选项可以存成一个字符串也可以拆成 A/B/C/D 四列实验报告里给的是一列选项那就按一列走。正确答案存选项标识即可。知识点代码是外键指向知识点表。知识点实体相对独立。知识点代码是主键知识点内容和知识点学科是描述属性。需求里明确说“课程的知识点是确定的可以扩展”这意味着知识点表是基础数据表试题通过外键引用它而不是把知识点内容冗余进试题表。试卷实体稍微特殊。试卷代码是主键试卷名称是描述题目代码是外键。但需求里有一句关键约束“一样知识点的试题只能在一试卷中出现一次”。这句话翻译成数据库语言就是试卷和试题之间虽然是一对多但同一试卷内不能出现相同知识点的题目。这个约束在纯表结构里不好直接表达通常靠应用层或存储过程保证实验阶段先在建表时把外键建好心里要清楚这个业务规则。考试管理实体把试卷、教师、学生、成绩串起来。考试名称、考试代码、考试时间、学生成绩、用户 ID 是主要字段。学生成绩可空因为考试还没结束时成绩不存在。用户 ID 是外键指向用户表。把这五个实体列成一张对照表进 Power Designer 之前先核对一遍能省掉大量返工。实体主键外键关键属性用户UserID无UserName、Role、Password知识点PointID无Pcontent、Psubject试题库ItemIDPointIDIcontent、Iscore、Ioption、Ianswer试卷PaperIDItemIDPapernName考试管理EIDUserIDEname、Etime、Egrade2.2 实体之间的关系怎么定实体列完接下来是关系。在线考试系统里有几条关系必须理清否则 E-R 图会画歪。用户和考试管理是一对多。一个教师可以安排多场考试一个学生可以参加多场考试所以用户表的主键在考试管理表里作为外键出现。这里要注意教师和学生都是用户考试管理表里的 UserID 到底指向教师还是学生需求里没有拆得很细。常见做法是考试管理表里同时保留教师 ID 和学生 ID 两个外键或者用角色字段区分。实验报告里只给了一个 UserID那就按一个外键处理但在设计说明里要写清楚这个字段的业务含义。知识点和试题库是一对多。一个知识点可以对应多道试题一道试题只考察一个知识点。所以知识点表的主键 PointID 在试题库表里作为外键。试题库和试卷是一对多。一道试题可以出现在多张试卷里一张试卷包含多道试题。试卷表里的 ItemID 是外键指向试题库。但前面提到的“同一知识点试题不能在同一试卷重复”这个约束在 E-R 图层面表达不出来需要在物理模型或应用逻辑里补。试卷和考试管理是一对多。一张试卷可以用于多场考试一场考试使用一张试卷。考试管理表里应该有 PaperID 作为外键但实验报告里考试管理表的字段列表没有明确列出 PaperID只列了考试名称、考试代码、考试时间、学生成绩、用户 ID。这是一个需要留意的地方如果考试管理表不关联试卷那“教师指定某次考试使用的试卷”这个需求就落不了地。稳妥的做法是在考试管理表里补一个 PaperID 外键或者在实验分析里说明这个字段的缺失。关系理清后E-R 图的骨架就出来了。五个实体四条主要关系画的时候用矩形表示实体椭圆表示属性菱形表示关系连线标注基数。Power Designer 里对应的操作是新建 Conceptual Data Model然后从工具面板拖实体和关系。3. Power Designer 从概念模型到物理模型转换步骤与参数设置3.1 新建概念模型并录入实体打开 Power DesignerFile → New Model → Conceptual Data Model。模型建好后工具面板里会有 Entity、Relationship、Inheritance 等图标。双击空白处或点 Entity 图标再点画布就能创建一个实体。创建实体后双击实体打开属性窗口。General 标签页填 Name 和 CodeCode 是物理模型里的表名建议用英文比如 User、KnowledgePoint、ItemBank、Paper、ExamManagement。Attributes 标签页里逐条加属性每条属性填 Name、Code、Data Type。Data Type 可以先选 Variable characters 或 Integer具体长度到物理模型再调。这里有个血泪经验概念模型阶段不要把数据类型卡得太死。很多同学在概念模型里就把 varchar 长度写成 20结果到物理模型发现密码字段不够用又回头改。概念模型关注的是实体和属性本身类型和长度是物理模型的事。外键在概念模型里通过 Relationship 表达不需要手动加外键属性。比如知识点和试题库是一对多就从知识点拖一条 Relationship 到试题库Power Designer 会自动在试题库实体里生成一个指向知识点主键的外键属性。如果手动加外键属性转换到物理模型时会出现重复字段。3.2 概念模型转物理模型的关键选项概念模型画完Tools → Generate Physical Data Model。弹窗里有几个关键选项DBMS 选 IBM DB2 v8.1因为实验环境就是这个版本。如果选错 DBMS生成的 SQL 语法会有差异比如自增列、数据类型名称都不一样。Name 和 Code 的转换规则要注意。概念模型里的实体 Code 会直接变成物理模型的表名属性 Code 变成列名。如果概念模型里用了中文 Code物理模型里也会是中文DB2 虽然支持但后续写 SQL 容易出问题。建议概念模型阶段就用英文 Code。Generate 按钮点下去后Power Designer 会新建一个 Physical Data Model里面是表、列、主键、外键的完整结构。这时候要逐张表检查主键有没有丢外键有没有重复数据类型合不合理。3.3 物理模型里补视图和约束物理模型里可以建视图。实验要求建两个视图考试信息视图和在线试卷视图。考试信息视图面向学生提供历年的考试时间、科目、成绩。这个视图需要关联考试管理表、试卷表、试题库表、知识点表。因为成绩在考试管理表里科目信息在知识点表里试卷名称在试卷表里所以视图的 SQL 大致是CREATE VIEW ExamInformation AS SELECT e.EID, e.Ename, e.Etime, e.Egrade, p.PapernName, k.Psubject FROM ExamManagement e JOIN Paper p ON e.PaperID p.PaperID JOIN ItemBank i ON p.ItemID i.ItemID JOIN KnowledgePoint k ON i.PointID k.PointID;这段 SQL 的逻辑是从考试管理表出发通过 PaperID 关联试卷表拿到试卷名称再通过 ItemID 关联试题库表最后通过 PointID 关联知识点表拿到学科信息。参数上要注意如果考试管理表里没有 PaperID 外键这个视图就建不起来所以前面提到的补外键这一步不能省。在线试卷视图面向考试中的学生提供当前试卷的题目内容、选项、分值。这个视图主要关联试卷表和试题库表CREATE VIEW RealPaper AS SELECT p.PaperID, p.PapernName, i.ItemID, i.Icontent, i.Ioption, i.Iscore FROM Paper p JOIN ItemBank i ON p.ItemID i.ItemID;这个视图的过滤条件通常由应用层传入比如 WHERE PaperID ?视图本身只负责把试卷和题目拼起来。如果要在视图里直接限制某张试卷可以把条件写死但那样视图就失去通用性了。物理模型里还可以设置主键、外键、非空约束、默认值。比如学生成绩可空就在 Egrade 列上不勾 Not Null。用户密码可以设默认值但实验阶段没必要。4. 导出 SQL 并在 DB2 中建表建视图执行顺序与常见报错4.1 从物理模型生成 SQL 脚本物理模型检查无误后Database → Generate Database。弹窗里选 Directory 和 File name生成 .sql 文件。Options 标签页里可以勾选 Generate create table、Generate create view、Generate foreign keys 等。实验要求生成表和视图所以这两项都要勾。生成的 SQL 文件里表的创建顺序很关键。Power Designer 通常会按依赖关系排序先建被引用的表再建引用表。比如知识点表先建试题库表后建因为试题库有外键指向知识点。如果顺序反了DB2 执行时会报外键约束错误。4.2 在 DB2 命令行执行脚本DB2 v8.1 的命令行工具是 db2cmd。打开后先连接数据库db2 connect to SAMPLE user db2admin using passwordSAMPLE 是数据库名db2admin 是用户名password 换成实际密码。连接成功后执行 SQL 脚本db2 -tvf D:\exam_system.sql-t 表示语句以分号结尾-v 表示回显执行的语句-f 指定文件路径。执行过程中如果某条语句报错命令行会停下来并显示错误码。常见的错误有SQL0204N对象不存在。通常是表创建顺序问题或者外键引用的表还没建。SQL0601N对象已存在。说明之前执行过一部分表已经建了。解决方法是先执行 DROP TABLE 或 DROP VIEW或者换一个干净的数据库。SQL0530N外键约束冲突。插入数据时引用了不存在的父表记录建表阶段一般不会遇到除非脚本里带了 INSERT 语句。4.3 验证表和视图是否建成功脚本执行完后用 DB2 命令查看SELECT TABNAME, TYPE FROM SYSCAT.TABLES WHERE TABSCHEMA DB2ADMIN;这条 SQL 从系统目录表 SYSCAT.TABLES 里查当前模式下所有表和视图。TYPE 列是 T 表示表V 表示视图。如果五个表和两个视图都在列表里说明建成功了。再查一下外键SELECT CONSTNAME, TABNAME, REFTABNAME FROM SYSCAT.REFERENCES WHERE TABSCHEMA DB2ADMIN;这条 SQL 查的是外键约束CONSTNAME 是约束名TABNAME 是子表REFTABNAME 是父表。确认试题库表指向知识点表、试卷表指向试题库表、考试管理表指向用户表的外键都在。视图的验证可以直接查SELECT * FROM DB2ADMIN.ExamInformation; SELECT * FROM DB2ADMIN.RealPaper;如果视图定义里有关联了不存在的字段这两条查询会直接报错比看建表语句快得多。5. 避坑与排查这份实验报告里最容易翻车的五个地方5.1 考试管理表缺 PaperID 导致视图建不起来现象生成考试信息视图时SQL 报错说 PaperID 列不存在。原因实验报告里考试管理表的字段列表没有明确列出 PaperID但视图需要关联试卷表。解决在物理模型里给考试管理表补一个 PaperID 外键指向试卷表的主键。或者在概念模型阶段就把试卷和考试管理的关系画上让 Power Designer 自动生成外键。5.2 概念模型里手动加外键导致物理模型重复列现象转换到物理模型后试题库表里出现两个 PointID 列一个来自手动添加一个来自 Relationship。原因概念模型里既画了 Relationship又手动加了外键属性。解决概念模型里只画 Relationship不手动加外键属性。如果已经加了删掉手动那个重新生成物理模型。5.3 DB2 脚本执行到一半报对象已存在现象执行 SQL 脚本时前面几张表建成功后面报 SQL0601N。原因之前执行过部分脚本表已经存在。解决在脚本开头加 DROP TABLE IF EXISTS 语句或者先手动删除所有表和视图。DB2 v8.1 不支持 DROP TABLE IF EXISTS需要先查 SYSCAT.TABLES 确认存在再删。5.4 视图查询报数据类型不匹配现象查 ExamInformation 视图时报 SQL0401N 数据类型不匹配。原因视图里 JOIN 的字段类型不一致比如一个表里 PointID 是 INTEGER另一个表里是 VARCHAR。解决在物理模型里统一关联字段的数据类型。Power Designer 生成物理模型时如果概念模型里属性类型不一致物理模型里也会不一致。回到概念模型改或者直接在物理模型里改列类型。5.5 生成的 SQL 在 DB2 里跑不通但语法看着没问题现象SQL 脚本在别的数据库能跑在 DB2 v8.1 报错。原因DB2 v8.1 对某些语法支持有限比如自增列、分页查询、字符串拼接函数都和 MySQL 不一样。解决生成物理模型时 DBMS 一定要选 IBM DB2 v8.1不要选通用 ODBC 或 MySQL。如果已经生成错了改 DBMS 重新生成。6. 进阶技巧用 Power Designer 反向工程验证建表结果实验做完表和视图都建好了但怎么确认物理模型和数据库里的实际结构完全一致我一般会用 Power Designer 的反向工程功能从 DB2 里把表结构导回来和原来的物理模型对比。这一步能抓出很多手工改脚本时留下的不一致。操作路径是 File → Reverse Engineer → Database。弹窗里选 DBMS 为 IBM DB2 v8.1然后配置连接。DB2 的连接需要指定数据库名、用户名、密码以及 ODBC 数据源。如果本机没配 ODBC可以在 Windows 的 ODBC 数据源管理器里新建一个 System DSN驱动选 IBM DB2 ODBC DRIVER填上数据库别名。连接成功后Power Designer 会列出所有表和视图勾选需要的对象点 OK。反向工程生成的物理模型会出现在新的 Diagram 里。这时候把反向生成的模型和原来的物理模型并排看重点对比三处列的数据类型和长度、主外键约束、视图定义。如果发现不一致比如数据库里某列是 VARCHAR(50)物理模型里是 VARCHAR(30)说明生成 SQL 后有人手动改过脚本。这种不一致在后续开发里会埋雷比如应用层按 30 长度校验数据库却允许 50数据截断或报错都可能在线上才暴露。反向工程还有一个用处当实验报告里的 SQL 脚本丢失或损坏时可以直接从数据库反向生成不用重新画 E-R 图。我带的几个做课程设计的学生最后交报告前都会走一遍反向工程确认数据库里的表和视图跟文档里写的一致。从那以后我每次做完数据库实验都强制走一遍反向工程再交省得答辩时被问“你这个视图到底建没建”答不上来。希望帮到你。本文还有配套的精品资源点击获取