设计无损连接，保留相关性的3NF数据库 [英] Designing lossless-join, dependency preserving, 3NF database

问题描述

我需要设计数据库来跟踪以下属性:

I need to design database which would keep track of the following attributes:

    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

我需要找到属性的无损联接，保留依存关系，第三范式分解.
我尝试了不同的分解，但是没有人遵循所有要求(它们是:无损联接，保留依赖项，第三范式).
例如如果我不更改原始关系(表将具有所有4个属性)，我将获得无损连接，保留相关性，但不会保留3NF，而只会保留2NF.
以下分解:

I need to find lossless-join, dependency preserving, 3rd normal form decomposition of the attributes.
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 original relation without changes (table would have all 4 attributes) I would get lossless-join, dependency preserving but not 3NF, only 2NF.
The following decomposition:

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

位于3NF中，保留相关性，但不包括无损连接.
我的问题有什么解决办法吗?

is in 3NF, dependency preserving, but not lossless-join.
Is there any solution for my problem?

谢谢.

推荐答案

为在此幻灯片中解释，始终有一个保留依赖性的无损连接3NF.在该序言脚本中实​​现的实现的算法 (说明和来源).

As explained in this slides, there's always a dependency preserving, lossless join 3NF. The described algorithm for computing it is implemented in this prolog script (explanation and source).

这种分解总是存在的，在这种情况下，这就是您所采用的分解方式:

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算法来检查它实际上是否无损加入.

You can run the Tableau Algorithm to check that it actually is lossless join.

这篇关于设计无损连接，保留相关性的3NF数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！