DBMS - Relational Algebra: Operations with Examples

 

Relational Algebra:

 

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. Relational algebra is performed recursively on a relation and intermediate results are also considered relations.

 

It uses operations to perform queries and operator can be either unary or binary. The fundamental operations in the relational algebra are Select, Project, Union, Set-Difference, Cartesian Product, and Rename. In addition to the fundamental operations, there are several other operations namely Set-Intersection, Natural Join, Division and an Assignment.

 

 

Operations in Relational Algebra:

 

1.    Select operation:

• The select operation selects tuples that satisfy a given predicate. We use the lower case Greek letter Sigma (σ) to denote selection.
• It is a unary operator.
• The predicate appears as a subscript to Sigma (σ).
• The argument relation is in the parentheses after the σ.
• Thus, to select those tuples of the loan relation where the branch is “Perryridge”, we write σ _{Branch _name}= “Perryridge” (loan)
• If the loan relation is as shown in figure (a) then, the relation that results from the preceding query is shown in figure (b).
• We can find all tables in which the amount lent is more than $1200 by writing:

σ _Amount > 1200 (loan)

 

Loan_number	Branch_name	Amount
L_14	Downtown	1500
L_15	Perryridge	1500
L_16	Downtown	1000
L_17	Perryridge	1300
L_23	Redwood	2000
L_93	Miami	500

                                                                 Fig (a):  loan relation

• In general we allow comparisons using =,_=,<,>,<=,>=  in the selection predicate.
• Furthermore, we can combine several predicates into a larger predicate by using the connectives AND, OR, and NOT. Thus, to find those tuples pertaining to loans of more than $1,200 made by the Perryridge branch we write:

σ _{Branch_name} = “Perryridge” Λ Amount > 1200(loan)

 

Loan_number	Branch_name	Amount
L_15	Perryridge	1500
L_17	Perryridge	1300

Fig (b): Result of σ _{Branch_name} = “Perryridge” (loan)

 

2.   Project operation:

• The project operation is a unary operation that returns its argument relation with certain attributes left out.
• Since a relation is a set, any duplicate rows are eliminated.
• Projection is denoted by the uppercase Greek letter Pi (Π).
• Suppose we want to list all loan numbers and the amount of loans but do not care about the branch name. The project operation allows us to produce this relation.
• We list those attributes that we wish to appear in the result as a subscript to Π. The argument relation follows in parenthesis. Thus, we write the query to list all loan numbers and the amount of the loan as: 

                    Π _{Loan_number, Amount} (loan)

 

 

3.   Union operation:

• The union of two relations is obtained by combining the tuples from one relation with tuples from the second relation to form a third relation.
• It is a binary operator.
• Consider a query to find the names of all bank customers who have either an account or a loan or both.
• We need the information in the depositor relation and in the borrower relation as customer relation does not contain this information.
• We know how to find the names of all customers with a loan in the bank:

Π _{customer_name}(borrower)

•  We also know how to find the names of all customers with an account in the bank:

            Π _{customer_name}(depositor)

• To answer the query we need the union of these two sets; that is we need all customer names that appeared in either or both of the two relations.
• We find these data by the binary operation Union denoted as in set theory by the symbol (⋃). So, the expression needed is:

            Π _{customer_name}(borrower)  ⋃  Π _{customer_name}(depositor)

• For a union operation r ⋃ s to be valid, we require that two conditions hold:

 

1. The relations r and s must be of the same arity. That is, they must have the same number of attributes.
2. The domain of i^th attribute of r and the i^th attribute of s must be same for all i.

 

4.   Set Difference operation:

• The set difference operation denoted by Dash (‒), allows us to find tables that are in one relation but are not in another.
• The expression r - s produces a relation containing those tuples in r but not in s. We can find all customers of the bank who have an account but not a loan as:

Π _{customer_name}(depositor) -  Π _{customer_name}(borrower)

• As with the union operation, we must ensure that set differences are taken between compatible relations.
• Therefore for a set difference operation r-s to be valid, we require that the relations r and s be of the same arity, and that the domains of the i^th attribute of r and the i^th attribute of s be the same.

 

 

5.   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 Emp1 and Emp2 as Emp1 x Emp2.

 Emp1:

 

Name	Address
Ajay	Delhi
Vijay	Bangalore

  

Emp2:

 

Salary	Deptno
20000	1
25000	2

 

Emp1 x Emp2:

 

Name	Address	Salary	Deptno
Ajay	Delhi	20000	1
Vijay	Bangalore	20000	1
Ajay	Delhi	25000	2
Vijay	Bangalore	25000	2

 

 

6.   Rename operation:

• The rename operation is used to provide new name to the relation for the output which comes out of the relational algebra expression.
• Unlike relations in the database, the results of a relational algebra expression do not have a name that we can use to refer to them.
• It is useful to be able to give them names; the rename operator, denoted by the lowercase Greek letter rho (ρ).

 

 

7.   Set - intersection  operation:

• The first additional relational algebra operation that we shall define is set intersection.
• It is used to select common tuple from two relations.
• It is denoted by intersection symbol (∩).
• 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)

• Note that we can rewrite any relational algebra expression that uses set intersection by replacing the intersection operation with a pair of set difference operation as:

 

r ∩ s= r-(r-s)

• Thus, set intersection is not a fundamental operation and does not add any power to the relational algebra.
• It is simply more convenient to write r ∩ s than to write r-(r-s).

 

 

 

8.   Natural Join operation:

• 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.
• It is often desirable to simplify certain queries that require a Cartesian product.
• Consider the query “find the names of all customers who have a loan at the bank along with the loan number and the loan amount.”
• We first form the Cartesian product of the borrower and loan relations.
• Then we select those tuples in that pertain to only the same loan_number, followed by the projection of the resulting customer_name,  loan_number  and amount:

Π _{customer_name, loan.loan_ number, amount} (σ _{borrower.loan_number} = loan.loan_number (borrower x loan))

• The natural join can be given as below:

 

Π _{customer_name, loan_ number, amount}

(borrower ⋈ loan)

9.   Division operation:

• The division operation denoted by division symbol ( ÷ ) is suited to queries that includes the phrase “ for all”.
• 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:

 

r1= Π _{branch_name}(σ _{branch_city}= “ Brooklyn”( branch))

• We can find all (customer_name, branch_name) pairs for which the customer has an account at a branch as:

 

r2= Π _{customer_name,branch_name}

(depositor ⋈account)

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

 

Π_{customer_name,branch_name}

(depositor ⋈account) ÷ Π_{branch_name}

(σ _{branch_city} = “Brooklyn” (branch))