Esercizi Normalizzazione PDF

Preview

Summary

Download Esercizi Normalizzazione PDF

Description

Normalizzazione: soluzione degli esercizi suggeriti Esercizi della diapositiva 23 nella presentazione: IntegritàNormalizzazione 1. Le righe di una tabella contengono le informazioni anagrafiche di uno studente e i voti conseguiti a fine anno in 9 materie. Normalizzare la tabella in modo da ottenere le tabelle in prima forma normale, eliminando gli attributi ripetuti. In base al testo dell’esercizio si può ipotizzare una tabella Studenti con il seguente schema: Studenti ( Matricola, Cognome, Nome, Nascita, V1, V2, V3, V4, V5, V6, V7, V8, V9 ) Nella tabella Studenti si assume, implicitamente, che V1 sia il voto della Materia1, V2 della Materia2 e così via, con V9 voto della Materia9. Per evitare la presenza di un array di voti bisogna disporre di un’anagrafica delle materie rappresentata, per esempio, da una tabella di nome Materie con i campi Codice e NomeMateria, e una tabella dei voti che rappresenta l’associazione molti a molti tra gli studenti e le materia. Riassumendo, si ottengono le tabelle: Studenti ( Matricola, Cognome, Nome, Nascita ) Materie ( Codice, NomeMateria ) Voti (Matricola, CodiceMateria, Anno, Voto ) Nella tabella Voti si è introdotto il campo Anno per ricordare l’anno nel quale i voti finali sono stati ottenuti. In tale caso la tabella Voti ha chiave primaria composta ottenuta dall’insieme dei tre attributi Matricola, CodiceMateria e Anno.

2. La tabella Anagrafica contiene informazioni sui dipendenti di un’azienda e ha il seguente schema: Anagrafica ( Nome, Cognome, Matricola, Nascita, Indirizzo, CodiceDipartimento, NomeDipartimento, IndirizzoDipartimento, Stipendio ) Definire le dipendenze funzionali che sussistono sullo schema di Anagrafica, individuare le eventuali violazioni alla seconda e terza forma normale e scomporre la tabella in due o più tabelle per eliminare le violazioni individuate. Nella tabella Anagrafica si identificano le due dipendenza funzionali: 1. Matricola  ( Nome, Cognome, Nascita, Indirizzo; CodiceDipartimento, Stipendio ) 2. CodiceDipartimento  ( NomeDipartimento, IndirizzoDipartimento ) Dalle dipendenze funzionali 1. e 2. si deduce che Matricola è determinate per l’intero schema di Anagrafica e, quindi, Matricola è una chiave candidata. La tabella è in prima e seconda forma normale ma non in terza forma normale. Infatti: la dipendenza funzionale 2. mostra una violazione alla 3FN per la presenza di due attributi che dipendono solo transitivamente dalla chiave Matricola. La normalizzazione di Anagrafica porta a costruire una prima tabella, Dipartimenti, con tutti gli attributi della dipendenza funzionale 2. e chiave primaria CodiceDipartimento; nella schema originale di Anagrafica bisogna poi rimuovere tutti gli attributi di Dipartimenti ad eccezione 1

del determinate della 2, CodiceDiparitmento, che diventa chiave esterna della tabella Anagrafica. In base a quanto detto, alla fine, si ottiene: Dipartimenti ( CodiceDipartimento, NomeDipartimento, IndirizzoDipartimento ) Anagrafica ( Nome, Cognome, Matricola, Nascita, Indirizzo, CodiceDipartimento, Stipendio )

3. La tabella Esami contiene informazioni sui risultati conseguiti dagli studenti negli esami e ha uno dei due seguenti schemi: Esami-1 (Corso, Matricola, Data, Voto, NomeCorso) Esami-2 (Corso, Matricola, Data, VotoInCifre, VotoInLettere) Definire, in entrambi i casi, le dipendenze funzionali che sussistono sullo schema della tabella e individuare le eventuali violazioni alla seconda e terza forma normale. Nella tabella Esami-1 si identificano le due dipendenza funzionali: 1. { Corso, Matricola, Data }  Voto 2. Corso  NomeCorso Dalle dipendenze funzionali 1. e 2. si deduce che l’insieme dei tre attributi Corso, Matricola, Data è determinate per l’intero schema di Esami-1 e, quindi, è una chiave candidata. La tabella Esami-1 è in prima ma non in seconda forma per la presenza della dipendenza funzionale 2. dove compare l’attributo NomeCorso che dipende da Corso e, quindi, solo parzialmente dalla chiave e non dall’intera chiave. Per normalizzare Esami-1 si procede come nell’esercizio precedente isolando in una nuova tabella gli attributi della dipendenza funzionale che contiene una violazione alla 2FN e semplificando lo schema originale di Esami-1: Corsi ( Corso, NomeCorso ) Esami-1( Corso, Matricola, Data, Voto ) Si osservi la presenza di Esami-1.Corso chiave esterna associata a Corsi.Corso Nella tabella Esami-2 si identificano le due dipendenza funzionali: 1. { Corso, Matricola, Data }  VotoInCifre 2. VotoInCifre  VotoInLettere Dalle dipendenze funzionali 1. e 2. si deduce che l’insieme dei tre attributi Corso, Matricola, Data è determinate per l’intero schema di Esami-2 e, quindi, è una chiave candidata per la tabella. Esami-2 è in prima e in seconda forma normale ma non in terza forma normale per la presenza della dipendenza funzionale 2. dove compare l’attributo VotoInLettere che dipende da VotoInCifre, attributo non chiave della tabella. Per normalizzare Esami-2 si procede come sopra isolando nella tabella Voti gli attributi della dipendenza funzionale 2. e semplificando lo schema originale di Esami-2: Voti ( VotoInCifre, VotoInLettere ) Esami-2( Corso, Matricola, Data, VotoInCifre ) Si osservi la presenza di Esami-2.VotoInCifre chiave esterna associata a Voti.VotoInLettere

2

4. Una tabella contiene per ogni riga il codice del libro, il codice dell’autore, il nome dell’autore, il titolo del libro, il nome dell’editore, l’indirizzo dell’editore, il prezzo e l’anno di edizione. Normalizzare la tabella in modo da ottenere tabelle in terza forma normale che eliminino la/le dipendenze transitive. La tabella, cui diamo nome Libri, ha il seguente schema: Libri ( Codice, Autore, NomeAutore, Titolo, Editore, IndirizzoEditore, Prezzo, Anno ) Nella tabella Libri si identificano le dipendenza funzionali: 1. Codice  ( Autore, Titolo, Editore, Prezzo, Anno ) 2. Autore  NomeAutore 3. Editore  IndirizzoEditore Dalle dipendenze funzionali 1., 2. e 3. si deduce che Codice è determinate per l’intero schema di Libri e, quindi, è una chiave candidata. La tabella Libri è in prima e in seconda forma normale, ma non in 3FN per la presenza delle dipendenze funzionali 2. e 3. dove compaiono attributi che dipendono transitivamente dalla chiave. Per normalizzare Libri si comincia ad eliminare la violazione rappresentata dalla dipendenza funzionale 2. Procedendo come nei casi precedenti si ottengono le tabelle: Autori ( Autore, NomeAutore ) Libri ( Codice, Autore, Titolo, Editore, IndirizzoEditore, Prezzo, Anno ) Anche la nuova versione di Libri non è in 3FN per la presenza della dipendenza funzionale 3. Per eliminare tale violazione si definisce una nuova tabella con i dati degli editori e si ridefinisce lo schema di Libri: Editori ( Editore, IndirizzoEditore ) Libri ( Codice, Autore, Titolo, Editore, Prezzo, Anno ) Al termine del processo lo schema originale di Libri è stato scomposto in: Autori ( Autore, NomeAutore ) Editori ( Editore, IndirizzoEditore ) Libri ( Codice, Autore, Titolo, Editore, Prezzo, Anno ) Si osservi la presenza di Libri.Autore chiave esterna associata a Autori.Autore e di Libri.Editore chiave esterna associata a Editori.Editore

3

Per comprendere l’osservazione che segue è necessario conoscere il linguaggio SQL. E’ quindi utile riguardare questo esercizio al termine del corso o dopo avere appreso i primi rudimenti del linguaggio SQL. Completiamo l’esercizio rispondendo alla seguente richiesta: si supponga di avere la tabella Libri, nella forma originale non normalizzata, popolata con i dati di migliaia di libri. Come si deve procedere per ottenere le tabelle Autori, Editori e la tabella dei libri, diciamo LibriNorm, nella forma normalizzata, con i relativi dati? Mostriamo le operazioni dell’algebra relazionale e i comandi SQL (si veda la presentazione LinguaggioSQL2) necessari per ottenere le tre tabelle richieste: Creazione della tabella Autori con i relativi dati La tabella Autori si ottiene per proiezione di Libri sui due campi: Autore, NomeAutore L’interrogazione SQL che visualizza tali dati è la seguente:

il comando SQL che crea la tabella Autori è:

Creazione della tabella Editori con i relativi dati La tabella Editori si ottiene per proiezione di Libri sui due campi: Editore, IndirizzoEditore L’interrogazione SQL che visualizza tali dati è la seguente:

Il comando SQL che crea la tabella Editori è:

Creazione della tabella LibriNorm con i relativi dati LibriNorm si ottiene per proiezione di Libri su: Codice, Autore, Titolo, Editore, Prezzo, Anno L’interrogazione SQL che visualizza tali dati è la seguente:

Si noti, nella clausola SELECT, l’assenza della specifica DISTINCT che non è necessaria perché il campo Codice (chiave primaria di Libri) garantisce l’unicità delle righe estratte. Il comando SQL che crea la tabella LibriNorm:

4...