Seminarski rad - BorkaPavlovic - Rdbms (4. godina, case alati).pdf

PANEVROPSKI UNIVERZITET APEIRON

FAKULTET POSLOVNE INFORMATIKE Vanredne studije Smer „Poslovna informatika informatika ”

Predmet RDBMS (SQL administracija i CASE alati)

„IZVOD IZ MATIČNE KNJIGE ROĐENIH” (seminarski rad)

Predmetni nastavnik Prof. dr Zoran Ž. Avramović, dipl.inž.elek.

Student

Borka Pavlović nastavnik informatike

Index br. 184-13/VPI-S

Banja Luka, januar 2014.

Izvo iz Matične knjige rođenih - seminarski rad - Borka Pavlovid

UVOD .......................................................... ........................................................................................................................... ............................................................................................3 ...........................3 1.

2.

BAZE PODATAKA ..................................................... ........................................................................................................................ .......................................................................4 ....4 2.1.

Pojam Baze podataka ............................................................. .............................................................................................................4 ................................................4

2.2.

RDBMS ............................................................. .................................................................................................................................. .......................................................................4 ..4

2.3.

.............................................................4 ....4 Fizički, konceptualni i korisnički nivo baze poataka .........................................................

MODELI ENTITETA I POVEZNIKA ............................................................... .....................................................................................................5 ......................................5 3.1.

Entitet / skup klasa entiteta / tip entiteta................................................................ ..............................................................................5 ..............5

3.2.

Poveznik .......................................................... ............................................................................................................................. .......................................................................5 ....5

3.3.

Kardinalitet ...................................................... ......................................................................................................................... .......................................................................7 ....7

Obilježja (atributi) ....................................................... .......................................................................................................................... .......................................................................8 ....8 3.4.

Domen ............................................................. .................................................................................................................................. .......................................................................9 ..9

3.5.

Ključ ................................................................... ...................................................................................................................................... .....................................................................9 ..9

3.6.

Integriteti domena, entiteta i referencijalni integriteti .........................................................9

3.7.

Nula vrijednosti ........................................................... ................................................................................................................... ........................................................ 10

3.

NORMALNE FORME............................................................. ..................................................................................................................... ........................................................ 10

4.

SQL – RELACIONI UPITNI JEZIK .................................................................................................... .................................................................................................... 11

5.

5.1.

Komande za definisanje podataka -DDL........................................................ .............................................................................. ...................... 11

5.2.

Komande za manipulaciju (ažuriranje) poataka DML ...................................................... ........................................................ 15

5.3.

Naredbe za upite ......................................................... ................................................................................................................. ........................................................ 17

RELACIONA ALGEBRA .......................................................... .................................................................................................................. ........................................................ 20 6.1.

Restrikcija ........................................................ ........................................................................................................................... ..................................................................... 20

6.2.

Projekcija ......................................................... ............................................................................................................................ ..................................................................... 20

6.3.

Unija .................................................................. .................................................................................................................................... .................................................................. 21

6.4.

Spajanje ........................................................... .............................................................................................................................. ..................................................................... 21

6.

UPITI .............................................................. ................................................................................................................................. .............................................................................. ........... 23

7.

INDEKSI .......................................................... ............................................................................................................................. .............................................................................. ........... 26

8.

Pogled ............................................................ ............................................................................................................................... .............................................................................. ........... 27 1


UVOD .......................................................... ........................................................................................................................... ............................................................................................3 ...........................3 1.

2.

BAZE PODATAKA ..................................................... ........................................................................................................................ .......................................................................4 ....4 2.1.

Pojam Baze podataka ............................................................. .............................................................................................................4 ................................................4

2.2.

RDBMS ............................................................. .................................................................................................................................. .......................................................................4 ..4

2.3.

.............................................................4 ....4 Fizički, konceptualni i korisnički nivo baze poataka .........................................................

MODELI ENTITETA I POVEZNIKA ............................................................... .....................................................................................................5 ......................................5 3.1.

Entitet / skup klasa entiteta / tip entiteta................................................................ ..............................................................................5 ..............5

3.2.

Poveznik .......................................................... ............................................................................................................................. .......................................................................5 ....5

3.3.

Kardinalitet ...................................................... ......................................................................................................................... .......................................................................7 ....7

Obilježja (atributi) ....................................................... .......................................................................................................................... .......................................................................8 ....8 3.4.

Domen ............................................................. .................................................................................................................................. .......................................................................9 ..9

3.5.

Ključ ................................................................... ...................................................................................................................................... .....................................................................9 ..9

3.6.

Integriteti domena, entiteta i referencijalni integriteti .........................................................9

3.7.

Nula vrijednosti ........................................................... ................................................................................................................... ........................................................ 10

3.

NORMALNE FORME............................................................. ..................................................................................................................... ........................................................ 10

4.

SQL – RELACIONI UPITNI JEZIK .................................................................................................... .................................................................................................... 11

5.

5.1.

Komande za definisanje podataka -DDL........................................................ .............................................................................. ...................... 11

5.2.

Komande za manipulaciju (ažuriranje) poataka DML ...................................................... ........................................................ 15

5.3.

Naredbe za upite ......................................................... ................................................................................................................. ........................................................ 17

RELACIONA ALGEBRA .......................................................... .................................................................................................................. ........................................................ 20 6.1.

Restrikcija ........................................................ ........................................................................................................................... ..................................................................... 20

6.2.

Projekcija ......................................................... ............................................................................................................................ ..................................................................... 20

6.3.

Unija .................................................................. .................................................................................................................................... .................................................................. 21

6.4.

Spajanje ........................................................... .............................................................................................................................. ..................................................................... 21

6.

UPITI .............................................................. ................................................................................................................................. .............................................................................. ........... 23

7.

INDEKSI .......................................................... ............................................................................................................................. .............................................................................. ........... 26

8.

Pogled ............................................................ ............................................................................................................................... .............................................................................. ........... 27 1

Izvo iz Matične knjige rođenih - seminarski rad - Borka Pavlovid 9.

FUNKCIJE ................................................................... ..................................................................................................................................... .................................................................. 29

10.

PROCEDURE ......................................................... ............................................................................................................................ ..................................................................... 31

11.

TRIGERI .................................................................. .................................................................................................................................... .................................................................. 33

12.

CASE ALATI .......................................................... ............................................................................................................................. ..................................................................... 36

2


UVOD Predmet seminarskog rada

Predmet seminarskog rada je Baza podataka koja omogućava radnicima u opštinskoj šalter sali evidenciju i izdavanje podataka iz Matiĉne knjige roĊenih. PredviĊeno je da baza

podataka sadrţi podatke vezane za jednu širu regiju (drţavu) a ne samo za jednu opštinu. U bazi podataka nalazi se više tabela, od kojih se u jednoj nalazi većina matiĉnih podataka, a ostale tabele predstavljaju šifarnike sa podacima o opštini, mjestima, drţavljanstvu, nacionalnosti, vjeroispovjesti, matiĉarima, bilješkama i šiframa bilješki Cilj rada je: - Modelirati bazu podataka na temu Matiĉna knjiga roĊenih - Uspostaviti integritete (na nivou entiteta, domena i referencijalnosti - Prikazati ER dijagram - Prikazati korištenje SQL upita: DML i DDL naredbe - Prikazati korištenje osnovnih funkcija, pogleda, uskladištenih proce dura, trigera - Prikazati uspostavljanje indeksa - Prikazati korištenje integrisane bezbijednosti u okviru RDMBS -a - Koristiti neki od CASE alata Proces projektovanja Baze obuhvata niz koraka, poĉevši od strukturne sistemske analize, preko formiranja tabela do upotrebe SQL programskog jezika za kreiranje objekata i izvršavanje nekih jednostavnijih upita nad ovom bazom podataka.

Baza podataka je raĊena na platformi MS SQL Server 2005.

3


1. BAZE PODATAKA 2.1.Pojam Baze podataka

Baza podataka je elektronsko skladiš te podataka zapisanih na mediju za masovnu pohranu, pri ĉemu su podaci tako organizovani da se lako i brzo pronaĊu i to u onom obimu koji zadovoljava postavljeni kriterij, te se predstaviti u pogodnom obliku. Pod pojmom podatak, podrazumjevamo skup zapisa koji se stvarno nalaze u bazi podataka a pod informacijom podrazumjevamo saznanje koje korisnik dobiva kao rezultat upita nad bazom

podataka, koje se mogu koristiti za stvaranje odluka koje bi inaĉe mogle biti mnogo teţe ili nemoguće za stvaranje. Spremanjem, pronalaţenjem i sortiranjem podataka upravlja poseban programski s istav: DBMS ( database management system).

Prema stepenu usluge u smislu spremanja, odrţavanja, ĉitanja I pretraţivanja podataka koji nudi program, te prema naĉinu organizacije podataka, baze moţemo podijeliti na datotetske, hijerarhijske, mreţne, relacijske i objektne. 2.2.RDBMS Programski sistem za upravljanje relacijskom bazom podataka RDBMS ( Relational database management system) nastao je kao implementacija pravila relacijskog modela podataka, koje je definirao Codd 1971. godine. Relacijski model je skup pravila koji mora biti zadovoljen kod projektovanja baze podataka. Isto tako, Codd je defisao operacije nad relacijskim modelom, koje kasnije implementira upitni jezik SQL ( Structured Query

Language). Uspjeh relacijskog modela leţi u ĉinjenici njegove utemeljenosti na dva dobro definirana podruĉja matematike: teoriji skupova i matematiĉke logike. Naravno, tijekom vremena, kako je raslo iskustvo i uoĉavane potebe, RDBMS je evoluirao, t e danas postoje na trţištu programska rješenja koja omogućavaju pridrţavanje svih pravila relacijskog modela, a da istovremeno odzivna vremena budu zadovoljavajuća. 2.3.Fizički, konceptualni i korisnički nivo baze podataka Preko procesa projektovanja baze podataka, njene implemantacije, testiranja i uopšte njenog “ţivota”, prolazi mnogo ljudi koji na razliĉite naĉine dolaze u dodir sa bazom podataka, te imaju razliĉitu ulogu u procesima nad bazom. Iz te razlliĉitosti, izdvajaju se tri nivoa rada nad bazom podataka. Konceptualni nivo (logiĉki) baze koriste projektanti i administrator baze podataka. Ovaj

nivo opisuje kako će podaci biti smješteni u baze. To je logiĉki nivo koji koristi skup struktura nekog informacionog sistema. Ovaj nivo se koristi u fazi planiranja i sistemske analize. Projektanti baze podataka ne moraju da znaju za organizaciju podataka na disku koja

pripada fiziĉkom nivou baze podataka

Fizički nivo baze podataka predstavlja fiziĉku strukturu podataka, koja se dobje samim smještanjem baze na memorijski ureĊaj. Fiziĉki mod el zavisi od tipova objekata u bazi podataka (duţina vrijednosti obiljeţja, postupci za memorisanje informacija o vezama objekata, postupci dodjele adrese lokacije na memorijskom ureĊaju…) i odreĊuju organizaciju datoteka na disku. Ovaj dio posla odraĊuju sistemski programeri i proizvoĊaĉi SUBP. (svi objekti koje baza sadrţi,view,trigers, kako će to biti kreirani na bazi ikako će biti realizovani) 4


Korisnički nivo apstrakcije pripada krajnjim korisnicima baze. U okviru jedne kompleksne baze postoji više skupova str uktuiranih podataka koje koriste razliĉiti korisnici. (npr. samo obraĉun plata u okviru nekog sistema; voĊenje evidencije skladišta sistema, nabavka i sl.). Takvi moduli se nazivaju pogledi, te se ovaj nivo ĉesto na ziva i nivo pogleda.

2. MODELI ENTITETA I POVEZNIKA ada se strukturno posmatara neki sistem nad kojim se pravi baza, potrebno je K detaljno poznavanje tog realnog sistema. U procesu upoznavanja sistema treba obezbijediti informacije o parametrima kao što su: struktura sistema, funkcije, ciljevi poslovanja, pravila poslovanja, uoĉavanje objekata i veza izmeĊu njih. Taj dio upoznavanja se obraĊuje tekstualno, a onda se pristupa izdvajanju objekata realnog sistema i uspostavljanje veza i

ograniĉenja meĊu njima. Grafiĉki prikaz objekata (entiteta) i obiljeţja koja pripadaju entitetima te veza meĊu entitetima i prirode tih veza naziva se E-R dij agr am . Postoje neka heuristiĉka upustva za prevoĊenje tekstualnog opisa u E -R dijagram 3.1.Entitet / skup klasa entiteta / tip entiteta U tekstualnom dijelu opisa realnog sistema, imenice ukazuju na potrebu uvoĊenja entiteta. Entitet 1) predstavlja nešto što se moţe jednoznaĉno identifikovati. Entitet se moţe opisati i kao jedinica posmatranja, i moţe se odnositi na svaki r ealni subjekat, objekat, dogaĊaj ili pojavu. Više entiteta ĉiji opis pripada istom skupu obiljeţja predstavlja klasu entiteta. Npr. entiteti u mom seminarskom radu iz klase entiteta su ljudi koji imaju svoje ime, prezime, datum roĊenja, drţavljanstvo, naci onalnos i sl. Nisu sva obiljeţja nekog

entiteta jednako vaţna za realizaciju zadatka informacionog sistema, te se uzimaju samo ona bitna obiljeţja od kojih se gradi model realne klase entiteta i taj model se naziva tipom entiteta. Npr. za informacioni sistem koji obezbjeĊuje izvod iz matiĉne knjige roĊenih, obiljeţje koje opisuje da li neki graĊanin ima plavu, smeĊu ili crnu kosu je sasvim nebitan i naravno da ga izbacujemo iz tipa entiteta. Prilikom crtanja ER dijagrama, tip entiteta stavljamo u pravougaonik u koji upisujemo ime entiteta.

MATIĈAR

BILJEŠKE

sl.1. prikaz entiteta u ER diagramu

3.2.Poveznik

ukazuju na potrebu uvoĊenja tipova poveznika. Poveznik predstavlja vezu izmeĊu dva ili više entiteta. Ta veza je konstituisana povezanim entitetma i opisom njihove veze. Npr. ako posmatramo entitete tipa bilješki i Glagolski oblici tekstualnog dijela opisa,

1

ens, entis (lat.)- bide, bitnost

5

Izvo iz Matične knjige rođenih - seminarski rad - Borka Pavlovid entitete tipa matiĉar, moţemo uoĉiti vezu da matiĉar obavlja matiĉne promjene i upisuje ih kao bilješke. Tipovi poveznika predstavlja relaciju izmeĊu dva ili više entiteta. Tipovi

poveznika u realnom sistemu, ĉesto nisu tek tako jednostavno uoĉljivi. Nekada meĊu entitetima moţe postojati više tipova poveznika koji ih povezuju, tada jedan ili više njih prelazi u tip entiteta i tretira se kao entitet a naziva se gerund.

TakoĊe, izmeĊu dva ista skupa entiteta moţe egzistirati više skupova poveznika, tako da entiteti jednog skupa imaju razliĉitu ulogu u posmatra nom skupu poveznika. Npr. jedno mjesto u skupu entiteta ”mjesto” moţe imati u jedno m sluĉaju ulogu mjesta roĊenja, a u drugom ulogu mjesta matiĉne knjige. Veza moţe biti i takva da je entitet sam sa sobom u vezi, npr. ako je neki dio proizvoda sastavljen od nekih drugih dijelova, tada jedan dio ima ulogu proizvoda, a drugi komponente koja se u taj proizvod ugraĊuje. To je rekurzivna veza. Poveznici se u E-R dijagramu predstavljaju kao romb u kojeg se upisuje ime veze.

MATIČAR

BILJEŠKE

UPISUJE

U ovom seminarskom radu, izdvojila sam devet entiteta i napravila sljedeću vezu izmeĊu njih. opština Šifra

pripa

pripa

Matični poaci

upisu

ržavljanstvo

rž.oso

matičar

Mj.ro

mjesto

Mj

bilješka

nacionaln

Nac.os

Vjer.os

vjeroispovjest

obe

sl.3. E-R dijagram 6


3.3.Kardinalitet Svaka veza ima svoju prirodu od nosa izmeĊu entiteta , koja se naziva kardinalitet. Kardinalitet je definisan brojem veza pojedinog elementa skupa entiteta sa elementima skupa entiteta s kojim je relacijski povezan. Kardinalitetom se obezbjeĊuju

ograniĉenja i specifiĉnost veza za svaki informacioni sistem. Te veze mogu biti razliĉite i obiljeţavaju kakva ograniĉenja ima realni sistem, u smislu minimalnih i maksimalnih preslikavanja. Kardinalnost se ustvari odnosi na brojnost elemenata partitativnog skupa u koji se preslikava jedan element skupa originala. U dijagramima entiteta i poveznika, kardinalitet tipa poveznika se predstavlja kao par (x,x1), (y,y1) gdje x pretstavlja broj minimalnih mogućih relacija jednog elementa skupa entiteta Ei u skup entiteta Ej, a x1 predstavlja maksimalan broj mogućih relacija jednog elementa skupa Ei u skup Ej. Par (y,y1) predstavlja takoĊe minimalni i maksimalni broj preslikavanja iz skupa Ej u skup Ei. Kardinalitet se moţe kraće pre dstaviti parom samo maksimalnih vrijednosti tj. (x1,z1). Npr.

jedan ĉovjek iz skupa entiteta “matiĉni podaci”, moţe da ima ni jednu ili više matiĉnih promjena (promjena prezimena, imena, razvod braka, smrt) (0,N) - jedna bilješka pod jednim rednim brojem moţe biti vezana za samo jedan matiĉni podatak (1,1) - jedna šifra bilješke moţe biti ni u jednoj od slogova tabele bilješki, a moţe biti u više (0,N), - jedan ĉovjek moţe da ima samo jedno mjesto roĊenja, a u jednom mjestu moţe da bude nijedan roĊen ĉovjek a , maksimalno N iz skupa prirodnih brojeva (1,1) -

,(0,N)

jedan ĉovjek moţe da ima a nemora da ima samo jednu vjeroispovjest, ali jednu vjeropispovjest moţe da nema niti jedan ĉovjek a najviše N ljudi (1,0),(0,N) - jedan matiĉar moţe da ne upiše ni jednu bilješku a moţe ih upisati više, dok jedna bilješka moţe biti upisana od strane samo jednog matiĉara -

MATIČAR

(1,1)

UPISUJE

(0,N)

BILJEŠKE

sl.4. prikaz kardinaliteta u E-R dijagramimu Postoji par odnosa kardinaliteta i

njihovih sluĉaja.

o Odnos jedan prema jedan (1:1), obezbjeĊuje da nekom ent itetu iz skupa entiteta Ei moţe odgovarati jedan entitet iz skupa entiteta Ej, i obratno. Postoje tri sluĉaja takve

veze, (predstaviću ih samo primjera radi, jer one nisu takve u ovom seminarskom radu): - odnos (0,1), (0,1), predstavljao bi ovakvu sliku: d a jedan ĉovjek iz tipa entiteta matiĉnih podataka moţe da nema nacionalnost ili maksim alno jednu naconalnu pripadnost, a jedna nacionalnost moţe da nepostoji ni u jednom slogu tipa entiteta matiĉni podaci ili da postoji u maks imalno jednom slogu. - odnos (1,1),(0,1), predstavljao bi situaciju: da jedan ĉovjek moţe da nema nacionalnost ili da ima maksimalno jednu, ali da jedna nacionalnost mora da se preslika na jednog ĉovjeka (ovaj primjer je totalno nerealan u ovom seminarskom, ali je tu naveden samo radi primjera, mada bi bolji primjer bio

odnos meĊu entitetima radnika i radnih mjesta) 7

Izvo iz Matične knjige rođenih - seminarski rad - Borka Pavlovid -

odnos (1,1),(1,1), predstavljao bi sledeći odnos: da jedna kat.

Ĉovjek moţe imati samo jednu nacionalnost i jedna nacionalnost moţe pripadati samo jednom ĉovjeku (takoĊe nerealno, ali navedeno primjera radi)

o Odnos jedan prema mnogo (N:1), je odnos kod koga nekom entitetu iz skupa entiteta Ei

moţe odgovarati više entiteta iz skupa Ej, ali nekom entitetu iz skupa Ej moţe odgovarati jedan entitet iz skupa Ei. I ovaj odnos ima svoje sluĉajeve:

a) (0,1),(0,N) b) (0,1),(1,N) c) (1,1),(0,N) d) (1,1),(1,N) o Odnos mnogo prema jedan (1:N) je inverzan sluĉaju 2. o Odnosu mnogo prema mnogo (N,M) odgovara sluĉaj kada nekom entitetu iz moţe odgovarati više entiteta iz skupa Ej, i obrnuto.

skupa Ei

opština

Šifra

1,1

1,1

pripa

pripada

0,N

Matični 0,N

0,

0,

0,

N

N

N

1,N 1,1

bilješka

mjesto

Mj .mk,

1,1

mj.rođ 0,N

upisu

ržavljanstvo

1,1

rž.osobe

1,1 0,1

matičar

Nac.osobe

nacionalnost

0,N

0,1

Mj.r

Vjer.osobe

vjeroispovjest

sl.5. kardinalitet u E-R dijagramu

Obilježja (atributi) Svi entiteti jedne klase posjeduju bar jednu zajedniĉku osobinu, na osnovu koje su svrstani u istu klasu. Ove osobine nazivaju se obiljeţjima ili atributima . Npr. atributi jednog matiĉnog lista bi bili: BROJ_ MATIĈNOG _LISTA, IME, PREZIME, POL, DATUM_ ROĐENJA, MJESTO_ ROĐENJA, OPŠTINA_ ROĐENJA, IME_OCA, IME_MAJKE, DJEVOJAĈKO_IME_ MAJKE, NAC IONALNOST, VJEROISPOVJEST, PREBIVALIŠTE 8


Atributi nekog drugog entiteta su drugaĉiji i predstavljaju neke osobine nekog tipa entiteta . 3.4.Domen Svakom atributu odgovara jedan skup mogućih vrijednosti, koje to obiljeţje u konkretnim sluĉajevima moţe imati. Taj skup se naziva domen obiljeţja. Npr. domen obiljeţja NAZIV NACIONALNOSTI je skup kome pripadaju elementi { srpska, hrvatska, islamska, ukrajinska, poljska, taljanska }.

3.5. Ključ

Vrijednost kljuĉa sluţi za jednoznaĉnu identifikaciju pojedinog entiteta i z skupa entiteta, vrijednost kljuĉa se pri aţuriranju ne moţe modifikovati. Kljuĉ, niti bilo koja komponenta kljuĉa ne smije imati nepoznatu vrijednost u pojavi nad šemom relacije . Skup obiljeţja postaje kljuĉ ako vaţe sledeća dva uslova: o jedinstvenost vr ijednosti kljuĉa u pojavi šeme relacije , u smislu da se svakoj n-torci2 (slogu, pojedinom entitetu) relacije dodjeljuje druga vrijednost kljuĉa i da se putem vrijednosti kljuĉa moţe izvršiti jednoznaĉna identifikacija n- torke. o minimalnost skupa obiljeţja kl juĉa Taj identifikator mora imati kardinalitet (1,1), tj. mora imati vrijednost za svaki element

entiteta, ali ne mogu postojati dva elementa sa istom vrijednošću tog identifikatora. Takav jedinstveni identifikator za svaki element entiteta naziva se Primarni ključ (Primary key). Primarni kljuĉ ĉine jedan ili više atributa. Sekundarni kl juč je bilo koji skup atributa koji ispunjavaju uslove za primarni kljuĉ ali nije izabran za primarni. Strani ključ je onaj koji je primarni u nekoj tabeli i atribut je druge tabele.

3.6.Integriteti domena, entiteta i referencijalni integriteti I ntegr itet domena obezbjeĊuje

da se pri unosu nekih podataka za neki atribut ne moţe unijeti vrijednost koja nije u skladu sa pravilima domena . Prema tome, kaţe se da je standardno ograniĉenje domena trojka koja predstavlja tip podatka, duţinu podatka i uslov. Miješanje više tipova podataka unutar jednog domena nije dopušteno. Tipovi podatka sa duţinom : o INTEGER(4) – cijeli broj sa 4 cifre o REAL(2,3) – realni broj sa dve cifre ispred zareza i tri decimale o CHARACTER(25) – znakovni tip sa duţinom od 25 znakova o LOGICAL o DATE

U praksi ĉesto nisu dovoljna samo ograniĉenja tipa podatka i duţine podatka, te se uvodi i uslov koji moţe biti jednostavan i sloţen. Primjer uslova bi bio da se domenu ocjena ne doz voli upis ocjene koja je manja od 5 i veća od 10 . (integer, (2), >5 AND <11) . Npr. Kao JMBG se ne moţe unijeti niz slova, broj koji je veći od 13 cifara, ili ne moţe se prihvatiti decimalni zapis broja, takoĊe postoji i provj era po modulu 11 zadnja cifra je kontrolna).

2

n-torka – jedan entitet iz skupa entiteta koji ima n atributa, neki autori n-norku nazivaju slogom tabele

9


predstavlja eksplicitni uslov integriteta. Vezan je za pojam I ntegr itet enti teta

kljuĉeva, i ima dvije komponente: - jedinstvenost kljuĉa -zahtjev da niti kompletan kljuĉ, niti bilo koje obiljeţje kljuĉa ne smije imati nepoznatu vrijednost (nula vrijednost). Referencij aln i in tegri tet se još naziva i

zavisnost sadrţaja. Definisanjem zavisnosti sadrţaja, uspostavlja se egzi stencijalno ograniĉenje u smislu da se u neku relaciju ne moţe upisati n -torka, ako nije upisana u relaciju koja je njen podskup. Npr. ako se neka vrijednost za šifru bilješke pojavljuje u tabeli „ biljeska“, tada mor a da postoji i u tabeli „sifra_biljeske“. Znaĉi, sve vrijednosti u tabelama moraju da budu usaglašene i ne smije se desiti da se prilikom nekog upita dobije nerealna slika s istema.

3.7.Nula vrijednosti

U realnim bazama podataka se ĉesto javlja potreba za upisom nove n -torke u neku relaciju, mada su vrijednosti nekih obiljeţja za tu n -torku nepoznate. Da bi se prevazišao problem oko definicije relacije gdje je relacija skup nekih vrijednosti, gdje se svakom obiljeţju iz relacije pridruţuje neka vrijednost iz domena, uvodi se pojam nula vrijednosti. To nije nula kao broj, nego su to kategorije kada je vrijednost:

o postojeća ali nepoznata o nepostojeća vrijednost o neinformativna nula (ne zna se da li vrijednost uopšte postoji)

UvoĊenje nula vrijednosti u bazu podataka, dovodi do toga da se uvode i nova ograniĉenja. Najjednostavnije od tih ograniĉenja je da se dozvoli ili ne dozvoli upis nul a vrijednosti u kolone pojedinih obiljeţja.

3. NORMALNE FORME okom strukturne sistemske analize realnog sisitema, nije moguće odmah T sagledati sve objekte, veze i domene. Cilj projektanta i programera baze podataka je stvoriti

bazu koju će biti što jednostavnije odrţavati, koja će imati konzistentnost i stabilnu strukturu podataka. Nestabilna struktura podataka dovodi do anomalija baze prilikom izmjene podataka. Postoji pet stepena normalne forme, i svaki viši stepen, u sebe ukljuĉuje prethodne. Prva normalna forma (1NF) Kroz pojmove entiteta, atributa i domena vidjeli smo da jedan entitet ima više atributa, a atribut skup domena. Uslovi za prvu normalnu formu : - za svaki tip entiteta treba da postoji jedan primarni kljuĉ - svako polje unutar jednog entiteta ima jednoznaĉno ime koje se ne ponavlja Druga normalna forma (2NF)

Druga normalna forma dolazi do izraţaja kod primarnih kljuĉeva sastavljenih od dva ili više polja. Uslovi druge normalne forme: - ispunjeni uslovi 1NF - svako polje unutar jednog entiteta koj e nije dio potpuno ovisi o cijelom kljuĉu.

primarnog kljuĉa, funkcionalno 10


Relacija je u 2NF ako: u tabeli postoji sloţeni kljuĉ , i svi nekljuĉni atributi su zavisni o cijelom kljuĉu, ne samo o dijelu kljuĉa, a oni atributi koji zavise od dijela kljuĉa mora ju se izdvojiti u posebnu tabelu. Treća normalna forma(3NF) Kaţemo da entitet zadovoljava treću normalnu formu ako: - je u drugoj normalnoj formi - ako ne postoji tranzitivna ovisnost nijednog polja o

nijednom kljuĉu

DIZAJNIRANJE BAZE PODATAKA ZA IZVOD IZ MATIČNE KNJIGE ROĐENIH U OKVIRU SQL-a

4. SQL – RELACIONI UPITNI JEZIK SQL (Structured Query Language) predstavlja kompletan jezik podataka, i

sluţi za kreiranje, organizaciju i manipulaciju podacima baze. U terminologi SQL umjesto pojma relacije, koristi se po jam tabela. SQL podrţava tri osnovne funkcije baza podataka i to: 1. DDL (Data Definition Language): dio SQL jezika koji se koristi za definisanje strukture baze podataka: – tabele, atributi, tipovi atributa, ograniĉenja, relacije 2. DML (Data Manipulation Language) Manipulacija bazom podataka: pored upita nad

bazom, kojima dobijamo ţeljene informacije, neophodno je obezbijediti i aţuriranje baza; unos, izmjenu i brisanje podataka.

5.1.Komande za definisanje podataka -DDL CREATE TABLE – kreira zaglavlje nove tabele

Komanda CREATE TABLE sluţi za definisanje skupa obiljeţja šeme relacije, odnosno zaglavlja tabele. Putem ove komande se ne unose podaci u vrste tabele, već se definiše: - Naziv tabele - Naziv svake kolone tabele - Tip podataka za svaku kolonu i eventualno vrijednost (NOT NULL)

da kolona ne smije sadrţati nula

create table maticari ( sifra_maticara int not null, ime varchar (20) not null, prezime varchar (30) not null, datum_roðenja datetime not null, jmbg char (13) not null, primary key (sifra_maticara) );

11


DROP TABLE – briše zaglavlje tabele i tabelu

Ova komanda sluţi za brisanje tabele. Ako postoji tabela koja se oslanja na tabelu koju

brišemo, javiti će se greška dok prvo ne izbrišemo tu tabelu. Oblik ove komande je: DROP TABLE ime_tabele Primjer: drop table matiĉari;

ALTER TABLE – izmjena definicije nekog objekta u tabeli

Naredba izmjene je sloţenija naredba jer treba da obezbijedi mogućnost izmjene tabela i to u smislu dodavanja nove kolone, izmjene postojeće kolone, uklanjanje postojeće kolone . pr. Ubacivanje još jedne kolone: alter table maticari add ime_majke varchar (25) null

pr. Brisanje kolone: USE [MaticnaKnjigaRodjenih] GO ALTER TABLE [dbo].[maticari] DROP COLUMN [jmbg] GO

Na osnovu prethodno odraĊenog ER dijagrama i izdvojenih tabela, te pokazanog naĉina za kreiranje tabela i korištenja ĉarobnjaka za izradu tabela, izdvojene su i kreirane sljedeće tabele:

o tabela OPSTINA- tabela sa atributima: šifra opštine i naziv opštine i oznaka opštine.

Primarni kljuĉ ove tabele je (sifra_opstine#). Iz ove tabele će se ĉitati podaci o opštini roĊenja nekog graĊanina. Spojena je sa glavnom

tabelom PODACI tako što je sifra_opstine# strani kljuĉ u tabeli PODACI.

12


Objašnjenje za atribut si fra_opstine: - sifra_opsine# je naziv atributa u koji se smješta šifra opštine - int je tip podatka INTEGER koji koji koristi atribut sifra_opstina. INTEGER je ti p podatka koji ima cjelobrojnu vrijednost i zauzima 4 bajta. Interval ovog tipa je od 231 do 231 – 1 ili (-2 147 483 648 , 2 147 483 647) - not null znaĉi da vrijednost sifre_opstine ne moţe biti nepoznata

Objašnjenje za atribut naziv_opstine: - naziv_opstine je naziv atributa u koji se smješta naziv opstine - varchar(50) je alfanumeriĉki znak promjenljive duţine od najviše 50 znakova - not null, kao i u prethodnom objašnjenju, znaĉi da vrijednost naziva opstine ne moţe biti nepoznata

o tabela MJESTO – tabela sa atributima: šifra mjesta, naziv mjesta i kao strani kljuĉ šifra

opstine iz tabele opstine. Primarni kljuĉ ove tabele je (sifra_mjesta#). Strani kljuĉ ove tabele je sifra_opstine# iz tabele OPSTINA, a to znaĉi da u tabeli MJESTO ne moţe da se upiše mjesto iz neke opstine ako ona nije upisana u tabelu OPSTINA. Ova tabela je sa tabelom PODACI ima dvostruku vezu, ima vezu i sa tabelom MATICAR i sa tabelom OPSTINA. Postupak kreiranja tabele

MJESTO sliĉan je postupku kreiranja tabele OPSTINA, i tipovi podataka su isti.

o tabela PODACI –ova tabela ima mnogo atributa koji predstavljaju matiĉne podatke i upisuju se u Izvod iz knjige roĊenih. P ovezana je sa ostalim tabelama putem stranog kljuĉa. Primarni kljuĉ ove tabele je (sifra_mk#). Postupak kreiranja ove tabele: create table maticni_ podaci ( sifra_mk# int not null god_mk smallint not null rbmk int not null sifra_mjesta_mk# int not null jmbg char(13) ime varchar(20) not null prezime varchar(30) not null pol char(1) not null not null datum_roĊenja date ime_oca varchar(20) ime_majke varchar(20) not null djevojacko_prezime_majke varchar(20) not null sifra_mjestarodj# int not null sifra_drzavljanstva# tinyint not null sifra_nacionalnosti# tinyint sifra_vjeroispovjesti# tinyint sifra_maticara# smallint not null prebivaliste varchar(50) not null )

13


Objašnjenja vezana samo za kreiranje atributa sa do sada ne objašnjenim tipovima podataka, iz tabele PODACI: - jmbg je jedinstveni matiĉni broj graĊana sa tipom podatka char(13) koji predstavlja

string taĉno odreĊene duţine od 13 znakova. Dozvoljno je da neki slogovi u ovoj tabeli imaju nepoznatu vrijednost jmbg -

datum_roĊenja je datm roĊenja osobe. Tip ovog podatka je datatime koji je predstavljen sa 8 bajtova i prihvata vrijeme od 01.01.1753 do 31.12.9999. Nije

-

dozvoljeno da podatak o roĊenju bude nepoznat. sifra_vjeroispovjest je jednoznaĉno odreĊena vjeroispovjest, ali u tabeli PODACI je dozvoljeno da bude nula v rijednost jer moţe postojati covjek koji je ateista. Sifra_vjeroispovjesti je strani kljuĉ u tabeli PODACI. Tip podatka je tinyint koji predstavlja cjelobrojnu vrijednost koja prikazuje brojeve od 0 do 255 i zauzima jedan bajt.

o Tabela ŠIFRA BILJEŠKI

o Tabela BILJEŠKE

o Tabela MATICAR

o Tabela DRŢAVLJANSTVO

14


o Tabela NACIONALNOST

o Tabela VJEROISPOVJEST

SQL omogućava prikaz ER dijagrama nakon kreiranih tabela .

5.2. Komande za manipulaciju (ažuriranje) podataka DML INSERT – upis novog sloga u tabelu

Za upis jedne vrste u neku tabelu, sluţi komanda INSERT . SQL ima mogućnost upisa velike koliĉine podataka u jednu ili više tabela. Naredba INSERT omogućava unos jednog sloga u tabelu. Vrijednost alfanumeriĉkih znakova se stavlja pod navodnike.

15

Izvo iz Matične knjige rođenih - seminarski rad - Borka Pavlovid Primjer: Tabela maticarii ima atribute: (sifra matiĉara, ime, prezime, datum rodjenja, ime majke), za

unos jednog sloga u tabelu navodimo sljedeće INSERT INTO maticarii values (6, 'Slavko', 'Jankovic', '03.03.1966' ,'Jelena')

UPDATE – modifikacija u jednoj ili vise tabela

Uz ovu naredbu mora se navesti: -

U kojoj tabeli se vr še izmjene Za koje kolone u redu mijenjamo vrijednosti, pod kojim uslovima mijenjamo vrijednosti

Oblik naredbe je: UPDATE ime_tabele SET atribut1=izraz1 [,atribut2=izraz2] [WHERE kriterijum selekcije n-torki], pr. Promjena imena i prezimena matiĉara gdje je šifra matiĉara 5

UPDATE maticarii SET imee='Jovana', prezime='Blagojevic' WHERE sifra_matiĉara=5

DELETE – brisanje iz tabele Uz ovu naredbu se mora navesti: - Iz koje tabele se vrši

-

brisanje

Pod kojim uslovima se ukljanja neki red

Sintaksa naredbe je: DELETE FROM ime_tabele WHERE R-Predikat

Primjer: DELETE FROM maticarii WHERE sifra_matiĉara=5

SUBP odbija uklanjanja, ako je to u suprotnosti sa specifikacijom referencijalnog integriteta. 16


5.3.Naredbe za upite

Osnovnu sintaksnu strukturu upitne komande jezika SQL ĉini: SELECT (lista obiljeţja) FROM (nazivi tabela) WHERE (uslovi) Primjer: SELECT ime, prezime, datum_rodjenja, ime_oca FROM maticni_podaci WHERE sifra_mp#=3 SELECT klauzula U ovoj klauzuli navodi se spisak atributa koje treba prikazati. Atributi se navode imenom kojim su definisani. Select klauzula moţe sadrţati i neke specijalne funkcije kao što su: - * koja zamjenjuju spisak svih atributa neke relacije

- DISITINICT koja selektuje samo razliĉite n -torke neke relacije -Skupovne funkcije kao što su: SUM, MAX, MIN, AVG i COUNT.

FROM klauzula

U FROM klauzuli specifiraju se

relacije iz kojih se ĉitaju podaci. Moţe biti navedena jedna ili više relacija (tabela)

WHERE klauzula Izrazom WHERE specificiraju se uslovi (predikati) P koji izdvajaju n-torke iz relacija. Prilikom definisanja kriterijuma moţemo se koristiti logiĉkim operatorima AND, OR, NOT i komparativnim operatorima =,<,>,<>,<=,>=, BETWEEN WHERE klauzula nije obavezna, a m oţe se koristiti u kombinaciji sa SEL ECT, UPDATE, DELETE komandama. Korištena u SELECT bloku, WHERE klauzula omoguć ava: • Selekciju specifiĉnih n -torki relacije (redova tabele), • Selekciju n-torki koje zadovoljavaju višestruke uslove, • Selekciju n-torki koje zadovoljavaju bar jedan od više uslova, • Selekciju n-torki koje ne zadovoljavaju odreĊene uslove, • Selekciju n-torki istovremenim korišćenjem AND i OR logiĉkih operatora, • Selekciju n-torki unutar izvjesnog raspona, • Selekciju n-torki koje zadovoljavaju vrednost u listi vrednosti i • Selekciju n-torki koje sadrţe odreĊenu kombinaciju karaktera.

Primjer: izdvojiti matiĉare koji su roĊeni u drugoj polovini godine 17


select ime, prezime from maticar where month(datum_rodjenja)>6

ORDER BY klauzula

Korišćenjem ORDER BY klauzule moguće je sortirati rezultujuću tabelu po jednom ili više atributla u rastućem ili opadajućem redosljedu.Za specifikaciju rastućeg redosljeda koristi se klauzula ASC, za specif ikaciju opadajućeg redosljeda klauzula DESC. Rastući redosled se podrazumijeva, pa klauzulu ASC nije neophodno navoditi, za razliku od klauzule DESC koju uvek treba navesti kada se sortira u opadajućem redosljedu. Primjer: prikazati osobe iz tabele PODACI poredane po rastućem redoslijedu prezimena

select * from podaci order by prezime

GROUP BY klauzula

Klauzula GROUP BY prouzrokuje

dobijanje sumarne informacije za svaku razliĉitu vrijednost kolone po kojoj se vrši grupisanje. GROUP BY klauzula se moţe koristiti zajedno sa klauzulom WHERE, pri ĉemuWHERE klauzula uvek ide pre GROUP BY klauzule. WHERE klauzulom se najpre izvrši selekcija n -torki, zatim se selektovane n-torke grupišu GROUP BY klauzulom, pa se, eventualno, izvrši selekc ija formiranih grupa HAVING klauzulom. Primjer: prikazati broj mjesta grupisanih po

opštinama 18

Izvo iz Matične knjige rođenih - seminarski rad - Borka Pavlovid select o.naziv_opstine, count (*) as Ukupno_mjesta from mjesto mj, opstina o where mj.sifra_opstine#=o.sifra_opstine# group by o.naziv_opstine

HAVING klauzula

HAVING klauzula odreĊuje kriterijume za selekciju grupa, pošto su grupe već formirane sa GROUP BY klauzulom.

select m.ime, m. prezime, count(*) as Ukupno_biljeski from biljeske b, maticar m where m.sifra_maticara#= b.sifra_maticara# group by m.ime, m. prezime having count(*)>2

Korištenje NULL vrijednosti

NULL vrijednosti su nedefinisane vrednosti. IzmeĊu NULL vr ijednosti i vrijednosti nula postoji znaĉajna semantiĉka razlika. Bez obzira o kom tipu da se radi NULL vrijednost odreĊene kolone moţe se testirati samo pomoću dve specijalne klauzule: IS NULL ili IS NOT NULL. Operatori poreĊenja se ne mogu koristiti za testiranje NULL vrednosti. Primjer: prikazati koje to osobe nemaju upisan jmbg

select ime, prezime from podaci where jmbg is null

LIKE klauzula

Klauzula LIKE omogućava pretraživanje na osnovu "UZORKA", odnosno, dobijanje informacija i kada ne znamo potpun naziv (tj. vrij ednost) odreĊenog atributa tipa character . Ona koristi dva specijalna karaktera ("%"," _ ") sa sledećim znaĉen jem: • "%" predstavlja string od 0 ili više karaktera, a • " _ " predstavlja poziciju jednog karaktera.

19

Izvo iz Matične knjige rođenih - seminarski rad - Borka Pavlovid Prmjer: prikaţi imena osoba ĉije se prezime

završava na vic select ime, prezime from podaci where prezime like '%vic'

5. RELACIONA ALGEBRA elaciona algebra predstavlja osnov za upitne jezike koje koriste ljudi. Svaki R od algebarskih izraza je jedan upit ili pretraţivanje. Upitni jezik je jezik kojim korisnici zahtijevaju informacije iz baza podataka. Postoje u narne i binarne operacije operacije relacione algebre. Unarne zahtijevaju jedan operand a, binarne, naravno, dva. Relaciona algebra je skup operacija nad relacijama. Relacioni izraz u relacionoj algebri sastoji se od niza operacija nad odgovaraju´cim relacijama. Medu va ţnijim ˇ operacijama relacione algebre su projekcija, restrikcija i prirodno spajanje I unija

Algebra je formalni matematiĉki sistem koji se sastoji od skupa objekata i operacija nad tim objektima.

6.1.Restrikcija

Restrikcija (selekcija, ograniĉenje) je elementarna, unarna operacija koja kao rezultat daje taĉno odreĊene n -torke iz tablice i to one koje zadovoljavaju uslov. Uslov je neki logiĉki izraz koji je izraĉunljiv nad svakom n -torkom: ∧ (and), ∨ (or), ¬ (not) SELECT A 1, A2,…Am FROM r WHERE P Primjer - - prikazati mjesta iz opštine Teslić select naziv_mjesta from mjesto where sifra_opstine#=5

6.2.Projekcija Projekcija je unarna, elementarna operacija koja kao rezultat daje relaciju koja se sastoji samo od odreĊenih atributa iz zadate relacije . Zadati skup atributa mora biti 20

Izvo iz Matične knjige rođenih - seminarski rad - Borka Pavlovid podskup skupa atributa polazne relacije. Vrijednosti atributa u ntorkama nastale relacije, odgovaraju onim u polaznoj relaciji.

SELECT A1, A2, …, Am FROM r Primjer - - prikazati ime i prezime osoba unesenih u tabelu podaci select ime, prezime from podaci

6.3.Unija Unija je elementarna, binarna operacija koja kao rezultat daje uniju dvije relacije koja se satoji od svih n-torki koje se nalaze i u jednoj I u drugoj relaciji. Da bi se dvije relacij e

stavile u uniju mora biti zadovoljeno sledeće: - šeme relacija moraju imati isti broj atributa - atributi relacija redom odgovaraju po znaĉ enju i tipu (ne mora po nazivu) (SELECT * FROM r) UNION (SELECT * FROM s) Primjer - - prikaţi imena i prezimena svih ljudi iz baze select ime, prezime from maticar union select ime, prezime from podaci

6.4.Spajanje Spajanje je izvedena, binarna operacija koja iz dvije polazne relacije formira novu sa ntorkama dobijenim iz dva koraka: 1. Svaka n-torka iz orve relacije redom se spaja sa svim n-torkama iz druge relacije 2. iz tako dobijenih n-torki izdvajaju se se one koje zadovoljavaju uslov P. Prirodno spajanje

Povezujemo dvije relacije preko zajedniĉkih atributa, te u rezultirajućoj relacji se pojavljuju samo one n-torke gdje zajednički atributi imaju iste vrijednosti.

21

Izvo iz Matične knjige rođenih - seminarski rad - Borka Pavlovid --primjer: prikaţi matiĉare koji su rodjeni u D oboju:

select m.ime, m. prezime from maticar m,mjesto mj, opstina o where m.sifra_mjesta_rodjenja=mj.sifra_mjesta# and mj.sifra_opstine#=o.sifra_opstine# and o.sifra_opstine#=3

Lijevo spajanje Povezujemo dvije relacije gdje prva tabela predstavlja lijevu tabelu a druga desnu. Spoj

sadrţi sve podatke iz lijeve tabele, ĉak i ako ne postoje odgovarajuće vrijednosti za zapisse u drugoj tabeli. --Primjer :prikaţi ime i prezime matiĉara i šifra biljeske koje su napravili select ime, prezime,sifra_biljeske# from maticar left outer join biljeske on maticar .sifra_maticara#= biljeske.sifra_biljeske#

Desno spajanje Povezujemo dvije relacije gdje prva tabela predstavlja desnu tabelu a druga lijevu. Spoj

sadrţi sve podatke iz desne tabele, ĉak i ako ne postoje odgovarajuće vrijednosti za zapisse u drugoj tabeli.

--Primjer: prikaţi naziv vjeroispovjesti i ljude koji koji i pripadaju toj vjeroispovjesti

select naziv_vjeroispovjesti,ime, prezime from podaci right outer join vjeroispovjest on vjeroispovjest.sifra_vjeroispovjesti#= podaci.sifra_vjeroispovj esti#

22


6. UPITI -- Primjer1: prikazati, uredjen po abecednom redu, spisak maticara iz Prnjavora rodjenih u prvoj polovini godine

select ime, prezime, naziv_mjesta, naziv_opstine from maticar m, mjesto mj, opstina o where m.sifra_mjesta_rodjenja= 1 and m.sifra_mjesta_rodjenja=mj.sifra_mjesta# and mj.sifra_opstine#=o.sifra_opstine# and month (m.datum_rodjenja)between 1 and 6 order by ime

--Primjer2 : prikazati koliko ljudi zivi u opstini Banjaluka, grupisanih po mjestu rodjenja

select p.sifra_mjestarodj#, count (*) as ukupno from podaci p, mjesto mj, opstina o where p. prebivalište=mj.sifra_mjesta# and mj.sifra_opstine#=o.sifra_opstine# and o.sifra_opstine#=2 group by p.sifra_mjestarodj# 23


--Primjer3 : prikazati nacionalnalnu strukturu po opstinama uredjen po abecednom redu

select o.naziv_opstine, n.naziv_nacionalnosti, count(*)as ukupno from podaci p, nacionalnost n, opstina o, mjesto mj where p.sifra_nacionalnosti#=n.sifra_nacionalnosti# and p. prebivalište=mj.sifra_mjesta# and mj.sifra_opstine#=o.sifra_opstine# group by o.naziv_opstine,n.naziv_nacionalnosti order by naziv_opstine

24

Izvo iz Matične knjige rođenih - seminarski rad - Borka Pavlovid --Primjer4:

Svi matiĉari rodjeni u Prnjavoru

select m.ime, m. prezime from mjesto mj, maticar m, opstina o where mj.sifra_mjesta#=m.sifra_mjesta_rodjenja and mj.sifra_opstine#=o.sifra_opstine# and o.sifra_opstine#=1

--Primjer5: prikazati sve maticare rodjene u Prnjavor u koji su napravili više od jedne bilješke i broj bilje ški

select m.ime, m. prezime, count(*)as brBiljeski from podaci p, biljeske b, maticar m, mjesto mj, opstina o where b.sifra_mk# = p.sifra_mk# and m.sifra_maticara# = b.sifra_maticara# and mj.sifra_mjesta# = m.sifra_mjesta_rodjenja and o.sifra_opstine# = mj.sifra_opstine# and o.sifra_opstine# =1 group by m.ime, m. prezime having count(*)>1

25


7. INDEKSI Indeks se moţe posmatrati kao ureĊeni skup pokazivaĉa na slogove bazne tabele. Svaki indeks baziran je na vrij ednostima podataka jedne ili više kolona tabele. Opcija UNIQUE precizira da dva razliĉita sloga u baznoj tabeli nad kojom se kreiraindeks ne mogu uzeti istovremeno istu vrijednost na indeksnoj koloni ili njihovoj kombinaciji.Kreiranje indexa moţemo uraditi uz pomoć SQL komandi ili u opciji index ispod tabele.

Indexi onemogućavaju upis iste kombinacije izabranih kolona. U ovom seminarskom radu šifra maticne knjige je izabrana za priomarni kljuĉ radi lakšeg izvršavanja u pita, da ne bi bilo potrebno kucati redni broj matiĉne knjige, godina matiĉne knjige I sifra mjesta matiĉne knjige. Ako se pokuša unos iste kombinacije indexa, sql će javiti grešku.

26


Indexi se mogu obrisati, onda nakon

toga prestaju da vaţe ograniĉenja uspostavljena indexom. Indekse moţemo izbrisati naredbom DROP INDEX ime_indexa ili desni klik/ delete.

8. Pogled Korisnici baze podataka imaju potrebu da ĉesto upotrebljavaju iste upite nad bazom podataka. Takve upite mogu da snime kao pogled. Pogledi su virtuelne tabele koje nisu

predstavljene fiziĉkim podacima, već je njihova definicija (u terminima drugih tabela) zapamćena u katalogu. Korisnik moţe ali i ne mora biti svestan da pogled nije bazna tabela, jer se nad pogledom mogu postavljati upiti (gotovo svi) kao i nad baznom tabelom.

CREATE VIEW ime_pogleda AS SELECT I FROM I WHERE

Primjer kreiranja pogleda:

create view IZVOD_IZ_MATICNE_KNJIGE_RODJENIH AS select o.naziv_opstine as opstina_mk, mj.naziv_mjesta as mjesto_maticne_knjige, p.rbmk, p.god_mk, p.ime,p.prezime,p.datum_rodjenja, mj1.naziv_mjesta as mjesto_rodjenja, o1.naziv_opstine, d.naziv_drzavljanstva, n.naziv_nacionalnosti,v.naziv_vjeroispovjesti,p.ime_oca, p.ime_majke,p.djevojacko_prezime_majke, mj.naziv_mjesta as mjesto_prebivalista from podaci p, maticar m, mjesto mj,mjesto mj1, opstina o,opstina o1, drzavljanstvo d, nacionalnost n, vjeroispovjest v where p.sifra_maticara#=m.sifra_maticara# and p.sifra_mjesta_mk#=mj.sifra_mjesta# and o.sifra_opstine# = mj.sifra_opstine# and p.sifra_mjestarodj#=mj1.sifra_mjesta# and mj1.sifra_opstine#=o1.sifra_opstine# and p.sifa_drzavljanstva#=d.sifra_drzavljanstva# and p.sifra_nacionalnosti#=n.sifra_nacionalnosti# and p.sifra_vjeroispovjesti#=v.sifra_vjeroispovjesti#

27


Primjer kojim se pokazuju matiĉni podaci osoba, dobijeni upotrebom prethodnog pogleda

28


9. FUNKCIJE Ako neku radnju mnogu puta koristimo, onda je praktiĉno naĉiniti funkcije koje se u datom trennutku pozivaju. Funkcija ima svojeime po kom je pozivamo i moţebiti pozvana iz bilo kog dijela upita. Funkcija vraća jednu vrijednost. Kod imena funkcije deklarišemo ulazni parametar koji ne mora da se zove iso kao i u tabeli. Opcija returns definiše kojeg će tipa biti izlazni podatak. Funkciju pozivamo SELECT P.SIFRA_MK#, P.IME, dbo.f_IME_OSOBE(114) AS IME_PREKO_FN

Primjer1 . Naĉini funkciju koja pripadaju podaci

za datu šifru matiĉnog podatka vraća ime osobe kojoj

CREATE FUNCTION F_IME_OSOBE(@p_sifra_mk int)--ulazni parametar (nemora biti istognaziva kao sto je u tabeli RETURNS CHAR(20) --fja vraca jednu vrijednost tipa char AS BEGIN declare @v_ime char(20);-- deklarisanje nove varijable u koju se smjesta vrijednost atributa kojeg pronadje SELECT @v_ime = ime -- poredi vrijednost ime iz tabele podaci gdje je p.sifra_mk#= onoj koja se ucitava prilikom kreiranja from podaci p where p.sifra_mk# = @p_sifra_mk; RETURN @V_IME;--vraca vrijednost varijable END;

Primjer2. Naĉini funkciju koja za datum upisa bilješke vraća šifru matiĉara koji

je napisao

bilješku . create function datumUpisa (@p_datum_upisa datetime) returns int as begin declare @v_ime_upisa int; select @v_ime_upisa=b.sifra_maticara# from biljeske b where @p_datum_upisa=b.datum_upisa; return @v_ime_upisa; end;

Poziv funkcije select

dbo.datumUpisa('10.10.2002')as ime

from biljeske b

29


Primjer3. Naĉiniti funkciju koja datum ispisuje slovima USE [MaticnaKnjigaRodjenih] GO create function fn_ispis_datuma(@p_datum_rodjenja datetime) returns char(25) as begin declare @v_datum char(25), @v_dan int, @v_mjesec int, @v_dan_str varchar(50), @v_mjesec_str varchar(50); set @v_dan = day(@p_datum_rodjenja); set @v_dan_str = ( case @v_dan when 1 then 'prvi' when 2 then 'drugi' when 3 then 'treci' when 4 then 'cetvrti' when 5 then 'peti' when 6 then 'sesti' when 7 then 'sedmi' when 8 then 'osmi' when 9 then 'deveti' when 10 then 'deseti' when 11 then 'jedanaesti' ... when 31 then 'tridesetprvi' else 'greska' end); set @v_mjesec=month(@p_datum_rodjenja); set @v_mjesec_str = ( case @v_mjesec when 1 then 'januar' when 2 then 'februar' when 3 then 'mart' when 4 then 'april' when 5 then 'maj' when 6 then 'juni' when 7 then 'juli' when 8 then 'avgust' when 9 then 'septembar' when 10 then 'oktobar' when 11 then 'novembar' when 12 then 'decembar' else 'Greska' end); set @v_datum = CONVERT(VARCHAR(2),@v_dan) +'.(' +@v_dan_str + ') ' + @v_mjesec_str; return @v_datum; end;

30


poziv funkcije : USE [MaticnaKnjigaRodjenih] GO SELECT P.IME, P.DATUM_RODJENJA, DBO.FN_ISPIS_DATUMA(P.DATUM_RODJENJA) AS FORMATIRAN_DATUM from podaci P

rezultat funkcije:

10. PROCEDURE Procedura za insertovanje podataka u tabelu PODACI_LOG koja će da sadrţi izmjenjenje podatke. USE [MaticnaKnjigaRodjenih] GO /****** Object: Table [dbo].[PODACI] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO

Script Date: 01/13/2014 16:16:06

CREATE PROCEDURE INSERT_PODACI_LOG(@p_sifra_mk int, @p_god_mk smallint, @p_rbmk int, @p_sifra_mjesta_mk int,

31

Izvo iz Matične knjige rođenih - seminarski rad - Borka Pavlovid @p_jmbg char(13), @p_ime varchar(20), @p_prezime varchar(30), @p_pol char(1), @p_datum_rodjenja datetime, @p_ime_oca varchar(20), @p_ime_majke varchar(20), @p_djevojacko_prezime_majke varchar(20), @p_sifra_mjestarodj int, @p_sifa_drzavljanstva int, @p_sifra_nacionalnosti int, @p_sifra_vjeroispovjesti int, @p_sifra_maticara int, @p_prebivalište varchar(50), @p_datum_promjene datetime) AS BEGIN INSERT INTO [MaticnaKnjigaRodjenih].[dbo].[PODACI_LOG] ([sifra_mk#] ,[god_mk] ,[rbmk] ,[sifra_mjesta_mk#] ,[jmbg] ,[ime] ,[prezime] ,[pol] ,[datum_rodjenja] ,[ime_oca] ,[ime_majke] ,[djevojacko_prezime_majke] ,[sifra_mjestarodj#] ,[sifa_drzavljanstva#] ,[sifra_nacionalnosti#] ,[sifra_vjeroispovjesti#] ,[sifra_maticara#] ,[prebivalište] ,[datum_promjene]) VALUES (@p_sifra_mk, @p_god_mk, @p_rbmk, @p_sifra_mjesta_mk, @p_jmbg, @p_ime, @p_prezime, @p_pol, @p_datum_rodjenja, @p_ime_oca, @p_ime_majke, @p_djevojacko_prezime_majke, @p_sifra_mjestarodj, @p_sifa_drzavljanstva, @p_sifra_nacionalnosti, @p_sifra_vjeroispovjesti, @p_sifra_maticara, @p_prebivalište , @p_datum_promjene); END; GO

32


11. TRIGERI Pr imjer trigera koji upisuje sistemski datum i vrijeme kada je novi slog upisan u tabeli PODACI. Triger prije insertovanja USE [MaticnaKnjigaRodjenih] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= CREATE TRIGGER BI_PODACI --triger se okida onda kad pritisnem enter na poslednjoj koloni ON [MaticnaKnjigaRodjenih].[dbo].[PODACI] INSTEAD OF INSERT -- umjesto inserta AS BEGIN INSERT INTO [MaticnaKnjigaRodjenih].[dbo].[PODACI] (sifra_mk# ,god_mk ,rbmk ,sifra_mjesta_mk# ,jmbg ,ime ,prezime ,pol ,datum_rodjenja ,ime_oca ,ime_majke ,djevojacko_prezime_majke ,sifra_mjestarodj# ,sifa_drzavljanstva# ,sifra_nacionalnosti# ,sifra_vjeroispovjesti# ,sifra_maticara# ,prebivalište ,datum_promjene) SELECT sifra_mk# ,god_mk ,rbmk ,sifra_mjesta_mk# ,jmbg ,ime ,prezime ,pol ,datum_rodjenja ,ime_oca ,ime_majke ,djevojacko_prezime_majke ,sifra_mjestarodj# ,sifa_drzavljanstva# ,sifra_nacionalnosti# ,sifra_vjeroispovjesti# ,sifra_maticara#

33

Izvo iz Matične knjige rođenih - seminarski rad - Borka Pavlovid ,Prebivalište ,GETDATE() --dodaje sistemsko vrijeme KREIRANJA sloga FROM INSERTED --tu se nalaze podaci kao u clipboardu, to je kao neka tabela END GO

Primjer trigera koji ažuriran poatak upisuje u tabelu PODACI_LOG i ažurira atum i vrijeme izmjene. USE [MaticnaKnjigaRodjenih] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= CREATE TRIGGER AU_PODACI --pokrece se nakon updatea sloga ON [MaticnaKnjigaRodjenih].[dbo].[PODACI] AFTER UPDATE AS DECLARE @p_sifra_mk int;--definisanje promjenliivih DECLARE @p_god_mk smallint; DECLARE @p_rbmk int; DECLARE @p_sifra_mjesta_mk int; DECLARE @p_jmbg char(13); DECLARE @p_ime varchar(20); DECLARE @p_prezime varchar(30); DECLARE @p_pol char(1); DECLARE @p_datum_rodjenja datetime; DECLARE @p_ime_oca varchar(20); DECLARE @p_ime_majke varchar(20); DECLARE @p_djevojacko_prezime_majke varchar(20); DECLARE @p_sifra_mjestarodj int; DECLARE @p_sifa_drzavljanstva int; DECLARE @p_sifra_nacionalnosti int; DECLARE @p_sifra_vjeroispovjesti int DECLARE @p_sifra_maticara int; DECLARE @p_prebivalište varchar(50); DECLARE @p_datum_promjene datetime; SELECT @p_sifra_mk = sifra_mk# FROM deleted;--kupi podatke iz memorije koja drzi izmjenjene podatke SELECT @p_god_mk = god_mk FROM DELETED; SELECT @p_rbmk = rbmk FROM DELETED; SELECT @p_sifra_mjesta_mk = sifra_mjesta_mk# FROM DELETED; SELECT @p_jmbg = jmbg FROM DELETED; SELECT @p_ime = ime FROM DELETED; SELECT @p_prezime = prezime FROM DELETED; SELECT @p_pol = pol FROM DELETED; SELECT @p_datum_rodjenja = datum_rodjenja FROM DELETED; SELECT @p_ime_oca = ime_oca FROM DELETED; SELECT @p_ime_majke = ime_majke FROM DELETED; SELECT @p_djevojacko_prezime_majke = djevojacko_prezime_majke FROM DELETED;

34

Izvo iz Matične knjige rođenih - seminarski rad - Borka Pavlovid SELECT @p_sifra_mjestarodj = sifra_mjestarodj# FROM DELETED; SELECT @p_sifa_drzavljanstva = sifa_drzavljanstva# FROM DELETED; SELECT @p_sifra_nacionalnosti = sifra_nacionalnosti# FROM DELETED; SELECT @p_sifra_vjeroispovjesti = sifra_vjeroispovjesti# FROM DELETED; SELECT @p_sifra_maticara = sifra_maticara# FROM DELETED; SELECT @p_prebivalište = prebivalište FROM DELETED; SELECT @p_datum_promjene = datum_promjene FROM DELETED; IF ((SELECT TRIGGER_NESTLEVEL()) < 2) BEGIN

EXEC [MaticnaKnjigaRodjenih].[dbo].INSERT_PODACI_LOG @p_sifra_mk,-pozivanje procedure koja snimi podatke u tabelu podaci log @p_god_mk, --podatke kupi iz memorije za brisanje @p_rbmk, @p_sifra_mjesta_mk, @p_jmbg, @p_ime, @p_prezime, @p_pol, @p_datum_rodjenja, @p_ime_oca, @p_ime_majke, @p_djevojacko_prezime_majke, @p_sifra_mjestarodj, @p_sifa_drzavljanstva, @p_sifra_nacionalnosti, @p_sifra_vjeroispovjesti, @p_sifra_maticara, @p_prebivalište , @p_datum_promjene; UPDATE [MaticnaKnjigaRodjenih].[dbo].[PODACI] SET datum_promjene = GETDATE() WHERE sifra_mk# = @p_sifra_mk; END GO

35


12. CASE ALATI Alati koji pomaţu projektantima da povećaju svoju produktivnost prilikom izrade softvera su case alati. To je softver k oji omogućava inţenjersko projektovanje softvera pomoću raĉunara, ustvari, softver namjenjen automatizaciji izrade softvera. Ciljevi primjene CASE tehologije su: - Povećanje produktivnosti projektnata - Skraćenje vremena izrade softvera - Povećanje kvaliteta sof tvera - UnapreĊenje performansi sistema Za potrebe ovog seminarskog rada koristila sam SAP-ov alat POWER DESIGNER. Obzirom da sam projektovanje baze uradila prije upoznavanja sa ovim alatom, uradila sam reverzni inţenjering pravljenje E-R dijagrama iz postojeće baze.

Izbor baze i objekata

Početni prozor

Proces reverznog stvaranja E-R dijagrama

Poešavanje parametara

36


E-R

dijagram dobijen pomoću Power Designer

37

Seminarski rad - BorkaPavlovic - Rdbms (4. godina, case alati).pdf

Recommend Documents