Tietokantojen perusteet 2015 Huomaa, että tämän dokumentin lopussa on kotitehtävä, joka sinun tulee ratkaista itsenäisesti ja esitellä tuo ratkaisusi seuraavissa harjoituksissa opettajalle. SQL-harjoitus Alla olevassa ohjeessa kirjoitettava koodi on korostettu courier-fontilla. Mikäli jokin osa komennosta on korvattava jollakin muulla tekstillä, se osa on merkitty <hakasten väliin>, tällöin itse ympäröiviä hakasia ei tule kirjoittaa komennon yhteydessä. Yhteyden luominen palvelimelle, jossa MySQL sijaitsee (Linux, Mac) • • Avaa järjestelmän Terminal-ikkuna. Avaa SSH-yhteys palvelimelle opetus-tol.oulu.fi kirjoittamalla komentoriville: ssh -l <käyttäjätunnus ilman sulkuja> opetus-tol.oulu.fi − Käyttäjätunnuksena käy pajun tunnus ja salasanana pajun salasana. MySQL:n käyttö Kirjoita seuraava komento, jossa tietokannan käyttäjätunnus on pääsääntöisesti sama kuin pajun tunnuksesi ja salasanan saat harjoitusten vetäjältä. Huomaa, että pajun salasana ei käy, koska palvelinkone (=opetus-tol.oulu.fi) ja mysql-palvelin sillä ovat eri asioita! mysql –u <käyttäjätunnus_tähän_ilman_sulkuja> –p (siis esim.: mysql –u testitunnus –p ) Edellä käynnistetään “MySQL monitor”-ohjelma. Komennolla mysql –-help saa selville valitsimien merkityksen (tehtävä ennen SQL-ikkunan avaamista). Hakutehtävien esimerkkitietokanta on tol (use tol) MySQL monitor -ohjelmasta poistutaan komennoilla quit tai exit. Omat ratkaisut voi ja kannattaa tallettaa halutessaan tekstitiedostoon. Muutama peruskomento SHOW DATABASES; Näyttää kaikki tietokannat - myös sellaiset, joihin ei oikeuksia. USE <DATABASE>; Valitaan käytettävä tietokanta. SHOW TABLES; Näyttää valitun tietokannan taulut. SELECT * FROM <TABLE>; Tulostaa taulun kaikki tiedot. DESCRIBE <TABLE>; Näyttää taulun rakenteen (sarakkeiden nimet ja tietotyypit). Tietokantojen perusteet 2015 Yksinkertaiset select-lauseet 1. Tulosta kaikkien osastojen kaikki tiedot. 2. Hae kaikkien projektien nimet ja budjetit. 3. Tulosta kaikkien niiden projektien numerot, joille joku työntekijä työskentelee (vihje: DISTINCT). Ehdolliset select-lauseet 4. Hae kaikki puolison tai avopuolison roolissa olevat omaiset. 5. Hae niiden Oulussa asuvien työntekijöiden nimet ja henkilötunnukset, joiden palkka on suurempi kuin 30000 ja joilla on ohjaaja (vihje: IS NOT NULL). Tulosta henkilöt nimen mukaan aakkosjärjestyksessä. 6. Tulosta kaikki niiden projektien nimet, joiden osasto alkaa B-kirjaimella. Ehdolliset select-lauseet, joissa käytetään funktioita 7. Hae osastolla A1 työskentelevien työntekijöiden lukumäärä ja heihin kuluvien palkkamenojen kokonaissuuruus (= palkkojen summa). 8. Hae kuinka paljon keskimäärin työntekijä käyttää työtunteja projekteissa E10-E20 (vihje: AVG). 9. Tulosta kaikkien työntekijöiden nimet, joiden katuosoitteessa esiintyy t-kirjain (vihje: kenttä LIKE '%etsittävä%' esim. nimi LIKE '%mu%' toteutuu Sumulalla). Yksinkertaiset (luonnolliset) liitokset 10. Tee kysely, joka tulostaa kaikkien osastojen nimet ja budjetit sekä niiden ko. osastojen projektien nimet ja budjetit. järjestä tulosjoukko osaston nimen mukaan. 12. Tulosta projektiin HUUHKAJA jokaisen työntekijän joka ko. projektille on tehnyt työtä työtunnit. Liitokset itseensä 14. Tulosta niiden työntekijöiden nimet, joiden kotipaikkakunta on eri kuin ohjaajansa. (vihje: FROM tyolainen a, tyolainen b). Alikyselyt 15. Tulosta niiden projektien nimet, joille ei kukaan työskentele yhtäkään tuntia. Tietokantojen perusteet 2015 Taulun luonti Oheinen ER-kaavio voisi olla osa elektronisen kaupankäynnin järjestelmää. Luo ER-kaavion pohjalta seuraavansisältöiset taulurakenteet: Tuote: numero, nimi, hinta, ryhmä, kuvaus Asiakas: tunnus, salasana, nimi, email, VISA Ostoskori: tilauspäivä, tuote (FK), asiakas (FK) Valitse yksittäisille sarakkeille (attribuutit) “järkevät” tietotyypit ja tauluille perusavaimet. Tietotyyppien valinnasta löytyy alustavasti vinkkiä Developer Shed -yhteisön MySQL-oppaasta: http://www.devshed.com/c/a/MySQL/Beginning-MySQL-Tutorial/2/ salasana nimi numero tilauspäivä hinta TUOTE tuoteryhmä N kuvaus Ostoskori tunnus nimi ASIAKAS M email VISA CREATE TABLE-komennon esimerkki: CREATE TABLE taulu ( attribuutti1 int(4) NOT NULL, attribuutti2 varchar(20), attribuutti3 varchar(40), PRIMARY KEY(attribuutti1), FOREIGN KEY(attribuutti2) REFERENCES taulu2(attribuuttiX) ON DELETE CASCADE ); Tarkista aluksi CREATE TABLE komennon syntaksi MySQL-oppaasta ja mieti mitä kukin esimerkin rivi tekee. 16. Luo jokaisesta taulurakenteesta CREATE TABLE-käskyllä tietokantataulu test -kantaan (käytä use test -komentoa ensin) tai oman tunnuksesi nimiseen kantaan. test-kantaa käyttäessäsi nimeä jokainen taulu siten, että nimen loppuun liität oman tunnuksesi, esim tuote_testitunnus. Tällöin työsi ei mene sekaisin toisten harjoitusten kanssa. Lisäys, poisto ja muokkaus 17. Luo jokaiseen tauluun ainakin yksi rivi. Käytä INSERT-komentoa (tarkista syntaksi). 18. Muokkaa jonkin tuotteen hintaa. (Käytä UPDATE-komentoa). 19. Muokkaa ostoskorin sisältöä siten, että asiakkaan tunnus ja tilauspäivä säilyvät, mutta tuotteen numeroksi tulee arvo NULL Tietokantojen perusteet 2015 20. Lisää vielä kaksi riviä jokaiseen tauluun. 21. Poista Asiakas-taulusta keskimmäinen asiakas. (Voit käyttää jotakin asiakkaan tunnistavaa kenttää yksilöimään ”keskimmäisen” asiakkaan.) 22. Poista tuote-taulusta viimeisin tuote. Lopuksi... 23. Poista luomasi taulut tunnuksesi nimisestä kannasta komennolla DROP TABLE <taulun_nimi>; Kotitehtävä: Tavoite: kertaa ohjelmoinnin peruskäsitteet, ja tutustu miten ne toteutetaan Javalla. Tutustu kurssisivuilla olevaan materiaaliin ja tee seuraavat vaatimukset täyttävä Java-ohjelma nimeltä Muistipeli: Ohjelma kysyy käyttäjältä 10 lukua ja 10 merkkijonoa tekstiä pareittain (luku, merkkijono, luku merkkijono,...), luo jokaisesta luku-merkkijono -parista oman olion ja tallettaa ko. olion samaan taulukkoon toisten vastaavien olioiden kanssa. Kun kaikki 10 oliota ovat taulukossa, ohjelma arpoo yhden kokonaisluvun väliltä 1-10, ja kysyy käyttäjältä mikä on kyseistä numeroa vastaava syötetty merkkijono. Käyttäjä syöttää merkkijonon, ja mikäli merkkijono on sama kuin aiemmin olioon syötetty, tulostaa ohjelma onnitteluviestin ja päättyy. Mikäli merkkijonot eivät ole samat, ohjelma tulostaa tästäkin tiedotteen (mutta siis erilaisen kuin onnistuneessa tapauksessa), ja päättyy. Pyri tekemään ohjelma, joka toimii ja täyttää kaikki em. ehdot. Mikäli tämä ei onnistu, tee joka tapauksessa toimiva Java-ohjelma, ja täytä edellä mainituista ehdoista mahdollisimman monta. Tarkoitus on tutustua mahdollisimman moneen Java-kielen elementtiin, joita tarvitaan osion 5 suorittamisessa, joten teet tätä tehtävää omaksi hyödyksesi, etkä niinkään kurssin suorittamiseksi.