Tietokannan suunnittelu - Luento 5

• Luentotaltiointi
• Mikä on tietokanta?
  • Mitä tietokannalta halutaan?
• Esimerkki: Laaja osoitekirja
  • Ratkaisu 1: Paperi ja kynä.
  • Ratkaisu 2: Mappi välilehdillä.
  • Ratkaisu 3: Tekstitiedosto tai dokumentti.
  • Ratkaisu 4: Tavallinen taulukko
  • Ratkaisu 5: Edistynyttä taulukkolaskentaa.
  • Ratkaisu 6: Tietokanta
• Taulukoista relaatiotietokantoihin
  • Toiston välttäminen
  • Käsitteitä
  • Viitteet ja suhteet
  • Tietokantojen hyödyt
• Tietokannan suunnittelun vaiheet
• Käsitteellinen mallintaminen (ER-malli)
  • Kohteet
  • Ominaisuudet
  • Suhteet
• ER-kaavioiden piirtäminen
  • Esimerkkikaavio
  • Esimerkkejä

Tällä luennolla selvitetään mikä on tietokanta. Käydään läpi myös tietokantojen suunnittelun perusteita ER-mallinnuksen avulla.

Mikä on tietokanta?

• Tietokanta on kokoelma yhteenliittyvää tietoa (data)
• Tietokanta on loogisesti yhtenäinen kokoelma tietoa jolla on jokin merkitys.
• Tietokanta on suunniteltu, rakennettu ja täytetty tiedolla jotain tiettyä tarkoitusta varten. Sillä on jokin tarkoitettu käyttäjäryhmä ja joitain ennaltalaadittuja ohjelmia joita käyttäjät käyttävät.

Mitä tietokannalta halutaan?

Halutaan...

• Säilöä tietoa turvallisesti.
• Muuttaa tietoja helposti.
• Hakea tietoa tietyin ehdoin.
• Tehdä yhteenvetoja ja laskutoimituksia.
• Jakaa tietoa.(vertaa esim. Google)

Esimerkki: Laaja osoitekirja

Ratkaisu 1: Paperi ja kynä.

• Halpa ja yksinkertainen ratkaisu
• Hankala etsiä, päivittää, jakaa ja laajentaa
  • Kenen puhelinnumero olikaan 123456?
  • Miten korjata osoite jos edellinen on kirjoitettu tussilla? Entäs jos tila ei riitä?
• Hankalaa lisätä uutta informaatiota esim. sähköpostiosoitteita
• Turhaa tiedon toistoa
  • Kalle Kuoma, opiskelijakatu 13 b 2, 40100 Jyväskylä
  • Olli Opiskelija, opiskelijakatu 13 b 2, 40100 Jyväskylä
• Korvataan toinen osoite viitteellä. Entä jos toinen muuttaakin muualle? Muistetaanko päivittää myös toiselle uusi osoite? Mitä jos osoitekirjassa oleva iso kommuuni hajoaa?
  • Kalle Kuoma, opiskelijakatu 13 b 2, 40100 Jyväskylä
  • Olli Opiskelija, kts. Kallen osoite
• Varmuuskopiointi?

Ratkaisu 2: Mappi välilehdillä.

• Voidaan helposti pitää järjestyksessä esim. nimen mukaan
• Voidaan helpommin lisätä, poistaa ja päivittää
• Edelleen samat ongelmat kuin ensimmäisessä vaihtoehdossa. Hakeminenkaan ei onnistu helposti kuin nimen mukaan.

Ratkaisu 3: Tekstitiedosto tai dokumentti.

Suppea osoitekirja Word-dokumenttina

• Vapaasti muotoiltavissa
• Rajoittamaton koko
• Helposti kopioitavissa
• Jaettavissa
• Tekstihaku
• Edelleen ongelmana tiedon toistuminen ja eheys.
• Entä jos vaatimukset kasvavat? Tiedosto voi kasvaa hyvin suureksi.
  • Hakeminen hidastuu
  • Haku: "Opiskelija" antaakin tulokseksi kaikki joiden osoitteessa lukee "opiskelijankatu" eikä pelkästään "Olli Opiskelija"

Ratkaisu 4: Tavallinen taulukko

Suppea osoitekirja Excel-taulukkona

• Jaetaan tieto kenttiin esim. nimi, osoite ja puhelinnumero.
• Nyt haku on helpompaa kun voimme hakea nimen tai osoitteen perusteella. Tämä vaatii ohjelmalta jo jonkinlaista hakulogiikkaa! Valitaan sarake ja etsitään. Miten haetaan useamman hakuehdon perusteella?
• Miten saadaan järjestykseen?
• Miten saadaan yhteenvetoja? Esim. kuinka monta kaveria asuu Jyväskylässä.
• Edelleen tieto toistuu. Päivittäminen työlästä.

Ratkaisu 5: Edistynyttä taulukkolaskentaa.

• Käytetään hakuihin Excelissä erikoissuodatusta.
• Järjestäminen onnistuu sopivilla työkaluilla.(esim. Sort tai Pivot)
• Kaavioilla ja ristiintaulukoinneilla saadaan yhteenvetoja. Funktioilla voidaan tehdä laskutoimituksia.
• Tietojen rajaukset (onnistuu osittain taulukkolaskennassakin).
  • Miten voidaan estää esim. tyhjät solut jos tieto on välttämätön?
  • Miten rajataan tiettyyn numeroväliin tai listaan vaihtoehdoista?
  • Miten estetään samaa henkilöä esiintymästä kahteen kertaan?
• Tietojen päivittämisen ongelma.
  • Miten yhdistetään tiedot?
  • Entä jos yhdellä henkilöllä voi olla monta osoitetta?
• Monen käyttäjän ongelmat.
  • Mitä jos haluamme, että monta käyttäjää voi katsella tietoja ja päivittää niitä?
  • Entä jos halutaan rajata tiedon käyttö- ja päivitysoikeuksia eri käyttäjille? (vertaa esim. Google)
• Entä jos haluammekin lisätä kokonaan uudenlaisia tietoja? Säilyykö rakenteen eheys?
• Laajan tietomäärän aiheuttamat ongelmat. Haku hidastuu.

Ratkaisu 6: Tietokanta

Yleisiä vaatimuksia tietokannalta:

• Kukin tieto tallennetaan tietokannassa vain yhteen paikkaan eli ei esiinny turhaa toistuvuutta.
• Tietoja pystytään hakemaan joustavasti erilaisin perustein, myös sellaisin, joita ei tietokantaa suunnitellessa ole pystytty ennakoimaan.
• Tietokannan rakenteellinen muuttaminen on joustavaa.
• Hyväksikäyttö ja sovellusohjelmat ovat riippumattomia tietojen fyysisestä talletusrakenteesta: tietoriippumattomuus.

Taulukoista relaatiotietokantoihin

Toiston välttäminen

Osoitteet ja nimet on erotettava toisistaan.

• Isoja perheitä, jotka muuttavat usein. Jonkun osoite saattaa unohtua päivittää.
  • Olli Opiskelija, Opiskelijaboksintie 13 b 2, 40500 Jyväskylä
  • Oili Opiskelija, Opiskelijaboksintie 13 b 2, 40500 Jyväskylä
  • Onni Opiskelija, Opiskelijaboksintie 13 b 2, 40500 Jyväskylä
  • Väinö Opiskelija, Opiskelijaboksintie 13 b 2, 40500 Jyväskylä
• Säästetään tilaa jos voidaan tehdä päivitys vain yhteen paikkaan. Päivittäminen helpottuu.
• Ei enää ongelmia joulukortteja lähetettäessä: yksi kortti jokaiseen osoitteeseen.

Ratkaisu

• Kaksi taulukkoa: yksi henkilöille ja yksi osoitteille

  

• Kuinka yhdistetään henkilö ja osoite?

  

  Myös postinumero ja postitoimipaikka voitaisiin laittaa eri taulukoihin.

Käsitteitä

Useimmat tietokannat ovat relaatiotietokantoja, joissa tieto tallennetaan tauluihin. Käsitteitä:

Tietue (engl. record)

Taulun yksi rivi. Sisältää useita kenttiä.

Kenttä (engl. field)

Tallennettava tieto. Pienin yksittäinen tiedon osanen. Kenttien nimiä voidaan ajatella taulukon sarakeotsikkoina.

Taulu (engl. table)

Yhteenkuuluvat tietueet (joissa yhtä monta samanlaista kenttää) muodostavat yhden taulun.

Tietokannan on kuitenkin huomioitava vielä monia asioita. Mitä jos haluamme pitää kirjaa henkilöistä, jotka asuvat useammassa osoitteessa (esim. varsinainen ja tilapäinen)? Miten erotamme kaksi samannimistä henkilöä?

Viitteet ja suhteet

Kukin tietue voi liittyä 0-n muuhun tietueeseen. Tietokannan rakenteen takia on järkevä määrätä millaisia nämä suhteet ovat, liittyykö joku tietue toiseen ollenkaan, liittyykö se aina yhteen vai voiko tietue liittyä useampaankin. Seuraavassa muutama esimerkkitapaus:

• Henkilöllä voi olla useita puhelinnumeroita. Tietyn puhelinnumeron omistaa kuitenkin vain yksi henkilö. (Yhden suhde moneen, 1-to-M)
• Lähiosoite kuuluu yhteen postiosoitteeseen. Postiosoitteessa voi olla monta lähiosoitetta. (Monen suhde yhteen, M-to-1)
• Henkilö voi omistaa useita asuntoja. Asunnon omistus voi olla jaettu usean henkilön kesken. (Monen suhde moneen, M-to-M)
• Yksi henkilö voi olla aviossa vain yhden henkilön kanssa (Yhden suhde yhteen, 1-to-1)

Suhde riippuu siitä millaista tietoa haluamme tallentaa! Esim. jossain tietokannassa voi olla tarpeen, että yhteen henkilöön liittyy monta asuntoa (halutaan pitää kirjaa omistuksista), mutta voi olla yhteen henkilöön riittää liittää yksi asunto (halutaan pitää kirjaa vain pääasiallisesta asuinpaikasta).

Ratkaisu

• Henkilöllä voi olla useita puhelinnumeroita (Yhden suhde moneen)

  

  Henkilo

  Hetu	Etunimi	Sukunimi
  000000-0000	Olli	Opiskelija
  000001-0001	Muuli	Hevostenhoitaja
  000002-0002	Reimo	Alaselkä
  000003-0003	Masi	Muurari
  000004-0004	Taavi	Ruudintekijä
  055555-0555	Oili	Opiskelija
  060606-0606	Väinö	Opiskelija

  Puhelinnumero

  Suuntanro	Numero	Hetu
  014	123456	000000-0000
  044	1234567	000000-0000
  0400	436637	000001-0001
  050	532235	000002-0002
  041	324211	000003-0003
  045	112234	000004-0004
  0500	145155	055555-0555
  +358 400	112234	060606-0606
  050	3215351	000000-0000

• Lähiosoite kuuluu yhteen postiosoitteeseen. Postiosoitteessa voi olla monta lähiosoitetta. (Monen suhde yhteen)

  

  Lahiosoite

  LahiosoiteID	Paikannimi	Numeroosa	Postinumero
  1	Opiskelijaboksintie	13 b 2	40500
  2	Pohjoisenkatu	245	99999
  3	Työntekijänkuja	6 a 6	00000
  4	Kommuunikatu	134 h 8	00000

  Postiosoite

  Postinumero	Postitoimipaikka
  40500	Jyväskylä
  99999	Nullula
  00000	Hesa

• Uusi taulu, joka yhdistää omistajan ja asunnon (Monen suhde moneen). Henkilö voi omistaa useita asuntoja ja asunnolla voi olla useita omistajia

  

  Henkilo

  Hetu	Etunimi	Sukunimi
  000000-0000	Olli	Opiskelija
  000001-0001	Muuli	Hevostenhoitaja
  000002-0002	Reimo	Alaselkä
  000003-0003	Masi	Muurari
  000004-0004	Taavi	Ruudintekijä
  055555-0555	Oili	Opiskelija
  060606-0606	Väinö	Opiskelija

  Lahiosoite

  LahiosoiteID	Paikannimi	Numeroosa	Postinumero
  Opiskelijaboksintie	1	13 b 2	40500
  Pohjoisenkatu	2	245	99999
  Työntekijänkuja	3	6 a 6	00000
  Kommuunikatu	4	134 h 8	00000

  Asuu

  Hetu	LahiosoiteID
  000000-0000	1
  000001-0001	3
  000002-0002	2
  000003-0003	3
  000000-0000	4
  000004-0004	3
  055555-0555	1
  060606-0606	1

Tietokantojen hyödyt

Tietokannan hallintajärjestelmä vastaa edellä esitettyihin haasteisiin ja välttää luetellut ongelmat. Useimmat tietokantojen hallintajärjestelmät mahdollistavat:

• Tietokantojen luomisen ja muuttamisen.
• Tietojen syöttämisen ja muuttamisen.
• Tiedonhaun ja yhteenvetojen tekemisen SQL -kyselykielellä.
• Käyttäjähallinnan.
  • Näkymät. Tietojen yhdistely.
  • Oikeustasot
• Monet käyttäjät. Toimintokokonaisuuksien hallinta. Tiedon varmistus.
  • Useat voivat päivittää tietokannan tietoja.
  • Päivitys tapahtuu aina kerralla.
  • Tapahtumat eivät jakaudu osiin ja tapahtuvat peräkkäin tietyssä järjestyksessä.
• Tiedon eheys. Tarpeellinen tieto ei katoa. Viitteet eivät mene rikki.
• Tietojen yksilöinti. Tietotyypit. Tietojen rajoitukset. Pakollisuus.

Koko tietokantajärjestelmää tehdessä on suunniteltava miten tieto organisoidaan ja miten sitä voidaan käsitellä.

Tietokannan suunnittelun vaiheet

• Vaatimusten määrittely
  • Haastatteluin, kirjalliseen materiaaliin tutustumalla ja keskusteluin selvitetään järjestelmältä vaadittavat ominaisuudet. Apuna käytetään usein käyttötapaus-tekniikkaa.
• Käsitteellinen mallintaminen
  • Laaditaan kaavio, joka kuvaa tietokannan sisällön ja rakenteen.
• Käyttöliittymän suunnittelu
  • Suunnitellaan sovelluksen rakenne ja sovellukseen kuuluvien näyttöjen sisältö ja rakenne
• Tietokannan looginen suunnittelu
  • Kaavion pohjalta laaditaan varsinainen tietokannan rakenne
• Toteutus
  • Toteutetaan edellisten osien suunnitelmat

Käsitteellinen mallintaminen (ER-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, sotu, 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.
• 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 suhden 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.

Esimerkkejä

Halutaan pitää kirjaa muutamien henkilöiden lainauksista. Lainaukset voivat olla esim. kirjoja, polkupyöriä, cd-levyjä, videoita...

• Esimerkki 1 Erkka-tiedostona
• Kaikkia erityyppisiä kohteita ei kannata liittää yhteen Lainaa-suhteeseen, koska tällä tarkoitetaan, että henkilö liittyy toiseen kohteeseen, mutta myös samalla lainaushetkellä kolmanteen kohteeseen jne.
• Kirja, Polkupyörä, Levy jne. on yleistetty ensin Tavaraksi, jolloin esimerkiksi tavaralainojen määrä on helpompi laskea. Tavara-kohteeseen tulee tavaroiden yhteiset ominaisuudet.
• Nyt saamme tavaroiden erilaiset ominaisuudet huomioon, mutta entä jos haluaisimme jälkikäteen lisätä uudentyyppisen tavaran?

• Esimerkki 2 Erkka-tiedostona
• Nyt eri tyyppejä on helppo lisätä.
• Entä eri tyyppisiin tavaroihin liittyvät ominaisuudet? Lisää luennolla 6.