Basi di Dati prof. A. Longheu

1. Basi di Datiprof. A. Longheu 6 – Progettazione Logica Cap. 7 “Basi di dati” Atzeni – Ceri – Paraboschi - Torlone

2. Progettazione Logica • Obiettivo della Progettazione Logica e’ quello di costruire uno schema logico ,in un determinato modello (ad es. relazionale), che descriva in maniera corretta ed efficiente tutte le informazioni contenute nello schema E-R prodotto dalla progettazione concettuale. • Non si tratta di una semplice traduzione • Nel seguito, il termine entità è lo stesso del modello e-r, il termine associazione è la relazione del modello e-r, per non confonderla con la relazione del modello relazionale (che è una semplice tabella).

3. Progettazione Logica La progettazione logica si articola in due fasi: • Ristrutturazione dello schema E-R: e’ una fase indipendente dal modello logico e si basa su criteri di ottimizzazione dello schema e di successiva semplificazione. • Traduzione verso il Modello Logico: fa riferimento ad un modello logico (ad es. relazionale) e puo’ includere ulteriore ottimizzazione che si basa sul modello logico stesso (es. normalizzazione).

4. Progettazione Logica

5. Ristrutturazione - 1 L’input ed output della fase di ristrutturazione dello schema E-R sono: • Input: Schema Concettuale E-R iniziale, Carico Applicativo previsto (in termini di dimensione dei dati e caratteristica delle operazioni) • Output : Schema E-R ristrutturato che rappresenta i dati e tiene conto degli aspetti realizzativi

6. Ristrutturazione – 2 Determinazione Carico Applicativo • L’input della ristrutturazione è lo schema E-R, ricavato dalla fase di progettazione concettuale, ed il carico applicativo. • Per determinare il carico applicativo vengono definiti degli indici di prestazione per la valutazione di schemi E-R. Tali indici sono due: • Costo di un’operazione: in termini di numero di occorrenze di entita’ ed associazioni che mediamente vanno visitate per rispondere a quella operazione sulla base di dati • Occupazione di memoria: viene valutata in termini dello spazio di memoria (misurato in byte) necessario per memorizzare i dati del sistema.

7. Ristrutturazione – 3 Determinazione Carico Applicativo • Per studiare questi indici abbiamo bisogno di conoscere: • Volume dei dati: a) numero (medio) di occorrenze di ogni entita’ ed associazione b) dimensioni di ciascun attributo • Caratteristiche delle operazioni: a) tipo di operazione (interattiva o batch) b) frequenza (esecuzioni/tempo) c) dati coinvolti (entita’ e o associazioni)

8. Esempio:ditta con sedi in citta’ diverse Ristrutturazione – 4 Determinazione Carico Applicativo

9. Ristrutturazione – 5 Determinazione Carico Applicativo Operazioni dell’esempio • Operazione 1: assegna un impiegato ad un progetto • Operazione 2: trova i dati di un impiegato, del dipartimento nel quale lavora e dei progetti in cui e’ coinvolto • Operazione 3: trova i dati di tutti gli impiegati di un certo dipartimento • Operazione 4: per ogni sede, trova i dipartimenti con il cognome del direttore e l’elenco degli impiegati.

10. Ristrutturazione – 6 Determinazione Carico Applicativo

11. Ristrutturazione – 7 Determinazione Carico Applicativo • Avendo a disposizione il volume dei dati (tabella dei volumi) e le caratteristiche delle operazioni (tabella delle operazioni), e’ possibile calcolare gli indici di prestazione. • Partendo dalla stima dei costi di ogni operazione, tale indice si determina contando il numero di accessi alle occorrenze di entita’ e relazioni necessario per eseguire l’operazione. • Prendiamo per esempio l’operazione 2 e facciamo riferimento allo schema di operazione

12. Ristrutturazione – 8 Determinazione Carico Applicativo Lo schema di operazione in figura mostra (tramite delle frecce) le entità e le relazioni effettivamente coinvolte nell’esecuzione dell’operazione (trova i dati di un impiegato, del dipartimento nel quale lavora e dei progetti in cui e’ coinvolto)

13. Ristrutturazione – 9 Determinazione Carico Applicativo Dobbiamo accedere ad un’occorrenza di Impiegato e di Afferenza e quindi di Dipartimento. Successivamente , per avere i dati dei progetti a cui lavora, dobbiamo accedere (in media) a tre occorrenze di Partecipazione e quindi a tre entita’ Progetto. Tutto viene riassunto nella tavola degli accessi, che costituisce una stima del costo

14. Ristrutturazione – 10 Determinazione Carico Applicativo

15. Ristrutturazione – 11 Determinazione Carico Applicativo Per valutare il secondo indice, l’occupazione di memoria, è sufficiente utilizzare la tabella dei volumi da cui prelevare il numero di occorrenze, aggiungere la dimensione della singola occorrenza ed ottenere quindi l’occupazione di memoria di tutte le entità e relazioni.

16. Ristrutturazione – 12 • Una volta determinato il carico applicativo, ed avendo dalla progettazione concettuale lo schema E-R, tutti gli input della ristrutturazione sono noti. • Si passa quindi alla ristrutturazione vera e propria, per determinare lo schema ristrutturato (output) • La ristrutturazione consta di quattro fasi: analisi delle ridondanze, eliminazione delle generalizzazioni, partizionamento/accorpamento di entità ed associazioni, e scelta degli identificatori principali (chiavi)

17. Ristrutturazione – 13

18. Ristrutturazione – 14 • Analisi delle Ridondanze: si decide se eliminare o no eventuali ridondanze. • Eliminazione delle Generalizzazioni: tutte le generalizzazioni vengono analizzate e sostituite da altro. • Partizionamento/Accorpamento di entita’ ed associazioni: si decide se partizionare concetti in piu’ parti o viceversa accorpare. • Scelta degli identificatori primari: si sceglie un identificatore per quelle entita’ che ne hanno piu’ di uno

19. Ristrutturazione – 15 Analisi delle ridondanze Una Ridondanza in uno schema E-R è la presenza di un attributo che può essere ricavato da altri. In particolare, (vedi esempi nelle tabelle seguenti): • Attributi derivabili da altri attributi della stessa entita’ (importo lordo nella tabella fattura) • Attributi derivabili da attributi di altre entita’(o associazioni) (Acquisto: Importo totale da Prezzo ) • Attributi derivabili da operazioni di conteggio (Citta’: Numero abitanti contando il numero di Residenza ) • Associazioni derivabili dalla composizione di altre associazioni in presenza di cicli. (Docenza da Frequenza ed Insegnamento). Tuttavia i cicli non necessariamente generano ridondanze (Tesi al posto di Docenza).

20. Ristrutturazione – 16 Analisi delle ridondanze

21. Ristrutturazione – 17 Analisi delle ridondanze • Vantaggi nell’avere e mantenere un dato derivabile: riduce gli accessi per calcolare il dato derivato. • Svantaggi : occupazione di memoria e necessita’ di effettuare operazioni aggiuntive per mantenere il dato aggiornato • Decisione: mantenere o eliminare? Basta confrontare i costi di esecuzione delle operazioni sull’oggetto

22. Ristrutturazione – 18 Analisi delle ridondanze • Consideriamo l’esempio Citta’-Persona per l’anagrafica di una regione. • Operazione 1: memorizza una persona nuova con la relativa citta’. • Operazione 2: stampa tutti i dati di una citta’(incluso il numero di abitanti). • Valutiamo gli indici di prestazione per l’attributo Numero Abitanti

23. Ristrutturazione – 19 Analisi delle ridondanze • Si effettua una prima valutazione in presenza della ridondanza: • Assumendo che il numero di abitanti richieda 4 byte il dato richiede 4*200 = 800 byte. L’operazione 1 richiede un accesso in scrittura a Persona uno in scrittura a Residenza ed uno in lettura ed uno in scrittura (per incrementare il numero di abitanti) a Citta’ ripetuto 500 volte si hanno 1500 accessi in scrittura e 500 in lettura. L’operazione 2 richiede un solo accesso in lettura a Citta’ 2 volte al giorno . Supponendo che la scrittura ha un costo doppio rispetto ad una lettura si hanno 3500 accessi al giorno in presenza della ridondanza.

24. Ristrutturazione – 20Analisi delle ridondanze • Si effettua una seconda valutazione in assenza della ridondanza: • Per l’operazione 1 ,un accesso in scrittura a Persona ed uno in scrittura a Residenza per un totale di 1000 accessi in scrittura al giorno.Per l’operazione 2 abbiamo bisogno di un acceso in lettura a Citta’(possiamo trascurare) e 5000 accessi in lettura a Residenza in media (persone/citta’) per un totale di 10.000 accessi in lettura al giorno. Il totale e’ di 12000 accessi in lettura al giorno. Quindi 8500 in piu’ rispetto al caso di ridondanza contro meno di un solo Kbyte di memoria in piu’. D’altra parte se l’operazione 2 fosse stata richiesta solo 1 volta ogni 4 settimane avremmo avuto 3500*24=84000 accessi ogni 4 settimane con ridondanza contro 58000 in assenza.

25. Ristrutturazione – 21Eliminazione delle generalizzazioni • Il modello relazionale non permette di rappresentare gerarchie quindi conviene trasformarle in altri concetti.

26. Ristrutturazione – 22Eliminazione delle generalizzazioni La prima soluzione prevede l’accorpamento dei figli nel padre: le entita’ figlie vengono eliminate e le loro proprieta’ (attributi, associazioni, generalizzazioni) vengono aggiunte al padre, che deve avere in più un attributo (TIPO) per distinguere le entità figlie.

27. Ristrutturazione – 23Eliminazione delle generalizzazioni La seconda soluzione prevede l’accorpamento del padre nei figli: l’entita’ padre viene eliminata e per ereditarieta’ i suoi attributi, identificatori e relazioni vanno ai figli.

28. Ristrutturazione – 24Eliminazione delle generalizzazioni La terza soluzione prevede la sostituzione: la generalizzazione si trasforma in associazioni uno ad uno che legano padre e figli

29. Ristrutturazione – 25Partizionamento/accorpamento • I principi generali sono: • gli accessi si riducono separando attributi di uno stesso concetto che vengono acceduti da operazioni diverse • gli accessi si riducono raggruppando attributi di concetti diversi che vengono acceduti dalle medesime operazioni

30. Ristrutturazione – 26Partizionamento/accorpamento Esempio di partizionamento:

31. Ristrutturazione – 27Partizionamento/accorpamento • Nell’esempio precedente vengono create due entita’ e gli attributi vengono divisi: partizionamento verticale • Se invece si suddivide in due entita’ con gli stessi attributi (ad esempio Analista e Venditore) con operazioni distinte sulle due si ha il partizionamento orizzontale

32. Ristrutturazione – 28Partizionamento/accorpamento • Un caso particolare di partizionamento è quello necessario per l’eliminazione di attributi multivalore, generalmente non supportati dal modello relazionale

33. Ristrutturazione – 29Partizionamento/accorpamento Esempio di partizionamento di associazione:

34. Ristrutturazione – 30Partizionamento/accorpamento Esempio di accorpamento:

35. Ristrutturazione – 31Partizionamento/accorpamento L’accorpamento e’ giustificato se le operazioni piu’ frequenti su Persona richiedono sempre i dati relativi all’appartamento e quindi vogliamo risparmiare gli accessi alla relazione che li lega. Normalmente gli accorpamenti si fanno su relazioni uno ad uno, raramente su uno a molti mai su molti a molti.

36. Ristrutturazione – 32Scelta degli identificatori • Gli identificatori con valori nulli non possono essere principali (non garantiscono l’accesso a tutti i record). • Un identificatore semplice (pochi attributi) e’ da preferire (indici piccoli fanno risparmiare memoria e facilitano la programmazione). • Un identificatore interno e’ preferibile ad uno esterno (stesso motivo del precedente) • Un identificatore usato da molte operazioni per accedere alle occorrenze di un’entita’ e’ da preferire (in quanto ottimizza il maggior numero di operazioni)

37. Traduzione – 1 Associazione molti a molti (m-n) • Nel caso di associazione molti a molti, la traduzione segue le regole: • Ogni entita’ coinvolta si traduce in una relazione (tabella) con lo stesso nome dell’entità, avente i suoi stessi attributi e per chiave il suo identificatore. • La relazione si traduce in una relazione (tabella)con lo stesso nome avente gli stessi attributi e come chiave l’insieme di tutti gli identificatori delle entita’ coinvolte.

38. Traduzione – 2 Associazione molti a molti (m-n) Esempio: • Impiegato(Matricola,Cognome,Stipendio) • Progetto(Codice,Nome,Budget) • Partecipazione(Matricola,Codice,Data inizio)

39. Traduzione – 3 Associazione molti a molti (m-n) • Nell’esempio, esistono vincoli referenziali fra Matricola,Codice di Partecipazione e gli omonimi di Impiegato e Progetto. • Per rendere piu’ comprensibile lo schema si possono rinominare gli attributi di partecipazione con Partecipazione(Impiegato, Progetto, Data inizio)

40. Traduzione – 4 Associazione molti a molti (m-n) In caso di relazioni ricorsive, la ridenominazione può essere indispensabile, come mostrato nell’esempio: • Prodotto(Codice,Nome,Costo) • Composizione(Composto,Componente,Quantita’) • Esiste un vincolo referenziale tra Composto,Componente e l’attributo Codice di Prodotto.

41. Traduzione – 5 Associazione molti a molti (m-n) Esempio di associazione molti a molti con più di due entità coinvolte (le regole sono le stesse): • Fornitore(PartitaIVA,NomeDitta) • Prodotto(Codice,Genere), • Dipartimento(Nome,Telefono) • Fornitura(Fornitore,Prodotto,Dipartimento,Quantita’)

42. Traduzione – 6 Associazione uno a molti (1-n) • Nel caso di associazione uno a molti, la traduzione segue le regole: • L’entita’ partecipante con cardinalità (1,1) si traduce in una relazione (tabella) con lo stesso nome dell’entità, avente i suoi stessi attributi, per chiave il suo identificatore e che ingloba anche la chiave della entità che partecipa con cardinalità (1,n) (tale attributo diventa quindi una chiave esterna), più tutti gli eventuali attributi della associazione (che non avrà quindi una tabella distinta come nel caso m-n). • L’entita’ partecipante con cardinalità (1,n) si traduce in una relazione (tabella) con lo stesso nome dell’entità, avente i suoi stessi attributi, per chiave il suo identificatore

43. Traduzione – 7 Associazione uno a molti (1-n) Giocatore(Cognome,DataNascita,Ruolo,NomeSquadra,Ingaggio) Squadra(Nome,Citta’,ColoriSociali) Vincolo di integrita’ referenziale tra Nomesquadra di Giocatore e Nome di Squadra.

44. Traduzione – 8 Associazione uno a molti (1-n) Esempio di entità con identificatore esterno (la regola ed il risultato sono ancora gli stessi): Studente(Matricola,NomeUniversita,Cognome,AnnoIscrizione) Universita(Nome,Citta,Indirizzo) Vincolo referenziale fra NomeUniversita e Nome.

45. Traduzione – 9 Associazione uno a uno (1-1) • Nel caso di associazione uno a uno con partecipazione obbligatoria per entrambe le entità (non (0,1) ma (1,1)), la traduzione segue le regole: • Una delle entita’ si traduce in una relazione (tabella) con lo stesso nome dell’entità, avente i suoi stessi attributi, per chiave il suo identificatore e che ingloba anche la chiave della altra entità (tale attributo diventa quindi una chiave esterna), più tutti gli eventuali attributi della associazione (come nel caso 1-n, l’associazione non avrà una tabella distinta). • L’entita’ restante si traduce in una relazione (tabella) con lo stesso nome dell’entità, avente i suoi stessi attributi, per chiave il suo identificatore

46. Traduzione – 10 Associazione uno a uno (1-1) In base alle regole, sono possibili due soluzioni: Direttore(Codice,Cognome,Stipendio,DipartimentoDiretto,InizioDirezione) Dipartimento(Nome,Telefono,Sede) oppure Direttore(Codice,Cognome,Stipendio) Dipartimento(Nome,Telefono, Sede, Direttore, InizioDirezione) Esiste in teoria l’opzione di fondere tutto in un’unica tabella; tale opzione viene però scartata perché se in sede di progettazione concettuale era stato deciso di avere due entità legate da una relazione, diventa inopportuno tornare indietro in sede di progettazione logica.

47. Traduzione – 11 Associazione uno a uno (1-1) Qualora una delle entità partecipi alla associazione con cardinalità (0,1) ossia è un’entità con partecipazione opzionale, delle due soluzioni si preferisce quella in cui la associazione viene inglobata dall’altra entità (quella che partecipa con cardinalità (1,1)), questo per evitare i valori nulli che figurerebbero negli attributi relativi all’associazione qualora venissero inglobati nell’entità che partecipa con cardinalità (0,1): Impiegato(Codice,Cognome,Stipendio) Dipartimento(Nome,Telefono,Sede, Direttore,InizioDirezione)

48. Traduzione – 12 Associazione uno a uno (1-1) Per entrambe le entità con partecipazione opzionale, l’associazione può essere tradotta in una tabella distinta (come per il caso m-n): Impiegato(Codice,Cognome,Stipendio) Dipartimento(Nome,Telefono,Sede) Direzione(Direttore,Dipartimento,DataInizioDirezione) Questo sempre per evitare valori nulli negli attributi relativi all’associazione. Lo svantaggio è avere una tabella in più, quindi questa soluzione conviene solo se le entità partecipano “poco” all’associazione, in tal caso infatti sarebbero molti i valori nulli e quindi è meglio avere una tabella distinta per l’associazione, altrimenti l’associazione può essere inglobata sull’entità che partecipa di più (per ridurre il numero dei valori nulli).

49. Traduzione – 13 Schema Riassuntivo delle traduzioni

50. Traduzione – 14 Schema Riassuntivo delle traduzioni