Podešavanje sistema

ČAS 1

ČAS 2

ČAS 3

ČAS 4

ČAS 5

ČAS 6

ČAS 7

ČAS 8

ČAS 9

SQL RECAP

ČAS 10

ČAS 11

ČAS 12

Rokovi

Kontakt: milica.gnjatovic[at]matf.bg.ac.rs

Zvanična stranica predmeta

Srećan početak novog semestra!
Sva važna obaveštenja će biti na zvaničnoj strani predmeta.

Podešavanje sistema

Softver potreban za vežbe:

• IBM DB2 sistem za upravljanje bazama podataka
• IBM Data Studio alat za rad sa bazama podataka
• Studentska baza podataka stud2020

Ili preuzeti virtuelnu mašinu. VM

Ukoliko preuzimate virtuelnu mašinu

Ako koristite stariju verziju VMware plejera i dobijate grešku pri pokretanju VM, potrebno je da otvorite .vmx datoteku i promenite vrednost za virtualHW.version na Vašu verziju plejera, npr. ako je 15 postavite
virtualHW.version = "15".

• korisnik: student
• šifra: abcdef

Ukoliko želite sami da preuzmete programe

Potrebno je napraviti nalog na IBM-u sa studentskim mejlom.

• uputstvo za instairanje DB2
  dodatno uputsvo
  Link za preuzimanje DB2, nalog se pravi sa alas mejlom, ukoliko traži multifaktorsku autentifikaciju najlakše je da stavite još jedan mejl.
• uputstvo za instairanje data studija
• skripta za uvoženje baze
  Uputstvo: unzipovati folder i pozicionirati se u njega. U terminalu pokrenuti ./create.sh.
  Napomena: može biti potrebno promeniti mod fajla komandom:

  chmod +x create.sh

  Ovu liniju je potrebno izvršiti u baznom folderu i dva podfoldera. Može da potraje par minuta.

Instalacija pomoću doker kontejnera

Ukoliko Vaš sistem ne podržava db2 možete koristiti docker. Uputstvo možete naći na linku U dokumentu sa uputstvom imate mogućnost da komentarišete. Ukoliko naiđete na neki problem možete dodati komentar kako bi pomogli drugim kolegama :)

Uputstvo kolege Petra Radojevića

Uputstvo

Struktura baze podataka

• DA - podaci zapisani ASCII kodnom shemom
• DB - podaci zapisani UTF-8 kodnom shemom

SQL Reference knjiga

ČAS 1

Komande u db2:

Baza podataka - kolekcija podataka organizovana na određen način; različiti tipovi, objektne, nerelacione, relacione...

Sistem za upravljanje bazom podataka (SUBP) - program koji omogućava pristup i rad sa podacima iz baze, to je ibm db2.

Relacione baze podataka (RBP) - podaci se predstavljaju u okviru relacija/tabela

Entitet - neki objekat, prikazuje se tabelom; student, predmet, ispit...

Atribut - opisuje objekat, kolone tabele; indeks, ime, prezime...

Student (indeks, ime, prezime, datum_rođenja, mesto_rođenja)

Red/ntorka - podaci o jednom entitetu/studentu/ispitu...

Relaciona baza - skup relacija/tabela

Relaciona shema - opis strukture relacija

SQL - sredstvo za upravljanje relacionom bazom podatka, upitni jezik

Primarni ključ - atribut ili skup atributa koji jedinstveno opisuju entitet

Strani ključ - atribut ili skup atributa kojim se pravi veza ka drugoj relaciji

Shema - grupisane tabele u pogled koji čini celinu, npr. shema za profesore i shema za studente

Redosled izvršavanja:

3 SELECT - kolone koje će se prikazati, * da se prikažu sve kolone
1 FROM - odakle izvlačimo podatke, koja tabela
2 WHERE - uslovi koje rezultati treba da ispunjavaju

Rad u Data studiju

Pre početka je potrebno pokrenuti db2 iz terminala komandom

db2start

1. Open perspective > Data
2. Desni klik > connect > uneti korisničko ime i šifru > test connection (treba da je okej) > connect
   Ukoliko nema ponuđeno stud2020: desni klik na Database Connections > new > DB2 for Linux, Unix and Windows > host: stud2020, username an password, identično prethodom koraku.
3. Pravljenje novog sql skripta
4. Click here to connect to database > izabrati stud2020 > finish
5. Dugme za izvršavanje unetog upita.
6. Ovde se nalazi rezultat upita ili eventualna poruka o grešci.

1. Desni klik na DA > Add to Overview Diagram > mogu se izabrati željene tabele, dovoljno je ok
2. Čekirati da bi se prikazale kolone u tabeli. Ako se ne vidi ovakav prikaz klikuti na polju pored neke od tabela (da nije tabela selektovana).
3. Ovde se mogu videti dodatne informacije o tabelama, npr koje kolone ima i kog su tipa.

ČAS 2

Vrste spajanja

Da li staviti uslov u WHERE ili u ON?

Bitno nam je gde ide uslov kada imamo left ili right join. Ako na primer tražimo da se izdvoje studenti i predmeti na kojima imaju desetke, i oni studenti koji nemaju ni jednu desetku.
Ako izvršimo:

SELECT D.INDEKS, I.INDEKS, OCENA
FROM DA.DOSIJE D LEFT JOIN DA.ISPIT I
ON I.INDEKS=D.INDEKS AND OCENA>5 AND STATUS='o';

Imaćemo 33679 redova (bar kod mene). Neki od tih redova su
20171010 | NULL | NULL
20171106 | NULL | NULL
20180087 | NULL | NULL
To su studenti koji nemaju ni jedan položen ispit. Dakle, sada imamo studente spojene sa njihovim ispitima, a ako nemaju ispite onda su spojeni sa NULL.
Ovde sada treba dodati uslov za 10ke. Ako dodamo samo

WHERE OCENA=10;

Dobićemo 7401 red, ali među njima će biti sve desetke. Izgubićemo prethodna 3 reda, a to su nam studenti koji nemaju ni jedan položen ispit (samim tim ni jednu 10ku), dakle želimo ih u rezultatu. Da bi ti redovi ostali potrebno je da uslov za 10 ide u ON:

SELECT D.INDEKS, I.INDEKS, OCENA
FROM DA.DOSIJE D LEFT JOIN DA.ISPIT I
ON I.INDEKS=D.INDEKS AND OCENA>5 AND STATUS='o' AND OCENA=10;

Sada imamo 9101 red i među njima i ona tri sa početka.
Ovo spajanje možemo da tumačimo ovako:

Svakog studenta iz tabele dosije probaj da spojiš sa njegovim ispitom na kom je dobio 10, ako takav ispit ne postoji ostavi studenta u rezultatu i spoji ga sa NULL.

Još jedan način da ovo gledate je da pre nego dodamo onaj WHERE razmislimo koje vrednosti će da poredi. U rezultatu pre toga smo videli da imamo redove gde je ocena null, znači da će where koji dodamo imati poređenje null=10. null=10 je false, pa će red time biti izbačen iz rezultata. Ovo razmatranje nam nije tako značajno ako imamo samo join jer će on spojiti samo studente sa njihovim ispitima, nećemo uopšte imati redove sa ocenom NULL na koje potencijalno treba da se pazi da se ne izgube dodavanjem where.

U prethodnim upitima namerno stoje D.INDEKS i I.INDEKS da bi obratili pažnju da su sve I.XXX vrednosti NULL ako ne postoji ispit sa kojim spajamo studenta.

ČAS 3

Podupiti

X [NOT] IN (SELECT Y FROM ... WHERE ...)

Gledamo da li je X u nizu Y-ona koji dobijamo iz podupita. Važno je da X i Y imaju isti broj atributa. Na primer možemo imati WHERE (OZNAKAROKA, SKGODINA) IN (SELECT OZNAKA, GODINA FROM ...).

[NOT] EXISTS (SELECT ... FROM ...)

Uslov je zadovoljen ako podupit vraća bar jedan red.

X < ALL (SELECT ... FROM ...)

Uslov je zadovoljen ako je X manje od svake vrednosti koju vraća podupit. Mogu se koristi i ostali operatori.

X < SOME (SELECT ... FROM ...)

Uslov je zadovoljen ako je X manje od neke vrednosti koju vraća podupit. Mogu se koristi i ostali operatori.

X = ANY (SELECT ... FROM ...)

Uslov je zadovoljen ako je X jednako nekoj vrednosti koju vraća podupit. Mogu se koristi i ostali operatori.

SVAKI <=> NE POSTOJI NEKI KOJI NIJE
Tražimo studenta koji je položio svaki ispit koji je polagao. <=> Tražimo studenta za kog NE POSTOJI ispit koji NIJE POLOŽIO.

Napomene posle časa:

• Drugom zadatku je dodata napomena uz još jedno rešenje.

• U narednom primeru ćemo imati grešku jer se u order by ne zna da li želimo da uredimo po indeksu iz tabele dosije ili po indeksu iz tabele ispit. Možemo to rešiti tako što ćemo staviti order by D.INDEKS ili order by I.INDEKS.

  SELECT *
  FROM DA.ISPIT I JOIN DA.DOSIJE D
  ON I.INDEKS = D.INDEKS
  ORDER BY INDEKS;

  Međutim, u narednom primeru nemamo grešku jer se u select nalazi jedan indeks, pa se po njemu uređuje.

  SELECT D.INDEKS, IME
  FROM DA.ISPIT I JOIN DA.DOSIJE D
  ON I.INDEKS = D.INDEKS
  ORDER BY INDEKS;

ČAS 4

Skupovni operatori

UNION

upit1 UNION upit2 nam daje sve redove iz upita1 i upita2 bez duplikata. Važno je da i prvi i drugi upit vraćaju isti broj kolona i da su te kolone istog tipa. Na primer u redu je ako prvi upit vraća ime (string), ocenu (broj), a drugi prezime (string), indkeks (broj). Nije u redu ako prvi vraca ocenu (broj), a drugi ime (string).

UNION ALL

Identično kao UNION samo što ovde možemo imati duplikate. Efikasnije je od UNION jer ne proverava duplikate. UNION je kao da smo na UNION ALL primenili DISITNCT.

INTERSECT

upit1 INTERSECT upit2 nam daje sve redove iz upita 1 koji postoje i u upitu 2 bez duplikata. Važno je da i prvi i drugi upit vraćaju isti broj kolona i da su te kolone istog tipa.

INTERSECT ALL

Identično kao INTERSECT samo što ovde možemo imati duplikate. Efikasnije je od INTERSECT jer ne proverava duplikate. INTERSECT je kao da smo na INTERSECT ALL primenili DISITNCT.

EXCEPT / MINUS

upit1 EXCEPT upit2 nam daje sve redove iz upita 1 koji ne postoje u upitu 2 bez duplikata. Važno je da i prvi i drugi upit vraćaju isti broj kolona i da su te kolone istog tipa. EXCEPT i MINUS su sinonimi i potpuno je svejedno da li koristimo jedno ili drugo.

EXCEPT ALL

upit1 EXCEPT ALL upit2 nam daje sve redove iz upita 1 koji ne postoje u upitu 2 uzimajući u obzir duplikate. Ako prvi upit vraća X za svaku ocenu 10, a drugi upit vraća X za svaku ocenu 6, i ako student ima 5 desetki i 3 šestice u rezultatu će biti 2 reda sa X. Ako bi u ovom primeru koristili samo EXCEPT dobili bi praznu tabelu kao rezultat.

Funkcije za rad sa datumom i vremenom

CURRENT TIME, CURRENT DATE

specijalni registri koji sadrže vrednost trenutnog vremena, odnosno datuma

DATE(expression)

expression može biti string u formatu "dd.mm.yyyy"

YEAR(date)

MONTH(date)

redni broj meseca u godini

WEEK(date)

redni broj nedelje u godini

DAY(date) / DAYOFMONTH(date)

redni broj dana u mesecu

DAYOFYEAR(date)

redni broj dana u godini

DAYOFWEEK(date)

redni broj dana u nedelji, 1 je subota, dok je 7 nedelja

DAYNAME(date)

naziv dana u nedelji; ukoliko želimo da zadamo jezik možemo ga dodati kao drugi argument, za srpski bi kod bio 'sr_latn_sr' ili 'sr_cirilc_sr'

MONTHNAME(date)

naziv meseca; ukoliko želimo da zadamo jezik možemo ga dodati kao drugi argument, za srpski bi kod bio 'sr_latn_sr' ili 'sr_cirilc_sr'

DAYS(date)

brojevna reprezentacija datuma

TIME(expression)

vraća vreme, expression može biti u formatu hh:mm

TIMESTAMP(date, time)

vraća format koji sadrži datum i vreme

HOUR(time), MINUTE(time), SECOND(time)

vraća broj sati, minuta i sekundi za vreme

YEARS_BETWEEN(date1, date2), MONTHS_BETWEEN(date1, date2), DAYS_BETWEEN(date1, date2)

Koliko je godina, meseci, dana prošlo između dva datuma. Ako gledamo mesece na primer a prošlo je 2 godine i 3 meseca reulatat će biti 27 meseci, a ne 3.

SECOND[S], MINUTE[S], HOUR[S], DAY[S], MONTH[S], YEAR[S]

ključne reči koje nam omogućuju da dodajemo na neki datum ili na neko vreme
Na primer:

• CURRENT TIME + 5 MINUTES - 5 SECONDS
• DATE('1.1.2020') + 5 YEARS (u ovom slučaju nije dovoljno samo niska za datum, već je potrebno i DATE da se naglasi da je datum jer db2 ne može sam da zaključi)

Funkcije za rad sa karakterima

SUBSTR(str, start, len)

Vraća podnisku niske str, koja počinje od start a dužine je len. Indeksiranje kreće od 1. Ukoliko se ne navede len izdvaja se podniska od pozicije start do kraja niske.

CONCAT(x, y), x || y

Spaja niske x i y. CONCAT prima tačno dva argumenta, dok ako koristimo operator || možemo ulančati više stringova (a || b || c ...).

SPACE(x)

Vraća x praznih mesta.

POSSTR(x, y)

Vraća indeks na kom počinje niska y u niski x. Ukoliko se y ne nalazi u x vraća 0.

REPEAT(str, x)

Ponavlja nisku str x puta.

REPLACE(str, x, y)

Zamenjuje sva pojavljivanja niske x u str sa y.

LTRIM(str), TRIM(str), RTRIM(str)

Uklanja beline levo, desno ili sa obe strane.

LENGTH(arg)

Vraća dužinu argumenta, koji može biti string ili broj.

Funkcije za nedefinisane vrednosti

COALESCE(x, y, z...)

Vraća prvi argument iz liste čija vrednost nije NULL. Ako su svi agumenti NULL onda se NULL i vraća.

NULLIF(x, y)

Vraća NULL ako su x i y jednaki. Ukoliko su argumenti različiti onda vraća prvi argumenti.

Funkcije za konverziju

DECIMAL(x, y, z), DEC(x, y, z)

Vraća decimalnu reprezentaciju x, pri čemu y predstavlja ukupan broj cifara, a z broj mesta iza decimalnog zareza. X može biti broj ili string.

DOUBLE(x)

Vraća broj veće preciznosti. X može biti string ili broj.

DECIMAL(expression)

Vraća string reprezentaciju broja.

CHAR(expression)

Skoro sve tipove pretvara u string.

CHAR(datetime, format)

Datum i vreme prikazuje u datom formatu. Dostupni formati su ISO, USA, EUR, JIS, LOCAL.

Numeričke funkcije

MOD(x, y)

ABS(x)

SIGN(x)

Vraća 1 za pozitivne, -1 za negativne, 0 za 0.

SQRT(x)

Vraća koren broja.

RAND(seed)

Vraća random broj. Seed argument nije obavezan, za isti seed se dobija isti 'random' broj.

CEIL, FLOOR

Zaokružuje broj na veći, odnosno manji.

SIN, COS, TAN, COTAN, ASIN, ACOS, ATAN, EXP...

ROUND(num, broj_decimala)

Na narednom linku možete naći još sklarnih funkcija i informacije o njima: dokumentacija.

ČAS 5

ČAS 6

WITH klauza

With klauzom možemo definisati privremenu tabelu koju koristimo u upitu.

WITH POMOCNA_TABELA AS (
SELECT ... FROM ...
)
SELECT ...
FROM POMOCNA_TABELA
...

WITH POMOCNA_TABELA(kolona1, kolona2 ...) AS (
SELECT ... FROM ...
)
SELECT ...
FROM POMOCNA_TABELA
...

Pomoćnu tabelu možemo koristiti kao i bilo koju drugu tabelu iz baze, možemo je spajati sa drugim tabelama, grupisati...
Možemo definisati više pomoćnih tabela:

WITH POMOCNA_TABELA AS (...),
DRUGA_POMOCNA_TABELA AS (...),
...
SELECT ...
FROM ...
WHERE ...

Važno je da pri definisanju pomoćne tabele svakoj koloni damo ime.

ČAS 7

Kreiranje tabela

CREATE TABLE NAZIV_TABELE (
NAZIV_KOLONE1 TIP_KOLONE1
NAZIV_KOLONE2 TIP_KOLONE2 NOT NULL
NAZIV_KOLONE3 TIP_KOLONE2 DEFAULT ...
...
NAZIV_KOLONEN TIP_KOLONEN
PRIMARY KEY (KOLONA_KLJUC1, KOLONA_KLJUC2...)
FOREIGN KEY FK_NAZIV_KLJUCA (NAZIV_KOLONE_OVE_TABELE) REFERENCES NAZIV_TABELE_KOJU_REFERISE
CONSTRAINT NAZIV_OGGRANICENJA CHECK ( ... )
)

Primarni ključ može činiti više kolona. Važno je da svaka kolona bude definisana sa NOT NULL. Primarni ključ se još može definisati tako što u nazivu kolone dodamo primary key, npr ID INTEGER NOT NULL PRIMARY KEY.
Umesto komande CREATE TABLE ... , možemo korisiti CREATE TABLE IF NOT EXISTS ... kojom kreiramo tabelu samo ukoliko ona već ne postoji.
Ukoliko ne želimo ručno da unosimo vrednost primarnog ključa ona može automatski da se generiše:

• NAZIV_KOLONE INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (MINVALUE 1)

  U ovom slučaju vrednost ne možemo da unosimo ručno već se uvek automatski dodaje. Minval je opciono.

• NAZIV_KOLONE INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY

  Vrednost se dodaje automatski ukoliko nije navedena.

Više o ovome se može naći u dokumentaciji.

Neki od dozvoljenih tipova za kolone su:

• SMALLINT, INTEGER/INT, BIGINT, DECIMAL/DEC, FLOAT(BROJ DECIMALA), REAL, DOBULE
• CHARACTER/CHAR
• CHARACTER/CHAR[max broj karaktera u niski]
• VARCHAR[max broj karaktera u niski]
• DATE, TIME, TIMESTAMP
• BOOLEAN

Lista tabela u bazi se može videti sa nekom od narednih komandi iz terminala:

• db2 lista tables
• db2 lista tables for naziv_sheme

Treba obratiti pažnju gde je kreirana baza, tj. da li u nekoj shemi.

Kopiranje tabele

CREATE TABLE NAZIV_KOPIJE_SA_PODACIMA AS (
SELECT *
FROM ORIGINALNA_TABELA
) WITH DATA;

CREATE TABLE NAZIV_KOPIJE_BEZ_PODATAKA AS (
SELECT *
FROM ORIGINALNA_TABELA
) WITH NO DATA;

Ovo je korisno ako želite da vežbate izmenu i brisanje redova, a da pri tom ne menjate originalnu tabelu.

Promena strukture tabele

ALTER TABLE NAZIV_TABELE ...

Moguće su naredne izmene:

• DROP NAZIV_KOLONE

• ADD NAZIV_KOLONE TIP_KOLONE

  Ukoliko ne navedemo DEFAULT NEKA_VREDNOST, vrednosti koje su već bile u bazi će za vrednost u toj koloni imati NULL. Ukoliko želimo da ta kolona koju dodajemo bude NOT NULL moramo obavezno navesti podrazumevanu vrednost.
• ADD CONSTRAINT NAZIV_OGRANICENJA CHECK (...)
• DROP CONSTRAINT NAZIV_OGRANICENJA
• ALTER COLUMN NAZIV_KOLONE SET DEFAULT ....

Nakon promene strukture tabele u nekim slučajevima je neophodno reorganizovati tabelu komandom iz terminala:

db2 connect to naziv_baze
db2 reorg table naziv_tabele

Brisanje tabele iz baze

DROP TABLE NAZIV_TABELE;

DROP TABLE IF EXISTS NAZIV_TABELE;

Ukoliko pokušamo da obrišemo tabelu koja ne postoji prva verzija će izbaciti grešku, dok druga neće.

Dodavanje redova u bazu

INSERT INTO NAZIV_TABELE(KOL1, KOL2, ... , KOLN) VALUES
(VREDNOST_KOL1, VREDNOST_KOL2, ... , VREDNOST_KOLN),
(VREDNOST_KOL1, VREDNOST_KOL2, ... , VREDNOST_KOLN),
...

INSERT INTO NAZIV_TABELE(KOL1, KOL2, ... , KOLN)
SELECT ...
FROM ...

Ukoliko se ubacuje vrednost za svaku kolonu redom onda nije neophodno navoditi nazive kolona. Vrednosti koje se ne unesu će biti NULL.

Izmena podataka u bazi

UPDATE NAZIV_TABELE
SET KOL1 = VREDNOST,
KOL2 = CASE WHEN ... THEN ... ... END
KOL3 = (SELECT ... FROM ... WHERE)
KOL4 = ...
WHERE ...

UPDATE NAZIV_TABELE
SET (KOL1, KOL2 ...) = (VREDNOST1, VREDNOST2, ...)
WHERE ...

Ako ne navedemo WHERE izmeniće se svi redovi.

Brisanje redova iz baze

DELETE FROM NAZIV_TABELE
WHERE ...

Ukoliko ne navedemo where biće obrisani svi redovi iz navedene tabele.

ČAS 8

Pogled

CREATE VIEW IME_POGLEDA AS
SELECT ...
FROM ...
WHERE ...
[WITH CHECK OPTION]

DROP VIEW IME_POGLEDA;

Originalna tabela može da se menja preko pogleda samo ako je pogled definisan nad jednom tabelom. Ukoliko bi nam bilo potrebno spajanje više tabela, to može da se zaobiđe podupitima. Ukoliko kolona nije navedena u pogledu pri unosu će njena vrednost biti null. Zbog ovoga je važno da ukoliko radimo unos preko pogleda u pogledu budu navedene sve not null kolone. Koje kolone su not null možemo videti u komandi za kreiranje tabele ili na dijagramu.
With check option navodimo ukoliko želimo da se pri unosu proveri da li dati red zadovoljava uslov pogleda. Na primer, ako pogleda sadrži studente upisane posle 2018e:

• Ukoliko je pogled defisnisan BEZ with chevk opcije moći ćemo da unesemo studenta upisanog na primer 2015e. Ovaj student će biti unet u tabelu, ali nećemo moći da ga vidimo preko pogleda (jer je pogled definisan tako da prikazuje studente upisane posle 2018e).
• Ukoliko je pogled definisan SA with check opcijom preko njega nećemo moći da unesemo studenta uposanog pre 2018e.

Indeksi

CREATE INDEX NAZIV_INDEKSA
ON NAZIV_TABELE
(kol1 ASC, kol2 DESC, kol3...);

DROP INDEX NAZIV_INDEKSA;

Korisnički definisane funkcije

CREATE FUNCTION IME_FUNKCIJE (ARG1 ARG1_TIP, ARG2 ARG2_TIP...)
RETURNS TIP_POVRATNE_VREDNOSTI
RETURN IZRAZ_KOJI_RAČUNA_REZULTAT

DROP FUNCTION IME_FUNKCIJE;

Pri kreiranju korisnički definisanih funkcija treba obratiti pažnju na sledeće:

• Izbegavati nazivanje argumenata funkcije kao kolone tabele koja se koristi. Ako na primer imamo argument funkcije INDEKS i poređenje D.INDEKS=INDEKS poređenje će uvek vraćati true. Odnosno dobićemo više redova umesto jedne vrednosti. Greška se ovde vidi tek pri pozivu funkcije.
• Tipovi argumenta sa kojim se poziva funkcija treba da se poklope sa vrednošću sa kojom se poziva funkcija. Ako ćemo na primer prosleđivati indeks kao argument, u funkciji mora biti integer tip argumenta, ako bi stavili smallint dobili bi grešku pri pozivu funkcije.
• Možemo imati više funkcija istog naziva koje primaju različite argumente. Koja funckija će biti pozvana zavisi od tipa argumenta. Ako imamo dve funkcije CENA, jedna prima INTEGER, druga SMALLINT. Ako pozovemo funkciju sa velikim brojem (više nego što samllint dozvoljava), biće pozvana funkcija koja prima INTEGER. Ako pozovemo sa 123 na primer, opet će biti pozvana za integer jer se vrši sutomatsko kastovanje. Ako želimo da naglasimo da se poziva funkcija za smallint potrebano je da vršimo kastovanje: SMALLINT(123). Identično može da uradi za integer: INTEGER(123). Drugi problem ako imamo više funkcija istog naziva je njihovo brisanje. Ako pokušamo DROP FUNCTION CENA neće uspeti jer sistem ne zna koju od dve funkcije da obriše. Da bi obrisali odgovarajuću funkciju moramo da naglasimo koju želimo, to radimo tako što stavimo koji je tip argumenta: DROP FUNCITON CENA(INTGER), ili DROP FUNCTION(SMALLINT). Tip nije potreban ako imamo samo jednu funkciju. Možemo imati funkcije istog naziva i različitog broja argumenata.

Gde naći šta sve ima u bazi?

Sve tabele se nalaze u tabeli SYSCAT.TABLES. Ako želimo samo tabele neke scheme možemo iskorisiti naredni upit:

SELECT *
FROM SYSCAT.TABLES
WHERE TABSCHEMA = 'DA';

Sve poglede možemo naći u tabeli SYSCAT.VIEWS. Kolona OWNERTYPE može imati vrednosti S (system) i U (user), to možemo koristiti da lakše nađemo poglede koje smo mi kreirali.

SELECT *
FROM SYSCAT.VIEWS
WHERE OWNERTYPE = 'U';

Indeksi se mogu naći u tabeli SYSCAT.INDEXES.

SELECT *
FROM SYSCAT.INDEXES
WHERE OWNERTYPE = 'U';

Sve funkcije se nalaze u tabeli SYSCAT.FUNCTIONS.

SELECT *
FROM SYSCAT.FUNCTIONS;

ČAS 9

LISTAGG

Listagg je agregatna funkcija koja spaja stringove iz grupe u jedan.

SELECT LISTAGG(STRING, SEPARATOR) WITHIN GROUP(ORDER BY ...)
FROM ...
GROUP BY ...;

Withing group (order by) je opcioni argument.
Ukupna dužina teksta ne može biti veća od 4000 karaktera. STRING može biti neka kolona, kol1 || kol2, DISTINCT kol...

MERGE

MERGE INTO IME_TABELE
USING (NEKI_UPIT)
ON USLOV_POKLAPANJA
WHEN MATCHED THEN
...
WHEN MATCHED AND ... THEN
...
WHEN NOT MATCHED THEN
...
WHEN NOT MATCHED AND ... THEN
...

Okidači

CREATE TRIGGER NAZIV_TRIGERA
BEFORE/AFTER INSERT/UPDATE/DELETE
ON NAZIV_TABELE
REFERENCING
OLD AS STARI
NEW AS NOVI
FOR EACH ROW
WHEN(USLOV_KOJI_ISPUNJAVAJU_REDOVI_NA_KOJE_CE_SE_PRIMENITI_TRIGGER)
BEGIN ATOMIC
... ;
... ;
... ;
END @

Izvršavanje okidača

Jako je bitno da znamo šta koja vrsta triggera radi da bi znali kada se koja koristi.

NEW/OLD

	OLD	NEW
INSERT	X Unosimo vrednos, nije bilo ništa prethodno.	O Vrednos koju unosimo, možemo je izmeniti preko new.
UPDATE	O Vrednost koja je pretodno bila u tabeli, ne možemo nju menjati, ali na osnovu nje možemo menjti novu vrednost.	O Vrednost koju postavljamo, možemo je menjti.
DELETE	O Vrednost koju pokušavamo da obrišemo, možemo proveriti da li ova vrednost zadovoljava neki uslov, pa da ne izvršimo brisanje ako zadovoljava.	X Obrisali smo vrednost, nema ništa više i ne znamo šta je bilo.

Napomena: OLD vrednost ako imamo one su u BEFORE triggeru, NEW vrednosti možemo imati u BEFORE i AFTER.

Kada radimo sa triggerima u većini slučajva ćemo imati više naredbi u telu i te naredbe treba razdvojiti sa ";". Ali ako ";" iskoistimo unutar kreiranja triggera (koji je jedna komanda), onda ne možemo to iskoristiti za kraj naredbe. Zbog toga uvodimo novi terminator, tipično @.

--#SET TERMINATOR @

Komanda ide baš ovako, -- u ovom slučaju ne gledaju ovo kao komentar.
Ukoliko izvršavamo komande preko terminala koristimo narednu komandu:

db2 -td@ -f putanja_do_fajla

Ukoliko želimo da trigger javi grešku i zaustavi operaciju:

SIGNAL SQLSTATE '75000' ('Poruka o grešci koja će se ispisati korisniku.')

Sve triggere možemo videti u tabeli SYSCAT.TRIGGERS.

SQL RECAP

ČAS 10

Relaciona algebra

Tabele sa kojima ćemo raditi:

DOSIJE	PREDMET	ISPITNIROK	ISPIT
indeks ime prezime mestorodjenja datupisa	idpredmeta oznaka naziv espb	skgodina oznakaroka naziv	indeks idpredmeta skgodina oznakaroka ocena datpolaganja bodovi

Osnovne operacije

Projekcija

biramo atribute/kolone
kao SELECT
TABELA[kol1, kol2...]

Restrikcija/Selekcija

biramo redove koje želimo u rezultatu
kao WHERE
tabela WHERE ...

Proizvod

Dekartov proizvod - spaja svaki red jedne tabele sa svakim redom druge tabele
u SQL-u: from tab1,tab2
tab1 TIMES tab2

Unija

spajaju se redovi rezultata jednog upita sa redovima drugog upita
u SQL-u: UNION
upit1 UNION

Presek

zadržavaju se redovi koji se nalaze u rezultatima oba upita
u SQL-u: INTERSECT
upit1 INTERSECT

Razlika

zadržavaju se redovi koji se nalaze u rezultatima prvog upita, ali ne u rezultatu drugog upita
u SQL-u: MINUS, EXCEPT
upit1 MINUS upit2

Slobodno spajanje

restrikcija proizvoda dveju relacija
(tab1 TIMES tab2) WHERE A.x=B.y

Prirodno spajanje

spaja se po kolonama koje imaju isti NAZIV i OPSEG(TIP)
u SQL-u uz join ide ON uslov, dok ovde ne navodimo uslov, već se on podrazumeva
tab1 JOIN tab2

Deljenje

• Relacija A ima grupe kolona X i Y
• Relacija B ima grupu kolona Y
• Relacija B ima neke redove
• Delimo A sa B i u rezultatu dobijamo kolone X iz A takve da važi da je taj red u A uparen sa svim redovima iz B

Primeri:

1. primer: U rezultatu se nalaze redovi koji su u A upareni sa p2 (iz B).
2. primer: U A postoje redovi s1-p2 i s1-p4 pa s1 ide u rezultat. Postoje redovi s4-p2 i s4-p4 pa i s4 ide u rezultat. Da bi se s2 našao u rezultatu bilo bi porebno da postoje reodvi a2-p2(postoji) i s2-p4(ne postoji). Identično za s3.
3. primer: Samo za vrednost s1 u A postoje parovi sa sve tri vrednost iz B, tj s1-p2, s1-p4 i s1-p1.

Izvršavanje koda

Za primere urađene na vežbama i date za vežbu rezultat možete videti pomoću parsera za relacioni račun i relacionu algebru koleginice Ane Vulović u sintaksi koja se koristi na vežbama. Pomoću .deb paketa možete instalirati program na Ubuntu. Ovaj parser ne morate instalirati na virtuelnoj mašini za vežbe, već možete i na svom Linux sistemu.

Na Ubuntu 20.04 - focal verzija
sudo dpkg -i ./ime_preuzetog_fajla
ili sa
sudo apt install ./ime_preuzetog_fajla

Na Ubuntu 18.04 - 19.* - bionic verzija
sudo dpkg -i ./ime_preuzetog_fajla
ili sa
sudo apt install ./ime_preuzetog_fajla

ime_preuzetog_fajle bi treblo da bude 24-rarrchecker_1.0-1_amd64_bionic.deb ili 24-rarrchecker_1.0-1_amd64_focal.deb.

Možete probati da intalirate dvoklikom na preuzeti .deb fajl, tima ćete otvoriti Linux Software Installer.
rarrchecker_1.0-1_amd64_bionic.deb
rarrchecker_1.0-1_amd64_focal.deb

Kad instalirate parser možete ga naći među aplikacijama u meniju, ikonica je:

Ukoliko nemate odgovarajuće podatke u parseru možete ih učitate iz datoteke. U parseru idete menu > create database, zatim izaberete preuzetu datoteku. U datoteku možete dodati podatke ukoliko želite.

ČAS 11

Relacioni račun

Projekcija

biramo atribute/kolone
range of red is relacija
red.A, red.B

Restrikcija/Selekcija

biramo redove koje želimo u rezultatu
range of red is relacija
red.X
WHERE ...

Proizvod

Dekartov proizvod - spaja svaki red jedne tabele sa svakim redom druge tabele
range of px is predmet
range of ix is ispit

exists x (uslov)

Navedeni uslov mora da važi za bar jednu n-torku x. Domen relacije x je samo uslov, ne možemo ga koristiti van zagrada uslova.

forall x (uslov)

Navedeni uslov mora da važi za sve n-torke iz x. Domen relacije x je samo uslov, ne možemo ga koristiti van zagrada uslova.
forall x (uslov) <=> not exists x (not uslov)

Implikacija

if form1 then form2
if a then b <=> not p or q

Ako radimo nad dve tabele, na primer predmet i ispit. U rezultatu možemo izdvojiti samo kolone iz na primer predmeta, ali onda kolone iz ispita ne možemo imati u where. Da bi u ovom slučaju gli da koristimo kolone iz where potrebno je da ispisujemo bar jednu kolonu iz tabele ispit (ne nužno onu koju koristimo) ili da koristimo exists/forall.

• exists x exists y (...) <=> exists y exists x (...)
• forall x forall y (...) <=> forall y forall x (...)
• forall x exists y (...) <=/=> exists y forall x (...)
• forall(if p then q) <=>
  forall (not p or q) <=>
  not exists (not (not p or q)) <=>
  not exists (p and not q)

ČAS 12

Primer ispita

Zadaci
Rešenje

Zadaci za vežbanje relacionog računa i algebre

Zadaci

Rokovi

Snimci konsultacija na kojima su rađeni rokovi:

Snimak 3.1, jan1 2022, šifra: Rbp03.01
Snimak 5.1, sep1 2021, šifra: Rbp05.01