- DBMS Tutorial
- DBMS - Home
- DBMS - Overview
- DBMS - Architecture
- DBMS - Data Models
- DBMS - Data Schemas
- DBMS - Data Independence

- Entity Relationship Model
- DBMS - ER Model Basic Concepts
- DBMS - ER Diagram Representation
- DBMS - Generalization, Aggregation

- Relational Model
- DBMS - Codd's Rules
- DBMS - Relational Data Model
- DBMS - Relational Algebra
- DBMS - ER to Relational Model
- DBMS- SQL Overview

- Relational Database Design
- DBMS - Database Normalization
- DBMS - Database Joins

- Storage and File Structure
- DBMS - Storage System
- DBMS - File Structure

- Indexing and Hashing
- DBMS - Indexing
- DBMS - Hashing

- Transaction And Concurrency
- DBMS - Transaction
- DBMS - Concurrency Control
- DBMS - Deadlock

- Backup and Recovery
- DBMS - Data Backup
- DBMS - Data Recovery

- DBMS Useful Resources
- DBMS - Quick Guide
- DBMS - Useful Resources
- DBMS - Discussion

# Explain the binary operations in relational algebra (DBMS)?

Query is a question or requesting information. Query language is a language which is used to retrieve information from a database.

Query language is divided into two types −

Procedural language

Non-procedural language

## Procedural language

Information is retrieved from the database by specifying the sequence of operations to be performed.

For Example − Relational algebra.

Structure Query language (SQL) is based on relational algebra.

Relational algebra consists of a set of operations that take one or two relations as an input and produces a new relation as output.

## Types of Relational Algebra operations

The different types of relational algebra operations are as follows −

Select operation

Project operation

Rename operation

Union operation

Intersection operation

Difference operation

Cartesian product operation

Join operation

Division operation

Union, intersection, difference, cartesian, join, division comes under binary operation (operate on two tables).

## Union operation

Union operation combines the values in R1, R2 removing duplicate ones.

### Syntax

The syntax for the union operation is as follows −

∏ regno(R1) ∪ ∏ regno(R2)

It displays all the regno of R1 and R2.

### Example

Consider two tables R1 and R2 as shown below −

### Table R1

Regno | Branch | Section |
---|---|---|

1 | CSE | A |

2 | ECE | B |

3 | MECH | B |

4 | CIVIL | A |

5 | CSE | B |

### Table R2

Regno | Branch | Section |
---|---|---|

1 | CIVIL | A |

2 | CSE | A |

3 | ECE | B |

To display all the regno of R1 and R2, use the following command −

∏ regno(R1) ∪ ∏ regno(R2)

### Output

Regno |
---|

1 |

2 |

3 |

4 |

5 |

No replacement is allowed for the entry.

Union All is used for the duplicate entries.

Common entries will be shown if a common intersection is used.

## Intersection operation

It displays the common values in R1 & R2. It is denoted by ∩.

The syntax for the intersection operation is as follows −

∏ regno(R1) ∩ ∏ regno(R2)

### Example 1

Consider two sets,

A={1,2,4,6} and B={1,2,7}

Intersection of A and B

A ∩ B ={1,2}

Elements that are present in both sets A and B are present in the set obtained by intersection of A and B.

In relational algebra if R1 and R2 are two instances of relation then,

R1 ∩ R2 ={ x | x€ R1 and x € R2}

That is, at the intersection of R1 and R2 only those tuples will be present that are in both R1 and R2.

### Example 2

Find all the customers whose account is in the bank and they have taken out a loan.

The expression will be as follows −

∏Name(Depositor) ∩ ∏Name(Borrower)

### Depositor

ID | Name |
---|---|

1 | A |

2 | B |

3 | C |

### Borrower

ID | Name |
---|---|

2 | B |

3 | A |

5 | D |

So intersection of Depositor and borrower is as follows −

A |

B |

## Cartesian product operation

It combines R1 and R2 without any condition.

It is denoted by X.

Degree of R1 XR2 = degree of R1 + degree of R2

{degree = total no of columns}

### Example

Consider R1 table as given below −

RegNo | Branch | Section |
---|---|---|

1 | CSE | A |

2 | ECE | B |

3 | CIVIL | A |

4 | IT | B |

Table R2 is as follows −

Name | RegNo |
---|---|

Bhanu | Priya |

2 | 4 |

Therefore, the result of R1 X R2 is as follows −

RegNo | Branch | Section | Name | RegNo |
---|---|---|---|---|

1 | CSE | A | Bhanu | 2 |

1 | CSE | A | Priya | 4 |

2 | ECE | B | Bhanu | 2 |

2 | ECE | B | Priya | 4 |

3 | CIVIL | A | Bhanu | 2 |

3 | CIVIL | A | Priya | 4 |

4 | IT | B | Bhanu | 2 |

4 | IT | B | Priya | 4 |

## Join operation

It combines the relation R1 and R2 with respect to a condition. It is denoted by ⋈.

The different types of join operation are as follows −

Theta join

Natural join

Outer join

Outer join

Left outer join.

Right outer join.

Full outer join.

## Theta join

If we join R1 and R2 other than the equal condition, then it is called theta join/ non-equi join.

### Example

Consider R1 table

RegNo | Branch | Section |
---|---|---|

1 | CSE | A |

2 | ECE | B |

3 | CIVIL | A |

4 | IT | B |

5 | IT | A |

### Table R2

Name | RegNo |
---|---|

Bhanu | 2 |

Priya | 4 |

R1 ⋈ R2 with condition R1.regno > R2.regno

RegNo | Branch | Section | Name | Regno |
---|---|---|---|---|

3 | CIVIL | A | Bhanu | 2 |

4 | IT | B | Bhanu | 2 |

5 | IT | A | Bhanu | 2 |

5 | IT | B | Priya | 4 |

In the join operation, we select those rows from the cartesian product where R1.regno>R2.regno.

Join operation = select operation + cartesian product operation

### Natural join

If we join R1 and R2 on equal condition then it is called natural join or equi join. Generally, join is referred to as natural join.

Natural join of R1 and R2 is as follows −

{we select those tuples from cartesian product where R1.regno=R2.regno}

R1 ⋈ R2

Regno | Branch | Section | Name |
---|---|---|---|

2 | - | - | Bhanu |

4 | - | - | Priya |

## Outer join

It is an extension of natural join to deal with missing values of relation.

Consider R1 and R2 shown below −

### Table R1

Regno | Branch | Section |
---|---|---|

1 | CSE | A |

2 | ECE | B |

3 | CIVIL | A |

4 | IT | B |

5 | IT | A |

### Table R2

Name | Regno |
---|---|

Bhanu | 2 |

Priya | 4 |

Hari | 7 |

Outer join is of three types. These are explained below with examples.

### Left outer join

It is denoted by R1 ⋈ R2

Regno | Branch | Section | Name | Regno |
---|---|---|---|---|

2 | - | - | Bhanu | 2 |

4 | - | - | Priya | 4 |

1 | - | - | NULL | NULL |

3 | - | - | NULL | NULL |

5 | - | - | NULL | NULL |

Here all the tuples of R1(left table) appear in output.

The mismatching values of R2 are filled with NULL.

Left outer join = natural join + mismatch / extra tuple of R1.

### Right outer join

It is denoted by R1 ⋈ R2

Here all the tuples of R2(right table) appear in output. The mismatching values of R1 are filled with NULL.

Regno | Branch | Section | Name | Regno |
---|---|---|---|---|

2 | - | - | Bhanu | 2 |

4 | - | - | Priya | 4 |

NULL | NULL | NULL | Hari | 7 |

Right outer join = natural join+ mismatch/extra tuple of R2.

### Full outer join

It is denoted by R1 ⋈ R2.

Full outer join=left outer join U right outer join.

Regno | Branch | Section | Name | Regno |
---|---|---|---|---|

2 | - | - | Bhanu | 2 |

4 | - | - | Priya | 4 |

1 | - | - | NULL | NULL |

3 | - | - | NULL | NULL |

5 | - | - | NULL | NULL |

NULL | NULL | NULL | Hari | 7 |

### Example

The diagram given below depicts the full outer join −

## Division operation

The division operator is used for queries which involve the ‘all’.

R1 ÷ R2 = tuples of R1 associated with all tuples of R2.

### Example

Retrieve the name of subject that is taught in all courses

Name | Course |
---|---|

System | Btech |

Database | |

Database | Btech |

Algebra | Btech |

Course |
---|

Btech |

Btech |

÷

Name |
---|

database |

=

The resulting operation must have all combinations of tuples of relation S that are present in the first relation or R.

- Related Articles
- Explain the relational algebra in DBMS?
- Explain project operation in relational algebra (DBMS)?
- Explain rename operation in relational algebra (DBMS)?
- Explain union operation in relational algebra (DBMS)?
- Explain intersection operation in relational algebra (DBMS)?
- Explain cartesian product in relational algebra (DBMS)?
- Explain division operation in relational algebra (DBMS)?
- Explain the select operation in relational algebra (DBMS)?
- Explain the evaluation of relational algebra expression(DBMS)
- Explain the unary operations of algebra relations in DBMS?
- What is join operation in relational algebra (DBMS)?
- Write queries using aggregate functions in relational algebra (DBMS)?
- Explain the Relational Model in DBMS?
- Binary Relational Operations: JOIN and DIVISION
- Difference between Relational Algebra and Relational Calculus