Basi di Dati e Sistemi Informativi

Basi di Dati e SistemiInformativi Normalizzazione Home page del corso: http://www.cs.unibo.it/~difelice/dbsi/

Progettazionelogica Analisideirequisitie progettazionein dettaglio … Studio/analisideirequisiti Fasidellaprogettazione Risultati SCHEMA CONCETTUALE Progettazioneconcettuale SCHEMA LOGICO Progettazionelogica SCHEMA FISICO Progettazionefisica

Normalizzazionedeidati • Lo schema logicoprodottodallaprogettazionedovrebberispettaredeirequisiti di qualita’: • Completezza lo schema logicodeveconteneretuttiiconcettidel diagramma E-R • Correttezza lo schema logicodevepreservare la semanticadel diagramma E-R • Efficienza  lo schema logico non devecontenereridondanze sui dati …

Normalizzazionedeidati • Le ridondanze sui datipossonoessere di due tipi: • Ridondanzaconcettuale  non ci sonoduplicazionidellostessodato, ma sonomemorizzateinformazionichepossonoesserericavate da altregia’ contenutenel DB. • Ridondanzalogica  esistonoduplicazioni sui dati, chepossonogenerareanomalienelleoperazioni sui dati …

Normalizzazionedeidati Esempi di ridondanzeconcettualichepossonopresentarsigia’ neldiagramma E-R… ATTRIBUTI DERIVABILI Lordo Netto Tasse Lo stipendionettosipuo’ ricavaredal lordo e dalletasse. STIPENDIO Il NumeroAbitantisipuo’ ricavarecontando le Persone Codice Fiscale Nome Numero Abitanti Codice Descrizione RESIDENTI (1,N) (1,1) PERSONA CITTA ANALISI DEI COSTI!

Normalizzazionedeidati • Le ridondanze sui datipossonoessere di due tipi: • Ridondanzaconcettuale  non ci sonoduplicazionidellostessodato, ma sonomemorizzateinformazionichepossonoesserericavate da altregia’ contenutenel DB. • Ridondanzalogica  esistonoduplicazioni sui dati, chepossonogenerareanomalienelleoperazioni sui dati …

Normalizzazionedeidati Q.Cosac’e’ di strano in questatabella?

Normalizzazionedeidati Q.Cosac’e’ di strano in questatabella? A. Lo stipendio di ciascundocente e’ ripetuto in tutte le tuple relative  Ridondanze sui dati!

Normalizzazionedeidati Q.Cosac’e’ di strano in questatabella? A. Il direttore di un dipartimento e’ ripetuto in tutte le tuple relative  Ridondanze sui dati!

Normalizzazionedeidati Q.Cosac’e’ di strano in questatabella? Anomalia di aggiornamento  se varia lo stipendio, devomodificaretutte le tuple del docente!

Normalizzazionedeidati Q.Cosac’e’ di strano in questatabella? Anomalia di cancellazione  Se un docente non ha corsi, dobbiamoeliminaretuttiisuoidati …

Normalizzazionedeidati Q.Cosac’e’ di strano in questatabella? Anomalia di inserimento  Non possoinserire un docenteche non ha un corsoassociato …

Normalizzazionedeidati • V1.Ognidipartimento ha un solo direttore. • V2.Ognidocente ha un solo stipendio(anche se ha piu’ corsi). • V3. Lo stipendiodipende dal livello e non dal dipartimento o dal corso tenuto! • PROBLEMA: Abbiamousatoun’unicarelazione per rappresentareinformazionieterogenee!

Normalizzazionedeidati • Da dove derivaunaridondanza? • Traduzioni non correttenelmodellologico… Cognome Codice Salario Sede Nome PARTECIPAZIONE (0,N) (1,N) PERSONA PROGETTO PERSONA

Normalizzazionedeidati • Da dove derivaunaridondanza? • Erroridurante la progettazioneconcettuale .. NomeComponente Nome L’entita’ PRODOTTO contienesia le informazionisulprodottosiaquellesulle sue componenti. PrezzoProdotto CollocazioneProdotto PRODOTTO TipoComponente Meglioristrutturare lo schema E-R, partizionandol’entita’ edintroducendodellerelazioniuno-a-molti o molti-a-molti!

Normalizzazionedeidati Per risolvere le anomalieviste fin qui, si introduce un nuovovincolo di integrita’ sulmodellorelazionale: la DipendenzaFunzionale (DF). Data unarelazione r suunoschema R(X) e due attributi Y e Z di X. Esistela dipendenzafunzionale Y Z se per ognicoppia di tuple t1 e t2 di rcon t1[Y]=t2[Y], si ha ancheche t1[Z]=t2[Z].

Normalizzazionedeidati DF1: Impiegato Stipendio Spiegazione: [Ogniimpiegato ha un unicostipendio]

Normalizzazionedeidati DF2: Progetto Sede Spiegazione: [Ogniprogetto ha un’unicasede]

Normalizzazionedeidati DF3: ImpiegatoProgetto Impiegato Spiegazione: ovvia! (ed inutile)

Normalizzazionedeidati DF4: ImpiegatoProgetto Ruolo Spiegazione: Un impiegatopuo’ coprire un solo ruolo per progetto!

Normalizzazionedeidati Le dipendenzefunzionalisono definite a livello di schema e non a livello di istanza! • DipendenzafunzionaleCorsoVoto? NO!

Normalizzazionedeidati Le dipendenzefunzionalisono definite a livello di schema e non a livello di istanza! • DipendenzafunzionaleCorsoDocente? Puo’ essere, • occorreconsiderare le specifiche del sistema…

Normalizzazionedeidati Le dipendenzefunzionalihannosempreun verso! • Corso Docente? OKDocente  Corso? NO!

Normalizzazionedeidati Le dipendenzefunzionalisonounageneralizzazione del vincolo di chiave. Data unarelazione r con schema R(X), con chiave K. Esiste un vincolo di dipendenzafunzionaletra K e qualsiasiattributodello schema r. Q. Cheaccade se K e’ unasuperchiave?

Normalizzazionedeidati ESEMPIO. Impiegato,Progetto e’ unachiavedellarelazione non possonoesistere due tuple con lo stessovaloredellacoppia <Impiegato, Progetto>! DF1: ImpiegatoProgetto Stipendio DF2:ImpiegatoProgetto  Sede DF3:ImpiegatoProgetto  Ruolo DF4:ImpiegatoProgetto SedeRuolo …. DFn:ImpiegatoProgetto ImpiegatoStipendioProgettoSedeRuolo

Normalizzazionedeidati DF1: Impiegato Stipendio DF2: Progetto Sede DF3: ImpiegatoProgetto  Sede

Normalizzazionedeidati • Dipendenzefunzionali “ buone” e “cattive”. • DF1: Impiegato Stipendio • DF2: Progetto  Sede • Ridondanza sui dati, possibilianomalie(aggiornamento, • cancellazione, etc)nelleoperazioni sui dati… • DF3: ImpiegatoProgetto Ruolo • Non determinaridondanze sui dati …

Normalizzazionedeidati • Perche’ DF3 non causaanomalie a differenza di DF1 e di DF2? • DF1: Impiegato Stipendio • DF2: Progetto  Sede • DF3: ImpiegatoProgetto Ruolo • Motivo: • DF3 ha sullasinistrauna(super)chiave. • DF1 e DF2 non contengonouna (super)chiave.

Normalizzazionedeidati FORMA NORMALE di BOYCE-CODD (FNBC) Uno schema R(X) si dice in forma normale di Boyce e Coddse per ognidipendenzafunzionale (non ovvia) Y Zdefinitasu di esso, Y e’ unasuperchiave di R(X). • Se unarelazione e’ in FNBC, non presenta le anomaliee ridondanzeviste fin qui. • Se unarelazioneNON e’ in FNBC, bisognatrasformarla (normalizzarla) -se possibile- in FNBC.

Normalizzazionedeidati Esempi di relazioni… DF: LocalitaStato Abitanti Rispetta la FNBC! DF: Stato Prefisso NONrispetta la FNBC!

Normalizzazionedeidati Q. Come normalizzareunarelazione? A. Crearetabelle separate per ognidipendenzafunzionale, ognuna in FNBC! IMPIEGATO, PROGETTO  RUOLO IMPIEGATO STIPENDIO PROGETTO  SEDE

Normalizzazionedeidati Q. Tutte le decomposizionivannobene? IMPIEGATO SEDE PROGETTO SEDE • DF1. Impiegato Sede(Ogniimpiegato lavora in una sola sede) • DF2. Progetto  Sede(Ogniprogetto ha la stessasede)

Normalizzazionedeidati Q. Tutte le decomposizionivannobene? = Se combino le due relazionidelladecomposizionetramiteoperatore di join, non ottengo la relazione di partenza! (decomposizione con perdita) TUPLE SPURIE

Normalizzazionedeidati DECOMPOSIZIONE SENZA PERDITA Uno schema R(X) sidecomponesenzaperditaneglischemi R1(X1) ed R2(X2) se, per ognipossibileistanza r di R(X), il join naturaledelleproiezioni di r su X1 ed X2 produce la relazione r di partenza. • In caso di decomposizione con perdite, possonogenerarsidelletuple spuriedopoil join.

Normalizzazionedeidati Sia r unarelazione con schema R(X) e siano X1ed X2sottoinsiemi di R(X) con . Siainoltre X0 un attributocomune di X1ed X2. La relazione r sidecomponesenzaperditaneglischemi R(X1) ed R(X2) se soddisfaunadelle due dipendenzefunzionali: X0 X1 o X0  X2. (In pratica, X0 e’ unasuperchiave di X1 o di X2)

Normalizzazionedeidati Anche se unadecomposizione e’ senzaperdite, puo’ comunquepresentaredeiproblemi di conservazionedelledipendenze … IMPIEGATO SEDE • Con questadecomposizione, non ho tuple spurie …

Normalizzazionedeidati Anche se unadecomposizione e’ senzaperdite, puo’ comunquepresentaredeiproblemi di conservazionedelledipendenze … IMPIEGATO SEDE • Cheaccade se aggiungol’impiegatoNeri al progettoMarte?

Normalizzazionedeidati Q. Tutte le decomposizionivannobene? IMPIEGATO SEDE = Violazione del vincolo di dipendenzaProgetto Sede

Normalizzazionedeidati REGOLA PRATICA: Per conservare le dipendenze in unadecomposizione, ognidipendenzafunzionaledello schema dovrebbecoinvolgereattributichecompaionotuttiinsiemein unodeglischemidecomposto… r1 r2 • DF1: IMPIEGATO  SEDE, OK, Compare nella prima relazione • DF2: PROGETTO  SEDE, Non compare in nessunarelazione

Normalizzazionedeidati • Q. Tutte le decomposizionivannobene? • NO! Le decomposizionedovrebberosempresoddisfaretreproprieta’: • Soddisfacimentodella FNBC: ognirelazioneprodottadeveessere in FNBC. • Decomposizionesenzaperdita: il join dellerelazionidecompostedeveprodurre la relazioneoriginaria. • Conservazionedelledipendenze: il join dellerelazionidecompostedeverispettaretutte le DF originarie.

Normalizzazionedeidati Q. Data unarelazione non in FNBC, e’ semprepossibileottenereunadecomposizione in FNBC? A. NO! consideriamo un controesempio … • DF1. ProgettoSede Dirigente • DF2.Dirigente  Sede • PROBLEMA:DF1 coinvolgetuttigliattributi, nessunadecomposizionepuo’ preservare la dipendenza!

Normalizzazionedeidati Per risolverecasi come quelloprecedente, si introduce unanuovadefinizione di forma normalemenorestrittivadella forma di Boyce e Codd… TERZA FORMA NORMALE (TFN) • Unarelazione r e’ in terza forma normalese per ognidipendenzafunzionale XA dello schema, almenounadelleseguenticondizioni e’ verificata: • X contieneunachiave K di r • A appartiene ad almenounachiave K di r

Normalizzazionedeidati La relazioneconsiderata fin qui rispetta la TFN! • DF1. ProgettoSede Dirigente • DF2.Dirigente  Sede • DF1: ProgettoSede e’ unachiave Condizione 1 soddisfatta! • DF2: Sede e’ parte di unachiave  Condizione 2 soddisfatta! Se e’ gia’ in TFN, non e’ necessariaalcunanomalizzazione! Putroppo le ridondanze sui datirestano …

Normalizzazionedeidati CONFRONTO TRA TFN e FNBC • (SVANTAGGI) La TFN e’ menorestrittivadella FNBC • Tolleraalcuneridondanzeedanomalie sui dati. • Certificameno lo qualita’ dello schema ottenuto. • (VANTAGGI) La TFN e’ sempreottenibile, qualsiasisia lo schema di partenza. • COME? Algoritmo di normalizzazionein TFN!

Normalizzazionedeidati ALGORITMO DI NORMALIZZAZIONE IN TERZA FORMA NORMALE (TFN)

Normalizzazionedeidati TERZA FORMA NORMALE (TFN) • Unarelazione r e’ in terza forma normalese per ognidipendenzafunzionale XA (non banale) dello schema, almenounadelleseguenticondizioni e’ verificata: • X contieneunachiave K di r (X e’ unasuperchiave di r) • A appartiene ad almenounachiave K di r

Normalizzazionedeidati DIPENDENZA FUNZIONALE BANALE UnadipendenzafunzionaleXY e’ banale se Y e’ contenuto in X. • ESEMPI: • ImpiegatoProgetto Impiegato • ImpiegatoProgettoSede  ImpiegatoProgetto Questogenere di dipendenzefunzionali non ci interessano, e non le conseriamo come talinelrestodellatrattazione …

Normalizzazionedeidati • Data unarelazione r con schema R(X) non in TFN, normalizzare in TFNvuol dire: decomporre r nellerelazioni r1,r2, … rn, garantendoche: • Ogniri(1<=i<=n) e’ in TFN. • La decomposizione e’ senzaperdite. • La decomposizioneconservatutte le dipendenzeF definite sullo schema R(X) di partenza.

Basi di Dati e Sistemi Informativi