Lezione 7
Problema
Vogliamo creare un piccolo database per tenere traccia di
tutti i medicinali che abbiamo in casa.
Per ogni medicinale vogliamo conoscere i seguenti dati:
il nome, il tipo (sciroppo, capsule, supposte, ecc.),
il costo, la data di scadenza e le
malattie curate.
Schema Concettuale: ER
Nota.
In una relazione N:N un elemento della prima entità può essere collegato a più di un elemento della seconda entità e viceversa.
Nota.
Il primo valore in parentesi indica la quantità minima di corrispondenze. In questo caso lo zero indica che non tutte le istanze della entità MALATTIE parteciperanno alla relazione. Questa condizione si chiama partecipazione parziale.
Schema logico: relazionale
medicine (id, nome, tipo, costo, dataDiScadenza)
malattie (id, descrizione)
medicinePerMalattie (id, idMedicina, idMalattia)
Nota.
Il mapping di una associazione N:N tra le entità A e B avviene creando una relazione che ha le due chiavi esterne su entrambe le entità A e B.
Nota.
La chiave primaria di questa relazione può essere la solita chiave fittizia id ma anche la congiunzione delle due chiavi esterne.
Esempio di istanza
| medicine | ||||
| id | nome | tipo | costo | dataDiScadenza |
|---|---|---|---|---|
| 1 | Ribex tosse | sciroppo | 12.00 | '2016-10-25' |
| 2 | Zepelin | compresse | 6.00 | '2018-9-10' |
| 3 | Moment | compresse | 7.50 | '2016-6-1' |
| malattie | |
| id | descrizione |
|---|---|
| 4 | Mal di testa |
| 5 | Raffreddore |
| 6 | Tosse |
| 7 | Dolori articolari |
| 8 | Pediculosi |
| medicinePerMalattie | ||
| id | idMedicina | idMalattia |
|---|---|---|
| 9 | 1 | 6 |
| 10 | 2 | 4 |
| 11 | 2 | 7 |
| 12 | 3 | 4 |
| 13 | 3 | 7 |
Interrogazione - 1
Mostrare il nome e la data di scadenza di tutte le compresse.
from medicine
where (tipo = 'compresse')
| nome | dataDiScadenza |
|---|---|
| Zepelin | '2018-9-10' |
| Moment | '2016-6-1' |
Interrogazione - 2
Mostrare il nome e la data di scadenza di tutte le medicine da usare contro il mal di testa.
from medicine as me, malattie as ma, medicinePerMalattie as mpm
where
(ma.descrizione = 'Mal di testa') and
(ma.id = mpm.idMalattia) and
(mpm.idMedicina = me.id)
| nome | dataDiScadenza |
|---|---|
| Zepelin | '2018-9-10' |
| Moment | '2016-6-1' |
Interrogazione - 3
Mostrare un elenco di tutte le malattie in ordine alfabetico con accanto il medicinale disponibile e la sua data di scadenza.
from medicine as me, malattie as ma, medicinePerMalattie as mpm
where (ma.id = mpm.idMalattia) and (mpm.idMedicina = me.id)
order by descrizione
| descrizione | nome | dataDiScadenza |
|---|---|---|
| Dolori articolari | Zepelin | '2018-9-10' |
| Dolori articolari | Moment | '2016-6-1' |
| Mal di testa | Zepelin | '2018-9-10' |
| Mal di testa | Moment | '2016-6-1' |
| Tosse | Ribex tosse | '2016-10-25' |
Interrogazione - 4
Mostrare un elenco di tutte le medicine la cui data di scadenza è superiore a una certa data (supponiamo 01/01/2017) con il relativo costo.
from medicine
where (dataDiScadenza > '2017-01-01')
| nome | costo |
|---|---|
| Zepelin | 6.00 |
Interrogazione - 5
Mostrare il nome e la data di scadenza di tutti gli sciroppi e di tutte le supposte da usare contro il mal di testa. Vogliamo l'elenco in ordine alfabetico e vogliamo solo i medicinali che scadono prima del 2023.
from medicine as me, malattie as ma, medicinePerMalattie as mpm
where
(ma.descrizione = 'Mal di testa') and
(ma.id = mpm.idMalattia) and
(mpm.idMedicina = me.id) and
( (m.tipo = 'sciroppo') or (m.tipo = 'supposte'))
order by
(me.nome)
| nome | dataDiScadenza |
|---|---|
| Ribex Tosse | '2016-10-25' |
Challenge
Challenge - 1
Realizzare una interrogazione che produca questo risultato.
| malattie |
|---|
| Dolori articolari |
| Mal di testa |
| Pediculosi |
| Raffreddore |
| Tosse |
Challenge - 2
Realizzare una interrogazione che produca questo risultato.
| formato |
|---|
| compresse |
| sciroppo |
Challenge - 3
Realizzare una interrogazione che produca questo risultato.
| medicina | malattie |
|---|---|
| Moment | Dolori articolari |
| Moment | Mal di testa |
| Ribex tosse | Tosse |
| Zepelin | Dolori articolari |
| Zepelin | Mal di testa |
Challenge - 4
Se volessimo conservare informazioni relative alla casa farmaceutica dei medicinali, in che modo cambierebbero il diagramma ER e lo schema relazionale?
Challenge - 5
Se volessimo conservare informazioni relative alla farmacia (nome e ubicazione) dove sono stati acquistati i farmaci, in che modo cambierebbero il diagramma ER e lo schema relazionale?