Tehtävät
Toisen osan oppimistavoitteet

Osaa luoda luokkakaaviosta relaatiokaavion ja osaa muuntaa luokkakaavion assosiaatiot tarvittaessa tietokannalle sopivampaan muotoon. Tuntee käsitteen oliokaavio. Tuntee käsitteet relaatio, relaatiomalli, pääavain ja viiteavain. Osaa luoda SQL-kielellä useampia tietokantatauluja sisältävän tietokannan, lisätä tietokantaan tietoa ja hakea tietokannasta tietoa. Tekee kyselyitä yhteen tietokantatauluun.

Luokkakaavio ja olioiden väliset yhteydet

Tässä luvussa kerrataan hieman edellisen osan luokkakaavioihin liittyvää materiaalia. Luokkakaavio on kuvaus tarkasteltavan ongelman käsitteistä sekä käsitteiden välisistä yhteyksistä. Luokkakaavio voi sisältää myös käsitteisiin liittyvät attribuutit. Ohjelmistokehityksessä luokkakaavio kuvaa järjestelmän luokkarakennetta, eli mitä luokkia ohjelmassa on, minkälaisia luokat ovat ja miten luokat liittyvät toisiinsa.

Alla on kuvattuna erään tilausjärjestelmän luokkakaavio. Tilausjärjestelmässä on asiakkaita, jotka voivat tehdä tilauksia. Tilauksiin liittyy joukko annoksia, joihin taas liittyy raaka-aineet sekä ravintola. Sama annos voi olla useammassa tilauksessa, ja useampi tilaus voi sisältää saman annoksen -- annos on toisaalta aina tietyn ravintolan tarjoama. Jokaisen annoksen kohdalla listataan annokseen kuuluvat raaka-aineet -- sama raaka-aine voi esiintyä useammassa annoksessa, ja yhdessä annoksessa voi olla useampia raaka-aineita.

[Asiakas|nimi:String;puhelinnumero:String;katuosoite:String;postinumero:Integer;postitoimipaikka:String]
						  [Ravintola|nimi:String;puhelinnumero:String;katuosoite:String;postinumero:Integer;postitoimipaikka:String]
						  [Annos|nimi:String;koko:String;hinta:double]
						  [Tilaus|aika:Date;kuljetustapa:String;vastaanotettu:Boolean;toimitettu:Boolean]
						  [RaakaAine|nimi:String]
						  
						  [Asiakas]1-*[Tilaus]
						  [Tilaus]*-*[Annos]
						  [Annos]*-*[RaakaAine]
						  [Ravintola]1-*[Annos]
Tilausjärjestelmän luokkakaavio

Jokaisen luokkakaaviossa olevan käsitteen kohdalle on merkitty käsitteeseen kuuluvat attribuutit sekä niiden tietotyypit. Esimerkiksi asiakkaalla on nimi, puhelinnumero, katuosoite, postinumero sekä postitoimipaikka. Edellisistä postinumero tallennetaan numerona, muut tallennetaan merkkijonona. Tilaukseen liittyy aika, kuljetustapa, tieto tilauksen vastaanottamisesta sekä tieto tilauksen toimittamisesta. Aika tallennetaan Date-tyyppisenä tietona, kuljetustapa on merkkijono (esim. "kuljetus" tai "nouto"), ja tieto tilauksen vastaanottamisesta sekä toimittamisesta on tallennettu totuusavoisena muuttujana.

Tarkastellaan yllä olevassa luokkakaaviossa olevia yhteystyyppejä eli käsitteiden välisiä suhteita hieman tarkemmin.

Yhden suhde moneen

Kahden käsitteen välillä on yhden suhde moneen (1-N) -yhteys, jos ensimmäisen käsitteen ilmentymään voi liittyä monta toisen käsitteen ilmentymää, mutta yhteen toisen käsitteen ilmentymään voi liittyä vain yksi ensimmäisen käsitteen ilmentymä.

Yllä olevassa esimerkissä tällainen suhde on esimerkiksi asiakkaan ja tilauksen välillä. Käytännössä yksittäinen asiakas -- esimerkiksi Anna Asiakas -- voi tehdä monta tilausta, mutta jokainen tehty tilaus liittyy täsmälleen yhteen asiakkaaseen. Sama tilaus ei voi liittyä samaan aikaan Anna Asiakkaaseen sekä Essi Esimerkkiin.

[Asiakas|nimi:String;puhelinnumero:String;katuosoite:String;postinumero:Integer;postitoimipaikka:String]
						    [Tilaus|aika:Date;kuljetustapa:String;vastaanotettu:Boolean;toimitettu:Boolean]
						    [Asiakas]1-*[Tilaus]
Yhden suhde moneen. Yllä yhteen asiakkaaseen voi liittyä monta tilausta, mutta yksi tilaus liittyy aina täsmälleen yhteen asiakkaaseen. Yhden suhde moneen merkitään luokkakaavioon piirrettyyn viivaan tähdellä ja numerolla 1. Tähti tulee yhteyden siihen päähän, joita voi olla monta, ja ykkönen siihen päähän, joita voi olla vain yksi.

Yhdestä moneen yhteystyyppi edellyttää sen käsitteen ilmentymän olemassaoloa, johon liittyy monta toisen käsitteen ilmentymää. Käytännössä tilauksen luominen edellyttää tilaukseen liittyvän asiakkaan olemassaoloa. Toisin sanoen, tilausta ei voi luoda ilman, että sille olisi asiakas.

Java-lähdekoodina luokka asiakas kuvattaisiin (esimerkiksi) seuraavasti.

public class Asiakas {
    String nimi;
    String puhelinnumero;
    String katuosoite;
    Integer postinumero;
    String postitoimipaikka;

    List<Tilaus> tilaukset;

    public Asiakas(String nimi, String puh, String katu, Integer postiNro, String paikka) {
        this.nimi = nimi;
        this.puhelinnumero = puh;
        this.katuosoite = katu;
        this.postinumero = postiNro;
        this.postitoimipaikka = paikka;
        this.tilaukset = new ArrayList<>();
    }
}    

Luokalla olisi lisäksi tarvittavat importit sekä metodeja mm. tilausten lisäämiseen.

Vastaavasti luokka tilaus kuvattaisiin lähdekoodina (esimerkiksi) seuraavasti. Alla tilaukseen on lisätty alkuperäisessä laajemmassa luokkakaaviossa näkyneet tuotteet.

public class Tilaus {
    Asiakas asiakas;
    Date aika;
    String kuljetustapa;
    Boolean vastaanotettu;
    Boolean toimitettu;
  
    List<Annos> annokset;
  
    public Tilaus(Asiakas asiakas, String kuljetustapa) {
        this.asiakas = asiakas;
        this.kuljetustapa = kuljetustapa;
        this.aika = new Date();
        this.vastaanotettu = false;
        this.toimitettu = false;
    }
}

Luokalla olisi -- taas -- lisäksi tarvittavat importit sekä metodeja tilauksen tilan muokkaamiseen. Jonkun vastuulla olisi esimerkiksi merkitä tilaus vastaanotetuksi kun tilauksen tekeminen aloitetaan, jonka lisäksi tilauksen toimituksen (tai noudon) yhteydessä tilaus tulisi merkitä toimitetuksi.

Yleisemmin ottaen voi todeta, että jos käsitteen ilmentymään liittyy monta toista käsitettä, merkitään se Java-luokassa listalla tai muulla tietorakenteella. Jos käsitteen ilmentymään liittyy taas vain yksi toinen käsite, merkitään se Java-luokassa yksittäisenä muuttujana joka viittaa toiseen luokkaan (käsitteeseen).

Monen suhde moneen

Kahden käsitteen välillä on monen suhde moneen (N-N) -yhteys, jos ensimmäisen käsitteen ilmentymään voi liittyä monta toisen käsitteen ilmentymää, ja toisen käsitteen ilmentymään voi liittyä monta ensimmäisen käsitteen ilmentymää.

Yllä olevassa esimerkissä tällainen suhde on esimerkiksi annoksen ja raaka-aineen välillä. Käytännössä yksittäinen annos -- esimerkiksi Poronkäristys -- voi sisältää montaa eri raaka-ainetta kuten perunaa, puolukkaa ja poroa. Toisaalta, yksittäinen raaka-aine kuten peruna voi sisältyä moneen eri annokseen.

[Annos|nimi:String;koko:String;hinta:double]
						      [RaakaAine|nimi:String]
						      [Annos]*-*[RaakaAine]
Monen suhde moneen. Yllä annokseen voi liittyä montaa eri raaka-ainetta, ja yksi raaka-aine voi esiintyä useammassa eri annoksessa. Monen suhde moneen merkitään luokkakaavioon piirrettyyn viivaan kahdella tähdellä, missä viivan kummassakin päässä on tähti.
Yhden suhde yhteen

Yhteystyyppien yhden suhde moneen ja monen suhde moneen lisäksi on myös yhteystyyppi yhden suhde yhteen. Kahden käsitteen välillä on yhden suhde yhteen (1-1) -yhteys, jos ensimmäisen käsitteen ilmentymään voi liittyä vain yksi toisen käsitteen ilmentymä, ja toisen käsitteen ilmentymään voi liittyä vain yksi ensimmäisen käsitteen ilmentymä.

Edellisessä tilausjärjestelmän luokkakaaviossa tätä yhteystyyppiä ei näkynyt. Eräs esimerkki tällaisesta yhteystyypistä voisi olla kuljettajien ja kuljetusvälineiden välinen -- ehkäpä hieman teennäinen -- yhteys. Yhdellä kuljettajalla voi olla käytössään vain yksi kuljetusväline, ja yksi kuljetusväline voi olla vain yhden kuljettajan käytössä. Vastaavan esimerkin voisi rakentaa myös lainaesineiden kautta -- esimerkiksi yhdellä opiskelijalla voi olla vain yksi laite lainassa ylläpidolta, ja ylläpito voi lainata tietyn laitteen vain yhdelle opiskelijalle.

Yhteysrajoitteet ja oliot

Tarkastellaan edellä kuvattuja yhteystyyppejä yhdestä moneen ja monesta moneen olioiden näkökulmasta. oliokaaviona. Oliokaavioita käytetään ohjelmien tilan muutosten tarkasteluun ohjelman suorituksen aikana. Keskiössä ovat ohjelman käsittelemät oliot, olioiden muuttujien arvot sekä viitteet olioiden välillä. Valmiit kokoelmaluokat (esim. ArrayList) sivuutetaan siten, että viitteet piirretään olioiden välille.

Oletetaan, että käytössämme on edellä kuvatut luokat Annos ja RaakaAine.

RaakaAine kk = new RaakaAine("Kesäkurpitsa");
RaakaAine jl = new RaakaAine("Jauheliha");
RaakaAine juusto = new RaakaAine("Emmentaljuusto");

Annos vuoka = new Annos("Jauheliha-kesäkurpitsavuoka", "iso", 3);
Annos paistos = new Annos("Jauheliha-kesäkurpitsapaistos", "sopiva", 4);

vuoka.lisaaRaakaAineet(kk, jl, juusto);
paistos.lisaaRaakaAine(kk, jl, juusto);

Ohjelman tila suorituksen lopussa piirretään oliokaaviona seuraavasti. Jokainen olio merkitään omana laatikkonaan, mikä sisältää sekä olion nimen ja tyypin että oliomuuttujat. Oliomuuttujien arvot merkitään myös oliokaavioon. Alla olevasta oliokaaviosta näkee, että annoksen ja raaka-aineen välillä on monesta moneen yhteys -- annoksella voi olla monta raaka-ainetta ja raaka-aine voi liittyä useampaan annokseen.

[kk:RaakaAine|nimi=Kesäkurpitsa], [jl:RaakaAine|nimi=Jauheliha], [juusto:RaakaAine|nimi=Emmentaljuusto], [vuoka:Annos|nimi=Jauheliha-kesäkurpitsavuoka;koko=iso;hinta=3], [paistos:Annos|nimi=Jauheliha-kesäkurpitsapaistos;koko=sopiva;hinta=4], [vuoka:annos]-[kk:RaakaAine], [vuoka:annos]-[jl:RaakaAine], [vuoka:annos]-[juusto:RaakaAine], [paistos:annos]-[kk:RaakaAine], [paistos:annos]-[jl:RaakaAine], [paistos:annos]-[juusto:RaakaAine]

Edellä kuvatun raaka-aineita ja annoksia käsittelevän ohjelman tila suorituksen lopussa.

Tarkastellaan seuraavaksi Asiakkaan ja Tilauksen välistä yhteyttä oliokaaviona. Oletetaan, että oliokaaviona piirretään alla kuvatun ohjelman lopputilanne.

Asiakas kusti = new Asiakas("Kusti", "...", "...", 33100, "Tampere");
Asiakas pukki = new Asiakas("JP", "...", "...", 99999, "Korvatunturi");

Tilaus t1 = new Tilaus(kusti, "polkupyörä");
Tilaus t2 = new Tilaus(pukki, "helikopteri");
Tilaus t3 = new Tilaus(pukki, "helikopteri");

t1.vastaanotettu = true;
t3.vastaanotettu = true;
[kusti:Asiakas|nimi=Kusti;puhelinnumero=...;katuosoite=...;postinumero=33100;postitoimipaikka=Tampere]
					[pukki:Asiakas|nimi=JP;puhelinnumero=...;katuosoite=...;postinumero=99999;postitoimipaikka=Korvatunturi]
					[t1:Tilaus|aika=1516562032;kuljetustapa=polkupyörä;vastaanotettu=true;toimitettu=false]
					[t2:Tilaus|aika=1516563032;kuljetustapa=helikopteri;vastaanotettu=false;toimitettu=false]
					[t3:Tilaus|aika=1516564032;kuljetustapa=helikopteri;vastaanotettu=true;toimitettu=false]
					[kusti:Asiakas]-[t1:Tilaus]
					[pukki:Asiakas]-[t2:Tilaus]
					[pukki:Asiakas]-[t3:Tilaus]

Edellä kuvatun asiakkaiden ja tilausten yhteyksiä käsittelevän ohjelman tila suorituksen lopussa.

Yllä olevaa oliokaaviota tarkasteltaessa huomaamme, ettei jokaiseen tilaukseen liittyy vain yksi asiakas, mutta asiakkaalla voi olla useampia tilauksia.

Miten oliokaaviot liittyvät tietokantoihin?

Oliokaavioiden avulla näytetään ohjelman suorituksen aikainen olioiden tila, joka kertoo olioiden muuttujien arvoista. Tietokannanhallintajärjestelmien tehtävänä on tallentaa ja ylläpitää tietoa -- esimerkiksi olioiden tilaa. Ymmärtämällä miten olioiden tila voidaan esittää, löydämme ehkäpä selkeän kytköksen tietokantojen sisältämän tiedon esittämiselle.

Luokkakaaviosta relaatiokaavioksi (eli tietokantakaavioksi)

Tietokantakaavio (myös relaatiokaavio) on kuvaus tietokantatauluista sekä tietokantatauluihin liittyvistä tiedoista. Relaatiokaavion perusteella luodaan tietokantataulujen luomiseen tarvittavat kyselyt. Tutustutaan ensin termiin relaatio ja harjoitellaan sen jälkeen luokkakaavion muuntamista relaatiokaavioksi.

Relaatiomalli

Relaatiomallin perusajatus on tallennettavan tiedon jakaminen käsitteisiin sekä käsitteiden välisiin yhteyksiin. Jokaista käsitettä vastaa relaatiotietokannassa taulu, ja jokaiselle käsitteen ominaisuudelle eli attribuutille on taulussa oma sarake. Jokainen taulun rivi vastaa yhtä käsitteen ilmentymää, ja tietokantatauluja määriteltäessä taululle määritellään tyypillisesti myös avain, jonka perusteella kukin rivi -- eli käsitteen ilmentymä -- voidaan yksilöidä.

Relaatiomallille on myös hieman formaalimpi määritelmä, johon tutustutaan seuraavaksi. Noudatamme tässä Edgar Coddin vuonna 1970 julkaistun artikkelin "A Relational Model of Data for Large Shared Data Banks" esitysasua.

Relaatio

Olkoon S1, S2, ..., Sn arvojoukkoja, joiden sisältämät arvot eivät ole välttämättä täysin erillisiä. Relaatio R on joukko n alkion kokoisia monikkoja. Jokaisen relaatiossa R olevan monikon ensimmäisen arvon tulee kuulua joukkoon S1, toisen arvon kuulua joukkoon S2 jne.

Relaatio R on siis osajoukko joukkojen S1, S2, ..., Sn välisestä karteesisesta tulosta S1 ⨯ S2 ⨯ ... ⨯ Sn.

Relaatio esitetään tyypillisesti taulukkona, jolla on seuraavat ominaisuudet:

  • Jokainen taulukon rivi kuvaa yhtä relaation R monikkoa.
  • Taulukon rivien järjestyksellä ei ole väliä.
  • Jokainen taulukon rivi on erilainen.
  • Sarakkeiden järjestyksellä kuvataan relaation arvojoukkoja; ensimmäisen sarakkeen arvot tulevat arvojoukosta S1, toisen sarakkeen arvojoukosta S2 jne..
  • Jokaiselle sarakkeelle annetaan nimi, joka kuvaa kunkin arvojoukon mahdollisia arvoja.

Pääavain, viittaaminen ja viiteavain

Jokaisella relaatiolla on tyypillisesti yksi arvojoukko tai arvojoukkojen yhdistelmä, joiden arvojen perusteella voidaan yksilöidä relaation monikko (eli taulukon rivi). Tällaista arvojoukkoa tai arvojoukkojen yhdistelmää kutsutaan pääavaimeksi. Oleellinen ominaisuus relaatioissa on myös saman tai toisen relaation arvoihin viittaaminen. Tämä tapahtuu viiteavaimen avulla. Relaatiossa R oleva arvojoukko tai arvojoukkojen yhdistelmä, joka ei ole relaation R pääavain, mutta sisältää jonkun relaation pääavaimia, on viiteavain.

Joukko-operaatiot ja kyselyt

Relaatiot ovat joukkoja, joten niitä voi käsitellä joukko-opin operaatioiden avulla. Tällä kurssilla näihin ei syvennytä tarkemmin, mutta teemasta kiinnostuneiden kannattanee lukea klassikkoteos "A Relational Model of Data for Large Shared Data Banks".

Muunnos luokkakaaviosta relaatiokaavioksi

Muunnos luokkakaaviosta relaatiokaavioksi tapahtuu seuraavia askeleita noudattaen:

  • Askel 1: määrittele jokaiselle luokkakaavion käsitteelle käsitteen ilmentymän yksilöivä pääavain.
  • Askel 2: monen suhden moneen -yhteyksien käsittely lisäämällä yhteyteen liitostaulu, mikä muuntaa yhteydet yhden suhde moneen -muotoisiksi
  • Askel 3: yhden suhde moneen -yhteyksien käsittely lisäämällä "moneen"-yhteyden päädyssä olevaan tauluun viiteavain

Tarkastellaan edellä kuvattuja askeleita ja muunnetaan aiemmin käsitelty tilausjärjestelmän luokkakaavio tietokantakaavioksi.

[Asiakas|nimi:String;puhelinnumero:String;katuosoite:String;postinumero:Integer;postitoimipaikka:String]
						  [Ravintola|nimi:String;puhelinnumero:String;katuosoite:String;postinumero:Integer;postitoimipaikka:String]
						  [Annos|nimi:String;koko:String;hinta:double]
						  [Tilaus|aika:Date;kuljetustapa:String;vastaanotettu:Boolean;toimitettu:Boolean]
						  [RaakaAine|nimi:String]
						  
						  [Asiakas]1-*[Tilaus]
						  [Tilaus]*-*[Annos]
						  [Annos]*-*[RaakaAine]
						  [Ravintola]1-*[Annos]
Tietokantakaavioksi muunnettava tilausjärjestelmän luokkakaavio.

Käsitteen yksilöivän pääavaimen määrittely

Ensimmäisessä askeleessa määrittelemme jokaiselle käsitteelle pääavaimen (primary key). Pääavain yksilöi käsitteen ilmentymän. Pääavaimella on muutamia ominaisuuksia: sen täytyy olla uniikki (sama arvo ei saa esiintyä samassa taulussa useampaan kertaan) ja se ei saa olla tyhjä. Pääavaimeksi valitaan (nykyään) tyypillisesti juokseva numero. Käytössämme olevissa relaatiokaaviossa pääavain merkitään merkinnällä (pk).

Alla tilausjärjestelmän luokkakaavioon on merkitty pääavaimet.

[Asiakas|(pk) id:Integer; nimi:String;puhelinnumero:String;katuosoite:String;postinumero:Integer;postitoimipaikka:String]
							       [Ravintola|(pk) id:Integer;nimi:String;puhelinnumero:String;katuosoite:String;postinumero:Integer;postitoimipaikka:String]
							       [Annos|(pk) id:Integer;nimi:String;koko:String;hinta:double]
							       [Tilaus|(pk) id:Integer;aika:Date;kuljetustapa:String;vastaanotettu:Boolean;toimitettu:Boolean]
							       [RaakaAine|(pk) id:Integer;nimi:String]
							       
							       [Asiakas]1-*[Tilaus]
							       [Tilaus]*-*[Annos]
							       [Annos]*-*[RaakaAine]
							       [Ravintola]1-*[Annos]
Tilausjärjestelmän luokkakaavion muunnos relaatiokaavioksi, askel 1. Ensimmäisessä askeleessa jokaiseen käsitteeseen määritellään pääavain, jonka perusteella kukin käsitteen ilmentymä voidaan yksilöidä. Tässä käytetään numeerista tunnusta, eli esimerkiksi uutta asiakasta luodessa asiakkaan tunnus on numero, joka ei ole vielä yhdenkään muun asiakkaan käytössä (numerot merkitään tyypillisesti juoksevasti 1...n).

Monen suhde moneen -yhteyksien käsittely

Askeleessa kaksi käsitellään monen suhde moneen -yhteydet.

Monen suhde moneen -yhteydet muunnetaan yhden suhde moneen -tyyppisiksi lisäämällä monen suhde moneen -yhteyksille niin kutsuttu liitostaulu. Liitostaulun avulla saadaan selville mikä käsitteen ilmentymä liittyy mihinkin toisen käsitteen ilmentymään.

Käytännössä muunnos tapahtuu seuraavasti. Alla olevassa kuvassa on monen suhde moneen -yhteys käsitteiden Annos ja Raaka-aine välillä. Käsitteille annos ja raaka-aine on jo määritelty pääavaimet.

[Annos|(pk) id:Integer;nimi:String;koko:String;hinta:double]
								   [RaakaAine|(pk) id:Integer;nimi:String]
								   [Annos]*-*[RaakaAine]
Monen suhde moneen -yhteys annoksen ja raaka-aineen välillä. Käsitteille annos ja raaka-aine on määritelty pääavaimet askeleessa yksi.

Luodaan käsitteiden välille liitostaulu AnnosRaakaAine. Liitostaulusta AnnosRaakaAine on yhden suhde moneen -yhteys käsitteisiin Annos ja Raaka-aine. Käytännössä yksi AnnosRaakaAine-käsitteen ilmentymä yksilöi aina yksittäisen annos - raaka-aine -parin.

[Annos|(pk) id:Integer;nimi:String;koko:String;hinta:double]
									       [RaakaAine|(pk) id:Integer;nimi:String]
									       [AnnosRaakaAine]
									       [Annos]1-*[AnnosRaakaAine]
									       [AnnosRaakaAine]*-1[RaakaAine]
Monen suhde moneen -yhteys annoksen ja raaka-aineen välillä muunnettu liitostaulun avulla kahdeksi yhden suhde moneen -yhteydeksi. Taulu (tai käsite) AnnosRaakaAine pitää kirjaa kuhunkin annokseen liittyvistä raaka-aineista.

Jokainen monesta moneen suhde käsitellään yksitellen. Kun kaikki monen suhde moneen -yhteydet on käsitelty, kaavio on seuraavanlainen.

[Asiakas|(pk) id:Integer;nimi:String;puhelinnumero:String;katuosoite:String;postinumero:Integer;postitoimipaikka:String]
											[Ravintola|(pk) id:Integer;nimi:String;puhelinnumero:String;katuosoite:String;postinumero:Integer;postitoimipaikka:String]
											[Annos|(pk) id:Integer;nimi:String;koko:String;hinta:double]
											[Tilaus|(pk) id:Integer;aika:Date;kuljetustapa:String;vastaanotettu:Boolean;toimitettu:Boolean]
											[RaakaAine|(pk) id:Integer;nimi:String]
											[AnnosRaakaAine]
											[TilausAnnos]
											
											[Asiakas]1-*[Tilaus]
											[Tilaus]1-*[TilausAnnos]
											[TilausAnnos]*-1[Annos]
											[Annos]1-*[AnnosRaakaAine]
											[AnnosRaakaAine]*-1[RaakaAine]
											[Ravintola]1-*[Annos]
Tilausjärjestelmän muunnos relaatiokaavioksi, askel 2. Toisessa askeleessa jokainen monesta moneen -yhteys on pilkottu osiin lisäämällä yhteyteen liitostaulu. Liitostaulut yhdistävät monesta moneen -yhteyden käsitteet yhdestä moneen -yhteydellä.

Yhden suhde moneen -yhteyksien käsittely

Yhden suhde moneen -yhteydet käsitellään lisäämällä yhteyden tähdellä merkittyyn käsitteeseen viiteavain, joka viittaa käsitteeseen, joka on merkitty yhteydessä ykkösellä. Viiteavain viittaa toisen käsitteen pääavaimeen. Viiteavain siis yksilöi (toisen) käsitteen ilmentymän, johon nykyinen käsite viittaa. Käytössämme olevissa relaatiokaaviossa viiteavain merkitään merkinnällä (fk), jonka lisäksi viiteavaimen nimi ja tyyppi kertoo minkä käsitteen pääavaimeen viiteavain viittaa.

[Asiakas|(pk) id:Integer;nimi:String;puhelinnumero:String;katuosoite:String;postinumero:Integer;postitoimipaikka:String]
								 [Tilaus|(pk) id:Integer;aika:Date;kuljetustapa:String;vastaanotettu:Boolean;toimitettu:Boolean]
								 [Asiakas]1-*[Tilaus]
Yhden suhde moneen. Yllä yhteen asiakkaaseen voi liittyä monta tilausta, mutta yksi tilaus liittyy aina täsmälleen yhteen asiakkaaseen.

Yllä oleva yhden suhde moneen yhteys käsitellään lisäämällä Tilaus-käsitteeseen viiteavain, joka viittaa asiakkaaseen. Tämän viiteavaimen perusteella jokaisesta tilauksesta saa selville tilaukseen liittyvän asiakkaan.

[Asiakas|(pk) id:Integer;nimi:String;puhelinnumero:String;katuosoite:String;postinumero:Integer;postitoimipaikka:String]
										   [Tilaus|(pk) id:Integer;(fk) asiakas_id:Asiakas;aika:Date;kuljetustapa:String;vastaanotettu:Boolean;toimitettu:Boolean]
										   [Asiakas]1-*[Tilaus]
Yhden suhde moneen. Yllä yhteen asiakkaaseen voi liittyä monta tilausta, mutta yksi tilaus liittyy aina täsmälleen yhteen asiakkaaseen.

Jokainen yhdestä moneen suhde käsitellään yksitellen. Kun kaikki yhdestä moneen -yhteydet on käsitelty, kaavio on seuraavanlainen.

[Asiakas|(pk) id:Integer;nimi:String;puhelinnumero:String;katuosoite:String;postinumero:Integer;postitoimipaikka:String]
										 [Ravintola|(pk) id:Integer;nimi:String;puhelinnumero:String;katuosoite:String;postinumero:Integer;postitoimipaikka:String]
										 [Annos|(pk) id:Integer;(fk) ravintola_id:Ravintola;nimi:String;koko:String;hinta:double]
										 [Tilaus|(pk) id:Integer;(fk) asiakas_id:Asiakas;aika:Date;kuljetustapa:String;vastaanotettu:Boolean;toimitettu:Boolean]
										 [RaakaAine|(pk) id:Integer;nimi:String]
										 [AnnosRaakaAine|(fk) annos_id:Annos;(fk) raaka_aine_id:RaakaAine]
										 [TilausAnnos|(fk) tilaus_id:Tilaus;(fk) annos_id:Annos]
										 
										 [Asiakas]1-*[Tilaus]
										 [Tilaus]1-*[TilausAnnos]
										 [TilausAnnos]*-1[Annos]
										 [Annos]1-*[AnnosRaakaAine]
										 [AnnosRaakaAine]*-1[RaakaAine]
										 [Ravintola]1-*[Annos]
Tilausjärjestelmän muunnos relaatiokaavioksi, askel 3. Kolmannessa askeleessa jokaiseen yhdestä moneen -yhteyteen on lisätty viiteavain. Viiteavain lisätään päätyyn, jossa on yhteyden tähti.

Edellä kuvatun tietokantakaavion voi kuvata myös tekstimuodossa seuraavasti. Pääavaimet merkitään etuliitteellä (pk), jonka lisäksi niille kerrotaan tyyppi. Viiteavaimet merkitään etuliitteellä (fk), jonka lisäksi niihin merkitään viitatun tietokantakaavion nimi, esim (fk) tilaus_id -> Tilaus.

  • Asiakas((pk) id:Integer, nimi:String, puhelinnumero:String, katuosoite:String, postinumero:Integer, postitoimipaikka:String)
  • Ravintola((pk) id:Integer, nimi:String, puhelinnumero:String, katuosoite:String, postinumero:Integer, postitoimipaikka:String)
  • Annos((pk) id:Integer, (fk) ravintola_id -> Ravintola, nimi:String, koko:String, hinta:double)
  • Tilaus((pk) id:Integer, (fk) asiakas_id -> Asiakas, aika:Date, kuljetustapa:String, vastaanotettu:Boolean, toimitettu:Boolean)
  • RaakaAine((pk) id:Integer, nimi:String)
  • AnnosRaakaAine((fk) annos_id - > Annos, (fk) raaka_aine_id -> RaakaAine)
  • TilausAnnos((fk) tilaus_id - > Tilaus, (fk) annos_id -> Annos)
Menikö oikein?

Luokkakaavion muunnos relaatiokaavioksi tapahtuu kolmen askeleen kautta. Voit tarkastella relaatiokaavion toimivuutta poistamalla kaikki yhteyksiä kuvaavat viivat.

Piirrä tämän jälkeen viivat takaisin viiteavainten perusteella. Viiteavaimen sisältävän käsitteen päätyyn päätyyn tulee aina tähti ja viivan toiseen päätyyn numero 1. Jos viiteavaimia seuraamalla tapahtunut piirtäminen tuottaa saman kaavion kuin mikä askelia seuraamalla saatu lopputulos oli, on kaavio melko suurella todennäköisyydellä kunnossa.

Perintä ja luokkakaaviosta tietokantakaavioksi

Perintä käsitellään tietokantakaaviossa yhden suhde yhteen -tyyppisenä yhteytenä. Kun perintänä merkittyä yhteyttä muunnetaan tietokantakaavioksi, lisätään perivään käsitteeseen viiteavain, joka viittaa perittävään käsitteeseen. Edellinen opiskelija-henkilö -esimerkki muuntuu seuraavanlaiseksi tietokantakaavioksi.

[Henkilo|(pk) id: Integer; nimi:String;syntymäaika:Date;email:String], [Opiskelija|(pk) id: Integer; (fk) henkilo_id: Henkilo; opiskelijanumero:String], [Henkilo]-[Opiskelija]

 

Structured Query Language (SQL)

Materiaalin ensimmäisen osan esimerkeissä tarkasteltiin tietokoneen kiintolevyllä sijaitsevan tiedon käsittelyä ohjelmallisesti -- käytännössä esimerkki käsitteli fyysistä näkymää tiedon tallennukseen. Esimerkeissä määritettiin tiedon rakenne sekä muuttujien maksimipituus: jos muuttujan todellinen arvo ei vastaa maksimipituutta, tyhjä tila täytetään esimerkiksi välilyönneillä. Tällä tavoin ohjelmoija voi olettaa, että tiettyyn muuttujaan liittyvä arvo alkaa aina samasta kohtaa. Laajemmin ajatellen, ohjelmoija tietää myös, että samaisen muuttujan arvo on samassa kohdassa jokaiselle tallennetulle tietueelle (tai oliolle).

Ensimmäisen osan esimerkeissä tiedon käsittelijän tai tiedon hakijan tulee tuntea käsite indeksi sekä osata hyödyntää indeksiä tiedon hakemisessa. Samalla, jos tiedon hakija haluaa vaikkapa useamman muuttujan arvon, tulee ohjelmaa sekä siinä käytettyjä indeksejä päivittää sopivasti. Ohjelmallisessa tiedon käsittelyssä tulee siis osata ohjelmointia.

Kun tietokantoja käytetään osana jokapäiväistä työtä, ei oletus "jokaisen tietokantaa käyttävän tulee osata ohjelmoida" ole kovin mielekäs. Tämä käytännössä vaatisi jokaiselta ohjelmointiosaamista sekä ymmärrystä tallennetusta tiedosta ja tiedon fyysisestä esitysmuodosta. Tiedon fyysinen esitysmuoto vaihtelee tallennettavan tiedon mukaan, joten tiedon käsittelyyn tarvitaan parempi ratkaisu. Tätä ongelmaa ja työläyttä ratkaisemaan on luotu useampia korkeamman abstraktiotason esitystapoja, joita käytetään tietokannassa olevan tiedon hakemiseen ja tiedon muokkaamiseen.

Tällä kurssilla keskitytään Structured Query Language (SQL) -kieleen. Structured Query Language (jatkossa SQL) on 1980-luvulla standardoitu kieli tietokantakyselyiden tekemiseen. SQL-kielen avulla voidaan määritellä tallennettavan tiedon muoto, luoda ja muokata tietokantatauluja, lisätä tietoa tietokantatauluihin, muokata tietokantatauluissa olevaa tietoa sekä hakea tietoa tietokannoista. Merkittävä osa tällä hetkellä käytössä olevista tietokannanhallintajärjestelmistä mahdollistaa SQL-kielellä tehtyjen kyselyiden käyttämisen tietokannanhallintajärjestelmässä olevien tietokantojen ja tietokantataulujen käsittelyyn. Voidaan ajatella, että SQL-kielellä tehdyt kyselyt ovat kuvattu rakenteellisella abstraktiotasolla, eli SQL-kieltä käytettäessä tiedon lopulliseen esitysmuotoon kiintolevyllä ei oteta kantaa.

Vuosien mittaan standardista on julkaistu useita versioita, joista viimeisin on vuodelta 2016. Tietokannanhallintajärjestelmät ja niiden eri versiot noudattavat SQL-kielen standardeja vaihtelevasti. Yhtä tietokannanhallintajärjestelmää varten luodut kyselyt eivät ole aina suoraan siirrettävissä toiseen tietokannanhallintajärjestelmään. On siis syytä huomioida että tietokannanhallintajärjestelmästä toiseen siirryttäessä joudutaan usein myös tekemään SQL-kyselyihin (pieniä) muutoksia. Tyypillisimpiä tietotyyppejä, joiden käsittelytapa vaihtelee eri tietokannanhallintajärjestelmien välillä ovat päivämäärät.

Kurssin toisessa osassa tutustutaan yhden tietokantataulun käsittelyyn SQL-kielellä. Opettelemme luomaan tietokantataulun, lisäämään tietokantatauluun tietoa, hakemaan tietokantataulusta tietoa sekä päivittämään ja poistamaan tietokantataulussa olevaa tietoa.

Käytämme tässä osassa SQLite-nimistä tietokannanhallintajärjestelmää.

SQLiten lataaminen ja käyttöönotto

SQLiten saa ladattua osoitteesta https://www.sqlite.org/download.html. Kun olet tallentanut (ja asentanut) SQLiten, käynnistä käyttöjärjestelmässä terminaali, ja kirjoita komento sqlite3 tietokanta.db.

Kyseinen komento luo tietokanta.db-nimisen tiedoston, joka sisältää käsittelemäsi tietokannan, ja avaa yhteyden kyseiseen tietokantaan.

Suorittamalla kyselyn "SELECT 1" sqlite tulostaa arvon 1.

kayttaja@kone:~/kansio/$ sqlite3 tietokanta.db
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> SELECT 1;
1
sqlite>

Voit käyttää kyseistä tietokantaa esimerkkien testaamiseen.

SQLiten konfigurointi

SQLite:n voi myös konfiguroida oman mieleseksi määrittelemällä .sqliterc-tiedoston kotihakemistoon. Tällöin samoja asetuksia ei tarvitse määritellä jokaisen käynnistyksen yhteydessä.

.sqliterc-pikaohje (macOS/Linux):

  1. Siirry kotihakemistoosi: cd ~ tai cd $HOME.
  2. Luo tiedosto nimeltä .sqliterc (jos sitä ei jo ole olemassa): touch .sqliterc.
  3. Avaa tiedosto mieleisellä tekstieditorilla, esim. nano .sqliterc.
  4. Lisää haluamasi asetukset erillisille riveille, tallenna muutokset ja poistu tekstieditorista. Useimmat järjestelmät vaativat terminaalin uudelleen käynnistämistä (tai ainakin uuden session avaamista) niin että SQLite lukee .sqliterc:n.

Esimerkiksi seuraavat asetukset voivat olla hyödyllisiä .sqliterc-tiedostossa:

  • .mode column – tulostaa kyselyn tuloksen sarakkeissa.
  • .headers on – näyttää sarakkeiden otsikot.
  • .timer on – tulostaa kyselyn tuloksen jälkeen kyselyyn kuluneen ajan.
  • .prompt "# " – käyttää merkkijonoa # rivin alussa normaalin sqlite> sijaan.

Tiedostoon voi myös lisätä PRAGMA-lauseita, kuten esimerkiksi PRAGMA FOREIGN_KEYS = ON;P jolloin SQLite tottelee viiteavainten rajoitteita.

SQL-kyselyiden muodosta

SQL-kieli on "case insensitive", eli sillä, että onko kysely kirjoitettu isoilla vai pienillä kirjaimilla kei ole kyselyn suorituksen kannalta merkitystä. Voimme kirjoittaa komennon SELECT yhtä hyvin muodossa select tai Select -- sama pätee myös taulujen ja sarakkeiden nimille.

Noudatamme tällä kurssilla seuraavaa käytäntöä:

  1. Kaikki SQL-kielen komennot, kuten SELECT, FROM ja WHERE, kirjoitetaan isolla.
  2. Taulujen nimet kirjoitetaan isolla alkukirjaimella. Esimerkiksi Henkilo ja Opiskelija.
  3. Taulujen sarakkeet eli attribuutit kirjoitetaan pienellä. Esimerkiksi nimi ja syntymavuosi.

Tietokantataulun luominen: CREATE TABLE

Tietokantataulu luodaan SQL-kielen CREATE TABLE lauseella, jota seuraa luotavan taulun nimi, ja suluissa tietokantataulun sarakkeiden tiedot pilkulla eroteltuna.

CREATE TABLE TAULUN_NIMI (
    sarakkeen 1 tiedot,
    sarakkeen 2 tiedot,
    sarakkeen 3 tiedot
)

Tietotyypit

Tietokantaan säilöttävä tieto voi olla montaa eri muotoa, esimerkiksi merkkijonoja, numeroita, binäärimuodossa olevia tiedostoja sekä päivämääriä. Tietokannan suunnittelijan tehtävänä on päättää kunkin sarakkeen tiedon tyyppi.

Sarakkeen tiedon tyyppi määrää minkämuotoista tietoa sarakkeen arvoksi voi tallentaa. Käytännössä tietokantataulua luotaessa sarakkeen määrittelyssä annettavat tiedot kertovat tietokannanhallintajärjestelmälle siitä, että minkälaista tietoa sarakkeeseen voidaan lisätä, ja toisaalta samalla minkälaista tietoa sarakkeeseen ei voida lisätä. Sarakkeen tyyppi vaikuttaa myös asioihin, joita sarakkeen arvoilla voi tehdä -- esimerkiksi keskiarvon laskeminen merkkijonotyyppisiä arvoja sisältävästä sarakkeesta ei ole kovin järkevää.

Tyypillisesti käytetyn tietotyypit ovat seuraavat:

  • varchar(n) korkeintaan n merkin pituinen merkkijono.
  • integer kokonaisluku
  • float liukuluku eli desimaaliluku
  • date päivämäärä, tallentaa vuoden, kuukauden ja päivän
  • timestamp aikaleima, tallentaa vuoden, kuukauden, päivän, tunnit, minuutit ja sekunnit -- mahdollisesti myös tarkempia arvoja
Lisää tietotyyppejä

Eri tietokannanhallintajärjestelmät kuten SQLite, MySQL ja PostgreSQL tarjoavat hieman erilaisia tietotyyppejä ohjelmoijan käyttöön. Seuraavissa dokumenteissa kerrotaan näistä enemmän.

Sarakkeen tietojen määrittely

Jokaisesta sarakkeesta kerrotaan sarakkeen nimi, sarakkeeseen tulevan tiedon tyyppi sekä tarvittaessa tietotyypille varattavan tilan koko -- esimerkiksi merkkijonoja tallennettaessa kerrotaan tallennettavan merkkijonon maksimipituus. Jokaisella sarakkeella tulee olla nimi sekä tyyppi. Jos sarakkeen tyyppiä ei määritellä tietokantataulua luotaessa, jotkut tietokannanhallintajärjestelmät määrittelevät sen automaattisesti, toiset taas näyttävät virheilmoituksen.

Sarakkeen tiedot ovat muotoa sarakkeen_nimi sarakkeen_tietotyyppi. Esimerkiksi syntymävuosi määritellään seuraavasti.

syntymavuosi integer

Korkeintaan 200 merkkiä pitkä nimi määriteltäisiin taas seuraavasti.

nimi varchar(200)

Henkilön syntymävuoden ja nimen tallentamiseen tarkoitettu taulu määriteltäisiin seuraavasti. Alla on oletettu, että nimi ei ole koskaan yli 200 merkkiä pitkä.

CREATE TABLE Henkilo (
    syntymavuosi integer,
    nimi varchar(200)
)

Yllä luodun esimerkkitaulun sisältö voisi olla esimerkiksi seuraavanlainen.

syntymävuosi nimi
1997 Pihla
1993 Joni

Pää- ja viiteavaimet

Edellisessä luvussa muunnettiin luokkakaavio relaatiokaavioksi kolmea askelta seuraamalla. Relaatiokaavio kuvaa tietokannan tauluja, ja taulut voidaan luoda SQL-kielisillä lauseilla. Relaatiokaavion voi kuvata tekstimuodossa seuraavasti.

Taulu ((pk) paaavaimen_nimi: PaaavaimenTyyppi, (fk) viiteavaimen_nimi: ViitattuTaulu, sarake:SarakeTyyppi, sarake:SarakeTyyppi, ...)

Tilausjärjestelmän relaatiokaavion voi kuvata tekstimuodossa seuraavasti.

  • Asiakas((pk) id:Integer, nimi:String, puhelinnumero:String, katuosoite:String, postinumero:Integer, postitoimipaikka:String)
  • Ravintola((pk) id:Integer, nimi:String, puhelinnumero:String, katuosoite:String, postinumero:Integer, postitoimipaikka:String)
  • Annos((pk) id:Integer, (fk) ravintola_id -> Ravintola, nimi:String, koko:String, hinta:double)
  • Tilaus((pk) id:Integer, (fk) asiakas_id -> Asiakas, aika:Date, kuljetustapa:String, vastaanotettu:Boolean, toimitettu:Boolean)
  • RaakaAine((pk) id:Integer, nimi:String)
  • AnnosRaakaAine((fk) annos_id - > Annos, (fk) raaka_aine_id -> RaakaAine)
  • TilausAnnos((fk) tilaus_id - > Tilaus, (fk) annos_id -> Annos)

Kun relaatiokaavion perusteella luodaan tietokantataulut, tulee taulut luoda järjestyksessä joka varmistaa viitattavien taulujen olemassaolon. Ensin luodaan taulut, joissa on vain pääavaimia (ei viiteavaimia), ja vasta sitten taulut, joissa on viiteavaimia. Eräs sopiva luontijärjestys edellisille tauluille on seuraava.

  • Asiakas((pk) id:Integer, nimi:String, puhelinnumero:String, katuosoite:String, postinumero:Integer, postitoimipaikka:String)
  • Ravintola((pk) id:Integer, nimi:String, puhelinnumero:String, katuosoite:String, postinumero:Integer, postitoimipaikka:String)
  • RaakaAine((pk) id:Integer, nimi:String)
  • Annos((pk) id:Integer, (fk) ravintola_id -> Ravintola, nimi:String, koko:String, hinta:double)
  • Tilaus((pk) id:Integer, (fk) asiakas_id -> Asiakas, aika:Date, kuljetustapa:String, vastaanotettu:Boolean, toimitettu:Boolean)
  • AnnosRaakaAine((fk) annos_id - > Annos, (fk) raaka_aine_id -> RaakaAine)
  • TilausAnnos((fk) tilaus_id - > Tilaus, (fk) annos_id -> Annos)

Pääavaimen määrittely CREATE TABLE -lauseessa

Pääavain on taulukohtainen tunniste, joka on uniikki jokaiselle taulun riville. Tämän lisäksi sen arvo ei saa olla tyhjä (null) millään rivillä. Pääavaimeksi valitaan joko olemassaoleva attribuutti (tällöin attribuutin tulee olla muuttumaton), tai sitä varten luodaan uusi attribuutti.

Pääavain määritellään tietokantataulun luonnin yhteydessä lisäämällä sarakkeen tyypin perään rajoite PRIMARY KEY. Tämä tarkoittaa sitä, että rivin arvon tulee olla on uniikki, ja että se ei saa koskaan olla tyhjä. Luodaan tietokantataulut Asiakas ja RaakaAine siten, että niissä on määriteltynä pääavain.

CREATE TABLE Asiakas (
    id integer PRIMARY KEY,
    nimi varchar(200),
    puhelinnumero varchar(20),
    katuosoite varcar(50),
    postinumero integer,
    postitoimipaikka varchar(20)
)
CREATE TABLE RaakaAine (
    id integer PRIMARY KEY,
    nimi varchar(200)
)

Viiteavaimen määrittely CREATE TABLE -lauseessa

Viiteavaimet ovat tietokantatauluissa sarakkeita, joissa olevat arvot viittaavat toisissa tauluissa oleviin pääavaimiin. Tietokantataulua määriteltäessä viiteavaimet listataan sarakkeiden määrittelyn jälkeen. Jokaisen viiteavaimen yhteydessä kerrotaan sekä luotavan taulun sarake -- eli sarake, joka on viiteavain -- että taulu ja sarake, johon viiteavaimella viitataan.

Viiteavaimen määrittely tapahtuu CREATE TABLE -lauseen loppuun asetettavan määreen FOREIGN KEY(sarake) REFERENCES ViitattavaTaulu(viitattavaSarake) avulla. Viiteavaimia voidaan määritellä useampia.

Oletetaan, että käytössämme on edellisessä alikuvussa määriteltytietokantataulu Asiakas. Nyt voimme luoda taulun Tilaus, jossa on viittaus tauluun Asiakas.

CREATE TABLE Tilaus (
    id integer PRIMARY KEY,
    asiakas_id integer,
    aika date,
    kuljetustapa varchar(40),
    vastaanotettu boolean,
    toimitettu boolean,
    FOREIGN KEY (asiakas_id) REFERENCES Asiakas(id)
);

Viiteavaimet ovat siis sarakkeita siinä missä muutkin sarakkeet, mutta niille määritellään erikseen tieto siitä, ette ne ovat viiteavaimia sekä tieto siitä, että mihin tauluun ja sarakkeeseen kukin viiteavain viittaa. Taulussa käytettävien viiteavainten määrä ei ole rajattu -- voi olla, että niitä ei ole yhtäkään, tai niitä voi olla useita.

SQLite ja viiteavaimet

Toistaiseksi käytössämme olevan SQLiten versiossa viiteavaimiin liittyvät tarkistukset -- eli tietokannanhallintajärjestelmän tekemä varmistus viitattavan rivin olemassaolosta -- ovat oletuksena poissa päältä. Tarkastukset saadaan päälle seuraavalla komennolla.

PRAGMA foreign_keys = ON;
Useampi arvo pääavaimena

Jokaisella taululla voi olla vain yksi määritelty pääavain. Joskus haluamme, että pääavain liittyy useampaan sarakkeeseen, jolloin sarakkeiden yhdistelmän tulee olla uniikki. Voimme esimerkiksi haluta rajoittaa annokseen määriteltäviä raaka-aineita siten, että kukin raaka-aine saa esiintyä kussakin annoksessa korkeintaan kerran. Tämä onnistuu määrittelemällä taululle AnnosRaakaAine pääavain, joka sisältää sekä annostunnuksen että raaka-aineen

CREATE TABLE AnnosRaakaAine (
    annos_id integer,
    raaka_aine_id integer,
    FOREIGN KEY (annos_id) REFERENCES Annos(id),
    FOREIGN KEY (raaka_aine_id) REFERENCES RaakaAine(id),
    PRIMARY KEY (annos_id, raaka_aine_id)
)

Tilausjärjestelmämme tietokannan CREATE TABLE -lauseet kokonaisuudessaan.

CREATE TABLE Asiakas (
    id integer PRIMARY KEY,
    nimi varchar(200),
    puhelinnumero varchar(20),
    katuosoite varcar(50),
    postinumero integer,
    postitoimipaikka varchar(20)
);

CREATE TABLE Ravintola (
    id integer PRIMARY KEY,
    nimi varchar(200),
    puhelinnumero varchar(20),
    katuosoite varcar(50),
    postinumero integer,
    postitoimipaikka varchar(20)
);

CREATE TABLE RaakaAine (
    id integer PRIMARY KEY,
    nimi varchar(200)
);

CREATE TABLE Annos (
    id integer PRIMARY KEY,
    ravintola_id integer,
    nimi varchar(200),
    koko varchar(30),
    hinta double,
    FOREIGN KEY (ravintola_id) REFERENCES Ravintola(id)
);

CREATE TABLE Tilaus (
    id integer PRIMARY KEY,
    asiakas_id integer,
    aika date,
    kuljetustapa varchar(40),
    vastaanotettu boolean,
    toimitettu boolean,
    FOREIGN KEY (asiakas_id) REFERENCES Asiakas(id)
);

CREATE TABLE AnnosRaakaAine (
    annos_id integer,
    raaka_aine_id integer,
    FOREIGN KEY (annos_id) REFERENCES Annos(id),
    FOREIGN KEY (raaka_aine_id) REFERENCES RaakaAine(id)
);

CREATE TABLE TilausAnnos (
    tilaus_id integer,
    annos_id integer,
    FOREIGN KEY (tilaus_id) REFERENCES Tilaus(id),
    FOREIGN KEY (annos_id) REFERENCES Annos(id)
);

Tietokantataulun poistaminen: DROP TABLE

Tietokantataulun poistaminen onnistuu DROP TABLE TAULUN_NIMI -lauseella, missä taulun nimi on poistettavan taulun nimi. Esimerkiksi edellä luodun Henkilo-taulun poistaminen onnistuisi seuraavasti.

DROP TABLE Henkilo

Huomaathan, että tietokantataulun poistaminen poistaa myös kaiken tietokantataulussa olevan datan. Komennosta löytyy myös versio, joka poistaa tietokantataulun vain jos kyseinen taulu on olemassa.

DROP TABLE IF EXISTS Henkilo
Olemassaolevien tietokantataulujen listaaminen

Olemassaolevien tietokantataulujen listaamiseen ei ole yhtä kaikissa tietokannanhallintajärjestelmissä toimivaa tapaa. Osoitteessa http://onewebsql.com/blog/list-all-tables oleva sivu listaa muutamien tietokannanhallintajärjestelmien syntaksit tietokantataulujen listaamiseen.

SQLiten tietokantataulut saadaan selville komennolla SELECT * FROM sqlite_master.

SELECT * FROM sqlite_master
Sarakkeiden tietotyypin selvittäminen

Attribuutin -- tai sarakkeen -- tietotyypin kysymiseen ei myöskään ole yhtä tapaa, vaan tapa liittyy käytettävään tietokannanhallintajärjestelmään. Käyttämässämme SQLite-versiossa sarakkeen tyypin saa selville kyselyllä PRAGMA TABLE_INFO(TAULUN_NIMI), missä TAULUN_NIMI on tarkasteltavan taulun nimi.

Esimerkiksi

PRAGMA TABLE_INFO(Henkilo)

Tiedon lisääminen tietokantatauluun: INSERT INTO

Tiedon lisääminen tietokantatauluun tapahtuu INSERT INTO -lauseella. Lausetta INSERT INTO seuraa kohdetaulun nimi, jonka jälkeen määritellään sarakkeet, joihin arvot asetetaan. Näitä seuraa vielä konreettiset arvot.

INSERT INTO TAULUN_NIMI (sarake1, sarake2, sarake3)
    VALUES ('merkkijono hipsuissa','numero ei', 123)

Oletetaan, että käytössämme on edellisessä aliluvussa luotu taulu Henkilo, jossa on sarakkeet syntymavuosi ja nimi. Uuden henkilon lisääminen tapahtuu seuraavasti.

INSERT INTO Henkilo (syntymavuosi, nimi)
    VALUES (1923, 'Edgar Frank Codd')

Yllä olevassa esimerkissä tietokantatauluun Henkilo lisätään uusi rivi. Sarakkeeseen syntymävuosi tulee arvo 1923 ja sarakkeeseen nimi tulee merkkijono 'Edgar Frank Codd'.

Vastaavasti vuonna 1947 syntyneen Raymond Boycen lisääminen tietokantatauluun Henkilo onnistuu seuraavasti.

INSERT INTO Henkilo (syntymavuosi, nimi)
    VALUES (1947, 'Raymond Boyce')

Pää- ja viiteavaimet tietoa lisättäessä

Tietyt tietokannanhallintajärjestelmät tarjoavat tuen automaattiselle pääavaimen arvojen luomiselle. Esimerkiksi SQLite luo automaattisesti kokonaislukutyyppiselle sarakkeelle arvoja, jos sarake on määritelty pääavaimeksi, ja ohjelmoija ei tauluun tietoa lisätessään arvoja erikseen määrittele. Vastaava toiminnallisuus löytyy myös useista muista tietokannanhallintajärjestelmistä -- tutustu esimerkiksi MariaDB:n dokumentaatioon asiasta.

Automaattista pääavaimen arvojen luomista hyödynnetään SQLitessä automaattisesti, jos INSERT-kyselyissä ei määritellä pääavaimelle arvoa. Seuraavilla lisäyskyselyillä tauluun RaakaAine lisättäviin riveihin lisätään automaattisesti lisättävät rivit yksilöivät pääavaimet.

INSERT INTO RaakaAine (nimi) VALUES ('Banaani');
INSERT INTO RaakaAine (nimi) VALUES ('Bataatti');
INSERT INTO RaakaAine (nimi) VALUES ('Peruna');
INSERT INTO RaakaAine (nimi) VALUES ('Porkkana');

Tiedon lisääminen tauluun Tilaus tapahtuu INSERT INTO -lauseen avulla. Nyt, koska taulussa Tilaus on viiteavain, tulee viitattavan asiakkaan tunnus olla tiedossa ennalta. Jos oletamme, että tilauksen tehnyt asiakkaan pääavaimen arvo on 7, onnistuu tilauksen lisääminen tietokantaan seuraavasti.

INSERT INTO Tilaus (asiakas_id, aika, kuljetustapa, vastaanotettu, toimitettu) VALUES (7, now(), 'nouto', false, false);

Yllä käytetty kutsu now() hakee järjestelmän ajan ja asetttaa sen lisättävän rivin sarakkeen aika arvoksi.

Miten tiedän lisätyn rivin pääavaimen?

Lisätyn rivin pääavaimen saa selville SQL-kyselyllä. Kun lisäämme uuden rivin, saa lisätyn rivin pääavaimen selville SELECT -lauseella, johon tutustumme kohta tarkemmin. Alla esimerkki RaakaAine-tauluun liittyen.

INSERT INTO RaakaAine (nimi) VALUES ('Banaani');
SELECT id FROM RaakaAine WHERE nimi = 'Banaani';

Ensimmäinen lause lisää raaka-aineen, ja toisella selvitetään raaka-aineen tunnus.

Tiedon hakeminen tietokantataulusta: SELECT

Tiedon hakeminen tietokantataulusta onnistuu SELECT-lauseella. Avainsanaa SELECT seuraa haettavat sarakkeet, tietokantataulun nimi sekä mahdollisesti rajausehto tai rajausehtoja.

Ilman rajausehtoa kyselyn rakenne on seuraava.

SELECT sarake1, sarake2 FROM TAULUN_NIMI

Rajausehdon kanssa kyselyn rakenne on seuraava.

SELECT sarake1, sarake2 FROM TAULUN_NIMI
    WHERE rajausehto

Oletetaan, että käytössämme on seuraava taulu, jonka nimi on Henkilo.

syntymavuosi nimi
1997 Pihla
1993 Joni
1947 Raymond
1923 Edgar

Jos haluamme listata kaikki taulun henkilöt, kysely olisi muotoa.

SELECT syntymavuosi, nimi FROM Henkilo

Valinta henkilöihin, jotka ovat syntyneet ennen vuotta 1950 onnistuu seuraavasti.

SELECT syntymavuosi, nimi FROM Henkilo WHERE syntymavuosi < 1950
Kaikki sarakkeet

Hakukyselyn SELECT-komentoa seuraava sarakelistaus voidaan korvata tähtimerkillä * jos halutaan hakea kaikki tietokantataulun sarakkeet. Olettaen, että taulussa Henkilo on vain sarakkeet syntymavuosi ja nimi, kysely:

SELECT syntymavuosi, nimi FROM Henkilo WHERE syntymavuosi < 1950

Voidaan korvata kyselyllä

SELECT * FROM Henkilo WHERE syntymavuosi < 1950

Jos sarakkeen arvot ovat merkkijonoja, kuten nimi, voi hakuehdossa käyttää LIKE-operaatiota. Tämän avulla hakutuloksia voi rajata osittaisen merkkijonon avulla. Esimerkiksi kysely SELECT * FROM Henkilo WHERE nimi LIKE '%a%' hakee kaikki henkilöt, joiden nimessä esiintyy a-kirjain.

Loogiset operaatiot

Rajausehtoihin voi lisätä loogisia operaatioita kyselyjen tulosten rajaamiseksi. Operaatio 'ja', eli kahden rajausehdon yhdistäminen, toimii avainsanalla AND. Esimerkiksi kysely SELECT * FROM Henkilo WHERE nimi = 'Ted' AND syntymavuosi = 1920 listaa vain ne henkilöt, joiden nimi on 'Ted' ja joiden syntymävuosi on 1920.

Operaation 'tai' lisääminen on myös mahdollista. Esimerkiksi kysely SELECT * FROM Henkilo WHERE nimi = 'Matti' OR nimi = 'Maija' listaisi kaikki ne henkilöt, joiden nimi on Matti tai Maija.

Kyselyissä toimivat myös suurempi kuin > ja pienempi kuin < -operaatiot.

Ehtoja voi myös yhdistää, jonka lisäksi suluilla voi rajata suoritusjärjestystä. Tutki kyselyä SELECT * FROM Kurssisuoritus WHERE (kurssi = 'Ohjelmoinnin perusteet' OR kurssi = 'Ohjelmoinnin jatkokurssi') AND arvosana = 3 ja mieti mitä se tekee.

Tiedon päivittäminen: UPDATE

Tietokantataulussa olevan tiedon päivittäminen onnistuu UPDATE-lauseella. Komentoa UPDATE seuraa tietokantataulun nimi, avainsana SET, jota seuraa sarakekohtaiset uudet arvot. Lopuksi kyselyyn lisätään rajausehto, jonka perusteella rajataan muutettavia rivejä.

UPDATE TAULUN_NIMI
    SET sarake1='uusiarvo', sarake2=1234
    WHERE sarake3='rajausarvo'

Esimerkiksi 'Joni'-nimisen henkilön nimen päivittäminen muotoon 'Joni S' onnistuu seuraavasti.

UPDATE Henkilo
    SET nimi='Joni S'
    WHERE nimi='Joni'

Tiedon poistaminen tietokantataulusta: DELETE FROM

Tiedon poistaminen tietokantataulusta onnistuu DELETE FROM -lauseella. Lauseeseen määritellään lisäksi tietokantataulu, mistä tietoa poistetaan, ja mahdollisesti ehtoja, jotka rajaavat poistettavia rivejä. Yksinkertaisimmillaan komennolla poistetaan kaikki rivit annetusta taulusta seuraavasti.

DELETE FROM TAULUN_NIMI

Poistettavien rivien rajaaminen tapahtuu WHERE-ehdolla, jota seuraa poistettavien arvojen rajaus. Esimerkiksi kaikki vuonna 1920 syntyneet henkilöt poistettaisiin tietokantataulusta Henkilo seuraavalla komennolla.

DELETE FROM Henkilo WHERE syntymavuosi = 1920

Poistoehtoon voi rakentaa loogisen lauseen, joka sisältää AND ja OR -määreitä. Näiden avulla poistorajausta voi tehdä laajemmin. Alla olevassa esimerkissä poistetaan henkilö (tai henkilöt), joiden syntymävuosi on 1947 ja joiden nimi on 'Raymond Boyce'.

DELETE FROM Henkilo WHERE syntymavuosi = 1947 AND nimi = 'Raymond Boyce'

Pienempi kuin ja suurempi kuin -vertailuoperaatiot sekä erisuuri kuin vertailuoperaatiot ovat myös mahdollisia. Alla olevassa esimerkissä poistetaan kaikki henkilöt, joiden syntymävuosi on pienempi kuin 2000 ja joiden nimi ei ole 'Boyce-Codd'.

DELETE FROM Henkilo WHERE syntymavuosi < 2000 AND nimi != 'Boyce-Codd'

Yhteenveto

Operaatio Avainsana Esimerkki
Tietokantataulun luominen CREATE
CREATE TABLE Opiskelija (
    opiskelijanumero integer,
    nimi varchar(60),
    sahkopostiosoite varchar(40)
)
Tietokantataulun poistaminen DROP
DROP TABLE Opiskelija
Tiedon lisääminen INSERT
INSERT INTO
    Opiskelija (opiskelijanumero, nimi, sahkopostiosoite)
    VALUES (1008286, 'Ari', 'posti@osoite.net');
Tiedon hakeminen SELECT
SELECT nimi FROM Opiskelija
Tiedon päivittäminen UPDATE
UPDATE Opiskelija
    SET nimi='Ari V'
    WHERE opiskelijanumero=1008286
Tiedon (rivien) poistaminen DELETE
DELETE FROM Opiskelija
    WHERE opiskelijanumero=1008286

Etsi tehtäväpohjan sisältävä kansio ja mene sen alikansioon db. Kyseisessä kansiossa on valmiina tiedosto esimerkki.db.

Luo kansioon db tietokanta levy.db. Avaa tietokanta sqliten avulla, ja luo tietokantaan tietokantataulu nimeltä Kappale, joka sisältää seuraavat sarakkeet:

  • nimi (merkkijono, maksimipituus 100 merkkiä)
  • artisti (merkkijono, maksimipituus 80 merkkiä)
  • levytysvuosi (kokonaisluku)
  • pituus (kokonaisluku)

Lisää tämän jälkeen tietokantatauluun Kappale seuraavat kappaleet.

Nimi Artisti Levytysvuosi Pituus
Capito Tutto Kummeli 1994 124
Kanada Kummeli 1994 119
Tango Vibrato Kummeli 1994 117

Voit testata tehtävän edistymistä TMC:n kautta. Jos tehtäväpohja näyttää punaista, klikkaa tehtäväpohjan Dependencies-kansiota hiiren oikealla napilla ja valitse "Download declared dependencies". Tämä lataa tehtäväpohjassa olevissa testeissä käytetyn SQLite-ajurin.

Etsi tehtäväpohjan sisältävä kansio ja mene sen alikansioon db. Kyseisessä kansiossa on valmiina tiedosto palkkatilastot.db. Kyseisessä tiedostossa on yksityisen sektorin mediaanipalkat. Palkkatilastot on noudettu tilastokeskuksen sivuilta osoitteesta http://www.stat.fi/til/yskp/2014/yskp_2014_2015-08-20_tie_001_fi.html.

Tietokannassa on käytetty seuraavanlaista CREATE TABLE -lausetta. Työtehtävät löytyvät sarakkeesta NAME, mediaaniansio sarakkeesta SALARY.

CREATE TABLE EMPLOYEE (
  ID INTEGER PRIMARY KEY NOT NULL,
  NAME TEXT NOT NULL,
  AGE INT NOT NULL,
  ADDRESS CHAR(50),
  SALARY REAL
);
  

Selvitä SQLite-ohjelman avulla tiedostosta seuraavat tiedot:

  • Mikä on ohutlevyseppien ("Ohutlevysepät") kuukausittainen mediaaniansio?
  • Kuinka monen työtehtävän mediaaniansio on alle 1900 euroa kuussa?
  • Kuinka monen työtehtävän mediaaniansio on yli 7000 euroa kuussa?
  • Minkä työtehtävän mediaaniansio on tasan 3300 euroa kuussa?

TMC:n tehtäväpohjassa on valmiit kohdat vastauksille. Kun tiedoston Tilastoja.java käynnistää nyt, ohjelma tulostaa.

Ohutlevyseppien mediaaniansio: 0
Työtehtäviä, joiden mediaaniansio on alle 1900 euroa: 0
Työtehtäviä, joiden mediaaniansio on yli 7000 euroa: 0
Työtehtävä, jonka mediaaniansio on tasan 3300 euroa kuussa: ???

Selvitä SQLite-ohjelman avulla edellisiin tietoihin oikeat vastaukset ja muokkaa ohjelmaa niin, että että ohjelma tulostaa edellä toivotut lukumäärät (ja työtehtävän) oikein. Muokkaa tulostuksissa vain kaksoispistettä seuraavaa tulostusta.

Tehtävässä on testit vain palvelimella. Testit odottavat, että tulostukset ovat täsmällisiä.

Tehtäväpohjassa on myös tiedosto palkkatilastot_varalla.db siltä varalta, että muokkaat vahingossa käsiteltävää tietokantatiedostoa.

Sisällysluettelo