Functional Dependency

kma@ittelkom.ac.id Functional Dependency

Overview

Basic Concepts A constraint on the set of legal relations to express facts about the enterprise for modeling database. Require that the value for a certain set of attributes determines uniquely the value for another set of attributes. A functional dependency is a generalization of the notion of a key.

Concept (cont.) • Functional dependencies (FDs) are used to specify formal measures of the "goodness" of relational designs • FDs and keys are used to define normal forms for relations • FDs are constraints that are derived from the meaning and interrelationships of the data attributes

Definition • A set of attributes X functionally determines a set of attributes Y if the value of X determines a unique value for Y • X Y holds if whenever two tuples have the same value for X, they must have the same value for Y If t1[X]=t2[X], then t1[Y]=t2[Y] in any relation instance r(R) • X Y in R specifies a constraint on all relation instances r(R) • FDs are derived from the real-world constraints on the attributes

Examples of FD constraints • Social Security Number determines employee name SSN ENAME • Project Number determines project name and location PNUMBER {PNAME, PLOCATION} • Employee SSN and project number determines the hours per week that the employee works on the project {SSN, PNUMBER} HOURS

Inference Rules for FDs • Given a set of FDs F, we can infer additional FDs that hold whenever the FDs in F hold • Armstrong's inference rules A1. (Reflexive) If Y subset-of X, then X Y A2. (Augmentation) If X Y, then XZ YZ (Notation: XZ stands for X U Z) A3. (Transitive) If X Y and Y Z, then X Z • A1, A2, A3 form a sound and complete set of inference rules

Armstrong's Rules • Reflexivity Rule • If X is a set of attributes and Y is a subset of X, then X -> Y holds. • Each subset of X is functionally dependent on X. • Augmentation Rule • If X -> Y holds and W is a set of attributes, then WX -> WY holds • If Y is determined by X then a set of attributes W and Y together will be determined by W and X together • Transitivity Rule • If X -> Y and Y -> Z hold, then X -> Z holds • If X functionally determines Y and Y functionally determines Z then X functionally determines Z.

Additional Useful Inference Rules • Decomposition • If X YZ, then X Y and X Z • Union • If X Y and X Z, then X YZ • Psuedotransitivity • If X Y and WY Z, then WX Z

Closure (F+) • The set of all functional dependencies that may be logically derived from F • For example, the student relation has the following functional dependencies • sno -> snamecno -> cnamesno -> addresscno -> instructorinstructor -> office

Closure Algorithms • Let X^c <- X • Let the next dependency be A -> B. If A is in X^c and B is not, X^c <- X^c + B. • Continue step 2 until no new attributes can be added to X^c. • The result of the algorithm is X^c that is equal to X+.

Closure Example (1) • Consider the following relation • student(sno, sname, cno, cname). • Determine the closure of (sno, cno). We have the following functional dependencies. • sno -> sname • cno -> cname

Closure Example (2) • Step 1 --- X^c <- X, that is, X^c <- (sno, cno) • Step 2 --- Consider sno -> sname, since sno is in X^c and sname is not, we have X^c <- (sno, cno) + sname • Step 3 --- Consider cno -> cname, since cno is in X^c and cname is not, we have X^c <- (sno, cno, sname) + cname • Step 4 --- Again, consider sno -> sname but this does not change X^c. • Step 5 --- Again, consider cno -> cname but this does not change X^c. • Therefore X+ = X^c = (sno, cno, sname, cname).

FD & Relationship • Functional dependencies also arise in relationships. • Let C be the primary key of an entity and D be the primary key of another entity. • Let the two entities have a relationship. • If the relationship is one-to-one: • C -> D • D -> C • If the relationship is many-to-one: • C -> D but not D -> C • For many-to-many relationships, no functional dependencies hold.

Examples of FD constraints • Social Security Number determines employee name SSN ENAME • Project Number determines project name and location PNUMBER {PNAME, PLOCATION} • Employee SSN and project number determines the hours per week that the employee works on the project {SSN, PNUMBER} HOURS

Example - 2 • C is student number and D is subject number, there is no functional dependency between them. If however, we were storing marks and grades in the database as well, we would have • (student_number, subject_number) -> marks • marks -> grades

Review • The concept of functional dependency is related to the concept of candidate key of a relation • Therefore any subset X of the attributes of a relation R that satisfies the property that all remaining attributes of the relation are functionally dependent on it (that is, on X), then X is candidate key as long as no attribute can be removed from X and still satisfy the property of functional dependence. • Functional dependence is an important concept and a large body of formal theory has been developed about it. • Once a complete set of functional dependencies has been obtained, we will study how these may be used to build normalised relations.

Functional Dependency