------------------------------
Relational Data Model:



Check all the keys for the following relation for managing your own
life and store your passwords (like keychain on Macs). Given a media
site, you can have multiple accounts, you can use the same password in
two different sites, you can create two accounts on the same date and
you can use the same username in two different sites.

OnlineAccounts(site,username,encrypted_password,creation_date)

site, username

Key minimal: no subset of the set of attributes is also a key


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

Relational Algebra:


Operators:

R union S
R intersect S
R - S    

T(A1, B1, C1) = R

select_{C} (R)
project_{A1,...,An} (R)
R x S

R intersect S = (R union S) - ( (R-S) union (S-R) )



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

Data Model:

MarvelHeroes(hid, hero, realname)
DCHeroes(hid, hero, realname)
Movies(mid, moviename, year)
HeroInMovie(hid, mid)


1. Find id of Marvel heroes who have starred in a movie

T1 = project_{hid} (Marvel_Heroes)    ## all marvel heroes

T2 = project_{hid} (HeroInMovie)      ## heroes who starred in a movie

Result = T1 intersect T2


2. Find id of all heroes who have starred in a movie

T1 = project_{hid} (Marvel_Heroes)    ## all marvel heroes

T2 = project_{hid} (DC_Heroes)  ## all dc heroes

T3 = T1 union T2

T4 = project_{hid} (HeroInMovie)      ## heroes who starred in a movie

Result = T3 intersect T4


### After lecture, someone pointed out (Thanks Venkat!) that I don't need
to union after all. This is true, this one works too:

T4 = project_{hid} (HeroInMovie)      ## heroes who starred in a movie



3. Find all DC comic heroes who NEVER starred in a movie

T1 = project_{hid} (DC_Heroes)    ## all DC heroes

T2 = project_{hid} (HeroInMovie)      ## heroes who starred in a movie

Result = T1 - T2   



For Queries 1, 2, 3: Return the hero and real name of the hero 
-----------------

T1 = project_{hid} (DC_Heroes)    ## all DC heroes

T2 = project_{hid} (HeroInMovie)      ## heroes who starred in a movie

T3(hid1)  = T1 - T2

T4 = T3 x DC_Heroes      ##(hid1, hid, hero, realname)

Result =   project_{hero, realname} ( select_{hid1 = hid} (T4) )


4. Find id of all movies that starred a DC comic hero

T1(hid1, mid1) = HeroInMovie

Result = project_{mid} (select_{hid=hid1 and mid1=mid} (DCHeroes x Movies x T1))

4alt. Find name, year of all movies that starred a DC comic hero

T1(hid1, mid1) = HeroInMovie

Result = project_{moviename, year} (select_{hid=hid1 and mid1=mid} (DCHeroes x Movies x T1))



5. Find the id of all movies made after 2016 who stars Marvel Heroes

T1(mid1)  = project_{mid} (select_{year > 2016} (Movies))  ##movies made after 2016

T2(hid1)  = project_{hid} (Marvel_heroes)    ##all heroes in a Marvel movie

T3 = select_{mid1= mid} (T1 x HeroInMovie)

T4 = select_{hid=hid1} ( T3 x T2 )      ### T4(mid1, hid, mid, hid1)

Results = project_{mid} (T4)


----

6. Find the id of all movies that does "not" star Thor

 T1(hid1, mid) = HeroInMovie

T2 = select_{hero='Thor'} (MarvelHeroes)

T3 = select_{hid1=hid} (T2 x T1)

T4 = project_{mid} (T3)    ###id of all movies with Thor

Result = project_{mid} (Movies} - T4   ## all movies without Thor, includes movies with no superheroes


Result1 = project_{mid} (HeroInMovie} - T4    ## all movies with superheroes but not Thor


---------

For Queries 1, 2, 3: Return the hero and real name of the hero 

For Queries 4, 5, 6: Return the name, year  of the movies

----------

7. Find the name of the hero for each movie made in year 2017

8. Return the id movies that star more than one hero.

## find two tuples for the same movie but different hero

T1(hid1, mid1) = HeroInMovie

T2 = HeroInMovie x T1   ##100 tuples!

T3 = select_{mid=mid1 and hid <> hid1} (T2)

Results = project_{mid} (T3) 


HeroInMovie
hid	Mid
-------------
d1	1
m1	2
d3	3
d4	4
d5	5
m2	6
m3	7
m3	8
m3	9
m5	9