Saturday, 9 September 2017

set union operation in relational algebra and sql

Union set operation in relational algebra, purpose of set union operation, example of set union relational algebra operation, relational algebra in dbms

Set Union Operation


Operation
UNION
Type of operation
Binary
Syntax
R1 U R2
     Example: DEPOSITOR U BORROWER
(Expression1) U (Expression2)
     Example: Π regno (student) U Π regno (sub_regd)
Rules to be satisfied
To perform union 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
UNION operation joins two relations vertically. That is, if we perform union between relations R1 and R2, then the records of R1 and R2 become part of new relation. And, if any duplicate records formed during this process, they will be eliminated.
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 deposited and borrowed from the bank, we can perform union between these two tables.
Example 1

AGE_GROUP
Min_Age
Max_Age
Age_Group
18
30
18-30
31
60
31-60
61
100
Above 60

CUSTOMER
Customer_Name
Age
Rahul   
35
Meera
25
Steve
62
RA: (Π max_age (age_group)) Uage (customer))
SQL: (SELECT max_age FROM age_group) UNION (SELECT age FROM customer);
Result: Joins the tuples of results of two expressions into one relation as follows;

max_age (age_group)) Uage (customer))
max_age
30
60
100
35
25
62
 
See the result heading. It is max_age, the attribute of left hand side expression.

Example 2

STUDENT
Regno
Name
Age
Phone
2 records
BIT001
Ram
15
8652398452
BIT023
Selvi
17
7894562310

SUB_REGD
Regno
Subject
3 records
BIT001
Physics
BIT023
Computer
BIT023
OS

RA: (Π regno (student)) Uregno (sub_regd))
SQL: (SELECT regno FROM student) UNION (SELECT regno FROM sub_regd);
Result: Joins the tuples of two relations student and sub_regd into one relation as follows;
regno (student)) Uregno (sub_regd))
Regno
BIT001
BIT023
You can observe from the result, that the duplicate values are eliminated.


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






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