Relaatiomallien teoreettinen perusta

Relaatiomalli on Edgar F. Coddin kirjoittama, vuonna 1970 julkaistu teoria [6], joka muodostaa relaatiotietokantojen teoreettiset perusteet. Teoria mallin takana on joukko-oppiin perustuva ja muihin tietokantaparadigmoihin nähden vahva. Tässä materiaalissa relaatiotietokannan perusteoria lähestytään esimerkein ja verraten tuttuihin käsitteisiin. Materiaalissa ei syvennytä teorian yksityiskohtiin, kuten relaatioalgebraan -- tästä kiinnostuneet voivat tutustua Coddin [6] alkuperäisiin kirjoituksiin.

Relaatiomalli on loogisen tason tietomalli: se kuvaa millaisiin tietorakenteisiin tietoa tallennetaan.

Relaatiomallin perusrakenne

Arkimaailmassa relaatiot voi verrata Excel-taulukoihin. Relaatiomallin käsitteet ja niiden vertautuminen taulukkoihin on esitetty alla olevassa kuvassa.

Esimerkki

Alla oleva relaatio TOIMITTAJA kuvastaa tuotteiden toimittajia. Relaatio on kuvattu taulukon muodossa, ja viereen on merkitty relaatioiden käsitteitä.

Relaatiot voi siis useimmiten ajatella käytännössä Excel-taulukoina.

Käydään vielä kaikki käsitteet läpi ns. "virallisin" määritelmin:

Relaatio (relation, vrt. "taulukko") on pari $<\!H, h\!>$ , joka koostuu sisällöstä (body) , joka on joukko monikoita (vrt. "rivejä") sekä otsakkeesta (vrt. "otsikkorivistä") .

Sanotaan, että " on :n otsake" ja ":n attribuutit ovat :n" attribuutteja. Relaation, jonka otsake koostuu kappaleesta attribuutteja (vrt. " kappaleesta sarakkeita"), sanotaan olevan asteluvultaan (degree) .
Attribuutti (attribute, vrt. "sarake") on pari $<\!A, a\!>$ , joka koostuu attribuutin nimestä ja arvosta .

Attribuutti on yksittäisen kohteen ominaisuus, esimerkiksi yksittäisellä toimittajalla on attribuutti Kaupunki (attribuutin nimi) ja sen arvo on "Kalej" (attribuutin arvo).

Jos kohteen ominaisuuden sisältöä ei tunneta tai se ei ole merkityksellinen, arvoa sanotaan tyhjäarvoksi (merkitään usein NULL). Tyhjäarvon huomioiminen esim. tietojen tallentamisessa ja kyselyissä käsitellään tarkemmin materiaalin osassa 4.
Otsake (header, vrt. "taulukon otsikkorivi") on yhteen relaatioon liittyvä joukko attribuuttien nimiä. Relaation otsake on relaation kaikkien attribuuttien nimet.
Monikko (tuple, vrt. "taulukon tietorivi") on otsakkeen mukainen joukko järjestettyjä pareja $(<\!A_1, m_1\!>; <\!A_2, m_2\!>; \ldots; <\!A_n, m_n\!>)$ . Yksittäinen pari koostuu attribuutin nimestä ja sitä vastaavasta arvosta .
Relaatiotietokanta koostuu relaatioista sekä niihin liittyvistä eheysrajoitteista. Eheysrajoitteet käsitellään omassa alaluvussa.

Materiaalissa ja eri yhteyksissä käytetään myös seuraavia apukäsitteitä:

Relaatiokaava tai relaation rakenne on yhteisnimitys relaation nimelle ja otsakkeelle.
Relaation sisältö on relaatiossa olevien monikkojen joukko. Toisin sanoin, relaation sisältö on sama kuin taulukossa olevat rivit.
Relaation kardinaalisuus tarkoittaa siihen tallennettujen monikoiden lukumäärää. Toisin sanoin, kuinka monta riviä taulukossa on.
Attribuutin kardinaalisuus tarkoittaa sen erilaisten tallennettujen arvojen lukumäärää. Toisin sanoin, kuinka monta eri arvoa sarakkeessa on.

Esimerkki

Tarkastellaan relaatiota TOIMITUS, joka kuvastaa toimittajien tekemiä toimituksia.

Tarkastellaan yllä olevia käsitteitä esimerkkien kautta:

Relaation relaatiokaava on

TOIMITUS(toimittajanro, osanro, projektinro, määrä)

Tässä materiaalissa relaatiot kuvataan jatkossa pääosin vain relaatiokaavoilla.
Relaation otsake on taulukon otsikkorivillä olevat otsikot, eli toimittajanro, osanro, projektinro sekä määrä ovat yhdessä otsake.
Relaatiossa on viisi monikkoa eli tietoriviä.

Esimerkiksi:

(<toimittajanro, 1>; <osanro, 2>; <projektinro, 'p123'>; <määrä, 17>)

on ensimmäistä tietoriviä vastaava monikko. Monikon voisi kirjoittaa lyhyemmin jättämällä pois attribuuttien nimet:

(1, 2, 'p123', 17)
Jokaisella monikolla on aina neljä attribuuttia. Yllä olevassa esimerkissä

<toimittajanro, 1>

on ensimmäisen rivin monikon attribuutti, jonka nimi on toimittajanro ja arvo 1.
Attribuutin määrä kardinaalisuus on 5, sillä relaatiossa sarakkeessa määrä on viisi erillistä arvoa: 17, 23, 9, 4, 12.

# monirel1

Relaatiomallin ydinominaisuudet

Käsitteiden kannalta relaatiot ja taulukot ovat siis jotenkin verrattavissa toisiinsa. Relaatiot poikkeavat kuitenkin taulukoista alla olevilla tärkeillä rajoituksilla.

Relaatiomallin mukaan taulukkona kuvatulla, asteluvultaan relaatiolle pätevät aina seuraavat ydinominaisuudet.

Jokainen monikko edustaa :n ns. "-monikkoa". Toisin sanoin, jokaisella rivillä on tasan sarakkeiden määrän () verran arvoja (tai tyhjäarvoja NULL).
Monikoiden järjestyksellä ei ole merkitystä. Toisin sanoin, relaatiossa olevan tiedon merkitys ei muutu vaikka rivit sekoitettaisiin eri järjestyksiin.
Jokainen monikko on erotettavissa toisistaan. Toisin sanoin, relaatiossa ei ole kahta samanlaista riviä. Sen sijaan on olemassa yksi tai joukko useampia sarakkeita, joiden arvoilla rivit voidaan erotella toisistaan.
Otsakkeen järjestyksellä ei ole merkitystä, kunhan tietyn attribuutin nimen ja sen arvon yhteys voidaan yksiselitteisesti ymmärtää. Toisin sanoin, sarakkeiden järjestyksellä ei ole merkitystä kunhan sarakkeiden nimi käy arvoihin.
Attribuutin merkitys käy ainakin osittain ilmi sen nimestä. Sarakkeen nimi siis kertoo sen merkityksestä.

Esimerkki

Palataan hetkeksi vielä tauluun TOIMITUS:

Nähdään, että tämä on järkevä relaatio:

Jokaisella rivillä on saman verran arvoja kuin on sarakkeita. Jokaiselle sarakkeelle löytyy rivillä jokin arvo.
Toimitustietojen tulkinta ei muutu, vaikka rivit olisivat eri järjestyksessä.
Mikään rivi ei ole täysin samanlainen. Esimerkiksi vaikka kahdella ensimmäisellä rivillä on sama toimittajanro, niillä on eri osanro.
Toimitustietojen tulkinta ei muutu, vaikka sarakkeet olisivat eri järjestyksessä.
Rivillä olevien arvot ovat hyvin ymmärrettävissä sarakkeiden nimien perusteella.

Millainen taulu ei olisi siis hyvä relaatio? Tässä esimerkki, jossa kaikki säännöt on rikottu:

Yllä oleva TOP5_TOIMITTAJAA-relaatio ei kuitenkaan edusta sopivaa relaatiota. Mikään ominaisuus ei siis päde seuraavista syistä:

Kaikilla riveillä ei ole kaikissa sarakkeissa arvoja. Kahdella viimeisellä rivillä on sarakkeessa määrä arvo, mutta muilla ei ole. Jos arvo puuttuu tai se ei ole merkityksellinen, relaatiossa arvon tulee olla tyhjäarvo NULL.
Taulussa on varmaan ajateltu, että toimittajan sija top 5 -listassa on toimittajan monikon sijainti taulussa. Tämä ei relaatioissa kelpaa, sillä rivien sijainnilla taulussa ei pitäisi olla merkitystä tiedon merkityksen kannalta. Pitäisi siis olla jokin sarake sija, joka kertoisi toimittajan sijainnin top 5 -taulussa.
Taulussa on kaksi täysin samaa riviä, joita ei voi erottaa toisistaan.
Taulussa on kaksi saraketta aika. Ehkä on ajateltu, että ne tarkoittaisivat aikaväliä (minimiaika, maksimiaika), mutta nyt sarakkeiden nimen perusteella niitä ei voi erotella toisistaan. Tämä korjaantuisi nimeämällä sarakkeet selkeästi.
Samaan liittyen, ei ole kunnolla selvä, mitä aika tarkoittaa: toimitusaikaa minuutteina, käsittelyaika tunteita, tai jotain muuta? Tosin tämä selviäisi vaatimusmäärittelystä tai nimeämällä sarakkeet hyvin.

Eheysrajoitteet

Eheysrajoitteet ovat olennainen osa relaatiotietokantaa: niillä esitellään eri relaatioissa olevan datan välisiä yhteyksiä sekä yleisesti määritellään, millaista dataa monikoissa voidaan säilyttää. Tarkemmin sanottuna, eheysrajoitteet (integrity constraint) ovat erilaisia tekniikoita viite-eheyden varmistamiseksi relaatiotietokannassa. Relaatiomallin tärkeimmät eheysrajoitteet esitellään alla. Jotkin eheysrajoitteet ovat tietokantahallintajärjestelmäkohtaisia; niitä esitellään tarkemmin osassa 4.

Perusavain

Yllä olevien ydinominaisuuksien mukaan relaation jokaisen monikon tulee olla yksilöitävissä (eli jokaisen rivin pitäisi olla taulussa jollain tapaa erilainen). Toisin sanoin, relaatiolla on yhden tai usean attribuutin joukko, jonka arvon suhteen kaikki monikot eroavat toisistaan.

Attribuuttijoukko (jossa yksi tai useampi attribuutti), joka yksilöi relaation monikot, on nimeltään avainehdokas (candidate key, CK). Avainehdokkaalla tulee olla seuraavat ominaisuudet:

Avainehdokas on yksilöivä, eli kahdella monikolla ei ole samaa arvoa. Avainehdokas ei voi saada edes osittain tyhjäarvoa, eli mikään avainehdokkaassa olevan attribuutin arvo voi olla NULL.
Avainehdokas on jakamaton. Toisin sanoin, avainehdokkaaseen kuuluvista attribuutista yhtäkään ei voi poistaa ilman sitä, että yksilöitävyys säilyy. Eli jos avainehdokkaasta ottaisi pois jonkun attribuutin, ominaisuus 1. ei enää voisi pitää paikkaansa.

Avainehdokkaiden joukosta valitaan yksi relaation perusavaimeksi (primary key, PK). Perusavain on niin ikään attribuuttijoukko, joka yksiselitteisesti yksilöi relaation monikot. Perusavaimeen usein liitetään lisävaatimus, että sen arvon tulisi olla jokaisella monikolla muuttumaton. Aina tämä ei kuitenkaan ole mahdollista, ja perusavaimeksi voidaan joutua valitsemaan sellainen attribuuttijoukko, jonka arvo saattaa muuttua.

Relaatiokaavassa perusavain yleensä merkitään alleviivaamalla perusavaimeen kuuluvat attribuutit, esimerkiksi

RELAATIO(attribuutti_1, attribuutti_2, attribuutti_3)

Esimerkki

Tarkastellaan relaatiota OPISKELIJA, joka kuvastaa Jyväskylän yliopiston opiskelijan tietoja opiskelijarekisterissä (vrt. Sisu). Relaation kaava voisi yksinkertaisuudessaan olla seuraava:

OPISKELIJA(opnro, hetu, etunimi, sukunimi, postinumero, paikkakunta)

missä opnro on yliopiston sisäinen opiskelijanumero, hetu opiskelijan henkilötunnus, etunimi ja sukunimi opiskelijan etu- ja sukunimi, postinumero opiskelijan osoitteen postinumero ja paikkakunta opiskelijan osoitteen paikkakunta.

Kohdealueen perusteella löydetään seuraavat avainehdokkaat:

{opnro}, sillä jokaisella opiskelijalla on oma opiskelijanumero;
{hetu}, sillä jokaisella henkilöllä on oma henkilötunnus eikä kahdella eri ihmisellä ole samaa henkilötunnusta.

Seuraavat attribuuttijoukot eivät ole avainehdokkaita:

{opnro, hetu}: vaikka sekä opnro sekä hetu yksilöivät opiskelijat, jommankumman voidaan poistaa ja silti säilyttää yksilöitävyys (eli siis ominaisuus 2 ei päde);
{etunimi}, {sukunimi}, {postinumero}, {paikkakunta} tai {etunimi, sukunimi} tai vastaava näiden yhdistelmä: mikään näistä ei ole yksilöivä, eli kahdella eri opiskelijalla voi olla sama etunimi, sukunimi, postinumero, paikkakunta sekä näiden yhdistelmä (eli ominaisuus 1 ei päde);
{opnro, etunimi}, {hetu, sukunimi} ja muut vastaavat: taas, joukosta voidaan poistaa attribuutteja ja silti säilyttää yksilöitävyys.

Näistä kahdesta järkevin valinta perusavaimelle lienee {opnro}. Vaikka {hetu} on yksilöllinen, se voi opiskelijalla muuttua.

Yleisesti perusavaimeksi on yleensä parempi valita sellainen avainehdokas, joka ei vaihdu. Aina tätä ei voida täysin taata; siinä tapauksessa voi olla perusteltua keksiä jokin id-attribuutti, joka voi olla esimerkiksi juokseva numero tai jokin satunnaisesti muodostettu tunniste (ks. UUID, Snowflake ID, NanoID).

Viiteavain

Relaatioiden kuvaamissa kohdealueissa on tavallista, että yhden kohteen (esim. työntekijä) on pystyttävä viittaamaan toisiin kohteisiin (esim. projekti, johon työntekijä kuuluu). Tämä viittaus tehdään tavallisesti lisäämällä relaatioon toisen relaation attribuutteja.

Viiteavain (foreign key, FK) on relaation R attribuuttijoukko, jonka arvot ovat peräisin jonkin toisen relaation S attribuutin tai attribuuttijoukon arvojoukosta. Toisin sanoen relaation R viiteavaimen arvojoukko on relaation S attribuuttijoukon arvojoukon osajoukko. Viiteavaimen tarkoitus on mahdollistaa eri relaatioiden datan välisten suhteiden kuvaamista, mutta samalla varmistaa, ettei tietokantaan voi syntyä järjettömiä suhteita (esim. tilataan tuotetta, jota ei ole olemassa).

Viiteavain on yleensä toisen relaation perusavain, mutta se käytännössä voi olla mikä vaan toisen relaation attribuutti(joukko). Viiteavain voi siis koostua yhdestä tai useasta attribuutista. Viiteavain voi viitata mihin tahansa toiseen relaatioon, myös saman relaation toiseen attribuuttiin.

Viiteavain voidaan merkitä relaatiokaavan yhteydessä nuolimerkinnöin:

R(a, b, c)
S(d, e, a, b)

S.a -> R.a
S.b -> R.b

Esimerkki

Tarkastellaan lopuksi tämän luvun relaatiokaavat yhdessä osana relaatiotietokantaa:

Kuvio: Relaatiotietokannan kaava. Perusavaimet on alleviivattu, viiteavaimet on osoitettu nuolilla. Nuoli alkaa viiteavaimesta ja osoittaa viitattuun attribuuttiin.

Yllä kuvatussa relaatiotietokannan kaavassa on viisi relaatiokaavaa. TOIMITUS-relaatiossa on kolme viiteavainta, ja TYÖNTEKIJÄ-relaatiossa yksi. Viiteavaimet viittaavat toisten relaatioiden avainattribuutteihin.

Katsotaan lopuksi vielä koko tietokanta tauluina:

Kuvassa perusavaimeen kuuluvat attribuutit ovat alleviivattu, ja viiteavaimet sekä niiden suhde toisiin relaatioihin merkitty nuolella.

Huomaa erityisesti seuraavat asiat:

Perusavaimen attribuutit voivat olla myös viiteavaimia. Esimerkiksi TOIMITUS-attribuutin kaikki perusavaimeen kuuluvat attribuutit ovat samalla viiteavaimet toisiin relaatioihin.
Toisin kuin perusavain, viiteavaimeen kuuluvien attribuuttien arvot voivat olla tyhjäarvoja (eli NULL). Esimerkiksi työntekijällä, jonka htun on h200, on projektinro:n arvona NULL.
Viiteavaimeen kuuluvien attribuuttien ei tarvitse olla myöskään yksilöiviä. Esimerkiksi kahdella työntekijällä on sama projektinumero (voidaan tulkita, että työntekijät kuuluvat samaan projektiin).
Viiteavaimen tarkoitus on varmistaa tietokannan viite-eheys. Esimerkiksi tietokanta estäisi asettamasta työntekijän projektinumeroksi p400, sillä sellaista projektia ei ole tietokannassa olemassa.

Yleisesti DBMS hoitaa viiteavainten viite-eheyden tarkistamista. DBMS:t yleensä antavat myös määrittää, miten menetellään, jos viitatussa relaatiossa tapahtuu muutoksia (esim. projekti, jossa on työntekijöitä, poistetaan tietokannasta). Lähtökohtaisesti DBMS:llä on kolme tapaa käsitellä muutoksia monikoihin, joihin on viittauksia toisista relaatioista:

Muutos voidaan estää (restriction), jolloin monikon poistaminen tai viiteavaimen arvon poistaminen estetään viitatusta relaatiosta.
Jos viitatun relaation monikko poistetaan, tyhjätään (nullification) viittaavassa relaatiossa vastaavat viiteavaimen arvot asettamalla ne NULL:ksi.
Muutos voidaan vyöryttää (cascading), jolloin viitatussa relaatiossa olevia monikon arvoja muutetaan (jos viitattu arvo muuttui) tai poistetaan relaatiosta (jos viitattu arvo poistettiin).

Tarkemmin viite-eheyden varmistamiseen mennään osassa 4. Mainittakoon kuitenkin, että DBMS varmistaa, että viiteavaimen arvot viittaavat aina johonkin olemassa olevaan riviin jossain tietokannassa.

# monirel2

Muut eheysrajoitteet

Perus- ja viiteavainten lisäksi relaatiotietokantaan voidaan määritellä tarkempia ja monimutkaisempia rajoitteita. Esimerkiksi:

Työntekijän palkka ei saa ylittää esimiehensä palkkaa.
Osaston johtajana voi olla vain sellainen henkilö, joka on samalla työsuhteessa osastoon.
Puhelinnumeron tulee alkaa merkeillä +358.
Attribuutin arvona ei saa olla tyhjäarvo.

Eheysrajoitteiden määrittelyyn palataan jonkin verran osassa 4, kun tarkastellaan tietokantojen määrittämistä SQL-kielellä.

Sallitut tietotyypit

Lopuksi mainittakoon, että relaatiomalli asettaa pari lisävaatimusta attribuuttien arvoihin. Ensiksi, attribuutin arvoiksi sallitaan vain sellaiset tietotyypit, jotka eivät ole moniarvoisia (esim. listoja, taulukoita, hierarkioita). Toisin sanoin, attribuuttien arvojoukko on atominen. Toiseksi, attribuutti ei voi olla viittaus toiseen relaatioon (huom: voi olla viittaus toisen relaation attribuuttiin, kuten viiteavain on).

Relaatio, joka ei täytä näitä vaatimuksia, on normalisoimaton. Normalisoimattomia relaatioita on mahdollista korjata normalisoimalla ne. Normalisointiin palataan tarkemmin osassa 5.

Muuten relaatiomalli sallii laajasti erilaisia tietotyyppejä. Yleisesti attribuutit voivat olla, lukuarvoja, merkkijonoja, aikatietoja, totuusarvoja tai raakaa binääridataa. Osassa 4 tutustumme tarkemmin SQL:n avulla erilaisiin sallittuihin tietotyyppeihin.