Relational Algebra in DBMS
Relational Algebra is a procedural query language which is a collection of operations to manipulate relations. Relational Algebra consists of a set of such operations that take one or more relations as input and produce a new relation as their result.
Relational Algebra Operations can be divided into two major parts :
- Relational-Oriented Operations
- Set-Oriented Operations
Relational-Oriented Operations can be classified into three major parts :
- Select (σ)
- Project (π)
- Equi join
- Theta (θ) join
- Natural join
- Outer 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 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 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 relationships existing between the operand relations.
Different Types of 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 equijoin, the comparison operator ‘=’ is only used this symbol. The result of an equijoin 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 :
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 equijoin contains two identical columns from the relation being joined. An equijoin 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 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 can be classified into four major parts :
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 a set union.
2. Set-intersection: The result of intersection operation denoted by ∩. It is an operation that includes 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 of 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 all fields of Y.