# COMP 5138 Relational Database Management Systems - PowerPoint PPT Presentation

PPT – COMP 5138 Relational Database Management Systems PowerPoint presentation | free to download - id: bf1cf-ZDc1Z

The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
Title:

## COMP 5138 Relational Database Management Systems

Description:

### ... of rows from relation. Projection ( ) Extracts only desired columns from relation. ... Result relation can be the input for another relational algebra operation! ... – PowerPoint PPT presentation

Number of Views:14
Avg rating:3.0/5.0
Slides: 15
Provided by: kellie7
Category:
Tags:
Transcript and Presenter's Notes

Title: COMP 5138 Relational Database Management Systems

1
COMP 5138 Relational 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.
• 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