Uvod u baze podataka. Modeli podataka. Relacioni model (strukturni deo)

• Baze podataka, prvi primer: pojednostavljena evidencija studentske službe (rezultati ispita)
• Baze podataka, definicija.
  • Arhitektura (fizički ili unutrašnji nivo, logički nivo ili model podataka, spoljašnji nivo ili podmodeli).
  • Model podataka (strukturni deo, manipulativni deo, integritetni deo).
  • Istorijski pregled modela podataka (hijerarhijski, mrežni, relacioni, objektno-orijentisani)
• Relacioni model podataka. Delovi (relacionog) modela:
  • Strukturni deo:

    Strukturni deo relacionog modela

    koncepti	matematička terminologija (relacioni model)	informatička terminologija (ACCESS)
    entitet (engl. entity), objekat (engl. object)	n-torka (engl. tuple)	red u tabeli (engl. table row, datasheet row), slog (engl. record)
    tip entiteta (engl. entity type), tip objekata (engl. object type)	relacija (engl. relation)	tabela (engl. table, datasheet)
    atribut entiteta, svojstvo entiteta (engl. entity attribute)	atribut relacije (engl. relation attribute)	kolona u tabeli (engl. table column, datasheet column), polje sloga (engl. field)
    odnos između tipova entiteta (engl. relationship between entity types)	relacija (engl. relation)	tabela (engl. table, datasheet)

  • Primer realizacije strukturnog dela u relacionom modelu podataka: shema baze podataka Studenti2009

    STUDENTI(ime,prezime,indeks,smer,status)
    ISPITI(sifra_predmeta,indeks,put,datum,ocena,sifra_nastavnika)
    PREDMETI(sifra_predmeta,naziv,smer,statut)
    NASTAVNICI(sifra_nastavnika,ime,prezime,zvanje,katedra)
    

    

  • Manipulativni deo: SQL iskazi (INSERT, SELECT, UPDATE, DELETE, tj. unos, pretraga, ažuriranje i brisanje podataka)
  • Integritetni deo: opšta i specifična pravila integriteta.

Relacioni model (integritetni deo)

• Opšta pravila integriteta:
  • integritet entiteta (primarni ključ)
  • referencijalni integritet (strani ključ).
  Primeri narušavanja opštih pravila integriteta:
  • [Narušen integritet entiteta pri unosu]: Unos sloga sa praznom vrednošću primarnog ključa (npr. unos podataka o studentu bez vrednosti za indeks).
  • [Narušen integritet entiteta pri unosu]: Unos sloga sa već postojećom vrednošću primarnog ključa (npr. unos podataka o studentu sa vrednošću indeksa koja već postoji u tabeli).
  • [Narušen integritet entiteta pri ažuriranju]: Slično, ažuriranje sloga pri kom se duplira ili briše vrednost primarnog ključa (npr. izmena indeksa studenta tako da se vrednost duplira ili briše).
  • [Narušen referencijalni integritet pri unosu]: U zavisnoj tabeli je unet slog sa sa vrednošću stranog ključa zavisne tabele koja ne postoji kao vrednost primarnog ključa odnosne tabele (npr. u zavisnoj tabeli Ispiti uneti su podaci o polaganju studenta kog nema u odnosnoj tabeli Studenti).
  • [Narušen referencijalni integritet pri ažuriranju]: U zavisnoj tabeli je izmenjen slog tako da nova vrednost stranog ključa ne postoji kao vrednost primarnog ključa odnosne tabele (npr. izmenom se u zavisnoj tabeli Ispiti nalaze podaci o polaganju studenta kog nema u odnosnoj tabeli Studenti).
  • [Narušen referencijalni integritet pri brisanju]: U odnosnoj tabeli je obrisan slog sa vrednošću primarnog ključa za koju postoji odgovarajuća vrednost stranog ključa u zavisnoj tabeli (npr. brisanje podataka o studentu iz odnosne tabele Studenti pri čemu ostaje evidencija o njegovim polaganjima ispita u zavisnoj tabeli Ispiti).
• Mehanizmi za detekciju i sprečavanje narušavanja integriteta baze u ACCESS-u.

Relacioni model (manipulativni deo). Uvod u SQL (upiti nad jednom tabelom)

• SQL jezik za rad sa relacionim bazama podataka i njegova primena u jednom relacionom sistemu.
• SELECT iskaz. Upiti nad jednom tabelom.

  Nekvalifikovani upit	Kvalifikovani upit
  SELECT kolone FROM tabele	SELECT kolone FROM tabele WHERE uslov restrikcije (ograničenja);

• Primeri:
  1. Imena i prezimena svih studenata u evidenciji, koji su sa smera R (vrednost se mora navesti onako kako je zapisana u bazi, dakle, latinicom R).
  2. SQL kao digitron i aliasi: Izračunati proizvod 11*24 i kolonu tabele sa rezultatom nazvati proizvod.
  3. Imena i prezimena svih studenata u evidenciji.
  4. Svi podaci o studentima sa smera R (ime, prezime, indeks, smer, status):
     • sa promenom redosleda kolona;
     • bez promene redosleda i bez navođenja svih imena kolona.
  5. Spisak različitih smerova na kojima su evidentirani studenti.
  6. Za sva polaganja ispita ispisati indeks studenta koji je polagao ispit i odgovarajuću šifru predmeta.
• Kucanje upita:
  1. Create / Query Design;
  2. klik na dugme Close novootvorenog prozora;
  3. desni klik na title bar novootvorenog prozora;
  4. klik na SQL view. (Ako imate staru verziju programa ACCESS u kome se veličina fonta upita ne može povećati u prozoru SQL view, kako biste izbegli moguće greške (npr, razmaci u imenima kolona i tabela), najbolje je upit otkucati u nekom programu za kucanje običnog teksta kakav je Notepad++, a onda ga kopirati u prozor SQL view radi testiranja.)
• Tok izvršavanja upita:
  1. FROM-deo (izbor tabele),
  2. WHERE-deo (restrikcija = izbor redova),
  3. SELECT-deo (projekcija = izbor kolona)
• Rezultat izvršavanja upita je ponovo tabela!
• Projekcija
  • * (sve kolone tabele)
  • DISTINCT (eliminacija duplikata redova)
  • Imenovani izrazi i aliasi (AS)
• Osnovni tipovi u SQL-u upotrebljeni u bazi podataka Studenti2009:
  • tekstualni tip (Text) i
  • numerički tip (Number).

Izrazi u SQL-u (brojevi i niske)

• Aritmetički izrazi
  1. Celobrojne i realne konstante (literali). Na primer:

     `2\text{ }-16\text{ }23.56\text{ }-47.39`

  2. Nazivi kolona numeričkog tipa. Na primer: put i ocena u bazi podataka Studenti2009;
  3. Pozivi funkcija (skalarnih i agregatnih) čija je vrednost broj. Na primer: Int(7 / 3);
  4. Ako su `A_1` i `A_2`aritmetički izrazi, onda su to i

     `A_1 + A_2` (zbir celih i realnih brojeva),
     `A_1 - A_2` (razlika celih i realnih brojeva),
     `A_1 * A_2` (proizvod celih i realnih brojeva),
     `A_1 \\ A_2` (količnik pri celobrojnom deljenju),
     `A_1 mod A_2` (ostatak pri celobrojnom deljenju),
     `A_1 // A_2` (količnik pri realnom deljenju),
     `A_1 \^ A_2` (stepen),
     kao i `(A_1)` i `(A_2)`.

  5. Ništa osim (A1)–(A4) nije aritmetički izraz.
  Primer: 2*(ocena + LEN(indeks)).
• Niske. Operator dopisivanja (konkatenacije) &
• Primeri:
  1. Ispisati imena i prezimena studenata u jednoj koloni i to u formatu:
     • ime razmak prezime (Hari Poter)
     • prezime razmak ime (Poter Hari)
     • prezime zapeta razmak ime (Poter, Hari)
• Skalarne funkcije za rad sa niskama (LEFT, RIGHT, MID, LEN, TRIM).
• Primeri:
  1. Ispisati prezimena studenata i dužine prezimena.
  2. Ispisati prva tri slova svakog imena studenta.
  3. Ispisati poslednja tri slova svakog imena studenta.
  4. Ispisati imena i prezimena studenata koji su upisali fakultet 98. godine.
  5. Ispisati imena i prezimena studenata i njihove godine upisa kao četvorocifrene brojeve (1998).
• Operator LIKE i džoker znaci (*, ?). Razlike u implementaciji standardnog SQL-a (%, _ )
• Primeri:
  1. Studenti čije prezime počinje slovom T.
  2. Studenti koji su upisali fakultet 1998. godine.
• Tekstualni izrazi
  1. Niske nad azbukom koju čine elementi karakterskog skupa Unicode. Na primer:

     brat Đorđe Miško 125/95 R

  2. Nazivi kolona tekstualnog tipa. Na primer: ime, prezime, indeks, smer i status tabele Studenti u bazi podataka Studenti2009;
  3. Pozivi funkcija (skalarnih i agregatnih) čija je vrednost niska. Na primer: RIGHT(indeks, 2) i RIGHT(TRIM(indeks), 2) ;
  4. Ako su `T_1` i `T_2` tekstualni izrazi, onda su to i
     

     `T_1 & T_2`,
     kao i `(T_1)` i `(T_2)`.

  5. Ništa osim (T1)–(T4) nije tekstualni izraz.
  Primer: '19' & RIGHT(TRIM(indeks), 2).

Izrazi u SQL-u (relacijski i logički). Spajanje dve tabele

• Relacijski operatori (=, <>, <, <=, >, >=). Poređenja brojeva i niski. Relacijski izrazi:
  1. Ako su `A_1` i `A_2` aritmetički izrazi, onda su

     `A_1 = A_2`,
     `A_1` <> `A_2`,
     `A_1` < `A_2`,
     `A_1`<= `A_2`,
     `A_1`> `A_2` i
     `A_1`>= `A_2`

     relacijski izrazi.
  2. Ako su `T_1` i `T_2` tekstualni izrazi, onda su

     `T_1 = T_2`,
     `T_1`<> `T_2`,
     `T_1` < `T_2`,
     `T_1`<= `T_2`,
     `T_1`> `T_2` i
     `T_1`>= `T_2`

     relacijski izrazi.
  3. Uopšte, ako su `I_1` i `I_2` izrazi istog tipa, onda su

     `I_1 = I_2`,
     `I_1`<> `I_2`,
     `I_1` < `I_2`,
     `I_1`<= `I_2`,
     `I_1`> `I_2` i
     `I_1`>= `I_2`

     relacijski izrazi.
• Primeri:
  1. Indeksi studenata koji su neki ispit polagali bar dva puta.
  2. Indeksi studenata koji su neki ispit polagali manje od 5 puta.
• Logički operatori (AND, OR, NOT)
• Logički operator BETWEEN … AND
• Logički operator IN i lista vrednosti (pripadnost skupu)
• Logički izrazi:
  1. Logičke konstante TRUE i FALSE su logički izrazi (realizovani kao celi brojevi -1 i 0).
  2. Imena kolona koje sadrže logičke vrednosti (Yes/No u ACCESS-u) su logički izrazi.
  3. Pozivi funkcija čije su vrednosti logičke konstante su logički izrazi. Na primer: IsNumeric(ocena) ili IsDate(datum).
  4. Ako su x, y i z izrazi istog tipa, (aritmetički ili tekstualni), onda je x BETWEEN y AND z logički izraz. Na primer:

     ocena BETWEEN 7 AND 9

  5. Ako su t i s tekstualni izrazi, onda je t LIKE s logički izraz. Na primer:

     indeks LIKE '*/98*'

  6. Ako je a izraz i L lista vrednosti istog tipa kao a, onda je i a IN L logički izraz. Na primer:

     ocena IN (9, 10)

  7. Ako su `L_1` i `L_2` logički izrazi, onda su to i

     `L_1` AND `L_2`,
     `L_1` OR `L_2`,
     NOT `L_1` i NOT `L_2`,
     kao i `(L_1)` i `(L_2)`.

     Na primer:

     smer = 'R' AND status = 'S'

  8. Ništa osim (L1)–(L8) nije tekstualni izraz.
• Prirodno-jezički zapis negacije operatora LIKE, BETWEEN … AND i IN:
  • t NOT LIKE s umesto NOT(t LIKE s). Na primer:

    ocena NOT BETWEEN 7 AND 9

  • x NOT BETWEEN y AND z umesto NOT(x BETWEEN y AND z). Na primer:

    indeks NOT LIKE '*/98*'

  • a NOT IN L umesto NOT(a IN L). Na primer:

    ocena NOT IN (9, 10)

• Restrikcija je zapravo konstrukcija WHERE logički_izraz. Zadaci:
  1. Indeksi studenata koji su neki ispit polagali bar dva puta, a manje od 5 puta
     • korišćenjem logičkih operatora,
     • korišćenjem operatora BETWEEN … AND,
     • korišćenjem operatora IN.
  2. Šifre predmeta iz kojih je bar jedan ispit položen.
  3. Indeksi studenata i šifre predmeta koje su ti studenti polagali u februarskom ispitnom roku 1999. godine;
• Spajanje dve tabele
  • Primer: Imena i prezimena svih studenata u evidenciji, koji su neki ispit položili sa osmicom.
  • Aliasi za tabele (AS)
  • Tok izvršavanja upita:
    1. FROM-deo (Dekartov proizvod tabela),
    2. WHERE-deo (prirodno spajanje i dodatna ograničenja, restrikcija = izbor redova),
    3. SELECT-deo (projekcija - izbor kolona)
  • Primer: Imena i prezimena svih studenata u evidenciji, koji su neki ispit položili kod nastavnika sa katedre Grifindor (vrednost se mora navesti onako kako je zapisana u bazi, dakle, latinicom Grifindor).
• Uputstvo za I domaći

  U okviru samostalnog rada kod kuće, student treba da napravi upite koji ispisuju:

  1. imena i prezimena nastavnika sa katedre Grifindor;
  2. Indeksi studenata koji su polagali predmet sa šifrom 1A01 bar dva puta;
  3. Šifre nastavnika kod kojih su studenti polagali predmet sa šifrom 1A01 ili predmet sa šifrom 2B01.
  4. smerove na kojima se slušaju predmeti čije ime počinje sa Istorija.
  5. smerove na kojima se slušaju predmeti čije ime ne počinje sa Istorija;
  6. indekse studenata i šifre nastavnika kod kojih su studenti polagali ispit u periodu februar-maj 1999. godine;
  7. ispisati nazive predmeta sa smerova R, N i G (odnosno R, N i G);

  Razmotriti pomoću kojih sve operatora mogu da se realizuju rešenja. U slučaju da postoji više rešenja, ispisati sva i uporediti rezultate.

Spajanje više tabela. Podupiti i operator IN

• Spajanje više tabela
  • Primer: Imena i prezimena svih studenata u evidenciji, koji su neki ispit položili kod nastavnika sa katedre Grifindor.
• Podupiti (IN)
  • Primer: Imena i prezimena svih studenata u evidenciji, koji su neki ispit položili sa osmicom.
  • Kako bismo problem rešavali 'ručno'?
  • Jedna kolona = lista vrednosti.
    Ako je rezultat izvršavanja upita tabela sa jednom kolonom, to je zapravo lista!
    Svaki operator koji može da se primeni na listu (na primer, IN), može se primeniti i na upit čiji je rezultat samo jedna kolona.
• Uputstvo za II domaći

  U okviru samostalnog rada kod kuće, student treba da napravi upite koji ispisuju:

  1. Ispisati smer predmeta koji je polagao bar jedan vanredan student (status je V, tj. V).
  2. Imena i prezimena svih mogućih parova (različitih) studenata sa istog smera.
  3. Imena i prezimena studenata koji su polagali bar jedan ispit.
  4. Imena i prezimena studenata koji su položili bar jedan ispit.
• Zadatak sa osmicama (za razmišljanje)
  • Imena i prezimena studenata koji imaju bar jednu osmicu.
  • Imena i prezimena studenata koji imaju bar jednu ocenu koja nije osmica.
  • Imena i prezimena studenata koji su polagali bar jedan ispit i nemaju nijednu osmicu.
  • Imena i prezimena studenata koji su polagali bar jedan ispit i na svakom ispitu su dobili osmicu.

Zadatak sa osmicama. Rešenje pomoću operatora IN

• Zagrevanje:
  1. Imena i prezimena studenata koji su polagali bar jedan ispit
  2. Imena i prezimena studenata koji su položili bar jedan ispit
• Zadatak sa osmicama (slučajevi)
  • Imena i prezimena studenata koji imaju bar jednu osmicu.
  • Imena i prezimena studenata koji imaju bar jednu ocenu koja nije osmica.
  • Imena i prezimena studenata koji su polagali bar jedan ispit i nemaju nijednu osmicu.
  • Imena i prezimena studenata koji su polagali bar jedan ispit i na svakom ispitu su dobili osmicu.
• Uputstvo za III domaći

  U okviru samostalnog rada kod kuće, student treba da napravi upite primenom operatora IN koji ispisuju:

  1. Nazivi predmeta iz koga je svaki student koji je polagao taj predmet dobio ocenu veću od 8.
  2. Imena i prezimena nastavnika čiji ispit nije uspeo da položi nijedan student koji je taj ispit polagao.

Predikatski račun I reda (sa jednakošću)

• Kvantifikatori `(\forall, \exists)`.
• Neprazan skup koji je predmet posmatranja nazivamo domen, a njegove elemente objektima. Objekti se označavaju promenljivama ($x$, $y$, $z$, …). Ako promenljive označavaju objekte određenog domena, još se kaže da promenljive uzimaju vrednosti iz tog domena. Domen može biti ceo kosmos ili samo neki neki određeni skup (na primer, svi studenti koji u ovoj školskoj godini slušaju predavanja iz predmeta Baze podataka). Podskupovi domena koji okupljaju objekte sa zajedničkim osobinama zvaćemo kategorije.
• Predikati (relacijski znaci) se koriste:
  1. da se predstavi pripadanje nekog objekta nekoj kategoriji (na primer, $Sx$, $x$ je student) ili
  2. da se predstavi nekakav odnos između više objekata (na primer, $Jxyz$, $x$ polaže $y$ kod $z$; pri tome je potrebno dodatno određenje kojim kategorijama pripadaju $x$, $y$ i $z$, tj. ne podrazumeva se da je $x$ student, $y$ predmet ili ispit, niti da je $z$ nastavnik, dok se to eksplicitno ne navede).
• Najvažniji zakoni i pravila izvođenja iskaznog i predikatskog računa
• Zlatna pravila:
  1. Kvantifikatore ne treba gurati napred (u smislu da su svi kvantifikatori navedeni na početku formule), već ih uvoditi postepeno, tek onda kada su nam stvarno potrebni.
  2. Ako se kvantifikator `\forall` odnosi na složeni iskaz, onda je taj složeni iskaz implikacija (`\Rightarrow`). Ako se kvantifikator `\exists` odnosi na složeni iskaz, onda je taj složeni iskaz konjunkcija (`\wedge`).
• Prevođenje formula sa predikatskog računa I reda na srpski jezik
  • Primer 1: Svaki čovek je živo biće.
    • Predikati: $Cx$ ($x$ je čovek), $Zx$ ($x$ je živo biće)
    • Formula: `(\forall x)(Cx \Rightarrow Zx)`
      (Svaki objekat (element domena), ako je čovek, onda je i živo biće)
  • Primer 2: Neko živo biće je čovek.
    • Predikati: $Cx$ ($x$ je čovek), $Zx$ ($x$ je živo biće)
    • Formula: `(\exists x)(Zx \wedge Cx)`
      (Postoji objekat (element domena) koji je živo biće i čovek)
  • Primer 3: Svaka biblioteka poseduje neku knjigu.
    • Predikati: $Bx$ ($x$ je biblioteka), $Kx$ ($x$ je knjiga), $Pxy$ ($x$ poseduje $y$)
    • Formula: `(\forall x)(Bx \Rightarrow (\exists y)(Kx \wedge Pxy)`
      (Za svaki objekat, ako je on biblioteka, onda postoji objekat koji je knjiga, tako da ta biblioteka poseduje tu knjigu)

Predikatski račun I reda (sa jednakošću). Nastavak

• Prevođenje formula sa srpskog jezika u formulu predikatskog računa I reda
  • Primer 4: Imena i prezimena studenata sa smera R ('R').
    • Rešenje 1:
      • Predikat: $\mathit{Studenti}\left(x\text{,}y\text{,}z\text{,}u\text{,}v\right)$, ($x$ i $y$ su ime i prezime studenta sa brojem indeksa $z$, koji studira na smeru $u$ sa statusom $v$)
      • Traženi skup može da se opiše kao skup uređenih parova oblika $\left(\mathit{neko\_ime}\text{,}\mathit{neko\_prezime}\right)$ takvih da za svaki par postoji $\mathit{neki\_indeks}$ i postoji $\mathit{neki\_status}$, pri čemu su $\mathit{neko\_ime}$, $\mathit{neko\_prezime}$, $\mathit{neki\_indeks}$, smer 'R' i $\mathit{neki\_status}$ u relaciji $\mathit{Studenti}$:

        `{(ii "neko_ime", ii "neko_prezime") | (\exists ii "neki_indeks") (\exists ii "neki_status")`
        `ii Studenti(ii "neko_ime", ii "neko_prezime", ii "neki_indeks", 'R', ii "neki_status")}`

    • Prethodna notacija je komplikovana. Stoga umesto da se student posmatra kao veza (relacija $\mathit{Studenti}$) između više prostih objekata (ime, prezime, indeks, smer, status), tretiramo ga kao jedan složeni objekat sa atributima, pri čemu uvodimo novi predikat $Sx$ ($x$ je student), a atribute studenta $x$ označavamo sa `x.ime`, `x.prezime`, `x.ii "indeks"`, `x.smer` i `x.status`.
    • Rešenje 2: skup uređenih parova oblika `(neko_ime, neko_prezime)` takvih da za svaki par postoji neki student $x$ čije je ime i prezime u oznaci (`x.ime`, `x.prezime`) jednako tom paru, i $x$ je sa smera R.

      `{(ii "neko_ime", ii "neko_prezime") | (\exists x) (Sx \wedge x.ime = ii "neko_ime" \wedge x.prezime = ii "neko_prezime" \wedge ii x.smer='R')}`

    • Rešenje 3: Ako uvedemo dogovor da se prethodno rešenje kraće zapisuje sa

      `{(x.ime, x.prezime) | (\exists x)(Sx \wedge x.smer='R')}`,

      novi zapis se neposredno prevodi u SQL:
      

      SELECT x.ime, x.prezime FROM studenti AS x WHERE x.smer='R';

      odnosno, ako izostavimo alias $x$ (podrazumevajući da znamo da se imena atributa odnose na redove tabele Studenti):

      SELECT ime, prezime FROM studenti WHERE smer='R';

  • Primer 5: Imena i prezimena studenata koji imaju bar jednu osmicu.
    • Predikati:
      1. $Sx$ ($x$ je student). Atributi: $x$.ime, $x$.prezime, $x$.indeks, $x$.smer, $x$.status
      2. $Ix$ ($x$ je ispit). Atributi: $x$.sifra_predmeta, $x$.indeks, $x$.datum, $x$.ocena, $x$.sifra_nastavnika, $x$.put
    • T1: Ime i prezime studenta $x$ takvog da postoji ispit $y$ koji je polagao student $x$ i na kome je dobio ocenu 8.
    • T2: `{(x.ime, x.prezime) | (\exists x)(Sx \wedge (\exists y)(Iy \wedge y.ii "indeks" = x.ii "indeks" \wedge y.ocena = 8))}`
• Uputstvo za IV domaći:

  Rečenice prethodnog domaćeg (II), prevesti u formule predikatskog računa I reda, koristeći predikate:
  $Sx$ ($x$ je student) i $Ix$ ($x$ je ispit).

Zadatak sa osmicama. Rešenje pomoću operatora EXISTS

• Zagrevanje:
  1. Imena i prezimena studenata koji su polagali bar jedan ispit
  2. Imena i prezimena studenata koji su položili bar jedan ispit
• Zadatak sa osmicama (slučajevi)
  • Imena i prezimena studenata koji imaju bar jednu osmicu.
  • Imena i prezimena studenata koji imaju bar jednu ocenu koja nije osmica.
  • Imena i prezimena studenata koji su polagali bar jedan ispit i nemaju nijednu osmicu.
  • Imena i prezimena studenata koji su polagali bar jedan ispit i na svakom ispitu su dobili osmicu.
• Korelisani podupiti
• Uputstvo za V domaći

  U okviru samostalnog rada kod kuće, student treba da napravi upite primenom operatora EXISTS koji ispisuju:

  1. Nazivi predmeta iz koga je svaki student koji je položio taj predmet dobio ocenu veću od 8.
  2. Imena i prezimena nastavnika čiji ispit nije uspeo da položi nijedan student koji je taj ispit polagao.

Skalarne i agregatne funkcije

• Skalarne funkcije (ulaz je skalarna vrednost: niska, broj ili datum)
  • Skalarne funkcije za rad sa niskama (LEFT, RIGHT, MID, LEN, TRIM).
  • Skalarne funkcije za rad sa datumom (YEAR, MONTH, DAY, DATE, DATEDIFF)
  • Skalarna funkcija Switch analizira parove (logički izraz, pridružena vrednost) i vraća kao rezultat onu pridruženu vrednost koja odgovara prvom logičkom izrazu koji je tačan. Switch se zapravo ponaša kao if-naredba u Python-u.
  • Primer: ispisati imena i prezimena studenata i pun naziv njihovog statusa (R - redovan, V - vanredan, S - samofinansirajući).

    SELECT ime, prezime, Switch(
              status = 'R', 'redovan',
              status = 'V', 'vanredan',
              status = 'S', 'samofinansirajući'
    ) AS nazivStatusa
    FROM studenti;
    

    Poziv funkcije Switch u ovom primeru ima isti efekat kao poziv sledeće funkcije u Python-u:

    def switch(status):
        if status == 'R':
            return 'redovan'
        elif status == 'V':
            return 'vanredan'
        elif status == 'S':
            return 'samofinansirajući'
    

    Standard SQL-a a ne poznaje funkciju Switch, umesto nje definiše izraze CASE-WHEN-THEN-ELSE koji imaju istu svrhu, ali nisu implementirani u MS ACCESS-u.
• Agregatne funkcije (ulaz je podskup skalarnih vrednosti jedne kolone): COUNT, SUM, AVG, MIN, MAX
• Primeri:
  1. Odrediti najveću i najmanju ocenu na svim polaganjima ispita, kao i ukupan broj pojedinačnih polaganja.
  2. Odrediti najveću i najmanju ocenu na svim polaganjima ispita, kao i ukupan broj pojedinačnih polaganja, ali samo onih koja su održana 18.09.1999.
  3. Odrediti prosek studenta sa indeksom 103/98:
     • koristeći SUM i COUNT;
     • koristeći AVG.
• Grupisanje (GROUP BY, HAVING).
• Sortiranje redova upita: ORDER BY kolona (ASC | DESC), kolona (ASC | DESC), ...
• Primeri:
  1. Za studente koji su položili bar jedan ispit ispisati njihov indeks i prosečnu ocenu.
  2. Za studente koji su položili bar jedan ispit ispisati njihovo ime, prezime i prosečnu ocenu.
  3. Za studente koji su položili bar jedan ispit i imaju prosečnu ocenu bar 8.50 ispisati njihovo ime, prezime i prosečnu ocenu.
  4. Za studente koji su položili bar jedan ispit ispisati njihovo ime, prezime i prosečnu ocenu i to sortirano opadajuće po proseku, a potom rastuće po prezimenu i imenu.
• Uputstvo za VI domaći

  U okviru samostalnog rada kod kuće, student treba da napravi upite koji ispisuju:

  1. Naziv i šifru predmeta, kao i prosečnu ocenu na položenim ispitima za svaki predmet ponaosob.
  2. Ispisati smerove na kojima studiraju studenti zajedno sa brojem studenata po smeru. Rezultat sortirati opadajuće po broju studenata, a potom po nazivu smera.
  3. Ispisati statuse studenata zajedno sa brojem studenata po statusu. Rezultat sortirati opadajuće po broju studenata, a potom po nazivu statusa.
  4. Ime, prezime i šifru nastavnika i prosečnu ocenu na svim položenim ispitima kod tog nastavnika za svakog nastavnika ponaosob.
  5. Ispisati za svaki predmet njegov naziv kao i najveći broj polaganja tog predmeta od strane nekog studenta.

Projektovanje baza podataka

• Model entiteta i odnosa (engl. Entity Relationship Model, skr. ERM). Notacija:
  
• Primer 1: istraživači na projektu
  
• Primer 2: istraživači na projektu (druga verzija)
  
• Primer 3: evidencija polaganja ispita (baza Studenti2009)
  
• Primer 4: evidencija tv kupovine (baza TVSHOP-2020)
  

Kreiranje baze podataka i njenih tabela. Tipovi u SQL-u. Specifikacija primarnog i stranog ključa

• Iskaz CREATE TABLE
  • Tipovi u SQL-u:
    • INTEGER
    • REAL
    • CHAR
    • NUMBER
    • DATE
  • Pregled tipova kolona u Access-u (Table > Design View) ukazuje da se nabrojani tipovi na kraju svode na neki od tipova Text, Number, Date/Time, čiji se podtipovi dodatno specifikuju u polju Field Size (na primer, za tip Number podtipovi su Integer, Long Integer, Single, Double, Decimal; za tip Date/Time podtipovi su General Date, Long Date, Medium Date, Short Date, Long Time, Medium Time, Short Time).
• Kreiranje baze TVSHOP (uputstvo, TVSHOP-2020.accdb)

  RACUN(IDRacuna, Banka, Stanje)
  KUPAC(IDKupca, Ime, Prezima, DatRodj, Ulica, PBroj,Mesto, IDRacuna)
  DOBAVLJAC(IDFirme, Firma)
  PROIZVOD(IDProizvoda, Naziv,Cena, IDFirme)
  NARUCITI(IDKupca, IDProizvoda, Datum, Kolicina)

  

• Voditi računa o:
  1. lokalnim podešavanjima (Control Panel > Regional Settings), posebno o unosu datuma (Regional Settings > Customize > Date). Na primer, mm/dd/yyyy, dd.mm.yyyy, yyyy-mm-dd
  2. narušavanju integriteta (redosled kreiranja tabela je bitan!)
• Zadaci za samostalan rad i vežbu za predstojeći praktikum

  U okviru samostalnog rada kod kuće, student treba da napravi upite koji ispisuju:

  1. Imena i prezimena kupaca iz mesta Mracni dol.
  2. Nazive proizvoda čija je cena manja od 100.
  3. Imena i prezimena kupaca čije je stanje na računu između 500 i 1000.
  4. Nazive proizvoda koje isporučuje firma Del-boj.
  5. Imena i prezimena kupca i naziv naručenog proizvoda za svaku narudžbinu.
  6. Naziv naručenog proizvoda i vrednost narudžbine za svaku narudžbinu. (Vrednost narudžbine je iznos koji kupac mora da plati da bi dobio naručeni proizvod).
  7. Imena i prezimena kupaca koji su naručili bar jedan proizvod dobavljača Pekam co..
  8. Nazive proizvoda koje nije poručio nijedan kupac sa stanjem računa većim od 1000.
  9. Nazive dobavljača čiji svaki proizvod ima cenu veću od 30.
  10. Nazive dobavljača čiji nijedan proizvod nije poručen od strane kupca iz mesta Kraj sveta.
  11. Imena i prezimena kupaca koji su naručili najskuplji proizvod.
  12. Za svaki proizvod ispisati njegov naziv i ukupan broj njegovih porudžbina.
  13. Za svakog kupca ispisati njegovo ime i prezime i ukupnu vrednost njegovih porudžbina. (Ukupna vrednost porudžbina jednog kupca je ukupan iznos koji taj kupac mora da plati da bi dobio sve proizvode koje je naručio).
  14. Nazivi dobavljača takvih da je svaki proizvod koji isporučuju naručen u ukupnoj vrednosti većoj od 2000. (Ukupna vrednost u kojoj je naručen neki proizvod je zbir vrednosti svih njegovih narudžbina.)

Rad sa datumima tipa DATE.

• Rad sa podacima tipa DATE (#mm/dd/yyyy#). Skalarne funkcije za rad sa datumom:
  • YEAR, vrednost godine u zadatom datumu;
  • MONTH, redni broj meseca (1–12) u zadatom datumu;
  • DAY, redni broj dana (1–31) u zadatom datumu;
  • DATE, današnji datum;
  • DATEDIFF, razlika dva zadata datuma (drugi i treći parametar) izražena u nekoj vremenskoj jedinici (prvi parametar). Ako je prvi parametar 'yyyy', vremenska jedinica je godina.
  Primer: ispisati za svakog kupca njegovo ime, prezime i koliko ima godina (broj godina je razlika današnjeg datuma i datuma rođenja kupca, izražena u godinama).

  SELECT ime, prezime,
  DATEDIFF('yyyy', DatRodj, DATE()) AS godine_kupca
  FROM Kupac;

• Skalarna funkcija za formatiranje izlaza FORMAT (d, m, y, #, 0, %)
• Skalarna funkcija za zaokruživanje broja na određen broj decimala ROUND
• UNION
• Zadaci za samostalan rad i vežbu za predstojeći praktikum

  U okviru samostalnog rada kod kuće, student treba da napravi upite koji ispisuju:

  1. Nazive proizvoda i njihove cene sortirane od najskupljih ka najjeftinijim proizvodima.
  2. Nazive dobavljača čiji su proizvodi naručeni u maju 2001. godine.
  3. Imena i prezimena kupaca koji su sve narudžbine naručili posle 31. maja 2001. godine, sortirana rastuće najpre po prezimenima, a potom po imenima.
  4. Imena i prezimena kupaca čije sve narudžbine isporučuje dobavljač Del-boj ili čije sve narudžbine imaju vrednost bar 1000.

Iskazi za unos, ažuriranje i brisanje redova u tabeli

• INSERT iskaz (SQL iskaz za unos reda u tabelu baze podataka)

  Sintaksa	INSERT INTO tabela VALUES (lista_vrednosti_kolona);	Ako je iskaz uspešno izvršen, ne može se ponovo izvršiti jer se narušava integritet entiteta (duplikat primarnog ključa).
  Primer 1	INSERT INTO Studenti VALUES ('Pera', 'Mitić', '126/95', 'R', 'R');
  Primer 2	INSERT INTO Ispiti VALUES ('2B03', '126/95', '30.05.2001.', 9, 'PROF3', 1);	Ovaj iskaz će se uspešno izvršiti samo ako se prethodno izvrši iskaz iz Primera 1 (ne može se uneti ispit studenta sa indeksom 126/95 ako podaci o tom studentu nisu u evidenciji studenata, tj. tabeli Studenti).
  Primer 3	INSERT INTO Racun VALUES (44444, 'Zdipi i brisi', 17000);
  Primer 4	INSERT INTO Kupac VALUES (6, 'Pera', 'Mitić', #01/23/1990#, 'Simina 2', 11000, 'Beograd', 44444);	Ovaj iskaz će se uspešno izvršiti samo ako se prethodno izvrši iskaz iz Primera 3 (ne može se uneti kupac sa računom 44444 ako podaci o tom računu nisu u evidenciji računa, tj. tabeli Racun).
  Primer 5	INSERT INTO Naruciti VALUES (6, 101, #11/29/2016#, 10);	Ovaj iskaz će se uspešno izvršiti samo ako se prethodno izvrši iskaz iz Primera 4 (ne može se uneti narudžbina kupca čiji je IDKupca 6 ako podaci o tom kupcu nisu u evidenciji kupaca, tj. tabeli Kupac).

• UPDATE iskaz (SQL iskaz za ažuriranje ćelije u tabeli baze podataka)

  Sintaksa	UPDATE tabela SET lista_dodela_vrednosti_kolona WHERE uslov restrikcije (ograničenja);
  Primer 1	UPDATE Studenti SET smer = 'N', status = 'V' WHERE indeks = '126/95'; ('2B03', '126/95', '30.05.2001.', 9, 'PROF3', 1);
  Primer 2	UPDATE Ispiti SET ocena = 10 WHERE indeks = '126/95' AND put = 1;

• DELETE iskaz (SQL iskaz za brisanje reda iz tabele baze podataka)

  Sintaksa	DELETE FROM tabela WHERE uslov restrikcije (ograničenja);
  Primer 2	DELETE FROM Ispiti WHERE indeks = '126/95' AND ocena = 5;
  Primer 1	DELETE FROM Studenti WHERE indeks = '126/95';

• Zadaci za samostalan rad i vežbu za predstojeći praktikum

  U okviru samostalnog rada kod kuće, student treba da napravi upite koji ispisuju:

  1. Povećati za 10% cenu svih proizvoda koje nije poručio nijedan kupac iz mesta Kraj sveta.
  2. U narudžbini svih proizvoda koje je isporučio Del-boj smanjiti količinu za 20.
  3. Povećati za 1500 stanje na računu svim kupcima koji su poručili manje od 5 različitih proizvoda.
  4. Obrisati sve proizvode koji nemaju više od 5 različitih kupaca.
  5. Obrisati sve proizvode čija je prosečna vrednost narudžbine manja od 1000.
  6. Obrisati sve proizvode čija je ukupna vrednost svih narudžbina manja od 500.
  7. Napisati INSERT-iskaze kojima se u bazu TVSHOP unosi informacija da Bojsi (dobavljač br. 5) isporučuje proizvod Pokvareni telefon sa šifrom 405 i cenom 105 (informacije o proizvodu nisu u bazi), pri čemu:
     • informacije o dobavljaču (Bojsi) već postoje u bazi;
     • informacije o dobavljaču (Bojsi) ne postoje u bazi.
     Ako je potrebno više INSERT-iskaza, obrazložiti da li je redosled njihovog izvršavanja bitan i zašto.

Relaciona algebra

SELECT ime, prezime
FROM Studenti;

SELECT *
FROM Studenti
WHERE status='V' 
  AND smer='R';

SELECT *
FROM Studenti, Ispiti;

SELECT *
FROM Studenti AS s, 
     Ispiti AS i
WHERE s.indeks = i.indeks;

SELECT *
FROM Studenti
WHERE smer = 'R'
   OR smer = 'N';

SELECT *
FROM Studenti
WHERE status='V'
  AND smer='R';

SELECT *
FROM Studenti
WHERE status='V'
  AND NOT(smer = 'R');

SELECT *
FROM Studenti
WHERE status='V'
  AND smer <> 'R';

Primetimo da je prirodno spajanje zapravo kompozicija dve operacije relacione algebre: Dekartovog proizvoda i restrikcije. S obzirom da nam je prirodno spajanje neophodno u svim primerima koji se odnose na neku bazu podataka, Dekartov proizvod praktično nećemo koristiti direktno, već isključivo kao deo prirodnog spajanja.

Većina SQL-iskaza za pretragu može se opisati nekom kompozicijom navedenih operacija relacione algebre.

• Primer 1: Imena i prezimena studenata sa smera R. Koraci:
  1. Rezultat prvog koraka je relacija `R_1`, dobijena operacijom restrikcije nad relacijom Studenti, tj. skup svih n-torki polazne relacije Studenti takvih da atribut `smer` ima vrednost R:

     `\sigma_{(smer='R')} Studenti`

  2. Rezultat drugog koraka je relacija `R_2`, dobijena operacijom projekcije nad relacijom `R_1` po atributima `ime` i `prezime`:

     `\prod_{(ime, prezime)} R_1`

  Prema tome, konačno rešenje je kompozicija projekcije i restrikcije, `\Pi\circ\sigma(Studenti) = \Pi(\sigma(Studenti))`, koju zapisujemo na sledeći način:

  `\prod_{(ime, prezime)}(\sigma_{(smer='R')} Studenti)`

• Primer: Imena i prezimena studenata koji nemaju nijednu osmicu (uključujući i brucoše). Ovaj zadatak rešavamo slično rešenju u SQL-u u kojem smo koristili operator IN: najpre ćemo naći komplement traženog skupa, tj. studente koji imaju bar jednu osmicu, a onda ćemo njihovom eliminacijom iz skupa svih studenata dobiti studente koji nemaju nijednu osmicu.
  1. Rezultat prvog koraka je relacija `R_1`, dobijena operacijom restrikcije nad relacijom Ispiti, tj. skup svih n-torki polazne relacije Ispiti takvih da atribut `ocena` ima vrednost 8:

     `\sigma_{(ocena=8)} ii "Ispiti";`

  2. Rezultat drugog koraka je relacija `R_2`, dobijena operacijom projekcije nad relacijom `R_1` po atributu `ii "indeks"`, tj. skup svih brojeva indeksa studenata koji imaju bar jednu osmicu:

     `\prod_{(ii "indeks")} R_1 = \prod_{(ii "indeks")}(\sigma_{(ocena=8)} ii "Ispiti")`

  3. Rezultat trećeg koraka je relacija `R_3`, dobijena operacijom projekcije nad relacijom Studenti po atributu `ii "indeks"`, tj. skup brojeva indeksa svih studenata:

     `\prod_{(ii "indeks")} Studenti`

  4. Rezultat četvrtog koraka je relacija `R_4`, dobijena kao skupovna razlika relacija `R_3` i `R_2`, tj. skup brojeva indeksa svih studenata koji nemaju nijednu osmicu, uključujući i brucoše:

     `R_3 - R_2=\prod_{(ii "indeks")} Studenti - \prod_{(ii "indeks")}(\sigma_{(ocena=8)} ii "Ispiti")`

  5. Rezultat petog koraka je relacija `R_5`, dobijena prirodnim spajanjem relacija `R_4` i relacije Studenti po zajedničkom atributu `ii "indeks"`, tj. skup svih studenata koji nemaju nijednu osmicu, uključujući i brucoše:

     `R_4\bowtie_{ii "indeks"=ii "indeks"}Studenti =(\prod_{(ii "indeks")} Studenti - \prod_{(ii "indeks")}(\sigma_{(ocena=8)} ii "Ispiti"))\bowtie_{ii "indeks"=ii "indeks"}Studenti`

  6. Rezultat šestog koraka je relacija `R_6`, dobijena projekcijom relacije `R_5`po atrubutima `ime`i `prezime`, tj. skup svih imena i prezimena studenata koji nemaju nijednu osmicu, uključujući i brucoše:

     `\prod_{(ime, prezime)}R_5 =` `=\prod_{(ime, prezime)}((\prod_{(ii "indeks")} Studenti - \prod_{(ii "indeks")}(\sigma_{(ocena=8)} ii "Ispiti"))\bowtie_{ii "indeks"=ii "indeks"}Studenti)`