Käsitteellinen mallintaminen ja relaatiotietokannat - Luento 2

• Luentotaltiointi
• Käsitteellinen mallintaminen (Entity-Relationship-malli)
  • Kohteet
  • Ominaisuudet
  • Suhteet
• ER-kaavioiden piirtäminen
  • Esimerkkikaavio
• Relaatiotietokannat
  • Esimerkki
• Taulut (tables)
• Kentät (fields)
  • Standardoidut tietotyypit
• Avaimet (keys, indexes)
• Viite-eheys (Referential Integrity)

Tutustutaan relaatiotietokantojen peruskäsitteisiin sekä ER-kaavioiden piirtämiseen ja muuntamiseen relaatiotietokannaksi.

Käsitteellinen mallintaminen (Entity-Relationship-malli)

Kohteet

• Kohde on ominaisuuskokoelma
• Kohde ei ole ominaisuus tai yksittäinen tieto!
• Kohteen yhteyteen liitetään vain kohteeseen välittömästi liittyviä tietoja (ominaisuuksia).
• Kunkin tiedon päivitys tapahtuu vain yhteen paikkaan
• Tunnistettavissa oleva asia tai tapahtuma.
• Kohdetta kuvaa usein substantiivi.
• Helposti havaittavia kohteita ovat käyttäjien puheissa esiintyvät henkilöt, esineet, tilat ja tuotteet. Hankalampia ovat käsitteelliset kohteet kuten tilaus tai sopimus.
• Kohteet ovat sellaisia, joista halutaan tallentaa tietoja pysyvästi tietokantaan. Raportit ja tulosteet eivät ole kohteita vaan tietokannan tiedoista johdettuja tulostietoja

Ominaisuudet

• Jokaisella samantyyppisellä kohteella on tiettyjä yhteisiä ominaisuuksia
  • Henkilöillä on kaikilla nimi, hetu, osoite yms.
• Ominaisuus vastaa taulukossa sarakeotsikoita.
• Ominaisuus ei ole tallennettava tieto!
• Ominaisuuksien joukosta valitaan avaimeksi sopivat
  • Avaimen pitää olla yksilöivä, uniikki.
  • Avain voi olla myös yhdistelmä useammasta kentästä. (esim. puhelinnumero: etuosa + loppuosa)
  • Avain voi olla juokseva numero omana ominaisuutena.
• Ominaisuus voi olla yksi- tai moniarvoinen. Moniarvoiset ominaisuudet (multivalued attributes) irrotetaan tällä kurssilla omiksi kohteikseen.
• Ominaisuuden arvojen kannattaa olla jakamattomia, osat kannattaa erottaa aina omiksi ominaisuuksikseen. Esim. etunimi ja sukunimi.
• Muista ominaisuuksista johdettavia tietoja (derived attributes) ei kannata laittaa ominaisuuksiksi (täydelliseen ER-kaavioon ne merkitään katkoviivalla).
  • esim. tilausten kokonaislukumäärä lasketaan yksittäistilausten kappalemääristä.
  • esim. ikä lasketaan syntymäajasta.
• Jokainen ominaisuus saa arvonsa tietystä arvojoukosta (esim. kurssin arvosana voi olla 1,2,3,4,5 tai 0 eli hylätty).
• Sallitaanko ominaisuuksille tyhjät arvot. (esim. koditon)
• Sallitaanko tuntemattomat arvot.
• Ominaisuus voi liittyä myös suhteeseen.

Suhteet

• Suhdetta kuvaa usein verbi.
• Suhde on vähintään kahden kohteen välillä vallitseva riippuvuus.
• Suhde voi merkitä olemassaoloa, toiminnallista suhdetta tai tapahtumaa
• Keksi, jos mahdollista, sellainen kuvaava sana, joka toimii molempiin suuntiin.
• Suhteen aste määräytyy suhteeseen liittyvien kohteiden lukumäärän mukaan.
• Suhteen kardinaalisuus:
  • Yhden suhde yhteen (one-to-one, 1-to-1)
  • Yhden suhde moneen (one-to-many, 1-to-M) (monen suhde yhteen (many-to-one, M-to-1))
  • Monen suhde moneen (many-to-many, M-to-M)

ER-kaavioiden piirtäminen

Piirto-ohjelmana voi käyttää mitä tahansa kaavioiden piirtämiseen sopivaa työkalua tai vaikka Exceliä. Järkevintä on kuitenkin käyttää erityisesti ER-kaavioita varten suunniteltuja työkaluja koska ne helpottavat kaavion muuntamista tietokannaksi. Tällä kurssilla käytetetään Erkka-ohjelmaa.

• Kohteet
  
  • Jokainen kohde esitetään suorakulmiona jonka sisällä lukee kyseisen kohteen nimi.
• Ominaisuudet
  
  • Esitetään ellipseillä jotka on liitetty jatkuvalla viivalla kohteeseen tai suhteeseen. Ellipsin sisällä lukee ominaisuuden nimi.
  • Avaimina toimivat ominaisuudet alleviivataan.
  • Hieman epästandardisti voidaan ominaisuus merkitä myös kohteen sisältävään suorakulmioon.

    

• Suhteet
  
  • Jokainen suhde esitetään timanttikuviona jonka sisällä lukee suhteen nimi.
  • Suhteeseen liittyvät kohteet liitetään siihen jatkuvalla viivalla joista jokaisen kohdalla lukee 1 tai M riippuen siitä onko kyseessä yhden suhde yhteen, yhden suhde moneen vai monen suhde moneen.
  • Lue kaaviota esimerkiksi seuraavasti:

    

    • "Yksi henkilö voi asua enintään yhdellä paikkakunnalla" (ei pakollinen tieto)
    • "Henkilö asuu tietyllä paikkakunnalla" (pakollinen tieto)
    • "(Yhdellä) paikkakunnalla voi asua monta henkilöä."

Esimerkkikaavio

Suunnitellaan tietokanta pienen yrityksen tietojen ja projektien hallintaan. Yksinkertaistettu versio tietokannan vaatimusmäärittelystä on seuraavanlainen:

• Työntekijöistä talletetaan nimi (etunimi, toinen nimi, sukunimi), puhelinnumerot, palkka ja tieto mahdollisista lapsista.
• Jokainen työntekijä toimii jollakin osastolla.
• Projekteissa työskentelee yksi tai useampia työntekijöitä. Sama työntekijä voi olla yhtäaikaa monessa projektissa.
• Jokaisella projektilla on yksi projektipäällikkö.
• Projekteissa rakennetaan monenlaisia laitteita. joihin tarvitaan tietty määrä tietynlaisia osia. Osia projekteille toimittavat monet eri toimittajat, joiden yhteystiedot pitää tallettaa järjestelmään. Järjestelmän pitää sisältää tieto siitä, mitä osia ja miltä toimittajalta on toimitettu millekin projektille.
• Jotkut osat koostetaan itse muista osista. Tietokannan pitää siis sisältää tieto myös osien koostumuksesta.
• Järjestelmästä pitää selvitä myös, mitkä toimittajat pystyvät toimittamaan mitäkin osia.

Relaatiotietokannat

• E.F. Codd esitteli relaatiomallin jo 1970
• yksinkertaisin, joustavin ja toteuttaa parhaiten tietokannalle asetettavat vaatimukset
• Vaatii eniten koneresursseja
• Relaatiotietokannassa tiedot esitetään tauluina (table) joiden yhtä riviä kutsutaan tietueeksi (record)
• taulun jokaisella rivillä on yhtä monta tietoa
• Jokaisella rivillä on yksikäsitteinen perusavain
• kutakin riviä vastaa jokin reaalimaailman kohde
• kuhunkin kohteeseen liitetään vain siihen välittömästi liittyvät ominaisuudet
• tauluja kutsutaan myös relaatioiksi
• Kukin yksittäinen tieto kannassa voidaan hakea ainakin ilmoittamalla taulun nimi, perusavaimen sarakenimi ja avaimen arvo sekä haettavan tiedon sarakenimi. Ainakin tämä hakutapa olemassa, lisäksi lukemattomia muita.

Esimerkki

Relaatiotietokantaesimerkki

Taulut (tables)

Taulun nimeksi kannattaa valita jokin selkeä ja kuvaava nimi. Nimessä kannattaa kuitenkin välttää välilyöntejä, erikoismerkkejä ja skandinaavisia merkkejä (å,ä,ö).

Jokainen taulu sisältää yhden tai useamman kentän

Kentät (fields)

Kenttiä luotaessa niille määritellään seuraavanlaisia ominaisuuksia:

• Kentän nimi

  Kentän nimeksi kannattaa valita jokin selkeä ja kuvaava nimi. Nimessä kannattaa kuitenkin välttää välilyöntejä, erikoismerkkejä ja skandinaavisia merkkejä (å,ä,ö).

• Tietotyyppi
  • Numeerinen

    Esim. lukumaara, paino, pituus, matka

  • Merkkijono

    Esim. Hetu, Etunimi, Sukunimi, postitoimipaikka, postinumero (!), kuvaus, puhelinnumero (!)

  • Aika

    Esim. Syntymäaika, Tenttipvm

• Kiinteä vai vaihtuvamittainen merkkijono?

  Esimerkkejä

  • Hetu on kiinteämittainen (aina 11 merkkiä)
  • Postinumero on kiinteämittainen (aina viisi merkkiä)
  • Etunimi on vaihtuvamittainen
  • Sukunimi on vaihtuvamittainen
  • Puhelinnumero on vaihtuvamittainen
• Maksimipituus ja mahdollinen tarkkuus

  Esimerkkejä

  • Etunimi-kentälle maksimipituus voisi olla 30 merkkiä.
  • Sukunimi-kentälle maksimipituus voisi olla 100 merkkiä.
  • Lukumaara-kentän tarkkuudeksi riittävät kokonaisluvut
  • Paino-kentän tarkkuus pitää olla vähintään yksi desimaali esim. 70,5 kg
• Onko tieto pakollinen

  Mahdollisimman moni kenttä pitää määritellä pakolliseksi tai kentälle pitää antaa jokin oletusarvo jolloin vältetään tuntemattomien arvojen tuomat ongelmat

• Tarkistukset syötettävän tiedon oikeellisuudelle tai sallittujen arvojen joukko.

  Esimerkkejä

  • Hetu-kentän tarkistus: alussa pitää olla kuusi numeroa, sitten +,- tai A, sitten kolme numeroa ja viimeisenä tarkistusmerkki, jonka arvo saadaan laskettua kaavalla.
  • Postinumeron tarkistus: Viisi merkkiä "0123456789"-joukosta
  • Syntymäajan tarkistus: Jos käsitellään vain elossa olevia niin voitaisiin tarkistaa että syntymäaika ei saa olla ennen vuotta 1880. Syntymäaika ei saa olla myöskään suurempi kuin meneillään oleva ajanhetki.
  • Lukumäärän on oltava suurempi kuin nolla
• Oletusarvo

  Esimerkkejä

  • Lukumäärän oletusarvo voisi olla esim. 1 tai 0

Standardoidut tietotyypit

Seuraavassa luetellaan tärkeimmät standardoidut tietotyypit.

CHAR [(pit)]

Kiinteänmittainen merkkijono. Oletuspituus on 1.

• Hetu: CHAR(11)
• Postinumero: CHAR(5)

VARCHAR [(pit)]

Vaihtuvanmittainen merkkijono jolle määritetään maksimipituus

• Etunimi: VARCHAR(30)
• Sukunimi: VARCHAR(100)

NUMERIC [(pit., [desim.osa])]

Tarkka numeerinen arvo, jonka maksimipituus on pit numeroa ja desim.osa kuvaa desimaaliosan pituutta.

• Paino: NUMERIC(4,1) eli enintään neljä numeroa joista yksi on varattu desimaaliosalle

INTEGER

Kokonaisluku, jolla on käyttöympäristöstä riippuva maksimikoko

• Lukumaara: INTEGER

DATE

Vuosi (0001 - 9999), kuukausi ja päivä.

• Syntymaaika: DATE

TIME

tunti, minuutti ja sekunti.

• Kellonaika: TIME

INTERVAL [(mittayksikkö)]

ajanväli, kulunut aika

• Juoksuaika: INTERVAL HOUR TO MINUTE

TIMESTAMP

Vuosi, kuukausi, päivä, sekä tunti, minuutti ja sekunti.

• Lahetysaika (esim. sähköposti): TIMESTAMP

Avaimet (keys, indexes)

• perusavain (primary key)
  • Perusavain on tietueen uniikki tunniste
  • yksikäsitteinen muodostuen yhdestä tai useammasta sarakkeesta
  • taulusta ei saa löytyä identtisiä perusavaimen arvoja
  • ei saa puuttua yhdeltäkään riviltä
• Toissijaiset avaimet (secondary key, secondary index)

  Nopeuttavat taulun järjestämistä vrt. hakemisto

  Toimivat rajoitteina

• Viite-avaimet (foreign key)

  kelpuutettavat arvot ovat samoja kuin jonkin taulun kentän arvot (viitatun taulun)

  Puhelinnumero-taulun Hetu-kenttään voi syöttää vain sellaisia arvoja jotka esiintyvät Henkilo-taulun hetu-kentästä:

  

Esimerkki tauluun syötetyistä tiedoista:

tenttii-esimerkki Erkalla

Tietokantaesimerkki samasta aiheesta (mdb)

Viite-eheys (Referential Integrity)

• jokaista taulussa esiintyvää viiteavaimen arvoa pitää vastata sama perusavaimen arvo taulussa, johon viiteavain viittaa
• yritettäessä rikkoa viite-eheyttä perusavaimen päivityksellä on kolme vaihtoehtoa:
  • Viiteavaimen nollaus

    Kaikki poistuvaan perusavaimeen viittaavat viiteavaimet muutetaan tuntemattomiksi. Soveltuu vain sarakkeisiin, joilta ei ole kielletty puuttuvaa arvoa.

  • Johdannaispoisto

    kaikki poistuvaan perusavaimeen viittaavat viiteavaimet ja vastaavat rivit poistetaan

  • Rajoitettu poisto

    Voidaan poistaa vain perusavaimet, joihin ei ole viittauksia