The Relational Algebra and SQL

The 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, and assignment.
The select, project, and rename operations are called unary operations, because they operate
on one relation. The other three operations operate on pairs of relations and are, therefore,
called binary operations.

Table

instructor(id, name, dept_name, salary)

department(dept_name, building, budget)
section(course_id, sec_id, semester, year,
building, room_number, time_slot_id)
teaches(ID , course_id, sec_id, semester,
The Select Operation year)
The select operation selects tuples that satisfy a
given predicate. We use the lowercase Greek
letter sigma (𝞂) to denote selection. The predicate appears as a subscript to 𝞂.

Structure: 𝞂p(r) [p = predicate / condition, r = releation]

1. To select those tuples of the instructor relation where the instructor is in the “Physics”
department.
𝞂dept_name = “Physics”(instructor)
2. To find all instructors with salary greater than $90,000
𝞂salary > 90000(instructor)
3. To find the instructors in Physics with a salary greater than $90,000
𝞂dept_name = “Physics” ^ salary > 90000 (instructor)

The Project Operation

Suppose we want to list all instructors’ ID , name, and salary, but do not care about the dept
name. The project operation allows us to produce this relation. 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
(∏).
1. Show the ID, name, salary.
∏ID, name, salary (instructor )
2. Show the all instructor name who are take the “Physics”
∏name(𝞂dept_name = “Physics”(instructor))

3. Show the instructor name, salary, tax(10%)

∏name, salary, ((salary*10)/100) as tax (instructor )

The Union Operation(∪)

1. Consider a query to find the set of all courses taught in the Fall 2009 semester, the
Spring 2010 semester, or both.
To find the set of all courses taught in the Fall 2009 semester
∏course id (𝞂semester = “Fall” ∧ year =2009 (section))
To find the set of all courses taught in the Spring 2010 semester
∏course id (𝞂semester = “Spring” ∧ year =2010 (section))

Final output:

∏course id (𝞂semester = “Fall” ∧ year =2009 (section)) ∪ ∏course id (𝞂semester = “Spring” ∧ year =2010
(section))

Therefore, 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.

The Set-Difference Operation

The set-difference operation, denoted by −, allows us to find tuples that are in one relation but
are not in another. The expression r − s produces a relation containing those tuples in r but not
in s.
1. find all the courses taught in the Fall 2009 semester but not in Spring 2010 semeste
∏course id (𝞂semester = “Fall” ∧ year =2009 (section)) − ∏course id (𝞂semester = “Spring” ∧ year =2010
(section))

The Set-Intersection Operation

The first additional relational-algebra operation that we shall define is set intersection (∩)
1. To find the set of all courses taught in both the Fall 2009 and the Spring 2010 semesters.
∏course id (𝞂semester = “Fall” ∧ year =2009 (section)) ∩ ∏course id (𝞂semester = “Spring” ∧ year =2010
(section))
The Cartesian-Product Operation
The Cartesian-product operation, denoted by a cross (×), allows us to combine
information from any two relations. We write the Cartesian product of relations r1 and r2
as r1 × r2 .
For example, the relation schema for r = instructor × teaches is
Instructor Table
Id Name Dept_name Salary(k)

10101 Salam CSE 56

10102 Rafiq EEE 65

10103 Jabbor CSE 67

Teaches Table
Id course_id sec_id semester year

10101 CSE-2423 A 4 2022

10102 EEE-1221 B 1 2022

r = instructor × teaches is
Ins.Id Ins.Na Ins.De Inst.Sal Tea.ID Tea.Co Tea.se Tea.se Tea.ye
me pt_nam ary urse_id c_id mester ar
e

10101 Salam CSE 56 10101 CSE-2 A 4 2022

423

10101 Salam CSE 56 10102 EEE-1 B 1 2022

221

10102 Rafiq EEE 65 10101 CSE-2 A 4 2022

423

10102 Rafiq EEE 65 10102 EEE-1 B 1 2022

221

10103 Jabbor CSE 67 10101 CSE-2 A 4 2022

423

10103 Jabbor CSE 67 10102 EEE-1 B 1 2022

221
** Find the teacher name, and course_d who taken course in CSE departmet
∏name, course id (𝞂 instructor.ID = teaches.ID (𝞂dept name = “CSE” (instructor × teaches)))
Rename
Unlike relations in the database, the results of relational-algebra expressions do not
have a name that we can use to refer to them. It is useful to be able to give them
names; the rename operator, denoted by the lowercase Greek letter rho (𝞺), lets us do
this. Given a relational-algebra expression E, the expression 𝞺x(E)
𝞺x(E) = 𝞺x(A1, A2, …., An)(E)

** From instructor table find the maximum salary.

∏instructor.salary − ∏instructor.salary (𝞂instructor.salary < d.salary (instructor ×
𝞺 d (instructor )))
Output of this instruction = instructor × 𝞺 d (instructor)
Id Name Dept_na Salary(k d.id d.Name d.Dept_ d.Slary(
me ) name k)

10101 Salam CSE 56 10101 Salam CSE 56

10101 Salam CSE 56 10102 Rafiq EEE 65

10101 Salam CSE 56 10103 Jabbor CSE 67

10102 Rafiq EEE 65 10101 Salam CSE 56

10102 Rafiq EEE 65 10102 Rafiq EEE 65

10102 Rafiq EEE 65 10103 Jabbor CSE 67

10103 Jabbor CSE 67 10101 Salam CSE 56

10103 Jabbor CSE 67 10102 Rafiq EEE 65

10103 Jabbor CSE 67 10103 Jabbor CSE 67

Output => ∏instructor.salary (𝞂instructor.salary < d.salary (instructor × 𝞺 d
(instructor )))
Salary

56

56

65
Final Output: ∏instructor.salary − ∏instructor.salary (𝞂instructor.salary < d.salary
(instructor × 𝞺 d (instructor )))

=> {56, 65, 67} - {56, 65} => {67}

Join
Natural Join(⋈):
We can perform a Natural Join only if there is at least one common attribute that exists between
two relations. In addition, the attributes must have the same name and domain.
Natural join acts on those matching attributes where the values of attributes in both the relations
are same.

Instructor Table
Id Name Dept_name Salary(k)

10101 Salam CSE 56

10102 Rafiq EEE 65

10103 Jabbor CSE 67

Teaches Table
Id course_id sec_id semester year

10101 CSE-2423 A 4 2022

10102 EEE-1221 B 1 2022

Instructor ⋈ Teaches

Id Name Dept_na Salary(k course_i sec_id semeste year

me ) d r

10101 Salam CSE 56 CSE-24 A 4 2022

23

10102 Rafiq EEE 65 EEE-12 B 1 2022

21
Left Outer Join(R S):
Instructor Teaches
Id Name Dept_na Salary(k course_i sec_id semeste year
me ) d r

10101 Salam CSE 56 CSE-24 A 4 2022

23

10102 Rafiq EEE 65 EEE-12 B 1 2022

21

10103 Jabbor CSE 67 - - - - - - - -

Right Outer Join(R S):

Instructor Teaches
Id Name Dept_ Salary cours Id cours sec_id seme year
name (k) e_id e_id ster

10101 Salam CSE 56 CSE- 10101 CSE- A 4 2022

2423 2423

10102 Rafiq EEE 65 EEE-1 10102 EEE-1 B 1 2022

221 221

Full Outer Join: ( R S)

Instructor Teaches

Id Name Dept_ Salary cours Id cours sec_id seme year

name (k) e_id e_id ster

10101 Salam CSE 56 CSE- 10101 CSE- A 4 2022

2423 2423

10102 Rafiq EEE 65 EEE-1 10102 EEE-1 B 1 2022

221 221

10103 Jabbo CSE 67 - - - - - - - - - - - -

r
Division operation
The division operator is used for queries which involve the ‘all’.
R1 ÷ R2 = tuples of R1 associated with all tuples of R2.
Example: Retrieve the name of the subject that is taught in all courses.
R1

Name Course

System Btech

Database Mtech

Database Btech

Algebra Btech

R2

Course

Btech

Mtech

Final output: R1 ÷ R2

Name

database

Assignment Operation
Aggregation
The second extended relational-algebra operation is the aggregate operation 𝓖 (calligraphic G)
, which permits the use of aggregate functions such as min or average, on sets of values.
Aggregate functions take a collection of values and return a single value as a result. Some
aggreagate function are sum, avg, max, min, count.
** Find the sum of the salary from instruction table.
𝓖 sum(salar y) (instructor )
** Find the average of the salary from instruction table.
𝓖 avg(salar y) (instructor )

Deletion
** Delete all the course which are teach on 2019
Teaches ← Teaches - 𝞂year = 2019(Teaches)

Insert
** Add an instructor Borkhot in an instructor table salary is 54k and he also take course where
course_id = cse-1224, sec_id = 2, semester = 2, year = 2023.
Instructor_id ← Instructor ∪ {(“Borkhot”, 54)}
Teaches ← {(Instructor_id, “cse-1224”, 2, 2, 2023)}
Update
** Salary of all the instructe over 55k increse 7%, whereas all other receive 5%
Instructor ← ∏id, name, dept_name, salary*1.07(𝞂salary>=55(Instructor))

∪ ∏id, name, dept_name, salary*1.05(𝞂salary<55(Instructor))

View
View is nothing but scope or permission with the help of which a portion of the database will be
made available to the user. User cannot see the full version of the database but he/she can see
only the portion which is required to him/her.

** Create vew v as <query expression> here v is the view name

1. Find the teacher name, and course_id who taken course in CSE departmet
Create view all_cse_dept_instructor as
∏name, course_id (𝞂 instructor.ID = teaches.ID (𝞂dept name = “CSE”
(instructor × teaches)))
2. Find the all cse dept teacher name who are taken cse-2423 course from
all_cse_dept_instructor view.
∏name (𝞂course_id = ‘cse-2423’(all_cse_dept_instructor))
Insert into view
● Create view dept_short_details as
∏id, name, dept_name(instructor)
 Inset into dept_short_details view:
dept_short_details ← dept_short_details ∪ {(‘34’, ‘dummy’, ‘DIS’)}

It will insert tuple (‘34’, ‘dummy’, ‘DIS’, NULL) to instructor.

Suggestion: Do not something insert through view

Nested sub-queries
A sub-query is a select form where expression that is nested within another query.
A common use of subquery is to perform on:
● Set membership
● Set comparison
● Test of empty relation and
● Test for absence of duplicate tuple

There are few rules that subqueries must follows:

1. Subqueries must be enclosed within parenthesis
2. A subquries can have only one column in the select cluse, unless multiple column are
in the main query for the subquery to compare with its selected cluse.
3. An order by or group by command cannot be used in subquery but main query can
use an order by or group by.
4. Subquries that returns more than one row can only be used with multiple value
operators such as in operator.
5. Between operator cannot be used with a subquery. However that between operator can
be used within the subquery.

Banking database

branch(branch_name, branch_city, assets)

customer (customer_name, customer_street, customer_city)
loan (loan number, branch_name, amount)
borrower (customer_name, loan_number)
account (account_number, branch_name, balance)
depositor (customer_name, account_number)
Set membership
● The in connective test for set
membership, where the set is collection of values produced by a select cluse.
● The not in connective test for the absence of set membership
● The in or not in operators can also be used on enumerated sets.
** Find those customer name who are having both account and loan on bank
Select custmer_name from borrower
Where customer_name in(select cutomer_name form depositor)
** Find those customer name who are having only account but no loan account on bank
Select custmer_name from borrower
Where customer_name not in(select cutomer_name form depositor)
Set Comparison
Set comparison sql
➔ <some, <= some, > some, >= some, = some and <>some
➔ <all, <= all, > all, >= all, = all and <> all

** Find the name of all branches that have assets greater than those of at least one branch
located at “chittagong”.
Select
branch_name
from
branch
Where
assets > some (select assets
Form branch
Where brach_city = ‘chittagong’
)
** Find the name of all branches that have assets greater than those of at least one branch
located at “chittagong”.
Select
branch_name
from
branch
Where
assets > all (select assets
Form branch
Where brach_city = ‘chittagong’
)
Example: A ={100, 200, 300} B={300, 400, 500}
A > some B False A > all B False
A >= some B True A >= all B False
A < some B True A < all B True
A <= some B True A <= all B True
A = some B True A = all B False
A <> some B True A <> all B False

** at least 1 value of A …………

Get More Information:
1. https://www.geeksforgeeks.org
2. https://www.youtube.com/watch?v=fHAfc7Hjq28&list=PLWPirh4EWFpGrpcMfZ6UcdI786
QdtSxV8

MMA
Adjunct Faculty, IIUC