Preserving Functional Dependencies - Teradata Database

Preserving Functional Dependencies

It is often true that a relation can be nonloss-decomposed in more than one way; however, some of those ways are better than others. For example, consider the following relvar:

employee
emp_num	dept_num	budget
PK

This relvar has the following FDs:

emp_no →  dept_no

dept_no →  budget

emp_no » budget

where » indicates the dependency is transitive.

A relvar like employee suffers from certain well known update anomalies that can be avoided by nonloss-decomposition into various projections. Relvar employee can be nonloss‑decomposed into two valid 5NF projections.

For example, consider the following set of 5NF projections:

emp_dept			dept_budget
emp_num	dept_num		dept_num	budget
PK	FK		PK

Call this decomposition A. The second valid 5NF decomposition of employee is as follows:

emp_dept			emp_budget
emp_num	dept_num		emp_num	budget
PK			PK

Call this decomposition B.

Note that the projection emp_dept is the same for both decompositions A and B.

Even though both decompositions are valid, decomposition A is better than decomposition B. For example, it is not possible to represent the fact that a given department has a budget unless that department also has at least one employee in decomposition B.

You can make the determination that decomposition A is better than decomposition B solely on an analysis of the FDs in the original employee relvar. Note that the projections in decomposition A correspond to the nontransitive FDs in employee (indicated by → ). As a result of this, you can update either projection without regard for the other as long as the referential constraint from emp_dept to dept_budget is satisfied. Provided only that the update in question is legal within the context of the given projection, which means only that it must not violate the primary key constraint for that projection, the join of the two projections after the update is still be a valid value for the employee relvar. In other words, the join cannot possibly violate the FD constraints on employee.

In decomposition B, by contrast, one of the two projections corresponds to the transitive dependency in employee (indicated by »). As a result, updates to either projection must at least be monitored to ensure that the FD dept_no →  budget is not violated (in other words, if two employees have the same department, they must also have the same budget - consider, for example, the machinations required in decomposition B to move an employee from department D1 to department D2.)

The problem with decomposition B is that the FD dept_no →  budget spans two relvars. On the other hand, while it is true that the transitive FD dept_no » budget in Decomposition A spans relvars, it is also true that the FD is enforced by default as long as the FDs emp_no →  dept_no and dept_no →  budget, which do not span relvars, are enforced, which only requires enforcement of the corresponding primary key uniqueness constraints.

The point being made is that you should decompose relvars in such a way that functional dependencies are preserved.