UNIVERSITÀ DEGLI STUDI DI UDINE
Facoltà di Medicina e Chirurgia
CORSO DI LAUREA IN TECNICHE DI RADIOLOGIA MEDICA PER IMMAGINI E RADIOTERAPIA
ESAME di
INFORMATICA e ARCHIVIAZIONE
10 settembre 2010
1
Progettazione di basi di dati
Si vuole progettare il sistema informativo per la gestione dei dati di un centro
di radiologia.
Si vuole mantenere traccia delle informazioni relative ai diversi pazienti:
codice fiscale, tessera sanitaria, nome, cognome, data di nascita, indirizzo di
residenza (via, numero, città, cap) e uno o più numeri di telefono.
Ad ogni visita radiologica, della quale si mantiene traccia della data e dell’ora dell’appuntamento, e del paziente a cui si riferiscono, viene associato un
fascicolo (archivio cartaceo). Ogni fascicolo, a cui è associata anche la diagnosi, contiene diverse immagini radiografiche. Per ogni immagine si vuole
mantenere traccia dell’ora di esecuzione e della zona corporea interessata.
Le immagini prodotte dal centro possono essere di due tipi: radiografia
convenzionale (RX), tomografia computerizzata (TC). Nel caso di TC si
mantiene traccia anche del tipo di esame (assiale, spirale o multistrato) e
dell’eventuale mezzo di contrasto (tipo e quantitativo).
Progettazione concettuale Definire uno schema Entità-Relazioni che
descriva il contenuto informativo del sistema, illustrando con chiarezza le
eventuali assunzioni fatte. Lo schema dovrà essere completato con attributi
ragionevoli per ciascuna entità (identificando le possibili chiavi) e relazione.
Vanno specificati accuratamente i vincoli di cardinalità e partecipazione di
ciascuna relazione.
1
Figura 1: Schema ER
Note:
• Si suppone non parlando di ambulatori o medici diversi non possa
essere effettuata più di una visita alla volta e quindi la data e l’ora di
una visita possano identificarle univcamente
• Lo schema riportato vede l’integrazione di quelle che potrebbero essere
modellate come due entità distinte VISITA e FASCICOLO nell’unica entità FASCICOLO. Modellandole infatti come due entità distinte
risulterebbero legate da una relazione di tipo uno a uno (ad ogni visita
corrisponde un fascicolo), in cui VISITA partecipa in modo parziale
(se è stata solo fissata ma non ancora eseguita non avrà un fascicolo
collegato) e FASCICOLO partecipa in modo totale. Inoltre, vista la
corrispondenza fra le due, definito l’identificatore per l’entità VISITA,
l’entità FASCICOLO risulta essere un’entità debole identificata esclusivamente attraverso la VISITA a cui fa riferimento. Lo schema infatti
potrebbe anche essere quello rappresentato in Figura 2. Questo ovviamente in fase di ristrutturazione potrebbe essere trasformato nello
schema precedente.
Progettazione logica Apportare le necessarie modifiche di ristrutturazione
allo schema Entità-Relazioni prodotto e tradurlo nello schema logico relazionale.
Ristrutturazione:
1. attributi composti: attributo indirizzo dell’entità P AZIEN T E e
attributo contrasto dell’entità T AC
2. attributi multivalore: attributo telef ono dell’entità P AZIEN T E e
attributo evidenze dell’entità IM M AGIN E
2
Figura 2: Schema ER 2
3. specializzazioni: supponendo che i reperti vengano considerati indipendentemente dal loro specifico tipo si decide di mantenere solo l’entità
padre, eliminando le entità figlie. La tipologia di specializzazione può
essere individuata dalla presenza o meno degli attributi di tipo della
specializzazione TC
Lo schema ER ristrutturato è rappresentato in Figura 3.
Figura 3: Schema ER ristrutturato
Traduzione:
1. entità:
P AZIEN T E(codiceF iscale, tesseraSanitaria, nome, cognome,
via, numero, citta, cap)
3
tesseraSanitaria, nome, cognome, via, numero, citta, cap NOT NULL
tesseraSanitaria UNIQUE
F ASCICOLO(data, ora, diagnosi)
IM M AGIN E(dataF , oraF , ora, zonaCorporea, tipoT AC, tipoContrasto,
quantitaContrasto)
zonaCorporea NOT NULL
EV IDEN ZA(descrizione)
T ELEF ON O(numero)
2. relazioni UNO-A-MOLTI (estensione delle tabelle precedenti):
relazione fa:
FASCICOLO(data, ora, diagnosi, paziente)
paziente NOT NULL
fk (paziente) references PAZIENTE(codiceFiscale)
relazione contiene:
IM M AGIN E(dataF , oraF , ora, zonaCorporea, tipoT C, tipoContrasto,
quantitaContrasto)
zonaCorporea NOT NULL
fk (dataF, oraF) references FASCICOLO(data, ora)
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:
presenta(dataF , oraF , ora, evidenza)
fk (dataF, oraF, ora) references IMMAGINE(dataF, oraF, ora)
fk (evidenza) references EVIDENZA(descrizione)
Lo schema logico risultante è stato schematizzato in Figura 4.
4
Figura 4: Schema logico completo
2
Modello relazionale e linguaggio SQL
Si consideri il seguente schema logico relazionale:
P AZIEN T E(codiceF iscale, nome, cognome, dataN ascita, sesso)
V ISIT A(data, ora, ambulatorio, paziente)
AM BU LAT ORIO(id, denominazione)
Definire preliminarmente le chiavi primarie e le chiavi esterne delle relazioni
date.
P AZIEN T E(codiceF iscale, nome, cognome, dataN ascita, sesso)
V ISIT A(data, ora, ambulatorio, paziente, note)
fk (ambulatorio) references AMBULATORIO(id)
fk (paziente) references PAZIENTE(codiceFiscale)
UNIQUE(data, ora, paziente)
– un paziente non può avere due visite contemporaneamente
AM BU LAT ORIO(id, denominazione)
Successivamente, supponendo di aver già creato le tabelle PAZIENTE ed
AMBULATORIO, formulare l’istruzione SQL per la creazione della tabella
VISITA con tutti i vincoli necessari.
CREATE TABLE VISITA (
data
DATE,
ora
TIME,
a m b u l a t o r i o INTEGER,
paziente
CHAR( 1 6 ) NOT NULL,
note
VARCHAR( 2 5 5 ) ,
5
PRIMARY KEY ( data , ora , a m b u l a t o r i o ) ,
UNIQUE ( data , ora , p a z i e n t e ) ,
FOREIGN KEY ( p a z i e n t e ) REFERENCES PAZIENTE( c o d i c e F i s c a l e ) ,
FOREIGN KEY ( a m b u l a t o r i o ) REFERENCES AMBULATORIO( i d ) ,
)
Infine, formulare opportune interrogazioni nel linguaggio SQL che permettano di determianre:
1. l’ora e il codice fiscale dei pazienti che avevano una visita nell’ambulatorio con id 15 il 15/07/2010, secondo l’ordine temporale delle
visite
SELECT ora , p a z i e n t e
FROM
VISITA
WHERE a m b u l a t o r i o =15
AND data= ’ 15/07/2010 ’
ORDER BY o r a
2. il nome e il cognome dei pazienti che hanno fatto almeno una visita in
un ambulatorio pediatrico (denominazione)
SELECT DISTINCT nome , cognome
FROM PAZIENTE, VISITA , AMBULATORIO
WHERE PAZIENTE . c o d i c e F i s c a l e = VISITA=p a z i e n t e
AND VISITA . a m b u l a t o r i o = AMBULATORIO. i d
AND AMBULATORIO. denominazione = ’ p e d i a t r i c o ’
SELECT DISTINCT nome , cognome
FROM PAZIENTE JOIN VISITA
ON PAZIENTE . c o d i c e F i s c a l e = VISITA=p a z i e n t e
JOIN AMBULATORIO
ON VISITA . a m b u l a t o r i o = AMBULATORIO. i d
WHERE AMBULATORIO. denominazione = ’ p e d i a t r i c o ’
3. il codice fiscale dei pazienti che almeno una volta hanno effettuato due
visite nello stesso giorno (possibilmente senza usare funzioni aggregate)
SELECT DISTINCT V1 . p a z i e n t e
FROM VISITA AS V1
, VISITA AS V2
WHERE V1 . p a z i e n t e = V2 . p a z i e n t e
AND V1 . data = V2 . data
AND V1 . o r a <> V2 . o r a
SELECT p a z i e n t e
FROM VISITA
GROUP BY p a z i e n t e , data
HAVING COUNT( ∗ )>1
4. il nome e il cognome dei pazienti che hanno fatto visite sia il 15/7/2010
che il 20/7/2010
6
SELECT DISTINCT nome , cognome
FROM PAZIENTE JOIN VISITA
ON PAZIENTE . c o d i c e F i s c a l e = VISITA=p a z i e n t e
WHERE VISITA . data = ’ 15/7/2010 ’
INTERSECT
SELECT DISTINCT nome , cognome
FROM PAZIENTE JOIN VISITA
ON PAZIENTE . c o d i c e F i s c a l e = VISITA=p a z i e n t e
WHERE VISITA . data = ’ 20/7/2010 ’
SELECT nome , cognome
FROM
PAZIENTE
WHERE c o d i c e F i s c a l e IN ( SELECT p a z i e n t e
FROM
VISITA
WHERE data = ’ 15/7/2010 ’ )
AND c o d i c e F i s c a l e IN ( SELECT p a z i e n t e
FROM
VISITA
WHERE data = ’ 20/7/2010 ’ )
SELECT nome , cognome
FROM
PAZIENTE
WHERE EXISTS ( SELECT ∗
FROM
VISITA
WHERE data = ’ 15/7/2010 ’
AND p a z i e n t e = c o d i c e F i s c a l e )
AND EXISTS ( SELECT ∗
FROM
VISITA
WHERE data = ’ 20/7/2010 ’
AND p a z i e n t e = c o d i c e F i s c a l e )
7