标识关系或非标识关系中的哪个子表? [英] Which is the child table in a Identifying or Non-Identifying Relationship?

问题描述

在识别表之间的关系和不识别关系的情况下，MySQL的文档在很大程度上将表称为父表和子表.

如何确定哪个表是父表，哪个表是子表?

解决方案

子表(又名弱实体)是一个表，其主键属性 依赖于另一个表，因此子表被标识或部分标识通过它依赖于的表中的行(父).子表中的行如果没有其父表中的相应行，则不能存在.

为了说明这一点，让我们举一个我们都熟悉的简单且完全相关的示例:家庭背景下的父母和孩子.我们可以使用如下表来建立这种关系的模型:

在上面的模型中，Parents表中的每一行都由SSN唯一标识 . SSN是每个父级的固有且唯一的属性，因此它是独立的或强"的实体，因为它不依赖于另一个表来定义其标识.

孩子，但是需要父母才能存在(Parent_SSN 必须 引用SSN >表格).

请注意Children表中的组合主键(Parent_SSN, Name).这意味着，通过Parent_SSN 和 Name的组合 唯一地标识了 .您不能仅根据Name字段查询单个孩子，因为多个父母可能有同名孩子.同样，您不能仅根据Parent_SSN字段查询单个孩子，因为一个父母可能有很多孩子.考虑到这一点，孩子由父母部分识别，因此 识别 关系.

但是SSN也不能唯一标识孩子吗?当然可以，为什么.让我们继续进行调整，以包括以下内容:

在此版本的模型中，请注意，我们为Children引入了SSN字段.现在，孩子的唯一身份由他们自己的内在且唯一的SSN定义.他们的身份不再取决于 表.尽管Parent_SSN字段仍引用Parents表的SSN，但它不属于孩子的唯一身份，因此父母具有 确定 与其子女的关系，这两个表现在都可以视为强"独立实体.

顺便说一句，此版本的模型比第一个模型具有一些优势:

• 一个父母现在可能有两个或更多个同名子女，而实体完整性前一个模型中的约束将不允许这样做.
• 您可以允许Parent_SSN字段包含NULL，以说明您拥有有关孩子的数据但不知道其父母是谁的情况.

在上述两个模型中，Parents表都被视为Children表的父表.但是，在类似于第二个模型的非识别关系中，Parents只是外键Parent_SSN上下文中的父表，因为Parent_SSN 引用/依赖Parents表中SSN上的em>，但是 并没有定义孩子的实际身份.

要说明在决定哪些表是父/子表时上下文为什么重要的原因，请考虑以下涉及循环依赖的示例:

在此示例中，员工和部门由他们自己的属性唯一标识，并且不从其他表派生其身份的任何部分.

在这里，我们有两个不可识别的关系:雇员在部门工作(在Employee表中的DeptNo)，部门由雇员管理(在Department表中的ManagerSSN) .父表是哪一个? ...子表?

这取决于上下文-您在谈论哪种外键关系?在Employee表的DeptNo上下文中，将Department表视为父表，因为DeptNo在Department表上是引用/从属.

但是，在Department表的ManagerSSN上下文中，Employee表将被视为父表，因为ManagerSSN在Employee表上是引用/相关. /p>

In the context of identifying and non-identifying relationships between tables, MySQL's documentation refers a lot to the tables as parent and child tables.

How do you determine which table is the parent table and which table is the child table?

解决方案

A child table (A.K.A. weak entity) is a table whose primary key attributes depend on another table, thus the child table is identified or partially identified by rows in the table it depends on (parent). Rows in a child table cannot exist without a corresponding row in its parent table.

To illustrate, let's take a simple and completely relevant example we are all familiar with: Parents and children in the context of family. We can model out this relationship with tables like so:

In the model above, each row in the Parents table is uniquely identified by an SSN. The SSN is an intrinsic and unique attribute to each parent, thus it is a standalone or "strong" entity because it does not rely on another table to define its identity.

Children however, require a parent in order to exist (Parent_SSN must reference to an existing SSN in the Parents table).

Notice the composite primary key (Parent_SSN, Name) in the Children table. This means that children are uniquely identified by the combination of Parent_SSN and Name. You cannot query for an individual child based only on the Name field because multiple parents may have children with the same name. Likewise, you cannot query for an individual child based only on the Parent_SSN field because one parent may have many children. Taking that into consideration, children are partially identified by their parent, hence identifying relationship.

But can't children be uniquely identified by an SSN as well? Why yes, certainly. Let's go ahead and adjust our model to include that:

In this version of the model, notice we have introduced the SSN field for Children. The unique identity of children is now defined by their own intrinsic and unique SSN. Their identity no longer depends on the Parents table. Although the Parent_SSN field still references the SSN of the Parents table, it has no part in the unique identity of the child, thus parents have a non-identifying relationship to their children, and both tables can now be considered "strong" standalone entities.

As an aside, this version of the model has a few advantages over the first:

• One parent may now have two or more children with the same name, whereas the entity integrity constraint in the previous model would not allow for this.
• You can allow the Parent_SSN field to contain NULL to account for the event that you have data about the child, but do not know who his/her parent is.

In both of the above models, the Parents table is considered to be the parent table of the Children table. However, in non-identifying relationships like in the second model, Parents is only a parent table in the context of the foreign key Parent_SSN because Parent_SSN references/depends on SSN in the Parents table, but does not have any part in defining the actual identity of children.

To illustrate why context is important when deciding which tables are parent/child tables, consider the following example involving a circular dependency:

In this example, employees and departments are uniquely identified by their own attributes and do not derive any part of their identity from other tables.

Here, we have two non-identifying relationships: an employee works for a department (DeptNo in the Employee table), and a department is managed by an employee (ManagerSSN in the Department table). Which one is the parent table? ...Child table?

It depends on context — which foreign key relationship are you talking about? The Department table would be considered the parent table in the context of DeptNo in the Employee table because DeptNo is referencing/dependent on the Department table.

However, the Employee table would be considered the parent table in the context of ManagerSSN in the Department table because ManagerSSN is referencing/dependent on the Employee table.

这篇关于标识关系或非标识关系中的哪个子表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！