- The Select Operation
Select selects tuples that satisfy a given predicate. Select is
denoted by a lowercase Greek sigma (
),
with the predicate appearing as a subscript. The argument relation is given
in parentheses following the
.
For example, to select tuples (rows) of the borrow relation
where the branch is ``SFU'', we would write
Let Figure 3.3 be the borrow and branch relations in
the banking example.
Figure 3.3: The
borrow and branch relations.
The new relation created as the result of this operation consists of one
tuple:
.
We allow comparisons using =,
,
<,
,
> and
in the selection predicate.
We also allow the logical connectives
(or) and
(and). For example:
Figure 3.4: The client
relation.
Suppose there is one more relation, client, shown in Figure 3.4,
with the scheme
we might write
to find clients who have the same name as their banker.
The Project Operation
Project copies its argument relation for the specified attributes
only. Since a relation is a set, duplicate rows are eliminated.
Projection is denoted by the Greek capital letter pi (
).
The attributes to be copied appear as subscripts.
For example, to obtain a relation showing customers and branches, but
ignoring amount and loan#, we write
We can perform these operations on the relations resulting from other
operations.
To get the names of customers having the same name as their bankers,
Think of select as taking rows of a relation, and project
as taking columns of a relation.
The Cartesian Product Operation
The cartesian product of two relations is denoted by a cross (
),
written
The result of
is a new relation with a tuple for each possible pairing of tuples
from
and
.
In order to avoid ambiguity, the attribute names have attached to them
the name of the relation from which they came. If no ambiguity will result,
we drop the relation name.
The result
is a very large relation. If
has
tuples, and
has
tuples, then
will have
tuples.
The resulting scheme is the concatenation of the schemes of
and
,
with relation names added as mentioned.
To find the clients of banker Johnson and the city in which they live, we
need information in both client and customer relations. We
can get this by writing
However, the customer.cname column contains customers of bankers
other than Johnson. (Why?)
We want rows where client.cname = customer.cname. So we can
write
to get just these tuples.
Finally, to get just the customer's name and city, we need a projection:
