As stated in Part 2, a conceptual model describes only concepts and their relationships essential to the database. A conceptual model is useful for mapping the information needs of the domain without yet thinking more closely about the final implementation of the database.

Unfortunately, a conceptual model (e.g. ER diagram) itself does not yet tell in which form data should actually be stored so that all concepts, attributes, relationships, and their cardinalities of the conceptual model could be described correctly. In the previous Chapter 3.1, we learned about the relational model, which is a logical level data model: it defines in what form data can be stored in the database and in what ways it could be processed.

However, there is no direct 1:1 correspondence between the conceptual model and the relational model. In the relational model, there are only relations, primary keys, and foreign keys, while the ER model has entity sets, relationship sets, abstraction structures, and other special structures. However, a conceptual model can be converted into a relational database using only relations and integrity constraints; this conversion is called transformation.

Transformation of a conceptual model means converting the ER diagram introduced in Chapter 2.2 into a logical model of a database. In transformation, the ER diagram is thus converted into an actual logical structure of the database.

To transform an ER diagram into a relational model, there is a ready-made collection of rules, following which a very largely ready relational database structure as well as suitable primary keys and foreign keys can be derived from the ER diagram. The rules presented below are adapted from the formal transformation rules of Elmasri and Navathe [15].

A more detailed justification of the rules remains outside the topic of this material for now. Let it be mentioned, however, that the rules have been chosen so that they can produce a correct relational database with a small number of relations (tables) and attributes (columns). Part 5 gives more precise tools for ensuring the "optimality" of transformation rules.

Transforming Entity Sets

Rule 2 (Weak entity sets): a separate relation is first made for every weak entity set according to rule 1. Key attributes of the entity set as well as key attributes of the identifying entity set are chosen as the primary key of the relation.

Transforming Attributes

Rule 3 (Derived attributes): every derived attribute is discarded.

Transforming Relationship Sets

Rule 6 (1:1 relationship): for every binary 1:1 relationship set, key attributes of either entity set are placed as a foreign key in the relation of the other entity set. From the transformation point of view, it does not matter which way the foreign key is set. If the relationship has attributes, they are placed in the same relation where the foreign key is placed.

Rule 9 (-ary relationships): every -ary () relationship set is transformed according to rule 8.

Transforming Abstraction Structures

There are four ways at a general level for transforming different abstraction structures (partial or total and overlapping or disjoint).

Way 1.: a separate relation is formed for every sub- and super-entity set. Key attributes of the super-entity set as well as sub-entity set's own key attributes are added to the primary key of sub-entity sets.

Way 2.: a separate relation is formed for every sub-entity set. Both the entity sets' own attributes and the super-entity set's attributes are chosen as attributes of the relations.

Way 3.: one relation is formed, whose attributes are chosen to be all attributes of all sub- and super-entity sets plus an additional attribute (usually named type) to indicate the tuple's role in the relation.

Way 4.: one relation is formed according to way 3., but instead of an additional attribute, boolean additional attributes ("flags") are used to indicate tuple roles in the relation (e.g. is_a, is_b to describe if the tuple is of type A, B or both).

In principle, all four ways are suitable for transforming all abstraction structures. In practice, the following "rules of thumb" or heuristics can be applied in transforming abstraction structures:

Heuristic 1 (Disjoint and Total): Transform a disjoint and total abstraction structure usually with way 2, but consider also the following ways:

• Way 1: produces more relations; same tuple information is repeated in two relations; on the other hand, it is easier to retrieve tuples of all sub-entity sets from the database.
• Way 3: produces one table; easy to make queries targeting different types; there can be a lot of NULL values in the same relation.

Heuristic 2 (Disjoint and Partial): Transform a disjoint and partial abstraction structure usually with way 1. Then repetition of same tuples in super- and sub-entity set relations is avoided.

Heuristic 3 (Overlapping and Total): Transform an overlapping and total abstraction structure usually with way 4. Then tuple type is modelled with boolean values. If way 4 does not work, consider way 1.

Based on the figure above, <IsStudent, true> is set for a student tuple in the relation, <IsStaff, true> for staff. If a student is also staff, (<IsStudent, true>; <IsStaff, true>) can be set for the tuple then.

Way 1 can cause unnecessary repetition especially for staff students: if a student also belongs to staff, their information must be stored in relations PERSON, STUDENT and STAFF.

Heuristic 4 (Overlapping and Partial): Transform an overlapping and partial abstraction structure usually with way 4. Also consider way 1: every sub-entity set has its own relation, for which it is easy to define relationships. On the other hand, in way 1 same tuple information may have to be repeated in several different relations.

Based on the image above, in way 4, defining student, staff, and staff student succeeds like in heuristic 3. In addition, if a person is neither a student nor staff, (<IsStudent, false>; <IsStaff, false>) can be set for the person tuple.

Here too way 1 can cause unnecessary repetition, so its use must be considered carefully.

Applying Rules

There are numerous exceptions to the presented rules. Rules must be applied in domains with more complex ER diagrams. A general approach to transformation is: transform entity sets, transform attributes, and transform relationships. Every rule above is applied in order. If you come across a situation where several rules could apply, try the following heuristics:

• Try applying all potentially suitable rules sequentially.
• Choose one of the suitable rules and apply it. You can try applying all suitable rules separately and look at which result might be more sensible from the database data point of view (e.g., fewer relations are created, final structure is clearer).
• All relationships (except identifying ones) can be transformed with rule 8 (N:M relationships) by creating a relationship relation. Use this way if the relationship has, for example, special attributes that are difficult to transform otherwise. However, take into account that you potentially have to normalize the relation later (normalization is covered in Part 5).
• Note that in transforming abstraction structures, way 1 always works regardless of the abstraction structure type! Especially if sub-entity sets have relationships to different entities, it may be justified to transform the abstraction structure with way 1. Then every sub-entity set has its own relation, for which it is easy to transform relationship sets.

Note that these are heuristics and not exact rules! You get to try transforming special situations in Exercise 3.2.

Kuten osassa 2 on todettu, käsitteellinen malli kuvaa vain tietokannan kannalta olennaisia käsitteitä ja niiden suhteita. Käsitteellinen malli on hyödyllinen kartoittamaan kohdealueen tietotarpeita miettimättä vielä tarkemmin tietokannan lopullista toteutusta.

Valitettavasti käsitteellinen malli (esim. ER-kaavio) itsessään ei vielä kerro, missä muodossa data tulisi varsinaisesti tallentaa, jotta käsitteellisen mallin kaikki käsitteet, attribuutit, suhteet ja niiden kardinaliteetit pystyttäisiin kuvaamaan oikein. Edellisessä Luvussa 3.1 opimme relaatiomallista, joka on loogisen tason tietomalli: se määrittää, missä muodossa dataa voi tallentaa tietokantaan ja millä tavoin sitä voisi käsitellä.

Käsitteellisen mallin ja relaatiomallin välillä ei kuitenkaan ole suoraa 1:1-vastaavuutta. Relaatiomallissa on vain relaatioita, perusavaimia ja viiteavaimia, kun taas ER-mallissa on kohdetyyppejä, suhdetyyppejä, abstraktiorakenteita ja muita erikoisrakenteita. Käsitteellinen malli on kuitenkin muunnettavissa pelkästään relaatioita ja eheysrajoitteita käyttäväksi relaatiotietokannaksi; tätä muunnosta kutsutaan transformoinniksi.

Käsitteellisen mallin transformoinnilla tarkoitetaan Luvussa 2.2 esitellyn ER-kaavion muuntamista tietokannan loogiseksi malliksi. Transformoinnissa ER-kaavio siis muunnetaan varsinaiseksi tietokannan loogiseksi rakenteeksi.

ER-kaavion transformoimiseksi relaatiomalliksi on olemassa valmis kokoelma sääntöjä, joita noudattamalla ER-kaaviosta saadaan johdettua hyvin pitkälti valmis relaatiotietokannan rakenne sekä valittua sopivat perusavaimet ja viiteavaimet. Alempana esitetyt säännöt on mukailtu Elmasrin ja Navathen [15] formaaleista transformointisäännöistä.

Sääntöjen tarkempi perustelu jää toistaiseksi tämän materiaalin aiheen ulkopuolelle. Mainittakoon kuitenkin, että säännöt ovat valittu niin, että niillä saa tuotettua oikeellisen relaatiotietokannan vähäisellä määrällä relaatioita (tauluja) ja attribuutteja (sarakkeita). Osassa 5 annetaan tarkempia työkaluja transformointisääntöjen "optimaalisuuden" varmistamiseen.

Kohdetyyppien transformointi

Sääntö 2 (Heikot kohdetyypit): jokaisesta heikosta kohdetyypistä tehdään ensin oma relaatio säännön 1. mukaisesti. Relaation perusavaimeksi valitaan kohdetyypin avainattribuutit sekä tunnistavan kohdetyypin avainattribuutit.

Attribuuttien transformointi

Sääntö 3 (Johdetut attribuutit): jokainen johdettu attribuutti hylätään.

Suhdetyyppien transformointi

Sääntö 6 (1:1-suhde): jokaisesta binäärisestä 1:1-suhdetyypistä sijoitetaan jommankumman kohdetyypin avainattribuutit viiteavaimeksi toisen kohdetyypin relaatioon. Transformoinnin kannalta ei ole merkitystä, miten päin viiteavain asetetaan. Jos suhteella on attribuutteja, ne sijoitetaan samaan relaatioon, johon on sijoitettu viiteavain.

Sääntö 9 (-ääriset suhteet): jokainen -äärinen () suhdetyyppi transformoidaan säännön 8 mukaisesti.

Abstraktiorakenteiden transformointi

Erilaisten abstraktiorakenteiden (osittainen tai kattava sekä leikkaava tai erillinen) transformoinnille on yleisellä tasolla neljä tapaa.

Tapa 1.: jokaisesta ali- ja ylikohdetyypistä muodostetaan oma relaatio. Alikohdetyyppien perusavaimeen lisätään ylikohdetyypin avainattribuutit sekä alikohdetyypin omat avainattribuutit.

Tapa 2.: jokaisesta alikohdetyypistä muodostetaan oma relaatio. Relaatioiden attribuuteiksi valitaan sekä kohdetyyppien omat attribuutit että ylikohdetyypin attribuutit.

Tapa 3.: muodostetaan yksi relaatio, jonka attribuuteiksi valitaan kaikkien ali- ja ylikohdetyyppien kaikki attribuutit sekä lisäattribuutti (yleensä nimeltään tyyppi) ilmaisemaan monikon roolia relaatiossa.

Tapa 4.: muodostetaan yksi relaatio tavan 3. mukaan, mutta lisäattribuutin sijaan käytetään totuusarvoisia lisäattribuutteja ("flageja") ilmaisemaan monikon rooleja relaatiossa (esim. onko_a, onko_b kuvaamaan, onko monikko tyyppiä A, B tai kumpaakin).

Lähtökohtaisesti kaikki neljä tapaa soveltuvat kaikkien abstraktiorakenteiden transformointiin. Käytännössä abstraktiorakenteiden transformoinnissa voi soveltaa seuraavia "nyrkkisääntöjä" eli heuristiikoita:

Heuristiikka 1 (Erillinen ja kattava): Transformoi erillinen ja kattava abstraktiorakenne yleensä tavalla 2, mutta harkitse myös seuraavat tavat:

• Tapa 1: tuottaa enemmän relaatioita; saman monikon tiedot toistetaan kahdessa relaatiossa; toisaalta tietokannasta on helpompaa hakea kaikkien alikohdetyyppien monikot.
• Tapa 3: tuottaa yhden taulun; helppoa tehdä eri tyyppeihin kohdistuvat kyselyt; voi olla tosi paljon NULL-arvoja samassa relaatiossa.

Heuristiikka 2 (Erillinen ja osittainen): Transformoi erillinen ja osittainen abstraktiorakenne yleensä tavalla 1. Silloin vältytään samojen monikoiden toistolta yli- ja alikohdetyyppien relaatioissa.

Heuristiikka 3 (Leikkaava ja kattava): Transformoi leikkaava ja kattava abstraktiorakenne yleensä tavalla 4. Silloin monikon tyyppi mallinnetaan totuusarvoilla. Jos tapa 4 ei käy, harkitse tapaa 1.

Yllä olevan kuvion perusteella relaatiossa opiskelijan monikolle asetetaan <OnkoOpiskelija, true>, henkilökunnalle <OnkoHenkilökunta, true>. Jos opiskelija on myös henkilökuntaa, voidaan silloin monikolle asettaa (<OnkoOpiskelija, true>; <OnkoHenkilökunta, true>).

Tapa 1 voi aiheuttaa turhaa toistoa erityisesti henkilökuntaopiskelijoiden kannalta: jos opiskelija kuuluu myös henkilökuntaan, hänen tietonsa tulee tallentaa relaatioihin HENKILÖ, OPISKELIJA sekä HENKILÖKUNTA.

Heuristiikka 4 (Leikkaava ja osittainen): Transformoi leikkaava ja osittainen abstraktiorakenne yleensä tavalla 4. Harkitse myös tapaa 1: jokaiselle alikohdetyypille on oma relaatio, johon on helppoa määritellä suhteita. Toisaalta, tavassa 1 saman monikon tiedot voi joutua toistamaan useassa eri relaatiossa.

Yllä olevan kuvan perusteella tavassa 4 opiskelijan, henkilökunnan ja henkilökuntaopiskelijan määritys onnistuu heuristiikan 3 tapaisesti. Lisäksi jos henkilö ei ole opiskelija eikä henkilökuntaa, voidaan henkilön monikolle asettaa (<OnkoOpiskelija, false>; <OnkoHenkilökunta, false>).

Tässäkin tapa 1 voi aiheuttaa turhaa toistoa, joten sen käyttöä tulee harkita tarkasti.

Sääntöjen soveltaminen

Esitettyihin sääntöihin on olemassa lukuisia poikkeuksia. Monimutkaisemmissa ER-kaavioissa kohdealueissa sääntöjä täytyy soveltaa. Yleinen lähestymistapa transformointiin on: transformoi kohdetyypit, transformoi attribuutit ja transformoi suhteet. Jokaista yllä olevaa sääntöä sovelletaan järjestyksessä. Jos törmäät vastaan tilanteeseen, johon voisi päteä useampi sääntö, kokeile seuraavia heuristiikoita:

• Kokeile soveltaa kaikki mahdollisesti sopivat säännöt peräkkäin.
• Valitse jokin sopivista säännöistä ja sovella sitä. Voit kokeilla soveltaa kaikkia sopivia sääntöjä erikseen ja katsoa, mikä tulos voisi olla tietokannan datan kannalta järkevämpi (esim. syntyy vähemmän relaatioita, lopullinen rakenne on selkeämpi).
• Kaikki suhteet (paitsi tunnistavat) voidaan transformoida säännöllä 8 (N:M-suhteet) luomalla suhderelaatio. Käytä tätä tapaa silloin, jos suhteella on esimerkiksi erikoisattribuutteja, joita on vaikea transformoida muuten. Ota kuitenkin huomioon, että joudut mahdollisesti normalisoimaan relaatiota myöhemmin (normalisointi käsitellään osassa 5).
• Huomaa, että abstraktiorakenteiden transformoinnissa tapa 1 käy aina riippumatta abstraktiorakenteen tyypistä! Erityisesti jos alikohdetyypeillä on suhteita eri kohteisiin, voi olla perusteltua transformoida abstraktiorakenne tavalla 1. Silloin jokaisella alikohdetyypillä on oma relaatio, joille on helppoa transformoida suhdetyypit.

Huomaa, että nämä ovat heuristiikkoja eivätkä tarkkoja sääntöjä! Pääset Harjoituksessa 3.2 kokeilemaan erikoistilanteiden transformointia.