FUNCTION DEPENDENCY AND DECOMPOSITION:

FUNCTION DEPENDENCY AND DECOMPOSITION

In the previous article we’ve discussed about normalization. In this section we’ll discuss about FUNCTION DEPENDENCY AND DECOMPOSITION. FUNCTION DEPENDENCY is what we have to remove it in second normal form. The single most important concept in relational schema design theory is that of FUNCTION DEPENDENCY AND DECOMPOSITION.

DEFNITION OF FUNCTION DEPENDENCY:

A function dependency is a constraint between two sets of attribute from the database. Suppose that our relational database schema has n attributes. A1,A2,….An. Let us think of the whole database as being described by a single universal relation schema R. A FUNCTIONAL DEPENDENCY , denoted by X —> Y, between two sets of attributes X and Y that are subset of R specifies a constraint on the possible tuple that can form a relation state r of R .

The constraints are:

  • For any two tuples t1 and t2 in r , that have t1[x]=t2[x],they must also have t1[y]=t2[y]
  • this means that the values of the y component of a tuple in r depends on , or are determine by the values of x component
  • alternatively ,the values of the x component of a tuple uniquely (or functionally) determine the values of the y component
  • we also say that there is functional dependency from X to Y

TYPES OF FUNCTION DEPENDENCIES:

  • Trivial function dependency
  • non-trivial function dependency

TRIVIAL FUNCTION DEPENDENCY

If a function dependency X –> Y holds , where Y is subset of X , then it is called trivial function dependency. Trivial function dependency always holds that means they are always true.

NON TRIVIAL FUNCTION DEPENDENCY

If a function dependency X–>Y holds, where Y is not subset of X ,then it is non trivial function dependency.

PROPERTIES OF FUNCTION DEPENDENCY (ARMSTRONG’S AXIOMS):

REFLEXIVE RULE

If Y is subset of X then X–>Y . This is call as reflexive rule.

AUGMENT RULE

If X–>Y, then XZ–>YZ. This is call as augment rule

TRANSITIVE RULE

If X–>Y, Y–>Z then X–>Z

UNION

IF X–>Y and Y–>Z THEN X–>YZ

DECOMPOSITION

If X–>YZ then X–>Y and X–>Z

PSEUDO TRANSITIVITY

If X–>Y and WY–>Z then WX–>Z

COMPOSITION

If X–>Y and Z–>W then XZ–>YW

DECOMPOSITION AND ITS TYPES