Relational Algebra
Relational Algebra
Practice Queries
CS430/630
Lecture 3
Slides based on “Database Management Systems” 3rd ed, Ramakrishnan and Gehrke
Relational Algebra
Basic operations:
Selection Selects a subset of rows from relation
Projection Deletes unwanted columns from relation
Cross-product Allows us to combine several relations
Join Combines several relations using conditions
Division A bit more complex, will cover later on
Set-difference Union Intersection
Renaming Helper operator, does not derive new result, just
renames relations and fields
F contains oldname newname pairs
)),(( EFR
Operator Precedence
In decreasing order of priority:
1. Selection Projection
2. Cross-product Join
3. Set-difference Intersection
4. Union
Example Schema
sid sname rating age
22 dustin 7 45.0
31 lubber 8 55.5
58 rusty 10 35.0
sid bid day
22 101 10/10/96
58 103 11/12/96
Reserves
Sailors
bid name color
101 interlake red
103 clipper green
Boats
Find names of sailors who’ve reserved boat #103
Sample Query 1
))Re((
103
Sailorsserves
bidsname
))Re(
103
( Sailorsserves
bidsname
sid sname rating age
sid bid day
Reserves
Sailors
bid name color
Boats
Example Schema
sid sname rating age
22 dustin 7 45.0
31 lubber 8 55.5
58 rusty 10 35.0
sid bid day
22 101 10/10/96
58 103 11/12/96
Reserves
Sailors
bid name color
101 interlake red
103 clipper green
Boats
Sample Query 2
Find names of sailors who’ve reserved a red boat
sname color red
Boats serves Sailors((
‘ ‘
) Re )
))Re))
”
(((( SailorssBoats
redcolorbidsidsname
sid sname rating age
sid bid day
Reserves
Sailors
bid name color
Boats
Example Schema
sid sname rating age
22 dustin 7 45.0
31 lubber 8 55.5
58 rusty 10 35.0
sid bid day
22 101 10/10/96
58 103 11/12/96
Reserves
Sailors
bid name color
101 interlake red
103 clipper green
Boats
Sample Query 3
Find names of sailors who’ve reserved a red or a green boat
( , (
‘ ‘ ‘ ‘
))Tempboats
color red color green
Boats
sname Tempboats serves Sailors( Re )
sid sname rating age
sid bid day
Reserves
Sailors
bid name color
Boats
Example Schema
sid sname rating age
22 dustin 7 45.0
31 lubber 8 55.5
58 rusty 10 35.0
sid bid day
22 101 10/10/96
58 103 11/12/96
Reserves
Sailors
bid name color
101 interlake red
103 clipper green
Boats
Sample Query 4
Find names of sailors who’ve reserved a red and a green boat
( , ((
‘ ‘
) Re ))Tempred
sid color red
Boats serves
sname Tempred Tempgreen Sailors(( ) )
( , ((
‘ ‘
) Re ))Tempgreen
sid color green
Boats serves
sid sname rating age
sid bid day
Reserves
Sailors
bid name color
Boats
Example Schema
sid sname rating age
22 dustin 7 45.0
31 lubber 8 55.5
58 rusty 10 35.0
sid bid day
22 101 10/10/96
58 103 11/12/96
Reserves
Sailors
bid name color
101 interlake red
103 clipper green
Boats
Sample Query 5
Find names of sailors who’ve reserved only red boats
( , ((
‘ ‘
) Re ))Tempred
sid color red
Boats serves
))(( SailorsTempothersTempredsname
))Re)
”
((,( servesBoats
redcolorsid
Tempothers
sid sname rating age
sid bid day
Reserves
Sailors
bid name color
Boats
An Example of Self-Joins
sid sname rating age
22 dustin 7 45.0
31 lubber 8 55.5
58 rusty 10 35.0
Sailors
Find sailors with maximum age
An Example of Self-Joins
sid sname rating age
22 dustin 7 45.0
22 dustin 7 45.0
22 dustin 7 45.0
31 lubber 8 55.5
31 lubber 8 55.5
31 lubber 8 55.5
58 rusty 10 35.0
58 rusty 10 35.0
58 rusty 10 35.0
sid sname rating age
22 dustin 7 45.0
31 lubber 8 55.5
58 rusty 10 35.0
22 dustin 7 45.0
31 lubber 8 55.5
58 rusty 10 35.0
22 dustin 7 45.0
31 lubber 8 55.5
58 rusty 10 35.0
Join condition: “left” age smaller than “right” age
An Example of Self-Joins
Finally, subtract the resulting left hand side from the
initial relation, and you get sailors with maximum ages
Final result is
)2
.2.1
1
),44,33,22,11((
S
ageSageS
S
ffffTempJoin
LeftHalfSailors
),1( SailorsS ),2( SailorsS
)
4,3,2,1
,( TempJoin
ffff
LeftHalf