Relacije

[jonagricar]

Uspeli sva sestaviti dve tabeli relacij in sicer "nahaja" in "donira". Zanima naju, kako ustvariti še relaciji "prejme" in "hrani". Kako posamezno vrečko, ki jo je donirala oseba v npr kraju A, ki se nahaja v državi B, povezati z bolnišnico, ki se nahaja v kraju A (če bolnišnice v kraju ni pač izberemo random bolnišnico v državi B), saj tabela kri ne vsebuje podatka od kje je vrečka prišla? In pa kako prejemniku dodeliti neko vrečko, ki ima pravilno krvno skupino in se vrečka in prejemnik oba nahajata v istem kraju C ali podobno kot prej vsaj v isti državi? Ali je rešitev morda tabeli "kri", ki vsebuje vrečke dodati lokacijo iziroma izvor? Hvala za pomoč!

[jaanos]

Kar se tiče relacije donira, imata trenutno (tako na ER diagramu kot tudi v bazi) relacijo več-na-več, kar morda ni najbolj smiselno - ali lahko ena vrečka vsebuje kri več donatorjev? Če ne, naj bo to raje relacija, ki posamezni vrečki dodeli donatorja - na ER diagramu naj bo torej puščica od entitete KRI do relacije donira, v bazi pa bosta v tabeli kri imeli stolpec donator, ki je referenca na donatorja.

Podobno velja tudi za relaciji hrani in prejme, ki sta na ER diagramu ustrezno označeni. Pri slednji bo sicer potrebno dovoliti vrednosti NULL (za še neporabljene vrečke).

Za izvor lahko tudi dodasta ustrezen podatek - če predpostavita, da se doniranje opravlja v bolnišnicah, potem je lahko med entitetama KRI in BOLNIŠNICA še ena relacija za izvor. Ob doniranju bo ta seveda zavzela isto vrednost kot mesto hrambe, seveda pa se lahko slednja vrednost naknadno spremeni.

Pri prejemnikih bo morda smiselno nekoliko spremeniti shemo, saj lahko ista oseba večkrat prejme kri (na različne datume, morda v različnih bolnišnicah). Svetoval bi, da naredita tako:

• Imejta eno entiteto (npr. OSEBA) s podatki tako za donatorje kot za prejemnike (relacija donira gre na to entiteto; datum vpisa v evidenco je lahko NULL za tiste, ki niso med donatorji).
• PREJEMNIK naj bo šibka entiteta, določena z osebo, bolnišnico in datumom vloge (ostali atributi so že pri entiteti OSEBA). V praksi bo bolje, da imata še stolpec id; na prejšnje tri stolpce (skupaj) pa lahko dasta lastnost UNIQUE, da ne bo prihajalo do ponovitev.
• Relacija prejme naj bo med entiteto KRI in šibko entiteto PREJEMNIK. Za isti zahtevek se seveda lahko porabi tudi več vrečk (ali pa nič, če za zahtevek še ni bila prejeta kri).

Podatke potem lahko generirata tako, da so smiselni - npr. za vsakega prejemnika najdeta eno še neporabljeno vrečko, ki se nahaja v isti bolnišnici in ima ustrezno krvno skupino. Lahko tudi na nivoju baze omejita, da lahko prejemnik prejme samo tako vrečko, ki se nahaja v ustrezni bolnišnici - relacijo prejme v tem primeru realizirata tako, da v tabeli kri naredita tuji ključ iz bolnišnice (za relacijo hrani) in ID-ja premenika na ustrezna stolpca v tabeli prejemnik (mislim, da bo potrebno v tem primeru nanju skupaj dati lastnost UNIQUE).

[katjamarina]

Pozdravljeni! Hvala za napotke. Diagram sva popravili, se vam zdi to v redu?

[jaanos]

Imam samo dve pripombi:

• Vsaka oseba je lahko večkrat prejemnik, tako da naj iz entitete OSEBA v relacijo je ne bo puščice. Nasprotno pa oseba določa prejemnika, tako da naj bo debela puščica iz šibke entitete PREJEMNIK v relacijo je, ki naj ima dvojni ali debel rob. Enako velja tudi za relacijo nahaja, datum vloge pa naj bo kot delni ključ črtkano podčrtan.
• Kot sem že omenil, ni jasno, kaj naj bi predstavljal atribut zaloga pri entiteti BOLNIŠICA. Zalogo krvi v bolnišnici je namreč mogoče dobiti iz relacij hrani in prejme (tj., tiste vrečke krvi v bolnišnici, ki še niso porabljene).

[jonagricar]

Pozdravljeni, uredili sva vse relacije razen povezave prejme med prejemnikom in krvno vrečko. Težave nama dela to, da ne znava povezati prejemnika in vrečke, ki bi mu glede na dano bolnišnico in krvno skupino ustrezala. Poskusili sva ustvariti pomožno tabelo prejme, kjer zadnji stolpec vsebuje logični vrednosti True/False, če se krvni skupini vrečke in prejemnika ujemata. Ali predlagate kakšno drugo pot? Hvala za odgovor in lep pozdrav!

[jaanos]

Tukaj gre bolj za problem generiranja podatkov, tako da pomožnih tabel ne potrebujeta - lahko si pa seveda pomagata z obstoječimi podatki v bazi.

Najprej bo treba ustrezno prirediti tabele. Ker pri vrečki potrebujeta relacijo na prejemnika, bo najbolje, če tabeli prejemnik dodasta stolpec id tipa SERIAL (tako se bodo ID-ji samodejno generirali). Ker tabela predstavlja šibko entiteto, bo treba poskrbeti še, da sta stolpca id_prejemnika in id_lokacije_zdravljenja (ta naj bo tipa INTEGER!) referenci na tabeli oseba in bolnisnica, na stolpce id_prejemnika, datum_vloge in id_lokacije_zdravljenja skupaj pa dasta lastnost UNIQUE, da se bo vsaka kombinacija lahko pojavila samo enkrat. Tudi v tabeli kri naj bosta stolpca donator in hrani referenci na tabeli oseba in bolnisnica, treba bo pa dodati še stolpec prejemnik (brez lastnosti NOT NULL!), ki naj bo referenca na stolpec id v tabeli prejemnik.

Ko imata tako popravljene tabele, lahko znova uvozita podatke, potem pa generirata še povezavo med vrečko krvi in prejemnikom. Najlažje bo to tako, da gresta čez vrstice tabele prejemnik (kar z zanko for - zraven pridružita še tabelo oseba, da dobita podatke o krvni skupini) in poiščeta ustrezno še neporabljeno vrečko:

SELECT stevilka_vrecke FROM kri
JOIN oseba ON donator = oseba.id
WHERE hrani = `id_lokacije_zdravljenja`
AND krvna_skupina = `krvna_skupina`
AND prejemnik IS NULL
ORDER BY RANDOM()
LIMIT 1

V navednicah sta podatka, ki ju vstavita v poizvedbo glede na trenutno obravnavanega prejemnika. Če rezultat poizvedbe ni prazen, potem sta našli ustrezno vrečko in jo lahko dodelita trenutnemu prejemniku:

UPDATE kri SET prejemnik = `id`
WHERE stevilka_vrecke = `stevilka_vrecke`

Svetujem, da po Poissonovi porazdelitvi določita, koliko vrečk bo prejel posamezen pacient (poglejta si R-jevo funkcijo rpois, ki generira števila po Poissonovi porazdelitvi). Tako lahko zgornji poizvedbi naredita tolikokrat, kot dobita to število - postopek lahko prekineta, če ustreznih vrečk zmanjka. Morda pa bi bilo smiselno v tabeli prejemnik imeti še število zahtevanih vrečk - tako bosta v aplikaciji tudi vedeli, kateri pacienti še čakajo na kri.

Mimogrede, zgornja poizvedba bo našla samo vrečke za isto krvno skupino. Če želita upoštevati pravila, kdo komu lahko daruje, bo najbolje, če naredita tabelo s temi podatki, pri čemer za vsak par krvnih skupin podasta še prioriteto (prej porabimo tisto kri, ki jo lahko manjkrat uporabimo) ter to upoštevata pri razvrščanju vrečk. Prav tako bi bilo smiselno upoštevati tudi datum zahtevkov in prejemov - po vrsticah iz tabele prejemnik gresta po datumu, nato pa lahko tudi pri iskanju ustreznih vrečk te razvrščata po datumu.

[jonagricar]

Pozdravljeni! Popravili sva tabele kot ste svetovali. Sedaj pa imava težavo pri povezavi vrečke in prejemnika. Poizvedba SELECT, ki ste jo napisali, nama je jasna, a ne razumeva kako to povezati s for zanko, ki teče po prejemniku. Hvala za pomoč!

[jaanos]

Najprej naredita osnovno poizvedbo, skozi katero bosta šli, npr.

prejemnik <- dbGetQuery(conn,
                        build_sql("SELECT oseba.id, krvna_skupina, id_lokacije_zdravljenja
                                   FROM oseba JOIN prejemnik ON oseba.id = id_prejemnika
                                   ORDER BY datum_vloge",
                                  con=conn))

Potem gresta lahko po vrsticah rezultata in v zanki for delata nadaljnje poizvedbe, npr.

for (i in 1:nrow(prejemnik)) {
  vrecka <- dbGetQuery(conn,
                       build_sql("SELECT stevilka_vrecke FROM kri
                                  JOIN oseba ON donator = oseba.id
                                  WHERE hrani = ", prejemnik[i, "id_lokacije_zdravljenja"], "
                                  AND krvna_skupina = ", prejemnik[i, "krvna_skupina"], "
                                  AND prejemnik IS NULL
                                  ORDER BY RANDOM()
                                  LIMIT 1", con=conn))
  ...
}

Še to: vidim, da v tabeli prejemnik nimata glavnega ključa. Na stolpcu id imata sicer nastavljeno UNIQUE in NOT NULL, tako da je učinek isti, vseeno pa svetujem, da nanj nastavita še PRIMARY KEY (UNIQUE potem ne bo več potreben).

[jonagricar]

Uporabili sva for zanko, a nama vrača napako pri SELECT poizvedbi:

Error in postgresqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not Retrieve the result : ERROR: syntax error at or near "AND" LINE 4: AND krvna_skupina = ^ ) Warning message: In postgresqlQuickSQL(conn, statement, ...) : Could not create execute: SELECT stevilka_vrecke FROM kri JOIN oseba ON donator = oseba.id WHERE hrani = AND krvna_skupina = AND prejemnik IS NULL ORDER BY RANDOM() LIMIT 1

[jaanos]

Nisem prepričan, zakaj pride do tega - izgleda, kot da se podatki iz prve poizvedbe niso vstavili v poizvedbo v zanki. Svetujem, da pogledata, kakšna je vsebina razpredelnice prejemnik pred izvajanjem zanke - morda je iz nekega razloga prazna?

Mimogrede, za drugo zanko exists in is.null ne bosta dala ustrezne informacije, saj bo rezultat vedno razpredelnica - namesto tega preverita, ali ima ta vsaj eno vrstico. Poleg tega bo seveda potrebno še vstaviti podatke v poizvedbo:

        if (nrow(vrecka) > 0) {
          dbSendQuery(conn,
                      build_sql("UPDATE kri SET prejemnik = ", prejemnik[i, "id"],"
                                 WHERE stevilka_vrecke = ", vrecka$stevilka_vrecke,
                                con=conn))
        }

[jonagricar]

Hvala, pogoj v zanki sva popravili. Še vedno pa nama ni jasno, zakaj prvi del zanke oziroma poizvedba ne deluje, kljub temu da sva pregledali tabelo. V čem bi lahko bil problem?

[jaanos]

Sem še enkrat preveril, pa vidim, da tabele v bazi sem2019_katjam trenutno nimajo nastavljenih pravic, tako da lahko z njimi dela le @katjamarina. Je pa res, da nazadnje to ni bil problem in sem lahko naredil poizvedbo tudi z uporabnikom javnost.

[jonagricar]

Še vedno nama ni uspelo odpraviti težave, saj nikakor ne moreva ugotoviti kje se zatakne, a se nama zdi, da bi to lahko bilo že pri osnovni poizvedbi. Ali obstaja kakšna druga pot ali pa bi ta koda morala delovati in se nekje skriva hakeljc? Hvala za pomoč!

[jaanos]

Če sta trenutno na faksu, se lahko zglasita v kabinetu 5.14, pa bomo pogledali (ne vem sicer, kako dolgo bom še tukaj).