{"id":88,"date":"2013-12-12T00:09:58","date_gmt":"2013-12-12T00:09:58","guid":{"rendered":"http:\/\/opentextbc.ca\/dbdesign01\/?post_type=chapter&#038;p=88"},"modified":"2019-06-11T16:42:21","modified_gmt":"2019-06-11T16:42:21","slug":"chapter-11-functional-dependencies","status":"publish","type":"chapter","link":"https:\/\/opentextbc.ca\/dbdesign01\/chapter\/chapter-11-functional-dependencies\/","title":{"raw":"Chapter 11 Functional Dependencies","rendered":"Chapter 11 Functional Dependencies"},"content":{"raw":"A <em>functional dependency<\/em>\u00a0(FD) is a relationship between two attributes, typically between the PK and other non-key attributes within a table. For any relation R, attribute Y is functionally dependent on attribute X (usually the PK), if for every valid instance of X, that value of X uniquely determines the value of <span style=\"color: #000000;\">Y. This relationship is indicated by the representation below :<\/span>\r\n<p style=\"padding-left: 90px;\"><strong>X \u2014\u2014\u2014\u2013&gt; Y<\/strong><\/p>\r\n<span style=\"color: #000000;\">The left side of the above FD\u00a0diagram\u00a0i<\/span>s called the<em> determinant<\/em>, and the right side is the <em>dependent<\/em>. Here are a few examples.\r\n\r\nIn the first example, below,\u00a0SIN determines Name, Address and Birthdate. Given SIN, we can determine any of the other attributes within the table.\r\n<div class=\"textbox\" style=\"text-align: center;\"><strong>SIN\u00a0\u00a0 \u2014\u2014\u2014-&gt; Name, Address, Birthdate<\/strong><\/div>\r\nFor the second example, SIN and Course determine the date completed (DateCompleted). This must also work for a composite PK.\r\n<div class=\"textbox\" style=\"text-align: center;\"><strong>SIN, Course\u00a0 \u2014\u2014\u2014&gt; \u00a0\u00a0\u00a0 DateCompleted<\/strong><\/div>\r\nThe third example indicates that\u00a0ISBN determines Title.\r\n<div class=\"textbox\" style=\"text-align: center;\"><strong>ISBN\u00a0 \u2014\u2014\u2014\u2013&gt;\u00a0 Title<\/strong><\/div>\r\n<h2>Rules of Functional Dependencies<\/h2>\r\nConsider the following table of data r(R) of the relation schema R(ABCDE) shown in Table 11.1.\r\n\r\n[caption id=\"attachment_81\" align=\"aligncenter\" width=\"141\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Table-R-Functional-Dependency-example.jpg\"><img class=\"wp-image-81 size-full\" alt=\"Table-R-Functional-Dependency-example\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Table-R-Functional-Dependency-example.jpg\" height=\"216\" width=\"141\" \/><\/a> Table 11.1. Functional dependency example, by A. Watt.[\/caption]\r\n\r\nAs you look at this table, ask yourself: <em>What kind of dependencies can we observe among the attributes in Table R? <\/em><span style=\"color: #000000;\">Since the values of A are unique (a1, a2, a3, etc.), it foll<\/span>ows from the FD definition that:\r\n\r\nA \u2192 B,\u00a0\u00a0\u00a0 A \u2192 C,\u00a0\u00a0\u00a0 A \u2192 D,\u00a0\u00a0\u00a0 A \u2192 E\r\n<ul>\r\n \t<li>It also follows that\u00a0 A \u2192BC \u00a0(or any other subset of ABCDE).<\/li>\r\n \t<li>This can be summarized as\u00a0\u00a0 A \u2192BCDE.<\/li>\r\n \t<li>From our understanding of primary keys, A is a primary key.<\/li>\r\n<\/ul>\r\nSince the values of E are always <span style=\"color: #000000;\">the same (all e1), it follo<\/span>ws that:\r\n\r\nA \u2192 E,\u00a0\u00a0 B \u2192 E,\u00a0\u00a0 C \u2192 E,\u00a0\u00a0 D \u2192 E\r\n\r\nHowever, we cannot generally summarize the above with\u00a0 ABCD \u2192 E\u00a0<span style=\"color: #000000;\"> because, <\/span>in general,\u00a0\u00a0 A \u2192 E,\u00a0\u00a0 B \u2192 E, \u00a0 AB \u2192 E.\r\n\r\nOther observations:\r\n<ol>\r\n \t<li>Combinations of BC are unique, therefore\u00a0 BC \u2192 ADE.<\/li>\r\n \t<li>Combinations of BD are unique, therefore\u00a0 BD \u2192 ACE.<\/li>\r\n \t<li>If C values match, so do D values.\r\n<ol>\r\n \t<li>Therefore,\u00a0 C \u2192 D<\/li>\r\n \t<li>However, D values don\u2019t determine C values<\/li>\r\n \t<li>So C does not determine D, and D does not determine C.<\/li>\r\n<\/ol>\r\n<\/li>\r\n<\/ol>\r\n<span>Looking at actual data can help clarify which attributes are dependent and which are determinants.\u00a0<\/span>\r\n<h2>Inference Rules<\/h2>\r\n<em>Armstrong\u2019s axioms<\/em> are a set of inference rules used to infer all the functional dependencies on a relational database. They were developed by William W. Armstrong. The following describes what will be used, in terms of notation, to explain these axioms.\r\n\r\nLet R(U) be a relation scheme over the set of attributes U. We will use the letters <span style=\"color: #000000;\">X, Y, Z <\/span>to represent any subset of and, for short, the union of two sets of attributes, instead of the usual\u00a0 <span style=\"color: #000000;\">X U Y.<\/span>\r\n<h3>Axiom of reflexivity<\/h3>\r\nThis axiom says, if Y is a subset of X, then X determines Y (see Figure 11.1).\r\n\r\n[caption id=\"attachment_82\" align=\"aligncenter\" width=\"289\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axion-Reflexivity.jpg\"><img class=\"wp-image-82 size-full\" alt=\"Ch-11-Axion-Reflexivity\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axion-Reflexivity.jpg\" height=\"59\" width=\"289\" \/><\/a> Figure 11.1. Equation for axiom of reflexivity.[\/caption]\r\n\r\nFor example, <strong>PartNo \u2014&gt; NT123<\/strong>\u00a0 where X (PartNo) is composed of more than one piece of information; i.e., Y\u00a0(NT) and partID (123).\r\n<h3>Axiom of augmentation<\/h3>\r\nThe axiom of augmentation, also known as a partial dependency, s<span>ays if X determines Y, then XZ determines YZ for any Z (see Figure 11.2 ).<\/span>\r\n\r\n[caption id=\"attachment_83\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-of-Augmentation-300x34.jpg\"><img src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-of-Augmentation-300x34.jpg\" alt=\"Ch-11-Axiom-of-Augmentation-300x34\" class=\"wp-image-83 size-full\" height=\"34\" width=\"300\" \/><\/a> Figure 11.2. Equation for axiom of augmentation.[\/caption]\r\n\r\n<span>The axiom of augmentation says that every non-key attribute must be fully dependent on the PK. In the example shown below, StudentName, Address, City, Prov, and PC (postal code) are only dependent on the StudentNo, not on the StudentNo and Grade.<\/span>\r\n\r\n<span style=\"color: #000000;\">StudentNo, C<\/span>ourse \u2014&gt; StudentName, Address, City, Prov, PC, Grade, DateCompleted\r\n\r\nThis situation is not desirable because <span style=\"color: #000000;\">every non-key attribute\u00a0has to be\u00a0<\/span>fully dependent on the PK. In this situatio<span style=\"color: #000000;\">n,\u00a0student information is o<\/span>nly partially dependent on the PK (StudentNo).\r\n\r\nTo fix this problem, we need to break <span style=\"color: #000000;\">the original table do<\/span>wn into two as follows:\r\n<ul>\r\n \t<li>Table 1: StudentNo, Course, \u00a0Grade, DateCompleted<\/li>\r\n \t<li>Table 2: StudentNo, StudentName, Address, City, Prov, PC<\/li>\r\n<\/ul>\r\n<h3>Axiom of transitivity<\/h3>\r\nThe axiom of transitivity says if X determines Y, and Y determines Z, then X must also determine Z (see Figure 11.3).\r\n\r\n[caption id=\"attachment_84\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-of-transitivity-300x30.jpg\"><img src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-of-transitivity-300x30.jpg\" alt=\"Ch-11-Axiom-of-transitivity-300x30\" class=\"wp-image-84 size-full\" height=\"30\" width=\"300\" \/><\/a> Figure 11.3. Equation for axiom of transitivity.[\/caption]\r\n\r\nThe table below has information not directly related to the student; for instance, ProgramID and ProgramName should have a table of its own. ProgramName is not dependent on StudentNo; it's dependent on ProgramID.\r\n\r\nStudentNo<span> \u00a0\u2014&gt; <\/span>StudentName, Address, City, Prov, PC, ProgramID, ProgramName\r\n\r\nThis situation is not desirable because a non-key attribute (ProgramName) depends on another non-key attribute (ProgramID).\r\n\r\n<span style=\"color: #000000;\">To fix this problem<\/span>, we need to break this table into two: one to hold information about the student and the other to hold information about the program.\r\n<ul>\r\n \t<li>Table 1: StudentNo \u2014&gt; StudentName, Address, City, Prov, PC, ProgramID<\/li>\r\n \t<li>Table 2: ProgramID \u2014&gt; ProgramName<strong>\r\n<\/strong><\/li>\r\n<\/ul>\r\nHowever we still need to leave an FK\u00a0in the student table so that we can identify which program the student is enrolled in.\r\n<h3>Union<\/h3>\r\nThis rule suggests that if two tables are separate, and the PK is the same, you may want to consider putting them together. It states that if X determines Y and X determines Z then X must also determine Y and Z (see Figure 11.4).\r\n\r\n[caption id=\"attachment_85\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-Union-300x23.jpg\"><img src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-Union-300x23.jpg\" alt=\"Ch-11-Axiom-Union-300x23\" class=\"wp-image-85 size-full\" height=\"23\" width=\"300\" \/><\/a> Figure 11.4. Equation for the Union rule.[\/caption]\r\n\r\nFor example, if:\r\n<ul>\r\n \t<li>SIN \u2014&gt; EmpName<\/li>\r\n \t<li>SIN \u2014&gt; SpouseName<\/li>\r\n<\/ul>\r\nYou may want to join these two tables into one as follows:\r\n\r\nSIN \u2013&gt; EmpName, SpouseName\r\n\r\nSome database administrators (<em>DBA<\/em>) might choose to keep these tables separated for a couple of reasons. One, each table describes a different entity so the entities should be kept apart. Two, if SpouseName is to be left NULL most of the time, there is no need to include it in the same table as EmpName.\r\n<h3>Decomposition<\/h3>\r\nDecomposition is the reverse of the Union rule. If you have a table that appears to contain two entities that are determined by the same PK, consider breaking them up into two tables. This rule states that if X determines Y and Z, then X determines Y and X determines Z separately (see Figure 11.5).\r\n\r\n[caption id=\"attachment_86\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-Decomposition-300x28.jpg\"><img src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-Decomposition-300x28.jpg\" alt=\"Ch-11-Axiom-Decomposition-300x28\" class=\"wp-image-86 size-full\" height=\"28\" width=\"300\" \/><\/a> Figure 11.5. Equation for decompensation rule.[\/caption]\r\n<h2>Dependency Diagram<\/h2>\r\nA dependency diagram, shown in Figure 11.6, illustrates the various dependencies that might exist in a <em>non-normalized table<\/em>. A non-normalized table is one that has data redundancy in it.\r\n\r\n[caption id=\"attachment_87\" align=\"aligncenter\" width=\"300\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Dependency-Diagram-300x67.jpg\"><img src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Dependency-Diagram-300x67.jpg\" alt=\"Ch-11-Dependency-Diagram-300x67\" class=\"wp-image-87 size-full\" height=\"67\" width=\"300\" \/><\/a> Figure 11.6. Dependency diagram.[\/caption]\r\n\r\nThe following dependencies are identified in this table:\r\n<ul>\r\n \t<li>ProjectNo and EmpNo, combined, are the PK.<\/li>\r\n \t<li>Partial Dependencies:\r\n<ul>\r\n \t<li>ProjectNo \u2014&gt; ProjName<\/li>\r\n \t<li>EmpNo \u2014&gt; EmpName, DeptNo, <span style=\"color: #ff0000;\">\r\n<\/span><\/li>\r\n \t<li>ProjectNo, EmpNo \u2014&gt; HrsWork<\/li>\r\n<\/ul>\r\n<\/li>\r\n \t<li>Transitive Dependency:\r\n<ul>\r\n \t<li>DeptNo \u2014&gt; DeptName<\/li>\r\n<\/ul>\r\n<\/li>\r\n<\/ul>\r\n<div class=\"textbox textbox--key-takeaways\"><header class=\"textbox__header\">\r\n<p class=\"textbox__title\">Key Terms<\/p>\r\n\r\n<\/header>\r\n<div class=\"textbox__content\"><strong>Armstrong\u2019s axioms<\/strong>: a set of inference rules used to infer all the functional dependencies on a relational database<strong>DBA<\/strong>: database administrator\r\n\r\n<strong>decomposition<\/strong>: a rule that suggests if you have a table that appears to contain two entities that are determined by the same PK, consider breaking them up into two tables\r\n\r\n<strong>dependent<\/strong>:\u00a0the right side of the functional dependency\u00a0diagram\r\n\r\n<strong>determinant<\/strong>: the left side of the functional dependency diagram\r\n\r\n<strong>functional dependency (FD):<\/strong>\u00a0a relationship between two attributes, typically between the PK and other non-key attributes within a table\r\n\r\n<strong>non-normalized table<\/strong>: a table that has data redundancy in it\r\n\r\n<strong>Union<\/strong>: a rule that\u00a0suggests that if two tables are separate, and the PK is the same, consider putting them together\r\n\r\n<\/div>\r\n<\/div>\r\n<div class=\"textbox textbox--exercises\"><header class=\"textbox__header\">\r\n<p class=\"textbox__title\">Exercises<\/p>\r\n\r\n<\/header>\r\n<div class=\"textbox__content\">\r\n\r\nSee Chapter 12.\r\n\r\n<\/div>\r\n<\/div>\r\n<h2>Attributions<\/h2>\r\n<span style=\"color: #1f1f1d;\">This chapter of\u00a0<\/span><em style=\"color: #1f1f1d;\">Database Design<\/em><span style=\"color: #1f1f1d;\"> (including images, except as otherwise noted) is a derivative copy of <a href=\"http:\/\/en.wikipedia.org\/wiki\/Armstrong%27s_axioms\">Armstrong's axioms<\/a>\u00a0<\/span>by Wikipedia the Free Encyclopedia licensed under <a href=\"http:\/\/creativecommons.org\/licenses\/by-sa\/3.0\/\">Creative Commons Attribution-ShareAlike 3.0 Unported\u00a0<\/a>\r\n\r\nThe following material was written by Adrienne Watt:\r\n<ol>\r\n \t<li>some of Rules of Functional Dependencies<\/li>\r\n \t<li>Key Terms<\/li>\r\n<\/ol>","rendered":"<p>A <em>functional dependency<\/em>\u00a0(FD) is a relationship between two attributes, typically between the PK and other non-key attributes within a table. For any relation R, attribute Y is functionally dependent on attribute X (usually the PK), if for every valid instance of X, that value of X uniquely determines the value of <span style=\"color: #000000;\">Y. This relationship is indicated by the representation below :<\/span><\/p>\n<p style=\"padding-left: 90px;\"><strong>X \u2014\u2014\u2014\u2013&gt; Y<\/strong><\/p>\n<p><span style=\"color: #000000;\">The left side of the above FD\u00a0diagram\u00a0i<\/span>s called the<em> determinant<\/em>, and the right side is the <em>dependent<\/em>. Here are a few examples.<\/p>\n<p>In the first example, below,\u00a0SIN determines Name, Address and Birthdate. Given SIN, we can determine any of the other attributes within the table.<\/p>\n<div class=\"textbox\" style=\"text-align: center;\"><strong>SIN\u00a0\u00a0 \u2014\u2014\u2014-&gt; Name, Address, Birthdate<\/strong><\/div>\n<p>For the second example, SIN and Course determine the date completed (DateCompleted). This must also work for a composite PK.<\/p>\n<div class=\"textbox\" style=\"text-align: center;\"><strong>SIN, Course\u00a0 \u2014\u2014\u2014&gt; \u00a0\u00a0\u00a0 DateCompleted<\/strong><\/div>\n<p>The third example indicates that\u00a0ISBN determines Title.<\/p>\n<div class=\"textbox\" style=\"text-align: center;\"><strong>ISBN\u00a0 \u2014\u2014\u2014\u2013&gt;\u00a0 Title<\/strong><\/div>\n<h2>Rules of Functional Dependencies<\/h2>\n<p>Consider the following table of data r(R) of the relation schema R(ABCDE) shown in Table 11.1.<\/p>\n<figure id=\"attachment_81\" aria-describedby=\"caption-attachment-81\" style=\"width: 141px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Table-R-Functional-Dependency-example.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-81 size-full\" alt=\"Table-R-Functional-Dependency-example\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Table-R-Functional-Dependency-example.jpg\" height=\"216\" width=\"141\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Table-R-Functional-Dependency-example.jpg 141w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Table-R-Functional-Dependency-example-65x99.jpg 65w\" sizes=\"auto, (max-width: 141px) 100vw, 141px\" \/><\/a><figcaption id=\"caption-attachment-81\" class=\"wp-caption-text\">Table 11.1. Functional dependency example, by A. Watt.<\/figcaption><\/figure>\n<p>As you look at this table, ask yourself: <em>What kind of dependencies can we observe among the attributes in Table R? <\/em><span style=\"color: #000000;\">Since the values of A are unique (a1, a2, a3, etc.), it foll<\/span>ows from the FD definition that:<\/p>\n<p>A \u2192 B,\u00a0\u00a0\u00a0 A \u2192 C,\u00a0\u00a0\u00a0 A \u2192 D,\u00a0\u00a0\u00a0 A \u2192 E<\/p>\n<ul>\n<li>It also follows that\u00a0 A \u2192BC \u00a0(or any other subset of ABCDE).<\/li>\n<li>This can be summarized as\u00a0\u00a0 A \u2192BCDE.<\/li>\n<li>From our understanding of primary keys, A is a primary key.<\/li>\n<\/ul>\n<p>Since the values of E are always <span style=\"color: #000000;\">the same (all e1), it follo<\/span>ws that:<\/p>\n<p>A \u2192 E,\u00a0\u00a0 B \u2192 E,\u00a0\u00a0 C \u2192 E,\u00a0\u00a0 D \u2192 E<\/p>\n<p>However, we cannot generally summarize the above with\u00a0 ABCD \u2192 E\u00a0<span style=\"color: #000000;\"> because, <\/span>in general,\u00a0\u00a0 A \u2192 E,\u00a0\u00a0 B \u2192 E, \u00a0 AB \u2192 E.<\/p>\n<p>Other observations:<\/p>\n<ol>\n<li>Combinations of BC are unique, therefore\u00a0 BC \u2192 ADE.<\/li>\n<li>Combinations of BD are unique, therefore\u00a0 BD \u2192 ACE.<\/li>\n<li>If C values match, so do D values.\n<ol>\n<li>Therefore,\u00a0 C \u2192 D<\/li>\n<li>However, D values don\u2019t determine C values<\/li>\n<li>So C does not determine D, and D does not determine C.<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<p><span>Looking at actual data can help clarify which attributes are dependent and which are determinants.\u00a0<\/span><\/p>\n<h2>Inference Rules<\/h2>\n<p><em>Armstrong\u2019s axioms<\/em> are a set of inference rules used to infer all the functional dependencies on a relational database. They were developed by William W. Armstrong. The following describes what will be used, in terms of notation, to explain these axioms.<\/p>\n<p>Let R(U) be a relation scheme over the set of attributes U. We will use the letters <span style=\"color: #000000;\">X, Y, Z <\/span>to represent any subset of and, for short, the union of two sets of attributes, instead of the usual\u00a0 <span style=\"color: #000000;\">X U Y.<\/span><\/p>\n<h3>Axiom of reflexivity<\/h3>\n<p>This axiom says, if Y is a subset of X, then X determines Y (see Figure 11.1).<\/p>\n<figure id=\"attachment_82\" aria-describedby=\"caption-attachment-82\" style=\"width: 289px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axion-Reflexivity.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-82 size-full\" alt=\"Ch-11-Axion-Reflexivity\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axion-Reflexivity.jpg\" height=\"59\" width=\"289\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axion-Reflexivity.jpg 289w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axion-Reflexivity-65x13.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axion-Reflexivity-225x45.jpg 225w\" sizes=\"auto, (max-width: 289px) 100vw, 289px\" \/><\/a><figcaption id=\"caption-attachment-82\" class=\"wp-caption-text\">Figure 11.1. Equation for axiom of reflexivity.<\/figcaption><\/figure>\n<p>For example, <strong>PartNo \u2014&gt; NT123<\/strong>\u00a0 where X (PartNo) is composed of more than one piece of information; i.e., Y\u00a0(NT) and partID (123).<\/p>\n<h3>Axiom of augmentation<\/h3>\n<p>The axiom of augmentation, also known as a partial dependency, s<span>ays if X determines Y, then XZ determines YZ for any Z (see Figure 11.2 ).<\/span><\/p>\n<figure id=\"attachment_83\" aria-describedby=\"caption-attachment-83\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-of-Augmentation-300x34.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-of-Augmentation-300x34.jpg\" alt=\"Ch-11-Axiom-of-Augmentation-300x34\" class=\"wp-image-83 size-full\" height=\"34\" width=\"300\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-of-Augmentation-300x34.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-of-Augmentation-300x34-65x7.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-of-Augmentation-300x34-225x25.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-83\" class=\"wp-caption-text\">Figure 11.2. Equation for axiom of augmentation.<\/figcaption><\/figure>\n<p><span>The axiom of augmentation says that every non-key attribute must be fully dependent on the PK. In the example shown below, StudentName, Address, City, Prov, and PC (postal code) are only dependent on the StudentNo, not on the StudentNo and Grade.<\/span><\/p>\n<p><span style=\"color: #000000;\">StudentNo, C<\/span>ourse \u2014&gt; StudentName, Address, City, Prov, PC, Grade, DateCompleted<\/p>\n<p>This situation is not desirable because <span style=\"color: #000000;\">every non-key attribute\u00a0has to be\u00a0<\/span>fully dependent on the PK. In this situatio<span style=\"color: #000000;\">n,\u00a0student information is o<\/span>nly partially dependent on the PK (StudentNo).<\/p>\n<p>To fix this problem, we need to break <span style=\"color: #000000;\">the original table do<\/span>wn into two as follows:<\/p>\n<ul>\n<li>Table 1: StudentNo, Course, \u00a0Grade, DateCompleted<\/li>\n<li>Table 2: StudentNo, StudentName, Address, City, Prov, PC<\/li>\n<\/ul>\n<h3>Axiom of transitivity<\/h3>\n<p>The axiom of transitivity says if X determines Y, and Y determines Z, then X must also determine Z (see Figure 11.3).<\/p>\n<figure id=\"attachment_84\" aria-describedby=\"caption-attachment-84\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-of-transitivity-300x30.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-of-transitivity-300x30.jpg\" alt=\"Ch-11-Axiom-of-transitivity-300x30\" class=\"wp-image-84 size-full\" height=\"30\" width=\"300\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-of-transitivity-300x30.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-of-transitivity-300x30-65x6.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-of-transitivity-300x30-225x22.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-84\" class=\"wp-caption-text\">Figure 11.3. Equation for axiom of transitivity.<\/figcaption><\/figure>\n<p>The table below has information not directly related to the student; for instance, ProgramID and ProgramName should have a table of its own. ProgramName is not dependent on StudentNo; it&#8217;s dependent on ProgramID.<\/p>\n<p>StudentNo<span> \u00a0\u2014&gt; <\/span>StudentName, Address, City, Prov, PC, ProgramID, ProgramName<\/p>\n<p>This situation is not desirable because a non-key attribute (ProgramName) depends on another non-key attribute (ProgramID).<\/p>\n<p><span style=\"color: #000000;\">To fix this problem<\/span>, we need to break this table into two: one to hold information about the student and the other to hold information about the program.<\/p>\n<ul>\n<li>Table 1: StudentNo \u2014&gt; StudentName, Address, City, Prov, PC, ProgramID<\/li>\n<li>Table 2: ProgramID \u2014&gt; ProgramName<strong><br \/>\n<\/strong><\/li>\n<\/ul>\n<p>However we still need to leave an FK\u00a0in the student table so that we can identify which program the student is enrolled in.<\/p>\n<h3>Union<\/h3>\n<p>This rule suggests that if two tables are separate, and the PK is the same, you may want to consider putting them together. It states that if X determines Y and X determines Z then X must also determine Y and Z (see Figure 11.4).<\/p>\n<figure id=\"attachment_85\" aria-describedby=\"caption-attachment-85\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-Union-300x23.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-Union-300x23.jpg\" alt=\"Ch-11-Axiom-Union-300x23\" class=\"wp-image-85 size-full\" height=\"23\" width=\"300\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-Union-300x23.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-Union-300x23-65x4.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-Union-300x23-225x17.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-85\" class=\"wp-caption-text\">Figure 11.4. Equation for the Union rule.<\/figcaption><\/figure>\n<p>For example, if:<\/p>\n<ul>\n<li>SIN \u2014&gt; EmpName<\/li>\n<li>SIN \u2014&gt; SpouseName<\/li>\n<\/ul>\n<p>You may want to join these two tables into one as follows:<\/p>\n<p>SIN \u2013&gt; EmpName, SpouseName<\/p>\n<p>Some database administrators (<em>DBA<\/em>) might choose to keep these tables separated for a couple of reasons. One, each table describes a different entity so the entities should be kept apart. Two, if SpouseName is to be left NULL most of the time, there is no need to include it in the same table as EmpName.<\/p>\n<h3>Decomposition<\/h3>\n<p>Decomposition is the reverse of the Union rule. If you have a table that appears to contain two entities that are determined by the same PK, consider breaking them up into two tables. This rule states that if X determines Y and Z, then X determines Y and X determines Z separately (see Figure 11.5).<\/p>\n<figure id=\"attachment_86\" aria-describedby=\"caption-attachment-86\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-Decomposition-300x28.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-Decomposition-300x28.jpg\" alt=\"Ch-11-Axiom-Decomposition-300x28\" class=\"wp-image-86 size-full\" height=\"28\" width=\"300\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-Decomposition-300x28.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-Decomposition-300x28-65x6.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Axiom-Decomposition-300x28-225x21.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-86\" class=\"wp-caption-text\">Figure 11.5. Equation for decompensation rule.<\/figcaption><\/figure>\n<h2>Dependency Diagram<\/h2>\n<p>A dependency diagram, shown in Figure 11.6, illustrates the various dependencies that might exist in a <em>non-normalized table<\/em>. A non-normalized table is one that has data redundancy in it.<\/p>\n<figure id=\"attachment_87\" aria-describedby=\"caption-attachment-87\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Dependency-Diagram-300x67.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Dependency-Diagram-300x67.jpg\" alt=\"Ch-11-Dependency-Diagram-300x67\" class=\"wp-image-87 size-full\" height=\"67\" width=\"300\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Dependency-Diagram-300x67.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Dependency-Diagram-300x67-65x14.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2013\/12\/Ch-11-Dependency-Diagram-300x67-225x50.jpg 225w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-87\" class=\"wp-caption-text\">Figure 11.6. Dependency diagram.<\/figcaption><\/figure>\n<p>The following dependencies are identified in this table:<\/p>\n<ul>\n<li>ProjectNo and EmpNo, combined, are the PK.<\/li>\n<li>Partial Dependencies:\n<ul>\n<li>ProjectNo \u2014&gt; ProjName<\/li>\n<li>EmpNo \u2014&gt; EmpName, DeptNo, <span style=\"color: #ff0000;\"><br \/>\n<\/span><\/li>\n<li>ProjectNo, EmpNo \u2014&gt; HrsWork<\/li>\n<\/ul>\n<\/li>\n<li>Transitive Dependency:\n<ul>\n<li>DeptNo \u2014&gt; DeptName<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<div class=\"textbox textbox--key-takeaways\">\n<header class=\"textbox__header\">\n<p class=\"textbox__title\">Key Terms<\/p>\n<\/header>\n<div class=\"textbox__content\"><strong>Armstrong\u2019s axioms<\/strong>: a set of inference rules used to infer all the functional dependencies on a relational database<strong>DBA<\/strong>: database administrator<\/p>\n<p><strong>decomposition<\/strong>: a rule that suggests if you have a table that appears to contain two entities that are determined by the same PK, consider breaking them up into two tables<\/p>\n<p><strong>dependent<\/strong>:\u00a0the right side of the functional dependency\u00a0diagram<\/p>\n<p><strong>determinant<\/strong>: the left side of the functional dependency diagram<\/p>\n<p><strong>functional dependency (FD):<\/strong>\u00a0a relationship between two attributes, typically between the PK and other non-key attributes within a table<\/p>\n<p><strong>non-normalized table<\/strong>: a table that has data redundancy in it<\/p>\n<p><strong>Union<\/strong>: a rule that\u00a0suggests that if two tables are separate, and the PK is the same, consider putting them together<\/p>\n<\/div>\n<\/div>\n<div class=\"textbox textbox--exercises\">\n<header class=\"textbox__header\">\n<p class=\"textbox__title\">Exercises<\/p>\n<\/header>\n<div class=\"textbox__content\">\n<p>See Chapter 12.<\/p>\n<\/div>\n<\/div>\n<h2>Attributions<\/h2>\n<p><span style=\"color: #1f1f1d;\">This chapter of\u00a0<\/span><em style=\"color: #1f1f1d;\">Database Design<\/em><span style=\"color: #1f1f1d;\"> (including images, except as otherwise noted) is a derivative copy of <a href=\"http:\/\/en.wikipedia.org\/wiki\/Armstrong%27s_axioms\">Armstrong&#8217;s axioms<\/a>\u00a0<\/span>by Wikipedia the Free Encyclopedia licensed under <a href=\"http:\/\/creativecommons.org\/licenses\/by-sa\/3.0\/\">Creative Commons Attribution-ShareAlike 3.0 Unported\u00a0<\/a><\/p>\n<p>The following material was written by Adrienne Watt:<\/p>\n<ol>\n<li>some of Rules of Functional Dependencies<\/li>\n<li>Key Terms<\/li>\n<\/ol>\n","protected":false},"author":1,"menu_order":11,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":["adrienne-watt"],"pb_section_license":""},"chapter-type":[],"contributor":[47],"license":[],"class_list":["post-88","chapter","type-chapter","status-publish","hentry","contributor-adrienne-watt"],"part":3,"_links":{"self":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/88","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/users\/1"}],"version-history":[{"count":27,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/88\/revisions"}],"predecessor-version":[{"id":1150,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/88\/revisions\/1150"}],"part":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/parts\/3"}],"metadata":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/88\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/media?parent=88"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapter-type?post=88"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/contributor?post=88"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/license?post=88"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}