Relational Algebra

RELATIONAL QUERY
LANGUAGE
Dr. Thomas Yeboah, Ph.D.
Christian Service University College
Relational Query Languages
• Query languages: Allow manipulation and retrieval of data
from a database.
• Two mathematical Query Languages form the basis for “real”

languages (e.g. SQL), and for implementation:
• Relational Algebra: More operational(procedural), very useful for

representing execution plans.
• Relational Calculus: Lets users describe what they want, rather than
how to compute it. (Non-operational, declarative.)
Relational Algebra
• Relational algebra is the basic set of operations for the
relational model
• These operations enable a user to specify basic retrieval

requests (or queries)
Relational Algebra Overview
• Relational Algebra consists of several groups of operations
• Unary Relational Operations
• SELECT (symbol:  (sigma))
• PROJECT (symbol: (pi))
• RENAME (symbol:  (rho))
• Relational Algebra Operations From Set Theory

• UNION (  ), INTERSECTION ( ), DIFFERENCE (or MINUS, – )
• CARTESIAN PRODUCT ( x )
• Binary Relational Operations

• JOIN (several variations of JOIN exist)
• DIVISION
• Additional Relational Operations

• OUTER JOINS, OUTER UNION
• AGGREGATE FUNCTIONS
In this lecture we will be using the
following tables to illustrate our
examples S2
S1
sid sname rating age sid sname rating age
28 yuppy 9 35.0
22 dustin 7 45.0
31 lubber 8 55.5
31 lubber 8 55.5 44 guppy 5 35.0
58 rusty 10 35.0 58 rusty 10 35.0
R1
sid bid day
22 101 10/10/96
58 103 11/12/96
Relational Algebra
• Basic operations:
• Selection (  ) Selects a subset of rows from relation.
• Projection (
 ) Deletes unwanted columns from relation.
• Cross-product (
) Allows us to combine two relations.
• Set-difference (
) Tuples in reln. 1, but not in reln. 2.
• Union (  ) Tuples in reln. 1 and in reln. 2.
• Additional operations:
• Intersection, join, division, renaming
Projection
• Deletes attributes that are not in S2
projection list. sid sname rating age
28 yuppy 9 35.0
31 lubber 8 55.5
 sname,rating(S2) 44 guppy 5 35.0
58 rusty 10 35.0
sname rating
yuppy 9  age(S 2)
lubber 8 age
guppy 5
35.0
rusty 10
55.5
Selection
• Selects rows that satisfy selection condition.
 rating 8(S2)
sid sname rating age
28 yuppy 9 35.0
58 rusty 10 35.0
• Result relation can be the input for another relational algebra
operation! (Operator composition.)
 sname,rating( rating 8(S2)) sname rating
yuppy 9
rusty 10
Union, Intersection, Set-
Difference
All of these operations take two input relations, which must be
union-compatible:
• Same number of fields.
• `Corresponding’ fields have the same type.
S1 S2
 UNION

22 dustin 7 45.0
31 lubber 8 55.5
58 rusty 10 35.0
44 guppy 5 35.0
28 yuppy 9 35.0
Intersection
S1 S2

31 lubber 8 55.5
58 rusty 10 35.0
Set-Difference
S1 S2

22 dustin 7 45.0
Cross-Product
• Each row of S1 is paired with each row of R1.
• Conflict: Both S1 and R1 have a field called sid.

(sid) sname rating age (sid) bid day
22 dustin 7 45.0 22 101 10/10/96
22 dustin 7 45.0 58 103 11/12/96
31 lubber 8 55.5 22 101 10/10/96
31 lubber 8 55.5 58 103 11/12/96
58 rusty 10 35.0 22 101 10/10/96
58 rusty 10 35.0 58 103 11/12/96
 Renaming operator:  (C(1 sid1, 5  sid 2), S1 R1)

Joins
• Condition Join:
R  c S   c ( R  S)
(sid) sname rating age (sid) bid day
22 dustin 7 45.0 58 103 11/12/96
31 lubber 8 55.5 58 103 11/12/96
S1  R1
S1. sid  R1. sid
• Result schema same as that of cross-product.

• Fewer tuples than cross-product. Filters tuples not
satisfying the join condition.
• Sometimes called a theta-join.
Joins
• Equi-Join: A special case of condition join where the
condition c contains only equalities.
sid sname rating age bid day
22 dustin 7 45.0 101 10/10/96
58 rusty 10 35.0 103 11/12/96
sid ,..,age,bid,..(S1  sid

R1)
• Result schema similar to cross-product, but only one copy of

fields for which equality is specified.
END

Relational Algebra

Uploaded by

Copyright:

Available Formats

Relational Algebra

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Relational Algebra

Uploaded by

Copyright:

Available Formats

RELATIONAL QUERY

• Two mathematical Query Languages form the basis for “real”

• Relational Algebra: More operational(procedural), very useful for

• These operations enable a user to specify basic retrieval

• Relational Algebra Operations From Set Theory

• Binary Relational Operations

• Additional Relational Operations

sid sname rating age

sid sname rating age

sid sname rating age

• Conflict: Both S1 and R1 have a field called sid.

 Renaming operator:  (C(1 sid1, 5  sid 2), S1 R1)

• Result schema same as that of cross-product.

sid ,..,age,bid,..(S1  sid

• Result schema similar to cross-product, but only one copy of

You might also like