UNIVERSITA' DELLA CALABRIA Corso di Laurea in Informatica Basi di Dati Relazionali a.a. 2009-2010 Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA PROGETTAZIONE DI UNA BASE DI DATI Raccolta e Analisi dei requisiti Progettazione concettuale Schema concettuale Progettazione logica Schema logico Progettazione fisica Schema fisico Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA PROGETTAZIONE DELLA BASE DI DATI Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA RICAPITOLANDO… FASI E STEP DEL PROGETTO 1. ANALISI E PROGETTAZIONE CONCETTUALE 2. PROGETTAZIONE LOGICA DELLA BASE DI DATI 3. PROGETTAZIONE FISICA ED IMPLEMENTAZIONE DELLA BASE DI DATI Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA STEP 1: PROGETTAZIONE CONCETTUALE 1.Analisi e progettazione concettuale 1.1 Raccolta e Analisi dei requisiti 1.2 Progettazione dello schema Entità­Relazione (E­R) 1.3 Specifica delle funzioni per operare sui dati 1.4 Stima del carico applicativo del sistema informatico Il prodotto è lo Schema Concettuale. Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA STEP 2: PROGETTAZIONE LOGICA 2. Progettazione logica della base di dati 2.1 Ristrutturazione dello schema E­R in uno schema E­R semplificato orientato al modello relazionale 2.2 Traduzione dello schema E­R ristrutturato in schema relazionale. Il prodotto è lo Schema Logico. Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA 3. STEP 3: PROGETTAZIONE FISICA Progettazione fisica ed implementazione della base di dati 3.1 Realizzazione della base di dati sul DBMS relazionale open­source MySQL 5.0 3.2 Popolamento delle tabelle 3.3 Realizzazione delle interrogazioni per operare sui Il prodotto è lo Schema Fisico. Corso di Basi di Dati Relazionali – Progettazione Logica dati UNIVERSITA' DELLA CALABRIA MODELLI DI DATI I prodotti della varie fasi della progettazione sono schemi basati su specifici modelli di dati: dati Schema concettuale modello concettuale Schema logico modello logico Schema fisico modello logico + parametri fisici Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA MODELLI DEI DATI ADOTTATI – Schema concettuale: • Modello Entità­Relazione (E­R) – Schema logico: • Modello Relazionale • DDL: SQL­standard – Schema fisico: • Modello relazionale + strutture fisiche • DDL: SQL supportato dal DBMS scelto (MySQL 5.0) Corso di Basi di Dati Relazionali – Progettazione Logica PROGETTAZIONE LOGICA OBIETTIVO: “Tradurre" lo schema concettuale in uno schema logico che rappresenti gli stessi dati in maniera corretta ed efficiente UNIVERSITA' DELLA CALABRIA PROGETTAZIONE LOGICA Obiettivo: Rappresentare i dati della realtà d’interesse in termini dei costrutti logici di una classe di DBMS (quelli basati su un determinato modello logico) • Input: Schema concettuale (output della progettazione concettuale) Modello logico DBMS scelto (conforme al modello logico scelto) Previsioni del carico applicativo (per ottimizzare la base di dati rispetto ad esso) • Output: Schema logico + documentazione associata • Qualità: Correttezza e completezza della rappresentazione Efficienza della manipolazione dei dati Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA PROGETTAZIONE LOGICA Non si tratta di una pura e semplice traduzione: alcuni aspetti non sono direttamente rappresentabili è necessario considerare le prestazioni Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA Progettazione logica Carico applicativo Schema E-R Ristrutturazione dello schema E-R Schema E-R ristrutturato Modello logico Traduzione nel modello logico Schema logico Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA RISTRUTTURAZIONE SCHEMA E-R Motivazioni: semplificare la traduzione "ottimizzare" le prestazioni Osservazione: uno schema E­R ristrutturato non è (più) uno schema concettuale nel senso stretto del termine Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA Analisi delle prestazioni Consideriamo “indicatori” dei parametri che regolano le prestazioni: Spazio (occupazione di memoria): numero di occorrenze previste Tempo (costo di una operazione): numero di occorrenze (di entità e relationship) visitate durante un’operazione Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA Analisi delle prestazioni Per lo studio di questi parametri servono le seguenti informazioni: Volume dei dati: numero di occorrenze di ogni entità e associazione dimensioni di ciascun attributo (di entità e associazione) Caratteristiche delle operazioni: Tipo di operazione (interattiva o batch) Frequenza (numero medio di esecuzione in un certo intervallo di tempo) Dati coinvolti (entità e/o associazioni) Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA ATTIVITÀ DELLA RISTRUTTURAZIONE • Analisi delle ridondanze • Eliminazione delle generalizzazioni • Partizionamento/accorpamento di entità e relazioni • Scelta degli identificatori primari Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA Analisi delle ridondanze Presenza di un dato che può essere derivato (attraverso una serie di operazioni) da altri dati. Vantaggi: riduzione degli accessi necessari per calcolare il dato derivato Svantaggi: maggiore occupazione di memoria e necessità di effettuare operazioni aggiuntive per tenere aggiornato il dato derivato. Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA Analisi delle ridondanze La decisione se mantenere o eliminare un dato ridondante va presa confrontando: il costo di esecuzione delle operazioni che coinvolgono il dato ridondante relativa occupazione di memoria nei casi di: presenza della ridondanza assenza della ridondanza Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA FORME DI RIDONDANZA Attributi derivabili: da altri attributi della stessa entità (o associazione) da attributi di altre entità (o associazione) attraverso il conteggio di occorrenze Relazioni derivabili dalla composizione di altre relazioni in presenza di cicli Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA Esempio Numero abitanti O2 PERSONA (1,1) Residenza (1,n) CITTA’ Città O1 Operazione O1: memorizza una nuova persona con relativa città di residenza Operazione O2: stampa tutti i dati di una città (incluso il numero di abitanti) Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA ANALISI DELLE PRESTAZIONI La valutazione si fa sulla base delle: Tavola dei Volumi Tavola delle Operazioni Tavola degli accessi In presenza di ridondanza In assenza di ridondanza (sulla base di uno schema di navigazione) Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA TAVOLA DEI VOLUMI Concetto Tipo Volume Schema E-R (Entità/Relazione) Città Persona Residenza E E R Corso di Basi di Dati Relazionali – Progettazione Logica 200 1000000 1000000 UNIVERSITA' DELLA CALABRIA TAVOLA DELLE OPERAZIONI Operazione Op. 1 Op. 2 Tipo Frequenza (Interattiva/Batch) I 500 al giorno I 2 al giorno Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA TAVOLE DEGLI ACCESSI IN PRESENZA DI RIDONDANZA Costo Operazione 1 (in presenza di ridondanza): Concetto Costrutto Accesso Tipo Persona E 1 S Città E 1 L Residenza R 1 S Città E 1 S Un accesso in scrittura alla entità PERSONA Un accesso in lettura per cercare la CITTA’ di interesse Un accesso in scrittura all’associazione RESIDENZA Un accesso in scrittura per incrementare il numero di abitanti in CITTA’ Il tutto ripetuto per 500 volte al giorno per un totale di 3 x 500 = 1500 accessi in scrittura 1 x 500 = 500 accessi in lettura Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA TAVOLE DEGLI ACCESSI IN PRESENZA DI RIDONDANZA Costo Operazione 1 (in assenza di ridondanza): Concetto Costrutto Accesso Tipo Città E 1 L Il costo è trascurabile in quanto richiede un solo accesso in lettura all’entità CITTA’ da ripetere 2 volte al giorno. Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA ESEMPIO DI VALUTAZIONE DI COSTO In presenza di ridondanza: supponendo che un accesso in scrittura abbia costo doppio rispetto all’accesso in lettura: in totale 1500 x 2 + 500 = 3500 accessi al giorno Corso di Basi di Dati Relazionali – Progettazione Logica Tavole degli accessi in assenza di ridondanza UNIVERSITA' DELLA CALABRIA Costo Operazione 1 (in assenza di ridondanza): Concetto Costrutto Accessi Tipo Persona Città Residenza E E R 1 1 1 S L S Un accesso in scrittura alla entità PERSONA Un accesso in lettura per trovare la Città di interesse Un accesso in scrittura all’associazione RESIDENZA Il tutto ripetuto per 500 volte al giorno per un totale di 2 x 500 = 1000 accessi in scrittura 1 x 500 = 500 accessi in lettura Corso di Basi di Dati Relazionali – Progettazione Logica Tavole degli accessi in assenza di ridondanza UNIVERSITA' DELLA CALABRIA Costo Operazione 2 (in assenza di ridondanza): Concetto Costrutto Accessi Tipo Città Residenza E R 1 5000 L L Un accesso in lettura all’entità CITTA’ (trascurabile), 5000 accessi in lettura all’associazione RESIDENZA in media (numero di persone/numero di città) per calcolare il numero di abitanti di questa città, Il tutto ripetuto per 2 volte al giorno per un totale di 5000 x 2 = 10000 accessi in lettura al giorno. Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA ESEMPIO DI VALUTAZIONE DI COSTO In assenza di ridondanza: Supponendo che un accesso in scrittura abbia costo doppio rispetto all’accesso in lettura Abbiamo in totale (1000 x 2 + 500) + 10000 = 12500 accessi al giorno in caso di assenza di ridondanza. Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA ESEMPIO DI VALUTAZIONE DI COSTO Riassumendo: presenza di ridondanza: 3500 accessi al giorno assenza di ridondanza: 12500 accessi al giorno Conclusioni: Eliminare il dato ridondante comporterebbe: 9000 accessi giornalieri in più rispetto al caso di dato ridondante presente un risparmio di un solo Kbyte Allora conviene in questo caso mantenere il dato ridondante Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA ATTIVITÀ DELLA RISTRUTTURAZIONE Analisi delle ridondanze Eliminazione delle generalizzazioni Partizionamento/accorpamento di entità e relazioni Scelta degli identificatori primari Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA ELIMINAZIONE DELLE GERARCHIE il modello relazionale non può rappresentare direttamente le generalizzazioni entità e relazioni sono invece direttamente rappresentabili si eliminano perciò le gerarchie, sostituendole con entità e relazioni Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA Eliminazione delle gerarchie 1. accorpamento delle figlie della generalizzazione nell’entità padre 2. accorpamento dell’entità padre della generalizzazione nelle entità figlie 3. sostituzione della generalizzazione con relazioni Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA A01 1. Accorpamento dell’entità figlie nell’entità padre A02 E0 R1 E1 E2 A11 A21 Corso di Basi di Dati Relazionali – Progettazione Logica E3 R2 E4 1.Accorpamento delle entità figlie nell’entità padre UNIVERSITA' DELLA CALABRIA A01 A02 (0,1) A11 A21 E0 E3 R1 (0,1) TIPO (0,..) R2 E4 Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA 2. Accorpamento dell’entità padre nelle entità figlie A01 A02 E0 R1 E1 E2 A11 A21 Corso di Basi di Dati Relazionali – Progettazione Logica E3 R2 E4 UNIVERSITA' DELLA CALABRIA 2. Accorpamento dell’entità padre nelle entità figlie R11 R12 E3 E1 E2 R2 A01 A11 A02 A01 A21 A02 Corso di Basi di Dati Relazionali – Progettazione Logica E4 UNIVERSITA' DELLA CALABRIA 3. Sostituzione della generalizzazione con relazioni A01 A02 E0 R1 E1 E2 A11 A21 Corso Basi di Dati Relazionali – Laboratorio Gruppo B Corsodidi Basi di Dati Relazionali – Progettazione Logica E3 R2 E4 UNIVERSITA' DELLA CALABRIA 3. Sostituzione della generalizzazione con relazioni A01 A02 E0 (0,1) R1 E3 (0,1) RG1 RG2 (1,1) (1,1) E1 E2 A11 A21 Corso di Basi di Dati Relazionali – Progettazione Logica R2 E4 UNIVERSITA' DELLA CALABRIA Strategia per la eliminazione delle generalizzazioni La scelta fra le alternative si può fare con metodo simile a quello visto per l'analisi delle ridondanze (però non basato solo sul numero degli accessi) • E’ possibile seguire alcune semplici regole generali: 1. conviene se gli accessi al padre e alle figlie sono contestuali 2. conviene se gli accessi alle figlie sono distinti 3. conviene se gli accessi alle entità figlie sono separati dagli accessi al padre • sono anche possibili soluzioni “ibride”, soprattutto in gerarchie a più livelli Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA Soluzione Ibrida A01 A02 E0 R1 E1 E2 A11 A21 Corso di Basi di Dati Relazionali – Progettazione Logica E3 R2 E4 UNIVERSITA' DELLA CALABRIA Soluzione ibrida A01 A02 E0 R1 E3 E2 R2 A21 E4 Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA Soluzione ibrida A01 A02 (0,1) A11 E0 R1 E3 (0,1) TIPO RG2 (1,1) E2 A21 Corso di Basi di Dati Relazionali – Progettazione Logica R2 E4 UNIVERSITA' DELLA CALABRIA ATTIVITÀ DELLA RISTRUTTURAZIONE • • • • Analisi delle ridondanze Eliminazione delle generalizzazioni Partizionamento/accorpamento di entità e relazioni Scelta degli identificatori primari Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA PARTIZIONAMENTO/ACCORPAMENTO DI ENTITÀ E RELAZIONI Ristrutturazioni effettuate per rendere più efficienti le operazioni Gli accessi si riducono: separando attributi di un concetto che vengono acceduti separatamente raggruppando attributi di concetti diversi acceduti insieme Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA ESEMPI DI PARTIZIONAMENTO/ ACCORPAMENTO partizionamento verticale di entità partizionamento orizzontale di entità/relationship eliminazione di attributi multivalore accorpamento di entità/ relationship Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA ESEMPIO DI PARTIZIONAMENTO/ACCORPAMENTO Se una delle due entità non è coinvolta in alcuna relazione con altre entità dello schema E/R, le due entità possono essere accorpate Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA ATTIVITÀ DELLA RISTRUTTURAZIONE • • • • Analisi delle ridondanze Eliminazione delle generalizzazioni Partizionamento/accorpamento di entità e relazioni Scelta degli identificatori primari Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA SCELTA DEGLI IDENTIFICATORI PRINCIPALI Operazione indispensabile per la traduzione nel modello relazionale Criteri assenza di opzionalità Semplicità (1 o pochi attributi) utilizzo nelle operazioni più frequenti o importanti Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA SCELTA DEGLI IDENTIFICATORI PRINCIPALI Se nessuno degli identificatori soddisfa i requisiti visti? Si introducono nuovi attributi (codici) contenenti valori speciali generati appositamente per questo scopo Corso di Basi di Dati Relazionali – Progettazione Logica UNIVERSITA' DELLA CALABRIA TRADUZIONE VERSO IL MODELLO RELAZIONALE Idea di base: • le entità diventano relazioni sugli stessi attributi • le associazioni (ovvero le relazioni E­R) diventano relazioni sugli identificatori delle entità coinvolte più gli attributi propri Corso di Basi di Dati Relazionali – Progettazione Logica