Unary Relational Operations

SELECT, PROJECT, and RENAME

Unary relational operations are operations that operate on a single relation and produce a new relation as their result.

The two most fundamental unary operations in relational algebra are:

1. SELECT (σ) – filters rows (tuples)

2. PROJECT (π) – filters columns (attributes)

An additional supporting operation, RENAME (ρ), is used to rename relations or attributes, especially when combining multiple operations.

---

1. The SELECT Operation (σ)

Purpose of SELECT

The SELECT operation is used to choose a subset of tuples from a relation that satisfy a specified condition.

• It filters rows, not columns

• The structure (attributes) of the relation remains unchanged

• Only tuples that satisfy the condition appear in the result

📌 SELECT can be viewed as:

• A filter

• A restriction

• A horizontal partition of a relation

---

Notation

$$\sigma_{<selection\ condition>}(R)$$

Where:

• σ (sigma) denotes SELECT

• R is a relation (or relational algebra expression)

• Selection condition is a Boolean expression

---

Example

Select employees who work in department 4:

$$\sigma_{Dno = 4}(EMPLOYEE)$$

Select employees whose salary is greater than 30,000:

$$\sigma_{Salary > 30000}(EMPLOYEE)$$

---

Selection Conditions

A selection condition consists of one or more clauses of the form:

• attribute comparison_operator constant

• attribute comparison_operator attribute

Valid comparison operators include:

$$\{ =, <, \le, >, \ge, \neq \}$$

Clauses can be combined using Boolean operators:

• AND

• OR

• NOT

---

Complex Condition Example

Retrieve employees who:

• Work in department 4 and earn more than 25,000
  OR

• Work in department 5 and earn more than 30,000

$$\sigma_{(Dno=4 \ AND \ Salary>25000)\ OR\ (Dno=5 \ AND \ Salary>30000)}(EMPLOYEE)$$

---

How SELECT Works (Tuple-by-Tuple)

• The condition is evaluated independently for each tuple

• Attribute values in the tuple are substituted into the condition

• If the condition evaluates to TRUE, the tuple is selected

---

Properties of SELECT

• Unary operation (applies to one relation)

• Does not change attributes

• Number of tuples in result ≤ number of tuples in original relation

  $$|\sigma_c(R)| \le |R|$$
  

• The fraction of selected tuples is called selectivity

• Commutative:

  $$\sigma_{c1}(\sigma_{c2}(R)) = \sigma_{c2}(\sigma_{c1}(R))$$
  

• Multiple SELECTs can be combined into a single SELECT using AND

---

SELECT vs SQL

Relational Algebra:

$$\sigma_{Dno=4 \ AND \ Salary>25000}(EMPLOYEE)$$

Equivalent SQL:

SELECT *
FROM EMPLOYEE
WHERE Dno = 4 AND Salary > 25000;

⚠️ Important:
Relational algebra SELECT ≠ SQL SELECT clause

• Algebra SELECT → selects rows

• SQL SELECT → selects columns

---

2. The PROJECT Operation (π)

Purpose of PROJECT

The PROJECT operation selects specific attributes (columns) from a relation.

• It filters columns, not rows

• It eliminates duplicate tuples

• It produces a vertical partition of the relation

---

Notation

$$\pi_{<attribute\ list>}(R)$$

Where:

• π (pi) denotes PROJECT

• Attribute list specifies required attributes

---

Example

Retrieve employee last name, first name, and salary:

$$\pi_{Lname, Fname, Salary}(EMPLOYEE)$$

---

Duplicate Elimination

If projected attributes do not include a key, duplicate tuples may arise.
PROJECT automatically removes duplicates to maintain the set property of relations.

Example:

$$\pi_{Sex, Salary}(EMPLOYEE)$$

If multiple employees share the same sex and salary, only one tuple appears in the result.

---

Properties of PROJECT

• Unary operation

• Reduces number of attributes

• Number of tuples in result ≤ number of tuples in original relation

• Result contains distinct tuples only

• If projection includes a key, no tuples are lost

• Not commutative

• Cascaded projections simplify to a single projection:

$$\pi_{L1}(\pi_{L2}(R)) = \pi_{L1}(R) \quad \text{if } L1 \subseteq L2$$

---

PROJECT vs SQL

Relational Algebra:

$$\pi_{Sex, Salary}(EMPLOYEE)$$

Equivalent SQL:

SELECT DISTINCT Sex, Salary
FROM EMPLOYEE;

Without DISTINCT, SQL allows duplicates—this is not allowed in formal relational algebra.

---

3. Sequences of Operations

Most database queries require multiple relational algebra operations.

Inline Expression

Retrieve first name, last name, and salary of employees in department 5:

$$\pi_{Fname, Lname, Salary}(\sigma_{Dno=5}(EMPLOYEE))$$

---

Using Intermediate Relations

DEP5_EMPS ← σDno=5(EMPLOYEE)
RESULT ← πFname, Lname, Salary(DEP5_EMPS)

This approach:

• Improves readability

• Helps in understanding complex queries

• Allows attribute renaming

---

4. The RENAME Operation (ρ)

Purpose of RENAME

The RENAME operation is used to:

• Rename a relation

• Rename its attributes

• Or both

This is especially useful in JOINs, UNIONs, and self-referencing queries.

---

Notation

$$\rho_S(B_1, B_2, ..., B_n)(R)$$

Other forms:

• Rename relation only:

$$\rho_S(R)$$

• Rename attributes only:

• $${\rho }_{(B_1,B_2,\mathrm{.}\mathrm{.}\mathrm{.},B_n)}(R)$$

  
  
  

$$\rho_{(B_1, B_2, ..., B_n)}(R)$$

---

Example

TEMP ← σDno=5(EMPLOYEE)
R(First_name, Last_name, Salary) ← πFname, Lname, Salary(TEMP)

---

RENAME vs SQL

In SQL, renaming is done using aliases:

SELECT E.Fname AS First_name,
       E.Lname AS Last_name,
       E.Salary AS Salary
FROM EMPLOYEE AS E
WHERE E.Dno = 5;

---

Summary of Unary Operations

Operation	Symbol	Purpose	Affects
SELECT	σ	Filters tuples	Rows
PROJECT	π	Selects attributes	Columns
RENAME	ρ	Renames relations/attributes	Names only