Selection in Relational Algebra

Selection in Relational Algebra

Selection (σ) is one of the most fundamental operations in relational algebra. It is used to retrieve a subset of rows (tuples) from a relation (table) based on a specified condition or predicate. The result of the selection operation is a new relation that contains only those rows that satisfy the condition. Selection is analogous to the WHERE clause in SQL.

Definition of Selection:

• Notation: σ_condition(R)
  • σ: The symbol for the selection operation.
  • condition: The condition (predicate) that specifies which rows should be selected. It is typically a logical expression, such as Age > 21, Name = 'John', etc.
  • R: The relation (table) on which the selection operation is applied.

Characteristics of Selection:

• The selection operation only affects the rows (tuples) of the relation, not the columns (attributes).
• It can apply conditions such as comparisons (=, <, >, etc.), logical operations (AND, OR), and other predicates (e.g., string matching, range checks).
• Selection operations can be combined with other operations like projection, join, and union to perform complex queries.

Result of Selection:

• The result of a selection operation is a subset of the original relation. It includes only the tuples that satisfy the condition, and the schema (structure) of the resulting relation is the same as that of the original relation.

Example:

Consider the following relation Students:

StudentID	Name	Age	Major
1	Alice	20	CS
2	Bob	22	Math
3	Charlie	21	Physics
4	Dave	23	CS
5	Eve	19	Math

Now, suppose we want to select all students whose Age is greater than 21.

Selection Operation:

σ(Age > 21)(Students)

This operation applies the condition Age > 21 on the Students table, and the result will be:

StudentID	Name	Age	Major
2	Bob	22	Math
3	Charlie	21	Physics
4	Dave	23	CS

In this case:

• Only the students who have an age greater than 21 (Bob, Charlie, and Dave) are selected.
• The relation returned has the same schema as the original (StudentID, Name, Age, Major), but only includes the rows that satisfy the condition.

Selection with Multiple Conditions:

The condition in the selection operation can also involve multiple predicates. These conditions are combined using logical operators like AND, OR, and NOT.

Example: Select students who are majoring in "CS" and are older than 21:

σ(Major = 'CS' AND Age > 21)(Students)

This operation will return the students who have both Major = 'CS' and Age > 21. The result will be:

StudentID	Name	Age	Major
4	Dave	23	CS

In this case, only Dave satisfies both conditions.

Selection with String Matching:

In relational algebra, selection can also include string matching using operations such as equality (=) or pattern matching.

Example: Select students whose name starts with "A":

σ(Name LIKE 'A%')(Students)

This operation will select students whose Name begins with the letter "A". The result would be:

StudentID	Name	Age	Major
1	Alice	20	CS

In this case, only Alice satisfies the condition Name LIKE 'A%'.

Selection and Logical Operators:

Logical operators can be used in selection conditions to combine multiple conditions.

• AND: Both conditions must be true.
• OR: At least one of the conditions must be true.
• NOT: The condition must not be true.

Example: Select students who are either younger than 21 or majoring in "Math":

σ(Age < 21 OR Major = 'Math')(Students)

This operation will return students who satisfy either Age < 21 or Major = 'Math'. The result will be:

StudentID	Name	Age	Major
1	Alice	20	CS
2	Bob	22	Math
5	Eve	19	Math

In this case:

• Alice is selected because her age is less than 21.
• Bob and Eve are selected because their major is "Math".

---

Summary of Selection (σ) in Relational Algebra

• Purpose: The selection operation is used to retrieve rows from a relation that satisfy a given condition.
• Syntax: σ_condition(R) where condition is a predicate that must hold for the rows.
• Effect: It filters rows based on the condition, producing a new relation with the same schema but fewer tuples.
• Examples:
  • σ(Age > 21)(Students) selects students older than 21.
  • σ(Major = 'CS' AND Age > 21)(Students) selects students who are both in CS and older than 21.

Selection is often used as the starting point for queries in relational algebra, as it allows you to filter data before applying other operations like projection, join, and union.

Key Points:

• Selection only affects rows, not columns.
• It is a filtering operation.
• It is equivalent to the WHERE clause in SQL.
• Selection can handle multiple conditions using logical operators like AND, OR, and NOT.