Please login

Prime

Prepinsta Prime

Video courses for company/skill based Preparation

(Check all courses)
Get Prime Video
Prime

Prepinsta Prime

Purchase mock tests for company/skill building

(Check all mocks)
Get Prime mock

Basic Operators in DBMS

Basic Operators in DBMS

 

In this article, 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 (ρ)
Learn more about Relational Algebra here on this page.

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

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
Attribute 1  Attribute 2
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
Attribute 1 Attribute 2
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
Attribute 1 Attribute 2
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