Set Operations in DBMS

In this Article you know about Set Operations in DBMS

Basic Set Operation

These are the binary operations; i.e., each is applied to two sets or relations. These two relations should be union compatible except in case of Cartesian Product. Two relations R (A1, A2, …, An) and S (B1, B2,……,B.) are said to be union compatible if they have the same degree n and domains of the corresponding attributes are also the same i.e Domain (Ai) = Domain (Bi) for 1<=i<=n.

UNION

If R1 and R2 are two union compatible relations then R3 = R1 ⋃ R2 is the relation containing tuples that are either in R1 or in R2 or in both. In other words, R3 will have tuples such that R3 = {t I R1∋ t v R2 ∋ t}.

Example :- 1

R3 = R1 ⋃ R2 is

Note :-

1) Union is a Commutative Operation , i.e ,

R ∪ S = S ∪ R is

2) Union is an associative operation , i.e

R ∪ ( S ∪ T ) = ( R ∪ S ) ∪ T

Intersection

If R1 and R2 are two union compatible functions or relations, then the result of R3 = R1 ∩ R2 is the relation that includes all tuples that are in both the relations In other words, R3will have tuples such that R3 = {t|R1 ∋ ⋀ t R2 ∋ t}.

Example 2 :-

R3 = R1 ∩ R2 is

Note :-

1) Intersection is a commutative operation , i,e

R1 ∩ R2 = R2 ∩ R1

2) Intersection is an associative operation , i,e Set Operations in DBMS

R1 ∩ ( R2 ∩ R3 ) = ( R1 ∩ R2 ) ∩ R3

Set Difference

If R1 and R2 are two union compatible relations or relations then result of R3 = R1-R2 is the relation that includes only those tuples that are in R1 but not in R2. in other words, R3 will have tuples such that R3 = {t|R1∋t 1 ∧ t ∉ R2}. Set Operations in DBMS

Example :- 3

R1-R2 =

R2-R1 =

Note :-

1 ) Defference operation is not commutative , i,e

R1 -R2 ≠ R2-R1

2) Difference operation is not associative , i,e

R1 – ( R2-R3 ) ≠ (R1-R2)-R3

Cartesian Product

If R1 and R2 are two functions or relations, then the result of R3 = R1 x R2 is the combination of tuples that are in R1 and R2. The product is commutative and associative. Set Operations in DBMS

Degree (R3) =Degree of (R1) + Degree (R2).

In other words, R3 will have tuples such that R3 = {t₁ || t₂ | R₁ ∋ t1∧R2 ∋ t₂}. Set Operations in DBMS

Example :- 4

R3= R1 x R2 is

Relational Operations

Let us now discuss the relational operations:

SELECT

The select operation is used to select some specific records from the databse based on some criteria. This is a unary operation mathematically denoted as σ

Syntax:

σ <selection condition>(Relation)

The Boolean expression is specified in <Select condition> is made of a number of clauses of the form:

<attribute name><comparison operator><constant value> or

Comparison operators in the set { ≤ ,≥ ,≠,=, <,<} apply to the attributes whose domains are ordered value like integer.

Example :- 5

Consider the relation PERSON. If you want to display details of persons having age less than or equal to 30 than the select operation will be used as follows: Set Operations in DBMS

σ AGE<=30(PERSON)

The resultant relation will be as follows: Set Operations in DBMS

Note

1) Select operation is commutative; i.e.,

σ condition1> (σ <condition2> (R)) = σ <condition2> (σ <condition1> (R))

Hence, Sequence of select can be applied in any order

2) More than one condition can be applied using Boolean operators AND & OR etc.

The PROJECT Operation

The project operation is used to select the records with specified attributes while discarding the others based on some specific criteria. This is denoted as II

II List of attribute for project ^(Relation)

Example:- 6

Consider the relation PERSON. If you want to display only the names of persons then the project operation will be used as follows:

II Name ^(PERSON)

The resultant relation will be as follows:

Note: –

1) II <List> (II list2> (R)= II list> (R)

As long as<list2> contains attributes in <list1>.

The JOIN operation

The JOIN operation is applied on two relations. When we want to select related tuples from two given relation join is used. This is denoted as M. The join operation requires that both the joined relations must have at least one domain compatible attributes. Set Operations in DBMS

Syntax:

R1 ⊠<join condition>R2 is used to combine related tuples from two relations R1 and R2 into a single tuple.

<join condition> is of the form: <condition>AND<condition>AND…………..AND<condition>.

Degree of Relation:

Degree (R1⊠<join condition>R2) <= Degree (R1) + Degree (R2). Set Operations in DBMS

Three types of joins are there:

a) Theta join

When each condition is of the form A∅B, A is an attribute of R1 and B is an attribute of R2 and have the same domain, and ∅ is one of the comparison operators { ≤ ,≥ ,≠,=, <,<}

b) Equijoin

When each condition appears with equality condition (=) only. Set Operations in DBMS

C) Natural join (denoted by R*S) is

When two join attributes have the same name in both relations. (That attribute is called Join attribute), only one of the two attributes is retained in the join relation. The Join condition in such a case is = for the join attribute. The case is for the inin attribute. The condition is not shown in the natural join.

Let us show these operations with the help of the following example.

Example

Consider the following relations :

If we want to display name of all the students along with their course details then natural join is used in the following way:

STUDENT ⊠ COURSE

Resultant relation will be as follows:

There are other types of joins like outer joins. You must refer to further reading for more details on those operations. They are also explained in Block 2 Unit 1.

The DIVISION operation:

To perform the division operation R1÷ R2, R2 should be a proper subset of R1. In the following example R1 contains attributes A and B and R2 contains only attribute B so R2 is a proper subset of R1. If we perform Rl ÷ R2 than the resultant relation will contain those values of A from Ri that are related to all values of B present in R2.