博客

题目要求

根据上述的要求绘制 ER 图

crow’s foot

Background information

由题目可以看出，本题难点主要在两部分关系。 sales offices 与 employees 的关系，以及 property与 owners 之间的关系。

对于前者，sales offices 与 employees 之间显然存在一个双重关系，分别是 assigned 和 managed。

• 每个 sales office 会assigned一个或多个 employees，而一个 employee 只能 assigned 一个 sales office。
• 每个 sales office 的 employees 中，将会有一个负责 managed。

对于后者，property 和 owners 之间显然是多对多的关系，因此需要一个中间表 own 来负责维护。

另外需要注意的是 location。他作为一个composite key的属性，本身包含其他属性。是将 location 作为 composite key 插入 property 表，还是作为 surrogate key 插入，这是两种可选的情况。

ER relationship description

Sales office 与 employee

如前面所述，sales office 与 employee 之间存在两种关系。

1.assigned 关系，属于一对多，并且每个 employees 都必须至少有一个 sales office。对于外键的设置，将设置在对应“多”的一方。于是将sales office 的主键 office number 作为employees 的外键。

2.managed 关系，属于一对一的关系，并且employees 可能是 manage也可能不是，所以关系中 sales office 端点允许为 0。由课上要求可知，对于一对一的关系，外键更倾向于设在sales office 一端，这样可以减少外键为 null 的可能。

3.为了保证“An employee may manage only the sales office to which she is assigned.”，需要保证 managed 关系的（identifier, office number）与 assigned 关系中的（office number，identifier）相对应。

Sales office 与 property

属于一对多的关系，外键设置在 property 表中。

Property 与 location

这里将 location 作为 surrogate key，因此另外新建一个实体给 location。二者属于 1 对 1 关系，外键设置在property 一端，原因在于有的 location 可能并不存在 property 与其对应。

Property 与 owners

这是一个多对多关系，将其转为两个一对多，设置一个中间表 owns 来表示两者的relationship。注意，property，owns，owners 之间为 1-m-1 的关系，不可误写成 m-1-m，这属于 fan trap。

所有的外键均设置在 owns 表中。这里将 owns 表中的所有 Owner_ID 外键设为非 NULL，保证所有 property 都有对应的 owner。

Concluding remark

总之，这次作业考察了如何绘制一对一，一对多，多对多关系的 ER 图。对于一对一关系，详细考察了对于


和


这两种情况如何进行外键的设置。引用课上原话：“In a 1:1 relationship, place the PK of the mandatory entity as a foreign key in the optional entity, as an FK in the entity that causes the fewest nulls, or as an FK where the role is played.”

而对于 composite key 和 surrogate key 的设置，也进行了考量。

另外，从这次绘制作业，我们也可以了解到绘制 ER 图的流程：

• 在需求分析阶段，设计数据库的一般步骤为：

  • 收集信息
  • 标识对象
  • 标识每个对象的属性
  • 标识对象之间的关系

• 在概要设计阶段和详细设计阶段，设计数据库的步骤为：

  • 绘制E-R图
  • 将E-R图转换为表格
  • 应用三大范式规范化表格