识别传递依赖关系 [英] Identifying Transitive Dependencies
问题描述
我正在和一个桌子它具有由两个属性组成的复合主键，表中共有10个属性，以1NF形式。
在我的情况下，完全功能的依赖关系涉及依赖于我的主键中的BOTH属性。部分依赖依赖于主键中的任一个属性。传递依赖涉及功能依赖中的两个或多个非关键属性，其中一个非关键属性取决于关键属性（来自我的PK）。
假设我没有误会，不管我的理解，把传递性依赖从表中拉出来，就是打乱我的大脑。看起来你会在这样的规范化之后做到这一点，但是我的分配要求我们在绘制依赖关系图之前，先确定所有的功能依赖关系，之后我们对表进行规范化。
我将列出表中的属性，然后提供业务规则 - 括号中标识PK属性：
（学生证），学生名称，学生地址，学生专业，（课程编号），课程名称，教师编号，讲师姓名，讲师办公室，Student_crse_grade
每个课程ID只教一个班。
学生最多可能需要4门课程。
每门课程最多可能有25名学生。
每门课程只有一名教练。
每个学生可能只有一个专业。
从你的问题看来，您不了解基础知识。
应用程序关系和情况
首先，您必须了解您对应用程序的了解（包括先验规则），并确定应用程序关系。每个都得到一个基表（又名关系）。这种应用关系的特征在于行成员标准（又称谓词）（也称为意义）。例如假设标准student [student_id] take course [course_title]有表TAKES。标准的参数是其表的列。我们可以使用带有列（如SQL声明）的表名作为标准的缩写。例如TAKES（student_id，course_title）。一个标准加上一行就作了一个陈述（命题）。例如行（17，'CS101'）给学生17课程CS101，即TAKES（17，'CS101'）。如果我们可以将一个标准分成两个，那就是AND一起，那么我们只有需要具有新标准的表。这是因为JOIN被定义为使得包含使其条件为true的行的两个表的JOIN返回使它们的条件的AND为真的行。所以我们可以加入这两张表来拿回原件。 （这是通过将表分解成组件来进行正常化。）
- 具有id [si]的学生名称[sn ]和地址[sa]和主要的[sm]
，并且从教员那里获取标题[ct]
的课程，ID为[ii]，名称为[in]，office [io]
与等级[scg]
T（si，sn，sa，sm，ci，ct，ii，in，io，scg）
- id [si]的学生名[sn]和地址[sa]和主要[sm]
并且考取[ci]与[scg]
SG（si，sn，sa，sm，ci，scg）
- 标题[ct]
的课程由具有id [ii]和名称[in]和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）和CI（ci，ct，ii，in，io，scg）
- T = SG JOIN CI
应用程序关系和情况确定规则和FD（和其他约束）！它们只是对于每个应用程序情况或每个数据库状态（即一个或多个基表的值）（它们是标准和可能的应用程序情况的函数）的真实情况。然后我们规范化以减少冗余。 p>
只有一个规则才能告诉你，你不知道从（推定的）标准和（推定的）情况已经知道的是当你不真的了解标准或什么情况可能出现，并且先验规则正在澄清一些事情。提供规则的人员已经使用了他们假定您理解的应用程序关系，并且他们只能通过使用它们和所有可能出现的应用程序情况来确定规则。 （尽管非正式）！
（很遗憾的是，许多信息建模演示甚至没有提及应用程序关系，例如：如果有人说有一个X：Y关系，那么他们必须已经记住了实体之间的特定二进制应用程序关系，知道它们以及应用程序的情况可能出现，他们报告说它在一定方向上具有一定的基数这将对应于使用标识实体的列集的一些应用程序关系和故事。）
（查看对象角色建模或Nijssen的NIAM演示文稿） p>
FDs，CKs和规范化
给出将行放入或离开它们的标准在表格和所有可能出现的情况下，只有一些值（一组行）可以在该表中。
对于每个列的每个子集您需要确定哪些其他列只能为这些列的给定子值赋予一个值。当它只能有一个我们说列的子集功能确定该列。但是，该子集的每个超集也将在功能上确定它，从而减少案例。相反，如果给定的集合不确定列，则集合的子集不会。另外，你可以考虑列集是唯一的;那么所有其他列在功能上取决于该集合。这样的一个集合称为超级键。
只有确定了FD后，您才能确定候选键！ CK是一个超级键，它不会有更小的超级键。 （CK和超级键的存在也是约束）我们可以选择CK作为主键。
部分依赖依赖于任一个来自
主键的属性。
不要使用涉及或依赖给出定义。说，何时或只有当。
阅读定义。当且仅当使用行列式的适当子集给出具有相同确定列的FD时，FD是部分的;否则它是满的。请注意，这不涉及CK。当所有非素数属性完全在功能上取决于每个CK时，关系在2NF。
传递依赖涉及两个或更多非关键属性在
功能依赖中，其中一个非关键属性依赖于
（来自我的PK）。
阅读定义。当S→X和X→T而不是（X→S）时，S→T是传递的。请注意，这不涉及CK。当2NF中的关系为3NF时，所有非素数属性都不依赖于每个CK。
So, I believe I have the understanding of Fully Functional Dependencies, and Partial Dependencies. I'll provide a brief explanation, in case I'm doing something wrong I don't end up too far down the rabbit hole.
I am working with a table that has a composite Primary Key composed of two attributes, with a total of 10 attributes in the table, in 1NF form.
In my situation, a fully functional dependency involves the dependent relying on BOTH attributes in my Primary Key. A partial dependency relies on either one of the attributes from the Primary key. 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).
Assuming that I'm not mistaken and regardless of my understanding, pulling the transitive dependencies out of the table is wracking my brain. It seems like you would 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.
I will list the attributes in the table, and then the business rules provided - parenthesis identify the PK attributes:
(Student ID), Student Name, Student Address, Student Major, (Course ID), Course Title, Instructor ID, Instructor Name, Instructor Office, Student_crse_grade
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.
From your question it seems that you do not have a clear understanding of basics.
Application relationships and situations
First you have to take what you were told about your application (including a priori rules) and identify the application relationships. Each gets a base table (aka relation). Such an application relationship is characterized by a row membership criterion (aka predicate) (aka meaning). Eg suppose criterion "student [student_id] takes course [course_title]" has table 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 (proposition). 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.
If we can split a criterion into two that are ANDed together then we only need the tables with the new 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 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 determine both the rules and FDs (and other 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.
The only time a rule can tell you something 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)!
(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 tale using column sets that identify entities.)
(Check out Object-Role Modeling or Nijssen's presentations of his NIAM.)
FDs, CKs and normalization
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 that table.
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. 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. 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.
Only after you have determined the FDs can you determine the candidate keys! A CK is a superkey that constains no smaller superkey. (The presence of CKs and superkeys are also constraints.) We can pick a CK as primary key.
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 "if and only if".
Read a definition. A FD is partial if and only if using a proper subset of the determinant gives a FD 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.
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).
Read a definition. S -> T is transitive when there is an X where S -> X and X -> T and not(X -> S). Note that this does not involve CKs. A relation is in 3NF when it is in 2NF and all non-prime attributes are non-transitively dependent on every CK.
这篇关于识别传递依赖关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
