1 / 16

Functional Dependence

Learn about functional dependence in databases, how attributes relate to each other, and discover examples and rules of functional dependencies.

issac
Download Presentation

Functional Dependence

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 Dependence Lecture 11 Inst: Haya Sammaneh

  2. Functional Dependence • Existence dependence: The existence of B depends on A • Functional dependence: B’s value depends on A’s value • EmpName is functionally dependent on EmpNo • Given the EmpNo, I can one and only one value of EmpName • Require that the value for a certain set of attributes determines uniquely the value for another set of attributes. • Functional dependence is a generalization of the notion of a key.

  3. Functional Dependencies • Loan-info = (branch-name, loan-number, customer-name, amount)We expect the following set of functional dependencies to hold: loan-number  amount loan-number  branch-namebut would not expect the following to hold: loan-number  customer-name

  4. Examples loan-number  amountloan-number  branch-nameloan-number  customer-name  Another example: reverse of the fd’s above

  5. Closure of a Set of Functional Dependencies • Given a set of functional dependencies F, there are certain other functional dependencies that are logically implied by F. • The set of all functional dependencies logically implied by F is the closure of F. • We denote the closure of F by F+. • We can find all of F+ by applying : • if   , then    (reflexivity) • if   , then    (augmentation) زياده • if    and  , then    (transitivity)تعدي

  6. Closure • We can further simplify computation of F+ by using the following additional rules. • If    holds and    holds, then    holds (union) • If    holds, then    holds and    holds (decomposition) • If    holds and    holds, then    holds (pseudotransitivity)

  7. Examples of Armstrong’s Axioms • We can find all of F+ by applying : • if   , then    (reflexivity)loan-no  loan-no loan-no, amount  loan-noloan-no, amount  amount • if   , then    (augmentation)loan-no  amount (given)loan-no, branch-name amount, branch-name • if    and  , then    (transitivity)loan-no  branch-name (given) branch-name  branch-city (given)loan-no  branch-city

  8. Example • R = (A, B, C, G, H, I) • F = {A  B A  C CG  H • CG  I • B  H} • some members of F+ • A  H • AG  I • CG  HI A  B; B  H A  C; AG  CG; CG  I

  9. result contains all of the attributes of R, so stop Example • R = (A, B, C, G, H, I)F = ( A  B A  C CG  H CG  I B  H} • (AG+)1. Result= AG2. Result= ABCG (A  C; A  B and A  AG)3. Result= ABCGH (CG  H and CG  AGBC)4. Result=ABCGHI (CG  I and CG  AGBCH) • Is AG a candidate key?1. AG  R

  10. Example • R = (A, B, C, G, H, I)F = { A BA CCG HCG IB H} • some members of F + • A H • by transitivity from A B and B H • AG I • by augmenting A C with G, to get AG CG and then transitivity with CG I • CG HI • from CG H and CG I : “union rule”

  11. Example Given this FD for this R(A,B,C,D,E,F) AB C AD E BD AFB Check if AB+ is a key for this relation? AB+ is key if AB+ can find all the attribute of R ABAB BD so B AB  AB+ABD ADE so AD ABDAB+ABDE ABC so AB ABDE AB+ABCDE AFB so AF Not ABDE  AB+ABCDE AB not a key because it does not contain all attributes such as F

  12. Example Given this FD for this R(A,B,C,D,E,F) AB C AD E BD AFB Check if AF+ is a key for this relation? AF is a key

  13. Example Given this FD for this R(A,B,C,D,E,F,G) A D D CG BE EF ABF What are all the keys for this relation? Solution: we look to the right side of FD and take all the attribute which does not found in the FD Here is AB Then find AB+ = ABCDEFG  AB is a key

  14. Example Given this FD for this R(A,B,C,D) AB C CD DA What are all the keys for this relation? Look to the right  B Take all minimum combination with B AB,BC,BD Find AB+=ABCD BC+=ABCD BD+=ABCD ALL AB,BC,BD are keys List all the super keys for R that are not key (not minimal number of attributes) ABC BCD ABCD

  15. Trivial and non trivial dependency • A Functional dependency A1,A2,….An Bn is said to be trivial dependency if B is one of A’s such as: • title, year  title • Not trivial if one of B’s not on A’s such as: • title, year length, year • Complete not trivial dependency if all B’s not found on A’s

  16. Example Given this FD for this R(A,B,C,D) AC D  completely non trivial BC A  completely non trivial DB  completely non trivial What are the keys? Look to right  C  take all combinations  AC, BC, CD What are the super key? ABC ADC BCD ABCD ABD not a super key because C must be found on left side

More Related