Unary Relational Operations

SELECT Statement ($\sigma$)

SELECT (Relational Algebra)

The SELECT operation, $\sigma$, is used to select a subset of tuples from a relation based on a selection condition. The condition acts as a filter and only keeps those tuples that satisfy the qualifying condition.

$${\sigma }_{<\text{selection condition}>}(R)$$

Where $\sigma$ denotes the SELECT operator. Where $R$ denotes the relation.

Example:

• Select the EMPLOYEE tuples whose department number if 4: ${\sigma }_{DNO=4}(EMPLOYEE)$
• Select the EMPLOYEE tuples whose salary is greater than £30,000: ${\sigma }_{SALARY>30,000}(EMPLOYEE)$

Properties:

• SELECT produces a relation $S$ that has the same schema as $R$.
• SELECT is commutative: ${\sigma }_{<1>}({\sigma }_{<2>(R)})={\sigma }_{<2>}({\sigma }_{<1>(R)})$.
• May be applied in any order.
• We can replace a cascade with a single conjunction. $${\sigma }_{<1>}({\sigma }_{<2>(R)})={\sigma }_{<1>\text{ AND }<2>}(R)$$

Link to original

PROJECT Statement ($\pi$)

PROJECT (Relational Algebra)

The PROJECT operation, $\pi$, is used to keep certain columns (attributes) from a relation and discard all others. PROJECT uses vertical partitioning, the list of specified columns is kept in each tuple, other attributes in each tuple are discarded. Duplicate rows are removed since relations are sets.

$${\pi }_{<\text{attribute list}>}(R)$$

Where $\pi$ denotes the PROJECT operator. Where $<\text{attribute list}>$ denotes the desired list of attributes from $R$. Where $R$ denotes the relation.

Example:

• Select the employee’s first name, last name, and salary: ${\pi }_{\text{LNAME, FNAME, SALARY}}(EMPLOYEE)$

Properties:

• The number of tuples in the result of a projection ${\pi }_{<\text{list}>}(R)$ is always less or equal to the number of tuples in $R$.

Link to original

RENAME Statement ($\rho$)

RENAME (Relational Algebra)

The RENAME operation, $\rho$, is used to rename attributes of a relation or the relation name or both. It is useful when a query requires multiple operations and is necessary in some cases. There are three possible cases:

• Change relation name to $S$: ${\rho }_S(R)$
• Change column (attribute) names to $B_1,B_2,\dots ,B_n$: ${\rho }_{(B_1,B_2,\dots ,B_n)}(R)$
• Change combined attribute and relation names: ${\rho }_{S(B_1,B_2,\dots ,B_n)}(R)$

Example:

• Apply multiple operations and rename (using $S\leftarrow R$): $TEMP\leftarrow {\sigma }_{DNO=5}(EMPLOYEE)$ $R(\text{First\_name, Last\_name, Salary})\leftarrow {\pi }_{\text{FNAME, LNAME, SALARY}}(TEMP)$

Link to original