BCNF:寻找例如实际使用超级键而不是候选键的示例 [英] BCNF: Looking for example that actually uses superkey instead of candidate key

问题描述

Boyce–Codd范式的定义指出，所有非平凡的函数依赖项的决定因素都必须是超键.

The definition of the Boyce–Codd normal form states that the determinants of all non-trivial functional dependencies have to be superkeys.

我发现的BCNF中所有关系示例均使用候选键.我正在寻找一个示例，该示例实际上有一个超键作为行列式，而不是候选键.

All the examples for relations in BCNF I found make use of candidate keys. I am looking for an example that actually has a superkey as determinant which is not a candidate key.

我无法提出仅使用超级键的关系，而超级键不能转换为使用候选键.

I fail to come up with a relation that only uses superkeys which can't be transformed to use candidate keys.

比方说，我们与候选键有关系，而附加功能依赖关系则由超键作为决定因素.

Let's say we have a relation with an candidate key and an additional functional dependency with a superkey as determinant.

R1(A,B,C)
{A}
A,B -> C

此额外的FD是多余的，因为它包含明显确定其他属性(A-> C)的候选键.

This additional FD is redundant because it contains an candidate key that obviously detemines the other attribute (A -> C).

尝试用两个候选键构建另一个示例也是没有用的.

Trying to build another example with two candidate keys is also useless.

R2(A,B,C,D)
{A,B},{B,C}
A,B,C -> D

这与上面的问题完全相同.

This has the exact same problem as above.

我实际上在想是否还有没有候选键的示例.但是，为什么定义会超出必要范围呢?还是定义是等效的，因为依赖项总是可以转换的?

I am actually wondering if there even is an example without candidate keys. But why would the definition be broader than necessary? Or are the definitions equivalent as the dependencies can always be transformed?

推荐答案

要点是，定义普通格式时，我们必须以普通格式将其表示为 all 的属性保持某种关系的功能依赖性.

The point is that, when defining a normal form, we must express it in a general form, as a property of all the functional dependencies holding on a certain relation.

相反，当我们推理特定的关系模式时，通常通常只有所有功能依赖项的一个子集(因为它们的数量可能太大，可能与属性的数量成指数关系).通常使用字母F表示的一组特定依赖项具有一个特殊的属性:它是关系中所有依赖项的 cover ，也就是说，我们可以从中推导所有依赖项.通过以所有可能的方式应用一组称为阿姆斯特朗公理的公理来实现这种关系.

Instead, when we reason about a particular relation schema, we usually have only a subset of all the functional dependencies (since their number can be too large, being possibly exponential with the number of attributes). The particular set of dependencies used, denoted usually by the letter F, has a special property: it is a cover of all the dependencies holding in the relation, that is from it we can derive all the dependencies of the relation by applying, in all the possible ways, a set of axioms, called Armstrong’s axioms.

F是在关系模式中与属性一起指定的一组依赖关系，可以用不同的方式给出:例如，在练习中，可以将它们作为练习的输入来给出；在实际的数据库设计中，它们可以描述一组对建模某个实词域等重要的约束条件，等等.

F, the set of dependencies specified together with the attributes in a relational schema, can be given in different ways: for instance in an exercise they can be given as input to the exercise, in real database design they can describe a set of constraints considered important for modelling a certain real-word domain, etc.

即使它们是从有关要通过数据库建模的情况的知识中提取的，它们也可能包含其他已经给出的隐含依赖关系，或者可能包含冗余属性等.

Even if they are extracted from the knowledge about a situation to be modeled through a database, they could contain dependencies implied by others already given, or maybe can contain redundant attributes, etc.

由于这些原因，找到给定依赖项集的规范封面是规范化的重要第一步，即由一组依赖项构成的封面:关键部分只有一个属性； b)左侧没有多余的属性(即可以保留为封面的属性而可以删除的属性)； c)没有多余的依存关系(即可以通过阿姆斯特朗公理从其他人得到的依存关系).

For these reasons, it is considered an important first step in normalization to find the canonical cover of the set of dependencies given, that is a cover constituted by a set of dependencies that: a) have only a single attribute on the rigth part; b) do not have superflous attributes on the left part (i.e. attributes that can be removed maintaing the property of being a cover); c) do not have redundant dependencies (i.e. dependencies that can be derived from others through the Armstrong’s axioms).

现在让我们考虑BCNF的一般定义:

Now let’s consider the general definition of BCNF:

< p>关系模式R< T，F>当且仅当对于F ⁺ 的每个非平凡依赖项X→Y，X是超键时，BCNF才是.

A relation schema R<T,F> is in BCNF if and only if for each non-trivial dependency X → Y of F⁺, X is a superkey.

请注意，我们正在谈论的是F ⁺ 中的依赖关系，它是F的 closure ，换句话说，它包含 all R中包含的依赖项，并以某种方式从F派生.因此，如果关系R具有候选键X _K ，则显然不仅X _K →T保持，对于实例，但是对于X _K 的所有超集S，我们将拥有S→T成立，因此普通形式 的定义必须允许这些依赖项.

Note that the we are talking about the dependencies in F⁺, which is the closure of F, in other words, which contains all the dependencies holding in R and derived in some way from F. So if the relation R has a candidate key X_K, obviously not only X_K → T holds, for instance, but for all the supersets S of X_K we will have that S → T holds, and so the definition of the normal form must allow those dependencies.

现在，可以从BCNF的一般定义证明以下定理，以某种方式简化了该定理(并使检查BCNF中是否存在关系的测试效率更高):

Now, it is possible to prove, from the general definition of BCNF, the following theorem, that in some way simplifies it (and makes efficient the test to check if a relation is already in BCNF):

定理:关系图式R< T，F>当且仅当对于F的每个非平凡依赖项X→Y，X是超键时，BCNF才存在.

Theorem: A relation schema R<T,F> is in BCNF if and only if for each non-trivial dependency X → Y of F, X is a superkey.

看到区别了吗?我们现在谈论的是F，而不是F ⁺ .

See the difference? We are now talking about F and not F⁺.

该定理具有以下推论:

推论:一种关系模式R< T，F>.当且仅当对于F的每个非平凡依赖项X→A，X是候选键时，其中F是规范封面在BCNF中.

Corollary: A relation schema R<T,F> in which F is a canonical cover, is in BCNF if and only if for each non-trivial dependency X → A of F, X is a candidate key.

由于规范封面中的依赖项没有多余的属性，因此，如果关系在BCNF中，则每个行列式(功能依赖项的左侧)显然都是候选键(而不是泛型超键)，这解释了差异在定义和我们有时在书上找到的例子之间.

Since the dependencies in a canonical cover do not have superfluous attributes, if the relation is in BCNF every determinant (left hand side of a functional dependency) is obviously a candidate key (not a generic superkey), and this explain the difference between the definition and the examples that sometimes we found on the books.

这篇关于BCNF:寻找例如实际使用超级键而不是候选键的示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！