9. Lisää SQL:stä: vaatimukset ja näkymät

1. 9. Lisää SQL:stä: vaatimukset ja näkymät • Tässä luvussa tarkastellaan vaatimusten esittämistä ja näkymien perustamista SQL-kielen avulla • Kirjassa oleva tietokantaohjelmoinnin osuus ( kappaleet 9.3 - 9.6 ) sivuutetaan tällä kurssilla. 9.1 Yleisten rajoitusten esittäminen vaatimuksina • Luvussa 8.2 käsiteltiin attribuuttien määrittelyjoukkoon, puuttuviin arvoihin sekä olio- ja viite-eheyteen liittyviä rajoituksia. • Edellä mainittuun ryhmään kuulumattomia rajoituksia kutsutaan ns. deklaratiivisiksi rajoituksiksi, jotka esitellään SQL-kyselyiden tavoin käyttämällä vaatimusten määrittelykomentoa CREATE ASSERTION. • Esimerkki: esitellään tietokannan tilaan liittyvä rajoitus, jonka mukaan minkä tahansa osaston työntekijän palkka ei saa ylittää saman osaston johtajan palkkaa:

2. 62) CREATE ASSERTION SALARY_CONSTRAINT • CHECK ( NOT EXISTS • ( SELECT * • FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D • WHERE E.SALARY > M.SALARY AND • E.DNO = D.DNUMBER AND • D.MGRSSN = M.SSN ) ); • Asetettaessa uutta arvoa attribuutille SALARY testataan, ylittääkö uusi palkka sen osaston johtajan palkan, jolla tarkasteltava työntekijä on kirjoilla. Tällöin sisin kysely palauttaa ei-tyhjän tulostaulun, jonka jälkeen ulompi NOT EXISTS -funktio saa arvon epätosi. Tällöin uusi tietokannan tila ei ole vaatimuksen mukainen, joten tehty päivitysyritys peruutetaan. • Edellisessä esimerkissä tunnusta SALARY_CONSTRAINT voidaan käyttää viittaamaan esiteltyyn rajoitukseen. • CREATE ASSERTION -lauseen avulla esiteltävillä rajoituksilla voidaan testata attribuuttien arvojen välisiä suhteita tiettyjen tuplien välillä. • Vaatimusten toteutuminen testataan aina tietokannan tilaa muutettaessa, kun taas kappaleiden 8.2.1 ja 8.2.4 CHECK-lauseiden mukaiset tarkastukset tehdään ainoastaan lisättäessä tai päivitettäessä tietueita ( ei tuhottaessa niitä ).

3. 63) CREATE DOMAIN D_NUM AS INTEGER CHECK • ( D_NUM > 0 AND D_NUM < 21 ) • Asetettaessa arvoa tyyppiä D_NUM olevalle kentälle tutkitaan arvon laillisuus eli kuuluminen välille 1..20. • Deklaratiivisia rajoituksia voidaan käyttää myös tietokannan kyseenalaisten ( ei välttämättä laittomien ) tilojen selvittämiseksi kontrolloimaan tietokantaan vietyä dataa. Tällöin rajoituksen rikkoutuminen aiheuttaa jonkin toimenpiteen käynnistymisen. Tällaisista rajoituksista käytetään nimitystä liipaisin ( trigger ). Liipaisintoiminto määritellään SQL:ssä komennolla CREATE TRIGGER. • Voitaisiin esimerkiksi generoida ilmoitus henkilöstöjohtajalle, jos työntekijöiden työtuntimäärät tai matkakulut ovat kasvaneet tietyn rajan ylitse. 9.2 Näkymät ( virtuaaliset taulut ) SQL-kielellä esitettyinä • Seuraavassa tarkastellaan näkymien ( View ) määrittelyä ja käsittelyä SQL-kielen avulla. • Lisäksi mietitään mahdollisuutta päivitysten tekemiseen näkymissä.

4. 9.2.1 Näkymän käsite SQL:ssä • Näkymällä tarkoitetaan yksittäistä taulua, jonka sisältö on johdettu tietokannan muiden taulujen perusteella. • Toisin kuin tähän mennessä käsiteltyjä relaatiotauluja, näkymiä ei tarvitse tallentaa fyysisesti. Siten näkymät mielletään usein ns. virtuaalisiksi tauluiksi. • Näkymien virtuaalinen olemus rajoittaa mahdollisuutta tehdä näkymissä päivitysoperaatioita, mutta se ei rajoita mitenkään mahdollisuutta toteuttaa kyselyjä. • Näkymän perustaminen tulee tarpeelliseksi erityisesti silloin, kun tiettyä kyselyä joudutaan toistamaan usein. • Esimerkki: Oletetaan, että joudutaan usein selvittämään projektien nimet, joihin yrityksen työntekijät tekevät työsuorituksia. Tämä edellyttäisi ilman näkymien olemassaoloa kyselyä, joka liittää taulut TYÖNTEKIJÄ, TYÖTUNNIT ja PROJEKTI toisiinsa. Jottei kyselyä tarvitsisi jatkuvasti toistaa, voidaan määritellä näkymä, joka on em. liitosten tulos.

5. 9.2.2 Näkymän määrittely SQL:ssä • Uusi näkymä voidaan perustaa komennolla CREATE VIEW. • Näkymälle annetaan komennon yhteydessä sitä osuvasti kuvaava nimi.Tämän jälkeen esitellään kysely, jonka tulostauluksi näkymä halutaan määritellä. • 64) Perustetaan näkymä, johon kerätään kunkin työntekijän etu- ja sukunimi, projektit, joissa työntekijä toimii sekä niihin tehdyt työtunnit:V1: CREATE VIEW WORKS_ON1AS SELECT FNAME, LNAME, PNAME, HOURSFROM EMPLOYEE, PROJECT, WORKS_ONWHERE SSN = ESSN AND PNO = PNUMBER; • Näkymän attribuutit tallentuvat edellisessä esimerkissä samalla nimellä kuin niiden vastinattribuutit alkuperäisissä tauluissa. • Haluttaessa voidaan näkymän luonnin yhteydessä sen attribuutit nimetä kuitenkin uudelleen.

6. 65) Perustetaan näkymä, joka sisältää koostetietoa fyysisesti tallennettujen taulujen sisällöstä:V2: CREATE VIEW DEPT_INFO( DEPT_NAME, NO_OF_EMPS, TOTAL_SAL )AS SELECT DNAME, COUNT ( * ), SUM ( SALARY )FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER = DNOGROUP BY DNAME; • Esimerkissä 65 näkymän attribuutit on nimettynä uudelleen sen nimeä seuraavassa attribuuttilistassa. Nimeäminen tapahtuu siinä järjestyksessä kuin SELECT-lauseessa valittavat näkymän attribuutit on esitelty. • Näkymän kuvaamisessa käytetään hyväksi kaavadiagrammeja. • Tarkastellaan kirjan kuvaa 9.1. • Kun näkymä on kertaalleen perustettu, siihen voidaan kohdistaa kyselyoperaatioita murehtimatta lainkaan siitä, mistä eri fyysisistä tietokannan tauluista näkymän sisältämät kentät ovat peräisin!

7. 66) Kohdistetaan edellisessä esimerkissä konstruoituun näkymään WORKS_ON1 kysely, jossa selvitetään projektissa ’ProductX’ työtä tekevät henkilöt:QV1: SELECT FNAME, LNAMEFROM WORKS_ON1WHERE PNAME = ’ProductX’ • Esimerkin 66 mukaisen kyselyn suorittaminen ilman näkymän WORKS_ON1 olemassaoloa vaatisi kahden liitoksen muodostamista. Näkymien käyttäminen mahdollistaa selvästikin tietokannan kyselyjen rakenteen yksinkertaistumisen. • Näkymiä voidaan käyttää myös tietoturvan parantamiseksi ja käyttöoikeuksien rajaamiseksi. • Näkymä on jatkuvasti reaaliaikainen. Joka kerta, kun tehdään näkymän taustalla oleviin fyysisiin tauluihin päivityksiä, myös näkymän sisältö muutetaan vastaamaan tehdyn päivityksen aiheuttamia muutoksia. • Näkymän reaaliaikaisuuden toteuttaminen jää TKHJ:n vastuulle. • Tarpeettomaksi käynyt näkymä voidaan poistaa komennolla DROP VIEW. • 67) Poistetaan näkymä WORKS_ON1:V1A: DROP VIEW WORKS_ON1;

8. 8.5.3 Näkymän toteutus SQL:ssä ja sen päivittäminen • Näkymän tehokas toteuttaminen tietokannan hallintajärjestelmässä kyselyiden nopeutta ajatellen on vaikea tehtävä. • Kaksi tärkeintä kyselyiden toteutusstrategiaa ovat kyselyn modifiointi( query modification ) ja näkymän materialisointi ( view materialization ). • Ensin mainitussa lähestymistavassa muunnetaan näkymään kohdistunut kysely kyselyksi kaikkiin niihin tauluihin, joihin näkymä perustuu. • Kyselyn modifiointi on kuitenkin tehotonta, mikäli näkymä on saatu aikaan monimutkaisen, useita tauluihin tarvitsevan kyselyn turvin. Jos näkymään tehdään toistuvia kyselyitä lyhyin väliajoin, joudutaan aikaa vievä modifioitu kyselyoperaatio suorittamaan usein, vaikkei välttämättä varsinaisessa datassa olisikaan tapahtunut mitään muutoksia. Tällöin näkymäkyselyn vasteajoista saattaa tulla tarpeettoman pitkiä. • Näkymän materialisoinnissa tallennetaan näkymän tiedot fyysisesti, kun ensimmäinen siihen perustuva kysely esitetään. • Koska näkymän taustalla olevien taulujen sisältämät tiedot kuitenkin aika ajoin muuttuvat, pitää olla tehokas menettelytapa automatisoitua fyysisen näkymätiedoston ylläpitoa varten.

9. Fyysisten näkymätiedostojen ajan tasalla pitämistä varten käytetään ns. asteittaisten päivitysten ( incremental updates ) tekniikkaa. Periaatteena on, että varsinaisiin datatiedostoihin tehtyjen päivitysten ( lisäysten, poistojen ja muutosten ) aiheuttamat näkymätiedoston muutokset tallennetaan samalla fyysiseen näkymätiedostoon pääosan näkymätiedostosta säilyessä muuttumattomana. • Oleellisena tekijänä fyysisen näkymätiedoston ylläpidon jatkumisen kannalta on aika, joka on kulunut näkymän viimeisestä käyttöhetkestä. • Jos näkymään kohdistetaan ahkerasti kyselyitä, on myös fyysisen näkymätiedoston päivittäminen tarpeen sitä mukaa kun sen taustalla oleviin datatiedostoihin tehdään muutoksia. • Jos puolestaan näkymän käytön intensiteetti heikkenee, voidaan fyysinen näkymätiedosto tuhota kokonaan, jottei sitä tarvitsisi ylläpitää käytön ollessa vähäistä. Kun näkymää jälleen seuraavan kerran tarvitaan, perustetaan se ’alkutekijöistään’ uudelleen. • Näkymissä tehtävissä olevat mahdolliset päivitysoperaatiot ovat varsinaisiin fyysisiin tauluihin tehtäviin päivityksiin verrattuna huomattavasti rajoittuneempia, ja niiden kanssa kannattaa olla varovainen. • Suurimpina ongelmina päivityksissä ilmenevät päivitysten monimutkaisuus, yksikäsitteisyyden puute sekä mahdollisuus järjettömiin päivityksiin.

10. Tarkastellaan seuraavaksi esimerkkejä päivitykseen liittyvistä pulmista. • 68) Yritetään muuttaa aikaisemmin tarkastellussa näkymässä WORKS_ON1 työntekijää ’John Smith’ kuvaavista tietueista projektin nimeä ’ProductY’:ksi riveillä, joilla projektin nimenä esiintyy ’ProductX’:UV1: UPDATE WORKS_ON1SET PNAME = ’ProductY’ WHERE LNAME = ’Smith’ AND FNAME = ’John’ AND PNAME = ’ProductX’; • Pulma: mitä halutaan saada aikaan kyseisellä päivityksellä? Onko tavoiteltu vaikutus taustalla olevaan tauluun WORKS_ON ehkä seuraavanlainen …68a): UPDATE WORKS_ONSET PNO = ( SELECT PNUMBER FROM PROJECTWHERE PNAME = ’ProductY’)WHERE ESSN IN ( SELECT SSNFROM EMPLOYEEWHERE LNAME = ’Smith’ AND FNAME = ’John’)AND PNO IN ( SELECT PNUMBER FROM PROJECTWHERE PNAME = ’ProductX’ );

11. … vai olisiko tarkoitus ollut sittenkin seuraavassa ehdotettu?68b) UPDATE PROJECTSET PNAME = ’ProductY’WHERE PNAME = ’ProductX’ • Kumpainenkin edellä esitetyistä vaihtoehdoista toteuttaisi käyttäjän esittämän päivityspyynnön, mutta toteutustavat poikkeavat kovasti toisistaan! • Kohdassa a) siirrettäisiin John Smithin työpanos projektista ’Product X’ eli numerosta 1 projektiin 2, mikä olisi mahdollisesti hyvinkin ajateltavissa oleva järkevä vaihtoehto – nyt tosin se olisi kelvoton ratkaisu, sillä John Smith on jo ennestään töissä projektissa 2, mikä rikkoisi taulun WORKS_ON pääavaimen yksikäsitteisyysvaatimusta. • Kohdassa b) saataisiin näkymä WORKS_ON1 myös halutun kaltaiseksi, eli John Smithin kohdalla attribuutin PNAME arvo ’ProductX’ muuttuisi kylläkin arvoksi ’ProductY’, mutta tämä on saatu nyt aikaan muuttamalla taulussa PROJECT projektin 1 nimeksi ’ProductY’. Tämäkään ei onnistuisi, koska projektin nimen pitää olla esimerkkitietokannassamme yksikäsitteinen, mutta koko ajatus näkymän korjaamiseksi b)-kohdassa esitetyllä tavalla tuntuisi intuitiivisesti ajatellen järjettömältä. Jos operaatio onnistuisi, se muuttaisi näkymässä samalla kaikkien projektissa 1 työskentelevien tietoja, vaikka esitetty valintaehto indikoisi selvästi, että muutos koskisi nimenomaan John Smithin tietoja. TKHJ ei kuitenkaan osaa itse ratkaista, kumpi vaihtoehto olisi järkevä suorittaa!

12. Lisäksi käyttäjän itsensä ehdottama päivitys näkymään voi olla mieletön. Ajatellaanpa päivitystä kenttään, joka on muodostettu soveltamalla aggregaattifunktiota. • 69) Yritetään päivittää kokonaispalkkasummatietoa tauluun, joka perustettiin esimerkissä 54:UV2: UPDATE DEPTS_INFOSET TOTAL_SAL = 100000WHERE DNAME = ’Research’ • Esimerkissä 69 pyritään suoraviivaisella tavalla ’tilastotietojen väärentämiseen’: ei ole mieltä yrittää päivittää sellaisen attribuutin arvoja, joka perustuu toisista tauluista kerättyyn koostetietoon! Olisi olemassa rajaton määrä mahdollisia datatiedoston päivityksiä, joiden avulla laskennallisen attribuutin TOTAL_SAL arvoksi saataisiin todellisuudessa juuri 100000! • Yleisesti ottaen, näkymästä käsin suoritettavaa päivitysyritystä voidaan pitää mielekkäänä ainoastaan silloin, kun se on tulkittavissa tarkalleen yhdellä tavalla. • Muutamat tutkijat ovat kuitenkin laajentaneet päivitettävyyden käsitettä myös tilanteisiin, joissa päivitys ei ole yksikäsitteinen, turvautumalla tietyin kriteerein määriteltyyn todennäköisimpään päivitykseen, tai antamalla ratkaisu operaation tulkinnasta epäselvissä tilanteissa käyttäjälle.

13. Pähkinänkuoressa voidaan näkymissä tapahtuvista päivitysoperaatioista todeta kuitenkin seuraavaa: • Näkymässä, joka perustuu vain yhteen tauluun, voidaan tehdä päivityksiä, jos sen attribuutit sisältävät taustalla olevan taulun pääavaimen tai mahdollisesti jonkin muun ehdokasavaimen, koska tällöin jokainen näkymän tupla kuvautuu tarkalleen yhdeksi datatiedoston tuplaksi. • Taulut, jotka sisältävät liitoksia, eivät ole yleensä päivityskelpoisia ( s. o. niissä ei pidä tehdä päivityksiä taustalla olevaan tietokantaan ). • Taulut, jotka on muodostettu käyttämällä ryhmittelyä ja koostefunktioita, eivät myöskään ole kelvollisia päivitysoperaatioiden tekemistä varten.