Tietokantasovellus kurssien palautekyselyjen käsittelemiseen.
Tämä työ on
Tietokannat -kurssin esimerkkiharjoitustyö.
© Tommi Lahtonen (tommi.j.lahtonen@jyu.fi)
Access 97 version tietokannasta voi kopioida tutkittavakseen osoitteesta http://www.jyu.fi/tt-appro/2000/tietokannat/harkka/malli.zip (120 kB)
Tietokantasovelluksella on tarkoitus pystyä helposti laatimaan kurssipalautekyselyjä. Tietokantaan kerätään kysymyspankkia, josta voi aina valita kurssille sopivat kysymykset tai luoda uusia kysymyksiä. Varsinainen vastausten kerääminen suoritetaan WWW-lomakkeella. Lomakkeen luominen automaattisesti tietokannan pohjalta on vielä suunitteluasteella.
Lomakkeella kerätyt tiedot importoidaan tietokantaan ja niistä saadaan tehtyä helpot yleiset yhteenvetoraportit. Tietokantasovellus mahdollistaa vastaustietojen ryhmittelyn minkä tahansa kysymyksen vastausten perusteella.
Tietokannan rakenne on määritelty kokonaisuudessaan SQL-kielellä. Vastaukset-taulun
luomisessa on jouduttu käyttämään Accessin omaa MEMO-tietotyyppiä. SQL-lauseisiin
on jälkikäteen lisätty tarkemmat viite-eheysmäärittelyt joita Access ei tue (ON DELETE ja ON UPDATE -määritykset).
CREATE TABLE Kurssit (
Kurssikoodi CHAR(6) NOT NULL,
Nimi VARCHAR(100) NOT NULL,
OV NUMERIC NOT NULL,
CONSTRAINT Kurssit_PK
PRIMARY KEY (kurssikoodi)
)
CREATE TABLE Kysymystyypit (
KysymystyyppiID INTEGER NOT NULL,
Kysymystyyppi VARCHAR(100) NOT NULL,
CONSTRAINT Kysymystyypit_PK
PRIMARY KEY (KysymystyyppiID)
)
CREATE TABLE Vaihtoehdot (
VaihtoehtoID INTEGER NOT NULL,
Vaihtoehto VARCHAR(100) NOT NULL,
CONSTRAINT Vaihtoehdot_PK
PRIMARY KEY (VaihtoehtoID)
)
CREATE TABLE Kysymykset (
KysymysID INTEGER NOT NULL,
Kysymys VARCHAR(100) NOT NULL,
Kysymystyyppi INTEGER NOT NULL,
CONSTRAINT Kysymykset_PK
PRIMARY KEY (kysymysID),
CONSTRAINT kysymykset_fk_kysymystyyppi
FOREIGN KEY (Kysymystyyppi)
REFERENCES Kysymystyypit (KysymystyyppiID)
ON DELETE NO ACTION
ON UPDATE CASCADE
)
CREATE TABLE Kurssikysymykset (
Kurssi CHAR(6) NOT NULL,
Kysymys INTEGER NOT NULL,
CONSTRAINT Kurssikysymykset_PK
PRIMARY KEY (Kurssi, Kysymys),
CONSTRAINT Kurssikysymykset_fk_kurssi
FOREIGN KEY (kurssi)
REFERENCES Kurssit (Kurssikoodi),
ON DELETE CASCADE
ON UPDATE CASCADE
CONSTRAINT Kurssikysymykset_fk_kysymykset
FOREIGN KEY (Kysymys)
REFERENCES Kysymykset (KysymysID)
ON DELETE CASCADE
ON UPDATE CASCADE
)
CREATE TABLE Kysymysvaihtoehdot (
Kysymys INTEGER NOT NULL,
Vaihtoehto INTEGER NOT NULL,
VaihtoehtoNro INTEGER NOT NULL,
CONSTRAINT Kysymysvaihtoehdot_PK
PRIMARY KEY (Kysymys, Vaihtoehtonro),
CONSTRAINT Kysymysvaihtoehdot_fk_kysymykset
FOREIGN KEY (Kysymys)
REFERENCES Kysymykset (KysymysID),
ON DELETE NO ACTION
ON UPDATE CASCADE
CONSTRAINT Kysymysvaihtoehdot_fk_vaihtoehto
FOREIGN KEY (Vaihtoehto)
REFERENCES Vaihtoehdot (VaihtoehtoID)
ON DELETE NO ACTION
ON UPDATE CASCADE
)
CREATE TABLE Vastaukset (
Vastaaja INTEGER NOT NULL,
Kurssi CHAR(6) NOT NULL,
Kysymys INTEGER NOT NULL,
Vastaus INTEGER NULL,
Tekstivastaus MEMO NULL,
CONSTRAINT Vastaukset_PK
PRIMARY KEY (Vastaaja, Kurssi, Kysymys),
CONSTRAINT Vastaukset_fk_kysymykset
FOREIGN KEY (Kysymys, Vastaus)
REFERENCES Kysymysvaihtoehdot (Kysymys, Vaihtoehtonro),
ON DELETE NO ACTION
ON UPDATE CASCADE
CONSTRAINT Vastaukset_fk_Kurssikysymykset
FOREIGN KEY (Kurssi, kysymys)
REFERENCES Kurssikysymykset (Kurssi, kysymys)
ON DELETE CASCADE
ON UPDATE CASCADE
)
CREATE INDEX Kurssikysymykset_kurssi ON Kurssikysymykset (Kurssi)
CREATE INDEX Kurssikysymykset_kysymys ON Kurssikysymykset (Kysymys)
CREATE INDEX Kysymykset_kysymystyyppi ON Kysymykset (Kysymystyyppi)
CREATE INDEX Kysymysvaihtoehdot_Kysymys ON Kysymysvaihtoehdot (Kysymys)
CREATE INDEX Kysymysvaihtoehdot_Vaihtoehtonro ON Kysymysvaihtoehdot (Vaihtoehtonro)
CREATE INDEX Vastaukset_Kurssi ON Vastaukset (Kurssi)
CREATE INDEX Vastaukset_Kurssi ON Vastaukset (Kurssi)
CREATE INDEX Vastaukset_Vastaus ON Vastaukset (Vastaus)
Jakaa vastaajat halutun kysymyksen (Forms!Raportit!Kysymys) pohjalta. Tämän kyselyn pohjalta voidaan tehdä tarkempi ryhmittely käyttäen ryhmittelyehtona mitä tahansa kysymystä (kts. Vapaa_ryhmittely ja Vapaa_ryhmittely2).
SELECT VA.Vastaaja, VA.Vastaus AS ryhma, VE.Vaihtoehto AS Selitys, KY.Kysymys
FROM Vastaukset AS VA, Kysymysvaihtoehdot AS K, Vaihtoehdot AS VE, Kysymykset AS KY
WHERE VA.kysymys = Forms!Raportit!Kysymys
AND VA.Kurssi = Forms!Raportit!Kurssikoodi
AND VA.Kysymys = K.kysymys
AND KY.KysymysID = K.Kysymys
AND VA.vastaus = K.Vaihtoehtonro
AND K.Vaihtoehto = VE.VaihtoehtoID
AND K.Vaihtoehto <> 0;
Käyttää edellistä kyselyä hyväkseen ja ryhmittelee vastaukset sen mukaan. Selkeyden vuoksi kysely on jaettu kahteen osaan (Vapaa_ryhmittely ja Vapaa_ryhmittely2). Tuloksien pyöristykseen on jouduttu käyttämään Accessin epästandardeja funktioita.
SELECT J.selitys, K.KysymysID, K.Kysymys AS Kysymys, CInt(Format(AVG(vastaus), "###0")) AS keskiarvo, Format(AVG(Vastaus), "###0.00") AS T, COUNT(Vastaus) AS lkm
FROM Vastaukset AS VA, Kysymykset AS K, Jaottelu AS J
WHERE VA.Kysymys = K.kysymysID
AND J.vastaaja = VA.vastaaja
AND Vastaus <> 0
GROUP BY J.selitys, K.KysymysID, K.Kysymys
ORDER BY K.KysymysID, K.Kysymys;
Hakee edellisen pohjalta vielä sanalliset vastineet vastauksien keskiarvoille.
SELECT Selitys, kysymysID, Kysymys, keskiarvo, CDbl(T) AS Tarkka, Lkm, V.vaihtoehto
FROM Vapaa_ryhmittely AS R, vaihtoehdot AS V, kysymysvaihtoehdot AS K
WHERE R.keskiarvo = K.vaihtoehtonro
AND K.kysymys = R.kysymysID
AND K.vaihtoehto = V.VaihtoehtoID
ORDER BY R.KysymysID, R.Selitys;
Listaa tiettyyn kurssiin liittyvät kurssikysymykset. Käytetään lomakkeissa.
SELECT Kurssi, Nimi, Kurssikysymykset.Kysymys, Kysymykset.Kysymys
FROM Kurssikysymykset, Kurssit, Kysymykset
WHERE kurssikysymykset.kurssi = kurssit.kurssikoodi
AND Kurssikysymykset.Kysymys = Kysymykset.KysymysID
AND Kurssit.Kurssikoodi = [Forms]![Vastaukset]![Kurssikoodi];
Tekee ryhmittelyn kysymyskohtaisesti. Käytetty Accessin epästandardeja funktioita.
SELECT K.KysymysID, K.Kysymys AS Kysymys, CInt(Format(AVG(vastaus), "###0")) AS keskiarvo, Format(AVG(Vastaus), "###0.00") AS Tarkka, COUNT(Vastaus) AS lkm
FROM Vastaukset AS VA, Kysymykset AS K
WHERE VA.Kysymys = K.kysymysID
AND Vastaus <> 0
AND VA.Kurssi = Forms!Raportit!Kurssikoodi
GROUP BY K.KysymysID, K.Kysymys
ORDER BY K.KysymysID, K.Kysymys;
Hakee edellisen perusteella vielä tekstivastineet vastausten keskiarvoille.
SELECT R.Kysymys, V.Vaihtoehto, R.Keskiarvo, R.Tarkka, R.lkm
FROM ryhmittely AS R, vaihtoehdot AS V, kysymysvaihtoehdot AS K
WHERE R.keskiarvo = K.vaihtoehtonro
AND K.kysymys = R.kysymysID
AND K.vaihtoehto = V.VaihtoehtoID;
Tekee listauksen tekstivastauksista kysymyksittäin ryhmiteltyinä.
SELECT Vastaukset.Vastaaja, Kysymykset.Kysymys, Vastaukset.Tekstivastaus
FROM Vastaukset, Kysymykset
WHERE Vastaukset.Kurssi = Forms!Raportit!Kurssikoodi
AND Vastaukset.Kysymys = Kysymykset.KysymysID
AND TekstiVastaus <> '0'
ORDER BY Kysymykset.KysymysID, Vastaukset.Vastaaja;
Listaa tiettyyn kysymykseen liittyvät vaihtoehdot.
SELECT KV.Vaihtoehtonro, V.Vaihtoehto
FROM Kysymysvaihtoehdot AS KV, Vaihtoehdot AS V
WHERE KV.Kysymys = [Forms]![Vastaukset]![Vastaukset_sub]![Kysymys]
AND KV.Vaihtoehto = V.VaihtoehtoID;
Listaa kaikki vastaukset vastaajittain ryhmiteltyinä.
SELECT Vastaukset.Vastaaja, Kysymykset.Kysymys, Vaihtoehdot.Vaihtoehto, Vastaukset.Tekstivastaus
FROM Vastaukset, Kysymykset, Kysymysvaihtoehdot, Vaihtoehdot
WHERE Vastaukset.Kysymys = Kysymykset.KysymysID
AND Kysymysvaihtoehdot.Kysymys = Kysymykset.KysymysID
AND Vastaukset.Kurssi = Forms!Raportit!Kurssikoodi
AND Vastaukset.Kysymys = Kysymysvaihtoehdot.Kysymys
AND Vaihtoehdot.VaihtoehtoID = Kysymysvaihtoehdot.Vaihtoehto
AND Vastaukset.Vastaus = Kysymysvaihtoehdot.Vaihtoehtonro
ORDER BY Vastaukset.vastaaja, Kysymykset.KysymysID;
Sovellus käynnistyy Aloitus-nimisellä lomakkeella, josta pääsee edelleen siirtymään muille tarvittaville lomakkeille. Lomakkeilla on lyhyesti selitetty niiden toimintaa. Sovellus on tarkoitettu lähinnä vain raportointiin, joten käyttöliittymään ei ole kiinnitetty suurta huomiota.
Halutunlaisten kyselyjen tekeminen tuotti päänvaivaa. Ratkaisuksi muodostui yksi ylimääräinen apukysely, joka tuotti jokaista vastaajaa kohti ryhmittelytiedon jonkun halutun kysymyksen mukaan. Tämän kyselyn avulla voidaan tuottaa raportteja minkä kysymyksen tahansa mukaan ryhmiteltynä riippumatta siitä onko kyseessä järkevä ryhmittely vai ei. Pallo on käyttäjällä.
Erityisiä ongelmia aiheutti järkevän vastaukset-lomakkeen luominen. Fiksua olisi ollut näyttää kaikki yhden tietyn vastaajan vastaukset kerralla. En keksinyt miten olisin pystynyt listaamaan vastausvaihtoehdoista alilomakkeella vain ne, jotka liittyvät aina käsiteltyyn kysymykseen. Access kyllä suostuu muuttamaan vaihtoehtolistausta aina kysymystä vaihdettaessa, mutta muutos tapahtuu kaikissa vastausvaihtoehtocombobokseissa eikä vain aktiivisessa. Jouduin muuttamaan alilomaketta niin, että se näyttää vain yhden kysymys ja vastausyhdistelmän kerralla.