1. Johdanto

Mikä on tietokanta?

Tietokanta (database) on tietokoneella oleva kokoelma tietoa, johon voidaan suorittaa hakuja ja jonka sisältöä voidaan muuttaa. Tietokantoja ovat esimerkiksi:

• yliopiston opiskelijarekisteri
• verkkokaupan tuotteet ja varastotilanne
• pankin tiedot asiakkaista ja tilitapahtumista
• päivittäin mitatut säätiedot eri paikoissa
• lentoyhtiön lentoaikataulut ja varaustilanne

Tietokantoja on nykyään valtavasti, ja useimmat ihmiset käyttävät tavallisen päivän aikana useita palveluja, jotka ovat yhteydessä tietokantoihin.

Tietokantojen haasteet

Tietokantojen tekniseen toteutukseen liittyy haasteita, kuten:

• Tehokkuus: Tietokannassa voi olla suuri määrä tietoa, johon kohdistuu jatkuvasti hakuja ja muutoksia. Miten toteuttaa tietokanta niin, että tietoon pääsee käsiksi tehokkaasti?

• Samanaikaisuus: Tietokannalla voi olla useita käyttäjiä, jotka voivat hakea ja muuttaa tietoa samaan aikaan. Mitä tietokannan toteutuksessa tulee ottaa huomioon tähän liittyen?

• Yllätykset: Tietokannan sisällön tulisi säilyä kunnossa myös yllättävissä tilanteissa. Esimerkiksi mitä tapahtuu, jos sähköt katkeavat juuri silloin, kun tietoa ollaan muuttamassa?

Tietokantajärjestelmät

Tietokantajärjestelmä pitää huolta tietokannan sisällöstä ja tarjoaa tietokannan käyttäjälle toiminnot, joiden avulla tietoa pystyy hakemaan ja muuttamaan. Huomaa, että sana tietokanta viittaa usein myös tietokantajärjestelmään.

Useimmat käytössä olevat tietokannat perustuvat relaatiomalliin ja SQL-kieleen, joiden teoreettinen perusta syntyi 1970-luvulla. Tutustumme tällä kurssilla SQLite-tietokantaan, joka soveltuu hyvin SQL:n opettelemiseen. Monet kurssin asioista toimivat samalla tavalla muissakin SQL-tietokannoissa.

Termi NoSQL viittaa tietokantaan, joka perustuu muuhun kuin relaatiomalliin ja SQL-kieleen. Esimerkiksi MongoDB ja Redis ovat tällaisia tietokantoja. Tällä kurssilla emme ´juurikaan käsittele NoSQL-tietokantoja.

Tee-se-itse-tietokanta

Ennen tutustumista olemassa oleviin tietokantajärjestelmiin on hyvä miettiä, mitä tarvetta tällaisille järjestelmille on. Miksi emme voisi vain toteuttaa tietokantaa itse vaikkapa tallentamalla tietokannan sisällön tekstitiedostoon sopivassa muodossa?

Katsotaan, mitä ongelmia tulee vastaan, kun koetamme toteuttaa itse yksinkertaisen tekstitiedostoon perustuvan tietokannan.

Esimerkki

Haluamme tallentaa tietokantaan tietoa kurssin opiskelijoiden ratkaisuista kurssin tehtäviin. Kun opiskelija lähettää ratkaisun, tietokantaan tallennetaan opiskelijanumero, tehtävän numero, ratkaisun lähetysaika sekä ratkaisun tuottama pistemäärä.

Yksinkertainen tapa toteuttaa tietokanta on luoda yksi tekstitiedosto, jonka jokaisella rivillä on yksi lähetys. Aina kun joku opiskelija lähettää ratkaisun, tiedostoon lisätään yksi rivi. Voisimme käytännössä tallentaa tietokannan CSV-formaatissa tiedostoon database.csv seuraavaan tapaan:

012121212;1;2020-05-03 12:50:32;100
012341234;1;2020-05-03 14:02:12;20
012121212;2;2020-05-04 14:05:50;70
012121212;3;2020-05-04 14:43:12;0
012341234;2;2020-05-04 10:15:23;0
012341234;2;2020-05-04 16:40:39;0
013371337;1;2020-05-06 18:11:13;0
012341234;2;2020-05-07 10:02:15;100

CSV-tiedostossa tietty erotinmerkki erottaa riveillä olevat kentät. Tässä tapauksessa erotinmerkkinä on puolipiste ;. Esimerkiksi tiedoston ensimmäinen rivi kertoo, että opiskelija 012121212 on lähettänyt ratkaisun tehtävään 1 ja saanut siitä 100 pistettä.

Nyt jos haluamme vaikkapa luoda tilaston, jossa on jokaisen opiskelijan lähetysten määrä, voimme hoitaa asian näin Python-kielellä:

stats = {}

for line in open("database.csv"):
    student_id = line.split(";")[0]

    if student_id not in stats:
        stats[student_id] = 0

    stats[student_id] += 1

print(stats)

Koodin tulostus on seuraava:

{'012121212': 3, '012341234': 4, '013371337': 1}

Tämä tarkoittaa, että opiskelija 012121212 on lähettänyt kolme ratkaisua, opiskelija 012341234 on lähettänyt neljä ratkaisua ja opiskelija 013371337 on lähettänyt yhden ratkaisun.

Mahdolliset ongelmat

Tällainen CSV-tiedostoa käyttävä tietokanta on periaatteessa toimiva, mutta sen käyttäminen voi johtaa ongelmiin:

Tehokkuus

Kun tiedon määrä kasvaa, tiedon hakeminen CSV-tiedostosta voi alkaa viedä aikaa. Tämä johtuu siitä, että joudumme useimmissa tilanteissa käymään läpi koko tiedoston sisällön alusta loppuun, kun haluamme saada selville tietyn asian.

Esimerkiksi jos haluamme selvittää, minkä pistemäärän opiskelija 012341234 on saanut tehtävästä 2, joudumme käymään läpi tiedoston kaikki rivit, jotta löydämme oikeat rivit. Joudumme tekemään näin, koska tiettyyn opiskelijaan liittyvät rivit voivat olla eri puolilla tiedostoa eikä meillä ole etukäteen tietoa niiden sijainnista.

Tiedoston läpikäynti ei ole ongelma, jos tiedosto on pieni. Esimerkiksi jos tiedostossa on sata riviä, läpikäynti sujuu hyvin nopeasti. Mutta tiedoston ollessa suuri on hidasta käydä kaikki rivit läpi aina, kun haluamme saada selville jonkin asian tietokannasta.

Samanaikaisuus

Mitä tapahtuu, jos kaksi opiskelijaa lähettävät ratkaisun samaan aikaan? Tällöin tiedoston loppuun pitäisi tulla kaksi riviä tietoa seuraavaan tapaan:

012341234;3;2020-05-07 15:42:02;0
013371337;7;2020-05-07 15:42:02;0

Jos käy huonosti, voi kuitenkin käydä näin:

012341234;3;2020013371337;7;2020-05-07 15:42:02;0
-05-07 15:42:02;0

Tällainen tilanne voi syntyä, kun kaksi samaan aikaan käynnissä olevaa prosessia muuttaa tiedoston sisältöä. Ensimmäinen prosessi kirjoittaa ensin tiedoston loppuun 012341234;3;2020, sitten toinen prosessi kirjoittaa väliin 013371337;7;2020-05-07 15:42:02;0 ja lopuksi ensimmäinen prosessi kirjoittaa -05-07 15:42:02;0. Tämän seurauksena tiedoston rakenne menee sekaisin.

Kun tiedostoon kirjoitetaan tietoa, ei ole itsestään selvää, että tieto menee perille yhtenäisenä, jos joku muu koettaa kirjoittaa samaan aikaan. Tämä riippuu tiedostojärjestelmästä, tiedon määrästä ja tiedoston käsittelytavasta.

Yllätykset

Oletetaan, että haluamme poistaa tietokannasta kaikki opiskelijan 012341234 lähetykset. Voimme tehdä tämän lukemalla ensin kaikki rivit muistiin ja kirjoittamalla sitten takaisin tiedostoon kaikki rivit, joissa opiskelija ei ole 012341234.

Mitä jos sähköt katkeavat juuri, kun olemme kirjoittaneet puolet riveistä takaisin? Kun käynnistämme tietokoneen uudestaan, huomaamme, että tiedostossa on vain puolet riveistä ja loput ovat kadonneet eikä meillä ole keinoa saada niitä takaisin.

Mitä opimme tästä?

Yksinkertainen tekstitiedosto ei ole sinänsä huono tapa tallentaa tietoa, mutta se ei sovellu kaikkiin käyttötarkoituksiin. Tämän vuoksi tarvitsemme erillisiä tietokantajärjestelmiä, joihin tutustumme tällä kurssilla.

Tietokantajärjestelmien kehittäjät ovat miettineet tarkasti, miten järjestelmä kannattaa toteuttaa, jotta tietoon pääsee käsiksi tehokkaasti, samanaikaiset käyttäjät eivät aiheuta ongelmia eikä tietoa katoa yllättävissä tilanteissa. Kun käytämme tietokantajärjestelmää, meidän ei tarvitse huolehtia tästä kaikesta itse.

Relaatiomalli ja SQL-kieli

Tällä kurssilla tutustumme tietokantoihin relaatiomallin ja SQL-kielen kautta. Tähän liittyvät perusideat ovat:

1. Kaikki tieto tallennetaan tauluihin riveinä, jotka voivat viitata toisiinsa.
2. Tietokannan käyttäjä käsittelee tietoa SQL-kielellä, joka kätkee käyttäjältä tietokannan sisäisen toiminnan yksityiskohdat.

Tietokannan rakenne

Tietokanta muodostuu tauluista (table), joissa on kiinteät sarakkeet (column). Tauluihin tallennetaan tietoa riveinä (row), joilla on tietyt arvot sarakkeissa. Jokaisessa taulussa on kokoelma tiettyyn asiaan liittyvää tietoa.

Seuraavassa kuvassa on esimerkki tietokannasta, jota voisi käyttää osana verkkokaupan toteutusta. Tauluissa Products, Customers ja Purchases on tietoa tuotteista, asiakkaista ja heidän ostoskoriensa sisällöstä.

Taulu Products sisältää tietokannassa olevat tuotteet, ja taulu Customers sisältää tietokannassa olevat asiakkaat. Tietokannassa on viisi tuotetta ja kolme asiakasta. Jokaisella tuotteella ja asiakkaalla on id-numero, jonka avulla niihin voidaan viitata.

Taulun Products sarakkeet ovat id, name ja price, eli tuotteen id-numero, nimi ja hinta. Esimerkiksi taulun ensimmäinen rivi tarkoittaa, että tietokannassa on tuote, jonka id-numero on 1, nimi on retiisi ja hinta on 7.

Taulun Customers sarakkeet ovat id ja name, eli asiakkaan id-numero ja nimi. Esimerkiksi taulun ensimmäinen rivi tarkoittaa, että tietokannassa on asiakas, jonka id-numero on 1 ja nimi on Uolevi.

Taulun Purchases tehtävänä on ilmaista asiakkaiden ostoskorien tuotteet. Jokainen taulun rivi kertoo, että tietyllä asiakkaalla on tietty tuote ostoskorissa. Sarake customer_id kertoo asiakkaan id-numeron ja sarake product_id kertoo tuotteen id-numeroon. Esimerkiksi taulun ensimmäinen rivi tarkoittaa, että asiakkaalla 1 (Uolevi) on ostoskorissa tuote 2 (porkkana).

Tässä esimerkissä Uolevin ostoskorissa on porkkana ja selleri ja Maijan ostoskorissa on retiisi, lanttu ja selleri. Aapelin ostoskori on puolestaan tyhjä, koska taulussa Purchases ei ole mitään riviä, jossa customer_id olisi 3.

SQL-kieli

SQL (Structured Query Language) on vakiintunut tapa käsitellä tietokannan sisältöä. Kielessä on komentoja, joiden avulla tietokannan käyttäjä (esimerkiksi tietokantaa käyttävä ohjelmoija) voi lisätä, hakea, muuttaa ja poistaa tietoa.

SQL-komennot muodostuvat avainsanoista (kuten SELECT ja WHERE), taulujen ja sarakkeiden nimistä sekä muista arvoista. Esimerkiksi komento

SELECT price FROM Products WHERE name = 'retiisi';

hakee tietokannan tuotteista retiisin hinnan. Komennon lopussa on puolipiste ; ja voimme käyttää välilyöntejä ja rivinvaihtoja haluamallamme tavalla. Voisimme kirjoittaa komennon myös usealle riville esimerkiksi seuraavasti:

SELECT price
FROM Products
WHERE name = 'retiisi';

SELECT
  price
FROM
  Products
WHERE
  name = 'retiisi';

Tutustumme SQL-kieleen tarkemmin materiaalin luvuissa 2–4.

SQL-kieli syntyi 1970-luvulla, ja siinä on paljon muistumia vanhan ajan ohjelmoinnista. Tällaisia piirteitä ovat esimerkiksi:

• Avainsanat ovat kokonaisia englannin kielen sanoja, ja komennot muistuttavat englannin kielen lauseita.
• Avainsanoissa kirjainkoolla ei ole väliä. Esimerkiksi SELECT, select ja Select tarkoittavat samaa. Avainsanat kirjoitetaan usein kokonaan suurilla kirjaimilla.
• Merkki = tarkoittaa sekä asetusta että yhtäsuuruusvertailua (nykyään ohjelmoinnissa yhtäsuuruusvertailu on yleensä ==).

SQL-kielestä on olemassa standardeja, jotka pyrkivät antamaan yhteisen pohjan kielelle. Käytännössä jokaisen tietokannan SQL-kielen toteutus toimii kuitenkin hieman omalla tavallaan. Tällä kurssilla tutustumme SQL-kieleen SQLite-tietokannan näkökulmasta, mutta pääpaino on SQL:n ominaisuuksissa, jotka ovat yleisesti käytettävissä eri tietokannoissa.

SQL-tietokannat

Tietokannan sisäinen toiminta

Tietokantajärjestelmän tehtävänä on käsitellä käyttäjän antamat SQL-komennot. Esimerkiksi kun käyttäjä antaa komennon, joka hakee tietoa tietokannasta, tietokantajärjestelmän tulee löytää jokin hyvä tapa käsitellä komento ja toimittaa tulokset käyttäjälle mahdollisimman nopeasti.

SQL-kielen hienoutena on, että käyttäjän riittää kuvailla, mitä tietoa hän haluaa, minkä jälkeen tietokantajärjestelmä hoitaa työn ja hankkii tiedot tietokannan uumenista. Tämä on mukavaa käyttäjälle, koska hänen ei tarvitse tietää mitään tietokannan sisäisestä toiminnasta vaan voi luottaa tietokantajärjestelmään.

Tietokantajärjestelmän toteuttaminen on vaikea tehtävä, koska järjestelmän täytyy sekä osata käsitellä tehokkaasti SQL-komentoja että huolehtia siitä, että kaikki toimii oikein samanaikaisilla käyttäjillä ja yllättävissä tilanteissa. Tällä kurssilla tutustumme tietokantoihin lähinnä tietokannan käyttäjän näkökulmasta emmekä perehdy tietokantojen sisäiseen toimintaan.