# Basic Operators in DBMS

## Relational Algebra in DBMS

• `Relational algebra` is a widely used procedural query language.
• Relational algebra collects instances of relations as input and gives occurrences of relations as output by using various operations.
• Relational algebra operations are performed recursively on a relation.
• The output of relational algebra operations is a new relation, which can be formed from one or more input relations.

### Basic Operators in Relational Algebra:

There are 6 basic operations in Relational Algebra. They are
```Select (σ) Project (∏) Union (∪) Set Difference (-) Cartesian product (X) Rename (ρ)``` ### Select (σ)

• The SELECT operation is used for selecting a subset of the tuples according to a given selection condition.
• This operation is denoted by symbol sigma(σ).

#### Syntax:

`σp(r)`
• σ denotes Select operation
• p denotes the condition
• r is the relation/table name

#### Example 1:

`σ course = "Java" (Student)`

Output– Selects tuples from Student where course = ‘Java’.

#### Example 2

```σ course = "Java" and age = "20"(Student)
```

Output – Selects tuples from Student where the course is ‘Java’ and age is ’20’.

#### Example 3

```σ salary > 50000 (Employee)
```

Output – Selects tuples from Employee where salary is greater than 50000

### Projection(π)

• The projection method defines a relation that contains a vertical subset of Relation.
• It is represented by symbol pi(π)

#### Example:

Consider the following table:

 Stu_ID Stu_Name City 1 Priya Hyderabad 2 Anjali Mumbai 3 Rahul Delhi 4 Rishi Chennai

Here, the projection of Stu_Name and City will give

```Π Stu_Name,City (Students)
```
 Stu_Name City Priya Hyderabad Anjali Mumbai Rahul Delhi Rishi Chennai

#### Note:

For the next 4 operations

Consider table ‘A’ as:

 Attribute1 Attribute2 1 1 1 2

Consider table ‘B’ as:

 Attribute1 Attribute2 1 1 1 3

### Union operation (υ)

• The operation A ∪ B includes all tuples that are in tables A or in B.
• Even if a tuple is present in both table A and table B the result will contain that tuple only once.

For a union operation to be valid, the following conditions must hold –

• A and B must have the same number of attributes.
• Attribute domains need to be compatible.
• Duplicate tuples should be automatically removed.

A ∪ B gives

 Attribute 1 Attribute 2 1 1 1 2 1 3

### Set Difference (-)

• It is represented by symbol ‘-‘ .
• Operation A – B, is a relation which includes all tuples that are in A but not in B.

For a set difference operation to be valid, the following conditions must hold –

• The attribute name of A has to match with the attribute name in B.
• The two-operand relations A and B should be either compatible or Union compatible.

#### Example

```A-B
```
 Attribute 1 Attribute 2 1 2

### Cartesian product(X)

• Cartesian product is helpful to merge columns from two relations.
• This operation is meaningful only when it is followed by other operations.

#### Example – Cartesian product

`σ Attribute 2 = '1' (A X B)`

Output – The above example shows all rows from relation A and B whose Attribute 2 has value 1

 Attribute 1 Attribute 2 1 1 1 1

### Rename (ρ)

• Rename operation can be used to rename a relation or an attribute of a relation.
• It is represented by symbol ‘ρ’.

#### syntax:

`ρ(new_relation_name, old_relation_name)`

#### Example:

```ρ(Att_2, ∏(Attribute2)(B))

```

Output:

 Att_2 1 3