3NF的合成算法 [英] Synthesis-algorithm for 3NF

问题描述

我正在学习数据库，显然我必须处理正常形式。现在我想出了这个很简单的例子;给定与属性< em> {A，B，C} 和函数依赖性 {A，B-> C，A-> C}的关系 。

I'm learning about databases and obviously I have to deal with normalforms. Now I came up with this very simple example; given a relation R with attributes {A, B, C} and functional dependencies {A,B -> C , A -> C}.

此关系的候选键 K 必须为 {A，B}以找到候选键）。该关系不在2NF中，因为非关键属性 C 仅依赖于 A ，它是 K 的一个子集。 （我认为即使我不知道属性的域，也给出1NF）。

The candidate key K for this relation has to be {A, B} (not going into how to find candidate keys). The relation is not in 2NF since the non-key attribute C only depends on A which is a proper subset of K. (I assume 1NF is given even though I can not know the domains of the attributes).

现在要得到3NF，我必须使用合成算法，所以我首先找到规则集的函数依赖，它将是{A - > C} （也不是如何找到规范集）。现在在3NF中获得关系，形成包含属性 {A，C}的新关系 R1 。由于 不包含在 中，我必须建立一个新的关系 R2 ，其中包含一个候选键/ em>）。

Now to get to 3NF I'll have to use the synthesis-algorithm, so I first find the canonical set of the functional dependencies which would be {A -> C} (also not going into how to find the canonical set). Now to get relations in 3NF I form the new relation R1 which contains attributes {A,C}. Since K is not contained in R1 I have to make a new relation R2 which contains one of the candidate keys (here K).

这导致两个关系 R1（A，C）因为 和 都在3NF。

This leads to the two relations R1 (A,C) and R2 (A,B) and I am done since both R1 and R2 are in 3NF.

我的工作是否正确？还有什么我需要知道吗？非常感谢任何建议！

Is my working correct? Is there anything else I have to be aware of? Thanks a lot for any suggestions!

编辑：作为评论指出我的例子是错误的。这将是正确的，具有略微不同的关系，即，R（A，B，C，D）与FD的{A，B→C，B→D}

As a comment pointed out my example is faulty. It would be right with a slightly different relation though, namely R (A,B,C,D) with FD's {A,B -> C, B -> D} .. I'll not go through the rest again since I think I did the algorithm correctly even though the example was false.

推荐答案

给出与属性{A，B，C}和函数依赖性{A，B - > C，A-> C}的关系R

given a relation R with attributes {A, B, C} and functional dependencies {A,B -> C , A -> C}

明确说明您正在寻址非平凡FD。 R 也有FD A，B，C - > C，但它是微不足道的。总是明确的是你正在给予什么FD。例如，经典/最小覆盖或所有非平凡的FD或封面，或只是你知道的一些FD，虽然其他非平凡的，也可以，无论哪种情况。你可能不会做后者，因为一般来说你不会给足够的信息来确定CK和进一步规范化。

Be explicit that you are addressing non-trivial FDs. R also has FD A,B,C -> C, but it's trivial. Always be explicit about just what FDs you are giving. Eg a canonical/minimal cover or all the non-trivial FDs or a cover or just some FDs that you know hold although other non-trivial ones might too, whichever is the case. You probably aren't ever doing the latter, because in general you won't be giving enough information to determine CKs and further normalize.

该关系不在2NF中，因为非键属性C仅取决于A，而A是K的一个子集。

The relation is not in 2NF since the non-key attribute C only depends on A which is a proper subset of K.

只要。它使语句不清楚，如果你的意思是C不依赖于A，B，那么你是错误的。

Drop the "only". It makes the statement unclear and if you mean C doesn't depend on A,B then you are mistaken.

即使我不知道属性的域）。

(I assume 1NF is given even though I can not know the domains of the attributes).

这些域是不相关的。我想你担心域可能涉及重复组和/或非原子值。这是基于收到的非智慧。规范化到更高的正常形式与域无关。

The domains are irrelevant. I suppose you are worried that domains might involve "repeating groups" and/or "non-atomic values". This is based on received non-wisdom. Normalization to higher normal forms is independent of domains.

根据定义，关系的元组属性具有来自域的 a 值。 Re：重复组：它不能有任何，这是来自前关系数据库的东西。 Renon-atomic：Codd定义的关系能够具有关系值域。他指出，在关系语境中，一个价值可以被考虑（在日常意义上）非原子的唯一方式是关系价值。即他在关系上下文中定义原子，意味着不是关系。他将规范化定义为没有关系值（即非原子）属性。 （所有这一切都在 1970 中。）后来他将1NF定义为规范化。并开发了2NF 3NF。然后（在肯特和博伊斯后）BCNF。因此，他对这些术语的使用假定没有关系值域。

By definition, a relation's tuple's attribute has a value from a domain. Re: "repeating groups": It can't have any, that's something from pre-relational databases. Re "non-atomic": Codd defined relations as able to have relation-valued domains. He pointed out that the only way that a value could be considered (in the everyday sense) non-atomic in a relational context was to be relation-valued. Ie he defined "atomic" in a relational context to mean not a relation. He defined "normalized" to mean having no relation-valued (ie non-atomic) attributes. (All this in 1970.) Later he defined "1NF" as normalized. And developed "2NF" & "3NF". Then (after Kent & with Boyce) "BCNF". So his use of these terms assumed no relation-valued domains.

但是归一化理论是独立于域而提供的。即它被认为只是对每个有问题的JD进行分解。所以1NF也被用来作为一个关系。而其他的NFs得到使用，而不考虑域。 （尽管如果存在关系值域，则可能存在与引起不同但相似的异常的但与引起不同但相似的异常但与其类似的约束的约束，并且甚至在对所有有问题的JD进行分解之后也导致组件中的约束和异常）。不管是否一个关系具有关系值域，并且不管1NF或规范化或规范化意味着什么，您从中将有问题的JD从有问题的FD移除到所谓的3NF的分解过程是独立的

But normalization theory is presented independent of domains. Ie it's considered to just be decomposition per problematic JDs. So "1NF" also gets used for just being a relation. And the other "NFs" get used without regard to domains. (Although if there are relation-valued domains then there can be constraints different from but similar to FDs & JDs that cause different but similar anomalies, and that cause constraints and anomalies in components even after decomposition per all problematic JDs.) Regardless of whether a relation has relation-valued domains and regardless of what one means by "1NF" or "normalized" or "normalization", the decomposition procedure you're following to remove problematic JDs from problematic FDs to what you're calling 3NF is independent of domains.

作为评论指出我的示例是错误的。

As a comment pointed out my example is faulty.

该评论：

您的示例不正确。 C取决于A和B，或者它只取决于A。 - Lorenzo Gatti

Your example is stated incorrectly. Either C depends on A and B, or it depends on A only. – Lorenzo Gatti

这个评论有误。 C取决于A和B ，取决于A。因为A - > C意味着A，B - > C。

The comment is wrong. C depends on A and B and it depends on A only. Because A -> C implies A,B -> C.

这篇关于3NF的合成算法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！