Question

我需要设计能够跟踪以下特征的数据库:

stdnum       // student number
postcode     // postal code
phone_number // student phone number
city         // student address: city

还列出功能上的依赖:

stdnum -> postcode
stdnum -> phone_number
postcode -> city
phone_number -> city

我需要找到一个无损失的工作机会,保护依赖,第三个正常形式的分解。

I have tried different decompositions but there was no one that obeys all requirements (they are: lossless-join, dependency preserving, 3rd normal form).
E.g. if I leave the original relation without changes (table would have all 4 attributes) I would get lossless-join and dependency preserving but not 3NF, only 2NF.

The decomposition(stdnum, postcode, Telephone_ number) JOIN (postcode, City) JOIN (phone_ number, City) is in 3NF and Depend conservation, but not lossless-join.

Answer 1

或许,任务的目的是让你发现对你的问题的否定答案,即“我的问题是否有任何解决办法?

贵国将数据库作为单一四重物品,必然意味着每个A(stud)只能有一个D(城市)。通常以B->D和C->DFD的方式处理,必然带来两种不同的D与每个A相关联的可能性。

Dealing with that requires the introduction of a database constraint into the design, but database constraints are outside the scope of normalization theory proper.

And not decomposing necessarily means that you won t get 3NF.

因此:或许任务的目的是让你发现,正常化是数据库设计的神圣通道。我认为你已经走上了这一轨道。

Answer 2

您对原始关系的详细分类表明,这些依赖性与同一性。

postcode -> city
phone_number -> city

在现实生活中,情况并不总是如此。例如,在我自己的地方,有一些地址有电话号码,有LONDON地区代码,但位于SSTON,SURREY员额代码。还有移动电话,没有绘制任何地理位置的地图。

因此,我将通过改变数据模式来解决你的问题。

"Attributes are abstraction. You don t need to understand meaning of atributes. All information about them is in the functional dependencies listed in the task."

用具体例子来思考,是了解我们抽象的错误的更好方法。在这种情况下,你实际上有5个特性,不是4个。或者,有功能性附属代码postcode -> City有效,但phone_ number -> City is bogus. 或者,也许你需要模仿这样的事实,即学生可以拥有一个以上的电话号码,例如挖掘土地线、移动电话(人员)。

Answer 3

http://courses.cs.vt.edu/~cs4604/Fall10/lectures-17-3nf.pdf rel=“nofollow”在本幻灯片中解释,始终保持依赖性,没有加入3NF。所描述的计算法是:,在这项添加文字上(explanation和。

Such a decomposition always exists, and in this case it s the one you approached:

(stdnum, postcode, phone_number) JOIN
(postcode, city) JOIN
(phone_number, city)

您可操作 Tableau Algorithm,以核实它实际上没有损失。

Answer 4

In dependency theory, a join dependency is a constraint on the set of legal relations over a database scheme. A table T is subject to a join dependency if T can always be recreated by joining multiple tables each having a subset of the attributes of T. If one of the tables in the join has all the attributes of the table T, the join dependency is called trivial.

合并扶养在第五个正常形式(也称为项目正常形式)中发挥着重要作用,因为可以证明,如果你在表R.1至R_n中推翻了计划R,如果你将R的法律关系限制在加入对R的依赖性(R_1,R_2,...R_n),那么这种分解将是无损失的。

描述共同依赖的另一个办法是说,共同依赖关系中的一套关系彼此独立。

与功能依赖性不同的是,合并依赖性没有健全和完全的对应性,尽管对诸如完全类型依赖性等更为明确的依赖性语言存在近似性。然而,合并依赖性的含义是可计量的。

友情链接