1 / 9

Eksempel

SELECT B,C,Y FROM R,S WHERE W=X AND A=3 AND Z = ‘a’. Eksempel. idé 1 – ta kartesisk produkt, velg tupler, projiser attributter  B,C,Y ( s W=X  A=3  Z = ‘a’ (R  S)). Merk : # attributter = # R- attributter + # S- attributter # tupler = # R- tupler * # S- tupler.

Download Presentation

Eksempel

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. SELECT B,C,Y FROM R,S WHERE W=X AND A=3 AND Z=‘a’ Eksempel • idé 1 – ta kartesisk produkt, velg tupler, projiser attributter • B,C,Y (sW=X  A=3 Z=‘a’ (R  S)) Merk:#attributter= #R-attributter+ #S-attributter #tupler= #R-tupler* #S-tupler RelasjonR RelasjonS B,C,Y s...  R S Resultat

  2. Eksempel (forts) SELECT B,C,Y FROM R,S WHERE W=X AND A=3 AND Z=‘a’ idé 2– velgtupler, gjør equijoin, projiserattributter • B,C,Y ((sA=3 (R)) ⋈W=X(sZ=‘a’(S))) B,C,Y RelasjonS RelasjonR ⋈W=X sA=3 sZ=‘a’ R S

  3. Eksempel (forts) SELECT B,C,Y FROM R,S WHERE W=X AND A=3 AND Z=‘a’ idé 3 – brukindekserpåR.A og S.X • brukindeksenpåR.A for å velgetupler med R.A = 3 • brukindeksenpå S.X for å finnetuplersom matcher R.W • plukk ut S-tupler hvor Z = ‘a’ • jointupler fra R og S som matcher • projiser B,C,Y RelasjonS RelasjonR IR.A 3 IS.X 7,9

  4. Enkelgrammatikk: eksempel Finn filmer med skuespillerefødti 1960: SELECT title FROM StarsIn WHERE starName IN ( SELECT name FROM MovieStar WHERE birthDate LIKE ‘%1960’); <Query> <SFW> SELECT<SelList> FROM<FromList> WHERE<Condition> <Tuple> IN<Query> <Attribute> <Relation> title StarsIn <Attribute> (<Query> ) starName <SFW> SELECT<SelList> FROM <FromList> WHERE<Condition> <Attribute> LIKE<Pattern> <Attribute> <Relation> INF3100 - 21.3.2014 - Ellen Munthe-Kaas name MovieStar birthDate ‘%1960’

  5. Konverteringav SFW – eksempel SELECT name FROM MovieStar WHERE birthDate LIKE ‘%1960’ • produktetavrelasjonenei <FromList> • gjørseleksjonbasertpå<Condition> • projiserpåattributtenei <SelList> <Query> <SFW> SELECT <SelList> FROM <FromList> WHERE <Condition> <Attribute> <Relation> <Attribute> LIKE <Pattern> name MovieStar birthDate ‘%1960’ name sbirthDate LIKE ‘%1960’ MovieStar

  6. Konverteringav subspørringer s • For subspørringer bruker vi en foreløpig operator – to-arguments seleksjon s. • Viderebehandlingavhengeravtypen <Condition>. Vi skal se påt IN Ssom et eksempel: • erstatt <Condition> med treet for S. Hvis S kaninneholde duplikater, må vi leggetilen –operator påtoppen. • ertstatt to-arguments seleksjonmed ett-arguments seleksjonsC, hvor Csammenligner hverkomponenti t med dettilsvarendeattributteti S. • la sCha produktetav R og S som argument. R <Condition> t S IN sC   R S

  7. Konverteringav subspørringer - eksempel SELECT title FROM StarsIn WHERE starNameIN(SELECT name FROM MovieStar WHERE birtDate LIKE ‘%1960’) • produktetavrelasjonenei <FromList> • gjørseleksjonbasertpå <Condition>, representertved to-arguments seleksjon • projiserpåattributtenei <SelList> • erstatt foreløpig subspørringen med dens parseringstre <Query> title <SFW> <Condition> SELECT <SelList> FROM <FromList> WHERE s <Tuple> IN <Query> <Attribute> <Relation> StarsIn <Condition> <Attribute> title StarsIn name <Tuple> IN <Tuple> IN <Query> starName sbirthDate LIKE ‘%1960’ INF3100 - 21.3.2014 - Ellen Munthe-Kaas MovieStar

  8. Eksempel (forts) SELECT title FROM StarsIn WHERE starName IN (...) • erstatt<Condition> med treetfor subspørringen • erstatt to-arguments seleksjon med ett-arguments seleksjonsC, hvor C erstarName = name • la sCha produktetavStarsInogMovieStarsom argument title title sstarName = name s sstarName = name StarsIn <Condition>  name StarsIn <Tuple> IN name sbirthDate LIKE ‘%1960’ sbirthDate LIKE ‘%1960’ <Attribute> MovieStar starName MovieStar

  9. Dyttingavseleksjonoppoveritreet • Noen ganger erdetnyttig å dytteseleksjon den andreveien, dvsoppoveritreet, ved å brukelovensa(R ⋈S) = R ⋈sa(S) “bakvendt”. • Eksempel:StarsIn(title, year, starName); Movies(title, year, studio …) • CREATE VIEW Movies96 AS SELECT * FROM Movies WHERE year = 1996; • SELECT starName, studio FROM Movies96 NATURAL JOIN StarsIn; starName, studio starName, studio starName, studio ⋈ ⋈ syear = 1996 syear = 1996 syear = 1996 syear = 1996 ⋈ Movies96 StarsIn INF3100 - 21.3.2014 - Ellen Munthe-Kaas Movies Movies Movies StarsIn StarsIn

More Related