Database Systems
Course Code: INSY122
1
Topic 5: Relational Algebra
2
Relational algebra
• Basic set of operations for the relational
model
• Similar to algebra that operates on numbers
• Operands and results are relations instead of
numbers
Relational algebra expression
• Composition of relational algebra operations
• Possible because of closure property
Model for SQL
• Explain semantics formally
• Basis for implementations
• Fundamental to query optimization 3
SELECT OPERATOR
Unary operator (one relation as operand)
• Returns subset of the tuples from a relation that satisfies a selection
condition:
𝜎<𝑠𝑒𝑙𝑒𝑐𝑡𝑖𝑜𝑛 𝑐𝑜𝑛𝑑𝑖𝑡𝑖𝑜𝑛> 𝑅
where <selection condition>
• may have Boolean conditions AND, OR, and NOT
• has clauses of the form:
<attribute name> <comparison op> <constant value>
or
<attribute name> <comparison op> <attribute name>
• Applied independently to each individual tuple t in operand
• Tuple selected iff condition evaluates to TRUE
Example:
𝜎 𝐷𝑛𝑜=4 AND 𝑆𝑎𝑙𝑎𝑟𝑦>2500 OR (𝐷𝑛𝑜=5 AND 𝑆𝑎𝑙𝑎𝑟𝑦>30000) EMPLOYEE
4
5
WHAT IS THE EQUIVALENT RELATIONAL ALGEBRA EXPRESSION?
6
PROJECT OPERATOR
7
WHAT IS THE EQUIVALENT RELATIONAL ALGEBRA
EXPRESSION?
8
WORKING WITH LONG EXPRESSIONS
9
OPERATORS FROM SET THEORY
10
11
Union R U S
• returns a relation instance containing all tuples that occur in
either relation instance R or relation instance S (or both).
• R and S must be union- compatible, and the schema of the result
is defined to be identical to the schema of R.
• It also eliminates duplicate tuples.
• For a union operation to be valid, the following conditions must hold
• R and S must be the same number of attributes.
• Attribute domains need to be compatible.
• Duplicate tuples should be automatically removed.
∏ author (Books) ∪ ∏ author (Articles)
Output − Projects the names of the authors who have either written a book
or an article or both.
12
Intersection: R ∩ S
• returns a relation instance containing all tuples that occur in
both R and S.
• The relations R and S must be union-compatible, and the schema
of the result is defined to be identical to the schema of R.
• ∏ Student_Name (COURSE) ∩ ∏ Student_Name (STUDENT)
• Output: Only those rows that are present in both the tables will
appear in the result set.
13
Set-difference: R − S
• returns a relation instance containing all tuples that occur in R
but not in S.
• The relations R and S must be union-compatible, and the schema
of the result is defined to be identical to the schema of R.
• Finds all the tuples that are present in r but not in s.
• ∏ author (Books) − ∏ author (Articles)
• Output − Provides the name of authors who have written books but
not articles.
14
Cross-product (Cartesian product ): R × S
• returns a relation instance whose schema contains all the fields of R (in
the same order as they appear in R) followed by all the fields of S
(in the same order as they appear in S).
• The result of R × S contains one tuple
• σauthor = 'tutorialspoint'(Books Χ Articles)
• Output − Yields a relation, which shows all the books and articles written
by tutorialspoint.
15
JOIN OPERATOR
16
17
18
