我的表需要更多的规范化？ [英] Does my table need more normalization?

问题描述

>

做一个简单的公共现金桌DB表来计算我的朋友和我一起吃饭支付了多少钱。
我的第一个表只有一个表，它有一列人与非原子值，所以我把我的表分成两个表如上。

<但是我不确定这是否足够规范。有没有任何函数依赖，应该规范化，但我不知道它的存在？

（我要使用MySql，但你可以回答我用任何种类DBMS。）

解决方案

规范化需要知道函数依赖（FD）和连接依赖（FD）。

您的应用程序如果我们确切知道什么是关系，我们只能告诉您FD和JD我想表达（即谓词）为每个表（即，我们可以看一个情况，知道每一个可能的行，它是否从谓词和一个真正的命题，因此属于表中），以及确切的可能

您的键您可以通过商业规则了解可能的应用情况，等同于对可能的数据库状态的约束。没有给予FD。你只给了一个候选键（CK）和一个唯一键。但是你不能确定一些或所有的CK，而不知道关于FD的某些事情。所以当你给CKs是相同的说，有和不是一定的FD。你需要告诉我们当给出一些标记为PK或唯一键的列集合时，我们给出的是什么：是唯一键是CK（不包含较小的唯一子集）还是只有一个超级键（唯一）？你给了所有的CK，还是有其他的？除了给定的超集之外，还有其他超级键吗？这是最有帮助的，如果你只是告诉我们确切地说，你知道哪些FDs（通过最小的封面），你知道不知道。

猜测

我不知道 is_calculated 是什么。

也许一个给定的日期和 meal 对只有一个内容？

TL; DR 您确实需要检查每个可能的列集合，看看它是否在功能上决定了每一列。即，对于每个数据库状态，列集合的值的子行是否只显示该列的一个值。我们只能猜测，没有清楚地了解你的谓词和你的应用。您可以通过以下方式减少工作：如果一组列是唯一的，那么其超集将确定所有其他列。如果一组列是最小唯一的（是CK），则其较小的子集不会确定所有其他列。您可以查找假设FD的反例，其中两行可以对推定行列式具有相同的亚行值，但对于推定的确定属性具有不同的值。 标准化为4NF和5NF涉及将表分为多个表，以消除其中的JD。 CKs暗示。当JD保存对应于谓词的列集合时，表的谓词可以精确地表示为其他的AND。当每个JD中的每个连接在至少一个CK上与另一个连接重叠时，关系为5NF。 （Fagin的隶属算法）

PS 如果你关心约束，当你从一个表到两个表时，你失去了一个吗？ p>

I am making a simple public cashbook DB table to calculate how much my friends and I paid for having meal together. My first table was only a single table and it had a column "person" with non-atomic values, so I divided my table into two tables like above.

But I'm not sure this is normalized enough. Is there any functional dependency which should be normalized but I don't know its existence?

(I'm going to use MySql but you can answer me with any kind of DBMS.)

解决方案

Normalization requires knowing functional dependencies (FDs) and join dependencies (FDs). You didn't give them.

Your application We can only tell you the FDs and JDs if we know exactly what is "the relationship that I wanted to express" (ie predicate) for each table (ie to the point where we could look at a situation and know for every possible row whether it makes a true proposition from the predicate and so belongs in the table) and exactly what possible situations can arise (via "business rules" about possible application situations, equivalent to constraints on possible database states).

Your "keys" You didn't give FDs. You just gave one candidate key (CK) and a "unique key". But you can't determine some or all the CKs without knowing certain things about the FDs. So when you give CKs that's the same as saying there are and are not certain FDs. You need to tell us what you have given us when you give some column sets labelled PK or "unique key": Is a "unique key" a CK (containing no smaller unique subset) or just a superkey (unique)? Did you give all the CKs or could there be others? Could there be other superkeys than the supersets of the given ones? It is most helpful if you just tell us exactly which FDs you know hold (via a minimal cover) and which you know don't hold.

Guesses
I have no idea what is_calculated is for.
Maybe a given date and meal pair has exactly one content?

TL;DR You really need to check each possible set of columns to see whether it functionally determines each other column. Ie whether for each database state a subrow of values for the set of columns only appears with one value for the column. We can only guess without a clear understanding of your predicates and your application. You can reduce work by the following: If a set of columns is unique then its supersets determine all other columns. If a set of columns is minimally unique (is a CK) then none of its smaller subsets determine all other columns. You can look for counterexamples to putative FDs, where two rows can have the same subrow of values for a putative determinant but have different values for a putative determined attribute. Armstrong's axioms generate all the FDs implied by given ones.

JDs Normalization to 4NF and 5NF involves splitting table into multiple tables to eliminate JDs that aren't implied by CKs. A table's predicate can be expressed as the AND of others exactly when a JD holds corresponding to the predicates' column sets. A relation is in 5NF when every conjunct in every JD overlaps some other one on at least a CK. (Fagin's membership algorithm.)

PS If you care about constraints, did you lose one when you went from one table to two?

这篇关于我的表需要更多的规范化？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！