Saturday, May 6, 2017

Relational Algebra

What is Relational Algebra?The relational algebra is a theoretical procedural query language which takes instance of relations and does operations that work on one or more relations to describe another relation without altering the original relation(s). Thus, both the operands and the outputs are relations, and so the output from one operation can turn into the input to another operation which allows expressions to be nested in the relational algebra, just as you nest arithmetic operations. This property is called closure: relations are closed under the algebra, just as numbers are closed under arithmetic operations.
The relational algebra is a relation-at-a-time (or set) language where all tuples are controlled in one statement without the use of loop. There are several variations of syntax for relational algebra commands and you use a common symbolic notation for the commands and present it informally.

The primary operations of relational algebra are as follows:
  • Select 
  • Project 
  • Union 
  • Set different 
  • Cartesian product 
  • Rename 
Select Operation (σ)
It selects tuples that satisfy the given predicate from a relation. Notation − σp(r)

Here σ stands for selection predicate and r stands for relation and p is prepositional logic formula which may use connectors like and, or, and not.

σ predicate(R): This selection operation functions on a single relation R and describes a relation which contains only those tuples of R that satisfy the specified condition (predicate).

Example: σteacher = "database"(Names)

Output – It selects tuples from names where teacher is ‘database’.

Project Operation (∏)
The Projection operation works on a single relation R and defines a relation that contains a vertical subset of R, extracting the values of specified attributes and eliminating duplicates.

Produce a list of salaries for all staff, showing only the staffNo, fName, lName, and

salary details.

ΠstaffNo, fName, lName, salary(Staff)

In the below mentioned example, the Projection operation defines a relation that contains only the designated Staff attributes staffNo, fName, lName, and salary, in the specified order. The result of this operation is shown in figure below
Union Operation
For R ∪ S, The union of two relations R and S defines a relation that contains all the tuples of R, or S, or both R and S, duplicate tuples being eliminated. R and S must be union-compatible.

For a union operation to be applied, the following rules must hold −
  • r, and s must have the same quantity of attributes. 
  • Attribute domains must be compatible. 
  • Duplicate tuples gets automatically eliminated. 
Set difference
For R − S The Set difference operation defines a relation consisting of the tuples that are in relation R, but not in S. R and S must be union-compatible.

Example: 
∏ writer (Nobels) − ∏ writer (papers)

Cartesian product
For R × S, the Cartesian product operation defines a relation that is the concatenation of every tuple of relation R with every tuple of relation S.

Example: 
σwriter = 'gauravray'(Articles Χ Notes)

Join Operations Typically, you want only combinations of the Cartesian product which satisfy certain situations and so you can normally use a Join operation instead of the Cartesian product operation. The Join operation, which combines two relations to form a new relation, is one of the essential operations in the relational algebra. There are various types of Join operation, each with subtle differences some more useful than others:
  • Theta join 
  • Equijoin (a particular type of Theta join) 
  • Natural join 
  • Outer join 
  • Semijoin 
Rename Operation (ρ) The results of relational algebra are also relations but without any name. The rename operation provides database designers to rename the output relation. The rename-operation is denoted using small Greek letter rho (ρ).

It is written as: ρ x (E)





















No comments:

Post a Comment