Uvod u relacione baze podataka¶
prof. dr Saša Malkov
Univerzitet u Beogradu - Matematički fakultet
2023/24
Čas 4 - Uvod u SQL¶
Na početku moramo da podesimo okruženje i povežemo se sa bazom podataka:
%run db2-jupyter-master/db2.ipynb
%sql connect to stud2020 user smalkov using ?
%sql set schema da
Db2 Extensions Loaded.
Connection successful. Command completed.
Rekurzivni upiti¶
Rekurzivni upiti se upotrebljavaju za pronalaženje tranzitivnih zatvorenja određenih relacija. Na primer, ako tabela sadrži grane grafa, onda rekurzivni upit može da pronađe sve parove indirektno povezanih čvorova.
Rekurzivni upiti se pišu u obliku podupita naredbe WITH
tako što se podupit gradi kao unija svih redova (obavezno UNION ALL
), gde prvi skup predstavlja početni korak (iteraciju 0, tj. neposredne veze podataka), a drugi predstavlja opis kako se na osnovu rezultata iteracije N (referenca na podupit) gradi dopuna do iteracije N+1.
with ...
rekurzivan_upit as (
<pocetni korak, ne koristi "rekurzivan upit">
union all
<rekurzivni korak, koristi "rekurzivan upit">
)
...
Prvi (nerekurzivan) deo upita se obično pravi sasvim jednostavno. Međutim, drugi deo može da bude složeniji. On obavezno sadrži spajanje nekih drugih podataka sa prethodnom iteracijom podupita rekurzivan_upit
.
Pri tome ima nekoliko pravila koja moraju da budu poštovana:
Rekurzivni podupit ne sme da sadrži spajanje pomoću operatora
JOIN
. Umesto toga koristi se implicitno spajanje: tabele se navode izaFROM
, a uslov spajanja se navodi izaWHERE
.Početni i rekurzivni korak moraju da imaju isti broj kolona. Štaviše, sve kolone moraju da imaju isti tip, pa čak i isti naziv. Zato što se radi o dva suštinski različita upita, često je jednostavnije da se spisak naziva kolona navede pri imenovanju podupita:
... rekurzivan_upit( ...imena kolona...) as (...telo upita...) ...
Upit se izračunava na sledeći način:
- Najpre izračuna početni (nerekurzivni korak);
- Zatim se računa rekurzivni korak, a kao sadržaj upita (rekurzivna referenca na upit) se koristi rezultat prethodnog koraka;
- Ponavlja se korak 2. sve dok se kao njegov rezultat ne dobije prazan skup. Pri tome se svaki put kao sadržaj rekurzivno referisanog upita koristi samo rezultat prethodnog ponavljanja tog koraka.
Primer - Tranzitivne uslovnosti predmeta¶
Naredni primer traži sve uslovnosti predmeta koje nisu neposredne. Neposredne uslovnosti su opisane u tabeli USLOVNIPREDMET
, a posredne mogu da se izračunaju na osnovu njih. Da bi se došlo do rezultata primenjujemo rekurziju:
- početni korak je izdvajanje neposrednih uslovnosti;
- naredni korak pravimo izračunavanjem za jedan daljeg puta, tj. spajanjem prethodno izračunatih uslovnosti i neposrednih uslovnosti.
U prvom koraku će se izračunati neposredne uslovnosti, sa udaljenošću 1. U drugom koraku se računaju uslovnosti dužine 2, zatim dužine 3 i tako do kraja sve dok se ne dobije prazan rezultat. U našem primeru nema uslovnosti dužine 5, pa će posle 5. koraka izračunavanje da se završi.
Glavni upit povezuje dobijene uslovnosti sa podacima o predmetima.
%%sql -a
with
uslovljenost( idprograma, idpredmeta, iduslovnogpredmeta, udaljenost ) as (
-- neposredno ustanovljene uslovnosti
select idprograma, idpredmeta, iduslovnogpredmeta, 1
from uslovnipredmet
union all
-- nadovezivanje up na u, gradjenje nove iteracije
select u.idprograma, u.idpredmeta, up.iduslovnogpredmeta, u.udaljenost+1
from uslovljenost u, uslovnipredmet up
where u.idprograma = up.idprograma
and u.iduslovnogpredmeta = up.idpredmeta
)
select u.*, p1.naziv, p2.naziv
from uslovljenost u
join predmet p1
on u.idpredmeta = p1.id
join predmet p2
on u.iduslovnogpredmeta = p2.id
--order by p1.naziv, p2. naziv, udaljenost
IDPROGRAMA | IDPREDMETA | IDUSLOVNOGPREDMETA | UDALJENOST | NAZIV | NAZIV | |
---|---|---|---|---|---|---|
0 | 101 | 1600 | 1595 | 1 | Programiranje 2 | Programiranje 1 |
1 | 101 | 1883 | 1594 | 1 | Diskretna matematika | Uvod u matematicku logiku |
2 | 101 | 1889 | 1878 | 1 | Algebra 2 | Algebra 1 |
3 | 101 | 1890 | 2166 | 1 | Diferencijalne jednacine A | Analiza 1 |
4 | 101 | 1890 | 2167 | 1 | Diferencijalne jednacine A | Linearna algebra |
... | ... | ... | ... | ... | ... | ... |
643 | 101 | 1932 | 2166 | 4 | Matematicke metode kvantne mehanike | Analiza 1 |
644 | 101 | 2023 | 1595 | 4 | Konstrukcija kompilatora | Programiranje 1 |
645 | 101 | 2490 | 2166 | 4 | Odabrana poglavlja analize | Analiza 1 |
646 | 103 | 2335 | 1578 | 4 | Metodologija strucnog i naucnog rada | Programiranje 1 |
647 | 103 | 2491 | 1578 | 4 | Istorija i filozofija racunarstva | Programiranje 1 |
648 rows × 6 columns
Ako ne navedemo uređivanje rezultata upita, onda će rezultat obično sadržati redove onim redom kako su izračunavani. U slučaju rekurzivnog upita, to lepo ilustruje ranije naglašen redosled izračunavanja.
Primetimo da rezultat može da sadrži ponavljanja. Neke zavisnosti predmeta mogu da budu i neposredno izražene i posredno izvodive. Takođe, neke zavisnosti mogu da se izvedu različitim putem. Zato nam čak ni upotreba DISTINCT
u upitu neće do kraja pomoći.
Rešenje je da grupišemo sve podatke za isti par predmeta i izdvojimo samo jednu minimalnu dužinu puta uslovljenosti. To radimo dodavanjem novog podupita najkrace
, koji ćemo zatim da koristimo u glavnom upitu umesto podupita uslovljenost
:
%%sql -a
with
uslovljenost( idprograma, idpredmeta, iduslovnogpredmeta, udaljenost ) as (
-- neposredno ustanovljene uslovnosti
select idprograma, idpredmeta, iduslovnogpredmeta, 1
from uslovnipredmet
union all
-- nadovezivanje up na u, gradjenje nove iteracije
select u.idprograma, u.idpredmeta, up.iduslovnogpredmeta, u.udaljenost+1
from uslovljenost u, uslovnipredmet up
where u.idprograma = up.idprograma
and u.iduslovnogpredmeta = up.idpredmeta
),
najkrace as (
select idprograma, idpredmeta, iduslovnogpredmeta, min(udaljenost) as udaljenost
from uslovljenost
group by idprograma, idpredmeta, iduslovnogpredmeta
)
select u.*, p1.naziv, p2.naziv
from najkrace u
join predmet p1
on u.idpredmeta = p1.id
join predmet p2
on u.iduslovnogpredmeta = p2.id
order by p1.naziv, p2. naziv, udaljenost
IDPROGRAMA | IDPREDMETA | IDUSLOVNOGPREDMETA | UDALJENOST | NAZIV | NAZIV | |
---|---|---|---|---|---|---|
0 | 103 | 2492 | 1578 | 1 | Alati za razvoj softvera | Programiranje 1 |
1 | 103 | 2492 | 1588 | 1 | Alati za razvoj softvera | Programiranje 2 |
2 | 301 | 1684 | 1680 | 1 | Algebarska geometrija | Algebra 4 |
3 | 301 | 1684 | 2295 | 1 | Algebarska geometrija | Komutativna algebra |
4 | 103 | 2328 | 1580 | 1 | Algebra 1 | Diskretne strukture 1 |
... | ... | ... | ... | ... | ... | ... |
444 | 101 | 2019 | 1600 | 1 | Vestacka inteligencija | Programiranje 2 |
445 | 101 | 1982 | 1976 | 1 | Vremenske serije i primene u finansijama | Slucajni procesi |
446 | 101 | 1982 | 2381 | 2 | Vremenske serije i primene u finansijama | Uvod u verovatnocu |
447 | 101 | 1913 | 1878 | 1 | Zasnivanje matematike | Algebra 1 |
448 | 101 | 1913 | 1594 | 1 | Zasnivanje matematike | Uvod u matematicku logiku |
449 rows × 6 columns
Ako bismo želeli da izdvojimo samo zavisnosti koje nisu neposredne, onda je potrebno da dodamo restrikciju po onim grupama za koje je minimalna udaljenost veća od 1:
%%sql
with
uslovljenost( idprograma, idpredmeta, iduslovnogpredmeta, udaljenost ) as (
-- neposredno ustanovljene uslovnosti
select idprograma, idpredmeta, iduslovnogpredmeta, 1
from uslovnipredmet
union all
-- nadovezivanje up na u, gradjenje nove iteracije
select u.idprograma, u.idpredmeta, up.iduslovnogpredmeta, u.udaljenost+1
from uslovljenost u, uslovnipredmet up
where u.idprograma = up.idprograma
and u.iduslovnogpredmeta = up.idpredmeta
),
najkrace as (
select idprograma, idpredmeta, iduslovnogpredmeta, min(udaljenost) as udaljenost
from uslovljenost
group by idprograma, idpredmeta, iduslovnogpredmeta
)
select u.*, p1.naziv, p2.naziv
from najkrace u
join predmet p1
on u.idpredmeta = p1.id
join predmet p2
on u.iduslovnogpredmeta = p2.id
where udaljenost > 1
order by p1.naziv, p2. naziv, udaljenost
IDPROGRAMA | IDPREDMETA | IDUSLOVNOGPREDMETA | UDALJENOST | NAZIV | NAZIV | |
---|---|---|---|---|---|---|
0 | 101 | 1915 | 2166 | 2 | Analiza 3A | Analiza 1 |
1 | 101 | 1957 | 2166 | 2 | Analiza 3A | Analiza 1 |
2 | 101 | 1972 | 2166 | 2 | Analiza 3A | Analiza 1 |
3 | 101 | 1918 | 2166 | 2 | Analiza 3B | Analiza 1 |
4 | 101 | 1958 | 2166 | 2 | Analiza 3B | Analiza 1 |
... | ... | ... | ... | ... | ... | ... |
46 | 101 | 2403 | 2166 | 2 | Uvod u teoriju ekstremalnih problema | Analiza 1 |
47 | 101 | 1967 | 2166 | 2 | Varijacioni racun | Analiza 1 |
48 | 101 | 1967 | 2357 | 2 | Varijacioni racun | Analiza 2 |
49 | 101 | 1967 | 2167 | 2 | Varijacioni racun | Linearna algebra |
50 | 101 | 1982 | 2381 | 2 | Vremenske serije i primene u finansijama | Uvod u verovatnocu |
51 rows × 6 columns
Pogledi¶
Ako se neki upit ili podupit često koristi, onda može da se ugradi kao logički objekat baze podataka koji se naziva pogled, koji se zatim upotrebljava kao tabela, ali ne sadrži podatke fizički. Pogled se ponaša kao vid virtualne tabele, koja se izračunava svaki put kada se koristi. Upotreba pogleda olakšava pisanje upita, ali ne doprinosti performansama.
Naredba za pravljenje pogleda ima sintaksu:
CREATE [OR REPLACE] VIEW <name> [( <lista imena kolona> )]
AS <upit>
...
Kasnije ćemo videti neke od opcija koje se mogu navesti pri pravljenju pogleda.
U narednom primeru pravimo pogled koji izdvaja samo studente osnovnih studija. Čitamo sve podatke iz tabele DOSIJE
, koji se odnose na studente koji su upisani na neki program osnovnih studija:
%%sql
-- pravimo pogled sa svim studentima osnovnih studija
create or replace view dosije_osnovne as
select d.*
from dosije d
join studijskiprogram sp
on d.idprograma = sp.id
where sp.idnivoa = 1
Command completed.
%%sql
select *
from dosije_osnovne
INDEKS | IDPROGRAMA | IME | PREZIME | POL | MESTORODJENJA | IDSTATUSA | DATUPISA | DATDIPLOMIRANJA | |
---|---|---|---|---|---|---|---|---|---|
0 | 20150369 | 101 | Jelena | Mandic | z | Jagodina | -1 | 2015-07-07 | <NA> |
1 | 20150069 | 101 | Nemanja | Nicic | m | Sabac | -2 | 2015-07-06 | 2019-09-12 |
2 | 20150090 | 103 | Andrijana | Beara | z | Sabac | -2 | 2015-07-06 | 2019-09-06 |
3 | 20150320 | 103 | Milos | Milenkovic | m | Krusevac | 2 | 2015-07-06 | <NA> |
4 | 20150185 | 101 | Valentina | Stojanov | z | Beograd (Savski venac) | 2 | 2015-07-06 | <NA> |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
2685 | 20170115 | 103 | Andrea | Novakovic | z | Beograd (Savski venac) | 2 | 2017-07-04 | <NA> |
2686 | 20180100 | 103 | Tara | Bozinovic | z | Beograd (Savski venac) | 1 | 2018-07-02 | <NA> |
2687 | 20190011 | 101 | Ana | Ilic | z | Vrsac | 1 | 2019-07-01 | <NA> |
2688 | 20190254 | 101 | Tamara | Zejak | z | Beograd (Savski venac) | 1 | 2019-07-02 | <NA> |
2689 | 20200213 | 103 | Uros | Stanojkov | m | Uzice | 1 | 2020-07-15 | <NA> |
2690 rows × 9 columns
U narednom primeru pravimo pogled koji sadrži samo one podatke o ispitima koji predstavljaju potvrdu da je ispit položen:
%%sql
-- pravimo pogled sa svim polozenim predmetima
create or replace view polozenpredmet as
select indeks, idpredmeta, datpolaganja, poeni, ocena
from ispit
where status = 'o'
and ocena > 5
Command completed.
%sql select count(*) from ispit
1 | |
---|---|
0 | 119143 |
%sql select count(*) from polozenpredmet
1 | |
---|---|
0 | 32823 |
Brisanje pogleda¶
Pogled se briše naredbom:
DROP VIEW <ime pogleda>
Kasnije ćemo videti da se naredba DROP
koristi i za brisanje drugih vrsta objekata baze podataka.
%%sql
drop view dosije_osnovne
Command completed.
%%sql
drop view polozenpredmet
Command completed.
Naredbe za menjanje podataka¶
Osnovne naredbe za menjanje podataka su:
INSERT
- dodavanje novih redova u tabelu;UPDATE
- promena sadržaja postojećih redova u tabeli iDELETE
- brisanje redova iz tabele.
U osnovnim oblicima ove naredbe su relativno jednostavne. Složenije forme imaju u kombinaciji sa upitima ili delovima upita koje smo već upoznali.
Naredba INSERT
¶
Naredba INSERT
služi za dodavanje novih redova u izabranu tabelu:
INSERT INTO <naziv tabele> [( <lista naziva kolona> )]
<upit>
Lista kolona je opciona. Ako se ne navede, podrazumevaju se sve kolone date tabela u redosledu u kome su navedene pri definisanju tabele.
Ako se navede lista naziva kolona, onda će vrednosti nenavedenih atributa u dodatim redovima biti ili njihove podrazumevane vrednosti (ako su definisane) ili nedefinisane vrednosti (ako nisu definisane podrazumevane vrednosti). Zbog toga lista naziva kolona mora da sadrži sve kolone koje ne smeju da sadrže nedefinisane vrednosti i nemaju definisane podrazumevane vrednosti.
U osnovnom obliku se kao upit navodi klauzula VALUES
sa spiskom redova. U složenijim slučajevima se navodi proizvoljan upit. U oba slučaja broj i redosled kolona moraju da odgovaraju broju i redosledu kolona u navedenoj listi naziva kolona (ako je navedena) ili broju i redosledu kolona tabele (ako nije navedena lista kolona).
U prvom primeru navodimo naredbu bez naziva kolona:
%%sql
-- upisujemo izborni predmet Teorija izracunljivosti (2022)
-- u prvom semestru 2020/21
-- studentima 20180008, 20180012, 20180057 i 20180145
insert into upisankurs
values
( 20180008, 2020, 1, 2022 ),
( 20180012, 2020, 1, 2022 ),
( 20180057, 2020, 1, 2022 ),
( 20180145, 2020, 1, 2022 )
Command completed.
U narednom primeru imamo isti upit ali sa eksplicitno navedenim imenima kolona. Dodavanje neće uspeti zato što ne možemo da dodamo podatke koji već postoje:
%%sql
-- upisujemo izborni predmet Teorija izracunljivosti (2022)
-- u prvom semestru 2020/21
-- studentima 20180008, 20180012, 20180057 i 20180145
insert into upisankurs( indeks, skgodina, semestar, idpredmeta )
values
( 20180008, 2020, 1, 2022 ),
( 20180012, 2020, 1, 2022 ),
( 20180057, 2020, 1, 2022 ),
( 20180145, 2020, 1, 2022 )
Command completed.
%%sql
select * from upisankurs
INDEKS | SKGODINA | SEMESTAR | IDPREDMETA | |
---|---|---|---|---|
0 | 20150069 | 2015 | 1 | 1594 |
1 | 20150069 | 2015 | 1 | 1595 |
2 | 20150069 | 2015 | 1 | 2166 |
3 | 20150069 | 2015 | 1 | 2167 |
4 | 20150069 | 2015 | 1 | 2168 |
... | ... | ... | ... | ... |
74039 | 20200165 | 2020 | 1 | 2168 |
74040 | 20180008 | 2020 | 1 | 2022 |
74041 | 20180012 | 2020 | 1 | 2022 |
74042 | 20180057 | 2020 | 1 | 2022 |
74043 | 20180145 | 2020 | 1 | 2022 |
74044 rows × 4 columns
U narednom primeru ćemo da obrišemo prethodno dodate podatke:
%%sql
delete from upisankurs
where indeks in (20180008, 20180012, 20180057, 20180145)
and idpredmeta = 2022
and skgodina = 2020 and semestar = 1
Command completed.
Isti rezultat dodavanja bi mogao da se dobije i primenom podupita (ali ne nakon što su redovi već dodati):
%%sql
-- upisujemo izborni predmet Teorija izracunljivosti (2022)
-- u prvom semestru 2020/21
-- studentima 20180008, 20180012, 20180057 i 20180145
insert into upisankurs
with indeksi(indeks) as ( values 20180008, 20180012, 20180057, 20180145 )
select indeks, 2020, 1, 2022
from indeksi
Command completed.
Naredba DELETE
¶
Naredba DELETE
služi za brisanje postojećih redova iz izabrane tabele:
DELETE FROM <naziv tabele>
[ WHERE <uslov> ]
Navođenje uslova je opciono, ali je važno da se ne izostavlja baz jasne namere - ako se ne navede uslov obrisaće se svi redovi tabele i neće biti pitanja "da li ste sigurni?".
%%sql
-- Ispisujemo upisan predmet 2022 navedenim studentima u tekućoj šk.godini
delete from upisankurs
where indeks in ( 20180008, 20180012, 20180057, 20180145 )
and idpredmeta = 2022
and skgodina = 2020
Command completed.
Naredba UPDATE
¶
Naredba UPDATE
služi za menjanje vrednosti postojećih redova tabele:
UPDATE <naziv tabele>
SET { <pojedinacne dodele> | <grupna dodela> }
WHERE <uslov>
Pojedinačne dodele se vrše u obliku:
<ime kolone> = <izraz> [, <ime kolone> = <izraz>]*
a grupne u obliku:
( <lista kolona> ) = <upit>
gde upit izračunava tačno jedan red sa odgovarajućim kolonama. U oba slučaja mogu da se koriste podupiti.
Za početak proverimo jedan podatak o polaganju, koji ćemo kasnije da menjamo:
%%sql
select *
from ispit
where indeks = 20150185
and idpredmeta = 1907
and status = 'o'
SKGODINA | OZNAKAROKA | INDEKS | IDPREDMETA | STATUS | DATPOLAGANJA | POENI | OCENA | |
---|---|---|---|---|---|---|---|---|
0 | 2019 | jan1 | 20150185 | 1907 | o | 2020-02-01 | 71 | 8 |
%%sql
-- studentu 20150185 ispraviti pogresno upisanu ocenu 8 i upisati 9
-- i povecati broj poena za 10
-- iz predmeta 'Odabrana poglavlja astronomije' (ID=1907)
-- u ispitnom roku jan1/2020 (sk.godina 2019)
update ispit
set ocena = 9,
poeni = poeni + 10
where indeks = 20150185
and idpredmeta = 1907
and status = 'o'
and ocena = 8
Command completed.
%%sql
-- Poništavamo izmenu i vraćamo stare podatke:
update ispit
set ocena = 8,
poeni = 71
where indeks = 20150185
and idpredmeta = 1907
and status = 'o'
Command completed.
%%sql
-- isto kao prethodno ažuriranje, ali sa drugom sintaksom
update ispit
set ( ocena, poeni ) = ( 9, poeni + 10 )
where indeks = 20150185
and idpredmeta = 1907
and status = 'o'
and ocena = 8
Command completed.
%%sql
-- Poništavamo izmenu ali sa drugom sintaksom:
update ispit
set ( ocena, poeni ) = ( 8, 71 )
where indeks = 20150185
and idpredmeta = 1907
and status = 'o'
Command completed.
Naredba MERGE
¶
Naredba MERGE
ažurira ciljnu tabelu koristeći datu izvornu tabelu. Redovi ciljne tabele koji odgovaraju redovima izvodne tabele se ili brišu ili ažuriraju, a redovi koji postoje samo u izvornoj tabeli se dodaju u ciljnu.
Sintaksa je:
MERGE INTO <ime tabele>
<upit> USING <upit> ON <uslov povezivanja>
{ WHEN [NOT] MATCHED [AND <uslov>] THEN <izraz> }+
[ELSE IGNORE]
gde <izraz>
može da bude:
UPDATE ... SET <pojedinacne dodele ili grupna dodela>
DELETE ...
INSERT [(<lista kolona>)] VALUES <izraz>
a ...
su dodatne opcije.
Na primer, ako bi u tabeli SEP2020
bili upisani ažurni rezultati ispita u septembarskom roku, a u tabeli ISPIT
možda postoje neki od tih podataka, ali su možda neažurni, onda bi prepisivanje svih rezultata ispita u tabelu ISPIT
moglo da se uradi naredbom:
%%sql
merge into ispit i
using (select * from sep2020) sep20
on i.skgodina = sep20.skgodina
and i.oznakaroka = sep20.oznakaroka
and i.indeks = sep20.indeks
and i.idpredmeta = sep20.idpredmeta
when matched then
update set
i.status = sep20.status,
i.datpolaganja = sep20.datpolaganja,
i.poeni = sep20.poeni,
i.ocena = sep20.ocena
when not matched then
insert values (
sep20.skgodina,
sep20.oznakaroka,
sep20.indeks,
sep20.idpredmeta,
sep20.status,
sep20.datpolaganja,
sep20.poeni,
sep20.ocena
)
SQL0204N "DA.SEP2020" is an undefined name. SQLSTATE=42704 SQLCODE=-204
Command completed.
Menjanje podataka i pogledi¶
Naredbe za menjanje podataka mogu da se primenjuju na poglede (umesto tabela) u slučajevima kada je nedvosmisleno jasno šta se i kako menja. Ako se pogled dobija iole složenijom operacijom, a ne samo čitanjem iz jedne tabele, onda obično ne može da se koristi za menjanj podataka.
Pogled ne može da se koristi za menjanje podataka ako se u njegovoj definiciji:
- spaja više tabela;
- koristi grupisanje;
- koriste skupovne operacije ili
- neke kolone predstavljaju rezultat izraza.
U tim slučajevima nije nedvosmisleno definisano koje podatke je potrebno promeniti.
Podsetimo se ranije napravljenog pogleda kojim se izdvajaju podaci o studentima osnovnih studija:
%%sql
-- pravimo pogled sa svim studentima osnovnih studija
create or replace view dosije_osnovne as
select d.*
from dosije d
join studijskiprogram sp
on d.idprograma = sp.id
where sp.idnivoa = 1
Command completed.
Ako bi se pokušalo menjanje podataka posredstvom ovog pogleda, nije sasvim jasno koji redovi bi trebalo da se menjaju, zato što obično postoji mogućnost da neki redovi ne budu spojeni ili da budu spojeni više puta i slično.
U nekim slučajevima ovaj problem bi mogao da se reši drugačijim definisanjem pogleda. Na primer, prethodni pogled može da se definiše upitom nad tačno jednom tabelom, a da se druga tabela upotrebi u podupitu. Takav pogled može da se koristi za menjanje podataka:
%%sql
-- pravimo pogled sa svim studentima osnovnih studija
create or replace view dosije_osnovne as
select *
from dosije d
where d.idprograma in (
select id
from studijskiprogram
where idnivoa = 1
)
Command completed.
Menjanje podataka u pogledima može da se rešava i pomoću okidača. Osnovna ideja je da se eksplicitno opisuje postupak kako se izvode operacije menjanja potataka posredstvom pogleda. Više reči o tome će biti na jednom od narednih časova.
Ograničavanje promena putem pogleda¶
Menjanje podataka putem pogleda može da ima i neke možda neočekivane posledice. Može da se ispostavi da novi ili izmenjeni podatak ne odogovara uslovima pogleda i da se ne vidi kroz pogled, iako je putem njega nastao ili dobio vrednost.
Prethodni pogled obuhvata samo studente osnovnih studija. Ako bismo probali da dodamo novi red u taj pogled, ali takav da se odnosi na nekog studenta master studenta, onda bi takvo dodavanje uspelo (!!!) ali novi red ne bi mogao da se pročita istim pogledom. Slično tome, ako bismo nekom studentu naredbom UPDATE
na tom pogledu zamenili upisan studijski program nekim programom master studija, i ta promena bi uspela (!!!) ali se izmenjen red više ne bi video tim pogledom. Ispalo bi kao da smo red obrisali iz pogleda, dok bi on ostao u tabeli DOSIJE
.
Takve promene nekada mogu da imaju smisla, ali najčešće bi trebalo da omogućimo podrazumevano ponašanje pogleda, tako da problematične operacije dodavanja i menjanja podataka proizvode grešku ili jednostavno ne rade.
Ako je potrebno da se pri radu sa pogledom proverava da li nastaju izmene koje nisu u skladu sa uslovima pogleda, onda se pogled pravi sa tzv. opcijom provere. Opcija provere se navodi na samom kraju definicije pogleda i ima oblik:
...
[ WITH [ LOCAL | CASCADED ] CHECK OPTION ]
Provera može da bude lokalna ili kaskadna. Lokalna provera znači da će se za proveru koristiti uslov naveden pri definisanju pogleda. Kaskadna znači da će se koristiti i taj uslov i (konjunkcijom) svi uslovi restrikcije koji su navedeni na pogledima na kojima on počiva (ako je pogled napravljen nad drugim pogledom a ne nad tabelom), bez obzira na to da li je u njima navođena opcija provere.
U svakom slučaju, ako na pogledu V postoji opcija provera, a pogled Q se definiše nad pogledom V, svaki pokušaj menjanja podataka putem Q će proveravati da li je ispunjen uslov provere na V.
Klauzula provere ne sme da se navodi ako je pogled samo za čitanje (tj. ako nije dopušteno menjanje, kao što je već ovde opisano) ili ako su definisani okidači za menjanje pogleda.
U narednom primeru pravimo pogled sa dosijeima osnovnih studija, sa opcijom provere uslova menjanja:
%%sql
create or replace view dosije_osnovne as
select *
from dosije d
where d.idprograma in (
select id
from studijskiprogram
where idnivoa = 1
)
with check option
Command completed.
Pravljenje objekata baze podataka¶
Za pravljenje, menjanje i brisanje objekata baze podataka postoji veći broj odgovarajućih naredbi, oblika:
CREATE <vrsta objekta> ...
ALTER <vrsta objekta> ...
DROP <vrsta objekta>
Pravljenje tabela baze podataka¶
Za pravljenje tabela se koristi naredba
CREATE TABLE [ IF NOT EXISTS ] <ime tabele>
( <lista elemenata> )
[ <opcije> ]
Elementi se razdvajaju zapetama. Element tabele može da bude:
- definicija kolone;
- definicija primarnog ključa;
- definicija stranog ključa;
- definicija jedinstvenog ključa;
- definicija uslova integriteta
Definicija kolone¶
Definicija kolone ima oblik:
<ime kolone> <tip> [<opcije>]
Gde opcije mogu da budu:
NOT NULL
- isključenje nedefinisanih vrednosti:[ CONSTRAINT <ime> ] <uslov integriteta> [...]
- neki od uslova integriteta;[WITH] DEFAULT <vrednost>
- definisanje podrazumevane vrednosti;- i mnoge druge.
Uslovi integriteta kolone mogu da budu:
PRIMARY KEY
- kolona pripada primarnom ključu;UNIQUE
- nikoja dva reda ne smeju imati istu vrednost u koloni;uslov referisanja (stranog ključa) oblika:
REFERENCES
[ ] [ ON DELETE { NO ACTION | RESTRICT | CASCADE | SET NULL } ] [ ON UPDATE { NO ACTION | RESTRICT } ] [...] CHECK( <uslov> )
- uslov ograničenja domena, u kome ne smeju da se neposredno pojavljuju druge kolone.
Definicija primarnog ključa¶
Primarni ključ se definiše u obliku:
[ <CONSTRAINT <ime> ] PRIMARY KEY (<lista imena kolona>)
Definicija jedinstvenog ključa¶
Jedinstveni ključ se definiše u obliku:
[ <CONSTRAINT <ime> ] UNIQUE (<lista imena kolona>)
Definicija stranog ključa¶
Definicija stranog ključa ima oblik:
[ <CONSTRAINT <ime> ] FOREIGN KEY( <lista imena kolona> )
REFERENCES <ime tabele> [<lista kolona>]
[ ON DELETE { NO ACTION | RESTRICT | CASCADE | SET NULL } ]
[ ON UPDATE { NO ACTION | RESTRICT } ]
[...]
Definicija uslova integriteta¶
Uslov integriteta koji predstavlja element tabele se definiše kao uslov koji mora da zadovolji red tabele da bi smeo da postoji. U okviru uslova mogu da se pojave sve kolone jednog reda tabele:
[ <CONSTRAINT <ime> ] CHECK ( <uslov> )
Primeri...¶
Primeri pravljenja tabela postoje u skriptovima za pravljenje studentske baze podataka...
Alternativni načini pravljenja tabela¶
Ako već imamo napravljene neke tabele baze podataka, onda nam često može biti jednostavnije da nove tabele pravimo po uzoru na neke već postojeće. Sistem DB2 omogućava dva načina da to uradimo.
Ako želimo da napravimo tabelu koja ima istu strukturu (kolone) kao neka druga, onda to radimo u obliku:
CREATE TABLE [ IF NOT EXISTS ] <ime tabele>
LIKE <ime postojeće tabele>
[...]
Ako želimo da napravimo tabelu koja je slična nekim postojećim, onda možemo da napravimo upit koji pravi uzorak podataka i na osnovu tipova izdvojenih kolona će se ustanoviti struktura nove tabele:
CREATE TABLE [ IF NOT EXISTS ] <ime tabele>
[(<lista imena kolona>)]
AS ( <upit> )
WITH [NO] DATA
ima istu strukturu (kolone) kao neka druga, onda to radimo u obliku:
%%sql
create table ispit_a like ispit
Command completed.
%%sql
create table ispit_b as (
select i.*, p.naziv
from ispit i
join predmet p
on i.idpredmeta = p.id
)
with data
Command completed.
%%sql
select * from ispit_b
limit 100
SKGODINA | OZNAKAROKA | INDEKS | IDPREDMETA | STATUS | DATPOLAGANJA | POENI | OCENA | NAZIV | |
---|---|---|---|---|---|---|---|---|---|
0 | 2015 | jan1 | 20150112 | 2170 | o | 2016-01-18 | 73 | 8 | Uvod u organizaciju i arhitekturu racunara 1 |
1 | 2015 | jan1 | 20150069 | 1594 | o | 2016-02-03 | 100 | 10 | Uvod u matematicku logiku |
2 | 2015 | jan1 | 20150069 | 1595 | o | 2016-02-02 | 96 | 10 | Programiranje 1 |
3 | 2015 | jan1 | 20150069 | 2168 | o | 2016-01-30 | 92 | 10 | Strani jezik |
4 | 2015 | jan1 | 20150109 | 1594 | o | 2016-02-17 | 100 | 10 | Uvod u matematicku logiku |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
95 | 2015 | jan1 | 20150405 | 2174 | o | 2016-01-22 | 99 | 10 | Engleski jezik 1 |
96 | 2015 | jan1 | 20150280 | 1594 | o | 2016-02-17 | 95 | 10 | Uvod u matematicku logiku |
97 | 2015 | jan1 | 20150280 | 1595 | o | 2016-02-02 | 99 | 10 | Programiranje 1 |
98 | 2015 | jan1 | 20150280 | 2168 | o | 2016-01-30 | 95 | 10 | Strani jezik |
99 | 2015 | jan1 | 20150274 | 1594 | o | 2016-02-17 | 9 | 5 | Uvod u matematicku logiku |
100 rows × 9 columns
Menjanje strukture tabele¶
Za menjanje strukture tabele služi naredba:
ALTER TABLE <ime tabele>
{ ADD <element> | ALTER <element> | DROP <element> }*
[ <opcije> ]
Sintaksa je vrlo bogata, pokazaćemo samo za kolone.
%%sql
alter table predmet
add column nalik_na int
add constraint fk_predmet foreign key(nalik_na)
references predmet
on delete set null
on update restrict
Command completed.
%%sql
select * from predmet
limit 20
ID | OZNAKA | NAZIV | ESPB | NALIK_NA | |
---|---|---|---|---|---|
0 | 1578 | P100 | Programiranje 1 | 8 | <NA> |
1 | 1580 | M105 | Diskretne strukture 1 | 6 | <NA> |
2 | 1588 | P101 | Programiranje 2 | 8 | <NA> |
3 | 1590 | M106 | Diskretne strukture 2 | 6 | <NA> |
4 | 1594 | M1.02 | Uvod u matematicku logiku | 5 | <NA> |
... | ... | ... | ... | ... | ... |
15 | 1684 | 3M013 | Algebarska geometrija | 9 | <NA> |
16 | 1685 | 3M016 | Homoloske metode u algebri | 9 | <NA> |
17 | 1686 | 3M019 | Model-teoretska algebra | 9 | <NA> |
18 | 1687 | 3M022 | Nekomutativna algebra | 9 | <NA> |
19 | 1688 | 3M025 | Uvod u Hopfove algebre | 9 | <NA> |
20 rows × 5 columns
%%sql
alter table predmet
--drop constraint fk_predmet
drop column nalik_na
Command completed.
Nakon promene strukture tabele,
a u zavisnosti od njene stukture, vrste promene i stepena popunjenosti,
može da se dogodi da neke operacije i upiti neće raditi.
Na primer, neke skupovne operacije mogu da budu onemogućene.
Zbog toga je posle promene strukture tabele potrebno da se izvrši i njena reorganizacija,
naredbom REORG TABLE <tabela>
.
Ova naredba ne radi u okviru Jupytera.
%%sql
reorg table da.predmet
SQL0104N An unexpected token "da" was found following "reorg table ". Expected tokens may include: "JOIN". SQLSTATE=42601 SQLCODE=-104
Command completed.
%%sql
drop table ispit_a
Command completed.
%%sql
drop table ispit_b
Command completed.