Progettazione Logica 1 Progettazione logica I.Analisi delle prestazioni su schemi E-R...

transcript

Progettazione Logica 1

Progettazione logica

I. Analisi delle prestazioni su schemi E-R

II. Ristrutturazione di schemi E-R

III. Traduzione verso il modello relazionale

requisiti delSistema informativo

progettazione concettuale

progettazione logica

progettazione fisica

SCHEMA LOGICO

SCHEMA CONCETTUALE

SCHEMA FISICO

Obiettivo dellaprogettazione logica

“Tradurre" lo schema concettuale (schema E-R) in uno schema logico (modello relazionale) che rappresenti gli stessi dati in maniera corretta ed efficiente

Dati di ingresso e uscita

Ingresso: schema concettuale informazioni sul carico applicativo modello logico

Uscita: schema logico documentazione associata

Lo schema E-R va ristrutturato: Semplificazione della traduzione: alcuni

aspetti dello schema concettuale non sono direttamente rappresentabili nel modello logico

Ottimizzazione del progetto: è necessario considerare le prestazioni

Non si tratta di una pura e semplice traduzione

Schema E-Rristrutturato

Modellologico

Traduzione nelmodello logico

Schema logico

Fase 2

Ristrutturazione dello schema E-R

Schema E-RCarico

applicativo

Fase 1

Fase 1: Ristrutturazione schema E-R

E' indipendente dal modello logico scelto e si basa sui due principi di: semplificazione ottimizzazione

Osservazione:

uno schema E-R ristrutturato non è (più) uno schema concettuale nel senso stretto del termine

Le prestazioni non sono valutabili con precisione su uno schema concettuale, dato che dipendono anche da parametri fisici:

- dal sistema per la gestione di basi di dati utilizzato

- dalle strutture dati utilizzate per memorizzare le tabelle

- dai vari modi in cui si possono realizzare le operazioni in SQL

Per ottimizzare il risultato abbiamo bisogno di analizzare le prestazioni a questo livello

Analisi delle prestazioni su schemi E-R

Consideriamo degli "indici di prestazione" dei parametri che regolano le prestazioni di uno schema E-R: occupazione di memoria (spazio): spazio di memoria

necessario per memorizzare i dati descritti dallo schema dipende dal numero di occorrenze di entità e relazioni previste

costo di una operazione (tempo): numero di occorrenze (di entità e relationship) visitate per rispondere a un’operazione sulla base di dati

Informazioni necessarie per l’analisi delle prestazioni

Per studiare questi parametri abbiamo bisogno di conoscere, oltre allo schema, le seguenti informazioni volume dei dati:

numero di occorrenze di ogni entità dello schemanumero di occorrenze di ogni relationship dello schemadimensioni di ciascun attributo (di entità o relationship)

caratteristiche delle operazioni: tipo di operazione (interattiva o batch) frequenza dell’esecuzione dati coinvolti (entità e/o relationship)

CittàIndirizzo

Telefono

Dipartimento

Composizione

Direzione

Afferenza

Impiegato

Progetto

Partecipazione

Cognome

Budget

(1,1)(0,1)

(1,N)(0,1)

Codice

Schema di riferimento

Tavola dei volumi

Concetto Tipo Volume Sede E 10

Dipartimento E 80 Impiegato E 2000 Progetto E 500

Composizione R 80 Afferenza R 1900 Direzione R 80

Partecipazione R 6000

vi sono riportati i concetti dello schema col volume previsto a regime

Occorrenze di una relationship

Il numero delle occorrenze di una relationship dipende da: Numero di occorrenze delle entità coinvolte nella relationship Numero medio di partecipazioni di una occorrenza di entità

all’occorrenza della relationship Esempio:

Volume Composizione = Volume Dipartimento Volume Afferenza Volume Impiegato Volume Direzione = Volume Dipartimento Volume Partecipazione = 3 Volume Impiegato

Esempio di valutazione di costo delle operazioni

Operazione:1. assegna un impiegato a un progetto2. trova tutti i dati di un impiegato, del dipartimento nel

quale lavora e dei progetti ai quali partecipa3. trova i dati di tutti gli impiegati di un certo

dipartimento4. per ogni sede trova i suoi dipartimenti con il cognome

del direttore e l’elenco degli impiegati del dipartimento

Descrizione delle operazioni

L’analisi delle operazioni principali richiede la codifica di: tipo dell’operazione : Interattiva (I) o Batch (B) frequenza: numero medio di esecuzioni in un certo

periodo di tempo schema di navigazione: frammento dello schema E/R

interessato dall’operazione sul quale viene disegnato (con delle frecce) il “cammino logico” da percorrere per accedere alle informazioni di interesse

Tavola delle operazioni

Operazione Tipo Frequenza Op.1 I 50 al giorno Op.2 I 100 al giorno Op.3 I 10 al giorno Op.4 B 2 a sett.

Regola 80-20: l’80% del carico è generato dal 20% delle operazioniDunque il carico si può valutare accuratamente basandoci sulle principali operazioni previste

Per ogni operazione significativa si costruisce uno schema di navigazione (frammento di di schema E-R interessato all’operazione)

Regola 80-20

Telefono

(1,N)(0,1)

Dipartimento

Afferenza

NomeBudget

Progetto

Partecipazione

Cognome

(0,N)Codice

Impiegato

schema di navigazione

Operazione 2: trova tutti i dati di un impiegato, del dipartimento nel quale lavora e dei progetti ai quali partecipa

Tavola degli accessi

Per ogni operazione significativa si costruisce una tavola degli accessi basata sullo schema di navigazione il campo costrutto specifica il tipo di concetto (entità o

associazione) nel campo accessi si conta il numero degli accessi

necessario per eseguire una volta l’operazione il campo tipo è riferito al tipo di operazione: le operazioni di

scrittura (S) sono più onerose di quelle di lettura (L)il peso degli accessi in scrittura è in genere considerato doppio di quello delle letture

Tavola degli accessi

Concetto Costrutto Accessi Tipo Impiegato Entità 1 L Afferenza Relazione 1 L

Dipartimento Entità 1 L Partecipazione Relazione 3 L

Progetto Entità 3 L

Un accesso può essere di tipo L (lettura) o S (scrittura)

Il numero delle istanze si ricava dalla tavola dei volumi mediante semplici operazioni

es: in media ogni impiegato partecipa a 6000/2000 = 3 progetti

Attività della ristrutturazione

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

relationship Scelta degli identificatori primari

1. Analisi delle ridondanze

Una ridondanza in uno schema E-R è una informazione significativa ma derivabile da altri dati

in questa fase si decide se eliminare le ridondanze eventualmente presenti o mantenerle

Ridondanze

Vantaggi semplificazione delle interrogazioni riduzione del numero degli accessi necessari per

calcolare il dato derivato

Svantaggi appesantimento degli aggiornamenti maggiore occupazione di spazio

Forme di ridondanza in uno schema E-R

attributi derivabili: da altri attributi della stessa entità (o

relazione) da attributi di altre entità (o relazioni)

relazioni derivabili dalla composizione di altre relazioni in presenza di cicli

Attributo derivabile

Fattura

Importo netto

Importo lordo

Attributo derivabile da altra entità

Importo totale

ComposizioneAcquisto Prodotto

Prezzo

(1,N) (1,N)

Importo totale è ottenibile con operazioni di aggregazione

Studente

Frequenza

Professore

Insegnamento

Docenza

Ridondanza dovuta a ciclo

Mantenere o meno la ridondanza?

La decisione di mantenere o eliminare una ridondanza va presa confrontando il costo di una esecuzione delle operazioni che coinvolgono il dato ridondante e l’occupazione di memoria, nei casi di presenza e assenza della ridondanza

Analisi di una ridondanza

ResidenzaPersona Città

Numero abitanti

(1,1) (1,N)

L’attributo numero abitanti è derivabile da una operazione di conteggio delle istanze di persona residenti in una città

Operazione 1: memorizza una nuova persona con la relativa città di residenza (500 volte al giorno)

Operazione 2: stampa tutti i dati di una città (incluso il numero di abitanti) (2 volte al giorno)

Concetto Tipo Volume Città E 200

Persona E 1000000 Residenza R 1000000

tavola dei volumi

Presenza di ridondanza

Concetto Costrutto Accessi Tipo Persona Entità 1 S

Residenza Relazione 1 S Città Entità 1 L Città Entità 1 S

Concetto Costrutto Accessi TipoCittà Entità 1 L

Operazione 1

Operazione 2

Assenza di ridondanza

Concetto Costrutto Accessi TipoPersona Entità 1 S

Residenza Relazione 1 S

Concetto Costrutto Accessi Tipo Città Entità 1 L

Residenza Relazione 5000 L

Operazione 1

Operazione 2

n.persone/n.città = media accessi per calcolare il numero di abitanti di una città

Presenza di ridondanza

Costi: Operazione 1: 1500 accessi in scrittura e 500

accessi in lettura al giorno Operazione 2: trascurabile.

Contiamo doppi gli accessi in scrittura Totale di 3500 accessi al giorno

Assenza di ridondanza

Costi: Operazione 1: 1000 accessi in scrittura Operazione 2: 10000 accessi in lettura al

giorno

Contiamo doppi gli accessi in scrittura Totale di 12000 accessi al giorno

Conviene dunque mantenere il dato ridondante

Ristrutturazione dello schema E-R

relazioni Scelta degli identificatori primari

2. 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

Tre possibilità

1. accorpamento delle figlie della generalizzazione nel genitore

2. accorpamento del genitore della generalizzazione nelle figlie

3. sostituzione della generalizzazione con relazioni

A01 A02

A11 A21

Schema generale

Accorpamento delle figlie della generalizzazione nel genitore

E1 e E2 sono eliminate e le loro proprietà vengono aggiunte al padre E0

A E0 viene aggiunto anche un nuovo attributo, TIPO, che serve a distinguere le occorrenze di E1 da quelle di E2

Gli attributi A11 e A21 possono assumere anche valori nulli su E0

(0,..)

A01 A02

Collasso verso l’alto

Studente

Laureando Diplomando

Matr.Nome

Tit.StageTit.Tesi

MatrRelatore

Denom.Azienda

(1,1) (1,1)

(1,n)(1,n)

Dom(Tipo)= {L,D,N}

Denom.

StudenteMatr.Nome

Tit.Stage (0,1)

Tit.Tesi (0,1)

MatrRelatore Azienda

(0,1) (0,1)

(1,n) (1,n)

Esempio

Osservazioni

Conveniente quando le operazioni sulla base di dati non fanno molta distinzione tra le occorrenze e tra gli attributi di E0, E1, E2

Vantaggi: numero minore di accessi Svantaggi: spreco di memoria per la presenza

di valori nulli

A01 A02

A11 A21

totale

Accorpamento del genitore della generalizzazione nelle figlie

E0 viene eliminato, i suoi attributi e identificatore vengono assegnati alle figlie

R11 e R12 sono le restrizioni di R1 alle occorrenze di E1 e E2

Nota Bene tale trasformazione è possibile solo se la generalizzazione è

totale, altrimenti le occorrenze di E0 - (E1 U E2) non sarebbero rappresentate

Conviene quando ci sono operazioni che si riferiscono solo a occorrenze di E1, o di E2 e fanno distinzioni tra tali entità

A11 A21

A01 A02 A01 A02

Collasso verso il basso

Esempio

Dipendente

Impiegato Operaio

CFNome

macchinecapacità

Dirigenten_sottoposti

SindacatoContribuisce0,1 1,n

1,n1,n

Dirige

Impiegato Operaio

macchinecapacitàDirigente

n_sottoposti

SindacatoContr_I

CF Nome

Contr_D

Contr_O

0,10,1

0,n Dir_DDir_ODir_I

CF NomeCF Nome

0,n 0,n

Quando conviene utilizzare questa alternativa? Quando ci sono operazioni che si riferiscono solo a

occorrenze di E1 oppure di E2, e fanno distinzioni tra le 2 entità

Gli attributi non assumono mai valori nulli

Sostituzione della generalizzazione

con relationship La generalizzazione si trasforma in due associazioni uno a

uno che legano l’entità padre E0 con E1 e E2 Si pongono dei vincoli per impedire che una occorrenza di E0

partecipi sia a E1 che a E2Nota bene

-conviene se gli accessi alle entità figlie sono separati dagli accessi al padre-è sempre possibile indipendentemente dalla copertura e conserva tutte le entità-le entità vengono mantenute e sono identificate esternamente

RG2RG1

A01 A02

E2E1 R2

E4A11 A21

Progetto

Prog_sw Prog_hw

CodDesc

N_schede

Mesiuomo

Comp_hw

Esempio

Progetto

Prog_sw Prog_hw

CodDesc

N_schedeMesiuomo

Comp_hw

0,1 0,1

1,11,1

Quando conviene usare questa alternativa? quando la generalizzazione non è totale, quando ci sono operazioni che si riferiscono solo

a occorrenze di E1, E2 oppure E0, Risparmio di memoria rispetto alla scelta (1):

non si introducono valori nulli Aumento degli accessi per mantenere la

consistenza delle occorrenze

relazioni Scelta degli identificatori primari

entità e associazioni in uno schema E-R possono essere partizionati o accorpati per rendere più efficienti le operazioni, in base a un semplice principio:

gli accessi si riducono: separando attributi di un concetto che vengono

acceduti separatamente; raggruppando attributi di concetti diversi acceduti

insieme;

Ristrutturazioni, casi principali

partizionamento orizzontale/verticale di entità

eliminazione di attributi multivalore

accorpamento di entità/ relationship

Impiegato

Livello

Stipendio

Ritenute

Cognome

Indirizzo

Datanascita

Codice

Partizionamento verticale

di entità

LivelloStipendio

Ritenute

Cognome

Indirizzo Datanascita

Codice

Dati ImpiegatoDati

anagraficiDati

lavorativi

(1,1) (1,1)

Si separano gli attributi in gruppi omogenei

Agenzia

Indirizzo

Città

Telefono

Eliminazione di attributi multivalore

Numero

Indirizzo

UtenzaAgenzia Telefono

(1,N) (1,1)

Città

Si introduce una entità le cui istanze sono identificatedai valori dell’attributo

L’associazione può essere uno a molti o molti a molti

Agenzia

Indirizzo

Città

Telefono

Se la cardinalità massima K è nota: è possibile prevedere K repliche degli attributi

Tel2Tel1 Tel3

Agenzia

Indirizzo Città

Eliminazione di attributi multivalore

IndirizzoInternoCognome

Indirizzo Datanascita

Codicefiscale

IntestazionePersona Appartamento

(0,1) (1,1)

Accorpamento di Entità

Persona

Interno

Indirizzo

Cognome

Indirizzo

Datanascita

Codicefiscale

Cognome

ComposizioneGiocatore Squadra

(1,N) (1,N)

RuoloNomeCittà

Data acquisto Data cessione

Cognome

Comp.passata

Giocatore Squadra

(0,N) (1,N)

Ruolo Nome

CittàData acquisto Data cessione

Comp.attuale

Data acquisto(1,1) (1,N)

Partizionamento orizzontale di associazioni

relationship Scelta degli identificatori primari

Scelta degli identificatori principali

operazione indispensabile per la traduzione nel modello relazionale perché nel modello relazionale le chiavi vengono usate per

stabilire legami tra dati in relazioni diverse; i DBMS richiedono di specificare una chiave primaria;

si deve decidere quale degli identificatori utilizzare come chiave principale

Criteri Gli attributi con valori nulli non possono essere

identificatori principali Gli identificatori con uno o pochi attributi sono preferibili

vantaggio in termini di risparmio di memoria nella realizzazione dei legami logici tra le varie relazioni

facilita le operazioni di join Un identificatore interno è preferibile Un identificatore utilizzato nelle operazioni più frequenti o

importanti è preferibile

Se nessuno degli identificatori soddisfa i requisiti visti?

Si introducono nuovi attributi (codici) contenenti valori speciali generati appositamente per questo scopo

ES. Contatori, Sigle

L’identificatore {Interno, Indirizzo} è opzionale, quindi non può essere scelto

Tra Codice fiscale e Codice SSN la scelta dipende da quale è più frequentemente usato per accedere a una persona

Interno

Indirizzo

Cognome

Indirizzo

Data nascita

Codice fiscale

(0,1)Codice SSN

Persona

Fase 2: Traduzione verso il modello relazionale

Input: schema concettuale modello logico

Output: schema logico

Sono possibili altre fasi: Verifiche sulla qualità dello schema Normalizzazione dello schema prodotto

Traduzione verso il relazionale

idea di base: le entità diventano relazioni sugli stessi attributi

e la chiave è l’identificatore principale le relationship (o associazioni) diventano

relazioni i cui attributi sono gli identificatori delle entità coinvolte e gli attributi propri dell’associazione

Ogni entità è tradotta con una relazione con gli stessi attributi La chiave primaria coincide con l’identificatore

principale dell’entità Gli attributi composti vengono ricorsivamente

suddivisi nelle loro componenti, oppure si mappano in un singolo attributo della relazione, il cui dominio va opportunamente definito

Per brevità, usiamo l’asterisco (*) per indicare la possibilità di valori nulli

Persona indirizzo

cittàn.civico (0,1)

nomecognome

cod_fiscale

Persona(CF, Cognome, Nome, Via, NCivico*,Città,CAP)

Impiegato(Matricola, Cognome, Stipendio)

Progetto(Codice, Nome, Budget)

Partecipazione(Matricola, Codice, DataInizio)

Partecipazione

(0,N) (1,N)

Cognome

Stipendio

Matricola

Impiegato

NomeCodice

Budget

Progetto

Data inizio

Entità e relationship molti a molti

Nomi più espressivi per gli attributi della chiave della relazione che rappresenta la

relationship

Partecipazione(Matricola, Codice, DataInizio)

Nomi più espressivi per gli attributi della chiave della relazione che rappresenta la

relationship

Partecipazione(Impiegato, Progetto, DataInizio)

Composizione

ProdottoComposto Componente

Costo Nome Codice

(0,N) (0,N)

Prodotto(Codice, Nome, Costo)

Composizione(Composto, Componente, Quantità)

Relationship ricorsive

Quantità

Fornitore Prodotto

Dipartimento

Fornitura

Partita IVA Genere CodiceQuantità

Telefono

(0,N) (1,N)

Relationship n-arie

Fornitore(PartitaIVA, Nome)Prodotto(Codice, Genere)

Dipartimento(Nome, Telefono)Fornitura(Fornitore, Prodotto, Dipartimento, Quantità)

Cognome

Giocatore SquadraContratto

Datanascita Città Nome

Ingaggio

(1,1) (0,N)

Ruolo Colori sociali

Relationship uno a molti

Giocatore(Cognome, DataNascita, Ruolo)Squadra(Nome, Città, ColoriSociali)

Contratto(CognGiocatore, DataNascG, Squadra, Ingaggio)

Soluzione più compatta

Giocatore(Cognome, DataNascita, Ruolo)Contratto(CognGiocatore, DataNascG, Squadra, Ingaggio)

Squadra(Nome, Città, ColoriSociali)

Giocatore e Contratto hanno la stessa chiave: possiamo fonderle in un’unica relazione

Giocatore(Cognome, DataNasc, Ruolo, Squadra, Ingaggio)Squadra(Nome, Città, ColoriSociali)

con vincolo di integrità referenziale fra Squadra in Giocatore e la chiave di Squadra

Cognome

Giocatore SquadraContratto

Datanascita Città Nome

Ingaggio

(0,1) (0,N)

Ruolo Colori sociali

Osservazione

se la cardinalità minima della relationship è 0, allora Squadra e Ingaggio in Giocatore devono ammettere valore nullo

Giocatore(Cognome, DataNasc, Ruolo, Squadra*, Ingaggio*)Squadra(Nome, Città, ColoriSociali)

Associazioni ricorsive uno a molti

In questo caso è possibile operare una traduzione con 1 o 2 relazioni

1 relazione:

Impiegato(Codice, Nome, Qualifica, Responsabile*) FK: Responsabile REFERENCES Impiegato

2 relazioni:

Impiegato(Codice, Nome, Qualifica)Dipendenza(Dipendente, Responsabile)FK: Dipendente REFERENCES Impiegato FK: Responsabile REFERENCES Impiegato

Responsabile Dipendente

Qualifica Nome Codice

(0,N) (0,1)Dipendenza

Impiegato

IscrizioneStudente Università

Cognome Matricola

AnnoDiCorso

Indirizzo

(1,1) (1,N)

Città

Entità con identificazione esterna

Studente(Matricola, Università, Cognome, AnnoDiCorso)

Università(Nome, Città, Indirizzo)

Nel caso di entità identificata esternamente, si “importa” l’identificatore della/e entità identificante/i.

L’associazione relativa risulta automaticamente tradotta

Direttore DipartimentoDirezione

Cognome Codice Sede NomeData inizio

(1,1) (1,1)

Stipendio Telefono

Relationship uno a uno (1,1) – (1,1)

Direttore (Codice, Cognome, Stipendio)

Dipartimento (Nome, Sede, Telefono, Direttore, InizioD)

Direttore (Codice, Cognome, Stipendio, InizioD, Dipartimento)

Dipartimento (Nome, Sede, Telefono)

Impiegato DipartimentoDirezione

(0,1) (1,1)

Stipendio Telefono

tradurre l’associazione Direzione inglobandola in Impiegatonon è in generale una buona scelta (dipende dai volumi dei dati in gioco): troppi valori nulli!

Impiegato(Codice, Cognome, Stipendio, Dipartimento*, DataInizio*)

Dipartimento(Nome, Sede, Telefono)

(0,1) (1,1)

Stipendio Telefono

Soluzione:

Impiegato (Codice, Cognome, Stipendio)

Dipartimento (Nome, Sede, Telefono, Direttore, InizioD)

• con vincolo di integrità referenziale, senza valori nulli

(0,1) (0,1)

Stipendio Telefono

Per evitare la presenza di valori nulli nelle chiavi esterne:

Impiegato (Codice, Cognome, Stipendio)

Dipartimento (Nome, Sede, Telefono, Direttore)

Direzione (Direttore, Dipartimento, DataInizio)

2 relazioni:Persona(CF, Nome)Matrimonio(Marito, Moglie)

Marito Moglie

Nome CF

(0,1) (0,1)Matrimonio

Persona

Un esempio particolare

(1,1)(0,1)

(1,N)(0,1)

CittàIndirizzo

Telefono

Dipartimento

Composizione

Direzione

Afferenza

Impiegato

Progetto

Partecipazione

Cognome

Budget

Codice

Per le entità E che partecipano ad associazioni sempre con max-card(E,R) = n la traduzione è immediata:

Sede(Città, Via, CAP)

Progetto(Nome, Budget)

Anche l’associazione Partecipazione si traduce immediatamente:

Partecipazione(Impiegato, Progetto)

L’entità Dipartimento si traduce importando l’identificatore di Sede e inglobando l’associazione Direzione

Dipartimento(Nome, Città, Telefono, Direttore)

Per tradurre l’associazione Afferenza, assumendo che siano pochi gli impiegati che non afferiscono a nessun dipartimento, si opta per una rappresentazione compatta

Impiegato(Codice, Cognome, Dipart*,CittàDip* Data*)

Progettazione Logica89

Esercizio 1

Corso_AA Docenteresponsabilitànomecognomedata_nascita

(0,n)(1,1)

cod_doc

AA ciclo

attivato

(0,n)cod_corso

FA_VISITA

(1, 1)

(0, N)

IN_VISITA

(0, N)

(1, 1)

MEDICO

VISITA

PAZIENTE

nomecognometelefonocod_med

nomecognomedata_nascitacod_paziente

Esercizio 2

GIOCATORI(n_giocatore, cognome, iniziali, data_nascita, sesso, iscritto_dal, indirizzo, numero, cap, citta, telefono, n_socio)SQUADRE(n_squadra, n_giocatore, categoria)PARTITE(n_partita, n_squadra, n_giocatore, vittorie, sconfitte)PENALITA(n_pagamento, n_giocatore, data_pagamento, importo)MEMBRI_COMMISSIONE(n_giocatore, data_inizio, data_fine, carica)

Trovare lo schema ER che dà luogo al seguente schema relazionale:

Progettazione Logica 1 Progettazione logica I.Analisi delle prestazioni su schemi E-R...

Documents