Relational Algebra

 Relational Algebra is a procedural Query Language.

 PQL is an extension of SQL.
 PQL is used to implement query step by step.
 The main purpose of relational algebra is to define operator that take two
relation as an input and give single relation as output.

Types of Relational Algebra

1. Fundamental(Basic) Operation
 Selection
 Projection
 Union
 Set-Difference
 Cartesian Product(Cross)
 Rename

2. Derived Operation
 Intersection
 Join
 Division
 1) Selection –
Selection is an operator which is used to select or filter the row from the
relation(table) based on the condition.

Denoted by – Sigma (σ)

Notation – σ p(r)
where σ stands for Selection operator,
p stands for propositional logic i.e condition
r stands for Relation (table)
Example-

STUDENT
Name Roll no Address
Ram 10 Nagpur
Sham 11 Pune

Input- σ Name=”Ram”(STUDENT)

Output-
Name Roll no Address
Ram 10 Nagpur
 2) Projection-
Projection is an operator which is used to select or project the column from
the relation (table) based on the condition.

Denoted by – pi (π)

Notation – π A1, A2, ……..An (r)

where π stands for projection operator,
A stands for attribute names (column)
r stands for Relation (table)

Example-

STUDENT
Name Roll no Address
Ram 10 Nagpur
Sham 11 Pune

Input- π Name, Roll no(STUDENT)

Output-
Name Roll no
Ram 10
Sham 11
 3) Union
Union is an operator which is used to combine, merge rows from
two relations into single relation (table).

Denoted by –> ∪

Notation – R1 ∪ R2
where ∪ stands for union operator
R stands for Relation (table)

Example-

A
Name Roll no
Ram 10
Sham 11

B
Name Roll no
Ram 10
Sita 12

Input- A ∪ B

Output-
Name Roll no
Ram 10
Sham 11
Sita 12
 4) Set-Difference

 Set-Difference is an operator which is used eliminate the common

(matching) rows from both the relation (table) and select or filter
the remaining rows from first relation (table).
 Set-Difference defines the rows which are present in present one
relation (table) but which are not present in second relation.

Denoted by –> -
Notation – R1 - R2
where - stands for Set-Difference operator
R stands for Relation (table)

Example-

A
Name Roll no
Ram 10
Sham 11

B
Name Roll no
Ram 10
Sita 12

Input- A -B
Output-
Name Roll no
Sham 11
 5) Cross Product
 Cross Product is also known as Cartesian Product.
 It is used to combine each row from one relation with each row
from another relation (table).
 Let A & B be two relations, so the cross product between A x B
result in all the attributes of A followed by each attributes of B.

Denoted by –> x
Notation – R1 x R2
where x stands for Cross Product operator
R stands for Relation (table)

Example-
A
Name Age
Ram 20
Sham 21

B
Course Id Course
101 BCA
102 MCA

Input- A x B
Output-
Name Age Course Id Course
Ram 20 101 BCA
Ram 20 102 MCA
Sham 21 101 BCA
Sham 21 102 MCA
 6) Rename

 Rename is an operator which is used to rename the relation

name.
 Rename is used to assign new name to the existing relation (table).

Denoted by –> ρ
Notation – ρ new relation name (Existing relation name)
where ρ stands for Rename operator
R stands for Relation (table)

Example-

STUDENT
Name Age
Ram 20
Sham 21

Input- ρ PERSON (STUDENT)

Output-

PERSON
Name Age
Ram 20
Sham 21
 1) Intersection

 Intersection is an operator which is used to combine, merge

matching rows or common rows from two relations into single
relation (table).

Denoted by –> ∩

Notation – R1 ∩ R2
where ∩ stands for intersection operator
R stands for Relation (table)

Example-

A
Name Roll no
Ram 10
Sham 11

B
Name Roll no
Ram 10
Sita 12

Input- A ∩ B

Output-
Name Roll no
Ram 10
 2) Join
Join is an operator which is used to combine or merge or join
rows of two relation based on related relations (table).

Denoted by –> ⋈
Notation – R1 ⋈ R2
where ⋈ stands for join operator
R stands for Relation (table)

Example-

Employee
Id Name
10 Ram
11 Sham

Salary
Id Salary
10 10,000
11 12,000

Input- Employee ⋈ Salary

Output-

Id Name Salary
10 Ram 10,000
11 Sham 12,000
 Types of Join
1) Inner Join
An Inner Join returns only the matching rows between the two tables
based on a specified condition.

Types of Inner Join

 Natural Join
 Theta Join
 Equi Join

2) Outer Join
An Outer Join returns all the rows from one table and matching rows
from the other table based on a specified condition.

Types of Outer Join

 Left Join
 Right Join
 Full Join
 1) Natural Join
 It is defined when two relation have atleast one
column (attribute) common.
 It operates on matching attributes where values of
attributes are same in both relation.

Denoted by –> ⋈
Notation – R1 ⋈ R2
where ⋈ stands for join operator
R stands for Relation (table)

Example-

Employee
Id Name
10 Ram
11 Sham

Salary
Id Salary
10 10,000
11 12,000

Input- Employee ⋈ Salary

Output-

Id Name Salary
10 Ram 10,000
11 Sham 12,000
 2) Theta Join
 Theta Join is an operator which is used to join two relation
based on the condition.
 The condition may be >, <, =, >=, <=, ≠

Denoted by –> θ

Notation – R1 ⋈θ R2
where ⋈θ stands for theta join operator
R stands for Relation (table)
Example-
Car
Car Model Price
C1 5,00,000
C2 8,00,000
C3 12,00,000

Jeep
Jeep Model Price
J1 11,00,000
J2 15,00,000

Input- Car ⋈car.price > jeep.price Jeep

Output-

Car Model Price Jeep Model Price

C3 12,00,000 J1 11,00,000
 3) Equi Join
 Equi Join is an operator which is used to join two
relation based on the condition.
 The condition in equi join must be equal (=).

Denoted by –> =
Notation – R1 ⋈= R2
where ⋈= stands for theta join operator
R stands for Relation (table)
Example-
Car
Car Model Price
C1 5,00,000
C2 8,00,000
C3 12,00,000

Jeep
Jeep Model Price
J1 11,00,000
J2 12,00,000

Input- Car ⋈car.price = jeep.price Jeep

Output-

Car Model Price Jeep Model Price

C3 12,00,000 J2 12,00,000
 1) Left Outer Join
 Left Outer Join select or gives matching rows from both
relations as well as remaining all rows from left relation
but from right relation.

Denoted by –> ⟕

Notation – R1 ⟕ R2

where ⟕ stands for Left Outer Join operator

R stands for Relation (table)
Example-
Employee
Name Id
Ram 101
Sham 102
Hari 103

Salary
Name Salary
Ram 20,000
Sham 40,000
Ravi 30,000

Input- Employee ⟕ Salary

Output-
Name Id Salary
Ram 101 20,000
Sham 102 40,000
Hari 103 NULL
 2) Right Outer Join
 Right Outer Join select or gives matching rows from both
relations as well as remaining all rows from Right relation
but from left relation.

Denoted by –> ⟖
Notation – R1 ⟖ R2

where ⟖ stands for Right Outer Join operator

R stands for Relation (table)
Example-
Employee
Name Id
Ram 101
Sham 102
Hari 103

Salary
Name Salary
Ram 20,000
Sham 40,000
Ravi 30,000

Input- Employee ⟖ Salary

Output-
Name Id Salary
Ram 101 20,000
Sham 102 40,000
Ravi NULL 30,000
 3) Full Outer Join
 Full Outer Join select or gives matching rows from both
relations and remaining all rows from Left relation as well
as Right relation.
Denoted by –> ⟗
Notation – R1 ⟗ R2
where ⟗ stands for Full Outer Join operator
R stands for Relation (table)
Example-
Employee
Name Id
Ram 101
Sham 102
Hari 103

Salary
Name Salary
Ram 20,000
Sham 40,000
Ravi 30,000

Input- Employee ⟗ Salary

Output-
Name Id Salary
Ram 101 20,000
Sham 102 40,000
Hari 103 NULL
Ravi NULL 30,000
 RELATIONAL MODEL

 It is collection of table.
 Table contain data. Data stored in the database in tabular
format.
 Table represent data which is stored in the form of rows
and columns.
 In RDBMS we refer TABLE as a RELATION.

RDBMS

DATABASE
RDBMS DATABASE

TABLE TABLE TABLE TABLE

FI
FIELD FIELD FIELD FIELD

FIELD FIELD
 Example-

Consider a relation STUDENT with attributes Name, Id, Address, Age in

below table.
STUDENT
Name Id Address Age
Ram 101 Nagpur 20
Sham 102 Pune 21
Sita 103 22

To define the relational model we define some terminology like:-

1) Attributes- Attributes are the properties that define a relation.

Example-
Name Id Address Age

2) Relational Schema- Relational Schema defines name of relation

with its attributes.
Example- STUDENT(Name, Id, Address, Age)

3) Tuple- It defines each row in the relation is known as TUPLE. The

above relation contain 3 tuples.
Example- one of the tuple is given in this example below

Sham 102 Pune 21

4) Degree- The number of attributes in the relation is known as

degree of relation. The STUDENT relation has degree 4.
Name Id Address Age
5) Cardinality- The number of tuple(row) in the relation is known as
cardinality. The STUDENT relation has cardinality 3.
Example-
Ram 101 Nagpur 20
Sham 102 Pune 21
Sita 103 22

6) Column- It represent set pf values for particular attributes. The

relation contain 4 columns.
Example-
Name Id Address Age

7) NULL Value- The value which is not present or value is unknown

or unavailable is called as NULL Value.
Example-
Address of STUDENT having Id= 103 is NULL.
Sita 103 22

NULL