Today’s class

1. Today’s class Intro. to Functional Dependency (FD) Theory of FD’s

2. Functional Dependency • A kind of Unique-value constraint • Knowledge of these constraints vital to eliminate redundancy in database • A FD on a relation R is of the form XY , read as X functionally determines Y where X, Y are sets of attributes of R, such that whenever two tuples in R have same values for X, they must have same values for Y • Given X  Y where Y is the set of all attributes of R then X is a key for R

3. Consider the following relational schema Movies(title, year, length, filmType, studioName, starName) Then following FD’s hold on ‘Movies’ title year  length title year  filmType title year  studioName or title year  length filmType studioName But title year  starName does not hold on ‘Movies’ Example FDs

4. ‘Key’ Definitions • A set of attributes X is a key for relation R if • X functionally determines all attributes of R • X is minimal • X is a ‘superkey’ for R if it contains a key • Primary key – A key so designated if there are several keys • If relations come from ER diagram finding keys is simple • Relation from entity sets: key of the entity set • Relation from binary relationship: • Many – One : key of Many side entity set • One – One : key of either side entity set • Many – Many : keys of both entity sets • Relation from Ternary relationship: no simple way

5. Theory of FD’s • Reason about FD’s • Rules that help derive new FD’s that may hold on a relation, given set of FD’s • Equivalences of sets of functional dependencies • Finding all functional dependencies that exist on a relation • Finding the key of a relation given the set of FD’s it satisfies

6. Equivalence rule • A set of FD’s S ‘follows’ from a set of FD’s T if every relation instance that satisfies all the FD’s in T also satisfies all the FD’s in S • Two sets of FD’s S and T are ‘equivalent’ if and only if S follows from T, and T follows from S

7. Rules of FD’s • If W, X, Y, Z are sets of attributes of R then • If Y X then X  Y (reflexivity) • If X  Y then ZX  ZY (augmentation) • If X  Y and Y  Z then X  Z (transitivity) • If X  Y and X  Z then X  YZ (Union) • If X  YZ then X Y and X  Z (decomposition) • If W  X and XY  Z then WY  Z • If XY  ZY then XY  Z • First three are known as Armstrong’s Axioms

8. The following is not a correct rule • If XY  Z then X  Y and Y  Z • Example: • title year  length

9. Attribute closure • Let F be a set of FD’s holding on a relation R. Let X,Y be sets of attributes of R. Then Y is said to be attribute closure of X, denoted by X+=Y, if XY ‘follows’ from F • Algorithm • Example: find attribute closure of {A,B} w.r.t the FD’s : AB  C, BC  AD, D  E • Useful to check whether a given FD follows from given set of FD’s: example AB  D, D  A • Useful to check whether a given set of attributes forms key w.r.t the FD’s • Useful to find all FD’s that hold on a relation