0
9.6kviews
Explain Relational algebra operations with proper examples

The terms are

i. Set Intersection

ii. Generalized Projection

iii. Division Operator

iv. Natural Join

1 Answer
0
129views

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

enter image description here

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.

Please log in to add an answer.