Document Sample

Algorithmic Foundation for an interactive Tutor for finding Candidate Keys of a Relation Jyoti Agarwal Advisor: Dr. Irwin B. Levinstein Old Dominion University Master’s Project Presentation Nov 17, 2009. Overview Introduction Textbook Algorithm Candidate Keys Finder Algorithm Complexity More on Complexity Properties Future Considerations Conclusion Overview Introduction Textbook Algorithm Candidate Keys Finder Algorithm Complexity More on Complexity Properties Future Considerations Conclusion Introduction Textbook Algorithm • Taught in Database class world wide • Developed in 1978 • Brute force technique Student • Hard to find candidate keys • Hit and trial technique Candidate Keys Finder Algorithm • Definite • Time efficient • Easy to follow Introduction Textbook Algorithm • Taught in Database class world wide • Developed in 1978 • Brute force technique Student • Hard to find candidate keys • Hit and trial technique Candidate Keys Finder Algorithm • Definite • Time efficient • Easy to follow Introduction Textbook Algorithm • Taught in Database class world wide • Developed in 1978 • Brute force technique Student • Hard to find candidate keys • Hit and trial technique Candidate Keys Finder Algorithm • Definite • Efficient • Easy to follow Overview Introduction Textbook Algorithm Candidate Keys Finder Algorithm Complexity More on Complexity Properties Future Considerations Conclusion Textbook Algorithm Algorithm 11.4(a) Finding a Key K for R Given a set F of Functional Dependencies Input: A universal relation R and a set of functional dependencies F on the attributes of R. 1. Set K:=R. 2. For each attribute A in K { compute (K − A)+ with respect to F; If (K − A)+ contains all the attributes in R, then set K:= K −{A}}; Overview Introduction Textbook Algorithm Candidate Keys Finder Algorithm Complexity More on Complexity Properties Future Considerations Conclusion Candidate Keys Finder Algorithm Input: A relation R and a set of functional dependencies F on the attributes of R. To Find: A set of Candidate Keys CK for R and F. 1. Set Left_only := attributes that are found ONLY on the left side of F. 2. Set Right_only := attributes that are found ONLY on the right side of F. 3. Set Unreachable := attributes that are not found on any side of F. 4. Set Both_sides := attributes that are found on the left side of one dependency and the right side of the other in F. 5. Set var_key := Left_only Unreachable 6. If function Check_key(var_key) returns true, then 7. Set CK := var_key and Exit 8.Else Set combination := all combination’s for attributes in Both_sides 9. For each element i in combination 10. Set new_var_key := var_key combination[i] 11. If function Check_key(new_var_key) returns true, then 12. Set CK := CK {new_var_key } 13. Set j = i+1 14. For each element j in combination 15. if combination[i] combination[j] 16. then combination := combination − {combination[j] } Candidate Keys Finder Algorithm Input: A relation R and a set of functional dependencies F on the attributes of R. To Find: A set of Candidate Keys CK for R and F. 1. Set Left_only := attributes that are found ONLY on the left side of F. 2. Set Right_only := attributes that are found ONLY on the right side of F. 3. Set Unreachable := attributes that are not found on any side of F. 4. Set Both_sides := attributes that are found on the left side of one dependency and the right side of the other in F. 5. Set var_key := Left_only Unreachable 6. If function Check_key(var_key) returns true, then 7. Set CK := var_key and Exit 8.Else Set combination := all combination’s for attributes in Both_sides 9. For each element i in combination 10. Set new_var_key := var_key combination[i] 11. If function Check_key(new_var_key) returns true, then 12. Set CK := CK {new_var_key } 13. Set j = i+1 14. For each element j in combination 15. if combination[i] combination[j] 16. then combination := combination − {combination[j] } Candidate Keys Finder Algorithm Input: A relation R and a set of functional dependencies F on the attributes of R. To Find: A set of Candidate Keys CK for R and F. 1. Set Left_only := attributes that are found ONLY on the left side of F. 2. Set Right_only := attributes that are found ONLY on the right side of F. 3. Set Unreachable := attributes that are not found on any side of F. 4. Set Both_sides := attributes that are found on the left side of one dependency and the right side of the other in F. 5. Set var_key := Left_only Unreachable 6. If function Check_key(var_key) returns true, then 7. Set CK := var_key and Exit 8.Else Set combination := all combination’s for attributes in Both_sides 9. For each element i in combination 10. Set new_var_key := var_key combination[i] 11. If function Check_key(new_var_key) returns true, then 12. Set CK := CK {new_var_key } 13. Set j = i+1 14. For each element j in combination 15. if combination[i] combination[j] 16. then combination := combination − {combination[j] } Candidate Keys Finder Algorithm Input: A relation R and a set of functional dependencies F on the attributes of R. To Find: A set of Candidate Keys CK for R and F. 1. Set Left_only := attributes that are found ONLY on the left side of F. 2. Set Right_only := attributes that are found ONLY on the right side of F. 3. Set Unreachable := attributes that are not found on any side of F. 4. Set Both_sides := attributes that are found on the left side of one dependency and the right side of the other in F. 5. Set var_key := Left_only Unreachable 6. If function Check_key(var_key) returns true, then 7. Set CK := var_key and Exit 8.Else Set combination := all combination’s for attributes in Both_sides 9. For each element i in combination 10. Set new_var_key := var_key combination[i] 11. If function Check_key(new_var_key) returns true, then 12. Set CK := CK {new_var_key } 13. Set j = i+1 14. For each element j in combination 15. if combination[i] combination[j] 16. then combination := combination − {combination[j] } Overview Introduction Textbook Algorithm Candidate Keys Finder Algorithm Complexity More on Complexity Properties Future Considerations Conclusion Complexity Textbook Algorithm • 2n where n is the total number of Attributes of the relation Candidate Keys Finder Algorithm • At max 2b possibilities where b is the total number of Attributes that are found on the left side of one dependency and the right side of the other in F. If a key is found then all possibilities that include it are not checked, as they will become a super key but not a candidate key. Example R = A, B, C, D, E, F F = A→B, B→C, C→A, D→E, E→F, F→D • Text book algorithm = 64 possibilities and 9 keys • Candidate Key Finder Algorithm = 64 possibilities, 40 cancelled and 9 keys. Only 24 possibilities checked! Complexity Textbook Algorithm • 2n where n is the total number of Attributes of the relation Candidate Keys Finder Algorithm • At max 2b possibilities where b is the total number of Attributes that are found on the left side of one dependency and the right side of the other in F. If a key is found then all possibilities that include it are not checked, as they will become a super key but not a candidate key. Example R = A, B, C, D, E, F F = A→B, B→C, C→A, D→E, E→F, F→D • Text book algorithm = 64 possibilities and 9 keys • Candidate Key Finder Algorithm = 64 possibilities, 40 cancelled and 9 keys. Only 24 possibilities checked! Complexity Textbook Algorithm • 2n where n is the total number of Attributes of the relation Candidate Keys Finder Algorithm • At max 2b possibilities where b is the total number of Attributes that are found on the left side of one dependency and the right side of the other in F. If a key is found then all possibilities that include it are not checked, as they will become a super key but not a candidate key. Example R = A, B, C, D, E, F F = A→B, B→C, C→A, D→E, E→F, F→D • Text book algorithm = 64 possibilities and 9 keys • Candidate Keys Finder Algorithm = 64 possibilities, 40 cancelled and 9 keys. Only 24 possibilities checked! Overview Introduction Textbook Algorithm Candidate Keys Finder Algorithm Complexity More on Complexity Properties Future Considerations Conclusion More on Complexity Math of 2b possibilities • 1 possibility for attributes of Left_only group together with Unreachable group • 2b −1 combinations for attributes of Both_sides group • bC1 + bC2 + bC3 +……+ bCb = 2b −1 Best Case = 1 • when attributes of Left_only group together with Unreachable group identify all Attributes of the Relation Worst Case = 2b • when all attributes of Both_sides group, Left_only group together with Unreachable group identify all Attributes of the Relation More on Complexity Math of 2b possibilities • 1 possibility for attributes of Left_only group together with Unreachable group • 2b −1 combinations for attributes of Both_sides group • bC1 + bC2 + bC3 +……+ bCb = 2b −1 Best Case = 1 • when attributes of Left_only group together with Unreachable group identify all Attributes of the Relation Worst Case = 2b • when all attributes of Both_sides group, Left_only group together with Unreachable group identify all Attributes of the Relation More on Complexity Math of 2b possibilities • 1 possibility for attributes of Left_only group together with Unreachable group • 2b −1 combinations for attributes of Both_sides group • bC1 + bC2 + bC3 +……+ bCb = 2b −1 Best Case = 1 • when attributes of Left_only group together with Unreachable group identify all Attributes of the Relation Worst Case = 2b • when all attributes of Both_sides group, Left_only group together with Unreachable group identify all Attributes of the Relation Overview Introduction Textbook Algorithm Candidate Keys Finder Algorithm Complexity More on Complexity Properties Future Considerations Conclusion Properties • If Both_sides = R then length(Key) ≠ b • It will always take less than or equal to 2b possibilities to find the key where b ≠ n • A newly found key by addition of attributes of Both_sides will only eliminate possibilities that have length greater than this newly found key. Overview Introduction Textbook Algorithm Candidate Keys Finder Algorithm Complexity More on Complexity Properties Future Considerations Conclusion Future Considerations • Generating possibilities if and only if required – Generate combinations in stages – Do not generate combinations if a prefix contains a key • Enhance tutoring capabilities • Finding and proving the average case complexity for Candidate Keys Finder Algorithm Future Considerations • Generating possibilities if and only if required – Generate combinations in stages – Do not generate combinations if a prefix contains a key • Enhance tutoring capabilities • Finding and proving the average case complexity for Candidate Keys Finder Algorithm Overview Introduction Textbook Algorithm Candidate Keys Finder Algorithm Complexity More on Complexity Properties Future Considerations Conclusion Conclusion Candidate Keys Finder Algorithm • Definite • Efficient • Easy to follow • Correctly finds candidate keys for R References • Database Concepts CS550 Old Dominion University – Dr. Irwin B. Levinstein • Fundamentals of Database Systems 5th edition – Elmasri and Navathe • “Candidate Keys for Relations” by CL Lucchesi and SL Osborn from “Journal of Computers and Systems Science”, vol 17, p.270, 1978. • Discrete Mathematics 6th edition –Richard Johnson Baugh

DOCUMENT INFO

Shared By:

Categories:

Tags:

Stats:

views: | 25 |

posted: | 7/21/2011 |

language: | English |

pages: | 31 |

OTHER DOCS BY liaoqinmei

How are you planning on using Docstoc?
BUSINESS
PERSONAL

Feel free to Contact Us with any questions you might have.