Progettazione: relazione N:N - partecipazione parziale

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

image/svg+xml Layer 1 dataDiScadenza costo tipo(...) MEDICINA nome (0,N) CURA descrizione MALATTIA (1,N)

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à MALATTIA parteciperanno alla relazione. Questa condizione si chiama partecipazione parziale.

Schema logico: relazionale

medicina (id, nome, tipo, costo, dataDiScadenza)

malattia (id, descrizione)

cura (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

medicina
id nome tipo costo dataDiScadenza
1 Ribex tosse sciroppo 12.00 25/10/2016
2 Zepelin compresse 6.00 10/09/2018
3 Moment compresse 7.50 01/06/2016

malattia
id descrizione
4 Mal di testa
5 Raffreddore
6 Tosse
7 Dolori articolari
8 Pediculosi

cura
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 medicine di tipo compresse.

select nome, dataDiScadenza
from medicina
where (tipo = 'compresse')
nome dataDiScadenza
Zepelin 10/09/2018
Moment 01/06/2016

Interrogazione - 2

Mostrare il nome e la data di scadenza di tutte le medicine da usare contro il mal di testa.

select nome, dataDiScadenza
from medicina as me, malattia as ma, cura as c
where
    (ma.descrizione = 'Mal di testa') and
    (ma.id = c.idMalattia) and
    (c.idMedicina = me.id)
nome dataDiScadenza
Ribex Tosse 25/10/2016

Interrogazione - 3

Mostrare un elenco di tutte le malattie in ordine alfabetico con accanto il medicinale disponibile e la sua data di scadenza.

select descrizione, nome, dataDiScadenza
from medicina as me, malattia as ma, cura as c
where (ma.id = c.idMalattia) and (c.idMedicina = me.id)
order by descrizione
descrizione nome dataDiScadenza
Dolori articolari Zepelin 10/09/2018
Dolori articolari Moment 01/06/2016
Mal di testa Zepelin 10/09/2018
Mal di testa Moment 01/06/2016
Tosse Ribex tosse 25/10/2016

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.

select nome, costo
from medicina
where (dataDiScadenza > '2017-01-01')
nome costo
Zepelin 6.00

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 malattia
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?