660 likes | 873 Views
2. Relaatiokielistä. Relaatiokalkyyli Relaatioalgebra Suhde SQL:ään. Taustaa. Relaatiomalliin kuuluu oleellisena osana toiminnallisuus , erityisesti uusien relaatioiden johtaminen olemassa-olevista.
E N D
2. Relaatiokielistä • Relaatiokalkyyli • Relaatioalgebra • Suhde SQL:ään 2-Relaatiokielistä Teuhola 2012
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
A Joukkojen A, B ja Cleikkaus C B Taustaa (jatk.) • Vrt. matematiikka, esim. joukko-oppi: • Algebrallinen esitys: A B C • Looginen esitys: { x | xA xB xC } 2-Relaatiokielistä Teuhola 2012
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Relaatioalgebra: operaatiotyypit • Unaariset: • Valinta • Projektio • Binääriset: • Joukko-op. • Liitos • Jako 2-Relaatiokielistä Teuhola 2012
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
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
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
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
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
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
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
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
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
Joukko-operaatioita:kaavamainen esimerkki R R S R S R - S S 2-Relaatiokielistä Teuhola 2012
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
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
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
Karteesinen tulo: kaavamainen esimerkki R R × S S 2-Relaatiokielistä Teuhola 2012
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
Theta-liitos: esimerkki R R ⋈A<DS S 2-Relaatiokielistä Teuhola 2012
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
Luonnollinen liitos: Esimerkki R S R S 2-Relaatiokielistä Teuhola 2012
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
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
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
Nimien yhdenmukaistusesimerkki R S R (C,E) S 2-Relaatiokielistä Teuhola 2012
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
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
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
Puolierotus: SQL-esimerkki • Etsi niiden työntekijöiden nimet, joilla ei ole omaisia. SELECT Fname, Lname FROM Employee, Dependent 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
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
Ulkoliitosesimerkkejä R S Liitettävätrelaatiot: Tulokset: S R R S R S 2-Relaatiokielistä Teuhola 2012
Ulkoliitoksen formulointi SQL:ssä • Vaihtoehdot: LEFT/RIGHT/FULL JOIN SELECT A, B, C, DFROM (R LEFT JOIN S) R S R S 2-Relaatiokielistä Teuhola 2012