### Day 15: Relational Algebra

Hello Dear Students,
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.

RELATIONAL ALGEBRA - 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-
• 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-

1. Basic Set Oriented Operations(Union, Intersection, Difference, Cartesian Product)
2. 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-
1. Both relations have same number of attributes means the same degree.
2. 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.

1. UNION (∪) - 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-

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.

2. INTERSECTION (∩) - 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-

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

3. DIFFERENCE (-) - 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,

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, A-B and B-A are not same.

4. CARTESIAN PRODUCT (✖) - 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,

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

SPECIAL RELATIONAL OPERATIONS - 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.

1. SELECTION (σ) - 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- σ <condition>(R), the selection symbol and the subscript is the condition which is to be satisfied and the relation name in parenthesis. For example,

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, σsalary>=15000(Employee) the resultant table is as follows-

σsalary>=15000(Employee)
 Id Name Salary 001 Ram 15,000 004 John 15,000 005 Amit 16,000 006 Alfez 16,000

2. PROJECTION (п) - 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 п<attribute1>,<attribute2>,...<attributen>(R), where п is projection symbol and attributes on the subscript and then relation name in parenthesis. For example,

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,пname(Employee), is  as follows-
 Name Ram Shyam John Alfez Ashraf

Note that all the duplicate values are removed.

3. JOIN (⋈) - 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 R1⋈<join condition>R2, where relation 1 and relation 2 are R1 and R2,  and ⋈ is join symbol with subscript of the koin condition. For example,

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