COMP 5138 Relational Database Management Systems

1
COMP 5138Relational Database Management Systems
Semester 2, 2007 Lecture 5A Relational Algebra
2
Overview

• There are many tasks that can be understood as
  calculating some relation by combining the
  information in one or more relation instances
• Relational algebra defines some operators that
  can be used to express a calculation like that
• A central insight a query that extracts
  information can be seen as calculating a relation
  from the current state of the database

3
Relational Algebra

• Users request information from a database using a
  query language
• Six basic and several additional operators
• Basic operations
• Selection ( ) Selects a subset of rows
  from relation.
• Projection ( ) Extracts only desired
  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 or in reln. 2.
• Rename ( ) Allows us to rename one field to
  another name.
• Additional operations
• Intersection, join, division Not essential, but
  (very!) useful.
• The operators take one or more relations as
  inputs and give a new relation as result

X
_
4
Running Example

• Sailors and Reserves relations for our
  examples.

Instance S2 of sailors
Instance S1 of sailors
Instance R1 of reserves
5
Projection

• Deletes attributes that are not in projection
  list.
• Schema of result contains exactly the fields in
  the projection list, with the same names that
  they had in the (only) input relation.

(S2)
age
(S2)
sname, rating
6
Selection

• Selects rows that satisfy selection condition.

• Result relation can be the input for another
  relational algebra operation! (Operator
  composition.)

( )
sname, rating
7
Set Operation

• All of these operations take two input relations
• Same number of fields.
• Corresponding fields have the same type.
• Set Union R ? S
• Definition R U S t t ? R ? t ? S
• Set Intersection R ? S
• Definition R ? S t t ? R ? t ? S
• Set Difference R - S
• Definition R - S t t ? R ? t ? S

(S2)
(S1)
(S2)
(S1)
_
(S2)
(S1)
8
Cross-Product

• Each row of S1 is paired with each row of R1.
• Result schema has one field per field of S1 and
  R1, with field names inherited if possible.
• Conflict Both S1 and R1 have a field called
  sid.
• Sometimes called Cartesian product

9
Renaming

• Allows us to name, and therefore to refer to, the
  results of relational-algebra expressions.
• Allows us to refer to a relation by more than one
  name.
• Notation 1 ? x (E)
• returns the expression E under the name X
• Notation 2 ?x (A1, A2, , An) (E)
• returns the result of expression E under the name
  X, and with the attributes renamed to A1, A2, .,
  An.
• (assumes that the relational-algebra expression E
  has arity n)
• Example

C( 1? sid1, 5? sid2)( S1XR1)
10
Joins

• Condition Join

• Result schema same as that of cross-product.
• Fewer tuples than cross-product, might be able to
  compute more efficiently
• Sometimes called a theta-join.

11
Joins

• Equi-Join A special case of condition join
  where the condition c contains only equalities.

• Result schema similar to cross-product, but only
  one copy of fields for which equality is
  specified.
• Natural Join Equijoin on all common fields.

R S
S
A
B
C
D
E
B
D
E
? ? ? ? ?
1 1 1 1 2
? ? ? ? ?
a a a a b

• ?
• ? ?
• ?
• ?

? ? ? ? ?
1 2 4 1 2
? ? ? ? ?
a a b a b
1 3 1 2 3
a a a b b
? ? ? ? ?

12
Division

• Not supported as a primitive operator, but useful
  for expressing queries like
• Find sailors who have served on all boats.
• Let A have 2 fields, x and y B have only field
  y
• A/B
• i.e., A/B contains all x tuples (sailors) such
  that for every y tuple (boat) in B, there is an
  xy tuple in A.
• Or If the set of y values (boats) associated
  with an x value (sailor) in A contains all y
  values in B, the x value is in A/B.
• In general, x and y can be any lists of fields y
  is the list of fields in B, and x y is the
  list of fields of A.

13
Examples of Division A/B
B1
B2
B3
A/B3
A/B2
A
A/B1
14
Equivalence Rules

• The following equivalence rules hold
• Commutation rules
• pA ( ?p ( R ) ) ? ?p ( pA ( R ) )
• R S ? S R
• Association rule
• R (S T) ? (R S) T
• Idempotence rules
• pA ( pB ( R ) ) ? pA ( R ) if A ?
  B
• ?p1 (?p2 ( R )) ? ?p1 ? p2 ( R )
• Distribution rules
• pA ( R ? S ) ? pA ( R ) ? pA ( S )
• ?P ( R ? S ) ? ?P ( R ) ? ?P ( S )
• ?P ( R S ) ? ?P (R) S if P
  only references R
• pA,B(R S ) ? pA ( R ) pB ( S ) if
  join-attr. in (A ? B)
• R ( S ? T ) ? ( R S ) ? ( R T )
• These rules are the basis for the automatic
  optimisation of relational algebra expressions