Relational Algebra

4.1 Background

Types of Operations

Origin of Operations

The Approach

4.2 Set Theory Operations

Student
NameNumber
Ben3412
Dan1234
Nel2341
Instructor
NameNumber
Don3412
Nel2341
Administrator
NameNum
Don3412
Ron4123
Student Instructor
NameNumber
Ben3412
Dan1234
Don3412
Nel2341
Student Instructor
NameNumber
Nel2341
Student - Instructor
NameNumber
Ben3412
Dan1234

The above expressions are undefined, if ‘Administrator’ replaces ‘Instructor’

4.3 Renaming (ρS(B1,...,Bn)(R))

Use

Facilitates set operations.
ρ(Name,Number)(Administrator)
NameNumber
Don3412
Ron4123
Student ρ(Name,Number)(Administrator)
NameNumber
Ben3412
Dan1234
Nel2341
Don3412
Ron4123

4.4 Assignments (S(B1,...,Bn) 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
NameNumberSex
Ben3412 M
Dan1234 M
Nel2341 F
σ(Name>“BEN”) AND (Number<2000)(Student)
NameNumberSex
Dan1234 M

An attribute with unordered domain allows only the comparison operations =,.

4.6 Projection (π<attribute list>(R))

Student
NameNumberSex
Ben3412 M
Dan1234 M
Nel2341 F
πName, Number(Student)
NameNumber
Ben3412
Dan1234
Nel2341

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(A1,...,An) × S(B1,...,Bm) = Q(A1,...,An,B1,...,Bm)

Instructor
NameNumber
Don3412
Nel2341
Administrator
AdmNum
Dean3412
Secretary4123
Instructor × Administrator
NameNumberAdmNum
Don3412Dean3412
Don3412Secretary4123
Nel2341Dean3412
Nel2341Secretary4123

Application

4.8 Join (R⊳⊲<join condition>S)

R(A1,...,An)⊳⊲<condition>S(B1,...,Bm) = Q(A1,...,An,B1,...,Bm)

Instructor
NameNumber
Don3412
Nel2341
Administrator
AdmNum
Dean3412
Secretary4123
Instructor ⊳⊲(Number=Num) Administrator
NameNumberAdmNum
Don3412Dean3412

Application

Variant of the previous example:

Temp Instructor ⊳⊲(Number=Num) Administrator
Result πName, Number,Adm(Temp)

4.9 Natural Join (R⊳⊲S)

4.10 Outer Joins

4.11 Division (R ÷ S)

R(Z) ÷ S(X) equals maximal T(Z - X) which satisfies T(Z - X) × S(X) R(Z)

R
AB
a1
a2
a3
b2
b3
b4
c1
c2
S1
B
1
2
R÷S1
A
a
c
    
Visited
NameCity
BenColumbus
DanCincinnati
DanColumbus
NelCleveland
NelColumbus
city visited by every one?

4.12 A Complete Set of 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
NameNumberSex
Ben3412 M
Dan1234 M
Nel2341 F
COUNT(Student)
Count
3
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 CountSUM
M 2 4646
F 1 2341

Note: ℱ denotes the character script-F

[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

Q&A

Reference: Ch. 5.3, 6.1–6.5 in textbook.