\(\newcommand{\AntiJoin}{ \ \bar{\Join} \ } \)
Operations of traditional RA:
Binary
Unary
Extended RA
$R_1 \cup R_2$
SQL:
SELECT * FROM R1 UNION SELECT * FROM R2
Example:
$\begin{array}{ c | c }
A & B \\ \hline \hline a_1 & b_1 \\ a_2 & b_1 \\
\end{array} \cup \begin{array}{ c | c }
A & B \\ \hline \hline a_1 & b_1 \\ a_3 & b_3 \\
\end{array} = \begin{array}{ c | c }
A & B \\ \hline \hline a_1 & b_1 \\ a_2 & b_1 \\ a_3 & b_3 \\
\end{array} $
$R_1 - R_2$
SELECT * FROM R1 EXCEPT SELECT * FROM R2
Example:
A & B \\ \hline \hline a_1 & b_1 \\ a_2 & b_1 \\
\end{array} \cup \begin{array}{ c | c }
A & B \\ \hline \hline a_1 & b_1 \\ a_3 & b_3 \\
\end{array} = \begin{array}{ c | c }
A & B \\ \hline \hline a_2 & b_1 \\
\end{array} $
$R_1 \cap R_2$
SQL
SELECT * FROM R1 INTERSECT SELECT * FROM R2
Example
$\begin{array}{ c | c }
A & B \\ \hline \hline a_1 & b_1 \\ a_2 & b_1 \\
\end{array} \cup \begin{array}{ c | c }
A & B \\ \hline \hline a_1 & b_1 \\ a_3 & b_3 \\
\end{array} = \begin{array}{ c | c }
A & B \\ \hline \hline a_1 & b_1 \\
\end{array} $
Can be expressed via Union and Difference
A binary operator
in SQL:
SELECT ... FROM R WHERE {condition}
For example
\begin{array}{ c | c }
A & B \\ \hline \hline 1 & 2 \\ 3 & 4 \\
\end{array} \right) = \begin{array}{ c | c }
A & B \\ \hline \hline 3 & 4 \\
\end{array} $
A condition can be anything
$\pi_{A_1, ..., A_n}(R)$
A binary operator
SQL:
SELECT A1, ..., An FROM R
example
Note that for set-based projection there are no duplicated in the output:
\begin{array}{ c | c | c | c}
A & B & C & D \\ \hline \hline 1 & 2 & 3 & 4 \\ 1 & 2 & 3 & 5 \\ 3 & 4 & 5 & 6 \\ 5 & 6 & 3 & 4 \\
\end{array} \right) = \begin{array}{ c | c}
A & C \\ \hline \hline 1 & 3 \\ 3 & 5 \\ 5 & 3 \\
\end{array}$
Sometimes also called "Cross-Product"
$R_1 \times R_2$
SQL
SELECT * FROM R1, R2
Example:
A & B \\ \hline \hline 1 & 2 \\ 3 & 4 \\
\end{array} \times \begin{array}{ c | c}
C & D \\ \hline \hline 2 & 6 \\ 3 & 7 \\ 4 & 9 \\
\end{array} = \begin{array}{ c | c | c | c}
A & B & C & D \\ \hline \hline 1 & 2 & 2 & 6 \\ 1 & 2 & 3 & 7 \\ 1 & 2 & 4 & 9 \\ 3 & 4 & 2 & 6 \\ 3 & 4 & 3 & 7 \\ 3 & 4 & 4 & 9 \\
\end{array} $
Or "Equi-Join"
$R \Join S$
Example:
A & B \\ \hline \hline 1 & 2 \\ 3 & 4 \\
\end{array} \Join \begin{array}{ c | c}
B & D \\ \hline \hline 2 & 6 \\ 3 & 7 \\ 4 & 9 \\
\end{array} = \begin{array}{ c | c | c}
A & B & D \\ \hline \hline 1 & 2 & 6 \\ 3 & 4 & 9 \\
\end{array} $
A & B \\ \hline \hline 1 & 2 \\ 3 & 4 \\
\end{array} \Join \begin{array}{ c | c}
C & D \\ \hline \hline 2 & 6 \\ 3 & 7 \\ 4 & 9 \\
\end{array} = \begin{array}{ c | c | c | c}
A & B & C & D \\ \hline \hline 1 & 2 & 2 & 6 \\ 1 & 2 & 3 & 7 \\ 1 & 2 & 4 & 9 \\ 3 & 4 & 2 & 6 \\ 3 & 4 & 3 & 7 \\ 3 & 4 & 4 & 9 \\
\end{array} $
$R_1 \Join_{\theta} R_2$
Examples
A & B \\ \hline \hline 1 & 2 \\ 3 & 4 \\
\end{array} \Join_{B = C} \begin{array}{ c | c}
C & D \\ \hline \hline 2 & 6 \\ 3 & 7 \\ 4 & 9 \\
\end{array} = \begin{array}{ c | c | c | c}
A & B & C & D \\ \hline \hline 1 & 2 & 2 & 6 \\ 3 & 4 & 4 & 9 \\
\end{array} $
$R \AntiJoin S$
Difference between $R \AntiJoin S$ and $R - S$ (Difference):
Example
A & B \\ \hline \hline 1 & 2 \\ 3 & 4 \\ 5 & 6 \\ 6 & 7 \\
\end{array} \AntiJoin \begin{array}{ c | c | c }
B & C & D \\ \hline \hline 2 & 5 & 6 \\ 6 & 4 & 2 \\
\end{array} = \begin{array}{ c | c }
A & B \\ \hline \hline 3 & 4 \\ 6 & 7 \\
\end{array} $
$\rho_{\text{prefix}}(R)$
SQL
SELECT * FROM Relation R
Example
\begin{array}{ c | c}
A & B \\ \hline \hline 1 & 2 \\ 3 & 4 \\
\end{array} \right) = \begin{array}{ c | c}
T.A & T.B \\ \hline \hline 1 & 2 \\ 3 & 4 \\
\end{array}$
find all hospitals within 5 ms of a school
SELECT DISTINCT h.name FROM Hospital h, School s WHERE distance(h.location, s.location) < 5
$\pi_\text{name} ( \rho_{\text{h}}(\text{Hospital}) \Join_{\text{h.location = s.location}} \rho_{\text{s}}(\text{School}) )$
...
$R \ltimes S = \pi_{R.*}(R \Join S)$
Duplicates
RA has two semantics:
All set-based operations are straightforwardly extended to bags
Intersection
Same idea with Difference and Union
$\gamma_{\text{grouping_attribute}, \ \text{func}(A) \ \to \ \text{name}}(R)$
Example
\begin{array}{ c | c | c }
A & B & C \\ \hline \hline 1 & 2 & a \\ 1 & 3 & b \\ 2 & 3 & c \\ 2 & 4 & a \\ 2 & 5 & d \\
\end{array} \right) = \begin{array}{ c | c | c }
A & D \\ \hline \hline 1 & 2 \\ 2 & 3 \\
\end{array} $
In Extended RA we also can allow renaming in projection
\begin{array}{ c | c | c | c}
A & B & C & D \\ \hline \hline 1 & 2 & 2 & 6 \\ 1 & 2 & 2 & 7 \\ 1 & 2 & 2 & 9 \\ 3 & 4 & 3 & 6 \\ 3 & 4 & 3 & 7 \\ 3 & 4 & 3 & 9 \\
\end{array} \right) = \begin{array}{ c | c}
A & D \\ \hline \hline 1 & 2 \\ 1 & 2 \\ 1 & 2 \\ 3 & 3 \\ 3 & 3 \\ 3 & 3 \\
\end{array} $ (bag semantics)
\begin{array}{ c | c | c | c}
A & B & C & D \\ \hline \hline 1 & 2 & 2 & 6 \\ 1 & 2 & 2 & 7 \\ 1 & 2 & 2 & 9 \\ 3 & 4 & 3 & 6 \\ 3 & 4 & 3 & 7 \\ 3 & 4 & 3 & 9 \\
\end{array} \right) = \begin{array}{ c | c}
A & D \\ \hline \hline 1 & 2 \\ 3 & 3 \\
\end{array} $ (set semantics)