Relational Algebra is a procedural query language used in database management systems (DBMS) to retrieve data from relational databases. It is a set of mathematical operators that manipulate the data stored in the database. In this article, we will explore the key concepts of Relational Algebra and how it can be used to retrieve data from a database.
Selection Operation:
The Selection Operation is used to select a subset of rows from a relation that meets a specified condition. It is represented by the sigma symbol (σ). For example, if we want to retrieve all students from the STUDENT relation who are above 25 years old, we can use the selection operation as follows:
σAGE > 25 (STUDENT)
Projection Operation:
The Projection Operation is used to select a subset of columns from a relation. It is represented by the pi symbol (π). For example, if we want to retrieve only the NAME and AGE columns from the STUDENT relation, we can use the projection operation as follows:
πNAME, AGE (STUDENT)
Union Operation:
The Union Operation is used to combine two or more relations and remove any duplicate tuples. It is represented by the symbol ∪. For example, if we have two relations STUDENT1 and STUDENT2, and we want to combine them to retrieve all students’ information, we can use the union operation as follows:
STUDENT1 ∪ STUDENT2
Intersection Operation:
The Intersection Operation is used to retrieve the common tuples between two or more relations. It is represented by the symbol ∩. For example, if we have two relations STUDENT1 and STUDENT2 and want to retrieve the students’ information common in both relations, we can use the intersection operation as follows:
STUDENT1 ∩ STUDENT2
Difference Operation:
The Difference Operation is used to retrieve tuples from one relation that are not present in another relation. It is represented by the symbol –. For example, if we have two relations STUDENT1 and STUDENT2 and want to retrieve the students’ information present in STUDENT1 but not in STUDENT2, we can use the difference operation as follows:
STUDENT1 – STUDENT2
Product Operation:
The Product Operation is used to combine two or more relations and retrieve all possible combinations of tuples. It is represented by the symbol ×. For example, if we have two relations STUDENT and COURSE, and we want to retrieve all possible combinations of students and courses, we can use the product operation as follows:
STUDENT × COURSE
Here are some additional concepts and operations of Relational Algebra that are worth exploring:
Join Operation:
The Join Operation is used to combine two or more relations based on a common attribute or set of attributes. It is represented by the symbol ⋈. For example, if we have two relations STUDENT and COURSE, and we want to retrieve the information of students who have taken the course, we can use the join operation as follows:
STUDENT ⋈ COURSE
Here, we are joining the STUDENT relation and the COURSE relation based on the common attribute COURSE_ID. The resulting relation will contain information about students who have taken the course.
Division Operation:
The Division Operation is used to retrieve a subset of tuples from one relation that can be combined with another relation to produce the entire second relation. It is represented by the symbol ÷. For example, if we have two relations COURSE and COURSE_STUDENT, and we want to retrieve the courses that are taken by all students, we can use the division operation as follows:
COURSE ÷ COURSE_STUDENT
Here, we are dividing the COURSE relation by the COURSE_STUDENT relation. The resulting relation will contain the courses that are taken by all students in the COURSE_STUDENT relation.
Aggregation Operation:
The Aggregation Operation is used to calculate summary values on a relation, such as average, sum, count, and maximum/minimum. It is represented by the symbol Γ. For example, if we want to calculate the average age of students in the STUDENT relation, we can use the aggregation operation as follows:
ΓAVG(AGE)(STUDENT)
Here, we are using the AVG function to calculate the average age of students in the STUDENT relation.
Set Operations:
Relational Algebra also supports set operations such as the complement operation and the outer join operation. The complement operation is used to retrieve tuples from one relation that are not present in another relation, and it is represented by the symbol -. The outer join operation is used to retrieve tuples from one relation and all matching tuples from another relation, and it is represented by the symbols ⟕ and ⟖.
