Relational Algebra

SQL AND RELATIONAL ALGEBRA DEFINITION WITH EXAMPLE 

Relational Algebra is a theoretical query language used to query and manipulate data in a relational database. It uses mathematical set operations to operate on relations (tables).

It forms the foundation of SQL and other relational query languages.

 

Basic Operations in Relational Algebra

 

Derived (Compound) Operations

 

1. Student

2. Department

1. Selection (σ): Get all CS students

 

σ Dept = 'CS' (Student)

 

Result:

2. Projection (π): Get student names

 

π Name (Student)

 

Result:

3. Union (∪): Combine two student lists

Student1 and Student2 are two relations with same schema.

 

Student1 ∪ Student2

 

Student − σ Dept = 'CS' (Student)

 

Result:

5. Cartesian Product (×)

 

Student × Department

 

Each student is paired with each department (not useful alone, but used in joins).

6. Natural Join (⋈): Join Student and Department on Dept

Student ⋈ Department

 

Result:

 

1. SELECT (symbol: σ)

 

Select Operation is used as an expression to choose tuples which meet the selection condition. 

 

Employee

 

 

Question : Select all employees whose salary is greater than 50000.

 

SQL Query : SELECT * FROM Employee WHERE Salary > 50000;

 

Relational Algebra:σ salary > 50000 (Employee)

 

Output : 

 

 

 

Question : Select all employees whose department is D1 and salary is greater than 50000.

 

SQL Query :SELECT * FROM Employee WHERE Salary > 50000 AND DeptID=”D1”;

 

Relational Algebra : σ(DeptID = "D1" AND Salary >50000)(Employee)

 

 

 

2. PROJECT (symbol: π)

 

This operation is used to select a subset of columns from a table.The projection eliminates all attributes of the input relation but those mentioned in the projection list. 

 

Employee

 

 

Question: Display Name and Salary from Employee Table 

Relational Query: Π Name, Salary (Employee)

Output: 

 

Question: Display DeptID from Employee Table 

Relational Query: Π DeptID (Employee)

Output: 

 

Note: Project Operation removes the duplicate values

 

 

3. RENAME (symbol: ρ)

 

Rename is a unary operation used for renaming attributes of a relation.

ρ (a/b)R will rename the attribute ‘b’ of relation by ‘a’.

 

 

4. UNION (∪)

UNION is symbolized by ∪ symbol. It includes all tuples that are in tables A or in B. It also eliminates duplicate tuples. So, set A UNION set B would be expressed as:

A ∪ B

For a union operation to be valid, the following conditions must hold –

• R and S must have the same number of attributes.
• Attribute domains need to be compatible.
• Duplicate tuples should be automatically removed.

 

 

 

SQL Query : SELECT ID, Name FROM Table A UNION SELECT ID, Name FROM Table B;

Relational Algebra:

Π ID, Name(Table A) ∪ Π ID, Name (Table B)

 

 

 

 

 

 

5. INTERSECTION ( ∩)

 

An intersection is defined by the symbol ∩

Defines a relation consisting of a set of all tuples that are in both A and B.

 

 

 

SQL Query :SELECT ID, Name FROM Table A INTERSECT SELECT ID, Name FROM Table B;

Relational Algebra: Π ID, Name(Table A)∩ Π ID, Name(Table B)

 

 

 

6. DIFFERENCE (-)

The result of A – B, is a relation which includes all tuples that are in A but not in B.

• The attribute name of A has to match with the attribute name in B.
• The two-operand relations A and B should be either compatible or Union compatible.
• It should be a defined relation consisting of the tuples that are in relation A, but not in B.

 

 

 

 

 

7. CARTESIAN PRODUCT ( x )

Cartesian Product associates every tuple of R1 with every tuple of R2.

R1 X R2= All possible pairing. We don't need any common attributes in both relation.

 

EMPLOYEE

 

 

DEPARTMENT

 

 

 

EMPLOYEE X  DEPARTMENT

 

 

 

Note : Can you tell me the difference between Union and Join ?

 

8. JOIN

 

A join is an operation that combines the rows of two or more tables based on related columns. 

 

1. NATURAL JOIN (⋈)

Natural join can only be performed if there is a common attribute (column) between the relations. The name and type of the attribute must be same.

Example

Consider the following two tables

 

SQL Query : SELECT * FROM TableA NATURAL JOIN TableB;

Relational Query: σ(A)⋈ Num= Num (B)

 

 

2. Equi Join 

 

Equi Join is a type of Inner join in which we use equivalence(‘=’) condition in the join condition

Employee

 

 

 DEPARTMENT 

 

SQL QUERY : SELECT Id, Name, Salary, Dname

FROM Employee

INNER JOIN Department

ON Employee.Dname = Department.Dname;

Relational Algebra : Π ID, Name,Salary, Dname(Employee) ⨝ Employee.Dname = Department.Dname (DEPARTMENT)

 

3. OUTER JOIN

In an outer join, along with tuples that satisfy the matching criteria, we also include some or all tuples that do not match the criteria.

 

c.i) Left Outer Join(A ⟕ B)

In the left outer join, operation allows keeping all tuple in the left relation. However, if there is no matching tuple is found in right relation, then the attributes of right relation in the join result are filled with null values.

 

CUSTOMERS Table:

 

ORDERS Table:

 

SQLQUERY:  

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS.CID = ORDERS.CID;

Π ID,Name, Salary (CUSTOMERS ⟕ customers.cid=orders.cid ORDERS)

Output: 

 

c.ii.) Right Outer Join

 

Right Outer Join It is also called a right join. This type of outer join retrieves all records from the right table and retrieves matching records from the left table. And for the record which doesn’t lies in Left table will be marked as NULL in result Set.

 

Consider Above Example: 

 

SQL Query:

 

SELECT CID, NAME, AMOUNT, DATE FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.CID = ORDERS.CID;

 

 

Π ID,Name, Salary (CUSTOMERS ⟖customers.cid=orders.cid ORDERS)

 

c.iii) Full Join (⟗)

 

FULL JOIN creates the result-set by combining results of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both tables. For the rows for which there is no matching, the result-set will contain NULL values.

 

SQL Query:

 

SELECT Student.NAME,Course.COURSE_ID FROM Student FULL JOIN Course ON Course.ROLL_NO = Student.ROLL_NO;

 

Π Name,Course_ID(STUDENT  ⟗Course.ROLL_NO=Student.ROLL_NO (COURSE)

 

 

Output :

 

 

Properties of Relational Algebra:

Closure: The result of a relational algebra operation is also a relation.

Commutativity: The order of operands does not affect the result of ∪ and ∩ operations.

Associativity: The grouping of operands does not affect the result of ∪ and ∩ operations.

Distributivity: ∩ distributes over ∪, and vice versa.