从功能依赖关系中确定密钥[英] Determine Keys from Functional Dependencies

本文是小编为大家收集整理的关于从功能依赖关系中确定密钥的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我正在学习数据库理论课程,并​​且在阅读阅读后,我尚不清楚如何推断键,给定一组功能依赖.

我有一个示例问题:

找到具有功能依赖性的关系R(ABCDEFG)的所有键

AB → C
CD → E
EF → G
FG → E
DE → C
BC → A

通过识别以下哪个是关键来证明您的知识.

a. BCDEF             
b. ADFG           
c. BDFG           
d. BCDE 

有人可以引导我完成如何分解功能依赖的结论,以结论某种属性是关键吗?我希望我会面对许多此类问题,我需要了解如何处理.

推荐答案

采用FD,例如AB→C

增加,直到提到所有属性,例如ABDEFG→CDEFG(请注意,这等同于Abdefg→ABCDEFG,因为A-> a和b-> b是微不足道的.

这告诉您Abdefg是超键.

检查LHS是您超键子集的其他FD,其RHS包含您的SuperKey的其他属性.

有两个. ef→g和fg→e.从您的SuperKey中删除这些RH的属性.这样做给您两个钥匙,当然是超级钥匙,但不一定是不可约的:Abdef和Abdfg.

但是,从ab→C和cd→e中,我们还可以得出ABD→E.因此,我们还可以从ABDEF密钥中删除E.令人讨厌的事情是,当我说"检查其他FD"时,这显然意味着您还应该检查一组FD的关闭中出现的任何FD(即,从给定的FDS集中衍生的任何FD) ...手工做有点不切实际...

一个有用的网站,用于验证您的结果是否正确:

http://rayrymondcho.net/remondcho.net/relationdatabasetools/relationaldatabasetools/relealationaldatabasetoolationaltionaldatabasetoolationaldatabasetools

您现在应该能够确定选项C是关键.

其他推荐答案

好吧,这应该很简单.您需要做的就是关闭每个密钥,看看它是否包含R的所有属性. ef-> g.由于此闭合包含R的所有属性,因此BCDEF是关键.关闭的主要目的是查看我们是否可以从给定的一组属性中"达到"每个属性.闭合是通过导航FD实际上可以达到的一组属性.

其他推荐答案

由于您在DB理论课程中,我将假设您有SQL经验,并尝试将理论与实施背景联系起来.

基本上,一个关系是您将其称为实现中的表,而键是任何一组属性(读列),可用于识别唯一的行(在DB理论中,这将是元组).这里最简单的类比是,一个键是您的主要键,以及您可以使用的任何其他可能的列,以识别关系中的单个元组(在表中行).因此,以下是这样做的基本步骤(我将浏览示例A,然后您可以尝试其余的):

  1. 列出您建议的密钥中未使用的所有属性(因此BCDEF不包括A和G).
  2. 对于您所缺少的每个属性,浏览函数依赖项列表,看看您的提议键是否能够推断您缺少的属性.

                 a. So for A, you have BC -> A.  Since both B and C are in the proposed
                    key BCDEF, you can infer that BCDEF -> A.  Your set now becomes
                    BCDEFA.
                 b. For G, again going through your FDs you find EF -> G.  Since both
                    E and F are in BCDEFA, you can infer BCDEFA -> G.
    

由于您能够从BCDEF推断A和G,因此选项A是关系ABCDEFG的关键.我知道有一种算法,它可能在您的课程文本中.也许还有一个例子.您应该手动浏览它,直到模式直观为止.

编辑:我要回到文本以寻找这种算法的原因是,您的考试很有可能被编写,而不是多项选择,因为它是DB理论课程.如果这是真的,那么如果您可以有条不紊地遵循课程文本/注释中的符号,您将获得更多的部分信用.

主要目标是将密钥转变为关系,这应该证明所提出的密钥实际上是钥匙.

本文地址:https://www.itbaoku.cn/post/597406.html

问题描述

I'm taking a database theory course, and it's not clear to me after doing the reading how I can infer keys, given a set of functional dependencies.

I have an example problem:

Find all keys of the relation R(ABCDEFG) with functional dependencies

AB → C
CD → E
EF → G
FG → E
DE → C
BC → A

Demonstrate your knowledge by identifying which of the following is a key.

a. BCDEF             
b. ADFG           
c. BDFG           
d. BCDE 

Can someone walk me through how I should decompose the functional dependencies to conclude that some combination of attributes is a key? I expect I'll face a number of these types of problems and I need to understand how to approach it.

推荐答案

Take an FD, e.g. AB→C

Augment until all attributes are mentioned, e.g. ABDEFG → CDEFG (note that this is equivalent to ABDEFG → ABCDEFG because it is trivially true that A->A and B->B).

This tells you that ABDEFG is a superkey.

Check the other FDs in which the LHS is a subset of your superkey, and that on its RHS contains some other attribute of your superkey.

There are two. EF→G and FG→E. Remove the attributes of the RHS of these from your superkey. Doing so gives you two keys, that are certainly superkeys, but not necessarily irreducible ones: ABDEF and ABDFG.

However, from AB→C and CD→E we can also derive that ABD→E. Hence we can also remove the E from our ABDEF key. The nasty thing here is that when I said "Check the other FDs", that apparently means that you should also check any FD that appears in the closure of your set of FDs (i.e. any FD that is derivable from your given set of FDs)... And that's a bit impractical to do by hand ...

A useful site for verifying whether your results are correct:

http://raymondcho.net/RelationalDatabaseTools/RelationalDatabaseTools

You should now be able to determine that option c is a key.

其他推荐答案

Well this should be rather straightforward. All you need to do is to take the closure of every key given and see if it contains all attributes of R. For example, closure of BCDEF = ABCDEFG since BC -> A and BC is a subset of BCDEF and so if EF for FD EF -> G. Since this closure contains all attributes of R, BCDEF is the key. The main aim of taking closures is to see if we can "reach" every attribute from a given set of attributes. The closure is the set of attributes that we can actually reach by navigating the FDs.

其他推荐答案

Since you're in a db theory course, I'm going to assume you have SQL experience and try and relate the theory to the implementation context.

Basically, a relation is what you would refer to as a table in implementation and a key is ANY set of attributes (read columns) which can be used to identify a UNIQUE row (in db theory this would be a tuple). The simplest analogy here is that a key is your primary key, as well as any other possible set of columns you can use to identify a single tuple in your relation (row in your table). So, here are the basic steps for doing this (I'll walk through example A, and then you can try the rest):

  1. List all of the attributes which are NOT in your proposed key (so BCDEF does not include A and G).
  2. For each attribute you're missing, go through the list of functional dependencies and see if your proposed key is capable of inferring the attribute you're missing.

                 a. So for A, you have BC -> A.  Since both B and C are in the proposed
                    key BCDEF, you can infer that BCDEF -> A.  Your set now becomes
                    BCDEFA.
                 b. For G, again going through your FDs you find EF -> G.  Since both
                    E and F are in BCDEFA, you can infer BCDEFA -> G.
    

Because you were able to infer both A and G from BCDEF, option a is a key of the relation ABCDEFG. I know there is an algorithm for this, and it is probably in your course text somewhere. There is also probably an example. You should step through it manually until the pattern is intuitive.

EDIT: The reason I would go back through the text to look for this algorithm is that chances are your exam is going to be written as opposed to multiple choice since it is a db theory course. If this is true then you would get more partial credit if you can methodically follow notation demonstrated in your course text/notes.

The main goal is to turn the key into the relation, which should prove that the proposed key is in fact a key.