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;
|