GATE CSEIT/Database Relational algebra part 1 ( Selection and projection operator).
GATE CSEIT/Database Relational algebra part 1 ( Selection and projection operator).
Relational algebra is a foundational aspect of database query languages, providing a formal framework for manipulating and retrieving data from relational databases. Two fundamental unary operations in relational algebra are selection and projection.
Selection Operator (σ):
The selection operator filters rows (tuples) from a relation based on a specified predicate. It retrieves only those tuples that satisfy the given condition, effectively performing a horizontal partitioning of the relation. The general form of the selection operation is:
σcondition(R)\sigma_{\text{condition}}(R)
Here, RR represents the relation, and the condition is a predicate applied to each tuple. For example, to select employees with a salary greater than 50,000 from an ‘Employee’ relation:
σsalary>50000(Employee)\sigma_{\text{salary} > 50000}(\text{Employee})
This operation will yield a relation containing only those employees whose salary exceeds 50,000.
Projection Operator (π):
The projection operator extracts specific columns (attributes) from a relation, effectively performing a vertical partitioning. It removes duplicate rows in the result, ensuring that the output is a set of unique tuples. The general form of the projection operation is:
πattribute1,attribute2,…,attributeN(R)\pi_{\text{attribute1}, \text{attribute2}, \ldots, \text{attributeN}}(R)
For example, to retrieve only the names and departments of all employees from an ‘Employee’ relation:
πname,department(Employee)\pi_{\text{name}, \text{department}}(\text{Employee})
This operation will produce a relation containing unique combinations of employee names and their corresponding departments.
Key Differences:
- Selection (σ): Operates on rows, filtering tuples based on a condition.
- Projection (π): Operates on columns, extracting specified attributes and removing duplicates.
Example:
Consider a ‘Student’ relation with attributes (StudentID, Name, Age, Major):
| StudentID | Name | Age | Major |
|---|---|---|---|
| 1 | Alice | 20 | ComputerSci |
| 2 | Bob | 22 | Math |
| 3 | Charlie | 20 | Physics |
| 4 | David | 23 | ComputerSci |
-
Selection: To find students aged 20:
σAge=20(Student)\sigma_{\text{Age} = 20}(\text{Student})
Result:
StudentID Name Age Major 1 Alice 20 ComputerSci 3 Charlie 20 Physics -
Projection: To list all unique majors:
πMajor(Student)\pi_{\text{Major}}(\text{Student})
Result:
Major ComputerSci Math Physics
Understanding these operators is crucial for formulating queries in relational databases and is a significant component of database management systems, especially in the context of GATE Computer Science examinations.
For a visual explanation and further examples of selection and projection operations in relational algebra, you might find the following video helpful: