Relational Algebra in DBMS

Relational Algebra:

Relational Algebra is a procedural query language that is a collection of operations to manipulate relations. It consists of a set of such operations that take one or more relations as input and produce a new relation as well as their result.

Relational Algebra Operations:

Relational Algebra Operations can be divided into two major parts :

  • Relational-Oriented Operations
  • Set-Oriented Operations

Relational-Oriented Operations:

Relational-Oriented Operations can be classified into three major parts :

  • Select (σ)
  • Project (π)
  • Join

1. Select (σ):

The select operation extracts specific tuples from a relation. The select operation to be a filter that keeps only those tuples that satisfy a qualifying condition. Selection is denoted by the lower greek letter sigma (σ).

2. Project (π):

The project operation is a unary operation that extracts attributes (columns) from a relation. If we are interested in only certain specific attributes of a relation, we use the project operation to project the relation over these attributes only. Projection is denoted by the Greek letter pi (π).

3. Join:

The join operator allows to a combination of two relations to form a single new relation. The tuples from the operand relations that participate in the operation and contribute to the result are related. The join operation allows the processing of relationships existing between the operand relations.

Different Types of join:

  • Equi join
  • Theta (θ) join
  • Natural join
  • Outer join

 

(i) Equi-join:

When two tables are joined together using equality of values in one or more columns, then it is called an Equi Join. In equi-join, the comparison operator ‘=’ is only used this symbol. As a result of an equi-join will always have one or more pairs of attributes that have identical values in every tuple.

(ii) Theta (θ) join:

A general join condition is of the form :

AND AND...AND

where each Condition is of the form Ai θ Bj . Here, Ai is an attribute of relation X. Bj is an attribute of relation Y. Here Ai and Bj have the same domain. The Greek letter θ is one of the comparison operators ( =, <, ≤, >, ≥, #). A join operation with such a general join condition is also called a Theta (θ) join.

(iii) Natural join:

In the natural join, the comparison operator is always the equality operator ‘=’ but only the equijoin contains two identical columns from the relation being joined. An equi-join with one of the two identical columns are eliminated which is called a Natural join. In Natural join, It will also give a new table that does not have any duplicate columns.

(iv) Outer join:

An Outer join is a type of equijoin that is used to show all data from one table, even if corresponding data is not found in a second table. Outer join is commonly used with tables having one too many relationships (1: M).

Set-Oriented Operations:

Set-Oriented Operations can be classified into four major parts :

  • Set-union
  • Set-intersection
  • Set-difference
  • Cartesian product

 

1. Set-union:

The result of the union operation is denoted by the symbol ∪ .
Example: X ∪ Y (where X and Y are two compatible relations) which is a relation that includes all tuples that are either in X or in both X and Y. A set union eliminates duplicate tuples.

2. Set-intersection:

The result of intersection operation is denoted by ∩. It is an operation that includes all tuples that are in both X and Y.

3. Set-difference:

The symbol – denotes the Set-difference operation. It allows us to find tuples that are in one relation but are not in another relation.

4. Cartesian product of Cross product:

The symbol x denotes the Cartesian product. It returns a relation on tuples whose schema contains all fields of X followed by all fields of Y.

Tuple Relational Calculus:

The primary construct of the tuple-oriented relational calculus is the tuple calculus expression. A tuple calculus expression is essentially a non procedural definition of some relation in terms of some given set of relations, such an expression can be clearly used to define the result of a query, or the target of an update ,m or a view, etc. The expression of the tuple calculus are constructed from the following elemenis.

1. Tuple variables
2. Conditions
3. Well formed formulas