从 ER 图到数据库表用校园故事讲清楚假设我们要给学校做一个管理系统需要记录学校有哪些学生、班级和课程每个学生在哪个班每个学生选了哪些课程每门课的成绩是多少。这些事情可以用 ER 图表达也可以用数据库表记录。我们先认识图里有哪些东西再学习它们如何转换。一、先认识 ER 图里的三个主角与关系模式ER是 Entity–Relationship 的缩写也就是“实体—联系”。1. 实体我们要记录的对象学生、班级、课程都可以是实体。更准确地说“学生”是一类对象张小明、李小红是其中一个个具体对象。画图时我们通常画出“学生”这一类。传统 ER 图一般用矩形表示实体。2. 属性用来描述对象的信息例如实体属性学生学号、姓名、出生日期班级班级编号、班级名称课程课程编号、课程名称、学分传统 ER 图一般用椭圆表示属性。其中有些属性能帮助我们准确找到某一个对象。比如姓名可能重复学号通常不能重复因此学号适合用作学生的标识。3. 联系对象之间的关联例如学生“属于”班级学生“选修”课程教师“教授”课程。传统 ER 图一般用菱形表示联系。不过不同工具的画法不完全相同。有些图直接用连线和符号表示联系。判断图的含义要看图例和标记不能只看形状。关系模式关系模式就是一张数据库表的“结构说明”这张表叫什么、包含哪些字段以及这些字段需要遵守哪些规则。比如我们要记录学生信息可以写成学生学号姓名出生日期班级编号这就是一个关系模式。其中学生关系名可以理解为表名。学号、姓名、出生日期、班级编号属性可以理解为列名也叫字段名。二、转换前最重要的一步判断“能对应几个”看到两个实体有联系先从两个方向各问一次对于这边的一个对象另一边最多可以有几个这样就能区分三种基本联系。联系类型校园中的例子判断方式一对一1∶1学生与校园账户假设每人最多一个账户且账户不共用一个学生最多一个账户一个账户最多属于一个学生一对多1∶N班级与学生一个班有多个学生一个学生只属于一个班多对多M∶N学生与课程一个学生可选多门课一门课可被多个学生选择这里的规则来自业务约定。例如如果学校允许一个学生同时属于多个教学班“班级与学生”的联系就需要重新判断。还要补问一个问题能不能一个也没有“最多几个”和“最少几个”是两件事。例如一个学生可以暂时不选课对应课程数可以是0..N每个学生必须属于一个班对应班级数是1..1。这决定了联系是否必须存在也会影响外键能不能留空。三、认识关系模式里的四个词关系数据库用表来组织数据。先记住术语简单理解表记录一类信息例如学生表行一条具体记录例如某个学生列字段一项信息例如姓名键字段或字段的组合主键用于唯一标识本表中的记录外键用于引用本表或其他表中的记录建立关联。其中最重要的是主键和外键。**主键PK**用于唯一识别一行。例如学生表用“学号”作主键。主键不能重复也不能为NULL有时需要几个字段一起组成主键。**外键FK**用于引用其他表中的记录。例如学生表里的“班级编号”可以引用班级表里的“班级编号”。可以这样记主键回答“这一行是谁”外键回答“这一行关联谁”。严格来说外键也可以引用满足唯一性要求的其他键。入门时先掌握引用主键的常见情况即可。四、从 ER 图变成关系模式四条基本规则规则一普通实体通常变成一张表例如学生实体可以转换为学生学号姓名出生日期 主键学号班级实体可以转换为班级班级编号班级名称 主键班级编号入门时可以先记住“实体变表简单属性变列”。多值属性等特殊情况后面再单独处理。规则二一对多把“一方”的主键放到“多方”一个班级对应多个学生所以把班级编号放进学生表班级班级编号班级名称 主键班级编号 学生学号姓名班级编号 主键学号 外键班级编号 → 班级.班级编号例如班级表班级编号班级名称B01七年级一班B02七年级二班学生表学号姓名班级编号S01小明B01S02小红B01S03小林B02学生表里的B01可以重复因为同一个班里本来就有多个学生。记忆方法一对多外键放在“多”的一边。规则三多对多通常需要增加一张中间表一个学生能选多门课一门课也能被多个学生选择因此建立“选课表”学生学号姓名 主键学号 课程课程编号课程名称 主键课程编号 选课学号课程编号成绩 主键学号课程编号 外键学号 → 学生.学号 外键课程编号 → 课程.课程编号选课表学号字段课程编号成绩S01字段值C0192S01字段值C0288S02字段值C0195这里每一行表示“一名学生选了一门课”。为什么成绩放在选课表里因为只知道学生无法确定是哪门课的成绩只知道课程也无法确定是谁的成绩同时知道学生和课程才能确定这次选课对应的成绩。这说明联系本身也可以有属性。上述主键有一个前提每名学生对每门课程只记录一次选课。如果允许不同学期重修就需要加入学期、开课编号等信息重新设计唯一标识。记忆方法多对多中间建表联系的属性跟着联系走。规则四一对一可以用“外键唯一约束”假设每个学生最多一个校园账户每个账户也只属于一个学生学生学号姓名 主键学号 校园账户账户编号学号账户名称 主键账户编号 外键学号 → 学生.学号 唯一约束学号不能重复 非空约束学号不能为 NULL为什么要给学号加唯一约束因为只有外键时同一个学号仍然可能出现在多个账户记录里加上唯一约束才能限制“一名学生最多一个账户”。这个设计允许某个学生暂时没有账户但要求每个已有账户必须属于一名学生。要保证“每名学生都必须有账户”还需要进一步的约束或业务流程。记忆方法一对一外键还要唯一是否必须存在要另外检查。五、从关系模式反推 ER 图看表更要看约束反向转换时可以按下面的顺序观察。1. 找出表示对象的表学生表、班级表、课程表通常可以还原成对应实体。但不要机械地认为“一张表一定是一个实体”因为中间表可能代表的是联系。2. 沿着外键找联系例如学生.班级编号 → 班级.班级编号说明学生与班级之间存在联系。3. 检查外键有没有唯一约束这是判断一对多和一对一的关键。表中约束通常可以推断的含义外键允许重复多行可以关联同一条被引用记录对应一对多结构外键有唯一约束最多一行关联同一条被引用记录对应一对一结构外键允许NULL这一行可以暂时不关联被引用对象外键不允许NULL这一行必须关联一个被引用对象注意外键不为空只能保证引用方必须有对应对象。例如每个学生必须有班级并不能因此推出每个班级都必须有学生。4. 找出连接两个实体的中间表例如选课学号课程编号成绩如果学号和课程编号都是外键而且它们的组合具有唯一性通常可以还原为学生与课程是多对多联系“成绩”是这个联系的属性。如果中间表还有独立身份和复杂业务例如订单、报名记录也可以把它建模为一个关联实体。只看几行样例数据不能可靠判断联系类型。当前每个班恰好只有一个学生不代表班级与学生就是一对一。应结合主键、外键、唯一约束和业务规则判断。六、知识补充1. 一个格子里不要塞一串编号违反第一范式下面的设计不适合常规关系数据库的入门建模学号所选课程S01C01、C02、C03这样很难检查课程是否存在也不方便逐门统计成绩。更合适的做法是建立选课表一行记录一次选课。这也有助于理解第一范式每个字段应保存符合其定义的单个值不用一个字段存放需要分别查询和关联的一组编号。2.NULL不等于 0也不等于空字符串成绩为0表示成绩就是零分成绩为NULL可能表示还没有录入。具体含义要由业务规则明确不能随意混用。3. 同一份事实尽量在合适的位置保存一次如果每个学生的记录里都写一遍班级名称班级改名时就要修改很多行还容易漏改。把班级名称保存在班级表里学生表只保存班级编号就能减少重复和修改不一致的问题。这是理解规范化的一个入口。4. 有些属性需要特殊处理复合属性例如地址可以按查询需要拆为省、市、详细地址。多值属性例如一个学生有多个联系电话通常另建联系电话表。派生属性例如年龄可以根据出生日期和当前日期计算通常优先保存出生日期。5. 有些对象必须依靠另一个对象来识别例如“订单明细”的明细序号只在某张订单内部唯一就可以用“订单编号明细序号”一起识别。这是理解弱实体的常见例子可以放在进阶部分讲。6. 对于“码”的一些理解码候选码能唯一标识本表记录的最小属性集合。外码外键表中用于引用某个关系的候选码的属性或属性组合 不要求在本表中唯一。码通常指候选码主码是选中的候选码外码则是另一个专门概念。超码就是能够唯一标识一条记录的属性或属性组合允许包含多余属性。7. 联系属性的放法联系类型联系属性通常放哪里一对多放在多方表中与外键放在一起多对多放在中间表中一对一放在保存外键的表中联系需要保存多次发生的历史通常单独建立记录表最关键的一点属性在概念上属于“联系”不代表转换成表时就一定要单独建一张表。只要那张表能准确记录这段联系及其属性就可以。七、一张表记住互转方法ER 图中的结构常见关系模式实现反向识别时看什么普通实体一张表表是否表示独立的业务对象简单属性一列列的含义和数据类型标识属性主键或候选键主键、唯一与非空约束一对多联系多方保存一方的外键外键通常允许重复一对一联系外键加唯一约束或共享主键外键是否唯一多对多联系建立中间表两端外键及其组合的唯一性联系的属性放在记录该联系的表中属性描述的是对象还是对象间的关联八、用三个问题检查自己是否真的会了问题一一个班有很多学生班级编号放哪张表放在学生表因为学生是“多”的一方。问题二学生可以参加多个社团一个社团也有多个学生怎么办多对多建立“参加社团表”记录学号、社团编号以及加入日期等联系属性。问题三校园账户表里已经有学号外键就能保证每人最多一个账户吗不能。还需要让学号具有唯一性否则多个账户仍然可以引用同一个学生。