BASI DATI: algebra relazionale - sira.diei.unipg.it ed Informatica... · BASI DATI: algebra...
Transcript of BASI DATI: algebra relazionale - sira.diei.unipg.it ed Informatica... · BASI DATI: algebra...
BASI DATI: algebra relazionale
BIOINGEGNERIA ED INFORMATICA MEDICA
Lezione IV - BioIngInfMed 1
Algebra relazionale Definizione
L'algebra relazionale è un insieme di operazioni (query) che servono per manipolare relazioni (tabelle). In altri termini, l'algebra relazionale è la formalizzazione matematica del modo in cui è possibile estrarre da un database le informazioni di interesse.
1. Operazione di selezione 𝜎(condizione sul valore della tupla)
2. Operazione di proiezione 𝜋(sottoinsieme di attributi della tupla)
3. Operazioni insiemistiche (unione, intersezione, differenza)
4. Operazione di join
Lezione IV - BioIngInfMed 2
L'operatore di selezione (1)
Lezione IV - BioIngInfMed 3
Selezione 𝝈
L'operatore di selezione 𝜎 è usato per selezionare da una
relazione un sottoinsieme di tuple che soddisfano un
certo criterio
Una operazione di selezione si specifica tramite la
seguente espressione:
𝜎𝑐𝑜𝑛𝑑𝑖𝑧𝑖𝑜𝑛𝑒(𝑟𝑒𝑙𝑎𝑧𝑖𝑜𝑛𝑒)
Una operazione di selezione, quindi, restituisce una
relazione con il medesimo schema, ma con le sole
tuple che soddisfano la condizione
L'operatore di selezione (2)
Lezione IV - BioIngInfMed 4
Ad esempio, consideriamo di voler selezionare il
questionario dello studente di matricola '120142‘
𝜎𝑚𝑎𝑡𝑟𝑖𝑐𝑜𝑙𝑎=′120141′(𝑞𝑢𝑒𝑠𝑡𝑖𝑜𝑛𝑎𝑟𝑖_𝑠𝑡𝑢𝑑𝑒𝑛𝑡𝑖)
L'operatore di selezione (3)
Lezione IV - BioIngInfMed 5
Ad esempio, consideriamo di voler selezionare i
questionari delle ragazze
𝜎𝑠𝑒𝑠𝑠𝑜=′𝑓′(𝑞𝑢𝑒𝑠𝑡𝑖𝑜𝑛𝑎𝑟𝑖_𝑠𝑡𝑢𝑑𝑒𝑛𝑡𝑖)
L'operatore di selezione (4)
Lezione IV - BioIngInfMed 6
Ad esempio, consideriamo di voler selezionare i
questionari delle ragazze con meno di 30 anni
𝜎𝑠𝑒𝑠𝑠𝑜=′𝑓′𝐴𝑁𝐷 𝑒𝑡𝑎<30(𝑞𝑢𝑒𝑠𝑡𝑖𝑜𝑛𝑎𝑟𝑖_𝑠𝑡𝑢𝑑𝑒𝑛𝑡𝑖)
L'operatore di proiezione (1)
Lezione IV - BioIngInfMed 7
Proiezione 𝜋
L'operazione di proiezione 𝜋 seleziona dalla
relazione solo le colonne specificate
Una operazione di proiezione si specifica tramite la
seguente espressione:
𝜋𝑝𝑟𝑜𝑖𝑒𝑧𝑖𝑜𝑛𝑒(𝑟𝑒𝑙𝑎𝑧𝑖𝑜𝑛𝑒)
Una operazione di proiezione, quindi, restituisce una
relazione con differente schema, ma con le stesse
tuple della relazione originaria
L'operatore di proiezione (2)
Lezione IV - BioIngInfMed 8
Ad esempio, consideriamo l'operazione seguente (i.e.
restituisci le colonne «matricola» e «r1»)
𝜋𝑚𝑎𝑡𝑟𝑖𝑐𝑜𝑙𝑎,𝑟1(𝑞𝑢𝑒𝑠𝑡𝑖𝑜𝑛𝑎𝑟𝑖_𝑠𝑡𝑢𝑑𝑒𝑛𝑡𝑖)
Sequenze di operatori
Lezione IV - BioIngInfMed 9
Ad esempio, consideriamo l'operazione seguente (i.e. restituisci le colonne
‘matricola’ e ‘r1’ dalle tuple in cui il sesso è ‘f ’)
𝜋𝑚𝑎𝑡𝑟𝑖𝑐𝑜𝑙𝑎,𝑟1(𝜎𝑠𝑒𝑠𝑠𝑜=′𝑓′(𝑞𝑢𝑒𝑠𝑡𝑖𝑜𝑛𝑎𝑟𝑖_𝑠𝑡𝑢𝑑𝑒𝑛𝑡𝑖))
Operazione di join (1)
Join
L'operazione di join viene usata per combinare tuple
provenienti da più relazioni in uniche tuple, secondo un
determinato criterio
Una operazione di join si specifica tramite la seguente
espressione:
Lezione IV - BioIngInfMed 10
Operazione join (2)
Lezione IV - BioIngInfMed 11
Ad esempio, consideriamo l'operazione seguente (i.e.
restituisci tutte le informazioni circa i pazienti e le
rispettive sedi elettorali)
Natural join (1)
L'operatore di join e fondamentale perchè permette di reperire informazioni che sono contenute in relazioni tra loro collegate tramite chiavi esterne
In tal caso, è possibile ricorrere al natural join
Il natural join è - essenzialmente - il join su tabelle collegate secondo quanto espresso dal vincolo di integrità referenziale
Lezione IV - BioIngInfMed 12
Natural join (2)
Lezione IV - BioIngInfMed 13
Siccome pazienti.numero sede elettorale è chiave
esterna per sedi elettorali.numero, allora
Come si compone una query
Lezione IV - BioIngInfMed 14
Una query in algebra relazionale si può sempre scrivere
come:
𝜋𝑎𝑡𝑡𝑟𝑖𝑏𝑢𝑡𝑖 (𝜎𝑐𝑟𝑖𝑡𝑒𝑟𝑖𝑜(𝑟1 ∗ 𝑟2 ∗∗∗∗∗ 𝑟𝑛))
Per comporre una query in algebra relazionale:
1. Identificare le tabelle che contengono i dati oggetto
della query, e metterle nei natural join
2. Identificare la parte della query che rappresenta il
criterio di selezione delle righe
3. Identificare la parte della query che specifica quali
attributi vogliamo ottenere in uscita tramite
proiezione
Esempi di query in algebra relazionale (1)
Lezione IV - BioIngInfMed 15
1. Una sola tabella è coinvolta nella query: pazienti
2. Il criterio di selezione indica i soli maschi: sesso='m'
3. Voglio tutti gli attributi: nessun 𝜋
Esempi di query in algebra relazionale (2)
Lezione IV - BioIngInfMed 16
1. Una sola tabella è coinvolta nella query: pazienti
2. Il criterio di selezione indica una età < di 30: eta < 30
3. Voglio il solo sesso come attributo: sesso
Esempi di query in algebra relazionale (3)
Lezione IV - BioIngInfMed 17
1. Due tabelle sono
coinvolte nella query:
pazienti, sedi elettorali
2. Il criterio di selezione
indica il sesso femminile:
sesso='f'
3. Voglio il solo sesso come
attributo: eta, distanza
Esempi di query in algebra relazionale (4)
Lezione IV - BioIngInfMed 18
Età e distanza dall'azienda chimica di tutti i pazienti di sesso
feminile
Esempi di query in algebra relazionale (4)
Lezione IV - BioIngInfMed 19
Esempi di query in algebra relazionale (4)
Lezione IV - BioIngInfMed 20
Esempi di query in algebra relazionale (4)
Lezione IV - BioIngInfMed 21
Esempi di query in algebra relazionale (5)
Lezione IV - BioIngInfMed 22
1. Tre tabelle sono coinvolte
nella query: pazienti,soffre,
patologie (N.B. anche le
tabelle ‘intermedie’ devono
essere considerate)
2. Il criterio di selezione non è
specificato: nessun 𝜋
3. Voglio l'età, il sesso e il DRG
come attributi: eta, sesso,
DRG
Esempi di query in algebra relazionale (6)
Lezione IV - BioIngInfMed 23
Eta, sesso e DRG di tutti i pazienti
Esempi di query in algebra relazionale (6)
Lezione IV - BioIngInfMed 24
Esempi di query in algebra relazionale (6)
Lezione IV - BioIngInfMed 25
Esempi di query in algebra relazionale (6)
Lezione IV - BioIngInfMed 26
SQL
SQL (Structured Query Language)
Linguaggio standard usato da ogni DBMS per
l’interrogazione dei dati
Può
Creare/distruggere database
Creare/alterare/distruggere tabelle
Inserire/rimuovere dati
Interrogare la base di dati
Lezione IV - BioIngInfMed 27
SELECT
Il comando SQL per estrarre dati da un DB la
SELECT
La SELECT si compone di (almeno) tre parti
Gli attributi da restituire
Le tabelle da cui estrarre i dati
Il criterio di selezione
... più eventuali indicazioni circa l’ordinamento, etc.
Lezione IV - BioIngInfMed 28
SELECT
La forma di una SELECT
Lezione IV - BioIngInfMed 29
L'SQL e l'algebra relazionale
Lezione IV - BioIngInfMed 30
Data una query scritta in algebra relazionale, è
possibile scrivere la corrispondente query in SQL
seguendo il seguente schema:
1. Gli attributi del 𝜋 diventano gli attributi della parte
SELECT
2. Le tabelle messe in * si elencano nella lista delle tabelle
della parte FROM
3. Il criterio di selezione e le condizioni di natural join
diventano il criterio della parte WHERE
Le condizioni di natural join sono l'equivalenza della chiave
primaria con la chiave esterna della tabella collegata dal
join
Pazienti maschi
Lezione IV - BioIngInfMed 31
Il sesso dei pazienti di età inferiore ai 30
anni
Lezione IV - BioIngInfMed 32
Paziente e distanza
Quando esistono delle omonimie, è necessario specificare da quale
relazione “preleviamo” gli attributi
Lezione IV - BioIngInfMed 33
Paziente e DRG
Lezione IV - BioIngInfMed 34
Conteggio di tuple
Lezione IV - BioIngInfMed 35
Valori medi
Lezione IV - BioIngInfMed 36
Raggruppamento
Lezione IV - BioIngInfMed 37
Ultima query
Lezione IV - BioIngInfMed 38
Esercizio
Lezione IV - BioIngInfMed 39
Si progetti una base di dati che contenga le informazioni necessarie per la gestione dell’agenda degli appuntamenti di un medico di base.
Il sistema a cui si sta pensando deve consentire al medico di: gestire gli appuntamenti dal lunedì al venerdì, a partire dalle ore 8:00 alle ore
19:00. (fissare gli appuntamenti, visionare gli appuntamenti, rimuovere un appuntamento); ogni appuntamento dura 1 ora.
gestire le informazioni ricevute dal telefono durante la conversazione telefonica
gestire le informazioni relative alle prescrizioni terapeutiche
annotare i dati dei pazienti all’interno dell’agenda, per poter gestire gli appuntamenti di una intera settimana. Per ogni paziente si dovranno memorizzare il nome, cognome,l’ anno di nascita;
Il sistema dovrà fornire al medico: 1. la lista dei pazienti visitati nel mese precedente
2. la lista degli appuntamenti presi per il mese corrente
3. per ogni paziente la lista di tutti gli appuntamenti presi e delle visite eseguite
4. la lista dei farmaci prescritti
5. selezionato il paziente “Pippo”, la lista dei Codici Impegnativa emessi per la prescrizione dei farmaci in terapia.