Corso di Informatica (Basi di Dati)

Corso di Informatica(Basi di Dati) Lezione 5 (14 gennaio 2009) Modello Relazionale: vincoli di integrità Da: Atzeni, Ceri, Paraboschi, Torlone - Basi di Dati Lucidi del Corso di Basi di Dati 1, Prof. Carlo Batini, Laurea in Informatica, AA 2007-2008

Vincoli di integrità Le strutture del modello relazionale (viste nella lezione precedente) permettono di organizzare i dati relativi alla realtà di interesse. Non è vero però che qualsiasi istanza su di uno schema (così definito, fino a questo punto) di una Base di Dati rappresenti informazioni corrette per l’applicazione. Si consideri ad esempio la seguente istanza sullo schema di Base di Dati definito nella lezione precedente (con le tre tabelle Studenti, Corsi ed Esami), modificato aggiungendo l’attributo Lode alla tabella Esami.

Matricola Cognome Nome DataNascita 200768 Verdi Fabio 12/02/1972 937653 Rossi Luca 10/10/1971 Codice Titolo Docente 937653 Bruni Mario 01/12/1971 01 Analisi Giani 03 Chimica Melli MatricolaStudente Voto Lode CodiceCorso 200768 36 05 04 Chimica Belli 937653 28 lode 01 937653 30 lode 04 276545 25 01 Vincoli di integrità Studenti Esami Corsi

Vincoli di integrità • Quali sono gli aspetti scorretti nell’istanza precedente? • nella tabella Esami • il valore di Voto, in corrispondenza dello studente con matricola 200768, è 36 (e il voto deve essere <= 30!) • il valore dell’attributo Lode, per lo studente con matricola 937653, non è nullo nonostante il valore di Voto sia 28 (e la lode si ha solo per un voto pari a 30!)

Vincoli di integrità • Quali sono gli aspetti scorretti nell’istanza precedente? • nella tabella Esami • lo studente con matricola 200768 ha sostenuto un esame con codice 05 che però non compare nella tabella Corsi • lo studente con matricola 276545 non compare nella tabella Studenti e quindi non si hanno informazioni su di esso • nella tabella Studenti ci sono due studenti che hanno lo stesso numero di matricola

Vincoli di integrità • Un vincolo di integrità è una proprietà che deve essere soddisfatta da tutte le istanze della Base di Dati al fine di rappresentare un’informazione corretta. • Un vincolo è un predicato che associa ad un’istanza un valore booleano: • VERO se l’istanza è corretta • FALSO se l’istanza NON è corretta

Vincoli di integrità • I vincoli di integrità sono dunque proprietà dello schema, cioè si riferiscono a tutte le istanze dello schema e: • permettono una rappresentazione più accurata della realtà • contribuiscono alla qualità dei dati, cioè fanno in modo che i dati rappresentino correttamente la realtà • portano in fase di progettazione alla definizione di uno schema di qualità

Vincoli di integrità • I vincoli di integrità sono possono essere di tipo: • intrarelazionale, cioè definiti all’interno di una relazione (o tabella) • interrelazionale, cioè definiti tra due o più relazioni (o tabelle)

Vincoli di integrità • I vincoli di tipo intrarelazionale che vedremo sono: • vincoli su singolo valore di attributo (o vincoli di dominio) • vincoli di ennupla (o di tupla) • vincoli di chiave e di chiave primaria Il vincolo di tipo interrelazionale che vedremo è il vincolo di integrità referenziale

Vincoli su singolo valore I vincoli su singolo valore coinvolgono un singolo attributo di una relazione. Ad esempio il valore di Voto delle ennuple della tabella Esami, definito sul dominio degli interi, può assumere valori che vanno da 18 a 30. Il vincolo è quindi esprimibile dalla seguente espressione booleana: (Voto >= 18) AND (Voto <= 30)

Vincoli di ennupla I vincoli di ennupla coinvolgono l’insieme dei valori di un’intera ennupla di una relazione. Ad esempio le ennuple della relazione Esami devono soddisfare il seguente vincolo: (Voto = 30) OR NOT (Lode = lode)

Vincoli su singolo valore e di ennupla Un vincolo su singolo valore o di ennupla è soddisfatto se ogni ennupla dell’istanza della relazione, sulla quale è definito, ha valori che soddisfano il vincolo stesso.

Vincoli di chiave e di chiave primaria La motivazione di un vincolo di chiave e di chiave primaria è la necessità di disporre di informazioni che permettano di rappresentare ogni oggetto della realtà di interesse tramite un’unica ennupla. In altre parole si ha la necessità di identificare in maniera univoca le ennuple di una relazione.

Definizione di superchiave e di chiave Data una relazione (o tabella) avente schema R(X), dove X è l’insieme degli attributi, un insieme K contenuto in X è una superchiave per R se R non contiene due ennuple distinte t1 e t2 tali che t1[K]= t2[K]. Si dice che K è superchiave minimale (o semplicemente chiave) se non contiene al suo interno nessun’altra superchiave, cioè se nessun sottoinsieme di K è esso stesso una superchiave.

Corso Matricola Cognome Nome DataNascita Ing Inf 276545 Rossi Mario 5/12/1978 Ing Mecc 485745 Rossi Mario 3/11/1976 Ing Mecc 200768 Neri Piero 10/07/1979 Ing Inf 587614 Neri Mario 3/11/1976 587544 Rossi Piero 5/12/1978 Ing El Superchiave e chiave (esempi) Nella tabella Studenti3 l’attributo Matricola è una chiave in quanto è una superchiave (non ci sono due studenti con lo stesso numero di matricola) minimale (la superchiave è composta da un solo attributo)... Studenti3

Corso Matricola Cognome Nome DataNascita Ing Inf 276545 Rossi Mario 5/12/1978 Ing Mecc 485745 Rossi Mario 3/11/1976 Ing Mecc 200768 Neri Piero 10/07/1979 Ing Inf 587614 Neri Mario 3/11/1976 587544 Rossi Piero 5/12/1978 Ing El Superchiave e chiave (esempi) … L’insieme di attributi (Cognome, Nome, DataNascita) è una superchiave minimale e quindi è un’altra chiave. Infatti nessun altro sottoinsieme di (Cognome, Nome, DataNascita) è una superchiave... Studenti3

Corso Matricola Cognome Nome DataNascita Ing Inf 276545 Rossi Mario 5/12/1978 Ing Mecc 485745 Rossi Mario 3/11/1976 Ing Mecc 200768 Neri Piero 10/07/1979 Ing Inf 587614 Neri Mario 3/11/1976 587544 Rossi Piero 5/12/1978 Ing El Superchiave e chiave (esempi) … L’insieme di attributi (Matricola, Cognome, Nome, DataNascita) è una superchiave ma non è una chiave in quanto contiene le due chiavi (Matricola) e (Cognome, Nome, DataNascita). Studenti3

Corso Matricola Cognome Nome DataNascita Ing Inf 276545 Rossi Mario 5/12/1978 Ing Mecc 485745 Rossi Mario 3/11/1976 Ing Mecc 200768 Neri Piero 10/07/1979 Ing Inf 587614 Neri Mario 3/11/1976 587544 Rossi Piero 5/12/1978 Ing El Superchiave e chiave (esempi) … L’insieme di attributi (Cognome, Corso) è una chiave. Studenti3

Superchiave e chiave NOTA BENE! Data una relazione avente schema R(X), dove X è l’insieme degli attributi, si ha che X è superchiave per definizione. Infatti tutte le ennuple della relazione sono per definizione distinte.

Vincolo di chiave Dato uno schema di relazione R(X), un vincolo di chiave su un sottoinsieme K di X, impone che ogni istanza di R(X) deve essere tale che, per ogni coppia di ennuple t1 e t2, si ha t1[K] diverso da t2[K].

Vincolo di chiave (esempio) Sullo schema della tabella Studenti3 si possono definire ad esempio due vincoli di chiave rispettivamente su K1=(Matricola) e su K2=(Cognome,Nome,DataNascita). L’istanza di Studenti3 delle slide precedenti (usata per definire superchiave e chiave) rispetta chiaramente tali due vincoli. L’istanza precedente non rispetterebbe invece un vincolo di chiave definito su K3=(Cognome, Nome) in quanto ci sono due ennuple che su K3 hanno valore (Rossi, Mario)

Vincolo di chiave • Le chiavi sono importanti perché: • permettono di identificare gli oggetti della base di dati • accedere agli oggetti della base di dati • correlare le informazioni tra relazioni diverse

Vincolo di chiave (esempio) La slide successiva riporta la Base di Dati (della lezione precedente) in cui in rosso sono evidenziati il vincolo di chiave definito per la tabella Studenti (sull’attributo Matricola) e quello per la tabella Corsi (sull’attributo Codice). Il legame tra Studenti e Corsi (tabella Esami) avviene grazie alla definizione dei due vincoli di chiave che consentono infatti di legare ogni ennupla di Esami a un’unica ennupla di Studenti e a un’unica ennupla di Corsi.

Codice Matricola Titolo Cognome Docente Nome DataNascita 01 276545 Rossi Analisi Maria Giani 25/11/1971 03 485745 Neri Chimica Anna Melli 23/04/1972 MatricolaStudente Voto CodiceCorso 200768 04 Chimica Verdi Belli Fabio 12/02/1972 276545 28 01 587614 276545 Rossi 27 04 Luca 10/10/1971 200768 24 04 Vincolo di chiave (esempio) Studenti Esami Corsi

Valori nulli • In una Base di Dati si ha spesso a che fare con informazione incompleta e quindi con valori nulli. • Un valore nullo può essere dovuto al fatto che: • il valore esiste ma è sconosciuto (tipo 1) • il valore è inesistente (tipo 2) • il valore è senza informazione (non si conosce oppure non esiste, non si sa nulla…) (tipo 3) Un DBMS non riesce purtroppo a distinguere il preciso significato di un valore nullo

Città IndirizzoPrefettura Roma Via Manzoni Firenze Tivoli Chiasso Valori nulli (esempio) Ad esempio si consideri un‘istanza della semplice tabella Prefetture sotto riportata Prefetture

Valori nulli (esempio) …Il valore nullo di Firenze è di tipo 1 in quanto a Firenze esiste sicuramente una Prefettura e quindi in questo caso non si conosce il suo indirizzo. Il valore nullo relativo a Tivoli è di tipo 2 poiché Tivoli non è provincia e quindi non esiste una Prefettura. Infine per Chiasso non si sa nulla (tipo 3) dal momento che si trova in Svizzera e in Svizzera non si sa se ci sono le Prefetture.

Valori nulli Come si rappresentano i valori nulli nel modello relazionale? Possibilità 1: usare particolari valori del dominio dell’attributo. Ad esempio: 0, la stringa nulla, etc. Questa possibilità è un po’ rischiosa in quanto si potrebbero utilizzare valori che poi diventano significativi…

Valori nulli Come si rappresentano i valori nulli nel modello relazionale? Possibilità 2: si usa un particolare valore denotato con NULL. Di conseguenza, data una ennupla t, si ha che il valore t[A] in corrispondenza dell’attributo A è un valore del dominio di A oppure il valore NULL.

Chiavi e valori nulli I valori nulli possono riguardare una chiave o parte di essa? Sì, anche se in presenza di valori nulli non è permesso alla chiave di svolgere correttamente le sue due funzioni di (1) identificare le ennuple della Base di Dati e (2) di determinare i riferimenti con altre relazioni. Quindi la presenza di valori nulli nelle chiavi deve essere limitata!

Vincolo di chiave primaria Dalle considerazioni della slide precedente emerge la necessità di definire un vincolo di chiavein cui i valori nulli non siano ammessi. Questo nuovo tipo di vincolo prende il nome di chiave primaria (primary key). Per uno schema di relazione R(X) si possono definire più di un vincolo di chiave e un solo vincolo di chiave primaria Nella Base di Dati della slide successiva, si possono definire due chiavi primarie rispettivamente su Matricola di Studenti e su Codice di Corsi (colonne in rosso).

Codice Matricola Titolo Cognome Docente Nome DataNascita 276545 01 Rossi Analisi Maria Giani 25/11/1971 485745 03 Chimica Neri Anna Melli 23/04/1972 MatricolaStudente Voto CodiceCorso 04 200768 Chimica Verdi Fabio Belli 12/02/1972 276545 28 01 587614 276545 Rossi 27 04 Luca 10/10/1971 200768 24 04 Vincolo di chiave primaria (esempio) Studenti Esami Corsi

Vincolo di integrità referenziale Dati due schemi di relazione R1(X1) e R2(X2), un vincolo di integrità referenziale (chiave esterna o foreign key) di R1 (detta interna) rispetto a R2 (detta esterna o master), impone che un sottoinsieme K1 di X1 sia legato ad un sottoinsieme K2 di X2, definito come chiave primaria per R2. In altre parole, tale vincolo impone che ogni ennupla t1 in R1 abbia valori su K1 che compaiano come chiave primaria in R2. Negli esempi successivi le chiavi esterne sono evidenziate in blu, mentre le chiavi primarie sono evidenziate in rosso.

Studenti Codice Matricola Titolo Cognome Docente Nome DataNascita 276545 01 Rossi Analisi Maria Giani 25/11/1971 03 485745 Neri Chimica Anna Melli 23/04/1972 Esami MatricolaStudente Voto CodiceCorso 200768 04 Chimica Verdi Fabio Belli 12/02/1972 276545 28 01 587614 276545 Rossi 27 04 Luca 10/10/1971 200768 24 04 Corsi Vincolo di integrità referenziale (esempio1)

Infrazioni Codice Data Vigile Provincia Numero 34321 1/2/95 3987 MI 39548K 53524 4/3/95 3295 TO E39548 64521 5/4/96 3295 PR 839548 73321 5/2/98 9345 PR 839548 Auto Nome Provincia Numero Cognome Mario MI 39548K Rossi Mario TO E39548 Rossi Luca PR 839548 Neri Vincolo di integrità referenziale (esempio2) Attenzione! Quando si definiscono chiavi esterne che coinvolgono più di un attributo occorre rispettare l’ordine degli attributi. In questo caso infatti (Provincia, Numero) in Infrazioni fa riferimento a (Provincia,Numero) in Auto presi in questo ordine.

Vincolo di integrità referenziale (violazione) • Cosa succede quando l’aggiunta, la modifica o la cancellazione di una ennupla viola un vincolo di integrità referenziale? • Sono possibili tre meccanismi compensativi: • rifiuto dell’operazione • eliminazione in cascata • introduzione di valori nulli

Vincolo di integrità referenziale (violazione) Ad esempio nella Base di Dati dell’esempio precedente (Infrazioni e Auto) se si tenta di eliminare da Auto una ennupla a cui è associata un’infrazione in Infrazioni, viene violato il vincolo di integrità referenziale. Le slide successive mostrano il comportamento di due dei meccanismi di compensazione in seguito alla cancellazione della ennupla (TO, E39548, Rossi, Mario) da Auto.

Infrazioni Codice Data Vigile Provincia Numero 34321 1/2/95 3987 MI 39548K 53524 4/3/95 3295 TO E39548 64521 5/4/96 3295 PR 839548 73321 5/2/98 9345 PR 839548 Auto Nome Provincia Numero Cognome Mario MI 39548K Rossi Mario TO E39548 Rossi Luca PR 839548 Neri Vincolo di integrità referenziale (violazione) Eliminazione in cascata della ennupla (53524, 4/3/95, 3295, TO, E39548) da Infrazioni

Infrazioni Codice Data Vigile Provincia Numero 34321 1/2/95 3987 MI 39548K 53524 4/3/95 3295 NULL NULL 64521 5/4/96 3295 PR 839548 73321 5/2/98 9345 PR 839548 Auto Nome Provincia Numero Cognome Mario MI 39548K Rossi Mario TO E39548 Rossi Luca PR 839548 Neri Vincolo di integrità referenziale (violazione) Introduzione di valori nulli nella ennupla (53524, 4/3/95, 3295, TO, E39548) in Infrazioni

Corso di Informatica (Basi di Dati)