The basic operators of algebra relational are unary or binary operators applied to relationships. The application of each operation produces a new relationship as a result. We distinguish the following operators: restriction, projection, join, union, difference, intersection, division.
Definitions of basic operators
(1) Restriction of a relationship:
The restriction is a unary operation that selects a set of lines (n-tuples) of a relation, based on a selection criterion (predicate or logical expression of predicates). The result of a restriction is a relationship with the same schema as the initial relationship.
(2) Projection of a relationship:
Projection is a unary operation that selects a set of columns from a relation. The result of a projection is a relation having as many lines as the initial relation. However, at the end of a projection, the result relationship may contain identical lines called “doubles”. According to theory, a relationship cannot have identical lines, but most DBMS allow, at the programmer's choice, to keep or delete them.
(3) Combination of restriction and projection operators:
The restriction and projection operations can be combined to carry out more elaborate processing on relationships. The frequency of these combinations often leads to the realization of a single operator called selection.
(4) Joining two relationships:
The join is a binary operation which, applied to two relations R1 and R2, produces a restriction of the Cartesian product of these two relations. When the restriction criterion is equality, we speak of equi-join, otherwise we speak of q-join.
(4a) Natural join
it is an equi-join which is done on the key of one relation and the reference to this key in the other relation.
A semi-join is a join in which we only keep the attributes of one of the two joined relations.
(4c) Outer join
An outer join is a join that includes in the result relation the tuples of one or other of the operand relations even if they do not satisfy the join condition. These tuples are completed by zero values in the result relation.
We speak of a left outer join when we take all the tuples of the left operand and of a right outer join when we take all the tuples of the right relation.
(5) Set operators:
Set operators correspond to the usual operators of set theory, defined on tables of the same schemas, considered as sets of tuples.
The result of dividing a relation R(X,Y) by a relation S(Y) is a relation Q(X) defined by: (i) the schema of Q made up of all the attributes of R not belonging to S, let X, (ii) the tuples qj of Q such that, whatever the tuple si of S, the tuple (qj,si) is a tuple of R (i.e. S).
The symbol * preceding the name of the relations in outer joins indicates the side with respect to which the operation is carried out.