Basic Operators in DBMS

February 1, 2023

Basic Operators

On this page, we will learn about the Basic Operators in DBMS. All the 6 types of Basic Operators in Relational Algebra are explained in brief.

Basic Operators in DBMS

Relational Algebra in DBMS

Relational algebra is a widely used procedural query language.
Relational algebra collects instances of relations as input and gives occurrences of relations as output by using various operations.
Relational algebra operations are performed recursively on a relation.
The output of relational algebra operations is a new relation, which can be formed from one or more input relations.

Basic Operators in Relational Algebra:

There are 6 basic operations in Relational Algebra. They are

Select (σ)
Project (∏)
Union (∪)
Set Difference (-)
Cartesian product (X)
Rename (ρ)

Select (σ)

The SELECT operation is used for selecting a subset of the tuples according to a given selection condition.
This operation is denoted by symbol sigma(σ).

Syntax:

σ_p(r)

σ denotes Select operation
p denotes the condition
r is the relation/table name

Example 1:

σ _{course = "Java"} (Student)

Output– Selects tuples from Student where course = ‘Java’.

Example 2

σ _{course = "Java" and age = "20"}(Student)

Output – Selects tuples from Student where the course is ‘Java’ and age is ’20’.

Example 3

σ _{salary > 50000}(Employee)

Output – Selects tuples from Employee where salary is greater than 50000

Projection(π)

The projection method defines a relation that contains a vertical subset of Relation.
It is represented by symbol pi(π)

Example:

Consider the following table:

Stu_ID	Stu_Name	City
1	Priya	Hyderabad
2	Anjali	Mumbai
3	Rahul	Delhi
4	Rishi	Chennai

Here, the projection of Stu_Name and City will give

Π _{Stu_Name,City}(Students)

Stu_Name	City
Priya	Hyderabad
Anjali	Mumbai
Rahul	Delhi
Rishi	Chennai

Note:

For the next 4 operations

Consider table ‘A’ as:

Attribute1	Attribute2
1	1
1	2

Consider table ‘B’ as:

Attribute1	Attribute2
1	1
1	3

What is Database

Participation Constraint

DBMS Architecture

RDBMS

Cardinality

Union operation (υ)

The operation A ∪ B includes all tuples that are in tables A or in B.
Even if a tuple is present in both table A and table B the result will contain that tuple only once.

For a union operation to be valid, the following conditions must hold –

A and B must have the same number of attributes.
Attribute domains need to be compatible.
Duplicate tuples should be automatically removed.

A ∪ B gives

Attribute1	Attribute2
1	1
1	2
1	3

Set Difference (-)

It is represented by symbol ‘-‘ .
Operation A – B, is a relation which includes all tuples that are in A but not in B.

For a set difference operation to be valid, the following conditions must hold –

The attribute name of A has to match with the attribute name in B.
The two-operand relations A and B should be either compatible or Union compatible.

Example

A-B

Attribute1	Attribute2
1	2

Cartesian product(X)

Cartesian product is helpful to merge columns from two relations.
This operation is meaningful only when it is followed by other operations.

Example – Cartesian product

σ _{Attribute 2}= _'1'(A X B)

Output – The above example shows all rows from relation A and B whose Attribute 2 has value 1

Attribute1	Attribute2
1	1
1	1

Rename (ρ)

Rename operation can be used to rename a relation or an attribute of a relation.
It is represented by symbol ‘ρ’.

Syntax:

ρ(new_relation_name, old_relation_name)

Example:

ρ(Att_2, ∏(Attribute2)(B))

Output:

Attribute2
1
3

Prime Course Trailer

Related Banners

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription