UNIVERSITA' DELLA CALABRIA
Corso di Laurea in Informatica
Basi di Dati Relazionali
a.a. 2009-2010
Laboratorio
Ing. G. Laboccetta
Dott.ssa. V. Policicchio
Coadiutore: Dott.ssa D. Nicotera
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 dei requisiti 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
PERSONA
(1,1)
Residenza
(1,n)
CITTA’
Città
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
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
Operazione 1: Tavola degli accessi (in presenza di ridondanza):
Concetto
Costrutto
Accesso
Tipo
Persona
E
1
S
Città
E
1
L
Residenza
R
1
S
Città
E
1
S
Costo:
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
Operazione 2 : Tavola degli accessi (in presenza 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
Operazione 1: Tavola degli accessi (in assenza di ridondanza):
Concetto
Costrutto
Accessi
Tipo
Persona
E
1
S
Residenza
R
1
S
Costo:
Un accesso in scrittura alla entità PERSONA
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
Corso di Basi di Dati Relazionali – Progettazione Logica
Tavole degli accessi
in assenza di ridondanza
UNIVERSITA' DELLA CALABRIA
Operazione 2: Tavola degli accessi (in assenza di
ridondanza):
Concetto
Costrutto
Accessi
Tipo
Città
Residenza
E
R
1
5000
L
L
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à,
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) + 10000 = 12000 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: 12000 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
ESEMPIO 2- VALUTAZIONE DI COSTO
Corso di Basi di Dati Relazionali – Progettazione Logica
UNIVERSITA' DELLA CALABRIA
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
clienti e il totale dei punti delle fidelity card)
OPERAZIONE
TIPO
FREQUENZA
Operazione 1
Operazione 2
I
I
1000 al giorno
2 al giorno
Corso
Basi
di Dati
Relazionali
– Laboratorio
Gruppo B
Corsodidi
Basi
di Dati
Relazionali
– Progettazione
Logica
UNIVERSITA' DELLA CALABRIA
TAVOLA DEGLI ACCESSI IN PRESENZA DI
RIDONDANZA
Operazione O1
Concetto
Prodotto
Fidelity card
Scontrino
Vendita
Supermercato
Costrutto Accessi Tipo
Entità
10
L
Entità
1
L
Entità
1
S
Relazione
10
S
Entità
1
S
Operazione O2
Concetto
Supermercato
Costrutto Accessi Tipo
Entità
1
L
Corso
Basi
di Dati
Relazionali
– Laboratorio
Gruppo B
Corsodidi
Basi
di Dati
Relazionali
– Progettazione
Logica
UNIVERSITA' DELLA CALABRIA
PRESENZA DI 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
Basi
di Dati
Relazionali
– Laboratorio
Corsodidi
Basi
di Dati
Relazionali
– Progettazione
Logica
UNIVERSITA' DELLA CALABRIA
TAVOLA DEGLI ACCESSI IN ASSENZA DI
RIDONDANZA
Operazione O1
Concetto
Prodotto
Fidelity card
Scontrino
Vendita
Costrutto Accessi Tipo
Entità
10
L
Entità
1
L
Entità
1
S
Relazione
10
S
Operazione O2
Concetto
Fidelity card
Supermercato
Costrutto Accessi Tipo
Entità
5000
L
Entità
1
L
Corso
Basi
di Dati
Relazionali
– Laboratorio
Gruppo B
Corsodidi
Basi
di Dati
Relazionali
– Progettazione
Logica
UNIVERSITA' DELLA CALABRIA
ASSENZA DI 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
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.
Corso
Basi
di Dati
Relazionali
– Laboratorio
Gruppo B
Corsodidi
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
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
