普通形式：4对5 [英] Normal forms: 4 vs 5

问题描述

我正在努力理解4NF，5NF及其区别。

这里是我描述4 / 5NF的方式（或者，我将如何描述步骤）达成目标）给不认识的人。我之所以这样说是因为，这将显示出我真正理解的内容。

通常，应通过为它们可能的组合使用联接表来实现N：N实体关系。如果存在3个或更多与N：N关系相关的实体，则应仔细考虑：

1. 更一般的（包括）解决方案是实现包含所有实体作为字段并将它们的所有组合作为值（行）的联接表


2. 但是，如果这些实体之间的关系不是真的， -全元组的情况，而是
   （某些）其直角N：N关系的（笛卡尔）乘积，然后仔细考虑两字段表的最低
   数量。


3. 归纳2，总是希望（如果正确的话）具有尽可能少的字段的联接表。而且显然，如果没有使用联接表，就不要创建联接表。


4. 区分上面的一个有用技巧是，插入完成后，如果heart（！）告诉您如果您正在做冗余或无效的事情，则应该选择一种较晚的方法。

E1）Wikipedia页面的示例在4NF上：
https://en.wikipedia.org/wiki/Fourth_normal_form

我们有实体餐厅，比萨饼店和送货区。我们可以用一个包括所有三个表的联接表来实现它们的多对多关系。但是，如果正确地考虑了数据，则这些三胞胎仅是2个N：N关系的产物：Restaurant：Pizza和Restaurant：Delivery Area。如果 A1披萨餐厅决定在其曲目中加入薄皮比萨饼品种，那么我要么将同一餐厅/比萨饼种类的一行插入 A1比萨饼的所有送餐区域，那会感觉到4 .redundant，或仅插入特定的送货区域，这会造成4.无效，因为没有一家商店会在送货区域中提供较少的品种（或者至少是说我们的规格如此）。

E2）维基百科关于5NF的页面示例：
https：/ /en.wikipedia.org/wiki/Fifth_normal_form

我们有实体Salesman，Brand和Type。我们可以用一个包括所有三个表的联接表实现它们的多对多关系。但是，由于遵循以下规则部分，三元组实际上是3 N：N关系可用的（笛卡尔）乘积，因此，正确的方法是为其提供3个连接表。 请注意此设置如何帮助删除冗余。部分与我的第4点很相似。

这种情况由于以下事实而更加令人困惑：尽管文章指出还请注意该表位于4NF中 ，事实是，如果表具有所有行以覆盖遵循规则，那么它将不覆盖4NF！

所以。.E1和E2之间的
有什么区别，使其中一个成为4NF，而另一个
成为5NF示例？

解决方案

E1和E2有什么区别，使它们之一成为4NF，而另一个5NF的例子？

非4NF&非5NF关系都显示由于JD导致的更新异常； 4NF表示没有二进制JD& J的异常。 5NF表示没有任何JD异常。 Wikipedia示例将4NF规范化摆脱了二进制JD，这种关系是一个有问题的2向联接。对5NF的规范化摆脱了3向JD的关系-该关系是一个有问题的3向联接。 （因为它始于4NF，所以不可能有任何有问题的二进制JD。）

---

关系（值或变量）在5NF中时，它可以以各种方式无损地分解（即分解为与之相连的投影）（即，对应的JD（join依赖项成立）），并且可以按某种顺序将组件重新结合在一起，其中每个组件的公共列join是原始的超键。 （Fagin的PJ / NF论文的隶属度算法。）4NF的定义相同，只是4NF的无损分解方式很重要（即对应的JD为二进制）（即a

（这种具有这样的连接顺序的允许JD被称为由CK（候选键）表示。 。）

这个想法是，如果我们可以分解为与原始对象连接的投影，那么我们应该这样做，除了超级键上的连接不会引起任何问题

当FD（功能依赖项）S-> A保持具有属性集R的关系时，该关系在SU {A}&上可无损地分解。 R-{A}。因此，JD {S U {A}，R-{A}}持有& MVD S->> {A}保持。

来自哪个最高范式

关系含义/谓词

另一方面，假设您知道该关系的含义，直到
的程度，您知道它包含从a发出真实语句
的元组。 （特征）谓词可表示为其他
个连词，例如

 票证由名字为Vname 
的人提交，还有一个名字为Vname Nname 
和票的人机票是由姓Nname 
 的人提交的pre> 
 
 
 Join的设计使其输出谓词为其输入的
谓词的AND。因此，您会知道要检查原始文档的任何
对应分解是否满足JD（即
是否来自合词的关系是
原始文档的投影），然后检查JD是否
 
 
 
归一化为较高NF的一点是，当
关系的谓词可以表示为JD时，JD成立。他人与
的关系的合取是原始关系的投影，因此我们可以使用
更为简单的单独关系来代替，除非我们也可以在成对共享中JOIN / AND 
关系/谓词CK，因为仍然有
仍然没有更新异常。 （如果FD  {x，...}-> a 成立，则某个
 MVD成立&某个二进制JD成立，以及$的谓词b $ b关系可以表示为 ... AND a = f（x，...）。）
 
 

I am struggling to understand 4NF, 5NF, and their difference.

Here is the way I would describe 4/5NF (or, how I would describe the steps to achieve it) to someone who doesn't know. I am stating this because this will show what I have really understood.

Typically, a N:N entity relationship should be implemented by having a join table for their possible combinations. If there are 3 or more entities connected with N:N relationships, one should consider carefully:

1. The more general(including) solution would be to implement a join table containing all entities as fields, and all the combinations of them as values(rows)
2. However, if the relationship of these entities is not realy a per-full-tuple case, but rather the (cartesian) product of (some of) their dyadic N:N relationships, then carefully think the minimum amount of two-field tables needed.
3. Generalizing 2, always prefer (if it is correct, of course) to have join tables with as few fields as possible. And oviously, do not create a join table if there is no use for it.
4. A helpful tip to distinguish the above is, when an insert is done, if your heart(!) tells you you are doing redundunt, or invalid, things, then you should choose one of the later methods.

E1) Example of Wikipedia's page on 4NF: https://en.wikipedia.org/wiki/Fourth_normal_form

We have entities Restaurant, Pizza Variety, and Delivery Area. We could implement their many-to-many relationships with one join table including all three. However, if one thinks of the data correctly, these triplets are a product of only 2 N:N relationships: Restaurant:Pizza and Restaurant:Delivery Area. If the "A1 Pizza" Restaurant decided to include "Thin Crust" Pizza Variety to its repertoire, then I'd have to either insert one row with the same restaurant/pizza variety to all delivery areas of "A1 Pizza" which would feel 4.redundant, or only insert for a specific delivery area, which would feel 4.invalid, because no shop would offer less variety to a delivery area (or at least, let's say our specification says so).

E2) Example of Wikipedia's page on 5NF: https://en.wikipedia.org/wiki/Fifth_normal_form

We have entities Salesman, Brand, and Type. We could implement their many-to-many relationships with one join table including all three. However, because of the "the following rule applies" part, the triplets are actually a (cartesian) product of the 3 N:N relationships available, and as such, the correct method is to have 3 join tables for it. The "Note how this setup helps to remove redundancy." part is much like my 4.th point.

That case is made even more confusing by the fact that while the article states "Also note that the table is in 4NF", the truth is that if the table had all the rows it should so as to cover the "following rule", then it would not cover 4NF! Right?

So.. What is the difference between E1 and E2 which makes one of them a 4NF and the other a 5NF example?

解决方案

What is the difference between E1 and E2 which makes one of them a 4NF and the other a 5NF example?

Non-4NF & non-5NF relations both exhibit update anomalies due to JDs; 4NF means no anomalies from binary JDs & 5NF means no anomalies from JDs of any arity. The Wikipedia example normalization to 4NF got rid of a binary JD--the relation was a problematic 2-way join. The normalization to 5NF got rid of a 3-way JD--the relation was a problematic 3-way join. (Since it started in 4NF, it couldn't have had any problematic binary JDs.)

---

A relation (value or variable) is in 5NF when for every way it can be losslessly decomposed (ie into projections that join back to it) (ie a corresponding JD (join dependency) holds) the components can be joined back in some order where the common columns of each join are a superkey of the original. (Fagin's PJ/NF paper's membership algorithm.) The definition of 4NF is the same except that only the ways that it can be losslessly decomposed into two projections matters (ie the corresponding JD is binary) (ie a corresponding MVD (multi-valued dependency) holds).

(Such a permissible JD having such a sequence of joins is said to be "implied by the CKs (candidate keys)".)

The idea is that if we can decompose into projections that join back to an original then we should, except that a join on a superkey does not cause any problems/anomalies.

When a FD (functional dependency) S -> A holds in a relation with attribute set R, the relation is losslessly decomposable on S U {A} & R - {A}. So JD {S U {A}, R - {A}} holds & MVD S ->> {A} holds.

From Which highest normal form is this table in?

Relation Meanings/Predicates

On the other hand, suppose you knew the relation's meaning to the extent that you knew that it holds tuples that make a true statement from a (characteristic) predicate expressible as the conjunction of others, say

    ticket Ticket was submitted by a person with first name Vname
AND there is a person with name Vname Nname
AND ticket Ticket was submitted by a person with last name Nname

Join is designed so that the predicate of its output is the AND of the predicates of its inputs. So you would know to check for whether any corresponding decompositions of the original satisfy the JD (ie whether the relations from the conjuncts are projections of the original) and so to check whether the JD is implied by the original's CKs.

The point of normalization to higher NFs is that a JD holds when a relation's predicate can be expressed as the conjunction of others and their relations are projections of the original, so we can use the simpler separate relations instead, except we might as well JOIN/AND the relations/predicates on pairwise shared CKs because there are still no update anomalies. (If FD {x, ...} -> a holds then a certain MVD holds & a certain binary JD holds and the predicate of the relation can be expressed as ... AND a = f(x, ...).)

这篇关于普通形式：4对5的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！