Relational algebra

(tanjina,niva,kavita,BCA-3^RD sem,DSVV,AUG,2014)

Introduction :

 Relational algebra is one of the formal query language that provides a foundation or influene the other commercial query langualges  like SQL(structural query language) and QBE (query by example ).

The other formal query language is the relational calculus, which is also a foundation for the commercial query languages.

Query languages:

Query languages are specialized languages for asking question or queries ,also it is called a request that involce data in a database.

Relational calculus:

·         Also called non procedural query language because there is no particular procedure for solving any query.

·          In this a query describes the desired answer without specifying how the answer is to be computed .

·         Also known as the declarative language.

Relational  algebra:

In relational algebra the queries are composed using a collection of operators, and each query describes a step by step procedure for computing the desired result ;

The queries are specified in operational and procedural manner that’s why its called the  procedural language also.

There are many operations which we include in the relational algebra  .

Each relational query describes a step by step procedure for computing the desired answer ,based on the order in which operators are applied in the query.

The procedural nature of the algebra allows us to think of an algebra as a recipe,or a plan ,for evaluating a query,and relational system in fact use algebra expressions to represent query evaluation plans.

Relational algebra expression

It is an expression which is a composition of the operators and  it  forms a complex query called a relational algebra expression.

A unary algebra operator applied to a single expression ,and a binary algebra operator applied to two expression

Types of oprations

1.fundamental

2.other types of oprations

1.fundamental oprations are

·        Unary

- select

- project

- rename

·        Binary(set oprations)

- union

- Set difference

- Cartesian product(cross product)

- intersection

2.Other operations are

·        Join

-condition join

-natural join

-equijoin

·        Division

Description on the various operations

Select operation

The SELECT operation (denoted by  (sigma)) is used to select a subset of the tuples from a relation based on a selection condition

·         The selection condition acts as a filter

·         Keeps only those tuples that satisfy the qualifying condition

·         Tuples satisfying the condition are selected whereas the other tuples are discarded (filtered out)

Examples:

A.     Select the STUDENT tuples whose age is 18

sigma _age=18 (STUDENT)

B.       Select the STUDENT tuples whose course is bca

sigma _course=BCA (STUDENT)

C.      Select the students from the “student relation instances”

      whose gender is male

      sigma_gender=M(STUDENT)

Student name	Age	gender		course
Ritika	18	F		BCA
Prerna	19	F		Bsc.
Ankush	20	M		BA
Preeti	18	F	Bsc.
Pragyan	20	M	BA
Ritu	18	F	BCA
Janvi	20	F	BCA

Student relation model/instances

Answer of the first select statement is :

A.

Student name	Age	gender	course
Ritika	18	F	BCA
Preeti	18	F	Bsc.
Ritu	18	F	BCA

Project operation

PROJECT Operation is denoted by p (pi)

If we are interested in only certain attributes of relation, we use PROJECT

This operation keeps certain columns (attributes) from a relation and discards the other columns.

Example:

To list all the students name and course only in the student relation model.

Pi_{student_name , course} (student)

Student-name	Course
Ritika	BCA
Prerna	Bsc.
Ankush	BA
Preeti	Bsc.
Pragyan	BA
Ritu	BCA
Janvi	BCA

(output from the table first)

Rename operation

RENAME operation – which can rename either the relation name or the attribute names, or both

·         The RENAME operator is denoted by r (rho)

·         In some cases, we may want to rename the attributes of a relation or the relation name or both

·         Useful when a query requires multiple operations

Example:

The general RENAME operation r can be expressed by any of the following forms:

·         r_S(R) changes:

-          the relation name only to S

·         r_{(B1, B2, …, Bn )}(R) changes:

-          the column (attribute) names only to B1, B1, …..Bn

·         r_{S (B1, B2, …, Bn )}(R) changes both:

-          the relation name to S, and

-          the column (attribute) names to B1, B1, …..Bn

Binary operations(set operations)

Union

It is a Binary operation, denoted by  sign of union in set theory.

 The result of R È S, is a relation that includes all tuples that are either in R or in S or in both R and S. Duplicate tuples are eliminated

The two operand relations R and S must be “type compatible” (or UNION compatible)

R and S must have same number of attributes

Each pair of corresponding attributes must be type compatible (have same or compatible domains)

Eg. in the bank enterprise we have depositor and borrower almost similar attributes and types

(Reference:Abraham silberschatz ,henry f.korth and s. sudarshan)

a.

Customer name	Id no.
RITA	301
GITA	302
RAM	303

(DEPOSITOR’S RELATIONAL MODEL)

b.

Customer name	Id no.
Sham	300
Surbhi	304
Rita	301
Ram	303

(borrower’s relational model)

Output:a union b

Customer_name	Id no
Rita	301
Gita	302
Ram	303
Sham	300
Surbhi	304

.The duplicacy has been removed

Set difference

SET DIFFERENCE (also called MINUS or EXCEPT) is denoted by – .The result of R – S, is a relation that includes all tuples that are in R but not in S.

The attribute names in the result will be the same as the attribute names in R

The two operand relations R and S must be “type compatible”

Output:a-b

Customer name	Id no
Gita	302

The elements of a which are not belongs to b contains only a single result.

Cartisian product

The resulting relation state has one tuple for each combination of tuples—one from R and one from S. Hence, if R has n_R tuples (denoted as |R| = n_R ), and S has n_S tuples, then R x S will have n_R * n_S tuples.

The two operands do NOT have to be "type compatible”.may be of different different relational instances.

Example:

R.

A	1
B	2
D	3
F	4

S.

D	3
E	4

Output: R*S

A	1	D	3
A	1	E	4
B	2	D	3
B	2	E	4
D	3	D	3
D	3	E	4
F	4	D	3
F	4	E	4

Other operations are

·        Join

·        Division

Join

-conditional join

-natural join

-equijoin

What is join:

It is just a cross product of two relations.

·         Join allow you to evaluate a join condition between the attributes of the relations on which the join operations undertaken . 

·         It is used to combine related tuples from two relations.

·          It is used to combine related tuples from two relations

·         Join condition is called theta.

Notation:-

                           R JOIN   join condition S

   

Let us take a instance:-

 

Natural Join

Invariably the JOIN involves an equality test, and thus is often described as an equi-join. Such joins result in two attributes in the resulting relation having exactly the same value. A `natural join' will remove the duplicate attribute(s).

• In most systems a natural join will require that the attributes have the same name to identify the attribute(s) to be used in the join. This may require a renaming mechanism.
• If you do use natural joins make sure that the relations do not have two attributes with the same name by accident.