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.
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,
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.