Relational Algebra

1
Relational Algebra

• Archana Gupta
• CS 157

2
What is Relational Algebra?

• Relational Algebra is formal description of how
  relational database operates.
• It is a procedural query language, i.e. user must
  define both how and what to retrieve.
• It consists of a set of operators that consume
  either one or two relations as input. An operator
  produces one relation as its output.

3
Introduction to Relational Algebra

• Introduced by E. F.
• Codd in 1970.
• Codd proposed such
• an algebra as a basis
• for database query
• languages.

4
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.

5
Algebra Operations

• Unary Operations - operate on one relation. These
  include select, project and rename operators.
• Binary Operations - operate on pairs of
  relations. These include union, set difference,
  division, cartesian product, equality join,
  natural join, join and semi-join operators.

6
Select Operator

• The Select operator selects tuples that satisfies
  a predicate e.g. retrieve the employees whose
  salary is 30,000
• ? Salary 30,000 (Employee)
• Conditions in Selection
• Simple Condition (attribute)(comparison)(attribu
  te)
• (attribute)(comparison)(constant)
• Comparison ,?,,,lt,gt
• Condition combination of simple conditions with
  AND, OR, NOT

7
Select Operator Example
Person
?Age34(Person)
?AgeWeight(Person)
8
Project Operator

• Project (?) retrieves a column. Duplication is
  not permitted.
• e.g., name of employees
• ? name(Employee)
• e.g., name of employees earning more than
  80,000
• ? name(?Salarygt80,000(Employee))

9
Project Operator Example
Employee
? name(Employee)
10
Project Operator Example
?Salarygt80,000(Employee)
Employee
? name(?Salarygt80,000(Employee))
11
Cartesian Product

• In mathematics, it is a set of all pairs of
  elements (x, y) that can be constructed from
  given sets, X and Y, such that x belongs to X and
  y to Y.
• It defines a relation that is the concatenation
  of every tuple of relation R with every tuple of
  relation S.

12
Cartesian Product Example
City
Person
Person X City
13
Rename Operator

• In relational algebra, a rename is a unary
  operation written as ? a / b (R) where
• a and b are attribute names
• R is a relation
• The result is identical to R except that the b
  field in all tuples is renamed to an a field.
• Example, rename operator changes the name of its
  input table to its subscript,
• ?employee(Emp)
• Changes the name of Emp table to employee

14
Rename Operator Example
? EmployeeName / Name (Employee)
Employee
15
Union Operator

• The union operation is denoted U as in set
  theory. It returns the union (set union) of two
  compatible relations.
• For a union operation r U s to be legal, we
  require that,
• r and s must have the same number of
  attributes.
• The domains of the corresponding attributes
  must be the same.
• As in all set operations, duplicates are
  eliminated.

16
Union Operator Example
Professor
Student
Student U Professor
17
Intersection Operator

• Denoted as ? . For relations R and S,
  intersection is R ? S.
• Defines a relation consisting of the set of all
  tuples that are in both R and S.
• R and S must be union-compatible.
• Expressed using basic operations
• R ? S R (R S)

18
Intersection Operator Example
Professor
Student
Student ? Professor
19
Set Difference Operator

• For relations R and S,
• Set difference R - S, defines a relation
  consisting of the tuples that are in relation R,
  but not in S.
• Set difference S R, defines a relation
  consisting of the tuples that are in relation S,
  but not in R.

20
Set Difference Operator Example
Professor
Student
Professor - Student
Student - Professor
21
Division Operator

• The division operator takes as input two
  relations, called the dividend relation (r on
  scheme R) and the divisor relation (s on scheme
  S) such that all the attributes in S also appear
  in R and S is not empty. The output of the
  division operation is a relation on scheme R with
  all the attributes common with S.

22
Division Operator Example
Completed
DBProject
Completed / DBProject
23
Natural Join Operator

• Natural join is a dyadic operator that is
  written as R lXl S where R and S are relations.
  The result of the natural join is the set of all
  combinations of tuples in R and S that are equal
  on their common attribute names.

24
Natural Join Example
For an example, consider the tables Employee and
Dept and their natural join
Employee
Employee lXl Dept
Dept
25
Semijoin Operator

• The semijoin is joining similar to the natural
  join and written as R ? S where R and S are
  relations. The result of the semijoin is only the
  set of all tuples in R for which there is a tuple
  in S that is equal on their common attribute
  names.

26
Semijoin Example
For an example consider the tables Employee and
Dept and their semi join
Employee
Employee ? Dept
Dept
27
Outerjoin Operator

• Left outer join
• The left outer join is written as R X S where R
  and S are relations. The result of the left outer
  join is the set of all combinations of tuples in
  R and S that are equal on their common attribute
  names, in addition to tuples in R that have no
  matching tuples in S.
• Right outer join
• The right outer join is written as R X S where
  R and S are relations. The result of the right
  outer join is the set of all combinations of
  tuples in R and S that are equal on their common
  attribute names, in addition to tuples in S that
  have no matching tuples in R.

28
Left Outerjoin Example
For an example consider the tables Employee and
Dept and their left outer join
Employee
Employee X Dept
Dept
29
Right Outerjoin Example
For an example consider the tables Employee and
Dept and their right outer join
Employee
Employee X Dept
Dept
30
Full Outer join Example
The outer join or full outer join in effect
combines the results of the left and right outer
joins. For an example consider the tables
Employee and Dept and their full outer join
Employee
Employee X Dept
Dept
31
References

• http//en.wikipedia.org/wiki/Relational_algebraOu
  ter_join
• http//www.cs.sjsu.edu/faculty/lee/cs157/cs157alec
  turenotes.htm
• Database System Concepts, 5th edition,
  Silberschatz, Korth, Sudarshan