Relational Algebra in DBMS

February 1, 2023

What is Relational Algebra in DBMS?

On this page, we will learn about Relational Algebra in DBMS.

Relational Algebra in DBMS is a query language which is procedural in nature, both of its input and output are relations. The theoretical foundations of relational databases and SQL is provided by Relational Algebra.

Relational Algebra in DBMS img

Relational Algebra in DBMS

Following operations can be applied via relational algebra –

  1. Select
  2. Project
  3. Union
  4. Set Different
  5. Cartesian product
  6. Rename
Relational Algebra in DBMS

Select Operation (σ)

Use – Fetching rows (tuples) from a table, which satisfied a given condition.

Notation – σp(r)

Breakdown –

  1. σ represents select predicate
  2. rfor relation​
  3. pfor proposition logics like – − =, ≠, ≥, < ,  >,  ≤. with us of connectors like OR, AND, or NOT

Like we used in the earlier example in the image above, of selecting rows for people who had age > 25 and give results in Fname format.

Employee Table in DBMS

Example 1

  • Query – σage > 25 (Student)
    • σ(select predicate)
    • r(relation​) – Employee
    • p(proposition logic) – age >25
    • Result – returning the list of students with age greater than 25.
    • In SQL – select * from Student where age > 25
Example 1 in DBMS

Example 2

    • Queryσage > 25 and Fname = 'Arya' (Student)
      • σ(select predicate)
      • r(relation​) – Employee
      • p(proposition logic) – age >25 and Fname = ‘Arya’
      • Result – returning the list of students with age greater than 25 and Fname equals to arya
      • In SQL – select * from Student where age > 25 & Fname = ‘Arya’
Example 2 in DBMS

Example 3

  • QueryσLname = 'Stark' (Student)
    • σ(select predicate)
    • r(relation​) – Employee
    • p(proposition logic) – Lname = ‘Stark’
    • Result – returning the list of students Lname = ‘Stark’
    • In SQL – select * from Student where Lname = ‘Stark’
Example 3 in DBMS

Project Operation (∏)

Use – Fetching on specific columns from a table

Notation – ∏ A1,A2,An(r)

Breakdown – 

  1. ∏ represents Project predicate
  2. rfor relation​
  3. A1, A2, A3for selection from columns for projection

Example 1

  • QueryσLname = 'Stark' (Student)
    • σ(select predicate)
    • r(relation​) – Employee
    • p(proposition logic) – Lname = ‘Stark’
    • Result – returning the list of students Lname = ‘Stark’
    • In SQL – select * from Student where Lname = ‘Stark’
ID Fname Lname Age
1 Jon Stark 25
2 Arya Stark 28
3 Bran Stark 26
4 Sansa Stark 27

Union Operation (∪)

Union Operation performs as expected, it essentially finds the union of the tables included in the union i.e finds only the unique rows/tuples from multiple tables, removing the duplications.

Use – Fetching union rows (tuples), i.e unique rows (tuples) from multiple tables removing the duplications

Notation – A(∪)B

Breakdown – 

  1. (∪) represents Union Operation
  2. A and B are the tables
Union Operation in DBMS
Example 1
  • QueryA(∪)B
    • (∪)(Union Operation)
    • A and BTable
    • In SQL – SELECT * FROM A UNION SELECT * FROM B;
The following table is removed as it is present in both table A and table B and union operation only has unique and non duplicates.

Resultant

1 Jon Stark 25

Resultant

Table A
ID FNAME LNAME AGE
1 John Stark 25
2 Arya Stark 28
3 Bran Stark 26
4 Sansa Stark 27
5 Cersie Lannister 40
6 Jamie Lannister 40
7 Tywin Lannister 65
DBMS desktop gif
DBMS mobile adv gif

What is Database

Participation Constraint

DBMS Architecture

RDBMS

Cardinality

Intersection Operation (∩)

Intersection operation works simply by helping to find the rows (tuples) that are common i.e. exists in both (all) the tables involved in the intersection operation.

Use – Fetching union rows (tuples), i.e common rows (tuples) from multiple tables and only the rows that exist in both (all) tables involved in the operation.

Notation – A(∩)B

Breakdown – 

  1. (∩) represents Intersection Operation
  2. A and B are the tables

Table A

ID Fname Lname Age
1 Jon Stark 25
2 Arya Stark 28
3 Bran Stark 26
4 Sansa Stark 27

Table B

ID Fname Lname Age
1 Jon Stark 25
5 Cersie Lannister 40
6 Jamie Lannister 40
7 Tywin Lannister 65
Example 1
  • QueryA(∩)B
    • A and BTables
    • (∩)(Intersection Operation)
    • In SQL – SELECT * FROM A INTERSECT SELECT * FROM B;
Finding the common row from both table A and B will be –

Resultant

ID Fname Lname Age
1 Jon Stark 25 
∏ player (Cricket) ∪ ∏ player (Football)
The following gives the result for selecting the people who either play only cricket or only play football or play both of them.

Set Difference (−)

Use – Fetching rows from which are present in relation but not the other one. Example players who play cricket but don’t play football.

Notation – A - B

Breakdown – 

  1. - represents Set Difference Operation
  2. A and B are the tables (relations)

Table Plays Cricket

ID Fname
1 Jon
2 Arya
3 Bran
4 Sansa

Table Football

ID Fname
1 Jon
2 Arya
5 Cersi
6 Jamie
Example 1
  • QueryA - B
    • A and BTables A: Plays Cricket B: Plays Football
    • -(Intersection Operation)
    • In SQL – SELECT * FROM A INTERSECT SELECT * FROM B;

Resultant A - B

ID Fname
3 Bran
4 Sansa

Resultant B - A

ID Fname
5 Cersie
6 Jamie

Cartesian Product (Χ)

Use – Merging columns from two different relations, i.e. combining them together. It is mostly not a suitable and meaningful data representation if we just calculate the cartesian product alone. To get meaningful data we must follow the same by subsequent operations. We will understand more about this in detail.

Notation – A X B

Breakdown – 

  1. X represents Cartesian Product
  2. A and B are the tables (relations)

Facebook

IDFnameCountry
3BranUK
4SansaUK
5JonIndia

Twitter

IDFnameCountry
5CersieIndia
6JamieAustralia
5SamwellIndia

Imaging that we have list of all Facebook and Twitter users and we want to find out the unique list of people from both of them who live in India.

σFname = 'Country'(Facebook Χ Twitter)

Result

Fname
Jon
Samwell

Rename Operation (ρ)

Use – When we find the result of a some relational operation they are just displayed !. However, we may want to store them in a new relation with a name that we can further use. For the same we use rename operation.

Don’t get confused with the name, the name might give idea that it is done to rename an existing table. However, this is used to store results of relation in a new named table.

Notation – ρx (E)

Breakdown – 

  1. ρ i.e. rho represents rename operation.
  2. x is the new name of the resultant table,
  3. E is the expression used for result.

Use – ρ users (E =A (∩) B)

The results of expression will be stored in new table names as users.

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

Get over 200+ course One Subscription

Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others

Get Prime
Course List
Course List

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout
Checkout