database 如何确定函数依赖

How to determine the functional dependencies

提问by Shiraz

I am currently working for a University project and I'm a little bit confused now about the functional dependencies part. For this project I had to create a logical data model based on my own project specification and also determine the functional dependencies.

我目前正在为一个大学项目工作，现在我对函数依赖部分有点困惑。对于这个项目，我必须根据我自己的项目规范创建一个逻辑数据模型，并确定功能依赖项。

For example, I have given the 'User' table the following attributes.
R(user_id, username, regDate, type, subscription)

例如，我为“用户”表提供了以下属性。
R(user_id, 用户名, regDate, 类型, 订阅)

Primary key:user_id
Unique key:username
Foreign key:subscription

主键：user_id
唯一键：用户名
外键：订阅

An example data set could be something like:

示例数据集可能类似于：

1, JohnS, 01-01-2012, Administrator, NULL
2, PeterB, 02-01-2012, Moderator, Movies
3, PeterA, 02-01-2012, User, Movies
4, Gary, 03-01-2012, User, Books
5, Irene, 03-01-2012, User, Movies
6, Stan, 03-01-2012, User, Movies
7, Isaac, 04-01-2012, User, Books

1, JohnS, 01-01-2012, 管理员, NULL
2, PeterB, 02-01-2012, 主持人, 电影
3, PeterA, 02-01-2012, 用户, 电影
4, Gary, 03-01-2012, 用户, Books
5, Irene, 03-01-2012, User, Movies
6, Stan, 03-01-2012, User, Movies
7, Isaac, 04-01-2012, User, Books

The part I don't understand is how I determine the functional dependencies. My initial feeling was that there are two functional dependencies and these are:
user_id -> username, regDate, type, subscription
username -> user_id, regDate, type, subscription

我不明白的部分是我如何确定函数依赖关系。我最初的感觉是有两个函数依赖，它们是：
user_id -> username, regDate, type, subscription
username -> user_id, regDate, type, subscription

However, looking at the other examples in the lecture slides, I am having doubts whether this is correct or not.

但是，查看讲座幻灯片中的其他示例，我怀疑这是否正确。

回答by Mike Sherrill 'Cat Recall'

If "username" is both unique and required (unique and not null), then it's a candidate key. In relational modeling, there's no theoretical difference between one candidate key and another. More specifically, in relational modeling, there's no theoretical reason to pick one candidate key and label it "primary key". A key is a key.

如果“用户名”既是唯一的又是必需的（唯一且非空），那么它就是一个候选键。在关系建模中，一个候选键和另一个候选键之间没有理论上的区别。更具体地说，在关系建模中，理论上没有理由选择一个候选键并将其标记为“主键”。一把钥匙就是一把钥匙。

So you're right. There are two functional dependencies here. (Or 8, if you decompose the right-hand-side into individual columns. user_id -> username, user_id -> regDate, etc.)

所以你是对的。这里有两个函数依赖。（或者8，如果你分解右手侧成单独的列。user_id -> username，user_id -> regDate等）

回答by Jordan Parmer

Functional dependencies are defined from a theoretical perspective as follows (Wikipedia):

函数依赖从理论角度定义如下（维基百科）：

Given a relation R, a set of attributes X in R is said to functionally determine another set of attributes Y, also in R, (written X → Y) if, and only if, each X value is associated with precisely one Y value; R is then said to satisfy the functional dependency X → Y.

给定一个关系 R，R 中的一组属性 X 被称为在功能上确定另一组属性 Y，也在 R 中，（写成 X → Y）当且仅当每个 X 值都与一个 Y 值相关联；则称 R 满足函数依赖 X → Y。

From a technical perspective, you are trying to find attributes that uniquely identify other attributes. As a shortcut, determine your candidate keysand the attributes that depend on them. Your examples are correct because a username, regDate, type, and subscriptionall depend on the value of user_id. If usernameis unique andnot null, it is a candidate key and also identifies the set of attributes.

从技术角度来看，您正在尝试查找唯一标识其他属性的属性。作为一种快捷方式，确定您的候选键和依赖于它们的属性。您的示例是正确的，因为 ausername, regDate, type, and subscription都取决于 的值user_id。如果username是独特的和不为空，这是一个候选键也标识属性集。

回答by Branko Dimitrijevic

In addition to what others have said, if an attribute (or a set of attributes) is a candidate key, then allthe attributes must functionally depend on it.

除了其他人所说的，如果一个属性（或一组属性）是候选键，那么所有属性在功能上都必须依赖于它。

• A "functional dependency" A->B simply means that no two different values of B are ever related to the same A. Slightly more formal definition is given on Wikipedia, but that's essentially it.
• Since a key must be unique, even if two tuples contain the same value of some attribute(s), the key values must be different nonetheless. So, different values can never relate to the same key value.

• “函数依赖” A->B 仅仅意味着 B 的两个不同值永远不会与同一个 A 相关。维基百科上给出了更正式的定义，但本质上就是这样。
• 由于键必须是唯一的，即使两个元组包含某些属性的相同值，键值也必须不同。因此，不同的值永远不会与相同的键值相关联。

Since all attributes are functionally dependent on the key(s), if there is any otherfunctional dependency, you automatically have a transitive dependencyand a violation of the 3NF. So a "non-key" dependency can act as a red flag for spotting normalization errors.

由于所有属性在功能上都依赖于键，如果有任何其他功能依赖，您将自动具有传递依赖和对 3NF 的违反。因此，“非关键”依赖项可以作为发现规范化错误的危险信号。

---

---

You can think of it from the opposite direction as well: first figure out which functional dependencies make sense in your domain, then use them to identify which attributes could act as keys.

您也可以从相反的方向考虑：首先找出哪些函数依赖在您的域中有意义，然后使用它们来确定哪些属性可以充当键。

回答by Lajos Arpad

I will assume you are using MySQL, but if not, you can implement your idea in any other RDBMS.

我假设您正在使用 MySQL，但如果没有，您可以在任何其他 RDBMS 中实现您的想法。

Run the following command to get all your tables:

运行以下命令以获取所有表：

show tables;

Then iterate all the tables and run the following command for each of them:

然后迭代所有表并为每个表运行以下命令：

show columns;

FDs can be described as follows:

FD 可以描述如下：

Determinant -> Dependent,
Determinant = {A1, ..., Am},
Dependent = {B1, ..., Bn}

where Aiand Bjare columns. You need to generate all possible scenarios for Determinantand Dependent. For each scenario you will need to view whether exists at least two separate records where the determinant columns match and at least one of the dependent columns do not match. If so, then the scenario is not an FD, otherwise it is an FD. Example: Let's assume, that m = 3 and n = 2:

whereAi和Bjare 列。您需要为Determinant和生成所有可能的场景Dependent。对于每种情况，您都需要查看是否存在至少两个单独的记录，其中行列式列匹配而至少一个相关列不匹配。如果是，则该场景不是 FD，否则就是 FD。示例：让我们假设 m = 3 且 n = 2：

select count(*) from mytable t1, mytable t2 where ((t1.A1 = t2.A1) and (t1.A2 = t2.A2) and (t1.A3 = t2.A3)) and ((t1.B1 <> t2.B1) or (t1.B2 <> t2.B2))

will return the number of records which break the FD-rule. If the value is 0, then the scenario is an FD.

将返回违反 FD 规则的记录数。如果值为 0，则场景为 FD。

Of course, in your particular case you can omit a few steps, and you have your columns instead of Aiand Bj, but you hopefully understand the idea.

当然，在您的特定情况下，您可以省略几个步骤，并且您有您的列而不是Aiand Bj，但您希望理解这个想法。