Data Model:
MarvelHeroes(hid, hero, realname)
DCHeroes(hid, hero, realname)
Movies(mid, moviename, year)
HeroInMovie(hid, mid)
Relational Algebra
----------------------
Basic Definitions:
input: set of tuples, a relation, or two relations
Output: set of tuples
Set Compatibility:
Two relations are set compatible, if they have the same schema: the same
set of attributes
Set Operations:
R Union S
R Intersection S
R Set Difference S
are defined for two relations R and S, only if R and S are set compatible.
R Union S = { tuples t such t is either in relation R or in relation S}
R Intersection S = { tuples t such that t is in both relation R and S }
R Set Difference S = { tuples t such that t is in R but not in S }
----
Rename
T1 = MarvelHeroes
T1 has the same schema as MarvelHeroes
T2(hid1, hero1, realname1) = T1
T3(hid1, hero, realname) = T1
number of attributes and number of tuples does not change.
Ugrad(RIN, Name)
Grad(GradRIN, FullName)
Ugrad and Grad are not set compatible.
T1(RIN, Name) = Grad
T2 = T1 union Ugrad
----------------
SELECTION
[[slice operation]]
SELECT_C R = {t in R such that t satisfies the boolean condition C}
The result of selection has the same schema as R, but has only those tuples
that satisfy the boolean condition
Movies
MID MOVIENAME YEAR
--------------------------------------------
1 Superman: Man of Tomorrow 2020
2 Spiderman: Homecoming 2017
3 Wonder Woman 2017
4 Deadpool 2016
5 Green Lantern 2011
6 Black Panther 2018
7 Thor 2011
8 Thor: The Dark World 2013
9 Avengers 2012
- Find all movies that are made after year 2011.
T1 = SELECT_{Year > 2011} (Movies)
T1
MID MOVIENAME YEAR
--------------------------------------------
1 Superman: Man of Tomorrow 2020
2 Spiderman: Homecoming 2017
3 Wonder Woman 2017
4 Deadpool 2016
8 Thor: The Dark World 2013
9 Avengers 2012
All movies with Thor in its title
T2 = SELECT_{Moviename like 'Thor%'} (Movies)
T2
MID MOVIENAME YEAR
--------------------------------------------
7 Thor 2011
8 Thor: The Dark World 2013
Find movies made after 2011 with title tat starts with Thor
T3 = SELECT_{Moviename like 'Thor%' AND year > 2011} (Movies)
MID MOVIENAME YEAR
--------------------------------------------
8 Thor: The Dark World 2013
Projection
-------------
PROJECT_{A1,...AN} (R) = {t in R but only the values for the attributes A1,...,AN}
where A1,...,AN are attributes in R
T4 = PROJECT_{MID, MOVIENAME} (Movies)
T4
MID MOVIENAME
---------------------------------
1 Superman: Man of Tomorrow
2 Spiderman: Homecoming
3 Wonder Woman
4 Deadpool
5 Green Lantern
6 Black Panther
7 Thor
8 Thor: The Dark World
9 Avengers
All years a movie was made in !
T5 = PROJECT_{YEAR} (Movies)
[[A relation is a set of tuples]]
YEAR
----
2020
2017
2016
2018
2011
2013
2012
-- Find all heroes who have not been featured in a movie
-- T1: all heroes who have been features in a movie
T1 = PROJECT_{hid} (HeroInMovie)
-- T2: all heroes in my database
T2 = Marvel_Heroes UNION DC_Heroes
--- T2 is not set compatible with T1
T3 = PROJECT_{hid} (T2)
--- All heroes who have not been in a movie
Result = T3 - T1