Structured Query Language Relational Algebra : Fundamental Operations

Relational Algebra : 

A query language is a language in which user requests information from the database. it can be categorized as either procedural or non procedural. In a procedural language the user instructs the system to do a sequence of operations on database to compute the desired result. In non procedural language the user describes the desired information without giving a specific procedure for obtaining that information.

The relational algebra forms the basis of the widely used SQL query language. 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. The fundamental operations in the relational algebra are as follows :

  • SELECT
  • PROJECT
  • UNION
  • SET DIFFERENCE
  • CARTESIAN PRODUCT
  • RENAME 

The select, project, and rename operations are called unary operations, because they operate on one relation. The other three operations operate on pairs of relations and are, therefore, called binary operations. In addition to the fundamental operations, there are several other operations, which are:

  • SET INTERSECTION
  • NATURAL JOIN
  • DIVISION
  • ASSIGNMENT

Select :

The select operation selects tuples that satisfy a given predicate or condition. It is an unary operation defined on a single relation. It is denoted by the lowercase Greek letter sigma ( σ ). The notation of Select Operation is :
Where σ stands for selection predicate and r stands for relation. p is prepositional logic formula which may use connectors like and(∧), or (∨), and not(¬). These terms may use relational operators like − =, ≠, ≥, < , >, ≤. The predicate appears as a subscript to σ. The argument relation is in parentheses after the σ. Consider the below table 'Employee' :


Thus, to select those tuples of the Employee relation where the Account_branch is “Axis_bank” we write


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



Project :

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 upper case Greek letter pi (Π). In simple words it projects column(s) that satisfy a given predicate. The notation of the Project operation is :
Where A1, A2 , An are attribute names of relation r. For example :

The above query lists all the Employee_id and Employee_name from the Employee table. The relation that results from the preceding query is will be :

UNION :

The union Operation performs binary union between two given relations. It is used when we need some attributes that appear in either or both of the two relations. It is denoted as U.

                                 Notation − r U s 

Where r and s are either database relations or relation result set (temporary relation). For a union operation r U s to be valid, the following conditions must 

1. The relation r and s must have the same number of attributes.
2. The domains of the ith attribute of r and the ith attribute of s must be the same for all i.
3. Duplicate tuples are automatically eliminated.

For example consider the two table Depositor and borrower



now to list all the customers from both table, the query will be




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




SET DIFFERENCE :

The set-difference operation, denoted by −, allows us to find tuples 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. For example at the Depositor and Borrower, to list all the customers from Depositor table who did not borrow from bank, the query will be :



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



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 number of attributes, and that the domains of the ith attribute of r and the ith attribute of s be the same.

Cartesian-Product

The Cartesian-product operation, denoted by a cross (×), allows us to combine information from any two relations. It is sometimes called the CROSS PRODUCT or CROSS JOIN. It works on two sets and combine the tuples of one relation with all the tuples of the other relation.

Notation − r Χ s

Where r and s are relations, and if relation r has n1 tuples and relation s has n2 tuples then r X s has n1*n2 tuples. For example consider the two tables or relations Customer and Items : 



the cartesian product will be : 


                 Customer X Items

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


Rename :

The results of relational algebra are also relations but without any name. The rename operation allows us to rename the output relation. 'rename' operation is denoted with small Greek letter rho ρ. 

               Notation − ρ x (E)

Where the result of expression E is saved with name of x.

Next Topic :- 

No comments:

Post a Comment