Uvod u relacione baze podataka i SQL

Uvod

U teoriji, termin "baza podataka" odnosi se na bilo kakvu organizovanu kolekciju podataka koja se čuva u elektronskom obliku, * međutim, godinama unazad navedeni pojam tipično se poistovećuje sa relacionim bazama podataka, budući da su upravo relacioni sistemi za skladištenje i obradu podataka (SQL Server, Oracle, Db2, MySql, PostgreSQL - da navedemo samo neke), ** najrasprostranjeniji i najčešće korišćeni. ***

Za prosleđivanje uputa, odnosno (u opštem smislu), za poslove administracije relacionih baza, koristi se SQL (Structured Query Language), deskriptivni/neproceduralni jezik specifično namenjen radu sa bazama podataka.

Ako se osvrnemo na to da se baze podataka koriste u većini desktop programa i na skoro svim sajtovima, jasno je da je u pitanju pojava od izuzetnog značaja u računarskoj industriji, i to je više nego dobar razlog da se u nastavku upoznamo sa relacionim bazama podataka i SQL-om ....

Osnove relacionog modela

U najpraktičnijem smislu, relaciona baza podataka može se shvatiti kao sistem međusobno povezanih tabela, u kome se teži što racionalnijem i što ekonomičnijem zapisu podataka.

Da bismo što bolje ilustrovali osnovnu ideju koja stoji iza relacionog modela (i principe dizajna i upotrebe relacionih baza podataka), kreiraćemo bazu podataka koja pamti informacije o prodavcima i prodaji artikala (u fiktivnoj prodavnici), i proći ćemo kroz sve korake u projektovanju.

Prva tabela sadrži podatke o prodavcima i gotovo se ne razlikuje od tabele kakvu bismo za slične potrebe napravili u Excel-u:

baze_podataka_01 — Slika 1. - Tabela sa podacima o prodavcima.

Sa druge strane, dizajn tabele prodaja (u kojoj se u svakom slogu beleže podaci o prodaji jednog artikla), zahteva malo više promišljanja, i stoga ćemo pažljivije razmotriti kako treba organizovati ovakvu tabelu.

Za početak, sagledaćemo zašto određene formate zapisa (koji bi u ovom trenutku vrlo verovatno mogli da vam padnu na pamet) - ne treba koristiti.

Teoretski, mogli bismo (pored ostalih podataka o prodaji), beležiti samo ime i prezime prodavca:

baze_podataka_03 — Slika 2. - Tabela sa podacima o prodaji artikala - prvi primer neoptimalne strukture.

Drugi način (koji takođe nije pravo rešenje), podrazumeva da se u tabeli prodaja beleže svi podaci o prodavcu:

baze_podataka_04 — Slika 3. - Tabela sa podacima o prodaji artikala - drugi primer neoptimalne strukture (u pitanju je svojevrsna "karikatura", budući da je polje prodavac krajnje "pretrpano", ali, svrha ovakvih primera je da ukažu na to kako podatke *ne treba* formatirati).

Prvi format zapisa ima dva nedostatka:

pre svega, kombinacija imena i prezimena nije jedinstven podatak i ne upućuje obavezno na jednu osobu (npr. "Petar Jovanović" može biti ime i prezime bar dva različita prodavca) *
ukoliko je potrebno dobiti više informacija o prodavcu:

u tabeli prodaja nema dodatnih podataka
ako se podaci o prodavcu 'ručno' pretražuju u tabeli prodavci, ne možemo biti sigurni da čitamo prave podatke (iz gore navedenih razloga)

Drugi format zapisa je potpun, u smislu da podaci o prodavcu (koji se inače beleže; preko tabele prodavci), jesu zapisani - i sada jeste moguće razlikovati prodavce, * ali ....

U pitanju je format koji nije ni malo elegantan, pregledan i praktičan (baš, baš - nimalo ), a ako bismo "skroz preterali" i podatke upisivali ručno, neelegantnosti i nepreglednosti bismo mogli dodati i povećanu mogućnost pojave grešaka.

Doduše, to sve važi i za prvi format (mada, manje je očigledno na prvi pogled, zbog manjeg obima podataka) i zapravo - u oba slučaja - krajnje nepotrebno se beleže isti podaci na dva mesta.

Potrebno je naći rešenje koje omogućava da jedinstveni prodavac bude naveden u tabeli prodaja na jednostavan, ekonomičan i nedvosmisleno prepoznatljiv način - po mogućnosti preko samo jednog podatka (i pogotovo bez navođenja (svih) detalja putem "copy-paste metoda"), a potrebno je istovremeno da podaci o prodavcima budu zapisani ("negde i nekako"), i - po potrebi dostupni.

Pošto su podaci o prodavcima već zapisani (u zasebnoj tabeli), praktično rešenje je - pripisati svakom prodavcu jedinstven identifikacioni broj - i potom povezati tabele (kasnije ćemo detaljnije prodiskutovati o tome zašto smo napravili baš takav izbor).

Primarni ključ

Jedinstveni podatak u okviru jednog sloga po kome je moguće prepoznati ceo slog, * naziva se primarni ključ.

U praksi, za primarni ključ se gotovo uvek koriste celobrojne vrednosti (tj. 'redni brojevi slogova'), pri čemu se kolona tipično označava kao "id".

U tabeli prodavci, dodaćemo kolonu sa nazivom id, u kojoj će redom biti upisane celobrojne vrednosti od 1 do n, tako da svaki broj upućuje na jednog prodavca - uvek istog.

baze_podataka_02 — Slika 4. - Tabela sa podacima o prodavcima, koja sada sadrži i polje "id", koje služi kao primarni ključ (svojevrstan identifikacioni broj svakog prodavca).

Uspostavljanje veze između tabela (sekundarni ključevi)

Nakon definisanja primarnog ključa u tabeli prodavci, potrebno je uspostaviti vezu između tabela prodavci i prodaja.

Kada se uspostavi veza između tabela (videćemo kasnije kako se to tehnički izvodi), u tabeli prodaja više nećemo navoditi ime i prezime prodavca (pogotovo ne sve podatke), već samo identifikacioni broj iz tabele prodavci - primarni ključ - što (naravno) znači da se polje prodavac_id * u tabeli prodaja nadalje popunjava podacima brojčanog tipa, a ne podacima tekstualnog tipa (u svakom slogu, polje prodavac_id predstavlja primarni ključ prodavca koji je prodao određeni artikl).

baze_podataka_05 — Slika 5. - Uspostavljanje relacije između primarnog ključa "id" iz tabele "prodavci", i sekundarnog (tj. spoljnjeg) ključa "prodavac_id" u tabeli "prodaja".

Kada se primarni ključ iz jedne tabele upotrebi u drugoj tabeli (radi povezivanja sa podacima iz prve tabele), takav podatak prepoznaje se kao sekundarni ključ.

* Za polje nećemo više koristiti naziv prodavac (kao u prvim primerima), već prodavac_id, da bi bilo što jasnije da se radi (upravo) o id-u prodavca (iz druge tabele).

Na ovom mestu je jasno da sličan postupak treba izvesti i za artikle: potrebno bi bilo kreirati zasebnu tabelu sa artiklima, svakom artiklu pripisati zaseban id i potom povezati tabelu sa artiklima, sa tabelom "prodaja" (međutim, i ovoga puta zarad preglednosti, takav postupak nećemo prikazivati).

Takođe, jedan "nerd alert" sa naše strane: mlađim čitaocima koji se zanimaju za baze podataka, unapred se izvinjavamo zbog upotrebe "krupnih reči" i stranih termina koja predstoji u sledećih nekoliko odeljaka, sve do poglavlja "Struktura relacione baze i organizacija podataka" (naravno, trudićemo se - koliko god je moguće - da u navedenim odeljcima takođe koristimo što jednostavniji rečnik).

Prirodni i surogatni primarni ključevi

U prethodnim odeljcima, opisali smo tipičan postupak koji podrazumeva da se za primarni ključ tabele biraju celobrojne vrednosti, međutim, iako određena celobrojna vrednost u polju id jeste vezana za samo jedan red (to jest, iako se vrednosti ne ponavljaju po slogovima), mora se primetiti (na primer), da broj 1 - sam po sebi - nema nikakve suštinske i prirodne veze sa osobom "Petar Jovanović" (čiji su podaci zabeleženi u prvom redu), * pa možemo reći da takav podatak predstavlja surogatni (tj. veštački) primarni ključ, a svakako se postavlja i pitanje da li je ceo pristup optimalan.

Da li (možda) postoji podatak u okviru sloga, koji na prirodan način identifikuje osobu, to jest, da li se neko od već postojećih polja javlja kao prirodni primarni ključ, koji može i formalno da se pojavi kao primarni ključ?

Da li onda takvo polje (ako postoji) - treba da se pojavi kao primarni ključ?

Načelno je moguće izabrati da jedan od već postojećih podataka bude primarni ključ, ali, to (razume se) može biti samo podatak koji se u okviru određene kolone ne ponavlja (tj. podatak koji je u okviru određene kolone jedinstven).

U primeru koji razmatramo, ime nije jedinstven podatak (više prodavaca može imati ime Petar, ili bilo koje drugo ime), prezime takođe nije jedinstven podatak (a nije ni datum rođenja).

E-mail adrese (kao što smo već naveli), zapravo jesu jedinstvene - pod uslovom da su verifikovane, međutim, u pitanju je podatak tekstualnog tipa, a tekstualni podaci se pretražuju na manje efikasan način od brojčanih.

Shodno navedenom, za primarni ključ se obično bira jedinstven celobrojni podatak iz kolone "id" (i u bazi koju smo uzeli za primer, i inače), ali - u praksi se najčešće koriste oba ključa u jednom slogu, to jest, nikako se ne treba "odreći" prirodnog primarnog ključa samo zato što je uveden veštački.

"Veštački" primarni ključ (tipično označen kao "id") - stvar je prakse (većina tabela u bazama podataka ima primarni ključ celobrojnog tipa, sa nazivom "id"), a prirodni primarni ključ je tu da spreči dupliranje slogova po kolonama.

Da odgovorimo usput i na pitanje koje (rekli bismo), na ovom mestu "dolazi samo od sebe": zašto nismo koristili JMBG?

(Kratak odgovor je sledeći: zarad očuvanja preglednosti članka, JMBG nismo ni upisivali, a malo duži odgovor sledi ispod.)

Kada su u pitanju osobe, lako možemo zamisliti da se trinaestocifreni JMBG - kao "najprirodniji" prirodni primarni ključ (ili bar "najočigledniji") - može koristiti za identifikaciju slogova.

Načelno može - podatak je brojčanog tipa i jedinstven je.

Međutim (kad već 'zamišljamo'), možemo zamisliti i (drugu) situaciju, u kojoj je u tabelu potrebno uneti slog sa podacima za osobu koja nema JMBG (na primer, strani državljanin sa drugačijim ličnim brojem koji nije u formatu JMBG-a), posle čega se "vraćamo na početak".

U teoriji, primarni ključ može biti i kombinacija dva polja (na primer: id države i lični broj koji odgovara izabranoj državi), ali, u primeru koju razmatramo, najverovatnije "ne bismo komplikovali": implementirali bismo sistem koji dozvoljava korišćenje različitih "ličnih brojeva" (i nedvosmisleno prepoznavanje osoba bez obzira na državljanstvo), ali, gotovo je sigurno da bismo za primarni ključ izabrali podatak iz kolone id (čiji je tip 'int').

U svakom slučaju - bez obzira na to što je tabeli dodat veštački primarni ključ, krajnje je neophodno da se prepozna (i koristi), i polje koje predstavlja prirodni primarni ključ (ili, kombinacija polja, koja zajedno predstavljaju prirodni primarni ključ) - i potrebno je postupati vrlo pažljivo pri dodavanju slogova (to jest, neophodno je implementirati mehanizam koji detaljno proverava podatke).

Da pojasnimo (preko primera koji već koristimo) ....

Pri dodavanju novog prodavca u tabelu, prodavcu će id (tipično) biti dodeljen automatski (i neće imati "prirodne i suštinske" veze sa prodavcem), što znači da se za proveru novog prodavca (u smislu toga da li prodavac sa navedenim podacima već postoji u tabeli), moraju koristiti ostali podaci.

Ako među podacima koji se koriste za upis novog prodavca, postoji podatak koji predstavlja prirodni primarni ključ (JMBG, verifikovana e-mail adresa i sl), moguće je proveriti da li u tabeli već postoji slog u kome je dati podatak naveden (da li već postoji prodavac sa datim JMBG-om, ili sa datom e-mail adresom i sl), dok - ukoliko među podacima nema prirodnog primarnog ključa - zapravo ne postoji način da se slogovi raspoznaju!

Entiteti i atributi

Pošto smo razumeli kako se podaci 'raspoređuju po tabelama' (u okviru relacionog modela), potrebno je da se upoznamo i sa nekoliko termina koji opisuju pojave sa kojima smo se prethodno susretali.

Entitet(i)

Pojam entiteta u bazama podataka, u opštem smislu označava objekat (tipično - složeni objekat), koji u datom sistemu ima značaj i koji se može nedvosmisleno odrediti i zapisati.

Entiteti iste kategorije zapisuju se u okviru jedne tabele, pri čemu je za svaki entitet vezan jedinstven primarni ključ.

Takođe (kao što smo već videli), entiteti iz jedne tabele mogu se navoditi u drugim tabelama preko sekundarnih (tj. spoljnih) ključeva.

U bazi koju projektujemo, jedan od entiteta je prodavac (sledi "prevod" gornja tri pasusa):

svaki prodavac određen je skupom pojedinačnih podataka (ime, prezime, datum rođenja i sl), to jest "nedvosmisleno je određen i zapisan"
svaki prodavac u tabeli prodavci označen je jedinstvenim primarnim ključem
prodavci (kao entiteti), pojavljuju se u tabeli prodaja - preko spoljnih ključeva (a mogu se naravno pojavljivati i u drugim tabelama)

Atribut(i)

Termin "atribut" može se definisati na dva načina:

sa jedne strane, u pitanju je pojedinačni podatak koji se koristi pri definisanju određenog entiteta
sa druge strane, može se reći i to da atribut predstavlja jednu od zajedničkih osobina svih entiteta određene kategorije

Da pojasnimo dodatno ....

Jedan entitet ima više atributa (za prodavca, atributi su: ime, prezime, datum rođenja, datum stupanja u radni odnos i sl)

Bilo koji pojedinačni entitet iz određene grupe entiteta (praktično, iz iste tabele), ima svaki od atributa koji su pripisani svim entitetima date kategorije: svi prodavci imaju ime i prezime (pri čemu svakog prodavca odlikuje njegovo sopstveno ime i prezime), za sve prodavce se beleži datum stupanja u radni odnos (pri čemu se za svakog prodavca beleži datum stupanja u radni odnos koji se odnosi na datog prodavca), i sl.

Tipovi relacija između entiteta

U smislu toga koliko puta se spoljni ključ iz jedne tabele može pojaviti u drugoj tabeli, razlikuju se sledeći tipovi relacija:

relacija 1:1 (jedan prema jedan) - spoljni ključ određenog entiteta iz jedne tabele, može se u drugoj tabeli pojaviti samo u jednom slogu
relacija 1:n (jedan prema više) - spoljni ključ određenog entiteta iz jedne tabele, može se u drugoj tabeli pojaviti u više slogova
relacija n:n (više prema više) - više entiteta iz jedne tabele povezuje se sa više entiteta iz druge tabele (preko posredničke tabele)

Relacija 1:n (jedan prema više)

Tipično se među entitetima uspostavlja relacija 1:n ("jedan prema više"), * koja - kao što je već navedeno - podrazumeva da se entitet iz tabele T1 može pojaviti u tabeli T2 proizvoljan broj puta (preko spoljnjeg ključa):

baze_podataka_jedan_prema_vise — Slika 6. - Opšta šema relacije "jedan prema više".

Kao konkretan primer možemo uzeti tabelu prodaja: jedan prodavac (entitet iz tabele prodavci), može se pojaviti u više slogova u tabeli prodaja.

Relacija 1:1 (jedan prema jedan)

Relacija 1:1 ("jedan prema jedan"), nije uobičajena kao relacija 1:n, ali, svakako se pojavljuje (dovoljno često). Ovoga puta, svaki entitet iz tabele T1 može se u tabeli T2 pojaviti samo jednom:

baze_podataka_jedan_prema_jedan — Slika 7. - Opšta šema relacije "jedan prema jedan".

Kao konkretan primer, možemo zamisliti bazu podataka u kojoj se beleže podaci u okviru određene sportske lige: ako svaki klub (entitet iz tabele klubovi), može imati samo jednog glavnog trenera, i ako svaki glavni trener (entitet iz tabele osoblje), ima pravo da trenira samo jedan klub, možemo zamisliti da u tabeli klubovi postoji polje glavni_trener, preko koga je naveden id tj. spoljni ključ trenera (čiji su podaci navedeni u tabeli osoblje).

Ali - ako ste raspoloženi za dodatno 'naprezanje vijuga' (pre nego što spontano dođemo do sličnih primera u kasnijem toku članka) - možemo zamisliti i nešto drugačiju organizaciju u bazi podataka "sportska_liga".

Recimo, preko pomoćne tabele treneri, id-ovi trenera mogli bi se spajati sa id-ovima klubova, čime se praktično beleži informacija o tome koji trener trenira koji klub.

U tehničkom smislu, tabela treneri bila bi sačinjena iz polja id, trener_id i klub_id, pri čemu je polje trener_id povezano sa tabelom osoblje preko relacije 1:1, a isto važi i za polje klub_id, koje je takođe povezano sa tabelom klubovi preko relacije 1:1.

Ako "zamišljanje primera" nije uspelo, budite bez brige, jer (kao što smo naveli), povezivanjem osnovnih tabela preko pomoćnih tabela, za prave ćemo se baviti tek kasnije u članku (i slobodno se odmah prebacite na sledeći odeljak, "relacija n:n").

Ako ste (sa druge strane) raspoloženi za još malo 'mozganja', možemo odmah razmotriti i naizgled sličan primer u kome bi takođe trebalo da se pojave dve relacije 1:1, ali - zapravo se pojavljuju relacije 1:1 i 1:n.

Ako za primer entiteta uzmemo grad i državu, a kao primer tabele koja spaja dva navedena entiteta, uzmemo tabelu glavni_grad, jasno je da se bilo koji grad u takvoj tabeli može pojaviti samo jednom (svaki grad može biti glavni grad samo jedne države), i stoga, između tabela grad i glavni_grad postoji relacija 1:1.

Međutim, budući da postoje države sa dva (pa čak i tri) glavna grada, jasno je da mora postojati mehanizam koji omogućava da se određena država u tabeli pojavi više puta, i stoga, između tabele države i tabele glavni_grad, zapravo stoji veza 1:n (to jest, nije u pitanju veza 1:1).

Još jednom napomena: implementacijom različitih relacija bavićemo se tek posle poglavlja o SQL upitima.

Relacija n:n (više prema više)

Što se tiče relacije n:n ("više prema više"), za sada ćemo (samo) dodatno razmotriti opšte ideje.

Uzmimo za primer bazu podataka filmska_enciklopedija, u kojoj se kao entiteti pojavljuju glumci i filmovi (čemu odgovaraju tabele glumci i filmovi), pri čemu je glavno pitanje: gde i kako (to jest, u kom formatu), treba zabeležiti podatke o tome u kojim filmovima se određeni glumac pojavljivao (ili - podatke o tome koji glumci su se pojavili u određenom filmu).

Intuitivno možete zaključiti (primera radi), da pokušaj da se u tabeli glumci, u jednom slogu navedu svi filmovi koji se vezuju za glumca, veoma podseća na "pokušaj" (koji smo videli ranije), da se u tabeli prodaja navedu svi podaci o prodavcu, i stoga - "razmišljamo dalje" ....

Za sada je najbitnije znati da se relacija n:n ne može implementirati na efikasan način preko jedne tabele, to jest - potrebno je koristiti posredničku tabelu - međutim, bez obzira na to što smo mnoge pojedinosti već nagovestili u prethodnim odeljcima, opširniju diskusiju o implementaciji relacije n:n (kao i ostalih relacija), ostavljamo za kasnije, jer smatramo da je potrebno da se prvo detaljnije upoznamo sa ostalim osnovnim osobinama baza podataka (takođe, i sa SQL sintaksom), da bismo što bolje razumeli kako su (i pre svega - zašto), implementacije različitih relacija izvedene na specifičan način koji ćemo opisati (ali, bez brige, nije ni iz daleka komplikovano). :)

Struktura relacione baze i organizacija podataka

Da bi baza podataka bila u stanju da pruži korisne informacije na brz i efikasan način (a upravo je, to što smo naveli - prava svrha relacionih i drugih baza podataka), potrebno je da tabele budu pravilno koncipirane i pravilno strukturirane i (takođe) - potrebno je koristiti efikasne upite.

Za početak, pozabavićemo se strukturom tabela.

Tabela, polje, slog

Tabela je sistem međusobno povezanih ćelija u koje se upisuju podaci o određenoj kategoriji entiteta, a same ćelije su raspoređene u redove i kolone.

Pojedinačna ćelija naziva se "polje":

baze_podataka_polje — Slika 8. - Polje u tabeli.

Polje je odrednica koja se koristi i za celu kolonu (budući da cela kolona sadrži "polja istog tipa"):

baze_podataka_polje_kolona — Slika 9. - Kolona u tabeli (kolona u tabeli takođe nosi naziv polje).

Ceo red naziva se "slog":

baze_podataka_slog — Slika 10. - Slog u tabeli.

U svakoj tabeli, "slog" predstavlja jedan složeni podatak (jedan entitet), pa tako, u tabeli prodavci, svaki red sadrži sve podatke koji se beleže o jednom prodavcu (što znači da cela tabela sadrži podatke o prodavcima). Podaci o osobama koje učestvuju u prodaji se (nadalje) u drugim tabelama ne zapisuju direktno (što bi samo povećalo memorijsko zauzeće i mogućnost pojave grešaka), već se po potrebi koriste podaci iz tabele prodavci - preko ključa (baš kao što smo još na početku ustanovili).

Svaki od podataka u jednom slogu pripada određenom (prigodno odabranom) tipu podataka: primarni ključ je (gotovo uvek) celobrojna vrednost, a ostali podaci se mogu zapisivati kao: brojevi, tekst, datumi (u posebnom formatu), boolean vrednosti (true i false) i sl.

Deluje da smo u tabelama prodavci i prodaja izabrali optimalan format za pojedinačne podatke, ali .... možda bi neko od čitalaca bio sklon da se zapita: da li smo (možda) mogli izabrati i neki sažetiji format (?!), i stoga ćemo prodiskutovati ukratko o optimalnom formatu slogova ....

Atomski podaci

Budući da je tabela u bazi podataka namenjena zapisivanju kolekcija složenih podataka (tako da se u svakom slogu pojavljuje jedan entitet), jedno od osnovnih pitanja je: kako (tačno) treba organizovati (odnosno zapisati) podatke u okviru jednog sloga.

Ako bismo, u tabeli prodavci, za zapis podataka u svakom slogu odredili svega dva polja: id (int) i osoba (text), prva kolona sadržala bi redni broj, dok bismo u drugu kolonu upisivali "sve ostale" podatke, što (na primeru jednog sloga), može imati sledeći oblik:

		
id:    1
osoba: "Jovan Petrović, Ruzveltova 25, 41 godina, Mašinski inženjer, jocapet79@gmail.com"

Slika 11. - Neoptimalna organizacija slogova u tabeli sa podacima o osobama (svi podaci o osobi su "zbijeni" u jednu ćeliju).

Nije teško intuitivno zaključiti da u gornjem zapisu "nešto ne štima", iako deluje da je u tehničkom smislu sve u redu.

U smislu efikasne obrade, prethodni format zapisa ne pomaže ni najmanje: zarad dolaženja do nekog od "unutrašnjih" podataka, potrebno je - umesto da se podatak pročita neposredno - proći kroz nisku znakova (polje osoba je samo obična niska), i potom je potrebno izdvajati pojedinačne delove (a ukoliko neki od izdvojenih podataka nije tekstualnog tipa, izvesna količina procesorskog vremena trošila bi se na konverziju podatka iz tekstualnog zapisa u brojčani).

Umesto zapisa preko (jedne) niske, složeni podaci se dele na "atome" (podatke koji se ne mogu 'dalje usitnjavati'), i - kao što smo već nagovestili - za svaki podatak koristi se odgovarajući tip.

U tabeli koju projektujemo, podatak u polju id zapisuje se kao celobrojna vrednost, podaci u poljima ime, prezime i e-mail zapisuju se kao niske, a datum se zapisuje preko specijalizovanog formata (datetime), koji ne predstavlja običan tekst, već (praktično), podatak brojčanog tipa.

Sada se može definisati mnogo praktičniji format tabele prodavci:

		
polje           | vrednost            | tip podatka
-----------------------------------------------------
id:             | 1                   | celobrojni
ime:            | Jovan               | niska znakova  
prezime:        | Petrović            | niska znakova
ulica:          | Ruzveltova          | niska znakova
broj:           | 25                  | celobrojni
datum_rodjenja: | 18.08.1979.         | datum
zanimanje:      | Mašinski inženjer   | niska znakova
email:          | jocapet79@gmail.com | niska znakova

Slika 12. - Optimalna organizacija slogova u tabeli sa podacima o osobama: svaki podatak ima odgovarajući tip i smešten je u zasebno polje.

Obrada podataka - Upiti i osnove SQL-a

Rad sa bazama podataka podrazumeva korišćenje tekstualnih komandi, bez obzira na to da li se bazama pristupa preko programa koji dolaze uz RDBMS (konzolni program, web aplikacija), ili, preko programskih jezika, i stoga je neophodno dobro se upoznati sa SQL sintaksom.

U praksi, SQL sintaksa nije univerzalna, tj. svaki RDBMS * ima svoju sintaksu, ali (srećom), međusobne razlike u većini situacija nisu 'velike i suštinske', već je svaka konkretna SQL implementacija svojevrsna "varijacija na temu", pri čemu ima mnogo više sličnosti nego razlika.

Početni plan za upoznavanje sa SQL-om je sledeći:

definisaćemo pojam upita
osvrnućemo se na jezik SQL u opštem smislu
upoznaćemo se ukratko sa MySql-om *

.... nakon čega ćemo preći na detaljno upoznavanje sa pojedinačnim upitima.

Pojam upita u bazama podataka

Upit je niz tekstualnih komandi * preko kojih se od baze zahteva određeni vid obrade podataka: kreiranje nove baze ili nove tabele u postojećoj bazi, unos podataka, čitanje, izmena, brisanje pojedinačnih podataka, dodavanje ili uklanjanje kolona (a postoje i određene komande koje ćemo za sada preskočiti, jer prevazilaze okvire uvodnog članka).

Upiti se zadaju preko komandi programskog jezika SQL (koje su unapred definisane).

* U najpraktičnijem smislu, može se reći da je upit "omanja skripta".

Navedimo na ovom mestu i jednu usputnu napomenu opšteg tipa (koja nema previše veze sa SQL-om) ....

Neki programi i sajtovi "deluju kao da koriste baze podataka" (na primer, kada otvorimo stranicu sa informacijama o određenom filmu na sajtu IMDB, znamo da sajt "čita podatke iz baze"). Sa druge strane, nedovoljno upućeni korisnici često nisu svesni toga da iza mnogih primera koji su manje očigledni (npr. chat aplikacije i društvene mreže uopšte), takođe stoje baze podataka.

Još se manje razmišlja o tome da se HTML stranice (u slučaju bilo kog iole ozbiljnije dizajniranog sajta u današnje vreme), ne čuvaju na serveru u obliku u kome se prosleđuju browserima, već, u obliku šablona, koji (kada se pozove određeni URL), bivaju popunjeni podacima koji se tipično preuzimaju iz (već znate šta ćemo reći) - baza podataka. :)

SQL (Structured Query Language) - osnovne postavke

SQL (Structured Query Language) je deskriptivni programski jezik koji se u sistemima za upravljanje bazama podataka koristi za prosleđivanje upita, to jest (kao što smo nagovestili još u uvodu), praktično za sve zadatke koji su vezani za obradu podataka i administraciju RDBMS sistema, međutim, budući da je u pitanju pristup koji se razlikuje od proceduralnih jezika (kako u opštem smislu, tako i u smislu sintakse samih naredbi), napravićemo kraću digresiju i objasnićemo razliku.

Jezici u kojima programeri moraju detaljno definisati postupke za rešavanje različitih problema (primer takvog jezika je C), pripadaju kategoriji proceduralnih programskih jezika.

Nasuprot navedenom principu, deskriptivni jezici (deskripcija = opis), koriste se za opisivanje (prirode) problema, korišćenjem unapred definisane sintakse, pri čemu se ne opisuje procedura za rešavanje problema, već se različiti zahtevi rešavaju po unapred definisanim procedurama na koje korisnici nemaju direktnog uticaja.

Na primer: U SQL-u, pri pozivanju upita za čitanje tabele, korisnik navodi tip upita (čitanje), naziv tabele i, po potrebi, ograničenja i uslove ("učitati i prikazati podatke iz tabele 'xyz', ali, samo podatke koji se odnose na jun 2020.") - posle čega sistem pristupa pronalaženju podataka i prikazuje rezultat korisniku.

U nastavku, prikazaćemo upite preko kojih se može kreirati baza koju smo u uvodnim poglavljima koristili kao primer, * s tim da ćemo se prvo upoznati (ukratko), sa konkretnim DBMS sistemom koji ćemo koristiti ....

Kratak uvod u MySql

MySql je kvalitetan i jednostavan * DBMS koji je veoma popularan i rado korišćen na brojnim sajtovima (i u drugim okolnostima), budući da je u pitanju program koji omogućava veliku brzinu i efikasnost u obradi podataka - i pri tom nije težak za savladavanje.

Što se tiče 'tehnikalija', MySql se može instalirati i konfigurisati samostalno, a dolazi i uz paket XAMPP o kome smo pisali u članku o pokretanju lokalnog web servera.

Za isprobavanje primera iz članka, pokrenite XAMPP (pokrenite servise Apache i MySql) i unesite sledeću adresu u adresnu liniju browsera:

		
localhost/phpmyadmin

Slika 13. - Adresa za pokretanje web aplikacije phpmyadmin, preko koje se može pristupati MySql bazama (program phpmyadmin je tipično deo paketa XAMPP).

.... čime se pokreće web aplikacija preko koje se može pristupati bazama podataka na sistemu.

Kreiranje baze preko upita (CREATE DATABASE)

Da bismo mogli da prosleđujemo upite, potrebno je pokrenuti prozor za unos SQL upita (uz MySql dolazi i "pravi" konzolni program, ali, uvažićemo to da je rad u grafičkom okruženju ipak udobniji za početno upoznavanje) ....

Nova baza kreira se po sledećem obrascu:

		
CREATE DATABASE naziv_baze;

Slika 15. - Opšta šema komande za kreiranje baze podataka.

U konkretnom primeru kojim se bavimo, nova baza može se kreirati preko sledećeg (konkretnog) upita:

		
CREATE DATABASE prodaja;

Slika 16. - SQL upit za kreiranje nove baze podataka.

Prethodni upit poslužiće sasvim dobro, ali, ako je kreiranje baze potrebno izvesti na "skroz zvaničan" način, upit je potrebno upotpuniti dodatnim uslovima i smernicama:

		
CREATE DATABASE IF NOT EXISTS prodaja
	CHARACTER SET utf8
	COLLATE utf8_general_ci;

Slika 17. - Prošireni SQL upit za kreiranje nove baze podataka.

Novi upit biće izvršen samo ukoliko baza sa navedenim imenom ne postoji, a usput smo definisali i metod za enkodiranje znakova (UTF-8), koji dozvoljava unos ćiriličnih i latiničnih znakova.

Kreiranje tabela preko upita (CREATE TABLE)

Nova tabela (unutar postojeće baze), kreira se po sledećem obrascu:

		
CREATE TABLE naziv_tabele
(
	polje_1 (tip polja, osobine ....)
	polje_2 (tip polja, osobine ....)
	....
	polje_n (tip polja, osobine ....)
);

Slika 18. - Opšta šema komande za kreiranje tabele (unutar postojeće baze podataka).

Pošto smo prethodno kreirali novu bazu (i pošto smo još na početku osmislili strukturu tabela), možemo napisati upite za kreiranje tabela:

		
USE prodaja;

CREATE TABLE prodavci
(
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	ime varchar(255) NOT NULL,
	prezime varchar(255) NOT NULL,
	datum_rodjenja datetime NOT NULL,
	adresa varchar(255) NOT NULL,
	broj int NOT NULL,
	email varchar(255) NOT NULL
);

CREATE TABLE prodaja
(
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	datum datetime NOT NULL,
	artikl varchar(255) NOT NULL,
	cena double NOT NULL,
	kolicina double NOT NULL,
	prodavac_id int NOT NULL,
	FOREIGN KEY (prodavac_id) REFERENCES prodaja(id)
)

Slika 19. - SQL upiti za kreiranje tabela "prodavci" i "prodaja". Više SQL upita može se proslediti odjednom, pod uslovom da su razdvojeni znakom ";" (napomena: takođe je bitno da poslednja instrukcija u svakom upitu bude zapisana bez zareza na kraju).

Kao što vidite: birali smo odgovarajuće tipove podataka za svako polje, polje id smo proglasili za primarni ključ u obe tabele, a dodali smo i direktivu AUTO_INCREMENT, koja će omogućiti da id (u obe tabele) bude automatski uvećan za 1 pri unosu svakog novog sloga.

Takođe, primetimo da su nezavisni upiti za kreiranje tabela odvojeni znakom ; (tačka-zarez), što je sintaksa koja se mora koristiti ukoliko je potrebno da se dva ili više upita proslede odjednom.

Budući da se u tabeli prodaja pozivamo na id-ove prodavaca iz tabele prodavci (FOREIGN KEY (prodavac_id) REFERENCES prodaja(id)) i budući da je unos podataka u tabele, sledeći korak koji preduzimamo, prvo ćemo razmotriti mehanizam koji proverava da li prodavci koji se navode u tabeli prodaja - zapravo postoje.

Referencijalni integritet

Pretpostavljamo da mnogi čitaoci unapred razumeju šta bi moglo da se desi ukoliko pokušamo da u tabelu prodaja unesemo nepostojeći id prodavca, ali, prodiskutujmo ipak malo više o celoj problematici (uz pretpostavku da za početak ne vodimo računa o "referencijalnom integritetu").

Ukoliko u tabelu prodaja pokušamo da unesemo novi slog koji je 'diskutabilan' (na primer: prodaju artikla koju je obavio prodavac sa rednim brojem 17), podatak će biti uredno unet i tabela prodaja će sama po sebi biti korektna (još jednom - pretpostavka je da za sada ne vodimo računa o referencijalnom integritetu), međutim, ako nakon unosa prosledimo (drugi) upit, preko koga se zahtevaju podaci o "fantomskom" prodavcu #17 - čiji id ne postoji u tabeli prodavci - sistem će (opravdano) prijaviti grešku.

Da se to ne bi dešavalo, potrebno je da baza podataka, pri svakom upisu u određenu tabelu, vodi računa o tome da li spoljnji ključ koji se upisuje - zapravo postoji u tabeli za koju je vezan, a pojam referencijalnog integriteta se odnosi upravo na mere koje DBMS preduzima da onemogući korisnike da se pozivaju na podatke (tj. entitete), koji ne postoje u bazi.

U tabeli koju smo kreirali, budući da jeste podešeno da baza podataka vodi računa o referencijalnom integritetu, pri pokušaju unosa prodaje koju je obavio prodavac sa id-om 17, bila bi prijavljena greška i slog ne bi bio unet.

Upis podataka u tabele (INSERT)

Sada kada znamo da će nas MySql sprečiti da se "upucamo u nogu" po pitanju referenciranja nepostojećih prodavaca, možemo se posvetiti unosu podataka.

Unos podataka (u postojeće tabele), obavlja se po sledećem obrascu:

		
INSERT INTO naziv_tabele
	(polje_1, polje_2 .... polje_n)
VALUES
	(vrednost_1, vrednost_2 .... vrednost_n)

Slika 20. - Opšta šema komande za unos podataka.

Pogledajmo i upit preko koga se mogu uneti podaci u tabele koje smo prethodno kreirali:

		
INSERT INTO prodavci
	(ime, prezime, datum_rodjenja, email)
VALUES
	("Petar", "Jovanović", "1979-06-15", "petar_jovanovic@str.co.rs"),
	("Ivan", "Kovačević", "1993-04-27", "ivan_kovacevic@str.co.rs"),
	("Jelena", "Marković", "1984-09-09", "jelena_markovic@str.co.rs"),
	("Ana", "Stanković", "1991-08-17", "ana_stankovic@str.co.rs");

INSERT INTO prodaja
	(datum, artikl, cena, kolicina, prodavac_id)
VALUES
	("2020-06-15", "Sveska", 80.00, 4, 1),
	("2020-06-15", "Olovka", 56.57, 2, 1),
	("2020-06-15", "Gumica", 34.96, 2, 2),
	("2020-06-15", "Selotejp", 134.57, 1, 2);

Slika 21. - SQL upiti za upis podataka u tabele koje smo prethodno kreirali (kao i u velikoj većini drugih programskih jezika, podaci tekstualnog tipa zapisuju se pod navodnicima, dok se podaci brojčanog tipa zapisuju bez navodnika).

Po pokretanju gornja dva upita, podaci su uredno uneti u obe tabele (nismo pokušali da "prevarimo" MySql), i stoga nadalje možemo podatke čitati, menjati, brisati (ili obrađivati na neki drugi način).

Čitanje podataka preko upita (SELECT)

Upit za čitanje podataka (iz postojeće tabele), zadaje se preko komande SELECT, prema sledećem obrascu:

		
SELECT
	polja
FROM
	naziv_tabele
WHERE
	uslov

Slika 22. - Opšta šema komande za čitanje podataka iz tabele.

Jednostavno čitanje celokupnog sadržaja tabele prodaja, može se izvesti preko sledećeg upita:

		
SELECT * FROM prodaja

Slika 23. - SQL upit koji vraća celokupan sadržaj tabele "prodaja" (sve slogove i sva polja).

Prethodni upit vratiće sve slogove iz tabele prodaja (odnosno: sva polja - svih slogova).

		
id | ime    | prezime   | datum_rodjenja | email
--------------------------------------------------------------------
1  | Petar  | Jovanović | 1979-06-15     | petar_jovanovic@str.co.rs
2  | Ivan   | Kovačević | 1993-04-27     | ivan_kovacevic@str.co.rs
3  | Jelena | Marković  | 1984-09-09     | jelena_markovic@str.co.rs
4  | Ana    | Stanković | 1991-08-17     | ana_stankovic@str.co.rs

Slika 24. - Rezultat izvršavanja upita sa slike #23. - prikaz sadržaja tabele "prodavci".

Međutim, ukoliko je potrebno (zarad preglednosti), prikazati samo određena polja, * ili je potrebno da se u prikaz rezultata dodaju polja koja podrazumevaju obradu postojećih polja, ** polja se mogu navesti pojedinačno (a postoje i dodatne opcije):

		
SELECT
	datum,
	artikl,
	cena AS 'Cena po artiklu',
	kolicina AS 'Količina',
	cena * kolicina AS 'Ukupna cena po artiklu',
FROM
	prodaja

Slika 25. - SQL upit preko koga se sadržaj tabele 'prodaja' može prikazati na pregledniji način (podaci iz kolona 'cena' i 'količina' se množe, a određenim poljima su pripisani i takozvani alijasi, alternativni nazivi).

Prethodni upit vratiće tabelu sledeće sadržine:

		
datum      | artikl    | Cena po artiklu | Količina | Ukupna cena po artiklu
----------------------------------------------------------------------------
2020-06-15 | Sveska    | 80              | 4        | 320
2020-06-15 | Olovka    | 56.57           | 2        | 113.14
2020-06-15 | Gumica    | 34.96           | 2        | 69.92
2020-06-15 | Selotejp  | 134.57          | 1        | 134.57

Slika 26. - Rezultat izvršavanja upita sa slike #25. - pregled tabele prodaja, sa podešavanjima koje smo sami definisali.

Kao što vidimo, preko upita je moguće dobiti (privremenu) tabelu sa poljima koja predstavljaju rezultat obrade polja tabele koja je zapisana u bazi podataka (množili smo vrednosti polja cena i kolicina u okviru svakog sloga), i takođe je moguće za polja koristiti preglednije nazive ("alijase").

Da bismo na najbolji način zaokružili priču o SELECT upitima, prikazaćemo kako se podaci mogu filtrirati i sortirati, za šta se koriste sledeće opcije:

za filtriranje se koristi klauza WHERE (uz precizno navođenje uslova)
za sortiranje se koristi klauza ORDER BY (uz navođenje konkretnog kriterijuma za sortiranje)

Sledeći upit:

		
SELECT
	datum,
	artikl,
	cena AS 'Cena po artiklu',
	kolicina AS 'Količina',
	cena * kolicina AS 'Ukupna cena po artiklu'
FROM
	prodaja
WHERE
	cena * kolicina > 300
ORDER BY
	cena * kolicina DESC

Slika 27. - SQL upit sa filtriranjem i sortiranjem slogova.

.... vratiće samo slogove kod kojih je ukupna vrednost (jednog) prodatog artikla veća od 300 Din (za razliku od upita SELECT bez klauze WHERE, koji vraća sve slogove), pri čemu će tabela biti sortirana u nerastući poredak, po kriterijumu najveće cene.

Upit za ažuriranje slogova (UPDATE)

Za izmenu već unetih podataka koristi se komanda UPDATE prema sledećem obrascu:

		
UPDATE
	naziv_tabele
SET
	polje=vrednost -- naredba dodele (nije uslov)
WHERE
	uslov

Slika 28. - Opšta šema komande za ažuriranje podataka u tabeli.

Za primer, pretpostavićemo da je potrebno ažurirati vrednost polja email u prvom slogu u tabeli prodavci:

		
UPDATE
	prodavci
SET
	email='petar_m_jovanovic@str.co.rs'
WHERE
	id=1

Slika 29. - SQL upit za ažuriranje slogova (moramo voditi računa da navedemo uslov za pristup određenim slogovima ("WHERE id=1"), jer će u suprotnom navedeni podatak ("petar_m_jovanovic@str.co.rs"), biti upisan u polje "email" SVIH slogova)!

Komanda UPDATE sama po sebi nije destruktivna (u tom smislu da ne briše slogove), ali, pri korišćenju ove komande moramo biti veoma pažljivi, jer - ukoliko se izostavi klauza WHERE (preko koje se precizira koje slogove je potrebno ažurirati) - biće ažurirani svi slogovi, pri čemu (u iole praktičnom smislu) - ne postoji "undo"!

Uz klauzu WHERE id=1, upit sa slike #29 se odnosi samo na slog u kome polje id ima vrednost 1.

Upit za uklanjanje slogova (DELETE)

Za uklanjanje slogova koristi se komanda DELETE prema sledećem obrascu:

		
DELETE FROM
	naziv_tabele
WHERE
	uslov

Slika 30. - Opšta šema komande za uklanjanje slogova.

Za primer, pretpostavićemo da je potrebno ukloniti treći slog u tabeli prodavci:

		
DELETE FROM
	prodavci
WHERE
	id=3

Slika 31. - SQL upit za uklanjanje slogova (ovoga puta moramo biti još pažljiviji nego u slučaju upita za ažuriranje i nikako ne smemo izostaviti uslov, jer će u suprotnom biti obrisani SVI SLOGOVI).

Pozivanjem gornjeg upita biće uklonjen slog u tabeli prodavci u kome polje id ima vrednost 3.

Naveli smo da je potrebno postupati veoma pažljivo pri pozivanju upita za ažuriranje, pri čemu se uslov ne sme izostavljati (osim, naravno, ukoliko nam jeste namera da polje u svim redovima dobije istu vrednost). *

Sa komandom DELETE potrebno je postupati još pažljivije, jer upit za brisanje jeste destruktivan: pri pozivu komande uz uslov, dolazi do nepovratnog brisanja jednog reda (što je slučaj sa gornjim primerom; inače uslov može obuhvatiti i više redova).

Ako se uslov izostavi, biće obrisani SVI slogovi (i nema 'vraćanja' preko opcije undo)!

Stoga - budite veoma pažljivi. :)

Izmena strukture tabele (ALTER TABLE, ADD, DROP)

Za izmenu strukture tabele, koristi se komanda ALTER TABLE uz dodatne komande kao što su ADD, DROP i sl, prema sledećem obrascu:

		
ALTER TABLE
	naziv_tabele
ADD ili DROP
	opcije

Slika 32. - Opšta šema komande za izmenu strukture tabele.

Komanda ALTER TABLE, preko dodatne komande ADD, umeće nove kolone u postojeće tabele, dok se preko (dodatne) komande DROP uklanjaju postojeće kolone (naravno, sa pripadajućim sadržajem).

Ako je potrebno dopuniti strukturu tabele prodavci, dodavanjem (na primer), kolone sa kućnim adresama prodavaca, može se koristiti sledeći kod:

		
ALTER TABLE
	prodavci
ADD
	kucna_adresa varchar(255) NOT NULL
AFTER
	email;

Slika 33. - SQL upit za dodavanje nove kolone.

Ako je iz tabele potrebno ukloniti određenu kolonu, * može se koristiti komanda DROP (u kombinaciji sa komandom ALTER TABLE):

		
ALTER TABLE
	prodavci
DROP
	kucna_adresa

Slika 34. - SQL upit za uklanjanje postojeće kolone.

Implementacija relacija u MySql-u

Iako se korisnici baza podataka u većini svakodnevnih situacija sreću sa relacijom 1:n, svakako je potrebno biti upoznat i sa načinom za implementiranje relacije 1:1 (sa kojom ćete se sretati bar ponekad), a pogotovo je potrebno biti upoznat sa načinom za implementiranje relacije n:n (koja se zapravo sreće prilično često).

Implementacija relacije 1:n

Pri implementaciji relacije 1:n, ne moraju se preduzimati dodatni koraci (budući da je u pitanju podrazumevana relacija).

Za primer (i podsećanje), uzećemo tabele prodavci i prodaja (koje smo već koristili) i, ukoliko se pri definisanju polja ne navedu dodatna ograničenja ....

		
CREATE TABLE prodaja
(
	--ostala polja,
	prodavac_id int NOT NULL,
	FOREIGN KEY (prodavac_id) REFERENCES prodavci(id)
)

Slika 35. - SQL kod za definisanje novog polja (ako se ne navede da se podatak u koloni može pojaviti samo jednom, važi relacija "jedan prema više").

.... bilo koji od entiteta koji se referenciraju preko spoljnjeg ključa, moći će u tabeli da se pojavi više puta (u gornjem primeru: prodavac, predstavljen preko spoljnjeg ključa prodavac_id, moći će da se pojavi u proizvoljnom broju slogova u tabeli prodaja).

Implementacija relacije 1:1

Relacija 1:1 takođe se definiše na jednostavan način, tako što se uz odgovarajuće polje navodi rezervisana reč UNIQUE.

Uzmimo (ponovo) za primer gradove, države i glavne gradove:

baze_podataka_erm_01 — Slika 36. - Dijagram baze podataka "glavni_gradovi".

Tabele sa prethodnog dijagrama mogu se kreirati i povezati preko sledećeg SQL koda:

		
CREATE DATABASE IF NOT EXISTS mini_geografija
	CHARACTER SET utf8,
	COLLATE utf8_general_ci;

USE mini_geografija;

CREATE TABLE grad
(
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	naziv varchar(255) NOT NULL,
	broj_stanovnika int NOT NULL
);

CREATE TABLE drzava
(
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	naziv varchar(255) NOT NULL,
	broj_stanovnika int NOT NULL
);

CREATE TABLE glavni_gradovi
(
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,

	-- uz polje grad_id mora stajati odrednica UNIQUE
	grad_id int NOT NULL UNIQUE,
	
	-- uz polje drzava_id (praktično) NE SME
	-- stajati odrednica UNIQUE
	drzava_id NOT NULL,
	
	FOREIGN KEY (grad_id) REFERENCES gradovi(id),
	FOREIGN KEY (drzava_id) REFERENCES drzave(id)
)

Slika 37. - SQL kod preko koga se definiše struktura baze podataka "glavni_gradovi" (možemo primetiti da je polje "grad_id" definisano preko odrednice UNIQUE).

Ranije smo razmotrili da se grad u tabeli glavni_gradovi može pojaviti jednom, a država više puta, i stoga, pri definiciji polja tabele glavni_gradovi: uz grad (grad_id), stoji odrednica UNIQUE, dok uz državu (drzava_id), ne stoje nikakve dodatne odrednice.

Implementacija relacije n:n

Videli smo da se relacije 1:1 i (pogotovo) 1:n, implementiraju na vrlo jednostavan način, međutim, za implementaciju relacije n:n potrebno je malo više pažnje, i dodatna tabela.

Ako se vratimo na primer sa povezivanjem glumaca i filmova, može se primetiti da sledeći format sloga u tabeli glumci (kao što smo već nagovestili prvi put kada smo pomenuli primer) ....

		
id:      1,
ime:     "Danilo",
nadimak: "Bata",
prezime: "Stojković",
filmovi: "Varljivo Leto '68; Ko to tamo peva; Maratonci trče počasni krug;"

Slika 38. - Primer (veoma) lošeg pokušaja definisanja relacije "više prema više".

.... umnogome podseća na neodgovarajuće formate za povezivanje entiteta (sa kojima smo se već sretali u uvodnim poglavljima članka), i može se zaključiti da nije u pitanju optimalno rešenje za implementaciju relacije n:n.

Budući da zapis podataka preko jedne tabele nije optimalno rešenje, relacija n:n se u praksi tipično implementira preko posredničke tabele (preko koje se spajaju id-ovi odgovarajućih entiteta):

baze_podataka_erm_02 — Slika 39. - Dijagram baze podataka "glumci_i_filmovi".

Jedna od osnovnih tabela (u konkretnom primeru), sadrži podatke o glumcima:

		
CREATE TABLE glumci
(
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	ime varchar(255) NOT NULL,
	prezime varchar(255) NOT NULL,
	datum_rodjenja datetime NOT NULL
)

Slika 40. - SQL kod za kreiranje tabele "glumci".

Druga osnovna tabela sadrži podatke o filmovima:

		
CREATE TABLE filmovi
(
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	naziv varchar(255) NOT NULL,
	godina_proizvodnje int NOT NULL
)

Slika 41. - SQL kod za kreiranje tabele "filmovi".

.... a preko dodatne (posredničke) tabele, entiteti iz prve tabele (glumci), spajaju se sa entitetima iz druge tabele (filmovima):

		
CREATE TABLE povezivanje
(
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	glumac_id int NOT NULL,
	film_id int NOT NULL,

	FOREIGN KEY (glumac_id) REFERENCES glumci(id),
	FOREIGN KEY (film_id) REFERENCES filmovi(id)
)

Slika 42. - SQL kod za kreiranje posredničke tabele "povezivanje" (preko koje je praktično implementirana relacija n:n).

Preko posredničke tabele, omogućili smo povezivanje proizvoljnog broja glumaca sa proizvoljnim brojem filmova (a budući da smo uključili proveru referencijalnog integriteta, možemo se osloniti na to da će sam DBMS voditi računa da se u tabeli povezivanje ne pojave glumci koji ne postoje u bazi, niti filmovi koji ne postoje u bazi).

Za kraj smo ostavili nešto složenije upite.

Ugnežđeni upiti

Ugnežđavanje upita (prva kategorija kompleksnijih upita o kojima ćemo diskutovati u uvodnom članku), podrazumeva korišćenje određenog upita unutar drugog upita.

Za primer ćemo uzeti operaciju koju smo nagovestili na početku: pronalaženje (svih) podataka o prodavcu koji je obavio određenu prodaju (koja je zabeležena u tabeli prodaja).

Ako se pitate "u čemu je (tačno) problem", podsetićemo se na organizaciju podataka koju smo (takođe) uveli na početku:

u tabeli prodaja upisani su samo id-ovi prodavaca (iz tabele prodavci)
u tabeli prodavci ne postoje podaci o prodaji artikala (to jest, podaci o tome ko je od prodavaca prodao koje artikle)

Problem se može rešiti preko sledećeg upita ....

		
SELECT
	ime, prezime, email
FROM
	prodavci
WHERE
	prodavci.id = (SELECT prodaja.prodavac_id FROM prodaja WHERE prodaja.id=3)

Slika 43. - Ugnežđeni SQL upit za pronalaženje podataka o prodavcu, preko id-a iz tabele prodaja.

.... koji prvo pronalazi id prodavca, posle čega se nađeni id koristi u upitu koji pretražuje tabelu prodavci.

Da pojasnimo:

prvo se izvršava unutrašnji upit (koji je obuhvaćen zagradama)
navedeni upit se svodi na jedan brojčani podatak
brojčani podatak koristi se kao kriterijum za pretragu u WHERE klauzi glavnog upita

Po koracima:

- "Unutrašnji" SELECT upit ....

		
SELECT prodaja.prodavac_id FROM prodaja WHERE prodaja.id=3

Slika 44. - Deo upita sa prethodne slike ("unutrašnji upit").

.... praktično pronalazi (samo) id prodavca koji je u tabeli prodaja povezan sa određenom prodajom.

- Rezultat izvršavanja unutrašnjeg upita je jedna celobrojna vrednost, posle čega 'glavni' upit praktično postaje ....

		
SELECT
	ime, prezime, email
FROM
	prodavci
WHERE
	prodavci.id = 2

Slika 45. - Posle izvršavanja "unutrašnjeg" upita, "glavni" upit se svodi na pronalaženje prodavca preko id-a.

.... što je sintaksa koja (sama po sebi) predstavlja vrlo jednostavan upit (nalik upitima sa kakvima smo se susreli već na početku).

Pridruživanje podataka (JOIN)

Pre nego što završimo uvod u relacione baze podataka, sagledaćemo nekoliko primera koje smo nagovestili na početku (i upoznaćemo se sa 'tehnikalijama' koje stoje iza svega).

Najvažniji primer tiče se kreiranja pregleda prodaje u kome su prodavci predstavljeni imenom i prezimenom.

Preko komande JOIN moguće je spojiti (i potom prikazati), podatke iz dve ili više tabela, pri čemu se kao kriterijum za spajanje koriste podaci koji se pojavljuju u svim tabelama koje učestvuju u spajanju (naravno, kriterijumi se moraju navesti precizno).

Izvršavanjem sledećeg upita:

		
SELECT
	prodaja.datum,
	prodaja.artikl,
	prodaja.cena,
	prodaja.kolicina,
	prodavci.ime,
	prodavci.prezime
FROM
	prodavci INNER JOIN prodaja
ON
	prodavci.id = prodaja.prodavac_id

Slika 46. - Primer upotrebe komande INNER JOIN, preko koje se objedinjuju podaci iz više tabela (u ovom slučaju dve), preko određenog kriterijuma, to jest, preko podataka koji se pojavljuju u obe tabele.

.... u pregledu se pojavljuju sve pojedinačne prodaje artikala - uz koje stoji ime i prezime prodavca:

		
datum      | artikl   | cena   | kolicina | ime   | prezime
-------------------------------------------------------------
2020-06-15 | Sveska   | 80     | 4        | Petar | Jovanović
2020-06-15 | Olovka   | 56.57  | 2        | Petar | Jovanović
2020-06-15 | Gumica   | 34.96  | 2        | Ivan  | Kovačević
2020-06-15 | Selotejp | 134.57 | 1        | Ivan  | Kovačević

Slika 47. - Rezultat izvršavanja upita sa slike #46.

Verujemo da se na ovom mestu (sasvim prirodno) javljaju i pitanja: kako se podaci zapravo izdvajaju i spajaju, i kako će (tačno) slogovi biti sortirani?

Kako se podaci spajaju i izdvajaju?

Pretpostavljamo da prepoznajete zašto smo praktičan rezultat izvršavanja komande za spajanje dve tabele prikazali odmah (to jest, pre 'uvodne teorije') - nismo želeli da vas ostavimo pod utiskom da ćete naredne odeljke čitati "tek onako". :)

Upoznavanje sa teorijom nećemo preskakati (naravno), ali, na ovom mestu pre svega moramo primetiti da glavni primer koji koristimo u članku (baza podataka o prodaji artikala), nije u stanju da obuhvati sve tipične situacije do kojih može doći pri spajanju podataka preko komande JOIN, i stoga ćemo se privremeno poslužiti drugim primerom - preko koga ćemo bolje objasniti teoriju.

Dodatni primer biće baza podataka preko koje se beleže podaci vezani za upis studenata na kurseve (na početku akademske godine), pri čemu će baza biti implementirana preko sledećih tabela:

studenti - podaci o studentima
kursevi - podaci o kursevima
upis - pomoćna tabela preko koje se povezuju podaci iz prve dve tabele

baze_podataka_erm_03 — Slika 48. - Dijagram baze podataka "upis_studenata".

Pre nego što se osvrnemo na različite varijacije komande JOIN, osvrnimo se na različite situacije opšteg tipa, koje se daju zamisliti tokom upisa studenata na kurseve. *

Dok upis (još uvek) traje, u određenom trenutku može nastati jedna od sledećih situacija:

među studentima ima onih koji se još uvek nisu prijavili ni za jedan kurs
postoje kursevi za koje nema prijava (i naravno ....)
postoje kursevi za koje je prijavljen određen broj studenata

.... pri čemu je potrebno da postoji sistem koji omogućava "očitavanje trenutnog stanja".

Različite varijante komande JOIN - mogu pomoći u rešavanju postavljenog zadatka.

Razmatraćemo šematski prikaz, u kome su tabele popunjene tako da je određen broj studenata (s1-s4), prijavljen za određen broj kurseva (k1-k3, k5), preko tabele upis:

baze_podataka_join_01 — Slika 49. - Šematski prikaz popunjenih tabela u bazi podataka "upis_studenata".

INNER JOIN

U opštem smislu, preko sledeće naredbe ....

		
SELECT
	polja
FROM
	T1 INNER JOIN T2 on T1.px=T2.py

Slika 50. - Opšta šema komande INNER JOIN.

.... prikazuju se svi slogovi iz obe tabele za koje važi da polje px iz tabele T1 ima istu vrednost kao polje py iz tabele T2.

Međutim, pravi smisao (različitih varijacija) komande JOIN, gotovo je nemoguće razumeti bez konkretnog konteksta, i stoga ćemo se usmeriti (upravo), na konkretan primer sa upisom studenata na kurseve (koji je doduše složeniji sam po sebi, i podrazumeva da se dve tabele povezuju preko posredničke tabele).

U primeru: ako se za spajanje koristi komanda INNER JOIN, upit će izdvojiti samo one slogove iz tabele studenti koji su se pojavili u tabeli upis, i samo one slogove iz tabele kursevi koji su se pojavili u tabeli upis.

		
SELECT
	CONCAT(studenti.ime, " ", studenti.prezime) AS "Student",
	studenti.br_indeksa,
	kursevi.naziv AS "Kurs"
FROM
	(studenti INNER JOIN upis ON studenti.id=upis.student_id)
	INNER JOIN kursevi ON kursevi.id=upis.kurs_id
ORDER BY
	studenti.id, kursevi.id

Slika 51. - Primer izvršavanja komande INNER JOIN nad tabelama u bazi podataka "upis_studenata".

U praktičnom smislu: u pregledu će se pojaviti samo studenti koji su prijavljeni za kurseve i samo oni kursevi za koje postoje prijave.

baze_podataka_join_02 — Slika 52. - Šematski prikaz rezultata izvršavanja upita sa slike #51.

LEFT JOIN

U opštem smislu, preko sledeće naredbe ....

		
SELECT
	polja
FROM
	T1 LEFT JOIN T2 on T1.px=T2.py

Slika 53. - Opšta šema komande LEFT JOIN.

.... prikazuju se svi slogovi iz obe tabele za koje važi da polje px iz tabele T1 ima istu vrednost kao polje py iz tabele T2 (kao u slučaju kada se poziva komanda INNER JOIN), ali, prikazuju se i svi ostali slogovi iz tabele T1 (to jest, slogovi iz "leve" tabele).

U primeru: ako se za spajanje koristi komanda LEFT JOIN, upit će izdvojiti sve slogove iz tabele studenti, i samo one slogove iz tabele kursevi koji su se pojavili u tabeli upis.

		
SELECT
	CONCAT(studenti.ime, " ", studenti.prezime) AS "Student",
	studenti.br_indeksa,
	kursevi.naziv AS "Kurs"
FROM
	(studenti LEFT JOIN upis ON studenti.id=upis.student_id)
	LEFT JOIN kursevi ON kursevi.id=upis.kurs_id
ORDER BY
	studenti.id, kursevi.id

Slika 54. - Primer izvršavanja komande LEFT JOIN nad tabelama u bazi podataka "upis_studenata".

U praktičnom smislu: u pregledu će se pojaviti svi studenti i samo oni kursevi za koje postoje prijave.

baze_podataka_join_03 — Slika 55. - Šematski prikaz rezultata izvršavanja upita sa slike #54.

U prethodnom slučaju (kada smo koristili komandu INNER JOIN): po izvršavanju upita, pored podataka o svakom studentu bio je naveden i jedan od kurseva (to nismo prikazali na "plavo-zelenim" šemama, ali, možete primetiti ako pokrenete upite).

U slučaju komande LEFT JOIN, pored podataka o studentu koji nije prijavljen za kurs, stajaće vrednost NULL (na slici, simbolično označeno tamnijom nijansom plave).

Na ovaj način (i s obzirom na metodu sortiranja koju smo izabrali), u rezultujućoj tabeli lako se mogu primetiti studenti koji nisu prijavljeni za kurseve.

RIGHT JOIN

U opštem smislu, preko sledeće naredbe ....

		
SELECT
	polja
FROM
	T1 RIGHT JOIN T2 on T1.px=T2.py

Slika 56. - Opšta šema komande RIGHT JOIN.

.... prikazuju se svi slogovi iz obe tabele za koje važi da polje px iz tabele T1 ima istu vrednost kao polje py iz tabele T2, a prikazuju se i sva polja iz tabele T2 (tj. iz "desne" tabele).

U primeru: ako se za spajanje koristi komanda RIGHT JOIN, upit će izdvojiti samo one slogove iz tabele studenti koji su se pojavili u tabeli upis i (ovoga puta), sve slogove iz tabele kursevi.

		
SELECT
	CONCAT(studenti.ime, " ", studenti.prezime) AS "Student",
	studenti.br_indeksa,
	kursevi.naziv AS "Kurs"
FROM
	(studenti RIGHT JOIN upis ON studenti.id=upis.student_id)
	RIGHT JOIN kursevi ON kursevi.id=upis.kurs_id
ORDER BY
	studenti.id, kursevi.id

Slika 57. - Primer izvršavanja komande RIGHT JOIN nad tabelama u bazi podataka "upis_studenata".

U praktičnom smislu: u pregledu će se pojaviti prijavljeni studenti i svi kursevi, i stoga (s obzirom na izabranu metodu sortiranja), biće lako uočiti kurseve za koje ne postoje prijave (na slici označeno tamnijom nijansom zelene).

baze_podataka_join_04 — Slika 58. - Šematski prikaz rezultata izvršavanja upita sa slike #57.

OUTER JOIN

U opštem smislu, preko sledeće naredbe ....

		
SELECT
	polja
FROM
	T1 FULL OUTER JOIN T2 on T1.px=T2.py

Slika 59. - Opšta šema komande OUTER JOIN.

.... prikazuju se svi slogovi iz obe tabele za koje važi da polje px iz tabele T1 ima istu vrednost kao polje py iz tabele T2, ali - prikazuju se i ostali slogovi iz obe tabele.

U primeru: ako se za spajanje koristi komanda OUTER JOIN, upit će prikazati sve studente i sve kurseve.

		
SELECT
	CONCAT(studenti.ime, " ", studenti.prezime) AS "Student",
	studenti.br_indeksa,
	kursevi.naziv AS "Kurs"
FROM
	(studenti FULL OUTER JOIN upis ON studenti.id=upis.student_id)
	FULL OUTER JOIN kursevi ON kursevi.id=upis.kurs_id
ORDER BY
	studenti.id, kursevi.id

Slika 60. - Primer izvršavanja komande OUTER JOIN nad tabelama u bazi podataka "upis_studenata".

Ukoliko je količina podataka umerena, ovakav pregled može (na primer) biti od koristi ako se rezultujuća tabela odštampa (pa 'odokativnom' metodom možemo primetiti informacije koje nas zanimaju).

baze_podataka_join_05 — Slika 61. - Šematski prikaz rezultata izvršavanja upita sa slike #60.

U svakom slučaju, kako god da su podaci izdvojeni, ostaje pitanje kako će slogovi u rezultujućoj tabeli biti sortirani.

Sortiranje podataka

Ako se pri prosleđivanju upita ne navede kriterijum za sortiranje, DBMS će samostalno izabrati način sortiranja (prema rasporedu podataka u memoriji i sl), i stoga - ukoliko je potrebno da podaci budu precizno sortirani prema određenom kriterijumu - svakako je najbolje da kriterijum navedemo sami.

U primerima sa upisom studenata, "prećutno" je izabran odgovarajući metod sortiranja, međutim, za primer iz baze prodaja, 'ručno' ćemo podesiti da podaci budu sortirani po datumu prodaje - ORDER BY prodaja.datum:

		
SELECT
	prodaja.datum,
	prodaja.artikl,
	prodaja.cena,
	prodaja.kolicina,
	prodavci.ime,
	prodavci.prezime
FROM
	prodavci INNER JOIN prodaja
ON
	prodavci.id = prodaja.prodavac_id
ORDER BY
	prodaja.datum

Slika 62. - Primer upotrebe komande INNER JOIN - sa preciziranim kriterijumom za sortiranje slogova.

Uprošćena sintaksa za INNER JOIN i još jedan praktičan primer

SQL dozvoljava da se upit za spajanje podataka, za koji bi inače bilo potrebno koristiti komandu INNER JOIN, pozove preko drugačije sintakse (koja je, reklo bi se, elegantnija):

		
SELECT
	prodaja.datum,
	prodaja.artikl,
	prodaja.cena,
	prodaja.kolicina,
	prodavci.ime,
	prodavci.prezime
FROM
	prodavci, prodaja
WHERE
	prodavci.id = prodaja.prodavac_id
ORDER BY
	prodaja.datum

Slika 63. - Alternativni način pozivanja komande INNER JOIN.

Prikazani pristup deluje "manje zvanično" (budući da se ne koristi komanda INNER JOIN), ali - rezultat je (praktično) isti.

Preostaje da se osvrnemo na još jedan primer (koji možete isprobati i preko uprošćene sintakse koju smo maločas prikazali).

Može se primetiti da polja cena i kolicina ne upućuju 'baš najbolje' na pravi smisao podataka koji se pojavljuju u kolonama: za cenu nije naznačeno da li je u pitanju cena po jedinici količine (ili ukupna cena za artikl, koja uzima u obzir i količinu), nije precizirano da li je u cenu uračunat porez (ili nije), za količinu nije naznačena jedinica mere (a mogli bismo navesti i druge primedbe).

Takođe (što se tiče prodavaca), izdvajanje imena i prezimena u zasebne kolone, deluje "previše zvanično" u ovakvom upitu.

Shodno svemu što smo naveli, upit se može sažeti i upotpuniti na sledeći način:

		
SELECT
	prodaja.artikl,
	CONCAT(prodaja.cena * prodaja.kolicina, "din.")
		AS "Cena po artiklu (bez PDV-a)",
	CONCAT(prodavci.ime, " ", prodavci.prezime)
		AS "Prodavac"
FROM
	prodavci INNER JOIN prodaja
ON
	prodavci.id = prodaja.prodavac_id
ORDER BY
	prodaja.datum

Slika 64. - Praktičan primer upita koji koristi INNER JOIN, obradu podataka iz više kolona (cena * količina; konkatenacija niski ime i prezime) i takođe, alijase.

vrednosti polja cena i kolicina (iz tabele prodaja), množe se unutar funkcije CONCAT, a rezultat množenja se predstavlja preko aliasa "Cena po artiklu (bez PDV-a)"
podaci o prodavcu se spajaju preko funkcije CONCAT *

Pokretanjem gornjeg upita, dobija se tabela sledećeg sadržaja:

		
artikl   | Cena po artiklu (bez PDV-a) | Prodavac
--------------------------------------------------------
Sveska   | 320                         | Petar Jovanović
Olovka   | 113.14                      | Petar Jovanović
Gumica   | 69.92                       | Ivan Kovačević
Selotejp | 134.57                      | Ivan Kovačević

Slika 65. - Rezultat izvršavanja upita sa slike #64.

Za kraj ....

Posle teoretske diskusije i praktičnih primera, osvrnućemo se takođe (za sam kraj), na svojevrstan "paradoks" koji ćete verovatno uočiti čim budete počeli da se bavite bazama podataka u 'produkcijskom' okruženju (pri čemu će situacija najverovatnije delovati .... "pomalo čudno").

Sa jedne strane, sasvim je moguće da ćete se odmah susresti sa projektima koji će od vas nedvosmisleno zahtevati da dodatno unapredite poznavanje baza podataka (u odnosu na nivo koji je prikazan u članku), dok - sa druge strane - lako možete doći i u kontakt sa projektima (koji su prilično ozbiljni sami po sebi), u kojima se koristi znatno manji podskup SQL komandi za rad sa bazama (u odnosu na opcije koje su prikazane u članku).

Ne dajte se zbuniti.

Prva situacija je jasna sama po sebi, a za drugu situaciju takođe postoji sasvim jednostavno objašnjenje: u mnogim sistemima nema potrebe za "ekstravagantnim zahvatima" nad bazama podataka, već je samo potrebno da baza podataka - isporučuje podatke (na brz i pouzdan način).

Sledeći korak u vezi sa bazama podataka (u smislu članaka koje ćemo objaviti - i nešto čemu ćemo se posvetiti u (vrlo) doglednoj budućnosti), biće korišćenje baza podataka u programskim jezicima (pre svega MySql i PHP, ali, biće i drugih primera).

Autor članka Nikola Vukićević Za web portal codeblog.rs

Napomena: Tekstovi, slike, web aplikacije i svi ostali sadržaji na sajtu codeblog.rs (osim u slučajevima gde je drugačije navedeno) predstavljaju intelektualnu svojinu autora sajta codeblog.rs i zabranjeno je njihovo korišćenje na drugim sajtovima i štampanim medijima, kao i bilo kakvo drugo korišćenje u komercijalne svrhe, bez eksplicitnog pismenog odobrenja autora.