Advanced Database Management System - Tutorials and Notes: set intersection in relational algebra in dbms

## Search Engine

Please visit, subscribe and share 10 Minutes Lectures in Computer Science

## Sunday, 10 September 2017

### set intersection in relational algebra in dbms

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
R1 R2
Example: DEPOSITOR BORROWER
(Expression1) (Expression2)
Example: Π regno (student) Π regno (sub_regd)
Rules to be satisfied
To perform difference operation, the following conditions must hold;
1. Both the relations R1 and R2 (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 ith attribute of R1 and ith attribute of R2 must be same for all i.
Function
INTERSECTION operation finds the records that are in both relations. If we find intersection between relations R1 and R2, then the records that are in both R1 and R2 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.
 STU_INDOOR Regno Sport 3 records BIT001 Chess BIT023 Carrom BCE020 Badminton

 STU_OUTDOOR Regno Sport 3 records BIT001 Soccer BIT023 Soccer BME023 Cricket
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;
 (Π regno (stu_indoor)) ∩ (Π regno (stu_outdoor)) Regno BIT001 BIT023
Example 2
Consider the tables below;

 STU_INDOOR Regno Sport 4 records BIT001 Chess BIT023 Carrom BCE020 Badminton BEE001 Badminton

 STU_OUTDOOR Regno Sport 3 records BIT001 Soccer BIT023 Soccer BCE020 Cricket
Find the regno of students who plays both badminton and any of the outdoor sports.
RA: (Π regnosport = ‘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;
 (Π regno (σsport = ‘Badminton’ (stu_indoor))) Regno BCE020 BEE001
Final result
 (Π regno (σsport = ‘Badminton’ (stu_indoor))) ∩ (Π regno (stu_indoor)) Regno BCE020

Note:
Intersection can be expressed using set difference operator as follows;
R1 ∩ R2 = R1 – (R1 – R2)

*************