Vježbe 1

 

Na vježbama se proučava primjer oblikovanja konceptualne sheme baze podataka o bolnici. Riječ je o prilogu P1 iz Udžbenika (stranice 143-151). U primjeru se kreće od specifikacije zahtjeva, provodi se sam postupak oblikovanja te se stvara dokumentacija konceptualne sheme u obliku reduciranog Chen-ovog dijagrama s popratnim tekstom.

 

Kao dodatni primjer za samostalni rad navodi se oblikovanje konceptualne sheme baze podataka o znanstvenoj konferenciji. Taj primjer također je dokumentiran u Udžbeniku i to kao prilog P2 (stranice 152-161).


Vježbe 2

 

Zadatak 1. Promatramo zapis o suradniku neke tvrtke i njegovoj djeci koji je prikazan slikom 4.1 na stranici 50 u Udžbeniku. Pretvorite taj zapis u 1NF.


Zadatak 2. Tvornica sklapa proizvode od dijelova, a dijelove kupuje od raznih dobavljača. Isti dio se može dobiti od raznih dobavljača po raznim cijenama, a isti dobavljač nudi razne dijelove. Situacija je opisana relacijom:

CJENIK(BR_DJELA, BR_DOBAVLJACA, IME_DOBAVLJACA, ADRESA_DOBAVLJACA, CIJENA).

Pretvorite tu relaciju u 2NF.


Zadatak 3. Rent-a-car agencija ima u bazi relaciju na osnovi koje se određuje cijena iznajmljivanja automobila:

 

KATALOG

Marka auta

Model auta

Zapremnina motora

Slobodnih komada

Kategorija cijene

Volvo

244

2.0

2

B

Honda

Legend

1.6

1

C

Austin

Mini

1.3

3

D

Volvo

360

2.0

5

B

BMW

320

2.0

2

B

Volvo

244

2.4

3

A

Ford

Sierra

1.6

7

C

Ford

Sierra

2.4

1

A

Austin

Metro

1.3

4

D

 

Prevedite tu relaciju u 2NF.


Zadatak 4. Suradnici neke tvrtke rade na raznim projektima. Pritom jedan suradnik radi na točno jednom projektu. Situacija je opisana relacijom:

SURADNIK(MATICNI_BROJ, IME_I_PREZIME, PLACA, BROJ_PROJEKTA, ROK_ZAVRSETKA_PROJEKTA).

Pretvorite relaciju u 3NF.


Zadatak 5. Knjižnica ima u bazi relaciju s knjigama koje se mogu posuditi:

KNJIGE(ISBN, NASLOV, PRVI_AUTOR, IME_IZDAVACA, MJESTO_IZDAVANJA).

Prebacite relaciju u 3NF.


Zadatak 6. Tvornica isporučuje svoje proizvode kupcima. Jedna isporuka šalje se jednom kupcu i može sadržavati više komada raznih proizvoda. Situacija je prikazana zapisom na slici 4.9 na stranici 66 u Udžbeniku. Pretvorite taj zapis u 3NF.


Zadatak 7. Jedan kolegij održava se uvijek u istoj predavaonici, no u nekoliko vremenskih termina tjedno. Situacija je opisana relacijom:

RASPORED(BR_PREDAVAONICE, VREMENSKI_TERMIN, BR_KOLEGIJA).

Prevedite relaciju u BCNF.


Zadatak 8. Studenti upisuju izborne kolegije iz matematike i izborne kolegije iz računarstva. Ne postavljaju se nikakvi uvjeti na izbor jednih u odnosu na druge. Situacija je opisana relacijom:

IZBOR(BR_INDEKSA, BR_M_KOLEGIJA, BR_R_KOLEGIJA).

Pretvorite relaciju u 4NF.


Vježbe 3

 

Svi zadatci odnose se na demo-bazu o fakultetu koja je dostupna na poslužitelju student.math.hr preko sustava za upravljanje bazom podataka MySQL.

 

Zadatak 1. Ispisati čitav sadržaj svake od 5 tablica u demo-bazi.


Zadatak 2. Ispisati imena i datume rođenja za sve studente.


Zadatak 3. Recimo da fakultet odluči svim nastavnicima udvostručiti plaću od iduće godine. Ispišite kolike će biti plaće nastavnika iduće godine.


Zadatak 4. Ispišite OIB-e nastavnika koji predaju bar jedan kolegij.


Zadak 5. Ispisati sve podatke o nastavnicima iz sobe 127. Ispisati imena nastavnika s plaćom većom od 10 000. Ispisati imena nastavnika koji ne sjede u sobi 315.


Zadatak 6. Ispisati sve podatke o nastavnicima koji sjede u sobi 127 ili 315. Ispisati sve podatke o nastavnicima koji ne sjede ni u sobi 127 ni u sobi 315.


Zadatak 7. Ispisati sve podatke o nastavnicima koji zarađuju između 10 000 i 12 000 (uključivo). Ispisati sve podatke o nastavnicima koji zarađuju manje od 9 000 ili više od 11 000.


Zadatak 8. Ispisati JMBAG-ove svih studenata koji su iz kolegija sa šifrom 72001 dobili ocjenu između 3 i 5. Ispisati JMBAG-ove studenata koji su bar iz jednog kolegija dobili ocjenu između 3 i 5.


Zadatak 9. Ispiši imena studenata koja počinju slovom 'M', zatim sva imena studenata u kojima se pojavljuje niz znakova 'ar'. Na kraju, ispisati imena studenata kojima se kao treće slovo pojavljuje 'a'.


Zadatak 10. Ispisati podatke o nastavnicima, uzlazno sortirane po plaći. Ispisati sve podatke o nastavnicima, silazno sortirano po plaći.


Zadatak 11. Ispišite prezimena studenata rođenih 1992 godine, koji su na 1. godini studija. Ispis treba biti abecedno sortiran po prezimenima.


Zadatak 12. Ispisati podatke o nastavnicima koji zarađuju između 10 000 i 12 000, tako da im prezimena budu sortirana po abecedi. Ispisati podatke o svim nastavnicima, sortirano po sobama, s time da nastavnici iz iste sobe slijede abecedno poredani po prezimenima.


Zadatak 13. Ispišite studente sortirano po starosti (počevši od najstarijeg).


Zadatak 14. Ispisati imena i udvostručene plaće nastavnika, sortirano po udvostručenim plaćama.


Vježbe 4

 

Na ovim vježbama studenti rade s vlastitom bazom podataka te su u stanju mijenjati podatke u njoj.


Zadatak 15. Stvoriti tablice koje odgovaraju onima u demo-bazi "fakultet".


Zadatak 16. U direktoriju /math/karaga/baze nalaze se datoteke s podacima za demo-bazu. Jedna datoteka odgovara jednoj tablici, jedan redak datoteke jednoj n-torki. Vrijednosti atributa razdvojene su znakovima tab. Imena datoteka su datanastavnik i datapredmet. Inicijalizirajte sadržaj za vlastite tablice NASTAVNIK i PREDMET tako da učitate podatke iz odgovarajućih datoteka.


Zadatak 17. Inicijalizirajte sadržaj za vlastite tablice UPISAO i STUDENT tako da prepišete podatke iz odgovarajućih tablica demo-baze "fakultet".


Zadatak 18. Upišite u bazu novog nastavnika s OIB-om 50000000000, prezimenom Mirkovic, imenom Mirko, koji će sjediti u sobi 101 i imati će plaću 8000 kuna.


Zadatak 19. Realizirajte situaciju u kojoj je nastavnik s OIB-om 44102179316 (Klein) dobio otkaz. Sve njegove kolegije preuzima nastavnik s OIB-om 50000000000 (Mirkovic).


Zadatak 20. Nastavnika Pascala premjestite u sobu 201 i dajte mu povišicu u visini 20% dosadašnje plaće.


Zadatak 21. Poništite upise svih kolegija koji nisu položeni.


Zadatak 22. Dajte svim nastavnicima plaću od 10000.


Zadatak 23. Stvorite indeks po primarnom kljucu za svaku od 4 tablice.


Zadatak 24. Provjerite da "unique indeks" zaista sprečava upis 2 n-torke s istom vrijednošću primarnog ključa.


Zadatak 25. Stvorite indekse kojima se ubrzava: traženje studenata na zadanoj godini, traženje nastavnika koji sjede u zadanoj sobi.


Korisni prilog uz ove vježbe je sljedeća komandna datoteka u MySQL-u koja služi za ponovo stvaranje kopije demo-baze te njezino punjenje s podacima: ponovo stvori i napuni bazu


Vježbe 5

 

Na ovim vježbama rade se složeniji upiti. Ponovo se koristi demo-baza o fakultetu.


Zadatak 1. Ispisati listu imena studenata zajedno s naslovima kolegija koje su oni upisali. Ispisati naslove onih kolegija koje je upisao student Vukovic Janko.


Zadatak 2. Ispisati listu imena studenata zajedno s nazivima kolegija koje su oni upisali, s time da isključimo studente na 3. godini. Ispisati nazive onih kolegija koje je upisao bar jedan student na 2. godini.


Zadatak 3. Ispisati sve parove nastavnika (po prezimenima) koji sjede u istoj sobi.


Zadatak 4. Ispisati prezimena i plaće svih nastavnika koji imaju veću plaću od nastavnika Codd-a.


Zadatak 5. Ispisati listu imena i prezimena studenata zajedno s naslovima kolegija koje ti studenti nisu upisali.


Zadatak 6. Ispisati imena i prezimena studenata koji nisu upisali ni jedan kolegij.


Zadatak 7. Ispisati imena studenata koji su upisali barem jedan kolegij. Ispisati sobe nastavnika koji predaju barem jedan kolegij.


U nastavku vježbi koristimo funkcije u MySQL-u. Popis svih dostupnih funkcija može se naći u dokumentaciji za MySQL.

 
Zadatak 8. Izračunaj izraz e^sinx + e^cosx za x=1.2 rad.


Zadatak 9. Ispiši plaće svih nastavnika u stranoj valuti, kao cijeli broj. Jedinica strane valute vrijedi kao 7.5 jedinica domaće.

 
Zadatak 10. Ispiši parove studenata koji su se rodili istog dana u tjednu.


Zadatak 11. Prikaži uspjeh studenata koji su položili predmet sa šifrom 56001 u obliku histograma (veća ocjena-dulji niz zvjezdica).


Zadatak 12. Ispišite svoje korisničko ime i trenutnu verziju MySQL-a.


Vježbe 6

Na ovim vježbama rade se grupirajući upiti. Ponovo se koristi demo-baza o fakultetu. Popis svih dostupnih grupnih funkcija može se naći u dokumentaciji za MySQL.

 

Zadatak 13. Ispisati: zbroj svih plaća nastavnika, prosječnu plaću nastavnika, ime i plaću nastavnika s najvećom plaćom.


Zadatak 14. Ispisati: broj studenata na 1. godini, broj studenata na 2. godini, broj svih godina, broj svih studenata.


Zadatak 15. Ispisati koliko studenata ima na svakoj godini studija.


Zadatak 16. Ispisati maksimum, minimum i raspon plaće za nastavnike iz svake od soba. Zatim ispisati iste podatke samo za sobu 127.


Zadatak 17. Ispišite srednje ocjene i standardne devijacije za ocjene po svim kolegijima.


Zadatak 18. Ispišite maksimalnu ocjenu koju su studenti s pojedine godine ostvarili iz pojedinog kolegija.


Zadatak 19. Ispišite prosječnu plaću za nastavnike iz iste sobe, no samo za sobe u kojima ima više od jednog nastavnika.


Zadatak 20. Zbrojite plaće po sobama i ispišite rang listu soba po plaćama.


Zadatak 21. Ispišite imena i plaće za tri najbolje plaćena nastavnika.


Kao prilog ovim vježbama dodajemo tekst o SQL-naredbi JOIN: Naredba JOIN


Vježbe 7

Ove vježbe odnose se na čuvanje integriteta baze podataka. Koristi se studentova vlastita kopija demo-baze o fakultetu. Pojam integritet baze odnosi se na korektnost i konzistentnost podataka - odnosno, mi pazimo da podatci u našoj bazi budu i ostanu konzistentni i točni. To osiguravamo tako da stvorimo tablice na način koji će osigurati da polja ključeva ne mogu ostati nepopunjena i koji ce omogućavati automatsku provjeru svojstva stranog ključa.


Zadatak 1.
Stvorite nove tablice STUDENT, NASTAVNIK, PREDMET, UPISAO, ali tako da u što većoj mjeri štitite integritet. Napunite nove tablice podatcima iz demo-baze fakultet.
Rješenje:
Prvo bi morali obrisati stare tablice naredbama:
DROP TABLE STUDENT;
DROP TABLE NASTAVNIK;
DROP TABLE PREDMET;
DROP TABLE UPISAO;
Nakon toga stvaramo tablice na novi način:
Stvori tablice, integritet
Na kraju napunimo tablice podatcima iz demo-baze:
INSERT INTO STUDENT SELECT * FROM fakultet.STUDENT;
INSERT INTO NASTAVNIK SELECT * FROM fakultet.NASTAVNIK;
INSERT INTO PREDMET SELECT * FROM fakultet.PREDMET;
INSERT INTO UPISAO SELECT * FROM fakultet.UPISAO;
Važno: kada smo ovako stvorili tablice moramo ih puniti baš tim redoslijedom, jer traže strani ključ.

Dodatno sredstvo koje osigurava korektnost su transakcije Transakcija je niz naredbi koji prevodi bazu iz jednog konzistentnog stanja u drugo. Među-stanja (stanja između pojedinih naredbi unutar transakcije) mogu biti nekonzistentna. Zato transakcija mora biti izvršena ili cijela ili ne smije biti uopće izvršena. Znači, kada želimo da se neke naredbe izvrše "u komadu" (npr, recimo da zelimo uplatiti neki iznos sa našeg računa na račun nekog poduzeća, ne bismo htjeli da se operacija oduzimanja novaca s našeg računa provede prije nego sto budemo sigurni da će se provesti i operacija uplate na račun primatelja) takve naredbe pišemo unutar transakcije. Transakciju počinjemo naredbom SET AUTOCOMMIT=0; (koja prebacuje SQL iz defaultnog stanja u kome se svaka naredba izvršava odmah nakon što je napisana) i naredbom BEGIN; (koja označava početak transakcije). Nakon toga pišemo naredbe koje su dio transakcije i kada smo gotovi pišemo naredbu COMMIT; (ako želimo izvršiti čitavu transakciju) ili ROLLBACK; (ako ne želimo da se transakcija izvrši). Na kraju vraćamo SET AUTOCOMMIT=1;. Da bi mogli koristiti transakcije, tablice moraju biti tipa "InnoDB".


Zadatak 2. Sastavite transakciju kojom se novčani iznos od 1 000 kuna prebacuje iz plaće nastavnika Cantor-a u plaću nastavnika Codd-a. Pod čuvanjem konzistencije smatramo činjenicu da je ukupan zbroj plaća ostao isti.
Rješenje:
SET AUTOCOMMIT=0;
BEGIN;
UPDATE NASTAVNIK SET SALARY=SALARY-1000 WHERE LNAME='Cantor';
UPDATE NASTAVNIK SET SALARY=SALARY+1000 WHERE LNAME='Codd';
COMMIT;
SET AUTOCOMMIT=1;
(možete još nakon svake naredbe pisati SELECT * FROM NASTAVNIK; da vidite efekt onoga što se događa).


Vježbe 8

Ove vježbe odnose se na zaštitu baze podataka od neovlaštenog korištenja. U SQL-u imamo naredbe GRANT (za dodjelu) i REVOKE (za oduzimanje) pomoću kojih se pojedinim korisnicima dodjeljuju ili oduzimaju ovlaštenja. Pritom se ovlaštenja mogu dati za čitavu bazu i sve operacije na njoj, ili za samo neke operacije na bazi, ili za samo neke operacije na samo nekim tablicama ili cak samo nekim redcima ili atributima. Uobičajeno se dodjeljuju ovlaštenja za: SELECT, INSERT, DELETE, UPDATE, ALTER, CREATE, DROP, ALL (sve prije navedeno). Ovlaštenja se pridružuju pojedinim korisnicima, odnosno njihovim korisničkim imenima. Kako je MySQL zamišljen da se koristi na Internetu, korisnička imena u MySQLu su oblika ime_korisnika@ime_računala, odnosno isti korisnik kada se prijavljuje sa različitih računala može imati različita ovlaštenja.

Iduće zadatke studenti neće moći izvršiti ako ih pokrenu nad originalnom demo-bazom o fakultetu, jer nemaju administratorske ovlasti nad tom bazom. Naravno, slični primjeri ipak se mogu isprobati na studentovoj vlastitoj kopiji demo-baze.

Zadatak 3. Napišite naredbu kojom se neregistiriranom (anonymous) korisniku, prijavljenom sa studentskog računala, dozvoljava pretraživanje demo baze fakultet.
Rješenje:
GRANT SELECT ON fakultet.* TO ' '@localhost;

Zadatak 4. Napišite naredbe kojima se stvara korisnik somestudent s lozinkom loz000, koji se prijavljuje sa studentskog računala, može raditi što želi u svojoj bazi s imenom somestudent, smije pretraživati demo bazu fakultet i smije učitavati ili ispisivati ASCII datoteke.
Rješenje:
CREATE DATABASE somestudent;
GRANT ALL ON somestudent.* TO somestudent@localhost IDENTIFIED BY 'loz000';
GRANT SELECT ON fakultet.* TO somestudent@localhost;
GRANT FILE ON *.* TO somestudent@localhost;


Zadatak 5. Oduzmite korisniku somestudent pravo čitanja tabele PREDMET u demo bazi fakultet.
Rješenje:
REVOKE SELECT ON fakultet.NASTAVNIK FROM somestudent@localhost;