Basi di Dati Relazionali -...

UNIVERSITA' DELLA CALABRIA

Basi di Dati Relazionali

Corso di Laurea in Informatica

a.a. 2009-2010

Laboratorio

Corso di Basi di Dati Relazionali – Progettazione Logica

LaboratorioIng. G. LaboccettaIng. G. Laboccetta

Dott.ssa.Dott.ssa. V. PolicicchioV. PolicicchioCoadiutore: Dott.ssa D. Nicotera Coadiutore: Dott.ssa D. Nicotera


PROGETTAZIONE DI UNA BASE DI DATI

Schema concettuale

Progettazione concettuale

Raccolta e Analisi dei requisiti


Progettazione logica

Progettazione fisica

Schema logico

Schema fisico


PROGETTAZIONE DELLA BASE DI DATI



RICAPITOLANDO…

FASI E STEP DEL PROGETTO

1. Analisi dei requisiti e Progettazione

Concettuale

2. Progettazione logica della base di dati


3. Progettazione fisica ed implementazione

della base di dati


STEP 1:

PROGETTAZIONE CONCETTUALE

1.Analisi e progettazione concettuale

1.1 Raccolta e Analisi dei requisiti

1.2 Progettazione dello schema EntitàRelazione (ER)

1.3 Specifica delle funzioni per operare sui dati


1.3 Specifica delle funzioni per operare sui dati

1.4 Stima del carico applicativo del sistema informatico

� Il prodotto è lo Schema Concettuale.


STEP 2:

PROGETTAZIONE LOGICA

2. Progettazione logica della base di dati

2.1 Ristrutturazione dello schema ER in uno schema ER

semplificato orientato al modello relazionale


2.2 Traduzione dello schema ER ristrutturato in schema

relazionale.

� Il prodotto è lo Schema Logico.


STEP 3:

PROGETTAZIONE FISICA

3. Progettazione fisica ed implementazione della base di dati

3.1 Realizzazione della base di dati sul DBMS

relazionale opensource MySQL 5.0

3.2 Popolamento delle tabelle


3.2 Popolamento delle tabelle

3.3 Realizzazione delle interrogazioni per operare sui dati

� Il prodotto è lo Schema Fisico.


MODELLI DI DATI

I prodotti della varie fasi della progettazione sono schemi basati su specifici

modelli di datimodelli di dati:

� Schema concettuale � modello concettuale

� Schema logico � modello logico


� Schema logico � modello logico

� Schema fisico � modello logico + parametri fisici


MODELLI DEI DATI ADOTTATI

– Schema concettuale:

• Modello EntitàRelazione (ER)

– Schema logico:


• Modello Relazionale Modello Relazionale

• DDL: SQLstandard

– Schema fisico:

• Modello relazionale + strutture fisiche

• DDL: SQL supportato dal DBMS scelto (MySQL 5.0)

PROGETTAZIONE

LOGICA

OBIETTIVO:

“Tradurre" lo schema concettuale in uno schema

logico che rappresenti gli stessi dati in maniera

corretta ed efficiente


PROGETTAZIONE LOGICAObiettivo: 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)


� DBMS scelto (conforme al modello logico scelto)

� Previsioni del carico applicativo (per ottimizzare la base di dati rispetto ad esso)

• Output: Schema logico + Schema logico + documentazione associata

• Qualità:

� Correttezza e completezza della rappresentazione

� Efficienza della manipolazione dei dati


Non si tratta di una pura e semplice traduzione:

� alcuni aspetti non sono direttamente rappresentabili

PROGETTAZIONE LOGICA


� è necessario considerare le prestazioni


Ristrutturazione dello schema E-R

Schema E-RCaricoapplicativo

Progettazione logicaProgettazione logicaProgettazione logicaProgettazione logica


Traduzione nelmodello logico

Schema E-Rristrutturato

Modellologico

Schema logico


RISTRUTTURAZIONE SCHEMA E-R

� Motivazioni:

� semplificare la traduzione

� "ottimizzare" le prestazioni


� Osservazione:

� uno schema ER ristrutturato non è (più) uno schema

concettuale nel senso stretto del termine


Consideriamo “indicatori” dei parametri che regolano le prestazioni:

� Spazio (occupazione di memoria): numero di occorrenze previste

Analisi delle prestazioniAnalisi delle prestazioniAnalisi delle prestazioniAnalisi delle prestazioni


previste

� Tempo (costo di una operazione): numero di occorrenze (di entità e relationship) visitate durante un’operazione


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)

Analisi delle prestazioniAnalisi delle prestazioniAnalisi delle prestazioniAnalisi delle prestazioni


� 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)


ATTIVITÀ DELLA RISTRUTTURAZIONE

• Analisi delle ridondanze

• Eliminazione delle generalizzazioni

Partizionamento/accorpamento di entità e relazioni


• Partizionamento/accorpamento di entità e relazioni

• Scelta degli identificatori primari


� 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

Analisi delle ridondanzeAnalisi delle ridondanzeAnalisi delle ridondanzeAnalisi delle ridondanze


derivato

� Svantaggi: maggiore occupazione di memoria e necessità di

effettuare operazioni aggiuntive per tenere aggiornato il dato

derivato.


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

Analisi delle ridondanzeAnalisi delle ridondanzeAnalisi delle ridondanzeAnalisi delle ridondanze


� presenza della ridondanza

� assenza della ridondanza


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


� attraverso il conteggio di occorrenze

� Relazioni derivabili dalla composizione di altre relazioni

in presenza di cicli


EsempioEsempioEsempioEsempio

CITTA’PERSONA Residenza Città(1,1) (1,n)

Numero abitanti


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)


TAVOLA DEI VOLUMI

Concetto Schema E-R

Tipo (Entità/Relazione)

Volume

Città E 200

Persona E 1000000


Persona E 1000000

Residenza R 1000000


TAVOLA DELLE OPERAZIONI

Operazione Tipo (Interattiva/Batch)

Frequenza

Op. 1 I 500 al giorno


Op. 1 I 500 al giorno

Op. 2 I 2 al giorno

UNIVERSITA' DELLA CALABRIA TAVOLE DEGLI ACCESSI

IN PRESENZAPRESENZA DI RIDONDANZA

Concetto Costrutto Accesso Tipo

Persona E 1 S

Città E 1 L

Residenza R 1 S

Città E 1 S

� Operazione 1: Tavola degli accessi (in presenzapresenza di ridondanza):


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

� Costo:

UNIVERSITA' DELLA CALABRIA TAVOLE DEGLI ACCESSI

IN PRESENZAPRESENZA DI RIDONDANZA

Concetto Costrutto Accesso Tipo

Città E 1 L

� Operazione 2 : Tavola degli accessi (in presenza presenza di ridondanza)


� Il costo è trascurabile in quanto richiede un solo accesso

in lettura all’entità CITTA’ da ripetere 2 volte al giorno.


ESEMPIO DI VALUTAZIONE DI COSTO

supponendo che un accesso in scrittura abbia costo doppio

rispetto all’accesso in lettura:

� in totale 1500 x 2 + 500 = 3500 accessi al giorno

In presenzapresenza di ridondanza:


� in totale 1500 x 2 + 500 = 3500 accessi al giorno


Concetto Costrutto Accessi Tipo

Persona E 1 S

Residenza R 1 S

� Operazione 1: Tavola degli accessi (in assenza assenza di ridondanza):

Tavole degli accessi Tavole degli accessi Tavole degli accessi Tavole degli accessi in in in in assenzaassenzaassenzaassenzaassenzaassenzaassenzaassenza di ridondanzadi ridondanzadi ridondanzadi ridondanza


� Un accesso in scrittura alla entità PERSONA

� Un accesso in scrittura all’associazione RESIDENZA


� 2 x 500 = 1000 accessi in scrittura

� Costo:



Città E 1 L

Residenza R 5000 L

Tavole degli accessi Tavole degli accessi Tavole degli accessi Tavole degli accessi in in in in assenzaassenzaassenzaassenzaassenzaassenzaassenzaassenza di ridondanzadi ridondanzadi ridondanzadi ridondanza

� Operazione 2: Tavola degli accessi (in assenza assenza di

ridondanza):


� Costo:� 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à,


� 5000 x 2 = 10000 accessi in lettura al giorno.



Supponendo che un accesso in scrittura abbia costo doppio rispetto all’accesso in lettura, abbiamo in totale

(1000 x 2) + 10000 = 12000 accessi al giorno

in caso di assenza di ridondanza.

In assenzaassenza di ridondanza:


in caso di assenza di ridondanza.



Riassumendo:

� presenza di ridondanza: 3500 accessi al giorno

� assenza di ridondanza: 12000 accessi al giorno

Conclusioni:

Eliminare il dato ridondante comporterebbe:


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


ESEMPIO 2- VALUTAZIONE DI COSTO



TAVOLA DELLE OPERAZIONI

Operazione 1:

Inserire una nuova vendita relativa a una fidelity card.

Operazione 2 :

Stampare i dati relativi a un supermercato (compreso il numero di

Corso di Basi di Dati Relazionali – Progettazione LogicaCorso di Basi di Dati Relazionali – Laboratorio Gruppo B

OPERAZIONE

TIPO

FREQUENZA

Operazione 1 I 1000 al giorno

Operazione 2 I 2 al giorno

Stampare i dati relativi a un supermercato (compreso il numero di

clienti e il totale dei punti delle fidelity card)


TAVOLA DEGLI ACCESSI IN PRESENZA DI

RIDONDANZA

Operazione O1


Prodotto Entità 10 L

Fidelity card Entità 1 L

Scontrino Entità 1 S


Vendita Relazione 10 S

Supermercato Entità 1 S

Corso di Basi di Dati Relazionali – Laboratorio Gruppo B

Operazione O2


Supermercato Entità 1 L


PRESENZADI RIDONDANZA

� Costi:

� Operazione 1: 11 operazioni di accesso in lettura e 12 in

scrittura. Contando le operazioni di scrittura doppi avremo 35

accessi. Per cui su 1000 operazioni di vendita al giorno avremo

35000 accessi.


� Operazione 2: 2 X 1 accessi in lettura sulla entità

SUPERMERCATO (Trascurabile)

� Totale: Circa 35000 accessi al giorno a fronte di 8 Byte aggiuntivi

per memorizzare gli attributo ridondante “Somma punti” e

“Media punti” nella entità SUPERMERCATO

Corso di Basi di Dati Relazionali – Laboratorio


TAVOLA DEGLI ACCESSI IN ASSENZA DI

RIDONDANZA

Operazione O1


Prodotto Entità 10 L


Scontrino Entità 1 S





Operazione O2



Supermercato Entità 1 L


ASSENZADI RIDONDANZA

� Costi:

� Operazione 1: 11 operazioni di accesso il lettura e 11 in scrittura. Contando le

operazioni di scrittura doppi avremo 33 accessi. Per cui su 1000 operazioni di

vendita al giorno avremo 33000 accessi.

� Operazione 2: 1 accesso in lettura sulla entità SUPERMERCATO e circa 5000

accessi sulla entità FIDELITY CARD (supponendo tale la media di clienti per


accessi sulla entità FIDELITY CARD (supponendo tale la media di clienti per

supermercato).

� Questa operazione viene effettuata due volte al giorno su un totale di 10.000

accessi.

� Totale: Circa 33000 + 10000 = 43000 accessi al giorno.

� Mantenendo la ridondanza avremo un risparmio di circa 8000 accessi al giorno.




• Analisi delle ridondanze• Eliminazione delle generalizzazioni

• Partizionamento/accorpamento di entità e relazioni


relazioni• Scelta degli identificatori primari


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


1. accorpamento delle figlie della generalizzazione nell’entità

padre

2. accorpamento dell’entità padre della generalizzazione nelle

entità figlie

Eliminazione delle gerarchieEliminazione delle gerarchieEliminazione delle gerarchieEliminazione delle gerarchie


entità figlie

3. sostituzione della generalizzazione con relazioni


E0 R1

A01 A02

E3

1.1.1.1.Accorpamento dell’entità figlie Accorpamento dell’entità figlie Accorpamento dell’entità figlie Accorpamento dell’entità figlie nell’entità padrenell’entità padrenell’entità padrenell’entità padre


R2

E4

E2E1

A11 A21


A11

A21

TIPO

(0,1)

(0,1)

E0

A01 A02

R1 E3

1.Accorpamento delle entità figlie 1.Accorpamento delle entità figlie 1.Accorpamento delle entità figlie 1.Accorpamento delle entità figlie nell’entità padrenell’entità padrenell’entità padrenell’entità padre


TIPO

(0,..)

R2

E4


E0 R1

A01 A02

E3

2. Accorpamento dell’entità padre 2. Accorpamento dell’entità padre 2. Accorpamento dell’entità padre 2. Accorpamento dell’entità padre nelle entità figlienelle entità figlienelle entità figlienelle entità figlie


R2

E4

E2E1

A11 A21


E3R12

R11

2. Accorpamento dell’entità 2. Accorpamento dell’entità 2. Accorpamento dell’entità 2. Accorpamento dell’entità padre nelle entità figlie padre nelle entità figlie padre nelle entità figlie padre nelle entità figlie


R2

E4

E2E1

A11 A21A01 A02 A01 A02


E0 R1

A01 A02

E3

3. Sostituzione della generalizzazione 3. Sostituzione della generalizzazione 3. Sostituzione della generalizzazione 3. Sostituzione della generalizzazione con relazionicon relazionicon relazionicon relazioni


R2

E4

E2E1

A11 A21



(0,1)(0,1)

E0

A01 A02

R1 E3

3. Sostituzione della generalizzazione 3. Sostituzione della generalizzazione 3. Sostituzione della generalizzazione 3. Sostituzione della generalizzazione con relazionicon relazionicon relazionicon relazioni


RG2RG1

(1,1)

(0,1)

(1,1)

(0,1)

E2E1 R2

E4A11 A21


� 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:

Strategia per la eliminazione delle Strategia per la eliminazione delle Strategia per la eliminazione delle Strategia per la eliminazione delle generalizzazionigeneralizzazionigeneralizzazionigeneralizzazioni


• 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


E0 R1

A01 A02

E3

Soluzione IbridaSoluzione IbridaSoluzione IbridaSoluzione Ibrida


R2

E4

E2E1

A11 A21


SoluzioneSoluzioneSoluzioneSoluzione ibridaibridaibridaibrida

E0

A01 A02

R1 E3


E2 R2

E4A21


E0

A01 A02

R1 E3

(0,1)

A11

TIPO

(0,1)

SoluzioneSoluzioneSoluzioneSoluzione ibridaibridaibridaibrida


E2 R2

E4A21

RG2

(1,1)

(0,1)TIPO



• Analisi delle ridondanze• Eliminazione delle generalizzazioni• Partizionamento/accorpamento di entità e relazioni


relazioni

• Scelta degli identificatori primari

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


� separando attributi di un concetto che vengono

acceduti separatamente

� raggruppando attributi di concetti diversi acceduti

insieme

UNIVERSITA' DELLA CALABRIA ESEMPI DI PARTIZIONAMENTO/ACCORPAMENTO

� partizionamento verticale di entità

� partizionamento orizzontale di

entità/relationship


entità/relationship

� eliminazione di attributi multivalore

� accorpamento di entità/ relationship



• Analisi delle ridondanze• Eliminazione delle generalizzazioni• Partizionamento/accorpamento di entità e

relazioni


relazioni• Scelta degli identificatori primari


SCELTA DEGLI IDENTIFICATORI PRINCIPALI

� Operazione indispensabile per la traduzione nel modello

relazionale

� Criteri


� Criteri

� assenza di opzionalità

� Semplicità (1 o pochi attributi)

� utilizzo nelle operazioni più frequenti o importanti


SCELTA DEGLI IDENTIFICATORI PRINCIPALI

� Se nessuno degli identificatori soddisfa i requisiti visti?

Si introducono nuovi attributi (codici)


Si introducono nuovi attributi (codici)

contenenti valori speciali generati appositamente per questo scopo


TRADUZIONE VERSO IL MODELLO

RELAZIONALE

Idea di base:

• le entità diventano relazioni sugli stessi attributi

• le associazioni (ovvero le relazioni ER) diventano


• le associazioni (ovvero le relazioni ER) diventano

relazioni sugli identificatori delle entità coinvolte più gli

attributi propri

Basi di Dati Relazionali -...

Documents

Transcript of Basi di Dati Relazionali -...