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            
 IdName 
001 Ram 
002 Shyam 
003Rishi
004 John 
005 Amit
                                                                                              
Relation B
 IdName 
002 Shyam
004 John
009Alfez 

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
 IdName 
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
 IdName 
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            
 IdName 
001 Ram 
002 Shyam 
003Rishi
                                                                                               
Relation B
 IdName 
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.NameB.Id B.Name 
 001Ram 002 Shyam 
 001Ram 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
 001Ram 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)
 IdName 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
 001Ram 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
 IdName Dept_Id
001 Ram 01
004 John 01 
005 Amit 02

Relation B
 Dep_NoD_Name 
01 Computer 
02 Physics
03 Chemistry

The join  ADept_Id=Dept_No B, is as follows-

 IdName Dept_Id Dep_No D_Name
001 Ram 01 01Computer 
004 John 01  02Physics 
005 Amit 02 03Chemistry




Best of Luck Students,
 Do share, subscribe and comment if you like our efforts.