Relational algebra in DBMS
Relational algebra in DBMS
Relational algebra is a procedural query language used to retrieve the data from a database in a different way. It works on the relational model. It performs various operations such as insert, delete, update, and many other operations in the table. As relational algebra is a procedural language, it knows how to retrieve data and which type of data to be retrieved.
Operations in relational algebra:
We can divide the operations in relational algebrainto two categories:
- Basic Operation
- Derived Operation
Basic Operations:
- Select (σ)
- Project (∏)
- Union (υ)
- Set Difference(-)
- Cartesian product (X)
- Rename (ρ)
Derived Operations:
- Natural Join
- Left, right, full outer join
- Division
- Intersection
Let us discuss all one by one:
Select operator
The select operator selects the specific rows (tuples) and shows them in a result set. The selected rows (tuples) will be displayed as the output. These rows (tuple) are selected according to the specified conditions.
The select operator is denoted by the sigma (σ) sign.
Syntax:
σ CONDITION (table_name)
Let's understand this with an example:
Below is a table named STUDENT. First, we will select a specific row where the STUDENT_NAME is "Ram."
Query:
? STUDENT_NAME=”Ram” (STUDENT)
Output:
It is the same as where clause in SQL.
Project Operator:
This operator is used to fetch the specific column which the user wants. The selection of the column is made according to the given condition. This operation deletes the duplicate rows in the table.
It is denoted by ?(pi).
Syntax:
? CONDITION (table_name)
For example, we have a table STUDENT with three columns, and we are going to fetch only two columns STUDENT_ID and STUDENT_NAME.
Query:
? STUDENT_ID, STUDENT_NAME (STUDENT)
Output:
Union Operator
This operator is used to fetch all rows from the two tables. The ? symbol denotes it. Thus, all the rows (tuple) of the two tables appear once in the union set.
Note: There is no duplicate row present after union operation. If the column is present in both the tables, then only one column will appear containing data of both the tables.
Syntax:
table_name1 ? table_name2
Let’s understand more with the help of an example. We have two tables name STUDENT and BATCH. So we can fetch the data ofthe column “STUDENT_NAME” from both the tables in a single column as below:
Table1:STUDENT
Table2:BATCH
Query:
? STUDENT_NAME (STUDENT) ? ? STUDENT_NAME (BATCH)
Output:
Set Difference
This operation is used to select specific rows. For example, suppose we have two tables (t1 and t2). Now, if we want to select the row which is present in t1, not in t2, this operation can be used. It is denoted by – symbol.
Syntax:
Table_name1 –table_name2
Let’s understand this operation with example taking the same table STUDENT and BATCH present above.
Query:
? STUDENT_NAME (BATCH) - ? STUDENT_NAME (STUDENT)
Output:
Cartesian product (X)
This operation takes place between two tables. For example, let's have two tables, t1 and t2, then the Cartesian product of these two tables is t1Xt2. The product occurs as each row of the first table combines with each row of the second table.
Syntax:
t1 X t2
Let’s take two tables, t2 and t1, for example.
Table t1:
Table t2:
Query:
t1 X t2
Output:
Rename
Rename operation in relational algebra is used to rename the table name or the column name. Rename is denoted by ?. Using rename statement, we can change the table name as well as the column name.
Syntax:
?(new_table_name, old_table_name)
Let's take the above table BATCH in this example. After using this syntax, the new table name will be replaced by the old one. Here we will change the table name to COURSE.
Query:
?(BATCH, COURSE)
The name of the table has been changed from BATCH to COURSE.
Intersection Operation
This operation will select the rows which are present in both tables. It is denoted by ? symbol.
Syntax:
table_name1 ? table_name2
For example: Let ustake table STUDENT and COURSE as an example.
The standard column (with all the data) present in these two tables will be selected and displayed.Here we are going to select STUDENT_ID from both the tables.
Query:
? STUDENT_ID (STUDENT) ? ? STUDENT_ID (COURSE)
Output:
