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 (σ).

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

BRANCH_NAME	LOAN_NO	AMOUNT
Shimla	LN-07	15000
Mandi	LN-03	42000
Kangra	LN-11	11500
Shimla	LN-34	81500
Chamba	LN-53	60500
Mandi	LN-110	9000
Kangra	LN-76	13000

Input:

1. σ BRANCH_NAME="kangra" (LOAN)  

Output:

BRANCH_NAME	LOAN_NO	AMOUNT
Kangra LN-11 11500
Kangra LN-76 13000	L-16	1300

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

1. Notation: ∏ A1, A2, An (r)   

Where

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

Example: CUSTOMER RELATION

NAME	STREET	CITY
Ashish	Main	Kangra
Rahul	South	Pathankot
Himanshu	Main	Talwara
Sahil	South	Pathankot
Namita	North	Palampur
Harsh	North	Palampur

Input:

1. ∏ NAME, CITY (CUSTOMER)  

Output:

NAME	CITY
Ashish	Kangra
Rahul	Pathankot
Himanshu	Talwara
Sahil	Pathankot
Namita	Palampur
Harsh	Palampur

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

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

CUSTOMER_NAME	ACCOUNT_NO
Johnson	A-101
Smith	A-121
Mayes	A-321
Turner	A-176
Johnson	A-273
Jones	A-472
Lindsay	A-284

BORROW RELATION

CUSTOMER_NAME	LOAN_NO
Jones	L-17
Smith	L-23
Hayes	L-15
Jackson	L-14
Curry	L-93
Smith	L-11
Williams	L-17

Input:

1. ∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSITOR)  

Output:

CUSTOMER_NAME
Johnson
Smith
Hayes
Turner
Jones
Lindsay
Jackson
Curry
Williams
Mayes

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

1. Notation: R ∩ S   

Example: Using the above DEPOSITOR table and BORROW table

Input:

1. ∏ CUSTOMER_NAME (BORROW) ∩ ∏ CUSTOMER_NAME (DEPOSITOR)  

Output:

CUSTOMER_NAME
Smith
Jones

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

1. Notation: R - S  

Example: Using the above DEPOSITOR table and BORROW table

Input:

1. ∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR)  

Output:

CUSTOMER_NAME
Jackson
Hayes
Willians
Curry

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.

1. Notation: E X D  

Example:

EMPLOYEE

EMP_ID	EMP_NAME	EMP_DEPT
1	Ashish	S/W
2	Himanshu	H/W
3	Rahul	N/W

DEPARTMENT

DEPT_NO	DEPT_NAME
S/W	Software
H/W	Hardware
N/W	Networking

Input:

1. EMPLOYEE X DEPARTMENT  

Output:

EMP_ID	EMP_NAME	EMP_DEPT	DEPT_NO	DEPT_NAME
1	Ashish	S/W	S/W	Software
1	Ashish	S/W	H/W	Hardware
1	Ashish	S/W	N/W	Networking
2	Himanshu	H/W	S/W	Software
2	Himanshu	H/W	H/W	Hardware
2	Himanshu	H/W	N/W	Networking
3	Rahul	N/W	S/W	Software
3	Rahul	N/W	H/W	Hardware
3	Rahul	N/W	N/W	Networking

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 Customer relation to Client.

1. ρ(Client, Customer)  

Anurag Rana