简介这是一份聚焦数据库概念设计的教学PPT对应《数据库系统概论》中实体-联系模型与ER图章节适合正在学习数据库原理的高校学生、备考复习者以及需要快速理解ER建模的入门者。内容从信息世界的基本概念讲起系统梳理实体、属性、码、域、实体型、实体集与联系的定义重点解析两个及以上实体型之间的一对一、一对多、多对多联系并通过班级-班长、课程-学生、供应商-项目-零件等经典示例演示映射基数的判定和E-R图绘制规则能帮助读者将抽象数据关系转化为规范的概念模型。资源包仅含1个PPT文件总大小622KB便于下载后直接阅读或打印。当前已有411人学习使用口碑与实用性已得到初步验证。对于正在准备数据库考试或课程设计的人来说这份课件能有效补齐实体联系建模的薄弱环节为后续关系模式转换与物理设计打下扎实基础。1. ER 图不是课堂作业实体-联系模型依然是数据库系统的沟通基线一个反直觉的经验工作五年以上的后端工程师平时可能很少亲手画 ER 图但一旦要评审新系统、核对存量表结构或给业务方解释字段关系他们最先要找的还是那张实体-联系模型图。本标题来自“数据库系统概论——实体-联系模型、ER图”听起来是大学课件但其中涉及的思考方式直接影响建表质量、查询性能和团队协作效率。这篇文章要讲清楚的正是实体-联系模型解决什么问题、ER 图怎么画、主键和外键如何体现在图上、从图到 SQL 的映射有哪些坑以及怎样利用现有数据库反向核对 ER 图。无论你正在准备数据库系统原理考试还是在维护一张混乱的业务大宽表这套方法都能派上用场。2. 实体-联系模型的三个核心元素与建模纪律先回答一个最常见的问题什么是数据库 ER 图。一句话说以实体、属性和联系为基本元素来表达信息结构的图就叫实体-联系图也就是 ER 图。很多人会把它和 UML 类图混淆但二者出发点不同类图面向对象实现ER 图面向数据存储与分析。在数据库系统概论课程里ER 建模被放在最前面是因为它不依赖具体数据库产品先把业务语义定清楚后面无论是 MySQL、Oracle 还是 PostgreSQL都只是语法翻译的问题。2.1 实体、属性、联系的符号与语义实体是现实世界中可独立存在的对象比如“学生”“课程”“银行账户”属性是实体的特征比如学生的“学号”“姓名”“所属学院”联系是实体之间的关联比如“学生选修课程”中的“选修”。ER 图上的常规画法用矩形表示实体椭圆形表示属性菱形表示联系而主键属性会在文本下方加一道下划线。在数据库系统概论第六版这类教材里通常还会区分强实体和弱实体。强实体有自己的主键弱实体只能靠父实体的主键加上自身部分键来标识。一个经典例子是银行储蓄系统中的“账户流水”流水号在单个账户下递增脱离账户没有意义所以它被建模成弱实体。把这个概念落到 SQL 中弱实体往往对应联合主键或复合外键这是后续建表最容易出错的地方。2.2 基数约束1:1、1:N、M:N 怎么选识别出实体之后下一步是定联系的数量关系也就是基数。ER 图上的基数用一条连接线上的 1、N、M 标记表示现代工具如 PowerDesigner 会用 crows foot 符号。基数选错后面的表结构一定是错的。基数业务特征建表策略1:1每侧实体最多对应另一侧一个实例任选一侧表加外键并加唯一约束或直接共用主键1:N一侧每个实例对应另一侧多个实例在 N 侧表加外键引用另一侧主键M:N两侧都能对应多个实例必须建关联表关联表中存两个外键可使用联合主键我们拿教学管理系统 ER 图举例。学院和教师之间是 1:N一个学院有多名教师教师表里放“学院ID”外键。学生和课程之间是 M:N一个学生选多门课一门课有多名学生选修那就需要一张选课表。选课表里同时放学生ID和课程ID两个字段共同组成主键或者再加一个学期字段组成联合主键避免同学期重复选课的伪数据。2.3 从需求到 ER 图的建模步骤我一般把这些步骤固定下来避免想到哪画到哪先列出需求中的名词筛掉纯状态词、操作词得到候选实体。给每个实体补属性并圈出能唯一标识该实体的属性或属性组合作为主键。在实体之间画菱形先画二元联系再处理三元或自关联。给每一条连接线标基数并用业务规则验证。检查是否有弱实体、派生属性、多值属性。派生属性如“年龄”由出生日期推导出来不要直接画成普通属性多值属性如“联系方式”应当拆成子实体或单独表。确认概念模型正确后再翻译成物理表结构。一套典型的教学管理 ER 图映射成 SQL 后核心表大致如下CREATE TABLE student ( student_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, dept_name VARCHAR(100) ); CREATE TABLE course ( course_id INT PRIMARY KEY, title VARCHAR(100) NOT NULL, credit DECIMAL(2,1) ); CREATE TABLE course_selection ( student_id INT NOT NULL, course_id INT NOT NULL, semester VARCHAR(20) NOT NULL, grade DECIMAL(3,1), PRIMARY KEY (student_id, course_id, semester), FOREIGN KEY (student_id) REFERENCES student(student_id), FOREIGN KEY (course_id) REFERENCES course(course_id) );这里的course_selection就是 M:N 联系“选修”落地的关联表。主键同时包含了semester原因是同一学生同一课程在不同学期可能重修保留历史成绩才合理。两个外键分别指向学生和课程确保不会出现选课记录引用了不存在的学生。整个结构里ER 图中的菱形联系被翻译成了一张物理表。3. 绘制 ER 图工具选择、主键表示与 SQL 反向生成知道了“ER 图怎么画”的理论接下来要在工具中落实。不要执着于某个特定工具重要的是工具是否能表达实体名、属性名、主键、外键、基数和弱实体这五类信息。哪怕是白板手绘只要符号一致也能承担评审职能。3.1 工具选型PowerDesigner 与在线工具的取舍点PowerDesigner 是传统企业里比较常见的建模工具也是老牌的数据建模产品适合需要输出完整设计文档、涉及多人协作、有版本管理要求的项目。它能同时维护概念数据模型和物理数据模型概念模型改成实体关系物理模型生成 SQL 脚本再反向从数据库更新模型形成正向设计与反向工程的闭环。缺点是需要客户端安装学习成本略高。如果你只是想快速把一张残缺的报表恢复到可理解的状态用浏览器搜索“sql转er图在线工具”会更划算。这类工具一般允许粘贴 CREATE TABLE 语句自动识别主键外键然后渲染出实体关系图。看起来不如 PowerDesigner 专业但胜在零安装。实际项目里我常常先在线工具出初稿再导入 PowerDesigner 补全约束和说明。3.2 用 PowerDesigner 画一张教学管理系统 ER 图的操作步骤新建模型时选择 Conceptual Data Model也就是概念数据模型。在工具箱里找到 Entity 工具在画布上分别放置 Student、Course、CourseSelection 三个实体。双击实体进入属性窗口在 Attributes 页签中添加字段选中主键字段并勾选 Primary Identifier。Student 的标识是student_idCourse 的标识是course_idCourseSelection 则用两个外键加semester组成复合主键。接下来用 Relationship 工具把 CourseSelection 分别连到 Student 和 Course 上双击连接线在 Cardinalities 页设置类型。一般设置成 Many 对 One并用 Mandatory 标记强制参与。概念模型完成后通过菜单生成 Physical Data Model再生成 Database Script就能得到上一节那组建表 SQL。如果你只需要画图过程其实只有三步放实体、加属性、连关系。3.3 ER 图里主键是怎么表示的ER 图主键怎么表示这个问题的标准答案是主键属性下加下划线。如果是复合主键比如选课表里的(student_id, course_id, semester)三个属性都需要划线。在 PowerDesigner 的图形界面里主键字段左侧会有小钥匙图标物理模型里则直接显示PK标记。对于弱实体ER 图使用双矩形和双菱形。比如“账户流水”依赖“账户”而存在流水表中的账户ID和流水序号共同构成主键。这类表示在概念模型中很清晰但映射到关系模型后物理表的主键仍然是联合主键不会额外多出表。3.4 用 SQL 反向生成 ER 图的最短路径存量数据库没有现成 ER 图时最直接的办法是导出表结构再转换。以 MySQL 为例先执行下面的命令导出建表语句mysqldump -u root -p --no-data school_db school_schema.sql参数-u root指定用户-p表示后续交互输入密码--no-data确保只导出表结构不导出业务数据避免生成出的 ER 图被大量无关行干扰。school_db换成你的库名输出文件school_schema.sql中会包含所有 CREATE TABLE 语句。然后打开任意支持 DDL 导入的在线 ER 图工具把文件中的 CREATE TABLE 部分粘贴进去。注意删掉 mysqldump 自动生成的注释以及 MySQL 特有的反引号部分在线工具解析不了这些方言。解析结果里主键会被标记成钥匙外键以及外键对应的连接线通常也能自动识别。这种方法不需要额外安装软件适合交付紧急复盘报告时快速拿到一张展示用 ER 图。4. 从 ER 图到 SQL映射规则、反向工程与规避反模式ER 图只是中间产物最终都要落到表结构上。数据库系统工程师面试中常见的一个题目是“如何把 ER 图转换成关系模式”本质上就是下面这张映射表。能熟练运用这张表才算真正理解了实体-联系模型。4.1 实体和联系的映射规则与主外键落地ER 结构转换规则SQL 落地要素强实体每个实体一张表实体属性变列主键变 PRIMARY KEY弱实体一张表外键含父实体主键联合主键 外键约束1:1 联系任一侧表加另一侧主键作为外键外键列加 UNIQUE1:N 联系N 侧表加另一侧主键作为外键外键列加索引M:N 联系独立关联表存两侧主键关联表联合主键 两个外键联系上的属性归入关联表或 N 侧表比如成绩放选课表以前面教学管理系统的 M:N 联系为例关联表要写成下面这样才算是合格的落地。注意 ALL 约束不是必需的但外键一定要建否则数据库层面根本保不住业务完整性。CREATE TABLE enrollment ( student_id INT NOT NULL, course_id INT NOT NULL, semester VARCHAR(20) NOT NULL, grade DECIMAL(3,1), PRIMARY KEY (student_id, course_id, semester), CONSTRAINT fk_enroll_student FOREIGN KEY (student_id) REFERENCES student(student_id), CONSTRAINT fk_enroll_course FOREIGN KEY (course_id) REFERENCES course(course_id) );CONSTRAINT语句给两个外键起了明确的名字后续排查外键问题时能一眼看出哪张表依赖谁。实际生产环境里我会额外给course_id加索引因为业务查询通常从课程反查学生名单而联合主键的最左前缀是student_id覆盖不到按课程检索的场景。4.2 从 MySQL 表导出 ER 关系图常见做法上一章提到的 mysqldump 方案适合单次导出。系统学习 MySQL 的表导出 ER 关系图时我更推荐直接在 PowerDesigner 里做逆向工程。操作路径是新建 Physical Data Model选择数据库类型 MySQL配置连接信息然后选择需要导入的库或表工具会自动读取表、列、主键、外键和索引。导入后 PowerDesigner 会生成一张带主外键连接线的图但布局往往很乱需要手动整理实体框位置。不装客户端也可以使用 information_schema 直接查询外键关系再把结果喂给绘图工具。下面的 SQL 能输出当前库中所有外键的映射关系SELECT tc.table_name, kcu.column_name, kcu.referenced_table_name, kcu.referenced_column_name FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name kcu.constraint_name AND tc.table_schema kcu.table_schema WHERE tc.constraint_type FOREIGN KEY AND tc.table_schema school_db ORDER BY tc.table_name;其中table_constraints存储约束类型key_column_usage存储列级别的键使用信息。两者按约束名连接才能同时拿到外键列和被引用列。实际使用时你可以用 Python 把这份结果画成图也可以在脑海里按表名和引用关系快速重建一张关系图。4.3 三个容易让 ER 图与表结构脱节的隐患第一个隐患是多对多关系没有独立关联实体。有人为了省一张表直接在“学生”表里加course_id列结果一个学生只能选一门课或者只能用逗号分隔保存多个课程 ID彻底违背第一范式。正确的做法是建关联表不要在 ER 图上把学生和课程直接连成一条 M:N 连线后省略中间实体。第二个隐患是弱实体没有用联合主键。银行储蓄系统 ER 图中账户流水如果用自增主键不包含账户 ID就会出现在两个账户下生成相同流水号、或者跨账户引用流水记录的情况。转化关系模式时弱实体必须把父实体主键并入自身主键这是设计层面就能规避的问题。第三个隐患是把派生属性物化成列。比如账户表里同时存“余额”和“流水明细”每次交易都去 update 余额一旦并发写金额就容易被覆盖。正确的 ER 模型应该让余额作为派生属性不出现在账户实体中需要时用流水表 SUM 计算或者用 MySQL 的生成列保存。ER 图上的属性要区分 базовое属性和派生属性这样建表时就不会冒出冗余列。5. 进阶验证三组 SQL 查询核对 ER 图与 MySQL 表结构光会画 ER 图和建表还不够存量表结构经常和最初设计不一致。下面这三组 SQL 能快速找出图与表之间的偏差我把它作为评审前的标准化动作。5.1 用 information_schema 找出无主键的表先扫描出所有没有主键的物理表。这些表一方面违背 ER 模型强实体必须有主键的规定另一方面在 InnoDB 引擎中会被迫使用隐藏主键影响复制和日志分析。SELECT t.table_name FROM information_schema.tables t WHERE t.table_schema school_db AND t.table_type BASE TABLE AND NOT EXISTS ( SELECT 1 FROM information_schema.table_constraints tc WHERE tc.table_schema t.table_schema AND tc.table_name t.table_name AND tc.constraint_type PRIMARY KEY );执行时把school_db换成你的目标库名。base table过滤掉视图NOT EXISTS子查询负责排除那些存在主键约束的表。如果结果非空说明库里有模型之外的临时表或汇总表需要回到 ER 图上补充说明。5.2 审计关联实体是否少外键关联实体比如选课表至少要有两个外键。下面的 SQL 按被引用表统计外键数量能在几十张表里快速定位被孤立的关系表SELECT referenced_table_name, table_name, COUNT(*) AS fk_count FROM information_schema.key_column_usage WHERE table_schema school_db AND referenced_table_name IS NOT NULL GROUP BY referenced_table_name, table_name ORDER BY referenced_table_name;referenced_table_name表示被引用的父表table_name表示外键所在的子表。如果设计上是选课表关联学生和课程结果里却只有一行说明有外键缺失。再配合 ER 图检查 M:N 联系会发现图中已经画了菱形表里却没有对应的关联外键。5.3 用重复数据反查实体属性选型很多 ER 图问题不是出在联系上而是出在实体属性选错。比如课程实体把title当成主键但业务里可能出现“数据库系统概论”在两个学期开课的情况。执行下面的查询SELECT title, COUNT(*) FROM course GROUP BY title HAVING COUNT(*) 1;如果返回多行说明title不具备唯一性它只能作为普通属性不能作为候选主键。ER 图上应该把course_id下划线标记为主键title保持普通椭圆。这类验证不需要复杂的建模工具一条 SQL 就能让属性设计露出破绽。将查询结果与 ER 图上的主键、外键标记逐一核对能把你手上的逻辑模型快速校正回数据库实际状态。本文还有配套的精品资源点击获取
