CS820: ADVANCED DATABASE CONCEPTS
MSIT 2k23
LECTURE OUTLINE
RelationalAlgebra-Introduction
Unary Operators
Binary Operators
2
RELATIONAL ALGEBRA
3
RELATIONAL QUERY LANGUAGES
Languages for describing queries on a relational
database
Structured Query Language (SQL)
Predominant application-level query language
Declarative
Relational Algebra
Intermediate language used within DBMS
Procedural
4
WHAT IS AN ALGEBRA?
A language based on operators and a domain of
values
Operators map values taken from the domain
into other domain values
Hence, an expression involving operators and
arguments produces a value in the domain
When the domain is a set of all relations (and
the operators are as described later), we get the
relational algebra
We refer to the expression as a query and the
value produced as the query result 5
RELATIONAL ALGEBRA
Domain: set of relations
Basic operators: select, project, union, set difference,
Cartesian product
Derived operators: set intersection, division, join
Procedural: Relational expression specifies query by
describing an algorithm (the sequence in which
operators are applied) for determining the result of an
expression
6
RELATIONAL ALGEBRA IN A DBMS
7
RELATIONAL ALGEBRA- OVERVIEW
} Relational Algebra consists of several
groups of operations:
◦ Unary Relational Operations
SELECT (symbol: s (sigma))
PROJECT (symbol: p (pi))
RENAME (symbol: r (rho))
◦ Relational Algebra Operations From Set Theory
UNION ( È ), INTERSECTION ( Ç ), DIFFERENCE (or MINUS, –
)
CARTESIAN PRODUCT ( x )
◦ Binary Relational Operations
JOIN (several variations of JOIN exist)
DIVISION
◦ Additional Relational Operations
OUTER JOINS, OUTER UNION 8
AGGREGATE FUNCTIONS
UNARY OPERATOR
9
UNARY RELATIONAL OPERATIONS
} Operate on one relation. These
include:
◦ SELECT (symbol: s (sigma))
◦ PROJECT (symbol: p (pi))
◦ RENAME (symbol: r (rho))
10
SELECTION OPERATOR
11
SELECT OPERATOR
Produce table containing subset of rows of argument
table satisfying condition
condition relation
Example:
Person Hobby=‘stamps’(Person)
Id Name Address Hobby Id Name Address Hobby
1123 John 123 Main stamps 1123 John 123 Main stamps
1123 John 123 Main coins 9876 Bart 5 Pine St stamps
5556 Mary 7 Lake Dr hiking
9876 Bart 5 Pine St stamps
12
SELECTION CONDITION
Operators: <, , , >, =,
Simple selection condition:
<attribute> operator <constant>
<attribute> operator <attribute>
<condition> AND <condition>
<condition> OR <condition>
NOT <condition>
13
SELECTION CONDITION - EXAMPLES
Id>3000 Or Hobby=‘hiking’ (Person)
Id>3000 AND Id <3999 (Person)
NOT(Hobby=‘hiking’) (Person)
Hobby‘hiking’ (Person)
14
PROJECT OPERATOR
15
PROJECT OPERATOR
Produces table containing subset of columns of
argument table
attribute list(relation)
Example:
Person Name,Hobby(Person)
Id Name Address Hobby Name Hobby
1123 John 123 Main stamps John stamps
1123 John 123 Main coins John coins
5556 Mary 7 Lake Dr hiking Mary hiking
9876 Bart 5 Pine St stamps Bart stamps
16
PROJECT OPERATOR
17
EXPRESSIONS
Id, Name ( Hobby=’stamps’ OR Hobby=’coins’ (Person) )
18
PROJECT OPERATION PROPERTIES
} PROJECT creates a vertical partitioning.
◦ The list of specified columns (attributes) is kept in
each tuple.
◦ The other attributes in each tuple are discarded.
} If the attribute list includes only non-key
attributes, duplicate tuples are likely to occur.
} The PROJECT operation removes any duplicate
tuples.
19
SINGLE VS. SEQUENCE OF RELATIONAL OPERATION
} We may want to apply several relational
algebra operations one after the other.
◦ 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.
20
SINGLE VS. SEQUENCE OF RELATIONAL OPERATION
21
RENAME OPERATOR
22
RENAME OPERATOR
23
RENAME OPERATOR
where the symbol ‘ρ’ is used to denote the RENAME operator and R
is the result of the sequence of operation or expression which is
saved with the name X.
24
RENAME OPERATOR
} The general RENAME operation r can be
expressed by any of the following forms:
◦ rS(R) changes:
the relation name only to S
◦ r(B1|B1’, B2|B2’,….., Bn|Bn’ )(R) changes:
the column (attribute) names only to B1’, B2’, ….., Bn’
◦ rS (B1|B1’, B2|B2’,….., Bn|Bn’ )((R) changes both:
the relation name to S, and
the column (attribute) names to B1’, B2’, ….., Bn’
25
RENAME OPERATOR-EXAMPLE 1
Query
The student table is renamed with newstudent
26
RENAME OPERATOR-EXAMPLE 1
Query
The student table is renamed with newstudent
27
RENAME OPERATOR-EXAMPLE 2
Query
The name, branch column of student table are renamed and
newbranch
28
RENAME OPERATOR-EXAMPLE 2
Query
The name, branch column of student table are renamed and
newbranch
29
RENAME OPERATOR-EXAMPLE 3
Query to rename the relation
Student as Male Student
and the attributes of Student
RollNo, SName as (Sno, Name).
30
RENAME OPERATOR-EXAMPLE 3
Query to rename the relation
Student as Male Student
and the attributes of Student
RollNo, SName as (Sno, Name).
31
THANK YOU