标准化3NF
我正在阅读一些规范化的例子，但是我遇到了一个我不理解的例子。
该示例的网站位于此处：http://cisnet.baruch。 cuny.edu/holowczak/classes/3400/normalization/#allinone
我不明白的部分是“第三范式”
在我的脑海中我看到了传递依赖EMPLOYEE_OFFICE_PHONE（姓名、办公室、楼层、电话） 如下 Name->->Office|Floor 和 Name->->Office|电话
作者将表 EMPLOYEE_OFFICE_PHONE（姓名、办公室、楼层、电话） 拆分为 EMPLOYEE_OFFICE（姓名、办公室、 Floor) 和 EMPLOYEE_PHONE (Office, Phone)
从我一开始的判断来看，我仍然在 Name->->Office|Floor 中看到传递依赖> 所以我不明白为什么它是3NF。我是否错误地指出 Name->->Office|Floor 中存在传递依赖？
传递性的推理： 列表
- 这是我的功能依赖项 办公室
- 名称 ->楼层
- 名称->电话
- 办公室->电话
- 办公室->楼（这是错误的吗？为什么？
谢谢大家的帮助！
I an reading through some examples of normalization, however I have come across one that I do not understand.
The website the example is located here: http://cisnet.baruch.cuny.edu/holowczak/classes/3400/normalization/#allinone
The part I do not understand is "Third Normal Form"
In my head I see the transitive dependencies in EMPLOYEE_OFFICE_PHONE (Name, Office, Floor, Phone) as the following Name->->Office|Floor and Name->->Office|Phone
The author splits the table EMPLOYEE_OFFICE_PHONE (Name, Office, Floor, Phone) into EMPLOYEE_OFFICE (Name, Office, Floor) and EMPLOYEE_PHONE (Office, Phone)
From my judgement in the beginning, I still see the transitive dependency in Name->->Office|Floor so I don't understand why it is in 3NF. Was I wrong to state that there is a transitive dependency in Name->->Office|Floor?
Reasoning for transitivity:
Here is my list of the functional dependencies
- Name -> Office
- Name -> Floor
- Name -> Phone
- Office -> Phone
- Office -> Floor (Is this the incorrect one? and why?
Thank-you your help everyone!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
5) 你在这里假设一个命名方案...办公室 4xx 必须位于 4 楼...5xx 必须位于 5 楼...如果存在这样的方案，您可以拥有您的依赖...只要这个不是规范的一部分...不。 5已经退出游戏了...
5) you assume a naming sheme here ... offices 4xx have to be on floor 4 ... 5xx have to be on floor 5 ... if such a scheme exists, you can have your dependency ... as long as this is not part of the specification ... no. 5 is out of the game ...
(1) 您和作者及我同意姓名->办公室。
(2)您和作者同意姓名->楼层。虽然仅根据样本数据确实如此，但 Office->Floor 也是如此。我会通过问这样的问题来探讨此类问题：“如果办公室是空的，我是否仍然知道该办公室位于第几层？” （是的）
这些事情表明存在传递依赖性，名称->办公室和办公室->楼层。因此，在这一点上我不同意你和作者的观点。
(3) 您说姓名->电话。作者说办公室->电话。作者还表示“每个办公室都有一个电话号码”。因此，给定 Office 的一个值，我就知道 Phone 的一个且仅有一个值。给定一个名称值，我就知道一个且仅有一个电话值。我会通过问“如果我搬到另一个办公室，我的电话号码会跟着我吗？”来探讨这个问题。如果是，则姓名 -> 电话。如果没有，则“办公室”->“电话”。
这里没有足够的信息来回答这个问题，而且我曾在以这两种方式工作的办公室工作过，所以现实世界的经验对我们也没有多大帮助。在这种情况下，我必须站在作者一边，尽管我认为对于规范化示例来说，这并没有经过深思熟虑。
(4) 这实际上只是上面(3) 的扩展。
(5) 参见上文(2)。这与命名方案没有任何关系，并且您不需要假设编号为 5xx 的办公室位于 5 楼。唯一相关的问题是：给定 Office 一个值，Floor 是否有且仅有一个值？ （是的）我可能会通过问“一个办公室可以在多个楼层吗？”来探讨这个问题。 （在现实世界中，这是完全可能的。但是样本数据不支持这种可能性。）
一些额外的 FD，仅基于样本数据。
(1) You and the author and I agree that Name->Office.
(2) You and the author agree that Name->Floor. While that's true based solely on the sample data, it's also true that Office->Floor. I'd explore this kind of issue by asking this question: "If an office is empty, do I still know what floor that office is on?" (Yes)
Those things suggest there's a transitive dependency, Name->Office, and Office->Floor. So I would disagree with you and with the author on this one.
(3) You say Name->Phone. The author says Office->Phone. The author also says that "each office has exactly one phone number." So given one value for Office, I know one and only one value for Phone. And given one value for Name, I know one and only one value for Phone. I'd explore this issue by asking, "If I move to a different office, does my phone number follow me?" If it does, then Name->Phone. If it doesn't, then Office->Phone.
There isn't enough information here to answer that question, and I've worked in offices that worked each of those two ways, so real-world experience doesn't help us very much, either. I'd have to side with the author in this case, although I think it's not very well thought through for a normalization example.
(4) This is really just an extension of (3) above.
(5) See (2) above. This doesn't have anything to do with a naming scheme, and you don't need to assume that offices numbered 5xx are on the 5th floor. The only relevant question is this: Given one value for Office, is there one and only one value for Floor? (Yes) I might explore this issue by asking "Can one office be on more than one floor?" (In the real world, that's remotely possible. But the sample data doesn't support that possibility.)
Some additional FDs, based solely on the sample data.
首先，让我明确定义 3NF：-
如果满足以下条件，则关系属于 3NF：-
1.)关系是2NF
2.) 没有非素数属性可以传递依赖于主键。
换句话说，3NF 中的关系对于每个函数依赖 X->Y 都满足以下条件之一：-
1.)X 是超级键
2.)Y是主要属性
对于您的问题，如果存在以下 FD：-
那么我们不能说有关 Office 和 Floor 的任何信息。您可以通过应用和检查任何阿姆斯特朗推理规则来验证这一点。当您应用这些规则时，您会发现您无法推断有关办公室和楼层的任何信息。
First of all,let me define 3NF clearly :-
A relation is in 3NF if following conditions are satisfied:-
1.)Relation is in 2NF
2.)No non prime attribute is transitively dependent on the primary key.
In other words,a relation is in 3NF is one of the following conditions is satisfied for every functional dependency X->Y:-
1.)X is superkey
2.)Y is a prime attribute
For Your Question,if the following FDs are present :-
Then we cannot say anything about Office and Floor.You can verify this by applying and checking any of the Armstrong Inference Rules.When you apply these rules, you will find that you cannot infer anything about office and floor.