Relational algebra is query language based on the set theory of mathematics. It is procedural as it defines the sequence in which the query must be operated to retrieve the result. Relational algebra has some basic fundamental operators along with some derived operators.
In this section, we will discuss the definition of relational algebra. We will study each operator along with an example and we will conclude the content by discussing the advantages and limitations of relational algebra.
Content: Relational Algebra
What is Relational Algebra?
Introduction
Relational algebra is a theoretical concept based on the set theory in mathematics. Relational algebra is a formal query language that can be used to retrieve information from the relational database that is designed using the relational data model.
Well, there are two kinds of formal languages for the relational data model that are relational algebra and relational calculus. It is a procedural query language as it provides a sequence of operations in which the query must be evaluated to get the result relation. It specifies how to evaluate the query.
Relational calculus is a declarative query language and it does not provide the sequence of operation in which the query must be evaluated to obtain the result relation. Instead, it only declares what information has to be retrieved. The structured query language (SQL) is based on both relational algebra and relational calculus.
Definition
Relational algebra is a procedural query language that has a basic set of operators that enable users to specify relational algebra expressions. The relational algebra expression works on one or more relations to obtain a result relation.
In relational algebra, both operand and result are the relations. So, we can conclude that the result relation of one relational algebra expression can be provided as an input to another relational algebra operation that allows nested relational algebra expressions.
The operators or the operations in relational algebra can be classified into two categories. The first category includes set operations from set theory in mathematics which includes UNION, INTERSECTION, SET DIFFERENCE, & CARTESIAN PRODUCT. The second category includes the database operations that are SELECT, PROJECT, JOIN.
Relational Algebra Operators
Some operators of relational algebra are unary that is they operate only on one relation and some operators are binary that is they operate on two relations. SELECT, PROJECT and RENAME are the unary operators and UNION, SET DIFFERENCE, CARTESIAN PRODUCT and JOIN are the binary operators.
Let us discuss all operators of relation algebra one by one.
Basic/Fundamental Operators
Select Operator
The select tuple retrieves the set of tuples from the given relation that satisfies the specified condition. The symbolic representation of the select operator is σ. The condition of the select operator appears as a subscript to the σ operator. The relation on which the σ operator has to work is specified in the parenthesis after the σ operator.
Consider that we have an Instructor relation and we want to display only those tuples where the salary of the instructor is greater than 61000. 
The relational algebra expression for the same would be:
σ salary>61000 (instructor)
Project Operator
The project operator is a unary operator and it returns the relation specified in its argument but by eliminating certain attributes of the relation. The projection operator is represented by the symbol π.
Consider that from the instructor relation above we have to display the list of instructor’s names and their respective departments. The relational algebra expression for the same is:
Πname, dept_name (instructor);
Rename Operator
The rename operator is a unary operator and works on a single relation. It returns the same relation provided to it in the argument but with a different name which is also specified in the expression. The symbol of rename operator is represented by ρ.
The rename operator is also used to rename the attributes of the relation provided in its argument.
Now suppose you want to rename the relation instructor with the new name tutor. Then the expression for the same would be:
ρ tutor(instructor);
Remember that you won’t be able to see that the name of instructor relation has been changed to tutor as the result of relational algebraic expression does not have names.
In case, you even want to rename the attribute dept_name to the department the expression would be;
ρtutor(instr_id, name, department, salary)(instructor);
Union Operator
Using the union operator, you can combine the tuples of two relations eliminating the duplicates. But before we apply union operator on two relation we must ensure that the two relations are union compatible.
Being union compatible means that the two relations must have the same number of attributes and they must have the same domain.
For example, we have two relations instructor_1 and instructor_2. You can notice that they have some common tuples also.
So, the union of relation instructor_1 and instructor_2 would be:
instructor_1 U instructor_2
Set Difference Operator
The set difference is a binary operator and works on two relations say R1 and R2. The symbol we use for the set difference operator is – which is similar to minus. If we are implementing a set difference operation on two relations R1 and R2 i.e. R1 – R2 then the result relation would have tuples from the relation R1 that are not present in R2.
If we implement set difference on relation instructor_1 and instructor_2 i.e. instructor_1 – instructor_2 the relation result will be:
Cartesian Product
The cartesian product is a binary operator as it works on two relations. The cartesian product combines the tuples from both the relations given in its argument. The symbol of the cartesian product is X.
Consider, that we have two relations instructor and department relation.
If we apply the cartesian product on the instructor X department relation the result would be:
Derived Operators
Set Intersection Operator
Set interaction operator is an additional relational algebra operator. The set operator is a binary operator and it works on two provided relations. The set operator returns the tuples that are present in both the specified relations. The symbol of set intersection operators is Ո.
If we apply set intersection on relation instructor_1 and instructor_2 i.e. instructor_1 Ո instructor_2 then the result would be:
The natural join integrates two relations from the database based on the common attributes of both the relations. There are several kinds of joins equijoin, theta joins, semi-join and left outer join, right outer join and full outer join.
I have explained the concept of all these joins in my previous content join operations in SQL. Please click on this URL https://binaryterms.com/join-operations-in-sql.html to get a better understanding of joins and their types.
Division Operator
The division operator is a binary operator that works on two relations. The division operator returns the tuples of the first relation based on the information present in the second table. Consider we have two relations instructor relation and department relation.
Now if we require the name of the instructors who teach in all the departments. Then we will apply division operation on the two relations i.e. instructor ÷ department.
Advantages and Limitations
Advantages
- Relational algebra is based on the set theory which is a mathematical concept due to which it has a scope of development.
- Like mathematics there can be many expressions for the same operation, in a similar way if there are two relational algebraic expressions for the same operation then the query optimizer will switch to the most efficient query.
- It is a high-level query language.
Limitations
- Relational algebra cannot perform arithmetic operations.
- It is unable to do aggregation operations even it cannot compute transitive closure.
- It cannot modify the data present in the database.
So, this is all about relational algebra, its operators. We have also discussed examples of each operator.
Leave a Reply