**Relational Algebra in DBMS Exercises and Solutions**

**RELATIONAL ALGEBRA**

Relational Algebra is a set of basic operations used to manipulate the data in relational model. These operations enable the user to specify basic retrieval request.. The result of retrieval is a new relation, formed from one or more relations. These operations can be classified in two categories:

**Basic Set Operations **

1) UNION

2) INTERSECTION

3) SET DIFFERENCE

4) CARTESIAN PRODUCT

**Relational Operations**

1) SELECT

2) PROJECT

3) JOIN

4) DIVISION

### 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 **

** R1 **

** R2**

**R3 = R1 ⋃ R2 is **

** Q**

**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 :- **

**R1 **

** R2 **

**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

**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}.**

**Example :- 3**

** R1 **

** R2**

**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.

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

In other words, R3 will have tuples such that **R3 = {t _{1} || t_{2} | R_{1} ∋ t1∧R2 ∋ t_{2}}.**

**Example :- 4**

** R1 **

** R2**

** 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

<attribute name><comparison operator><attribute name>

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:

**σ AGE<=30(PERSON)**

The resultant relation will be as follows:

**Note **

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

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

Hence, Sequence of select can be applied in any order

OR

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.

**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).

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.

**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.

**Example :-**

### Also Read

## Types of Attributes in DBMS

## Advantages of DBMS Over File System

## Weak Entity in DBMS