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.

R-Schema(id, name)

R – Relation

S-Schema(id, name)

S – Relation

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 two-argument 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 –

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

Relation B

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 (R-Schema) and S(S-Schema) are relations and any attribute of S – Schema is also in R – Schema. The relation
R / S is a relation on schema R-Schema – S-Schema 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:
  1. tr[s] = ts[s]
  2. tr[R-S] = 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

Now, answer below questions related to join operation –

Question A. Find P ÷ Q if Q is

Question B. Find P ÷ Q if Q is

Question C. Find P ÷ Q if Q is

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

Salary relation

When we perform join operation on Relation Employee and Salary we get –

Join Operation Result (Employee ⋈ Salary) is

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 (doc-id, name)

Permanent-doc (doc-id, address, birthdate, sal)

Doctor relation

It contains id and name of the doctor.

Permanent Document relation

It contains id, address, birth date and salary of doctors.

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 Natural-Join operation as follows:

= (Doctors ⋈ Permanent-Doc)

The result of above is shown below :

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 Permanent-doc. 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.

1. Left outer join
2. Right outer join
3. 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 Permanent-Doc relation –

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 Permanent-Doc relation –

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 Permanent-Doc relation –

That’s end of our post on relational algebra operations in dbms.