Module 01 · Relational Database Concepts
Reading: Advanced Relational Model Concepts阅读：高级关系模型概念
Estimated time needed: 15 minutes
预计所需时间：15 分钟
Introduction 引言
In this module, you have learned about advanced relational concepts such as functional dependencies, multi-valued dependencies, and candidate keys.
在本模块中，您学习了高级关系概念，如函数依赖、多值依赖和候选键。
Review your knowledge: 复习您的知识：
-
Functional dependency (FD): This refers to a relationship between attributes where the value of one attribute uniquely determines the value of another.
函数依赖（FD）：这指的是属性之间的关系，其中一个属性的值唯一地决定了另一个属性的值。 -
Multi-valued dependency (MVD): This describes a relationship between attributes where one attribute determines a set of possible values for another.
多值依赖（MVD）：这描述了属性之间的关系，其中一个属性决定了另一个的可能值集。 -
Candidate key: This denotes a minimal set of attributes that uniquely identifies each row in a relation.
候选键：这表示一个能唯一标识关系中每一行属性的最小集合。
Now, in this reading, let's apply the concepts learned in this module to a real-world example of a database.
现在，在这个阅读中，让我们将本模块学到的概念应用于一个现实世界的数据库实例。
Objectives 目标
After completing this reading, you will be able to evaluate your knowledge of Advanced relational model concepts.
完成这个阅读后，你将能够评估你对高级关系模型概念的理解。
Here you are going to:
在这里你将要：
- Apply advanced relational concepts like functional dependencies, multi-valued dependencies, and candidate keys to the "Car Dealership" database schema.
将函数依赖、多值依赖和候选键等高级关系概念应用于“汽车经销商”数据库模式。 - Identify constraints within the schema based on these concepts.
根据这些概念识别模式中的约束。 - Understand the impact of these concepts on data integrity and manipulation.
理解这些概念对数据完整性和操作的影响。
Exercise 练习
In this exercise, we will work on a relational database schema called Car Dealership, designed to keep track of automobile sales in a car dealership.
在这个练习中，我们将处理一个名为 Car Dealership 的关系数据库模式，该模式旨在跟踪汽车经销商的汽车销售情况。
Schema diagram for the Car Dealership relational database:
Car Dealership 关系数据库的方案图：
Relational instance of SALE:
SALE 的关系实例：
Now, let's go through some questions based on the above database schema of Car Dealership and the relational instance of SALE:
现在，让我们根据上述 Car Dealership 数据库模式和 SALE 关系实例来回答一些问题：
-
Identify FDs in the Car Dealership schema:
识别汽车经销商模式中的函数依赖（FDs）：
A. Analyze each pair of attributes in each relation (Car, Sale, Salesperson, Customer).
A. 分析每个关系中（汽车、销售、销售人员、客户）的每对属性。B. For each pair, consider if the value of one attribute always determines the value of the other.
B. 对于每对属性，考虑一个属性的值是否总是决定另一个属性的值。C. List all identified FDs for each relation.
C. 列出每个关系中所有识别出的 FDs。Answer 答案
Car: 汽车：
-
Serial_no -> (Model, Manufacturer, Price)
序列号 -> (型号，制造商，价格) -
Model -> Manufacturer 型号 -> 制造商
Sale: 销售：
-
Serial_no -> Date 序列号 -> 日期
-
Serial_no -> Sale_Price 序列号 -> 销售价格
Salesperson: 销售人员：
-
Salesperson_id -> Name 销售员 ID -> 姓名
-
Salesperson_id -> Phone 销售员 ID -> 电话
-
-
Explore MVDs: 探索多值依赖：
A. Consider if any attribute in the schema determines a set of possible values for another.
A. 考虑模式中是否任何属性决定了另一组可能的值。B. For example, does "Car Model" determine a set of possible values for "Sale Price"?
B. 例如，“汽车型号”是否确定“销售价格”的一组可能值？C. List any identified MVDs for the schema.
C. 列出已识别的模式多值依赖（MVD）。Answer 答案
No MVDs are explicitly identified in the given schema.
在给定的模式中未明确识别出任何多值依赖（MVD）。 -
Determine candidate keys:
确定候选键：
A. Analyze each relation and identify any subset of attributes that uniquely identifies each row.
A. 分析每个关系，并识别任何唯一标识每一行的属性子集。B. Remember, a candidate key must not contain any redundant attributes.
B. 请记住，候选键不能包含任何冗余属性。C. List all identified candidate keys for each relation.
C. 列出每个关系中所有已识别的候选键。Answer 答案
-
Car: Serial_no 汽车：序列号
-
Sale: Serial_no 销售：序列号
-
Salesperson: Salesperson_id
销售人员：销售人员 ID
-
-
Discuss the implications:
讨论其影响：
A. How do the identified FDs and MVDs impact data integrity and manipulation in the schema?
A. 已识别的 FDs（函数依赖）和 MVDs（多值依赖）如何影响模式中的数据完整性和操作？B. Could any data inconsistencies arise due to violating these constraints?
B. 是否可能由于违反这些约束而产生数据不一致？C. How do candidate keys affect query optimization and data retrieval?
C. 候选键如何影响查询优化和数据检索？Answer 答案
A. Data Integrity: The identified FDs and candidate keys help ensure data integrity by preventing inconsistencies:
A. 数据完整性：已识别的函数依赖和候选键通过防止不一致性来帮助确保数据完整性：-
Changing Serial_no automatically updates dependent attributes in Car.
更改 Serial_no 会自动更新 Car 中的依赖属性。 -
Serial_no and Date prevents duplicate sales and ensures association with correct car and salesperson.
Serial_no 和 Date 防止重复销售，并确保与正确的汽车和销售人员关联。 -
Unique keys (Serial_No, Salesperson_id ) guarantee distinct, identifiable entities.
唯一键（Serial_No，Salesperson_id）确保了不同实体的唯一性和可识别性。
Data Manipulation: FDs guide proper data updates. Modifying VIN requires cascading changes to dependent attributes.
数据操作：函数依赖（FDs）指导正确的数据更新。修改 VIN 需要级联更改相关属性。B. Potential Inconsistencies due to Constraint Violations:
B. 由于约束违反而产生的潜在不一致性：-
Incorrect data updates: Forgetting to update dependent attributes when modifying a determining attribute (e.g., changing Serial_no without updating Model) can lead to inconsistencies.
不正确的数据更新：在修改决定性属性时忘记更新相关属性（例如，更改 Serial_no 而不更新 Model）会导致不一致。 -
Duplicate data: if Serial No doesn't determine Price, multiple entries with the same car could have different prices.
重复数据：如果序列号不能确定价格，同一辆车可能有多个条目且价格不同。 -
Inaccurate queries: A salesperson selling a non-existent car if Salesperson_id doesn't determine Serial_no.
查询不准确：如果销售员 ID 不能确定序列号，销售员可能会销售不存在的车辆。
C. Effects on query optimization and data retrieval:
C. 对查询优化和数据检索的影响：-
Impact of candidate keys: Unique candidate keys (Serial_no for Car, Serial_no for Sale, etc.) significantly improve query performance by creating efficient indexing mechanisms.
候选键的影响：唯一的候选键（如车辆序列号、销售序列号等）通过创建高效的索引机制显著提高查询性能。 -
Fast lookups: Queries using candidate keys can quickly locate specific rows without scanning the entire table, resulting in faster data retrieval.
快速查找：使用候选键的查询可以快速定位特定行，而无需扫描整个表，从而实现更快的数据检索。 -
Reduced redundancy: Candidate keys eliminate redundant identifiers, resulting in smaller data storage requirements and potentially faster table scans for non-indexed searches.
减少冗余：候选键消除了冗余标识符，导致数据存储需求减小，并且可能使非索引搜索的表扫描速度更快。
-