Operations in Relational Algebra

Union in Relational Algebra

The union operation in Relational Algebra merges two relations, eliminating any duplicate rows. It is denoted as R ∪ S, where R and S are two relations with identical schemas. This operation is widely used in SQL queries and other database management systems to combine data from multiple sources.

Let Consider the original Table

Original Table: Customers

ID	Name	City
1	John Doe	New York
2	Sarah Smith	Los Angeles
3	Michael Johnson	Chicago
4	Emily Brown	Houston
5	David Wilson	Miami
6	Olivia Jones	San Francisco

Original Table: Suppliers

ID	Name	City
101	XYZ Company	Seattle
102	ABC Corporation	Dallas
103	LMN Enterprises	Atlanta
104	PQR Inc.	Boston
105	DEF Industries	Philadelphia
106	GHI Co.	Phoenix

The syntax for the union operation

• It denoted as R ∪ S
• where R and S represent two relations with the same schemas.
• The resulting relation maintains the same schema as R and S, ensuring consistency in the structure.
• To better illustrate the union operation, let's consider two relations, Customers and Suppliers, with the following schemas: Customers ∪ Suppliers.

                        Customers (ID, Name, City)
                        Suppliers (ID, Name, City)

Customers _Suppliers

ID	Name	City
1	John Doe	New York
2	Sarah Smith	Los Angeles
3	Michael Johnson	Chicago
4	Emily Brown	Houston
5	David Wilson	Miami
6	Olivia Jones	San Francisco
1	XYZ Company	Seattle
2	ABC Corporation	Dallas
3	LMN Enterprises	Atlanta
4	PQR Inc.	Boston
5	DEF Industries	Philadelphia
6	GHI Co.	Phoenix

Intersection in Relational Algebra

The intersection operation identifies common rows between two relations. It is represented as R ∩ S, where R and S are relations with the same schemas. This operation is beneficial in extracting meaningful information from overlapping data.

The syntax for the Intersection operation

• To perform an intersection operation, we use the symbol "∩"
• The syntax R ∩ S,
• Where R and S represent two relations with identical schemas.
• It's important to note that the resulting relation will also have the same schema as R and S, ensuring consistency in the structure.
• Let's consider two relations, Customers and Suppliers, with the following schemas:

                 Student (ID, Name, Department, Age, Hobby)

                  Hobby (ID, Name, Hobby, Age)

• Suppose we want to find all entities that exist as both Students and Hobby.
• By utilizing the intersection operation on these two relations,
• we can identify the common rows that satisfy this condition.

Student table:

ID	NAME	Department	AGE	HOBBY
1	Birhanu	IT	30	Movie
2	Atlaw	Mathematics	29	Football
3	Chala	Physics	25	Karate
4	Alemayehu	Sport	25	Karate
5	Dawit	Computer Science	33	Football
6	Anbiko	Agriculture	30	Reading

Hobby table:

ID	NAME	HOBBY	AGE
1	Bogale	Driving	30
2	Atlaw	Football	29
3	Solomon	Game	25
4	Kaltamo	Football	20
5	Selamu	Football	26
6	Dawit	Football	33

Student ∩ Hobby

ID	NAME	HOBBY	AGE
2	Atlaw	Football	29
5	Dawit	Football	33

Set Difference in Relational Algebra

Set Difference allows identification of rows present in one relation but absent in another. It uses the syntax R - S, where R and S are relations with the same schema. This operation aids in comparing two sets of data and extracting unique elements.

Syntax for SET DIFFERENCE operation

• To perform the SET Difference operation, we use the "-" symbol
• The syntax is R - S,
• Where R and S represent two relations with the same schema.
• We obtain a resulting relation that retains the schema of R and S.
• Example
• Let's consider two relations, Student and Hobby, with the following schemas:

                  Student (ID, Name, Department, Age, Hobby)

                  Hobby (ID, Name, Hobby, Age)

• Suppose our objective is to find all Student who have not Hobby. In other words, we want to identify the rows in the student relation that do not exist in the Hobby relation.
• To accomplish this, we can utilize the SET Difference operation.

Student - Hobie

ID	NAME	Department	AGE	HOBBY
1	Birhanu	IT	30	Movie
3	Chala	Physics	25	Karate
4	Alemayehu	Sport	25	Karate
6	Anbiko	Agriculture	30	Reading

Cartesian Product (Cross Product) in Relational Algebra

The Cartesian Product combines all possible pairs of rows from two relations. It is represented as R x S, where R and S denote relations with different schemas. This operation generates comprehensive result sets and is often used in SQL queries by employing the CROSS JOIN option.

Syntax

• The syntax for the Cartesian Product operation is represented as R x S
• where R and S denote two relations with different schemas.
• The resulting relation has a schema that is the union of the schemas of R and S, incorporating all the attributes from both relations
• Example
• Let's consider two relations, Customers and Orders, with the following schemas:

            Customers (ID, Name)

            Orders (OrderID, CustomerID, Product)

• Suppose our goal is to find all possible combinations of customers and orders.
• We can accomplish this by employing the Cartesian Product operation, which combines each row from the Customers relation with every row from the Orders relation.
• Here's an example of the Cartesian Product operation applied to these two relations:

multiplication (cross join) of the Customers and Orders tables

Customers x Orders

ID	Name	OrderID	CustomerID	Product
101	John Smith	201	101	Laptop
101	John Smith	202	103	Headphones
101	John Smith	203	102	Smartphone
101	John Smith	204	105	Tablet
101	John Smith	205	101	Smartwatch
101	John Smith	206	104	Camera
102	Emily Davis	201	101	Laptop
102	Emily Davis	202	103	Headphones
102	Emily Davis	203	102	Smartphone
102	Emily Davis	204	105	Tablet
102	Emily Davis	205	101	Smartwatch
102	Emily Davis	206	104	Camera
103	Alex Johnson	201	101	Laptop
103	Alex Johnson	202	103	Headphones
103	Alex Johnson	203	102	Smartphone
103	Alex Johnson	204	105	Tablet
103	Alex Johnson	205	101	Smartwatch
103	Alex Johnson	206	104	Camera
104	Sarah Brown	201	101	Laptop
104	Sarah Brown	202	103	Headphones
104	Sarah Brown	203	102	Smartphone
104	Sarah Brown	204	105	Tablet
104	Sarah Brown	205	101	Smartwatch
104	Sarah Brown	206	104	Camera
105	Michael Wilson	201	101	Laptop
105	Michael Wilson	202	103	Headphones
105	Michael Wilson	203	102	Smartphone
105	Michael Wilson	204	105	Tablet
105	Michael Wilson	205	101	Smartwatch
105	Michael Wilson	206	104	Camera
106	Olivia Lee	201	101	Laptop
106	Olivia Lee	202	103	Headphones
106	Olivia Lee	203	102	Smartphone
106	Olivia Lee	204	105	Tablet
106	Olivia Lee	205	101	Smartwatch
106	Olivia Lee	206	104	Camera

Selection in Relational Algebra

Selection, also known as the Restriction operation, filters rows based on specified conditions. It uses the syntax σ<condition>(R), where R is a relation, and <condition> is a logical expression evaluating to true or false.

Syntax

• The syntax for Selection is: σ<condition>(R)
• R is a relation and <condition> is a logical expression that evaluates to either true or false.
• The resulting relation contains only the rows from R that satisfy the <condition>.

Example

• Let's consider a relation, Employees, with the following schema:

              Employees (ID, Name, Department, Salary)

Employees Table:

ID	Name	Department	Salary
201	Emma Johnson	IT	$62,000
202	Liam Anderson	Sales	$58,500
203	Sophia Clark	HR	$67,200
204	Noah Baker	Marketing	$59,800
205	Olivia Garcia	Sales	$52,500
206	Ethan Martinez	Sales	$33,700

• Suppose we want to find all employees who belong to the Sales department and have a salary greater than $50,000.
• We can use the Selection operation to filter the rows based on this condition.
• Here's an example of the Selection operation applied to this relation:

σ(Department = 'Sales' AND Salary > $50,000)(Employees)

D	Name	Department	Salary
202	Liam Anderson	Sales	$58,500
205	Olivia Garcia	Sales	$52,500

Projection in Relational Algebra

Projection selects certain columns (attributes) from a relation while discarding others. It employs the syntax π<list of attributes>(R), where R is a relation, and <list of attributes> is a comma-separated list of attribute names.

Syntax

• The syntax for Projection is: π<list of attributes>(R)
• R is a relation and <list of attributes> is a comma-separated list of attribute names.
• The resulting relation contains only the selected attributes from R.
• Example
• Let's consider a relation, Employees, with the following schema:

                    Employees (ID, Name, Department, Salary)

Employees Table:

ID	Name	Department	Salary
101	John Smith	IT	$60,000
102	Emily Davis	Sales	$55,000
103	Alex Johnson	HR	$65,000
104	Sarah Brown	Marketing	$58,000
105	Michael Wilson	Finance	$70,000
106	Olivia Lee	Operations	$52,000

• Suppose we want to find the names and salaries of all employees.
• We can use the Projection operation to select the Name and Salary columns from the Employees relation.
• Here's an example of the Projection operation applied to this relation:

                π (Name, Salary) (Employees):

Name	Salary
John Smith	$60,000
Emily Davis	$55,000
Alex Johnson	$65,000
Sarah Brown	$58,000
Michael Wilson	$70,000
Olivia Lee	$52,000

Join Operation

Join combines related tuples from two relations into single "longer" tuples. It is denoted by the symbol "⋈" and is crucial for processing relationships among relations in a database.

Types of join Operation

EMPLOYEE

EMP_NO	EMP_NAME	CITY	EXPERIENCE
Emp-1	Lelisa	Assosa	6
Emp-2	Birhanu	Addis Ababa	8
Emp-3	Dawit	Nekemte	9
Emp-4	Debelo	Hawasa	10

DEPARTMENT

DEP_NO	D_NAME	EMP_NO
Dep-01	Chemistry	Emp-4
Dep-02	Economics	Emp-2
Dep-03	Computer Science	Emp-3

1. EQUIJOIN Operation

The EQUIJOIN operation is a type of join in relational databases that combines rows from two tables based on a common column where values are equal. Specifically, an EQUIJOIN is synonymous with an INNER JOIN, where the join condition uses the equality operator (=) to match values in the specified columns of the participating tables.

When performing an EQUIJOIN (or INNER JOIN) between two tables, the resulting table will include only the rows that have matching values in the specified columns from both tables.

This operation retrieves rows from both tables where the values in the specified columns are equal. Rows that do not have a matching value in the specified columns between the tables will not be included in the resulting output.

EMPLOYEE ⨝ (EMPLOYEE.EMP_NO = DEPARTMENT.EMP_NO) DEPARTMENT

Here's the result of performing an EQUIJOIN operation between the EMPLOYEE and DEPARTMENT tables based on the condition EMPLOYEE.EMP_NO = DEPARTMENT.EMP_NO:

EMP_NO	EMP_NAME	CITY	EXPERIENCE	DEP_NO	D_NAME
Emp-2	Birhanu	Addis Ababa	8	Dep-02	Economics
Emp-3	Dawit	Nekemte	9	Dep-03	Computer Science
Emp-4	Debelo	Hawasa	10	Dep-01	Chemistry

NATURAL JOIN (⋈ ) or ( ⋈ )in Relational Algebra

NATURAL JOIN combines two relations based on common attributes between them, ensuring the result contains only one copy of each common attribute. It is denoted by a multiplication symbol (*) OR ⋈ and requires common attributes to have the same name in both relations.

EMPLOYEE ⋈ DEPARTMENT

Here's the result of performing a NATURAL JOIN operation between the EMPLOYEE and DEPARTMENT tables:

EMP_NO	EMP_NAME	CITY	EXPERIENCE	DEP_NO	D_NAME
Emp-2	Birhanu	Addis Ababa	8	Dep-02	Economics
Emp-3	Dawit	Nekemte	9	Dep-03	Computer Science
Emp-4	Debelo	Hawasa	10	Dep-01	Chemistry

Outer join Operation

OUTER JOIN operation is a type of join that combines tuples from two relations (tables) based on a common attribute and includes unmatched tuples by using special symbols. There are three types of OUTER JOINs:

• LEFT OUTER JOIN (⟕):

• Retrieves all records from the left table and matching records from the right table. If there's no match in the right table, NULL values are included for those columns.

Here's the result of performing a LEFT JOIN operation between the EMPLOYEE and DEPARTMENT tables based on the condition EMPLOYEE.E_NO = DEPARTMENT.E_NO:

EMP_NO	EMP_NAME	CITY	EXPERIENCE	DEP_NO	D_NAME
Emp-1	Lelisa	Assosa	6	NULL	NULL
Emp-2	Birhanu	Addis Ababa	8	Dep-02	Economics
Emp-3	Dawit	Nekemte	9	Dep-03	Computer Science
Emp-4	Debelo	Hawasa	10	Dep-01	Chemistry

• RIGHT OUTER JOIN (⟖): 

• Opposite of the LEFT OUTER JOIN, it fetches all records from the right table and matching records from the left table. If there's no match in the left table, NULL values are included for those columns.

• 
  

• Here's the result of performing a RIGHT OUTER JOIN operation between the EMPLOYEE and DEPARTMENT tables based on the condition EMPLOYEE.EMP_NO = DEPARTMENT.EMP_NO:

  EMP_NO	EMP_NAME	CITY	EXPERIENCE	DEP_NO	D_NAME
  Emp-4	Debelo	Hawasa	10	Dep-01	Chemistry
  Emp-2	Birhanu	Addis Ababa	8	Dep-02	Economics
  Emp-3	Dawit	Nekemte	9	Dep-03	Computer Science

• FULL OUTER JOIN (⟗):

• Combines all records from both tables. It includes matching rows and adds NULL values for columns where there are no matches in either table.

• 
  

• Here's the result of performing a FULL OUTER JOIN operation between the EMPLOYEE and DEPARTMENT tables based on the condition EMPLOYEE.EMP_NO = DEPARTMENT.EMP_NO:

  EMP_NO	EMP_NAME	CITY	EXPERIENCE	DEP_NO	D_NAME
  Emp-1	Lelisa	Assosa	6
  Emp-2	Birhanu	Addis Ababa	8	Dep-02	Economics
  Emp-3	Dawit	Nekemte	9	Dep-03	Computer Science
  Emp-4	Debelo	Hawasa	10	Dep-01	Chemistry