CSCI 4380 Lecture  3 In Class Notes
----------------------------------

Logistics:

1. Set up for meeting:

   Do not post questions that you want me to answer on Webex Teams
   chat during lecture, I will only monitor Webex Meetings chat.

2. How to make use of groups

3. How to make use of Submitty vs. Webex Teams
   -- I need your help.

4. Hw1 will be posted later today, due next monday.
----------------------------------------

Relational Algebra:

- Review of theta join/natural join

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

- Example queries

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

T1(hid1, mid1) = HeroInMovie
Result = project_{hero} (select_{hid=hid1} (MarvelHeroes x T1))

T1(hid1, mid1) = HeroInMovie
Result = project_{hero} (MarvelHeroes join_{hid=hid1}  T1))

T1 = MarvelHeroes * HeroInMovie       ##T1(hid, hero, realname, mid)
Result = project_{hero} (T1)

|><| == join

2. Find name of all heroes who did not star in a movie

T1 = project_{hid} (HeroInMovie)   ##all heroes who starred in a movie
T2 = project_{hid} (MarvelHeroes union DCHeroes)   ## all heroes in the database
Result = project_{hero} ((T2-T1) * (MarvelHeroes union DCHeroes))

T1 = project_{hid} (HeroInMovie)   ##all heroes who starred in a movie
T2 = MarvelHeroes union DCHeroes   ## all heroes in the database
Result = project_{hero} ( project_{hid}(T2) - T1 ) * T2

T1 = project_{hid} (HeroInMovie)   ##all heroes who starred in a movie
T2 = MarvelHeroes union DCHeroes   ## all heroes in the database
Result = project_{hero} (T2 - (T2*T1))

3. Find the name of all movies made after 2016 that stars a Marvel Hero

Result = project_{moviename} (select_{year>2016} ( (MarvelHeroes * HeroInMovie * Movies) )
Result = project_{moviename} (select_{year>2016} ( (MarvelHeroes * Movies * HeroInMovie) )
Result = project_{moviename} ( ( (MarvelHeroes * HeroInMovie * select_{year>2016} (Movies)) )


4. What is the result of this?

T1 = Movies * MarvelHeroes = Marvel x MarvelHeroes
Result = T1 * HeroInMovie    


5. Find the title of a pair of different movies that were made in the same year
and star the same hero

## two heroinmovie tuples with different movie id but same hero

T1(hid1, mid1) = HeroInMovie
T2(hid2, mid2) = HeroInMovie
T3(mid3, moviename3, year3) = Movies

T4 = T1 join_{hid1=hid2 and mid1<>mid2} T2     ## hid1,mid1,hid2,mid2

T5 = T4 join_{mid1=mid} Movies

T6 = (T5 join_{mid2=mid3 and year=year3} T3)

Result = project_{moviename,moviename3} (T6)

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

Functional Dependencies

- Review: fd definition, closure, fd inference, keys.

Student(RIN, Name, Email, Year, Major, Advisor)

RIN -> Name Email
Email -> RIN
Email -> Name
RIN -> Year
RIN Major -> Advisor    ###single academic advisor per major

RIN -> Name Email   #splitting
RIN -> Name 
RIN -> Email

Email -> RIN
Email -> Name
Email -> RIN Name  ## combining rule

RIN -> Email
Email -> Name
RIN -> Name       ## transitivity

RIN -> Email
RIN Name -> Email Name  ## augmentation

Name -> Name          ##trivial
Name Email -> Email   ## trivial


Given a set F, the closure F+ the set of all functional dependencies that are implied by F.

F1 = {A->B, B->C}
F2 = {A->B, B->C, A->C}
F4 = {A->B, B->C, A->C, A->A}
F3 = {A->B, A->C}

F1 equivalent? F2 Yes, F1 same as F4

Is everything in F2 implied by F1?  
Is everything in F1 implied by F2?  

-------------
Key:

A set of attributes X such as X implies all attributes in the relation and X is minimal (no subset of it is a key).

R(A,B,C,D)
A->BC
C->D

If A is key, then A->ABCD must be implied by the above fds

A->BC
A->ABC  ##augment A
A->C    ##decomposition
C->D
A->D    ##transivitiy
A->ABCD  ##combining rule

Closure of a set of functional dependencies, X+

R(A,B,C,D,E,F)
F = {A->BCD, D->E, D->F}

A is a key

A+ = {A,B,C,D,E,F}

A->ABCDEF

Key: A

R(A,B,C,D,E,F)
F = {AB->C, BC->D, BD->A, D->E}

ABF+ = {A,B,C,D,E,F}

BCF+ = {B,C,D,A,E,F}

BDF+ = {B,D,F,A,C,E}

Keys: ABF, BCF, BDF