了解数据库规范化 - 第二范式(2NF)

问题描述

我一直在从Elmasri 和 Navathe 的数据库系统基础(第 6 版)"中学习规范化，但我无法理解以下关于 2NF 的部分.

I have been learning Normalization from "Fundamentals of Database Systems by Elmasri and Navathe (6th edition)" and I am having trouble understanding the following part about 2NF.

下图是课本中2NF下的一个例子

The following image is an example given under 2NF in the textbook

候选键是{SSN,Pnumber}依赖项是SSN,Pnumber -> hours, SSN -> ename, pnumber->pname, pnumber -> plocation

The candidate key is {SSN,Pnumber}The dependencies areSSN,Pnumber -> hours, SSN -> ename, pnumber->pname, pnumber -> plocation

正式定义:

A relation schema R is in 2NF if every nonprime attribute A in R is
fully functionally dependent on the primary key of R.

例如上图:

如果假设，我定义了一个额外的函数依赖 SSN -> 小时，然后取两个函数依赖，

if suppose, I define an additional functional dependency SSN -> hours, then taking the two functional dependencies,

{SSN,Pnumber} -> hours and SSN -> hours

关系不会是 2NF，因为现在 SSN ->hours 现在是部分函数依赖，因为 SSN 是给定候选键 {SSN,Pnumber} 的真子集.

the relation wont be in 2NF, because now SSN ->hours is now a partial functional dependency as SSN is a proper subset for the given candidate key {SSN,Pnumber}.

看一下2NF上的关系及其一般定义，我假设上述关系在2NF中

Looking at the relation and its general definition on 2NF, i presume that the above relation is in 2NF

就我的理解以及我如何理解 2NF 是什么，

As far as my understanding goes and how i understand what 2NF is,

A relation is in 2NF if one cannot find a proper subset (prime attributes)
of the on the left hand side (candidate key) of a functional dependency
which defines the NPA(non prime attribute).

我的第一个问题是，为什么上述关系不在 2NF 中?(教科书已经考虑了上述关系在2NF中不存在)

My first question is, Why is the above relation not in 2NF? (The textbook has considered the above relation as not in 2NF)

然而，本章开头定义了一种非正式的方法(按照教科书的步骤，不知道规范化的正常人可以采取的步骤来减少冗余):

There is, however, a informal ways(steps as per the textbook where a normal person not knowing normalization can take to reduce redundancy) being defined at the beginning of this chapter which are:

■ Making sure that the semantics of the attributes is clear in the schema
■ Reducing the redundant information in tuples
■ Reducing the NULL values in tuples
■ Disallowing the possibility of generating spurious tuples

所提到的指导方针如下:

The guideline mentioned is as follows:

我的第二个问题是，如果考虑到上面描述的步骤，并考虑为什么下面的关系不在2NF中，你是否假设以下函数依赖，它们是，

My second question is, If the above steps described are taken into account, and consider why the following relation is not in 2NF, do you assume the following functional dependencies, which are,

{SSN,Pnumber} -> Pname
{SSN,Pnumber} -> Plocation
{SSN,Pnumber} -> Ename

使关系的分解正确吗?如果假设的函数依赖不正确，那么导致关系不满足 2NF 条件的因素是什么?

making the decomposition of the relation correct? If the functional dependencies assumed are incorrect, then what are the factors leading for the relation to not satisfy 2NF condition?

从一般的角度来看......因为该表包含多个主要属性，并且存储的信息涉及员工和项目信息，可以指出这些需要分开，因为 Pnumber 是复合键的一个主要属性，冗余可以以某种方式直观地猜测.这是因为我们知道属性的语义.

When looked at a general point of view ... because the table contains more than one primary attributes and the information stored is concerned with both employee and project information, one can point out that those need to be separated, as Pnumber is a primary attribute of the composite key, the redundancy can somehow be intuitively guessed. This is because the semantics of the attributes are known to us.

如果属性被替换为 A、B、C、D、E、F 会怎样

what if the attributes were replaced with A,B,C,D,E,F

我的第三个问题是，函数依赖是否是基于数据库的功能和具有属性领域知识的数据库设计者"预先确定的?

My Third question is, Are functional dependencies pre-determined based on "functionalities of database and a database designer having domain knowledge of the attributes" ?

因为基于给定点的数据和关系状态，函数依赖可以改变，在一个状态下有效的在某个状态下可能变得无效.一般来说，这可以说是任何非主要属性决定非主要属性.

Because based on the data and relation state at a given point the functional dependencies can change which was valid in one state can go invalid at a certain state.In general this can be said for any non primary attribute determining non primary attribute.

正式定义:

A functional dependency, denoted by X → Y, between two sets of
attributes X and Y that are subsets of R specifies a constraint on the
possible tuples that can form a relation state r of R. The constraint is
that, for any two tuples t1 and t2 in r that have t1[X] = t2[X], they must
also have t1[Y] = t2[Y].

那么预定义函数依赖不会是错误的，因为 on 不能在任何给定点泛化关系状态?

So won't predefining a functional dependency be wrong as on cannot generalize relation state at any given point?

如果我对事物的基本理解一开始就有缺陷，请原谅我.

Pardon me if my basic understanding of things is flawed to begin with.

推荐答案

您对 2NF 的原始/第一个/非正式定义"是乱码且没有帮助.甚至教科书中的引用也是错误的，因为 2NF 不是根据PK(主键)"定义的，而是根据所有 CK(候选键)定义的.(如果只有一个 CK，他们的定义才有意义.)

Your original/first/informal "definition" of 2NF is garbled and not helpful. Even the quote from the textbook is wrong since 2NF is not defined in terms of "the PK (primary key)" but rather in terms of all the CKs (candidate keys). (Their definition makes sense if there is only one CK.)

当非主属性对 CK 没有部分依赖时，表处于 2NF.即，当非主要属性的决定因素是 CK 的适当/较小子集时.即当每个非主属性在功能上完全依赖于每个 CK 时.

A table is in 2NF when there are no partial dependencies of non-prime attributes on CKs. Ie when no determinant of a non-prime attribute is a proper/smaller subset of a CK. Ie when every non-prime attribute is fully functionally dependent on every CK.

这里唯一的CK是{Ssn, Pnumber}.但是有 {Ssn} 和 {Pnumber} 之外的 FD(函数依赖)，它们都是 CK 的较小子集.所以原始表在 2NF 中不是.

Here the only CK is {Ssn, Pnumber}. But there are FDs (functional dependencies) out of {Ssn} and {Pnumber}, both of which are smaller subsets of the CK. So the original table is not in 2NF.

如果考虑到上面的说法，你是否假设了下面的函数依赖

那么每次遇到这种情况时，仅基于非正式方式所显示的相同分解过程会不会很困难?

一个表保存着将一些谓词(由列名参数化的语句模板)变成一个真正的命题(语句)的行.给定业务规则，只能出现某些业务情况.然后给定表谓词，从业务情况给出表值，只能出现某些数据库值.这导致某些表具有某些 FD.

A table holds the rows that make some predicate (statement template parameterized by column names) into a true proposition (statement). Given the business rules, only certain business situations can arise. Then given the table predicates, which give table values from a business situation, only certain database values can arise. That leads to certain tables having certain FDs.

然而，给定一些成立的 FD，我们可以正式使用阿姆斯壮公理来获得所有其他也必须成立的 FD.因此，我们可以使用非正式和正式的方式来找出哪些 FD 成立，哪些不成立.

However, given some FDs that hold, we can formally use Armstrong's axioms to get all other FDs that must also hold. So we can use both informal and formal ways to find which FDs hold and don't hold.

还有遵循公理的速记规则.例如，如果一组属性在每个元组中具有不同的子行值，那么它的每个超集也是如此.例如，如果 FD 成立，则其行列式的每个超集确定其确定集的每个子集.例如，超键的每个超集都是超键 &没有一个 CK 的真子集是一个 CK.还有算法.

There are also shorthand rules that follow from the axioms. Eg if a set of attributes has a different subrow value in each tuple then so does every superset of it. Eg if a FD holds then every superset of its determinant determines every subset of its determined set. Eg every superset of a superkey is a superkey & no proper subset of a CK is a CK. There are also algorithms.

是否根据数据库的功能和具有属性领域知识的数据库设计者"预先确定了函数依赖关系?

在规范化时，我们关心的是无论业务情况如何，即数据库状态如何，都保持不变的 FD.每个业务的每个表都可以具有每个表谓词的特定 FD可能的业务情况.

When normalizing we are concerned with the FDs that hold no matter what the business situation is, ie what the database state is. Each table for each business can have its own particular FDs per the table predicate & the possible business situations.

PS 当它们的定义是根据现实世界时，请根据现实世界理解"形式事物.例如，将谓词应用于所有可能的情况以获取所有可能的表值.但是一旦你有了必要的正式信息，就只使用正式的定义和程序.例如，确定一个 FD 对一个表是成立的，因为它包含在每个可能的表值中.

PS Do "make sense" of formal things in terms of the real world when their definitions are in terms of the real world. Eg applying a predicate to all possible situations to get all possible table values. But once you have the necessary formal information, only use formal definitions and procedures. Eg determining that a FD holds for a table because it holds in every possible table value.

那么基于具有复合主键的表的单独条件，任何通用表都在 2NF 中吗?

在 5NF 中有表格(因此也是所有较低的 NF)，其中包含各种复合和非复合 CK.PK 无关紧要.

There are tables in 5NF (hence too all lower NFs) with all sorts of mixes of composite & non-composite CKs. PKs don't matter.

人们经常错误地说，没有复合 CK 就保证了 2NF.一个没有复合键且其中 {} 不能确定任何属性的表属于 2NF.但是，如果 {} 确定一个属性，那么它是具有任何属性的任何/每个 CK 的适当/较小子集.{} 确定该属性的每一行都必须具有相同的值时.

It is frequently wrongly said that having no composite CKs guarantees 2NF. A table without composite keys and where {} does not determine any attribute is in 2NF. But if {} determines an attribute then it's a proper/smaller subset of any/every CK with any attributes. {} determines an attribute when every row has to have the same value for that attribute.

这篇关于了解数据库规范化 - 第二范式(2NF)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！