Lecture 2:
-------------

Class exercise to be released later today (some config debugging in progress!)

A database is a set of relations.

A relation is a set of tuples, each tuple has values for attributes of
that relation.

Key: A key is a minimal set of attributes such that no two tuples in
the relation can have the same values for the key.

Student(RIN, SSN, FirstName, LastName, Email)

Key: RIN
     SSN
     Email


StudentInfo(RIN, SSN, FirstName, LastName, Email, Major, Advisor)

Students can have multiple majors, for a major and student, there is a
single advisor, an advisor can only advise one major for student

Key: RIN, Major
     Email, Najor
     SSN, Major
     RIN, Advisor
     Email, Advisor
     SSN, Advisor


StudentInfo2(RIN, SSN, FirstName, LastName, Email, Major, Advisor)

Students can have multiple majors, for a major and student, there is a
single advisor

Key: RIN, Major
     Email, Najor
     SSN, Major


Relational Algebra
---------------------

Relational algebra consists of a set of operations that takes as input
a set of tuples (or two sets of tuples) and return a new relation (a
new set of tuples).


MarvelHeroes(heroname, realname, power, location, multiverseid)
Key: heroname, multiverseid


DCHeroes(heroname, realname, power, location, multiverseid)
Key: heroname, multiverseid

Movies(movieid, name, release_date)
HeroInMovie(movieid, heroname, multiverseid)

Set Operations
------------------

Set compatibility: Two relations are set compatible, if they have the
same data model: same attributes and attributes have the same name.

Set Union:

R union S = {set of all tuples that are either in R or S or both if R
and S are set compatible}


Set Intersection

R interect S = {set of all tuples that are either in R and S if R
and S are set compatible}

Set Difference

R - S = {the set of all tuples in R that are NOT in S, if R and S are
set compatible}

-----------------

Example:

R1 = MarvelHeroes union DCHeroes
All heroes in either universe

R2 = MarvelHeroes - DCHeroes
All heroes that are only in the Marvel Universe
(in this case, same as MarvelHeroes)

R3 = MarvelHeroes intersect DCHeroes
All heroes that are in both Marvel and DC Universes
(in this case empty)


Projection
--------------
Given a relation R

Project_{A1,...,An} (R) = {all tuples in R, but only the attributes in the projection}

Find name of all heroes in the Marvel Universe
R1 = project_{heroname} (MarvelHeroes)

Find all multiverses in the Marvel Universe
R2 = project_{multiuniverseid} (MarvelHeroes)


R3 = (project_{multiverseid} (MarvelHeroes)) intersect
     (project_{multiverseid} (DCHeroes))
All multiverses that are common (assuming the same id means the same universe)

Find all locations that are unique to MarvelUniverse Heroes

R4 = (project_{location} (MarvelHeroes) -- all locations in Marvel Universe
R5 = (project_{location} (DCHeroes) -- all locations in DC Universe
R6 = R4 - R5 --- locations only in Marvel Universe

R7 = project_{heroname, multiverseid} (MarvelHeroes)
R8 = project_{locations, multiverseid} (MarvelHeroes)


Selection
------------

Given a relation R and a boolean condition C over the attributes of R:

select_C (R) = {the set of all tuples in R that satisfy the condition C}

Find name of all heroes who are located in NYC and in multiverse 1.


R1 = project_{heroname} (select_{location='NYC' and multiverseid=1} (MarvelHeroes))

R2 = project_{heroname} (select_{location='NYC' and multiverseid=1} (DCHeroes))
R3 = R1 union R2

----------
Find name of all heroes from multiverse=2 who where in a movie.

project_{heroname} (select_{multiverseid = 2} (HeroInMovie))


Find name of all Marvel heroes from multiverse=2 who where in a movie.

(project_{heroname} (select_{multiverseid = 2} (HeroInMovie)))
intersect (project_{heroname} ((select_{multiverseid = 2} MarvelHeroes))

-----
Rename operator

Given a relation R with data model R(A1,A2,...,An), rename will name
every attribute in R.

S(B1,B2,...,Bn) = R

------------

Cartesian Product
-----------------

Given two relations R(A1,..,An) and S(B1,..,Bm) such that R and S do
not have any attributes in common,

R x S = {Set of all tuples (r,s) such that r is a tuple in R, s is a
tuple in S, and (r,s) has attributes (A1,..,An,B1,..,Bm)}

-----------------------

Find heroes and the multiverse they are from in the new spiderman movie 

M1(movieid1, name1, release_date1) = Movies  --- rename

R1 = M1 x HeroInMovie
R2 = select_{movieid1=movieid} (R1)
R3 = select_{moviename = 'No way home'} (R2)
R4 = project_{heroname, multiverseid} (R3)

-----------------------

Find all locations that the heroes in the 'Avengers End Game' are from.

Movies -> moviename
HeroInMovie -> to find which hero is in the movie
MarvelHeroes -> for location


R1(movieid1} = project{movieid} (select_{moviename = 'Avengers End Game'} (Movies))

R2 = R1 x HeroInMovie
R3 = select_{movieid1=movieid} (R3) ---> heroes in this movie

R4(heroname1, realname1, power1, location1, multiverseid1) = MarvelHeroes

R5 = R3 x R4
             -- attributes in R5: movieid1, heroname, multiverseid,
	                          heroname1, realname1, power1,
	                          location1, multiverseid1

R6 = select_{heroname=heroname1 and multiverseid=multiverseid1} (R5)
                   -- same hero from R3 and R4

R7 = project_{location1} (R6)