Transcript No Slide Title

Presented By: Miss N. Nembhard
Relation Algebra
Relational Algebra is :
 the formal description of how a relational database
operates
 the mathematics which underpin SQL operations.
Operators in relational algebra are not necessarily the
same as SQL operators, even if they have the same name.
Terminology
 Relation - a set of tuples.
 Tuple - a collection of attributes which describe some
real world entity.
 Attribute - a real world role played by a named
domain.
 Domain - a set of atomic values.
 Set - a mathematical definition for a collection of
objects which contains no duplicates.
Operators - Retrieval
There are two groups of operations:
 Mathematical set theory based relations:
UNION, INTERSECTION, DIFFERENCE, and
CARTESIAN PRODUCT.
 Special database operations:
SELECT (not the same as SQL SELECT), PROJECT, and
JOIN.
Query Languages
 A language in which a user requests information
from the database.
 Usually on a higher level than that of standard
programming languages.
Query Languages
 Can be categorized as either procedural or non-
procedural language.
 Procedural - user instructs the system to a sequence of
operations on the database to compute the desired result.
 Non-procedural - the user describes the desired
information without giving the specific procedure for
obtaining the information.
Relational Algebra
 Relational Algebra is a “pure” language because it is
procedural whereas the tuple relational calculus and
domain relational are non-procedural.
 These languages lack the syntax of commercial
languages, but illustrate the fundamental techniques
for extracting data from a database.
Relational Algebra
 A procedural query language. That consists of a set of
operations that take one or two relations as input and
produce a new relation as their result.
 The fundamental operations in the relational algebra
are:
 select, project, union, set difference, Cartesian
product, and rename.
Relational Algebra
 There are several other operations :
 set intersection
 natural join
 division
 assignment
Fundamental Operations
 The select, project, and rename operations are called
unary operations, because they operate on one
relation.
 The other three operations (union, set difference,
Cartesian product) operate on pairs of relations and
are, therefore, called binary, operations.
Select Operation
 Select operation selects tuples that satisfy a given
predicate.
 Use lower case Greek letter sigma () to denote
selection. The predicate appears as a subscript to .
The argument relation is in the parenthesis after the
sign.
Select Operation
SELECT is used to obtain a subset of the tuples of a
relation that satisfy a select condition.
For example, find all employees born after 1st Jan 1950:
SELECT dob > ’01/JAN/1950’ (employee)
Project Operation
 A unary operation that returns its argument relation,
with certain attributes left out.
 Since the relation is a set duplicate rows are
eliminated.
 Projection is denoted by the uppercase Greek letter pi
().
Project Operation
The PROJECT operation is used to select a subset of the
attributes of a relation by specifying the names of the
required attributes.
For example, to get a list of all employees surnames and
employee numbers:
PROJECT surname,empno (employee)
SELECT and PROJECT
SELECT and PROJECT can be combined together. For example,
to get a list of employee numbers for employees in department
number 1:
PROJECT empno (SELECT depno = 1
Mapping this back to SQL gives:
SELECT empno
FROM employee
WHERE depno = 1;
(employee))
Cartesian Product
 The Cartesian product operation, denoted by a cross
(x).
 Allows us to combine information from two relations.
 We write the Cartesian product of relations r1 and r2 as
r1 x r2.
Cartesian Product
The Cartesian Product is also an operator which works
on two sets. It is sometimes called the CROSS
PRODUCT or CROSS JOIN.
It combines the tuples of one relation with all the tuples
of the other relation.
Cartesian Product
R
A
B
D
F
E
1
2
3
4
5
S
A
C
D
E
1
2
3
4
R CROSS S
A
A
A
A
B
B
B
B
D
D
D
D
1
1
1
1
2
2
2
2
3
3
3
3
A
C
D
E
A
C
D
E
A
C
D
E
1
2
3
4
1
2
3
4
1
2
3
4
F
F
F
F
E
E
E
E
4
4
4
4
5
5
5
5
A
C
D
E
A
C
D
E
1
2
3
4
1
2
3
4