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 perusavaimeen.

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.

Next, let's transform all special attributes in entity sets. Special attributes are, among others, multivalued, composite, derived, and all others for which their own transformation rules apply.

In the student database, it is noticed that the entity set STUDENT has two special attributes: name and phone_numbers. Transformation rules 4 and 5 apply to these:

Note that in the example above, a relation F2 was created for the multivalued attribute and the key attribute f1 of relation F was added to it. Relation F2 attribute f1 is thus a foreign key to relation F. In other words, a foreign key, as its name suggests, refers to an attribute in another relation (see Chapter 3.1).

Foreign keys can be marked in a relation schema in the following way:

F2.f1 -> F.f1

Meaning: Relation F2 attribute f1 is a foreign key referring to relation F attribute f1.

Let's transform attributes according to the rules into the box below:

1. Move the cursor to a new line and add a new relation.
2. Give the relation the name STUDENT_PHONE_NUMBERS. This relation corresponds to the multivalued attribute phone_numbers of the entity STUDENT.
3. Add the primary key id of relation STUDENT as attributes to relation STUDENT_PHONE_NUMBERS. Set its name to student_id for clarity.
4. Add a new attribute phone_number to the relation, which contains a single phone number.
5. Mark student_id and phone_number as the primary key.
6. Move the cursor to a new line and add a new foreign key by pressing "New foreign key".
   • Set the foreign key to be the attribute student_id of relation STUDENT_PHONE_NUMBERS and define it to refer to attribute id of relation STUDENT.
7. Apply rule 4 to attribute name.

You can float the diagram editor with the Float button. Then the text moves to the right edge, and space is freed up for the editor on the right.

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 F2 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 sen nimeksi selkeyden vuoksi 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.

Finally, let's transform relationship sets. In the relational model, transforming relationships depends only on the maximum cardinality of the relationship. Generally, minimum cardinality is not marked in relations in any way, but the handling of minimum cardinalities is usually universally the responsibility of either the application/person using the database or the database management system.

Let's examine from the ER diagram what kind of cardinalities are in the student database:

• One-to-many (1:N) relationships are those where the maximum cardinality of one entity participating in the relationship is 1 and the other entity's maximum cardinality is N.
  • Student has a 1:N relationship to Department (student Studies in at most one department, and a department can have multiple students)
  • Department has a 1:N relationship to Faculty (department Belongs to at most one faculty, and a faculty can have multiple departments)
  • Course has a 1:N relationship to Department (course is Organized by at most one department, and a department can have multiple courses)
• Many-to-many (N:M) relationships are those where the maximum cardinality of both entities participating in the relationship is N.
  • Student has an N:M relationship to Course (student Takes multiple exams for courses, a course can have many students taking exams)
• One-to-one (1:1) relationships are relatively rare, and there are none in this student database

Before you continue, go through the student database ER diagram once more and ensure that you understand the classification above.

Since the student database has only 1:N and N:M relationships, only the following two transformation rules are needed:

Let's transform relationships into the submission box below:

N:M relationships:

Let's first examine the only N:M relationship TakesExam, whose transformation is straightforward:

1. Move the cursor to an empty line and add a new relation with the "New relation" button.
2. Set the relation name to TAKES_EXAM. This relation will become a relationship relation.
3. Add the attributes belonging to the primary key of relations STUDENT and COURSE as attributes of relation TAKES_EXAM:
   • student_id (corresponds to primary key id of relation STUDENT)
   • course_code (corresponds to primary key course_code of relation COURSE)
4. Add the attributes of relationship TakesExam to the attributes of relation TAKES_EXAM
   • date
   • grade
5. Mark the combination of primary keys taken from relations STUDENT and COURSE as well as attribute date as the primary key.
   • Paint attributes student_id, course_code and date and press the "Mark as primary key" button.
   • Note: Here date is exceptionally also taken into the primary key, as in the ER diagram it is marked as a key attribute of the relationship. This choice was justified in Exercise 2.1.
6. Finally, mark student_id and course_code as foreign keys
   • Move cursor to a new line and press "New foreign key"
   • Set the foreign key to attribute TAKES_EXAM.student_id
   • Set the referenced attribute to STUDENT.id
   • Repeat the same for attribute course_code

1:N relationships:

Let's examine the relationship Studies:

1. Move the cursor to the STUDENT relation side (since in the ER diagram entity STUDENT has the "fork" i.e. N-side in the Studies relationship).
2. Add attribute dept_id to relation STUDENT, corresponding to the id key attribute of DEPARTMENT relation.
3. Mark dept_id also as a foreign key pointing to attribute id of relation DEPARTMENT
   • Move cursor to a new line and press "New foreign key"
   • Set foreign key as STUDENT.dept_id
   • Set referenced attribute as DEPARTMENT.id

Transform the rest of the relationship sets yourself using the instructions above.

You can float the diagram editor with the Float button. Then the text moves to the right edge, and space is freed up for the editor on the right.

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, joissa 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, joissa kummankin 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:M-suhteita, tarvitaan vain seuraavat kaksi transformointisääntöä:

Transformoidaan suhteet alla olevaan palautuslaatikkoon:

N:M-suhteet:

Tarkastellaan ensin ainoaa N:M-suhdetta 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-puoli Opiskelee-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.