Docstoc

CS 385 Database Theory and Applications

Document Sample
CS 385 Database Theory and Applications Powered By Docstoc
					      CS 385
Database Theory and
    Applications

     Lecture Set # 4
    Blase B. Cindric
University of Mount Union
Relational Algebra (Sec. 5.1)
A set of operators (either unary or binary) on
  relations, giving relations

Forms the basis of Database Query operations
  in Relational DBs

2 types of operators in Relational Algebra:
      Set Operations -- from Discrete Mathematics
      Relational Operations -- custom designed for DBs


                CS 385 DB Theory & Applications          79
Set Operations in Relational Algebra
There are four set operators:

    union
    intersection
    difference
    Cartesian product

    For each of these operations, the closure property
      holds (operands are relations, and results are
      relations)
                CS 385 DB Theory & Applications      80
Relational Operations in Relational
Algebra
There are five relational operators:

     selection (sometimes also called restriction)
     projection
     join (actual symbol is the bow-tie operator)
     divide
:=     relational assignment

     Closure also holds for each of these….

        This is important, because we can nest several operations inside
          one another
                    CS 385 DB Theory & Applications                 81
Details of Set Operations
For , , and , the operands must be type
  compatible
     (same degree for both relations, and corresponding
       attributes must come from the same domains)


R  S  all tuples in either R or S or both
R  S  all tuples in both R and S
R  S  all tuples in R and NOT in S
R  S  concatenation of each tuple in R with
  every tuple in S

                CS 385 DB Theory & Applications           82
Examples of Set Operations
  Relation R:                    Relation S:
       A        B    C               A       B     C
       a1       b1   c1              a1      b2    c3
       a1       b2   c3              a2      b2    c2
       a1       b1   c2              a3      b2    c1
       a3       b2   c1

  R  S:
       A        B    C
       a1       b1   c1
       a1       b2   c3
       a1       b1   c2
       a3       b2   c1
       a2       b2   c2



                     CS 385 DB Theory & Applications    83
Examples of Set Operations
  Relation R:                    Relation S:
       A        B    C               A       B    C
       a1       b1   c1              a1      b2   c3
       a1       b2   c3              a2      b2   c2
       a1       b1   c2              a3      b2   c1
       a3       b2   c1

  R  S:
      A         B    C
      a1        b2   c3
      a3        b2   c1

  R  S:
       A        B    C
       a1       b1   c1
       a1       b1   c2




                          CS 385 DB Theory & Applications   84
Examples of Set Operations
  Relation T:                   Relation U:
       F      G    H                P       Q
       f1     g1   h1               p1      q1
       f2     g2   h1               p1      q2
                                    p2      q2

  T  U:
       F     G     H       P      Q
       f1    g1    h1      p1     q1
       f1    g1    h1      p1     q2
       f1    g1    h1      p2     q2
       f2    g2    h1      p1     q1
       f2    g2    h1      p1     q2
       f2    g2    h1      p2     q2




                        CS 385 DB Theory & Applications   85
Relational Operations
Select (also called Restrict)

general form:        CONDITION ( Relation )

semantics:
   result of this operation contains only those tuples in Relation for
      which CONDITION is true
   all attributes of Relation are included in the result




                   CS 385 DB Theory & Applications               86
Examples of Selection
Consider the relation Student:
        StNum     Name     Phone     Dorm         GPA
        111111    Helga    7788      Ketcham      3.7
        222222    Sven     6677      McCready     1.8
        333333    Lars     7878      Shields      2.4
        444444    Ivan     7878      Shields      3.5
        555555    Fritz    7610      McCready     3.1


 GPA >= 3.5 ( Student )

        StNum     Name     Phone     Dorm         GPA
        111111    Helga    7788      Ketcham      3.7
        444444    Ivan     7878      Shields      3.5




                      CS 385 DB Theory & Applications   87
Examples of Selection
 Dorm = “Ketcham” OR Dorm = “McCready” ( Student )

        StNum     Name       Phone      Dorm          GPA
        111111    Helga      7788       Ketcham       3.7
        222222    Sven       6677       McCready      1.8
        555555    Fritz      7610       McCready      3.1


 GPA >= 3.5 AND Dorm = “Shields” ( Student )

        StNum     Name       Phone      Dorm          GPA
        444444    Ivan       7878       Shields       3.5




                    CS 385 DB Theory & Applications         88
Relational Operations
Project

general form:       ATTR_LIST ( Relation )

semantics:
   result of this operation contains only those attributes
      specifically listed in ATTR_LIST
   all tuples of Relation are included in the result, unless this
      would cause duplication of tuples in the result




                   CS 385 DB Theory & Applications                  89
Examples of Projection
Again, consider the relation Student:
        StNum    Name      Phone   Dorm       GPA
        111111   Helga     7788    Ketcham    3.7
        222222   Sven      6677    McCready   1.8
        333333   Lars      7878    Shields    2.4
        444444   Ivan      7878    Shields    3.5
        555555   Fritz     7610    McCready   3.1



 StNum, GPA ( Student )
        StNum    GPA
        111111   3.7
        222222   1.8
        333333   2.4
        444444   3.5
        555555   3.1




                         CS 385 DB Theory & Applications   90
Examples of Projection
 Dorm ( Student )
        Dorm
        Ketcham
        McCready
        Shields



 Phone ( Student )
        Phone
        7788
        6677
        7878
        7610




                      CS 385 DB Theory & Applications   91
Relational Operations
A common operation in relational databases is to join two relations
   that share a common attribute, linking only those tuples from
   each relation that have the same value in their shared attribute.

Effectively, we have a table lookup operation, matching tuples from
   the first relation only with tuples from the second relation that
   match

Example: List the phone #’s of all students who have registered for
  RE 777, Section 03

    Two relations (Student and Regis)
    Phone # data is only in the Student relation
    Course # and Section are only in the Regis relation
                     CS 385 DB Theory & Applications             92
Telephone #’s for RE 777 Section 03
Student:
         StNum    Name    Phone     Dorm     GPA
         111111   Helga   7788      Ketcham   3.7
         222222   Sven    6677      McCready 1.8
         333333   Lars    7878      Shields   2.4
         444444   Ivan    7878      Shields   3.5
         555555   Fritz   7610      McCready 3.1

Regis:
         StNum    CourseNum     Section
         111111   CS 385        01
         111111   PE 999        02
         111111   RE 777        03
         222222   PE 999        15
         222222   RE 777        03
         444444   CS 385        01
         444444   PE 999        04
         444444   MA 888        07

                     CS 385 DB Theory & Applications   93
Telephone #’s for RE 777 Section 03
Step 1: Form the Cartesian Product of the Student and Regis
   relations

Step 2: Select the tuples from step 1 whose StNum values are the
   same in both Student and Regis

Step 3: Select only those tuples from the result of step 2 that have
   RE 777 as the CourseNum value, and 03 as the Section value

Step 4: Project the Name and Phone attributes from the result of
   step 3




                   CS 385 DB Theory & Applications              94
Telephone #’s for RE 777 Section 03
Step 1:

   Student relation has 5 tuples, with 5 attributes each
   Regis relation has 8 tuples, with 3 attributes each

   So … Student  Regis contains 40 tuples, each with 8 attributes

   Consult the distributed page for today’s lecture to see actual contents
     of Student  Regis ….




                    CS 385 DB Theory & Applications                   95
Telephone #’s for RE 777 Section 03
Step 2:

    Student.StNum = Regis.StNum ( Student  Regis )

     Student.                                      Regis.
       StNum    Name      Phone   Dorm     GPA    StNum   CourseNum   Section
       111111   Helga     7788    Ketcham   3.7    111111 CS 385       01
       111111   Helga     7788    Ketcham   3.7    111111 PE 999       02
       111111   Helga     7788    Ketcham   3.7    111111 RE 777       03
       222222   Sven      6677    McCready 1.8     222222 PE 999       15
       222222   Sven      6677    McCready 1.8     222222 RE 777       03
       444444   Ivan      7878    Shields   3.5    444444 CS 385       01
       444444   Ivan      7878    Shields   3.5    444444 PE 999       04
       444444   Ivan      7878    Shields   3.5    444444 MA 888       07




                        CS 385 DB Theory & Applications                     96
Telephone #’s for RE 777 Section 03
Step 3:

 CourseNum = “RE 777” AND Section = “03” (
      Student.StNum = Regis.StNum ( Student  Regis ) )

       Student.                                       Regis.
         StNum     Name      Phone   Dorm     GPA    StNum   CourseNum   Section
         111111    Helga     7788    Ketcham   3.7    111111 RE 777       03
         222222    Sven      6677    McCready 1.8     222222 RE 777       03




                           CS 385 DB Theory & Applications                     97
Telephone #’s for RE 777 Section 03
Step 4:

 Name, Phone (  CourseNum = “RE 777” AND Section = “03” (
      Student.StNum = Regis.StNum ( Student  Regis ) ) )

          Name       Phone
          Helga      7788
          Sven       6677




                         CS 385 DB Theory & Applications      98
The Natural Join Operation
Linking relations based on the values of their shared field(s) is such a
   common operation that a special operator has been included in the
   relational algebra just for this purpose

Join

general form:     Relation1  Relation2

semantics:
    result of this operation contains one copy of each attribute in both
       relations (note: only one copy of the shared attributes), and one
       tuple for each pair of tuples from Relation1 and Relation2 that have
       the same value(s) in their shared attribute(s)



                      CS 385 DB Theory & Applications                      99
Examples of Join Operations
  Relation T:                   Relation U:
       A        B    C              C       D      E
       a1       b1   c1             c3      d3     e1
       a2       b1   c2             c2      d4     e2
       a2       b2   c2             c1      d1     e3
       a3       b1   c3             c1      d2     e4

  T  U:
      A         B    C     D      E
      a1        b1   c1    d1     e3
      a1        b1   c1    d2     e4
      a2        b1   c2    d4     e2
      a2        b2   c2    d4     e2
      a3        b1   c3    d3     e1



                     CS 385 DB Theory & Applications    100
Examples of Join Operations
  Relation R:                    Relation S:
       A        B    C               B       C     D
       a1       b1   c1              b1      c2    d3
       a2       b1   c2              b1      c2    d4
       a2       b2   c2              b1      c3    d1
       a3       b1   c3              b3      c3    d2

  R  S:
      A         B    C     D
      a2        b1   c2    d3
      a2        b1   c2    d4
      a3        b1   c3    d1




                     CS 385 DB Theory & Applications    101
Natural Join Operation
If the two relations have no attributes in common, what
    happens?

      The join operation is identical to the Cartesian Product of the two
        relations


What if the two relations being joined have exactly the
 same attributes?

      The join operation is identical to the Intersection of the two
        relations



                   CS 385 DB Theory & Applications                     102
Relational Operations
Relational Assignment

general form:       Destination := Expression

semantics:
   Expression is evaluated, and the result is stored as the new
     value of Destination
   Destination may be either a temporary name for a query result,
     or an actual base relation
       in this latter case, relational assignment represents a database
          modification operation

example: see the next section on the divide operator…

                   CS 385 DB Theory & Applications                  103

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:1
posted:6/29/2012
language:
pages:26