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 se tipično 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, ili jednostavnije - 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 se može 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 takođe, 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 kroz sve korake u projektovanju.

Prva tabela sadržaće podatke o prodavcima i neće se mnogo (gotovo nimalo), razlikovati 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 ("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 ručno tražimo podatke o prodavcu u tabeli prodavci, ne možemo biti sigurni da čitamo prave podatke (iz gore navedenih razloga)

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

U pitanju je format koji nije ni malo elegantan i pregledan (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 je manje očigledno na prvi pogled, zbog manjeg obima podataka), pa 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, u tabeli prodaja, jedinstveni prodavac bude naveden 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 i 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 - da se svakom prodavcu pripiše jedinstven identifikacioni broj i da se potom tabele povežu (s tim da ćemo, kasnije, detaljnije prodiskutovati o tome zašto smo napravili baš takav izbor).

Primarni ključ

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.

Jedinstveni podatak u okviru jednog sloga, po kome je moguće prepoznati ceo slog (na slikama - red u tabeli), naziva se primarni ključ.

U praksi, za primarni ključ se gotovo uvek koriste celobrojne vrednosti (praktično - redni brojevi slogova), a kolona se tipično označava sa "id".

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 (a 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 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 (spoljnjeg) ključa "prodavac_id" u tabeli "prodaja".

Kada se primarni ključ iz jedne tabele upotrebi u drugoj tabeli (radi prepoznavanja podataka iz prve tabele), takav podatak nosi naziv 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 i u navedenim odeljcima 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 redovima), 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 (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.

U našem primeru, 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.

Stoga ćemo za primarni ključ izabrati jedinstven celobrojni podatak iz kolone "id", ali - u praksi se najčešće koriste oba ključa u jednom slogu, to jest, nikako se nećemo "odreći" prirodnog primarnog ključa zato što smo uveli veštački.

"Veštački" primarni ključ (id) je stvar 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 se (rekli bismo), na ovom mestu "postavlja samo od sebe": zašto nismo koristili JMBG?

(Kratak odgovor je sledeći: zarad očuvanja preglednosti članka, JMBG nismo ni upisivali; 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 situaciji 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, za primarni ključ bismo (gotovo sigurno) - izabrali id (int).

U svakom slučaju - bez obzira na to što smo tabeli dodali veštački primarni ključ, krajnje je neophodno da prepoznamo (i koristimo), i polje koje predstavlja prirodni primarni ključ (ili kombinaciju polja, koja zajedno predstavljaju primarni ključ) - i moramo biti vrlo pažljivi pri dodavanju slogova (to jest, potrebno je da implementiramo mehanizam koji detaljno proverava podatke).

Da pojasnimo ....

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 za proveru novog prodavca (u smislu toga da li prodavac sa navedenim podacima već postoji u tabeli), moramo koristiti ostale podatke.

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), možemo 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), dok - ukoliko među podacima nema prirodnog primarnog ključa - zapravo nemamo načina da raspoznajemo slogove!

Entiteti i atributi

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

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 i za svaki entitet je vezan jedinstven primarni ključ.

Takođe (kao što smo već videli), entiteti iz jedne tabele se mogu navoditi u drugim tabelama preko (sekundarnih/spoljnjih) 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 se (kao entiteti), pojavljuju u tabeli prodaja - preko spoljnjih ključeva (a mogu se naravno pojavljivati i u drugim tabelama)

Atribut(i)

Pojam atributa označava pojedinačni podatak koji se koristi pri definisanju određenog entiteta, a u opštijem smislu, može se reći i to da atribut predstavlja jednu od zajedničkih osobina svih entiteta određene kategorije.

Da pojasnimo:

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, razlikujemo sledeće tipove 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 (preko spoljnjeg ključa), u tabeli T2 pojaviti proizvoljan broj puta:

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 se svakako 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".

Kako 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/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, mogli bismo spojiti id-ove trenera, 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 povezano sa tabelom klubovi, takođe 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), pa 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, pa stoga između tabele države i tabele glavni_grad zapravo stoji veza 1:n (a ne 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 da bi pokušaj da (na primer), u tabeli glumci, u jednom slogu navedemo sve filmove koji se vezuju za glumca, veoma podsećao na "pokušaj" (koji smo videli ranije), da u tabeli prodaja navedemo sve podatke o prodavcu, i stoga - "razmišljamo dalje" ....

Za sada je najbitnije znati da se podaci ne mogu na efikasan način povezati preko jedne tabele, to jest - da je potrebna posrednička tabela - međutim, bez obzira na to što smo mnoge detalje već nagovestili u prethodnim odeljcima, detaljniju 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 (i takođe 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, raspoređene su 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 (koja se, u opštem smislu, takođe naziva 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) ne zapisuju direktno u drugim tabelama (š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.

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 bi sadržala redni broj, dok bismo u drugu kolonu upisivali "sve ostale" podatke, što bi (na primeru jednog sloga), moglo 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: ukoliko je potrebno doći do nekog od "unutrašnjih" podataka, morali bismo - umesto da podatak pročitamo neposredno - proći kroz nisku znakova (polje osoba je samo obična niska), i potom bismo morali izdvajati delove koji nas zanimaju (a ukoliko neki od izdvojenih podataka nije tekstualnog tipa, izvesna količina procesorskog vremena bila bi utrošena i 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, za svaki podatak - kao što smo već nagovestili - koristi se odgovarajući tip.

U tabeli koju projektujemo, za id treba izabrati celobrojni tip, za ime, prezime i e-mail - niske, dok će za datum biti korišćen specijalizovani tip podatka (datetime), koji nije običan tekst, već podatak brojčanog tipa.

Sada možemo 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 i 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", i u svemu 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 prevazilaze okvire uvodnog članka, koje ćemo za sada preskočiti).

Upiti se pokreću unosom (unapred definisanih), komandi programskog jezika SQL.

* 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", pa tako 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 da iza mnogih manje očiglednih primera (kao što su na primer 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 se (kada se pozove određeni URL), popunjavaju podacima koji se 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, a pošto je u pitanju pristup koji se razlikuje od proceduralnih jezika, napravićemo malu digresiju i objasniti razliku.

Jezici u kojima je potrebno detaljno definisati postupak za rešavanje određenog problema (primer takvog jezika je C), pripadaju kategoriji proceduralnih programskih jezika.

Nasuprot tome, 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.

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 (a to je, kao što smo već nagovestili - MySql).

Kratak uvod u MySql

MySql je open-source sistem za upravljanje bazama podataka koji se može instalirati samostalno, a dolazi i uz XAMPP, paket aplikacija o kome smo pisali u članku o pokretanju lokalnog web servera.

U pitanju je kvalitetan i jednostavan * DBMS koji se koristi na brojnim sajtovima, omogućava veliku brzinu i efikasnost u obradi podataka - i nije težak za savladavanje.

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 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 želimo da budemo "skroz zvanični", možemo koristiti upotpunjen i sveobuhvatan upit ....

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

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

.... koji će biti izvršen samo ukoliko baza sa navedenim imenom ne postoji (a usput smo definisali i metod enkodiranja 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 dva nezavisna upita 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, razmotrićemo 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 (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, kojim 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, odnosi se upravo na mere koje DBMS preduzima da onemogući korisnike da se pozivaju na podatke (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).

		
ime    | prezime   | datum_rodjenja | email
---------------------------------------------------------------
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

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

Međutim, ukoliko želimo (zarad preglednosti), da prikažemo samo određena polja, * ili da u prikazu rezultata dodamo polja koja podrazumevaju obradu postojećih polja, ** možemo polja 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 sadržaj tabele 'prodaja' možemo prikazati na pregledniji način (podatke iz kolona 'cena' i 'količina' smo množili, a određenim poljima smo pripisali i takozvane alijase, alternativne nazive).

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 #21. - 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, i - u tom smislu:

za filtriranje se koristi klauza WHERE (uz precizno navođenje uslova)
za sortiranje se koristi naredba 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), a tabela će 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 (koja precizira koje slogove je potrebno ažurirati) - biće ažurirani svi slogovi, pri čemu (u iole praktičnom smislu) - ne postoji "undo"!

(U primeru, uz klauzu WHERE id=1, upit 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 moramo biti veoma pažljivi pri pozivanju upita za ažuriranje i da ne smemo izostaviti uslov (osim naravno ukoliko nam jeste namera da polje u svim redovima dobije istu vrednost). *

Sa komandom DELETE moramo biti još pažljiviji, jer je upit za brisanje 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 komande DROP uklanjaju postojeće kolone (naravno, sa pripadajućim sadržajem).

Ako je u tabelu prodavci potrebno dodati (na primer), kolonu 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 implementacije relacije 1:1 (sa kojom ćete se sretati bar ponekad), a pogotovo je potrebno biti upoznat sa načinom implementacije 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 pri definisanju polja ne navedemo 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 ne navedemo da se podatak u koloni može pojaviti samo jednom, važiće 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".

Ranije smo razmotrili da se grad u tabeli glavni_gradovi može pojaviti jednom, a država više puta, pa 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, vidimo 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), zbog čega se može reći 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 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 (naravno) 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
navedeni 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 - obavio određenu prodaju.

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 sa kakvim smo se susreli već na početku.

Pridruživanje podataka (JOIN)

Za sam kraj, sagledaćemo još jedan primer koji smo nagovestili na početku (i upoznati se sa 'tehnikalijama' koje stoje iza svega).

Zadatak je: napraviti pregled 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, a rezultat izvršavanja je sledeća tabela:

		
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 #39.

Na ovom mestu (rekli bismo sasvim prirodno), postavlja se i pitanje: kako se zapravo podaci 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 (umesto posle uvodnog teoretskog dela) - nismo želeli da vas ostavimo pod utiskom da ćete naredne odeljke čitati "tek onako". :)

Upoznavanje sa teorijom (naravno) nećemo izbegavati, ali, na ovom mestu se pre svega mora primetiti da glavni primer koji koristimo u članku (baza podataka o prodaji artikala), nije u stanju da obuhvati sve 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 koja beleži upis studenata na kurseve (na početku akademske godine), i biće implementirana preko sledećih tabela:

studenti - podaci o studentima
kursevi - podaci o kursevima
upis - pomoćna tabela preko koje se povezuju podaci iz prethodne 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 (pre svega), na različite situacije opšteg tipa, koje se daju zamisliti tokom upisa studenata na kurseve. *

Dok upis (još uvek) traje, lako je zamisliti, da - u određenom trenutku:

među studentima ima onih koji se još 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 da se dođe do navedenih informacija, a različite varijante komande JOIN - mogu nam pomoći da rešimo prethodno navedeni zadatak.

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 na konkretan primer (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 se prijavili za kurseve i samo oni kursevi za koje postoje prijave.

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

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, 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 #45.

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 (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 će, ovoga puta (s obzirom na izabranu metodu sortiranja), biti 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 #47.

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 #49.

U svakom slučaju, kako god da smo izdvojili podatke, 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 smo "prećutno" birali odgovarajući metod sortiranja, a za primer iz baze prodaja smo izabrali 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 bismo inače koristili INNER JOIN, pozove preko drugačije (i reklo bi se - elegantnije) sintakse:

		
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.

.... što deluje "manje zvanično" (budući da ne koristimo komandu INNER JOIN), ali - rezultat je isti.

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

Primećujemo 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 još detalja).

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 #53.

Za kraj ....

Na samom kraju, osvrnućemo se 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 susresti sa projektima koji od vas nedvosmisleno zahtevaju 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, vrlo često, 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 jasna je sama po sebi, dok - 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).