Mitmetabelilise andmebaasi loomine ja sellesse andmete tõmbamine tekstifailidest

 

Teeme PostgreSQL keskkonda uue andmebaasi Randaja, mis koosneb kaheksast tabelist (olemist). Ette on antud loogiline andmebaasimudel, mida läheb vaja tabelite vaheliste seoste mõistmiseks. Nendele seostele tuginedes õpime hiljem mitut tabelit hõlmavaid päringuid koostama.

mudel

 

 

Andmebaas on disainitud andmete hoidmiseks 19. sajandi lõpus ja 20. sajandi alguses Venemaale erinevatesse kohtadesse välja rännanud leibkondade kohta. Teada on leibkonda kuulunud inimesed ja terve rida andmeid nende kohta, mis summale oli hinnatud leibkonnale kuuluv vara, uude elukohta ümberkirjutamise aeg, senine elukoht ja ka sihtkoht. Elukoht on jagatud eraldi tabeliteks (olemiteks) seepärast, et andmeid hõlpsamini hallata, see on andmebaasi tehnilisest aspektist kõige korrektsem viis (välditud on kordused) mitmetasandilise kohainfo talletamiseks. Sihtkoha andmed on koos ühes olemis, sest kohtade omavaheline hierarhia on ebaselge ja erinevate administratiivüksuste varieeruvus on suurem.

 

Andmemudel on tehtud mudeldamistööriistaga DB Designer (https://www.dbdesigner.net/). See on veebipõhine tarkvara, millest mudeli koostamise järel saab eksportida SQL käsud PostgreSQL rakenduses andmebaasi tabelite loomiseks. Tabelitesse on seejärel võimalik andmed failidest importida.

 

Kuna tahame, et DB Designerist saaks SQL käsud importida, siis on oluline, et kõik andmetüübid ja võimalikud kitsendused (primaarvõtmed (primary key), tunnuse väärtuse automaatne täitmine, unikaalsus, kas tunnusel võib ka väärtus puududa, nt kõigil isikutel ei ole märgitud isanime ) saaksid korrektsed. Hiljem saab kõike küll ka PostgreSQLis muuta, aga vea avastamine võib võtta aega. DB Designeri vaikimisi säte on, et ei lubata olukorda, kus tunnuse väärtus puudub (NULL), see tähendab, et väljal peab alati mingi info olema, et lubataks andmed salvestada. Selleks, et mõne olemi eksemplari puhul võiks väli ka tühjaks jääda, peab seda eraldi linnukesega märkima iga tunnuse juures (allow nulls).

 

nulls

Andmebaasi loomine

Loo PostgreSQLi graafilises kasutajaliideses pgAdmin uus andmebaas.

Create

 

 

Pane sellele nimeks Randaja. Loome tabelid DB Designerist saadud SQL käskudega. Saad need alla laadida siit.

Ava see fail ja kopeeri sealt kõik CREATE TABLE käsud, aga mitte veel faili lõpus olevaid ALTER TABLE käske, nendega lisame pärast välisvõtmed.

 

Ava PostgreSQL Query Tool ja kleebi kopeeritud käsud sinna.

Query tool

 

Enne käivitamist vaata kõik SQL käsud üle, kas väljanimed on nii nagu soovitud ja andmetüübid korrektsed ning NOT NULL märgitud ainult nende väljade kohta, mis kindlasti kunagi ei jää tühjaks. Näiteks on meil jäänud tabelis „Isik“ tunnuse „sugu“ kitsenduseks NOT NULL, aga oletame, et mõnikord ei ole inimese eesnime järgi võimalik tema sugu määrata ja tahame andmed salvestada ka juhul kui ei ole seda välja võimalik kohe täita. Selle lubamiseks tuleks NOT NULL tunnuse „sugu“ järelt ära kustutada.

 

Query Editor

 

Siin saaks muuta ka andmetüüpe. Need on aga juba sellised nagu vaja.

Sünniaja andmetüübiks on date, see sobib ainult siis kui sünniaeg sisaldab nii päeva, kuud kui aastat.  Kui on ainult aastaarv teada, siis tuleks andmetüübiks määrata integer, mis sobib kõigi täisarvude puhul. Kuna sihtkohta ümberkirjutamise kohta on teada ainult aasta, siis tunnuse ymberkirjutus andmetüübiks sobibki kõige paremini integer.

Vanus võib olla mõnes allikas märgitud komaga, seepärast on andmetüübiks numeric. Sobiks ka  real, mõlemad lubavad sisestada komaga arve (nagu nt 47.5).

Vanus ja sünniaeg on eraldi tunnustena seepärast, et mõnes allikas on kirjas vanused, mõnes sünniajad. Need eeldavad erinevaid andmetüüpe. Kui paneksime nii vanuse kui sünniaja info samale väljale, peaksime määrama andmetüübiks text, siis käituksidki nii sünniajad kui vanused tekstina ja andmetega ei saaks teha tehteid, nt arvutada keskmist vanust või sünniaja põhjal inimese vanust.   

Kui kõik on korras kõigi tabelite loomise andmetüüpide ja kitsendustega, siis jooksuta käsud korraga läbi. Vasakule sirvimispuusse tekkivad tabelite alla kõik kaheksa tabelit ja igas tabelis olevad tunnused.

Andmete importimine

Nüüd on kõik valmis andmete tabelisse importimiseks.

Seda saab nii SQL käskude abil kui pgAdmin graafilises liideses.

Proovime esmalt pgAdmin liideses. Importimiseks vajalikud andmed saad lahtipakkimiseks alla laadida siit. Jäta meelde, kuhu andmed alla laadisid. Andmete importimisel on oluline, et andmed oleksid salvestatud utf-8 vormingus, see tagab, et andmetes olevad täpitähed ei saa moonutatud. Importida saab nii csv kui txt faile. Oluline on teada, mis on neis failides andmete eraldajaks. Meie imporditavatel andmetel on eraldajaks semikoolon.

Import

 

Importimiseks klõpsa pgAdminis vasakul serval olevas sirvimispuus selle tabeli nimel, millesse tahad andmeid tõmbama hakata. Laeme kõigepealt andmed tabelisse Isik. Paremklõps tabeli nimel ning Import/Export.

Import/Export

 

 

Klõpsa sisselülitatuks Import/Export liugur kindlasti asendisse Import (vaikimisi on Export). Otsi üles andmeid sisaldav fail sealt kuhu selle lahti pakkisid. Encoding peab olema UTF8. Kuna meil on tabelil veeru nimed, lülita Päise (Header) liugur „Yes“ asendisse. Delimiter peab vastama failis kasutatud eraldajale, meil on see semikoolon. Quote ja Escape jäägu nii nagu on. Ära klõpsa veel OK nuppu.

import/export

 

Vali ülevalt menüüst teine sakk Columns.

Siin saab määratleda kõik veerud, mida tahad importida. Peaks küll olema nii, et kui ei märgi ühtegi tunnust/veergu, siis imporditakse kõik. Aga sellisel juhul ei tea PostreSQL, mis järjekorras tuleks tunnused eksportida. Seepärast tuleks pakutud tunnuste järjekord kustutada ja tunnused uuesti sisestada. Tunnused peab sisestama üleslaetava faili veergude järjekorras. Kõige hõlpsam on seda teha nii, et hakkad tippima tunnuse nime ja kui see välja pakutakse klõpsad ENTER.

Andmete importimine kulgeks siis sujuvalt kui andmemudeli tunnuste järjekord vastaks kohe imporditava tabeli tunnuste järjekorrale. See on kõige lihtsam variant, seepärast proovime läbi keerulisema juhtumi, kus tunnused on neis erinevas järjekorras.

Tunnused

 

Tee kõigi teiste tabelitega sama.

 

Andmed saab importida ka SQL käsuga. Selleks saab kasutada käsku COPY. Proovime seda tabeliga Maakond. SQL lauses peame samuti nimetama tabeli, kuhu andmed kopeerime (public.“Maakond“) ja õiges järjekorras kõik väljad, mida kopeerime, nimetama ka andmete eraldaja:

COPY public."Maakond" (maakond_id, nimi)

FROM 'C:\Users\Kadri\Documents\randaja\maakond.csv' HEADER CSV DELIMiTER ';';

See käsk aga ei tööta päringutööriistas (Query Tool), sest fail ei asu samas serveris ja PostgreSQLil pole luba seda avada.

Error

 

PostgreSQLil on ka interaktiivne terminal psql andmebaasiga töötamiseks. See ongi veateates viidatud klient rakendus. Selle käivitamiseks toksi oma arvutis start menüü kõrval olevasse aknasse psql ja ava pakutav rakendus SQL Shell (psql).

Enda andmebaasi sisenemiseks täida ükshaaval küsitavad väljad ja vajuta iga sisestuse järel ENTER. Pordi väli jäta tühjaks. Password on seesama postgreSQL keskkonna password, millega logid ka pgAdmini.

Shell 1

 

Käsk, millega saab kopeerida andmed csv failist „Maakond“ vastavasse tabelisse on selline:

\COPY public."Maakond" (maakond_id, nimi) FROM 'C:\Users\Kadri\Documents\randaja\maakond.csv' HEADER CSV DELIMiTER ';' ENCODING 'UTF8';

COPY tabeli nimi (tunnuste/veergude nimed) FROM ’faili asukoht Sinu arvutis ülakomade vahel’ info faili kohta;

Erinevalt pgAdmini päringuaknast tuleb psql terminalis kirjutada käsk ühe reana, muidu käsitletakse seda kahe erineva käsuna. (Kui sql lause on pikk ja ei mahu ära, siis see kohandub ise).

Shell 2 

 

Kui kuvatakse tulemuseks COPY 5, siis teame, et viis rida kopeeriti meie tabelisse ära.

 

Välisvõtmete lisamine

Kui andmed on üles laetud, saame lisada seosed (välisvõtmed) andmebaaside vahel. Võimalik oleks teha seda enne andmete importimist. Sellisel juhul peaks hoolikalt jälgima tabelite üleslaadimise järjekorda, sest välisvõtme kitsendus ei luba näiteks laadida andmeid tabelisse Isik kui tabelis Leibkond ei ole veel andmeid, sest üleslaadimisel kontrollitakse, et Leibkond tabeli tunnuse leibkond_id väärtustes leiduks vaste tabeli Isik tunnuse leibkond väärtustele.

Selleks, et luua seos kahe tabeli vahel, tuleb lisada välisvõtmed „Foreign Key“.

Ühe tabeli (olemi) primaarvõti (või ka mõni muu unikaalne tunnus) läheb teise olemi välisvõtmeks. Võti lisatakse selle olemi juurest, mille mitu eksemplari võivad olla seotud ühe eksemplariga teises olemis. Nt mitu isikut kuuluvad ühte leibkonda, järelikult tabelite isik ja leibkond vahel seose loomiseks peab tabelis Isik üks tunnustest olema tabeli Leibkond tunnuse leibkond_id väärtuste talletamiseks, et saaks need tabelid omavahel siduda. Isik tabelis võib tunnusel olla teine nimi, näiteks andmebaasi Randaja on Isik tabelis selle tunnuse nimi „leibkond“, aga see sisaldab samu väärtusi, mis tabelis Leibkond tunnus „leibkond_id“ ja välisvõtet lisades sisuliselt lisame kitsenduse, et Isik tabeli välja leibkond väärtused peavad leidma vastavuse tabeli Leibkond välja leibkond_id väärtustega.

Dbdesigneris on see lahendatud võimalusena viidata teisele tabelile.

 

 

VõtmedVõtmed2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Kuna välisvõtmed on lisatud DB Designeris, siis on võtmete lisamise käsud olemas sealt eksporditud tabelite loomise sql faili lõpus. Kopeeri faili lõpust kõik ALTER TABLE käsud ja jooksuta need pgAdmini päringute aknas (Query Tool) läbi. 

Võtmeid saab lisada ka PostgreSQL pgAdmin liideses. Tehniliselt on tegu kitsenduste (constraints) seadmisega.

 

Välisvõtmete olemasolu saab kontrollida sirvimispuust, kus need tekkivad kitsendustena (constraints). Kuldne on primaarvõti, hõbedane välisvõti. 

Võtmed