- (Primjer za analizu potreba - radimo bazu za bolnicu)
Pacijenti koji zauzimaju sobe Pacijent se obicno smjesta u bolnicku sobu prilikom dolaska u bolnicu. Svaka soba moze primiti mnogo pacijenata, i imamo sobe razlicitih tipova. Konzultanti (stariji kirurzi) bolnice smiju imati i svoje pacijente, koji su smjesteni u jednokrevetnim privatnim sobama. Informacije koje treba pamtiti o pacijentu ukljucuju jedinstveni broj zdravstvenog osiguranja, ime, adresu itd.
Medicinske sestre zaduzene za sobe Sestra moze ili ne mora biti zaduzena za sobu. Pritom jedna sestra moze biti zaduzena za najvise jednu sobu, no za istu sobu moze biti zaduzeno vise sestara. Sestra je jednoznacno odredjena svojom identifikacijskom oznakom.
Operacije koje se obavljaju nad pacijentima Nad istim pacijentom moze se obaviti vise operacija. Informacije o jednoj operaciji su: tip operacije, pacijent, kirurg, datum i vrijeme.
Kirurzi koji obavljaju operacije jednu operaciju obavlja samo jedan kirurg, a za ostale prisutne kirurge se smatra da oni asistiraju operaciji. Kirurge nadgledaju stariji kirurzi, tzv. konzultanti, koji takodjer mogu obavljati operacije ili asistirati. Informacije o jednom kirurgu su ime (pretp. jedinstveno), adresa, br. telefona itd. Svaki konzultant ima svoju specijalnost.
Operacione sale u kojima se obavljaju operacije Jedna operacija se odvija u samo jednoj sali, no ista sala moze biti mjesto mnogih operacija. Svaka sala ima svoj identifikacijski broj. Neke sale su specijalno opremljene za neke vrste operacija.
Medicinske sestre zaduzene za sale Sestra moze ili ne mora biti zaduzena za salu, no ne moze biti zaduzena za vise od jedne sale. Za jednu salu moze biti zaduzeno mnogo sestara.
Slika, Reducirani Chenov dijagram, razlike, obavezno clanstvo
Vjezbe 2
Zad 1 Promatramo zapis o suradniku neke firme i njegovoj djeci: vidi zapis. Pretvorite taj zapis u 1NF.
Zad 2 Tvornica sklapa proizvode od djelova, a djelove kupuje od raznih dobavljaca. Isti dio se moze dobiti od raznih dobavljaca po raznim cijenama, a isti dobavljac nudi razne djelove. Situacija je opisana relacijom:
Zad 3 Rent-a-car agencija ima u bazi relaciju na osnovu koje se odredjuje cijena iznajmljivanja automobila: vidi relaciju. Prevedite tu relaciju u 2NF.
Zad 4 Suradnici neke firme rade na raznim projektima. Pritom jedan suradnik radi na tocno jednom projektu. Situacija je opisana relacijom:
Zad 5 Knjiznica ima u bazi relaciju s knjigama koje se mogu posuditi:
Zad 6 Tvornica isporucuje svoje proizvode kupcima. Jedna isporuka salje se jednom kupcu i moze sadrzati vise komada raznih proizvoda. Situacija je prikazana zapisom: vidi zapis. Pretvorite taj zapis u 3NF.
Zad 7 Jedan kolegij odrzava se uvijek u istoj predavaonici, no u nekoliko vremenskih termina tjedno. Situacija je opisana relacijom:
Zad 8 Studenti upisuju izborne kolegije iz matematike i izborne kolegije iz racunarstva. Ne postavljaju se nikakvi uvjeti na izbor jednih u odnosu na druge. Situacija je opisana relacijom:
Vjezbe 3
Zad 1 Ispisati citav sadrzaj svake od 4 tabele u demo bazi.
Zad 2 Ispisati imena i datume rodjenja za sve studente.
Zad 3 Recimo da fakultet odluci svim nastavnicima udvostruciti placu od iduce godine. Ispisite kolike ce biti place nastavnika iduce godine.
Zad 4 Ispisite OIB-e nastavnika koji predaju bar jedan kolegij.
Zad 5 Ispisati sve podatke o nastavnicima iz sobe 127. Ispisati imena nastavnika s placom vecom od 10 000. Ispisati imena nastavnika koji ne sjede u sobi 315.
Zad 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.
Zad 7 Ispisati sve podatke o nastavnicima koji zaradjuju izmedju 10 000 i 12 000 (ukljucivo). Ispisati sve podatke o nastavnicima koji zaradjuju manje od 9 000 ili vise od 11 000.
Zad 8 Ispisati JMBAG-ove svih studenata koji su iz kolegija sa sifrom 72001 dobili ocjenu izmedju 3 i 5. Ispisati JMBAG-ove studenata koji su bar iz jednog kolegija dobili ocjenu izmedju 3 i 5.
Zad 9 Ispisi imena studenata koja pocinju slovom 'M', zatim sva imena studenata u kojima se pojavljuje niz znakova 'ar'. Na kraju, ispisati imena studenata kojima se kao trece slovo pojavljuje 'a'.
Zad 10 Ispisati podatke o nastavnicima, uzlazno sortirane po placi. Ispisati sve podatke o nastavnicima, silazno sortirano po placi.
Zad 11 Ispisite prezimena studenata rodjenih 1992 godine, koji su na 1. godini studija. Ispis treba biti abecedno sortiran po prezimenima.
Zad 12 Ispisati podatke o nastavnicima koji zaradjuju izmedju 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.
Zad 13 Ispisite studente sortirano po starosti (pocevsi od najstarijeg).
Zad 14 Ispisati imena i udvostrucene place nastavnika, sortirano po udvostrucenim placama.
Vjezbe 4
Zad 15 Stvoriti tablice koje odgovaraju onima u demo bazi "fakultet".
Zad 16 U direktoriju /math/karaga/baze nalaze se datoteke s podacima za demo bazu. Jedna datoteka odgovara jednoj tabeli, jedan redak datoteke jednoj n-torki. Vrijednosti atributa razdvojene su znakovima tab. Imena datoteka su datanastavnik i datapredmet. Inicijalizirajte sadrzaj za vlastite tabele NASTAVNIK i PREDMET t. d. ucitate podatke iz odgovarajucih datoteka.
Zad 17 Inicijalizirajte sadrzaj za vlastite tabele UPISAO i STUDENT t. d. prepisete podatke iz odgovarajucih tabela demo baze "fakultet".
Zad 18 Upisite u bazu novog nastavnika s OIB-om 50000000000, prezimenom Mirkovic, imenom Mirko, koji ce sjediti u sobi 101 i imati ce placu 8000 kuna.
Zad 19 Realizirajte situaciju u kojoj je nastavnik s OIB-om 44102179316 (Klein) dobio otkaz. Sve njegove kolegije preuzima nastavnik s OIB-om 50000000000 (Mirkovic).
Zad 20 Nastavnika Pascala premjestite u sobu 201 i dajte mu povisicu u visini 20% dosadasnje place.
Zad 21 Ponistite upise svih kolegija koji nisu polozeni.
Zad 22 Dajte svim nastavnicima placu od 10000
Zad 23 Stvorite indeks po primarnom kljucu za svaku od 4 tablice.
Zad 24 Provjerite da "unique indeks" zaista sprecava upis 2 n-torke s istom vrijednoscu primarnog kljuca.
Zad 25 Stvorite indekse kojima se ubrzava: trazenje studenata na zadanoj godini, trazenje nastavnika koji sjede u zadanoj sobi.
MySQL Reference Manual
Ponvno stvori i napuni bazu
Osnovni tipovi podataka u SQL-u
Vjezbe 5
Zad 1 Ispisati listu imena studenata zajedno s naslovima kolegija koje su oni upisali. Ispisati naslove onih kolegija koje je upisao studnet Vukovic Janko.
Zad 2 Ispisati listu imena studenata zajedno s nazivima kolegija koje su oni upisali, s time da iskljucimo studente na 3. godini. Ispisati nazive onih kolegija koje je upisao bar jedan student na 2. godini.
Zad 3 Ispisati sve parove nastavnika (po prezimenima) koji sjede u istoj sobi.
Zad 4 Ispisati prezimena i place svih nastavnika koji imaju vecu placu od nastavnika Codd-a.
Zad 5 Ispisati listu imena i prezimena studenata zajedno s naslovima kolegija koje ti studenti nisu upisali.
Zad 6 Ispisati imena i prezimena studenata koji nisu upisali ni jedan kolegij.
Zad 7 Ispisati imena studenata koji su upisali bar jedan kolegij. Ispisati sobe nastavnika koji predaju bar jedan kolegij.
Funkcije u MySQL-u: funkcije 1, funkcije2
Zad 8 Izracunaj izraz e^sinx + e^cosx za x=1.2 rad.
Zad 9 Ispisi place svih nastavnika u stranoj valuti, kao cijeli broj. Jedinica strane valute vrijedi kao 7.8 jedinica domace.
Zad 10 Ispisi parove studenata koji su se rodili istog dana u tjednu.
Zad 11 Prikazi uspjeh studenata koji su polozili predmet s sifrom 56001 u obliku histograma (veca ocjena-dulji niz zvjezdica. )
Zad 12 Ispisite svoje korisnicko ime i trenutnu verziju MySQL-a.
Vjezbe 6
Grupne funkcije u MySQL-u: funkcije3
Zad 13 Ispisati: zbroj svih placa nastavnika, prosjecnu placu nastavnika, ime i placu nastavnika s najvecom placom.
Zad 14 Ispisati: broj studenata na 1. godini, broj studenata na 2. godini, broj svih godina, broj svih studenata.
Zad 15 Ispisati koliko studenata ima na svakoj godini studija.
Zad 16 Ispisati maksimum, minimum i raspon place za nastavnike iz svake od soba. Zatim ispisati iste podatke samo za sobu 127.
Zad 17 Ispisite srednje ocjene i standardne devijacije za ocjene po svim kolegijima.
Zad 18 Ispisite maksimalnu ocjenu koju su studenti s pojedine godine ostvarili iz pojedinog kolegija.
Zad 19 Ispisite prosjecnu placu za nastavnike iz iste sobe, no samo za sobe u kojima ima vise od jednog nastavnika.
Zad 20 Zbrojite place po sobama i ispisite rang listu soba po placama.
Zad 21 Ispisite imena i place za tri najbolje placena nastavnika.
Naredba JOIN
Vjezbe 7-cuvanje integriteta baze
Pojam integritet baze odnosi se na korektnost i konzistentnost podataka - odnosno, mi pazimo da podatci u nasoj bazi budu i ostanu konzistentni i tocni. To osiguravamo tako da stvorimo tablice na nacin koji ce osigurati da polja kljuceva ne mogu ostati nepopunjena i koji ce omogucavati automatsku provjeru svojstva stranog kljuca. Dodatni sredstvo koje osigurava korektnost su transakcije.
Zad 1 Stvorite nove tablice STUDENT, NASTAVNIK, PREDMET, UPISAO, ali tako da u sto vecoj mjeri stitite integritet. Napunite nove tablice podatcima iz demo-baze fakultet.
Rjesenje:
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 nacin:
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;
Vazno: kada smo ovako stvorili tablice moramo ih puniti bas tim redoslijedom, jer traze strani kljuc.
Transakcija je niz naredbi koji prevodi bazu iz jednog konzistentnog stanja u drugo. Medjustanja (stanja izmedju pojedinih naredbi unutar transakcije) mogu biti nekonzistentna. Zato transakcija mora biti izvrsena ili cijela ili ne smije biti uopce izvrsena. Znaci, kada zelimo da se neke naredbe izvrse "u komadu" (npr, recimo da zelimo uplatiti neki iznos sa naseg racuna na racun nekog poduzeca, ne bismo htjeli da se operacija oduzimanja novaca s naseg racuna provede prije nego sto budemo sigurni da ce se provesti i operacija uplate na racun primatelja) takve naredbe pisemo unutar transakcije. Transakciju pocinjemo naredbom SET AUTOCOMMIT=0; (koja prebacuje SQL iz defaultnog stanja u kome se svaka naredba izvrsava odmah nakon sto je napisana) i naredbom BEGIN; (koja oznacava pocetak transakcije). Nakon toga pisemo naredbe koje su dio transakcije i kada smo gotovi pisemo naredbu COMMIT; (ako zelimo izvrsiti citavu transakciju) ili ROLLBACK; (ako ne zelimo da se transakcija izvrsi). Na kraju vracamo SET AUTOCOMMIT=1;. Da bi mogli koristiti transakcije, tablice moraju biti tipa "InnoDB".
Zad 2 Sastavite transakciju kojom se novcani iznos od 1 000 kuna prebacuje iz place nastavnika Cantor-a u placu nastavnika Codd-a. Pod cuvanjem konzistencije smatramo cinjenicu da je ukupan zbroj placa ostao isti.
Rjesenje:
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;
(mozete jos nakon svake naredbe pisati SELECT * FROM NASTAVNIK; da vidite efekt onoga sto se dogadja).
Vjezbe 8 -zastita podataka
Vazno je svoje baze i podatke u njima zastititi od neovlastenog koristenja. U SQL-u imamo naredbe GRANT (za dodjelu) i REVOKE (za oduzimanje) pomocu kojih se pojedinim korisnicima dodjeljuju ili oduzimaju ovlastenja. Ovlastenja se mogu dati za citavu 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 retcima ili atributima. Uobicajeno se dodjeljuju ovlastenja za: SELECT, INSERT, DELETE, UPDATE, ALTER, CREATE, DROP, ALL (odnosi se na sva ovlastenja)...
Ovlastenja se pridruzuju pojedinim korisnicima, odnosno njihovim korisnickim imenima. Kako je MySQL zamisljen da se prvenstveno koristi na internetu, to su u MySQLu korisnicka imena oblika ime_korisnika@ime_stroja_na_kojem_korisnik_trenutno_radi, odnosno isti korisnik kada se prijavljuje sa razlicitih racunala moze imati razlicita ovlastenja.
(Rjesenja iducih zadataka necete moci izvrsiti jer nemate administratorske ovlasti nad bazama koje se koriste. Naravno, slicne stvari mozete isprobati na svojoj vlastitoj bazi.)
Zad 3 Napisite naredbu kojom se neregistiriranom (anonymous) korisniku, prijavljenom sa studentskog racunala, dozvoljava pretrazivanje demo baze fakultet.
Rjesenje:
GRANT SELECT ON fakultet.* TO ' '@localhost;
Zad 4 Napisite naredbe kojima se stvara korisnik somestudent s lozinkom loz000, koji se prijavljuje sa studentskog racunala, moze raditi sto zeli u svojoj bazi s imenom somestudent, smije pretrazivati demo bazu fakultet i smije ucitavati ili ispisivat ASCII datoteke.
Rjesenje:
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;
Zad 5 Oduzmite korisniku somestudent pravo citanja tabele PREDMET u demo bazi fakultet.
Rjesenje:
REVOKE SELECT ON fakultet.NASTAVNIK FROM somestudent@localhost;