PROJEKTIRNJE INFORMACIJSKIH SUSTAVA
Osnove bazi podataka + Osnove SQL-a
PONAVLJANJE ZA STUDENTE
Tomislav Gligora spec.ing.
BAZA PODATAKA
Baza podataka je strukturirana kolekcija zapisa podataka. U nju pohranjujemo fiziĉke zapise. Baza podataka je raĉunalna aplikacija koja se sastoji od podataka, metapodataka i podprograma. TakoĊer baza podataka je objekt koji sadrži tablice i druge objekte radi pospremanja i obrade podataka. Dakle baza podataka pohranjuje, obraĊuje i izvještava o podacima koji su potrebni za funkcioniranje informacijskog sustava, a samim time i za poslovanje tvrtke. Podaci su vrijednosti, informacije koje unosimo u bazu podataka u njima predviĊena polja, dok metapodaci su ti koji nam daju informacije o podacima u bazi, oni nam govore o duljini i tipu svakog polja. Podprogrami u bazi podataka su procedure, funkcije, paketi i okidaĉi koji omogućavaju upravljanje podacima unutar baze podataka. TakoĊer za bazu podataka možemo reći da je implementacija ili kreacija fiziĉkog modela baze podataka. Dok je model baze podataka nacrt koji nam govori kako se podaci pohranjuju u bazi podataka.
RELACIJSKI MODEL BAZE PODATAKA
Relacijski model baze podataka je poboljšana verzija hijerarhijskog modela baze podataka. Glavna prednost relacijskog modela baze podataka je ta što se svakoj tablici može pristupiti bez dodatnog pristupa roditeljiskim tablicama. To nam omogućava ĉinjenica da u ovom modelu svaka tablica može biti povezana sa svakom tablicom bez obzira na njihov hijerarhijski poredak. Baza podataka se izraĊuje na temelju poslovnih pravila i poslovnih procesa. Jasno je da poslovna pravila i njezini podaci moraju biti sadržani u bazi podataka, jer u suprotnom informacijski sustav ne bi zadovljavao poslovni sustav organizacije za koju je predviĊen. Samim time što su poslovna pravila obuhvaćena u bazi podataka i poslovni procesi su sadržani u bazi, jer gotovo svaki poslovni proces obuhvaća jedno ili više poslovnih pravila. Prilikom izrade baze podataka prvi korak je izrada relacijskog modela (ER dijagarama – entity relationship). Njega dobivamo iz objašnjenog postupka obuhvaćanja poslovnih pravila i poslovnih procesa. ER dijagram je relacijska shema, koja nam oznaĉujemo u kojem obliku su definirane veze izmeĊu tablica. ER dijagram je kljuĉno polazište za daljnji razvoj baze podataka. Iz ER dijagrama vidimo tablice, definicije podataka koje unosimo, njihov tip i duljinu. TakoĊer iz ER dijagrama vidimo koji su primarni kljuĉevi, a koji su strani kljuĉevi. U ER dijagramu tablice se zove entitetima, a polja u tablici atributima.
Da bismo mogli bazu podataka fiziĉki napraviti, ažurirati i u krajnjem procesu funkcionalno implenemtirati, moramo poznavati SQL (Structured Query Language), što bi se moglo prevesti kao strukturirani jezik upita.
SQL – Structured Query Language
SQL omogućava da tvorimo i promijenimo strukturu baze podataka, dodamo prava korisniku za pristup bazama podataka ili tablicama, da tražimo informacije od baze podataka, zapravo su tu dvije skupine funkcija: DDL (Data Definition Language) funkcije za definiciju podataka i DML (Data Manipulation Language) funkcija za upravljanje podacima.
Primjer 1. Tipiĉan primjer DDL funkcije: CREATE TABLE imeTablice; Primjer 2. Tipiĉan primjer DML funkcije SELECT *FROM imeTablice;
PRAVILA PISANJA KODA SQL ne pravi razliku izmeĊu velikih i malih slova, što znaĉi da su sljedeće dvije naredbe jednake: select prezime from gosti where ime='Marko' ili SELECT prezime FROM gosti WHERE ime='Marko' Radi lakšeg ĉitanja koda tim naĉinom ćemo sve naredbe SQL-a dalje pisati. Preporuĉa se da kljuĉne rijeĉi (naredbe) budu napisane velikim slovima, a svi ostali elementi malim slovima, radi lakšeg ĉitanja i snalaženja po kodu. U nekim bazama niz znakova (string) mora biti napisan kao što je u bazi. Znaĉi u gornjim naredbama nije isto ako se piše 'Marko' ili 'MARKO', ali ne mora vrijediti za Microsoft SQL. Napravilniji naĉin pisanja SQL koda: SELECT prezime FROM gosti WHERE ime='Marko'
Komentari su tekst koji pišemo kao podsjetnik, a koji se neće izvršiti. Imamo ih dvije vrste: za samo jedan red:
-- ovo je komentar
tj. oznaka za komentar je --, a iza slijedi tekst komentara ili komentar kroz više redova: /* ovo je također komentar, ali se proteže kroz više redova */
Imena objekata u SQL-u (imena tablica, stupaca, pogleda) prave se tako da prvi znak mora biti a - z, A - Z ili podvuĉeno (underscore) _, te u nekim sluĉajevima @ i #. Na primjer u MS SQL-u imena mogu biti preko 100 znakova.
TIPOVI PODATAKA Pri kreiranju tablica odreĊujemo nazive stupaca te tip podataka koji će bit spremljen. To bi se moglo usporediti sa inicijalizacijom varijable u programiranju. Tipovi podataka su: CJELOBROJNI
- bit, int, smallint, tinyint
DECIMALNI
- decimal ili numeric
NOVAC
- money, smallmoney
POMIĈNI ZAREZ
- float, real
DATUMI
- datetime, smalldatetime
NIZOVI ZNAKOVA
- char, nchar, text, varchar, nvarchar
BINARNI
- binary, varbinary, image
FIZIĈKA IZRADA BAZE PODATAKA SQL naredba za kreiranje baze podataka je: CREATE DATABASE imeBaze;
I ona predstavlja prvi korak prilikom izrade fiziĉke baze podataka na hard disku raĉunala. Većina komercijalnih i najpoznatijih inaĉica SQL-a podržava naredbu CREATE DATABASE. Kreiranje baze podataka male turistiĉke tvrtke: CREATE DATABASE tourism_company;
Ukoliko iz bilo kojeg razloga želimo pobrisati bazu podataka, što u našem informacijskom sustavu neće biti sluĉaj, koristimo naredbu: DROP DATABASE imeBaze;
KREIRANJE TABLICA Svaka baza podataka, kao što je već naglašeno, se sastoji od tablica koje sadrže potrebne podatke. Pa stoga sljedeći korak u fiziĉkoj izradi baze podataka je izrada tablica i punjenje tablica pripadajućim podacima. Tablice predstavljaju dvodimenzionalne matrice ĉiji redovi predstavljaju naziv i svojstva objekata pohranjenih u tablicu, a stupci svojstva objekata izražena odgovarajućim tipom podataka. Uz pomoć jedne n-torke opisali smo jedan objekt. Tablica je objekt baze podataka u kojem se ĉuvaju podaci. Naredba za kreiranje tablice u bazi podataka je: CREATE TABLE imeTablice ( imeKolone1 tipPodatka [NOT NULL], imeKolone2 tipPodatka [NOT NULL], ...... imeKoloneN tipPodatka [NOT NULL] ) te u skladu sa bazom imamo i naredbu za brisanje tablice: DROP TABLE imeTablice
Nakon brisanja tablice bespovratno uništavamo podatke, strukturu tablice ili privremene tablice.
Primjer naredbe za kreiranje tablice Odjeli: CREATE TABLE Odjeli ( ID NUMBER(10) PRIMARY KEY, Naziv NVARCHAR(30) NOT NULL, Br_zaposlenih NUMBER(5) NOT NULL, Napomena TEXT(1000), UNIQUE (ID) )
Nakon izvedbe ove naredbe i fiziĉki smo na disku napravili tablicu Odjeli. Dakle naša tablica je napravljena sa pripadajućim kolonama: ID, Naziv, Br_zaposlenih, Napomena. Vidljivo je iz naredbe da moramo definirati tip podataka za svaku kolonu te ako želimo i maksimalnu duljinu znakova koju želimo zauzeti sa pojedinim podatkom u kolonama. TakoĊer definiramo NULL i NOT NULL vrijednosti. Sve kolone su NULL već pri samoj izradi tabele te NULL kao dio naredbe ne treba pisati, on se podrazumijeva; NULL ne znaĉi da je neki podatak u tablici nula već da se smije ostaviti prazna kolona u tablici ako nam je to potrebno. NOT NULL vrijednost nam predstavlja obavezno upisivanje podatka u kolonu koju smo na taj naĉin deklarirali, i NOT NULL kao takav se mora zapisati u okviru naredbe za kreiranje tablice.
PROMJENA STRUKTRE TABLICE NAKON NJENOG KREIRANJA Mnogo puta naš dizajn baze podataka nije uzeo u obzir sve što bi trebalo. TakoĊer zahtjevi za aplikacijama i bazama podataka su uvijek skloni promjenama. ALTER TABLE naredba omogućava administratoru baze podataka ili projektantu baze podataka da promijeni strukturu tabele nakon što je kreirana. Sintaksa naredbe je: ALTER TABLE imeTablice
ALTER TABLE naredba nam omogućava: •
Da dodajemo kolone postojećoj tabeli
•
Promijenimo kolonu koja već postoji
Recimo da na našoj tablici Odjeli moramo promijeniti duljinu podataka sa 30 znakova na 50 znakova u koloni Naziv, upotrijebili bismo sljedeću naredbu: ALTER TABLE Odjeli MODIFY Naziv NVARCHAR(50); TakoĊer ako bismo morali u tablici Odjeli dodati novu kolonu Br_racunala jer do sada nismo u bazi imali podatak koliko koji odjel ima raĉunala, koristili bismo sljedeću naredbu: ALTER TABLE Odjeli ADD Br_racunala NUMBER (4);
UNOS PODATAKA U TABLICE Nakon što smo napravili tablicu u bazi podataka moramo tu tablicu napuniti pripadajućim podacima, tako u našoj tablici Odjeli moramo pridodati odgovarajuće vrijednosti za kolone ID, Naziv, Br_zaposlenih i Napomena kao što je to prikazano u tablici. INSERT naredba nam omogućava da unosimo podatke u tablice u bazi podataka. Može se podijeliti u dvije naredbe: INSERT.....VALUES
i
INSERT.....SELECT
INSERT.....VALUES naredba unosi podatke u tabelu red po red. Korisna je za male operacije koje imaju samo nekoliko redova. Sintaksa naredbe je sljedeća: INSERT INTO imeTablice (imeKolone1, imeKolone2, ....) VALUES (vrijednost1, vrijednost2,....)
Moramo poštovati tri pravila kada unosimo podatke upotrebom ove naredba: •
Vrijednosti koje unosimo moraju istog tipa podataka kao polja u koja unosimo podatke
•
Veliĉina podataka mora odgovarati veliĉini kolone. Na primjer, ne možemo unijeti 80 karaktera u kolonu ĉija je širina 40 karaktera
•
Podaci u listi VALUES moraju odgovarati kolonama u listi kolona, odnosno prva vrijednost se mora unijeti u prvu kolonu, druga u drugu i tako dalje.
Dakle, prilikom unošenja podataka u tablicu Odjeli koristimo sljedeće naredbe: INSERT INTO Odjeli (ID, Naziv, Br_zaposlenih, Napomena) VALUES ( 1, 'Odjel_prodaje', 5);
INSERT INTO Odjeli (ID, Naziv, Br_zaposlenih, Napomena) VALUES (2, 'Odjel_nabave', 3);
INSERT INTO Odjeli (ID, Naziv, Br_zaposlenih, Napomena) VALUES (3, 'Financijski_odjel', 4);
INSERT INTO Odjeli (ID, Naziv, Br_zaposlenih, Napomena) VALUES (4, 'Odjel_recepcije', 6, 'Radno vrijeme 0-24h');
Ova naredba je jako korisna kada dodajemo po jedan red tablici baze podataka, ali oĉigledno ima ograniĉenja. Da li bismo željeli da je koristimo da dodamo 25000 redova tabeli. U takvim situacijama, INSERT.....SELECT naredba je puno korisnija. On omogućava projektantu da kopira podatke iz tablice ili grupe tablica u drugu tablicu. Ovu naredbu bi mogli upotrijebit u nekoliko situacija. No mi nećemo koristiti ovu naredbu jer nemamo potrebu kopiranja podataka iz baze podataka te prijenosa u druge tablice. Sintaksa ove naredbe je sljedeća:
INSERT INTO imeTablice (imeKolone1, imeKolone2,....) SELECT imeKolone1, imeKolone2,... FROM imeTablice1 WHERE uvjet Ista pravila koja važe za INSERT....VALUES naredbu važe i za INSERT....SELECT naredbu.
PROMJENA VRIJEDNOSTI U VEĆ POSTOJEĆIM RETCIMA Ĉesto prilikom projektiranja, ali i prilikom ažuriranja baze podataka moramo promijeniti neki zapis u tablici, u tu svrhu koristimo UPDATE naredbu. Sintaksa naredbe je: UPDATE imeTablice SET imeKolone1=vrijednost1 [,imeKolone2=vrijednost2] WHERE uvjet Ova naredba prvo provjerava WHERE klauzulu. Za sve redove iz izabrane tablice, za koje se WHERE klauzula izraĉunava sa vrijednošću TRUE, odgovarajuće vrijednosti se ažuriraju. Recimo, kada bi se u odjelu prodaje u turistiĉkoj tvtrki povećao broj zaposlenih sa 5 na 7, sukladno navedeno u prijašnjim poglavljima da bismo zadovoljili poslovna pravila i procese moramo taj podatak promijeniti i u bazi podataka u tablici Odjeli, i to na sljedeći naĉin: UPDATE Odjeli SET Br_zaposlenih=7 WHERE Naziv=Odjel_prodaje;
Nakon izvršenja ove naredbe i fiziĉki se u bazi podataka broj zaposlenika u odjelu prodaje poveća sa 5 na 7.
BRISANJE PODTAKA Kao što nam je ponekad prilikom izrade ili ažuriranje baze podataka potrebno dodati neki podatak u tablice u bazi, tako je potrebno ponekad i pobrisati neki podatak iz baze podataka. Za tu operaciju se koristi naredba DELETE. Sintaksa naredbe je: DELETE FROM imeTabele WHERE uvjet Važno je napomenuti da kada u SQL-u izvedete sljedeću naredbu neće vas alat pitati potvrdu da želite brisati redak ili više redaka u bazi podataka u nekoj tablici, već će to uĉiniti bez potvrde, za razliku od većine današnjih korisniĉkih programa koje u svakodnevnoj upotrebi koristimo. Primjer brisanja odjela prodaje: DELETE FROM Odjeli WHERE Naziv=Odjel_nabave; U zavisnosti od upotrebe WHERE klauzule u DELETE iskazu, SQL može da: obriše jedan red, obriše više redova, obriše sve redove ili ne obriše ni jedan red. Ova naredba ne može obrisati vrijednost iz jednog polja, uklanja cijeli redak iz tabele, može se pojaviti problem relacije sa drugim tablicama, i upotrebom ove naredbe uklanjamo jedan redak iz tabele ne i cijelu tabelu.
RELACIJSKO SPAJANJE TABLICA Postavlja se pitanje kako pravilno spojiti tablice u bazi podataka, a odgovor opet leži u poslovnim pravilima, poslovnim procesima i organizacijskoj strukturi tvrtke, u našem sluĉaju male turistiĉke tvrtke. Da bismo uopće mogli govoriti o relacijskom spajanju tablica u bazi podataka, moramo objasniti pojam relacije. RELACIJA je zapravo naĉin vezivanja tablica u bazi podataka, i postoji više vrsta relacija: •
Varnina noga
•
Jedana-na-jedan relacija
•
Jedan-na-više relacija
•
Više-na-više relacija.
S obzirom da se većinom bazama podataka koristi samo jedan-na-više relacijei i ona je najĉešća relacija prilikom projektiranja baze podataka, te smo ostale relacije implementirali pomoću jedan-na-više relacije nju ćemo i objasniti.
JEDAN – NA – VIŠE RELACIJA ODJELI
ZAPOSLENICI
# ID * Naziv * Br_zaposlenih o Napomena
# ID * Ime * Prezime o Spol * Datum_rodjenja * Ulica_br * Mjesto * Drzava * Post_br o Tel_kucni o Tel_posao o Tel_mob o E-mail * ID_ODJ * ID_OBJ
Ovaj primjer pokazuje kako su spojene dvije tablice jedan-na-više relacijom. U maloj nekoj tvrtki postoje odjeli sa svojim pripadajućim zaposlenicima, pa su stoga napravljene tablice Odjeli i Zaposlenici. Iz poslovnih pravila znamo da jedan zaposlenik može raditi samo u jednom odjelu, te da jedan odjel može imati više od jednog zaposlenika. Odatle dolazi i relacija jedan-na-više.
Da bismo mogli fiziĉki povezati te dvije tablice moramo napraviti kljuĉeve koji će biti poveznica izmeĊu ova dvije tablice. KLJUĈ je termin korišten za opis polja u tablicama spojenima zajedno formirajući relaciju. Kljuĉ kopira polja iz tablice u efikasniju strukturu koju je puno jednostavnije pretraživati. Kljuĉ kreira specijalnu oznaku za polje, što omogućuje tom polju da se povezuje sa drugom tablicom stvarajući relacije izmeĊu njih. Sljedeće vrste kljuĉeva se mogu definirati u tablicama baze podataka: PRIMARNI KLJUĈ se koristi za jedinstvenu identifikaciju u tablici. Jedinstvena identifikacija je potrebna za svaki unos jer ne postoji drugaĉiji naĉin na koji bismo mogli identificirati jedan jedinstveni unos. U našoj bazi podataka smo definirali sve kolone koje se nazivaju ID kao primarne kljuĉeve, radi jednostavnosti i bolje mogućnosti manipuliranja podacima nad bazom podataka. Primarni kljuĉ se definira prilikom izrade tablice i to
navoĊenje kljuĉne rijeĉi PRIMARY KEY uz kolonu tablice za koju želimo da bude primarni kljuĉ, kao što je prikazano na u naredbi za kreiranje tablice Odjeli.
Definiranje primarnog kljuĉa u tablici Odjeli: CREATE TABLE Odjeli ( ID NUMBER(10) PRIMARY KEY, Naziv NVARCHAR(30) NOT NULL, Br_zaposlenih NUMBER(5) NOT NULL, Napomena TEXT(1000), UNIQUE (Naziv) )
Možemo vidjeti da obje tablice imaju jedinstvenu identifikaciju implementiranu u koloni ID.
JEDINSTVENI KLJUĈ u bazi podataka je kljuĉ kojim osiguravamo jedinstvenost podataka u tablici. On je po svojoj funkciji sliĉan primarnom kljuĉu, ali je glavna razlika to što pomoću njega ne definiramo relacije izmeĊu tablica. Jedinstveni kljuĉ se definira prilikom izrade tablice i to navoĊenjem kljuĉne rijeĉi UNIQUE uz kolonu tablice za koju želimo da bude jedinstveni kljuĉ, kao što je prikazano na primjeru. STRANI KLJUĈ je kopija primarnog kljuĉa stvorena u tablici koja je podreĊena nekoj drugoj tablici na drugoj strani relacije, odnosno na našem primjeru u tablici Zaposlenici, koja je podreĊena tablici Odjeli. Konkretno svi zaposlenici koji rade u financijskom odjelu će u koloni ID_ODJ (strani kljuĉ u tablici Zaposlenici od tablice Odjeli) imate vrijednost 3, jer je kolona ID primarni kljuĉ u tablici Odjeli, a financijski odjel ima jedinstvenu ID vrijednost 3. Strani kljuĉ se stvara prilikom kreiranja tablice i to navoĊenjem kljuĉne rijeĉi FOREIGN KEY uz kolonu za koju želimo da nam bude strani kljuĉ.
Kreiranje tablice Zaposlenici: CREATE TABLE Zaposlenici ( ID NUMBER (10) PRIMARY KEY, Ime NVARCHAR(50) NOT NULL, Prezime NVARCHAR(50) NOT NULL, Spol BINARY, Datum_rodjenja DATE NOT NULL, Ulica_br NVARCHAR(50) NOT NULL, Mjesto NVARCHAR(50) NOT NULL, Drzava NVARCHAR(50) NOT NULL, Post_br NUMBER(10) NOT NULL, Tel_kucni NVARCHAR(20), Tel_posao NVARCHAR(20), Tel_mob NVARCHAR(20), E-mail NVARCHAR(50), ID_Odj NUMBER(10), ID_Odj NUMBER(10) ); Ako malo bolje analiziramo kreirane kolone u tablici Zaposlenici vidimo da nismo još uvijek definirali strane kljuĉeve iako smo stvorili kolonu koja nam je strani kljuĉ na tablicu Odjeli. Strane kljuĉeve možemo kao takve definirati prilikom izrade tablice kao što je već navedeno, ali postoji bolji naĉin kojim definiramo strane kljuĉeve, a ujedno i stvaramo konaĉno i fiziĉku vezu izmeĊu ove dvije tablice. Dakle sintaksa naredbe ALTER TABLE za stvaranje stranog kljuĉa, a ujedno i stvaranje fiziĉke relacijske veze izmeĊu tablica je: ALTER TABLE imeTablice ADD ( CONSTRAINT imeKonstrainta FOREIGN KEY (imeStranogKljuča) REFERENCES imeTablice(imePrimarnogKljuča));
U ovoj naredbi do sada se nismo upoznali sa izrazima CONSTRAINT i REFERENCES. Dakle kljuĉna rijeĉ CONSTRAINT zapravo oznaĉuje naziv relacijske veze izmeĊu dvije tablice. Kljuĉnom rijeĉi FOREIGN KEY defniramo koja će kolona biti strani kljuĉ u tablici, i na poslijetku kljuĉnom rijeĉi REFERENCES definiramo na koju tablicu i na koju kolonu će se spajati tablica nad kojom radimo naredbu ALTER TABLE. Tako za naše relacijsko spajanje tablica Odjeli i Zaposlenici bismo morali nakon što smo kreirali ove tablice (a jesmo), napraviti sljedeću naredbu: ALTER TABLE Zaposlenici ADD ( CONSTRAINT Odj_zap FOREIGN KEY (ID_ODJ) REFERENCES Odjeli(ID)); I konaĉno nakon izvršenja ove naredbe smo i fiziĉki spojili ove dvije tablice.
MANIPULIRANJE PODACIMA IZ BAZE PODATAKA
Da bismo u potpunosti iskoristili mogućnosti relacijske baze podataka koju smo objasnili i napravili u prethodnim poglavljima potrebno je da komuniciramo sa bazom podataka. Recimo da direktor neke tvrtke da zahtjev zaposleniku u odjelu prodaje da mu prikaže prodajne rezultate nekog artikla u prvih pet mjeseci prošle godine, ili da želi od financijskog odjela da mu prikaže odreĊene financijske rezultate koji su ostvareni u prošloj godini, u takvim sluĉajevima je potrebno komunicirati sa bazom podataka, odnosno informacijki sustav kojeg projektiramo mora moći dati takve i sliĉne informacije. Takve mogućnosti nam daje SQL sa svojim naredbama koje nisu objašnjene u prethodnim poglavljima, a najpoznatija naredba je SELECT, pa ćemo stoga i krenuti u ovo poglavlje od navedene naredbe.
NAREDBA SELECT Osnovna naredba SQL-a, služi nam za manupiliranje podacima, bilo da je rijeĉ o cijelim tablicama, dijelovima tablica ili samo ponekom podatku u tablici. TakoĊer ova naredba može prikazivati i podatke iz više tablica odjednom, što joj daje najvažniju i najbolju funkcionalnost.
Sintaksa SELECT naredbe: SELECT imeKolone1, imeKolone2,.... FROM imeTablice WHERE uvjet; Primjer: Recimo da u tablici zaposlenici imamo izmeĊu ostalog zaposlenike ĉije je ime Ivana Gavran, Ivana Luter i Ivana Pregiban. Ako bismo željelji prikazati ime i prezime svih zaposlenika ĉije je ime Ivana, morali bismo izvesti sljedeću naredbu: SELECT Ime, Prezime FROM Zaposlenici WHERE Ime='Ivana'; Kao rezultat ove naredbe dobivamo sljedeće podatke: Ime
Prezime
Ivana
Gavran
Ivana
Luter
Ivana
Pregiban
ELIMINIRANJE DUPLIKATA IZ TABLICE Suprotno prethodnom primjeru mogli bismo imati potrebu da prikažemo sve brojeve telefona od zaposlenika koji se zovu Ivana, ali tako da ne prikazujemo duplicirane podatke, već samo jednom da se prikaže pojedini broj telefona. Za takve potrebe, odnosno za elimiranje duplikata koristimo kljuĉnu rijeĉ DISTINCT, i to na sljedeći naĉin: SELECT DISTINCT Tel_posao, Ime, Prezime FROM Zaposlenici WHERE Ime='Ivana' Kao rezultat ove operacije dobili bismo jednaku tablicu kao i u primjeru 5.1. sa dodatnom kolonom Tel_posao, ali ako bi se sluĉajno jedan broj telefona ponovio, kljuĉna rijeĉ DISTINCT bi eliminirala jedan rezultat.
IZRAZI UVJETI I OPERATORI Prilikom kreiranja SELECT naredbe ĉesto nam je potreban viši nivo pretraživanja, mogli bismo reći „finije“ sortiranje podataka, i u tom sluĉaju su na izrazi, uvjeti i operatori od velike korisiti, jer nam omogućavaju da to i uĉinimo. IZRAZ kao rezultat daje vrijednost. Izrazi su veoma rašireni i pokrivaju razliĉite tipove podataka, kao što su stringovi, numeriĉki i logiĉki podaci. U sljedećem primjeru Ime i Prezime su izrazi koji za razultat daju vrijednost iz kolone Ime i Prezime, iz tablice Zaposlenici. SELECT Ime, Prezime FROM Zaposlenici WHERE Ime='Ivana'
UVJETI – kada god naiĊemo na odreĊeni objekt ili grupu objekata u našoj bazi podataka, bit će nam potreban jedan ili više uvjeta. Uvjeti se navode u WHERE kljuĉnoj rijeĉi. U prethodnom primjeru uvjet je: WHERE Ime='Ivana' Uvjeti nam omogućuju da formiramo upite selekcije. U svojem najopćenitijem obliku, uvjeti obuhvaćaju kolonu, konstantu i operator usporedbe. Dakle u ovom primjeru kolona je Ime, konstanta je Ivana, a operator je =.
WHERE kljuĉna rijeĉ – SELECT, FROM i WHERE su tri najĉešće korištene kljuĉne rijeĉi u SQL-u. WHERE ĉini da naši upiti budu selektivniji. Ukoliko ne upotrijebimo WHERE kljuĉnu rijeĉ, jedino što možemo raditi sa našim upitom je da prikažemo sve podatke iz odreĊene tablice. Potrebno je naglasiti da unutar ove kljuĉne rijeĉi možemo dodatni nivo grupiranja uvesti navoĊenjem sljedećih kljuĉnih rijeĉi: STARTING WITH, ORDER BY, GROUP BY, HAVING. Ove kljuĉne rijeĉi se koriste za grupiranje podataka iz tablica i naćemo ih dodatno objašnjavati.
OPERATORI su elementi koje koristimo u izrazima za izražavanje uvjeta pod kojima se pretražuju podaci. Vrste operatora: ARITMETIĈKI:
+, -, /, *, %
USPOREDBNI:
=,
> ili >=,
ZA KARAKTERE (STRINGOVE):
LIKE,
< ili <=, _,
||
<> ili !=
LOGIĈKI OPERATORI:
AND, NOT, OR
SKUPOVNI OPERATORI:
UNION i UNION ALL, MINUS, IN i BETWEEN
INTERSECT,
Na kraju ovog poglavlja možemo zakljuĉiti da nam izrazi, uvjeti i operatori služe za ĉim selektivnije pretraživanje podataka. No, sada želimo prijeći na viši nivo manipuliranja podacima, odnosno želimo na neki naĉin oblikavati dobivene podatke. Za to nam služe razne ugradbene funkcije u SQL-u koje ćemo onda obraditi u sljedećem poglavlju.
FUNKCIJE: OBLIKOVANJE DOBIVENIH PODATAKA Funkcije nam u SQL-u omogućavaju „majstorije“, kao što su odreĊivanje sume vrijednosti kolone ili konvertiranje svih karaktera nekog stringa u velika slova. Pa tako postoje sljedeće funkcije: •
Agregatne funkcije
•
Funkcije za vrijeme i datum
•
Aritmetiĉke funkcije
•
Funkcije za rad sa karakterima
•
Funkcije za konverziju
•
Miješovite funkciije
AGREGATNE FUNKCIJE – ove funkcije još se nazivaju i funkcijama grupe. Kao rezultat daju vrijednost baziranu na vrijednostima iz kolone. U tu grupu funkcija spadaju: COUNT, SUM, AVG, MAX, MIN, VARIANCE, STDDEV. Na sljedećem primjeru ćemo pokazati kako zbrojiti sve zaposlenike u tablici Odjeli u našoj bazi podataka: SELECT SUM(Br_zaposlenih) Ukupno_zaposlenih FROM Odjeli
FUNKCIJE ZA VRIJEME I DATUM – s obzirom da živimo u vremenu kojim upravljaju vrijeme i datum sve više implementacija SQL-a ukljuĉuje funkcije za vrijeme i datum. Pa stoga u SQL-u postoje sljedeće funkcije: ADD_MONTHS, LAST_DAY, MONTHS_BETWWEN, NEW_TIME, NEST_DATE, SYS_DATE.
ARITMETIĈKE FUNKCIJE – današnja upotreba podataka koje dobijemo iz baze podataka ukljuĉuje i upotrebu matematike, pa stoga mnoge implementacije SQL-a ukljuĉuju aritmetiĉke funkcije, a to su: ABS, CEIL i FLOOR,COS, COSH, SIN, SINH, TAN i TANH, EXP, LN i LOG, MOD, POWER,SIGN, SQRT. Ove funkcije rade matematiĉke operacije nad brojĉanim podacima u bazi podataka. FUNKCIJE ZA RAD SA KARAKTERIMA – ove funkcije rade razne operacije nad karakterima, a to su: CHR, CONCAT, INITCAP, LOWER i UPPER, LPAD i RPAD, LTRIM i RTRIM, REPLACE, SUBSTR, TRANSLATE, INSTR, LENGTH. FUNKCIJE ZA KONVERZIJU – ove nam funkcije omogućavaju da na jednostavan naĉin konvertiramo jedan tip podataka u drugi, a to su: TO_CHAR, TO_NUMBER.
Sada kada smo odradili jedan viši nivo manipuliranja podacima u tablicama, javlja se potreba da spajamo tablice i prikazujemo rezultate spajanja. Na primjer, kada bi direktor tvrtke tražio da se izlistaju svi zaposlenici u odjelu prodaje, informacijski sustav mora biti u mogućnosti dati odgovor na to pitanje. To bismo mogli izvesti samo na jedan naĉin da spojimo tablice Odjeli i Zaposlenici, te prikažemo odjel prodaje i zaposlenike koji u njemu rade.
SPAJANJE TABLICA Jedna od najvećih mogućnosti SQL-a je mogućnost prikupljanja i manipuliranja podacima iz više tablica istovremeno. Bez ove mogućnosti bili bismo primorani sve podatke za jednu aplikaciju saĉuvati u jednoj tablici. Bez korisniĉkih tablica morali bismo saĉuvati iste podatke u više tablica. JOIN iskaz SQL-a nam omogućava da kreiramo manje, specifiĉne tablice, koje je lakše održavati nego velike tablice. Jedini problem se nameće kako spojiti dvije tablice i prikazati odreĊene podatke iz svake tablice. Taj problem riješava upotreba ALIASA, koji su zapravo pokazivaĉi na tablice, odnosno pokazuju koje kolone iz kojih tablica treba prikazati. Konkretno na našem primjeru kada bismo morali izlistati imena i prezimena svih zaposlenika iz odjela prodaje koji se zovu Marko napravili bismo sljedeće: SELECT
z.Ime, z.Prezime, o.Naziv
FROM
Zaposlenici z, Odjeli o
WHERE
(z.Ime='Marko' AND o.Naziv='Odjel_prodaje')
AND
o.ID=z.ID_ODJ;
Ako pomno promotrimo ovu SELECT naredbu vidimo da se razlikuje od do sada navedenih; jer pretražujemo dvije tablice te definiramo alias za obje tablice. Tako možemo selektirati velik broj tablica i vršiti sjajna pretraživanja unutra baze podataka, što nam je i bio cilj, jer iz dobro definiranog ER-dijagrama i velike mogućnosti pretraživanja nad stvorenom bazom podataka, imamo sve preduvjete da stvorimo dobar informacijski sustav. SQL nudi i mogućnost još jednog višeg nivoa pretraživanja podataka, a to je uglježĊeni upit, gdje se onda kao dio uvjeta pojavljuje SELECT naredba umjesto nekog objekta, zapravo mogli bismo govoriti da u uvjetu imamo novu grupu objekata. TakoĊer možemo imati više uvjeta i u svakom od njih drugu SELECT naredbu. Do sada smo prikazali kako manipulirati podacima, te se onda te manipulacije spremaju ili ne spremaju na disku. No, što ukoliko imamo potrebu ĉestog manipuliranja istih podataka, a istovremeno ne želimo da se to oĉituje na našem disku. SQL i za to ima rješenje, a to je upotreba pogleda (VIEW) i indeksa (INDEX).
KREIRANJE POGLEDA I INDEKSA Pogled (VIEW) se ĉesto odnosi na virtualnu tabelu, koji je zapravo virtualni SELECT, odnosno ako vršimo neki veliki upit nad bazom podataka, i koristit ćemo ga ponovno u radu, preporuĉljivo je kreiranje pogleda, jer je to onda virtualna tabela koja sadrži rezultat pretraživanja upita, i nije potrebno isti upit raditi ponovno, a time skraćujemo složene upite. Sintaksa za izradu upita je: CREATE OR REPLACE VIEW imePogleda [(imeKol1, imeKol2,...)] AS SELECT imeTablice imeKolone FROM imeKolone Pogledi ne zauzimaju fiziĉki prostor u bazi podataka kao što to ĉine tabele. Isto tako, nakon kreiranog pogleda, ako se promijene podaci u tabeli, ono što ćemo dobiti kada ponovno izvršimo upit nad pogledom se takoĊer mijenja. Na sljedećem primjeru možemo vidjeti upotrebu pogleda: CREATE OR REPLACE VIEW Marko_u_odjelu_prodaje AS SELECT
z.Ime, z.Prezime, o.Naziv
FROM
Zaposlenici z, Odjeli o
WHERE
(z.Ime='Marko' AND o.Naziv='Odjel_prodaje')
AND
o.ID=z.ID_ODJ
Indeks (INDEX) je drugi naĉin kojim se podaci predstavljaju drugaĉije, nego što su zapisani na disku. Dodavanjem indeksa bazi podataka omoćujemo SQL-u da koristi direktnu metodu pristupa (Direct Access Method). Sintaksa za kreiranje indeksa je: CREATE INDEX imeIndeksa ON imeTablice (imeKolone1, imeKolone1,....);
