### Day 15: Relational Algebra

Hello Dear Students,

The above relations are union because they have same degree means the number of attributes i.e. 2, and 1st table attribute data type is same as the data type of 2nd table attributes.

Hope you all are doing good.

Today we will cover the topic of Relational Algebra.

Let's get started....

Relational database basically stores the information in the form of tables. The operations being performed by the relational database are greatly understand by the relational algebra. These are the basis of the relational languages. So let us study about both.

**- In mathematics, algebra is solving the equations basically using various operations such as addition, subtraction, and so on. The relational algebra is the relational database algebra in which the data in the relational database is manipulated using a set of operations of the relational algebra. The set of operations are there which are performed on the relation(table). The operations are of 2 types basically-**

__RELATIONAL ALGEBRA__- Unary operations- The operations which are operated on one relation.
- Binary operations- The operations which are operated on 2 relations.

__The set of operations can be as follows-__- Basic Set Oriented Operations(Union, Intersection, Difference, Cartesian Product)
- Special Relational Operations(Selection, Projection, Join)

**BASIC SET OPERATIONS**- The set operations are basically binary operations which are performed on a pair of relations(tables). The set operations are union, intersection, difference and Cartesian product from which Union, Intersection and Difference operations operand relation must be union. To be union compatible, the relation must-

- Both relations have same number of attributes means the same degree.
- The data type of 1st table column should be same as the data type of 2nd table column.

For example,

**Relation A** Id | Name |

001 | Ram |

002 | Shyam |

003 | Rishi |

004 | John |

005 | Amit |

*Relation B* Id | Name |

002 | Shyam |

004 | John |

009 | Alfez |

The above relations are union because they have same degree means the number of attributes i.e. 2, and 1st table attribute data type is same as the data type of 2nd table attributes.

**- The union operation states that when we perform the union operation on both tables, then the one resulting table contains both tables values with the duplicated values removed. In simple words, delete the duplicate and write only once. For example, in above example relations, the resulting relation will be-**

__1. UNION (∪)__**R=A∪B**

Id | Name |

001 | Ram |

002 | Shyam |

003 | Rishi |

004 | John |

005 | Amit |

009 | Alfez |

Since above relations A and B are union so the Union operation

**R=A∪B**is above.**- The intersection operation states that when we perform the intersection operation on both tables, then the resulting table contains only the common values or data, no duplicacy and only write the common values. For example, in above relations A and B, the resulting will be-**

__2. INTERSECTION (∩)__**R=A∩B**

Id | Name |

002 | Shyam |

004 | John |

Since above relations A and B are union and the Intersection operation

**R=A∩B**is above which takes only the common values in both relations(tables).**- The difference operation states when the resultant relation contains those rows(tuples) which are in 1st relation and not in the 2nd relation. For example, in above relations A and B,**

__3. DIFFERENCE (-)__**R=A-B**

Id | Name |

001 | Ram |

003 | Rishi |

005 | Amit |

In above relation, only those values are

**R=A-B**which are in 1st table and not in 2nd table or relation. However,*and***A-B****are not same.***B-A***- It is also known as Cross Product or Cross Join. In this, it is a combination of every row of 1st table with every row of 2nd table. It is not Union and is a binary operation on a pair of tables. For example,**

__4. CARTESIAN PRODUCT (✖)__For example,

**Relation A** Id | Name |

001 | Ram |

002 | Shyam |

003 | Rishi |

*Relation B* Id | Name |

002 | Shyam |

004 | John |

The Cartesian product is - {(001,Ram,002,Shyam), (001,Ram, 004, John),(002,Shyam,002,Shyam),(002,Shyam,004,John),(003,Rishi,002,Shyam),(003,Rishi,004,John)}

**R=A✖B**

A.Id | A.Name | B.Id | B.Name |

001 | Ram | 002 | Shyam |

001 | Ram | 004 | John |

002 | Shyam | 002 | Shyam |

002 | Shyam | 004 | John |

003 | Rishi | 002 | Shyam |

003 | Rishi | 004 | John |

**- These operations are- Selection, Projection, Join and Division from which the Selection and Projection are unary operations in which they operate on 1 table only and the Join is binary operation in which the operations are performed on 2 tables.**

__SPECIAL RELATIONAL OPERATIONS__**- It is also known as the Restriction operation. In this operation, a certain condition is defined and only those rows of relations are there which satisfies that condition. It is a unary operation means operated on 1 table. The syntax is-**

__1. SELECTION (σ)__**σ**the selection symbol and the subscript is the condition which is to be satisfied and the relation name in parenthesis. For example,

_{<condition>}(R),Relation

**Employee**Id | Name | Salary |

001 | Ram | 15,000 |

002 | Shyam | 13,000 |

003 | Rishi | 14,000 |

004 | John | 15,000 |

005 | Amit | 16,000 |

006 | Alfez | 16,000 |

In above relation Employee, to find all employees having salary >= 15,000. So,

**σ**the resultant table is as follows-_{salary>=15000}(Employee)**σ**

_{salary>=15000}(Employee)Id | Name | Salary |

001 | Ram | 15,000 |

004 | John | 15,000 |

005 | Amit | 16,000 |

006 | Alfez | 16,000 |

**- Projection is the operation in which it retrieves the number of columns from a relation and also eliminates all the duplicate values from it. It is also binary operation which is performed on 1 table. The syntax is**

__2. PROJECTION (п)__**п**, where п is projection symbol and attributes on the subscript and then relation name in parenthesis. For example,

_{<attribute1>,<attribute2>,...<attributen>}(R)Relation

**Employee**Id | Name | Salary |

001 | Ram | 15,000 |

002 | Shyam | 13,000 |

003 | Ram | 14,000 |

004 | John | 15,000 |

005 | Shyam | 16,000 |

006 | Alfez | 16,000 |

007 | Ashraf | 14,000 |

In above relation,

**п**, is as follows-_{name}(Employee)Name |

Ram |

Shyam |

John |

Alfez |

Ashraf |

Note that all the duplicate values are removed.

**- Join as the name suggests join two tables. The cartesian product also joins two tables but it contains too many values which is not used, thus join is there which returns the values which satisfies certain conditions. It is binary operation which is operated on 2 tables. Syntax is**

__3. JOIN (⋈)__**R1⋈**, where relation 1 and relation 2 are R1 and R2, and ⋈ is join symbol with subscript of the koin condition. For example,

_{<join condition>}R2 Relation A

Id | Name | Dept_Id |

001 | Ram | 01 |

004 | John | 01 |

005 | Amit | 02 |

Relation B

Dep_No | D_Name |

01 | Computer |

02 | Physics |

03 | Chemistry |

The join A

**⋈**_{Dept_Id=Dept_No }B, is as follows-

Id | Name | Dept_Id | Dep_No | D_Name |

001 | Ram | 01 | 01 | Computer |

004 | John | 01 | 02 | Physics |

005 | Amit | 02 | 03 | Chemistry |

Best of Luck Students,

Do share, subscribe and comment if you like our efforts.