Extended Operations in SQL Relational Algebra

In addition to the fundamental operations, there are several other Extended Operations, which are as follows: 

  • SET INTERSECTION
  • NATURAL JOIN
  • DIVISION
  • ASSIGNMENT

Set Intersection :

The set-intersection operation finds tuples in both the relations which are common to them. In other words it picks only duplicate tuples from the relations. It is denoted as ∩. For example consider the relations Depositor and Borrower :


Now to lists all the customers who Deposit and also Borrow from bank, the query will be 



The relation that results from the preceding query is will be :




Natural Join :

it is a binary operation and a combination of certain selections and a Cartesian product into one operation. 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 schema, and finally removes duplicate attributes. It is denoted as |X|, and it is an associative operation. For example consider the relations Borrower and loan : 



Now to find the names of all customers who have a loan at the bank, along with the loan number and the loan amount, the query will be : 
Since the schema for Borrower and Loan have the attribute loan_number in common, the natural-join operation considers only pairs of tuples that have the same value on loan_number. It combines each such pair of tuples into a single tuple on the union of the two schema (that is, customer_name, loan_number, amount). After performing the projection, we obtain the relation is :



Division :

As the name of this operation implies, it involves dividing one relation by another. The division operation, denoted by ÷, is suited to queries that include the phrase “for all.” consider the relations Account, Branch and Depositor : 




Suppose that we wish to find all customers who have an account at all the branches located in Brooklyn. We can obtain all branches in Brooklyn by the expression


The relation that results from the preceding query is will be :


after, We can find all (customer-name, branch-name) pairs for which the customer has an account at a branch by query :


The relation that results from the preceding query is will be :  


Now, we need to find customers who appear in relation s with every branch name in relation r. The operation that provides exactly those customers is the divide operation. We
formulate the query by writing


The result of this expression is a relation that has the schema (customer-name) and that contains the tuple (Johnson).


Assignment : 

Sometimes it is useful to be able to write a relational algebra expression in parts using a temporary relation variable. The assignment operation, denoted , works like assignment in a programming language. for example, let r(R) and s(S) be relations. We could write r ÷ s as

The evaluation of an assignment does not result in any relation being displayed to the user. Rather, the result of the expression to the right of the ← is assigned to the relation variable may be used in subsequent expressions.


Next topic :- 

No comments:

Post a Comment