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