您如何确定功能依赖性和主键？ [英] How do you determine functional dependencies and a primary key?

问题描述

在我的Oracle数据库编程课程中，我们的最终实验评估的第一部分要求我们：

In my Oracle Database Programming course, the first part of our final lab assessment requires that we:

• 标识数据库的主键当前显示的表


• 查找我们将要使用的表的所有功能依赖项。


• 绘制该表的依赖关系图（s）

该表以1NF开头。

从我所做的研究来看，似乎我需要实质上组合所有可能的FD，这不仅会占用大量时间，而且考虑到他希望我们然后在依赖关系图中映射这些关系，这似乎很奇怪。基本上，这将导致所有内容链接到所有内容，这就是为什么我认为我不了解功能依赖关系的原因。

From the research I have done, it seems like I need to essentially combine every possible FD, which would not only consume a very large amount of time, but seems bizarre considering he wants us to then map these relationships in the dependency diagram. This would basically cause everything to link to everything - and this is why I believe I do not understand functional dependencies.

我知道功能依赖关系是R，X中的依赖关系-> Y，其中Y不是由其他X产生的，因此应该使您能够通过此依赖关系来确定表中的所有其他值。

I understand that a functional dependency constitutes that in R, X->Y, where Y is not produced by anything other X, and should enable you to determine every other value in the table through this dependency.

我也知道' X'和'Y'可以包含多个属性。这是我不了解的，因为如果我将属性映射到代数变量中（这似乎是这样做的方法？），则字母A-J表示；我想出的依赖项数量令人震惊，而且我不想浪费时间以错误的方式做事。

I also understand that 'X' and 'Y' can consist of more than one attribute. This is what I don't understand, because if I map my attributes into algebraic variables (which seems to be the way to do it?), I have the letters A-J; the number of dependencies I would come up with seems astounding, and I don't want to waste time doing something the wrong way.

这是否意味着我需要提供所有功能完全依赖，部分依赖和传递依赖？

Does this mean I need to provide all fully functional dependencies, partial dependencies, and transitive dependencies? Just the thought of this is overwhelming.

我的表由10列组成，其当前状态为1NF-因此，A-J将是我在该过程中的属性标识符。我已经发现R（AD）构成了一个强大的主键，但是我不确定是否需要通过布局所有FD派生PK，或者我是否可以选择PK并从这一点上找到我的FD。如果是这样的话-考虑到我的PK会真正确定模型中关系的映射，我是否仍要布置每个FD？

My table consists of 10 columns in its current state of 1NF - thus, A-J would be my attribute identifiers in the process. I have found that R(AD) constitutes a formidable Primary Key, but I'm not sure if I need to derive the PK from laying out all of the FD's, or if I can choose a PK and find my FD's from this point. If that's the case - do I still lay out every FD, given that my PK will really determine the mapping of the relationships within the model?

抱歉，文本墙，我感到压力，困惑和疲惫。任何帮助都将有助于您理解这一点，如果我能提供更多信息，请告诉我。



编辑：我无法在此显示该表，因为它的宽度为10列，但我已通过Dropbox提供了一个共享链接，供其他人查看以帮助：
https://www.dropbox.com/ s / 3vwo1axe7a1i20s / final％20lab％20instructions.pdf？dl = 0


非常感谢。

Sorry for the wall of text, I'm stressed, confused, and exhausted. Any help would be wonderful in understanding this, and if I can provide more information please let me know.

I'm unable to present the table here because it is 10 columns wide, instead I have provided a shared link via Dropbox for others to view in order to help:
https://www.dropbox.com/s/3vwo1axe7a1i20s/final%20lab%20instructions.pdf?dl=0

Thanks much.

推荐答案

您必须首先了解表和规则的含义。显然，该表的含义如下：

You must know what the table and rules mean first. Apparently, the table means something like:

/*
student with id [si] has name [sn] and address [sa] and major [sm]
    and takes course [ci] with title [ct]
        from instructor with id [ii] and name [in] and office [io]
        with grade [scg]
*/
t(si,sn,sa,sm,ci,ct,ii,in,io,scg)

表（aka关系）包含使条件（aka谓词）成立的行。该标准可以表示为一堆独立标准的AND，然后将其分解为设计和规范化所针对的标准。像这样来自ANDing的表可以将您可以说出的关于应用程序的所有信息称为其通用关系。

The table (aka relation) holds rows that make a criterion (aka predicate) true. That criterion can be expressed as the AND of a bunch of separate criteria and decomposing it into them is what design and normalization is about. A table like this from ANDing together everthing you could say about your application is called its "universal relation".

现在，使用该条件的含义和规则的含义找出每个列的子集，当它的给定值总是与另一个列的一个值一起出现时，即FD持有什么。请注意，如果子集确定一列，则该列的所有超集都将确定该列。当您知道给定的子集可以出现一个列的多个值时，也可以排除FD。了解一组FD的传递闭包和最小覆盖率，以简洁地表达所有FD。您必须证明自己已经考虑了所有可能的行列式，即列的每个子集。

Now using what that criterion means and what the rules mean you have to figure out for every subset of columns when a given value for it always appears with one value for another column, ie what FDs hold. Note that if a subset determines a column then all supersets of that column determine that column. You can also rule out FDs when you know that a given subset can appear with more than one value of a column. Learn about the "transitive closure" and "minimal cover" of a set of FDs to express all FDs concisely. You must show that you have accounted for every possible determinant, ie for every subset of columns.

每次分解时，都必须决定是否只需要给定的组件保持原件行在其列上的投影，或者是否也可以容纳其他行。例如，教授可以不教课程而存在吗？如果是这样，则意味着您的原始标准/表实际上不足以说出您想要关于应用程序的所有事情，并且您需要一个额外的表，该表不是该表的投影，但具有相同的列。通常将其描述为标准化的一部分，但事实并非如此。值得注意的是，您进行规范化时，您的设计错误。

Every time you decompose you have to decide whether you want a given component only ever to hold the projection of rows of the original on its columns or whether it could hold other rows as well. Eg can a professor exist without teaching a course? If so it means that your original criterion/table is not actually sufficient to say all the things that you want to about your application and you need an extra table that is not a projection of it but has the same columns. This is often described as part of normalization but it isn't. It is noticing that your design was wrong while you are normalizing.

您可能必须进行假设，因为您不知道会发生什么情况。我们通常假设X_id与Xs的比为1：1；是这样吗？一个学生可以有多个名字吗？如果不是，则{si}-> sn。即使他们这样做，表也只能记录一个吗？如果是这样，则条件变为 ...并且具有被选择要记录的名称[sn] ...，并且所有涉及学生姓名的查询都将包含那些已被选择记录的名称和

You may have to hypothesize because you don't know what situations can arise. We normally assume X_ids are 1:1 with Xs; is that so? Can a student have more than one name? If not then {si} -> sn. Even if they do, is the table going to only record one? If so, the criterion becomes "...and has name [sn] that has been chosen to be recorded..." and all your queries that involve student names are going to involve those that "have been chosen to be recorded" and the above FD holds.

• 每个课程ID仅教一门课程。


• 学生可以最多可修4门课程。


• 每门课程最多可容纳25名学生。


• 每门课程仅由一名讲师讲授。


• 每个学生可能只有一个专业。

• Only one class is taught for each course ID.
• Students may take up to 4 courses.
• Each course may have a maximum of 25 students.
• Each course is taught by only one Instructor.
• Each student may have only one major.

什么是课程？

ci是否确定ct？ ii确定吗？根据规则，给定的ci仅与ii一起出现。 FD。根据规则，给定的si仅显示单个sm。 FD。

Does ci determine ct? Does ii determine in? From the rules, a given ci only appears with one ii. FD. From the rules, a given si only appears with a single sm. FD. From the rules, si does not determine ci and course does not determine si.

未解决的问题必须在您和您的客户（教授）之间解决。

Unanswered questions must be resolved between you and your client (professor).

您只能在FD上确定候选键（可以调用主键之一）。

You can only determine candidate keys (one of which you can call the primary key) on FDs.

（您的分配使用ER因此您需要找到有关实体的条件/谓词，然后找到与标识实体的列集相对应的条件/谓词，以获取表。）

(Your assignment uses E-R so you need to find criteria/predicates about entities, then find corresponding ones about column sets that identify entities, which get tables.)

这篇关于您如何确定功能依赖性和主键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！