Progettazione Logica

Dallo schema E-R alle tabelle

Lo schema concettuale descrive la realtà; il DBMS però vuole tabelle. La progettazione logica fa da ponte, e non è una traduzione meccanica: prima lo schema E-R viene ristrutturato tenendo conto di come i dati verranno usati, e solo dopo viene tradotto.

È la fase in cui si prendono le decisioni che poi si pagano (o si incassano) per anni: tenere o no una ridondanza, come sciogliere una generalizzazione, quale identificatore promuovere a chiave primaria.

Obiettivo della fase

Ingressi e uscite

La ristrutturazione dipende da come i dati verranno usati; la traduzione invece segue regole fisse, che conviene sapere a memoria.

Dopo la ristrutturazione, lo schema non è più uno schema concettuale in senso stretto: contiene scelte dettate dall'efficienza e dai limiti del modello relazionale. È normale e voluto, ma va documentato.

Valutare le prestazioni

Per decidere fra due alternative servono numeri, non opinioni. Si usano due indicatori grossolani ma efficaci:

spazio: quante occorrenze di entità e associazioni saranno memorizzate;
tempo: quante occorrenze bisogna visitare per portare a termine un'operazione tipica.

Tavola dei volumi

Elenca, per ogni concetto dello schema, quante occorrenze ci si aspetta. I numeri vengono dall'analisi dei requisiti («circa 2000 impiegati», «ogni impiegato segue in media 3 progetti»).

Concetto	Tipo	Volume
Sede	Entità	10
Dipartimento	Entità	80
Impiegato	Entità	2 000
Progetto	Entità	500
Afferenza	Associazione	1 900
Partecipazione	Associazione	6 000

I volumi delle associazioni dicono molto: 6 000 partecipazioni su 2 000 impiegati significa che in media ogni impiegato partecipa a 3 progetti. È questo numero, non la cardinalità «N», che serve per stimare i costi.

Tavola degli accessi

Per ogni operazione frequente si segue il percorso sullo schema («schema di navigazione») e si conta quante occorrenze si leggono o scrivono. Esempio: «trova tutti i dati di un impiegato, del suo dipartimento e dei progetti a cui partecipa».

Concetto	Costrutto	Accessi	Tipo
Impiegato	Entità	1	lettura
Afferenza	Associazione	1	lettura
Dipartimento	Entità	1	lettura
Partecipazione	Associazione	3	lettura
Progetto	Entità	3	lettura

Gli accessi in scrittura contano doppio. Una scrittura costa più di una lettura (log, indici, eventuali blocchi). Nei confronti fra alternative si usa la convenzione: costo = letture + 2 × scritture.

Ristrutturazione dello schema E-R

Quattro attività, in quest'ordine:

analisi delle ridondanze;
eliminazione delle generalizzazioni;
partizionamento e accorpamento di entità e associazioni;
scelta degli identificatori primari.

Analisi delle ridondanze

Una ridondanza è un'informazione significativa ma derivabile da altre già presenti. Le forme tipiche:

Forma	Esempio
Attributo derivabile da altri della stessa entità	ImportoLordo = ImportoNetto + IVA in Fattura
Attributo derivabile da altre entità	ImportoTotale di un acquisto, somma di prezzo × quantità
Attributo derivabile da un conteggio	NumeroAbitanti di una città
Associazione derivabile da un ciclo	tre associazioni che formano un triangolo

Tenere una ridondanza riduce le letture (il dato è già pronto) ma aumenta lo spazio e le scritture (va tenuta aggiornata a ogni modifica). Non esiste una risposta giusta a priori: si fanno i conti.

Il conto che decide

Schema: Persona —(1,1)— Residenza —(1,N)— Città, dove Città ha l'attributo ridondante NumeroAbitanti. Volumi: 200 città, 1 000 000 di persone, quindi 1 000 000 di residenze e in media 5 000 abitanti per città.

Operazioni previste:

Op1: registra una nuova persona con la sua città — 500 volte al giorno;
Op2: stampa i dati di una città, numero di abitanti incluso — 2 volte al giorno.

Con la ridondanza

Op1: scrivo la persona (1 W), leggo la città (1 R), riscrivo la città col contatore aggiornato (1 W), scrivo la residenza (1 W).
→ 3 W + 1 R, per 500 volte = 1 500 W + 500 R

Op2: leggo la città (1 R), per 2 volte = 2 R

Totale: 1 500×2 + 500 + 2 = 3 502 accessi/giorno

Senza la ridondanza

Op1: scrivo la persona (1 W), scrivo la residenza (1 W).
→ 2 W, per 500 volte = 1 000 W

Op2: leggo la città (1 R) e conto le 5 000 residenze (5 000 R), per 2 volte = 10 002 R

Totale: 1 000×2 + 10 002 = 12 002 accessi/giorno

Qui la ridondanza conviene, e di parecchio: il contatore costa poco da mantenere e fa risparmiare una scansione enorme. Ribalta i numeri (per esempio 10 000 nuove persone al giorno e una sola stampa al mese) e la conclusione si rovescia. È esattamente il ragionamento che l'esame chiede di saper fare.

Eliminare le generalizzazioni

Il modello relazionale non sa rappresentare le gerarchie: entità e associazioni sì, generalizzazioni no. Vanno quindi sciolte, in uno dei tre modi.

Soluzione	Come	Quando conviene	Prezzo
1. Figlie nel padre	una sola entità con tutti gli attributi, più un attributo "tipo" che dice di quale figlia si tratta	quando padre e figlie si accedono insieme	molti valori NULL
2. Padre nelle figlie	si elimina il padre e si replicano i suoi attributi e le sue associazioni in ogni figlia	quando le figlie si accedono separatamente (possibile solo se la generalizzazione è totale)	duplicazione della struttura; le associazioni del padre vanno replicate
3. Associazioni	si tengono padre e figlie come entità distinte, legate da associazioni uno a uno	quando gli accessi al padre sono distinti da quelli alle figlie	ogni accesso completo richiede un join

Sono ammesse soluzioni ibride, soprattutto nelle gerarchie a più livelli: si accorpa dove gli accessi sono contestuali e si separa dove sono distinti.

Partizionare e accorpare

Il principio è uno solo: a ogni accesso si legge l'intera occorrenza. Quindi conviene separare ciò che si usa separatamente e unire ciò che si usa insieme.

Partizionamento verticale di entità: Impiegato con dati anagrafici e dati retributivi diventa due entità legate da un'associazione uno a uno, se le due parti si consultano in momenti diversi.
Partizionamento orizzontale di associazione: Composizione di una squadra diventa Composizione attuale e Composizione passata, perché le query sul presente non devono scorrere lo storico.
Accorpamento: un'entità collegata con (1,1)–(0,1) a un'altra e senza vita propria viene assorbita, evitando un join sistematico.

Eliminare gli attributi multivalore

Una colonna contiene un valore solo: gli attributi multivalore vanno tolti. La trasformazione standard è promuoverli a entità collegata da un'associazione uno a molti.

Scelta degli identificatori primari

Se un'entità ha più identificatori bisogna sceglierne uno come chiave primaria. I criteri, in ordine di importanza:

niente opzionalità: gli attributi della chiave non possono essere NULL;
semplicità: pochi attributi, meglio se uno solo, meglio se numerico;
uso nelle operazioni frequenti: la chiave su cui si cerca più spesso.

Se nessun identificatore va bene si introduce un codice artificiale (un contatore). Ricorda però di aggiungere un vincolo di unicità sull'identificatore "vero", altrimenti il database accetterà duplicati logici.

Traduzione verso il modello relazionale

L'idea di fondo si riassume in due righe: le entità diventano relazioni sui propri attributi; le associazioni diventano relazioni sugli identificatori delle entità coinvolte, più i propri attributi. Le regole che seguono sono la declinazione di questa idea caso per caso.

Entità forti

Una relazione con lo stesso nome, tutti gli attributi dell'entità (gli attributi composti si sciolgono nei componenti), l'identificatore primario come chiave primaria e gli altri identificatori come chiavi candidate. Gli attributi opzionali ammettono NULL.

Impiegato

Matricola PK
Cognome
Stipendio

Associazioni binarie molti a molti

Servono tre relazioni: una per ciascuna entità e una per l'associazione. Quest'ultima ha come attributi le due chiavi primarie più gli attributi propri dell'associazione; la sua chiave primaria è l'unione delle due chiavi, e su ciascuna metà si mette un vincolo di integrità referenziale.

Impiegato

Matricola PK
Cognome
Stipendio

Partecipazione

Matricola PK
Codice PK
DataInizio

Progetto

Codice PK
Nome
Budget

La cardinalità minima 1 (ogni progetto deve avere almeno un impiegato) diventa un vincolo di esistenza che il modello relazionale non sa esprimere: va scritto nella documentazione e, se serve, imposto con un trigger.

Associazioni binarie uno a molti

Bastano due relazioni: l'associazione viene assorbita dall'entità che partecipa con cardinalità massima 1, che riceve la chiave dell'altra (diventando chiave esterna) più gli eventuali attributi dell'associazione.

Associazioni binarie uno a uno

Si può accorpare da una parte o dall'altra, o fondere tutto in un'unica relazione. Il criterio è minimizzare i NULL: si accorpa dal lato che partecipa obbligatoriamente.

Cardinalità	Scelta
(1,1) e (1,1)	indifferente; si può anche fondere in una sola relazione (poco realistico, ma didatticamente utile)
(1,1) e (0,1)	accorpare nell'entità con (0,1): così la chiave esterna non è mai NULL
(0,1) e (0,1)	accorpare dal lato che produce meno NULL, stimandolo con i volumi

Associazioni ricorsive

Stesse regole, con l'accortezza di rinominare le due copie della chiave, usando i ruoli.

Prodotto (ricorsiva N:N)

Codice PK
Nome
Costo

Composizione

Composto PK FK
Componente PK FK
Quantità

Persona (ricorsiva 1:N)

CF PK
Padre FK NULL

Associazioni n-arie

Generalizzano il caso molti a molti: una relazione per ciascuna delle n entità più una per l'associazione, la cui chiave primaria è l'unione delle n chiavi e che porta n vincoli di integrità referenziale.

Fornitura

Fornitore PK FK
Prodotto PK FK
Dipartimento PK FK
Quantità

Entità deboli

L'entità debole diventa una relazione che contiene i propri attributi più la chiave dell'entità che la identifica: la chiave primaria è l'unione delle due parti.

Università

Nome PK
Indirizzo

Studente

Matricola PK
Università PK FK
Cognome
AnnoCorso

Un ordine di lavoro che funziona. 1) traduci le entità forti che partecipano sempre con cardinalità massima N; 2) traduci le entità che partecipano con (1,1) ad almeno un'associazione, assorbendola; 3) poi quelle con (0,1); 4) traduci le entità deboli; 5) infine crea una relazione per ogni associazione rimasta. Seguendolo non ti dimentichi pezzi e non crei tabelle inutili.

Caso di studio: dalla clinica alle tabelle

Riprendiamo lo schema E-R costruito nel modulo precedente: Personale con le figlie Medico e Infermiere, Paziente, e Visita come entità debole identificata da medico, paziente e data.

Ristrutturazione

Ridondanze. Nello schema non ce ne sono. Se ne potrebbe introdurre una (TotaleVisite su Medico, UltimaVisita su Paziente): i conti dicono che con inserimenti frequenti e statistiche rare non conviene, quindi si lascia perdere. La decisione va scritta nella documentazione, perché è una scelta, non una dimenticanza.

Generalizzazione. Medici e infermieri vengono consultati in contesti diversi: le visite riguardano solo i medici, i turni di reparto solo gli infermieri. Gli accessi alle figlie sono quindi distinti e la generalizzazione è totale: si sceglie la soluzione 2, accorpare il padre nelle figlie. Matricola e DataAssunzione vengono replicate in entrambe.

Identificatori. Matricola per il medico (numerica, mai nulla, usata ovunque), CodFiscale per il paziente. Per la visita l'identificatore è composto: medico, paziente e data.

Traduzione passo passo

✓

Questo è esattamente lo schema su cui hai scritto le query nel modulo SQL: MEDICO, PAZIENTE, VISITA(Medico, Paziente, Data, Mutuata). Il giro è chiuso: requisiti → E-R → tabelle → query.

Esercizi svolti

Tradurre uno schema

1. Traduci nel modello relazionale: Impiegato —(0,1)— Direzione —(1,1)— Dipartimento (un dipartimento ha esattamente un direttore, un impiegato dirige al più un dipartimento), con Impiegato(Matricola, Cognome) e Dipartimento(Nome, Sede, Telefono); l'associazione ha l'attributo DataInizio.

Soluzione

È un'associazione uno a uno con un lato obbligatorio e uno opzionale. Si accorpa dal lato obbligatorio, cioè dentro Dipartimento: così la chiave esterna è sempre valorizzata e non si generano NULL.

Impiegato

Matricola PK
Cognome

Dipartimento

Nome PK
Sede, Telefono
Direttore FK
DataInizio

La soluzione opposta (mettere Dipartimento dentro Impiegato) funziona ma riempie di NULL la tabella: su 2 000 impiegati e 80 dipartimenti, 1 920 righe avrebbero direttore nullo.

2. Traduci un'entità debole Copia identificata da Libro (ISBN) più il proprio Numero, sapendo che ogni copia appartiene a un solo libro e un libro ha almeno una copia.

Soluzione

Libro

ISBN PK
Titolo, Anno

Copia

ISBN PK FK
Numero PK
Stato

Il vincolo «un libro ha almeno una copia» (cardinalità minima 1 dal lato del libro) non è traducibile: va documentato.

Decidere una ridondanza

3. In un e-commerce, l'entità Ordine potrebbe avere l'attributo ridondante Totale, derivabile dalle righe d'ordine (prezzo × quantità). Sapendo che si inseriscono 5 000 righe d'ordine al giorno, che un ordine ha in media 4 righe, e che il totale viene letto 20 000 volte al giorno (ogni volta che un cliente apre la lista dei suoi ordini), conviene tenerlo?

Soluzione

Con la ridondanza: ogni riga inserita comporta anche l'aggiornamento del totale dell'ordine (1 lettura + 1 scrittura in più): 5 000 × (1 R + 1 W) = 5 000 R + 5 000 W → 5 000 + 10 000 = 15 000. Le letture del totale costano 20 000 × 1 = 20 000. Totale ≈ 35 000.

Senza la ridondanza: gli inserimenti non costano nulla in più, ma ogni lettura del totale richiede di leggere le 4 righe dell'ordine: 20 000 × 4 = 80 000 letture. Totale ≈ 80 000.

La ridondanza conviene: le letture sono molto più frequenti delle scritture e il ricalcolo costa quanto il numero di righe. Va però mantenuta coerente, tipicamente con un trigger sulle righe d'ordine.

→

Prossimo passo. Lo schema ottenuto è corretto, ma è davvero "ben fatto"? Le dipendenze funzionali e le forme normali danno un criterio formale per rispondere, e una procedura per rimediare quando la risposta è no.