Poglavlje 8: Projektovanje baze podataka

Priča za početak. Šezdesetih godina podaci velikih firmi čuvali su se u bazama koje su ličile na lavirint: da bi program našao narudžbu jednog kupca, morao je znati tačan put kroz pokazivače od zapisa do zapisa. Kad bi se struktura promijenila, svi programi su se morali prepisati. Matematičar Edgar F. Codd, koji je radio u istraživačkom centru firme IBM u Kaliforniji, predložio je 1970. godine nešto sasvim drugačije, u radu „A Relational Model of Data for Large Shared Data Banks” („Relacioni model podataka za velike zajedničke banke podataka”). Podaci se čuvaju u običnim tabelama, a veza između tabela nije skriveni pokazivač nego ista vrijednost u dvije kolone. Program ne pita „kojim putem do podatka”, nego samo „šta želim”, a baza sama nađe put. U početku ni sam IBM nije žurio s tom idejom, jer je zarađivao na starim bazama. Ali ideja je bila prejaka: krajem sedamdesetih pojavile su se prve komercijalne relacione baze s jezikom SQL, a danas skoro svaka banka, bolnica, prodavnica i aplikacija za dostavu čuva podatke upravo u Coddovim tabelama. Codd je 1981. dobio Turingovu nagradu.

U poglavlju 6 nacrtali smo domenski model DeliveryApp: klase Kupac, Restoran, Jelo, Narudžba, Stavka i ostale, s vezama i višestrukošću. Klase žive u memoriji dok program radi; kad se server ugasi, nestanu. Podaci moraju preživjeti: narudžba N-1001 mora postojati i sutra, i za pet godina u izvještaju. Ovo poglavlje pretvara domenski model u relacionu bazu podataka. Prvo učimo tabele, ključeve i ER dijagram, i pravila kako se klase pretvaraju u tabele. Zatim normalizaciju, koja sprečava da isti podatak bude zapisan na dva mjesta. Na kraju pišemo SQL: pravimo tabele, punimo ih, postavljamo upite i čuvamo narudžbu u transakciji. Na praktičnoj nastavi sve radimo u SQLiteu i provjeravamo da baza za N-1001 daje 24,50 €.

Literatura. Osnovna literatura predmeta:

  1. B. Popović, K. Kuk: Informacioni sistemi, Kriminalističko-policijski univerzitet, Beograd, 2017.
  2. J. S. Valacich, J. F. George: Modern Systems Analysis and Design, 9th ed., Pearson, 2020.
  3. L. Jelenković: Sustavi za rad u stvarnom vremenu – skripta, FER, Zagreb, 2015.
  4. D. Milićev: Programiranje u realnom vremenu – skripta, Beograd, 2002.
  5. V. Kovačević, M. Popović: Sistemska programska podrška u realnom vremenu 1, FTN, Novi Sad, 2013.

Za ovo poglavlje posebno: B. Lazarević, Z. Marjanović, N. Aničić, S. Babarogić: Baze podataka, FON, Beograd, 2016.

Riječi ovog poglavlja.

  • Baza podataka – organizovan skup podataka koji program trajno čuva i iz kojeg ih brzo nalazi.
  • SUBP (sistem za upravljanje bazom podataka) – program koji vodi bazu: SQLite, PostgreSQL, MySQL, SQL Server.
  • Tabela (relacija) – podaci o jednoj vrsti stvari, u redovima i kolonama.
  • Red (n-torka) i kolona (atribut) – jedan zapis i jedno svojstvo.
  • Primarni ključ – kolona (ili više njih) čija vrijednost jedinstveno određuje red, kao broj lične karte.
  • Strani ključ – kolona koja čuva primarni ključ reda iz druge tabele i tako pravi vezu.
  • ER dijagram (entity–relationship) – crtež entiteta (tabela) i veza između njih.
  • Normalizacija – raspoređivanje podataka u tabele tako da se svaka činjenica čuva na jednom mjestu.
  • Anomalija – greška koja nastaje kad je ista činjenica zapisana na više mjesta (izmjena na jednom, a ne na drugom).
  • SQL (Structured Query Language) – jezik kojim se prave tabele, upisuju i traže podaci.
  • Upit – pitanje bazi napisano u SQL-u, npr. „koje narudžbe nemaju dostavljača?”.
  • JOIN – spajanje redova dvije tabele po istoj vrijednosti ključa.
  • Indeks – pomoćna struktura koja ubrzava pretragu, kao kazalo na kraju knjige.
  • Transakcija – niz izmjena koje se izvrše sve zajedno ili nijedna.
  • ACID – četiri osobine dobre transakcije: atomarnost, konzistentnost, izolacija, trajnost.

Ciljevi poglavlja. Nakon poglavlja možete: objasniti zašto se podaci DeliveryApp čuvaju u bazi, a ne u tabeli Excela; opisati tabelu, red, kolonu, primarni i strani ključ; nacrtati ER dijagram s vrstama veza; pretvoriti klasni dijagram u tabele po pravilima (1:N, M:N, kompozicija); prepoznati anomalije u nenormalizovanoj tabeli; dovesti tabelu do treće normalne forme; napisati CREATE TABLE s ključevima i ograničenjima; napisati INSERT, SELECT s JOIN i GROUP BY; izračunati u SQL-u zbir artikala N-1001 (2150 centi); objasniti čemu služi indeks; objasniti transakciju i osobine ACID na primjeru narudžbe i plaćanja.

Plan poglavlja – Sedmica 8: tri časa teorije i tri časa praktične nastave

Čas Sadržaj Na času
Teorija 1 8.1–8.3 Zašto baza; tabele i ključevi; od klasa do tabela ER dijagram DeliveryApp
Teorija 2 8.4–8.6 Anomalije i normalizacija Jedna velika tabela → pet malih
Teorija 3 8.7–8.9 SQL, upiti, indeksi, transakcije SUM za N-1001 = 2150; ACID
Praksa 1–3 SQLite: schema.sql, podaci.sql, upiti, transakcija baza.py ispisuje 2450

Dio 1 – Tabele, ključevi i ER dijagram

8.1 Zašto baza, a ne Excel

Prvi DeliveryApp bi se mogao voditi u Excelu: jedna tabela, red po narudžbi. Zašto to ne valja čim sistem počne raditi?

Problem U Excel tabeli U bazi
Istovremeni rad dva konobara otvore isti fajl; jedan pregazi drugog baza pušta stotine korisnika istovremeno, bez gaženja
Ispravnost u ćeliju za količinu može se upisati „dva” ili −3 ograničenje CHECK (kolicina > 0) odbije pogrešan unos
Veze ime restorana se prepisuje u svaki red restoran je zapisan jednom, narudžbe pokazuju na njega
Pretraga filter radi dobro do nekoliko hiljada redova indeks nađe jedan red među milionima za djelić sekunde
Greška usred posla struja nestane dok se upisuje; fajl je napola sačuvan transakcija: sve ili ništa
Pristup ko ima fajl, vidi sve svaka uloga vidi samo svoje (poglavlje 12)

Excel je odličan alat za računanje i analizu (izvještaj na kraju mjeseca). Baza je alat za trajno, ispravno i zajedničko čuvanje podataka. Mnogi sistemi koriste oboje: podaci žive u bazi, a izvještaji se izvoze u tabelu.

Program koji vodi bazu zove se sistem za upravljanje bazom podataka (SUBP; engleski database management system, DBMS). U ovoj knjizi koristimo SQLite, koji dolazi uz Python i čuva cijelu bazu u jednom fajlu. U velikim sistemima češći su PostgreSQL, MySQL, SQL Server i Oracle; jezik SQL je u svima skoro isti.

Ukratko.

  • Excel je dobar za računanje i analizu, a baza za trajno, ispravno i zajedničko čuvanje podataka.
  • Baza podržava istovremeni rad, ograničenja, veze, brzu pretragu, transakcije i prava pristupa.
  • SUBP je program koji vodi bazu; u knjizi koristimo SQLite, a SQL je skoro isti u svim sistemima.

8.2 Tabela, primarni i strani ključ

U relacionoj bazi sve je u tabelama. Tabela čuva podatke o jednoj vrsti stvari: restorane, jela, narudžbe. Svaki red je jedna stvar (jedan restoran), a svaka kolona jedno svojstvo (naziv, je li otvoren).

Tabela restoran:

oznaka naziv otvoren
R-1 Picerija 1
R-2 Roštilj 1
R-3 Salatarnica 1

Primarni ključ je kolona čija vrijednost jedinstveno određuje red. Za restoran je to oznaka: ne mogu postojati dva restorana s oznakom R-1. Slika iz života: broj lične karte. Dvije osobe mogu se zvati isto i živjeti u istoj ulici, ali broj lične karte je samo jedan. Primarni ključ ne smije biti prazan i ne smije se ponavljati; baza to sama provjerava.

Strani ključ je kolona koja čuva primarni ključ reda iz druge tabele. Tako se pravi veza. Tabela jelo ima kolonu restoran u kojoj piše „R-1”; to znači „ovo jelo pripada restoranu R-1”.

id restoran naziv cijena
1 R-1 Pizza Margarita 950
3 R-1 Sok 250
4 R-2 Ćevapi (10 komada) 800

Baza čuva referencijalni integritet: ne dozvoljava da strani ključ pokazuje na nešto čega nema. Ako neko pokuša upisati jelo za restoran „R-9”, baza odbije upis. Ako neko pokuša obrisati restoran R-1 dok postoje njegova jela, baza ili odbije brisanje ili obriše i jela, zavisno od toga kako smo vezu definisali (8.3).

Primijetite da je cijena u centima (950), kao u Python kodu iz poglavlja 6. Novac se u bazama čuva kao cijeli broj centi ili kao poseban decimalni tip, nikad kao obični decimalni broj s pomičnim zarezom, jer bi se sabiranjem hiljada stavki skupile greške zaokruživanja.

Ukratko.

  • Tabela čuva podatke o jednoj vrsti stvari; red je jedna stvar, kolona jedno svojstvo.
  • Primarni ključ jedinstveno određuje red, kao broj lične karte.
  • Strani ključ čuva primarni ključ iz druge tabele i tako pravi vezu.
  • Referencijalni integritet znači da strani ključ ne smije pokazivati na nešto čega nema.
  • Novac se čuva kao cijeli broj centi ili decimalni tip, ne kao broj s pomičnim zarezom.

8.3 ER dijagram i od klasa do tabela

ER dijagram (od engleskog entity–relationship, entitet–veza) crta tabele i veze između njih. Danas se najčešće crta u obliku „vranine noge” (crow’s foot): na kraju linije oznaka pokazuje koliko redova može biti povezano. Mermaid ga crta kao erDiagram:

Oznaka u Mermaidu Značenje
\|\| tačno jedan
o\| nula ili jedan
\|{ jedan ili više
o{ nula ili više

ER dijagram DeliveryApp:

erDiagram
    RESTORAN ||--|{ JELO : nudi
    KUPAC ||--|{ ADRESA : ima
    KUPAC ||--o{ NARUDZBA : pravi
    RESTORAN ||--o{ NARUDZBA : prima
    ADRESA ||--o{ NARUDZBA : "dostava na"
    DOSTAVLJAC |o--o{ NARUDZBA : nosi
    NARUDZBA ||--|{ STAVKA : sadrzi
    JELO ||--o{ STAVKA : "naručeno u"
    NARUDZBA ||--o| PLACANJE : "plaćena"

Linija KUPAC ||--o{ NARUDZBA : pravi čita se isto kao asocijacija u poglavlju 6: „jedan kupac pravi nula ili više narudžbi; svaka narudžba ima tačno jednog kupca”. Linija DOSTAVLJAC |o--o{ NARUDZBA kaže da narudžba ima nula ili jednog dostavljača.

Slika 8.1 – ER dijagram baze DeliveryApp

Pravila: od klasnog dijagrama do tabela

Domenski model iz poglavlja 6 pretvara se u tabele po nekoliko pravila:

  1. Klasa → tabela. Klasa Narudzba postaje tabela narudzba.
  2. Atribut → kolona. udaljenost_km: float postaje kolona udaljenost_km REAL.
  3. Identitet → primarni ključ. Ako klasa ima prirodnu oznaku (N-1001, R-1), ona je ključ; ako nema, dodaje se brojač (id INTEGER PRIMARY KEY).
  4. Veza 1 : N → strani ključ na strani „N”. Jedan restoran ima mnogo narudžbi, pa tabela narudzba dobija kolonu restoran. Nikad obrnuto: restoran ne može imati kolonu za „sve svoje narudžbe”.
  5. Veza M : N → vezna tabela. Narudžba ima mnogo jela, a jelo je u mnogo narudžbi. Zato postoji tabela stavka sa dva strana ključa (narudzba, jelo) i svojim kolonama (kolicina, cijena_u_trenutku). Kod nas je to već bila klasa Stavka.
  6. Kompozicija → brisanje zajedno. Stavka ne postoji bez narudžbe, pa strani ključ dobija ON DELETE CASCADE: kad se obriše narudžba, brišu se i njene stavke.
  7. Operacija → nije kolona. ukupno() se ne čuva kao kolona, nego se računa upitom (8.8).

Za nasljeđivanje (Korisnik → Kupac, Dostavljač) postoje tri uobičajena rješenja: jedna tabela za sve s kolonom „vrsta”; posebna tabela za svaku podklasu; ili zajednička tabela plus tabela za dodatne kolone svake podklase. Za DeliveryApp biramo najjednostavnije: posebne tabele kupac i dostavljac.

Ukratko.

  • ER dijagram crta tabele i veze; oznake „vranine noge” kažu nula, jedan ili više.
  • Klasa postaje tabela, atribut kolona, identitet primarni ključ.
  • Kod veze 1 : N strani ključ ide na stranu N; veza M : N dobija veznu tabelu, kao stavka.
  • Kompozicija postaje ON DELETE CASCADE, a operacije se ne čuvaju nego računaju upitom.

Provjeri se.

  1. Zašto oznaka R-1 može biti primarni ključ restorana? – Jer je jedinstvena i ne smije biti prazna; nijedna dva restorana nemaju istu oznaku.
  2. U koju tabelu ide strani ključ za vezu „restoran ima mnogo narudžbi”? – U tabelu narudzba, na stranu „mnogo”.
  3. Zašto postoji tabela stavka? – Jer je veza narudžbe i jela M : N, a vezna tabela čuva i količinu i cijenu.

Dio 2 – Normalizacija

8.4 Jedna velika tabela i njene anomalije

Zamislimo da je neko, bez projektovanja, sve narudžbe zapisao u jednu veliku tabelu:

broj kupac telefon kupca restoran naziv restorana jela dostava
N-1001 K-01 061 111 R-1 Picerija 2 × Margarita 9,50; 1 × Sok 2,50 3,00
N-1003 K-01 061 111 R-3 Salatarnica 1 × Voćna salata 4,50 –
N-1007 K-01 061 111 R-1 Picerija 1 × Margarita 9,50 2,50

Izgleda pregledno, ali krije tri vrste grešaka, koje se zovu anomalije:

  1. Anomalija izmjene. Kupac K-01 promijeni broj telefona. Treba ga promijeniti u svim redovima. Ako se zaboravi jedan, baza ima dva različita telefona za istog kupca i niko ne zna koji je tačan.
  2. Anomalija unosa. Otvara se četvrti restoran, R-4. Ne može se upisati dok neko iz njega ne naruči, jer tabela ima mjesta samo za narudžbe.
  3. Anomalija brisanja. Obrišemo N-1003 jer je bila odbijena. Ako je to bila jedina narudžba iz Salatarnice, s njom je nestao i jedini zapis da Salatarnica uopšte postoji.

Uz to, kolona „jela” čuva više vrijednosti u jednoj ćeliji („2 × Margarita 9,50; 1 × Sok 2,50”). Takav tekst baza ne može sabrati; da bi se izračunao promet Margarita, program bi morao rastavljati tekst, s greškama.

Uzrok svih anomalija je isti: jedna činjenica je zapisana na više mjesta, ili su različite činjenice zapisane zajedno. Lijek je normalizacija.

Ukratko.

  • Jedna velika tabela izgleda pregledno, ali krije anomalije izmjene, unosa i brisanja.
  • Anomalija izmjene: ista činjenica na više mjesta se promijeni samo na jednom.
  • Anomalija unosa i brisanja: ne može se upisati ili se gubi podatak koji nema veze s narudžbom.
  • Uzrok je ista činjenica na više mjesta ili različite činjenice zajedno; lijek je normalizacija.

8.5 Normalne forme

Normalizacija je postupak raspoređivanja podataka u tabele tako da se svaka činjenica čuva na jednom mjestu. Radi se u koracima koji se zovu normalne forme. Za skoro sve praktične potrebe dovoljne su prve tri.

Prva normalna forma (1NF): jedna ćelija, jedna vrijednost. Nema lista u ćeliji i nema ponavljajućih kolona (jelo1, jelo2, jelo3). Kolonu „jela” rastavljamo tako da svaka stavka bude svoj red:

broj kupac telefon restoran naziv restorana jelo količina cijena
N-1001 K-01 061 111 R-1 Picerija Margarita 2 950
N-1001 K-01 061 111 R-1 Picerija Sok 1 250

Ključ ove tabele je sada par (broj, jelo), jer tek oba zajedno određuju red.

Druga normalna forma (2NF): sve zavisi od cijelog ključa. U tabeli iznad, kupac, telefon i restoran zavise samo od broj (pola ključa), a ne od para (broj, jelo). Zato ih izdvajamo u tabelu narudzba s ključem broj. U tabeli stavka ostaju samo kolone koje zavise od cijelog para: kolicina i cijena_u_trenutku.

Treća normalna forma (3NF): ništa ne zavisi od nečega što nije ključ. U tabeli narudzba, telefon zavisi od kupac, a ne od broja narudžbe; naziv restorana zavisi od restoran. To su tranzitivne zavisnosti (broj → kupac → telefon). Izdvajamo ih u tabele kupac i restoran, a u narudžbi ostaju samo strani ključevi.

Rezultat su upravo tabele iz 8.3: kupac, restoran, jelo, narudzba, stavka. Sada se telefon kupca mijenja na jednom mjestu, novi restoran se upisuje bez narudžbe, a brisanje narudžbe ne briše restoran.

Postoji poznata rečenica koja pamti sve tri forme: svaka kolona koja nije ključ mora zavisiti „od ključa, od cijelog ključa, i ni od čega osim ključa”.

Slika 8.2 – Normalizacija: od jedne velike tabele do pet malih

Ukratko.

  • Normalizacija raspoređuje podatke tako da se svaka činjenica čuva na jednom mjestu.
  • 1NF: jedna ćelija, jedna vrijednost; stavke postaju posebni redovi.
  • 2NF: kolone zavise od cijelog ključa; podaci narudžbe odvajaju se od podataka stavke.
  • 3NF: kolone ne zavise od drugih kolona koje nisu ključ; kupac i restoran dobijaju svoje tabele.
  • Pravilo za pamćenje: od ključa, od cijelog ključa i ni od čega osim ključa.

8.6 Kada se odstupa od normalizacije

Normalizovana baza je ispravna, ali ponekad nije najbrža za čitanje: izvještaj „promet po restoranu za godinu” mora spojiti hiljade narudžbi i stavki. Zato se ponekad svjesno denormalizuje: dodaje se izvedena kolona ili posebna tabela za izvještaje, koja se puni noću. To je odluka arhitekte, zapisana i obrazložena (poglavlje 9), a ne slučajnost.

Važno je razlikovati denormalizaciju od istorijskog podatka. Kolona cijena_u_trenutku u tabeli stavka izgleda kao ponavljanje cijene iz tabele jelo, ali nije. To su dvije različite činjenice: koliko jelo košta danas i koliko je koštalo kad je kupac platio. Isto važi za kolone dostava i popust u tabeli narudžbe: one pamte iznos obračunat u trenutku plaćanja, po pravilima koja su tada važila. Kad udruženje sutra promijeni PP-2, stare narudžbe ne smiju promijeniti iznos. Ovo smo otkrili u poglavlju 6, vježba 5.

Česta zabluda. „Što više normalizacije, to bolje.” Normalne forme iznad treće postoje, ali u praksi se rijetko koriste. A pretjerivanje, npr. posebna tabela za svaku riječ adrese, otežava rad bez stvarne koristi. Cilj nije što više tabela, nego da svaka činjenica ima jedno mjesto.

Ukratko.

  • Denormalizacija je svjesno dodavanje izvedenih podataka radi brzine izvještaja, i zapisuje se kao arhitektonska odluka.
  • Cijena u trenutku, dostava i popust u narudžbi nisu ponavljanje nego istorijske činjenice.
  • Cilj normalizacije nije što više tabela, nego jedno mjesto za svaku činjenicu.

Provjeri se.

  1. Šta je anomalija izmjene? – Kad se ista činjenica zapisana na više mjesta promijeni samo na nekima, pa baza ima dvije različite vrijednosti.
  2. Zašto kolona „jela” s tekstom „2 × Margarita; 1 × Sok” krši 1NF? – Jer u jednoj ćeliji ima više vrijednosti.
  3. Zašto telefon kupca ne smije biti u tabeli narudžbe po 3NF? – Jer zavisi od kupca, a ne od broja narudžbe (tranzitivna zavisnost).

Dio 3 – SQL, indeksi i transakcije

8.7 SQL: pravimo tabele

SQL (Structured Query Language, čita se „es-kju-el” ili „sikvel”) je jezik kojim se razgovara s relacionom bazom. Ima dva glavna dijela: naredbe koje prave strukturu (CREATE TABLE, engleski data definition) i naredbe koje rade s podacima (INSERT, SELECT, UPDATE, DELETE).

Evo kako izgleda tabela narudžbe iz primjeri/deliveryapp/schema.sql:

CREATE TABLE narudzba (
    broj           TEXT PRIMARY KEY,
    kupac          TEXT NOT NULL REFERENCES kupac(oznaka),
    restoran       TEXT NOT NULL REFERENCES restoran(oznaka),
    adresa         INTEGER NOT NULL REFERENCES adresa(id),
    dostavljac     TEXT REFERENCES dostavljac(oznaka),
    udaljenost_km  REAL NOT NULL CHECK (udaljenost_km BETWEEN 0 AND 10),
    kod            TEXT,
    popust         INTEGER NOT NULL DEFAULT 0,
    dostava        INTEGER NOT NULL,
    stanje         TEXT NOT NULL DEFAULT 'NOVA' CHECK (stanje IN
                   ('NOVA','PRIHVAĆENA','ODBIJENA','U_PRIPREMI','NA_PUTU','ISPORUČENA','OTKAZANA')),
    vrijeme        TEXT NOT NULL
);

CREATE TABLE stavka (
    narudzba           TEXT NOT NULL REFERENCES narudzba(broj) ON DELETE CASCADE,
    jelo               INTEGER NOT NULL REFERENCES jelo(id),
    kolicina           INTEGER NOT NULL CHECK (kolicina > 0),
    cijena_u_trenutku  INTEGER NOT NULL,
    PRIMARY KEY (narudzba, jelo)
);

Svaka riječ ovdje je jedno pravilo iz projektovanja, pretočeno u ograničenje koje baza sama čuva:

Ograničenje Šta čuva Odakle dolazi
PRIMARY KEY jedinstven broj narudžbe identitet objekta (pogl. 6)
NOT NULL obavezna polja višestrukost 1 (pogl. 6)
REFERENCES strani ključ, referencijalni integritet asocijacija (pogl. 6)
dostavljac bez NOT NULL narudžba može biti bez dostavljača višestrukost 0..1
CHECK (udaljenost_km BETWEEN 0 AND 10) zona dostave pravilo PP-3
CHECK (stanje IN (…)) samo sedam stanja dijagram stanja (pogl. 7)
CHECK (kolicina > 0) nema stavke s nula komada zdrav razum i višestrukost
ON DELETE CASCADE stavke nestaju s narudžbom kompozicija (pogl. 6)

Ovo je odbrana u dubinu: pravilo provjerava i aplikacija i baza. Ako programer napravi grešku u kodu, baza je posljednja straža. Kad se u SQLiteu pokuša upisati stavka s količinom 0, baza odgovori: CHECK constraint failed: kolicina > 0. Napomena za SQLite: provjera stranih ključeva se mora uključiti naredbom PRAGMA foreign_keys = ON;, inače je isključena.

Primijetite i šta baza ne provjerava: da li je prelaz iz NOVA u NA_PUTU dozvoljen. CHECK vidi samo jedan red u jednom trenutku, ne istoriju. Pravilo prelaza čuva operacija promijeni() u kodu (poglavlje 7).

Ukratko.

  • SQL ima naredbe za strukturu (CREATE TABLE) i za podatke (INSERT, SELECT, UPDATE, DELETE).
  • Ograničenja PRIMARY KEY, NOT NULL, REFERENCES, CHECK i ON DELETE CASCADE prevode projektne odluke u pravila koja baza sama čuva.
  • Pravilo se provjerava i u aplikaciji i u bazi (odbrana u dubinu).
  • CHECK vidi jedan red, pa prelaze stanja i dalje čuva kod.

8.8 Upiti: od tabela do odgovora

Podaci se upisuju naredbom INSERT:

INSERT INTO stavka VALUES ('N-1001', 1, 2, 950);   -- 2 × Pizza Margarita po 9,50 €
INSERT INTO stavka VALUES ('N-1001', 3, 1, 250);   -- 1 × Sok po 2,50 €

A traže naredbom SELECT, koja ima ustaljen oblik: šta želim (SELECT), odakle (FROM, JOIN), koji redovi (WHERE), kako grupisati (GROUP BY) i kojim redom (ORDER BY).

Upit 1 – stavke narudžbe N-1001, s nazivom jela. Naziv jela je u tabeli jelo, a količina u tabeli stavka; spajamo ih po ključu (JOIN):

SELECT j.naziv, s.kolicina, s.cijena_u_trenutku,
       s.kolicina * s.cijena_u_trenutku AS iznos
FROM stavka s
JOIN jelo j ON j.id = s.jelo
WHERE s.narudzba = 'N-1001';
naziv kolicina cijena_u_trenutku iznos
Pizza Margarita 2 950 1900
Sok 1 250 250

Upit 2 – ukupno po narudžbi. Funkcija SUM sabira, a GROUP BY kaže da se sabira posebno za svaku narudžbu:

SELECT n.broj,
       SUM(s.kolicina * s.cijena_u_trenutku)                        AS artikli,
       n.popust,
       n.dostava,
       SUM(s.kolicina * s.cijena_u_trenutku) - n.popust + n.dostava AS ukupno
FROM narudzba n
JOIN stavka s ON s.narudzba = n.broj
GROUP BY n.broj
ORDER BY n.broj;
broj artikli popust dostava ukupno
N-1001 2150 0 300 2450
N-1002 3200 0 0 3200
N-1005 2400 240 400 2560

N-1001 daje 2450 centi = 24,50 €, isto kao račun rukom (poglavlje 1), Python klase (poglavlje 6) i kriteriji prihvatanja (poglavlje 3). N-1005 je narudžba iz vježbe 4 poglavlja 6 (25,60 €).

Upit 3 – narudžbe bez dostavljača. Prazna vrijednost u SQL-u je NULL i provjerava se sa IS NULL, a ne sa = NULL:

SELECT broj, stanje FROM narudzba WHERE dostavljac IS NULL;

Rezultat: N-1005 | PRIHVAĆENA. To je narudžba kojoj aplikacija treba naći dostavljača.

Upit 4 – promet po restoranu (izvještaj za vlasnike, poslovni cilj iz poglavlja 3):

SELECT r.naziv, COUNT(DISTINCT n.broj) AS narudzbi,
       SUM(s.kolicina * s.cijena_u_trenutku) AS promet_artikala
FROM restoran r
LEFT JOIN narudzba n ON n.restoran = r.oznaka
LEFT JOIN stavka s ON s.narudzba = n.broj
GROUP BY r.oznaka;

LEFT JOIN znači „uzmi sve restorane, i one bez narudžbi”; obični JOIN bi izostavio restoran bez ijedne narudžbe.

Ukratko.

  • INSERT upisuje red, a SELECT traži: šta, odakle (JOIN), koji redovi (WHERE), grupisanje (GROUP BY) i redoslijed.
  • JOIN spaja tabele po istoj vrijednosti ključa, a LEFT JOIN zadržava i redove bez para.
  • SUM s GROUP BY za N-1001 daje 2150 centi artikala i ukupno 2450 centi, odnosno 24,50 €.
  • Prazna vrijednost je NULL i provjerava se sa IS NULL.

8.9 Indeksi i transakcije

Indeks

Kad tabela ima milion narudžbi, upit „sve narudžbe restorana R-1” bez pomoći mora pročitati svaki red. Indeks je pomoćna struktura koja to ubrzava, kao kazalo pojmova na kraju knjige: umjesto da listate svih 500 stranica tražeći riječ „normalizacija”, pogledate kazalo i odmah idete na stranicu 214.

CREATE INDEX ix_narudzba_restoran ON narudzba(restoran);

SQLite tada pokazuje da upit koristi indeks (SEARCH narudzba USING INDEX ix_narudzba_restoran). Primarni ključ uvijek ima indeks sam po sebi.

Indeks nije besplatan: zauzima prostor i usporava upis, jer se pri svakom novom redu mora dopuniti i kazalo. Zato se indeksi prave za kolone po kojima se često traži (restoran, kupac, stanje, vrijeme), a ne za svaku kolonu.

Transakcija

Kad kupac plati N-1006, baza mora upisati tri stvari: narudžbu, njene stavke i plaćanje. Šta ako server padne poslije drugog upisa? Imali bismo narudžbu sa stavkama, a bez plaćanja, ili plaćanje bez narudžbe: novac uzet, a restoran ne zna ništa.

Transakcija je niz izmjena koje se izvrše sve zajedno ili nijedna. Ako bilo koji korak ne uspije, baza vraća (rollback) sve izmjene te transakcije, kao da se ništa nije desilo. Ako svi uspiju, baza ih potvrđuje (commit) odjednom.

BEGIN;
INSERT INTO narudzba (...) VALUES ('N-1006', ...);
INSERT INTO stavka VALUES ('N-1006', 1, 2, 950);
INSERT INTO placanje (narudzba, iznos, odobreno) VALUES ('N-1006', 2450, 1);
COMMIT;      -- ili ROLLBACK; ako nešto nije uspjelo

Na praktičnoj nastavi namjerno ćemo pokvariti treći korak (plaćanje upisano za pogrešnu narudžbu) i vidjeti da poslije greške u bazi nema ni narudžbe N-1006: transakcija je vraćena cijela.

Slika 8.3 – Transakcija: ako treći korak ne uspije, vraćaju se i prva dva

Dobra transakcija ima četiri osobine, poznate po skraćenici ACID:

Slovo Osobina Značenje za DeliveryApp
A atomarnost (atomicity) narudžba, stavke i plaćanje upisuju se sve ili ništa
C konzistentnost (consistency) poslije transakcije sva ograničenja (ključevi, CHECK) i dalje važe
I izolacija (isolation) dva kupca koja istovremeno naručuju ne vide napola upisane narudžbe jedan drugog
D trajnost (durability) kad baza kaže „potvrđeno”, narudžba ostaje i ako odmah zatim nestane struje

Slika iz života: prenos novca između dva računa. Skidanje 100 € s jednog i dodavanje na drugi račun moraju biti jedna transakcija. Ako se desi samo prvo, novac je nestao; ako samo drugo, novac je nastao ni iz čega.

Iz stvarnog svijeta. Kraj dvadesetog vijeka obilježio je problem 2000. godine (Y2K). Šezdesetih i sedamdesetih godina memorija je bila skupa, pa su mnogi sistemi godinu čuvali sa dvije cifre: „99” za 1999. Kad bi došla 2000, „00” bi se moglo shvatiti kao 1900, i računi kamata, rokova i starosti bili bi pogrešni. Banke, aerodromi i države potrošile su godine i ogroman novac na pregled i ispravku starih programa i baza prije 1. januara 2000. Pouka za projektanta baze: izbor tipa i veličine kolone je odluka koja živi decenijama. Datum se čuva kao datum, novac kao novac, a ne kao „ono što trenutno stane”.

Ukratko.

  • Indeks ubrzava pretragu kao kazalo u knjizi, ali zauzima prostor i usporava upis.
  • Indeks se pravi za kolone po kojima se često traži; primarni ključ ga ima sam po sebi.
  • Transakcija izvršava niz izmjena sve ili ništa: COMMIT potvrđuje, ROLLBACK vraća.
  • ACID su atomarnost, konzistentnost, izolacija i trajnost.
  • Problem 2000. godine pokazuje da izbor tipa kolone živi decenijama.

Provjeri se.

  1. Koliko je SUM(kolicina × cijena_u_trenutku) za N-1001? – 2150 centi.
  2. Zašto ne pravimo indeks za svaku kolonu? – Jer svaki indeks zauzima prostor i usporava upis.
  3. Šta znači atomarnost? – Da se sve izmjene transakcije izvrše zajedno ili nijedna.

Praktična nastava – baza DeliveryApp u SQLiteu

Cilj je napraviti bazu DeliveryApp iz SQL skripti, postaviti upite i isprobati ograničenja i transakciju. Radi se u folderu kod/ repozitorija deliveryapp-projekat.

Korak 1 – Alati

  1. SQLite dolazi uz Python (modul sqlite3), pa ništa ne treba instalirati za rad iz koda.
  2. Za grafički pregled instalirajte DB Browser for SQLite (sqlitebrowser.org) ili ekstenziju SQLite Viewer u VS Codeu.
  3. U kod/ kopirajte schema.sql, podaci.sql i baza.py iz primjeri/deliveryapp/.

Korak 2 – Pravimo bazu

  1. Pokrenite:
python baza.py
  1. Očekivani ispis:
Ukupno po narudžbi (u centima):
   ('N-1001', 2150, 0, 300, 2450)
   ('N-1002', 3200, 0, 0, 3200)
   ('N-1005', 2400, 240, 400, 2560)
Promet po restoranu (artikli, u centima):
   ('Picerija', 1, 2150)
   ('Roštilj', 1, 3200)
   ('Salatarnica', 1, 2400)
Pokušaj nedozvoljenog unosa:
   baza je odbila: CHECK constraint failed: kolicina > 0
  1. Otvorite deliveryapp.db u DB Browseru i pogledajte svaku tabelu. Pronađite strane ključeve u kartici Database Structure.

Korak 3 – ER dijagram

U modeli/baza.md nacrtajte ER dijagram iz 8.3 u Mermaidu (erDiagram) i uporedite ga s domenskim modelom iz poglavlja 6: za svaku vezu provjerite da se višestrukost slaže.

Korak 4 – Upiti

U DB Browseru (kartica Execute SQL) ili u Python skripti napišite i pokrenite:

  1. sve narudžbe kupca K-01, s nazivom restorana;
  2. jelo koje je naručeno u najviše komada (SUM(kolicina), GROUP BY, ORDER BY … DESC, LIMIT 1);
  3. narudžbe u stanju NA_PUTU s oznakom dostavljača;
  4. prosječan iznos artikala po narudžbi (u eurima, / 100.0);
  5. restorane koji nemaju nijednu narudžbu (dodajte prije toga restoran R-4 bez narudžbi).

Korak 5 – Ograničenja i transakcija

  1. Pokušajte upisati: narudžbu na 11 km; stavku za nepostojeću narudžbu N-9999; stanje „NESTALA”. Zapišite poruku baze za svaki pokušaj.
  2. Napravite transakciju za N-1006 (K-01, R-1, 2 × Margarita, 4,2 km) koja u trećem koraku namjerno upiše plaćanje za N-1001 (koje već postoji, a kolona narudzba u tabeli placanje je UNIQUE). U Pythonu:
try:
    with db:                      # 'with db' = jedna transakcija
        db.execute("INSERT INTO narudzba (broj, kupac, restoran, adresa, udaljenost_km, dostava, vrijeme) "
                   "VALUES ('N-1006', 'K-01', 'R-1', 1, 4.2, 300, '2026-10-02 13:00')")
        db.execute("INSERT INTO stavka VALUES ('N-1006', 1, 2, 950)")
        db.execute("INSERT INTO placanje (narudzba, iznos, odobreno) VALUES ('N-1001', 2200, 1)")
except sqlite3.IntegrityError as e:
    print("transakcija vraćena:", e)
print(db.execute("SELECT COUNT(*) FROM narudzba WHERE broj = 'N-1006'").fetchone())
  1. Očekivano: transakcija vraćena: UNIQUE constraint failed: placanje.narudzba i zatim (0,). Narudžbe N-1006 nema, jer je cijela transakcija vraćena. Ispravite treći korak (plaćanje za N-1006, 2200 centi) i pokrenite ponovo: sada je rezultat (1,).

Korak 6 – AI kao pomoćnik za SQL (opcionalno)

Dajte AI asistentu sadržaj schema.sql (bez podataka o ljudima; ovdje su ionako izmišljeni) i zatražite upit „promet po danu u sedmici za svaki restoran”. Pokrenite predloženi upit i provjerite rezultat ručno na tri narudžbe. Ako upit koristi kolonu koje nema u šemi, to je znak da AI nagađa.

Rezultat koji se predaje: kod/schema.sql, kod/podaci.sql (dopunjeni s R-4), modeli/baza.md s ER dijagramom, fajl kod/upiti.sql s pet upita iz koraka 4 i zapisanim rezultatima, te zapis rezultata koraka 5.

Vježbe

Vježba za studenta 1 – ključevi.

Za tabele dostavljac, adresa i placanje iz schema.sql napišite primarni ključ i strane ključeve, i za svaki strani ključ rečenicu veze.

Rješenje. dostavljac: primarni ključ oznaka; nema stranih ključeva. adresa: primarni ključ id; strani ključ kupac → kupac(oznaka): „svaka adresa pripada tačno jednom kupcu, a kupac ima jednu ili više adresa”. placanje: primarni ključ id; strani ključ narudzba → narudzba(broj), i to UNIQUE: „svako plaćanje pripada tačno jednoj narudžbi, a narudžba ima najviše jedno plaćanje”.

Vježba za studenta 2 – anomalije.

U tabeli jelo neko je dodao kolonu naziv_restorana. Koju normalnu formu to krši i koje anomalije donosi?

Rješenje. Krši 3NF: naziv_restorana zavisi od kolone restoran, a ne od ključa id (tranzitivna zavisnost id → restoran → naziv). Anomalija izmjene: ako Picerija promijeni naziv, treba ga promijeniti u svim njenim jelima, a zaboravljeni red daje dva naziva. Uz to se isti naziv bespotrebno ponavlja.

Vježba za studenta 3 – normalizacija.

Normalizujte do 3NF tabelu: (broj_narudzbe, datum, kupac, adresa_kupca, dostavljac, vozilo_dostavljaca). Ključ je broj_narudzbe.

Rješenje. Tranzitivne zavisnosti: kupac → adresa_kupca i dostavljac → vozilo_dostavljaca. Rezultat: narudzba(broj, datum, kupac, dostavljac), kupac(oznaka, adresa), dostavljac(oznaka, vozilo). (Ako kupac ima više adresa, adresa ide u posebnu tabelu kao u DeliveryApp.)

Vježba za studenta 4 – upit.

Napišite upit koji vraća ukupan iznos koji je kupac K-01 platio, i izračunajte rezultat za podatke iz podaci.sql.

Rješenje.

SELECT SUM(p.iznos) FROM placanje p
JOIN narudzba n ON n.broj = p.narudzba
WHERE n.kupac = 'K-01' AND p.odobreno = 1;

K-01 ima samo N-1001, pa je rezultat 2450 centi, odnosno 24,50 €.

Vježba za studenta 5 – ACID.

Za svaku situaciju recite koja osobina ACID je narušena: a) poslije nestanka struje, narudžba koju je baza potvrdila nije u bazi; b) tokom upisa jedan kupac vidi stavke tuđe narudžbe koja još nije završena; c) upisano je plaćanje, ali ne i narudžba; d) poslije transakcije postoji stavka s količinom −1.

Rješenje. a) trajnost (D); b) izolacija (I); c) atomarnost (A); d) konzistentnost (C).

Sažetak u pet rečenica

  1. Relacioni model, koji je Codd predložio 1970, čuva podatke u tabelama povezanim istim vrijednostima ključeva, a baza za razliku od Excela podržava istovremeni rad, ograničenja, brzu pretragu i transakcije.
  2. Primarni ključ jedinstveno određuje red, strani ključ pravi vezu, a klasni dijagram se pretvara u tabele tako da veza 1 : N dobija strani ključ na strani N, veza M : N veznu tabelu, a kompozicija ON DELETE CASCADE.
  3. Normalizacija do treće normalne forme sprečava anomalije izmjene, unosa i brisanja, jer svaka kolona zavisi od ključa, od cijelog ključa i ni od čega osim ključa.
  4. SQL ograničenja (PRIMARY KEY, NOT NULL, REFERENCES, CHECK) prevode projektna pravila u pravila baze, a upit sa SUM i GROUP BY za N-1001 daje 2150 centi artikala i ukupno 24,50 €.
  5. Indeks ubrzava pretragu kao kazalo u knjizi, a transakcija s osobinama ACID čuva da se narudžba, stavke i plaćanje upišu sve zajedno ili nijedno.

Pitanja za ponavljanje

  1. Šta je Codd predložio 1970. i zašto je to bila velika promjena?
  2. Navedite četiri razloga zašto se podaci DeliveryApp čuvaju u bazi, a ne u Excelu.
  3. Šta su primarni i strani ključ? Dajte primjer iz DeliveryApp.
  4. Šta je referencijalni integritet?
  5. Zašto se novac čuva u centima?
  6. Navedite pravila pretvaranja klasnog dijagrama u tabele za veze 1 : N i M : N i za kompoziciju.
  7. Šta su anomalije izmjene, unosa i brisanja? Dajte primjer za svaku.
  8. Objasnite 1NF, 2NF i 3NF na primjeru narudžbe.
  9. Zašto cijena_u_trenutku nije ponavljanje podatka?
  10. Koja SQL ograničenja znate i koje projektno pravilo čuva svako?
  11. Napišite upit koji računa ukupno za N-1001 i navedite rezultat.
  12. Šta je razlika između JOIN i LEFT JOIN?
  13. Šta je indeks i kada ga ne treba praviti?
  14. Šta je transakcija i šta znači ACID?

Odgovori na pitanja za ponavljanje.

  1. Codd je predložio da se podaci čuvaju u običnim tabelama, a da veza između tabela bude ista vrijednost u dvije kolone, a ne skriveni pokazivač. Program pita samo šta želi, a baza sama nađe put; zato se struktura može mijenjati bez prepisivanja svih programa.
  2. Istovremeni rad mnogo korisnika bez gaženja, ograničenja koja odbijaju pogrešan unos, veze bez prepisivanja podataka, brza pretraga indeksom, transakcije (sve ili ništa) i prava pristupa po ulogama.
  3. Primarni ključ jedinstveno određuje red (oznaka R-1 u tabeli restoran, broj N-1001 u tabeli narudzba). Strani ključ čuva primarni ključ iz druge tabele (kolona restoran u tabeli jelo sadrži R-1).
  4. Pravilo da strani ključ ne smije pokazivati na red koji ne postoji; baza odbija upis jela za nepostojeći restoran i brisanje restorana koji ima jela (ili briše i jela, ako je tako definisano).
  5. Jer se sabiranjem mnogo decimalnih brojeva s pomičnim zarezom skupljaju greške zaokruživanja; cijeli brojevi centi se sabiraju tačno.
  6. Kod veze 1 : N strani ključ ide u tabelu na strani N (narudzba.restoran). Veza M : N dobija veznu tabelu s dva strana ključa (stavka s narudzba i jelo). Kompozicija dobija ON DELETE CASCADE, tako da se dijelovi brišu s cjelinom.
  7. Anomalija izmjene: telefon kupca promijenjen u jednom redu, a ne u ostalim. Anomalija unosa: novi restoran se ne može upisati dok nema narudžbu. Anomalija brisanja: brisanjem jedine narudžbe nestaje i zapis o restoranu.
  8. 1NF: jedna ćelija, jedna vrijednost, pa svaka stavka narudžbe postaje svoj red. 2NF: podaci koji zavise samo od broja narudžbe (kupac, restoran) odvajaju se od podataka stavke, koji zavise od para (narudžba, jelo). 3NF: podaci koji zavise od kupca ili restorana (telefon, naziv) izdvajaju se u tabele kupac i restoran.
  9. Jer je to druga činjenica: cijena jela u trenutku plaćanja, a ne današnja cijena. Ako restoran kasnije promijeni cijenu, iznos plaćene narudžbe ne smije se promijeniti.
  10. PRIMARY KEY (jedinstven identitet), NOT NULL (obavezno polje, višestrukost 1), REFERENCES (veza i referencijalni integritet), CHECK (npr. udaljenost od 0 do 10 km po PP-3, sedam dozvoljenih stanja, količina veća od nule), ON DELETE CASCADE (kompozicija).
  11. SELECT n.broj, SUM(s.kolicina * s.cijena_u_trenutku) − n.popust + n.dostava FROM narudzba n JOIN stavka s ON s.narudzba = n.broj WHERE n.broj = ‘N-1001’ GROUP BY n.broj; rezultat je 2150 − 0 + 300 = 2450 centi, odnosno 24,50 €.
  12. JOIN vraća samo redove koji imaju par u obje tabele; LEFT JOIN vraća sve redove lijeve tabele, i one bez para (npr. restoran bez narudžbi).
  13. Indeks je pomoćna struktura koja ubrzava pretragu, kao kazalo u knjizi. Ne treba ga praviti za kolone po kojima se rijetko traži, jer zauzima prostor i usporava svaki upis.
  14. Transakcija je niz izmjena koje se izvrše sve zajedno ili nijedna (COMMIT ili ROLLBACK). ACID: atomarnost (sve ili ništa), konzistentnost (ograničenja važe i poslije), izolacija (istovremene transakcije ne vide napola gotove izmjene jedna druge), trajnost (potvrđeno ostaje i poslije kvara).