Luento 3 - Tietokannan suunnittelu ER-mallinnuksella

Tietokannan suunnittelun vaiheet:

• Vaatimusten määrittely ja analyysi
  • haastatteluin, kirjalliseen materiaaliin tutustumalla ja keskusteluin selvitetään järjestelmältä vaadittavat ominaisuudet
• Käsitteellinen mallintaminen
  • laaditaan käsitekaava, joka kuvaa tietokannan sisällön ja rakenteen
• Looginen suunnittelu
  • käsitekaavan pohjalta laaditaan sisällöstä ja rakenteesta relaatiokaava käytettävissä olevalla DDL:llä (Data Definition Language)
• Fyysinen suunnittelu
  • päätetään tietokannan tallennusrakenteista ja saantimenetelmistä
• Toimintoanalyysi
  • kuvaus toiminnoista, jotka käsittelevät suunniteltavan tietokannan tietoja.
• Ohjelmasuunnittelu
  • hahmotellaan sovellusohjelmien rajat, rakenne ja logiikka
• Toteutus
  • kuvaukset koodataan DML:llä (Data Definition Language) ja isäntäkielellä, jonka jälkeen niiden toimivuus testataan.

Käsitteellinen mallintaminen (ER-malli)

• Kohde (entity)
  • "a thing that can be distinctly identied"
  • tunnistettavissa oleva asia tai tapahtuma
  • heikko tyyppi (weak entity)
    • olemassaolo riippuu toisesta kohteesta eli ei voi olla olemassa jos tätä toista kohdetta ei ole myös olemassa.
      • esim. tenttitulosta ei voi olla olemassa ilman tenttijää
• ominaisuudet (properties, attributes)
  • jokaisella samantyyppisellä kohteella on tiettyjä yhteisiä ominaisuuksia
    • opiskelijoilla on kaikilla nimi, sotu, osoite yms
  • Jokainen ominaisuus saa arvonsa (value) tietystä arvojoukosta (domain)
  • ominaisuudet voivat olla koottuja useasta osasta tai yksittäisiä.
    • Nimi voi koostua etu- ja sukunimestä.
  • Avaimia
    • uniikkeja ainakin jossain yhteydessä
  • yksi- tai moniarvoisia
  • tyhjä arvo
  • tuntematon arvo
  • johdettu
    • esim. tilausten kokonaislukumäärä lasketaan yksittäistilausten kappalemääristä.
• suhde (relationship)
  • vähintään kahden kohteen välillä vallitseva riippuvuus
  • suhteen aste määräytyy suhteeseen liittyvien kohteiden lukumäärän mukaan.
  • Jos jokaista suhteeseen liittyvää kohdetta A vastaa vähintään yksi kohde B on kyseessä täysi suhde muussa tapauksessa osittainen suhde
  • suhde voi olla:
    • yhden suhde yhteen (one-to-one)
    • yhden suhde moneen (one-to-many) (monen suhde yhteen (many-to-one))
    • monen suhden moneen (many-to-many)
    • kardinaalisuus (cardinality)
• alityypit
• perintä
  • Jokainen kohde on vähintään yhtä kohdetyyppiä mutta voi olla samaan aikaan useampaakin
  • ohjelmoija on on työntekijä eli ohjelmoija on alityyppi työntekijän ollessä ylityyppi
  • ohjelmoijalla on kaikki työntekijän ominaisuudet mutta ei päinvastoin
  • tyyppihierarkia

ER-diagrammit

• Kohteet
  • Jokainen kohde esitetään suorakulmiona jonka sisällä lukee kyseisen kohteen kohdetyyppi.
  • heikoilla kohdetyypeillä suorakulmion kehä kaksinkertaistetaan
• ominaisuudet
  • esitetään ellipseillä jotka on liitetty jatkuvalla viivalla kohteeseen tai suhteeseen. Ellipsin sisällä lukee ominaisuuden nimi.
  • perityt ominaisuudet liitetään katkoviivalla
  • moniarvoiset ominaisuudet liitetään kaksinkertaisella viivalla
  • koottujen ominaisuuksien osaset esitetään kukin omana ellipsinään jotka liitetään jatkuvilla viivoilla koostettuun ominaisuuteen.
  • Avaimina toimivat ominaisuudet alleviivataan.
• suhteet
  • Jokainen suhde esitetään timanttikuviona jonka sisällä lukee suhteen nimi
  • Jos suhde on heikon kohteen ja "vahvan" kohteen välillä niin timanttikuvion kehä kaksinkertaistetaan.
  • 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.
  • suhteen ja kohteen välinen viiva kaksinkertaistetaan jos kyseessä täysi suhde
• alityypit
  • alityyppi yhdistetään ylityyppiin yhtenäisellä viivalla jonka toisessa päässä on nuoli (ylityypin päässä).

ER-mallin muuttaminen tietokannaksi

• Jokainen tavallinen kohde muutetaan relaatioksi (tauluksi)
  • Jokaiselle määritellään perusavain diagrammissa määriteltyjen avainten mukaan
• Many-to-Many -suhteet
  • Jokainen Many-to-Many -suhde muutetaan tavalliseksi relaatioksi.
  • Määritellään relaatioon yhtä monta viiteavainta (foreign key) kuin on suhteeseen liittyviä kohteita.
    • Määritellään viite-eheys jokaiseen kohteeseen seuraavin säännöin:
      • DELETE RESTRICTED
        • Viite-eheys ei siis salli poistamista
      • UPDATE CASCADES
        • päivitykset sallitaan
    • Perusavaimen määritykseen kaksi mahdollisuutta:
      • Muodostetaan yhdistetty avain viiteavaimista
        • Viiteavaimien yhdistelmän pitää olla ainutkertainen joka tietueelle (näin yleensä onkin)
        • Tyhjiä arvoja ei sallita
      • Luodaan uusi kenttä perusavaimeksi
• Many-to-One
  • Tavalliset kohdetyypit
    • Ei luoda uusia relaatioita mutta lisätään ennestään olevaan relaatioon uusi viiteavain
      • Viite-eheys määrittelyt seuraavin säännöin:
        • DELETE RESTRICTED
        • UPDATE CASCADES
  • Heikot kohdetyypit
    • Ei luoda uusia relaatioita mutta lisätään ennestään olevaan relaatioon uusi viiteavain
      • Viite-eheys määrittelyt seuraavin säännöin:
        • DELETE CASCADES
        • UPDATE CASCADES
    • Perusavaimen määrittely:
      • Yhdistetty avain viiteavaimesta ja ER-diagrammissa määritellystä avaimesta
      • Uusi kenttä perusavaimeksi
• One-to-One
  • Käytännössä harvinaisia
  • Käsitellään kuten Many-to-One -tapaukset
• Ominaisuudet
  • Jokainen ER-diagrammista löytyvä ominaisuus lisätään siihen liittyvään relaatioon paitsi:
    • Moniarvoisista ominaisuuksista luodaan uusi relaatio
• alityypit
  • jokaisesta luodaan uusi relaatio
  • jokaiseen alityyppiin lisätään siihen liittyvät ominaisuudet
  • perusavain sama kuin päätyypissä
    • samalla myös viiteavain
      • viite-eheys määrittelyt seuraavasti:
        • DELETE CASCADES
        • UPDATE CASCADES