Explain the following Relational Algebra Operations with example:

i. Set Intersection

ii. Generalized Projection

iii. Division Operator

iv. Natural Join

Mumbai University > Computer Engineering > Sem 4 > Database Management System

Marks: 10 M

Year: Dec 2014, May 2014

The relational algebra is a procedural query language. It consists of a set of operations that take one or two relations as input and produce a new relation as their result.

i. Set Intersection

• Set Intersection is used to find intersecting tuples from two relations.

• Suppose that we wish to find all customers who have both a loan and an account. Using set intersection, we can write

  Πcustomer-name (borrower) ∩ Πcustomer-name (depositor)

• Any relational algebra expression that uses set intersection can be rewritten simply by replacing the intersection operation with a pair of set-difference operations as:

  r ∩ s = r − (r − s)

• Thus, set intersection is not a fundamental operation but derived operation in relational algebra. It is simply more convenient to write r ∩ s than to write r − (r − s)

ii. Generalized Projection

• The generalized-projection operation extends the projection operation by allowing arithmetic functions to be used in the projection list.

• The generalized projection operation has the form

  ΠF1,F2,...,Fn(E)

  Where, E is any relational-algebra expression and each of F1, F2, . . .,Fn is an arithmetic expression involving constants and attributes in the schema of E. As a special case, the arithmetic expression may be simply an attribute or a constant.

• For example, suppose we have a relation credit-info, which lists the credit limit and expenses so far (the credit-balance on the account). If we want to find how much more each person can spend, we can write the following expression:

  Πcustomer-name, limit − credit-balance (credit-info)

iii. Division Operator

• The division operation, denoted by ÷, is suited to queries that include the phrase“for all.”

• Let’s assume that we wish to find all customers who have an account at all the branches located in Mumbai. This expression will give all the branches in Mumbai -

  r1 = Πbranch-name (σbranch-city =“Mumbai” (branch))

• Let’s find all (customer-name, branch-name) pairs for which the customer has an account at a branch

  r2 =Πcustomer-name, branch-name (depositor ∞ account)

• Now, we need to find customers who appear in result r2 with every branch name in result r1. The operation that provides exactly those customers is the divide operation.

  Πcustomer-name, branch-name(depositor ∞ account)÷ Πbranch-name(σbranch-city =“Mumbai” (branch))

  The result of this expression is a relation that has the schema (customer-name) and contains tuple with customer having account in all branches of branch-city (Mumbai).

• Formally, let r(R) and s(S) be relations, and let S ⊆ R i.e. every attribute of schema S is also in schema R. The relation r ÷ s is a relation on schema R − S (i.e. on the schema containing all attributes of schema R that are not in schema S).

iv. Natural Join

• Usually, a query that involves a Cartesian product includes a selection operation on the result of the Cartesian product. In order to simplify this dual operation, Natural Join can be used.
• 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 relation schemas, and finally removes duplicate attributes.
• Here’s an example- “Find the names of all customers who have a loan at the bank, and find the amount of the loan.”

Since the schemas for borrower and loan have the attribute Loan_no in common, the natural-join operation considers only pairs of tuples that have the same value on Loan_no. It combines each such pair of tuples into a single tuple on the union of the two schemas (that is, Loan_no, Borrower_name, Loan_amount) as shown in table above.