# ESpeak Online Speak Test

Document Sample

```					 Algorithmic Foundation for
an interactive Tutor for finding
Candidate Keys of a Relation

Jyoti Agarwal
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