database 函数依赖和规范化

Functional dependency and normalization

提问by aherlambang

I am trying to find a great resource to study for functional dependency and normalization.

我试图找到一个很好的资源来研究功能依赖和规范化。

Anyone have any idea where should I look to? I am having difficulty differentiating whether a FD is in 1NF, 2NF or 3NF?

任何人都知道我应该去哪里寻找？我很难区分 FD 是 1NF、2NF 还是 3NF？

I've been reading Wikipedia and used Google search to find good research, but can't find any that explains it in simple terms.

我一直在阅读维基百科并使用谷歌搜索来找到好的研究，但找不到任何简单的解释。

Maybe you all can share on how you learned FD's and normalization during your life as well.

也许你们都可以分享你在生活中是如何学习 FD 和正常化的。

回答by NealB

A functional dependency defines a functional relationship between attributes. For example: PersonIdfunctionally determines BirthDate(normally written as PersonId -> BirthDate). Another way of saying this is: There is exactly one Birth Date for any given instance of a person. Note that the converse may or may not be true. Many people may have been born on the same day. Given a BirthDatewe may find many PersonIdsharing that date.

函数依赖定义了属性之间的函数关系。例如：PersonId功能确定BirthDate（通常写为PersonId -> BirthDate）。另一种说法是：对于一个人的任何给定实例，都只有一个出生日期。请注意，反过来可能是也可能不是。许多人可能在同一天出生。鉴于 aBirthDate我们可能会发现许多PersonId共享该日期。

Sets of functional dependencies may be used to synthesize relations (tables). The definition of the first 3 normal forms, including Boyce Codd Normal Form (BCNF) is stated in terms of how a given set of relations represent functional dependencies. Fourth and fifth normal forms involve Multi-Valued dependencies (another kettle of fish).

函数依赖集可用于合成关系（表）。前 3 个范式的定义，包括 Boyce Codd 范式 (BCNF)，是根据给定的关系集如何表示函数依赖关系来说明的。第四和第五范式涉及多值依赖（另一锅鱼）。

Here are a few free resources about Functional Dependencies, Normalization and database design. Be prepared to exercise your brain and math skills when studying this material.

这里有一些关于函数依赖、规范化和数据库设计的免费资源。在学习这些材料时，准备好锻炼你的大脑和数学技能。

The following are "slide shows" from various academic sites...

以下是来自各个学术网站的“幻灯片”...

• Functional Dependencies
• Functional Dependencies and Normalization for Relational Databases
• The Relational Data Model: Functional-Dependency Theory

• 功能依赖
• 关系数据库的功能依赖和规范化
• 关系数据模型：功能依赖理论

The following are academic papers. Heavier reading but well worth the effort.

以下为学术论文。阅读量更大，但值得付出努力。

• The Application of Functional Dependency Theory to Relational Databases
• A Simple Guide to Five Normal Forms in Relational Database
• Simple Conditions for Guaranteeing Higher Normal Forms in Relational Databases

• 函数依赖理论在关系数据库中的应用
• 关系数据库中五种范式的简单指南
• 关系数据库中保证更高范式的简单条件

If you are seriously interested in this subject I suggest you put out the cash for a good book on the subject of Relational Database Design. For example: An Introduction to Database Systems by C.J. Date

如果您对这个主题非常感兴趣，我建议您花钱买一本关于关系数据库设计主题的好书。例如：CJ Date 数据库系统简介

回答by Agnieszka

A functional dependency is a constraint between columns of your table. For example in the table person:

函数依赖是表列之间的约束。例如在表中的人：

SSN         | Name         | Date of birth | Address     | Phone number 
------------------------------------------------------------------------
123-98-1234 | Cindy Cry    | 15-05-1983    | Los Angeles | 123-456-7891
121-45-6145 | John O'Neill | 30-01-1980    | Paris       | 568-974-2562
658-78-2369 | John Lannoy  | 30-01-1980    | Dallas      | 963-258-7413

Here, the value in the column SSN (Social Security Number) determines the values in columns name, date of birth, address and phone number. This means that if we had two rows with the same value in the SSN column, then values in columns name, date of birth, address and phone number would be equal. A person with SSN 123-98-1234 is always called Cindy Cry, is born on 15-05-1983, and so on. A situation like this is called functional dependency.

此处，SSN（社会安全号码）列中的值决定了姓名、出生日期、地址和电话号码列中的值。这意味着如果我们在 SSN 列中有两行具有相同的值，那么列名称、出生日期、地址和电话号码中的值将相等。SSN 123-98-1234 的人总是被称为 Cindy Cry，出生于 15-05-1983，依此类推。这种情况称为功能依赖。

The notion of functional dependencies is used to define second, and third normal form, and the Boyce-Codd normal form (BCNF).

函数依赖的概念用于定义第二和第三范式，以及 Boyce-Codd 范式 (BCNF)。

To read more about functional dependencies and normalization you can go to then well-known academic books like Introduction to Databases by C.J. Date, or any of the books by the H. Garcia-Molina, J.Ullman, J.Widom trio.

要阅读有关函数依赖项和规范化的更多信息，您可以阅读当时著名的学术书籍，例如 CJ Date 的数据库介绍，或H. Garcia-Molina、J.Ullman、J.Widom 三人组的任何书籍。

If you want a less formal approach, we're starting a series of posts on data normalization on our company blog.

如果您想要一种不太正式的方法，我们将在公司博客上发布一系列有关数据规范化的文章。

回答by SBTec

We can understand functional dependency in a way that assume we have two attribute and one attribute in totally dependent on the other is called functional dependency.

我们可以通过假设我们有两个属性并且一个属性完全依赖另一个属性的方式来理解函数依赖，这称为函数依赖。

Say take a real life example. We know that everybody has a social security number against the name of the person . Say Frank is a person and we want to know the social security number of this person but database will be unable to help out with this information because there may be many persons named frank but we can determine name of person against social security number so name of the person is totally functional dependent on Social security number.

说一个现实生活中的例子。我们知道，每个人的姓名都有一个社会安全号码。假设 Frank 是一个人，我们想知道这个人的社会安全号码，但数据库将无法帮助提供此信息，因为可能有很多人名为 Frank，但我们可以根据社会安全号码确定人名，所以 name of该人的功能完全取决于社会安全号码。

回答by nvogel

A functional dependency isn't in any normal form. Normal Form is a property of a relation that satisfies some given set of FDs.

函数依赖不是任何正常形式。范式是满足某些给定 FD 的关系的属性。

Don't rely on Wikipedia or Google. There is too much misinformation and rubbish from online sources. Try:

不要依赖维基百科或谷歌。来自在线资源的错误信息和垃圾太多。尝试：

Introduction to Database Systems by Chris Date

Chris Date 数据库系统简介

Practical Issues in Database Management by Fabian Pascal

Fabian Pascal 在数据库管理中的实际问题

回答by Premraj

What is Functional Dependency?

什么是函数依赖？

Functional Dependencies are fundamental to the process of Normalization Functional Dependency describes the relationship between attributes(columns) in a table. In other words, a dependency FD: X → Y means that the values of Y are determined by the values of X. Two tuples sharing the same values of X will necessarily have the same values of Y.

函数依赖是规范化过程的基础 函数依赖描述了表中属性（列）之间的关系。换句话说，依赖 FD: X → Y 意味着 Y 的值由 X 的值决定。共享相同 X 值的两个元组必然具有相同的 Y 值。

What is Database Normalization?

什么是数据库规范化？

Database Normalization is a step wise formal process that allows us to decompose Database Tables in such a way that both Data Redundancy and Update Anomalies(see above for more info on update anomalies) are minimized.

数据库规范化是一个逐步的正式过程，它允许我们以最小化数据冗余和更新异常（有关更新异常的更多信息，请参见上文）的方式分解数据库表。

Courtesy

礼貌

回答by rashedcs

Functional Dependency

功能依赖

Functional dependency can be defined as relationship that exits between attributes in relation. Functional dependencies are used to create relation in Boyce Codd Normal Form abbreviated as BCNF. If C and D are the attributes of relation R and attribute C functionally determines attributes D then the functional dependency between both attribute can be expressed as C->D.

功能依赖可以定义为相关属性之间存在的关系。函数依赖用于在 Boyce Codd 范式中创建关系，缩写为 BCNF。如果 C 和 D 是关系 R 的属性，并且属性 C 在功能上决定了属性 D，那么这两个属性之间的功能依赖可以表示为 C->D。

Example : Below the person schema

示例：在人员架构下方

person(national_id, name, address);

人（national_id，姓名，地址）；

Here national_id functionally determines the person_name. So functional dependency is national_id -> name;

这里 national_id 在功能上决定了 person_name。所以函数依赖是national_id -> name;

Functional dependency is important in relational database design for the purpose of eliminating redundancy.

功能依赖在关系数据库设计中很重要，目的是消除冗余。