Harjoitus: ER-kaavion transformointi

Katsotaan kertaukseksi vielä opiskelijatietokannan ER-kaavio:

Aloitetaan transformoimalla kohdetyypit ja niiden perusattribuutteja. Perusattribuuteilla tarkoitetaan tässä kaikkia niitä attribuutteja, jotka eivät ole koottuja, moniarvoisia tai johdettuja.

Sovelletaan tähän ensimmäinen transformointisääntö:

Relaatiotietokannalle on olemassa erilaisia merkintätapoja: relaatiot voidaan kuvata taulukkona tai vaikkapa UML-kaavioiden luokkina. Tällä kurssilla käytämme samaa merkintätapaa, joka on esitetty Luvussa 3.1:

RELAATION_NIMI(attribuutti1, attribuutti2, attribuutti3)

Perusavaimet merkitään näissä tehtävissä poikkeuksellisesti merkitsemällä avainattribuutit {| |}-sulkujen sisään. Esimerkiksi seuraavassa kaaviossa

RELAATION_NIMI({|attribuutti1, attribuutti2|}, attribuutti3)

attribuutit attribuutti1 sekä attribuutti2 kuuluvat relaation perusavaimen.

Lisätään siis kaikki kohdetyypit ja niiden perusattribuutit vastauslaatikkoon. Tässä vaiheessa ei vielä kannata transformoida erikoisattribuutteja, kuten koottuja, johdettua tai moniarvoisia. Määritä ensin relaatiot ja aseta niihin kaikki yksinkertaiset attribuutit:

1. Siirrä kursori tyhjälle riville
2. Paina "Uusi relaatio" -painiketta, joka lisää pohjan uudelle relaatiolle.
3. Aseta relaation nimeksi OPISKELIJA ja lisää relaatiolle opiskelijan tavalliset attribuutit (eli kaikki attribuutit, jotka eivät ole koottuja, moniarvoisia tai johdettuja):
   • id
   • sähköpostiosoite
   • osoite
   • aloitusvuosi
4. Merkitse avainattribuutti id perusavaimeksi
   • Maalaa attribuutti id
   • Paina "Merkitse perusavaimeksi" -painiketta
5. Lisää uusi tyhjä rivi ja toista vaiheet 1-4 kaikille muille kohdetyypeille

Voit kelluttaa kaavioeditorin Kelluta-painikkeella. Silloin teksti siirtyy oikeaan laitaan, ja editorille vapautuu tilaa oikealle.

Transformoidaan seuraavaksi kaikki kohdetyypeissä olevat erikoisattribuutit. Erikoisattribuutit ovat muun muassa moniarvoiset, kootut, johdetut ja kaikki muut sellaiset, joille pätevät omat transformointisäännöt.

Opiskelijatietokannassa huomataan, että kohdetyypillä OPISKELIJA on kaksi erikoisattribuuttia: nimi sekä puhelinnumerot. Näihin sopivat transformointisäännöt 4 ja 5:

Huomaa, että yllä olevassa esimerkissä moniarvoiselle attribuutille luotiin relaatio F2 ja siihen lisättiin relaation F avainattribuutti f1. Relaation F2 attribuutti f1 on siis viiteavain relaatioon F. Toisin sanoin, viiteavain nimensä mukaisesti viittaa johonkin toisessa relaatiossa olevaan attribuuttiin (ks. Luku 3.1).

Relaatiokaavassa viiteavaimet voidaan merkitä seuraavalla tavalla:

F2.f1 -> F.f1

Eli: Relaation F1 attribuutti f1 on viiteavain, joka viittaa relaation F attribuuttiin f1.

Transformoidaan attribuutit sääntöjen mukaan alla olevaan laatikkoon:

1. Siirrä kursori uudelle riville ja lisää uusi relaatio.
2. Anna relaation nimeksi OPISKELIJA_PUHELINNUMEROT. Tämä relaatio vastaa kohteen OPISKELIJA moniarvoista attribuuttia puhelinnumerot.
3. Lisää relaatiolle OPISKELIJA_PUHELINNUMEROT attribuuteiksi relaation OPISKELIJA perusavain id. Aseta selkeyden vuoksi sen nimeksi opiskelija_id.
4. Lisää relaatiolle vielä uusi attribuutti puhelinnumero, joka sisältää yksittäisen puhelinnumeron.
5. Merkitse opiskelija_id sekä puhelinnumero perusavaimeksi.
6. Siirrä kursori uudelle riville ja lisää vielä uusi viiteavain painamalla "Uusi viiteavain".
   • Aseta viiteavaimeksi relaation OPISKELIJA_PUHELINNUMEROT attribuutti opiskelija_id ja määritä se viittaamaan relaation Opiskelija attribuuttiin id.
7. Sovella vielä sääntöä 4 attribuutille nimi.

Voit kelluttaa kaavioeditorin Kelluta-painikkeella. Silloin teksti siirtyy oikeaan laitaan, ja editorille vapautuu tilaa oikealle.

Transformoidaan lopuksi vielä suhdetyypit. Relaatiomallissa suhteiden transformointi riippuu vain suhteen maksimikardinaliteettista. Yleisesti minimikardinaliteettia ei merkitä relaatioihin millään tavalla, vaan minimikardinaliteettien käsittelystä vastaa yleensä joko tietokantaa käyttävä sovellus/henkilö tai tietokannanhallintajärjestelmä.

Tarkastellaan ER-kaaviosta, millaisia kardinaliteetteja on opiskelijatietokannassa:

• yhdestä moneen (1-N) -suhteet ovat ne, jossa yhden suhteeseen osallistuvan kohteen maksimikardinaalisuus on 1 ja toisen kohteen maksimikardinaalisuus on N
  • Opiskelijalla on 1-N -suhde Laitokseen (opiskelija Opiskelee korkeintaan yhdessä laitoksessa, ja laitoksessa voi olla useita opiskelijoita)
  • Laitoksella on 1-N -suhde Tiedekuntaan (laitos Kuuluu korkeintaan yhteen tiedekuntaan, ja tiedekunnalla voi olla useita laitoksia)
  • Kurssilla on 1-N -suhde Laitokseen (kurssia Järjestää korkeintaan yksi laitos, ja laitoksella voi olla useita kursseja)
• monesta moneen (N-M) -suhteet ovat ne, jossa kumpaankin suhteeseen osallistuvan kohteen maksimikardinaalisuus on N
  • Opiskelijalla on N-M -suhde Kurssiin (opiskelija Tenttii useita kursseja, kurssilla voi olla montaa opiskelijaa tentittävänä)
• yhdestä yhteen (1-1) -suhteet ovat suhteellisen harvinaisia, eikä niitä ole tässä opiskelijatietokannassa

Ennen kuin jatkat, käy läpi vielä opiskelijatietokannan ER-kaavio läpi ja varmista, että ymmärrät yllä olevan luokittelun.

Koska opiskelijatietokannassa on vain 1-N ja N-N -suhteita, tarvitaan vain seuraavat kaksi transformointisääntöä:

Transformoidaan suhteet alla olevaan palautuslaatikkoon:

N-M -suhteet:

Tarkastellaan ensin ainoa N-M -suhde Tenttii, jonka transformointi on suoraviivaista:

1. Siirrä kursori tyhjälle riville ja lisää uusi relaatio "Uusi relaatio"-painikkeella.
2. Aseta relaation nimeksi TENTTII. Tästä relaatiosta tulee suhderelaatio.
3. Lisää relaation TENTTII attribuuteiksi relaatioiden OPISKELIJA ja KURSSI perusavaimeen kuuluvat attribuutit:
   • opiskelija_id (vastaa relaation OPISKELIJA perusavainta id)
   • kurssi_kurssikoodi (vastaa relaation KURSSI perusavainta kurssikoodi)
4. Lisää relaation TENTTII attribuutteihin vielä suhteen Tenttii attribuutit
   • päivämäärä
   • arvosana
5. Merkitse relaatioista OPISKELIJA ja KURSSI otettujen perusavainten sekä attribuutin päivämäärä yhdistelmä perusavaimeksi.
   • Maalaa attribuutit opiskelija_id, kurssi_kurssikoodi ja päivämäärä ja paina "Merkitse perusavaimeksi" -painiketta.
   • Huom: Tässä päivämäärä otetaan poikkeuksellisesti myös perusavaimeen, sillä ER-kaaviossa se on merkitty suhteen avainattribuutiksi. Tätä valintaa perusteltiin Harjoituksessa 2.1.
6. Merkitse lopuksi opiskelija_id ja kurssi_kurssikoodi viiteavaimeksi
   • Siirrä kursori uudelle riville ja paina "Uusi viiteavain"
   • Aseta viiteavaimeksi attribuutti TENTTII.opiskelija_id
   • Aseta viitatuksi attribuutiksi OPISKELIJA.id
   • Toista sama attribuutille kurssi_kurssikoodi

1-N -suhteet:

Tarkastellaan suhdetta Opiskellee:

1. Siirrä kursori relaation OPISKELIJA puolelle (sillä ER-kaaviossa kohteella OPISKELIJA on "haarukka" eli N-suhde Opiskellee-suhteessa).
2. Lisää relaatioon OPISKELIJA attribuutti laitos_id, joka vastaa LAITOS-relaation id-avainattribuuttia.
3. Merkitse laitos_id vielä viiteavaimeksi, joka osoittaa relaation LAITOS attribuuttiin id
   • Siirrä kursori uudelle riville ja paina "Uusi viiteavain"
   • Aseta viiteavaimeksi OPISKELIJA.laitos_id
   • Aseta viitatuksi attribuutiksi LAITOS.id

Transformoi loput suhdetyypit itse käyttäen yllä olevia ohjeita.

Voit kelluttaa kaavioeditorin Kelluta-painikkeella. Silloin teksti siirtyy oikeaan laitaan, ja editorille vapautuu tilaa oikealle.