Relational Algebra
4.1 Background
 Considered: structural properties and constraints
 Want to consider: foundations of database operations
Types of Operations
 Query: mapping of a database into a relation
 Update: mapping between databases
Origin of Operations
 From set theory
 Specifically developed for relational databases
The Approach
 Relational algebra:
 A procedural language
 Based on algebraic concepts
 Algebra:
 Studies quantities
 Refers to relations and properties by symbols
4.2 Set Theory Operations
 Union (A ∪ B)
 Intersection (A ∩ B)
 Difference (A  B)
 Defined only for relations over identical attributes
Student 
Name  Number 
Ben  3412 
Dan  1234 
Nel  2341 


Instructor 
Name  Number 
Don  3412 
Nel  2341 




Student ∪ Instructor 
Name  Number 
Ben  3412 
Dan  1234 
Don  3412 
Nel  2341 



Student  Instructor 
Name  Number 
Ben  3412 
Dan  1234 



The above expressions are undefined, if ‘Administrator’ replaces ‘Instructor’
4.3 Renaming (ρ_{S(B1,...,Bn)}(R))
 The relation name from R to S: ρ_{S}(R)
 The attributes to B_{1},...,B_{n}: ρ_{(B1,...,Bn)}(R)
 Both: ρ_{S(B1,...,Bn)}(R)
Use
Facilitates set operations.
ρ_{(Name,Number)}(Administrator) 
Name  Number 
Don  3412 
Ron  4123 


Student ∪ ρ_{(Name,Number)}(Administrator) 
Name  Number 
Ben  3412 
Dan  1234 
Nel  2341 
Don  3412 
Ron  4123 


4.4 Assignments (S(B_{1},...,B_{n}) ← R)
The renaming operation under alternative notation
Student ∪ ρ_{Temp(Name,Number)}(Administrator)
vs.
Temp(Name,Number) ← Administrator
Student ∪ Temp
4.5 Selection σ_{<selection condition>}(R)
The condition must be a propositional formula
 Clauses

of the operations {=,<,≤,>,≥,≠} on attributes
 Boolean operations

AND, OR, NOT
Student 
Name  Number  Sex 
Ben  3412  M 
Dan  1234  M 
Nel  2341  F 


σ_{(Name>“BEN”) AND (Number<2000)}(Student)  

An attribute with unordered domain allows only the comparison operations =,≠.
4.6 Projection (π_{<attribute list>}(R))
 The selection operation chooses tuples (rows of tables)
 The projection operation chooses attributes (columns of tables)
Student 
Name  Number  Sex 
Ben  3412  M 
Dan  1234  M 
Nel  2341  F 


π_{Name, Number}(Student) 
Name  Number 
Ben  3412 
Dan  1234 
Nel  2341 


Identical tuples collapse into a single tuple
4.7 Cross Join (R × S)
Also called Cartesian product and cross product. Combines the attributes of the given relations, in all possible
ways.
R(A_{1},...,A_{n}) × S(B_{1},...,B_{m}) = Q(A_{1},...,A_{n},B_{1},...,B_{m})
Instructor 
Name  Number 
Don  3412 
Nel  2341 


Administrator 
Adm  Num 
Dean  3412 
Secretary  4123 



Instructor × Administrator 
Name  Number  Adm  Num 
Don  3412  Dean  3412 
Don  3412  Secretary  4123 
Nel  2341  Dean  3412 
Nel  2341  Secretary  4123 


Application
 Combine related information with the cross join operation
Instructor × Administrator 
Name  Number  Adm  Num 
Don  3412  Dean  3412 
Don  3412  Secretary  4123 
Nel  2341  Dean  3412 
Nel  2341  Secretary  4123 

 Extract tuples with the selection operation
Temp ← σ_{Number = Num} (Instructor × Administrator) 
Name  Number  Adm  Num 
Don  3412  Dean  3412 


 Weed out attributes with the projection operation
π_{Name,Number,Adm}(Temp) 
Name  Number  Adm 
Don  3412  Dean 


4.8 Join (R_{<join condition>}S)
R(A_{1},...,A_{n})_{<condition>}S(B_{1},...,B_{m}) = Q(A_{1},...,A_{n},B_{1},...,B_{m})
Instructor 
Name  Number 
Don  3412 
Nel  2341 


Administrator 
Adm  Num 
Dean  3412 
Secretary  4123 



Instructor _{(Number=Num)} Administrator 
Name  Number  Adm  Num 
Don  3412  Dean  3412 


 Cross product operation restricted to combinations which satisfy the stated conditions
 Cross product includes all possible combinations of tuples
 Join = cross product + selection
 Called equijoin if only relational operations ‘=’ are in use.
Application
Variant of the previous example:
Temp
← Instructor
_{(Number=Num)} Administrator
Result
← π_{Name, Number,Adm}(
Temp)
4.9 Natural Join (RS)
 Naturaljoin = equijoin + projection
 The join checks for equality of values of common attributes
 The projection collapses together the common attributes
Instructor 
Name  Number 
Don  3412 
Nel  2341 


Administrator 
Adm  Number 
Dean  3412 
Secretary  4123 



Instructor Administrator 
Name  Number  Adm 
Don  3412  Dean 


 When the operands have no attribute in common, the natural join operations reduces to the join cross (i.e.,
Cartesian product) operation
4.10 Outer Joins
 A join operation is complete, if all the tuples of the operands contribute to the result.
 Tuples not participating in the result are said to be dangling
 Outer join operations are variants of the join operations in which the dangling tuples are appended with NULL
fields. They can be categorized into left, right, and full outer joins.
Instructor ][ Administrator 
Name  Number  Adm 
Don  3412  Dean 
Nel  2341  NULL 
NULL  4123  Secretary 

Instructor ] Administrator 
Name  Number  Adm 
Don  3412  Dean 
Nel  2341  NULL 

4.11 Division (R ÷ S)
R(Z) ÷ S(X) equals maximal T(Z  X) which satisfies T(Z  X) × S(X) ⊆ R(Z)
R 
A  B 
a  1 
a  2 
a  3 
b  2 
b  3 
b  4 
c  1 
c  2 

  
 
Visited 
Name  City 
Ben  Columbus 
Dan  Cincinnati 
Dan  Columbus 
Nel  Cleveland 
Nel  Columbus 

 city visited by every one? 

4.12 A Complete Set of Operations
 A subset of the relational algebra operations is complete, if it can express any relational algebra operation
 {σ,π,∪,,×} is complete for relational algebra
 The extra operations are provided for convenience
 The outer join operations, and the following operations, can’t be expressed in terms of the basic relational algebra
operations
Example: A ∩ B = A ∪ B  ((A  B) ∪ (B  A))
4.13 Global Aggregate Functions
 Aggregate Functions (ℱ_{<function list>}(R))

Mathematical functions on collections of values from the database: SUM, AVERAGE, MAXIMUM,
COUNT
Student 
Name  Number  Sex 
Ben  3412  M 
Dan  1234  M 
Nel  2341  F 

 
 Grouping (_{<grouping attributes>}ℱ_{<function list>}(R))

of tuples in a relation by the value of some of their attributes
_{Sex}ℱ_{
COUNT, SUM(Number)}(Student) 
Sex  Count  SUM 
M  2  4646 
F  1  2341 

Note: ℱ denotes the character scriptF
[Problem 6.21]
4.14 Assignment #2
Due: Mon, Feb 2
Problems 6.16, 6.22 (a–d, f), and 5.17 (5th ed) / 5.16 (4th ed) from the textbook (pages 214,
217, 169 5th ed; 186, 188, 147 4th ed)
Notes
 If you submit your homework electronically, use the departmental submit utility: submit XXX lab2
filename. XXX represents ‘c670aa’ for students of the 3:30 section, and ‘c670ab’ for students of the 5:30
section.
 Files are restricted to 1,048,576 bytes
 The submit program issues error messages to the student’s CSE email account
Q&A
Reference: Ch. 5.3, 6.1–6.5 in textbook.