1 / 17

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.

Download Presentation

Functional Dependency

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. kma@ittelkom.ac.id Functional Dependency

  2. Overview

  3. 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.

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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.

  9. 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

  10. 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

  11. 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+.

  12. 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

  13. 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).

  14. 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.

  15. 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

  16. 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

  17. 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.

More Related