Relational Algebra

Relational algebra is a procedural query language. It gives a step by step process to obtain the result of the query. It uses operators to perform queries.

Types of Relational operation

1. Select Operation:

• The select operation selects tuples that satisfy a given predicate.
• It is denoted by sigma (σ).

Notation:  σ p(r)

Where:

σ is used for selection prediction
r is used for relation
p is used as a propositional logic formula which may use connectors like: AND OR and NOT. These relational can use as relational operators like =, ≠, ≥, <, >, ≤.

For example: LOAN Relation

Input:

σ BRANCH_NAME="perryride" (LOAN)

Output:

2. Project Operation:

• This operation shows the list of those attributes that we wish to appear in the result. Rest of the attributes are eliminated from the table.
• It is denoted by ∏.

Notation: ∏ A1, A2, An (r) 

Where

A1, A2, A3 is used as an attribute name of relation r.

Example: CUSTOMER RELATION

Input:

∏ NAME, CITY (CUSTOMER)

Output:

3. Union Operation:

• Suppose there are two tuples R and S. The union operation contains all the tuples that are either in R or S or both in R & S.
• It eliminates the duplicate tuples. It is denoted by ∪.

Notation: R ∪ S 

A union operation must hold the following condition:

• R and S must have the attribute of the same number.
• Duplicate tuples are eliminated automatically.

Example:

DEPOSITOR RELATION

BORROW RELATION

Input:

∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSITOR)

Output:

4. Set Intersection:

• Suppose there are two tuples R and S. The set intersection operation contains all tuples that are in both R & S.
• It is denoted by intersection ∩.

Notation: R ∩ S 

Example: Using the above DEPOSITOR table and BORROW table

Input:

∏ CUSTOMER_NAME (BORROW) ∩ ∏ CUSTOMER_NAME (DEPOSITOR)

Output:

5. Set Difference:

• Suppose there are two tuples R and S. The set intersection operation contains all tuples that are in R but not in S.
• It is denoted by intersection minus (-).

Notation: R - S

Example: Using the above DEPOSITOR table and BORROW table

Input:

∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR)

Output:

6. Cartesian product

• The Cartesian product is used to combine each row in one table with each row in the other table. It is also known as a cross product.
• It is denoted by X.

Notation: E X D

Example:

EMPLOYEE

DEPARTMENT

Input:

EMPLOYEE X DEPARTMENT

Output:

7. Rename Operation:

The rename operation is used to rename the output relation. It is denoted by rho (ρ).

Example: We can use the rename operator to rename STUDENT relation to STUDENT1.

ρ(STUDENT1, STUDENT)

Note: Apart from these common operations Relational algebra can be used in Join operations.