Reading: Deep Dive into Advanced Relational Model Concepts阅读：深入理解高级关系模型概念

Estimated time: 10 minutes
预计时间：10 分钟

Objective 目标

By the end of this reading, you will:
完成本次阅读后，您将能够：

1. Understand functional dependencies
   理解函数依赖
2. Learn about multi-valued dependencies
   学习多值依赖
3. Focus on candidate keys
4. Practice the concept on a practical scenario

In the world of managing databases, it's super important to understand some fancy concepts to make databases work well. Three big ones are functional dependencies, multi-valued dependencies, and candidate keys. These are like the building blocks for organizing and speeding up databases.

1. Functional Dependencies (FDs)

FDs are fundamental building blocks for ensuring data integrity and consistency in relational databases. They represent a specific type of relationship between attributes in a relation, where the value of one attribute (determinant) uniquely determines the value of another attribute (dependent). In simpler terms, if you know the value of the determinant, you can always identify the exact value of the dependent attribute.
FDs 是确保关系数据库中数据完整性和一致性的基本构建块。它们表示关系属性之间的一种特定类型的关系，其中一个属性（决定因素）的值唯一地决定了另一个属性（依赖属性）的值。简单来说，如果你知道决定因素的值，你总能确定依赖属性的值。

• For example, if you know an employee's ID number, you can look it up in the table and find out their first and last name. In this case, the employee ID "determines" the first and last name. This is a functional dependency.
  例如，如果你知道一个员工的 ID 号码，你可以在表中查找并得知他们的名和姓。在这种情况下，员工 ID“决定了”名和姓。这就是一个函数依赖。
  Think of it as a special kind of connection between pieces of information. The determining attribute acts like a key that unlocks the related information.
  可以将其视为信息之间的一种特殊连接。决定属性就像一把钥匙，解锁相关的信息。

Properties of FDs: FDs 的性质

• Notation: FDs are typically written as X -> Y, where X is the determinant and Y is the dependent attribute.
  符号：FDs 通常表示为 X -> Y，其中 X 是决定因素，Y 是依赖属性。

• Properties:  性质：

  • Reflexivity: X -> X (every attribute determines itself).
    自反性：X -> X（每个属性都决定自身）。
  • Transitivity: If X -> Y and Y -> Z, then X -> Z (dependencies can chain together).
    传递性：如果 X -> Y 且 Y -> Z，则 X -> Z（依赖可以链接在一起）。
  • Closure: The minimal set of FDs that implies all other FDs in a relation.
    闭包：关系式中蕴含所有其他函数依赖的最小函数依赖集。

• Key points  要点

  • FDs are essential for maintaining data accuracy and minimizing redundancy.
    函数依赖对于维护数据准确性和最小化冗余至关重要。
  • They play a crucial role in database normalization, ensuring the efficient organization of data.
    它们在数据库规范化中起着关键作用，确保数据的有效组织。
  • FDs help in eliminating unnecessary repetition and ensuring the correctness of data.
    FDs 有助于消除不必要的重复并确保数据的正确性。

2. Multi-Valued Dependencies (MVDs) 多值依赖（MVDs）

MVDs are more complex than FDs. With an MVD one attribute, the determinant, determines a set of possible values for another attribute, the dependent. In other words, knowing the value of the determinant narrows down the potential values the dependent attribute can hold.
MVDs 比 FDs 更复杂。一个 MVD 中，一个属性，即决定属性，决定了另一个属性，即依赖属性的一组可能值。换句话说，知道决定属性的值可以缩小依赖属性可能持有的潜在值范围。

Here's how it works:
这是其工作原理：

• If you know an employee's ID (let's say ID 123), you can't just look it up and find their one assigned project.
  如果你知道一个员工的 ID（比如 ID 123），你不能仅仅通过查找就能找到他们分配的一个项目。
• With MVD, knowing ID 123 tells you there will be multiple rows in the table, each listing a different project that employee 123 works on.
  使用 MVD，知道 ID 123 会告诉你表中会有多行，每行列出该员工 123 正在处理的不同项目。
• So, the employee ID "multi-determines" the project because it doesn't give you a single answer, but lets you know there are potentially several project assignments for that employee in separate rows. This is different from a regular dependency, where one piece of information leads to one or two specific others.
  因此，员工 ID“多决定”了项目，因为它不会给出单一答案，而是让你知道该员工可能有多个项目分配，分别在不同的行中。这与常规依赖不同，在常规依赖中，一个信息会直接导致一个或两个特定的其他信息。

Properties of MVDs: MVD 的特性

• Notation: MVDs are written as X -> {Y1, Y2, …, Yn}, where X is the determinant and Y1, Y2, …, Yn are the possible values for the dependent attribute.
  符号：MVD 表示为 X -> {Y 1 , Y 2 , …, Y n }，其中 X 是决定因素，Y 1 , Y 2 , …, Y n 是依赖属性的可能的值。

• Properties:   属性：

  • MVDs have similar properties to FDs, like reflexivity and transitivity. However, they lack the closure property.
    MVDs（多值依赖）与 FDs（函数依赖）具有相似的属性，如自反性和传递性。然而，它们缺乏封闭性。
  • Identifying MVDs helps understand the complex relationships between attributes and ensures data consistency within those relationships. Violations of MVDs can lead to invalid data entries.
    识别 MVDs 有助于理解属性之间的复杂关系，并确保这些关系中的数据一致性。违反 MVDs 会导致无效数据条目。

• Key points:  要点：

  • MVDs are essential for organizing complex relationships between sets of attributes.
    MVDs 对于组织属性集之间的复杂关系至关重要。
  • They help in avoiding mix-ups and ensuring proper organization of data.
    它们有助于避免混淆并确保数据的正确组织。
  • Understanding MVDs is crucial for maintaining data integrity and optimizing database performance.
    理解 MVDs 对于维护数据完整性和优化数据库性能至关重要。

3. Candidate Keys 3. 候选键

Candidate keys uniquely identify each row in a relation. They represent a minimal set of attributes that collectively guarantee no duplicates exist in the table. In other words, if you know the values of the candidate key, you can pinpoint a specific row and only that row. The keys are unique within that table.
候选键唯一标识关系中的每一行。它们代表一个最小的属性集合，该集合共同保证表中不存在重复。换句话说，如果你知道候选键的值，你就能精确定位特定的一行，也只有这一行。这些键在该表中是唯一的。

Imagine going back to the basic table with just employee ID, first name, and last name.
想象一下回到只有员工 ID、名和姓的基本表。

• An employee ID is a strong candidate key on its own. Why? Because every employee has a unique ID, knowing the ID instantly tells you which record belongs to that particular employee.
  员工 ID 本身就是一种强候选键。为什么？因为每个员工都有一个唯一的 ID，知道 ID 就能立刻告诉你哪个记录属于该特定员工。
• Here's the twist: This table might have another candidate key! Think about it: what if two employees have the same first and last name (less common, but possible)? In that case, the combination of first name and last name wouldn't be enough to distinguish between them.
  这里有个转折：这个表可能还有另一个候选键！想想看：如果两个员工的名和姓相同（虽然不太常见，但有可能），那么名和姓的组合就不足以区分他们。
• But the employee ID, by itself, will always be unique and pinpoint the exact employee. That's why a single attribute (employee ID) can be a candidate key in this scenario.
  但员工 ID 本身始终是唯一的，并能精确指向特定员工。这就是为什么在当前场景中，单个属性（员工 ID）可以是候选键。

Key points:  要点：

• Uniqueness: Each combination of values in the candidate key must uniquely identify a distinct row.
  唯一性：候选键中每个值组合必须唯一标识一个不同的行。
• Minimality: No proper subset of the candidate key should be able to uniquely identify a row. This ensures that the candidate key is the smallest possible set of attributes needed for unique identification.
  最小性：候选键的任何真子集都不应能够唯一标识一行。这确保了候选键是唯一标识所需的最小属性集。
• Performance: Having well-defined candidate keys significantly improves query performance. Queries searching for specific rows can utilize indexing on the candidate key for faster data retrieval. It also helps maintain data integrity by preventing duplicate entries.
  性能：定义良好的候选键显著提高查询性能。搜索特定行的查询可以利用候选键上的索引进行更快的数据检索。它还有助于维护数据完整性，通过防止重复条目。
• Multiple keys: A relation can have multiple candidate keys, meaning different minimal sets of attributes can uniquely identify each row.
  多个键：一个关系可以有多个候选键，这意味着不同的最小属性集可以唯一标识每一行。

Here's the important thing: A table can have multiple candidate keys. As long as a set of attributes guarantees finding one specific employee record and no others, it qualifies as a candidate key. The key thing is that it uniquely identifies a row in the table.
重要的是：一个表可以有多个候选键。只要一个属性集保证找到一条特定的员工记录并且不找到其他记录，它就符合候选键的定义。关键在于它唯一地标识表中的一行。

Practical scenario 实际场景

Let's consider a hypothetical scenario where we have a database for tracking employee projects. Each employee can work on multiple projects, and each project can involve multiple employees. We'll create a table to represent this scenario:
让我们考虑一个假设场景，其中我们有一个用于跟踪员工项目的数据库。每个员工可以参与多个项目，每个项目可以涉及多个员工。我们将创建一个表来表示这个场景：

In this table:  在此表格中：

• EmployeeID: Unique identifier for each employee.
  EmployeeID：每个员工的唯一标识符。
• ProjectID: Unique identifier for each project.
  项目 ID：每个项目的唯一标识符。
• EmployeeName: Name of the employee.
  员工姓名：员工姓名。
• ProjectName: Name of the project.
  项目名称：项目名称。
• Department: Department to which the project belongs.
  部门：项目所属部门。

Functional Dependencies (FDs) 函数依赖（FDs）

EmployeeID -> EmployeeName

• Knowing the EmployeeID uniquely determines the EmployeeName.
  知道 EmployeeID 可以唯一确定 EmployeeName。

• For instance, if you look up EmployeeID 1 in the table, it uniquely identifies the EmployeeName as "Alice".
  例如，如果你在表中查找 EmployeeID 1，它可以唯一确定 EmployeeName 为"Alice"。

  EmployeeID -> EmployeeName
  员工 ID -> 员工姓名
  1 -> Alice

ProjectID -> ProjectName  项目 ID -> 项目名称

• Knowing the ProjectID uniquely determines the ProjectName.
  知道项目 ID 可以唯一确定项目名称。
• In the table, ProjectID 101 corresponds to "Project X" and ProjectID 102 corresponds to "Project Y", demonstrating a one-to-one relationship.
  在表格中，ProjectID 101 对应 "项目 X"，ProjectID 102 对应 "项目 Y"，展示了一对一的关系。

ProjectID ↔ ProjectName
101 ↔ Project X  101 ↔ 项目 X
102 ↔ Project Y  102 ↔ 项目 Y

Multi-Valued Dependencies (MVDs)

{EmployeeID} ->> {ProjectID}

• Knowing the EmployeeID tells us all the projects that employees are working on, independent of each other.

Let's revisit the example of EmployeeID 1 (Alice) from the table:

• EmployeeID 1 is associated with ProjectID 101 (Project X) in the HR department.
  员工 ID 1 与人力资源部门的 ProjectID 101（项目 X）相关联。
• EmployeeID 1 is also associated with ProjectID 102 (Project Y) in the Finance department.
  员工 ID 1 也与财务部门的 ProjectID 102（项目 Y）相关联。

EmployeeID – {associated with} –> ProjectID (department)
员工 ID – {与} –> ProjectID (部门)
1———–> 101 (HR)
1———–> 102 (Finance)

Knowing only EmployeeID 1 doesn't tell you which specific project Alice works on. It indicates there are potentially several project assignments for her, reflected in separate rows in the table. This highlights the many-to-many relationship between EmployeeID and ProjectID.

Candidate Keys

There are two candidate keys (CKs):

• EmployeeID: As discussed earlier, each employee has a unique identifier (EmployeeID) that pinpoints their specific record. You can identify any employee solely based on their EmployeeID, making it a candidate key.
  员工 ID：如前所述，每位员工都有一个唯一的标识符（员工 ID），用于定位其特定记录。您可以根据员工的员工 ID 识别任何员工，使其成为候选键。

  EmployeeID -> EmployeeName
  员工 ID -> 员工姓名
  1 -> Alice

• Combination of EmployeeID, ProjectID: This might seem surprising, but consider a scenario where every employee has a distinct name within a project (no duplicates within projects). In that case, the combination of EmployeeName and ProjectID would uniquely identify each employee record.
  员工 ID 和项目 ID 的组合：这可能看起来令人惊讶，但请考虑这样一种情况：在项目中，每位员工都有一个独特的姓名（项目内无重复）。在这种情况下，员工姓名和项目 ID 的组合将唯一地识别每个员工记录。

  EmployeeID and ProjectID determine the Department
  员工 ID 和项目 ID 决定部门
  EmployeeID -> ProjectID ==> Department
  员工 ID -> 项目 ID ==> 部门
  1 -> 101 => HR
  1 -> 101 => 人力资源部

For instance, if Alice was assigned to Project X and Charlie to Project Y (assuming unique names within projects), then the combination of "John" and "Project X" or "Mary" and "Project Y" would uniquely identify their respective rows.
例如，如果爱丽丝被分配到项目 X，查理被分配到项目 Y（假设项目内名称唯一），那么"约翰"和"项目 X"的组合或"玛丽"和"项目 Y"的组合将唯一标识他们各自的数据行。

Summary 摘要

Author(s) 作者