RELATIONAL ALGEBRA

Relational Algebra
• The relational algebra is a procedural query language. It consists
of a set of operations
• that take one or two relations as input and produce a new relation
as their result. The
• fundamental operations in the relational algebra are select, project,
union, set difference,
• Cartesian product, and rename. In addition to the fundamental
operations, there are
• several other operations—namely, set intersection, natural join,
division, and assignment.
• We will define these operations in terms of the fundamental
operations.
• In general, we allow comparisons using =, =, , ≥ in the selection
predicate. Furthermore, we can combine several predicates into a
larger predicate by using the connectives and (∧), or (∨), and not
(¬)
Tables used

Borrower (customer_name, loan_number)

Depositor (customer_name, account_number)

Customer( customer_name, customer_street, customer_city)

Loan( loan_number, branch_name, amount)

Account( account_number, branch_name, balance)

Selection: σselection_criteria (TABLE-NAME)
Find all tuples in which the amount lent is less than $1200
σamount < 1200 (LOAN)

Find all tuples in which the branch name is ‘pnbwaknaghat

σbranch-name = “pnbwaknaghat” (LOAN)

Find all tuples in which the branch name is ‘pnbwaknaghat’ is

and amount is more than $1200

σbranch-name = “pnbwaknaghat” ∧ amount >1200 (LOAN)

∧ represents and
˅ represents or
Cont.
The selection predicate may include comparisons between two attributes. To
illustrate, consider the relation loan-officer that consists of three attributes:
customer-name, banker-name, and loan-number, which specifies that a particular
banker is the loan officer for a loan that belongs to some customer.

Eg.
Find all customers who have the same name as their loan officer

σcustomer-name = banker-name (LOAN-OFFICER)

Projection (Π )
• The project operation is a unary operation that returns its
argument relation, with certain attributes left out.
• Since a relation is a set, any duplicate rows are eliminated.
• Projection is denoted by the uppercase Greek letter PI (Π).

Eg.
List all loan numbers and the amount of the loan

Π loan-number, amount (LOAN)

Eg.
Find those customers who live in ‘Harrison’.

Πcustomer-name ( σcustomer-city = “Harrison” (CUSTOMER ) )

Union (∪)
For a union operation R ∪ S to be valid, we require that two conditions hold:

1. The relations R and S must be of the same arity. That is, they must have
the same number of attributes.
2. The domains of the ith attribute of R and the ith attribute of S must be the
same, for all i. Note that R and S can be, in general, temporary relations that
are the result of relational algebra expressions.

P Q R=P∪Q

Id Name Id Name Id Name

101 Jones 103 Smith 101 Jones

103 Smith 104 Lamb 106 Smith

104 Lamb 106 Byron 107 Lamb

107 Evan 110 Drew 112 Smith

110 Drew
112 Smith
Borrower (customer_name, loan_number)
Depositor (customer_name, account_number)
Customer( customer_name, customer_street, customer_city)
Loan( loan_number, branch_name, amount)
Account( account_number, branch_name, balance)

Example: Find the names of all bank customers who have either an
account or a loan or both.

Πcustomer-name (BORROWER) ∪ Πcustomer-name (DEPOSITOR)

Explanation
• Note that the customer relation does not contain the information, since a
customer does not need to have either an account or a loan at the bank.
• To answer this query, we need the information in the depositor relation and
in the borrower relation.
• We know how to find the names of all customers with a loan in the bank:
Πcustomer-name (BORROWER)
• We also know how to find the names of all customers with an account in the
bank:
Πcustomer-name (DEPOSITOR)
• To answer the query, we need the union of these two sets; that is, we need all
customer names that appear in either or both of the two relations. We find
these data by the binary operation union, denoted, as in set theory, by ∪. So
the expression needed is
Πcustomer-name (BORROWER ) ∪ Πcustomer-name (DEPOSITOR)
• Notice that there are 10 tuples in the result, even though there are seven
distinct borrowers and six depositors.
• This apparent discrepancy occurs because Smith, Jones, and Hayes are
borrowers as well as depositors.
• Since relations are sets, duplicate values are eliminated.
Intersection ( ∩ )
P Q R=P∩Q
Id Name Id Name Id Name
101 Jones 103 Smith 103 Smith
103 Smith 104 Lamb 104 Lamb
104 Lamb 106 Byron 110 Drew
107 Evan 110 Drew
110 Drew
112 Smith
Difference (-) The set-difference operation allows us to
find tuples that are in one relation but are not in another.

P Q R=P-Q
Id Name Id Name Id Name
101 Jones 103 Smith 101 Jones
103 Smith 104 Lamb 107 Evan
104 Lamb 106 Byron 112 Smith
107 Evan 110 Drew
110 Drew
112 Smith
Borrower (customer_name, loan_number)
Depositor (customer_name, account_number)
Customer( customer_name, customer_street, customer_city)
Loan( loan_number, branch_name, amount)
Account( account_number, branch_name, balance)

Example: Find all customers of the bank who have an

account but not a loan.

Πcustomer-name (depositor) − Πcustomer-name (borrower )

The Rename Operation (ρ)
• The rename operator in relational databases is used to change the name of a
relation (table) or its attributes (columns).
• It is denoted by rho (ρ).
• It helps in making the database schema more meaningful or clearer by providing
more descriptive names. This operation doesn't change the actual data in the
relation, but only the labels used to identify the relation or its attributes.
• In relational databases, it's possible to rename both the relation name and its
attributes.

EmpID EmpName EmpSalary

1 John 50000

2 Alice 60000

3 Bob 55000
 1. Change both the relation name and attribute names:
The syntax is: ρS (B1​,B2​,…,Bn​)​ (R)
Here R is the old relation name, and S is the new name of the relation.
The attributes of the relation are renamed from their original names to B1, B2, ..., Bn.

Example:
A table with attributes is given as:
Employee (emp_id, emp_name, emp_salary)
Rename the table Employee with Staff, and its attributes with
staff_id, staff_name, and staff_salary

ρStaff (staff_id, staff_name, staff_salary)​( Employee)

2. Change only the relation name

The syntax is: ρS ​(R)

• Here, R is the old relation name, and S is the new name of
the relation, while the attribute names remain unchanged.
Example:
ρE2(Employee):
• The relation name has changed from Employee to E2,
but the attributes remain unchanged.
3. Change only the attribute names

• The syntax is:

ρ(B1​,B2​,…,Bn​)​(R)
• Here, the relation name R stays the same, but
the column names are changed to B1, B2, ..., Bn.
Example:
ρ( staff-id, staff-name, staff-salary)(Employee)
The relation name Employee remains the same,
but the column names are updated to staff-id,
staff-name, and staff-salary.
Cartesian Product (×)
• On applying CARTESIAN PRODUCT on two relations that is on two sets of
tuples, it will take every tuple one by one from the left set(relation) and will pair it
up with all the tuples in the right set(relation).
• So, the CROSS PRODUCT of two relation A(R1, R2, R3, …, Rm) with degree m,
and B(S1, S2, S3, …, Sn) with degree n, is a relation C(R1, R2, R3, …, Rm, S1, S2,
S3, …, Sn) with degree m + n attributes.

P Q R=P×Q
Id Name S Id Name S
101 Jones J1 101 Jones J1
103 Smith J2 101 Jones J2
104 Lamb 103 Smith J1
107 Evan 103 Smith J2
104 Lamb J1
104 Lamb J2
107 Evan J1
107 Evan J2
Borrower (customer_name, loan_number)
Depositor (customer_name, account_number)
Customer ( customer_name, customer_street, customer_city)
Loan ( loan_number, branch_name, amount)
Account ( account_number, branch_name, balance)

Example: Find the names of all customers who have a loan at the
‘Perryridge’ branch.
Sol.: We need the information in both the Loan relation and the
Borrower relation to do so.
Πcustomer_name (σBorrower.loan_number = Loan.loan_number (σbranch-name =‘Perryridge’
(Borrower × Loan) ) )
OR
Πcustomer_name(Borrower ⨝ ( Πloan_number (σbranch-name =‘Perryridge’ (Loan) ) ) )
OR
Πcustomer_name (σ[Link]-name =‘Perryridge’ (Borrower ⨝ Loan) )
Division (÷)
P Q1 R = P ÷ Q1 Q2 R = P ÷ Q2
A B B A B A
a1 b1 b1 a1 b1 a1
a1 b2 b2 a5 a2
a2 b1 a3
a3 b1 a5
a4 b2
a5 b1
a5 b2
Q4 R = P ÷ Q4
Q3 R = P ÷ Q3
B A
B A
a1
b1
a2
b2
a3
b3
a4
a5
Join (⨝)
Join

Inner Join Outer Join

Conditional Join or Theta Join Left Outer Join

Equi Join Right Outer Join

Natural Join Full Outer Join

Inner Join
• Inner Join is a join operation in DBMS that combines
two or more tables based on related columns and
returns only rows that have matching values among
tables. Inner join has three types.
– Conditional join
– Equi Join
– Natural Join
Conditional Join (theta join)

• Conditional join is a type of inner join in which tables are

combined based on the specified condition.
• In conditional join, the join condition can include <, >, <=, >=, ≠
operators in addition to the '=' operator.
• Example: Suppose two tables A and B
Table A
Table B
A ⨝ S<TB
Output

A B C
R S T U R S T U

10 5 10 12 10 5 10 12

7 20 17 6
(b) Equi Join
• Equi Join is a type of inner join where the join condition uses the
equality operator ('=') between columns.
• Example: Suppose there are two tables A and B

A B

Column1 Column2 Column1 Column2

a a a a

a c a b

A ⨝ A.Column1 = B.Column2 (B) C

Output Column1 Column1

a a
 (c) Natural Join

• Natural join is a type of inner join in which we do not need any

comparison operators.
• In natural join, columns should have the same name and domain.
• There should be at least one common attribute between the two
tables.

Number Cube Number Square

2 8 2 4

3 27 3 9
Table B
A⨝B Number Square Cube
Output

2 4 8

3 9 27
2. Outer Join

• Outer join is a type of join that retrieves

matching as well as non-matching records
from related tables. There are three types of
outer join
– Left outer join
– Right outer join
– Full outer join
(a) Left Outer Join

• It is also called left join. This type of outer join

retrieves all records from the left table and
retrieves matching records from the right table.
A B C

Number Square Number Cube Number Square Cube

2 4 2 8 2 4 8

3 9 3 27 3 9 27

4 16 5 125 4 16 NULL
(b) Right Outer Join

• This join retrieves all records from the right table and
retrieves matching records from the left table.
• The record which doesn't lies in left table is marked as
NULL in result Set.

A B C

Number Square Number Cube Number Square Cube

2 4 2 8 2 4 8

3 9 3 27 3 9 27

4 16 5 125 5 NULL 125

(c) Full Outer Join
• Full join creates the result set by combining the 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.

A B C

Number Square Number Cube Number Square Cube

2 4 2 8 2 4 8

3 9 3 27 3 9 27

4 16 5 125 4 16 NULL

5 NULL 125
Examples of Relational Algebra Queries

All the examples are based on the following three tables:

PROJECT (Project#, Project_Name, Chief_Architect)

EMPLOYEE (Emp#, EmpName)

ASSIGNED_TO (Project#, Emp#)

PROJECT (Project#, Project_Name, Chief_Architect)
EMPLOYEE (Emp#, EmpName)
ASSIGNED_TO (Project#, Emp#)

1. Get Emp# of employees working on project ‘Comp353’.

ΠEmp# (σProject# = ‘Comp353’ (ASSIGNED_TO) )

 PROJECT (Project#, Project_Name, Chief_Architect)
EMPLOYEE (Emp#, EmpName)
ASSIGNED_TO (Project#, Emp#)

2 Get the details of employees (both number and

name) working on project ‘COMP353’.

EMPLOYEE ⨝ ΠEmp# (σProject# = ‘Comp353’ (ASSIGNED_TO) )

PROJECT (Project#, Project_Name, Chief_Architect)
EMPLOYEE (Emp#, EmpName)
ASSIGNED_TO (Project#, Emp#)
3. Get the details of employees (Emp#, EmpName)
working on the ‘Database’ project.

EMPLOYEE ⨝
ΠEmp# (ASSIGNED_TO ⨝
ΠProject# ( σProject_Name = ‘Database’ (PROJECT) ) )
PROJECT (Project#, Project_Name, Chief_Architect)
EMPLOYEE (Emp#, EmpName)
ASSIGNED_TO (Project#, Emp#)
4. Get the details of employees (Emp#, EmpName) working
on both COMP353 and COMP354 projects.

EMPLOYEE ⨝
(ASSIGNED_TO ÷
ΠProject#(σ(Project# = ‘COMP353’ ∨ Project# = ‘COMP354’) (ASSIGNED_TO) ) ) )
 PROJECT (Project#, Project_Name, Chief_Architect)
EMPLOYEE (Emp#, EmpName)
ASSIGNED_TO (Project#, Emp#)

5. Find the employee numbers of employees who work on at

least all of the projects that employee 107 works on.

(ASSIGNED_TO ÷ ΠProject# (σ(Emp# = 107 ) (ASSIGNED_TO) ) ) - 107

PROJECT (Project#, Project_Name, Chief_Architect)
EMPLOYEE (Emp#, EmpName)
ASSIGNED_TO (Project#, Emp#)
6. Find the employee numbers of employees who
do not work on projects ‘COMP453’.

ΠEmp# (ASSIGNED_TO –
ΠEmp# (σProject# = ‘COMP453’ (ASSIGNED_TO) ) )
PROJECT (Project#, Project_Name, Chief_Architect)
EMPLOYEE (Emp#, EmpName)
ASSIGNED_TO (Project#, Emp#)

7. Find the employee numbers of employees who

work on all the projects.

ASSIGNED_TO ÷ Π Project# (PROJECT)

 PROJECT (Project#, Project_Name, Chief_Architect)
EMPLOYEE (Emp#, EmpName)
ASSIGNED_TO (Project#, Emp#)

8. List the employee numbers of employees other

than employee 107 who work on at least one
project that employee 107 works on.
(Π Emp# (ASSIGNED_TO ⨝ (σ(Emp# = 107 ) (ASSIGNED_TO) ) ) ) - 107
Borrower (borrower_id, loan_number)
Depositor (depositor_id, account_number)
Customer( customer_name, customer_street, customer_city)
Loan( loan_number, branch_name, amount)
Account( account_number, branch_name, balance)
Branch (branch_name, branch_city, assets)

1. Find the name of each branch located in “Chicago”.

Π branch_name ( σ(branch_city = ‘Chicago’ ) (Branch) )

 Borrower (borrower_id, loan_number)
Depositor (depositor_id, account_number)
Customer( customer_name, customer_street, customer_city)
Loan( loan_number, branch_name, amount)
Account( account_number, branch_name, balance)
Branch (branch_name, branch_city, assets)

2. Find the ID of each borrower who has a loan in branch “Downtown”.

Π borrower_id (Borrower ⨝ Π loan_number ( σbranch_name = ‘Downtown’ (Loan) ) )

OR

Π borrower_id ( σbranch_name = ‘Downtown’ (Borrower ⨝ Loan) )

Borrower (borrower_id, loan_number)
Depositor (depositor_id, account_number)
Customer( customer_name, customer_street, customer_city)
Loan( loan_number, branch_name, amount)
Account( account_number, branch_name, balance)
Branch (branch_name, branch_city, assets)

3. Find each loan number with a loan amount greater than $10000.

Π loan_number ( σamount > 10000 (Loan) ) )

Borrower (borrower_id, loan_number)
Depositor (depositor_id, account_number)
Customer( customer_name, customer_street, customer_city)
Loan( loan_number, branch_name, amount)
Account( account_number, branch_name, balance)
Branch (branch_name, branch_city, assets)

4. Find the ID of each depositor who has an account with a balance

greater than $6000

Πdepositor_id ( Depositor ⨝ ( Π account_number ( σbalance > 6000 (Account) ) ) )

Borrower (borrower_id, loan_number)
Depositor (depositor_id, account_number)
Customer( customer_name, customer_street, customer_city)
Loan( loan_number, branch_name, amount)
Account( account_number, branch_name, balance)
Branch (branch_name, branch_city, assets)

5. Find the ID of each depositor who has an account with a balance

greater than $6000 at the “Uptown” branch.

Πdepositor_id ( Depositor ⨝
( Π account_number ( σbalance > 6000 ˄ branch_name=‘Uptown’ (Account) ) ) )
Employee (person_name, street, city)
Works(person_name, company_name, salary)
Company (company_name, city)

• Find the name of each employee who lives in

city “Miami”.
Πperson_name ( σcity = ‘Miami’ (Employee) )
Employee (person_name, street, city)
Works(person_name, company_name, salary)
Company (company_name, city)

• Find the name of each employee whose salary

is greater than $100000.
Πperson_name ( σsalary > 100000 (Works) )
Employee (person_name, street, city)
Works(person_name, company_name, salary)
Company (company_name, city)

• Find the name of each employee who lives in “Miami” and

whose salary is greater than $100000.
Πperson_name ( σcity = ‘Miami’ ˄ salary > 10000 (Works ⨝ Employee) )
 employee(person-name, street, city)
works(person-name, company-name, salary)
company(company-name, city)
manages(person-name, manager-name)

Find the names of all employees who work for FirstBankCorporation.

Πperson_name ( σcompany_name = ‘FirstBankCorporation’ (Works) )
employee (person-name, street, city)
works (person-name, company-name, salary)
company (company-name, city)
manages (person-name, manager-name)

Find the names and cities of residence of all employees who

work for FirstBankCorporation.

Πperson_name, city (Employee ⨝

(Πperson_name ( σcompany_name = ‘FirstBankCorporation’ (Works) ) ) )
employee (person-name, street, city)
works (person-name, company-name, salary)
company (company-name, city)
manages (person-name, manager-name)

Find the names, street address, and cities of residence of all

employees who work for First Bank Corporation and earn more than
$10,000 per annum.

Employee ⨝
(Πperson_name(σcompany_name = ‘FirstBankCorporation’ ˄ salary > 10000 (Works) ) )
employee (person-name, street, city)
works (person-name, company-name, salary)
company (company-name, city)
manages (person-name, manager-name)

Find the names of all employees who live in the same

city as the company for which they work.

Πperson_name(Employee ⨝ Works ⨝ Company)

Employee (person-name, street, city)
Works (person-name, company-name, salary)
Company (company-name, city)
Manages (person-name, manager-name)
Find the names of all employees who live in the same city and on the
same street as do their managers.

Πperson_name( (Employee ⨝ Manages)

⨝ (manager_name=Emp.person_name ˄ [Link] = [Link] ˄ [Link] = [Link])
(ρEmp (Employee) ) )
 For more examples, follow the following
link
• Relational Algebra Exercises: Database Queries
& Schemas