识别功能依赖性
我在 第一范式 (1NF) 中有以下模式 - 即包含原子值的所有单元格：
ClientRental (clientNo, propertyNo, clientName, propertyAddress, rent,
rentStart, rentFinish, ownerNo, ownerName)
总体概要是客户可以从租赁代理处租用许多房产。每个财产都有一个所有者。对于熟悉这本书的人来说，这是从 Connolly & 的数据库系统中提取的示例。乞讨。
我正在尝试识别函数依赖→候选键、部分依赖和传递依赖等。
我正在阅读一本教科书，但他们的建议解释得有些糟糕。我的建议正确还是错误？
FD1 -> clientNo -> clientName
FD2 -> propertyNo -> propertyAddress, rent, ownerNo, ownerName
FD3 -> ownerNo -> ownerName
我肯定错过了更多的功能依赖项，但由于我缺乏经验，我无法识别它们。显然，我无法确定部分依赖关系，因为我尚未为上述关系/模式分配主键。
如何识别其他功能依赖性？我也不清楚是什么决定了某些东西作为传递依赖...
我的 3NF 关系：
Client {clientNo(PK), clientName}
Owner {ownerNo(PK), ownerName}
Property {propertyNo (PK), propertyAddress, rent}
ClientRental {clientNo(PK), propertyNo(PK), rentStart, rentFinish, ownerNo(FK)}
I have the following schema in first normal form (1NF) - that is all cells containing atomic values:
ClientRental (clientNo, propertyNo, clientName, propertyAddress, rent,
rentStart, rentFinish, ownerNo, ownerName)
The general outline is that clients can rent many properties from a letting agents. Each property has one owner. For those of you familiar with the book, it is an example extracted from Database Systems by Connolly & Begg.
I am trying to identify the functional dependencies → candidate keys, partial dependencies and transitive dependencies, etc.
I am following a textbook, but their suggestions are somewhat poorly explained. Are my suggestions correct or not?
FD1 -> clientNo -> clientName
FD2 -> propertyNo -> propertyAddress, rent, ownerNo, ownerName
FD3 -> ownerNo -> ownerName
There are definitely more functional dependencies that I have missed, but my lack of experience is preventing me from identifying them. Obviously, I cannot determine the partial dependencies, because I haven't yet allocated a primary key for the above relation/schema.
How can I identify the other functional dependencies? I am not clear what determines something as a transitive dependency either...
My 3NF relations:
Client {clientNo(PK), clientName}
Owner {ownerNo(PK), ownerName}
Property {propertyNo (PK), propertyAddress, rent}
ClientRental {clientNo(PK), propertyNo(PK), rentStart, rentFinish, ownerNo(FK)}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
要改进到 2NF，请识别仅依赖于候选键的一部分的非键属性，不是全部。首先确定集合 {clientName、propertyAddress、rent、rentStart、rentFinish、ownerNo、ownerName} 中的任何属性是否仅依赖于 clientNo 或 propertyNo。
现在，您在网上遇到的问题之一是函数依赖关系实际上是由值决定的，而不是由列名决定的。如果没有代表性的样本值，我们就必须进行一些猜测。但也许
我们可以这样分解ClientRental。
在美国，propertyNo -> 是不正确的。租。 （您的FD2。除非租金指的是要价。）在美国，租约决定租金，从法律上讲，租约必须包括地址和租户。 （实际上是所有租户。但这是一个不同的问题。）
由于“client”和“properties”的候选键中只有一列，因此它们必须位于 2NF 中。我认为这三种关系都属于 2NF。
您可以自行改进 3NF （删除传递依赖项）吗？
稍后。 。 .
是的，这里至少有一个传递依赖：propertyNo ->所有者No ->所有者名称。通过引入所有者关系来消除这种传递依赖。
关系“客户”、“财产”和“所有者”属于 3NF。在现实世界中，财产通常由多个人或企业拥有，并且通常也出租给多个人或企业。但这类问题与正常化没有任何关系。 （直到您决定支持现实世界的情况。）
还有其他吗？
To improve to 2NF, identify the non-key attributes that depend on only part of a candidate key, not on all of it. Start by determining whether any of the attributes in the set {clientName, propertyAddress, rent, rentStart, rentFinish, ownerNo, ownerName} depend only on either clientNo or propertyNo.
Now, one of the problems you'll run into online is that functional dependencies are actually determined by values, not by column names. Without representative sample values, we have to guess a little. But probably
So we can decompose ClientRental this way.
In the USA, it's not true that propertyNo -> rent. (Your FD2. Unless by rent you mean the asking price.) In the USA, the lease determines the rent, and legally the lease has to include the address and the tenant. (All the tenants, actually. But that's a different issue.)
Since "client" and "properties" have only one column in their candidate keys, they must be in 2NF. I think all three of these relations are in 2NF.
Can you handle improving to 3NF (removing transitive dependencies) on your own?
Later . . .
Yes, there's at least one transitive dependency here: propertyNo -> ownerNo -> ownerName. Remove that transitive dependency by introducing a relation of owners.
The relations "clients", "properties", and "owners" are in 3NF. In the real world, properties are often owned by multiple people or businesses, and they're also often leased to multiple people or businesses. But that kind of issue doesn't have anything to do with normalization. (Until you decide to support that real-world situation, that is.)
Anything else?
可能需要识别 4 种关系：
然后，给定 ClientRental 中的属性，我们可以推理：
对于给定的房产，开始日期为唯一，因此组合可以提供一个关键（行列式）；您还可以认为rentFinish和propertyNo将提供钥匙。
租金可能是财产和租金的属性；前者是要价租金，后者是获得的租金。更现实的租金要价可能会因一年中的不同时间而异——夏季的房产可能比冬季更有价值。
对于传递依赖关系，请考虑原始的 ClientRental 关系。 propertyNo 标识了ownerNo（和ownerName），因此其中潜伏着传递依赖。
There are probably 4 relations that should be identified:
Then, given the attributes in the ClientRental, we can reason:
For a given property, the start date is unique, so the combination is can provide a key (determinant); you can also argue that rentFinish and propertyNo would provide a key.
The rent could probably be an attribute of both Property and Rental; in the former, it is the asking rent, in the latter, the rent obtained. A more realistic asking rent might well vary by time of year - the property might be more valuable in the summer months than in the winter months.
For transitive dependencies, consider the original ClientRental relation. The propertyNo identifies the ownerNo (and ownerName), so there is a transitive dependency lurking in there.