Define and discuss the concept of transitive dependency

VI.E. Third Normal Form

Third normal form is based on the notion of transitive dependency. If A, B, and C are attributes of relation R, such that A → B, and B → C, then C is transitively dependent on A, unless either B or C is a candidate key or part of a candidate key. Equivalently, a transitive dependency exists when a nonprime attribute determines another nonprime attribute. A relation is in third normal form if it is in second normal form and no nonprime attribute is transitively dependent on any candidate key. In the Employee table of the company database in Figure 17, we see that deptID → deptMgr, so we have one nonprime attribute determining another. The only candidate keys for the relation are empID and SSN. This means that deptMgr is transitively dependent on empID, the primary key, since neither deptID nor deptMgr is part of any candidate key for this relation. Because of the transitive dependency, we can still have anomalies. If we have a new department with a new manager, we cannot insert this information unless we have an employee, which is an insertion anomaly. If the manager of department 10 changes from Munez to Miller, we may update one employee's record and fail to update another's in the same department, leading to an update anomaly. If we delete the record of the only employee in a department, we lose the information about the department, including the manager's name.

To correct the design, we can decompose the Employee table into

resulting in the schema shown in Figure 18. The Project and Assignment tables are already in third normal form. In the Project table, projID and projName are both candidate keys, and there are no transitive dependencies. In Assignment, only the composite {empID, projID} is a candidate key, and there are no other functional dependencies.

3NF Correction

Continuing on with the normalization process, the designers now search their new structures for violations of third normal form, which rules out “transitive dependencies.” Transitive dependencies occur when a non-key element determines the value for another non-key element. We can see three such dependencies in the second normal form version of this database. First, in the Sales Order Header table, the sales channel code is not part of the records’ primary key, but it does determine the values for the ad manager, ad site, and promotion that make up the buyer’s path to the purchasing site. Second, in the Line Item table, Package ID is not part of the primary key, but it determines the Package Name. This connection is another transitive relationship that violates third normal form. Finally, the Fulfillment Channel code is not part of the Line Item records’ primary key either, but it dictates the values for Service Category, Manufacturer, and Installer. Considering the update anomalies for just the first of these dependencies, we can see that if this record were to be deleted, the company could lose all knowledge that the Google ad manager has displayed half-off advertisements or any ads on OnlineDepot.com at all. Similarly, if the business rules made advertising sites or promotional styles mandatory values for the record, a transaction missing either of them would get blocked during an insert action, making incomplete the data warehouse’s information regarding ads that have been displayed.

To correct these weaknesses, the designers diligently eliminate each transitive dependency so that each set of items that were not determined directly by a data record’s primary key receive their own single-themed table. In this case, the data modelers updated the design as follows, all of which are illustrated in Figure 12.14:

Sales channel fields receive a dedicated table with columns for Ad Manager, Ad Site, and Promo. Sales order headers will link to this table using the field for the Sales Channel code.

Package Description is split out to a separate table also. Line item records will link to this table via the Package ID.

The fulfillment channel fields land in a table of their own that will have columns for Service Category, Manufacturer, and Installer. Line item records will link to this table using the Fulfillment Channel code.

Note that this type of correction was used for the Package ID and Package Name fields, which represent a code and its meaning. Data modelers learn early on to look for code-decode pairs within the same table, since they are usually third normal form violations. As a general practice, EDW teams will create many reference tables for data warehouses that hold code meanings in order to avoid the update anomalies that third normal form violations engender.

13.3.5 Third normal form

The third normal form (3NF) is based on the concept of a transitive dependency. A functional dependency X → Y in a relation R is a transitive dependency if there is a set of attributes Z that is not a subset of any key of R, and both X → Z and Z → Y hold. In this case we must split this relation into two new relations and a join between both will recover the original relation. In the case of our example, for LOTS2 there is no problem, but for LOTS1 there is a transitive dependency: Property-ID → Area, and Area → Price. So we need to change LOTS1 into (Figure 13.2c):

LOTS1A (Property-ID, County_name, Lot-ID, Area)

LOTS1B (Area, Price)

The resulting rearrangement to satisfy the three normal forms is a set of three relations as shown in Figure 13.2d.

I.C.2.c.iii. Third normal form (3NF)

A table is in 3NF if it is in 2NF and it contains no transitive dependencies; that is, a condition in which an attribute (non-primary-key) is dependent on another attribute (non-primary-key) that is not part of the primary key.

Note the above Emp table is in 2NF, but not in 3NF because JobTitle is the determinant of HourlyRate. Functional dependency analysis on the Emp table can be written in the following format:

We normalized Table II from 1NF to 2NF, and then to 3NF. According to the function dependency analysis we can finally decompose Table II into four different tables. All the tables are in 3NF:

B.4.1 Transitive Dependencies

Consider a table based on the subgraph of Figure B.3:

Candidate Degrees(Candidate,¯Personal Name,Programme,Degree Name)

This table would contain the attributes of the relational product of two FDs:

Programme:Candidates→ProgrammesDegree:Programmes→Degree Names

As a result, the FD Degree: Programmes→Degree Names would be expressed many times, once for each candidate admitted to a programme. This would be redundant and allow anomalies.

Degree Name is said to be transitively dependent on Candidate. A transitive dependency occurs when one non-prime attribute is dependent on another non-prime attribute.

If a table schema T(X¯,Y,Z)contains a dependency f:Y→Z, where Yand Zare non-prime attributes, we say it contains a transitive dependency from Xto Z.

We can see the same situation more clearly in Figure B.3. The path from Candidates to Degree Names is compound, with length two in this case. In general, a transitive dependency can be decomposed by applying Algorithm B.1. In this example, the correct decomposition is into

Candidates(Candidate,¯Personal Name,Programme),Programmes(Programme¯,Degree Name).

Although the transitive dependency is obvious in this example, it is possible for a transitive dependency to be obscured. If we had started with the schema

Candidates(Candidate,¯Personal Name,Degree Name),Programmes(Programme¯,Degree Name),

neither table would have contained a transitive dependency, but the problem would still have been present — so it couldn’t be eliminated by Algorithm B.1.3

Third Normal Form

The 2NF tables we established in the previous section represent a significant improvement over 1NF tables. However, they still suffer from the same types of anomalies as the 1NF tables, although for different reasons associated with transitive dependencies. If a transitive (functional) dependency exists in a table, it means that two separate facts are represented in that table, one fact for each functional dependency involving a different left side. For example, if we delete a report from the database, which involves deleting the appropriate rows from report1 and report3 (see Figure 6.2), we have the side effect of deleting the association between dept_no, dept_name, and dept_addr as well. If we could project report1 over report_no, editor, and dept_no to form table report11, and project report1 over dept_no, dept_name, and dept_addr to form table report12, we could eliminate this problem. Example tables for report11 and report12 are shown in Figure 6.3.

A table is in third normal form (3NF) if and only if for every nontrivial functional dependency X -> A, where X and A are either simple or composite attributes, one of two conditions must hold: either attribute X is a superkey, or attribute A is a member of a candidate key. If attribute A is a member of a candidate key, A is called a prime attribute. Note: A trivial FD is of the form YZ -> Z.

In the preceding example, after projecting report1 into report11 and report12 to eliminate the transitive dependency report_no -> dept_no -> dept_name, dept_addr we have the following 3NF tables and their functional dependencies (and example data in Figure 6.3):

report11: report_no -> editor, dept_no

report12: dept_no -> dept_name, dept_addr

report2: author_id -> author_name, author_addr

report3: (report_no, author_id) is a candidate key (no FDs)

3.3.3 How Expressive Are Bayesian Networks?

One might expect the introduction of directionality into the language of graphs to render directed graphs more expressive, i.e., capable of portraying more conditional independencies. We saw, indeed, that the d-separation criterion permits us to display induced and non-transitive dependencies that were excluded from the Markov network vocabulary. So we might ask how DAGs compare for expressive power with undirected graphs and probability models. Two questions arise:

Can all dependencies that are representable by a Markov network also be represented by a Bayesian network?

How well can Bayesian networks represent the type of dependencies induced by probabilistic models?

The answer to the first question is clearly no. For instance, the dependency structure of a diamond-shaped Markov network (e.g., Figure 3.2) with edges (AB), (AC), (CD), and (BD) asserts two independence relationships: I(A, BC, D) and I(B, AD, C). No Bayesian network can express these two relationships simultaneously and exclusively. If we direct the arrows from A to D, we get I (A, BC, D) but not I(B, AD, C); if we direct the arrows from B to C, we get I(B, AD, C) but not I(A, BC, D). In view of property iv of Theorem 7, it is clear that this difficulty will always be encountered in non-chordal graphs. No matter how we direct the arrows, there will always be a pair of nonadjacent parents sharing a common child, a configuration that yields independence in Markov networks and dependence in Bayesian networks.

On the other hand, property iv of Theorem 7 also asserts that every chordal graph can be oriented so that the tails of every pair of converging arrows are adjacent. Hence, every dependency model that is isomorphic to a chordal graph is also isomorphic to a DAG. We conclude that the class of probabilistic dependencies that can be represented by both a DAG and an undirected graph are those that form decomposable models, i.e., probability distributions that have perfect maps in chordal graphs. These relationships are shown schematically in Figure 3.12.

The answer to Question 2 is also not encouraging. Clearly, no graphical representation can distinguish connectivity between sets from connectivity among their elements. In other word, in both directed and undirected graphs, separation between two sets of vertices is defined in terms of pairwise separation between their corresponding individual elements. In probability theory, on the other hand, independence of elements does not imply independence of sets (see Eq. (3.6b)), as the coins and bell example demonstrated. When the two coins are fair, any two variables are mutually independent, but every variable is (deterministically) dependent on the other two.

CHORDALITY AND AUXILIARY VARIABLES

The chordality axiom (Eq. (3.34g)) excludes dependency models that are isomorphic to non-chordal graphs (such as the one in Figure 3.13a), since these cannot be completely captured by DAGs (see Figure 3.12). In essence, Eq. (3.34g) insists that we either add the appropriate chords to any long cycle (length ≥ 4), thus disobeying the antecedent of Eq. (3.34g), or nullify some of its links, thus satisfying the consequent of Eq. (3.34g).

Figure 3.13. The dependencies of an undirected graph (a) are represented by a DAG (c) using an auxilliary node E.

Though DAGs cannot represent non-chordal dependencies, this deficiency can be eliminated by introducing auxiliary variables. Consider the diamond-shaped graph of Figure 3.13a, which asserts two independence relationships: I(A, BC, D) and I (B, AD, C).

Introducing an auxiliary variable E as shown in Figure 3.13b creates a DAG model of five variables whose dependencies are represented by the joint distribution function

p(a,b,c,d,e)=P(e\d,c)P(d\b)P(c\a)P(b\a)P(a).

Now imagine that we clamp the auxiliary variable E to some fixed value E = e1, as in Figure 3.13c. The dependency structure that the clamped DAG induces on A, B, C, D is identical to the original structure (Figure 3.13a). Indeed, applying the d-separation criterion to Figure 3.12c uncovers the two original independencies: I(A, BC, D) and I(B, AD, C). The marginal distribution of the original variables conditioned upon E = e1 is given by

P(a,b,c,d\E=e1)=P(a,b,c,d,e1)P(e1)        =K P(e1|d,c)P(d\b)P(c\a)P(b\a)P(a)        =g1(d,c)g2(d,b)g3(a,c)g4(a,b).

Using the analysis of Section 3.2.3, we see that this distribution is equivalent to the one portrayed by Figure 3.13a. Thus, the introduction of auxiliary variables permits us to dispose of the chordality restriction of Eq. (3.34g) and renders the DAG representation superior to that of undirected graphs; every dependency model expressible by the latter is also expressible by the former.

Weak transitivity and chordality are not the only dependencies that are sanctioned by probability theory but are not representable by DAGs. For example, one can show that the following axiom must hold in DAGs:

I(Y,X,Z)&I(Z,Y,X)&I(W,Z,X)⇒I(X,∅,W).

But its denial,

I(Y,X,Z)&I(Z,Y,X)&I(W,Z,X)⇒−I(X,∅,W),

is tolerated by probability theory (see Exercise 3.7). The question arises whether the class of properties specific to DAGs can be characterized axiomatically the way that of undirected graphs was (Theorem 2). The answer is probably no. The results of Geiger [1987] strongly suggest that the number of axioms required for a complete characterization of the d-separation in DAGs is unbounded.

Transitive Dependencies

A transitive dependency exists when you have the following functional dependency pattern:

This is precisely the case with the original items relation. The only reason that the warehouse phone number is functionally dependent on the item number is because the distributor is functionally dependent on the item number, and the phone number is functionally dependent on the distributor. The functional dependencies are really:

Note: Transitive dependencies take their name from the transitive property in mathematics, which states that if a > b and b > c, then a > c.

There are two determinants in the original items relation, each of which should be the primary key of its own relation. However, it is not merely the presence of the second determinant that creates the transitive dependency. What really matters is that the second determinant is not a candidate key (could be used as a primary key) for the relation.

Consider, for example, this relation:

The item number is an arbitrary number that Antique Opticals assigns to each merchandise item. The UPC is an industry-wide code that is unique to each item as well. The functional dependencies in this relation are:

Is there a transitive dependency here? No, because the second determinant is a candidate key. (Antique Opticals could just as easily have used the UPC as the primary key.) There are no insertion, deletion, or modification anomalies in this relation; it describes only one entity—the merchandise item.

A transitive dependency therefore exists only when the determinant that is not the primary key is not a candidate key for the relation. For example, in the items table we have been using as an example, the distributor is a determinant, but not a candidate key for the table. (There can be more than one item coming from a single distributor.)

When you have a transitive dependency in a 2NF relation, you should break the relation into two smaller relations, each of which has one of the determinants in the transitive dependency as its primary key. The attributes determined by the determinant become nonkey attributes in each relation. This removes the transitive dependency—and its associated anomalies—and places the relation in third normal form.

Note: A second normal form relation that has no transitive dependencies is, of course, automatically in third normal form.

5.4 Third Normal Form (3NF)

Another normal form can address these problems. A table is in Third Normal Form (3NF) if it is in 2NF and for all X → Y, where X and Y are columns of a table, X is a key or Y is part of a candidate key. (A candidate key is a unique set of columns that identify each row in a table; you cannot remove a column from the candidate key without destroying its uniqueness.) This implies that the table is in 2NF, since a partial key dependency is a type of transitive dependency. Informally, all the nonkey columns are determined by the key, the whole key, and nothing but the key.

The usual way that 3NF is explained is that there are no transitive dependencies, but this is not quite right. A transitive dependency is a situation where we have a table with columns (A, B, C) and (A → B) and (B → C), so we know that (A → C). In our case, the situation is that (course_name, class_section) → room_nbr and room_nbr → room_size. This is not a simple transitive dependency, since only part of a key is involved, but the principle still holds. To get our example into 3NF and fix the problem with the room_size column, we make the following decomposition:

CREATE TABLE Rooms

(room_nbr INTEGER NOT NULL PRIMARY KEY,

room_size INTEGER NOT NULL);

CREATE TABLE Students

(student_name CHAR(25) NOT NULL PRIMARY KEY,

student_major CHAR(10) NOT NULL);

CREATE TABLE Classes

(course_name CHAR(7) NOT NULL,

class_section CHAR(1) NOT NULL,

PRIMARY KEY (course_name, class_section),

time_period INTEGER NOT NULL,

room_nbr INTEGER NOT NULL

REFERENCES Rooms(room_nbr));

CREATE TABLE Enrollment

(student_name CHAR(25) NOT NULL

REFERENCES Students(student_name),

course_name CHAR(7) NOT NULL,

PRIMARY KEY (student_name, course_name),

class_section CHAR(1) NOT NULL,

FOREIGN KEY (course_name, class_section)

REFERENCES Classes(course_name, class_section),

student_grade CHAR(1) NOT NULL);

A common misunderstanding about relational theory is that 3NF tables have no transitive dependencies. As indicated above, if X → Y, X does not have to be a key if Y is part of a candidate key. We still have a transitive dependency in the example—(room_nbr, time_period) → (course_name, class_section)—but since the right side of the dependency is a key, it is technically in 3NF. The unreasonable behavior that this table structure still has is that several course_names can be assigned to the same room_nbr at the same time.

Another form of transitive dependency is a computed column. For example:

CREATE TABLE Boxes

(width INTEGER NOT NULL,

length INTEGER NOT NULL,

height INTEGER NOT NULL,

volume INTEGER NOT NULL

CHECK (width * length * height = volume),

PRIMARY KEY (width, length, height));

The volume column is determined by the other three columns, so any change to one of the three columns will require a change to the volume column. You can use a computed column in this example which would look like:

What is transitive dependency explain with example?

What is a transitive dependency in DBMS?

What is a transitive dependency in software development?

What is meant by transitive dependency in Maven?