Announcements: ---------------- - Lecture 2 to be graded very soon! - Lecture 3 exercise to be released a bit after class - announced on Submitty - Homework 1 to be released later today, due next monday Lecture 3: ------------- ------------------------------- 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) ------------------------------- Find name and location of all heroes in Movie titled 'Avengers'. T1(movieid1, name1, release_date1) = select_{title = 'Avengers'} (Movies) T2 = T1 x HeroInMovie T3(heroname1,multiverseid1) = project_{heroname,multiverseid} (select_{movieid = movieid1} (T2)) T4 = T3 x MarvelHeroes T5 = project_{heroname, location} (select_{heroname=heroname1 and multiverseid =multiverseid1} T4) Join operation --------------- R join_{C} S = select_{C} (R x S) if R and S have no attributes in common and C is a join condition (a boolean condition containing expressions of the form A op B where A is an attribute from R and B is an attribute from S) T1(movieid1, name1, release_date1) = select_{title = 'Avengers'} (Movies) T2 = T1 join_{movieid=movieid1} HeroInMovie T3(heroname1, multiverseid1) = project_{heroname,multiverseid} (T2) T4 = T3 join_{heroname=heroname1 and multiverseid=multiverseid1} MarvelHeroes T5 = project_{heroname, location} (T4) ---------- Find the id and title of all movies starring a hero with the power to 'Stop Time'. T1 = project_{heroname,multiverseid) select_{power = 'stop time'}(MarvelHeroes) T2 = project_{heroname,multiverseid) select_{power = 'stop time'}(DCHeroes) T3(heroname1,multiverseid1) = T1 union T2 T4(heroname1,movieid1, heroname, multiverseid1) = T3 join_{heroname=heroname1 and multiverseid=multiverseid1} HeroInMovie T5 = project_{movieid, title} (T4 join_{movieid = movieid1} Movies) --------------------- Natural Join R * S is a join of R and S for the join condition of equality of all attributes that are in common to R and S. Result does not repeat attributes with the same name. T1 = MarvelHeroes * HeroInMovie (join over heroname and multiverseid) T1 has schema: (heroname, realname, power, location, multiverseid, movieid) T2 = T1 * Movies T2 has schema: (heroname, realname, power, location, multiverseid, movieid, name, release_date) -------------- Query: Return the heroname of all heroes that were in a movie in 2020 and in 2019. T1 = project_{heroname} (select_{year=2019} (Movies) * HeroInMovie * (MarvelHeroes union DCHeroes)) T2 = project_{heroname} (select_{year=2020} (Movies) * HeroInMovie * (MarvelHeroes union DCHeroes)) T3 = T1 intersect T2 ---------- Option 2: T1 = project_{heroname} (select_{year=2019} (Movies) * HeroInMovie * (MarvelHeroes union DCHeroes)) T2 = project_{heroname} (select_{year=2020} (Movies) * HeroInMovie * (MarvelHeroes union DCHeroes)) T3 = T1 * T2 --------- T1 = MarvelHeroes * Movies -- same as Cartesian product T2 = MarvelHeroes * DCHeroes -- same as Intersection ------------- Return heroname of all heroes who were in a movie in two consecutive year. T1= Movies * HeroInMovie * (MarvelHeroes union DCHeroes) T2 = project_{heroname, multiverseid, movieyear} (T1) T3(heroname1, multiverseid1, movieyear1) = T2 T4 = T2 x T3 T5 = select_{heroname=heroname1 and multiverseid=multiverseid1 and year = year1+1} (T5) --- same hero, but consecutive year T6 = project_{heroname} (T5) *** Note in this query, it is not actually necessary to join with MarvelHeroes and DCHeroes, unless we need an attribute from those relations like the location. I missed this in class. The query is correct, but just has unnecessary operations. --------------------- Return heroname of all heroes who were NEVER in a movie in two consecutive years. T7 = project_{heroname} (MarvelHeroes union DCHeroes) -- All heroes T8 = T7 - T6 ----------------- Normalization Theory --------------------- Functional Dependencies: Given a relation R is given by A1..An -> B1...Bm Where A1..An, B1...Bm are attributes in R. It means that whenever you have two tuples in R with the same values for attributes A1..An, then these tuples must also have the same values for attributes B1...Bm. Showname, Startyear, Creator, StreamSite, URL, ShowSeasons, StreamSeasons Showname -> StartYear, Creator, ShowSeasons (assuming a single creator) StreamSite -> URL Showname StreamSite -> StreamSeasons ------------- Showname -> Showname Showname -> Showname, StartYear ShowName StreamSite -> URL StreamSite StreamSeasons --------------- Functional Dependency Inference Rules Given a relation R and a set F of functional dependencies, the following inference rules allows you to find new functional dependencies (fds) 1. Trivial (always true for any relation) If A is a subseteq of B, then B->A 2. Transitivity: If X -> Y, Y->Z, then X->Z 3. Decomposition If X -> YZ, then X->Y and X->Z Showname -> StartYear, Creator, ShowSeasons Showname -> StartYear Showname -> Creator Showname -> ShowSeasons 4. Combining rule If X->Y and X->Z then X->YZ 5. Augmentation rule: If X->Y, then XZ -> YZ