Baze podataka - vježbe

Autor originala je Maja Karaga.

• Analiza potreba
• Normalizacija
• Jednostavni upiti
• Kreiranje i modificiranje tablica
• Složeni upiti
• Funkcije
• Grupirajući upiti
• Čuvanje integriteta baze
• Zaštita podataka
• Neki korisni linkovi

Za praćenje vježbi vezanih za SQL (od "Jednostavni upiti" nadalje), te za izradu druge zadaće, trebat će Vam korisnički račun za pristup MySQL bazi na računalu student. To nije isto što i korisnički račun za pristup računalnom sustavu na PMF-MO-u, nego račun za pristup MySQL-u morate posebno otvoriti. Korisnički račun za pristup MySQL-u kreirate tako da Vašem asistentu pošaljete e-mail sa subjectom otvoriti bazu, sljedećeg sadržaja:

CREATE DATABASE username;

GRANT ALL ON username.* TO username@localhost
IDENTIFIED BY 'lozinka';

GRANT SELECT ON fakultet.* TO username@localhost;

GRANT SELECT ON mangerdva.* TO username@localhost;

GRANT FILE ON *.* TO username@localhost;

Pri čemu username treba zamijeniti svojim korisničkim imenom (na svih 6 mjesta), a lozinka zamijeniti svojom željenom lozinkom. Prilikom utipkavanja lozinke pripazite da ne obrišete jednostruke navodnike - oni moraju okruživati lozinku. Lozinka se smije sastojati od znamenki te malih i velikih slova engleske abecede i mora biti dugačka između 4 i 8 znakova. Nakon slanja e-maila, u roku tjedan dana Vaš račun će biti kreiran.

Vježbe 1: Analiza potreba

Baza podataka za bolnicu

Pacijenti koji zauzimaju sobe

Pacijent se obično smješta u bolničku sobu prilikom dolaska u bolnicu. Svaka soba može primiti mnogo pacijenata, i imamo sobe različitih tipova. Konzultanti (stariji kirurzi) bolnice smiju imati i svoje pacijente, koji su smješteni u jednokrevetnim privatnim sobama. Informacije koje treba pamtiti o pacijentu uključuju jedinstveni broj zdravstvenog osiguranja, ime, adresu itd.

Medicinske sestre zadužene za sobe

Sestra može ili ne mora biti zadužena za sobu. Pritom jedna sestra može biti zadužena za najviše jednu sobu, no za istu sobu može biti zaduženo više sestara. Sestra je jednoznačno određena svojom identifikacijskom oznakom.

Operacije koje se obavljaju nad pacijentima

Nad istim pacijentom može se obaviti više 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 također mogu obavljati operacije ili asistirati. Informacije o jednom kirurgu su ime (pretp. jedinstveno), adresa, br. telefona itd. Svaki konzultant ima svoju specijalnost.

Operacijske sale u kojima se obavljaju operacije

Jedna operacija se odvija u samo jednoj sali, no ista sala može biti mjesto mnogih operacija. Svaka sala ima svoj identifikacijski broj. Neke sale su specijalno opremljene za neke vrste operacija.

Medicinske sestre zadužene za sale

Sestra može ili ne mora biti zadužena za salu, no ne može biti zadužena za više od jedne sale. Za jednu salu može biti zaduženo mnogo sestara.

Rješenje

• Model entiteta i veza
  
• Reducirani Chenov dijagram
• Obavezno članstvo

Vježbe 2: Normalizacija

1. Promatramo zapis o suradniku neke firme i njegovoj djeci:

   SURADNIK
   MATIČNI_BROJ_SURADNIKA	IME_I_PREZIME	GOD_ROĐENJA_SURADNIKA	IME_DJETETA	GOD_ROĐENJA_DJETETA

   Pretvorite taj zapis u 1NF.
   
   Rješenje:

   SURADNIK
   MATIČNI_BROJ_SURADNIKA	IME_I_PREZIME	GOD_ROĐENJA_SURADNIKA

   DIJETE
   JMBG_DJETETA	IME_DJETETA	GOD_ROĐENJA_DJETETA

   IMA_DIJETE
   MATIČNI_BROJ_RADNIKA	JMBG_DJETETA

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_DIJELA	BR_DOBAVLJAČA	NAZIV_DIJELA	NAZIV_DOBAVLJAČA	ADRESA_DOBAVLJAČA	CIJENA

   Prebacite ovu relaciju u 2NF.
   
   Rješenje:

   DOBAVLJAČ
   BR_DOBAVLJAČA	NAZIV_DOBAVLJAČA	ADRESA_DOBAVLJAČA

   DIO
   BR_DIJELA	NAZIV_DIJELA

   PRODAJE
   BR_DOBAVLJAČA	BR_DIJELA	CIJENA

3. Suradnici neke firme rade na raznim projektima. Pritom jedan suradnik radi na točno jednom projektu. Situacija je opisana relacijom

   SURADNIK
   MATIČNI_BROJ	IME_I_PREZIME	PLAĆA	BROJ_PROJEKTA	ROK_ZAVRŠETKA_PROJEKTA

   Prebacite relaciju u 3NF.
   
   Rješenje:

   SURADNIK
   MATIČNI_BROJ	IME_I_PREZIME	PLAĆA	BROJ_PROJEKTA

   PROJEKT
   BROJ_PROJEKTA	ROK_ZAVRŠETKA_PROJEKTA

4. 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 sljedećim zapisom:

   ISPORUKA
   BR_ISPORUKE	DATUM_SLANJA	BR_KUPCA	IME_KUPCA	ADRESA_KUPCA	BR_PROIZVODA	NAZIV_PROIZVODA

   Pretvorite taj zapis u 3NF.
   
   Rješenje:

   KUPAC
   BR_KUPCA	IME_KUPCA	ADRESA_KUPCA

   PROIZVOD
   BR_PROIZVODA	NAZIV_PROIZVODA

   ISPORUKA
   BR_ISPORUKE	DATUM_SLANJA	BR_KUPCA

   PROIZVOD_U_ISPORUCI
   BR_PROIZVODA	BR_ISPORUKE

5. 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.
   
   Rješenje:

   KOLEGIJ
   BR_KOLEGIJA	BR_PREDAVAONICE

   RASPORED
   BR_KOLEGIJA	VREMENSKI_TERMIN

Vježbe 3: Jednostavni upiti

1. Ispišite čitav sadržaj svake od 4 tablice u demo bazi.
2. Ispišite imena i datume rođenja za sve studente.
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.
4. Ispišite OIB-e nastavnika koji predaju bar jedan kolegij.
5. Ispišite sve podatke o nastavnicima iz sobe 127. Ispišite imena nastavnika s plaćom većom od 10000. Ispišite imena nastavnika koji ne sjede u sobi 315.
6. Ispišite sve podatke o nastavnicima koji sjede u sobama 127 i 315. Ispišite sve podatke o nastavnicima koji ne sjede ni u sobi 127 ni u sobi 315.
7. Ispišite sve podatke o nastavnicima koji zarađuju između 10000 i 12000 (uključivo). Ispišite sve podatke o nastavnicima koji zarađuju manje od 9000 ili više od 11000.
8. Ispišite JMBAG-ove svih studenata koji su iz kolegija sa šifrom 72001 dobili ocjenu izmedju 3 i 5. Ispišite JMBAG-ove studenata koji su bar iz jednog kolegija dobili ocjenu između 3 i 5.
9. Ispišite imena studenata koja počinju slovom 'M', zatim sva imena studenata u kojima se pojavljuje niz znakova 'ar'. Na kraju, Ispišite imena studenata kojima se kao treće slovo pojavljuje 'a'.
10. Ispišite podatke o nastavnicima, uzlazno sortirane po plaći. Ispišite sve podatke o nastavnicima, silazno sortirano po plaći.
11. Ispišite prezimena studenata rođenih 1992. godine, koji su na 1. godini studija. Ispis treba biti abecedno sortiran po prezimenima.
12. Ispišite podatke o nastavnicima koji zarađuju izmedju 10000 i 12000, tako da im prezimena budu sortirana po abecedi. Ispišite podatke o svim nastavnicima, sortirano po sobama, tako da nastavnici iz iste sobe budu abecedno poredani po prezimenima.
13. Ispišite studente sortirano po starosti (počevši od najstarijeg).
14. Ispišite imena i udvostručene plaće nastavnika, sortirano po udvostručenim plaćama.

Vježbe 4: Kreiranje i modificiranje tablica

1. Stvorite tablice koje odgovaraju onima u demo bazi fakultet.

   		CREATE TABLE STUDENT
   		   (JMBAG NUMERIC(10) UNSIGNED NOT NULL,
   		    PREZIMES CHAR(20),
   		    IMES CHAR(20),
   		    DATR DATE,
   		    GODINA ENUM('1','2','3','4','5'),
   		    PRIMARY KEY(JMBAG));
   
   		CREATE TABLE NASTAVNIK
   		   (OIB NUMERIC(11) UNSIGNED NOT NULL,
   		    PREZIMEN CHAR(20),
   		    IMEN CHAR(20),
   		    BRSOBE NUMERIC(3) UNSIGNED,
   		    PLACA NUMERIC(5) UNSIGNED,
   		    PRIMARY KEY(OIB));
   
   		CREATE TABLE PREDMET
   		   (SIFRA NUMERIC(5) UNSIGNED NOT NULL,
   		    NASLOV CHAR(80),
   		    OIB NUMERIC(11) UNSIGNED,
   		    SEMESTAR ENUM('Z','L'),
   		    ECTS NUMERIC(2) UNSIGNED,
   		    PRIMARY KEY(SIFRA));
   
   		CREATE TABLE UPISAO
   		   (JMBAG NUMERIC(10) UNSIGNED NOT NULL,
   		    SIFRA NUMERIC(5) UNSIGNED NOT NULL,
   		    DATU DATE,
   		    OCJENA ENUM('1','2','3','4','5'),
   		    PRIMARY KEY(JMBAG,SIFRA));
   	 

2. 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 tab-ovima. Nazivi datoteka su datanastavnik i datapredmet. Inicijalizirajte sadržaj za vlastite tabele NASTAVNIK i PREDMET učitavajući podatke iz odgovarajućih datoteka.
3. Inicijalizirajte sadržaj za vlastite tabele UPISAO i STUDENT koristeći podatke iz odgovarajućih tabela demo baze fakultet.
4. Upišite u bazu novog nastavnika s OIB-om 50000000000, prezimenom Mirkovic, imenom Mirko, koji će sjediti u sobi 101 i imat će plaću 8000 kuna.
5. Realizirajte situaciju u kojoj je nastavnik s OIB-om 44102179316 (Klein) dobio otkaz. Sve njegove kolegije preuzima nastavnik s OIB-om 50000000000 (Mirkovic).
6. Nastavnika Pascala premjestite u sobu 201 i dajte mu povišicu u visini 20% dosadašnje plaće.
7. Poništite upise svih kolegija koji nisu položeni.
8. Dajte svim nastavnicima plaću od 10000
9. Stvorite indeks po primarnom ključu za svaku od 4 tablice.
10. Stvorite indekse kojima se ubrzava: traženje studenata na zadanoj godini, traženje nastavnika koji sjede u zadanoj sobi.

Vježbe 5: Složeni upiti

1. Ispišite listu imena studenata zajedno s naslovima kolegija koje su oni upisali. Ispišite naslove onih kolegija koje je upisao studnet Vukovic Janko.
2. Ispišite listu imena studenata zajedno s nazivima kolegija koje su oni upisali, ali bez studanata 3. godine. Ispišite nazive onih kolegija koje je upisao bar jedan student 2. godine.
3. Ispišite sve parove nastavnika (po prezimenima) koji sjede u istoj sobi. (Bez dupliciranih parova - parove (A,B) i (B,A) smatramo jednakima i bez "simetričnih" parova (A,A).)
4. Ispišite prezimena i plaće svih nastavnika koji imaju veću plaću od nastavnika Codd-a.
5. Ispišite listu imena i prezimena studenata zajedno s naslovima kolegija koje ti studenti nisu upisali.
6. Ispišite imena i prezimena studenata koji nisu upisali ni jedan kolegij.
7. Ispišite imena studenata koji su upisali bar jedan kolegij. Ispišite sobe nastavnika koji predaju bar jedan kolegij.

Vježbe 6: Funkcije

1. Izračunajte izraz e^{sin x} + e^{cos x} za x=1.2.
2. Ispišite plaće svih nastavnika u stranoj valuti, kao cijeli broj. Jedinica strane valute vrijedi kao 7.8 jedinica domaće.
3. Ispišite parove studenata koji su se rodili istog dana u tjednu. (Bez dupliciranih parova - parove (A,B) i (B,A) smatramo jednakima i bez "simetričnih" parova (A,A).
4. Prikažite uspjeh studenata koji su položili predmet sa šifrom 56001 u obliku histograma (veća ocjena - dulji niz zvjezdica).
5. Ispišite svoje korisničko ime i trenutnu verziju MySQL-a.

Vježbe 7: Grupirajući upiti

1. Ispišite zbroj svih plaća nastavnika, prosječnu plaću nastavnika, ime i plaću nastavnika s najvećom plaćom.
2. Ispišite broj studenata na 1. godini, broj studenata na 2. godini, broj svih godina, broj svih studenata.
3. Ispišite koliko studenata ima na svakoj godini studija.
4. Ispišite maksimum, minimum i raspon plaće za nastavnike iz svake od soba. Zatim ispišite iste podatke samo za sobu 127.
5. Ispišite srednje ocjene i standardne devijacije za ocjene po svim kolegijima.
6. Ispišite maksimalnu ocjenu koju su studenti s pojedine godine ostvarili iz pojedinog kolegija.
7. Ispišite prosječnu plaću za nastavnike iz iste sobe, no samo za sobe u kojima ima više od jednog nastavnika.
8. Zbrojite plaće po sobama i ispišite rang listu soba po plaćama.
9. Ispišite imena i plaće za tri najbolje plaćena nastavnika.

Vježbe 8: Čuvanje integriteta baze

Pojam integritet baze odnosi se na korektnost i konzistentnost podataka - odnosno, mi pazimo da podaci 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 će omogućavati automatsku provjeru svojstva stranog ključa. Dodatno sredstvo koje osigurava korektnost su transakcije.

Primjer 1: Stvorit ćemo nove tablice STUDENT, NASTAVNIK, PREDMET i UPISAO, ali tako da u što većoj mjeri štitimo integritet. Nove tablice ćemo napuniti podacima iz demo baze fakultet.

Prvo moramo obrisati stare tablice naredbama:

DROP TABLE STUDENT;
DROP TABLE NASTAVNIK;
DROP TABLE PREDMET;
DROP TABLE UPISAO;

Nakon toga stvaramo tablice na novi način:

CREATE TABLE STUDENT
   (JMBAG NUMERIC(10) UNSIGNED NOT NULL,
    PREZIMES CHAR(20),
    IMES CHAR(20),
    DATR DATE,
    GODINA ENUM('1','2','3','4','5'),
    PRIMARY KEY(JMBAG))ENGINE=INNODB;

CREATE TABLE NASTAVNIK
   (OIB NUMERIC(11) UNSIGNED NOT NULL,
    PREZIMEN CHAR(20),
    IMEN CHAR(20),
    BRSOBE NUMERIC(3) UNSIGNED,
    PLACA NUMERIC(5) UNSIGNED,
    PRIMARY KEY(OIB))ENGINE=INNODB;

CREATE TABLE PREDMET
   (SIFRA NUMERIC(5) UNSIGNED NOT NULL,
    NASLOV CHAR(80),
    OIB NUMERIC(11) UNSIGNED,
    SEMESTAR ENUM('Z','L'),
    ECTS NUMERIC(2) UNSIGNED,
    PRIMARY KEY(SIFRA),
    FOREIGN KEY (OIB) REFERENCES NASTAVNIK(OIB))ENGINE=INNODB;

CREATE TABLE UPISAO
   (JMBAG NUMERIC(10) UNSIGNED NOT NULL,
    SIFRA NUMERIC(5) UNSIGNED NOT NULL,
    DATU DATE,
    OCJENA ENUM('2','3','4','5'),
    PRIMARY KEY(JMBAG,SIFRA),
    FOREIGN KEY (JMBAG) REFERENCES STUDENT(JMBAG),
    FOREIGN KEY (SIFRA) REFERENCES PREDMET(SIFRA))ENGINE=INNODB;

Na kraju napunimo tablice podacima 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č.

Transakcija je niz naredbi koji prevodi bazu iz jednog konzistentnog stanja u drugo. Međustanja (stanja između pojedinih naredbi unutar transakcije) mogu biti nekonzistentna. Zato transakcija mora biti izvršena cijela ili ne smije uopće biti izvršena. Znači, kada želimo da se neke naredbe izvrse "u komadu" (recimo da želimo 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 što 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 početnog stanja u kome se svaka naredba izvršava odmah nakon što je napisana u stanje u kojem se čeka eksplicitni zahtjev za izvršavanjem naredbi) 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;. Za korištenje transakcija, tablice moraju biti tipa InnoDB.

Primjer 2: Transakcija kojom se novčani iznos od 1000 kuna prebacuje iz plaće nastavnika Cantora u plaću nastavnika Codda. Pod čuvanjem konzistentnosti smatramo činjenicu da je ukupan zbroj plaća ostao isti.

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 nakon svake naredbe pisati SELECT * FROM NASTAVNIK; kako biste vidjeli efekt onoga sto se događa).

Vježbe 9: Zaštita podataka

Važno je svoje baze i podatke u njima zaštititi od neovlaštenog korištenja. U SQL-u imamo naredbe GRANT i REVOKE pomoću kojih se pojedinim korisnicima dodjeljuju, odnosno oduzimaju ovlaštenja. Ovlaštenja se 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 čak samo nekim recima ili atributima. Uobičajeno se dodjeljuju ovlaštenja za SELECT, INSERT, DELETE, UPDATE, ALTER, CREATE, DROP i ALL. Ovlaštenja se pridružuju pojedinim korisnicima, odnosno njihovim korisničkim imenima. MySQL je zamišljen da se prvenstveno koristi na Internetu, pa su u MySQL-u korisnička imena oblika ime_korisnika@naziv_stroja_na_kojem_korisnik_trenutno_radi, odnosno isti korisnik kada se prijavljuje sa različitih računala može imati različita ovlaštenja.

Napomena: Dolje navedene primjere nećete moći izvršiti jer nemate administratorske ovlasti nad bazama koje se koriste. Naravno, slične stvari možete isprobati na svojoj vlastitoj bazi.

Primjer 1: Naredba kojom se neregistiriranom (anonymous) korisniku, prijavljenom sa lokalnog računala (tj. računala na kojem se nalazi baza) dozvoljava pretraživanje baze fakultet.

GRANT SELECT ON fakultet.* TO ' '@localhost;

Primjer 2: Niz naredbi kojima se stvara korisnik somestudent s lozinkom loz000, koji se prijavljuje sa lokalnog 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.

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;

Primjer 3: Naredba kojom se korisniku somestudent oduzima pravo čitanja tablice PREDMET u bazi fakultet.

REVOKE SELECT ON fakultet.NASTAVNIK FROM somestudent@localhost;

Dodatak: korisni linkovi

• MySql online dokumentacija.
• SQL tutorial na w3schools.com.