Chapter 08

The Relational Algebra and Relational Calculus (Plus QBE- Appendix

C)

By: Miss Aiman Iftikhar

What is Relational Algebra?
Relation = Table
1) Relational algebra is the basic set of operations for the relational model

BASIC SET OF OPERATIONS.

The Basic Operation Relational algebra include:
• SELECT
• PROJECT
• JOIN
• UNION
• INTERSECT
• DIFFERENCE
• etc.

By: Miss Aiman Iftikhar

2) These operations let a user describe basic data-retrieval requests, also known as queries.
When we retrieve data from a database, we use statements that express some condition.

For example:
• Students with marks greater than 80
• Employees who work in the IT department
• Products priced below 500
When we run an SQL query, the database does not execute SQL directly in its raw form.
Internally, the query is translated into a logical representation based on relational algebra
rules, and then the query optimizer decides the best way to execute it.

3) The result of any relational-algebra operation is always a new relation.

This new relation can be created from one input relation or from several input relations.
Because every operation produces another relation, the system is said to be closed.
In other words, everything you work with in relational algebra, and everything you get
back, remains a relation.

By: Miss Aiman Iftikhar

4) Relational-algebra operations create new relations, and these newly created relations can
be used again as input for more operations of the same algebra.
It works like building with Lego bricks: every piece you create becomes another piece you
can attach something to, letting you keep constructing bigger and more complex results.

5) A series of relational-algebra operations put together is called a relational-algebra

expression.
When this full expression is evaluated, the final output is also a relation.
That final relation represents the answer to a database query, the data you asked the system
to retrieve.

By: Miss Aiman Iftikhar

 Brief History of Origins of Algebra
Muhammad ibn Musa al-Khwarizmi was a Muslim mathematician from the
region of Central Asia, often associated with present-day Morocco. He wrote the
first official book on algebra, titled Al-Jabr. In this book, he explained methods
for solving equations and working with variables. Later, European scholars
translated the book into Latin for better understanding. During this translation,
the title Al-Jabr was adapted to Algebra, and from that point onward, the term
“algebra” became widely used.

Al-Khwarizmi referred to a variable as “Shay,” which means “thing” in Arabic. When this
knowledge reached Spain, the Spanish wrote “Shay” as “Xay”, because in Spanish, the letter X
is pronounced like “sh.”
Over time, “Xay” was shortened to “X”, which is why we commonly use X to represent a
variable in mathematics today. In other words, the use of X in math comes directly from Al-
Khwarizmi’s term “Shay.”

By: Miss Aiman Iftikhar

Relational Algebra
• Relational Algebra consists of several groups of operations.
1) Unary Relational Operations
1) SELECT (symbol:  (sigma))
2) PROJECT (symbol:  (pi))
3) RENAME (symbol:  (rho))
2) Relational Algebra Operations From Set Theory
1) UNION (  )
2) INTERSECTION (  )
3) DIFFERENCE (or MINUS, – )
4) CARTESIAN PRODUCT ( x )
3) Binary Relational Operations
1) JOIN (several variations of JOIN exist)
2) DIVISION
4) Additional Relational Operations
1) Outer Joins , outer union
2) Aggregate Functions (SUM, COUNT, AVG, MIN, MAX)
By: Miss Aiman Iftikhar
In the following examples, we use this
Company Database

By: Miss Aiman Iftikhar

Unary Relational Operations:
• SELECT Operation (σ – Sigma)
 Purpose: To pick some rows (tuples) from a table (relation) based on a condition.
 Filter: The condition works like a filter.
 Keep: Only rows that satisfy the condition are kept.
 Discard: Rows that do not meet the condition are removed.

Example:
Question: Select the EMPLOYEE tuples whose department number is 4:
Relational algebra (unary selection): σ DNO=4 ​(EMPLOYEE)
Mysql: SELECT * FROM EMPLOYEE WHERE DNO = 4;
Sigma
Example:
Question: Select the employee tuples whose salary is greater than $30,000:
Relational algebra (unary selection): σ salary >= 30,000​(EMPLOYEE)
Mysql: SELECT * FROM EMPLOYEE WHERE salary >= 30,000;

By: Miss Aiman Iftikhar

SELECT Operation (σ – Sigma) Cont’d:
The select operation is used to pick rows (tuples) from a table (relation) that meet a certain
condition.
It is written as:
Syntax
σ condition (R)

where R is the table and condition is a true/false rule applied to the rows.
Only the rows that satisfy the condition are included in the result.
Rows that don’t satisfy the condition are ignored.
The Greek letter σ (sigma) represents the select operation.

By: Miss Aiman Iftikhar

SELECT Operation Properties:
1. SELECT keeps the same schema
When you perform a select operation:
σ condition (R)
The result has the same columns (attributes) as the original table R, it just filters the rows.
Example:
EMPLOYEE table has columns (EmpID, Name, DNO, Salary).

If you do:
σ DNO=4 (EMPLOYEE)
the result still has all four columns, but only for employees in department 4.

By: Miss Aiman Iftikhar

2. Commutative property
The select operation is commutative, meaning the order of conditions doesn’t matter.
Mathematically:
σ condition1 (σ condition2 (R)) = σ condition 2 ( σ condition1 (R)
Example:
condition1 = Salary > 5000
condition2 = DNO = 4

σ Salary > 5000 ( σ DNO = 4 (EMPLOYEE)) = σ DNO=4 ( σ Salary > 5000 (EMPLOYEE))
You’ll get the same rows no matter which condition you apply first.

In MYSQL:
SELECT * FROM EMPLOYEE WHERE Salary > 5000 AND DNO = 4;
SELECT * FROM EMPLOYEE WHERE DNO = 4 AND Salary > 5000;

By: Miss Aiman Iftikhar

3. Cascading SELECTS
You can apply multiple selects one after another, called a cascade:

σcond1(σcond2(σcond3(R)))

Because of commutativity, the order of applying conditions doesn’t matter:

σ cond1 ​( σ cond2 ​( σ cond3 ​(R))) = σ cond2 ​( σ cond3​( σ cond1​(R)))

4. Combining multiple conditions into one

Instead of writing several cascaded selects, you can combine all conditions into a single select using
AND:

σ cond1 ( σ cond2 ( σ cond3 (R))) = σ cond1 AND cond2 AND cond3(R)

This makes queries simpler and easier to read.

Example:
σ DNO = 4 ( σ Salary > 5000 (EMPLOYEE)) = σ DNO=4 AND Salary>5000 (EMPLOYEE)
By: Miss Aiman Iftikhar
5. Number of rows decreases or stays the same
SELECT only filters rows; it does not add new rows.
So, the number of rows in the result is always < or = the number of rows in the original table R.
Example:
EMPLOYEE has 100 rows.
After
σ DNO = 4 (EMPLOYEE), maybe only 30 rows satisfy the condition.

All Properties In short:

 SELECT filters rows but keeps columns.
 Order doesn’t matter for multiple selects.
 Multiple selects can be combined with AND.
 Result is always smaller or equal in size to the
original table.

By: Miss Aiman Iftikhar

Unary Relational Operations:
• PROJECT Operation (π - PI)
The project operation is used to pick certain columns from a table
and ignore the other columns.
It is written as:
SYNTAX:

πcolumn1, column2, …(R)

where R is the table and the columns are the ones you want to keep.
This is called vertical partitioning because you are keeping some PI
columns (vertically) and dropping others.
Each row in the result only has the selected columns; all other columns
are removed.
By: Miss Aiman Iftikhar
Example:
If you want to see only each employee’s last name, first name, and salary:
In Unary Relatioanl:

π LNAME,FNAME,SALARY (EMPLOYEE)

In MYSQL:

SELECT LNAME,FNAME,SALARY FROM EMPLOYEE

Result will have just these three columns, no matter how many other columns exist in EMPLOYEE.

By: Miss Aiman Iftikhar

PROJECT Operation (π - PI) Cont’d:
General form:
π attribute list (R)
 π (pi) is the symbol for the project operation.
 attribute list = the columns you want to keep from the table R.

PROJECT removes duplicate rows automatically.

Reason: The result must be a set of tuples, and in mathematics, sets cannot have duplicates.
Example:
π LNAME , FNAME (EMPLOYEE)
• Will return only the LNAME and FNAME columns.
• If two employees have the same name, only one copy will appear in the result

The set {1,2,3} is the same as {1,2,2,3,3} because repeated elements are ignored.
This is not a programming quirk, it’s a mathematical rule.
By: Miss Aiman Iftikhar
PROJECT Operation Properties:
1) NUMBER OF TUPLES

Projection selects only certain attributes (columns) from a relation R.

If some tuples in R become identical when you keep only the projected attributes,
duplicates are removed because the result of a projection is a set of tuples.
So, the resulting number of tuples can never be more than the original, and may be less
if duplicates are eliminated.
Example:
Table EMPLOYEE (R):

FNAME LNAME SALARY DEPT AGE

Result (S):
LNAME FNAME SALARY

By: Miss Aiman Iftikhar

Duplicated rows (SELECT DISTINCT)
Original Table
FNAME LNAME SALARY
Ali Khan 50000
Ali Khan 50000
Sara Ahmed 60000

Relational Algebra Query

π LNAME, FNAME, SALARY (EMPLOYEE)

After Projection (Duplicates Removed):

LNAME FNAME SALARY

Khan Ali 50000
Ahmed Sara 60000

By: Miss Aiman Iftikhar

2) If the list of attributes includes a key

then the number of tuples in the result of PROJECT is equal to the number of tuples in R.
A key is an attribute (or set of attributes) that uniquely identifies each tuple.
If your projection keeps the key, then all tuples remain distinct, because no two tuples can have
the same key.
Therefore, projection does not reduce the number of tuples in this case.
Example:
ID FNAME LNAME SALARY
1 Ali Khan 50000
2 Ali Khan 50000
3 Sara Ahmed 60000

“PROJECT IS NOT COMMUTATIVE”

By: Miss Aiman Iftikhar

 Differences
Feature Select (σ) Project (π)
What it filters Rows (tuples) Columns (attributes)
Based on A condition (e.g., A > 5) A list of attributes
Effect on rows Keeps or removes rows based May reduce rows if duplicates
on the condition appear

Effect on columns No change Removes all attributes not listed

Duplicate handling Does nothing about duplicates Collapses identical tuples into
one

Intuition Which rows do I want? Which columns do I want?

By: Miss Aiman Iftikhar

 Relational Algebra Expressions
When working with relational algebra, we often need to perform several operations
in sequence. We can do this in two ways. One option is to nest the operations
inside a single expression, where the output of one feeds directly into the next. The
other option is to perform each operation one at a time, producing intermediate
results after each step. If we follow the step-by-step approach, every intermediate
relation must be given a name, so it can be used as input for the following
operation. This keeps the process organized and easy to reference.

By: Miss Aiman Iftikhar

NEST THE OPERATIONS OR SINGLE
EXPRESSION
QUESTION:
To retrieve the first name, last name, and salary of all employees who
work in department number 5, we must apply a select and a project operation

In MYSQL:
SELECT first_name, last_name, salary FROM employees WHERE
department_number = 5;

In Relational algebra Operation :

USING SINGLE EXPRESSION
π LNAME, FNAME, SALARY( σ DNO = 5 ​(EMPLOYEE))

By: Miss Aiman Iftikhar

 INTERMEDIATE RELATION:
Same Question But in intermediate relation:

We can explicitly show the sequence of operations, giving a name to each intermediate
relation.

In MYSQL:
WITH DEP5_emp AS ( SELECT * FROM EMPLOYEE WHERE DNO
=5)
SELECT LNAME, FNAME, SALARY
FROM DEP5_emp;

In Relational algebra Operation :

USING INTERMEDIATE RELATION:
DEP5_emp <= σ DNO = 5 ​(EMPLOYEE)
RESULT <= π LNAME, FNAME, SALARY(DEP5_emp)
By: Miss Aiman Iftikhar
Unary Relational Operations:
• RENAME Operation ( ρ - rho)
The RENAME operator is represented by the symbol ρ (rho).
It is used to change the name of a relation, rename attributes, or rename
both.

Why do we use the RENAME operator?

1) To avoid confusion in complex queries
When a query involves multiple steps, renaming helps keep the expressions
simple and clear. rho
2) When the same relation is used more than once
For example, in self-joins, you must rename the relation to use it again.
3) To avoid attribute name conflicts
During operations like JOIN, if two relations have attributes with the same name, renaming
becomes necessary.
By: Miss Aiman Iftikhar
1. Rename BOTH relation and its attributes
Form:
ρ S (B1, B2, …, Bn)(R)
Meaning:
The relation R is renamed to S
All attribute (column) names are changed to B1, B2, …, Bn
Example:
R = EMPLOYEE(SSN, Name, Salary)
ρ WORKERS (ID, FullName, Pay)(EMPLOYEE)
Now relation name = WORKERS, and column names = ID, FullName, Pay
2. Rename ONLY the relation (table) name
Form:
ρ S (R)
Meaning:
Only the relation name changes
Attribute names remain the same
Example:
ρ EMP (EMPLOYEE)
Only the table name changes from EMPLOYEE → EMP
By: Miss Aiman Iftikhar
 3. Rename ONLY the attribute (column) names
Form:
ρ (B1, B2, …, Bn)(R)
Meaning:
Only the column names change
Relation name stays the same
Example:
ρ (ID, FullName, Pay)(EMPLOYEE)
Only the column names change.

Important Note
Order of attributes matters!
Because the new names replace the old names in the
same order.

By: Miss Aiman Iftikhar

Important Note
The symbol ← is an assignment operator
It means:
“Put the result of this operation into this new relation.”
Example:
RESULT ← something
means
Create a new relation called RESULT.
Shorthand Renaming
Table: Student
Roll Name Age
1 Ali 20

If we write:
RESULT ← π Name, Age (STUDENT)
Its means that result is a new table and have the same attribute names as
student

By: Miss Aiman Iftikhar

Projection WITH rename
But if we write:
RESULT (N, A) ← ρ (N, A)(STUDENT)
New names replace old names in same order
2) Rename ALL attributes (new column names)
Case 1: Projection without rename SELECT
→ Old column names stay same FNAME AS F,
Case 2: Projection with rename list MNAME AS M,
→ New names replace old names in same order LNAME AS L
FROM DEP5_EMPS;

In MySQL:
1) Rename the whole relation (table name)
ALTER TABLE DEP5_EMPS
RENAME TO NEW_TABLE;

By: Miss Aiman Iftikhar

Binary Algebra Operations from set theory:
1) Set Operations (Binary)
Binary set operations = need two relations/tables
Examples:
• UNION ( ∪ )
• INTERSECTION ( ∩ ) 3. Resulting Relation Attribute Names
• SET DIFFERENCE ( − ) When you do: R1 ∪ R2
2) Type Compatibility The resulting table takes attribute names of the
For these operations to work: first table (R1)
[Link] number of attributes (columns) This is a convention in relational algebra
1. R1(A1, A2, ..., An) and R2(B1, B2, ..., Bn) Example:
2. Both must have exactly n columns R1(A, B)
[Link] columns must have compatible domains R2(X, Y)
(types) R1 ∪ R2 → result columns = A, B (not X, Y)
1. Ai in R1 and Bi in R2 must have same data type
2. Example: if A1 = INT, B1 must also be INT
Easy words:
“Two tables can only be UNIONED(or INTERSECTED, or
DIFFERENCED) if they look the same , same number of
columns and each column has the same type.”

By: Miss Aiman Iftikhar

Binary Algebra Operations from set theory:
• UNION Operation (∪)

UNION is a binary operation (works on two relations/tables)

Denoted by ∪ (or sometimes UNION)
Combines all tuples (rows) from both relations R and S
Duplicates are removed automatically
Easy words:
“Take all rows from R, all rows from S, but don’t repeat the Union
same row twice.”

By: Miss Aiman Iftikhar

 Rules / Requirements (UNION Compatible)
For UNION to work:
[Link] number of columns in both tables
[Link] columns must be of the same type (or compatible)
Example:
• If R has columns (ID, Name, Age)
• S must also have 3 columns in the same order and compatible types: (ID, Name, Age)

Result of UNION
If: R: S:
ID Name ID Name
1 Ali 2 Sara
2 Sara 3 Ahmed

R ∪ S: ID Name
1 Ali
Notice: Duplicate row (2, Sara) is removed
2 Sara
3 Ahmed
By: Miss Aiman Iftikhar
VENN DIAGRAM OF UNION:

By: Miss Aiman Iftikhar

Scenario:
•We want social security numbers (SSN) of all employees who:
• Work in department 5 (RESULT1)
• OR supervise an employee who works in department 5 (RESULT2)
Step 1: Select employees of department 5
DEP5_EMPS ← σ DNO=5 (EMPLOYEE)
• σ DNO=5 (EMPLOYEE) → Pick rows where department = 5
• Save them in a temporary table DEP5_EMPS
Step 2: Project SSNs of employees in department 5
RESULT1 ← π SSN(DEP5_EMPS)
• π SSN(...) → Take only the SSN column
• RESULT1 has all SSNs of dept 5 employees
Step 3: Project SSNs of supervisors of dept 5 employees
RESULT2(SSN) ← π SUPERSSN(DEP5_EMPS)
• SUPERSSN = SSN of the supervisor
• RESULT2 has all SSNs of supervisors of employees in dept 5
By: Miss Aiman Iftikhar
Step 4: UNION of both results
RESULT ← RESULT1 ∪ RESULT2
• Combine RESULT1 and RESULT2
• Removes duplicates automatically
• RESULT now has all SSNs either working in dept 5 or supervising dept 5 employees

CONCLUSION:
RESULT1 → Dept 5 employees
RESULT2 → Supervisors of dept 5 employees
RESULT → everyone from RESULT1 OR RESULT2
SQL Version of the Same Example

-- Step 1 & 2: SSNs of employees in dept 5 -- Step 3: SSNs of supervisors of employees in dept 5
SELECT SSN SELECT SUPERSSN AS SSN
UNION
FROM EMPLOYEE FROM EMPLOYEE
WHERE DNO = 5 WHERE DNO = 5;

By: Miss Aiman Iftikhar

Binary Algebra Operations from set theory:
• INTERSECTION Operation (∩)
• INTERSECTION Denoted by ∩
• R ∩ S = all rows that are in BOTH R and S
• Only common rows are kept
• Duplicates automatically removed
R1 (Name, Age): Name Age
Ali 20
Intersection
Sara 25
R2 (Name, Years):
Fname Years
Sara 25
Ahmed 30

By: Miss Aiman Iftikhar

R1 ∩ R2 → Column names = Name, Age

Name Age
Sara 25

Another Example Of Intersection:

R1(A, B) R2(X, Y) R1 ∩ R2

1, 10 2, 20 2, 20

2, 20 3, 30

By: Miss Aiman Iftikhar

VENN DIAGRAM OF INTERSECTION:

By: Miss Aiman Iftikhar

Binary Algebra Operations from set theory:
• SET DIFFERENCE (-)

Also called MINUS or EXCEPT

Denoted by −
R − S = all rows that are in R but NOT in S
Easy words:
“Take rows from R, remove all rows that also appear in S.”
SET DIFFERENCE

• “SET DIFFERENCE = R MINUS S”

• Only rows unique to R

By: Miss Aiman Iftikhar

VENN DIAGRAM OF SET DIFFERENCE:

By: Miss Aiman Iftikhar

Properties of UNION, INTERSECTION, and
DIFFERENCE
1. Commutative Property (Order doesn’t matter)
UNION and INTERSECTION are commutative.
R∪S=S∪R and R∩S=S∩R

2. Associative Property (Grouping doesn’t matter)

UNION and INTERSECTION are associative
R ∪ (S ∪ T) = (R ∪ S) ∪ T and (R ∩ S) ∩ T = R ∩ (S ∩ T)

•“It doesn’t matter which two tables you combine first, the final result is the same.”

By: Miss Aiman Iftikhar

3. Difference (−) is NOT commutative
SET DIFFERENCE is not commutative
What it means:
R − S ≠ S − R (in general)
Order matters here!
R − S → rows in R but NOT in S
S − R → rows in S but NOT in R → usually different
Example:
R: {1, 2, 3}
S: {2, 3, 4}
R − S = {1} S − R = {4}
Notice: Different results

By: Miss Aiman Iftikhar

 QUESTION
Scenario: Company Employee Management
The company has two departments: IT and HR. They maintain a database of employees:
Employee Relation:
EID Name Dept Salary Age
1 Ali IT 50000 30
2 Sara HR 60000 28
3 Ahmed IT 55000 35
4 Kinza HR 58000 32
5 Javeria IT 52000 29

HR_Bonus Relation:

Name Bonus
Sara 50000
Kinza 60000
Javera 55000
By: Miss Aiman Iftikhar
Question 01) Find all employees in IT
IT_EMP ← σ Dept = “IT” (Employee)

Question 02) Get only names and salaries of IT employees:

IT_TABLE ← π Name, Salary (IT_EMP)

Question 03) RENAME THE SALARY TO MONTHLY_PAY FOR CLARITY

ρ (Name, MONTHLY_PAY) (IT_TABLE)

Question 04) Employees who either have IT salary or HR bonus:

π Name (IT_EMP) ∪ π Name(HR_BONUS)

Question 05) Employees who are both in IT and also listed in HR bonus:
π Name (IT_EMP) ∩ π Name(HR_BONUS)
Question 06) Employees in IT but not in HR_BONUS: :
π Name (IT_EMP) - π Name(HR_BONUS)
By: Miss Aiman Iftikhar