正常形式 - 第二和第三 - 是区别只是复合键？非平凡依赖？ [英] Normal forms - 2nd vs 3rd - is the difference just composite keys? non trivial dependency?

问题描述

我已查看过这篇文章，但我并不真正了解使用的字词（非平凡函数依赖， superkey）

从我读到的第二个正常形式似乎与复合键相关，而第三个正常形式与主键相关。

我不知道这是否正确。

所以第二个正常表单 - 有一个复合键，表中的所有字段必须与两个复合键字段相关。如果某些东西不相关，那么它应该被重构到另一个表中。

第三普通形式 - 一切都必须依赖于主键 - 所以我猜测在第三普通形式只有一个键，而不是第二普通形式可以有复合键？

任何建议。

解决方案

集合的正确子集或超集不等于它。

我们说，一组列S在表中的每个子行总是出现与第二个相同的子行时，在功能上确定另一个集合T.我们写S - > T，说S是行列式集合，T是确定的集合。我们称S→T是函数依赖（FD）。当S是T的超集时，我们说它是一个平凡的FD。当A是T中的列时，我们说S函数地确定A。

超级键是唯一标识行的一组列。候选键（CK）是不包含适当的超级键的超级键。我们可以选择CK作为主键（PK）。

这足以理解答案：

2NF和3NF之间的区别是这样的。假设某些关系
满足形式A-> B的非平凡函数依赖性，其中
B是非初级属性。

2NF如果A不是超级键，但是是
候选键的正确子集，则违反

如果A不是超级键，则违反3NF

当且仅当使用行列式的正确子集给出具有相同确定列的FD时，FD是部分的;否则为满。注意，这不涉及CK。当所有非主列完全功能上依赖于每个CK时，表是在2NF中。当存在X时，S→T是可传递的，其中S→X和X→T而不是（X→S）。注意，这不涉及CK。当它在2NF中时，表是在3NF中，并且所有非素数列是非传递依赖于每个CK。

I've viewed This post but I don't really understand the terms used (non-trivial function dependency, superkey)

From what I've read 2nd normal form seems to relate to composite keys whereas 3rd normal form relates to primary keys.

I'm not sure if this is correct though.

So 2nd normal form - there's a composite key and all fields in the table must relate to both of the composite key fields. If something doesn't relate then it should be refactored into another table.

3rd normal form - everything has to be dependent on the primary key - so I'm guessing that in 3rd normal form there's only 1 key rather than in 2nd normal form where there can be composite keys?

Any advice appreciated.

解决方案

A proper subset or superset of a set is one that is not equal to it.

We say that a set of columns S functionally determines another set T in a table when each subrow for the first always appears with the same subrow for the second. We write S -> T and say S is the determinant set and T is the determined set. We call S -> T a functional dependency (FD). When S is a superset of T we say it is a trivial FD. When A is a column in T we say S functionally determines A.

A superkey is a set of columns that uniquely identifies rows. A candidate key (CK) is a superkey that contains no proper superkey. We can pick a CK as primary key (PK). A column is prime when it is in some CK.

That's enough to understand that answer:

The difference between 2NF and 3NF is this. Suppose that some relation satisfies a non-trivial functional dependency of the form A->B, where B is a nonprime attribute.

2NF is violated if A is not a superkey but is a proper subset of a candidate key

3NF is violated if A is not a superkey

A FD is partial if and only if using a proper subset of the determinant gives a FD with the same determined column; otherwise it is full. Note that this does not involve CKs. A table is in 2NF when all non-prime columns are fully functionally dependent on every CK.

S -> T is transitive when there is an X where S -> X and X -> T and not(X -> S). Note that this does not involve CKs. A table is in 3NF when it is in 2NF and all non-prime column is non-transitively dependent on every CK.

这篇关于正常形式 - 第二和第三 - 是区别只是复合键？非平凡依赖？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！