Poročilo knjižnice se je skrčilo po brisanju članov

SELECT c.id_clana, c.ime, c.priimek,
       COUNT(i.id_izposoje) AS st_izposoj
FROM Clan AS c
LEFT JOIN Izposoja AS i
       ON i.id_clana = c.id_clana
      AND i.datum_vrnitve IS NULL
GROUP BY c.id_clana, c.ime, c.priimek
ORDER BY st_izposoj DESC;

Zakaj ON in ne WHERE

LEFT JOIN ohrani vse vrstice leve tabele (Clan) tudi takrat, ko na desni strani ni ujemanja; manjkajoče stolpce zapolni z NULL. Pogoj v ON se uporabi med združevanjem, zato član brez aktivne izposoje ostane v rezultatu, njegov i.id_izposoje pa je NULL.

Če bi pogoj zapisali v WHERE, bi se uporabil po združevanju in bi vrstice z NULL vrednostmi izločil. Član, ki ima vse knjige že vrnjene, bi iz rezultata izginil, namesto da bi imel vrednost 0 – poročilo bi bilo napačno. LEFT JOIN se s takim pogojem v WHERE dejansko obnaša kot INNER JOIN.

Zakaj COUNT(i.id_izposoje) in ne COUNT(*)

COUNT(*) prešteje vrstice, torej bi pri članu brez izposoj vrnil 1 (vrstica z NULL). COUNT(stolpec) ne šteje NULL vrednosti, zato vrne pravilno 0.

Vzrok

V SQL je NULL oznaka za »neznano«, ne za vrednost. Primerjava z NULL vrne tretjo logično vrednost UNKNOWN, vrstica pa se ne izbere, ker WHERE izbere le vrstice, kjer je pogoj TRUE. Zato = NULL nikoli ne vrne ničesar; pravilno je IS NULL (oziroma IS NOT NULL).

Pravilna poizvedba

SELECT c.ime, c.priimek, k.naslov, i.datum_izposoje
FROM Izposoja AS i
JOIN Clan AS c   ON c.id_clana = i.id_clana
JOIN Knjiga AS k ON k.id_knjige = i.id_knjige
WHERE i.datum_vrnitve IS NULL
  AND i.datum_izposoje < DATE '2024-05-15' - INTERVAL '30' DAY;

Razlaga

• datum_izposoje < 15. 4. 2024 pomeni, da je od izposoje minilo več kot 30 dni; knjiga še ni vrnjena (IS NULL), torej je v zamudi. Enakovredno bi lahko zapisali datum_izposoje + INTERVAL '30' DAY < DATE '2024-05-15'.
• Opozorilo: zapis datuma se med sistemi razlikuje (DATE_SUB, date('now','-30 days')), zato je treba preveriti dialekt. Če se primerja datum z nizom, mora biti niz v obliki, ki jo sistem pravilno pretvori.
• Sorodna past: NOT IN (SELECT id_clana FROM ...), kjer podpoizvedba vrača NULL, vrne prazno množico iz istega razloga – tudi tam je treba NULL obravnavati izrecno.

Kaj se je zgodilo

ON DELETE CASCADE pomeni, da baza ob brisanju vrstice v tabeli Clan samodejno izbriše vse pripadajoče vrstice v Izposoja. Referenčna celovitost je ostala nespremenjena, a zgodovina je izgubljena: 42 izposoj iz leta 2023 je izginilo skupaj s članoma, zato se je poročilo skrčilo s 1.240 na 1.198. Statistika je postala neresnična za nazaj, kar je resen problem, ker se poročila pogosto pripravljajo retrogradno.

Predlog modela

1. Mehko brisanje: v tabelo Clan dodamo stolpca aktiven (privzeto 1) in datum_izbrisa. Član ob »brisanju« ostane v bazi, le označen je; poizvedbe za tekoče poslovanje filtrirajo WHERE aktiven = 1.
2. Tuji ključ ON DELETE RESTRICT namesto CASCADE – baza prepreči brisanje člana, ki ima izposoje, in s tem prepreči tiho izgubo podatkov.
3. Anonimizacija po preteku roka: ko osebni podatki niso več potrebni, se ime, priimek in email nadomestijo z oznako (na primer »Nekdanji član 4711«), id_clana pa ostane, da tuji ključi in agregati še naprej delujejo. Anonimizirani zapis ni več osebni podatek, zato ga lahko hranimo za statistiko.

Utemeljitev: načelo omejitve hrambe zahteva, da osebnih podatkov ne hranimo dlje, kot je potrebno, ne pa, da uničimo celotno poslovno evidenco. Ločitev identitete od dogodka je standardna rešitev. Brisanje naj bo dokumentirano (kdo, kdaj, zakaj), obnovitev pa mogoča iz varnostne kopije.