Relational Algebra in DBMS

Relational Algebra is a procedural query language which 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 their result.
Relational Algebraic Operations can be divided into two major parts :

  • Relational-Oriented Operations
  • Set-Oriented Operations
    Relational-Oriented Operations can 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 lower greek letter sigma (σ) .

    2. Project (π) : The project operation is a unary operation which 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 greek letter pi (π) .

    3. Join : The join operator allows to combine 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 relationship 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. The 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 :

    <Condition> AND <Condition> AND...AND <Condition>
    

    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 called a Theta (θ) join.

    (iii) Natural join : In the natural join, the comparison operator is always the equality operator ‘=’ but only the equi join contains two identical columns from the relation being joined. An equi join with one of the two identical columns eliminated that 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 equi join 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 to many relationships (1:M).

    Set-Oriented Operations can classified into four major parts :

  • Set-union
  • Set-intersection
  • Set-difference
  • Cartesian product
  • 1. Set-union : The result of 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. Duplicate tuples would be eliminated by using set union.

    2. Set-intersection : The result of intersection operation denoted by. It is an operation that include all tuples that are in both X and Y.

    3. Set-difference : The Set-difference operation is denoted by the symbol – . It allows us to find tuples that are in one relation but are not in another relation.

    4. Cartesian product or Cross product : Cartesian product is denoted by the symbol x . It returns a relation on tuples whose schema contains all fields of X followed by the all fields of Y.