识别传递依赖 [英] Identifying transitive dependencies
问题描述
我正在处理一个表，该表具有一个复合主键，该主键由 1NF 形式的两个属性(总共 10 个)组成.
I am working with a table that has a composite primary key composed of two attributes (with a total of 10) in 1NF form.
- 在我的情况下，全功能依赖涉及依赖我的主键中的两个属性.
- 部分依赖依赖于主键中的任一属性.
- 传递依赖涉及函数依赖中的两个或多个非键属性，其中一个非键属性依赖于我的主键中的一个键属性.
从表中提取传递依赖，似乎在规范化之后这样做，但我的任务要求我们在绘制依赖关系图之前识别所有函数依赖(之后我们对表进行规范化).括号标识主键属性:
Pulling the transitive dependencies out of the table, seems do this after normalization, but my assignment requires us to identify all functional dependencies before we draw the dependency diagram (after which we normalize the tables). Parenthesis identify the primary key attributes:
(Student ID), Student Name, Student Address, Student Major, (Course ID), Course Title, Instructor ID, Instructor Name, Instructor Office, Student_crse_grade
- 每个课程 ID 只教授一门课.
- 学生最多可以参加 4 门课程.
- 每门课程最多可有 25 名学生.
- 每门课程仅由一名讲师授课.
- 每个学生可能只有一个专业.
推荐答案
从你的问题看来，你对基础知识的理解不是很清楚.
From your question it seems that you do not have a clear understanding of basics.
应用关系&情况
首先，您必须了解有关您的应用程序(包括业务规则)的信息，并确定应用程序关系(也称为关联).每个都有一个(基)表(又名关系)变量.这种应用关系可以通过作为语句模板的行成员资格标准(又名谓词)(又名含义)来表征.例如，假设标准student [student_id] 选择课程[course_title] 具有表变量TAKES.标准的参数是其表的列.我们可以使用带有列的表名(如 SQL 声明)作为标准的简写.例如 TAKES(student_id,course_title).一个标准加上一行就一种情况做出陈述(又名命题).例如，行 (17,'CS101') 给 student 17 学习课程 'CS101' 即 TAKES(17,'CS101').给出正确语句的行进入表中，而给出错误语句的行则留在表中.
First you have to take what you were told about your application (including business rules) and identify the application relationships (aka associations). Each gets a (base) table (aka relation) variable. Such an application relationship can be characterized by a row membership criterion (aka predicate) (aka meaning) that is a statement template. Eg suppose criterion student [student_id] takes course [course_title] has table variable TAKES. The parameters of the criterion are the columns of its table. We can use a table name with columns (like an SQL declaration) as a shorthand for the criterion. Eg TAKES(student_id,course_title). A criterion plus a row makes a statement (aka proposition) about a situation. Eg row (17,'CS101') gives student 17 takes course 'CS101' ie TAKES(17,'CS101'). Rows that give a true statement go in the table and rows that make a false one stay out.
如果我们可以将一个标准重新表述为另外两个的 AND/连接，那么我们只需要具有这些其他标准的表格.这是因为 JOIN 被定义为使得包含使它们的条件为真的行的两个表的 JOIN 返回使它们的条件的 AND/连接为真的行.所以我们可以JOIN这两个表来找回原来的.(这就是规范化通过将表分解为组件所做的.)
If we can rephrase a criterion as the AND/conjunction of two others then we only need the tables with those other criteria. This is because JOIN is defined so that the JOIN of two tables containing the rows making their criteria true returns the rows that make the AND/conjunction of their criteria true. So we can JOIN the two tables to get back the original. (This is what normalization is doing by decomposing tables into components.)
/* 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)
/* student with id [si] has name [sn] and address [sa] and major [sm] */
and takes course [ci] with grade [scg]
SG(si,sn,sa,sm,ci,scg)
/* course [ci] with title [ct]
is taught by instructor with id [ii] and name [in] and office [io] */
CI(ci,ct,ii,in,io,scg)
/* T(si,sn,sa,sm,ci,ct,ii,in,io,scg)
IFF SG(si,sn,sa,sm,ci,scg) AND CI(ci,ct,ii,in,io,scg) */
T = SG JOIN CI
应用程序关系和可能出现的情况一起决定规则和约束！它们只是适用于每种应用情况或每种数据库状态(即一个或多个基表的值)(它们是标准和可能的应用情况的函数.)
Together the application relationships and situations that can arise determine both the rules and constraints! They are just things that are true of every application situation or every database state (ie values of one or more base tables) (which are are a function of the criteria and the possible application situations.)
然后我们标准化以减少冗余.规范化将表变量替换为其他变量，这些变量的谓词与/连接在一起是有益的.
Then we normalize to reduce redundancy. Normalization replaces a table variable by others whose predicates AND/conjoin together to the original's when this is beneficial.
只有当您不真正理解标准或什么时，规则才能告诉您一些您不知道的事情，而这些事情您已经从(推定的)标准和(推定的)情况中了解到情况可能会出现，而先验规则正在澄清一些事情.为您提供规则的人已经在使用他们假设您理解的应用关系，并且他们只能通过使用它们以及可能出现的所有应用情况来确定规则成立(虽然是非正式的)！
The only time a rule can tell you something that you don't know already know from the (putative) criteria and (putative) situations is when you don't really understand the criteria or what situations can turn up, and the a priori rules are clarifying something about that. A person giving you rules is already using application relationships that they assume you understand and they can only have determined that a rule holds by using them and all the application situations that can arise (albeit informally)!
(遗憾的是，许多信息建模演示甚至没有提到应用程序关系.例如:如果有人说存在 X:Y 关系"，那么他们一定已经想到了特定实体之间的二元应用关系；知道它以及可能出现什么应用情况，他们报告它在某个方向上具有一定的基数.这将对应一些应用关系和使用标识实体的列集的表. 加上一些演示/方法将 FK 称为关系"——将它们与这些关系混淆.)
(Sadly many presentations of information modeling don't even mention application relationships. Eg: If someone says "there is a X:Y relationship" then they must already have in mind a particular binary application relationship between entities; knowing it and what application situations can arise, they are reporting that it has a certain cardinality in a certain direction. This will correspond to some application relationship and table using column sets that identify entities. Plus some presentations/methods call FKs "relationships"--confusing them with those relationships.)
检查基于事实"信息建模方法对象-角色建模或(其前身)NIAM.
Check out "fact-based" information modeling methods Object-Role Modeling or (its predecessor) NIAM.
FD &CK
鉴于将行放入或离开表的标准以及可能出现的所有可能情况，只有某些值(行集)可以存在于表变量中.
Given the criterion for putting rows into or leaving them out of a table and all possible situations that can arise, only some values (sets of rows) can ever be in a table variable.
对于列的每个子集，您需要决定对于那些列的给定子行值，哪些其他列只能有一个值.当它只能有一个时，我们说列的子集在功能上决定了该列.我们说有一个FD(函数依赖)列->列.这是我们可以将表的谓词表示为... AND column=F(columns)"的时候.对于某些函数 F.但是该子集的每个超集也将在功能上确定它，从而减少案例.相反，如果给定的集合不能确定列，则该集合的任何子集都不能确定.应用阿姆斯壮公理给出所有当给定 FD 成立时成立的 FD.此外，您可能会认为列集是唯一的；那么所有其他列在功能上都依赖于该集合.这样的集合称为超级键.
For every subset of columns you need to decide which other columns can only have one value for a given subrow value for those columns. When it can only have one we say that the subset of columns functionally determines that column. We say that there is a FD (functional dependency) columns->column. This is when we can express the table's predicate as "... AND column=F(columns)" for some function F. But every superset of that subset will also functionally determine it, so that cuts down on cases. Conversely, if a given set does not determine a column then no subset of the set does. Applying Armstrong's axioms gives all the FDs that hold when given FDs hold. Also, you may think in terms of column sets being unique; then all other columns are functionally dependent on that set. Such a set is called a superkey.
只有在您确定了 FD 之后，您才能确定 CK(候选密钥)！CK 是不包含更小的超级密钥的超级密钥.(存在 CK 和/或超键也是一个约束.)我们可以选择一个 CK 作为 PK(主键).PK 在关系理论中没有其他作用.
Only after you have determined the FDs can you determine the CKs (candidate keys)! A CK is a superkey that contains no smaller superkey. (That a CK and/or superkey is present is also a constraint.) We can pick a CK as PK (primary key). PKs have no other role in relational theory.
部分依赖依赖于来自主键.
A partial dependency relies on either one of the attributes from the Primary key.
不要使用涉及"或依赖"给出定义.说，什么时候"或如果"(当且仅当").
Don't use "involve" or "relies on" to give a definition. Say, "when" or "iff" ("if and only if").
阅读定义.当/iff 使用行列式的适当子集给出具有相同确定列的 FD 时，成立的 FD 是部分的；否则就满了.请注意，这不涉及 CK.当所有非主属性在功能上完全依赖于每个 CK 时，关系处于 2NF.
Read a definition. A FD that holds is partial when/iff using a proper subset of the determinant gives a FD that holds with the same determined column; otherwise it is full. Note that this does not involve CKs. A relation is in 2NF when all non-prime attributes are fully functionally dependent on every CK.
一个传递依赖涉及两个或多个非关键属性非关键属性之一依赖的功能依赖一个关键属性(来自我的 PK).
A transitive dependency involves two or more non-key attributes in a functional dependence where one of the non-key attributes is dependent on a key attribute (from my PK).
阅读定义.S->T 是可传递的，当/如果存在 X，其中 S ->X 和 X ->T而不是(X-＞S)而不是(X＝T).请注意，这不涉及 CK.当所有非主要属性都非传递性地依赖于每个 CK 时，关系处于 3NF.
Read a definition. S -> T is transitive when/iff there is an X where S -> X and X -> T and not (X -> S) and not (X = T). Note that this does not involve CKs. A relation is in 3NF when all non-prime attributes are non-transitively dependent on every CK.
这篇关于识别传递依赖的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
