Relational Algebra Operations Continued

The Natural-Join Operation

It is often desirable to simplify certain queries that require a Cartesian product. Usually, a query that involves a Cartesian product includes a selection operation on the result of the Cartesian Product. Consider the query “Find the names of all customers who have a loan at the bank, along with the loan number and the loan amount.”

The natural join is a binary operation that allows us to combine certain selections and a Cartesian product into one operation. It is denoted by the join symbol ∞. The natural-join operation forms a Cartesian product of its two arguments, performs a selection forcing equality on those attributes that appear in both the relation schemas and finally removes duplicate attributes.

We express this query by using the natural join as follows:

• Find the names of all branches with customers who have an account in the bank and who live in Dhaka.

     

The natural join operation on the three relations can be executed in any order.

• Find all customers who have both a loan and an account at the bank.

     

 

We can write an expression for this query by using set intersection as follows:

                      

Relational Algebra Operations (Continued)

The Cartesian-Product Operation

The Cartesian-product operation, denoted by a cross (x), allows us to combine information from any two relations. We write the Cartesian product of relations r1 and r2 as r1 X r2. For example, the relation schema r= borrow x loan is

(borrow.customer_id, borrow.loan_number, loan.loan-number, loan.branch_name, loan.amount)

We can write the relation schema for r as

(customer_id, borrow.loan_number, loan.loan-number, branch_name, amount)

We have dropped relation-name prefixes from those attributes that appear in only one of the two schemas and with the simplified relation schema, we can distinguish borrow.loan_number from loan.loan_number.

Now consider sample values in the two relations borrow and loan.

Borrow:

customer_id	loan_number
01	L-15
02	L-16
03	L-17

Loan:

loan_number	branch_name	amount
L-15	Kakrail	10000
L-17	Motijheel	15000
L-20	Rampura	20000

Now r=borrow x loan would be:

customer_id	borrow.loan_number	loan.loan_number	branch_name	amount
01	L-15	L-15	Kakrail	10000
01	L-15	L-17	Motijheel	15000
01	L-15	L-20	Rampura	20000
02	L-16	L-15	Kakrail	10000
02	L-16	L-17	Motijheel	15000
02	L-16	L-20	Rampura	20000
03	L-17	L-15	Kakrail	10000
03	L-17	L-17	Motijheel	15000
03	L-17	L-20	Rampura	20000

Suppose we want to find the customer ids with loans at Kakrail Branch.

First we need to find that in Kakrail branch customers have a loan in the bank. We write:

Since the Cartesian-product operation combines every tuple of loan with every tuple of borrow, we know that, if a customer has a loan in the Kakrail branch, then there is some tuple in borrow x loan that contains his id, and borrow.loan_number = loan.loan_number. So if we write,

 

we get only those tuples of borrow x loan that pertain to customers who have a loan at the Kakrail branch.

Finally, since we want only customer_id, we do a projection:

The result of this expression, shown in the following table, is the correct answer to the query.

The result of expression [1]

customer_id

01