Relational Algebra  in DBMS

This Relational algebra in DBMS tutorial will be helpful for computer science students in understanding the concepts of relational algebra and various relational algebra operators.

In 1971, Relational algebra is defined by E.F. Codd based on relational language.

In this tutorial entitled with Relational algebra in DBMS Questions  are explained with examples .

Every year some  Relational algebra in DBMS Questions are asked in GATE (CS/IT) and UGC NET Exam.

Computer science students should prepare the relational algebra very for for the GATE exam.

Computer science graduate will able to get the answer of the following questions after reading this Relational algebra in DBMS Questions and Answers  tutorial.

Frequently Asked Questions

Questions on Relational Algebra in DBMS are generally asked in various competitive exam like GATE(CS/IT) and UGC NET.

Questions on Relation algebra in DBMS  are also asked in the Technical Interview as well as the University Examination.

Some frequently asked questions are as follow –

• What is Relational Algebra in DBMS ?
• What are different DBMS languages?
• What is procedural query language?
• Explain different relational algebra operations in DBMS with example?
• What do you understand by Cartesian Product Operation ?
• What are different types of joins in database ?
• What are different Relational algebra operations?
• What is Theta Join?
• What is Equi Join in database ?

What is Relational Algebra in DBMS ?

• Relational algebra in DBMS is a procedural query language and main foundation of Relational Algebra is the Relational Database and SQL.
• The goal of a relational algebra query language is to fetch data from database or to perform various operations like delete, insert, update on the data.
• When it is said that relational algebra is a procedural query DBMS language, it means that it performs series of operations to produce the required result
• Procedural Query Language tells the user what data to be retrieved from database and how to retrieve it ?
• Expression in relational algebra in DBMS takes one relation as input and generate another relation as output.
• It takes an instance of relations and performs operations on one or more relations to describe another relation without changing the original relations.

Relational Algebra Operations in DBMS

Different  Relational algebra operations in DBMS are lised here .

• Select
• Project
• Union
• Set different
• Cartesian product
• Rename

Let’s see each relational algebra operation one by one in this section.

1. Select Operation (σ) 

Selection operation in Relational algebra is used to find the tuples in a relation satisfy the given condition. It is denoted by sigma (σ).

Notation − σ_p(r)

Where σ indicates selection predicate and r denotes relation and p is a propositional logic formula which may use relational operators like and, or, and not.

σ_{account_type = “saving”}(Account)

Output – It selects tuples from relation Account where the account type is ‘saving.’

2.Project Operation (∏)

Project or projection operation in relational algebra is used to select required attributes or columns from relation. Symbol ∏ indicates Project operation.

Notation − ∏_Attr1, _{Attr2, —Attrn} (r)

Where Attr1, Attr , Attrn are attribute of relation r.

∏_{Stud_rollno, name, city} (Students)

Output – It selects attributes stud_rollno, name and city from relation Student

3.Union Operation (∪)

Union operaton in Relational Algebra is used to select all the tuples from two relations. Symbol ∪ indicates union operators.

Notation – R1 U R2

Where R1 and R2 are the relations in the database.

For example, in R1 ∪ R2, the union of two relations R1 and R2 produces an output relation that contains all the tuples of R1, or R2, or both R1 and R2, duplicate tuples being eliminated. The condition is that R1 and R2 must have same number of attributes.

To perform the union operation, the following rules must be followed

R1 and R2 must have the same number of attributes. In both relations, attribute domains must have same scope.

∏ _{customer_name} (Depositor) ∪ ∏ _{customer_name} (Borrower)

Output – It gives the customer name from both relation Depositor and Borrower by eliminating duplication.

4.Set Difference (−) Operation

Suppose we have two relation R1 and R2, then the set difference operation R1 – R2, produces a relation consisting of the tuples that are in relation R1, but not in R2.  Both the relations R1 and R2 must have same number of attributes.

Notation: R1 – R2

Where R1 and R2 are the relations in the database.

∏ _{customer_name} (Depositor) – ∏ _{customer_name} (Borrower)

Output – It gives the customer name which are present in relation Depositor but not in relation Borrower.

5.Cartesian Product (Χ)

Suppose we have two relations r1 and r2 then the Cartesian product of these two relations (r1 X r2) will be the combination of each tuple of relation r1 with each tuple of relation r2. Cartesian Product is indicated by X symbol.

Notation – R1 Χ R2

Where R1 and R2 are two relations.

Σ_{city = “Kolkata”}(Depositor Χ Borrower) 

Output – It selects all tuples from both relations Depositor and Borrower where city is Kolkata.

6.Rename Operation (ρ)

The results of relational algebra operations are always the relations but they are without any name. The rename operation allows user to rename the output relation. It is denoted using small Greek letter rho (ρ).

Notation − ρ _x (E)

Where E is the expression with different relational algebra operations and x is, the name given to their result.

ρ_{(CUST_NAMES, ∏ (Customer_Name)(Account))}

Output – The output relation from the expression, ∏(Customer_Name)(Account) rename with CUST_NAMES.

7. Joins operation in Relational Algebra

Join operation in relational algebra is a combination of a Cartesian product followed by which satisfy certain condition. A Join operation combines two tuples from two different relations, if and only if a given condition is satisfied.

There are different types of join operations.

(I) Natural Join (⋈)

A result of natural join is the set of tuples of all combinations in R and S that are equal on their common attribute names.

It is denoted by ⋈.

Consider the following example to understand natural Joins.

EMPLOYEE

SALARY

∏ EMP_NAME, SALARY (EMPLOYEE ⋈ SALARY)

Output:

(II) Outer Join

Outer joins are used to include all the tuples from the relations included in join operation in the resulting relation.

An outer join is of three types:

1. Left outer join
2. Right outer join
3. Full outer join

Consider the example

EMPLOYEE

FACT_WORKERS

1. Left outer join (⟕)

In Left outer join, all the tuples from the Left relation, say R, are included in the resulting relation. If there are some tuples in relation R which are not matched with tuple in the Right Relation S, then the attributes of relation R of the resulting relation become NULL.

EMPLOYEE ⟕ FACT_WORKERS 

Output:

1. Right outer join (⟖)

In Right outer join, all the tuples from the Right relation, say S, are included in the resulting relation. If there are some tuples in relation S which are not matched with tuple in the Right Relation R, then the attributes of relation S of the resulting relation become NULL.

EMPLOYEE ⟖ FACT_WORKERS

    Output :

Full outer join

Full outer join is the combination of both left outer join and right outer join. It contains all the tuples from both relations.

For example

EMPLOYEE ⟗ FACT_WORKERS

• Theta (θ) Join

Theta join is denoted by the symbol θ. It combines those tuples from different relations which satisfies the condition.

Notation – R1 ⋈_θ R2

Where R1 and R2 are relations with n numbers of attributes such that the attributes do not have anything in common, it means R1 ∩ R2 = Φ.

STUDENT ⋈_{Student.Std = Subject.Class} SUBJECT Output:

• Equi Join

When Theta join uses equality operator for comparison, then it is called equi join. The above example of theta join is applicable for equi join.

Conclusion

We have explained various Relational algebra Operations in DBMS with suitable examples in this tutorial.

Different Relational algebra in DBMS Questions or examples with solution also have been explained.

I hope that this tutorial will be helpful in understanding the relational algebra concepts.

If you find this relational algebra in DBMS with examples tutorial useful then please Like and Share the post on Facebook, Twitter, Linkedin through their icons as given below.

Previous Tutorial – Difference Between Generalization and Specialization

Next Tutorial – Relational Database Management System