Il Modello Relazionale - · 745 Neri Anna 23/04/1982 768 Verdi Giuseppe 12/02/1982 614 Rossi...

1

Elaborazione Automatica dei Dati

IL MODELLO LOGICO DEI DATI

Enrico Cavalli

Anno Accademico 2012-2013

Il Modello Relazionale

2

Elaborazione automatica dei dati - Enrico Cavalli - Università di Bergamo 3

Le relazioni

• Il successo del modello relazionale si fonda sulla visione tabellare dei dati, semplice e intuitiva, ma basata sul concetto matematico di relazioneche ne permette una formalizzazione rigorosa

• Relazione:– In matematica si definisce prodotto cartesiano di due insiemi A e B,

A×B l’insieme delle coppie ordinate (a,b), dove: a∈A, b∈B.

– A = {2, 3}, B = {4, 9, 16}

– A×B = { (2,4), (2,9), (2,16), (3,4), (3,9), (3,16) }

– una relazione (binaria) sugli insiemi A e B, detti domini della relazione , è un insieme R ⊆⊆⊆⊆ A×B.

– R = { (2,4), (3,9) } è una relazione su A e B che si può indicare con il nome RadiceQuadrataDi � RadiceQuadrataDi (2,4)


Relazioni e Tabelle

– A = {2, 3}, B = {4, 9, 16}

– A×B = { (2,4), (2,9), (2,16), (3,4), (3,9), (3,16) }– R = { (2,4), (3,9) } ⊆⊆⊆⊆ A×B.

2 42 92 163 43 93 16

2 42 92 163 43 93 16

A ×××× B

Relazione su A e B

Un dominio rappresenta l’insieme di valori ammessi per

l’attributo

93

42

RadiceQuadrataDi (a, b)

3


Relazioni e Tabelle

• Le definizioni di prodotto cartesiano tra due insiemi e di relazione binaria si estendono naturalmente al caso di n insiemi.

• Grado della relazione : il numero delle componenti del prodotto cartesiano

• Cardinalità della relazione : il numero di n-uple che compaiono nella relazione

• Consideriamo una relazione sui domini A, B, C:A = { Fiat, Citroen }, B = { Punto, Idea, C3 }, C = { B, G }

Fiat Punto B

Fiat Punto GFiat Idea BFiat Idea GFiat C3 BFiat C3 G

Citroen Punto BCitroen Punto GCitroen Idea BCitroen Idea GCitroen C3 BCitroen C3 G

Fiat Punto B

Fiat Punto GFiat Idea BFiat Idea GFiat C3 BFiat C3 G

Citroen Punto BCitroen Punto GCitroen Idea BCitroen Idea GCitroen C3 BCitroen C3 G

Fiat Punto B

Fiat Punto GFiat Idea BFiat Idea G

Citroen C3 BCitroen C3 G

Fiat Punto B

Fiat Punto GFiat Idea BFiat Idea G

Citroen C3 BCitroen C3 G

A ×××× B ×××× CR( A, B, C )


Juventus Lazio 3 1 Lazio Milan 2 0

Juventus Roma 1 2 Roma Milan 0 1 Milan Inter 0 0 Inter Milan 1 1

Relazioni con attributi

La relazione, che descrive i risultati di alcune partite di calcio, è

un sottoinsieme di:

stringa × stringa × intero × intero

Una relazione è un insieme, pertanto:

• Non è definito un ordinamento tra le n-uple della relazione: le righe della relazione non sono ordinate (gli insiemi non sono ordinati)

• Le n-uple della relazione sono distinte l’una dall’altra. In una relazione non ci possono essere due righe uguali (gli elementi di un insieme sono distinti)

• La n-upla è al proprio interno ordinata : l’i-esimo valore proviene dall’i-esimo dominio. La posizione è significativa per l’interpretazione dei dati della relazione: si veda ad esempio la n-upla: ( Juventus Roma 1 2 )

4



• Associamo ad ogni dominio della relazione un nome, detto attributo , che descrive il ruolo giocato dal dominio stesso

• Il nome della relazione con i suoi attributi è detto schema della relazioneCAMPIONATO(Squadra1, Squadra2, Goal1, Goal2)

• Le righe di una relazione, ad esclusione della riga che descrive gli attributi, sono dette tuple .

• Il nome di un attributo è costruito mediante la dot notation :

NomeRelazione.NomeAttributo

Squadra 1 Squadra 2 Goal 1 Goal 2

Juventus Lazio 3 1Lazio Milan 2 0

Juventus Roma 1 2Roma Milan 0 1Milan Inter 0 0Inter Milan 1 1


Juventus Lazio 3 1Lazio Milan 2 0

Juventus Roma 1 2Roma Milan 0 1Milan Inter 0 0Inter Milan 1 1




Juventus Lazio 3 1

Lazio Milan 2 0

Juventus Roma 1 2

Roma Milan 0 1

Milan Inter 0 0Inter Milan 1 1


Lazio Juventus 3 1

Milan Lazio 2 0

Roma Juventus 1 2

Milan Roma 0 1

Inter Milan 0 0Milan Inter 1 1

• L’introduzione degli attributi modifica la definizione di relazione e rende l’ordine degli attributi irrilevante

• Relazioni matematiche : elementi individuati per la posizione

• Database relazionali : elementi riconosciuti dal nome degli attributi

5


Relazioni e basi di dati

• Una base dati è composta da diverse relazioni

• Schema della base dati è l’insieme degli schemi delle relazioni che la compongono

• Il modello relazionale è un modello basato sui valori : il collegamento fra dati in relazioni diverse è rappresentato mediante valori corrispondenti

Matricola Cognome Nome DataNascita

545 Rossi Maria 25/11/1981

745 Neri Anna 23/04/1982768 Verdi Giuseppe 12/02/1982

614 Rossi Pablito 10/10/1981

653 Bruni Carla 01/12/1981314 Cavour Benso 05/10/1983

Codice Titolo Docente

1 Matematica Allevi

3 Informatica Gnudi4 Economia Leoni

Studente Voto Corso

545 28 1545 27 4653 25 1768 24 3314 30 4

Corsi

Esami

Studenti


Relazioni e basi di dati

• Hanno senso relazioni con un solo attributo?

• Si pensi alla relazione Studenti ed alla necessità di avere informazioni sugli studenti lavoratori, oppure sui laureandi:


545 Rossi Maria 25/11/1981



653 Bruni Carla 01/12/1981314 Cavour Benso 05/10/1983

Studenti

Matricola

545

745653

314

Lavoratori Matricola

545

768653

614

Laureandi

6


Chiavi di una relazione

• Supponendo che Cognome, Nome e DataNascita identifichino univocamente un individuo, una tupla può essere riconosciuta dai valori di:

− Matricola

− Cognome, Nome, DataNascita

− Matricola, DataNascita

− Matricola, Facoltà

− Cognome, Nome, DataNascita, Facoltà

− …

Matricola Cognome Nome DataNascita Facoltà

545 Rossi Maria 12/05/1983 Eco653 Neri Anna 23/04/1982 Eco768 Verdi Giuseppe 12/02/1982 Ing834 Rossi Maria 10/10/1981 Mat314 Cavour Benso 10/10/1981 Ling

Matricola Cognome Nome DataNascita Facoltà

545 Rossi Maria 12/05/1983 Eco653 Neri Anna 23/04/1982 Eco768 Verdi Giuseppe 12/02/1982 Ing834 Rossi Maria 10/10/1981 Mat314 Cavour Benso 10/10/1981 Ling

Studenti

Ogni insieme di attributi che include Matricola

oppure l’insieme di attributi Nome, Cognome, DataNascita

permette di identificareuna sola tupla


• Studenti ( Matricola, Cognome, Nome, DataNascita, Facoltà, CodFis )

7


Chiavi

• Ci sono insiemi di attributi che permettono di identificare univocamente ogni tupla di una relazione, mentre altri non lo permettono

• Un insieme di attributi A si dice superchiave di una relazione se nella relazione non vi sono due tuple con il medesimo valore di A

• Un insieme di attributi A si dice chiave (o chiave candidata ) di una relazione se A è una superchiave minimale della relazione, cioè non esiste alcun sottoinsieme proprio di A che sia superchiave

• Chiave primaria di una relazione è una delle chiavi candidate secondo un criterio di scelta deciso dal progettista del database

– Identificare superchiavi e chiavi di Studenti– Una relazione ha sempre una chiave ?– Perché DataNascita non può essere una chiave di Studenti ?

– Se nella tabella Studenti tutti i Nomi fossero differenti si potrebbe dire che Nome è una chiave? Sarebbe una scelta ragionevole?


Chiave esterna di una relazione

• Infrazioni contiene riferimenti ad Agenti e Veicoli:

– Infrazioni.Agente �� Agenti.Matricola

– { Infraz.Prov, Infraz.Numero } �� { Veicoli.Prov, Veicoli.Numero }

– Agente e { Prov, Numero } sono Chiavi Esterne di In frazioni

– Infrazioni( Codice , Data, Agente, Articolo, Prov, Numero )

Codice Data Agente Articolo Prov Numero

3256 25/10/2002 567 44 RM 4E54324554 26/10/2002 456 34 RM 4E54322557 26/10/2002 456 34 RM 2F76433876 15/10/2002 456 53 MI 2F76467856 12/10/2002 567 44 MI 2F7646

Matricola CF Cognome Nome

567 RSSMRO… Rossi Mario456 NRELGI…… Neri Luigi638 NREPRO…. Neri Piero

Prov Numero Proprietario Indirizzo

RM 2F7643 Verdi Piero Via TigliRM 1A2396 Verdi Piero Via TigliRM 4E5432 Bini Luca Via AceriMI 2F7646 Luci Gino Via Aceri

Veicoli

Agenti

Infrazioni

Gli attributi chiave sono sotto-

lineati, le chiaviesterne sono

in corsivo

8


Esercizi

Per ognuna delle seguenti basi di dati, identificare: dominio di ogni attributo, chiavi, chiavi esterne

1. Data Base Multe :• Agenti ( Matricola, CodFis, Cognome, Nome ) • Infrazioni ( Codice, Agente, Data, Articolo, Prov, Numero ) • Veicoli ( Prov, Numero, Proprietario, Indirizzo )

2. Data Base Università :• Studenti ( Matricola, Cognome, Nome, DataNascita, CodiceFiscale )• Corsi ( Codice, NomeCorso, Docente )• Esami ( Studente, Corso, Voto, Lode, Data )

3. Data Base Banca1 :– Clienti ( Nome, Via, Città, NumConto )– Conti ( NumConto, Saldo )

4. Data Base Banca2 :• Agenzie ( NomeAgenzia, Indirizzo, Capitalizzazione )• Clienti ( NomeCliente, Indirizzo, Città )• Prestiti ( NomeAgenzia, NumeroPrestito, NomeCliente, Importo )• Depositi ( NomeAgenzia, NumeroDeposito, NomeCliente, Importo )

5. Data Base ContiTrattoria:• Ricevute ( Num, Data, Totale )• Dettaglio ( Num, Qta, Descrizione, Importo ) – e anche l’alternativa per Dettaglio: • Dettaglio (Num, Riga, Qta, Descrizione, Importo)

Dal modello concettuale al modello logico

9


Dal modello E/R al modello relazionale

• Ogni Entità diventa una relazione– Ogni attributo delle Entità diventa una colonna di una relazione– Domini degli attributi

– Chiavi

Impiegati

Matricola Nome Età Stipendio

101 M.Rossi 34 40103 M.Bianchi 23 35104 L.Neri 38 61105 N.Bini 44 38210 M.Celli 49 60231 S.Bisi 50 60

252 N.Bini 44 70301 S.Rossi 34 70

375 M.Rossi 50 65

Impiegato

Matricola {PK}NomeEtàStipendio


Le associazioni: regole base (1)

• L’associazione uno a uno diventa un’unica relazione che contiene gli attributi di entrambe le entità e, se ci sono, gli attributi dell’associazione

• L’associazione uno a molti si traduce in una coppia di tabelle:

– una prima relazione è derivata dall’entità che gioca il ruolo di entitàa uno nell’associazione

– la seconda relazione è ottenuta integrando gli attributi dell’entitàche gioca il ruolo di entità a molti nell’associazione con:

� la chiave primaria dell’entità a uno , che diventa chiave esternadella nuova relazione

� gli eventuali attributi dell’associazione

10


Le associazioni: regole base (2)

• L’associazione molti a molti si traduce in tre relazioni:

– due tabelle sono derivate dalle due Entità che compaiono nell’associazione con i corrispondenti attributi

– la terza tabella è costruita con:

� le chiavi delle precedenti entità

� gli eventuali attributi dell’associazione

� la chiave primaria della nuova relazione è composta dalle chiavi delle due entità e da ogni altro attributo necessario a garantire l’unicità della chiave


Associazione “uno a uno”

Occupazione

Codice Nome Settore Fila Numero Note

2340 Nino Verdi A 12 12370 Lino Bianchi A 12 2 R21323 Marzia Rossi A 3 434510 Franco Dini B 1 545678 Silvia Gualeni B 4 3 H53325 Franco Bassetti B 2 156789 Enrico Toti A 1 8

Spettatore Posto1 1

Codice {PK}Nome

Settore {PPK}Fila {PPK}Numero {PPK}Note

Occupare

Occupazione :Qual è la chiave?Come si sceglie?

Si consideri il caso:

Cittadini, Parlamentari

11


Associazione “uno a molti”

StudentiMatricola Nome Indirizzo CodFacoltà DataIscrizione AltriDati

2340 Nino Verdi Milano ing 12/12/022370 Lino Bianchi Torino ing 13/12/0221323 Marzia Rossi Bergamo eco 15/9/8934510 Franco Dini Bergamo eco 17/10/9945678 Silvia Gualeni Como lin 18/11/0153325 Franco Bassetti Milano eco 15/09/0056789 Enrico Toti Brescia lin 15/10/01

FacoltàCodice Descrizione NumeroAnni

eco Economia 4ing Ingegneria 5lin Lingue 4

med Medicina 6

Facoltà Studente1 N

DataIscrizioneAltriDati

Codice {PK}DescrizioneNumeroAnni

Matricola {PK}NomeIndirizzo

Essere Iscritto


Associazione “molti a molti”

Studenti

Matricola Nome Indirizzo

122 Adriana Firenze128 Daniele Lecce125 Enrico Milano123 Franco Torino129 Gian Franco Bergamo130 Giorgio Bergamo120 Giovanna Como126 Laura Milano121 Lucia Brescia124 Marida Bergamo127 Vittorio Bergamo

Corsi

Codice Docente Descrizione

econ Leoni Economiafilo Cottino Filosofiainf Cavalli Informatica

mfin Bertocchi Mat.Finanziariamgen Allevi Mat.Generale

Esami

Matricola Codice Data Voto

123 inf 01/01/1999 22123 mgen 01/01/1998 25124 filo 03/01/1997 30125 mgen 25/07/1997 26126 mgen 25/06/1998 23127 mfin 30/09/1999 28127 mgen 30/06/1998 26

Qual è la chiave di Esami ?

NStudente Corso

N

DataVoto

Matricola {PK}NomeIndirizzo

Codice {PK}DocenteDescrizione

Superare

12


Le associazioni: dettagli (1)

• La regola base per l’associazione uno a uno viene integrata con:• Associazione uno a uno con partecipazione opzionale di una delle due

entità: viene trattata come un’associazione uno a molti dove l’entità con partecipazione facoltativa gioca il ruolo a uno

– Per evitare di costruire una sola tabella con molti campi a valore nullo

– Esempi: Cittadino-Parlamentare, Docente-Classe (Coordinare), Docente-Facoltà (Presiedere), Dipendente-PostoMacchina

Dipendente Parchegio1 1

Matricola {PK}Nome

Numero {PK}Settore PostoNote

Occupare

Dipendenti (Matricola, Nome)Parcheggi (Numero, Settore, Posto, Note, Matricola )

Se ci sono molti dipendenti e pochi postiuna sola tabella avrebbe

molte righe con valori nulli



• Associazione uno a uno con partecipazione opzionale di entrambe le entità . La scelta dell’entità che gioca il ruolo a uno va fatta caso per caso, in base alla numerosità delle singole partecipazioni

– Per evitare di costruire una sola tabella con molti campi a valore nullo– Esempi: Maschio-Genitore; Dipendente-AutoAziendale

Dipendente Auto1 1

Matricola {PK}Nome

Targa {PK}Tipo

Assegnare

• Due possibili scelte:

Dipendenti (Matricola, Nome, Targa) Auto (Targa, Tipo)

Dipendenti (Matricola, Nome) Auto (Targa, Tipo, Matricola )

13



• Associazioni uno a uno ricorsive con partecipazione opzionale in dei due ruoli o di entrambi . Si può procede come nei casi precedenti ma, in alternativa, si può creare una nuova tabella.

– Esempi: Femmine (Essere primogenito), Carrozza(Trainare)

Carrozza

Precedente

Successiva

Trainare

1

1

Carrozze ( IDCarro, TipoCarro, DataCostruzione, DataRevisione, NextCarro )

Carrozze ( IDCarro, TipoCarro, DataCostruzione, DataRevisione) Composizione ( IDCarro, NextCarro )



• Considerazioni analoghe valgono per le associazioni uno a molti ricorsive (e non ) con partecipazione opzionale del ruolo a molti

– Esempi: Femmina (EssereFiglia), Dipendente(Dirigere), – Madre – Figlio (AvereGemelli)

Dipendenti ( Matricola, Nome, Cognome, Assunzione, Manager )

Dipendenti ( Matricola, Nome, Cognome, Assunzione )Supervisione ( Collaboratore, Manager)

Dipendente

Manager

Collaboratore

Dirigere

1

N

14


Esempi ed esercizi (1)

Attori ( IDAttore, Nome, Cognome )

Film ( IDFilm, Titolo, Anno, Durata, Acolori, Genere )

Cast ( IDAttore, IDFilm, NumeroScene, Ingaggio )

Attore FilmRecitareN N

NumeroSceneIngaggio

IDAttore {PK} IDFilm {PK}



Attori ( IDAttore, Nome, Cognome )

Film ( IDFilm, Titolo, Anno, Durata, Acolori, Genere )

Contratti (IDAttore, IDFilm, Data, NumeroScene, Ingaggio )

Attore FilmStipulare

N N

IDAttore {PK} IDFilm {PK}

Contratto

DataNumeroSceneIngaggio

11

Riguardare

Si confronti questo schema con quello

dell’esempio precedente ...

15



Indirizzo Residente1 NEssere residenza

CodiciSSN ( SSN, Azienda, Sede )

Cittadini ( CodiceFiscale, Nome, Cognome, Nascita, SSN )

CodiceSSN Cittadino1 1

Essere attribuito

DataResidenza

Indirizzi ( Via, Numero, CAP, Quartiere )

Residenti ( CodiceFiscale, Nome, Cognome, Via, Numero, DataResidenza )



Clienti ( IDCliente, CodFiscale, Nome, Cognome, Indirizzo, Telefono )Corsi ( IDCorso, Descrizione, Giorno, Ora, Costo )Iscrizioni (Numero, DataIscrizione, Pagamento, IDCliente, IDCorso )

Fornitore Prodotto1 NFornire

Fornitori ( IDFornitore, RagioneSociale, Indirizzo, Telefono )Prodotti ( IDProdotto, Descrizione, IDFornitore )

Iscrizione

Numero {PK}DataIscrizionePagamento

Corso

IDCorso {PK}DescrizioneGiornoOraCosto

Cliente

IDCliente {PK}CodFiscaleNomeCognomeIndirizzoTelefono

RicevereEffettuare

N N1 1

16



Posto Passeggero1 NOccupare

Docente MateriaN NInsegnare

DaStazioneAStazione

Posti ( Carrozza, Fila, Numero, Caratteristiche )

Passeggeri ( Codice, Nome, Carrozza, Fila, Numero, DaStazione, AStazione )

AnnoScolastico

Docenti ( IDDocente, Nome, Cognome, DataAssunzione )

Materie ( IDMateria, Descrizione, Gruppo )

Assegnazioni ( IDDocente, IDMateria, AnnoScolastico )



Docenti ( IDDocente, Nome, Cognome, DataAssunzione )

Classi ( IDClasse, Classe, Sezione, Descrizione, Localizzazione )

Insegnamenti ( IDDocente, IDClasse, Materia, NumOre )

Docente ClasseN NInsegnare

MateriaNumOre

17



Addetto Negozio

1 1Dirigere

N 1

EssereAssegnato

Manager

Collaboratore

Coordinare

Addetti ( Matricola, Nome, Cognome, DataAssunzione, Qualifica, IDNegozio, Manager, DataAssegnazione )

Negozi ( IDNegozio, Indirizzo, Città, Manager )

Addetti ( Matricola, Nome, Cognome, DataAssunzione, Qualifica, IDNegozio,DataAssegnazione )

Negozi ( IDNegozio, Indirizzo, Città, Manager )

Supervisione ( Collaboratore, Manager )

DataAssegnazione



Locomotive ( IDMotrice, TipoMotrice, DataCostruzione, DataRevisione )

Carrozze ( IDCarro, TipoCarrozza, DataCostruzione, DataRevisione )

Composizione1 ( IDMotrice, IDCarro, NumTreno )

Composizione2 ( IDCarro, IDCarroTrainato, NumTreno )

Carrozza Locomotiva1 1Trainare

PrecedenteSuccessiva

Precedere

IDMotrice {PK}TipoMotriceDataCostruzioneDataRevisione

IDCarro {PK}TipoCarroDataCostruzioneDataRevisione

1

1

NumTrenoNumTreno

18



Prodotti ( Codice, Descrizione, Gruppo, Prezzo )

Composizione ( IDParte, IDComponente, Quantità, Lavorazione )

Parte

Essere Formata

N

N

Componente

Prodotto

Codice {PK}DescrizioneGruppoPrezzo

QuantitàLavorazione


Esercizi (1)

• Derivare le relazioni dal seguente modello E/R:

Capo

Coordinare

1

N

N

Dipendente

Matricola {PK}NomeDataAssunzione

1

Vendere

N

Reparto

IDReparto {PPK}IDNegozio {PPK}NomeRepartoTelefono

Negozio

IDNegozio {PK}Indirizzo Telefono

1

Prodotto

IDProdotto {PK}DescrizioneFornitore

N1

1

1

1N

Dirigere

Sovrintendere

Collaboratore

Direttore

Caporeparto

19


Esercizi (2)

• Costruire il modello E/R che ha originato il seguente schema di DB

Reparti( CodReparto , NomeReparto )Prodotti( CodProdotto , Descrizione, Prezzo, CodReparto)Vendite( Numero , Data, Quantità, CodProdotto)

• Costruire le relazioni per il problema delle ricette e ingredienti (Vedi Modello E/R)

• Costruire le relazioni per il problema dei dipendenti e le relative specializzazioni (Vedi ..)

• Costruire le relazioni per il problema dei prestiti di libri ai soci di una Biblioteca (Vedi ..)

• Costruire le relazioni per il problema di studenti, esami, corsi, docenti. Ogni corso ha un solo docente che in genere è titolare di più corsi. Un esame può essere ripetuto (Vedi .. )

• Costruire le relazioni per il problema del catalogo di una biblioteca (Documento, Soggetto, Autore, Genere, ... ) (Vedi E/R)

• Costruire le relazioni per il problema degli ordini ricevuti dai clienti e le relative fatture. A un ordine è associata una sola fattura (Vedi (E/R)

• Costruire le relazioni per il problema della progettazione aziendale: progetti, dipendenti, reparti (Vedi E/R)

Vedi “Algebra relazionale”

20

Dipendenze funzionali


Modello relazionale dei dati (1)

• Relazione

• Dipendenza funzionale

• Determinante

• Chiave candidata• Chiave composta

• Chiave primaria

• Chiave surrogata

• Chiave esterna

• Vincoli di chiave esterna• Forme normali

• Dipendenze multivalore

Terminologia

21



Caratteristiche delle tabelle

• Le righe contengono dati di una (sola) entità

• I valori delle colonne descrivono attributi delle entità

• Tutti i valori in una colonna sono del medesimo tipo

• Ogni colonna ha un unico nome

• Ogni campo contiene un solo valore

• L’ordine delle colonne non è rilevante

• L’ordine delle righe non è rilevante

• Non ci possono essere righe duplicate

RecordCampoFile

TuplaAttributoRelazione

RigaColonnaTabella



Prodotti

DamianoMontagnaSci discesa2051

DamianoMontagnaSci fondo2050

GianniCampingTenda doppio igloo2012

GianniCampingTenda igloo2010

MaryMareMaschera grande1011

MaryMareMaschera media1010

EnricoMarePinne medie1002

EnricoMarePinne piccole1001

CompratoreRepartoDescrizioneIDProdotto

Ordini

2020110103000

2814210023000

1212110013000

5614410022000

2412210012000

250250120121000

180180120101000

TotalePrezzoQuantitàIDProdottoIDOrdine

Le due tabelle

sono ben progettate?

22



Compratori








CompratoreRepartoDescrizionePrezzoQuantitàIDProdottoIDOrdine

Inglese2051Damiano

Inglese2050Damiano

Tedesco1011Mary

Laurea1011Mary

Tedesco1010Mary

Laurea1010Mary

SkillProdottoCompratore

Entrambe le tabelle hanno

problemi

Ottenuta dalla fusione delle due precedenti tabelle . Meglio due tabelle o una sola ?


Le dipendenze funzionali (1)

• AreaRet = Base * Altezza ( Base, Altezza ) � AreaRet• Totale = Quantità * Prezzo ( Quantità, Prezzo ) � Totale

• Si ha dipendenza funzionale tra attributi quando il valore di uno o piùattributi A determina univocamente il valore di un attributo B e si indica con A �� B– A determina funzionalmente B

– B dipende funzionalmente da A

– A è un determinante per B

• Sono di interesse le sole dipendenze funzionali non esprimibili matematicamente. Con queste cosa si fa?

23



IDProdotto � Descrizione

IDProdotto � Reparto

IDProdotto � Compratore

• IDProdotto �� ( Descrizione, Reparto, Compratore )

• Descrizione �� ( IDProdotto, Reparto, Compratore )

• Compratore �� Reparto

DamianoMontagnaSci discesa2051

DamianoMontagnaSci fondo2050



MaryMareMaschera grande1011




CompratoreRepartoDescrizioneIDProdottoProdotti

• Scoprire le dipendenze funzionali non èfacile!

• Attività simile alla progettazione concettuale dei dati



Ordini

IDProdotto � Prezzo

( IDOrdine, IDProdotto ) � Prezzo

• ( IDOrdine, IDProdotto ) �� ( Quantità, Prezzo, Totale )

• ( Quantità, Prezzo ) �� Totale

2020110103000

2814210023000

1212110013000

5614410022000

2412210012000

250250120121000

180180120101000


Quale delle due dipendenze ?

24



Dipendenti ( Matricola, Nome, Cognome, Stipendio, CodiceCapo, NomeCapo )

Matricola � Nome Cognome Stipendio CodiceCapoCodiceCapo � NomeCapo Matricola � NomeCapo transitivamente[Matricola ]++++ � Matricola Nome Cognome Stipendio CodiceCapo NomeCapo

Dipendenza transitiva : se A�B, B�C allora A�C. Si dice che A determina C transitivamente o che C dipende transitivamente da A

A+, chiusura di A date certe dipendenze funzionali , risponde alla domanda:

Quali sono tutti gli attributi che dipendono funzionalmente da A, in base a un insieme di dipendenze funzionali?


Chiavi

• Un insieme di attributi A si dice superchiave di una tabella se A determina funzionalmente tutti gli altri attributi della relazione

• Un insieme di attributi A si dice chiave candidata di una tabella se A è una superchiave minimale della tabella

• Chiave primaria di una tabella è una delle chiavi candidate

• Chiave surrogata di una tabella è una colonna aggiunta alla tabella, i cui valori sono attribuiti automaticamente dal DBMS, e che è usata come chiave primaria

[ A1 A2. . . An ]++++ è l’insieme di tutti gli attributi di R

se e solo se A1 A2. . . An è una superchiave per R

25

Vincoli di integrità


Valori nulli

• NULL indica l’assenza di informazione su un attributo, non applicabile in quella riga, valore ignoto . . .


1 Matematica Allevi

3 Informatica NULLNULL Economia Leoni

Studente Voto Corso

545 28 1

NULL 27 NULL653 25 1

768 24 NULL314 30 4


545 Rossi Maria NULLNULL Neri Anna 23/04/1982768 Verdi Giuseppe 12/02/1982


NULL Bruni Carla 01/12/1981314 Cavour Benso 05/10/1983

Studenti

Esami

Corsi

• Matricola di Neri e Bruni?

• Quando è nata Maria Rossi?• 768 ha meritato 24 in ?

• 27 in che materia? Studente?• Chi insegna Informatica ?• Qual è il codice di Economia ?

La presenza di valori nulli ha effetti diversi: in alcuni casi è tollerabile in altri no

26



• I valori degli attributi devono essere sottoposti ad opportuni vincoli di integritàsia internamente alla tabella che in relazione ai dati contenuti in altre tabelle

Studenti

Esami

Corsi

• Il voto 36 non è corretto • Il voto 27 lode è irregolare• La matricola 653 è duplicata

• Chi è lo studente 823 ?• Il corso 5 non esiste• Lo studente 653 ha sostenuto

due volte l’esame 4 con esitidifferenti ( 28 e 30 Lode )


545 Rossi Maria 12/05/83



314 Cavour Benso 05/10/83


1 Matematica Allevi

3 Informatica Gnudi4 Economia Leoni

Studente Voto Lode Corso

545 36 5

653 27 lode 1

653 30 lode 4

823 24 3653 28 4



• Vincoli di tupla– I vincoli di tupla esprimono condizioni che devono essere

soddisfatte dai valori di ciascuna riga indipendentemente dalle altre. Riguardano i domini degli attributi e la relativa semantica

– Definire il vincolo per gli attributi Voto e Lode in Esami

• Vincoli di chiave– Si impone alla chiave primaria di essere unica ed a valori non nulli

• Vincoli di integrità referenziale– Riguarda un attributo od un insieme di attributi che compare come

chiave esterna in una relazione e come chiave primaria in un’altra: i valori assunti da una chiave esterna devono essere assunti anche dalla corrispondente chiave primaria

27


Vincoli di integrità referenziale

Veicoli

Agenti

InfrazioniCodice Data Agente Articolo Prov Numero

554 26/10/2002 456 34 RM 4E5433876 15/10/2002 456 53 FI 2F7643856 12/10/2002 567 44 MI 2F7646


567 RSSMRO… Rossi Mario638 NREPRO…. Neri Piero

Prov Numero Proprietario Indirizzo

FI 2F7643 Verdi Piero Via TigliRM 1A2396 Verdi Piero Via TigliMI 2F7646 Luci Gino Via Aceri

Come si èarrivati a questa

situazione ?


Integrità referenziale

In una tabella, definita una chiave esterna e imposti i vincoli di integrità referenziale, il DBMS impedisce:

� L’inserimento di un record con un valore di chiaveesterna che non compare nella tabella madre associata

� La cancellazione di una riga, la cui chiave primaria èchiave esterna in altre tabelle, se nelle tabelle associateci sono righe con quel valore nella chiave esterna.

� La variazione dei valori dei campi di una riga se sonoviolate le precedenti condizioni

Tre possibili cause all’origine delleviolazioni all’integrità referenziale

28


Vincoli di integrità referenziale

Agenti

InfrazioniCodice Data Agente Articolo Prov Numero

554 26/10/2002 34 RM 4E5433876 15/10/2002 456 53 FI 2F7643856 12/10/2002 567 44 MI 2F7646


567 RSSMRO… Rossi Mario638 NREPRO…. Neri Piero

In Agente c’è un valore nullo. E’ un caso di

violazione dell’integritàreferenziale?

No. Non si tratta di un caso di viola-zione dell’integrità referenziale. L’integrità referenziale richiede solo che se in Agente c’è un valore questo deve comparire anche in Matricola.


Esercizi

Per ognuna delle basi di dati riportate identificar e: chiavi, chiavi esterne e vincoli di integrità

1. Data Base Multe :• Agenti ( Matricola, CodFis, Cognome, Nome ) • Infrazioni ( Codice, Agente, Data, Articolo, Prov, Numero ) • Veicoli ( Prov, Numero, Proprietario, Indirizzo )

2. Data Base Università :• Studenti ( Matricola, Cognome, Nome, DataNascita, CodiceFiscale )• Corsi ( Codice, NomeCorso, Docente )• Esami ( Studente, Corso, Voto, Lode, Data )

3. Data Base Banca1 :– Clienti ( Nome, Via, Città, NumConto )– Conti ( NumConto, Saldo )

4. Data Base Banca2 :• Agenzie ( NomeAgenzia, Indirizzo, Capitalizzazione )• Clienti ( NomeCliente, Indirizzo, Città )• Prestiti ( NomeAgenzia, NumeroPrestito, NomeCliente, Importo )• Depositi ( NomeAgenzia, NumeroDeposito, NomeCliente, Importo )

5. Data Base ContiTrattoria:• Ricevute ( Num, Data, Totale )• Dettaglio ( Num, Qta, Descrizione, Importo ) – ed anche l’alternativa per Dettaglio: • Dettaglio (Num, Riga, Qta, Descrizione, Importo)

29

Normalizzazione


Anomalie (1)

• La tabella Inventario presenta diverse anomalie:

– Se Mi8 si sposta da via Tonale bisogna aggiornare tutte le occorrenze di Mi8 in Inventario: anomalia di aggiornamento

– Se un magazzino si svuota vengono perse le informazioni sul suo indirizzo: anomalia di cancellazione

– Se viene aperto un nuovo magazzino in mancanza di merci a magazzino mancano le informazioni sul suo indirizzo: anomalia di inserimento

Inventario

Prodotto Magazzino Quantità IndirMag

545 Mi8 800 via Tonale 25545 Mi25 700 via Stelvio 35545 Roma2 356 via Ardeatina 3100 Roma3 245 via Salaria200 Mi25 230 via Stelvio 35545 Roma3 370 via Salaria100 Mi25 350 via Stelvio 35100 Mi8 720 via Tonale 25

Inventario èuna buona

tabella ?

30


Anomalie (2)

• Si intuisce che le anomalie sono causate dalla presenza di informazioni eterogenee in un’unica tabella. Possibile terapia: mettere le informazioni in tabelle separate

Inventario1

Prodotto Magazzino Quantità

545 Mi8 800545 Mi25 700545 Roma2 356100 Roma3 245200 Mi25 230545 Roma3 370100 Mi25 350100 Mi8 720

Magazzini

Codice IndirMag.

Mi8 via Tonale 25Mi25 via Stelvio 35

Roma2 via Ardeatina 3Roma3 via Salaria


Anomalie (3)

• Per eliminare le anomalie riscontrate in Inventario abbiamo scomposto lo schema:

– Inventario ( Prodotto, Magazzino, Quantità, IndirMag )

• Nei due schemi: – Inventario1 ( Prodotto, Magazzino, Quantità )

– Magazzini ( Codice, IndirMag )

• Le tabelle, ottenute per proiezione dalla tabella originale, devono permettere la ricostruzione delle informazioni originarie.

– Inventario1 Join Magazzini, con: Magazzino = Codice

– La congiunzione deve ricostruire tutte le informazioni originarie

– La congiunzione deve ricostruire solo le informazioni originarie

• Come procedere in modo sistematico per identificare possibili anomalie nello schema delle relazioni ?

31


Forme Normali (1)

1NF

2NF

3NF

BCNF

4NF

5NF


Forme Normali (2)

• Sintesi sulle diverse forme di normalizzazione

Trasformare i vincoli in funzione delle chiavi candidate e dei domini

5NFRare situazioni di vincoli sui dati

Mettere tutte le dipendenze multivalore in una tavola a se4NF

Dipendenze multivalore

BCNF: progettazione delle tabelle in modo che ogni determinante sia una chiave candidata

2NF, 3NF BCNF

Dipendenze Funzionali

Rispetto delle caratteristiche base del modello relazionale1NFDati non atomici

Principi di progettazioneForme Normali

Origine dell’anomalia

32


Forme Normali (3)

• La prima forma normale (1NF) , richiede che siano rispettate le regole fondamentali del modello relazionale. In una tabella:

– non ci sono righe uguali e tutte le righe contengono lo stesso numero di colonne

– le righe e le colonne non sono ordinate

– i valori che compaiono in una colonna sono omogenei per dominio e semplici, ossia gli attributi contengono informazioni elementari

• Considereremo la sola forma normale di Boyce-Codd , indicata in letteratura con l’acronimo BCNF (Boyce Codd Normal Form) e la 4NF


Condizione di Boyce e Codd (1)

• La Forma Normale di Boyce Codd (BCNF) garantisce l’assenza di anomalie causate dalle dipendenze funzionali

Una relazione R è in forma normale di Boyce e Codd ( BCNF ) se è in

prima forma normale e ogni determinante è una chiave candidata

• Anagrafica ( Nome, Cognome, Matricola, Nascita, Indirizzo, NumFigli, CodDip, NomeDip, IndirDip )

CodDip �� ( NomeDip, IndirDip )

• Inventario ( Prodotto, Magazzino, Quantità, IndirMag )

Magazzino �� IndirMag

Anagrafica non è in BCNF

Inventario non è in BCNF

33


Condizione di Boyce Codd (2)

• Nella tabella: – Inventario ( Prodotto, Magazzino, Quantità, IndirMagazzino )

• Valgono le dipendenze funzionali:

– Prodotto Magazzino � Quantita IndirMagazzino– Magazzino � IndirMagazzino

• Inventario non è in BCNF

• Abbiamo scomposto Inventario in due tabelle BCNF:

– Inventario ( Prodotto, Magazzino, Quantità)– Magazzini ( Magazzino, IndirMagazzino) Esiste un metodo

automatico per normalizzare o

bisogna procedere intuitivamente?


Scomposizione in BCNF

Algoritmo di scomposizione

1. Identifica tutte le dipendenze funzionali e le chiavi candidate

2. Se esiste una dipendenza funzionale il cui determinante non è chiave candidata:

A. Costruisci una nuova tabella con le colonne della dipendenza funzionale

B. Il determinante è la chiave primaria della tabella definita in A.

C. Costruisci un’altra tabella con: le colonne rimaste e una copia deldeterminante della dipendenza funzionale

D. Crea un vincolo di integrità referenziale tra le due tabelle

3. Ripeti il passo 2. sino a che tutti i determinanti di tutte le tabelle sono chiavicandidate

Se al passo 2. ci sono più dipendenze funzionali, inizia da quella con il maggiornumero di colonne

34


Scomposizione in BCNF (Esempio 1)

Prodotti ( IDProdotto, Descrizione, Reparto, Compratore )

• IDProdotto � ( Descrizione, Reparto, Compratore ) • Compratore � Reparto• Descrizione � ( IDProdotto, Reparto, Compratore )

Compratori ( Compratore, Reparto )

Prodotti ( IDProdotto, Descrizione, Compratore )

Non BCNF



Macchine ( Macchina , Tipo, PrezzoAcq )

Riparazioni ( Macchina, DataRipar, NumRipar, CostoRipar )

Riparazioni

27521/11/200534753500Trapano100

005/10/200534703500Trapano100

180015/09/2005346535000Tornio300

17820/08/200534603500Trapano100

25519/09/200534554500Levigatrice200

50025/07/200534503500Trapano100

CostoRiparDataRiparNumRiparPrezzoAcqTipoMacchina

Riparazioni ( Macchina, Tipo, PrezzoAcq, NumRipar, DataRipar, CostoRipar )

• Macchina � ( Tipo, PrezzoAcq )

• NumRipar � ( Macchina, Tipo, PrezzoAcq, DataRipar, CostoRipar )

Non BCNF

35



Sports ( IDSocio, Nome, Sport, Costo, Pagato )

• IDSocio � Nome

• ( IDSocio, Sport ) � Pagato

• Sport � Costo

Sports

400400SciGiuseppe400

200200ArrampicataEnrico300

400400SciFrancesco200

150150CalcioFrancesco200

0150CalcioGiovanni100

PagatoCostoSportNomeIDSocio

Non BCNF



Sports ( IDSocio, Nome, Sport, Costo, Pagato )

• IDSocio � Nome

• ( IDSocio, Sport ) � Pagato

• Sport � Costo

Soci ( IDSocio, Nome )

Temp ( IDSocio, Sport, Costo, Pagato )

Sports ( Sport, Costo )

Pagamenti ( IDSocio, Sport, Pagato )

Non BCNF

36



Prodotti ( IDProdotto, Descrizione, Reparto, Budget, Compratore )

• IDProdotto � ( Descrizione, Reparto, Budget, Compratore )

• Descrizione � ( IDProdotto, Reparto, Budget, Compratore )

• Compratore � ( Reparto, Budget )

• Reparto � Budget

Prodotti

DamianoBR_020MontagnaSci discesa2051

DamianoBR_020MontagnaSci fondo2050

GianniBR_010CampingTenda doppio igloo2012

GianniBR_010CampingTenda igloo2010

MaryBR_001MareMaschera grande1011

MaryBR_001MareMaschera media1010

EnricoBR_001MarePinne medie1002

EnricoBR_001MarePinne piccole1001

CompratoreBudgetRepartoDescrizioneIDProdotto

Non BCNF



Compratori ( Compratore, Reparto, Budget )

Prodotti ( IDProdotto, Descrizione, Compratore )

Reparti ( Reparto, Budget )

Compratori ( Compratore, Reparto )

Prodotti ( IDProdotto, Descrizione, Reparto, Budget, Compratore )

• IDProdotto � ( Descrizione, Reparto, Budget, Compratore )

• Descrizione � ( IDProdotto, Reparto, Budget, Compratore )

• Compratore � ( Reparto, Budget )

• Reparto � Budget Non BCNF

37


Scomposizione in BCNF - Esercizio 1

Ordini ( IDOrdine, IDProdotto, Quantità, Prezzo, Totale )

• ( IDOrdine, IDProdotto ) � ( Quantità, Prezzo, Totale )

• ( Quantità, Prezzo ) � Totale

Ordini

2020110103000

2814210023000

1212110013000

5614410022000

2412210012000

250250120121000

180180120101000


Ordini non è in BCNF



Dipendenti ( Matricola, Nome, Cognome, Stipendio,

CodiceCapo, NomeCapo )

• Matricola � ( Nome, Cognome, Stipendio, CodiceCapo,NomeCapo )

• CodiceCapo � NomeCapoDipendenti non è in BCNF

38



Film ( Titolo, Anno, Attore, IndirizzoAttore, NomeStudio, Produttore, IndirizzoProduttore )

• Titolo Anno � NomeStudio Produttore

• Attore � IndirizzoAttore

• Produttore � IndirizzoProduttore

• [Titolo Anno ]+ ??

Film non è in BCNF



Sono in BCNF ?

Dipendenti ( Matricola, Nome, Cognome, Residenza, Reparto, SedeReparto, Stipendio, Capo, CognomeCapo )

Matricola � Nome, Cognome, Residenza, Reparto, Stipendio, Capo

Reparto � SedeReparto

Capo � CognomeCapo

Reparti ( Reparto, SedeReparto )

Capi ( Capo, CognomeCapo ) ( Capo è Matricola )

Dipendenti ( Matricola, Nome, Cognome, Residenza, Reparto, Stipendio, Capo )

39



Anagrafica ( Nome, Cognome, Matricola, Nascita, Indir, NumFigli, CodDip, NomeDip, IndirDip, Stipendio )

• Matricola � ( Nome, Cognome, Nascita, Indir, NumFigli,CodDip, NomeDip, IndirDip, Stipendio )

• CodDip � ( NomeDip, IndirDip )

Inventario ( Prodotto, Magaz, Quantità, IndirizzoMag )

• ( Prodotto, Magaz ) � Quantità

• Magaz � IndirizzoMag

Non sono in BCNF



– Anagrafica ( Nome, Cognome, Dipart, Ufficio, Stipendio, Città )

– Dipartimenti ( Nome, Indirizzo, Città )

Nome Indirizzo Città

Amministrazione Via Tito Livio, 27 MilanoDirezione Via Tito Livio, 27 MilanoDistribuzione Via Segre, 9 RomaProduzione Piazza Lavater, 3 TorinoRicerca Via Morone, 6 Milano

Nome Indirizzo Città

Amministrazione Via Tito Livio, 27 MilanoDirezione Via Tito Livio, 27 MilanoDistribuzione Via Segre, 9 RomaProduzione Piazza Lavater, 3 TorinoRicerca Via Morone, 6 Milano

Nome Cognome Dipart Ufficio Stipendio Città

Carlo Bianchi Produzione 20 36 TorinoCarlo Rossi Direzione 80 MilanoFranco Neri Distribuzione 45 NapoliGiuseppe Verdi Amministrazione 20 40 RomaLorenzo Lanzi Direzione 7 73 GenovaMarco Franco Produzione 20 46 RomaMario Rossi Amministrazione 10 45 MilanoPaola Borroni Amministrazione 75 40 Venezia

Nome Cognome Dipart Ufficio Stipendio Città

Carlo Bianchi Produzione 20 36 TorinoCarlo Rossi Direzione 80 MilanoFranco Neri Distribuzione 45 NapoliGiuseppe Verdi Amministrazione 20 40 RomaLorenzo Lanzi Direzione 7 73 GenovaMarco Franco Produzione 20 46 RomaMario Rossi Amministrazione 10 45 MilanoPaola Borroni Amministrazione 75 40 Venezia

Anagrafica e Dipartimenti

sono in BCNF ?

40



Film (Titolo, Anno, Attore, NomeStudio, Produttore, Lunghezza, aColori)

• Titolo Anno � NomeStudio Produttore Lunghezza aColori

• Chiave candidata ?

Film è in BCNF ?


Ricostruzione delle informazioni

Anagrafica ( Nome, Cognome, Matricola, Nascita, Indir, NumFigli, CodDip, NomeDip, IndirDip, Stipendio )

è stata decomposta nelle due tabelle:

• Anagrafica ( Nome, Cognome, Matricola, Nascita, Indir, NumFigli, CodDip, Stipendio )

• Dipartimenti ( CodDip, NomeDip, IndirDip )

Teorema

Se una tabella viene scomposta con il metodo descritto la tabella originaria può essere recuperata esattamente combinando, tramitejoin , le righe delle nuove tabelle

L’informazione originaria è recuperabile esattamente?

41


Forme Normali - 4NF

• La 4NF vuole prevenire le anomalie causate dalla presenza di attributi multivalore . Situazioni di questo tipo si hanno quando un attributo è determinante di un altro che può avere molti valori

– una persona � i suoi fratelli– uno studente � le sue abilità– un cibo � i vini adatti a quel cibo

Compratori

Inglese2051Damiano

Inglese2050Damiano

Tedesco1011Mary

Laurea1011Mary

Tedesco1010Mary

Laurea1010Mary


Prodotto � CompratoreCompratore �� Skill


Forme Normali - 4NF

• Se tali situazioni sono trattate in tabelle a sè stanti non ci sono anomalie di sorta, mentre le tabelle che contengono dipendenze multivalore e altri attributi evidenziano anomalie di aggiornamento.

Studenti ( Matricola, Abilità ) Studenti_1 ( Matricola, Abilità, Fratelli ) Studenti_2 ( Matricola, Abilità, Cognome )

• Studenti: Matricola � Abilità;

• Studenti_1: Matricola � Abilità; Matricola � Fratelli

• Studenti_2: Matricola � Abilità; Matricola � Cognome

Studenti OK

42


Forme Normali - 4NF

• Studenti: Matricola � Abilità;

• Studenti_1: Matricola � Abilità; Matricola � Fratelli

• Studenti_2: Matricola � Abilità; Matricola � Cognome

• Studenti_1– Studenti_1A ( Matricola, Abilità )

– Studenti_1B ( Matricola, Fratelli )

• Studenti_2– Studenti_2A ( Matricola, Abilità )– Studenti_2B ( Matricola, Cognome )

Anomalie

Anomalie


Forme Normali - 4NF

• Le dipendenze multivalore devono essere trattate isolatamente, in tabelle a sé stanti

Compratori

Inglese2051Damiano

Inglese2050Damiano

Tedesco1011Mary

Laurea1011Mary

Tedesco1010Mary

Laurea1010Mary


Prodotto � CompratoreCompratore �� Skill

43


4NF - Esempio

Compratori

2051Damiano

2050Damiano

1011Mary

1010Mary

ProdottoCompratoreProdotto � Compratore

IngleseDamiano

TedescoMary

LaureaMary

SkillPersonaSkillsCompratore �� Skill

• Normalizzazione della tabella Compratori:

Compratori ( Compratore, Prodotto, Skill )

– Prodotto � Compratore– Compratore � Skill


Forme normali

La 1NF richiede che siano rispettate le regole di base del

modello relazionale

La 2NF vieta le dipendenze da un

sottoinsieme propriodi una chiave

La 3NF vieta le dipendenze transitive

da una chiave

• 1NF: requisiti base del modello relazionale

• 2NF: Una relazione è 2NF se è 1NF e non ci sono attributi non chiaveche dipendono parzialmente dalla chiave

• 3NF: Una relazione è 3NF se è 2NF e non ci sono attributi non chiaveche dipendono transitivamente dalla chiave

• BCNF: Una relazione è BCNF se è 1NF e ogni determinante è chiave candidata

44


Esercizi

• Le informazioni sugli esami sono memorizzate secondo uno dei due seguenti schemi:

Esami-1 ( Corso, Matricola, Data, Voto, NomeCorso )

Esami-2 ( Corso, Matricola, Data, VotoInCifre, VotoInLettere )Definite, in entrambi i casi, le dipendenze funzionali e individuate le eventuali violazioni alla BCNF e dire se sono in 2NF o 3NF. Se già non lo sono, portarle in BCNF

• Una tabella contiene per ogni riga il codice del libro, 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 BCNF

• Portare in BCNF la tabella Materiali definita dallo schema: Materiali ( Codice, Descrizione, Fornitore, Prezzo, IndirizzoFornitore, Quantità,

PosizioneInMagazzino, Acciaio, NomeAcciaio, ResistenzaAcciaio, NomeFornitore )

Normalizzazione in pratica

45


Come si attua la normalizzazione (1)

---------------------------------------------------- SELECT ... INTO Macchine FROM ... -- Sintassi SQLServer per creare tabella Macchine--------------------------------------------------SELECT DISTINCT Macchina, Tipo, PrezzoAcqINTO MacchineFROM Riparazioni;

Riparazioni ( Macchina, Tipo, PrezzoAcq, NumRipar, DataRipar, CostoRipar )

Macchine ( Macchina , Tipo, PrezzoAcq )

Riparazioni_1 ( Macchina, DataRipar, NumRipar, CostoRipar )

Riparazioni esiste già

MYSQL:CREATE TABLE... SELECT ...


Come si attua la normalizzazione (2)

---------------------------------------------------- Creazione di Riparazioni_1--------------------------------------------------SELECT Macchina, DataRipar, NumRipar, CostoRiparINTO Riparazioni_1FROM Riparazioni;

---------------------------------------------------- Ricostruzione della tabella Riparazioni --------------------------------------------------CREATE VIEW Riparazioni AS SELECT M.Macchina, M.Tipo, M.PrezzoAcq,

R. DataRipar, R.NumRipar, R.CostoRiparFROM Riparazioni_1 R JOIN Macchine M ON

R.Macchine = M.Macchine;

46


Pro e Contro la normalizzazione

• Vantaggi

– Elimina le anomalie di inserimento, ...

– Riduce la ridondanza dei dati

• Elimina i problemi di inconsistenza dei dati

• Limita lo spazio occupato dai dati

• Svantaggi

– Necessità di costruire interrogazioni più complicate

– Maggior carico di lavoro per il DBMS


Ragioni per non normalizzare

Cliente ( Codice, Nome, Via, Città, Provincia, CAP )

– CAP � ( Città, Provincia )

• Tabelle come Cliente , in genere, non vengono normalizzate perché il CAP non cambia mai (o quasi ... )

• In genere è fortemente consigliata la normalizzazione ad eccezione di quelle situazioni nelle quali i dati cambiano raramente ( per maggior semplicità applicativa )

• I database di sola lettura non sono normalizzati anzi, di norma, sono denormalizzati

47


Riprogettazione di un database

• Per esaminare le diverse tabelle: contare le righe e esaminare la natura delle colonne di ogni tabella

– TABLESAMPLE, TOP n, LIMIT n, . . .

• Esaminare i dati ed intervistare gli utenti per accertarsi dellapresenza di:

– Dipendenze multivalore, Multicolonna– Dipendenze funzionali– Chiavi e chiavi esterne

• Controllare la validità dell’integrità referenziale e i vincoli sui dati:

– Valori non nulli– Valori duplicati– . . .


Esercizi

• Creare con Access un database di nome Test .

• Creare le tabelle Prodotti , Ordini , OrdiniProdotti e Compratori, vedi diapositive 40 e 41, e popolarle con gli stessi dati.

• Normalizzare le tabelle creando le nuove tabelle con opportuni comandi SELECT, senza eliminare le tabelle originarie.

• Creare opportune viste logiche per accedere alle nuove tabelle come se fossero le tabelle originarie.

• Modificare la tabella Prodotti inserendo alcune righe per avere: righe con descrizioni duplicate, prodotti senza descrizione, compratori che operano in più di un reparto. Inserire in Ordini una riga che faccia riferimento a un prodotto inesistente. Controllare tutte le situazioni anomale e controllare l’esistenza della dipendenza funzionale:IDProdotto IDOrdine � Prezzo

48


Fine della presentazione


Terza forma normale

• Ci sono situazioni nelle quali la BCNF non è raggiungibile e si rinuncia alla BCNF per non avere perdita di dipendenze funzionali

Interventi

– Chirurgo �� Reparto– Paziente Reparto �� Chirurgo

A ogni paziente è assegnato un chirurgo del reparto

[ Paziente Reparto ] chiave

Interventi non è in BCNF

LanzettaChir. GeneraleVerdi

VeronesiChir. OncologicaBianchi

RomanoChir. GeneraleBianchi

RomanoChir. GeneraleRossi

De BakeyCardiochirurgiaRossi

ChirurgoRepartoPaziente

49


Terza forma normale

• Per effetto della normalizzazione:

• La dipendenza funzionale [ Paziente Reparto ] �� Chirurgo è persa:

Volendo registrare il fatto (errato) che Neri è stato operato da Lanzetta in cardiochirurgia, ci si limiterebbe a inserire Neri e Lanzetta nella prima tabella e solo la congiunzione delle tabelle permetterebbe di evidenziare l’errore

• Si preferisce lasciare la tabella nella forma originale (3NF)

LanzettaVerdi

VeronesiBianchi

RomanoBianchi

RomanoRossi

De BakeyRossi

ChirurgoPaziente

Chir. GeneraleLanzetta

Chir. OncologicaVeronesi

Chir. GeneraleRomano

CardiochirurgiaDe Bakey

RepartoChirurgo


Terza forma normale

• Interventi (Paziente, Reparto, Chirurgo) è in 3NF

– Paziente Reparto � Chirurgo

– Chirurgo �� Reparto

– [ Paziente Reparto ] è superchiave

– Reparto ∈ [ Paziente Reparto ]

Una relazione è in 3NF se è in 1NF e ogni attributo non chiave dipende funzionalmente da una chiave candidata. In altre parole. Se A1 . . An � B, allora deve valere una delle due condizioni: B fa parte di una chiave oppure A1..An è una superchiave

Il Modello Relazionale - · 745 Neri Anna 23/04/1982 768 Verdi Giuseppe 12/02/1982 614 Rossi...

Documents

Transcript of Il Modello Relazionale - · 745 Neri Anna 23/04/1982 768 Verdi Giuseppe 12/02/1982 614 Rossi...