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