Set intersection is denoted by 
, and returns a relation that
contains tuples that are in both of its argument relations.
It does not add any power as
To find all customers having both a loan and an account at the SFU branch, we write
Often we want to simplify queries on a cartesian product.
For example, to find all customers having a loan at the bank and the cities in which they live, we need borrow and customer relations:
Our selection predicate obtains only those tuples pertaining to only one cname.
This type of operation is very common, so we have the natural join,
denoted by a 
sign.
Natural join combines a cartesian product and a selection into one operation.
It performs a selection forcing equality on those attributes that
appear in both relation schemes.
Duplicates are removed as in all relation operations.
To illustrate, we can rewrite the previous query as
The resulting relation is shown in Figure 3.7.
Figure 3.7: Joining borrow and customer relations.
We can now make a more formal definition of natural join.
and 
to be sets of attributes.
.
.
and 
.
and 
, denoted by 
is a relation
on scheme .
of a selection on 
where the predicate requires 
for each attribute 
in .
Formally,
where 
.
To find the assets and names of all branches which have depositors living in Stamford, we need customer, deposit and branch relations:
Note that is associative.
To find all customers who have both an account and a loan at the SFU branch:
This is equivalent to the set intersection version we wrote earlier. We see now that there can be several ways to write a query in the relational algebra.
If two relations and have no attributes in common, then
, and 
.
Division, denoted 
, is suited to queries that include the phrase
``for all''.
Suppose we want to find all the customers who have an account at all branches located in Brooklyn.
Strategy: think of it as three steps.
We can obtain the names of all branches located in Brooklyn by
Figure 3.19 in the textbook shows the result.
We can also find all cname, bname pairs for which the customer has an account by
Figure 3.20 in the textbook shows the result.
Now we need to find all customers who appear in 
with every
branch name in 
.
The divide operation provides exactly those customers:
which is simply 
.
Formally,
and be relations.
.
is a relation on scheme 
.
is in if for every tuple 
in
there is a tuple 
in satisfying both of the following:
portion of a tuple
is in if and only if there are tuples with the 
portion
and the portion in for every value of the portion
in relation .
We will look at this explanation in class more closely.
The division operation can be defined in terms of the fundamental operations.
Read the text for a more detailed explanation.
Sometimes it is useful to be able to write a relational algebra expression
in parts using a temporary relation variable
(as we did with and in the division example).
The assignment operation, denoted 
, works like assignment in
a programming language.
We could rewrite our division definition as
No extra relation is added to the database, but the relation variable created can be used in subsequent expressions. Assignment to a permanent relation would constitute a modification to the database.