To join a table with itself, we specify different variables to represent the different references to the table
There’s two formal languages for the relational model:
These were developed before the SQL language. SQL is primarily based on concepts from relational calculus and has been extended to incorporate some concepts from relational algebra as well — thus you might see some similarities.
<aside> <img src="/icons/map-pin_gray.svg" alt="/icons/map-pin_gray.svg" width="40px" /> For this chapter, I won’t cover relational calculus.
</aside>
The basic set of operations for the formal relational model is the relational algebra. These operations enable a user to specify basic retrieval requests as relational algebra expressions.
There are five basic operations in relational algebra. These perform most of the data retrieval operations needed.
Figure 8.1 Operations of relational algebra.
These operators can be divided into two categories: unary and binary operators. Selection, projection, and renaming are unary operations — need only one table.
The SELECT operation is used to choose a subset of the tuples from a relation that satisfies a selection condition.
$$ \sigma_{\textrm{<selection condition>}}(R) $$
The PROJECT operation, on the other hand, selects certain columns from the table and discards the other columns.
$$ \pi_{\textrm{<attribute list>}}(R) $$
It implicitly removes duplicate tuples from the result relation, that might result when $\textrm{<attribute list>}$ includes only nonkey attributes of $R$.
The RENAME operation renames a degree $n$ relation $R$ to relation $S$ with attribute names changed to $A_1, A_2,\dots, A_n$.
$$ \rho_{\textrm{S}}(R) \hspace{2cm} \rho_{(A_1,A_2,\dots,A_n)}(R) \hspace{2cm}\rho_{\textrm{S(}A_1,A_2,\dots,A_n)}(R) $$
You can either choose to rename the relation name or the attribute names, or both.
The other three are binary operators — each operation requires two tables.
The CARTESIAN PRODUCT operation provides the basic capability to combine data from several relations.
$$ R \times S $$
The attributes of $R$ precede the attributes of $S$.