Introduction to Relational Algebra in DBMS
Relational Algebra in DBMS is mainly used in the procedural query language. In which we take relational input and produce a relation output. In short, it is used to collect the relation as the input and try to provide the result of relational occurrence as the output. The result that we get from Relational Algebra which will be a new relationship which is formed by using one or more type of relational input. Generally, it makes us of the operator to perform the query and this operator basically is binary or unary. We have different type of operations of relation algebra few of them are select, union, project etc. In the coming section of the tutorial, we can discuss more on the internal working and implementation of the Relational Algebra in DBMS for better understanding.
Operators in Relational Algebra
As we have already discussed why we used them, it is basically used in the procedural query language, and we have several types of operations available, Relational Algebra, in this section we will discuss majorly on the types we have with their subtype define. So let’s take a closer look at various types available, see below;
Basically, it is divided into three groups, which will be further divide into several sub group, and they are mentioned below;
1) Binary Relational Operations
a) Join
b) Division
2) Unary Relational Operations
a) Project
b) Select
c) Rename
3) Set Theory Relational Algebra Operations
a) CARTESIAN PRODUCT
b) INTERSECTION
c) DIFFERENCE
d) UNION
Above are the different type of operation available in each type of Relational Algebra, each we can use to perform the action and get the desired output based on the input, and able to create or form new relation based on the multiple relation input.
Relational algebra in dbms with examples
In this section, we will go to see the internal working of the operations available in the Relational Algebra, with a simple example. It will help us to understand them in better way for usages in our application if any. So let’s get started with it for better clarity with sample examples see below;
1) Union operation (υ) :
Union operation is represented by the ‘(υ)’ symbol, it is used to eliminate the duplicate elements or we can say record from the two tables. Let’s say we have two tables ‘T1’ and ‘T2’ so it will remove all the duplicate records from both the tables and print put the distinct one.
Example:
Table 1 :
| Column 1 | Column 2 |
| 100 | 200 |
| 100 | 300 |
Table 2:
| Column 1 | Column 2 |
| 100 | 200 |
| 100 | 400 |
Output : Table1 ∪ Table 2
| Column 1 | Column 2 |
| 100 | 200 |
| 100 | 300 |
| 100 | 400 |
2) Set Difference (-):
This is used to get the records which are in the first table but not in the second. Which means all the remaining record from the first table which are not present in the second table. Consider the above table and see below output for difference;
Output Table 1 – Table 2:
| Column 1 | Column 2 |
| 100 | 300 |
3) Intersection:
Now we have Intersection which is used to represent all the records which are present in both the tables. It is used to define by this symbol opposite of union, that is ‘∩’. Consider the same table 1 and table 2 for the input and see the result after applying Intersection on them, see below;
Output Table 1 ∩ Table 2:
| Column 1 | Column 2 |
| 100 | 200 |
4) NATURAL JOIN (⋈) :
It is used to join tables, a=and can only be applied on two tables if they have any common attribute in them. Consider below tables which contain common attributes and now we will apply then NATURAL Join on them to provide the result, see below;
table 1:
| Id | value |
| 1 | 100 |
| 2 | 200 |
| Id | price |
| 1 | 500 |
| 2 | 600 |
Output:
| ID | value | price |
| 1 | 100 | 500 |
| 2 | 200 | 600 |
5) EQUI join:
This join is used to join two tables based on the equivalence condition. Consider the above two tables one see the output below;
Output:
| ID | value | price |
| 1 | 100 | 500 |
6) Left Outer Join:
Left outer join will join two table and produce the resultant table with all the records matching in both table and all record from left table that is first table. Consider the below tables and try to understand the internal working for this see below;
Example: table 1:
| Id | value |
| 1 | 10 |
| 2 | 20 |
Table 2:
| Id | price |
| 1 | 30 |
| 4 | 40 |
Output:
| ID | value | price |
| 1 | 10 | 30 |
| 2 | 20 | – |
7) Right Outer Join:
Right outer join is used to join the two tables and produce the result table for us. In this it will contain all the records from the right table that is second table and matched records from the first table. Consider the above two tables and let us try to understand the output out of that for better clarity of its working see below;
Output:
| ID | value | price |
| 1 | 10 | 30 |
| 4 | 40 | – |
8) SELECT (σ):
This operation is used to select the record from the table bases on the selection condition we passed. Symbol used to indicate this operation is ‘(σ)’ that is sigma. We can simply write our expression using the Select operation, see the below;
Example:
σ City = “Mumbai” (Information)
In the above example, we are trying to select the City from Information where the record match with Mumbai. This is the select condition here to fetch the data.
Types of Relational operation
In this section we will try to understand, the types of the Relational operations, that we have already discussed in the above section of the tutorial. Let’s try to understand them better with the following mentioned points below;
1) Project operation
2) Select operation
3) union operation
4) Set difference
5) Cartesian product
6) Rename Operation
7) Set interaction
Conclusion
By the use of this we can easily perform operation on the relational input and try to get the output, we have already seen different type of Relational Algebra which can be used in procedural query language. It is easy to use, readable, understandable and maintainable by the developers as well.
Recommended Articles
This is a guide to Relational Algebra in DBMS. Here we discuss the Introduction, different types of Operators in Relational Algebra for better understanding. You may also have a look at the following articles to learn more –
