1 Johdanto
URAANI-niminen tietotekniikan cum laude -työprojekti toteutti kevään 2002 aikana Vainu-nimisen WWW-pohjaisen opiskeluneuvonta- ja urasuunnittelusovelluksen Jyväskylän yliopiston avoimelle yliopistolle. Sovelluksen taustalla oleva tietokanta toteutettiin mahdollisimman joustavaksi, jotta uusia ominaisuuksia voidaan lisätä myöhemmin ja käyttöä voidaan laajentaa Jyväskylän yliopiston muiden laitosten tarpeisiin.
Projektissa toteutetulla järjestelmällä voidaan tarjota Jyväskylän avoimen yliopiston opiskelijoille opiskeluneuvontapalveluja WWW-muodossa. Tällä tavoin toteutettuna opiskeluneuvonta tehostuu, sillä opiskelijat saavat yleisen opiskeluun tarvitsemansa tiedon verkon välityksellä. Lisäksi avoimen henkilökohtainen opiskeluneuvonta voi keskittyä vaikeampien kysymysten tarkasteluun.
URAANI-projektiryhmä vastasi WWW-sovelluksen teknisestä suunnittelusta ja toteutuksesta. Projektilaisten tehtäviin sisältyvät mm. tietokannan sekä käyttäjän ja henkilökunnan käyttöliittymien toimintojen ja ulkoasun vaatimusmäärittely, suunnittelu, toteutus ja testaus.
Tässä raportissa kuvataan sovelluksen tietokannan rakennetta ja perustellaan erinäisiä poikkeuksellisia ratkaisuja. Tietokannan osalta jossain määrin yhtäläisiä Kotka-järjestelmää ja Koppelo-sovellusta ei esitellä tarkemmin, sillä Kotkasta ja kaikista sen jatkokehitysprojekteista on erilliset hyvin kattavat dokumentit saatavilla. Muita projektin lopussa vaadittuja tehtyjä dokumentteja ovat sovellusraportti ja projektiraportti.
On huomattava, että tämä dokumentti kuvaa tietokannan rakennetta tilanteessa, jossa se oli projektin toteutusvaiheen päätyttyä. Dokumentin kirjoitusvaiheessa tietokantaan on tullut paljonkin uusia tauluja jatkokehityksen seurauksena, mutta niihin ei tässä raportissa puututa.
Luvussa 2 pyritään selventämään lukijalle syitä tietokannan rakenteen takana. Alaluku 2.1 on omistettu Vainu- ja Kotka-järjestelmien väliselle yhteydelle. Luvussa 2.2 tarkastellaan viite-eheyksiä ja tietueiden poisto-operaatioita sekä luvussa 2.3 muita huomioitavia seikkoja.
Luvun 3 tarkoitus on selventää lukijalle tietokannan rakennetta ensin sanallisesti ja sitten kuvallisesti. Lukujen 3.1, 3.2 ja 3.3 tarkoituksena on pääasiallisesti kuvata kunkin taulun ja sen kenttien käyttötarkoituksia. Taulujen väliset suhteet ilmenevät parhaiten luvun 3.4 ER-kaavioista.
Raportin liitteenä ovat tietokannan luontiin käytetyt lauseet.
2 Vainu-tietokannan ratkaisujen perusteita
Luvussa kuvataan Vainu-järjestelmän yhteyttä Jyväskylän yliopistossa käytössä olevaan Kotka-järjestelmään ja eritellään tietokantaan liittyviä toteutuksellisia ratkaisuja.
2.1 Kotka-tietokannan osuus
KOTKA-järjestelmän kehitti syksyn 2000 KOTKA-työprojekti. Se suunnitteli ja toteutti korvaavan järjestelmän Jyväskylän yliopiston tietotekniikan laitoksella käytössä olleelle KURKI-nimiselle kurssikirjanpitojärjestelmälle. KOTKA-projektiryhmä keskittyi lähinnä järjestelmän ominaisuuksien määrittelemiseen, ohjelmistojen ominaisuuksien kartoittamiseen ja vertailuun sekä tietokannan suunnitteluun. Tämän lisäksi syksyn 2000 ryhmä tuotti henkilötietojen hallintamoduulin prototyypin testaustarpeisiin ja malliksi seuraavalle ryhmälle.
Kotka-järjestelmän laajuudesta johtuen työ jaettiin alusta lähtien useamman projektiryhmän kesken. Kevään 2001 KORPPI-projektiryhmä jatkoi kehitystyötä kurssikirjanpitomoduulin kehittämisen osalta. Järjestelmään kehitti WWW-pohjaisen päivyriosion syksyn 2001 KOLIBRI-projektiryhmä. Kevään 2002 KOPPELO-projekti kehitti opinnäytteiden hallintasovelluksen osaksi KOTKA-järjestelmää.
Noin puolet Vainu-järjestelmän tietokannan tauluista on yhtäläisiä Kotka-järjestelmän kanssa. Kotka-tietokannan viimeisin tietokantaraportti löytyy WWW-osoitteesta http://kotka.it.jyu.fi/koppelo. Kotka-tietojärjestelmästä lainatut taulut on merkitty luvun 3.4 ER-kaavioihin merkinnällä <<Korppi>>. Taulut pohjautuvat Kotka- ja Korppi-työprojektin kurssikirjanpitosovelluksen tarpeisiin laatimiin ER-kaavioihin Person, Organisation ja Course_bookkeeping.
Mahdollinen Kotka- ja Vainu-tietokantojen yhdistäminen ei kuitenkaan tule onnistumaan suoralta kädeltä, sillä Kotka-järjestelmässä oli käytetty hieman hämäävästi kolmea eri nimeämiskäytäntöä monikielistystaulujen yhteydessä. Esimerkiksi CourseTranslation, CourseStatusLang ja DegreeTypeLanguage. Jossain vaiheessa käytiin sähköpostikeskustelua siitä, että Kotka-järjestelmän kaikki vastaavat taulut nimettäisiin lang-päätteisiksi. Niinpä Vainu-järjestelmän tietokanta rakennettiin tämän periaatteen mukaisesti. Täten järjestelmät eivät ole tällä hetkellä suoraan sulautettavissa.
Toinen tarkkuutta vaativa toimenpide mahdollisen Vainun ja Kotkan tietokantojen yhdistämisen tullessa ajankohtaiseksi ovat personparametertypeid:n kaltaiset kentät. Ainakin Vainu-järjestelmästä löytynee edelleen jonkin verran kovakoodausta luottaen esim. siihen, että arvo 1 vastaa sähköpostiosoitetta. Suurin osa Vainu-järjestelmässä käytetyistä tietokantavakioista on kuitenkin määrätty DBVariables.jsp-tiedostossa, jotta muutokset voidaan rajata yhteen paikkaan. Jostakin kuitenkin löytyy varmasti myös kiinteitä arvoja. Tällaiset seikat tulee ensin tarkistaa kehityspalvelimella.
Koska PostgreSql ei tiedetty tukevan autonumber-ominaisuutta, Vainu-sovelluksessa otettiin käyttöön Kotka-tietokannassa käytetty autonumber-taulu. Tässä taulussa kutakin yhden perusavaimen taulua vastaa numeroarvo, joka kertoo suurimman käytössä olevan tunnuksen. Tämän perusteella lisäys voidaan tehdä uudella perusavaimen arvolla. Jokaisen lisäyksen yhteydessä on totta kai kasvatettava kyseistä taulua vastaavaa lukua yhdellä. Itse asiassa PostgreSql:ssä on kyllä autonumberia vastaava serial-tyyppi, mutta tätä emme toteutusvaiheessa huomanneet.
2.2 Kurssit, oppiaineet, opintolinjat ja tutkintovaatimukset
Kurssit sisältävä Course-taulu on
Vainu-järjestelmään lainattu Kotka-järjestelmän tietokannasta. Sen sijaan
opintokokonaisuuksien, opintolinjojen ja oppiaineiden osalta projekti lähti
puhtaalta pöydältä. Kotkasta poikkeavaa on lisäksi se, että Vainu-tietokannassa
kursseilla ei ole erillisiä instansseja, kuten kurssikirjanpitoon keskittyvässä
Korppi-sovelluksessa. Niinpä CompletionType-taulu (esim. iltaopinnot ja verkko-opinnot) Vainussa assosioidaan
suoraan Course-tauluun CourseInstance-taulun sijasta.
Alunperin
Vainu-järjestelmässä oli erillinen taulu oppiaineille, mutta yhteensopivuussyistä
Koppelo-projektin (kevään 2002 Kotkan jatkokehitysprojekti) kanssa oppiaineet
tallennetaan nykyisin organisaatiotauluun tietyllä oppiaineen
organisaatiotyypillä. Nämä liitetään sitten asianmukaisesti oikeiden laitosten
alle.
Huomattavaa
on myös se, että vaikka alunperin otettiin mukaan opintolinjat (täsmälleen sama
taulu on muuten myös Koppelo-projektin toteuttamassa taulurakenteessa mukana),
ei niille itse asiassa ole Avoimen yliopiston kannalta käyttöä. Koska ne
kuitenkin ovat tietokannassa oppiaineiden ja opintokokonaisuuksien välissä,
käytimme näennäistietueita (nk. dummy-tietueita). Nämä ovat ikäänkuin
näkymättömiä, varsinaista dataa sisältämättömiä linkittäjiä. Tässä
nimenomaisessa tapauksessa jokaista suomenkielistä oppiainetta vastaa samanniminen
näennäisopintolinja, joka täytyy ottaa jatkokehityksessä huomioon.
Päätimme merkitä tietueen
dummyksi asettamalla sitä vastaavassa käännöstaulussa languageid=-1. Dummy-tietuetta ei nimittäin ole tarvetta kääntää
useammalle kielelle. Lisäksi koska dummy-tietueita ei tietenkään ole tarkoitus
listata käyttäjälle esim. hakutuloksessa, tämä menettelytapa suorittaa
karsimisen automaattisesti, koska haut tehdään aina tietyllä languageid:llä. Yleisestikin ottaen negatiiviset arvot on
hyvä varata poikkeustapauksille.
Vaikka Vainu-järjestelmässä ei ainakaan toistaiseksi ole tarvetta tutkintovaatimuksien esittämiseen, projektiryhmä teki kattavan tietokantaratkaisun sille saralle. Avoimen yliopiston väylään liittyvät vaatimukset voidaan esittää hieman yksinkertaistaen samalla rakenteella. Toisaalta nyt on valmis runko, jos Kotka-järjestelmään yleensä halutaan lisätä tutkintovaatimukset.
2.3 Viite-eheyksistä ja poisto-operaatioista
Tietokannassa on määritelty kaikki asianmukaiset viite-eheysavaimet (foreign key). Kaikki update-lauseet vieritetään alas (on update cascade), mutta delete-lauseita ei sallita (on delete no action), jos tuhottavaan tietueeseen on muualla viitteitä. Poiston vierittäminen koettiin liian vaaralliseksi, koska esim. language-taulusta suomen kielen poisto tuhoisi hetkessä kaiken järjestelmään suomeksi tallennetun tiedon.
Tietueen poisto (joka on toistaiseksi toteuttamatta) onkin tarkoitus tehdä samoin kuin Kotka-järjestelmässä, eli jokainen taulu sisältää boolean-tyyppisen deleted-kentän. Niinpä mitään tietoa ei varsinaisesti poisteta, vaan ainoastaan merkitään poistetuksi. Tämä tietysti vaatii sen, että jokaisessa SQL-kyselyssä jokaisen mukana olevan taulun tietueet pitää tarkastaa poiston suhteen. Tarkastuksen toteuttamista ohjelmointiteknisesti tulee jatkokehityksessä pohtia, ennen kuin poisto voidaan toteuttaa.
Ainoat varsinaiset poisto-operaatiot sovelluksessa liittyvät tällä hetkellä varsinaista dataa sisältäviä tauluja yhdistäviin viitetauluihin, eli many-to-many-suhteisiin. Vanhat suhteet poistetaan säälimättä. Tällöin kyselyissäkään WHERE-lauseet eivät paisu älyttömiksi, koska viitetaulujen deleted-tilaa ei tarvitse tarkistaa. Tätä tuskin kannattaa mennä jatkokehityksessä muuttamaan.
Viitetaulujen tietueiden poistossahan ei tule mitään ongelmia, koska niihin ei ikinä kukaan viittaa, vaan ne viittaavat muihin. Olennaista on siis se, että esim. poistetun kurssin tiedot ovat poistonkin jälkeen saatavissa. Sen sijaan esim. se tieto, että kurssiin liittyi aikoinaan kiinnostus autourheilu, ei ole niin relevanttia. Jos tämä kuitenkin nähdään tärkeänä, on jatkokehityksessä enemmän urakkaa luvassa, koska many-to-many -suhteiden hallintamoduulikin pitää tältä osin päivittää.
3 Taulujen ja kenttien kuvaus
Kotka-tietokannasta laadittu raportti löytyy Koppelo-projektin projektikansiosta projektitilasta ja WWW-sivulta http://kotka.it.jyu.fi/koppelo. Kotka-järjestelmästä lainattujen Vainu-järjestelmän taulujen kuvaukset löytyvät ko. raporteista. Luvussa käydäänkin siten läpi ainoastaan Vainu-järjestelmään lisätyt uudet taulut.
3.1 Taulujen esitystapa
Huomattavaa on, ettei luvun taulujen esittelyssä eritellä monikielistystauluja erikseen. Lang-taulujen monikielistyskentät sisältävät varchar- tai text-tyyppistä tietoa. Ne esitellään perustaulun esittelyn yhteydessä. Lisäksi kaikissa tauluissa esiintyvää Deleted-kenttää ei erikseen mainita. Perusavainten kentät on vahvennettu.
Otetaan
esimerkiksi opintokokonaisuuksien tietojen tallentamiseen käytettävät StudyModule- ja StudyModuleLang-taulut. StudyModule-tauluun tallennetaan opintokokonaisuuksien kielistä riippumattomat
tiedot. Se sisältää seuraavat kentät:
Deleted kertoo, onko tietue poistettu.
StudyModuleID on taulun perusavain.
StudyLineID kertoo, mihin opintolinjaan
opintokokonaisuus kuuluu.
LevelID kertoo opintojen tason, esim. approbatur.
Year kertoo opintokokonaisuuden vuosiluvun.
Opintokokonaisuuden määritykset voivat muuttua vuosittain.
Link kertoo opintokokonaisuuden linkin
osoitteen eli URI:n.
StudyModuleLang-taulu
määrittää
opintokokonaisuustaulun käännettävät tiedot. Sen kentät
ovat seuraavat:
Deleted kertoo, onko tietue poistettu.
StudyModuleID on yhdistetyn
perusavaimen toinen osapuoli.
LanguageID kertoo käytetyn kielivaihtoehdon id:n.
Kenttä on yhdistetyn perusavaimen
toinen osapuoli.
Name on opintokokonaisuuden nimi
LinkName on opintokokonaisuuden linkin nimi.
LinkTitle on linkin pop-up-otsikko.
Sanoja, nimiä tai lauseita sisältävät kentät
ovat siis monikielistystaulussa, jonka yhdistetty perusavain kertoo kyseessä
olevan tietueen ja käytetyn kielen. Varsinaisen taulun ja sen
monikielistystaulun suhde on aina vastaavanlainen, joten luvuissa 6.1 ja 6.2
yhdistämme taulut seuraavassa listassa esitetyllä tavalla. Lisäksi mainitaan kentän
tietotyyppi ja suhtautuminen tyhjiin arvoihin.
StudyModule-tauluun tallennetaan opintokokonaisuudet. Sen kentät ovat seuraavat:
StudyModuleID on taulun
perusavain (int4 not null).
StudyLineID kertoo opintolinjan, johon opintokokonaisuus kuuluu (int4 not null).
LevelID kertoo opintojen tason, esim. approbatur (int4 not null).
Year kertoo
opintokokonaisuuden vuosiluvun (varchar not null). Opintokokonaisuuden määritykset voivat
muuttua vuosittain.
Link on
opintokokonaisuuden linkin osoite eli URI (varchar null).
Name on opintokokonaisuuden nimi (varchar null).
LinkName on opintokokonaisuuden linkin nimi (varchar null).
LinkTitle on linkin pop-up-otsikko (varchar null).
Lisäksi on huomattavaa, että luettavuuden vuoksi tässä dokumentissa taulujen ja kenttien nimissä käytetään isoja kirjaimia erottamaan eri sanoja, vaikka todellisuudessa koko tietokanta on yksinomaan pienillä kirjaimilla. Vaikka SQL-standardien mukaan isoilla ja pienillä kirjaimilla ei pitäisi olla eroa, meillä ilmeni ongelmia isoja kirjaimia käytettäessä. Jos tietokannassa käytettiin myös kapitaaleja, jouduttiin taulujen ja kenttien nimet kirjoittamaan SQL-lauseissa lainausmerkkien sisään. Tämä on varsinkin ohjelmakoodissa vaivalloista, koska lainausmerkit pitää vielä suojata escape-merkillä.
3.2 Varsinaiset datataulut
Luvussa esitellään luvussa 3.1 esitetyllä tavalla tiivistäen taulut, jotka kuvaavat varsinaisia tietokokonaisuuksia tai reaalimaailman olioita (engl. entity). Lihavoidut kentät ovat perusavaimia.
StudyModule-taulu sisältää opintokokonaisuudet. Sen kentät ovat seuraavat:
StudyModuleID on taulun perusavain (int4 not null).
StudyLineID kertoo opintolinjan, johon opintokokonaisuus kuuluu (int4 not null).
LevelID kertoo opintojen tason, esim. approbatur (int4 not null).
Year kertoo
opintokokonaisuuden vuoden. (varchar not null). Opintokokonaisuuden määritykset voivat
muuttua vuosittain.
Link on
opintokokonaisuuden WWW-sivujen linkin osoite eli URI (varchar null).
Name on opintokokonaisuuden
nimi (varchar null).
LinkName on opintokokonaisuuden linkin nimi (varchar null).
LinkTitle on linkin pop-up-otsikko (varchar null).
StudyLine-taulu sisältää opintolinjat. Sen kentät ovat seuraavat:
StudyLineID on taulun perusavain (int4 not null).
OrganisationID kertoo opintolinjan isäntäorganisaation (int4 not null).
Name on opintolinjan nimi (varchar not null).
DiaryNote-taulu sisältää päiväkirjamerkinnät. Sen kentät ovat seuraavat:
PersonID kertoo kenen päiväkirjamerkinnästä on kyse (int4 not null). Kenttä viittaa Person-tauluun.
DateTime kertoo merkinnän luontiajan (datetime not null).
Text on itse
merkintä (text not null).
Story-taulu sisältää kertomukset. Kertomus voi liittyä useaankin eri asiaan. Sen kentät ovat seuraavat:
StoryID on taulun perusavain (int4 not null).
DateTime kertoo merkinnän luontiajan (datetime not null).
Title on kertomuksen otsikko (varchar not
null).
Text on itse kertomus (text not null).
Interest-taulu sisältää kiinnostuksen kohteet. Se liittyy tyypillisesti kursseihin ja ammatteihin. Sen kentät ovat seuraavat:
InterestID on taulun perusavain (int4 not null).
Name on kiinnostussana (varchar not null).
Profession-taulu sisältää ammattinimikkeet. Sen kentät ovat seuraavat:
ProfessionID on taulun perusavain (int4 not null).
Name on ammatin nimi (varchar not null).
Topic-taulu sisältää etusivulla näytettävät ajankohtaiset uutiset.
TopicID on taulun perusavain (int4 not null).
DateTime kertoo
ajankohtaista-uutisen luontiajan
(datetime not null).
Title on tekstin otsikko (varchar not null).
Text on itse uutisteksti (text not null).
PageItem-taulu sisältää JSP-sivuilla sijaitsevat staattiset HTML-elementit. Sen kentät ovat seuraavat:
ID kertoo kyseisen sivun nimen, esim. ainehaku.jsp
(varchar not null).
LanguageID kertoo kielen tunnisteen (int4 not null).
Item on JSP-sivun HTML-osa (text not null).
FAQ-taulu sisältää usein kysytyt kysymykset. Sen kentät ovat seuraavat:
FAQID on taulun perusavain (int4 not null).
DateTime kertoo FAQ-merkinnän luontiajan (datetime not
null).
FAQTypeID kertoo palautteen tyypin (int4 not null). Arvo
viittaa FAQType-tauluun. Sitä
käytetään FAQ:n lajitteluun.
Question on kysymys (text not null).
Reply on vastaus
kysymykseen (text not null).
FAQType-taulu sisältää FAQ-tyypit. Sen kentät ovat seuraavat:
FAQTypeID on taulun perusavain (int4 not null).
Se kertoo usein kysytyn kysymyksen tyypin. Sitä käytetään FAQ:n
lajitteluun.
Name on FAQTypen nimi (varchar not
null).
Feedback-taulu sisältää palautteet ja kysymykset. Sen kentät ovat seuraavat:
FeedbackID on taulun perusavain (int4 not null).
FeedbackTypeID on palautteen tyyppi (int4 not null). Se viittaa FeedbackType-tauluun. Käytetään lajitteluun.
OrganisationID kertoo organisaatiotyypin (int4 not null). Se viittaa Organisation-tauluun.
DateTime kertoo FAQ-merkinnän luontiajan (datetime not
null).
Title on palautteen otsikko (varchar not null).
Text on palautteen
teksti (text null).
Email on palautteen antajan sähköpostiosoite (varchar null).
Read kertoo onko vastuuhenkilö lukenut palautteen
(bool not null).
FeedbackTypeID ja OrganisationID määräävät
yhdessä, kenelle kysymys välitetään. Esimerkiksi FeedbackTypeID voi olla kurssisihteeri ja organisationID Humanistinen tdk.
FeedbackType-taulu sisältää palautteen tai kysymyksen tyypit. Sen kentät ovat seuraavat:
FeedbackTypeID on taulun perusavain (int4 not null). Käytetään lajitteluun.
Name on palautteen tyypin nimi (varchar not null).
NavLink-taulu sisältää käyttäjäpuolen navigointipalkin linkit. Sen kentät ovat seuraavat:
NavLinkID on taulun perusavain (int4 not null).
ParentID on ylemmän
tason linkin tunniste (int4 null).
Link on linkin
osoite eli URI (varchar not null).
Name on linkin nimi (oikeasti monikielistystaulussa; varchar not null).
Title on linkin pop-up-otsikko (oikeasti monikielistystaulussa;
varchar null).
AdLink-taulu sisältää
ylläpitopuolen navigointipalkin linkit. Sen kentät ovat seuraavat:
AdLinkID on taulun perusavain (int4 not null).
ParentID on ylemmän tason linkin tunniste (int4 null).
Link on linkin
osoite eli URI (varchar not null).
Name on linkin nimi (vain suomeksi, ei erillistä
monikielistystaulua; varchar not null).
Title on linkin pop-up-otsikko (vain suomeksi, ei erillistä
monikielistystaulua; varchar null).
CompletionType-taulu sisältää kurssien suorittamistavat, esim. iltaopinnot tai verkko-opinnot. Sen kentät ovat seuraavat:
CompletionTypeID on taulun perusavain (int4 not null).
Name kertoo kurssin suorittamistavan (varchar not null).
Description on suoritustavan kuvaus (text null).
GraduationDegree-taulu sisältää tutkintotasot (esim. maisteri). Sen kentät ovat seuraavat:
GraduationDegreeID on taulun perusavain (int4 not null).
Name kertoo tutkinnon nimen (varchar not null).
Requirement-taulu sisältää kaikki tutkintovaatimukset. Sen kentät ovat seuraavat:
RequirementID on
taulun perusavain (int4 not null).
GraduationDegreeID kertoo tutkinnon tason (int4 not
null). Se viittaa GraduationDegree-tauluun.
StudyLineID kertoo opintolinjan, jonka tutkinnosta on kyse (int4 not null). Viittaa StudyLine-tauluun.
Year kertoo tutkintovaatimuksen vuoden (varchar not
null). Tutkintovaatimukset
voivat muuttua vuosittain.
MinCredits kertoo tutkintovaatimuksen
vähimmäisopintoviikkomäärän (float8 not null).
Link kertoo linkin osoitteen eli URI:n (varchar not null).
Name kertoo vaatimuksen nimen (varchar not null).
LinkName on linkin
nimi (varchar null).
LinkTitle on linkin pop-up-otsikko (varchar null).
Text kuvaa
tutkintovaatimusta (text null).
Info sisältää lisätietoa (text null).
Relationship-taulu sisältää opintokokonaisuuteen kuuluvien kurssien suhteen. Sen kentät ovat seuraavat:
RelationshipID on
taulun perusavain (int4 not null).
Name kuvaa suhteen tyyppiä, esim. required, obligatory, forbidden (varchar not null).
CourseGroup-taulu sisältää kurssiryhmät. Se kertoo opintokokonaisuuksien kurssivaatimukset. Sen kentät ovat seuraavat:
CourseGroupID on taulun perusavain (int4 not null).
MinCredits kertoo vähimmäisopintoviikkomäärän (float8 null).
MaxCredits kertoo enimmäisopintoviikkomäärä (float8 null).
MinCount kertoo kurssien vähimmäislukumäärä (int4 null).
MaxCount kertoo kurssien enimmäislukumäärä (int4 null).
3.2.1 Jatkokehitysvaiheessa Vainu-sovelluksen tietokantaan lisätyt taulut
Diaryquisanswer-taulu sisältää päiväkirjakysymysten
vastaukset. Sen kentät ovat seuraavat:
PersonID on taulun perusavain.
groupid on kysymysryhmän tunnus.
ansindex on vastaus-tekstilaatikon järjestysnumero.
text on vastaus kysymykseen.
Diaryquizgrouplang-taulu sisältää päiväkirjakysymysten ryhmänimet.
Sen kentät ovat seuraavat:
id on taulun perusavain.
laguageid kertoo kielen id-tunnuksen.
groupname on kysymysryhmän nimi.
Diaryquizlang-taulu sisältää päiväkirjakysymykset. Sen kentät ovat
seuraavat:
id on taulun perusavain.
languageid kertoo kielen id-tunnuksen.
text on kysymys.
groupid kertoo kysymyksen ryhmätunnusnumeron.
Searched-taulu
sisältää haetut kiinnostussanat. Sen kentät ovat seuraavat:
word on taulun perusavain (kiinnostussana).
count1 kertoo sanan hakukerrat.
exists1 esiintyykö.
Searchbooksmarks-taulu sisältää tallennetut hakutulokset. Sen kentät ovat seuraavat:
content on taulun perusavain (tallennettu haku).
info kertoo infoa hakutuloksesta.
userid on käyttäjän tunnus.
3.3 Viitetaulut
Viitetaulut eivät kuvaa niinkään reaalimaailman olioita, vaan niiden välisiä suhteita. Tyypillisesti esimerkiksi taulu StudyModuleProfession sisältää ainoastaan kentät StudyModuleID ja ProfessionID. Niinpä näiden kenttien luettelu tässä ei ole tarpeellista.
Lisäksi kaikki viitetaulut sisältävät myös deleted-kentän Kotka-järjestelmän perintönä. Tätä kenttää ei Vainu-järjestelmässä ainakaan tällä hetkellä käytetä, sillä viitetietueet todella poistetaan DELETE-lauseella.
Seuraavassa esitellään lyhyesti viitetaulujen merkitykset:
StudyModuleProfession sisältää opintokokonaisuuteen liittyvät ammatit.
StudyModulePerson sisältää käyttäjän suorittamat opintokokonaisuudet.
CourseStudyModule sisältää opintokokonaisuuteen kuuluvien kurssien ja opintokokonaisuuden määräävien kenttien lisäksi kentät RelationshipID ja CourseGroupID.
CoursePerson sisältää käyttäjän suorittamat kurssit.
InterestProfession sisältää
ammattiin liittyvät kiinnostuksen kohteet.
CourseCompletionType sisältää kurssin mahdolliset suorittamistavat.
InterestCourse sisältää kurssiin liittyvät kiinnostuksenkohteet.
RequirementCourse sisältää
tutkintovaatimuksen sisältämät kurssit, lisäkenttänä RelationshipID.
RequirementStudyModule sisältää tutkintovaatimuksiin kuuluvat opintokokonaisuudet, lisäkenttänä RelationshipID.
StoryCompletionType sisältää kurssin
suorittamistapaan liittyvät kertomukset.
StoryOrganisation sisältää
organisaatioon liittyvät kertomukset.
StoryProfession sisältää
ammattiin liittyvät kertomukset.
StoryStudyModule sisältää
opintokokonaisuuteen liittyvät kertomukset.
Vaylakertomukset sisältää avoimen väylään liittyvät kertomukset.
FeedbackDelivery kertoo, kuka vastaa
tietyntyyppisestä palautteesta.
Väyläkertomukset-taulu on ikään kuin viitetaulu. On kuitenkin mahdotonta varsinaisesti liittää kertomuksia väylään, koska väylä ei ole mikään tietokannan olio. Niinpä taulun tietueet sisältävät deleted-kentän lisäksi ainoastaan StoryID:n. Jostain syystä taulun nimi on lipsahtanut suomen- kieliseksi. Taulun nimi ei esiinny kovin monessa JSP-tiedostossa, joten jos poikkeavuus häiritsee, ei sen korjaaminen jatkokehityksessä ole suuri vaiva.
FeedbackDelivery sisältää kolme kenttää: FeedbackTypeID, OrganisationID ja PersonID. Esimerkiksi tyypin tunniste voi vastata kurssisihteeri-tyyppiä ja organisaation tunniste Informaatioteknologian tiedekuntaa. Tällöin Informaatioteknologian tiedekunnan kurssisihteerille osoitetut kysymykset ohjataan tietueessa määritellyn henkilön sähköpostiosoitteeseen.
3.4 Tietokantakaaviot
Kuvissa
1 ja 2 esitellään tietokannan rakennetta ER-kaavioiden avulla. Niissä esitetään
on myös Kotka-järjestelmän kanssa yhteiset taulut. Kaavioissa esiintyvät pienet
pallot kuvaavat monikielistämistä. Kaavio on jaettu kahtia, jotta sen
esittäminen A4-koossa onnistuu järkevästi. Jakoperusteena on ollut lähinnä
piirtämisen helppous, eikä niinkään looginen jako. Kaikki taulut ovat toistensa
kanssa siinä määrin tekemisissä, ettei loogista jakoa oikeastaan olekaan.
Taulujen väliset relaatiot selviävät parhaiten kuvasta 3.
Kuva 1. ER-kaavion ensimmäinen osa.
Kuva 2. ER-kaavion toinen osa.
Kuva 3. Relaatiokuva.
Liite. Taulujen
luontilauseet
Liitteessä esitetään kaikkien (myös Kotka-tietokannasta lainattujen) taulujen luontiin käytetyt lauseet. Käytetyt tietotyypit eivät ole SQL-standardien mukaisia. Tämä täytyy ottaa huomioon, jos tietokantaa ollaan joskus siirtämässä pois PostgreSql:stä.
Tyypit int4 ja float8 ovat Kotka-järjestelmän perintöä. Tarvittaessa nämä kannattaa korvata standardeilla integer- ja real-tyypeillä. (float8 on oikeastaan double, mutta tarvetta näin suurille muuttujille ei ole.)
Huomattavaa on, että varcharille ei yleensä ole määritelty enimmäispituutta. Tämä on PostgreSql:n laajennus. Jos pituutta ei mainita, varchar voi olla minkä mittainen hyvänsä. Vastaavalla tavalla toimii myös text, joka yleisyydestään huolimatta ei sekään ole standardi. Vainussa on tapana käyttää esimerkiksi nimien ja otsikoiden yhteydessä varcharia ja pidempien tekstien tallentamiseen text-tyyppiä.
CREATE TABLE language (
deleted bool NOT NULL,
languageid int4 NOT NULL,
abbreviation varchar NOT NULL,
name
varchar NOT NULL,
CONSTRAINT language_pkey PRIMARY KEY (languageid)
);
CREATE TABLE adlink (
adlinkid
int4 NOT NULL,
name
varchar NOT NULL,
title
varchar,
link
varchar NOT NULL,
parentid
int4,
CONSTRAINT adlink_pkey PRIMARY KEY (adlinkid),
CONSTRAINT adlink_parent FOREIGN KEY (parentid) REFERENCES adlink
(adlinkid)
ON DELETE NO ACTION ON UPDATE CASCADE
);
CREATE TABLE autonumber (
tablename
varchar NOT NULL,
value
int4 NOT NULL,
CONSTRAINT autonumber_pkey PRIMARY KEY (tablename)
);
CREATE TABLE level (
deleted
bool NOT NULL,
levelid
int4 NOT NULL,
CONSTRAINT level_pkey PRIMARY KEY (levelid)
);
CREATE TABLE levellang (
deleted
bool NOT NULL,
levelid
int4 NOT NULL,
languageid int4 NOT NULL,
name
varchar NOT NULL,
CONSTRAINT levellang_pkey PRIMARY KEY (languageid, levelid),
CONSTRAINT levellang_level FOREIGN KEY (levelid) REFERENCES level
(levelid)
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT
level_lang FOREIGN KEY (languageid)
REFERENCES language(languageid) ON DELETE NO
ACTION ON UPDATE CASCADE
);
CREATE TABLE accessrightlevel (
deleted
bool NOT NULL,
accessrightlevelid int4 NOT NULL,
CONSTRAINT accessrightlevel_pkey PRIMARY KEY (accessrightlevelid)
);
CREATE TABLE accessrightlevellang (
deleted
bool NOT NULL,
accessrightlevelid int4 NOT NULL,
languageid int4 NOT NULL,
name
varchar NOT NULL,
CONSTRAINT accessrightlevellang_pkey
PRIMARY KEY (accessrightlevelid, languageid),
CONSTRAINT arlevellang_arlevel FOREIGN KEY (accessrightlevelid)
REFERENCES accessrightlevel
(accessrightlevelid)
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT arlevel_lang FOREIGN KEY (languageid) REFERENCES
language (languageid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE organisationtype (
deleted
bool NOT NULL,
organisationtypeid int4 NOT NULL,
CONSTRAINT organisationtype_pkey PRIMARY KEY (organisationtypeid)
);
CREATE TABLE organisationtypelang (
deleted
bool NOT NULL,
organisationtypeid int4 NOT NULL,
languageid int4 NOT NULL,
name
varchar NOT NULL,
CONSTRAINT organisationtypelang_pkey
PRIMARY KEY (languageid, organisationtypeid),
CONSTRAINT orgtypelang_orgtype FOREIGN KEY (organisationtypeid)
REFERENCES organisationtype
(organisationtypeid)
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT orgtype_lang FOREIGN KEY (languageid) REFERENCES
language (languageid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE organisation (
deleted
bool NOT NULL,
organisationid int4 NOT NULL,
organisationtypeid int4 NOT NULL,
stylesheet varchar,
CONSTRAINT organisation_pkey PRIMARY KEY (organisationid),
CONSTRAINT org_orgtype FOREIGN KEY (organisationtypeid)
REFERENCES organisationtype (organisationtypeid)
ON DELETE NO ACTION ON UPDATE CASCADE
);
CREATE TABLE organisationlang (
deleted
bool NOT NULL,
organisationid int4 NOT NULL,
languageid int4 NOT NULL,
abbreviation varchar,
name
varchar NOT NULL,
CONSTRAINT organisationlang_pkey PRIMARY KEY (languageid,
organisationid),
CONSTRAINT orglang_org FOREIGN KEY (organisationid) REFERENCES
organisation (organisationid) ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT org_lang FOREIGN KEY (languageid) REFERENCES language (languageid)
ON DELETE NO ACTION ON UPDATE CASCADE
);
CREATE TABLE orgparametertype (
deleted
bool NOT NULL,
orgparametertypeid int4 NOT NULL,
CONSTRAINT orgparametertype_pkey PRIMARY KEY (orgparametertypeid)
);
CREATE TABLE orgparametertypelang (
deleted
bool NOT NULL,
orgparametertypeid int4 NOT NULL,
languageid int4 NOT NULL,
name
varchar NOT NULL,
CONSTRAINT orgparametertypelang_pkey
PRIMARY KEY (languageid, orgparametertypeid),
CONSTRAINT optypelang_optype FOREIGN KEY (orgparametertypeid)
REFERENCES orgparametertype
(orgparametertypeid)
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT orgparamtype_lang FOREIGN KEY (languageid) REFERENCES
language (languageid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE organisationparameter (
deleted
bool NOT NULL,
organisationparameterid int4 NOT NULL,
organisationid int4 NOT NULL,
orgparametertypeid int4 NOT NULL,
value
varchar NOT NULL,
CONSTRAINT organisationparameter_pkey PRIMARY KEY
(organisationparameterid),
CONSTRAINT
orgparam_org FOREIGN KEY (organisationid) REFERENCES
organisation (organisationid) ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT orgparam_orgparamtype FOREIGN KEY (orgparametertypeid)
REFERENCES orgparametertype
(orgparametertypeid)
ON DELETE NO ACTION ON UPDATE CASCADE
);
CREATE TABLE orgparametertypeprot (
deleted
bool NOT NULL,
accessrightlevelid int4 NOT NULL,
orgparametertypeid int4 NOT NULL,
CONSTRAINT orgparametertypeprot_pkey
PRIMARY KEY (accessrightlevelid, orgparametertypeid),
CONSTRAINT optypeprot_arlevel FOREIGN KEY (accessrightlevelid)
REFERENCES accessrightlevel
(accessrightlevelid)
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT optypeprot_optype FOREIGN KEY (orgparametertypeid)
REFERENCES orgparametertype
(orgparametertypeid)
ON DELETE NO ACTION ON UPDATE CASCADE
);
CREATE TABLE orgrelationship (
deleted
bool NOT NULL,
organisationid int4 NOT NULL,
suborganisationid int4 NOT NULL,
CONSTRAINT orgrelationship_pkey
PRIMARY KEY (organisationid, suborganisationid),
CONSTRAINT orgrel_org FOREIGN KEY (organisationid) REFERENCES
organisation (organisationid) ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT orgrel_suborg FOREIGN KEY (suborganisationid) REFERENCES
organisation (organisationid) ON DELETE NO
ACTION ON UPDATE CASCADE
);
CREATE TABLE person (
deleted
bool NOT NULL,
personid
int4 NOT NULL,
callname
varchar NOT NULL,
firstnames varchar NOT NULL,
lastname
varchar NOT NULL,
homeorganisationid int4 NOT NULL,
fundingorganisationid int4 NOT NULL,
account
varchar NOT NULL,
password
varchar NOT NULL,
registeredon timestamp NOT NULL,
lastlogin
timestamp,
lastlogout timestamp,
sessiontimeout int4 NOT NULL,
autologout bool NOT NULL,
socsecbirth varchar(6) NOT NULL,
socseccentury varchar(1) NOT NULL,
socsecend
varchar(4) NOT NULL,
CONSTRAINT person_pkey PRIMARY KEY (personid),
CONSTRAINT person_homeorg FOREIGN KEY (homeorganisationid) REFERENCES
organisation (organisationid) ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT person_fundorg FOREIGN KEY (fundingorganisationid) REFERENCES
organisation (organisationid) ON DELETE NO
ACTION ON UPDATE CASCADE
);
CREATE UNIQUE INDEX personaccount_unique ON person
(account);
CREATE TABLE relationship (
deleted
bool NOT NULL,
relationshipid int4 NOT NULL,
CONSTRAINT relationship_pkey PRIMARY KEY (relationshipid)
);
CREATE TABLE relationshiplang (
deleted
bool NOT NULL,
relationshipid int4 NOT NULL,
languageid int4 NOT NULL,
name varchar
NOT NULL,
CONSTRAINT relationshiplang_pkey PRIMARY KEY (languageid,
relationshipid),
CONSTRAINT relatlang_relat FOREIGN KEY (relationshipid) REFERENCES
relationship (relationshipid) ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT relationship_lang FOREIGN KEY (languageid) REFERENCES
language (languageid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE studyline (
deleted
bool NOT NULL,
studylineid int4 NOT NULL,
organisationid int4 NOT NULL,
CONSTRAINT studyline_pk PRIMARY KEY (studylineid),
CONSTRAINT studyline_org FOREIGN KEY (organisationid) REFERENCES
organisation (organisationid) ON DELETE NO
ACTION ON UPDATE CASCADE
);
CREATE TABLE studylinetranslation (
deleted
bool NOT NULL,
studylineid int4 NOT NULL,
languageid
int4 NOT NULL,
name
varchar NOT NULL,
CONSTRAINT studylinetranslation_pk PRIMARY KEY (languageid,
studylineid),
CONSTRAINT slinelang_sline FOREIGN KEY (studylineid) REFERENCES
studyline (studylineid) ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT studyline_lang FOREIGN KEY
(languageid) REFERENCES
language (languageid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE studymodule (
deleted
bool NOT NULL,
studymoduleid int4 NOT NULL,
studylineid int4,
levelid
int4 NOT NULL,
year
varchar NOT NULL,
link
varchar,
CONSTRAINT studymodule_pkey PRIMARY KEY (studymoduleid),
CONSTRAINT studymodule_sline FOREIGN KEY (studylineid) REFERENCES
studyline (studylineid) ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT studymodule_level FOREIGN KEY (levelid) REFERENCES level
(levelid)
ON DELETE NO ACTION ON UPDATE CASCADE
);
CREATE UNIQUE INDEX studymodule_unique ON
studymodule (levelid, studylineid, year);
CREATE TABLE studymodulelang (
deleted
bool NOT NULL,
studymoduleid
int4 NOT NULL,
languageid int4 NOT NULL,
name
varchar,
linkname
varchar,
linktitle
varchar,
CONSTRAINT studymodulelang_pkey PRIMARY KEY (languageid, studymoduleid),
CONSTRAINT smodulelang_smodule FOREIGN KEY (studymoduleid) REFERENCES
studymodule (studymoduleid) ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT studymodule_lang FOREIGN KEY (languageid) REFERENCES
language (languageid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE studymoduleperson (
deleted
bool NOT NULL,
studymoduleid int4 NOT NULL,
personid
int4 NOT NULL,
CONSTRAINT studymoduleperson_pkey PRIMARY KEY (personid, studymoduleid),
CONSTRAINT moduleperson_module FOREIGN KEY (studymoduleid) REFERENCES
studymodule (studymoduleid) ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT moduleperson_person FOREIGN KEY (personid) REFERENCES
person (personid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE completiontype (
deleted
bool NOT NULL,
completiontypeid int4 NOT NULL,
CONSTRAINT completiontype_pkey PRIMARY KEY (completiontypeid)
);
CREATE TABLE completiontypelang (
deleted
bool NOT NULL,
completiontypeid int4 NOT NULL,
languageid int4 NOT NULL,
name
varchar NOT NULL,
description text,
CONSTRAINT completiontypelang_pkey
PRIMARY KEY (completiontypeid, languageid),
CONSTRAINT ctypelang_ctype FOREIGN KEY (completiontypeid)
REFERENCES completiontype (completiontypeid)
ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT ctype_lang FOREIGN KEY (languageid) REFERENCES
language (languageid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE course (
deleted
bool NOT NULL,
courseid
int4 NOT NULL,
organisationid int4 NOT NULL,
mincredits float8 NOT NULL,
maxcredits float8 NOT NULL,
code
char(6) NOT NULL,
level
int4 NOT NULL,
CONSTRAINT course_pkey PRIMARY KEY (courseid),
CONSTRAINT course_org FOREIGN KEY (organisationid) REFERENCES
organisation (organisationid) ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT course_level FOREIGN KEY (level) REFERENCES level (levelid)
ON DELETE NO ACTION ON UPDATE CASCADE
);
CREATE TABLE coursecompletiontype (
deleted
bool NOT NULL,
courseid
int4 NOT NULL,
completiontypeid int4 NOT NULL,
CONSTRAINT coursecompletiontype_pkey PRIMARY KEY (completiontypeid,
courseid),
CONSTRAINT cctype_course FOREIGN KEY (courseid) REFERENCES course
(courseid)
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT cctype_cctype FOREIGN KEY (completiontypeid)
REFERENCES completiontype (completiontypeid)
ON DELETE NO ACTION ON UPDATE CASCADE
);
CREATE TABLE coursegroup (
deleted
bool NOT NULL,
coursegroupid int4 NOT NULL,
mincredits float8,
maxcredits float8,
mincount
int4,
maxcount
int4,
CONSTRAINT coursegroup_pkey PRIMARY KEY (coursegroupid)
);
CREATE TABLE coursegrouplang (
deleted
bool NOT NULL,
coursegroupid int4 NOT NULL,
languageid int4 NOT NULL,
name
varchar NOT NULL,
CONSTRAINT coursegrouplang_pkey PRIMARY KEY (coursegroupid, languageid),
CONSTRAINT cgrouplang_cgroup FOREIGN KEY (coursegroupid) REFERENCES
coursegroup (coursegroupid) ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT coursegroup_lang FOREIGN KEY (languageid) REFERENCES
language (languageid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE courselang (
deleted
bool NOT NULL,
courseid
int4 NOT NULL,
languageid int4 NOT NULL,
name
varchar NOT NULL,
CONSTRAINT courselang_pkey PRIMARY KEY (courseid, languageid),
CONSTRAINT courselang_course FOREIGN KEY (courseid) REFERENCES
course (courseid) ON DELETE NO ACTION ON
UPDATE CASCADE,
CONSTRAINT course_lang FOREIGN KEY (languageid) REFERENCES
language (languageid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE courseperson (
deleted
bool NOT NULL,
courseid
int4 NOT NULL,
personid
int4 NOT NULL,
CONSTRAINT courseperson_pkey PRIMARY KEY (courseid, personid),
CONSTRAINT courseperson_course FOREIGN KEY (courseid) REFERENCES
course (courseid) ON DELETE NO ACTION ON
UPDATE CASCADE,
CONSTRAINT courseperson_person FOREIGN KEY (personid) REFERENCES
person (personid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE coursestudymodule (
deleted
bool NOT NULL,
courseid
int4 NOT NULL,
studymoduleid int4 NOT NULL,
coursegroupid int4,
relationshipid int4 NOT NULL,
CONSTRAINT coursestudymodule_pkey PRIMARY
KEY (courseid, studymoduleid),
CONSTRAINT coursesm_course FOREIGN KEY (courseid) REFERENCES
course (courseid) ON DELETE NO ACTION ON
UPDATE CASCADE,
CONSTRAINT coursesm_sm FOREIGN KEY (studymoduleid) REFERENCES
studymodule (studymoduleid) ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT coursesm_coursegroup FOREIGN KEY (coursegroupid) REFERENCES
coursegroup (coursegroupid) ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT coursesm_relationship FOREIGN KEY (relationshipid) REFERENCES
relationship (relationshipid) ON DELETE NO
ACTION ON UPDATE CASCADE
);
CREATE TABLE diarynote (
deleted
bool NOT NULL,
personid
int4 NOT NULL,
datetime
timestamp NOT NULL,
text text
NOT NULL,
CONSTRAINT diarynote_pkey PRIMARY KEY (datetime, personid),
CONSTRAINT diary_person FOREIGN KEY (personid) REFERENCES person
(personid)
ON DELETE NO ACTION ON UPDATE CASCADE
);
CREATE INDEX diarynote_timestamp ON diarynote
(datetime);
CREATE TABLE faqtype (
deleted bool
NOT NULL,
faqtypeid
int4 NOT NULL,
CONSTRAINT faqtype_pkey PRIMARY KEY (faqtypeid)
);
CREATE TABLE faqtypelang (
deleted
bool NOT NULL,
faqtypeid
int4 NOT NULL,
languageid int4 NOT NULL,
name
varchar NOT NULL,
CONSTRAINT faqtypelang_pkey PRIMARY KEY (faqtypeid, languageid),
CONSTRAINT faqtypelang_faqtype FOREIGN KEY (faqtypeid) REFERENCES
faqtype (faqtypeid) ON DELETE NO ACTION ON
UPDATE CASCADE,
CONSTRAINT faqtype_lang FOREIGN KEY (languageid) REFERENCES
language (languageid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE faq (
deleted
bool NOT NULL,
faqid
int4 NOT NULL,
faqtypeid
int4 NOT NULL,
datetime
timestamp NOT NULL,
question
text NOT NULL,
reply
text NOT NULL,
CONSTRAINT faq_pkey PRIMARY KEY (faqid),
CONSTRAINT faq_faqtype FOREIGN KEY (faqtypeid) REFERENCES faqtype
(faqtypeid)
ON DELETE NO ACTION ON UPDATE CASCADE
);
CREATE INDEX faq_timestamp ON faq (datetime);
CREATE TABLE feedbacktype (
deleted
bool NOT NULL,
feedbacktypeid int4 NOT NULL,
CONSTRAINT feedbacktype_pkey PRIMARY KEY (feedbacktypeid)
);
CREATE TABLE feedbacktypelang (
deleted
bool NOT NULL,
feedbacktypeid int4 NOT NULL,
languageid int4 NOT NULL,
name
varchar NOT NULL,
CONSTRAINT feedbacktypelang_pkey PRIMARY KEY (feedbacktypeid,
languageid),
CONSTRAINT fbtypelang_fbtype FOREIGN KEY (feedbacktypeid) REFERENCES
feedbacktype (feedbacktypeid) ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT feedbacktype_lang FOREIGN KEY (languageid) REFERENCES
language (languageid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE feedback (
deleted
bool NOT NULL,
feedbackid int4 NOT NULL,
feedbacktypeid int4 NOT NULL,
organisationid int4 NOT NULL,
datetime
timestamp NOT NULL,
title
varchar NOT NULL,
text text
NOT NULL,
email
varchar,
read bool
NOT NULL,
CONSTRAINT feedback_pkey PRIMARY KEY (feedbackid),
CONSTRAINT fb_fbtype FOREIGN KEY (feedbacktypeid) REFERENCES
feedbacktype (feedbacktypeid) ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT fb_org FOREIGN KEY (organisationid) REFERENCES
organisation (organisationid) ON DELETE NO
ACTION ON UPDATE CASCADE
);
CREATE INDEX feedback_timestamp ON feedback
(datetime);
CREATE INDEX feedback_title ON feedback (title);
CREATE INDEX feedback_text ON feedback (text);
CREATE INDEX feedback_read ON feedback (read);
CREATE TABLE feedbackdelivery (
deleted
bool NOT NULL,
feedbacktypeid int4 NOT NULL,
organisationid int4 NOT NULL,
personid
int4 NOT NULL,
CONSTRAINT feedbackdelivery_pkey
PRIMARY KEY (feedbacktypeid, organisationid),
CONSTRAINT fbdelivery_fbtype FOREIGN KEY (feedbacktypeid) REFERENCES
feedbacktype (feedbacktypeid) ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT fbdelivery_org FOREIGN KEY (organisationid) REFERENCES
organisation (organisationid) ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT fbdelivery_person FOREIGN KEY (personid) REFERENCES
person (personid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE graduationdegree (
deleted
bool NOT NULL,
graduationdegreeid int4 NOT NULL,
CONSTRAINT graduationdegree_pkey PRIMARY KEY (graduationdegreeid)
);
CREATE TABLE graduationdegreelang (
deleted
bool NOT NULL,
graduationdegreeid int4 NOT NULL,
languageid int4 NOT NULL,
name
varchar NOT NULL,
CONSTRAINT graduationdegreelang_pkey
PRIMARY KEY (graduationdegreeid, languageid),
CONSTRAINT gdegreelang_gdegree FOREIGN KEY (graduationdegreeid)
REFERENCES graduationdegree
(graduationdegreeid)
ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT
gdegree_lang FOREIGN KEY (languageid) REFERENCES
language (languageid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE profession (
deleted
bool NOT NULL,
professionid int4 NOT NULL,
CONSTRAINT profession_pkey PRIMARY KEY (professionid)
);
CREATE TABLE professionlang (
deleted
bool NOT NULL,
professionid int4 NOT NULL,
languageid int4 NOT NULL,
name
varchar NOT NULL,
description text,
CONSTRAINT professionlang_pkey PRIMARY KEY (languageid, professionid),
CONSTRAINT
proflang_prof FOREIGN KEY (professionid) REFERENCES
profession (professionid) ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT profession_lang FOREIGN KEY (languageid) REFERENCES
language (languageid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE interest (
deleted
bool NOT NULL,
interestid int4 NOT NULL,
CONSTRAINT interest_pkey PRIMARY KEY (interestid)
);
CREATE TABLE interestcourse (
deleted
bool NOT NULL,
interestid int4 NOT NULL,
courseid
int4 NOT NULL,
CONSTRAINT interestcourse_pkey PRIMARY KEY (courseid, interestid),
CONSTRAINT intcourse_interest FOREIGN KEY (interestid) REFERENCES
interest (interestid) ON DELETE NO ACTION ON
UPDATE CASCADE,
CONSTRAINT intcourse_course FOREIGN KEY (courseid) REFERENCES
course (courseid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE interestlang (
deleted
bool NOT NULL,
interestid int4 NOT NULL,
languageid int4 NOT NULL,
name
varchar NOT NULL,
CONSTRAINT interestlang_pkey PRIMARY KEY (interestid, languageid),
CONSTRAINT interestlang_interest FOREIGN KEY (interestid) REFERENCES
interest (interestid) ON DELETE NO ACTION ON
UPDATE CASCADE,
CONSTRAINT interest_lang FOREIGN KEY (languageid) REFERENCES
language (languageid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE interestprofession (
deleted
bool NOT NULL,
interestid int4 NOT NULL,
professionid int4 NOT NULL,
CONSTRAINT interestprofession_pkey PRIMARY KEY (interestid,
professionid),
CONSTRAINT intprof_interest FOREIGN KEY (interestid) REFERENCES
interest (interestid) ON DELETE NO ACTION ON
UPDATE CASCADE,
CONSTRAINT intprof_prof FOREIGN KEY (professionid) REFERENCES
profession (professionid) ON DELETE NO ACTION
ON UPDATE CASCADE
);
CREATE TABLE navlink (
deleted
bool NOT NULL,
navlinkid
int4 NOT NULL,
parentid
int4,
link
varchar NOT NULL,
CONSTRAINT navlink_pkey PRIMARY KEY (navlinkid),
CONSTRAINT navlink_parent FOREIGN KEY (parentid) REFERENCES
navlink (navlinkid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE navlinklang (
deleted
bool NOT NULL,
navlinkid
int2 NOT NULL,
languageid int2 NOT NULL,
name
varchar NOT NULL,
title
varchar,
CONSTRAINT navlinklang_pkey PRIMARY KEY (languageid, navlinkid),
CONSTRAINT navlang_nav FOREIGN KEY (navlinkid) REFERENCES navlink
(navlinkid)
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT navlink_lang FOREIGN KEY (languageid) REFERENCES
language (languageid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE pageitem (
deleted
bool NOT NULL,
id
varchar NOT NULL,
languageid int4 NOT NULL,
item text
NOT NULL,
CONSTRAINT pageitem_pkey PRIMARY KEY (id, languageid),
CONSTRAINT pageitem_lang FOREIGN KEY (languageid) REFERENCES
language (languageid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE personparametertype (
deleted
bool NOT NULL,
personparametertypeid int4 NOT NULL,
CONSTRAINT personparametertype_pkey PRIMARY KEY (personparametertypeid)
);
CREATE TABLE personparametertypelang (
deleted
bool NOT NULL,
personparametertypeid
int4 NOT NULL,
languageid int4 NOT NULL,
name
varchar NOT NULL,
CONSTRAINT personparametertypelang_pkey
PRIMARY KEY (languageid,
personparametertypeid),
CONSTRAINT pptypelang_pptype FOREIGN KEY (personparametertypeid)
REFERENCES personparametertype
(personparametertypeid)
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT pptype_lang FOREIGN KEY (languageid) REFERENCES
language (languageid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE personparameter (
deleted
bool NOT NULL,
personparameterid int4 NOT NULL,
personid
int4 NOT NULL,
personparametertypeid int4 NOT NULL,
personparametervalue varchar NOT NULL,
CONSTRAINT personparameter_pkey PRIMARY KEY (personparameterid),
CONSTRAINT pparam_person FOREIGN KEY (personid) REFERENCES person
(personid)
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT pparam_pparamtype FOREIGN KEY (personparametertypeid)
REFERENCES personparametertype
(personparametertypeid)
ON DELETE NO ACTION ON UPDATE CASCADE
);
CREATE TABLE personparametertypeprot (
deleted
bool NOT NULL,
accesstype int4 NOT NULL,
personparametertypeid int4 NOT NULL,
accessrightlevelid int4 NOT NULL,
CONSTRAINT personparametertypeprot_pkey
PRIMARY KEY (accessrightlevelid, personparametertypeid),
CONSTRAINT pptypeprot_pptype FOREIGN KEY (personparametertypeid)
REFERENCES personparametertype
(personparametertypeid)
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT pptypeprot_arlevel FOREIGN KEY (accessrightlevelid)
REFERENCES accessrightlevel
(accessrightlevelid)
ON DELETE NO ACTION ON UPDATE CASCADE
);
CREATE TABLE requirement (
deleted
bool NOT NULL,
requirementid int4 NOT NULL,
graduationdegreeid int4 NOT NULL,
studylineid int4 NOT NULL,
year
varchar(4) NOT NULL,
mincredits float8 NOT NULL,
link
varchar,
CONSTRAINT requirement_pkey PRIMARY KEY (requirementid),
CONSTRAINT req_graddegree FOREIGN KEY (graduationdegreeid)
REFERENCES graduationdegree
(graduationdegreeid)
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT req_studyline FOREIGN KEY (studylineid) REFERENCES
studyline (studylineid) ON DELETE NO ACTION
ON UPDATE CASCADE
);
CREATE UNIQUE INDEX requirement_unique ON
requirement (graduationdegreeid,studylineid,year);
CREATE TABLE requirementcourse (
deleted
bool NOT NULL,
requirementid int4 NOT NULL,
courseid
int4 NOT NULL,
relationshipid int4 NOT NULL,
CONSTRAINT requirementcourse_pkey PRIMARY KEY (courseid, requirementid),
CONSTRAINT reqcourse_req FOREIGN KEY (requirementid) REFERENCES
requirement (requirementid) ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT reqcourse_course FOREIGN KEY (courseid) REFERENCES
course (courseid) ON DELETE NO ACTION ON
UPDATE CASCADE,
CONSTRAINT reqcourse_relation FOREIGN KEY (relationshipid) REFERENCES
relationship (relationshipid) ON DELETE NO
ACTION ON UPDATE CASCADE
);
CREATE TABLE requirementlang (
deleted
bool NOT NULL,
requirementid int4 NOT NULL,
languageid int4 NOT NULL,
name
varchar NOT NULL,
linkname
varchar,
linktitle
varchar,
text
text,
info
text,
CONSTRAINT requirementlang_pkey PRIMARY KEY (languageid, requirementid),
CONSTRAINT reqlang_req FOREIGN KEY (requirementid) REFERENCES
requirement (requirementid) ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT
requirement_lang FOREIGN KEY (languageid) REFERENCES
language (languageid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE TABLE requirementstudymodule (
deleted
bool NOT NULL,
requirementid int4 NOT NULL,
studymoduleid int4 NOT NULL,
relationshipid
int4 NOT NULL,
CONSTRAINT requirementstudymodule_pkey
PRIMARY KEY (requirementid, studymoduleid),
CONSTRAINT reqmodule_req FOREIGN KEY (requirementid) REFERENCES
requirement (requirementid) ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT
reqmodule_module FOREIGN KEY (studymoduleid) REFERENCES
studymodule (studymoduleid) ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT reqmodule_relation FOREIGN KEY (relationshipid) REFERENCES
relationship (relationshipid) ON DELETE NO
ACTION ON UPDATE CASCADE
);
CREATE TABLE story (
deleted
bool NOT NULL,
storyid
int4 NOT NULL,
datetime
timestamp NOT NULL,
CONSTRAINT story_pkey PRIMARY KEY (storyid)
);
CREATE INDEX story_timestamp ON story (datetime);
CREATE TABLE storycompletiontype (
deleted
bool NOT NULL,
storyid
int4 NOT NULL,
completiontypeid int4 NOT NULL,
CONSTRAINT storycompletiontype_pkey PRIMARY KEY (completiontypeid,
storyid),
CONSTRAINT storyct_story FOREIGN KEY (storyid) REFERENCES story
(storyid)
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT storyct_ct FOREIGN KEY (completiontypeid)
REFERENCES completiontype (completiontypeid)
ON DELETE NO
ACTION ON UPDATE CASCADE
);
CREATE TABLE storylang (
deleted
bool NOT NULL,
storyid
int4 NOT NULL,
languageid
int4 NOT NULL,
title
varchar NOT NULL,
text text
NOT NULL,
CONSTRAINT storylang_pkey PRIMARY KEY (languageid, storyid),
CONSTRAINT storylang_story FOREIGN KEY (storyid) REFERENCES story
(storyid)
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT
story_lang FOREIGN KEY (languageid) REFERENCES
language (languageid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE INDEX storylang_title ON storylang (title);
CREATE INDEX storylang_text ON storylang (text);
CREATE TABLE storyorganisation (
deleted bool NOT NULL,
storyid
int4 NOT NULL,
organisationid int4 NOT NULL,
CONSTRAINT storyorganisation_pkey PRIMARY KEY (organisationid, storyid),
CONSTRAINT storyorg_story FOREIGN KEY (storyid) REFERENCES story
(storyid)
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT storyorg_org FOREIGN KEY (organisationid) REFERENCES
organisation (organisationid) ON DELETE NO
ACTION ON UPDATE CASCADE
);
CREATE TABLE storyprofession (
deleted
bool NOT NULL,
storyid
int4 NOT NULL,
professionid int4 NOT NULL,
CONSTRAINT storyprofession_pkey PRIMARY KEY (professionid, storyid),
CONSTRAINT storyprof_story FOREIGN KEY (storyid) REFERENCES story
(storyid)
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT storyprof_prof FOREIGN KEY (professionid) REFERENCES
profession (professionid) ON DELETE NO ACTION
ON UPDATE CASCADE
);
CREATE TABLE storystudymodule (
deleted
bool NOT NULL,
storyid
int4 NOT NULL,
studymoduleid int4 NOT NULL,
CONSTRAINT storystudymodule_pkey PRIMARY KEY (storyid, studymoduleid),
CONSTRAINT storysmodule_story FOREIGN KEY (storyid) REFERENCES
story (storyid) ON DELETE NO ACTION ON UPDATE
CASCADE,
CONSTRAINT storysmodule_smodule FOREIGN KEY (studymoduleid) REFERENCES
studymodule (studymoduleid) ON DELETE NO ACTION
ON UPDATE CASCADE
);
CREATE TABLE studymoduleprofession (
deleted
bool NOT NULL,
studymoduleid int4 NOT NULL,
professionid int4 NOT NULL,
CONSTRAINT studymoduleprofession_pkey
PRIMARY KEY (professionid, studymoduleid),
CONSTRAINT moduleprof_module FOREIGN KEY (studymoduleid) REFERENCES
studymodule (studymoduleid) ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT moduleprof_prof FOREIGN KEY (professionid) REFERENCES
profession (professionid) ON DELETE NO ACTION
ON UPDATE CASCADE
);
CREATE TABLE topic (
deleted
bool NOT NULL,
topicid
int4 NOT NULL,
datetime
timestamp NOT NULL,
CONSTRAINT topic_pkey PRIMARY KEY (topicid)
);
CREATE INDEX topic_timestamp ON topic (datetime);
CREATE TABLE topiclang (
deleted
bool NOT NULL,
topicid
int4 NOT NULL,
languageid int4 NOT NULL,
title
varchar NOT NULL,
text text
NOT NULL,
CONSTRAINT topiclang_pkey PRIMARY KEY (languageid, topicid),
CONSTRAINT topiclang_topic FOREIGN KEY (topicid) REFERENCES topic
(topicid)
ON DELETE NO ACTION ON UPDATE CASCADE,
CONSTRAINT topic_lang FOREIGN KEY (languageid) REFERENCES
language (languageid) ON DELETE NO ACTION ON
UPDATE CASCADE
);
CREATE INDEX topiclang_title ON topiclang (title);
CREATE INDEX topiclang_text ON topiclang (text);
CREATE TABLE vaylakertomukset (
deleted
bool NOT NULL,
storyid
int4 NOT NULL,
CONSTRAINT vaylakertomukset_pkey PRIMARY KEY (storyid),
CONSTRAINT vayla_story FOREIGN KEY (storyid) REFERENCES story (storyid)
ON DELETE NO ACTION ON UPDATE CASCADE
);
CREATE TABLE accessright (
deleted
bool NOT NULL,
personid
int4 NOT NULL,
organisationid int4 NOT NULL,
accessrightlevelid int4 NOT NULL,
CONSTRAINT accessright_pkey
PRIMARY KEY (accessrightlevelid,
organisationid, personid),
CONSTRAINT accessright_person FOREIGN KEY (personid) REFERENCES
person (personid) ON DELETE NO ACTION ON
UPDATE CASCADE,
CONSTRAINT accessright_org FOREIGN KEY (organisationid) REFERENCES
organisation (organisationid) ON DELETE NO
ACTION ON UPDATE CASCADE,
CONSTRAINT
accessright_lvl FOREIGN KEY (accessrightlevelid)
REFERENCES accessrightlevel
(accessrightlevelid)
ON DELETE NO ACTION ON UPDATE CASCADE
);