Advanced Database Management System - Tutorials and Notes: 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)) U (Π age (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)) U (Π regno (sub_regd)) Regno BIT001 BIT023
You can observe from the result, that the duplicate values are eliminated.

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