---

Relational Algebra in DBMS

---

Relational Algebra is not actually implemented anywhere. It is collection of mathematical expressions. So it is a theoretical or logical model to access the database.

In simple words we can say, Relation Algebra provides the base to SQL and Non-SQL. SQL and Non-SQL are physically implemented.

Relational Algebra is a procedural query language or formal query language, which takes instances of one or more relation as an input and generates a new relation as an output. It uses a different set of operators (like unary or binary operators) and operands to perform queries.

In procedural query language every user has to mention two things to access data

What to do?
How to do?

The primary operations of relational algebra has classified as

Projection Operator(ℼ)

Projection Operator (ℼ) displays the columns of a single relation(Table) R based on the specified attributes and eliminating duplicates.

Syntax

$${\prod }_{\mathrm{attribute\; list}}(R)$$

Example: Retrieve the Roll_no and name of the students

$${\prod }_{\mathrm{Roll\_no,Name}}(\mathrm{Students})$$

Example: Retrieve the name of the students

$${\prod }_{\mathrm{Name}}(\mathrm{Students})$$

Note: This operator removes the redundancy

Selection Operator(σ)

Selection Operator (σ) performs a selection operation. It selects complete rows or tuples from the table which satisfies the selection condition.

Syntax

$${\sigma }_{\mathrm{selection\_condition}}(R)$$

Example:

$${\sigma }_{\mathrm{Branch=\text{'}cse\text{'}}}(\mathrm{Students})$$

Important Points

1) We may use logical operators like ∧ , ∨ , ! and relational operators like = , ≠ , > , < , <= , >= with the selection condition.

2) Selection operator only selects the required tuples according to the selection condition. It does not display the selected tuples. To display the selected tuples, projection operator is used.

3) Selection operator always selects the entire tuple. It can not select a section or part of a tuple.

4) Selection operator is commutative in nature i.e.

$${\sigma }_{\mathrm{A\wedge B}}(R)={\sigma }_{\mathrm{B\wedge A}}(R)$$

5) The number of rows returned by a selection operation is obviously less than or equal to the number of rows in the original table

.

• Minimum Cardinality = 0
• Maximum Cardinality = |R|

Example: Retrieve the name of the students whose age is < 18;

$${\prod }_{\mathrm{Name}}({\sigma }_{\mathrm{age<18}}(R))$$

Rename Operator

Rename operation is denoted by "Rho"(ρ). As its name suggests it is used to rename the resultant relation.

Syntax

$${{\rm P}}_x(R)$$

where the symbol ‘ρ’ is used to denote the RENAME operator and R is the result which is saved with the name X.

Example: Retrieve Roll_no, Name of the students who admited in CSE and rename to cse_students

$${{\rm P}}_{\mathrm{cse\_students}}({\prod }_{\mathrm{Roll\_no,Name}}({\sigma }_{\mathrm{Branch=\text{'}cse\text{'}}}(\mathrm{Students})))$$

Union Operator

Union operator is denoted by ∪ symbol and it is used to select all the tuples from two relations(R1 and R2).

Syntax

$${\mathrm{R1\; \cup \; R2}}_{}$$

To perform union operation, R1 and R2 must be the same number of attributes with same order and same domains. In the result, Duplicate tuples will automatically removed

Cartesian/CROSS Product

Cartesian or CROSS Product denoted by X. It is used to combine each row in one Relation(R1) with each row in the other Relation(R2).

Syntax

$${\mathrm{R1\; X\; R2}}_{}$$

Difference Operator

Difference Operator denoted by intersection minus (-). The difference between two relations(R1 and R2) is a relation which includes all tuples that are in R1 but not in R2.

Syntax

$${\mathrm{R1\; -\; R2}}_{}$$

Intersection operator

Intersection operator is denoted by ∩ symbol and it is used to select common tuples from two relations(R1 and R2)

Syntax

$${\mathrm{R1\; \cap \; R2}}_{}$$

To perform Intersection operation, R1 and R2 must be the same number of attributes with same order and same domains. In the result, Duplicate tuples will automatically removed

Division Operator

Division Operator denoted by / or ÷. We can apply Division operator on two Relations(R1 and R2) if and only if: Attributes of R2 is proper subset of Attributes of R1 and when query contain all or every.

Syntax

$${\mathrm{R1\; ÷\; R2}}_{}$$

Join Operations

Join operator denoted by ⨝. A Join operation combines related tuples from different relations.

Join operators can apply, if and only if there must be some common attributes in two relations, It is used when we need to access data from more than one Relations.

Join operator = cross product + condition

Types of JOINs:

1. Inner Join

     Natural Join

     Equi Join

     Theta Join

2. Outter Join

     Left Outter Join

     Right Outter Join

     Full Outter Join

1. Inner Join

In an inner join, only those tuples that satisfy the matching criteria are included, while the rest are excluded. Let’s study various types of Inner Joins

Natural Join

Natural join can only be performed if there is a common attribute (column) between the relations. The name and type of the attribute must be same(one as a primary key and other as foreign key).

Syntax

$${\mathrm{R1\; ⨝\; R2}}_{}$$

Equi Join

In Equi join we uses only equivalence condition while performing join between two relations.

Syntax

$${\mathrm{R1\; ⨝}}_{\mathrm{(...\; =\; ...)}}\mathrm{R2}$$

Theta Join

Theta Join is used to join two relations based on some conditions(θ). The condition can be on any attributes of the relations performing Theta join. Any comparison operator can be used in the condition.

Syntax

$${\mathrm{R1\; ⨝}}_{\theta }\mathrm{R2}$$

2. Outter Join

In an outer join, along with tuples that satisfy the matching criteria, we also include some or all tuples that do not match the criteria.

Left Outter Join (⟕)

Syntax

$${\mathrm{R1\; ⟕\; R2}}_{}$$

Right Outter Join (⟖)

Syntax

$${\mathrm{R1\; ⟖\; R2}}_{}$$

Full Outter Join (⟗)

Syntax

$${\mathrm{R1\; ⟗\; R2}}_{}$$

---

Next Topic :Tuple Relational Calculus in DBMS