Additional Relational Algebra Operations in DBMS

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
Id Name
101 Raj
102 Rahul
103 Sachin
104 Anil
105 Prasad

S-Schema(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 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
P = R ∩ S

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 (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
P = R ÷ S

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
Relational Variable ← Expression.

or,

R ← E.

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 = R1R2

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
P = R ⋈ S

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

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

Doctor relation

It contains id and name of the doctor.

Doc-id Name
1 Anil
2 Ganesh
3 Sunil
4 Reena

Permanent Document relation

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

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

= (Doctors ⋈ Permanent-Doc)

The result of above is shown below :

Doc-id 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 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
P = R ⟕ S

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 –

Doc-id 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
P = R ⟖ S

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 –

Doc-id 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 (⟗)
P = R ⟗ S

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 –

Doc-id 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.

Leave a Reply