laborid,pr2_et

PR2ET14: SQL

Labori materjal

Laboriülesanded

Selle labori käigus liidestame me enda C-keelse programmi SQLite3 andmebaasiga kasutades libsqlite3-dev  teeki. Esimese ülesande käigus alustad andmete lisamisest olemasolevasse andmebaasi, millele järgneb andmete pärimine teise ülesande raames.

Laboril on kaks edasijõudnute ülesannet, millest esimeses muudame andmete lisamist paremaks. Teises ülesandes tuleb uuendad juba andmebaasis olevaid andmeid.

Lae alla tunnitööks kasutatav andmebaas: study_information.db

Andmemudel

Ülesanne 1: Lisa iseennast andmebaasi

NB! Ülesanne võib osutuda mitte tehtavaks kui andmebaasi fail asetseb P kettal tänu Windows SMBle. Andmebaas jääb lukustatuks ning muudatuste lisamine ei tööta! Lahenduseks on töö ajaks andmebaasifail hoiustada  väljaspool P ketast, nt töölaual ning hiljem sinna kopeerida.

Selle ülesande raames harjutad INSERT päringuid andmete lisamiseks. Baasülesanne ei ole vajalik realiseerida C koodis. Kirjuta oma päringud ja jooksuta neid kasutades “Execute SQL” funktsiooni SQLite browser rakenduses.

Nõuded
  • Kirjuta üleskõik SQL päringud mida sa jooksutad (nt tavalise tekstidokumendina)! Näita jooksutatud päringuid ning näita oma andmebaasi pärast päringute tegemist. Kokku peab olema 5 päringut!
  • Tabeli primaarvõtmeks olevaid ID väärtusi (students.id , subjects.id , declarations.id ) ei tohi lisamispäringutesse sisse kirjutada! Need teeb andmebaas ise (id atribuut on andmebaasi disainis omadusega auto increment ning kõigi kolme tabeli id atribuudid omavad vastavat generaatorit)
  • Teosta järgmised andmete lisamise päringud
    • Lisa andmebaasi enda kui tudengi andmed
    • Lisa andmebaasi õppeaine, mille oled sooritanud
    • Lisa endale 3 aine deklaratsiooni. Üks nendest peab olema ainele, mille just lõid, lisaks 2 tk olemasolevatele.
  • Märkus: hinded ja isikukood ei pea olema reaalsed.

Kui andmed lisatud, näita enda tehtud päringud ning näita oma andmebaasi SQLite Browser rakenduse kaudu.

Ülesanne 2: Andmete pärimine

Selle ülesande raames tuleb sul kirjutada päringuid millega andmebaasis eksisteerivaid andmeid pärida. Kõik päringud peavad olema kirjutatud C-keelse programmi sisse. Sulle on antud 3 koodinäidet selle lehe alguses materjalide all. Vali milline neist on sulle kõige arusadavam ning kasuta seda mallina enda päringute kirjutamisel.

Ülesanne on jaotatud kolmeks eraldi hinnatavaks osaks.

Nõuded
  • Kõik päringud vastavas ülesande osas peavad olema sooritatud. Iga osa hinnatakse eraldi.
  • Andmed peavad olema täielikult ette valmistatud andmebaasihalduri poolel – st kõik arvutused (nt summa, keskmine, max jne) peavad olema kirjutatud SQL päringu sisse. Arvutusi C koodis ei tohi teha!
  • Kõik olulised andmeväljad tuleb väljastada. Ära väljasta identifikaatori väärtusi (nt subjects.id, declarations.id)
  • Päringuid võid jooksutada läbi menüü valikuliselt või ühes rakenduses üksteise järel. Sinu otsustada.
  • Kõik ressursid peavad olema programmi lõpuks vabastatud – kontrolli lekete puudumist valgrindiga.
Ülesanne 2 hinnatav osa 1

Esimeses osas teeme lihtsaid SELECT päringuid

Päring 1: Leia kõik õppeained, mis on vähem kui 6 EAPd

Päring 2: Leia kõik õppeaineid, milles on eksam. Järjesta tulemused EAPde arvu alusel vähimast suurimani.

Ülesanne 2 hinnatav osa 2

Teises osas harjutame veidi keerukamaid SELECT päringuid, kus tuleb kasutada JOIN märksõna ühe- või kahekordselt, et liita mitme tabeli vahelisi andmeid kasutades nende PK-FK võtmepaare.

Päring 1: Leia õppuri ‘Marko’ kõik hinded

Päring 2: Leia kõik õppeaineid mis sa oled sooritanud. Näita neid koos hinnetega, alustades kõrgeimast hindest.

Ülesanne 2 hinnatav osa 3

Kolmandas osas harjutame andmete koondamist tunnuste alusel ja tulemuste arvutamist oma SELECT päringutes. Meil on vaja nii JOIN kui GROUP BY märksõnu ning mõningaid arvutamise funktsioone.

Päring: Leia iga tudengi poolt teenitud EAPde arv ning keskmine hinne.

Edasijõudnute ülesanne 1: Enda andmebaasi lisamine (usaldusväärsemalt)

Esimese baasülesande raames ei olnud sul otseselt piiranguid kuidas ennast andmebaasi lisama peaks. Võisid vaadata mis ID väärtused tulid ning neid siis jooksvalt kasutada. Reaalses elus kasutatava andmebaasi puhul päris nii mõistlik teha ei oleks.

Taustinfot

Paremaks lahenduseks pakun välja 2 võimalust. Kummagi puhul pole tegu ideaalse lahendusega, kuid saame sammu võrra paremuse poole.

Võimalus 1a: SQL toetab alampäringuid – me saame ühe päringu sisse peita teise päringu ja selle tulemust siis kasutada esimeses päringus. Näiteks me saame INSERT päringu sisse kirjutada SELECT päringu.

Oletame, et tahame oma näidisandmebaasi lisada isikule Marko Mets veel ühte autot. Selleks saaksime jooksutada järgmise päringu.

Antud näide pole veatu – näiteks kui meil oleks 2 isikut kelle nimi on Marko Mets, siis läheks see katki. Sellest parem versioon on 1b, mille võid esitada laboriülesandena.

Võimalus 1b: Sarnaselt eelmise võimalusega saad teha sarnase päringu eesti isikukoodi põhjal. Tegu ei ole garanteeritult eksisteeriva väärtuse ega ei pruugi ka unikaalsust garanteerida kui välisriigi isikukoodid sekka tuua aga on parem kui eelmine.

Võimalus 2: Parem viis sellele läheneda oleks teha mitu päringut. Esiteks lisaksime end tudengina andmebaasi. Seejärel päriksime andmebaasilt mis meie ID väärtuseks sai kasutades  SELECT last_insert_rowid();  päringut. See annab meile tagasi automaatselt genereeritud ID väärtuse. Seejärel saaksime juba kasutada seda ID väärtust järgnevate andmete lisamiseks

Ka see pole veatu ega ideaalne. Näiteks kui meie kahe päringu vahel lisatakse mõne teise klientrakenduse poolt uus õppur, siis saaksime tagasi vale koodi (race condition). Seda lahendatakse tegelikus maailmas mehhanismiga TRANSACTION, mis paneb baasi lukku päringute ajaks teistele – ehk siis kõik või mitte midagi meetod. Küll aga kuna meil on kohalik failipõhine andmebaas, siis me saame sellest täna mööda vaadata.

Nõuded
  • Kirjuta enda andmebaasi lisamise päringud C keelse rakenduse sisse.
  • Kui lisamise koodi jooksutatakse mitu korda siis tuleb veenduda, et duplikaate ei tekiks (kontrolli kasutades mõnda teadatuntud unikaalset väärtust – nt kas sinu eID juba eksisteerib).
  • Pead kasutama automaatselt genereeritud ID väärtusi. Ühtegi ID väärtust ei tohi koodi jäigalt sisse kodeerida.
    • Kasuta ühte eelnevalt välja pakutud meetoditest.

Edasijõudnute ülesanne 2 : Olemasolevate andmete muutmine

Andmete muutmiseks andmebaasis saame kasutada UPDATE päringuid. Selliste päringute jaoks peame esmalt tuvastama millist rida me uuendada soovime ja seejärel saame kirjutada sisse uuendatud väärtused. Oluline on, et tuvastaksime ainult selle või need read, mida me peameuuendama. Ülesande raames pead genereerima kõigile tudengitele Uni-ID tunnused.

Nõuded
  • Genereeri kõigile tudengitele Uni-ID tunnused
  • Uni-IDd tuleb lisada andmebaasi kasutades UPDATE lauset. Täita tuleb uni_id atribuut.
  • Kood peab töötama ükskõik kui paljude tudengite puhul
  • Kui genereeritav Uni-ID juba eksisteerib, pead tegema konfliktilahendust. Selle jaoks peaksid kirjutama vastava koodi C keeles.

Pärast seda tundi peaksid

Täiendav materjal