1 / 67

2. Relaatiokielistä

2. Relaatiokielistä. Relaatiokalkyyli Relaatioalgebra Suhde SQL:ään. Taustaa. Relaatiomalliin kuuluu oleellisena osana toiminnallisuus , erityisesti uusien relaatioiden johtaminen olemassa-olevista.

roz
Download Presentation

2. Relaatiokielistä

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. 2. Relaatiokielistä • Relaatiokalkyyli • Relaatioalgebra • Suhde SQL:ään 2-Relaatiokielistä Teuhola 2012

  2. Taustaa • Relaatiomalliin kuuluu oleellisena osana toiminnallisuus, erityisesti uusien relaatioiden johtaminen olemassa-olevista. • Relaatiokalkyylija -algebra ovat formaalisia kyselykieliä, joilla toiminnallisuus voidaan määritellä. • Mihin näitä tarvitaan – eikö SQL riitä? • Relaatiokalkyyli muodostaa SQL:n teoreettisen perustan.Se toimii myös mittapuuna muiden relaatiokielten voimakkuudelle • Relaatioalgebra on pohjana SQL-kyselyiden optimoinnissa.SQL:ää on aikojen kuluessa laajennettu algebran suuntaan. 2-Relaatiokielistä Teuhola 2012

  3. A Joukkojen A, B ja Cleikkaus C B Taustaa (jatk.) • Vrt. matematiikka, esim. joukko-oppi: • Algebrallinen esitys: A  B  C • Looginen esitys: { x | xA  xB  xC } 2-Relaatiokielistä Teuhola 2012

  4. Taustaa (jatk.) • Relaatiokalkyyli on korkean tason deklaratiivinen kieli, joka määrittelee kyselyn tulosrelaation käyttäen matemaattista logiikkaa, mutta ei spesifioi suoritusjärjestystä. • Relaatioalgebra on operationaalinen kieli.Sen kyselylauseke koostuu relaatio-operaa-tioista, joiden laskentajärjestys on (osittain) määrätty. Operaation tulos on aina relaatio, joten lausekkeet voivat olla sisäkkäisiä. 2-Relaatiokielistä Teuhola 2012

  5. Alustava vertailuesimerkki SQL versio 1: SQL versio 2: SELECT R.A SELECT R.AFROM R, S FROM R JOIN S WHERE R.C = S.D ON R.C = S.D AND S.B = 123 WHERE S.B = 123 Relaatiokalkyyli: Relaatioalgebra: { r.A | R(r) AND A (R ⋈C=D (B=123 (S))) (s) (S(s) AND s.B = 123 AND r.C = s.D } 2-Relaatiokielistä Teuhola 2012

  6. 2.1. Relaatiokalkyyli • Relaatiokalkyyli (Relational Calculus) on logiikkakieli, jossa lähderelaatioista johdetaan tulosrelaatio perustuen muuttujia sisältäviin ehtolausekkeisiin. • Muuttujat saavat arvoikseen joko monikoita eli relaation rivejä (jolloin puhutaan monikko-kalkyylistä) tai attribuuttiarvoja (jolloin puhutaan määrittelyjoukkokalkyylistä). Tällä kurssilla rajoitutaan edelliseen. 2-Relaatiokielistä Teuhola 2012

  7. Relaatiokalkyylin historiaa • Predikaattikalkyyliä ehdotettiin kyselykielen pohjaksi jo 1967 (Kuhns); huom. ennen relaatiomallin esittelyä. • Relaatiomallille sovitettua kalkyyliä ehdotti Codd (1972), samoin kuin siihen perustuvaa kysely-kieltä (Alpha). • Myöhemmin Ingres-järjestelmässä käytetty Quel-kyselykieli oli melko lähellä Alphaa. 2-Relaatiokielistä Teuhola 2012

  8. Monikkokalkyyli (tuple relational calculus): kielen komponentit • Monikkomuuttujat (tuple variables): Näitä voidaan pitää eräänlaisina silmukkaindekseinä. Kussakin laskenta-vaiheessa muuttuja edustaa siihen liittyvän relaation jotain monikkoa (riviä). • Ilmaus t.A tarkoittaa A-attribuutin arvoa muuttujan t edustamalla rivillä. • Ehdot ovat jompaakumpaa seuraavista muodoista: • R(t), mikä tarkoittaa, että t:n arvoalue (range) on relaation R rivit. • x<vertailuop> y, missä x ja y ovat muotoa t.A tai vakio. • Hyvin muodostetut kaavat (HMK) koostuvat ehdoista, loogisista operaatioista (AND, OR, NOT) ja kvanttoreista(, ). 2-Relaatiokielistä Teuhola 2012

  9. Hyvin muodostettu kaava (HMK) Säännöt: • Jokainen ehto on HMK. • Jos F on HMK niin samoin ovat (F) ja NOT(F). • Jos F ja G ovat HMK, niin samoin ovat(F AND G) ja (F OR G). • Jos F on HMK ja t sen vapaa muuttuja, niin(t)(F) ja (t)(F) ovat HMK. • Mikään muu ei ole HMK. 2-Relaatiokielistä Teuhola 2012

  10. Hyvin muodostettujen kaavojen tulkinta • R(t) on tosi, jos t viittaa johonkin relaation R riviin. • NOT(F), (F AND G) ja (F OR G) noudattavat normaaleja logiikan tulkintasääntöjä • (t)(F) on tosi, jos muuttujalle t löytyy vähintään yksi mahdollinen arvo (= jonkin relaation rivi), jolle ehto F on tosi. • (t)(F) on tosi, jos ehto F on tosi kaikilla mahdollisilla muuttujan t arvoilla. 2-Relaatiokielistä Teuhola 2012

  11. Monikkokalkyylin kyselyt • Kyselyn yksinkertainen perusmuoto:{ t | HMK(t) } missä HMK(t) sisältää vapaan monikkomuuttujan t. Tulkinta: Vastauksen muodostaa niiden t:n arvojen(= relaatiorivien) joukko, joille HMK(t) on tosi. • Yleisempi muoto:{ t.A, u.B, v.C, … | HMK(t, u, v, …) } Tulkinta: Vasen puoli = kyselyn tulostaulu, joka muodos-tuu ehdon toteuttavien rivikombinaatioiden <t, u, v, …> attribuuteista A, B, C, … 2-Relaatiokielistä Teuhola 2012

  12. Asiakas M Tilaus M Tuote Esimerkki kyselystä monikkokalkyylillä • Relaatiot: • Tuote (Tno, Tnimi, Valmistaja, Hinta) • Asiakas (Ano, Animi, Paikka) • Tilaus (Ano, Tno, Kpl, Pvm) • Alle 1000 € maksavien, Vipu Oy:n valmistamien tuotteiden nimet:{ t.Tnimi | Tuote(t) ANDt.Hinta < 1000 ANDt.Valmistaja = ‘Vipu Oy’ } 2-Relaatiokielistä Teuhola 2012

  13. Sama kysely SQL-kielellä SELECT TnimiFROM TuoteWHERE Hinta < 1000AND Valmistaja = ‘Vipu Oy’ • Ero monikkokalkyyliin on lähinnä syntaktinen. • Huom. Myös SQL:ssä olisi voitu määritellä ‘muuttuja’ t: SELECT t.TnimiFROM Tuote AS tWHERE t.Hinta < 1000AND t.Valmistaja = ‘Vipu Oy’ 2-Relaatiokielistä Teuhola 2012

  14. Olemassolokvanttorin  käyttöesimerkki:kolmen taulun liitos • Etsi Vipu Oy:n valmistamien tuotteiden tilaajat:{ a.Animi | Asiakas(a) AND(t)(Tuote(t) ANDt.Valmistaja=‘Vipu Oy’ AND (x)(Tilaus(x) ANDt.Tno=x.Tno ANDx.Ano=a.Ano ) ) } 2-Relaatiokielistä Teuhola 2012

  15. Sama SQL-kielellä … SELECT a.AnimiFROM Asiakas AS a, Tuote AS t, Tilaus AS xWHERE t.Valmistaja = ‘Vipu Oy’AND a.Ano = x.AnoAND t.Tno = x.Tno • Huom. Tässä voidaan ajatella t:lle ja x:lle implisiittinen -kvanttori. 2-Relaatiokielistä Teuhola 2012

  16. Universaalikvanttorin  käyttöesimerkki Etsi asiakkaat, jotka ovat tilanneet kaikkia tuotteita. { a.Animi | Asiakas(a) AND (t)(NOT (Tuote(t)) OR ( (x)(Tilaus(x) AND a.Ano=x.Ano AND t.Tno=x.Tno ) ) ) } • Huomaa t:n rajoittaminen NOT … OR –ilmauksella Tuote-joukkoon (muuten t liitetään minkä tahansa relaation riveihin). 2-Relaatiokielistä Teuhola 2012

  17. Sama SQL-kielellä … ilman -kvanttoria! SELECT a.AnimiFROM Asiakas AS aWHERENOT EXISTS (SELECT *FROM Tuote AS tWHERE NOT EXISTS (SELECT *FROM Tilaus AS xWHERE x.Ano = a.AnoAND x.Tno = t.Tno)) • Kyseessä niin sanottu kaksoisnegaatiotekniikka 2-Relaatiokielistä Teuhola 2012

  18. Sama SQL-kielellä … ilman -kvanttoria! (jatkoa) • Edelläesitetyn SQL-kyselynvaiheet: • Viimeinen alikysely etsii kaikki attribuutit sellaisilta tilausriveiltä x, joilla uloimmassa kyselyssä paraikaa tarkasteltavana olevan asiakkaan aasiakasnumero a.Ano esiintyy keskimmäisessä kyselyssä sillä hetkellä kiinnitetyn tuotteen t tilaajana. • Keskimmäinen kysely valitsee tarkalleen sellaiset tuotteet t, joita kohti ei löydy paraikaa tarkasteltavan asiakkaan a.Anotekemää tilausta taulusta Tilaus.  mikäli löytyy yksikin sellainen tuote t, jota asiakas a ei tilaa, se tulee nyt valituksi keskimmäisen alikyselyn tulostauluun. • Uloimman kyselyn tulostauluun valitaan puolestaan ne asiakkaat a, joita kohti keskimmäisen kyselyn tulos jäi tyhjäksi. toisin sanoen, listataan yksistään ne asiakkaat a, joita kohti ei löytynyt yhtään tuotetta, joita aei tilaa … … eli siis juuri ne asiakkaat, jotka tilaavat kaikkia tuotteita! 2-Relaatiokielistä Teuhola 2012

  19. Sama SQL-kielellä … toisin SELECT a.AnimiFROM Asiakas AS aWHERE NOT EXISTS ( (SELECT TnoFROM Tuote)EXCEPT (SELECT TnoFROM Tilaus xWHERE x.Ano = a.Ano)) • EXCEPT laskee SELECT-tulosten erotusjoukon, jonka tyhjyyttä NOT EXISTS testaa. • Ratkaisu on lähempänä relaatioalgebraa! 2-Relaatiokielistä Teuhola 2012

  20. Kvanttorilausekkeiden päättelysääntöjä (x) (P(x)) = NOT (x) (NOT P(x)) -- vrt. ed. esim. (x) (P(x)) = NOT (x) (NOT P(x)) (x) (P(x) AND Q(x)) = NOT (x) (NOT P(x) OR NOT Q(x)) (x) (P(x) OR Q(x)) = NOT (x) (NOT P(x) AND NOT Q(x)) (x) (P(x) AND Q(x)) = NOT (x) (NOT P(x) OR NOT Q(x)) (x) (P(x) OR Q(x)) = NOT (x) (NOT P(x) AND NOT Q(x)) (x) (P(x))  (x) (P(x)) NOT (x) (P(x))  NOT (x) (P(x)) 2-Relaatiokielistä Teuhola 2012

  21. Relaatioalgebra • Algebra on annettuun perusjoukkoon liittyvä joukko operaatioita, jotka kohdistuvat perusjoukon alkioihin ja tuottavat tuloksena niinikään perusjoukon alkion. • Relaatioalgebran perusjoukon alkiot ovat relaatioita ja operaatiot niihin kohdistuvia kyselyitä, joiden tulos on relaatio. 2-Relaatiokielistä Teuhola 2012

  22. Relaatioalgebra: operaatiotyypit • Unaariset: • Valinta • Projektio • Binääriset: • Joukko-op. • Liitos • Jako 2-Relaatiokielistä Teuhola 2012

  23. Relaatioalgebra vs. SQL • SQL-kysely (SELECT … FROM … WHERE …) sisältää elementtejä relaatioalgebrasta, mutta ei ole suoranaisesti sen syntaktinen variaatio. • SELECT-lista vastaa projektiota ja WHERE-osa valintaa, tosin mukana voi olla myös liitosehtoja. • SQL:ssä on mahdollista kirjoittaa eksplisiittinen liitos FROM-osassa, mikä on lähellä algebraa. 2-Relaatiokielistä Teuhola 2012

  24. Relaatioalgebra: Valinta (select) Syntaksi: <valintaehto> (<relaation nimi>)Ehdon toteuttavien rivien valinta relaatiosta. Ehto on looginen lauseke, joka kohdistuu relaation sarakkeisiin.Lauseke voi koostua vertailuista (=, <, , >, , ) ja Boolen operaatioista AND, OR, NOT Esim. ”Etsi miestyöntekijät, joiden palkka on < 40000”:Sex=’M’ AND Salary<40000 (Employee) Huom. Kohteena oppikirjan Company-tietokanta 2-Relaatiokielistä Teuhola 2012

  25. Valintaoperaation ominaisuuksia • Sisäkkäiset valinnat ovat kommutatiivisia:<Ehto1> (<Ehto2> (R)) = <Ehto2> (<Ehto1> (R)) • Sisäkkäiset valinnat voidaan aina yhdistää yhdeksi valinnaksi yhdistämällä ehdot AND-operaattorilla:<Ehto1> (<Ehto2> (R)) = <Ehto1> AND <Ehto2> (R) 2-Relaatiokielistä Teuhola 2012

  26. Relaatioalgebra: Projektio (project) Syntaksi:<Attribuuttilista>(Relaatio) Valitsee listan mukaiset sarakkeet tulos-relaatioon. Jos tulosrivien joukossa on duplikaatteja (identtisiä), ylimääräiset esiintymät poistetaan (teoriassa, ei aina käytännössä). Esim. Kaikkien työntekijöiden täydelliset nimet: Fname, Minit, Lname (Employee) 2-Relaatiokielistä Teuhola 2012

  27. Projektio-operaation ominaisuuksia • Yleisessä tapauksessa voi syntyä duplikaatteja eli toistuvia tulosrivejä, jotka pitää poistaa.Duplikaatteja ei synny, jos relaation pääavainon tulossarakkeiden joukossa. • Sisäkkäiset projektiot voidaan yhdistää:<Lista-1>(<Lista-2> (R)) = <Lista-1> (R)Ehtona on, että Lista-1  Lista-2, muuten vasen puoli on virheellinen. 2-Relaatiokielistä Teuhola 2012

  28. Lausekkeiden laskenta vaiheittain • Monimutkaisen relaatioalgebran lausekkeen laskenta voidaan tehdä paloittain, tallettamalla välitulokset apurelaatioihin ja käyttämällä niitä jatkolaskennassa. Tarvitaan asetuslause:<Apurelaatio>  <Rel.algebran lauseke>Tulosrelaation attribuutit voidaan nimetä (muuten nimet periytyvät lausekkeesta). 2-Relaatiokielistä Teuhola 2012

  29. Esimerkki vaiheittaisesta laskennasta • “Etsi osaston 5 naispuolisten työntekijöiden sukunimet ja osoitteet”:NAISET  Sex=’F’ (Employee)OS5_NAISET  Dno=5 (NAISET)TULOS(Snimi, Os)  Lname,Address (OS5_NAISET) 2-Relaatiokielistä Teuhola 2012

  30. Eksplisiittinen uudelleennimeäminen • Joskus on tarpeen nimetä relaatioita ja niiden attribuutteja uudelleen laskennan aikana esim. nimien yksikäsitteisyyden saavuttamiseksi. • Syntaksi: UusiRel(A1, A2, …) (VanhaRel)Voidaan uudelleennimetä relaatio, attribuutit tai molemmat. [  = ‘rho’ = ‘rename’] 2-Relaatiokielistä Teuhola 2012

  31. Relaatioalgebra: Joukko-operaatiot • Relaatiot ovat rivien joukkoja, joten joukko-operaatiot soveltuvat niille luonnostaan:Unioni (), leikkaus (), erotus () ja karteesinen tulo (×) • Unionia, leikkausta ja erotusta koskeva ehto: Operandien tulee olla unioni-yhteensopivia (union compatible): • Sama määrä attribuutteja • Vastinattribuuteilla sama perusjoukko (domain) 2-Relaatiokielistä Teuhola 2012

  32. Joukko-operaatioita:kaavamainen esimerkki R R  S R  S R - S S 2-Relaatiokielistä Teuhola 2012

  33. Esimerkkejä joukko-operaatioista • “Etsi osaston 5 naispuoliset työntekijät”Sex=’F’ (Employee) Dno=5 (Employee) • ”Työntekijät, jotka ovat osastolla 5 tai joiden palkka < 40000 (tai molemmat)”:Dno=5 (Employee) Salary<40000 (Employee) Huom! Leikkaus vastaa AND-ehtoa, unioni OR- ehtoa. 2-Relaatiokielistä Teuhola 2012

  34. Joukko-operaatioiden ominaisuuksia • Unioni ja leikkaus ovat vaihdannaisia (kommutatiivisia) ja liitännäisiä (assosiatiivisia):R  S = S  RR  S = S  R(R  S)  T = R  (S  T)(R  S)  T = R  (S  T) • Leikkaus voidaan toteuttaa erotuksen avulla:R  S = R  (R  S) = S  (S  R) 2-Relaatiokielistä Teuhola 2012

  35. Karteesinen tulo (Cartesian product)eli ristitulo (cross product) • Tulo (merk. R  S) sisältää operandirelaatioiden (R, S) monikoiden kaikki mahdolliset keskinäiset yhdelmät. • Jos R sisältää m monikkoa ja S n monikkoa, niin R  S sisältää m·n monikkoa (siis paljon). • Voidaan soveltaa mille tahansa relaatioparille(ei yhteensopivuusvaatimusta). • Ei yleensä mielekäs sellaisenaan, vaan valintaoperaa-tioon yhdistettynä, ks. liitos. 2-Relaatiokielistä Teuhola 2012

  36. Karteesinen tulo: kaavamainen esimerkki R R × S S 2-Relaatiokielistä Teuhola 2012

  37. Relaatioalgebra: Liitos (join) • Kahden relaation (R, S) yhdistäminen yhdeksi tulosrelaatioksi jonkin ehdon perusteella, merk. R ⋈<ehto>S • Loogisesti kyseessä on karteesinen tulo & valinta, joka sisältää molempien relaatioiden attribuutteja koskevan ehdon. • Yleinen ns. theta-liitos: valintalauseke koostuu muotoa Ai  Bj, olevista ehdoista missä  {=, <, , >, , }, ja attribuutit Ai  R ja Bj  S. 2-Relaatiokielistä Teuhola 2012

  38. Theta-liitos: esimerkki R R ⋈A<DS S 2-Relaatiokielistä Teuhola 2012

  39. Luonnollinen liitos (natural join) • Theta-liitoksessa vertailu on yleensä ‘=‘, jolloin kyseessä ns. EQUIJOIN. • Yhtäsuuruudesta johtuen tuloksessa on kaksi identtistä saraketta. Jos toinen projisoidaan pois, tuloksena on luonnollinen liitos, merk R  S,joka on selvästi yleisin liitostyyppi. • Luonnollinen liitos perustuu yleensä viiteavaimiin (foreign key), jolloin tuloksessa yhdistyvät kukin viittaava ja viitattu monikko. 2-Relaatiokielistä Teuhola 2012

  40. Luonnollinen liitos: Esimerkki R S R  S 2-Relaatiokielistä Teuhola 2012

  41. Liitoksen formulointeja SQL:ssä • Relaatiot: R(A, B, C), S(C, D) • Kysely: A,B (R  D=2(S)) (1) SQL-lauseke, jossa implisiittinen liitos:SELECT A, BFROM R, SWHERE D=2 AND R.C=S.C (2) SQL-lauseke, jossa eksplisiittinen liitos:SELECT A, BFROM (R NATURALJOIN S)WHERE D=2 2-Relaatiokielistä Teuhola 2012

  42. Liitoksen formulointeja SQL:ssä (jatk.) (3) Alikyselyä käyttäen:SELECT A, BFROM RWHERE C IN (SELECT CFROM SWHERE D=2) • Vaihtoehto (1) eli implisiittinen liitos on yleensä kyselyn optimoinnin kannalta suositeltavin. 2-Relaatiokielistä Teuhola 2012

  43. Attribuuttinimet luonnollisessa liitoksessa R  S on yksiselitteinen vain, jos liitosattribuuteilla on relaatioissa R ja S samat nimet (ja muilla eri).Jos näin ei ole, pitää joko muuttaa toisen relaation attribuuttinimet (-operaatio) …<relaatio1>  (Attr1, Attr2, …) <relaatio2> … tai kertoa eksplisiittisesti vastinparit:<relaatio1> (rel1:n liitosattr),(rel2:n liitosattr) <relaatio2> 2-Relaatiokielistä Teuhola 2012

  44. Nimien yhdenmukaistusesimerkki R S R  (C,D) S 2-Relaatiokielistä Teuhola 2012

  45. Muita liitostyyppejä: Puoliliitos (semijoin) • Yhdistelmä: liitos & projektio vain toisen operandirelaation attribuuteille. • Joskus nopeampi kuin erilliset operaatiot. A,B,C (R  S) R S 2-Relaatiokielistä Teuhola 2012

  46. Puoliliitos: SQL-esimerkki • Etsi niiden työntekijöiden nimet, joilla on omaisia. SELECT Fname, Lname FROM Employee, Dependent WHERE Ssn = Essn; Huom! Dependent-relaatiosta ei tule mukaan yhtään tulosattribuuttia. 2-Relaatiokielistä Teuhola 2012

  47. Puolierotus (semidifference) • Määrittely: R <puolierotus> S = R  (R <puoliliitos> S) • Merkitys: Tulokseen mukaan ne R:n monikot, joilla ei ole vastinetta S:ssä. R S R <puolierotus> S 2-Relaatiokielistä Teuhola 2012

  48. Puolierotus: SQL-esimerkki • Etsi niiden työntekijöiden nimet, joilla ei ole omaisia. SELECT Fname, Lname FROM Employee WHERE Ssn NOTIN ( SELECT Essn FROM Dependent); Huom 1. Ehto Ssn <> Essn ei toimi. Huom 2. Dependent-relaatiosta ei tule tässäkään mukaan yhtään tulosattribuuttia. 2-Relaatiokielistä Teuhola 2012

  49. Muita liitostyyppejä: Ulkoliitos (outer join) • Tulokseen toisen/molempien relaatioiden vastineettomat rivit NULLien kera • Versiot ja vastaavat symbolit: • LEFT OUTER JOIN ( ) • RIGHT OUTER JOIN ( ) • FULL OUTER JOIN ( ) 2-Relaatiokielistä Teuhola 2012

  50. Ulkoliitosesimerkkejä R S Liitettävätrelaatiot: Tulokset: S R R S R S 2-Relaatiokielistä Teuhola 2012

More Related