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