Sunday, 10 September 2017

Set difference operation in relational algebra in dbms

Set difference operation in relational algebra, purpose of set difference operation, example of set difference relational algebra operation, relational algebra in dbms, relational algebra equivalent SQL examples

Set Difference Operation

Operation
DIFFERENCE
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
DIFFERENCE operation finds the records that are in one relation but not in other. If we perform difference operation between relations R1 and R2, then the records that are in R1 but not in 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 only depositors and not borrowed from the bank, we can perform difference 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 indoor sports but not outdoor sports.
RA: regno (stu_indoor)) - (Π regno (stu_outdoor))
SQL: (SELECT regno FROM stu_indoor) MINUS (SELECT regno FROM stu_outdoor);
Result: Finds the tuples that are only in stu_indoor relation but not in stu_outdoor;
regno (stu_indoor)) - (Π regno (stu_outdoor))
Regno
BCE020
Example 2
Consider the tables given in example 1;
Find the regno of students who plays outdoor sports but not indoor sports.
RA: regno (stu_outdoor)) - (Π regno (stu_indoor))
SQL: (SELECT regno FROM stu_outdoor) MINUS (SELECT regno FROM stu_indoor);
Result: Finds the tuples that are only in stu_outdoor relation but not in stu_indoor;
regno (stu_outdoor)) - (Π regno (stu_indoor))
Regno
BME023
Example 3
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
BME023
Cricket
Find the regno of students who plays only badminton as indoor sports but not any of the outdoor sports.
RA: regnosport = ‘Badminton’(stu_indoor))) - (Π regno (stu_outdoor))
SQL: (SELECT regno FROM stu_indoor WHERE sport=’Badminton’) MINUS (SELECT regno FROM stu_outdoor);
Result: Finds the tuples that are only in stu_indoor relation that satisfy a condition but not in stu_outdoor;
regnosport = ‘Badminton’ (stu_indoor))) - (Π regno (stu_indoor))
Regno
BCE020
BEE001






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






No comments:

Post a comment

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents