Projektovanje baze podataka

Šta učimo ovog semestra

Petnaest sedmica, svaka s tri časa teorije i tri časa prakse. Danas: poglavlje 8 (Sedmica 8, označeno). Isti primjer kroz knjigu: DeliveryApp, narudžba N-1001 (24,50 €).

Literatura

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.

Priča za početak

Zašto ovo poglavlje

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 €.

Riječi ovog poglavlja (1/3)

• 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.

Riječi ovog poglavlja (2/3)

• 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.

Riječi ovog poglavlja (3/3)

• 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

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?

8.1 Zašto baza, a ne Excel

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 – 8.1 Zašto baza, a ne Excel

• 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:

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.

8.2 Tabela, primarni i strani ključ

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”.

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 – 8.2 Tabela, primarni i strani ključ

• 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:

8.3 ER dijagram i od klasa do tabela

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"

8.3 ER dijagram i od klasa do tabela

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.

8.3 ER dijagram i od klasa do tabela

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).

8.3 ER dijagram i od klasa do tabela

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 – 8.3 ER dijagram i od klasa do tabela

• 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.

Odgovori: tipka R ili klik na zamućeno polje.

8.4 Jedna velika tabela i njene anomalije

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

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.

8.4 Jedna velika tabela i njene anomalije

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 – 8.4 Jedna velika tabela i njene anomalije

• 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:

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

8.5 Normalne forme

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”.

Ukratko – 8.5 Normalne forme

• 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.

Ukratko – 8.6 Kada se odstupa od normalizacije

• 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).

Odgovori: tipka R ili klik na zamućeno polje.

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)
);

8.7 SQL: pravimo tabele

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

8.7 SQL: pravimo tabele

8.7 SQL: pravimo tabele

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 – 8.7 SQL: pravimo tabele

• 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):

8.8 Upiti: od tabela do odgovora

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';

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

8.8 Upiti: od tabela do odgovora

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;

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 €).

8.8 Upiti: od tabela do odgovora

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 – 8.8 Upiti: od tabela do odgovora

• 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.

8.9 Indeksi i transakcije

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.

8.9 Indeksi i transakcije

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

8.9 Indeksi i transakcije

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.

8.9 Indeksi i transakcije

Ukratko – 8.9 Indeksi i transakcije

• 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.

Odgovori: tipka R ili klik na zamućeno polje.

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](https://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žba za studenta 1 – ključevi.

Vježba za studenta 1 – ključevi. – rješenje

Vježba za studenta 2 – anomalije.

Vježba za studenta 2 – anomalije. – rješenje

Vježba za studenta 3 – normalizacija.

Vježba za studenta 3 – normalizacija. – rješenje

Vježba za studenta 4 – upit.

Vježba za studenta 4 – upit. – 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;

Vježba za studenta 5 – ACID.

Vježba za studenta 5 – ACID. – rješenje

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?