Turun yliopisto, IT-laitos / Tietokannat II / 4. demonstraatiot 2013-05-16 Tehtävät 1, 4 ja 5 käsittelevät oppikirjan yritystietokantaa (liitteenä). 1. Oletetaan, että on ehditty perustaa seuraavanlainen näkymä: CREATE VIEW OSASTOYHTEENVETO ( OSASTONUM, TT_LKM, PALKKASUMMA, KESKIPALKKA) AS SELECT DNO, COUNT (*), SUM (SALARY), AVG (SALARY) FROM EMPLOYEE GROUP BY DNO; (Toiminto GROUP BY <attr> ryhmittelee taulun rivit kentän <attr> arvojen mukaan.) Selvitä, mitkä seuraavista edelliseen näkymään kohdistuvista kysely- ja muutosoperaatioista ovat kelvollisia. Mikäli esitetty operaatio on kelvollinen, selvitä, mitä se saa aikaan tehtäväpaperin lopussa listatussa tietokannan tilassa suoritettuna (mitä tietoja tulostuu tai mitä muutoksia tapahtuu tietokannan sisällössä). Jos taas operaatio on puolestaan kelvoton, perustele, miksi asia on näin. a) SELECT * FROM OSASTOYHTEENVETO; b) SELECT OSASTONUM, TT_LKM FROM OSASTOYHTEENVETO WHERE PALKKASUMMA > 100000; c) SELECT OSASTONUM, KESKIPALKKA FROM OSASTOYHTEENVETO WHERE TT_LKM > (SELECT TT_LKM FROM OSASTOYHTEENVETO WHERE OSASTONUM = 4 ); d) UPDATE OSASTOYHTEENVETO SET OSASTONUM = 3 WHERE OSASTONUM = 4; e) DELETE FROM OSASTOYHTEENVETO WHERE TT_LKM > 4; 2. Mieti, millaisia pulmatilanteita voi esiintyä haluttaessa lisätä uusia työntekijöitä tai vastaavasti päivittää tai tuhota olemassa olevia rivejä taulusta TYÖNTEKIJÄ ( 3NF) verrattuna tilanteeseen, jossa kyseinen relaatio on normalisoitu 3NF:ään tauluiksi TYÖNTEKIJÄ’ ja OSASTO (kts. luentokalvot 3/3 sivu 159)? 3. Oletetaan, että pääavaimen mukaan järjestetty relaation datatiedosto sisältää 45 000 tietuetta. Kunkin tietueen pituus on kiinteät 100 tavua, ja tietueet ovat yhtenäisiä (yksittäisen tietueen kaikki osat sijaitsevat välttämättä kokonaan samassa levyblokissa). Levyblokin koko on 1 024 tavua. Mikä on tarvittavien levyblokkihakujen enimmäismäärä puolitushakua käyttäen, jos yritetään hakea yksittäistä tietuetta pääavainkentän mukaan suoraan relaation datatiedostosta? Entä miten tilanne muuttuu, jos datatiedostolle luodaan 1-tasoinen primäärihakemisto, joka koostuu pareista (pääavain, blokkiosoitin) olettaen, että hakemistonkin tietueet ovat kiinteitä ja 15 tavun mittaisia, ja nämäkin tietueet pitää mahtua blokkiin kokonaisina? Levyblokkikoko on nytkin 1 024. Paljonko blokkihakuja tarvitaan tuolloin enintään? 4. Määrittele SQL:llä seuraavat rajoitteet (assertions). (a) Työntekijä ei saa olla itsensä esimies (superior). (b) Työntekijällä saa olla enintään yksi puoliso (spouse). 5. Kirjoita JDBC-rajapintaa käyttävä Java-metodi, joka tulostaa näytölle yrityksen tietokannasta työntekijöiden ja heidän lastensa nimet muodossa John Smith: Michael, Alice Franklin Wong: Alice, Theodore Alicia Zelaya: … jne ... Metodi saa parametrina tietokantayhteys-olion (Connection).