Module 3�Relational Algebra

Ms. Khushbu Tikhe

Assistant Professor

Department of Electronics and Computer Science

SLRTCE, Mira Road

​

Relational Algebra

• Relational algebra is a procedural query language, which is useful for representing query execution plan.
• It uses operators to perform queries.
• They accept relations as their input and yield relations as their output.
• Relational algebra is performed recursively on a relation and intermediate results are also considered relations.

​

• The fundamental operations of relational algebra are as follows −

Unary Relational Operation

• Select operation
• Project operation
• Rename operation

Relational Algebra

Set Theory Operation

• Union Operation
• Different Operation
• Intersection Operation

Binary Operation

• Cartesian product Operation
• Join Operation
• Division Operation

​

​

Employ Table-2

​

Student table-1

​

Select Operation (σ)

• It selects tuples that satisfy the given predicate from a relation.
• Notation −
• Where σ stands for selection predicate and r stands for relation. p is prepositional logic formula which may use connectors like and, or, and not. These terms may use relational operators like − =, ≠, ≥, < , >, ≤.
• Syntax:

​

• For example: Extract students whose age is greater than 18 from STUDENT relation given in Table 1

​

​

​

​

​

​

​

​

Projection Operator (∏)

• Project operation is used to project only a certain set of attributes of a relation. In simple words, If you want to see only the names all of the students in the Student table, then you can use Project Operation.
• It will only project or show the columns or attributes asked for, and will also remove duplicate data from the columns.
• Syntax:
• Example: Extract ROLL_NO and NAME from STUDENT relation given in Table 1

Rename Operation (ρ)

• This operation is used to rename the output relation for any query operation which returns result like Select, Project etc. Or to simply rename a relation(table)
• 'rename' operation is denoted with small Greek letter rho ρ.
• Syntax:

​

• To rename STUDENT relation to STUDENT1, we can use rename operator like:

​

​

• If you want to create a relation STUDENT_NAMES with ROLL_NO and NAME from STUDENT, it can be done using rename operator as:

SET Operation

• SQL SET operator allow combining result from two or more SELECT statement or combine result set of multiplies queries.

​

Union Operation (∪)

• This operation is used to fetch data from two relations(tables) or temporary relation(result of another operation).
• Union on two relations R1 and R2 can only be computed if R1 and R2 are union compatible (These two relation should have same number of attributes and corresponding attributes in two relations have same domain) .
• Union operator when applied on two relations R1 and R2 will give a relation with tuples which are either in R1 or in R2. The tuples which are in both R1 and R2 will appear only once in result relation.
• Syntax:

​

​

Example: Find person who are either student or employee, we can use Union operator like:

Different Operation(−)

• This operation is used to find data present in one relation and not present in the second relation.
• This operation is also applicable on two relations, just like Union operation.
• Different Operation on two relations R1 and R2 can only be computed if R1 and R2 are union compatible.
• Minus operator when applied on two relations as R1-R2 will give a relation with tuples which are in R1 but not in R2.
• Syntax: Relation1 - Relation2
• Example: Find person who are student but not employee

STUDENT - EMPLOYEE

Intersection Operation(∩)

• Intersection on two relations R1 and R2 can only be computed if R1 and R2 are union compatible (These two relation should have same number of attributes and corresponding attributes in two relations have same domain).
• Intersection operator when applied on two relations as R1∩R2 will give a relation with tuples which are in R1 as well as R2.
• Syntax: Relation1 ∩ Relation2
• Example: Find a person who is student as well as employee-

STUDENT ∩ EMPLOYEE

• Table 4

​

• Table 3

Binary Operation�Cartesian product Operation/Cross Product(X)

• Cross product is used to join two relations. For every row of Relation1, each row of Relation2 is concatenated. If Relation1 has m tuples and and Relation2 has n tuples, cross product of Relation1 and Relation2 will have m X n tuples.
• This is used to combine data from two different relations(tables) into one and fetch data from the combined relation.
• Syntax: Relation1 X Relation2
• To apply Cross Product on STUDENT relation given in Table 1 and STUDENT_SPORTS relation given in Table 3,
• Example: STUDENT X STUDENT_SPORTS

​

Example

• Cross product between two relations let say A and B, so cross product between A X B will results all the attributes of A followed by each attribute of B. Each record of A will pairs with every record of B.

Division Operator (÷)

• Division operator A÷B can be applied if and only if, Attributes of B is proper subset of Attributes of A.
• The relation returned by division operator will have

attributes = (All attributes of A – All Attributes of B)

• The relation returned by division operator will return those tuples from relation A which are associated to every B’s tuple.
• Consider the relation STUDENT_SPORTS and ALL_SPORTS given in Table 3 and Table 4 above.
• To apply division operator as

STUDENT_SPORTS÷ ALL_SPORTS

The operation is valid as attributes in ALL_SPORTS is a proper subset of attributes in STUDENT_SPORTS.

The attributes in resulting relation will have attributes {ROLL_NO,SPORTS}-{SPORTS}=ROLL_NO

The tuples in resulting relation will have those ROLL_NO which are associated with all B’s tuple {Badminton, Cricket}. ROLL_NO 1 and 4 are associated to Badminton only. ROLL_NO 2 is associated to all tuples of B. So the resulting relation will be:

Join Operation (⋈)

• Join Operation help us to retrive data from multiple table or relations.
• A Join operation combines related tuples from different relations, if and only if a given join condition is satisfied.
• Conditional Join is used when you want to join two or more relation based on some conditions. Example: Select students whose ROLL_NO is greater than EMP_NO of employees
• in conditional join we can specify the any condition such as greater than, less than, not equal
• Example:Operation: (EMPLOYEE ⋈ SALARY)

• Employee

​

​

​

​

​

• Salary

​

• Operation: (EMPLOYEE ⋈ SALARY)

​

Join Operation

Various forms of join operation are:

Inner Joins:

• Theta join
• EQUI join
• Natural join

​

Outer Join:

• Left outer join
• Right outer join
• Full outer join

​

Inner Join:

• In an inner join, only those tuples that satisfy the matching criteria are included, while the rest are excluded. Let's study various types of Inner Joins:

Theta Join:

• The general case of JOIN operation is called a Theta join. It is denoted by symbol θ
• THETA JOIN allows you to merge two tables based on the condition represented by theta. Theta joins work for all comparison operators.
• Example

​

• Theta join can use any conditions in the selection criteria.

​

Example

Student_Detail −

​

Natural join

• Natural join between two or more relations will result set of all combination of tuples where they have equal common attribute.
• While applying natural join on two relations, there is no need to write equality condition explicitly. Natural Join will also return the similar attributes only once as their value will be same in resulting relation.

Student

• Example: Select students whose ROLL_NO is equal to ROLL_NO of STUDENT_SPORTS as:

STUDENT⋈STUDENT_SPORTS

• Natural Join is by default inner join because the tuples which does not satisfy the conditions of join does not appear in result set.
• e.g.; The tuple having ROLL_NO 3 in STUDENT does not match with any tuple in STUDENT_SPORTS, so it has not been a part of result set.

Equi join

• It is also known as an inner join. It is the most common join.
• It is based on matched data as per the equality condition.
• The equi join uses the comparison operator(=).
• Example:

​

• CUSTOMER RELATION

​

​

​

​

​

• PRODUCT

​

​

• Input:

CUSTOMER ⋈ PRODUCT

Outer Join:

• The outer join operation is an extension of the join operation. It is used to deal with missing information.
• An outer join is basically of three types:

Left outer join

Right outer join

Full outer join

Example:

• Input:
• (EMPLOYEE ⋈ FACT_WORKERS)

Outer Join: a. Left Outer Join(⟕)

• When applying join on two relations R and S, some tuples of R or S does not appear in result set which does not satisfy the join conditions.
• Left outer join contains the set of tuples of all combinations in R and S that are equal on their common attribute names.
• In the left outer join, tuples in R have no matching tuples in S.
• It is denoted by ⟕.
• Example : Using the above EMPLOYEE table and FACT_WORKERS table

Right Outer Join(⟖)

• When applying join on two relations R and S, some tuples of R or S does not appear in result set which does not satisfy the join conditions.
• Right outer join contains the set of tuples of all combinations in R and S that are equal on their common attribute names.
• In right outer join, tuples in S have no matching tuples in R.
• It is denoted by ⟖.
• Example: Using the above EMPLOYEE table and FACT_WORKERS Relation
• Input: EMPLOYEE ⟖ FACT_WORKERS

​

​

Full Outer Join(⟗)

• Full outer join is like a left or right join except that it contains all rows from both tables.
• In full outer join, tuples in R that have no matching tuples in S and tuples in S that have no matching tuples in R in their common attribute name.
• It is denoted by ⟗.
• Example: Using the above EMPLOYEE table and FACT_WORKERS table
• Input: EMPLOYEE ⟗ FACT_WORKERS
• Output:

Summary

Summary

Summary

Reference

• https://www.tutorialspoint.com/dbms/relational_algebra.htm
• https://www.studytonight.com/dbms/relational-algebra.php
• https://www.geeksforgeeks.org/extended-operators-in-relational-algebra/
• https://www.geeksforgeeks.org/basic-operators-in-relational-algebra-2/?ref=lbp
• https://www.geeksforgeeks.org/introduction-of-relational-algebra-in-dbms/
• https://www.javatpoint.com/dbms-join-operation