Esercitazioni riassuntive Laboratorio di basi di dati 07

Esercitazioni riassuntive Laboratorio di basi di dati 07-08
1) Sia dato il seguente schema relazionale che modella parte del database per la gestione di una
fumetteria:
FUMETTI(Nome, Disegnatore, Editore, Prezzo)
NUMERI(Fumetto:FUMETTO, Numero, Anno, NumPagine)
AVVENTURE(Fumetto:Fumetto:NUMERI, Numero:Numero:NUMERI, Titolo, Descrizione,
NumPagine)
PERSONAGGI(Nome, Descrizione)
COMPARE_IN(Pers:PERSONAGGIO, Fumetto:Fumetto:AVVENTURE,
Numero:Numero:AVVENTURE, Titolo:Titolo:AVVENTURE, NumVignette)
a) Si definisca la base di dati su ORACLE
b) Si scriva la funzione Correla(VFum1,VFum2) che calcoli il livello di correlazione tra due
fumetti passati in input come somma del numero di volte in cui ogni personaggio di VFum1
compare in una avventura di VFum2.
2) Sia dato il seguente schema relazionale che modella parte del database del SI di un ospedale:
PAZIENTI(CF,Nome,Cognome,Sesso,DataN)
PRESTAZIONI(Cod Nome,Costo)
RICOVERI(Paziente:PAZIENTI,DataInizio,DataFine)
EROGAZIONI(Paziente:Paziente:RICOVERI,DataRicovero:DataInizio:RICOVERI,
Prestazione:PRESTAZIONI)
a) Si definisca la base di dati su ORACLE
b) Si scriva la stored procedure Costi che, dato in input un intervallo temporale, calcoli il
ricovero più costoso come quello che massimizza la seguente funzione di costo
SE Durata < 7 gg --> F = 20 + Sum(Costo) - Durata*2
SE Durata >= 7 gg --> F = 15 + Sum(Costo) - Durata*1.5
In output si forniscano tutti i dati ad esso relativi (dati paziente, dati ricovero, e dati erogazioni).
La Durata si considera espressa in giorni. Attenzione ad escludere dal computo i ricoveri non
ancora conclusi.
3) Sia dato il seguente schema relazionale che il database di un sistema BioInformatico:
PROTEINE(CodP,Nome,Funzione,Classe)
AMINOACIDI(CodAm, Nome, Funzione)
ATOMI(CodAt, Nome, Potenziale)
REGIONI(CodR,CodP:PROTEINE,Posizione, Potenziale)
AT_IN_AM(CodAm:AMINOACIDI,CodAT:ATOMI,Posizione)
AM_IN_R(CodR:REGIONI,CodAm:AMINOACIDI,Posizione)
a) Si definisca la base di dati su ORACLE
b) Si scriva la stored procedure Score(CodP) che, dato in input il codice di una proteina, calcoli
la bontà della scomposizione in regioni come media della bontà delle singole regioni. La bontà di
ogni regione è calcolata come media sul numero degli atomi dei pesi riportati nella seguente
tabella che indica la congruenza tra il potenziale associato alla regione
(Potenziale:REGIONI) e quello degli atomi che la compongono (Potenziale:Atomi).
Si assuma per semplicità che atomi e proteine possano assumere i soli potenziali -1,0,+1.
Regione/atomi
0
+
+1
-1
-2
0
-1
+1
-1
+
-2
-1
+1
Soluzioni
Esercizio 1
CREATE OR REPLACE FUNCTION CORRELA(v_fum1 FUMETTI.Nome%TYPE, v_fum2
FUMETTI.Nome%TYPE) RETURN INTEGER IS
CURSOR crs_comparsa IS
SELECT DISTINCT Pers as Personaggio
FROM COMPARE_IN C
WHERE C.Fumetto=v_fum1;
v_comparsa crs_comparsa%ROWTYPE;
v_numvolte
integer;
v_tot
integer;
BEGIN
v_tot:=0;
open crs_comparsa;
loop
fetch crs_comparsa into v_comparsa;
EXIT WHEN crs_comparsa%NOTFOUND;
SELECT COUNT(*) INTO v_numvolte
FROM COMPARE_IN c
WHERE c.Pers=v_comparsa.Personaggio
AND c.Fumetto=v_fum2;
if v_numvolte is null then
v_numvolte := 0;
end if;
v_tot := v_tot + v_numvolte;
end loop;
close crs_comparsa;
return v_tot;
END;
Esercizio 2
create or replace procedure Costi(v_DataI date, v_DataF date) IS
CURSOR ricoveri_cursor IS
select R_PAZIENTE,R_DATAI,R_DATAF,sum(S_COSTO)
from RICOVERI,EROGAZIONI,PRESTAZIONI
where R_PAZIENTE=E_PAZIENTE and R_DATAI=E_DATARIC and R_DATAI>=v_DataI and
R_DATAF<=v_DataF
and S_COD=E_PRESTAZIONE and R_DATAF IS NOT NULL
group by R_PAZIENTE,R_DATAI,R_DATAF;
v_costo float;
v_durata INT;
v_DurataMax INT;
v_max float :=0;
v_Data date;
v_CF PAZIENTI.P_CF%TYPE;
c_CF PAZIENTI.P_CF%TYPE;
c_DataI RICOVERI.R_DATAI%TYPE;
c_DataF RICOVERI.R_DATAF%TYPE;
c_Costo PRESTAZIONI.S_COSTO%TYPE;
BEGIN
open ricoveri_cursor;
loop
fetch ricoveri_cursor into c_CF,c_DataI,c_DataF,c_Costo;
exit when ricoveri_cursor%NOTFOUND;
v_Durata:=c_DataF-C_DataI;
if (v_Durata<7) then
v_costo:=20 + c_Costo -2*(v_Durata);
else
v_costo:=15 + c_Costo -1.5*(v_Durata);
end if;
if (v_costo>v_max) then
v_max:=v_costo;
v_DurataMax:=v_Durata;
v_CF:=c_CF;
v_Data:=c_DataI;
end if;
end loop;
close ricoveri_cursor;
DBMS_OUTPUT.PUT_LINE('Il ricovero piu’ costoso è durato ' || v_DurataMax || '
giorni, il costo è ' || v_max || ' per il paziente con CF' || c_CF || ' in data
' || v_Data);
END Costi;
Esercizio 3
create or replace procedure Score(v_prot integer) IS
CURSOR c_regioni IS
select CodR,Potenziale
from REGIONI
where Proteina=v_prot;
v_rscore float;
v_score float;
v_pcount integer;
v_rcount integer;
v_count integer;
v_regId integer;
v_regPot integer;
BEGIN
v_pcount:=0;
open c_regioni;
loop
fetch c_regioni into v_regId,v_regPot;
exit when c_regioni%NOTFOUND;
v_pcount:=v_pcount+1;
v_rscore:=0;
v_rcount:=0;
select count(*) into v_count
from AT_IN_R,AT_IN_AM,ATOMI
where Regione=v_regid and AT_IN_R.Aminoacido=AT_IN_AM.Aminoacido
and PotenzialeAt=1;
v_rcount:=v_rcount+v_count;
if (v_regPot>0) then
v_rscore:=v_rscore+v_count;
elsif (v_regPot=0) then
v_rscore:=v_rscore-v_count;
else v_rscore:=v_rscore-2*v_count;
end if;
select count(*) into v_count
from AT_IN_R,AT_IN_AM,ATOMI
where Regione=v_regid and AT_IN_R.Aminoacido=AT_IN_AM.Aminoacido
and PotenzialeAt=0;
v_rcount:=v_rcount+v_count;
if (v_regPot>0) then
v_rscore:=v_rscore-v_count;
elsif (v_regPot=0) then
v_rscore:=v_rscore+v_count;
else v_rscore:=v_rscore-v_count;
end if;
select count(*) into v_count
from AT_IN_R,AT_IN_AM,ATOMI
where Regione=v_regid and AT_IN_R.Aminoacido=AT_IN_AM.Aminoacido
and PotenzialeAt<0;
v_rcount:=v_rcount+v_count;
if (v_regPot>0) then
v_rscore:=v_rscore-2*v_count;
elsif (v_regPot=0) then
v_rscore:=v_rscore-v_count;
else v_rscore:=v_rscore+v_count;
end if;
v_score:=v_score+v_rscore/v_rcount;
end loop;
close c_regioni;
v_score=v_score/v_pcount;
DBMS_OUTPUT.PUT_LINE('Lo score della proteina
END Score;
e''' || v_score);