Docstoc

ESpeak Online Speak Test

Document Sample
ESpeak Online Speak Test Powered By Docstoc
					 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