In the previous post, we have seen fundamental operations in relational algebra. Now, we will see some additional relational algebra operations in dbms. These additional operations (set intersection, assignment, natural join operations, left outer join, right outer join and full outer join operation etc.) can be seen expressed using fundamental operations. But, These additional operations have been created just for convenience. We will see how these additional relational algebra operations in dbms can be represented using fundamental operations.
Additional Operations in Relational Algebra
Additional operations in relational algebra are as below –
 Set Intersection operation
 Division operation
 Assignment operation
 Natural Join operation
 Left Outer Join Operation
 Right Outer Join Operation
 Full Outer Join Operation
Tables used for Examples
Note – We are going to use below relation tables to show different dbms relational algebra examples. These tables will be used for different examples shown in this post.
RSchema(id, name)
R – Relation
Id  Name 

101  Raj 
102  Rahul 
103  Sachin 
104  Anil 
105  Prasad 
SSchema(id, name)
S – Relation
Id  Name 

101  Raj 
104  Anil 
106  Kapil 
107  Sumit 
The first additional relational algebra operations in dbms is Set intersection operation –
(1) Set Intersection Operation (∩)
Let’s say you have two relation tables A and B. Now, what if you are asked to find out the common tuples in tables A and B. How can you achieve it?
To make this task easier, there is an operation defined in relational algebra. This is called set intersection operation.
Set Intersection operation selects common tuples from the two relations. It is denoted by (∩)
Necessary condition to apply set intersection operation –
For set intersection operation, the twoargument relation must be compatible relation.
So, if R and S are two relations, P = R ∩ S has tuples drawn from R and S, such that each tuple in P is in R and S.
Notation of Set Intersection Operation
Where, P = result of set intersection operation,
R and S are relations on which set intersection operation has been applied.
Consider R and S relation shown at start of the post,
Result after applying set intersection operation in R and S relations –
Id  Name 

101  Raj 
104  Anil 
It means only row (101, Raj) and (104, Anil) are common in two relations (R and S).
Exercises –
Question A. Given two relations A and B –
Relation A
Id  Name 

101  Rahul 
104  Anil 
Relation B
Id  Name 

101  Anil 
104  Anil 
Find all tuples that are common in relations A and B.
Question B. Find names of customers having an account and loan (using Borrower and Deposit, given at start of this post).
The second additional relational algebra operations in dbms is Division operation –
(2) Division Operation (÷)
Division operation is denoted by ÷ sign. It is useful in queries, which involve the phrase “for all objects having all the specified properties”.
Let R (RSchema) and S(SSchema) are relations and any attribute of S – Schema is also in R – Schema. The relation
R / S is a relation on schema RSchema – SSchema i.e. on the schema containing all the attributes of Schema R that are not in Schema S.
A tuple t is in r ÷ s if and only if both the conditions hold.
 T is in πR – S (r)

For every tuple ts in S, there is a tuple tr in R satisfying both of the following:
 tr[s] = ts[s]
 tr[RS] = t
Or,
In other words, we can say
Division operation R ÷ S can only be applied if and only if –
 Attributes of S is proper subset of Attributes of R.
 The relation returned by division operator will have attributes = (All attributes of R – All Attributes of S)
 The relation returned by division operator will return those tuples from relation R which are associated to every S’s tuple.
Notation of Division Operation
Where,
P is result we get after applying division operator,
R and S stands for relation (name of the tables) on which division operation is applied.
Exercises on Division Operation
Let say Relation P is
A  B 

A1  B1 
A1  B2 
A2  B1 
A3  B1 
A4  B2 
A5  B1 
A5  B2 
Now, answer below questions related to join operation –
Question A. Find P ÷ Q if Q is
B 

B1 
B2 
Question B. Find P ÷ Q if Q is
B 

B1 
Question C. Find P ÷ Q if Q is
B 

The third additional relational algebra operations in dbms is Assignment operation –
(3) Assignment operator (←)
Now we will see what is assignment operator in relation algebra.
Suppose you wish to assign result of an expression into a relation R. How would you denote it?
For such work, we use assignment operator (←).
Notation of Assignment Operator
or,
Where,
R is relation,
r stands for relation variable.
E is Expression whose result we wish to assign to relation variable R.
The result of the expression to the right hand side of ← is assigned to relation variable on the left side of ←. The
relation variable may be used in subsequent expressions.
R1 ← πname(Customer)
R2 ← πname(Employee)
R = R1 – R2
The fourth additional relational algebra operations in dbms is Natural Join operation –
(4) Natural Join Operation (⋈)
It is denoted by the join symbol ⋈. The natural join operation forms a Cartesian product of its two arguments,
performs selection forcing equality on those attributes that appear in both relation schema, and finally removes
duplicate attributes. Natural join can be defined as:
Notation of Natural Join Operation
Where,
Pis resultant relation after applying natural join operation on R and S,
R and S stands for relation (name of the table).
Employee relation
Id  Name 

101  Sachin 
103  Rahul 
104  Kapil 
107  Ajay 
Salary relation
Id  Salary 

101  65000 
103  35000 
104  22000 
107  21910 
When we perform join operation on Relation Employee and Salary we get –
Join Operation Result (Employee ⋈ Salary) is
Id  Name  Salary 

101  Sachin  65000 
103  Rahul  35000 
104  Kapil  22000 
107  Ajay  21910 
The fifth additional relational algebra operations in dbms is Outer Join operation –
(5) Outer Join Operation
In this section, we will deal with outer join operations, i.e. why do we need outer join operation, when to use it etc.
The outer join operation is an extension of the join operation to deal with missing information.
Suppose we have the relations with the following schemas which contain data on permanent doctors.
Doctors (docid, name)
Permanentdoc (docid, address, birthdate, sal)
Doctor relation
It contains id and name of the doctor.
Docid  Name 

1  Anil 
2  Ganesh 
3  Sunil 
4  Reena 
Permanent Document relation
It contains id, address, birth date and salary of doctors.
Docid  Address  Birthdate  Sal 

1  Pune  12/12/1970  20000 
2  Mumbai  12/1/1970  20000 
3  Nagpur  5/1/1970  30000 
4  Nashik  5/1/1979  20000 
Suppose we wish to generate a single relation with all the information (Name, address, birthdate, sal) about Permanent doctors.
One approach would be to use NaturalJoin operation as follows:
= (Doctors ⋈ PermanentDoc)
The result of above is shown below :
Docid  Name  Address  Birthdate  Sal 

1  Anil  Pune  12/12/1970  20000 
2  Ganesh  Mumbai  12/1/1970  20000 
4  Reena  Nashik  5/1/1979  20000 
It is seen that, we have information about Doctor with docid=3 and his address, birthdate, sal. Since the tuple describing docid=3, is absent in Permanentdoc. Similarly we have lost the name of docid=5, since the tuple describing docid=5, is absent from Doctors relation.
The outer join operation can be used to avoid this loss of information. There are 3 forms of the operation.
 Left outer join
 Right outer join
 Full outer join
All these 3 forms of join compute the join and add extra tuples to the result of join
Now, we will study about types of outer join operation –
5.1 Left Outer Join Operation (⟕)
The left outer join takes all tuples in the left relation that did not match with any tuple in the right relation pads the tuples with NULL values for all other attributes from the right relation, and adds them to the result of natural join.
Notation of Left Outer Join Operation
Where,
Pis resultant relation after applying natural join operation on R and S,
R and S stands for relation (name of the table).
Result of the left outer join operation when applied on Doctors and PermanentDoc relation –
Docid  Name  Address  Birthdate  Sal 

1  Anil  Pune  12/12/1970  20000 
2  Ganesh  Mumbai  12/1/1970  20000 
4  Reena  Nashik  5/1/1979  20000 
3  Sunil  NULL  NULL  NULL 
5.2 Right Outer Join Operation (⟖)
The right outer join is symmetric with the left outer join. Tuples from the right relation that did not match any from the left relation are padded with Nulls and added to the result of Natural join.
Notation of Right Outer Join Operation
Where,
Pis resultant relation after applying natural join operation on R and S,
R and S stands for relation (name of the table).
Result of the right outer join operation when applied on Doctors and PermanentDoc relation –
Docid  Name  Address  Birthdate  Sal 

1  Anil  Pune  12/12/1970  20000 
2  Ganesh  Mumbai  12/1/1970  20000 
4  Reena  Nashik  5/1/1979  20000 
5  NULL  Nagpur  5/1/1970  30000 
5.3 Full Outer Join Operation
The full outer join does both of those operations, padding tuples from left relation that did not match any right relation, as well as tuples from the right relation that did not match any from the left relation and adding them to the result of join.
Notation of Right Outer Join Operation (⟗)
Where,
Pis resultant relation after applying natural join operation on R and S,
R and S stands for relation (name of the table).
Result of the full outer join operation when applied on Doctors and PermanentDoc relation –
Docid  Name  Address  Birthdate  Sal 

1  Anil  Pune  12/12/1970  20000 
2  Ganesh  Mumbai  12/1/1970  20000 
4  Reena  Nashik  5/1/1979  20000 
3  Sunil  NULL  NULL  NULL 
5  NULL  Nagpur  5/1/1970  30000 
That’s end of our post on relational algebra operations in dbms.