UNIVERSIT `A DEGLI STUDI DI UDINE Facolt`a di Medicina e Chirurgia
CORSO DI LAUREA IN TECNICHE DI RADIOLOGIA MEDICA PER IMMAGINI E RADIOTERAPIA
ESAME di
INFORMATICA e ARCHIVIAZIONE
28 settembre 2011
1 Progettazione di basi di dati
Si vuole progettare il sistema informativo per la gestione delle informazioni relative alle malattie studiate presso un centro di ricerca.
In particolare, si vuole mantener traccia delle informazioni relative alle malattie studiate: il nome, la descrizione ed eventuali sintomi (anche pi`u di uno).
Per ogni malattia si vuole memorizzare le informazioni relative ai pazienti studiati, con la data della prima manifestazione e l’eventuale terminazione.
Per ognuno si vuole memorizzare il nome, il cognome, il codice fiscale, il numero della tessera sanitaria, la data di nascita, l’indirizzo di residenza (citt`a, via, numero) e uno o pi`u numeri di telefono. Tenere presente che lo stesso paziente potrebbe essere oggetto di studio rispetto a diverse malattie.
Infine, nel centro vengono eseguiti diversi tipi di esami. Per ogni paziente si vuole mantener traccia degli esami effettuati o in programma. Per ciascun esame deve essere memorizzato il tipo di esame, la data e l’ora di esecuzione, la relativa rilevazione (che a seconda del tipo di esame pu`o essere uno valore numerico o una descrizione testuale) e un esito.
Progettazione concettuale Definire uno schema Entit`a-Relazioni che descriva il contenuto informativo del sistema, illustrando con chiarezza le eventuali assunzioni fatte. Lo schema dovr`a essere completato con attribu- ti ragionevoli per ciascuna entit`a (indicando i possibili identificatori) e re-
Figura 1: Schema ER
Progettazione logica Apportare le necessarie modifiche di ristrutturazione allo schema Entit`a-Relazioni prodotto e tradurlo nello schema logico re- lazionale.
Ristrutturazione:
1. attributo composto: l’attributo indirizzo dell’entit`a PAZIENTE ven- gono scomposti nei diversi attributi semplici che li compongono;
2. attributi multivalore: l’attributo telefono dell’entit`a PAZIENTE `e sos- tituito dall’entit`a TELEFONO e dalla relativa relazione che lo lega all’entit`a PAZIENTE e l’attributo sintomo dell’entit`a MALATTIA `e sostituito dall’entit`a SINTOMO e dalla relativa relazione che lo lega all’entit`a MALATTIA;
3. specializzazione dell’entit`a MALATTIA: supponendo che l’accesso ai dati dei risultati degli esami sia fatta indipendentemente dal tipo di esame si `e deciso di mantenere solo il padre della specializzazione, eliminando le figlie.
Lo schema ER ristrutturato `e rappresentato in Figura 2.
Figura 2: Schema ER ristrutturato
Traduzione:
1. entit`a:
P AZIEN T E(codiceF iscale, nome, cognome, citta, via, numero, tesseraSanitaria, dataN ascita)
nome, cognome, citta, via, numero, tesseraSanitaria, dataNascita NOT NULL
tesseraSanitaria UNIQUE (chiave alternativa a codiceFiscale)
T ELEF ON O(numero)
M ALAT T IA(nome, descrizione) desrizione NOT NULL
SIN T OM O(descrizione)
ESAM E(data, ora, tipoEsame, esito, valore, descrizione) tipoEsame NOT NULL
fk (paziente) references PAZIENTE(codiceFiscale)
3. relazioni MOLTI-A-MOLTI (nuove tabelle):
relazione ha:
HA(paziente, telef ono)
fk (paziente) references PAZIENTE(codiceFiscale) fk (telefono) references TELEFONO(numero)
relazione presenta:
P RESEN T A(paziente, malattia, primaM anif estazione, terminazione) primaManifestazione NOT NULL
fk (paziente) references PAZIENTE(codiceFiscale) fk (malattia) references MALATTIA(nome)
relazione evidenza:
P RESEN T A(malattia, sintomo)
fk (malattia) references MALATTIA(nome) fk (sintomo) references SINTOMO(descrizione)
2 Modello relazionale e linguaggio SQL
Si consideri il seguente schema logico relazionale:
OSP EDALE(codice, nome, comune, via, annoF ondazione)
REP ART O(ospedale, nome, nomeP rimario, cognomeP rimario, dataApertura) ST AN ZA(ospadale, reparto, numeroStanza, numeroLetti, televisore)1
Definire preliminarmente le chiavi primarie e i vincoli di chiave esterna sulle relazioni date.
OSP EDALE(codice, nome, comune, via, annoF ondazione)
REP ART O(ospedale, nome, nomeP rimario, cognomeP rimario, dataApertura) fk (ospedale) references OSPEDALE(codice)
ST AN ZA(ospadale, reparto, numeroStanza, numeroLetti, televisore) fk (ospedale, reparto) references REPARTO(ospedale, reparto) Successivamente, supponendo di aver gi`a creato le tabelle OSPEDALE e REPARTO, formulare l’istruzione SQL per la creazione della tabella STAN- ZA con tutti i vincoli necessari.
CREATE TABLE STANZA(
o s p e d a l e CHAR( 5 ) , r e p a r t o VARCHAR( 2 0 ) , numeroStanza INTEGER,
n u m e r o L e t t i INTEGER NOT NULL, t e l e v i s o r e BOOLEAN NOT NULL,
PRIMARY KEY ( o s p e d a l e , r e p a r t o , numeroStanza ) ,
FOREIGN KEY ( o s p e d a l e , r e p a r t o ) REFERENCES REPARTO( o s p e d a l e , r e p a r t o )
)
Infine, formulare opportune interrogazioni nel linguaggio SQL che permet- tano di determinare:
1. il numero totale dei letti dell’ospedale con codice OSP05
SELECT SUM( n u m e r o L e t t i )
FROM REPARTO
WHERE o s p e d a l e= ’ OSP05 ’
2. il nome e il comune degli ospedali in cui c’`e almeno un primario con cognome Rossi
SELECT OSPEDALE. nome , comune
FROM OSPEDALE JOIN REPARTO ON OSPEDALE. c o d i c e=REPARTO.
o s p e d a l e
WHERE cognomePrimario= ’ R o s s i ’
3. il codice degli ospedali che hanno almeno due reparti aperti dopo il 31/12/2009 (possibilmente senza usare funzioni aggregate)
SELECT DISTINCT R1 . o s p e d a l e FROM REPARTO AS R1
, REPARTO AS R2
WHERE R1 . o s p e d a l e = R2 . o s p e d a l e AND R1 . nome > R2 . nome
AND R1 . d a t a A p e r t u r a > ’ 3 1 / 1 2 / 2 0 0 9 ’ AND R2 . d a t a A p e r t u r a > ’ 3 1 / 1 2 / 2 0 0 9 ’ SELECT o s p e d a l e
FROM REPARTO
WHERE d a t a A p e r t u r a > ’ 3 1 / 1 2 / 2 0 0 9 ’ GROUP BY o s p e d a l e
HAVING COUNT( ∗ ) >1
4. il nome e il cognome dei primari dei reparti che non hanno nessuna stanza con il televisore e il cui ospedale di appartenenza `e stato fondato prima del 1980 (si assuma che non esistano due primari omonimi per nome e cognome)
SELECT nomePrimario , cognomePrimario
FROM OSPEDALE JOIN REPARTO ON c o d i c e=o s p e d a l e WHERE a n n o F o n d a z i o n e < 1980
EXCEPT
SELECT nomePrimario , cognomePrimario
FROM REPARTO JOIN STANZA ON REPARTO. o s p e d a l e=STANZA.
r e p a r t o AND REPARTO. nome=STANZA. r e p a r t o WHERE t e l e v i s i o n e
SELECT nomePrimario , cognomePrimario
FROM OSPEDALE JOIN REPARTO ON c o d i c e=o s p e d a l e WHERE a n n o F o n d a z i o n e < 1980
AND ( o s p e d a l e ,REPARTO. nome ) NOT IN ( SELECT o s p e d a l e , r e p a r t o
FROM STANZA
WHERE t e l e v i s i o n e ) SELECT nomePrimario , cognomePrimario
FROM OSPEDALE JOIN REPARTO ON c o d i c e=o s p e d a l e WHERE a n n o F o n d a z i o n e < 1980
AND NOT EXISTS ( SELECT ∗
FROM STANZA
WHERE o s p e d a l e=REPARTO. o s p e d a l e AND r e p a r t o=REPARTO. nome AND t e l e v i s i o n e )
