# CS 385 Database Theory and Applications by dffhrtcv3

VIEWS: 1 PAGES: 26

• pg 1
```									      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
333333    Lars     7878      Shields      2.4
444444    Ivan     7878      Shields      3.5

 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

 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
333333   Lars      7878    Shields    2.4
444444   Ivan      7878    Shields    3.5

 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
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
333333   Lars    7878      Shields   2.4
444444   Ivan    7878      Shields   3.5

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

```
To top