BAZE PODATAKA - uni-mo.sve-mo.bauni-mo.sve-mo.ba/~goran/nastava/BAZE_Nastava.pdf · ak.god....
Transcript of BAZE PODATAKA - uni-mo.sve-mo.bauni-mo.sve-mo.ba/~goran/nastava/BAZE_Nastava.pdf · ak.god....
Ak.god. 2017/2018. BAZE PODATAKA 1
BAZE PODATAKA
FAKULTET STROJARSTVA I RAČUNARSTVA
SVEUČILIŠTE U MOSTARU
Doc.dr.sc. GORAN KRALJEVIĆ
Ak.god. 2017/2018. BAZE PODATAKA 2
Web:
http://www.uni-mo.ba/~goran
Pitanja, primjedbe, dogovor za konzultacije ...
o E-mail: [email protected]
Baze podataka
Ak.god. 2017/2018. BAZE PODATAKA 3
Polaganje ispita (kolokviji ...)
Prolazna ocjena putem kolokvija: min. 50 bodova
50,0 bod. ............ dovoljan (2)
62,5 bod. ............ dobar (3)
75,0 bod. ............ vrlodobar (4)
87,5 bod. ............ izvrstan (5)
1. kolokvij (Model+SQL DDL+Teorija) 25 bodova
2. kolokvij (SQL DML+Teorija) 40 bodova
Vježbe (SQL) 20 bodova
Domaće zadaće 15 bodova
Ukupno : 100 bodova
Ak.god. 2017/2018. BAZE PODATAKA 4
Polaganje ispita (ispitni rokovi ...)
Pismeni + Usmeni
Prolazna ocjena na ispitu: min. 60 bodova
60-69 bod. .......... dovoljan (2)
70-79 bod. .......... dobar (3)
80-89 bod. .......... vrlodobar (4)
90-100 bod. ........ izvrstan (5)
Napomena:
Studenti koji tijekom semestra (kolokviji ...) osvoje 40-49 bodova dobit će 10 bodova koji će im se pribrojiti na svim pismenim ispitnim rokovima do kraja akademske godine.
Studenti koji tijekom semestra (kolokviji ...) osvoje 30-39 bodova dobit će 5 bodova koji će im se pribrojiti na svim pismenim ispitnim rokovima do kraja akademske godine.
Ak.god. 2017/2018. BAZE PODATAKA 6
Baza podataka
• Baza podataka je skup međusobno povezanih podataka,
pohranjenih zajedno bez štetne ili nepotrebne (nekontrolirane)
zalihosti (redundancije), s ciljem da ih koriste različite aplikacije.
Podaci su pohranjeni u obliku neovisnom od programa koji ih
koriste. Unos, izmjena i dohvat podataka obavlja se ISKLJUČIVO
kroz zajedničko i kontrolirano sučelje.
Ak.god. 2017/2018. BAZE PODATAKA 7
Zajedničke osobine za sve sustave baza podataka (Ullman)
• Apstraktni model podataka
• Visoka razina pristupa ili upitnih jezika
• Upravljanje transakcijama u višekorisničkom okruženju
• Kontrola pristupa i vlasništvo nad podacima
• Validacija podataka i provjera konzistentnosti
• Konzistentni oporavak podataka nakon ispada sustava i/ili strojne opreme
Ak.god. 2017/2018. BAZE PODATAKA 8
Ciljevi razvoja baza podataka
• Razdvajanje podataka od aplikacija koje ih koriste
• Prezentiranje logičkog pogleda na podatke neovisno od fizičkih detalja njihove pohrane u bazu podataka
• Omogućavanje različitih pogleda na istu bazu podataka, ovisno o korisničkim i aplikativnim potrebama
Ak.god. 2017/2018. BAZE PODATAKA 9
Faze razvoja baza podataka
FAZE razvoja Baza podataka Aplikacija
Zahtjevi
(analiza)
Izrada modela podataka
Specificiranje podataka
Definiranje ograničenja i poslovnih pravila
Utvrđivanje zahtjeva za aplikaciju
Dizajn
Tablice
Relacije
Indeksi
Ograničenja
Pohranjene procedure i okidači
Forme
Izvješća (Reports)
Upiti (Queries)
Kod aplikacije
Implementiranje
Kreiranje tablica
Kreiranje relacija
Kreiranje ograničenja
Pisanje procedura i okidača
Punjenje baze podataka
Testiranje
Kreiranje formi
Kreiranje izvješća
Kreiranje Upita
Pisanje koda aplikacije
Testiranje
Ak.god. 2017/2018. BAZE PODATAKA 10
Povijesni razvoj baza podataka
Sustavi bazirani na datotečnim sustavima (filesystem)
• podaci su spremljeni u datotekama (files)
• svaka datoteka ima svoj format
programi koji koriste bazu moraju poznavati taj format
• Problemi:
nema standarda
višestruko ponavljanje podataka
međuovisnost podataka
teško je vršiti neuobičajena pretraživanja
integritet podataka
sigurnost
istovremeni pristup, ...
Ak.god. 2017/2018. BAZE PODATAKA 11
Modeli podataka
• Model podataka je skup pravila koji određuju kako može izgledati logička struktura baze
• U 60-tim i 70-tim godinama su bili u upotrebi hijerarhijski i mrežni model
• Hijerarhijski model baza je predočena stablom ili skupom stabala jedan član može imati samo jednog vlasnika putovi pretraživanja su fiksni
• Mrežni model opći slučaj hijerarhijskog modela odnosi definirani eksplicitno aplikacija mora poznavati interni model baze podataka
• Implementacije u računalu koristile su pokazivače koji izravno adresiraju mjesto zapisa na disku
Ak.god. 2017/2018. BAZE PODATAKA 12
Hijerarhijski i mrežni model
Hijerarhijski model podataka Mrežni model podataka
Ograničenja hijerarhijskih i mrežnih baza:
- Nemaju pokriće u formalnoj teoriji- Sva pretraživanja se izvode po unaprijed definiranim i točno navedenim putovima- Svi odnosi između objekata se moraju unaprijed i točno definirati- Optimizacija se provodi ručno - programer sam optimizira kod i određuje metodu
koja će biti korištena pri komunikaciji između aplikacije i baze podataka
Ak.god. 2017/2018. BAZE PODATAKA 13
Modeli podataka
• Relacijski model
1970. događa se relacijska "revolucija“
E.F.Codd objavljuje članak "A Relational Model of Data for Large Shared Databanks"
koji postavlja osnove skoro svim današnjim sustavima baza podataka
• Objektni model
inspiriran objektno orijentiranim programskim jezicima
baza je skup objekata koji se sastoje od podataka i metoda koje vrše operacija nad njima
Ak.god. 2017/2018. BAZE PODATAKA 14
Sustav za upravljanje bazama podataka
• SUBP (DBMS - Database Management System)
Programski sustav koji omogućava upravljanje bazom podataka je sustav za upravljanje bazama podataka.
• Korisnički programi ne pristupaju podacima direktno već preko DBMS-a
• Korisnik ili korisnički program postavlja zahtjev za obavljanjem neke operacije s podacima, a SUBP ga analizira, provjerava, optimizira, transformira u niz operacija koje je potrebno obaviti na fizičkoj razini, obavlja operacije i vraća rezultat.
Ak.god. 2017/2018. BAZE PODATAKA 15
Fizička i logička organizacija podataka
• Važna posljedica primjene SUBP jest razdvajanje fizičke i logičke organizacije podataka. Dok logička organizacija podataka predstavlja organizaciju sa stanovišta korisnika baze podataka ili programera te je koncentrirana na vrste podataka i njihove međusobne logičke veze, fizička organizacija predstavlja organizaciju fizičke pohrane podataka unutar računala. Oblik i organizacija pohranjenih podataka tu su često potpuno različiti od njihovog logičkog oblika i organizacije.
• U okviru toga, zadaća je SUBP-a omogućiti korisniku (programeru) manipuliranje podacima uz poznavanje samo logičkog opisa baze podataka, a ne nužno i poznavanja načina fizičke pohrane podataka.
Ak.god. 2017/2018. BAZE PODATAKA 16
Sustav za upravljanje bazama podataka
Funkcije SUBP-a:
• Definiranje baze podataka (DDL–Data Definition Language)
• Manipuliranje podacima u bazi (DML–Data Manipulation Language)
• Upravljačke funkcije:
Sigurnost i zaštita od neovlaštenog pristupa
Očuvanje integriteta (backup i recovery)
Statističko praćenje rada baze podataka
Optimizacija rada
Ak.god. 2017/2018. BAZE PODATAKA 17
Sustav za upravljanje bazama podataka
Glavni proizvođači SUBP (DBMS):
Ak.god. 2017/2018. BAZE PODATAKA 19
Relacijski model podataka
• Relacijski model je osnovne koncepte preuzeo iz matematičke teorije skupova, a to su:
Relacija
Atribut
Domena
Ak.god. 2017/2018. BAZE PODATAKA 20
Relacijski model podataka
• Većina suvremenih DBMS je bazirana na relacijskom modelu
• Informacije su pohranjene kao zapisi ili slogovi (records) u relacijama (tablicama)
• Baza podataka je skup relacija (tablica)
Ak.god. 2017/2018. BAZE PODATAKA 21
Ključevi u relacijskoj bazi podataka
• Super ključ (superkey) – atribut ili skup atributa koji
jedinstveno određuje n-torku unutar relacije.
• Kandidat ključ (candidate key) – super ključ takav da nema
nijedan odgovarajući podskup koji bi bio super ključ unutar relacije.
• Primarni ključ (primary key) – kandidat ključ koji je odabran
da jedinstveno odredi n-torku unutar relacije.
• Jedinstveni ključ (unique key) – kandidat ključ koji nije
odabran da bude primarni ključ.
Ak.god. 2017/2018. BAZE PODATAKA 22
Ključevi u relacijskoj bazi podataka
Primjer:
• Super ključ: npr. {Matbr, Prezime}
• Kandidat ključ: {Matbr}, {JMBG}
• Primarni ključ: {Matbr}
• Jedinstveni ključ: {JMBG}
Ak.god. 2017/2018. BAZE PODATAKA 23
Ključevi u relacijskoj bazi podataka
Kandidat (za PRIMARNI) ključ mora zadovoljiti 2 uvjeta:
• Jedinstvenost … na relacijskoj shemi niti u jednom trenutku
ne mogu postojati dvije n-torke s jednakim vrijednostima skupa atributa K.
• Minimalnost … niti jedan pravi podskup od skupa atributa K
nema svojstvo jednoznačnosti.
o I primarni i jedinstveni ključ moraju ispuniti uvjete
jedinstvenosti i minimalnosti, ali relacija može imati
samo jedan primarni ključ, dok jedinstvenih ključeva
može imati više.
Ak.god. 2017/2018. BAZE PODATAKA 24
Ključevi u relacijskoj bazi podataka
• Vanjski ili strani ključ (foreign key) – atribut ili skup
atributa unutar jedne relacije koji odgovara kandidat ključu neke (moguće i iste) relacije.
• Vanjski ključevi omogućuju povezivanje n-torki iz različitih tablica.
Primjer:
Ak.god. 2017/2018. BAZE PODATAKA 25
Ograničenja u relacijskom modelu podataka
Dva opća ograničenja:
• Entitetski integritet
- povezan sa primarnim ključem
• Referencijalni integritet
- povezan sa stranim ključem
Ak.god. 2017/2018. BAZE PODATAKA 26
Entitetski integritet
• Vrijednost primarnog ključa kao cjeline, ne smije biti jednaka NULL vrijednosti.
Ako je primarni ključ relacije složen, niti jedna njegova komponenta ne smije poprimiti NULL vrijednost.
Primjeri:
NASTAVNIK = {SifNas, PrezNas}PK (NASTAVNIK) = {SifNas} → SifNas ne smije biti NULL
ISPIT = {Matbr, SifPred, DatIsp}PK (ISPIT) = {Matbr, SifPred, DatIsp} → Matbr, SifPred, DatIsp ne smiju biti NULL
• Integritet ključa:
U relaciji ne smiju postojati dvije n-torke s jednakim vrijednostima ključa (vrijedi za sve moguće ključeve).
Ak.god. 2017/2018. BAZE PODATAKA 27
Referencijalni integritet
• Ako u relacijskoj shemi R postoji strani ključ (foreign key) koji odgovara primarnom ključu (primary key) relacijske sheme S, tada svaka vrijednost stranog ključa u relaciji r(R) mora biti ili jednaka vrijednosti primarnog ključa neke n-torke iz relacije s(S) ili jednaka NULL vrijednosti.
Primjer:
Relacije OSOBA i MJESTO ne zadovoljavaju pravilo referencijalnog integriteta jer u relaciji OSOBA postoji vrijednost stranog ključa (77000) za koju ne postoji odgovarajuća n-torka u relaciji MJESTO.
Ak.god. 2017/2018. BAZE PODATAKA 28
Ograničenja u relacijskom modelu podataka
Domenski integritet
• Kako svaki atribut ima pridruženu domenu, postoje ograničenja (engl. domain constraints) koja čine restrikcije nad skupom dozvoljenih vrijednosti atributa relacije.
NULL / NOT NULL
• NULL – predstavlja vrijednost atributa koja je trenutno nepoznata ili nije primjenjiva za konkretnu n-torku.
• NULL je način rada s nepotpunim podacima ili izuzetcima.
• NULL nije nula (0) za numeričke vrijednosti ili “spaces” za tekst.
• NULL znači ODSUSTVO (nepostojanje) vrijednosti.
Ak.god. 2017/2018. BAZE PODATAKA 29
Operacije u relacijskom modelu
Osnovne operacije u relacijskom modelu:
• Unija
• Razlika
• Presjek
• Kartezijev produkt
• Projekcija
• Selekcija
• Spajanje (join)
Ak.god. 2017/2018. BAZE PODATAKA 30
Operacije u relacijskom modelu
• Unija … relacija koju čine sve n-torke prve i druge relacije
Ak.god. 2017/2018. BAZE PODATAKA 31
Operacije u relacijskom modelu
• Razlika … relacija koju čine sve n-torke koje se nalaze u prvoj,
ali se ne nalaze u drugoj relaciji
Ak.god. 2017/2018. BAZE PODATAKA 32
Operacije u relacijskom modelu
• Presjek … relacija koju čine n-torke zajedničke za obje relacije
Ak.god. 2017/2018. BAZE PODATAKA 33
Operacije u relacijskom modelu
• Kartezijev produkt … relacija koju čine sve moguće kombinacije
parova n-torki s tim da je prva n-torka iz prve, a druga iz druge relacije
Ak.god. 2017/2018. BAZE PODATAKA 34
Operacije u relacijskom modelu
• Projekcija … rezultat je izbor određenih atributa polazne relacije
Ak.god. 2017/2018. BAZE PODATAKA 35
Operacije u relacijskom modelu
• Selekcija (ograničenje, restrikcija, izbor) … rezultat su samo
one n-torke koje zadovoljavaju postavljene uvjete
Ak.god. 2017/2018. BAZE PODATAKA 36
Operacije u relacijskom modelu
• Spajanje … iz dvije relacije stvara novu relaciju od svih
kombinacija parova n-torki koji zadovoljavaju postavljene uvjete
• U svakoj se tablici (relaciji) bira stupac (polje) preko čijih se vrijednosti uspostavlja veza
Ak.god. 2017/2018. BAZE PODATAKA 37
Operacije u relacijskom modelu
• Opisane operacije relacijske algebre primjenjuju se uvijek u kombinaciji:
iz više tablica selektiramo samo one zapise koji udovoljavaju uvjetima
napravimo spajanje (join) projekcijom odaberemo željena polja
• Na taj se način iz vrlo malog broja osnovnih operacija relacijske algebre može izvesti veliki broj kombinacija za obradu i analizu podataka.
• Prijevod operacija relacijske algebre u jezik za definiciju i manipulaciju podacima koji danas predstavlja osnovni standard
za relacijske baze podataka: SQL.
Ak.god. 2017/2018. BAZE PODATAKA 40
ER modeliranje
Model entiteti - veze (entity-relationship model)
• ili bolje: model entiteti - veze - atributi
ER modeliranje:
• je sastavljeno iz entiteta, veza i atributa
• je slikovni prikaz sustava baze podataka
• je neovisno o DBMS i hardveru
• predstavlja konceptualni model visokog nivoa
• podržava korisnikovu percepciju podataka
• je alat za projektiranje
Ak.god. 2017/2018. BAZE PODATAKA 41
ER modeliranje
• ER modeliranje je iterativan postupak – realno je očekivati više “razvojnih” verzija modela
Entiteti
Atributi
Veze
• Postoji više rješenja problema – neka su optimalnija od drugih
Ak.god. 2017/2018. BAZE PODATAKA 42
Entitet
• Entitet je bilo koji objekt u sustavu koji želimo modelirati i
o kojem želimo sačuvati informaciju
Pojedinačni objekti zovu se entiteti
Skupine objekata istog tipa zovu se tipovi entiteta ili skupovi entiteta
Moguća su dva tipa entiteta: jaki i slabi
Ak.god. 2017/2018. BAZE PODATAKA 43
Jaki i slabi tip entiteta
• JAKI entitet
Tip entiteta čija egzistencija nije vezana za postojanje nekog drugog tipa entiteta.
• SLABI entitet
Tip entiteta čija egzistencija ovisi o postojanju nekog drugog tipa entiteta, tj. onaj tip entiteta koji ne može postojati u bazi podataka ukoliko neki drugi tip entiteta također ne postoji u bazi.
Ak.god. 2017/2018. BAZE PODATAKA 44
Atribut
• Atribut je svaki detalj koji služi da pobliže odredi,
identificira, klasificira, kvantificira ili izrazi stanje entiteta.
• Predstavlja opis entiteta
Ak.god. 2017/2018. BAZE PODATAKA 45
Veze
• Entiteti se mogu povezivati jedan s drugim u veze (relacije).
• Broj entiteta u vezi predstavlja STUPANJ VEZE.
Binarna veza
veza 2 entiteta
Ternarna veza
veza 3 entiteta
Unarna veza
isti entitet više puta
egzistira u različitim ulogama
Ak.god. 2017/2018. BAZE PODATAKA 46
Kardinalnost veze
• Odnos omjera među povezanim entitetima nazivamokardinalnost veze
- Jedan na jedan (1:1)
- Jedan na više (1:m)
- Više na jedan (m:1)
- Više na više (m:n)
Ak.god. 2017/2018. BAZE PODATAKA 48
Razbijanje M:N veza
• Veza m:n u ER modelu se može razbiti uvođenjem novog posredničkog entiteta.
Primjer:
• Vezu više na više možemo razbiti uvođenjem entiteta najam, koji sadrži atribut datum_najma
Ak.god. 2017/2018. BAZE PODATAKA 49
VEZA JEDAN:VIŠE
• Primarni ključ entiteta sa strane veze JEDAN doda se kao strani ključ u entitet sa strane veze VIŠE.
VEZA VIŠE:VIŠE
• Doda se novi entitet, koji sadrži primarne ključeve obaju rubnih entiteta.
• Ti atributi zajedno tvore složeni primarni ključ novonastalog entiteta.
Preslikavanje ER modela u relacije
Ak.god. 2017/2018. BAZE PODATAKA 50
Usporedne i povratne veze
Usporedne veze – dvije usporedne veze između dva entiteta ...
Povratne veze – veza entiteta “na samog sebe” ...
Ak.god. 2017/2018. BAZE PODATAKA 51
Usporedne veze
• Svaku vezu zamijenimo s po jednim stranim ključem u relaciji na strani veze VIŠE (usporedne veze se preslikaju u jednu, ali s uvođenjem dodatnog stranog ključa).
• Da bi razlikovali veze među entitetima stranim ključevima damo različite nazive.
Povratne veze
• Doda se strani ključ jednak primarnom ključu relacije.
• Za povratne veze vrijedi da je strani ključ jednak primarnom ključu relacije, ali pod drugim imenom.
Preslikavanje ER modela u relacije
Ak.god. 2017/2018. BAZE PODATAKA 52
UML (Unified Modeling Language)
Zašto UML (odnosno korištenje jedne notacije)?
• Korištenje jednog jezika i notacije bitno olakšava komunikaciju između članova različitih timova (projektanti baze podataka, analitičari, programeri aplikacije) i time doprinosi da se svi sudionici koji participaraju u projektu osjećaju dijelom jedne cjeline.
Ak.god. 2017/2018. BAZE PODATAKA 53
Relacije u UML-u
ZAVISNOST (dependency)
ASOCIJACIJA (association)
AGREGACIJA (aggregation)
GENERALIZACIJA (generalization)
REALIZACIJA (realization)
1 1..*
Ak.god. 2017/2018. BAZE PODATAKA 56
Normalizacija podataka
• Normalizacija je proces kojime se nastoji eliminirati redundancija, ali tako da se sačuva integritet podataka u bazi
• Redundancija se izražava kroz pojam funkcijske zavisnosti
• Definiraju se normalne forme (NF)
prva NF, druga NF, treća NF, itd.
svaka normalna forma garantira da nema određenog tipa zavisnosti
svaka viša NF uključuje prethodnu NF te dodatno uređuje model, tj. eliminira dodatne redundantnosti
Ak.god. 2017/2018. BAZE PODATAKA 57
Normalne forme
• 1. NF – Prva normalna forma
• 2. NF – Druga normalna forma
• 3. NF – Treća normalna forma
• BCNF – Boyce-Coddova normalna forma
1 NF, 2 NF, 3 NF i BCNF se temelje na funkcijskim zavisnostima.
• 4. NF – Četvrta normalna forma
Temelji se na višeznačnim zavisnostima.
• PJNF – Projekcijsko-spojna normalna forma
Temelji se na spojnim zavisnostima.
Ak.god. 2017/2018. BAZE PODATAKA 58
Definicije funkcijskih ovisnosti
Funkcijska ovisnost atributa
• Ako promatramo tablicu R sa atributima X i Y koji mogu biti kompozitni tj. složeni: za atribut Y tablice R kaže se da je funkcijski ovisan o atributu X iste tablice
R.X->R.Y
ako je svaka pojedina vrijednost atributa X povezana sa samo jednom vrijednošću atributa Y.
Ak.god. 2017/2018. BAZE PODATAKA 59
Definicije funkcijskih ovisnosti
Potpuna funkcijska ovisnost atributa
• U tablici R s atributima X i Y koji mogu biti kompozitni tj. složeni, Y je potpuno funkcijski ovisan o X ako vrijedi da je Y funkcijski ovisan o X i nije funkcijski ovisan niti o jednom manjem podskupu atributa X.
Odnosno, ako vrijedi X->Y tada ne smije postojati niti jedan podskup Z koji sadrži samo dio atributa od kojih se sastoji atribut X, za koji bi vrijedilo da je Z->Y.
Ak.god. 2017/2018. BAZE PODATAKA 60
Definicije funkcijskih ovisnosti
Tranzitivna funkcijska ovisnost atributa
• Ako vrijedi X->Y i Y-/->X (Y je funkcijski ovisan o X, a X nije funkcijski ovisan o Y), i ako Y->A (A je funkcijski ovisan o Y) tada vrijedi da je A funkcijski ovisan i o X (X->A).
Ako vrijedi A-/->Y tada je A striktno tranzitivno ovisan o X.
Ak.god. 2017/2018. BAZE PODATAKA 61
1NF
Prva normalna forma (1NF)
Tablica se nalazi u prvoj normalnoj formi ako su svi neključni atributi funkcijski ovisni o ključu.
Uklanjanje ponavljajućih atributa ili grupa atributa
Ak.god. 2017/2018. BAZE PODATAKA 62
2NF
Druga normalna forma (2NF)
Tablica je u drugoj normalnoj formi ako i samo ako je u 1NF i ako su svi neključni atributi potpuno funkcijski ovisni o ključu.
Uklanjanje atributa ovisnih samo o dijelu jedinstvenog identifikatora.
Ak.god. 2017/2018. BAZE PODATAKA 63
3NF
Treća normalna forma (3NF)
Tablica je u trećoj normalnoj formi ako i samo ako je u 2NF i ako niti jedan neključni atribut nije tranzitivno ovisan o ključu.
Uklanjanje atributa ovisnih o atributima koji nisu dio jedinstvenog identifikatora.
Ak.god. 2017/2018. BAZE PODATAKA 64
Primjer normalizacije podataka
• Na slici je dana denormalizirana tablica s nazivima stupaca i vrijednostima redaka.
Napraviti normalizirani model podataka s pripadajućim entitetima, atributima i vezama između njih.
Ak.god. 2017/2018. BAZE PODATAKA 65
Normalizacija podataka (1NF)
– Postoji funkcijska zavisnost :
Matbr → Prezime, Ime
– Ne postoji funkcijska zavisnost : Matbr → SifPred Matbr → NazPredMatbr → DatIsp Matbr → Ocjena Matbr → SifNas Matbr → PrezNas
Ak.god. 2017/2018. BAZE PODATAKA 66
Normalizacija podataka (1NF)
Rješenje:
PK (STUDENT) = {Matbr}
PK (ISPIT) = {Matbr, SifPred, DatIsp}
Ak.god. 2017/2018. BAZE PODATAKA 67
Normalizacija podataka (2NF)
{Matbr, SifPred, DatIsp} → {NazPred}
{SifPred} → {NazPred} dakle, ne postoji potpuna funkcijska ovisnost
neključnih atributa o ključu
ISPIT ne zadovoljava 2 NF !
Rješenje:
Ak.god. 2017/2018. BAZE PODATAKA 68
Normalizacija podataka (3NF)
{Matbr, SifPred, DatIsp} → {SifNas}
{SifNas} → {PrezNas} dakle, postoji tranzitivna funkcijska ovisnost
(koju je potrebno eliminirati)
ISPIT ne zadovoljava 3 NF !
Rješenje:
Ak.god. 2017/2018. BAZE PODATAKA 69
Normalizacija podataka
Sve tablice zadovoljavaju 3 NF !
STUDENT (Matbr, Prezime, Ime)
PREDMET (SifPred, NazPred)
NASTAVNIK (SifNas, PrezNas)
ISPIT (Matbr, SifPred, DatIsp, Ocjena, SifNas)
Važno:
- Baza podataka treba biti barem u 3 NF !
- Osim 1NF, 2NF, 3NF postoje i BCNF (Boyce-Coddova normalna forma), 4NF i 5NF.
Ak.god. 2017/2018. BAZE PODATAKA 72
Primjer 1.
• Profesionalni programer Ivo Ivić odlučio je napraviti program za evidentiranje svih programa koje je uradio, korisnika kod kojih ti programi rade, kao i ostvarene zarade po svakom programu.
Za svaki program se evidentira vrijeme utrošeno za njegovu izradu, kao i vrijeme utrošeno kod korisnika za prilagodbu programa korisničkim zahtjevima, kad je program pušten u rad kod korisnika, kao i koliko je od korisnika naplaćeno za taj program. Također se evidentira da li je program u uporabi tj. aktivan ili ne.
Ak.god. 2017/2018. BAZE PODATAKA 75
Primjer 2.
• Faktura je dokument koji se šalje kupcu kako bi mogao izvršiti plaćanje kupljene robe. Da bi se napravila faktura moraju postojati osnovni podaci o kupcu (naziv, adresa, telefon i sl.), podaci o fakturi (broj fakture, datum izdavanja, broj narudžbe prema kojoj je rađena, rok plaćanja, iznos za plaćanje i sl.), kao i podaci o kupljenim artiklima (naziv, jedinica mjere, kupljena količina, cijena i sl.).
Faktura se sastoji od 2 osnovna dijela:
- Zaglavlje fakture koje sadrži zajedničke, opće podatke
- Stavke fakture s pojedinačnim artiklima, količinama i cijenama.
Definirati sve entitete, atribute i veze za proces Fakturiranja kupcima.
Ak.god. 2017/2018. BAZE PODATAKA 78
Primjer 3.
• Na Fakultetu strojarstva i računarstva u Mostaru evidentirani su podaci o kandidatima koji su se prijavili na razradbeni ispit i njihovim rezultatima. Evidentiraju se podaci o:
- kandidatu: JMBG, ime, prezime, mjesto rođenja, završena srednja škola (šifra, naziv, adresa, poštanski broj mjesta, mjesto, šifra općine i naziv općine u kojoj se škola nalazi)
- za svakog kandidata ocjena iz pojedinih predmeta iz srednje škole –šifra i naziv predmeta, razred i ocjena. Šifra određuje predmet u nekom razredu (godini), npr. Matematika u 1.razredu i Matematika u 2.razredu imaju različite šifre. Isti predmeti u različitim školama imaju istu šifru npr. Matematika iz 1.razreda ima istu šifru za sve škole
- podaci o zadacima na razradbenom ispitu – redni broj zadatka, tekst zadatka, točan odgovor (A, B, C, D ili E)
- za svakog kandidata odgovori koje je dao na zadatke (odgovor za svaki pojedini zadatak mogu biti A, B, C, D, E ili ništa)
Ak.god. 2017/2018. BAZE PODATAKA 81
Primjer 4.
• U bazi podataka potrebno je evidentirati podatke o zrakoplovima, aerodromima i letovima.
Zrakoplov je identificiran svojim jedinstvenim registracijskim brojem (npr. N6061U). Za zrakoplov se evidentiraju godina proizvodnje i tip zrakoplova. Za svaki tip zrakoplova evidentira se šifra tipa i naziv tipa zrakoplova (npr. šifra: 123, naziv tipa: "Airbus-A319") te najveća ukupna dozvoljena težina pri polijetanju. Za svaki aerodrom se evidentira šifra i naziv aerodroma.
Zrakoplov prema namjeni može biti ili putnički ili teretni (jedno isključuje drugo). Za svaki pojedini zrakoplov, ovisno o njegovoj namjeni, evidentira se je li u zrakoplov ugrađena dodatna oprema: za svaki pojedini putnički zrakoplov evidentira se ima li ugrađenu internu televiziju, ima li uređaj za satelitsku komunikaciju, a za svaki pojedini teretni zrakoplov broj dodatno ugrađenih klimatiziranih kontejnera za prijevoz životinja i je li ugrađen katapult za izbacivanje tereta padobranom.
Ak.god. 2017/2018. BAZE PODATAKA 82
Primjer 4.(nastavak zadatka ...)
...
Evidentiraju se letovi samo putničkih zrakoplova. Let je identificiran šifrom i datumom leta (npr. "OU763", 1.6.2010), a za let se evidentira s kojeg aerodroma zrakoplov polijeće, na koji aerodrom slijeće te vrijeme polijetanja i vrijeme slijetanja. Evidentira se koji zrakoplov leti na kojem letu.
Evidentiraju se kategorije cijena karata. Svaka kategorija cijena ima svoju šifru (jedinstveno identificira kategoriju) i naziv (npr. "poslovna", "ekonomska", "s popustom za zaposlenika kompanije", "s popustom za osobe mlađe od 27 godina", itd). Putnik kupuje kartu točno određene kategorije cijene za određeni let. Putnik ne može za jedan let kupiti više od jedne karte. Za svakog putnika se evidentira jmbg (jedinstveno određuje putnika), prezime i ime.
Ak.god. 2017/2018. BAZE PODATAKA 86
Primjer 1. (Evidentiranje objavljenih radova)
• Profesor programiranja Ivo Ivić odlučio je napraviti program za evidentiranje svojih objavljenih radova.
To podrazumijeva evidentiranje svih do sada objavljenih radova, naslova radova, koautora koji su s njim učestvovali u pisanju radova, godina objavljivanja kao i časopisa u kojima je objavljivano.
Također treba uspostaviti klasifikaciju radova po principu: znanstveni, stručni ili pregledni rad, te da li je rad objavljen u međunarodnom ili domaćem časopisu.
Za svaki rad unose se podaci o naslovu rada, klasifikacijama, koautorima i časopisu u kojem je rad objavljen.
Ak.god. 2017/2018. BAZE PODATAKA 87
Primjer 2. (Ugovori)
• Na slici je dana denormalizirana tablica UGOVORI s nazivima stupaca i vrijednostima redaka. Napraviti normalizirani model podataka s pripadajućim entitetima, atributima i vezama između njih.
Ak.god. 2017/2018. BAZE PODATAKA 88
Primjer 3. (Filmovi)
• U bazi podataka su evidentirani podaci o filmovima. Za svaki film evidentira se žanr (šifra, naziv) i država (šifra, naziv).
Uz svaki film evidentiraju se različiti izvođači (šifra, ime, prezime) i funkcije koje su obavljali u filmu.
Funkcije su predstavljene kraticom i nazivom, a mogu biti npr. GL-glumac, RED-redatelj, SC-scenarist, SKL-skladatelj, itd.
Obratite pozornost na tip veze između entiteta funkcija, izvođač i film:
Ak.god. 2017/2018. BAZE PODATAKA 89
Primjer 4. (Multipleks kino)
• Napraviti normalizirani model podataka s pripadajućim entitetima, atributima i vezama između entiteta koji će podržavati proces rezervacije karata za multipleks kino.
U bazi podataka je potrebno evidentirati sljedeće podatke o filmovima: šifra, naziv, naziv originala, godina proizvodnje, trajanje filma, šifru i naziv žanra, glumce u filmu (šifra, ime, prezime).
Baza podataka također treba sadržavati popis dvorana u multipleks kinu (sifra, naziv, broj mjesta) kao i raspored svih projekcija filmova po dvoranama (datum i vrijeme projekcije, cijena karte za projekciju).
Ak.god. 2017/2018. BAZE PODATAKA 90
Primjer 5. (Tvrtka informatičke opreme)
• Tvrtka koja se bavi prodajom informatičke opreme prilikom prodaje iste sa svojim kupcima dogovara uvjete servisiranja opreme. U tvrtki radi više servisera i pri kupnji se ugovara koji je serviser zadužen za kojeg kupca s tim da na jednog servisera dođe više kupaca. Jednim ugovorom se definira točno razdoblje servisiranja (od kojeg do kojeg datuma), a podrazumijeva više izlazaka servisera na teren unutar definiranog vremenskog razdoblja. Naravno, intervencija servisera nije nužna. Prilikom izlaska na teren potrebno je zabilježiti točno vrijeme odlaska i povratka, vrijeme provedenu na terenu izraženo u satima, te na kojoj je komponenti, ili više njih, vršena popravka.
Napraviti normalizirani model podataka s pripadajućim entitetima, atributima i vezama između entiteta.
Ak.god. 2017/2018. BAZE PODATAKA 91
Primjer 6. (Tehnički biro)
• Napraviti normalizirani model podataka s pripadajućim entitetima, atributima i vezama između entiteta koji bi osigurao evidentiranje projekata, rokova i izvršitelja u jednom tehničkom birou.
Osnovni način poslovanja tehničkog biroa jeste rad na izradi određenih projekata, što znači da se za svaki projekt trebaju evidentirati osnovni podaci o naručitelju projekta (šifra, naziv, adresa, telefon, mail). Za svaki projekt postoji samo jedan glavni naručitelj s kojim se sklapa ugovor o poslu. O projektu se vode slijedeći podaci: naziv projekta, opis projekta, planirani datum početka rada na projektu, planirani svršetak rada na projektu, stvarni početak rada na projektu, stvarni svršetak rada na projektu, vrijednost projekta, ugovoreni penali za kašnjenje, dodatna napomena.
Na svakom projektu radi više djelatnika iz biroa (šifra, ime, prezime, zanimanje, titula, adresa, telefon, mail) koji rade na određenom radnom mjestu u birou (šifra, naziv radnog mjesta, opis). Projekt se razlaže na više različitih poslova (zadataka). Svakom članu projektnog tima dodjeljuje se točno određeni posao (zadatak) što se posebno i evidentira. Uz svaki posao (zadatak) evidentira se i naziv zadatka, kratak opis, planirani početak, planirani svršetak, stvarni početak, stvarni svršetak, vrijednost i napomena.
Ak.god. 2017/2018. BAZE PODATAKA 92
Primjer 7. (Svjetsko prvenstvo u nogometu)
• U bazi podataka potrebno je evidentirati podatke o nogometnim utakmicama na Svjetskom prvenstvu.
Utakmice se igraju između dviju država, od kojih jedna ima ulogu domaćina, a druga gosta. Svaka utakmica je identificirana šifrom, a osim toga se za utakmicu bilježi i datum odigravanja, rezultat i stadion na kojem je utakmica odigrana. Za svaki stadion se bilježi jedinstvena šifra, naziv stadiona i kapacitet. Na svakom stadionu se odigra više utakmica na Svjetskom prvenstvu. Svaka država odigra na prvenstvu više utakmica. Za svaku državu se bilježi jedinistvena šifra, naziv i godina osnutka nogometnog saveza u toj državi.
U bazi se evidentiraju i svi igrači koji su nastupili na utakmicama. Za igrače se bilježi jedinstvena šifra, ime, prezime, datum rođenja i država iz koje dolaze. Za svakog igrača se pored toga bilježi i broj minuta odigranih na svakoj od utakmica u kojoj je nastupio, te broj postignutih golova na utakmici.
Ak.god. 2017/2018. BAZE PODATAKA 94
1.) Modeliranje podataka
1.) Modeliranje podataka
o Relacijski model podataka
Opis zadatka:
Potrebno je za odabrani problem kreirati relacijski model u MS Accessu.
Opcionalno: Kreirati forme i izvješća u MS Accessu.
1. domaća zadaća se radi u MS Accessu.
Ak.god. 2017/2018. BAZE PODATAKA 96
SQL
• SQL = Structured Query Language
• SQL je upitni jezik temeljen na relacijskoj algebri i predikatnom računu.
• SQL se koristi kao programski jezik i interaktivni upitni jezik. Kao programski jezik može se ugrađivati u jezike treće i četvrte generacije.
• Zadaća SQL-a je omogućiti definiciju podataka, upravljanje podacima i provođenje kontrole nad podacima u relacijskoj bazi podataka.
• Proizvođači komercijalnih sustava također ugrađuju i svoje, uglavnom nestandardne, DDL i DML naredbe.
Ti su nestandardni dijelovi problematični jer programski kod postaje neprenosiv između različitih SQL sustava, a također se bitno otežava usaglašavanje oko budućih standarda.
Ak.god. 2017/2018. BAZE PODATAKA 97
SQL
• DDL (Data Definition Language)
= izrazi za definiranje podataka
CREATE, ALTER, DROP, GRANT, REVOKE ...
• DML (Data Manipulation Language)
= izrazi za upravljanje podacima
SELECT, INSERT, UPDATE, DELETE ...
Ak.god. 2017/2018. BAZE PODATAKA 98
SQL
• Primjer SQL naredbe iz DDL dijela jezika (kreiranje tablice mjesto):
CREATE TABLE mjesto
( pbr_mjesta VARCHAR2(5) NOT NULL ,
naziv_mjesta VARCHAR2(30) NOT NULL ,
sifra_opcine NUMBER(4)
);
• Primjer SQL naredbe iz DML dijela jezika (iz tablice mjesto dohvaća sve
n-torke kojima je vrijednost atributa poštanski broj jednaka 88000):
SELECT *
FROM mjesto
WHERE pbr = 88000;
Ak.god. 2017/2018. BAZE PODATAKA 102
SQL – Kreiranje i brisanje tablica
Kreiranje tablice:
CREATE TABLE ime_tablice
(ime_stupca tip (veličina) (ograničenje),
ime_stupca tip (veličina) (ograničenje),
ime_stupca tip (veličina) (ograničenje),
...);
Brisanje tablice:
DROP TABLE ime_tablice;
Ak.god. 2017/2018. BAZE PODATAKA 103
Ograničenja nad stupcima tablice i nad tablicama ...
• NULL/NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
Ak.god. 2017/2018. BAZE PODATAKA 104
Tipovi podataka (ORACLE)
• VARCHAR2(size)
• CHAR[(size)]
• NUMBER[(p,s)]
• DATE
Ak.god. 2017/2018. BAZE PODATAKA 107
SQL – Kreiranje tablica
CREATE TABLE MJESTO
( PBR NUMBER(10) NOT NULL,
NAZIV VARCHAR2(40) NOT NULL,
CONSTRAINT mjesto_pk PRIMARY KEY(PBR)
);
Ak.god. 2017/2018. BAZE PODATAKA 108
SQL – Kreiranje tablica
CREATE TABLE OSOBA
( MBR NUMBER(10) NOT NULL,
IME VARCHAR2(25) NOT NULL,
PREZIME VARCHAR2(25) NOT NULL,
EMAIL VARCHAR2(40),
PBR NUMBER(10) NOT NULL,
CONSTRAINT osoba_pk PRIMARY KEY(MBR),
CONSTRAINT osoba_mjesto_fk FOREIGN KEY(PBR)
REFERENCES MJESTO(PBR)
);
Ak.god. 2017/2018. BAZE PODATAKA 109
SQL – Izmjena definicije tablice
ALTER TABLE
dodavanje novih atributa
modificiranje postojećih atributa
definiranje default-ne vrijednosti za novi atribut
brisanje atributa
Ak.god. 2017/2018. BAZE PODATAKA 110
SQL – ALTER TABLE
Primjeri:
ALTER TABLE osoba
DROP COLUMN email;
ALTER TABLE osoba
ADD (email VARCHAR2(25));
ALTER TABLE mjesto
MODIFY (naziv VARCHAR2(50));
Ak.god. 2017/2018. BAZE PODATAKA 112
Najčešće korišteni DML izrazi
SELECT - Pretraživanje podataka
INSERT - Upisivanje novih podataka
UPDATE - Promjena vrijednosti podataka
DELETE - Brisanje postojećih podataka
Ak.god. 2017/2018. BAZE PODATAKA 113
INSERT – Unos podataka u tablicu
• INSERT INTO ime_tablice (stupac, stupac,…)
VALUES (vrijednost, vrijednost, …);
Primjeri:
• INSERT INTO osoba (mbr, ime, prezime, email)
VALUES (1, 'Ivo', 'Ivic', '[email protected]');
• INSERT INTO osoba
VALUES (1, 'Ivo', 'Ivic', '[email protected]');
Ak.god. 2017/2018. BAZE PODATAKA 114
INSERT – Unos podataka u tablicu
• INSERT se može koristiti i za kopiranje podataka
iz druge tablice.
Primjer:
• INSERT INTO osoba_2 (mbr, ime, prezime, email)
SELECT mbr, ime, prezime, email
FROM osoba;
Ak.god. 2017/2018. BAZE PODATAKA 115
UPDATE – Ažuriranje podataka u tablici
• UPDATE ime_tablice [alias] SET
stupac [,stupac…] = {iskaz, podupit}
[WHERE uvjet];
Primjer:
• UPDATE osoba SET email='[email protected]'
WHERE mbr=1;
Ak.god. 2017/2018. BAZE PODATAKA 116
DELETE – Brisanje podataka u tablici
• DELETE FROM ime_tablice
[WHERE uvjet];
Primjer:
• DELETE FROM osoba
WHERE mbr=2;
Ak.god. 2017/2018. BAZE PODATAKA 117
SELECT – Selektiranje podataka iz tablice
• SELECT stupac, stupac, stupac, …
FROM ime_tablice
[WHERE uvjet];
Primjeri:
• SELECT mbr, ime, prezime, email
FROM osoba;
• SELECT * FROM osoba;
Ak.god. 2017/2018. BAZE PODATAKA 118
INSERT – Primjeri
• INSERT INTO mjesto (pbr, naziv)
VALUES (88000, 'Mostar');
• INSERT INTO mjesto (pbr, naziv)
VALUES (88220, 'Široki Brijeg');
• INSERT INTO osoba (mbr, ime, prezime, email, pbr)
VALUES (1, 'Ivo', 'Ivic', '[email protected]', 88000);
• INSERT INTO osoba (mbr, ime, prezime, pbr)
VALUES (2, 'Mate', 'Matic', 88220);
Ak.god. 2017/2018. BAZE PODATAKA 119
INSERT – Primjeri
• INSERT INTO osoba (mbr, ime, prezime, email, pbr)
VALUES (2, 'Pero', 'Peric', '[email protected]', 88000);
→ ORA-00001: unique constraint (PC-1.OSOBA_PK) violated
• INSERT INTO osoba (mbr, ime, prezime, email, pbr)
VALUES (3, 'Pero', 'Peric', '[email protected]', 90000);
→ ORA-02291: integrity constraint (PC-1.OSOBA_MJESTO_FK)
violated - parent key not found
• INSERT INTO osoba (mbr, ime, email, pbr)
VALUES (3, 'Pero', '[email protected]', 88000);
→ ORA-01400: cannot insert NULL into ("OSOBA"."PREZIME")
Ak.god. 2017/2018. BAZE PODATAKA 120
UPDATE – Primjeri
• UPDATE mjesto SET pbr=10000
WHERE naziv='Mostar';
→ ORA-02292: integrity constraint (PC-1.OSOBA_MJESTO_FK) violated - child record found
• UPDATE mjesto SET naziv='Š.Brijeg'
WHERE pbr=88220;
• UPDATE osoba SET email='[email protected]'
WHERE mbr=2;
Ak.god. 2017/2018. BAZE PODATAKA 121
DELETE – Primjeri
• DELETE FROM mjesto
WHERE pbr=88000;
→ ORA-02292: integrity constraint (PC-1.OSOBA_MJESTO_FK) violated - child record found
• DELETE FROM osoba
WHERE mbr=1;
• DELETE FROM mjesto
WHERE pbr=88000;
Ak.god. 2017/2018. BAZE PODATAKA 123
Sigurnost i integritet
• Sigurnost baze podataka se brine da samo ovlašteni korisnici pristupaju podacima
• Integritet baze podataka se brine da ovlašteni korisnici koriste podatke na ispravan način
Integritet:
korektnost (dopuštene zdravorazumske vrijednosti podataka)
konzistencija (međusobna suglasnost podataka)
Ak.god. 2017/2018. BAZE PODATAKA 125
Korisnici i sigurnost
• CREATE USER pero IDENTIFIED BY pero;
• CONNECT pero/pero;
• ALTER USER pero IDENTIFIED BY pero1;
Ak.god. 2017/2018. BAZE PODATAKA 126
Sistemske privilegije
• više od 100 sistemskih privilegija
• Tipične DBA privilegije:
– CREATE USER
– DROP USER
– DROP ANY TABLE
– BACKUP ANY TABLE
– SELECT ANY TABLE
– CREATE ANY TABLE
Ak.god. 2017/2018. BAZE PODATAKA 127
Sistemske privilegije
• Osnovna sintaksa za dodjeljivanje sistemskih privilegija:
GRANT sistemske_privilegije
TO korisnik|uloga|PUBLIC
[WITH ADMIN OPTION]
• Osnovna sintaksa za ukidanje sistemskih privilegija:
REVOKE sistemske_privilegije
FROM korisnik|uloga|PUBLIC
Ak.god. 2017/2018. BAZE PODATAKA 128
Sistemske privilegije
Primjeri:
• GRANT create session, create table,
create sequence, create view
TO pero;
• REVOKE create view
FROM pero;
Ak.god. 2017/2018. BAZE PODATAKA 129
Uloga (Role)
Korisnici
Privilegije
Uloga
Dodjeljivanje privilegija korisnicima bez definirane uloge
Dodjeljivanje privilegija korisnicima nakon definiranja uloge
Ak.god. 2017/2018. BAZE PODATAKA 130
Uloga (Role)
• CREATE ROLE uloga_1 IDENTIFIED BY uloga_1;
• GRANT create session, create table, create view
TO uloga_1;
• GRANT uloga_1
TO pero;
Ak.god. 2017/2018. BAZE PODATAKA 131
Objektne privilegije
Najčešće korištene objektne privilegije su:
• SELECT – tablice, pogledi, sekvence
• INSERT, UPDATE, DELETE – tablice, pogledi
• INDEX, REFERENCES – tablice
• ALTER – tablice, sekvence
• EXECUTE – procedure
• ALL, ALL PRIVILEGES
Ak.god. 2017/2018. BAZE PODATAKA 132
Objektne privilegije
• Osnovna sintaksa za dodjeljivanje objektnih privilegija:
GRANT objektne_privilegije
ON ime_objekta
TO korisnik|uloga|PUBLIC
[WITH GRANT OPTION]
• Osnovna sintaksa za ukidanje objektnih privilegija:
REVOKE objektne_privilegije
ON ime_objekta
FROM korisnik|uloga|PUBLIC
Ak.god. 2017/2018. BAZE PODATAKA 133
Objektne privilegije
Primjer:
• GRANT select, insert
ON osoba
TO pero, mate
WITH GRANT OPTION;
• GRANT select
ON mjesto
TO PUBLIC;
Ak.god. 2017/2018. BAZE PODATAKA 135
Implementacija relacijskog modela i sigurnost baze podataka
U izabranom sustavu za upravljanje bazom podataka potrebno je:
Instalacija
o Kratko opisati proces instalacije (opisati karakteristične stvari pri instalaciji, eventualne probleme na koje ste naišli, ...)
1) Implementacija relacijskog modela
o Opisati proces implementacije relacijskog modela u bazi podataka (relacijski model je kreiran u 1. domaćoj zadaći).
o Pri kreiranju tablica posebnu pozornost obratiti na različite tipove podataka, implementaciju integriteta itd.
2) Sigurnost baze podataka
o Navesti koji korisnici su automatski bili definirani u bazi podataka pri instalaciji i sa kojim ovlastima.
o Kreirati minimalno 2 nova korisnika s tim da jedan ima samo SELECT ovlasti nad svim tablicama kreiranog relacijskog modela, a drugi korisnik neka ima sve ovlasti za rad sa podacima u kreiranom relacijskom modelu.
Ak.god. 2017/2018. BAZE PODATAKA 138
Testna baza (Oracle DBMS) (Baza je napunjena testnim podacima - koristiti ćemo je na vježbama za SELECT upite ...)
Ak.god. 2017/2018. BAZE PODATAKA 139
SELECT – Odabir podataka iz tablice
• SELECT stupci [alias]
FROM ime_tablice [alias]
[WHERE uvjeti za redak]
[GROUP BY stupci]
[HAVING uvjeti za grupu redaka]
[ORDER BY stupci];
Primjer – odabir svih podataka iz tablice djelatnik
• SELECT * FROM djelatnik;
Ak.god. 2017/2018. BAZE PODATAKA 140
Operatori usporedbe, Logički i SQL operatori
=, >, <, >=, <=, <>
BETWEEN … AND… – između dvije vrijednosti (uključivo)
IN (lista) – odgovara bilo kojoj vrijednosti iz liste
LIKE – odgovara znakovnom “uzorku”
IS NULL – je NULL vrijednost
AND – vraća TRUE ako su svi uvjeti TRUE
OR – vraća TRUE ako je jedan od uvjeta TRUE
NOT – logičko NE
Ak.god. 2017/2018. BAZE PODATAKA 141
Primjeri
• SELECT id_djelatnika, ime, prezime, placa
FROM djelatnik
WHERE placa > 1000
AND sifra_radmj='IT-PROG';
• SELECT id_djelatnika, ime, prezime, placa
FROM djelatnik
WHERE placa BETWEEN 1000 AND 2000;
Ak.god. 2017/2018. BAZE PODATAKA 142
Primjeri
• SELECT id_djelatnika, ime, prezime, placa
FROM djelatnik
WHERE ime LIKE 'A%';
• SELECT id_djelatnika, ime, prezime, placa
FROM djelatnik
WHERE ime LIKE '_a%';
Ak.god. 2017/2018. BAZE PODATAKA 143
Primjeri
• SELECT id_djelatnika, ime, prezime, placa
FROM djelatnik
WHERE placa IN (1000, 2000);
• SELECT id_djelatnika, ime, prezime, placa
FROM djelatnik
WHERE placa NOT IN (1000, 2000);
Ak.god. 2017/2018. BAZE PODATAKA 144
Primjeri
• SELECT id_djelatnika, ime, prezime, placa, email
FROM djelatnik
WHERE email IS NULL;
• SELECT id_djelatnika, ime, prezime, placa, dodatak
FROM djelatnik
WHERE dodatak IS NOT NULL
AND dodatak <> 100;
Ak.god. 2017/2018. BAZE PODATAKA 145
Primjeri
• SELECT id_djelatnika, ime, prezime, placa
FROM djelatnik
WHERE placa < 1000
OR placa > 2000;
• SELECT id_djelatnika, ime, prezime, sifra_radmj
FROM djelatnik
WHERE sifra_radmj = 'IT-PROG'
OR sifra_radmj = 'IT-ADM';
Ak.god. 2017/2018. BAZE PODATAKA 146
Primjeri
Obratiti pozornost – razlikovati sljedeća 2 upita !
• SELECT id_djelatnika, ime, prezime, sifra_radmj, placa
FROM djelatnik
WHERE placa > 1300
AND sifra_radmj = 'IT-PROG'
OR sifra_radmj = 'IT-ADM';
• SELECT id_djelatnika, ime, prezime, sifra_radmj, placa
FROM djelatnik
WHERE placa > 1300
AND ( sifra_radmj = 'IT-PROG'
OR sifra_radmj = 'IT-ADM' );
Ak.god. 2017/2018. BAZE PODATAKA 147
Agregatne funkcije SQL-a
• ISO standard definira 5 agregatnih funkcija:
o COUNT
o SUM
o AVG
o MIN
o MAX
• Ove funkcije se izvršavaju nad jednim stupcem tablice i vraćaju jednu vrijednost.
• VAŽNO ! Agregatne funkcije se mogu koristiti samo
u SELECT listi i HAVING iskazu.
Ak.god. 2017/2018. BAZE PODATAKA 148
Agregatne funkcije SQL-a – Primjeri
• SELECT SUM(placa), AVG(placa), MIN(placa), MAX(placa)
FROM djelatnik;
• SELECT COUNT(*) broj_djelatnika
FROM djelatnik;
• SELECT COUNT (DISTINCT sifra_radmj)
FROM djelatnik;
DISTINCT – eliminira duple vrijednosti
Nema efekta na funkcije MIN i MAX, ali ima na SUM i AVG. Može
se navesti samo jedanput u upitu.
Ak.god. 2017/2018. BAZE PODATAKA 149
Agregatne funkcije SQL-a – Primjeri
• SELECT ime, prezime
FROM djelatnik
WHERE placa > AVG(placa);
→ORA-00934: group function is not allowed here
• SELECT ime, prezime, SUM(placa)
FROM djelatnik;
→ORA-00937: not a single-group group function
Ak.god. 2017/2018. BAZE PODATAKA 150
GROUP BY
• Grupiranje se obavlja tako da se n-torke koje imaju jednake vrijednosti atributa navedenih u listi za grupiranje, kombiniraju u zajedničku grupu. Za svaku dobivenu grupu, u rezultatu se pojavljuje samo jedna n-torka.
• Grupiranje je vrlo korisno u kombinaciji s agregatnim funkcijama (COUNT, SUM, AVG, MIN, MAX).
Primjer:
• SELECT sifra_radmj, AVG(placa) prosjecna_placa
FROM djelatnik
GROUP BY sifra_radmj;
Ak.god. 2017/2018. BAZE PODATAKA 151
GROUP BY
• P R A V I L O !
Bilo koji atribut ili izraz u SELECT listi koji
nije agregatna funkcija mora biti i u GROUP BY iskazu.
Međutim, dopušteno je u GROUP BY iskazu koristiti i one atribute koji se ne nalaze u SELECT listi.
Ak.god. 2017/2018. BAZE PODATAKA 152
GROUP BY – Primjeri
• SELECT sifra_radmj, AVG(placa) prosj_placa
FROM djelatnik
GROUP BY sifra_radmj;
• SELECT AVG(placa)
FROM djelatnik
GROUP BY sifra_radmj;
Ak.god. 2017/2018. BAZE PODATAKA 153
GROUP BY – Primjeri
• SELECT sifra_radmj, spol, AVG(placa)
FROM djelatnik
GROUP BY sifra_radmj;
→ ORA-00979: not a GROUP BY expression
• SELECT sifra_radmj, spol, AVG(placa)
FROM djelatnik
GROUP BY sifra_radmj, spol;
Ak.god. 2017/2018. BAZE PODATAKA 154
GROUP BY – Primjeri
• SELECT AVG(COUNT(*)) prosjek_djel_rm
FROM djelatnik
GROUP BY sifra_radmj;
• SELECT MAX(COUNT(*)) max_djel_rm
FROM djelatnik
GROUP BY sifra_radmj;
Ak.god. 2017/2018. BAZE PODATAKA 155
HAVING
• Koristi se ako se želi specificirati koje grupe treba prikazati, odnosno, koristi se za restrikciju grupa koje se prikazuju, tj. za ispitivanje vrijednosti agregatnih funkcija.
• VAŽNO ! WHERE iskaz se ne može koristiti za
restrikciju grupa (kao HAVING).
WHERE se koristi samo za restrikciju pojedinačnih redaka.
Ak.god. 2017/2018. BAZE PODATAKA 156
HAVING – Primjeri
• SELECT sifra_radmj radno_mjesto, AVG(placa) prosj_placa
FROM djelatnik
GROUP BY sifra_radmj
HAVING AVG(placa)>1500;
• SELECT sifra_opcine, COUNT(*) broj_dj
FROM djelatnik
GROUP BY sifra_opcine
HAVING COUNT(*)>=3;
Ak.god. 2017/2018. BAZE PODATAKA 157
ORDER BY
• ASC – uzlazno sortiranje (po default-u)
• DESC – silazno sortiranje
→ ORDER BY iskaz dolazi na kraju SELECT upita.
Primjer:
• SELECT ime, prezime, placa
FROM djelatnik
ORDER BY placa DESC;
Ak.god. 2017/2018. BAZE PODATAKA 158
Izvršavanje SQL iskaza ...
• SELECT sifra_radmj, COUNT(*) broj_djelatnika
FROM djelatnik
WHERE placa>1000
GROUP BY sifra_radmj
HAVING COUNT(*)>2
ORDER BY 2 desc;
• Pregled radnih mjesta na kojima radi više od 2 djelatnika s plaćom većom od 1000 KM.
Ak.god. 2017/2018. BAZE PODATAKA 159
Izvršavanje SQL iskaza ...
• Pregled radnih mjesta na kojima radi više od 2 djelatnika s plaćom većom od 1000 KM.
ID_DJELATNIKA SIFRA_RADMJ PLACA
1 DIR 4800
2 TAJNIK 1100
100 IT-VOD 2500
101 IT-PROG 1600
102 IT-ADM 1400
103 IT-PROG 1800
104 IT-PROG 1500
105 IT-PROG 1500
106 IT-ADM 1300
107 IT-PROG 1250
108 IT-PROG 1200
109 IT-PROG 1000
110 IT-PROG 800
111 IT-PROG 900
112 IT-ADM 700
113 IT-PROG 1100
114 IT-PROG 950
115 IT-PROG 1350
200 PROD-VOD 2000
201 PROD-KAM 1000
202 PROD-KAM 1400
203 PROD-KAM 1100
204 PROD-KAM 1200
... ... ...
WHEREplaca > 1000
1. 2.
ID_DJELATNIKA SIFRA_RADMJ PLACA
1 DIR 4800
2 TAJNIK 1100
100 IT-VOD 2500
101 IT-PROG 1600
102 IT-ADM 1400
103 IT-PROG 1800
104 IT-PROG 1500
105 IT-PROG 1500
106 IT-ADM 1300
107 IT-PROG 1250
108 IT-PROG 1200
113 IT-PROG 1100
115 IT-PROG 1350
200 PROD-VOD 2000
202 PROD-KAM 1400
203 PROD-KAM 1100
204 PROD-KAM 1200
... ... ...
GROUP BY sifra_radmj
Ak.god. 2017/2018. BAZE PODATAKA 160
Izvršavanje SQL iskaza ...
SIFRA_RADMJ COUNT(*)
PROD-KAM 3
IT-ADM 2
IT-PROG 8
...
GROUP BY sifra_radmj
HAVING COUNT(*)>2
ORDER BY 2 desc
2. 3.
4.
• Pregled radnih mjesta na kojima radi više od 2 djelatnika s plaćom većom od 1000 KM.
SIFRA_RADMJ COUNT(*)
PROD-KAM 3
IT-PROG 8
SIFRA_RADMJ COUNT(*)
IT-PROG 8
PROD-KAM 3
Ak.god. 2017/2018. BAZE PODATAKA 161
WHERE – GROUP BY – HAVING
• WHERE dio naredbe određuje koje n-torke će
formirati grupe.
• GROUP BY lista određuje strukturu grupa tj. po
kojim atributima se obavlja grupiranje n-torki.
• HAVING dio naredbe određuje koje od nastalih
grupa će biti prihvaćene kao rezultat.
Ak.god. 2017/2018. BAZE PODATAKA 163
Funkcije (ORACLE)
• CHARACTER funkcije
- LOWER, UPPER, INITCAP, CONCAT, LPAD, RPAD, SUBSTR, INSTR, LTRIM, RTRIM, LENGTH, TRANSLATE, REPLACE
• NUMBER funkcije
- ROUND, TRUNC, SIGN, SQRT, ABS, MOD
Matematičke (LOG, SIN, TAN, COS, EXP)
• DATE funkcije
- ADD_MONTHS, MONTHS_BETWEEN, NEXT_DAY, LAST_DAY, ROUND, TRUNC
• Funkcije za konverziju podataka
- TO_CHAR
- TO_NUMBER
- TO_DATE
Ak.god. 2017/2018. BAZE PODATAKA 164
Funkcije – Primjeri
Pr.) CHARACTER funkcije
• SELECT ime || ' ' || prezime NAZIV, spol, placa
FROM djelatnik
WHERE UPPER(prezime)='IVIĆ';
• SELECT ime, LENGTH(ime), SUBSTR(ime,1,3)
FROM djelatnik;
Ak.god. 2017/2018. BAZE PODATAKA 165
Pr.) NUMBER funkcije
• SELECT sifra_radmj, ROUND(AVG(placa),2)
FROM djelatnik
GROUP BY sifra_radmj
HAVING ROUND(AVG(placa),2)>1200
ORDER BY 2 desc;
Funkcije – Primjeri
Ak.god. 2017/2018. BAZE PODATAKA 166
Pr.) DATE funkcije
• SELECT ime, prezime, placa, datum_zaposlenja,
MONTHS_BETWEEN(SYSDATE, datum_zaposlenja)/12 STAZ
FROM djelatnik
ORDER BY 5 desc;
Funkcije – Primjeri
Ak.god. 2017/2018. BAZE PODATAKA 167
NULL vrijednost u izrazima
• Ukoliko vršimo neku od aritmetičkih operacija (+, -, *, /), a jedan ili oba operanda poprimaju NULL vrijednost, tada je rezultat izraza također NULL vrijednost.
• Primjeri:
o 3 + NULL = NULL
o NULL – NULL = NULL
o NULL * 2 = NULL
• Obratiti pozornost ! NULL i nula (0) nisu isto.
Ak.god. 2017/2018. BAZE PODATAKA 168
NVL funkcija
• SELECT id_djelatnika, ime, prezime, placa, dodatak,
placa+dodatak UKUPNA_PLACA
FROM djelatnik;
• SELECT id_djelatnika, ime, prezime, placa, dodatak,
placa+NVL(dodatak,0) UKUPNA_PLACA
FROM djelatnik;
Ak.god. 2017/2018. BAZE PODATAKA 169
IF-THEN-ELSE
• IF-THEN-ELSE logika u SQL upitima se može realizirati korištenjem:
o CASE izraza
o DECODE funkcije(Oracle)
Ak.god. 2017/2018. BAZE PODATAKA 170
CASE, DECODE – Primjeri
Primjer:
• SELECT ime, prezime, spol,
CASE spol
WHEN 'M' THEN 'Muško'
WHEN 'Ž' THEN 'Žensko'
ELSE '???'
END
FROM djelatnik;
• SELECT ime, prezime, spol,
DECODE ( spol, 'M', 'Muško', 'Ž', 'Žensko', '???')
FROM djelatnik;
Ak.god. 2017/2018. BAZE PODATAKA 171
CASE, DECODE – Primjeri
Primjer:
• SELECT ime, prezime, spol, placa,
CASE spol
WHEN 'M' THEN placa*0.95
WHEN 'Ž' THEN placa*1.1
ELSE placa
END nova_plaća
FROM djelatnik;
• SELECT ime, prezime, spol, placa,
DECODE (spol, 'M', placa*0.95, 'Ž', placa*1.1, placa) nova_plaća
FROM djelatnik;
Ak.god. 2017/2018. BAZE PODATAKA 173
SQL (spajanje tablica)
SQL – osnovne metode spajanja tablica:
equi-join
non-equi join
outer join
self join
Ak.god. 2017/2018. BAZE PODATAKA 174
SQL (osnovna sintaksa spajanja tablica)
• SELECT tablica1.atribut1, tablica2.atribut2
FROM tablica1
[INNER JOIN tablica2
ON (tablica1.atribut1=tablica2.atribut2)] |
[LEFT|RIGHT|FULL OUTER JOIN tablica2
ON (tablica1.atribut1=tablica2.atribut2)];
• Rezervirane riječi INNER i OUTER se mogu izostaviti !
• JOIN ON – Sintaksa za spajanje koja se najčešće koristi bilo
da je u pitanju equi-join ili self-join.
Ak.god. 2017/2018. BAZE PODATAKA 175
SQL (osnovna sintaksa spajanja tablica)
• SELECT tablica1.atribut1, tablica2.atribut2, ...
FROM tablica1, tablica2, tablica3, ...
WHERE uvjet spajanja 1 (FK<->PK)
AND uvjet spajanja 2 (FK<->PK)
...
P R A V I L O !
Broj tablica – 1 = Broj uvjeta spajanja
Ak.god. 2017/2018. BAZE PODATAKA 176
SQLCROSS JOIN (Kartezijev produkt)
Primjer:
• SELECT id, ime, naziv_mjesta
FROM osoba, mjesto
• SELECT id, ime, naziv_mjesta
FROM osoba
CROSS JOIN mjesto
Obratiti pozornost ! Obavljanje Kartezijevog produktarelacija često je posljedica pogreške programera – kad se zaboravi navesti uvjet spajanja.
Ak.god. 2017/2018. BAZE PODATAKA 177
ID IME PBR
1 Pero 88000
2 Mate 88000
3 Ivo 88220
PBR NAZIV_MJESTA
88000 Mostar
88220 Široki Brijeg
OSOBA MJESTO
ID IME NAZIV_MJESTA
1 Pero Mostar
2 Mate Mostar
3 Ivo Mostar
1 Pero Široki Brijeg
2 Mate Široki Brijeg
3 Ivo Široki Brijeg
OSOBA x MJESTO
SQLCROSS JOIN
Ak.god. 2017/2018. BAZE PODATAKA 178
ID IME PBR
1 Pero 88000
2 Mate 88000
3 Ivo 88220
4 Tomo 88000
5 Marija 88220
PBR NAZIV_MJESTA
88000 Mostar
88220 Široki Brijeg
OSOBA MJESTO
Primjer:
• SELECT id, ime, naziv_mjesta
FROM osoba, mjesto
WHERE osoba.pbr=mjesto.pbr;
• SELECT id, ime, naziv_mjesta
FROM osoba JOIN mjesto
ON osoba.pbr=mjesto.pbr;
SQLEQUI JOIN
Ak.god. 2017/2018. BAZE PODATAKA 179
ID IME NAZIV_MJESTA
1 Pero Mostar
2 Mate Mostar
3 Ivo Široki Brijeg
4 Tomo Mostar
5 Marija Široki Brijeg
Rezultat upita
Primjer:
• SELECT id, ime, naziv_mjesta
FROM osoba, mjesto
WHERE osoba.pbr=mjesto.pbr;
• SELECT id, ime, naziv_mjesta
FROM osoba JOIN mjesto
ON osoba.pbr=mjesto.pbr;
SQLEQUI JOIN
OSOBA wv MJESTO
Ak.god. 2017/2018. BAZE PODATAKA 180
Primjer (spajanje 3 tablice):
• SELECT ime, prezime, placa, naziv_opcine, z.naziv_zupanije
FROM djelatnik d, opcina o, zupanija z
WHERE d.sifra_opcine = o.sifra_opcine
AND o.sifra_zupanije = z.sifra_zupanije;
• SELECT ime, prezime, placa, naziv_opcine, z.naziv_zupanije
FROM djelatnik d
JOIN opcina o ON d.sifra_opcine = o.sifra_opcine
JOIN zupanija z ON o.sifra_zupanije = z.sifra_zupanije;
SQLEQUI JOIN
Ak.god. 2017/2018. BAZE PODATAKA 181
• EQUI JOIN – spajanje koje se najčešće koristi !
o Kao operator spajanja koristi se isključivo operator jednakosti (=)
• NON-EQUI JOIN – spajanje koje se relativno rijetko koristi.
o Kod pisanja uvjeta spajanja ne koristi se operator jednakosti (=), nego se koriste drugi operatori usporedbe (>, <, >=, <=, <>)
Primjer:
• SELECT o.ime, o.prezime, o.placa
FROM osoba o, platni_razred p
WHERE o.placa > p.min_placa;
SQLNON-EQUI JOIN
Ak.god. 2017/2018. BAZE PODATAKA 182
• NATURAL JOIN – bazira se na spajanju onih atributa iz tablica koji imaju isto ime i tip.
U donjem primjeru to je atribut sifra_zupanije (PK u tablici zupanija, FK u tablici opcina).
Primjer:
• SELECT o.sifra_opcine, o.naziv_opcine, z.naziv_zupanije
FROM opcina o NATURAL JOIN zupanija z;
SQLNATURAL JOIN
Ak.god. 2017/2018. BAZE PODATAKA 183
ID IME PBR
1 Pero 88000
2 Mate 88000
3 Ivo 88220
4 Tomo 88000
5 Marija NULL
PBR NAZIV_MJESTA
88000 Mostar
88220 Široki Brijeg
OSOBA MJESTO
?
SQLOUTER JOIN
Primjer:
• SELECT id, ime, naziv_mjesta
FROM osoba JOIN mjesto
ON osoba.pbr=mjesto.pbr;
• SELECT id, ime, naziv_mjesta
FROM osoba LEFT JOIN mjesto
ON osoba.pbr=mjesto.pbr;
Ak.god. 2017/2018. BAZE PODATAKA 184
ID IME NAZIV_MJESTA
1 Pero Mostar
2 Mate Mostar
3 Ivo Široki Brijeg
4 Tomo Mostar
5 Marija
• SELECT id, ime, naziv_mjesta
FROM osoba JOIN mjesto
ON osoba.pbr=mjesto.pbr;
ID IME NAZIV_MJESTA
1 Pero Mostar
2 Mate Mostar
3 Ivo Široki Brijeg
4 Tomo Mostar
• SELECT id, ime, naziv_mjesta
FROM osoba LEFT JOIN mjesto
ON osoba.pbr=mjesto.pbr;
SQLOUTER JOIN
Ak.god. 2017/2018. BAZE PODATAKA 185
Primjer:
• SELECT d.ime, d.prezime, d.placa, rm.naziv_radmj
FROM djelatnik d JOIN radno_mjesto rm
ON d.sifra_radmj=rm.sifra_radmj
WHERE placa>1500;
• SELECT d.ime, d.prezime, d.placa, rm.naziv_radmj, o.naziv_odjela
FROM djelatnik d JOIN radno_mjesto rm
ON d.sifra_radmj=rm.sifra_radmj
LEFT JOIN odjel o
ON rm.sifra_odjela=o.sifra_odjela
WHERE placa>1500;
SQLOUTER JOIN
Ak.god. 2017/2018. BAZE PODATAKA 186
Primjeri:
• SELECT rm.naziv_radmj, o.naziv_odjela
FROM radno_mjesto rm
LEFT (OUTER) JOIN odjel o
ON rm.sifra_odjela=o.sifra_odjela;
• SELECT rm.naziv_radmj, o.naziv_odjela
FROM radno_mjesto rm
RIGHT (OUTER) JOIN odjel o
ON rm.sifra_odjela=o.sifra_odjela;
• SELECT rm.naziv_radmj, o.naziv_odjela
FROM radno_mjesto rm
FULL (OUTER) JOIN odjel o
ON rm.sifra_odjela=o.sifra_odjela;
SQLOUTER JOIN
Ak.god. 2017/2018. BAZE PODATAKA 187
ID IME ID_SEFA
1 Pero NULL
2 Mate 1
3 Ivo 1
4 Tomo 1
5 Marija 3
OSOBA
Primjer:
• SELECT a.id, a.ime, b.ime šef
FROM osoba a, osoba b
WHERE a.id_sefa=b.id;
ID IME_DJ IME_SEFA
2 Mate Pero
3 Ivo Pero
4 Tomo Pero
5 Marija Ivo
SQLSELF JOIN
• SELECT a.id, a.ime, b.ime šef
FROM osoba a JOIN osoba b
ON a.id_sefa=b.id;
Ak.god. 2017/2018. BAZE PODATAKA 188
Primjer:
• SELECT d1.ime || ' ' || d1.prezime djelatnik, d1.placa, d2.ime || ' ' || d2.prezime šef, d2.placa
FROM djelatnik d1, djelatnik d2
WHERE d1.id_sefa=d2.id_djelatnika
AND d1.placa > d2.placa;
• SELECT d1.ime || ' ' || d1.prezime djelatnik, d1.placa, d2.ime || ' ' || d2.prezime šef, d2.placa
FROM djelatnik d1 JOIN djelatnik d2
ON d1.id_sefa = d2.id_djelatnika
WHERE d1.placa > d2.placa;
SQLSELF JOIN
Ak.god. 2017/2018. BAZE PODATAKA 190
Podupiti
SELECT ...
FROM ...
WHERE ...
( SELECT ...
FROM ...
WHERE ... )
Podupit
Ak.god. 2017/2018. BAZE PODATAKA 191
Korištenje podupita
Primjer:
• SELECT ime, prezime, placa
FROM djelatnik
WHERE placa >
( SELECT placa
FROM djelatnik
WHERE ime = 'Ernest' );
• U ovom primjeru «unutarnji» SELECT iskaz (PODUPIT) vraća plaću djelatnika po imenu Ernest.
• «Vanjski» SELECT iskaz koristi taj rezultat «unutarnjeg» upita da bi prikazao sve djelatnike koji zarađuju više od tog iznosa.
1800
Ak.god. 2017/2018. BAZE PODATAKA 192
Tipovi podupita
• Podupiti koji vraćaju jedan redak
Operatori: =, >, <, >=, <=, <>
• Podupiti koji vraćaju više od jednog retka
Operatori: IN, ANY, ALL
Ak.god. 2017/2018. BAZE PODATAKA 193
Podupiti – Primjeri najčešćih pogrešaka
GREŠKA ! Podupit vraća više od jednog retka, a koristi se
operator jednakosti (=).
Primjer:
• SELECT id_djelatnika, ime, prezime, placa
FROM djelatnik
WHERE placa =
( SELECT MIN(placa)
FROM djelatnik
GROUP BY sifra_radmj );
→ ORA-01427: single-row subquery returns more than one row
Ak.god. 2017/2018. BAZE PODATAKA 194
Podupiti koji vraćaju više od jednog retka
IN operator
Da bi ispravili grešku u prethodnom upitu operator (=) npr. možemo promijeniti u operator (IN).
Primjer:
• SELECT id_djelatnika, ime, prezime, placa
FROM djelatnik
WHERE placa IN
( SELECT MIN(placa)
FROM djelatnik
GROUP BY sifra_radmj );
Ak.god. 2017/2018. BAZE PODATAKA 195
Podupiti – Primjeri najčešćih pogrešaka
GREŠKA ! Podupit ne vraća niti jedan redak.
Primjer:
• SELECT ime, prezime, sifra_radmj
FROM djelatnik
WHERE sifra_radmj =
( SELECT sifra_radmj
FROM djelatnik
WHERE ime='Grigor' );
Ak.god. 2017/2018. BAZE PODATAKA 196
Podupiti – Primjer
Primjer:
• Prikaži djelatnike čije radno mjesto je isto kao i djelatnika 101.
• SELECT ime, prezime, sifra_radmj
FROM djelatnik
WHERE sifra_radmj =
( SELECT sifra_radmj
FROM djelatnik
WHERE id_djelatnika = 101 );
IT-PROG
Ak.god. 2017/2018. BAZE PODATAKA 197
Podupiti – Primjer korištenja agregatnih funkcija
Primjer:
• Prikaži sve djelatnike (ime, prezime, datum zaposlenja, plaća) čija plaća je veća od prosječne plaće u poduzeću.
• SELECT ime, prezime, datum_zaposlenja, placa
FROM djelatnik
WHERE placa >
( SELECT AVG (placa)
FROM djelatnik
);
1.450
Ak.god. 2017/2018. BAZE PODATAKA 198
Podupiti – Primjer
Primjer:
• Prikaži djelatnike čije radno mjesto je isto kao i djelatnika 101 i čija plaća je veća od plaće djelatnike 2.
• SELECT ime, prezime, sifra_radmj, placa
FROM djelatnik
WHERE sifra_radmj = ( SELECT sifra_radmj
FROM djelatnik
WHERE id_djelatnika = 101 )
AND placa > ( SELECT placa
FROM djelatnik
WHERE id_djelatnika = 2 );
Ak.god. 2017/2018. BAZE PODATAKA 199
Podupiti – Primjer korištenja podupita u HAVING dijelu iskaza
Primjer:
• SELECT sifra_radmj, AVG (placa)
FROM djelatnik
GROUP BY sifra_radmj
HAVING AVG (placa) >
( SELECT AVG(placa)
FROM djelatnik
WHERE sifra_radmj='IT-PROG');
• Podupit se može koristiti ne samo u WHERE dijelu SELECT iskaza, nego i u HAVING dijelu iskaza.
• Prvo se izvršava podupit, a zatim se rezultat podupita vraća u HAVING dio vanjskog upita.
Ak.god. 2017/2018. BAZE PODATAKA 200
Podupiti koji vraćaju više od jednog retka
ANY i ALL operator
< ANY = manje od maximuma
> ANY = više od minimuma
< ALL = manje od minimuma
> ALL = više od maximuma
Ak.god. 2017/2018. BAZE PODATAKA 201
Podupiti – Primjer
Primjer:
• Prikaži djelatnike koji nisu IT programeri i čija plaća je niža od plaće nekog od IT programera.
• SELECT id_djelatnika, ime, prezime, sifra_radmj, placa
FROM djelatnik
WHERE placa < ANY
( SELECT placa
FROM djelatnik
WHERE sifra_radmj = 'IT-PROG' )
AND sifra_radmj <> 'IT-PROG' ;
Ak.god. 2017/2018. BAZE PODATAKA 202
Podupiti – Primjer
Primjer:
• Prikaži djelatnike koji nisu na radnom mjestu PROD-KAM i čija plaća je niža od plaće svih djelatnika na radnom mjestu PROD-KAM.
• SELECT id_djelatnika, ime, prezime, sifra_radmj, placa
FROM djelatnik
WHERE placa < ALL
( SELECT placa
FROM djelatnik
WHERE sifra_radmj = 'PROD-KAM' )
AND sifra_radmj <> 'PROD-KAM' ;
Ak.god. 2017/2018. BAZE PODATAKA 203
Korelacijski podupiti
uzima se kandidat red iz vanjskog upita
izvršava se unutarnji upit koristeći podatke iz
kandidat reda
vrijednosti iz unutarnjeg upita se koriste da se kandidat red
kvalificira ili diskvalificira
• Podupit (unutarnji upit) se izvršava jedanput za svaki redak iz vanjskog upita.
Ak.god. 2017/2018. BAZE PODATAKA 204
Korelacijski podupiti
Primjer:
• Prikaži sve djelatnike koji zarađuju više od prosječne plaće u njihovom odjelu.
• SELECT ime, prezime, d.sifra_radmj, placa
FROM djelatnik d, radno_mjesto vanjski_rm
WHERE d.sifra_radmj=vanjski_rm.sifra_radmj
AND placa > ( SELECT AVG(placa)
FROM djelatnik d, radno_mjesto rm
WHERE d.sifra_radmj=rm.sifra_radmj
AND rm.sifra_odjela =vanjski_rm.sifra_odjela );
Ak.god. 2017/2018. BAZE PODATAKA 205
EXISTS operator
• EXISTS operator testira postojanje redaka u rezultatu podupita:
– ako podupit vrati bar jedan redak operator EXISTS će vratiti TRUE.
– ako podupit ne vrati niti jedan redak operator EXISTS će vratiti FALSE.
Ak.god. 2017/2018. BAZE PODATAKA 206
Podupiti – Primjer
Primjer:
• Prikaži sve djelatnike koji imaju bar jednu osobu koja im je “podređena”.
a) korištenjem EXISTS operatora
• SELECT id_djelatnika, ime, prezime, sifra_radmj
FROM djelatnik d
WHERE EXISTS (
SELECT 'X'
FROM djelatnik
WHERE id_sefa=d.id_djelatnika
);
Ak.god. 2017/2018. BAZE PODATAKA 207
Podupiti – Primjer
Primjer:
• Prikaži sve djelatnike koji imaju bar jednu osobu koja im je “podređena”.
b) korištenjem IN operatora
• SELECT id_djelatnika, ime, prezime, sifra_radmj
FROM djelatnik
WHERE id_djelatnika IN (
SELECT id_sefa
FROM djelatnik
WHERE id_sefa IS NOT NULL
);
Ak.god. 2017/2018. BAZE PODATAKA 208
Podupiti – Primjer
Primjer:
• Prikaži općine u kojima ne živi niti jedan djelatnik poduzeća.
a) korištenjem NOT EXISTS operatora
• SELECT sifra_opcine, naziv_opcine
FROM opcina o
WHERE NOT EXISTS (
SELECT 'X'
FROM djelatnik
WHERE sifra_opcine=o.sifra_opcine
);
Ak.god. 2017/2018. BAZE PODATAKA 209
Podupiti – Primjer
Primjer:
• Prikaži općine u kojima ne živi niti jedan djelatnik poduzeća.
b) korištenjem NOT IN operatora
• SELECT sifra_opcine, naziv_opcine
FROM opcina
WHERE sifra_opcine NOT IN
( SELECT sifra_opcine
FROM djelatnik );
Ak.god. 2017/2018. BAZE PODATAKA 212
Upravljanje transakcijama
• Pod transakcijom se podrazumijeva aktivnost ili niz aktivnosti koje izvršava jedan korisnik ili aplikacijski program, a koja čita ili ažurira sadržaj baze podataka.
• To je logička radna jedinica baze podataka
• Transakcija se logički mora provesti kao nedjeljiva cjelina
svaka transakcija unosi promjenu u bazi
pojedinačne operacije unutar transakcije nisu bitne same za sebe
Ak.god. 2017/2018. BAZE PODATAKA 213
Upravljanje transakcijama
1) UPDATE racun SET saldo=saldo-500
WHERE id_racuna=1;
2) UPDATE racun SET saldo=saldo+500
WHERE id_racuna=2;
Transakcija se mora izvršiti u potpunosti (u gornjem slučaju obe
UPDATE naredbe) ili nikako – “SVE ili NIŠTA”
Račun 1 Račun 2
500 KM
5.000 KM 1.000 KM
Ak.god. 2017/2018. BAZE PODATAKA 214
Osobine transakcija
Tzv. ACID osobine transakcija:
• Atomicity – ‘sve ili ništa’ (transakcije nije moguće samo
djelomično izvršiti)
• Consistency – transakcija mora transformirati bazu iz jednog
konzistentnog stanja u drugo
• Isolation – učinak transakcije postaje vidljiv drugim transakcijama
tek nakon završetka transakcije promatrajući izvana transakcija može biti ili izvedena ili ne
• Durability – rezultat uspješno završenih (potvrđenih) transakcija
se trajno bilježi u bazu podataka i ne smije se izgubiti zbog naknadnih grešaka (čak ni u slučaju pada sustava)
Ak.god. 2017/2018. BAZE PODATAKA 215
Upravljanje transakcijama (ORACLE)
• 2 tipa transakcija:
o DDL – sadrži jedan DDL iskaz
o DML – sadrži jedan ili više DML iskaza
• Transakcija započinje kada je:
o DDL naredba izdana
o Pokrenut prvi DML iskaz nakon COMMIT-a
• Transakcija se može završiti na sljedeći način:
o COMMIT iskazom – potvrdi sve izmjene
o ROLLBACK iskazom – poništi sve izmjene
o DDL iskazom → automatski COMMIT
o Padom sustava → automatski ROLLBACK
Ak.god. 2017/2018. BAZE PODATAKA 216
Upravljanje transakcijama
Primjer
SELECT placa FROM djelatnikWHERE id_djelatnika=1;
UPDATE djelatnikSET placa=placa+1000WHERE id_djelatnika=1;
COMMIT;
t1
t2
t3
t4
t5
Vrijeme Sesija 1 Sesija 2
SELECT placa FROM djelatnikWHERE id_djelatnika=1;
SELECT placa FROM djelatnikWHERE id_djelatnika=1;
2000
2000 3000
SELECT placa FROM djelatnikWHERE id_djelatnika=1;
SELECT placa FROM djelatnikWHERE id_djelatnika=1;
3000 3000
Ak.god. 2017/2018. BAZE PODATAKA 217
Upravljanje transakcijama
Primjer
SELECT placa FROM djelatnikWHERE id_djelatnika=1;
UPDATE djelatnikSET placa=placa+1000WHERE id_djelatnika=1;
ROLLBACK;
t1
t2
t3
t4
t5
Vrijeme Sesija 1 Sesija 2
SELECT placa FROM djelatnikWHERE id_djelatnika=1;
SELECT placa FROM djelatnikWHERE id_djelatnika=1;
2000
2000 3000
SELECT placa FROM djelatnikWHERE id_djelatnika=1;
SELECT placa FROM djelatnikWHERE id_djelatnika=1;
2000 2000
Ak.god. 2017/2018. BAZE PODATAKA 218
Tehnike kontrole konkurentnosti
Konzervativne (pesimistične) tehnike
• uzrokuju odgađanje transakcija u slučaju da će biti u konfliktu s drugim transakcijama u budućnosti
-> Zaključavanje (locking)
Optimistične tehnike
• bazirane na pretpostavci da su konflikti rijetki tako da dozvoljavaju transakcijama da nastave i nesinkronizirane
• provjeravaju konflikt na kraju (kada se transakcija potvrđuje)
Ak.god. 2017/2018. BAZE PODATAKA 219
Zaključavanje (locking)
Dijeljeno zaključavanje (shared lock, read lock)
Ključ za čitanje – npr. transakcija SQL naredbom SELECT zaključa objekt za čitanje.
Bilo koja druga transakcija taj objekt može zaključati za čitanje, ali ga niti jedna transakcija ne može zaključati za pisanje.
Ekskluzivno zaključavanje (exclusive lock, write lock)
Ključ za pisanje – npr. transakcija SQL naredbom (INSERT, UPDATE ili DELETE) zaključa objekt za pisanje.
Niti jedna transakcija taj objekt ne može zaključati niti za čitanje niti za pisanje, dok ga transakcija u kojoj je izvršeno zaključavanje ne otključa.
Ak.god. 2017/2018. BAZE PODATAKA 220
Zaključavanje (locking)
Proces1 – postavio ključ na objekt
Proces2 – pokušava postaviti ključ na isti objekt kao i Proces1
Ak.god. 2017/2018. BAZE PODATAKA 221
Protokol dvofaznog zaključavanja
Two-phase locking protocol (2PL)
1) prije obavljanja operacije nad objektom (npr. n-torkom iz baze), transakcija mora za taj objekt zatražiti ključ
2) nakon otpuštanja ključa, transakcija ne smije više zatražiti nikakav ključ
Transakcije koje poštuju 2PL protokol imaju 2 faze:
1) fazu pribavljanja ključeva (faza rasta - growing phase)
2) fazu otpuštanja ključeva (fazu sužavanja - shrinking phase)-> COMMIT ili ROLLBACK na kraju transakcije
Zaključavanje (locking)
Ak.god. 2017/2018. BAZE PODATAKA 222
• Svojstvo nesmetanog konkurentnog (višekorisničkog) izvođenja transakcija naziva se serijalizabilnost.
• Serijalizabilnost (serializability) redoslijeda izvršavanja transakcija je osigurana ako sve transakcije poštuju protokol dvofaznog zaključavanja (2PL).
Zaključavanje (locking)
Ak.god. 2017/2018. BAZE PODATAKA 223
Razine izolacije (isolation levels)
• Kod zapisivanja se uvijek traži ekskluzivno zaključavanje koje se zadržava do kraja transakcije.
• Kod čitanja možemo imati različite načine zaključavanja:
o read uncommitted
čitanje bez zaključavanja;
o read committed
zahtijeva dijeljeno zaključavanje za sve zapise koji su dohvaćeni upitima;
otključava zapise odmah nakon što su pročitani;
o repeatable read
zahtijeva dijeljeno zaključavanje za sve zapise koji su dohvaćeni upitima;
podaci ostaju zaključani do kraja transakcije;
Ak.god. 2017/2018. BAZE PODATAKA 224
Granulacija zaključavanja
• Granulacija je određena veličinom objekta koji se zaključava:
o n-torka, stranica (fizički blok na kojem su zapisi pohranjeni), tablica ili baza podataka.
Ak.god. 2017/2018. BAZE PODATAKA 225
Mrtva točka (deadlock)
• Mrtva točka (deadlock) nastaje kada dvije (ili više)
transakcija, obje (sve) čekaju da se otpusti zaključavanje koje drži druga transakcija.
Ak.god. 2017/2018. BAZE PODATAKA 226
Deadlock Primjer
UPDATE djelatnikSET placa=placa-500WHERE id_djelatnika=2;
t1
t2
t3
t4
t5
Vrijeme Sesija 1 Sesija 2
UPDATE djelatnikSET placa=placa+1000WHERE id_djelatnika=1;
UPDATE djelatnikSET placa=placa-500WHERE id_djelatnika=2;
UPDATE djelatnikSET placa=placa+1000WHERE id_djelatnika=1;
→ ORA-00060: deadlock detected while waiting for resource
Ak.god. 2017/2018. BAZE PODATAKA 227
Upravljanje transakcijama i zaključavanje
• Stanje sustava prije COMMIT ili ROLLBACK naredbe:
– moguće je vratiti sustav u stanje prije izvršavanja naredbi iz transakcije
– korisnik koji radi izmjene podataka kroz transakciju može vidjeti efekte tih izmjena (npr. izvršavanjem SELECT iskaza)
– ostali korisnici ne mogu vidjeti efekte navedenih izmjena
Zaključavanje:
– n-torke nad kojima se rade izmjene kroz transakciju (INSERT, UPDATE, DELETE) automatski se zaključavaju i ostali korisnici ih ne mogu mijenjati
Napomena: Npr. u Oracle sustavu implicitno zaključavanje će se desiti za sve SQL iskaze osim SELECT-a
Ak.god. 2017/2018. BAZE PODATAKA 228
Upravljanje transakcijama i zaključavanje
• Stanje sustava nakon izvršavanja COMMIT naredbe:
– izmjene podataka u bazi postaju trajne
– sustav se više ne može vratiti u stanje prije izvršavanja naredbi iz transakcije
– svi korisnici mogu vidjeti efekte izmjena učinjenih kroz transakciju
Zaključavanje:
– n-torke nad kojima su vršene izmjene kroz transakciju nisu više zaključane i navedene n-torke postaju dostupne za izmjenu i drugim korisnicima
Ak.god. 2017/2018. BAZE PODATAKA 229
• Stanje sustava nakon izvršavanja ROLLBACK naredbe:
– izmjene podataka u bazi se poništavaju
– sustav se vraća u stanje prije izvršavanja naredbi iz transakcije
Zaključavanje:
– n-torke koje su bile implicitno zaključane kroz transakciju nisu više zaključane te postaju dostupne za izmjenu i drugim korisnicima
Upravljanje transakcijama i zaključavanje
Ak.god. 2017/2018. BAZE PODATAKA 230
Objekti u bazi podatakaTABLE, VIEW, INDEX, SEQUENCE, SYNONYM,
STORED PROCEDURE, TRIGGER
Ak.god. 2017/2018. BAZE PODATAKA 231
Objekti u bazi podataka
• TABLE – Tablica
• VIEW – Pogled
• INDEX – Indeks
• SEQUENCE – Sekvenca(Oracle)
• SYNONYM – Sinonim
• STORED PROCEDURE – Pohranjena procedura
• TRIGGER – Okidač
Ak.god. 2017/2018. BAZE PODATAKA 232
Pogled (VIEW)
Što je pogled?
o “prozor” u podatke
o podaci se izvode, ne posjeduju
o pohranjuje se kao SELECT izraz u rječnik podataka
Uporaba pogleda
o za ograničenje pristupa podacima
o za pojednostavljenje složenih upita
o za omogućavanje neovisnosti podataka
o za prikaz različitih pogleda na iste podatke
Ak.god. 2017/2018. BAZE PODATAKA 234
Pogled (VIEW)
Jednostavni pogled
o podaci su iz jedne tablice
o ne sadrži funkcije ili grupe
o može izvršiti DML
Složeni pogled
o podaci su iz više tablica
o sadrži funkcije ili grupe
o ne može izvršiti DML
Ak.god. 2017/2018. BAZE PODATAKA 235
Kreiranje pogleda (VIEW)
CREATE OR REPLACE VIEW djelatnik_imenik
AS
SELECT id_djelatnika, ime, prezime, email, telefon
FROM djelatnik;
CREATE VIEW radno_mj_stat (radmj,minplac,maxplac,prosjek)
AS
SELECT rm.naziv_radmj, min(placa), max(placa), avg(placa)
FROM djelatnik d, radno_mjesto rm
WHERE d.sifra_radmj=rm.sifra_radmj
GROUP BY rm.naziv_radmj;
Ak.god. 2017/2018. BAZE PODATAKA 236
Brisanje pogleda (VIEW)
• Pogled možete izbrisati bez ikakvog gubitka podataka jer je pogled (view) baziran na tablicama u bazi.
• Brisanje pogleda nema nikakvog utjecaja na te tablice i podatke u njima.
DROP VIEW ime_pogleda
Ak.god. 2017/2018. BAZE PODATAKA 237
Indeks (INDEX)
o Brzina pristupa podacima u relaciji je važno svojstvo sustava za upravljanje podacima. Najjednostavniji način pristupa, sekvencijalna pretraga, u većini slučajeva ne zadovoljava.
o Kreiranjem indeksa formira se struktura B-stabla koja omogućava nesekvencijalni pristup do n-torke u relaciji. Nesekvencijalni pristup moguć je prema vrijednostima onih atributa nad kojima je izgrađena indeksna struktura.
o Nad jednom relacijom može biti izgrađeno više indeksa, od kojih svaki može sadržavati jedan ili više atributa.
o Osim radi poboljšanja performansi sustava, indeksi se kreiraju i radi osiguranja jedinstvenosti vrijednosti atributa u relaciji.
Ak.god. 2017/2018. BAZE PODATAKA 238
Kreiranje indeksa (INDEX)
AUTOMATSKI
• UNIQUE INDEX se automatski kreira kada se definira PRIMARY KEY ili UNIQUE CONSTRAINT u definiciji tablice.
RUČNO
• Korisnik može ručno dodati indeks na neki drugi atribut (radi ubrzanja pristupa podacima).
Primjer:
• CREATE INDEX djelatnik_prez_idx
ON djelatnik(prezime);
Ak.god. 2017/2018. BAZE PODATAKA 239
Indeks (INDEX)
Indekse bi u principu trebalo primjenjivati u sljedećim slučajevima:
• za atribute prema kojima se obavlja spajanje relacija
• za atribute koji se često koriste za postavljanje uvjeta selekcije
• za atribute prema kojima se često obavlja grupiranje ili sortiranje
Ak.god. 2017/2018. BAZE PODATAKA 240
Indeks (INDEX)
Prilikom kreiranja indeksa treba voditi računa i o nekim njihovim negativnim aspektima, te ih treba koristiti samo tamo gdje je njihova uporaba opravdana.
• Indeksi zauzimaju značajan prostor
• Ažuriranje vrijednosti atributa nad kojima je izgrađen indeks traje znatno dulje nego ažuriranje vrijednosti nad kojima nema indeksa (ovdje treba razlikovati atribute prema kojima se pronalaze n-torke koje treba ažurirati, od atributa čije se vrijednosti ažuriraju)
Ak.god. 2017/2018. BAZE PODATAKA 241
Indeks (INDEX)
Indekse ne bi trebalo primjenjivati ukoliko:
• vrijednosti atributa za kojeg se gradi indeks imaju relativno mali broj različitih vrijednosti (npr. spol_osobe s dopuštenim vrijednostima M, Ž, u relaciji s 30 000 n-torki)
• u relaciji predstoji velik broj upisa, izmjena ili brisanja n-torki. Preporučljivo je u takvim slučajevima postojeće indekse izbrisati, te ih ponovo izgraditi tek nakon obavljenih promjena na podacima.
• relacija sadrži vrlo mali broj n-torki (npr. do stotinu). U takvim slučajevima sustav lakše pristupa sekvencijalnom pretragom, nego prolaskom kroz strukturu B-stabla.
Ak.god. 2017/2018. BAZE PODATAKA 242
Primarni ključ (PK) – Automatsko generiranje vrijednosti
• Oracle –> SEQUENCE, IDENTITY (od v.12c)
• MS SQL Server –> IDENTITY
• MS Access –> AUTONUMBER
• MySQL –> AUTO_INCREMENT
• PostgreSQL –> SERIAL, BIGSERIAL
• ...
Ak.god. 2017/2018. BAZE PODATAKA 243
Sekvenca (SEQUENCE)
Primjer:
Sekvenca (Oracle)
o automatski generira jedinstveni broj
o najčešće se koristi za dodijeljivanje vrijednosti PK (PRIMARNOM KLJUČU)
o sekvenci može pristupati više korisnika
Ak.god. 2017/2018. BAZE PODATAKA 244
Sekvenca (SEQUENCE)
• CREATE SEQUENCE odjel_sifra_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 9999
NOCACHE
NOCYCLE;
Ak.god. 2017/2018. BAZE PODATAKA 245
Sekvenca (SEQUENCE)
• INSERT INTO odjel (sifra_odjela, naziv_odjela)
VALUES (odjel_sifra_seq.NEXTVAL, 'Strategija i razvoj');
• SELECT odjel_sifra_seq.CURRVAL
FROM dual;
Ak.god. 2017/2018. BAZE PODATAKA 246
Sinonim (SYNONYM)
Sinonim (Synonym) je drugi naziv za objekt
koristan kod referenciranja na objekte drugih korisnika.
Primjer:
CREATE [PUBLIC] SYNONYM ime_sinonima
FOR [vlasnik.]ime_objekta;
CREATE SYNONYM djelatnik
FOR fsr.djelatnik;
Ak.god. 2017/2018. BAZE PODATAKA 247
Pohranjena procedura (STORED PROCEDURE)
• Pohranjena procedura ili pohranjena funkcija je
potprogram koji je pohranjen u rječniku podataka i koji se izvršava u kontekstu sustava za upravljanje bazama podataka.
• Može se promatrati kao procedura ili funkcija kojom se proširuje skup SQL funkcija ugrađenih u SUBP.
o Pohranjena procedura je potprogram koji u pozivajući
program ne vraća rezultat.
o Funkcija je potprogram koji u pozivajući program vraća
rezultat.
Ak.god. 2017/2018. BAZE PODATAKA 248
• Proizvođači SUBP koriste vlastite inačice jezika za definiranjepohranjenih procedura (standard postoji, ali je rijetko gdje implementiran)
o Oracle: PL/SQL PL/SQL (Procedural Language / Structured Query Language)
o Microsoft SQL Server: T-SQLT-SQL (Transact-SQL)
• Navedeni jezici proširuju mogućnosti SQL jezika proceduralnim elementima koji se koriste u strukturiranim jezicima (C, Java, ...).
Osim SQL naredbi, pohranjene procedure omogućuju korištenje:
o varijabli
o naredbi za kontrolu toka programa (if, for, while, ...)
o naredbi za rukovanje iznimkama (exception handling)
Pohranjena procedura (STORED PROCEDURE)
Ak.god. 2017/2018. BAZE PODATAKA 249
• Upotrebom pohranjenih procedura omogućena je zaštita podataka na razini funkcije (a ne samo objekta).
• Osnovna sintaksa za dodjeljivanje dozvole za izvršavanje procedure:
GRANT EXECUTE
ON { ime_procedure | ime_funkcije }TO { korisnici | uloge | PUBLIC }[ WITH GRANT OPTION ]
• Osnovna sintaksa za ukidanje dozvole za izvršavanje procedure:
REVOKE EXECUTE
ON { ime_procedure | ime_funkcije }FROM { korisnici | uloge | PUBLIC }[ CASCADE | RESTRICT ]
Pohranjena procedura (STORED PROCEDURE)
Ak.god. 2017/2018. BAZE PODATAKA 250
• Upotrebom pohranjenih procedura omogućena je upotreba klijent-server arhitekture oslonjene na server.
o postiže se veća učinkovitost (efikasnost) SUBP
(SUBP ne mora ponavljati prevođenje i optimiranje SQL upita)
o postiže se veća produktivnost programera i smanjuje se mogućnost pogreške
(Programski kôd potreban za obavljanje nekog postupka koji čini logičku cjelinu implementira se i testira na samo jednom mjestu)
Pohranjena procedura (STORED PROCEDURE)
Ak.god. 2017/2018. BAZE PODATAKA 251
Primjer (klijent-server arhitektura oslonjena na klijenta)
1) SELECT COUNT (*) FROM racun
WHERE id_racuna=1;
2) SELECT COUNT (*) FROM racun
WHERE id_racuna=2;
3) UPDATE racun SET saldo=saldo-500
WHERE id_racuna=1;
4) UPDATE racun SET saldo=saldo+500
WHERE id_racuna=2;
Primjer: Prebacivanje iznosa s jednog računa na drugiPrvo se provjerava postoje li zadani brojevi računa i ako postoje, prebacuje se iznos (od 500 KM) s jednog računa na drugi.
Ak.god. 2017/2018. BAZE PODATAKA 252
Primjer (klijent-server arhitektura oslonjena na server)
EXECUTE PROCEDURE
prebaci (1, 2, 500);
CREATE PROCEDURE prebaci (...)
DEFINE ...
SELECT ...
SELECT ...
UPDATE ...
UPDATE ...
END PROCEDURE;
Primjer: Prebacivanje iznosa s jednog računa na drugiPrvo se provjerava postoje li zadani brojevi računa i ako postoje, prebacuje se iznos (od 500 KM) s jednog računa na drugi (rješenje s korištenjem pohranjene procedure).
Ak.god. 2017/2018. BAZE PODATAKA 253
Primjer (klijent-server arhitektura oslonjena na klijent / server)
Ak.god. 2017/2018. BAZE PODATAKA 254
Okidač (TRIGGER)
“Pasivni” SUBP
• konvencionalni SUBP je pasivan
• operacije nad podacima se izvršavaju isključivo na temelju eksplicitnog zahtjeva korisnika / aplikacije
“Aktivni” SUBP i “aktivne” baze podataka
• aktivni SUBP autonomno reagira na određene događaje (events)
• u aktivnim bazama podataka neke operacije nad podacima se izvršavaju automatski, reakcijom na određeni događaj ili stanje
Ak.god. 2017/2018. BAZE PODATAKA 255
• SUBP – definiranje aktivnih pravila (active rules)
• Događaj-Uvjet-Akcija (ECA: Event-Condition-Action)
→ Okidači (triggers)
o događaj (event): ako se dogodi, izračunava se uvjet(npr. unos - INSERT, izmjena - UPDATE ili brisanje - DELETE podataka)
o uvjet (condition): ako je rezultat izračunavanja uvjeta istina, obavljaju se akcije
o akcije (action): niz operacija, najčešće operacije nad podacima
ON event IF condition THEN action
Okidač (TRIGGER)
Ak.god. 2017/2018. BAZE PODATAKA 256
• Okidači (trigeri) se izvršavaju automatski kod izvršavanja akcijskih SQL upita (INSERT, UPDATE, DELETE) nad pojedinim tablicama u bazi podataka.
Okidač (TRIGGER)
Ak.god. 2017/2018. BAZE PODATAKA 257
• Pri definiciji okidača moguće je specificirati koje akcije (operacije) aktiviraju okidač (triger):
INSERT, UPDATE, DELETE
• Također, pri definiciji okidača moguće je specificirati da li se akcijenavedene u samom okidaču obavljaju:
o nakon što se obavi operacija koja je aktivirala okidač
AFTER INSERT, AFTER UPDATE, AFTER DELETE
o prije nego se obavi operacija koja je aktivirala okidač
BEFORE INSERT, BEFORE UPDATE, BEFORE DELETE
o te da li se akcije u okidaču obavljaju jednom za svaku n-torku na koju je djelovala operacija koja je aktivirala okidač
FOR EACH ROW
Okidač (TRIGGER)
Ak.god. 2017/2018. BAZE PODATAKA 258
• Primjeri primjene okidača (trigera):
o evidentiranje (logiranje) promjena nad podacima,
o implementacija integritetskih ograničenja,
o ažuriranje izvedenih atributa (npr. saldo računa),
o praćenje rada korisnika (logiranje pristupa bazi ...),
o sustavi obavještavanja,
o itd.
Okidač (TRIGGER)
Ak.god. 2017/2018. BAZE PODATAKA 260
Web:
http://www.uni-mo.ba/~goran
Pitanja, primjedbe, dogovor za konzultacije ...
o E-mail: [email protected]
Baze podataka