Relational Algebra: Lecture Outline

Relational Algebra
Lecture Outline
 Relational Query Languages

 Why Relational Algebra is important
 Basic operations
 Joins Operation
Relational Query Languages
 Structured Query Language (SQL)

 The standard relational database language
 Declarative- what data to retrieve
 Relational Algebra
 Intermediate language within DBMS
 Procedural- specify a strategy for evaluating a query
 Relational Calculus
 Non-procedural- no description of how to evaluate a query
SQL and Relational Algebra
 In SQL we write WHAT data we want to retrieve from the database

 The database system needs to figure out HOW to get the data
 The translation from WHAT to HOW is accomplished through the Relational Algebra
Relational Algebra: a language within DBMS

Relational Algebra : HOW
Relational Algebra
 Relational algebra operations work on one or more relations to define another relation without
changing the original relations.
 Both operands and results are relations, so output from one operation can become input to
another operation.
 Allows expressions to be nested, just as in arithmetic. This property is called closure.
Relational Algebra
 Five basic operations:

1. Selection (σ)
2. Projection (Π)
3. Cartesian product (X)
4. Union (∪) and
5. Set Difference (-)
 Also have extended operations:
1. Join
2. Intersection (∩) and
3. Division operations
 The extended operations can be expressed in terms of 5 basic operations.
Relational Algebra Operations
Selection (or Restriction)
 σpredicate (R)
 Works on a single relation R
 Defines a relation that contains only those tuples (rows) of R that satisfy the specified
condition (predicate).
 The predicate can be =, , ≥, <>
Projection
 Πcol1, . . . , coln(R)
 Works on a single relation R
 Defines a relation that contains a vertical subset of R, extracting the values of specified
attributes and eliminating duplicates.
Union
 R∪S
 Union of two relations R and S defines a relation that contains all the tuples of R, or S, or
both R and S, duplicate tuples being eliminated.
 R and S must be union-compatible.
 If R and S have I and J tuples, respectively, union is obtained by concatenating them into one
relation with a maximum of (I + J) tuples.
Set Difference
 R–S
 Defines a relation consisting of the tuples that are in relation R, but not in S.
Intersection
 R∩S
 Defines a relation consisting of the set of all tuples that are in both R and S.
 Expressed using basic operations:
 R ∩ S = R – (R – S)
Cartesian product
 RXS
 Defines a relation that is the concatenation of every tuple of relation R with every tuple of
relation S.
Join Operations
 Join is a derivative of Cartesian product.

 Equivalent to performing a Selection, using join predicate as selection formula, over Cartesian
product of the two operand relations.
 One of the most difficult operations to implement efficiently in an RDBMS and one reason why
RDBMSs have intrinsic performance problems.
Join Operations
 Various forms of join operation

 Theta join
 Equijoin (a particular type of Theta join)
 Natural join
 Outer join
 Semijoin
Theta join (θ-join)
 R FS
 Defines a relation that contains tuples satisfying the predicate F from the Cartesian product
of R and S.
 The predicate F is of the form R.ai θ S.bi where θ may be one of the comparison operators (,
≥, =, ≠).
 Can rewrite Theta join using basic Selection and Cartesian product operations.
R FS = σF(R Χ S)
 Degree of a Theta join is sum of degrees of the operand relations R and S. If predicate F contains
only equality (=), the term Equijoin is used.
Natural join
 RS
 An Equijoin of the two relations R and S over all common attributes x.
 One occurrence of each common attribute is eliminated from the result.
Outer join
 To display rows in the result that do not have matching values in the join column, use Outer join.
Left Outer join
 RS
 (Left) outer join is join in which tuples from R that do not have matching values in common
columns of S are also included in result relation.
Semijoin
 RFS
 Defines a relation that contains the tuples of R that participate in the join of R with S.
 Can rewrite Semijoin using Projection and Join:
R F S = ΠA(R F S)
where A is the set of attributes for R

Relational Algebra: Lecture Outline

Uploaded by

Copyright:

Available Formats

Relational Algebra: Lecture Outline

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: Lecture Outline

Uploaded by

Copyright:

Available Formats

Relational Algebra

 Relational Query Languages

Relational Query Languages

 Structured Query Language (SQL)

SQL and Relational Algebra

 In SQL we write WHAT data we want to retrieve from the database

Relational Algebra: a language within DBMS

 Five basic operations:

Relational Algebra Operations

Selection (or Restriction)

 Join is a derivative of Cartesian product.

 Various forms of join operation

Theta join (θ-join)

Left Outer join

where A is the set of attributes for R

You might also like