Osa 2

Tietokantataulujen lisääminen ja poistaminen

Tietokannassa oleva tieto tallennetaan pysyväismuistiin kovalevylle, jossa se säilyy vaikka tietokantaa ylläpitävä tietokannanhallintajärjestelmä tai tietokannanhallintajärjestelmäohjelmistoa suorittava tietokone käynnistetään uudestaan. Tiedon tallennusmuoto vaikuttaa tietokannan tehokkuuteen — täysin satunnaisessa muodossa ja sitä kautta täysin satunnaisessa paikassa olevan tiedon hakeminen on hitaampaa kuin määrätyssä muodossa ja sitä kautta määrätyssä paikassa olevan tiedon hakeminen.

Tallennettavan tiedon muoto ja tyyppi halutaan määrittää sekä tiedon hakemiseen ja tallentamiseen liittyvän tehokkuuden vuoksi, että tietokannanhallintajärjestelmän toiminnan vuoksi. Kun tietokannanhallintajärjestelmälle kerrotaan tallennettavan tiedon muoto ja tyyppi, tietokannanhallintajärjestelmä pystyy varmistamaan, että tallennettava tieto on oikean muotoista. Tämä ennaltaehkäisee tiedon väärästä muodosta johtuvia ongelmatilanteita, kuten vaikkapa tilanteita, missä tietokantaan on tallennettu kokonaisluvun sijaan merkkijono, ja käyttäjä yrittää lukea ja muuntaa merkkijonon luvuksi.

Tarkastellaan seuraavaksi tietokantataulujen luomista SQL-kielellä. Tietokantataulu luodaan CREATE TABLE-lauseella, jossa määritellään tietokantataulun sarakkeet ja niiden tietotyypit sekä tietokantatauluun kuuluvat rajoitteet kuten pääavaimet ja viiteavaimet. Syntaksi tietokantataulun luomiselle on seuraava.

CREATE TABLE TaulunNimi (
    sarake1 tietotyyppi1,
    sarake2 tietotyyppi2,
    sarake3 tietotyyppi3,
    ... ...,
    rajoite1,
    rajoite2,
    ...
);

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

Tutustutaan seuraavaksi tietokantatauluissa käytettyihin tietotyyppeihin sekä rajoitteisiin.

Tallennettavan tiedon tyyppi

Tallennettavaa tietoa määriteltäessä kuvataan sekä tiedon tyyppi että tietoa varten varattavan tilan koko. Tieto tilan koosta auttaa tiedon hakemisessa.

Tarkastellaan tiedon koon merkitystä esimerkin kautta. Oletetaan, että haluamme tallentaa henkilöiden tietoja. Jokaisesta henkilöstä tallennetaan nimi ja osoite. Nimen tallentamiseen varataan 32 merkkiä ja osoitteen tallentamiseen 64 merkkiä. Nyt henkilöiden läpikäynti onnistuu käymällä muistia läpi 96 merkin kokonaisuuksissa. Mikäli haluaisimme esimerkiksi löytää kolmantena tallennetun henkilön tiedot, löytyisivät ne 192 merkin päästä henkilöiden tallentamiseen käytetyn alueen alusta.

Mikäli nimi ja merkkijono tallennettaisiin vaihtuvanmittaisina merkkijonoina, tulisi tietokannanhallintajärjestelmän pitää tarkempaa kirjaa kunkin henkilön tietojen alkukohdasta. Toinen mahdollisuus olisi henkilön tietojen loppumisen ja seuraavan henkilön tietojen alkamisen ilmaiseminen jollain muulla tavalla, kuten esimerkiksi jollain erikoismerkillä. Tällöin henkilöiden läpikäynti olisi kuitenkin hitaampaa, sillä seuraavan henkilön löytäminen vaatisi aina edellisen henkilön tietojen läpikäyntiä.

Esimerkkimme on kuitenkin hieman naiivi, sillä tietokannanhallintajärjestelmissä käytetään normaalin läpikäynnin lisäksi erilaisia tietorakenteita tiedon hakemiseen. Tarkastelemme näitä lyhyesti kurssin viidennessä osassa.

Tutustutaan seuraavaksi tiedon määrittelyyn SQL-kielen standardissa kuvattujen tietotyyppien avulla. Keskitymme muutamaan yleisimpään kategoriaan.

  • Merkkijonojen tallentamiseen tarkoitetut tietotyypit

    • Tietyn mittainen merkkijono CHAR(pituus) - käytetään mikäli merkkijonoa varten varataan aina saman mittainen alue. Mikäli tallennettavan tiedon pituus on lyhyempi kuin pituus, loppu alueesta täytetään tyhjällä tai erikoismerkeillä, joita ei huomioida kyseisestä sarakkeesta tietoa haettaessa.

    • Vaihtelevan pituinen merkkijono VARCHAR(maksimipituus) - käytetään mikäli merkkijonoa varten varattava alue vaihtelee tallennettavasta merkkijonosta riippuen. Tilaa käytetään aina korkeintaan maksimipituus-parametrin verran.

    • Hyvin iso merkkijono CLOB - käytetään tarvittaessa hyvin isojen merkkijonojen tallentamiseen — tietokannanhallintajärjestelmillä on tyypillisesti hyvin isojen merkkijonojen käsittelyä varten myös järjestelmäkohtaisia tietotyyppejä.

  • Numerojen tallentamiseen tarkoitetut tietotyypit

    • Kokonaislukujen tallentamiseen käytetyt SMALLINT, INTEGER ja BIGINT. Tyyppiä SMALLINT käytetään pienten kokonaislukujen tallentamiseen, tyyppiä INTEGER käytetään keskikokoisten kokonaislukujen tallentamiseen, ja tyyppiä BIGINT käytetään isojen kokonaislukujen tallentamiseen. Näiden konkreettinen koko on tietokannanhallintajärjestelmäkohtainen. Esimerkiksi PostgreSQL-järjestelmässä SMALLINT tallennetaan 8 bitin kokoisena, INTEGER 32 bitin kokoisena, ja BIGINT 64 bitin kokoisena.
    • Tarkkojen (desimaalilukujenkin) tallentamiseen käytetty NUMERIC, jolle määritellään tallennettujen lukujen määrä sekä tallennettuihin lukuihin sisältyvien desimaalilukujen määrä NUMERIC(lukujen_maara_yhteensa, desimaalilukujen_maara_korkeintaan). Esimerkiksi NUMERIC(3,1) tallentaa korkeintaan 3 lukua sisältävän numeron, jossa korkeintaan 1 desimaali — esim. 32.1 ja 999.

    • Epätarkkojen desimaalilukujen tallentamiseen käytetty FLOAT.

  • Päivämäärän ja ajan tallentamiseen tarkoitetut tietotyypit

    • Päivämäärän tallentamiseen käytetty DATE. Päivämäärä tallennetaan ja haetaan tyypillisesti muodossa yyyy-mm-dd eli esimerkiksi 2019-06-09 ja 2013-08-23.

    • Ajan tallentamiseen käytetty TIME. Aika tallennetaan ja haetaan tyypillisesti muodossa hh:mm:ss eli esimerkiksi 23:59:59 ja 13:01:32.

    • Aikaleiman tallentamiseen käytetty TIMESTAMP. Aikaleima on kulunut aika ns. Unix-ajanlaskun alusta, eli 1.1.1970. Aikaleiman tallennustarkkuus riippuu tietokannanhallintajärjestelmästä — tyypillisesti se tallennetaan joko sekuntien tai millisekuntien tarkkuudella.

  • Binääridata

    • mm. BLOB isojen binääritiedostojen tallentamiseen.
  • Totuusarvo BOOLEAN, jonka arvoksi voi asettaa true, false tai null. Näistä jälkimmäinen — mikäli sitä käytetään — tulkitaan yleensä epävarmuudeksi.

Yllä kuvattuja tietotyyppeja käyttävä tietokantataulu Tietotyypit luotaisiin seuraavasti.

CREATE TABLE Tietotyypit (
  neljan_merkin_merkkijonoalue CHAR(4),
  korkeintaan_neljan_merkin_merkkijonoalue VARCHAR(4),
  hyvin_iso_merkkijono CLOB,
  pieni_kokonaisluku SMALLINT,
  keskisuuri_kokonaisluku INTEGER,
  suuri_kokonaisluku BIGINT,
  tarkka_desimaaliluku NUMERIC(5,2),
  epatarkka_desimaaliluku FLOAT,
  paiva DATE,
  aika TIME,
  aikaleima TIMESTAMP,
  binaaridata BLOB,
  totuusarvo BOOLEAN
);

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

Pääavain on taulukohtainen kunkin rivin yksilöivä tunniste, jonka arvon tulee olla uniikki jokaiselle tauluun lisättävälle riville. Pääavain ei saa olla tyhjä (null) millään rivillä. Pääavaimeksi valitaan joko olemassaoleva attribuutti (tällöin attribuutin tulee olla käytännössä muuttumaton — esimerkiksi laskun numero), tai sitä varten luodaan uusi attribuutti.

Pääavain määritellään tietokantataulun luonnin yhteydessä lisäämällä sarakkeiden jälkeen PRIMARY KEY -rajoite, jota seuraa suluissa pääavaimeksi valittu sarake. Alla olevassa esimerkissä luodaan taulu Opiskelija. Opiskelijalla on sarakkeet opiskelijanumero sekä nimi — esimerkissä sarake opiskelijanumero valitaan pääavaimeksi.

CREATE TABLE Opiskelija (
  opiskelijanumero INTEGER,
  nimi VARCHAR(32),
  PRIMARY KEY (opiskelijanumero)
);

Yllä olevassa esimerkissä jokaisella opiskelijalla tulee olla eri opiskelijanumero. Tämä mahdollistaa tilanteen, missä tietokannassa voi olla kaksi saman nimistä opiskelijaa, jotka kuitenkin ovat oikeasti eri henkilöt. Opiskelijat voidaan erottaa toisistaan opiskelijanumeron perusteella.

Tietokantataulussa oleva pääavain voi muodostua myös useammasta sarakkeesta. Alla olevassa esimerkissä opiskelijanumero ja nimi muodostavat yhdessä taulun Opiskelija pääavaimen.

CREATE TABLE Opiskelija (
  opiskelijanumero INTEGER,
  nimi VARCHAR(32),
  PRIMARY KEY (opiskelijanumero, nimi)
);

Yllä kuvatussa tietokantataulussa ei voi olla kahta opiskelijaa, jolla on sama opiskelijanumero ja sama nimi. Esimerkissämme kahden sarakkeen pääavain ei ole kuitenkaan kovin järkevä, sillä siinä useammalla opiskelijalla voi olla sama opiskelijanumero, ja toisaalta useammalla opiskelijalla voi olla sama nimi.

Viiteavaimen määrittely CREATE TABLE -lauseessa

Viiteavaimet ovat sarakkeita, joissa olevat arvot viittaavat toisissa tauluissa oleviin pääavaimiin. Tietokantataulua määriteltäessä viiteavaimet listataan sarakkeiden ja pääavainten 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ä esimerkissä luotu tietokantataulu Opiskelija, jonka pääavaimena on sarake opiskelijanumero. Luodaan taulu Opintosuoritus, joka sisältää tiedon opintosuorituksesta sekä opinnon suorittaneesta opiskelijasta viitteenä käytettävän sarakkeen opiskelijanumero kautta.

CREATE TABLE Opintosuoritus (
    opiskelijanumero INTEGER,
    kurssi VARCHAR(64),
    suorituspaivamaara DATE,
    arvosana NUMERIC(1,0),
    FOREIGN KEY (opiskelijanumero)
        REFERENCES Opiskelija(opiskelijanumero)
);

Viiteavaimet ovat sarakkeita siinä missä muutkin sarakkeet. Niille määritellään erikseen tieto siitä, että 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.

Yllä olevassa esimerkissä taululla Opintosuoritus ei myöskään ole pääavainta. Tämä olisi periaatteessa sallittua, mutta lisätään tauluun pääavain. Luodaan pääavaimen avulla rajoite, missä sama opiskelija ei voi suorittaa samaa kurssia saman päivän aikana kahdesti.

CREATE TABLE Opintosuoritus (
    opiskelijanumero INTEGER,
    kurssi VARCHAR(64),
    suorituspaivamaara DATE,
    arvosana NUMERIC(1,0),
    PRIMARY KEY (opiskelijanumero, kurssi, suorituspaivamaara),
    FOREIGN KEY (opiskelijanumero)
        REFERENCES Opiskelija(opiskelijanumero)
);

Laajempi esimerkki tietokannasta

Käytimme edellisessä osassa ravintoloiden aterioihin ja tilauksiin keskittynyttä tietokantaa. Tarkastellaan lyhyesti tämän tietokannan luomista — käytössämme on edellisestä osasta tuttu tietokannan kuvaus.

  • 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)

Kuvana tietokanta näyttää seuraavalta.

Asiakkaita, ravintoloita, ruoka-annoksia, raaka-aineita, sekä ruokatilauksia sisältävä tietokanta.  
Asiakkaita, ravintoloita, ruoka-annoksia, raaka-aineita, sekä ruokatilauksia sisältävä tietokanta.

Tietokantataulut luodaan yksi kerrallaan. Kun tietokannan kuvauksesta luodaan tietokantataulut, tulee taulut luoda järjestyksessä joka varmistaa viitattavien taulujen olemassaolon. Luomme siis ensin ne taulut, joissa on vain pääavaimia, ja vasta sitten niihin viittaavat taulut. Eräs mahdollinen luontijärjestys edellisille tauluille on seuraava.

Ensin vain pääavaimia sisältävät taulut.

  1. Asiakas
  2. Ravintola
  3. RaakaAine

Sitten taulut, jotka viittaavat edellä luotuihin tauluihin, mutta eivät muihin tauluihin.

  1. Annos
  2. Tilaus

Ja sitten taulut, jotka viittaavat viiteavaimia sisältäviin tauluihin.

  1. AnnosRaakaAine
  2. TilausAnnos

SQL-kielellä em taulujen luonti onnistuu seuraavasti.

CREATE TABLE Asiakas (
    id INTEGER,
    nimi VARCHAR(200),
    puhelinnumero VARCHAR(20),
    katuosoite VARCHAR(50),
    postinumero INTEGER,
    postitoimipaikka VARCHAR(20),
    PRIMARY KEY (id)
);

CREATE TABLE Ravintola (
    id INTEGER,
    nimi VARCHAR(200),
    puhelinnumero VARCHAR(20),
    katuosoite VARCHAR(50),
    postinumero INTEGER,
    postitoimipaikka VARCHAR(20),
    PRIMARY KEY (id)
);

CREATE TABLE RaakaAine (
    id INTEGER,
    nimi VARCHAR(200),
    PRIMARY KEY (id)
);

CREATE TABLE Annos (
    id INTEGER,
    ravintola_id INTEGER,
    nimi VARCHAR(200),
    koko VARCHAR(30),
    hinta NUMERIC(5,2),
    PRIMARY KEY (id),
    FOREIGN KEY (ravintola_id) REFERENCES Ravintola(id)
);

CREATE TABLE Tilaus (
    id INTEGER,
    asiakas_id INTEGER,
    aika TIMESTAMP,
    kuljetustapa VARCHAR(40),
    vastaanotettu BOOLEAN,
    toimitettu BOOLEAN,
    PRIMARY KEY (id),
    FOREIGN KEY (asiakas_id) REFERENCES Asiakas(id)
);

CREATE TABLE AnnosRaakaAine (
    annos_id INTEGER,
    raaka_aine_id INTEGER,
    PRIMARY KEY (annos_id, raaka_aine_id),
    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-lauseella, jota seuraa poistettavan taulun nimi. Esimerkiksi edellä luodun Asiakas-taulun poistaminen onnistuisi seuraavasti.

DROP TABLE Asiakas;

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;

Tietokannanhallintajärjestelmät pyrkivät tyypillisesti ylläpitämään viite-eheyttä. Viite-eheydellä tarkoitetaan tilaa, missä viiteavaimet ovat jatkuvasti kunnossa ja toimivia. Tämä tarkoittaa mm. sitä, että edellä kuvatusta tilauksia sisältävästä tietokannasta ei voi poistaa pelkkää taulua Asiakas. Taulun Asiakas poistaminen onnistuu vasta kun siihen viittaava tai viittaavat taulut on poistettu.

Pääsit aliluvun loppuun! Jatka tästä seuraavaan osaan: