0.4 Relational algebra  (Page 2/3)

• Note: As we can notice, the CARTESIAN PRODUCT operation by itself is not a useful querying mechanism since the result size is large. However, it is an extremely important operation of relational algebra since it is the basic mechanism for combining information across relations. We will discuss about this topic in more detail in Query Processing lecture.

Other relational algebra operations

1. SELECT Operation

The SELECT operation is an unary operation. It means the input of this operation is only one relation and it output is also a relation.

The SELECT operation returns a subset of the tuples from a relation that satisfies a selection condition. The SELECT operation can be viewed as a horizontal filter of the relation. It partitions the input relation into two sets of tuples: those tuples that satisfies the condition are select, those do not satisfy the condition are discarded.

• Notation: $\begin{array}{}{\sigma }_{\text{selection}-\text{condition}>(r)}\\ \end{array}$

• ${\sigma }_F(r)=\{t\mid t\in r\wedge F(t)\}$ where r(R) and F is a boolean expression on attributes in R

The selection condition is made up of a number of clauses of the form

• <attribute name><comparison op><constant value>OR
• <attribute name 1><comparison op><attribute name 2>

In the clause, the comparison operations could be one of the following: ≤, ≥, ≠, =,>,<. Clauses are connected by Boolean operators : and, or , not

• Result size
• Result schema: R
• Producing the result of the SELECT operation
  • Selection condition F is evaluate for each tuple in r, with the attribute variables in F set to their values in the tuples
  • Any tuple t that F(t) = true is placed in the result set
  • Other tuples are not include in the result.
• Example: Retrieve the Id, Name, Suburb of students who live in Bundoora

• Example: Retrieve the Id, Name, Suburd of student who’s name is Mary or students who live in Bundoora

1. PROJECT Operation

The PROJECT operation is another unary operation. This operation returns a set of tuples containing a subset of the attributes in the original relation. Thus, as we state that the SELECT operation selects some rows and discards the others. The PROJECT operation, on the other hand, selects some columns of the relation and discards the other column. The PROJECT operation can be viewed as the vertical filter of the relation.

• Notation: ${\pi }_{\text{attribute}-\text{list}}(r)$

• ${\pi }_X(r)=\{t[X]\mid t\in r\}$ where r(R)
• Result size:
• Result schema: R’(X)
• Producing the result of PROJECT operation
  • Take each tuple in the original relation, extract the values of the specified attributes
  • Form new tuple from these values and place the new tuple in the result if it is not already there. This steps includes the duplicate removal phase, this makes the result of PROJECT operation a relation

• Example: Retrieve the suburbs that are stored in database

• Retrieve the name of the subjects and department which is responsible for the subject