确定该数据是否真的是第四范式? [英] Determining if this data is really in 4th normal form?
问题描述
我有一些-公司，位置和产品详细信息可存储在数据库中.
I got a few - company, location and product details to store in a db.
sample data
company location product
------------------------------
abc hilltop alpha
abc hilltop beta
abc riverside alpha
abc riverside beta
buggy underbridge gama
buggy underbridge theta
buggy underbridge omega
据我了解，这种关系是多值的.而且数据需要按照MVD进行归一化
The relationships are multi-valued, as I understand. And the data needs to be normalized as the MVD's are
- 不是从候选密钥(公司不是候选密钥的公司->>位置和公司->>产品衍生)
- 或者并集不构成整个集合(公司U位置< R等与产品相同).
但是我的同事不同意我的看法，他坚持认为对于具有多值依赖关系的关系，每个公司的公司列中至少应存在四个相同的值.即
t1(公司)= t2(公司)= t3(公司)= t4(公司)，
对于公司abc，这是事实.但是对于在三个地方只生产一种产品的越野车"公司来说，这是不正确的.
- not derived from a candidate key (company ->> location and company ->> product where company is not a candidate key)
- or the union does not make the whole set (company U location < R and so with product).
But my colleague disagrees with me, who insists that for a relation to have multi-valued dependency at least four same values in company column should exist for each company. i.e
t1(company) = t2(company) = t3(company) = t4(company),
for company abc this is true. But for company "buggy", which does only one product in three locations, this is untrue.
对于我所引用的正式定义和类似示例: https://en.wikipedia.org/wiki/Multivalued_dependency 和wiki上的Fourth_normal_form示例.
For the formal definition and similar examples I refernced: https://en.wikipedia.org/wiki/Multivalued_dependency and Fourth_normal_form example also on wiki.
我知道我的同事正在接受教学法，但在阅读正式定义后，我也开始看到相同的问题.(毕竟这些都是在数学基础上得出的.)
I know my colleague is being pedagogy, but I too started seeing the same question after reading the formal definition. (After all these are derived on mathematical basis.)
更新:我不是在问如何将这些数据标准化为4NF，我想我知道这一点.(我需要将其分为两个表1)公司-位置和2)公司-产品.
我已经完成了.
update: I am not asking how to normalize this data in to 4NF, I think I know that. (I need to break it in to two tables 1) company - location and 2) company - product.
which I have done already.
有人可以解释即使不符合正式定义，该关系如何仍是MVD?非常欢迎详细的解释.
Can some one explain how this relation is still a MVD even though it does not satisfy the formal definition? Detailed explanations are very much welcome.
推荐答案
存在"表示存在一些值，它们不一定必须与其他不同. EXISTS后跟一些名称，表示存在由名称所指的一些值，并有条件成立.多个名称可以引用相同的值.(所有人"都可以用存在"来表示.)
"There exist" says some values exist, and they don't have to be different. EXISTS followed by some name(s) says that there exist(s) some value(s) referred to by the name(s), for which a condition holds. Multiple names can refer to the same value. (FOR ALL can be expressed in terms of EXISTS.)
MVD的概念既可以应用于变量也可以应用于值.实际上，链接定义的形式是，MVD在值意义上保持不变时在变量意义上保持在任何法律关系中" .要知道某个特定值是合法的，您需要业务知识.然后，您可以显示该值是否满足MVD.但是要显示其变量是否满足MVD，您必须证明该MVD在任何合法关系中"都满足该变量可以容纳的值.一个有效值可以告诉您MVD 不保留在它的变量中，但不能告诉您MVD 不保留在它的变量中.这需要更多的业务知识.
The notion of MVD can be applied to both variables and values. In fact the form of the linked definition is that a MVD holds in the variable sense when it holds in the value sense "in any legal relation". To know that a particular value is legal, you need business knowledge. You can then show whether that value satisfies an MVD. But to show whether its variable satisfies the MVD you have to show that the MVD is satisfied "in any legal relation" value that the variable can hold. One valid value can tell you that a MVD doesn't hold in (it and) its variable, but it can't tell you that a MVD does hold in its variable. That requires more business knowledge.
通过使用MVD的定义，您可以证明此值违反了4NF.定义说，当某个条件满足对于任何情况"时，关系变量满足MVD.有效关系"值:
You can show that this value violates 4NF by using that definition of MVD. The definition says that a relation variable satisfies a MVD when a certain condition holds "for any valid relation" value:
对于所有成对的元组t1&r中的t2使得t1 [a] = t2 [a]存在元组t3& 2.t4 [...]
for all pairs of tuples t1 & t2 in r such that t1[a] = t2[a] there exist tuples t3 & t4 [...]
对于t1&的MVD和值t2您的同事是否声称不存在t3&的值?t4?MVD和t1& amp;的值不存在这种组合.t2.例如，{company}↠{product}和t1&两者都为t3(越野车，下桥，伽玛)，我们可以将(公司，下桥，伽玛)作为t3和a的值.t4，对于t1&的所有其他选择，依此类推.t2.
For what MVD and values for t1 & t2 does your colleague claim there doesn't exist values for t3 & t4? There is no such combination of MVD and values for t1 & t2. Eg for {company} ↠ {product} and t1 & t2 both (buggy, underbridge, gamma), we can take (company, underbridge, gamma) as a value for both t3 & t4, and so on for all other choices for t1 & t2.
F↠T保持的另一种定义是二进制JD(联接依赖)* {FUT，FU(A-T)}成立，即该关系等于其投影的联接在FUT&F U(A-T).此定义可能会立即对您有所帮助&您的同事避免使用您&他们在误解.例如，您的示例数据是这两个预测的结合:
Another definition for F ↠ T holding is that binary JD (join dependency) *{F U T, F U (A - T)} holds, ie that the relation is equal to the join of its projections on F U T & F U (A - T). This definition might be more immediately helpful to you & your colleague in that it avoids the terminology that you & they are misinterpreting. Eg your example data is the join of these two of its projections:
company location
--------------------
abc hilltop
abc riverside
buggy underbridge
company product
----------------
abc alpha
abc beta
buggy gamma
buggy theta
buggy omega
因此它满足JD * {{{company，location}，{company，product}}}，因此满足MVD {company}↠{location}和{company}↠{product}(以及其他).(也许您可以想到具有零个，一个，两个，三个等元组的关系的示例，这些元组具有一个或多个(平凡的和/或非平凡的)MVD.)
So it satisfies the JD *{{company, location}, {company, product}}, so it satisfies the MVDs {company} ↠ {location} and {company} ↠ {product} (among others). (Maybe you will be able to think of examples of relations with zero, one, two, three etc tuples for which one or more (trivial and/or non-trivial) MVDs hold.)
当然，这两个定义是描述相同条件的两种不同方式.
Of course, the two definitions are two different ways of describing the same condition.
PS 1 每当FD F→T成立时，MVD F↠T就会成立.对于BCNF中的关系，违反4NF& amp; amp;的MVD会被删除.5NF是那些与FD无关的人.
PS 1 Whenever a FD F → T holds, the MVD F ↠ T holds. For a relation in BCNF, the MVDs that violate 4NF & 5NF are those not so associated with FDs.
PS 2 关系变量用于当且仅当将其值替换为给定的语句模板或 predicate .加上MVD的JD定义，就为满足业务术语的MVD的关系变量提供了条件.在这里，我们的谓词的形式为 ... company ... location ... product ... .(例如，名为 company 的公司位于 位置 并生产产品 product .)在所有有效的业务情况下，对于所有公司来说，此MVD都适用于变量.，位置，产品，
PS 2 A relation variable is meant to hold a tuple if and only if it makes a true statement in business terms when its values are substituted into a given statement template, or predicate. That plus the JD definition for MVD gives conditions for a relation variable satisfying a MVD in business terms. Here our predicate is of the form ...company...location...product.... (Eg company namedcompanyis located atlocationand makes productproduct.) It happens that this MVD holds for a variable when for all valid business situations, FOR ALL company, location, product,
EXISTS product [...company...location...product...]
AND EXISTS location [...company...location...product...]
IMPLIES ...company...location...product...
这篇关于确定该数据是否真的是第四范式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
