1 / 18

Functional Dependencies (Chapter 14)

Functional Dependencies (Chapter 14). What are functional dependencies. A FD is a constraint between two sets of attributes from the database.. A FD, denoted by X Y, between two sets of attributes X and Y forms a constraint that states:

amber
Download Presentation

Functional Dependencies (Chapter 14)

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. Functional Dependencies(Chapter 14)

  2. What are functional dependencies • A FD is a constraint between two sets of attributes from the database.. • A FD, denoted by X Y, between two sets of attributes X and Y forms a constraint that states: • For any two tuples t1 and t2 such that t1[X] =t2[X], we much also have t1[Y] =t2[Y]. • The values of the Y component of a tuple depend on, or are determined by, the values of X component.

  3. Example Employee(SSN, EName) Project(ProjectNo, ProjectName, DeptName) WorksOn(SSN, ProjectNo, Hours) Functional dependencies: SSN Ename ProjectNo ProjectName DeptName SSN ProjectNo Hour

  4. Additional Notes • A functional dependency is a property of the semantics or meaning of the attributes. • A functional dependency is a property of the relation schema R, not of a particular legal relation state r of R. • FD cannot be inferred from a given relation state. • It must be defined explicitly by someone who knows the semantics of the attributes of R.

  5. Keys and FDs • A key is a determinant, while a determinant may or may not be a key. • A key is always unique to the relation, while a determinant may or may not be unique to the relation. • The most significant difference between keys and FDs is UNIQUENESS and MINIMALITY. • Several terms: • Superkey • Candidate Key • Primary Key

  6. Example Student(SSN, Name, Phone, City, Street, Zip) SSN Phone Name City Street Zip SSN Name City Street Zip City Street Zip

  7. EMP_DEPT EMP_PROJ Example

  8. Computing the Closure of Attributes • What is the closure of attributes? • Given a set of attributes A = {A1, A2, …, An} and a set of FDs S, the closure of A under the FDs in S is the set of attributes B such that every relation that satisfies all the FDs in S also satisfies A1, A2, …, An B. • Bis denoted by A+ ={A1, A2, …, An}+ • How to compute a closure? Algorithm: Determining X+, the closure of X under F X+ = X; repeat oldX+ = X+; for each FD Y Z in F do if Y  X+ then X+ = X+  Z; until ( oldX+ = X+ );

  9. Example Let us consider a relation with attribute A, B, C, D, E and F. Suppose that this relation has the functional dependencies AB C, BC AD. D E, and CF B. 1. What is the closure of {A, B}, that is {A,B}+ ? 2. How to test whether AB D follows from these dependencies? How about D A? 3. What can be the primary key of this relation?

  10. Splitting/Combing Rule • Splitting rule A FD A1 A2 … An B1 B2 … Bm can be replaced by a set of FDs A1 A2 … An Bi for i = 1, 2, …, m. • Combining rule A set of FDs A1 A2 … An Bi for i = 1, 2, …, m can be replaced by a single FD A1 A2 … An B1 B2 … Bm

  11. Trivial-dependency Rule A FD A1 A2 … An B1 B2 … Bm is • Trivial if the B’s are a subset of the A’s. • Nontrivial if at least one of the B’s is not among the A’s. • Completely nontrivial if none of the B’s is also one of the A’s. Trivial-dependency rule The FD A1 A2 … An B1 B2 … Bm is equivalent to A1 A2 … An C1 C2 … Ck, where the C’s are all those B’s that are not also A’s

  12. The Transition Rule • If two FDs A1, A2, …, An B1, B2, …, Bm and B1, B2, …, Bm C1, C2, …, Ck hold in relation R, then A1, A2, …, An C1, C2, …, Ck also holds in R. • This rule can be proved by computing the closure of {A1, A2, …, An} under {A1, A2, …, An B1, B2, …, Bm B1, B2, …, Bm C1, C2, …, Ck }.

  13. A complete Set of Inference Rules • Armstrong’s axioms (1974) • Reflexivity • if X  Y then X Y • Augmentation • {X Y} XZ YZ • Transitivity • {X Y, Y Z} X Z

  14. Other rules • Decomposition ( or projective) rule • { X YZ} X Y • Union ( or additive) rule • {X Y, X Z} X YZ • Pseudotransitive rule • {X Y, WY Z} WX Z

  15. Proof 1. X--->YZ (given) YZ ---> Y ( using Reflexivity) X ---> Y ( applying transitivity to the above two FDs) 2. X ---> Y (given) X----> Z (given) X ----> XY (applying augmentation to rule 1) XY --> YZ (applying augmentation to rule 2) X ---> YZ ( applying transitivity to the above two rules) 3. X ---> Y (given) WY ---> Z (given) WX ---> WY ( using augmentation to rule 1) WX ---> Z (applying transitivity to rule 2 and 3)

  16. Equivalence of Sets Dependencies Given two sets of dependencies E and F, the closures of these two sets are E+ and F+ respectively, We say • E is covered by F, if EF+. • E and F are equivalent if E+ = F+. • How to determine the equivalence? • calculating X+ under F for each FD X Y in E, and then checking whether this X+ includes the attributes in Y. (This is to determine whether E is covered by F) • determining whether F is covered by E by the same approach above.

  17. Example • E = {A B, B A, B C, C A} • F = {A B, B C, C A }

  18. Minimal Sets of FDs • A set of FDs F is minimal if it satisfies the following condition: • Every FD in F has a single attribute for its right-hand side. • We cannot replace any FD X A with a FD Y A, where Y is a proper subset of X, and still have a set of dependencies that is equivalent to F. • We cannot remove any dependency from F and still have a set of FDs that is equivalent to F. • A minimal basis of F is denoted by Fmin, and there may be several minimal bases for one particular F.

More Related