{"id":14,"date":"2024-04-04T06:16:28","date_gmt":"2024-04-04T03:16:28","guid":{"rendered":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/mitmetabelilise-andmebaasi-loomine-ja-sellesse-andmete-tombamine-tekstifailidest\/"},"modified":"2024-04-04T06:16:44","modified_gmt":"2024-04-04T03:16:44","slug":"mitmetabelilise-andmebaasi-loomine-ja-sellesse-andmete-tombamine-tekstifailidest","status":"publish","type":"page","link":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/mitmetabelilise-andmebaasi-loomine-ja-sellesse-andmete-tombamine-tekstifailidest\/","title":{"rendered":"Mitmetabelilise andmebaasi loomine ja sellesse andmete t\u00f5mbamine tekstifailidest"},"content":{"rendered":"

\n\t\u00a0\n<\/p>\n

\n\tTeeme PostgreSQL keskkonda uue andmebaasi Randaja, mis koosneb kaheksast tabelist (olemist). Ette on antud loogiline andmebaasimudel, mida l\u00e4heb vaja tabelite vaheliste seoste m\u00f5istmiseks. Nendele seostele tuginedes \u00f5pime hiljem mitut tabelit h\u00f5lmavaid p\u00e4ringuid koostama.\n<\/p>\n

\n\t\"mudel\"\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tAndmebaas on disainitud andmete hoidmiseks 19. sajandi l\u00f5pus ja 20. sajandi alguses Venemaale erinevatesse kohtadesse v\u00e4lja r\u00e4nnanud leibkondade kohta. Teada on leibkonda kuulunud inimesed ja terve rida andmeid nende kohta, mis summale oli hinnatud leibkonnale kuuluv vara, uude elukohta \u00fcmberkirjutamise aeg, senine elukoht ja ka sihtkoht. Elukoht on jagatud eraldi tabeliteks (olemiteks) seep\u00e4rast, et andmeid h\u00f5lpsamini hallata, see on andmebaasi tehnilisest aspektist k\u00f5ige korrektsem viis (v\u00e4lditud on kordused) mitmetasandilise kohainfo talletamiseks. Sihtkoha andmed on koos \u00fches olemis, sest kohtade omavaheline hierarhia on ebaselge ja erinevate administratiiv\u00fcksuste varieeruvus on suurem.\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tAndmemudel on tehtud mudeldamist\u00f6\u00f6riistaga DB Designer (https:\/\/www.dbdesigner.net\/<\/a>). See on veebip\u00f5hine tarkvara, millest mudeli koostamise j\u00e4rel saab eksportida SQL k\u00e4sud PostgreSQL rakenduses andmebaasi tabelite loomiseks. Tabelitesse on seej\u00e4rel v\u00f5imalik andmed failidest importida.\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tKuna tahame, et DB Designerist saaks SQL k\u00e4sud importida, siis on oluline, et k\u00f5ik andmet\u00fc\u00fcbid ja v\u00f5imalikud kitsendused (primaarv\u00f5tmed (primary key), tunnuse v\u00e4\u00e4rtuse automaatne t\u00e4itmine, unikaalsus, kas tunnusel v\u00f5ib ka v\u00e4\u00e4rtus puududa, nt k\u00f5igil isikutel ei ole m\u00e4rgitud isanime ) saaksid korrektsed. Hiljem saab k\u00f5ike k\u00fcll ka PostgreSQLis muuta, aga vea avastamine v\u00f5ib v\u00f5tta aega. DB Designeri vaikimisi s\u00e4te on, et ei lubata olukorda, kus tunnuse v\u00e4\u00e4rtus puudub (NULL), see t\u00e4hendab, et v\u00e4ljal peab alati mingi info olema, et lubataks andmed salvestada. Selleks, et m\u00f5ne olemi eksemplari puhul v\u00f5iks v\u00e4li ka t\u00fchjaks j\u00e4\u00e4da, peab seda eraldi linnukesega m\u00e4rkima iga tunnuse juures (allow nulls).\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\"nulls\"\n<\/p>\n

\n\tAndmebaasi loomine<\/strong>\n<\/p>\n

\n\tLoo PostgreSQLi graafilises kasutajaliideses pgAdmin uus andmebaas.\n<\/p>\n

\n\t\"Create\"\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tPane sellele nimeks Randaja. Loome tabelid DB Designerist saadud SQL k\u00e4skudega. Saad need alla laadida siit<\/a>.\n<\/p>\n

\n\tAva see fail ja kopeeri sealt k\u00f5ik CREATE TABLE k\u00e4sud, aga mitte veel faili l\u00f5pus olevaid ALTER TABLE k\u00e4ske, nendega lisame p\u00e4rast v\u00e4lisv\u00f5tmed.\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tAva PostgreSQL Query Tool ja kleebi kopeeritud k\u00e4sud sinna.\n<\/p>\n

\n\t\"Query\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tEnne k\u00e4ivitamist vaata k\u00f5ik SQL k\u00e4sud \u00fcle, kas v\u00e4ljanimed on nii nagu soovitud ja andmet\u00fc\u00fcbid korrektsed ning NOT NULL m\u00e4rgitud ainult nende v\u00e4ljade kohta, mis kindlasti kunagi ei j\u00e4\u00e4 t\u00fchjaks. N\u00e4iteks on meil j\u00e4\u00e4nud tabelis \u201eIsik\u201c tunnuse \u201esugu\u201c kitsenduseks NOT NULL, aga oletame, et m\u00f5nikord ei ole inimese eesnime j\u00e4rgi v\u00f5imalik tema sugu m\u00e4\u00e4rata ja tahame andmed salvestada ka juhul kui ei ole seda v\u00e4lja v\u00f5imalik kohe t\u00e4ita. Selle lubamiseks tuleks NOT NULL tunnuse \u201esugu\u201c j\u00e4relt \u00e4ra kustutada.\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\"Query\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tSiin saaks muuta ka andmet\u00fc\u00fcpe. Need on aga juba sellised nagu vaja.\n<\/p>\n

\n\tS\u00fcnniaja andmet\u00fc\u00fcbiks on date<\/b>, see sobib ainult siis kui s\u00fcnniaeg sisaldab nii p\u00e4eva, kuud kui aastat.\u00a0 Kui on ainult aastaarv teada, siis tuleks andmet\u00fc\u00fcbiks m\u00e4\u00e4rata integer<\/b>, mis sobib k\u00f5igi t\u00e4isarvude puhul. Kuna sihtkohta \u00fcmberkirjutamise kohta on teada ainult aasta, siis tunnuse ymberkirjutus andmet\u00fc\u00fcbiks sobibki k\u00f5ige paremini integer.\n<\/p>\n

\n\tVanus v\u00f5ib olla m\u00f5nes allikas m\u00e4rgitud komaga, seep\u00e4rast on andmet\u00fc\u00fcbiks numeric. <\/b>Sobiks ka\u00a0 real, m\u00f5lemad lubavad sisestada komaga arve (nagu nt 47.5).\n<\/p>\n

\n\tVanus ja s\u00fcnniaeg on eraldi tunnustena seep\u00e4rast, et m\u00f5nes allikas on kirjas vanused, m\u00f5nes s\u00fcnniajad. Need eeldavad erinevaid andmet\u00fc\u00fcpe. Kui paneksime nii vanuse kui s\u00fcnniaja info samale v\u00e4ljale, peaksime m\u00e4\u00e4rama andmet\u00fc\u00fcbiks text, siis k\u00e4ituksidki nii s\u00fcnniajad kui vanused tekstina ja andmetega ei saaks teha tehteid, nt arvutada keskmist vanust v\u00f5i s\u00fcnniaja p\u00f5hjal inimese vanust.\u00a0 \u00a0\n<\/p>\n

\n\tKui k\u00f5ik on korras k\u00f5igi tabelite loomise andmet\u00fc\u00fcpide ja kitsendustega, siis jooksuta k\u00e4sud korraga l\u00e4bi. Vasakule sirvimispuusse tekkivad tabelite alla k\u00f5ik kaheksa tabelit ja igas tabelis olevad tunnused.\n<\/p>\n

\n\tAndmete importimine<\/strong>\n<\/p>\n

\n\tN\u00fc\u00fcd on k\u00f5ik valmis andmete tabelisse importimiseks.\n<\/p>\n

\n\tSeda saab nii SQL k\u00e4skude abil kui pgAdmin graafilises liideses.\n<\/p>\n

\n\tProovime esmalt pgAdmin liideses. Importimiseks vajalikud andmed saad lahtipakkimiseks alla laadida <\/span><\/span>siit<\/a>. J\u00e4ta meelde, kuhu andmed alla laadisid. Andmete importimisel on oluline, et andmed oleksid salvestatud utf-8 vormingus, see tagab, et andmetes olevad t\u00e4pit\u00e4hed 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. <\/span><\/span>\n<\/p>\n

\n\t\"Import\"\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tImportimiseks kl\u00f5psa pgAdminis vasakul serval olevas sirvimispuus selle tabeli nimel, millesse tahad andmeid t\u00f5mbama hakata. Laeme k\u00f5igepealt andmed tabelisse Isik<\/b>. Paremkl\u00f5ps tabeli nimel ning Import\/Export.\n<\/p>\n

\n\t\"Import\/Export\"\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tKl\u00f5psa sissel\u00fclitatuks Import\/Export liugur kindlasti asendisse Import (vaikimisi on Export). Otsi \u00fcles andmeid sisaldav fail sealt kuhu selle lahti pakkisid. Encoding peab olema UTF8. Kuna meil on tabelil veeru nimed, l\u00fclita P\u00e4ise (Header) liugur \u201eYes\u201c asendisse. Delimiter peab vastama failis kasutatud eraldajale, meil on see semikoolon. Quote ja Escape j\u00e4\u00e4gu nii nagu on. \u00c4ra kl\u00f5psa veel OK nuppu.\n<\/p>\n

\n\t\"import\/export\"\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tVali \u00fclevalt men\u00fc\u00fcst teine sakk Columns.\n<\/p>\n

\n\tSiin saab m\u00e4\u00e4ratleda k\u00f5ik veerud, mida tahad importida. Peaks k\u00fcll olema nii, et kui ei m\u00e4rgi \u00fchtegi tunnust\/veergu, siis imporditakse k\u00f5ik. Aga sellisel juhul ei tea PostreSQL, mis j\u00e4rjekorras tuleks tunnused eksportida. Seep\u00e4rast tuleks pakutud tunnuste j\u00e4rjekord kustutada ja tunnused uuesti sisestada. Tunnused peab sisestama \u00fcleslaetava faili veergude j\u00e4rjekorras. K\u00f5ige h\u00f5lpsam on seda teha nii, et hakkad tippima tunnuse nime ja kui see v\u00e4lja pakutakse kl\u00f5psad ENTER.\n<\/p>\n

\n\tAndmete importimine kulgeks siis sujuvalt kui andmemudeli tunnuste j\u00e4rjekord vastaks kohe imporditava tabeli tunnuste j\u00e4rjekorrale. See on k\u00f5ige lihtsam variant, seep\u00e4rast proovime l\u00e4bi keerulisema juhtumi, kus tunnused on neis erinevas j\u00e4rjekorras.\n<\/p>\n

\n\t\"Tunnused\"\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tTee k\u00f5igi teiste tabelitega sama.\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tAndmed saab importida ka SQL k\u00e4suga. <\/b>Selleks saab kasutada k\u00e4sku COPY. Proovime seda tabeliga Maakond. SQL lauses peame samuti nimetama tabeli, kuhu andmed kopeerime (public.\u201cMaakond\u201c) ja \u00f5iges j\u00e4rjekorras k\u00f5ik v\u00e4ljad, mida kopeerime, nimetama ka andmete eraldaja:\n<\/p>\n

\n\tCOPY public.\u201dMaakond\u201d (maakond_id, nimi)\n<\/p>\n

\n\tFROM \u2018C:UsersKadriDocumentsrandajamaakond.csv\u2019 HEADER CSV DELIMiTER \u2018;\u2019;\n<\/p>\n

\n\tSee k\u00e4sk aga ei t\u00f6\u00f6ta p\u00e4ringut\u00f6\u00f6riistas (Query Tool), sest fail ei asu samas serveris ja PostgreSQLil pole luba seda avada.\n<\/p>\n

\n\t\"Error\"\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tPostgreSQLil on ka interaktiivne terminal psql andmebaasiga t\u00f6\u00f6tamiseks. See ongi veateates viidatud klient rakendus. Selle k\u00e4ivitamiseks toksi oma arvutis start men\u00fc\u00fc k\u00f5rval olevasse aknasse psql ja ava pakutav rakendus SQL Shell (psql).\n<\/p>\n

\n\tEnda andmebaasi sisenemiseks t\u00e4ida \u00fckshaaval k\u00fcsitavad v\u00e4ljad ja vajuta iga sisestuse j\u00e4rel ENTER. Pordi v\u00e4li j\u00e4ta t\u00fchjaks. Password on seesama postgreSQL keskkonna password, millega logid ka pgAdmini.\n<\/p>\n

\n\t\"Shell\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tK\u00e4sk, millega saab kopeerida andmed csv failist \u201eMaakond\u201c vastavasse tabelisse on selline:\n<\/p>\n

\n\tCOPY<\/span> public.\u201dMaakond\u201d (maakond_id, nimi) FROM<\/span> \u2018C:UsersKadriDocumentsrandajamaakond.csv\u2019 HEADER CSV DELIMiTER \u2018;\u2019 ENCODING \u2018UTF8\u2019;\n<\/p>\n

\n\tCOPY tabeli nimi (tunnuste\/veergude nimed) <\/i>FROM \u2019faili asukoht Sinu arvutis \u00fclakomade vahel\u2019 info faili kohta;<\/i>\n<\/p>\n

\n\tErinevalt pgAdmini p\u00e4ringuaknast tuleb psql terminalis kirjutada k\u00e4sk \u00fche reana, muidu k\u00e4sitletakse seda kahe erineva k\u00e4suna. (Kui sql lause on pikk ja ei mahu \u00e4ra, siis see kohandub ise).\n<\/p>\n

\n\t\"Shell\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tKui kuvatakse tulemuseks COPY 5, siis teame, et viis rida kopeeriti meie tabelisse \u00e4ra.\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tV\u00e4lisv\u00f5tmete lisamine<\/b>\n<\/p>\n

\n\tKui andmed on \u00fcles laetud, saame lisada seosed (v\u00e4lisv\u00f5tmed) andmebaaside vahel. V\u00f5imalik oleks teha seda enne andmete importimist. Sellisel juhul peaks hoolikalt j\u00e4lgima tabelite \u00fcleslaadimise j\u00e4rjekorda, sest v\u00e4lisv\u00f5tme kitsendus ei luba n\u00e4iteks laadida andmeid tabelisse Isik kui tabelis Leibkond ei ole veel andmeid, sest \u00fcleslaadimisel kontrollitakse, et Leibkond tabeli tunnuse leibkond_id v\u00e4\u00e4rtustes leiduks vaste tabeli Isik tunnuse leibkond v\u00e4\u00e4rtustele.\n<\/p>\n

\n\tSelleks, et luua seos kahe tabeli vahel, tuleb lisada v\u00e4lisv\u00f5tmed \u201eForeign Key\u201c.\n<\/p>\n

\n\t\u00dche tabeli (olemi) primaarv\u00f5ti (v\u00f5i ka m\u00f5ni muu unikaalne tunnus) l\u00e4heb teise olemi v\u00e4lisv\u00f5tmeks. V\u00f5ti lisatakse selle olemi juurest, mille mitu eksemplari v\u00f5ivad olla seotud \u00fche eksemplariga teises olemis. Nt mitu isikut kuuluvad \u00fchte leibkonda, j\u00e4relikult tabelite isik ja leibkond vahel seose loomiseks peab tabelis Isik \u00fcks tunnustest olema tabeli Leibkond tunnuse leibkond_id v\u00e4\u00e4rtuste talletamiseks, et saaks need tabelid omavahel siduda. Isik tabelis v\u00f5ib tunnusel olla teine nimi, n\u00e4iteks andmebaasi Randaja on Isik tabelis selle tunnuse nimi \u201eleibkond\u201c, aga see sisaldab samu v\u00e4\u00e4rtusi, mis tabelis Leibkond tunnus \u201eleibkond_id\u201c ja v\u00e4lisv\u00f5tet lisades sisuliselt lisame kitsenduse, et Isik tabeli v\u00e4lja leibkond v\u00e4\u00e4rtused peavad leidma vastavuse tabeli Leibkond v\u00e4lja leibkond_id v\u00e4\u00e4rtustega.\n<\/p>\n

\n\tDbdesigneris on see lahendatud v\u00f5imalusena viidata teisele tabelile.\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\"V\u00f5tmed\"\"V\u00f5tmed2\"\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tKuna v\u00e4lisv\u00f5tmed on lisatud DB Designeris, siis on v\u00f5tmete lisamise k\u00e4sud olemas sealt eksporditud tabelite loomise sql faili l\u00f5pus. Kopeeri faili l\u00f5pust k\u00f5ik ALTER TABLE k\u00e4sud ja jooksuta need pgAdmini p\u00e4ringute aknas (Query Tool) l\u00e4bi.\u00a0\n<\/p>\n

\n\tV\u00f5tmeid saab lisada ka PostgreSQL pgAdmin liideses. Tehniliselt on tegu kitsenduste (constraints) seadmisega.\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\tV\u00e4lisv\u00f5tmete olemasolu saab kontrollida sirvimispuust, kus need tekkivad kitsendustena (constraints). Kuldne on primaarv\u00f5ti, h\u00f5bedane v\u00e4lisv\u00f5ti.\u00a0\n<\/p>\n

\n\t\"V\u00f5tmed\"\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0\n<\/p>\n

\n\t\u00a0<\/p>\n","protected":false},"excerpt":{"rendered":"

\u00a0 Teeme PostgreSQL keskkonda uue andmebaasi Randaja, mis koosneb kaheksast tabelist (olemist). Ette on antud loogiline andmebaasimudel, mida l\u00e4heb vaja tabelite vaheliste seoste m\u00f5istmiseks. Nendele seostele tuginedes \u00f5pime hiljem mitut tabelit h\u00f5lmavaid p\u00e4ringuid koostama. \u00a0 \u00a0 Andmebaas on disainitud andmete …<\/p>\n","protected":false},"author":231,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"class_list":["post-14","page","type-page","status-publish","hentry"],"acf":[],"_links":{"self":[{"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/pages\/14","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/users\/231"}],"replies":[{"embeddable":true,"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/comments?post=14"}],"version-history":[{"count":1,"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/pages\/14\/revisions"}],"predecessor-version":[{"id":137,"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/pages\/14\/revisions\/137"}],"wp:attachment":[{"href":"https:\/\/sisu.ut.ee\/ajalooandmebaasid\/wp-json\/wp\/v2\/media?parent=14"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}