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 image

Basic Operators in DBMS

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_IDStu_NameCity
1PriyaHyderabad
2AnjaliMumbai
3RahulDelhi
4RishiChennai

Here, the projection of Stu_Name and City will give

Π Stu_Name,City (Students)
Stu_NameCity
PriyaHyderabad
AnjaliMumbai
RahulDelhi
RishiChennai

 

Note: 

For the next 4 operations

Consider table ‘A’ as:

Attribute1Attribute2
11
12

Consider table ‘B’ as:

Attribute1Attribute2
11
13
DBMS desktop gif
DBMS mobile adv gif

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

Attribute1Attribute2
11
12
13

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
Attribute1Attribute2
12

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

Attribute1Attribute2
11
11

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

Get Prime
DBMS Tutorial banner