ER图中3个实体之间的关系 - 三元足够还是还需要2个二元? [英] relationships between 3 entities in ER diagram--is a ternary enough or are 2 binaries also needed?
问题描述
我正在尝试为我的项目管理软件绘制 ER 图描述如下.它包含以下实体:
I'm trying to draw an ER diagram for my project management software describing the following. It contains these entities:
- 项目 - 软件项目
- 任务 - 可以分解为多个任务的软件项目
- 员工 - 属于该软件的员工
还有:
一个项目可以分为多个任务.(任务可以由管理员用户创建，管理员可以将这些任务分配给选定的项目.这里只将任务分配给项目，而不是将员工分配给项目.)
A project can be divided into tasks. (Tasks can be created by the admin user, who can assign those tasks to selected projects. Here there is only assignment of tasks to projects, not assignment of employees to projects.)
员工可以分配到项目中.
(可以给员工分配项目，这里只给员工分配项目，不分配项目任务.)
Employees can be assigned to projects.
(An employee can be assigned to projects. Here there is only assignment of employees to projects, not assignment to tasks of projects.)
对于选定项目的选定任务，我们可以从池中分配员工——在 2 中分配给该项目的员工.(这次我们必须指定项目、任务和员工；这三个选项都是必填项.)
For a selected task of a selected project we can assign employees from the pool--employees that are assigned to that project in 2. (This time we must specify project, task & employee; all 3 selections are mandatory.)
1, 2 & 的输入过程以上3个可以在系统的单独页面中完成.您可以先选择其中任何一个.
The input processes for 1, 2 & 3 above can be done in separate pages in the system. You can select any of them first.
对于上述关系，我创建了这个 ERD:
For the above relationships I created this ERD:
考虑
- 项目和任务之间的关系 1
- 项目与员工的关系2
是否需要像 ER 图中的两种独立关系，关系没有 1 &关系没有2?
Is there a need for the two separate relationships as in the ER diagram, relationship no 1 & relationship no 2?
或
我们是否可以只使用项目、员工和任务之间的关系 3，关系编号 3?
can we use only relationship 3 among project, employee and task, relationship no 3, for that also?
推荐答案
TL;DR 您需要所有三种关系类型/表.因为如果你丢掉一个，那么在某些情况下你会丢失数据——没有办法用剩下的来回答所有相同的问题.
TL;DR You need all three relationship types/tables. Because if you drop one then in some situations you lose data--there is no way to use the remaining ones to answer all the same questions.
不同的约束可能意味着我们可以删除一个关系/表格，因为它可以用其他人的方式表达.归一化到更高的 NF(范式)告诉我们何时可以用更小/更简单的关系/表替换关系/表.
Different constraints could mean we can drop a relationship/table because it can be expressed in terms of others. Normalization to higher NFs (normal forms) tells us when we can replace a relationship/table by smaller/simpler ones.
每个关系表包含参与关系的行.我们可以通过一个谓词(语句模板)来描述这种关系:
Each relationship table holds the rows that participate in the relationship. We can describe the relationship via a predicate (statement template):
1 Divides_to 包含 (T, P) 行，其中 project P 划分为任务 T
2 Has 包含 (E, P) 行，其中 employee E 分配给项目 P
3 保存 (E, T, P) 行，其中 employee E 被分配到项目 P 上的任务 T
1 Divides_to holds (T, P) rows where project P divides to task T
2 Has holds (E, P) rows where employee E is assigned to project P
3 holds (E, T, P) rows where employee E is assigned to task T on project P
我们可以丢 1 吗?如果我们忽略 3 中的员工，那么我们会得到 某个员工被分配到项目 P 上的任务 T 的行.但是(如上所示)这不是 1 中的行.也许项目 p1 在 1 中划分为任务 t1，但没有员工分配给项目 p1 中的任务 t1；那么 1 中的行 (t1, p1) 不是 3 中的子行.并且 2 中没有任务信息.所以我们不能使用 3 &2 替换 1.
Can we drop 1? If we ignore employees in 3 then we get rows where some employee is assigned to task T on project P. But (per above) that is not the rows in 1. Maybe project p1 divides to task t1 in 1 but no employee is assigned to task t1 on project p1; then row (t1, p1) in 1 is not a subrow in 3. And there is no task info in 2. So we can't use 3 & 2 to replace 1.
我们可以丢弃 2 个吗?类似地:如果我们忽略 3 中的任务，那么我们会得到行，其中 employee E 被分配到项目 P 上的某个任务.但是(如上所示)这不是 2 中的行.也许员工 e1 被分配到项目 p1 但没有分配到项目 p1 上的任务；那么 2 中的行 (e1, p1) 不是 3 中的子行.并且 1 中没有员工信息.所以我们不能使用 3 &1代替2.
Can we drop 2? Similarly: If we ignore tasks in 3 then we get rows where employee E is assigned to some task on project P. But (per above) that is not the rows in 2. Maybe employee e1 is assigned to project p1 but is not assigned to a task on project p1; then row (e1, p1) in 2 is not a subrow in 3. And there is no employee info in 1. So we can't use 3 & 1 to replace 2.
我们可以丢弃 3 个吗?使用 1 &2 我们可以得到 employee E 分配给项目 P AND 项目 P 分配给任务 T 的行.但是(如上所示)这不是 3 中的行.如果分配给项目的员工未分配到其所有任务，或者项目的任务未分配给所有员工，则它们会有所不同.没有其他方法可以从 1 & 生成 32. 所以我们不能使用 1 &2代替3.
Can we drop 3? Using 1 & 2 we can get rows where employee E is assigned to project P AND project P divides to task T. But (per above) that is not the rows in 3. They differ if an employee assigned to a project isn't assigned to all its tasks or if a task of a project doesn't have all its employees assigned to it. There's no other way to generate 3 from 1 & 2. So we can't use 1 & 2 to replace 3.
所以我们需要所有三个关系.
So we need all three relationships.
当约束成立时，某些查询表达式总是返回与某些其他表达式相同的结果，否则不会.因此，在不同的约束下，我们可能能够删除关系/表，因为我们可以通过其他人的查询/视图来表达其内容.我们可能会选择不同的关系/表.
When constraints hold, certain query expressions always return the same results as certain others that otherwise wouldn't. So under different constraints we might be able to drop a relationship/table because we can express its content via queries/views of others. And we might choose different relationships/tables.
标准化到更高的 NF 指导将关系分解为更简单的其他关系，通过这些关系可以根据某些约束来表达.
Normalization to higher NFs guides decomposing a relationship into simpler others by which it can be expressed instead per certain constraints.
PS 1 这也是我们需要实体类型/表而不仅仅是关系类型/表的原因.(如果我们不希望它们用于特定于实体的属性或只是 ER 建模约定.)例如，这三个关系无法告诉您未分配给项目或任务的员工的信息.项目.类似的任务 &用于项目.
PS 1 That's also why we need the entity types/tables and not just the relationship types/tables. (If we didn't want them anyway for entity-specific attributes or just ER modeling conventions.) Eg the three relationships can't tell you about employees that aren't assigned to a project or to a task & project. Similarly for tasks & for projects.
PS 2 我们通过不projecting 来忽略关系代数中的属性.我们通过不 select 来忽略 SQL 中的列.结果的谓词是属性/列的 FOR SOME 值，旧谓词成立.关系 natural join 给出其关系/谓词是输入关系/谓词的 AND 的行.在 SQL 中没有重复的行 &没有 select distinct from natural join 的共享可空列.
PS 2 We ignore an attribute in relational algebra by not projecting on it. We ignore a column in SQL by not selecting it. The result's predicate is that FOR SOME value for the attribute/column, the old predicate holds. Relational natural join gives the rows whose relationship/predicate is the AND of input relationships/predicates. In SQL for no duplicate rows & no shared nullable columns that's select distinct from natural join.
PS 3 根据常识，您的设计满足某些限制条件:如果任务-项目对出现在 3 中，则它必须出现在 1 中，如果员工-项目对出现在 3 中，则它必须出现在 2 中.反映的一种方式在 ER 建模中是通过具体化任务项目和员工-项目与关联实体的关系，然后用 ER 对这些 实体 所称的二元关系替换 3.在关系上，关系/表仍然是三元值，其中某些子行恰好标识这些实体.获得约束关系二进制 3 的一种方法是在 2 中添加一个员工项目 PK(主键)或 CK(候选键)id，并用这样的 id 替换 3 中的复合 FK(外键).然后我们有一个关于实体和值的二进制文件.一些伪 ER 方法可以做到这一点.
PS 3 Under common sense your design satisfies certain constraints: If a task-project pair appears in 3 then it must appear in 1 and if an employee-project pair appears in 3 then it must appear in 2. One way to reflect that in ER modeling is by reifying the task-project & employee-project relationships to associative entities then replacing 3 by a what ER calls a binary relationship on those entities. Relationally, the relationship/table is still ternary on values, where certain subrows happen to identify those entities. A way to get a constrained relationally binary 3 is to add an employee-project PK (primary key) or CK (candidate key) id in 2 and replace the composite FK (foreign key) in 3 by such an id. Then we have a binary on entities and on values. Some pseudo-ER methods do this.
PS 4 这种风格的(真陈)ER 图通常不使用 SQL 空值.但碰巧的是，您可以将所有三个关系替换为 3 的变体和空值.您将null-扩展二元关系并将它们union 与三元.像往常一样，空值使谓词复杂化.通常我们添加一个可为空的列作为添加一个共享无空 CK(候选键)的单独表的替代方法.但这是不同的，没有空间或连接的节省；它只是使事情复杂化.(包括重要的约束.)
PS 4 This style of (true Chen) ER diagram doesn't typically use SQL nulls. But as it happens you could replace all three relationships by a variant of 3 with nulls. You would null-extend the binary relations and union them with the ternary. As usual, nulls complicate predicates. Usually we add a nullable column as an alternative to adding a separate table sharing a null-free CK (candidate key). But this is different, without the savings in space or joins; it just complicates things. (Including important constraints.)
E IS NULL
AND task T is of project P
AND NOT EXISTS E [employee E is assigned to task T of project P]
OR T IS NULL
AND employee E is assigned to project P
AND NOT EXISTS T [employee E is assigned to task T of project P]
OR employee E is assigned to task T of project P
(这在 SQL 中也是有问题的，因为 SQL unique, primary key & join 不是这些名称的关系事物，因为它们处理null 特别是.)
(Also it's problematic in SQL because SQL unique, primary key & join are not the relational things by those names because they treat null specially.)
PS 5 我的一些关于三元与二元关系(船舶)类型/表/谓词的答案:
这个ER图是否应该使用三元关系
最佳解决方案 - 三元或二元关系
你为什么不能加入粉丝陷阱?
并重新设计 &谓词:
对关系数据库中相同实体之间的多个多对多关系进行建模
实体关系模型和关系模型有什么区别?
是否有根据人类可读的描述构造 SQL 查询的经验法则?
PS 5 Some answers of mine re such ternary vs binary relation(ship) types/tables/predicates:
Should this ER diagram use a ternary relationship instead
Best Solution - Ternary or Binary Relationship
Why can't you just join in fan trap?
And re design & predicates:
Modeling multiple many to many relationships between the same entities in a relational database
What is the difference between an entity relationship model and a relational model?
Is there any rule of thumb to construct SQL query from a human-readable description?
PS 6 Has 是一个无用的通用关系名称/含义/表.使用有意义的名称，例如 Is_assigned_to 或 Assignment.
PS 6 Has is an unhelpfully generic relationship name/meaning/table. Use meaningful names like Is_assigned_to or Assignment.
这篇关于ER图中3个实体之间的关系 - 三元足够还是还需要2个二元?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
