Set intersection in relational algebra, examples for set intersection, equivalent relational algebra expression for intersection operator, equivalent SQL queries for relational algebra intersection operator
Set Intersection
Operation

INTERSECTION


Type of operation

Binary


Syntax

R_{1}
∩ R_{2}
Example: DEPOSITOR ∩ BORROWER
(Expression_{1})
∩
(Expression_{2})
Example: Π _{regno}
(student) ∩ Π _{regno}
(sub_regd)


Rules to be satisfied

To
perform difference operation, the following conditions must hold;
1.
Both the relations R_{1} and R_{2} (or the result of
expression 1 and expression 2) must have the same number of attributes. That
is, Arity should be same.
2.
The domain of i^{th} attribute of R_{1} and i^{th}
attribute of R_{2} must be same for all i.


Function

INTERSECTION
operation finds the records that are in both relations. If we find
intersection between relations R_{1} and R_{2}, then the
records that are in both R_{1} and R_{2} become part of new
result relation.


Purpose

To
perform set operation. For example, assume that you have two relations as
depositor and borrower. If we would like to find the customers who are both depositors
and borrowers from the bank, we can perform intersection between these two
tables.


Example 1

The
table STU_INDOOR records information about the indoor sports entry of
students. The table STU_OUTDOOR stores information about the outdoor sports
entry of students.
Find
the regno of students who plays both indoor and outdoor sports.
RA: (Π _{regno} (stu_indoor)) ∩ (Π _{regno} (stu_outdoor))
SQL: (SELECT
regno FROM stu_indoor) INTERSECT (SELECT regno FROM stu_outdoor);
Result: Finds the tuples
that are in both stu_indoor and stu_outdoor relations;


Example 2

Consider
the tables below;
Find
the regno of students who plays both badminton and any of the outdoor sports.
RA: (Π _{regno} (σ_{sport = ‘Badminton’}(stu_indoor)))
∩ (Π _{regno} (stu_outdoor))
SQL: (SELECT
regno FROM stu_indoor WHERE sport=’Badminton’) INTERSECT (SELECT regno FROM stu_outdoor);
Result: Finds the tuples
that are in both in stu_indoor and stu_outdoor relations but plays only
badminton as indoor sport;
Final result

Note:
Intersection can be expressed using set
difference operator as follows;
R1 ∩ R2 = R1 – (R1 –
R2)
*************
Go to Relational Algebra Operations page
No comments:
Post a Comment