SAI VIDYA INSTITUTE OF TECHNOLOGY

BACHELOR OF ENGINEERING

Department of Computer Science and Engineering

Database Management System
Module-2
Ch-5: Relational Model
Ch-8: Relational Algebra
Ch-9: Mapping Conceptual Design into a Logical Design
 Text Books:
1. Fundamentals of Database Systems, Ramez Elmasri and Shamkant B. Navathe, 7th Edition, 2017, Pearson.
2. Database management systems, Ramakrishnan, and Gehrke, 3rd Edition, 2014, McGraw Hill
 Chapter-8
The Relational Algebra

3
Outline

 Introduction

 Unary Relational Operations: SELECT, PROJECT.

 Relational Algebra Operations from Set Theory.

 Binary Relational Operations: JOIN, DIVISION.

 Additional Relational Operations.

 Examples of Queries in Relational Algebra

4
Introduction
• Relational Algebra(RA) came in 1970 and given by EF code father of DBMS.
• RA is also called procedural language or formal query language.
• In procedural or formal query language programmer or user who is writing query has to
mentions two things:
• What to do? and
• How to do?
• RA is like C-programming, we have to mention what we actually want to do plus how to do
that work. This is the meaning of procedural language.
• RA came in 1970’s!!! Why is it important? What is its value in today’s time?

5
Introduction
• Actually what we use in today’s time that is SQL, it is the base of relational
algebra.
• RA is the collection of mathematical expression.
• By using various mathematical expressions a theoretical model is being made
to show how to access data.
• But that mathematical expression model is used to make SQL.
• We use SQL, Oracle, Microsoft sequel server, IBM DB2 whatever platform we
are using, what is used there? SQL!! But who has helped in making this SQL?
• The base of all the technology of accessing data is Relational Algebra.
6
Introduction
Operators

Basic Operators Derived Operators

Projection (π) Join (⨝)

Selection (σ) Intersect (∩)
(X ∩ Y) = X-(X-Y)
Cross Product (X)
Division (/, ÷)
Union (U)
Rename (ρ)
Set Difference (-)

7
Projection in RA
• Projection (π) operator.
• What is the use of projection operation?
Student
RollNo Name Age
1 A 20
2 B 21
3 A 19

• What is the function of π operator?

• Retrieving the data.
• Whenever we have to fetch the data from a particular column, it can be one or more than
one column, then we will have to use π projection.
• Query1: Retrieve the RollNo from the table Student.
8
 Projection in RA cntd.. Student
RollNo Name Age
• To fetch the RollNo, how we write in RA is:
1 A 20
2 B 21
π RollNO (Student) 3 A 19
• π means project. Project means from the whole table, extract only the RollNo (as per query)

• Query2: Retrieve the RollNo and Name from the table Student.

• To fetch the RollNo and Name, how we write in RA is:

π RollNO,Name (Student) RollNo Name

1 A
2 B
3 A
Table: Result of the above Query2

9
Projection in RA cntd..
• The Projection (π) operator always works in DISTINCT format. Student
RollNo Name Age
• Distinct means unique. 1 A 20

π
Name
2 B 21
• Ex: Name (Student) A
3 A 19
B
• Projection (π) operator by default will give unique answer, it wont show duplicate data.
• Fetching RollNo and Name wont be a problem in the above table student because in that
table 1,A and 3,A are different(unique). They are not duplicate.
• Projection we always write in the last, before that we write other operators.

10
Selection in RA
• Selection (σ) operator.
• What is the use of Selection operation?
Student
RollNo Name Age
1 A 20
2 B 21
3 A 19

• What is the function of σ operator?

• Selecting the data.
• Whenever we have to select the data from a particular row(tuple), then we will have to
use σ operator
• Query1: Retrieve the Name of the student whose RollNo=2.
11
Selection in RA cntd..
• Finally what I have to show as per the query?
 Retrieve the Name.
• Retrieve means π (we have to apply projection operation).
• But projection operation we always apply at last. So first select the data as per the condition
given in the query: Student
σRollNo=2 (Student) RollNo Name Age
Output of this is 2, B, 21 1 A 20
2 B 21

πName(σ RollNo=2 (Student))

3 A 19
FinalOutput

• “Selection works on rows(tuples). Projection works on columns(attributes)”

12
Selection in RA cntd..
• Say suppose I do reverse: Student
RollNo Name Age
σRollNo=2 (πName(Student))
1 A 20
Output σRollNo=2 (A,B) 2 B 21
3 A 19

• “Selection operator first extracts the desired tuple. From that tuple what output should
be shown will be projected by projection operation”

13
8.1 Unary Relational Operations: SELECT, PROJECT
1. The Select Operation
• The SELECT operation is used to choose a subset of the tuples from a relation that satisfies
a selection condition.
• We can consider the SELECT operation to be a filter that keeps only those tuples that satisfy
a qualifying condition.
• The SELECT operation can also be visualized as a horizontal partition of the relation into
two sets of tuples—those tuples that satisfy the condition and are selected, and those tuples
that do not satisfy the condition and are filtered out.
• For example, to select the EMPLOYEE tuples whose department is 4, or those whose salary
is greater than $30,000, we can individually specify each of these two conditions with a
SELECT operation as follows:
14
Unary Relational Operation: SELECT
• In general, the SELECT operation is denoted by

• where the symbol σ (sigma) is used to denote the SELECT operator and the selection
condition is a Boolean expression (condition) specified on the attributes of relation R.
• The Boolean expression specified in <selection condition> is made up of a number of
clauses of the form:

15
Unary Relational Operation: SELECT
• where <attribute name> is the name of an attribute of R, <comparison op> is normally one of the
operators {=, <, ≤, >, ≥, ≠}, and <constant value> is a constant value from the attribute domain.
• Clauses can be connected by the standard Boolean operators and, or, and not to form a general
selection condition.
• For example, to select the tuples for all employees who either work in department 4 and make
over $25,000 per year, or work in department 5 and make over $30,000, we can specify the
following SELECT operation:
The result is shown in Figure(a).

16
Unary Relational Operation: SELECT
• The SELECT operator is unary; that is, it is applied to a single relation.
• Moreover, the selection operation is applied to each tuple individually; hence, selection
conditions cannot involve more than one tuple.
• The number of tuples in the resulting relation is always less than or equal to the number of
tuples in R.
• The fraction of tuples selected by a selection condition is referred to as the selectivity of the
condition.
•Fraction of rows selected
👉 Example:
•Selected: 2 rows
•Total: 4 rows
•Selectivity = 2/4 = 0.5 17
Unary Relational Operation: SELECT

18
Unary Relational Operations: SELECT, PROJECT
2. The Project Operation
• If we think of a relation as a table, the SELECT operation chooses some of the rows from
the table while discarding other rows.
• The PROJECT operation, on the other hand, selects certain columns from the table and
discards the other columns.
• If we are interested in only certain attributes of a relation, we use the PROJECT operation to
project the relation over these attributes only. Therefore, the result of the PROJECT
operation can be visualized as a vertical partition of the relation into two relations:
• One has the needed columns (attributes) and contains the result of the operation, and the
other contains the discarded columns. For example, to list each employee’s first and last
name and salary, we can use the PROJECT operation as follows:
19
Unary Relational Operation: PROJECT

The resulting relation is shown in Figure(b).

20
Unary Relational Operation: PROJECT
• The general form of the PROJECT operation is

• where π (pi) is the symbol used to represent the PROJECT operation, and <attribute list> is
the desired sub list of attributes from the attributes of relation R.
• The result of the PROJECT operation has only the attributes specified in <attribute list> in
the same order as they appear in the list. Hence, its degree is equal to the number of
attributes in <attribute list>.
• If the attribute list includes only nonkey attributes of R, duplicate tuples are likely to occur.
The PROJECT operation removes any duplicate tuples, so the result of the PROJECT
operation is a set of distinct tuples. This is known as duplicate elimination.

PROJECT = select columns + remove duplicates

Sequences of Operations and the RENAME Operation (ρ)
• The relations shown in Figure 8.1 that depict operation results do not have any names.
• In general, for most queries, we need to apply several relational algebra operations one after
the other.
Sequences of Operations and the RENAME Operation (ρ) Cntd...
• Either we can write the operations as a single relational algebra expression by nesting the
operations, or we can apply one operation at a time and create intermediate result relations.
• In the latter case, we must give names to the relations that hold the intermediate results.
• For example, 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.
• We can write a single relational algebra expression, also known as an in-line expression, as
follows:

Figure 8.2(a) shows the result of this in-line relational algebra

expression.
Sequences of Operations and the RENAME Operation (ρ) Cntd...
• Alternatively, we can explicitly show the sequence of operations, giving a name to each
intermediate relation, and using the assignment operation, denoted by ← (left arrow), as
follows:

• It is sometimes simpler to break down a complex sequence of operations by specifying

intermediate result relations than to write a single relational algebra expression.
• We can also use this technique to rename the attributes. To rename the attributes in a relation,
we simply list the new attribute names in parentheses, as in the following example:

 These two operations are illustrated in Figure 8.2(b) 

 Rename operation (ρ-Rho) is used to change the name
of a table (relation) or its columns (attributes).
Sequences of Operations and the RENAME Operation Cntd...
• The general RENAME operation when applied to a relation R of degree n is denoted by any of
the following three forms:

• where the symbol ρ (rho) is used to denote the RENAME operator, S is the new relation
name, and B1, B2, … , Bn are the new attribute names.
• The first expression renames both the relation and its attributes, the second renames the
relation only, and the third renames the attributes only.

• In SQL, a single query typically represents a complex relational algebra expression.

• Renaming in SQL is accomplished by aliasing using AS, as in the following example: