Sistemi di Basi di Dati (appunti) 1.2a ed.

Appunti
di
Sistemi di Basi di Dati
1.2ª ed.
per le lauree triennali
di Informatica
a.a. 2010-11
Prof. Ezio Lefons
Un Corso Online di Basi di Dati è alla URL: http://db-course.di.uniba.it/ATutor4/login.php
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
i
Indice
Appunti di Sistemi di Basi di Dati 1.2ª ed. per le lauree triennali di Informatica a.a.
2010-11 Prof. Ezio Lefons .................................................................................... 1 Strutture informative: DATO ....................................................................................1 Strutture informative: CAMPO ..................................................................................2 Strutture informative: RECORD ................................................................................ 3 Strutture informative: ARCHIVIO.............................................................................. 4 Sistemi di archiviazione ........................................................................................... 6 Sistemi di archiviazione: limiti ................................................................................ 7 Sistemi di archiviazione: necessità .......................................................................... 8 Strutture informative: BASE DI DATI ........................................................................ 9 Sistemi di Gestione di Basi di Dati ........................................................................ 10 Il DBMS ...................................................................................................10 Struttura dei DBMS ............................................................................................... 12 Sistema di comunicazione: Linguaggi ................................................................... 13 Linguaggi di Basi di Dati: Tipi .............................................................................. 14 Sistema di comunicazione: Interfacce ................................................................... 15 Sistema di utilità .................................................................................................... 16 I modelli di dati ...................................................................................................... 17 Il modello relazionale ............................................................................................ 18 Modello Relazionale: DOMINIO ............................................................................. 19 Modello Relazionale: RELAZIONE.......................................................................... 20 Relazione: definizioni ............................................................................................ 21 Relazione: proprietà ............................................................................................... 22 Relazione: Attributo e Schema .............................................................................. 23 Nome degli Attributi .............................................................................................. 24 Relazione: Vincolo di Integrità .............................................................................. 25 Vincolo di dominio ...................................................................................25 Vincolo d’integrità semantica...................................................................25 Vincolo di chiave ......................................................................................27 Integrità dell’entità ...................................................................................28 Vincolo di Integrità Referenziale .............................................................29 Violazione dei vincoli d’integrità .......................................................................... 30 Politiche di reazione alle violazioni dell’integrità referenziale ............................. 31 Operatori Relazionali ............................................................................................. 32 Operatori insiemistici (´ ,¨ ,-) ......................................................................... 33 Esempi ......................................................................................................34 Ridenominazione (r)..............................................................................................35 Ridenominazione ......................................................................................35 Esempio ....................................................................................................36 Selezione (s) .......................................................................................................... 37 Formula proposizionale ......................................................................................... 38 Proiezione (p) ........................................................................................................39 Selezione e Proiezione ........................................................................................... 41 Indice
a.a 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
ii
Prodotto (*) .......................................................................................................... 42 Join (ä) ...................................................................................................................44 Theta-Join (äF) .......................................................................................................45 Equi-join (äF=) ....................................................................................................... 46 Join naturale (&, ä) ................................................................................................47 Join completo ......................................................................................................... 48 Join non completo .................................................................................................. 49 Join vuoto ............................................................................................................... 50 0 \ card (R1 ä R2) \ card (R1)*card (R2) .............................................................51 Join esterni (ï, , , ïF , …) .................................................................................. 52 Semijoin ( , , F , F)........................................................................................... 53 Divisione (%, /)...................................................................................................... 54 Esempio Divisione.............................................................................................. 55 Divisione ................................................................................................................56 Riepilogo Operatori Relazionali ............................................................................ 57 esempi + complessi ................................................................................................ 58 VISTE (Relazioni Derivate) .................................................................................. 59 Viste virtuali e materializzate ................................................................................ 60 Viste: motivazioni .................................................................................................. 61 Viste ....................................................................................................................... 62 Viste come strumento di programmazione ............................................................ 63 Viste e aggiornamenti ............................................................................................ 64 SQL ........................................................................................................................65 Sommario della sintassi di SQL () ......................................................................... 66 DDL ....................................................................................................................... 67 SQL Domini Elementari ........................................................................................ 68 Tabelle ......................................................................................................70 Vincoli Intrarelazionali .............................................................................71 Vincoli Interrelazionali .............................................................................72 Violazione del vincolo FOREIGN KEY .......................................................73 DML - Interrogazioni............................................................................................. 75 Struttura della query SQL .........................................................................75 Esempio ....................................................................................................76 Operatore LIKE ..........................................................................................77 Proiezione: duplicati .................................................................................78 Espressioni nella target list .......................................................................78 Ordinamento .............................................................................................78 Join ...........................................................................................................79 inner join ...................................................................................................79 outer join ...................................................................................................79 Variabili (alias) .........................................................................................80 Operatori aggregati ...................................................................................81 Interrogazioni con Raggruppamento ........................................................82 Predicati sui Gruppi ..................................................................................83 Interrogazioni Insiemistiche .................................................................................. 84 Indice
a.a 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
iii
Interrogazioni Nidificate ........................................................................................ 85 Asserzioni ..............................................................................................................87 Viste (Virtuali) ....................................................................................................... 87 Ciclo di vita dei Sistemi Informativi...................................................................... 89 Progettazione guidata dai dati ................................................................................ 90 Il modello E-R (Entità-Relationship) ..................................................................... 91 I costrutti del modello E-R..................................................................................... 91 Entità.........................................................................................................92 Associazione (relationship) ......................................................................93 Attributo ...................................................................................................94 Cardinalità della relationship ....................................................................96 Cardinalità dell’attributo ........................................................................100 Identificatore dell’entità..........................................................................100 Generalizzazione ....................................................................................103 Proprietà di copertura della generalizzazione.........................................103 Gerarchie di generalizzazione ................................................................106 Ereditarietà .............................................................................................107 Progettazione Logica ........................................................................................... 108 Ristrutturazione EER in E-R ..................................................................109 Attributi multivalore ...............................................................................111 Traduzione ER vs Relazionale ............................................................................. 112 Entità e associazioni molti-a-molti .........................................................112 Associazioni ternarie (molti-a-molti) .....................................................113 Associazioni uno-a-molti........................................................................113 Entità con identificatore esterno .............................................................114 Associazioni uno-a-uno ..........................................................................115 Indice
a.a 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
1
Strutture informative: DATO
“Cristoforo Colombo”
“125”
??????
In che via abita Gianni e a quale numero civico ?
Dato: ciò che è immediatamente presente alla conoscenza,
prima di ogni elaborazione; rappresentazione convenzionale, che individua univocamente un’informazione.
Informazione: notizia, dato o elemento che consente di
avere conoscenza più o meno esatta di fatti, situazioni.
Lo stesso dato può fornire informazioni diverse
Chi ha scoperto le Americhe?
Quant’è il peso di Cristoforo Colombo in libbre ?
Dati diversi possono fornire la stessa informazione
Anni vissuti da Cristoforo Colombo: AnniVita [55];
Anni(DataMorte-DataNascita) [19/05/1506-?/?/1451]
Parte I: Basi di Dati
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
2
Strutture informative: CAMPO
Il Campo (Item) è la struttura informativa più semplice
Il campo ha un nome (attributo, proprietà) e un valore
Città di residenza
‘Bari’
nome
valore
Il campo può essere:
• semplice
• composto
• ripetuto
indirizzo
città
via
civico
Ing. Bianchi Roberto via ... 5664477 / 5016688
Il valore di un campo può essere:
• dato valido
• dato non valido (nullo: sconosciuto, inesistente, …)
Ulteriori attributi del campo, oltre al nome e al valore,
sono: l’indicatore di esistenza, la chiave di accesso, il tipo,
la lunghezza, il range, i sinonimi, ecc.
Parte I: Basi di Dati
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
3
Strutture informative: RECORD
Il record è una struttura informativa costituita da un
insieme di campi logicamente correlati che individuano un
elemento in una popolazione di oggetti, descritti da un
insieme di proprietà.
Esempio
nome :
Matricola :
Cognome :
Nome :
Data di nascita :
Indirizzo :
§:
valore
444
Rossini
Domenico
24/05/70
via Cavour 13
§
Schema (tracciato)
del record
record
Dal punto di vista fisico, il record può essere a lunghezza
• fissa
• variabile
• indefinita
Parte I: Basi di Dati
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
4
Strutture informative: ARCHIVIO
Un Archivio (file) è un insieme di record omogenei
(descritti dallo stesso schema) identificato da un nome.
Principali operazioni sugli archivi:
Creazione
Inserimento
Cancellazione
Aggiornamento
Interrogazione
Ordinamento
Si può definire sugli archivi il tipo di accesso
•
•
•
•
sequenziale
diretto
con chiave
...
Dal punto di vista fisico esistono:
Primari
File
Secondari (Indice)
Il file indice non può esistere senza un file primario di
riferimento
Parte I: Basi di Dati
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
5
Esempio
ARCHIVIO (DIPENDENTI)
I#
Matr
1
100
2
111
3
222
4
555
5
101
6
201
7
501
8
333
9
301
10
99
Cognome
Albanese
Binolini
Brio
Burro
Cialda
Corradi
Corradi
Rossini
Rota
Zizzi
Nome
Franco
Luigi
Giuseppe
Filippo
Mario
Maria
Leo
Luisa
Maria
Silvio
Ind.
…
…
…
…
…
…
…
…
…
…
Residenza
Bari
Cassano
Bari
Molfetta
Molfetta
Bari
Bari
Cassano
Bari
Molfetta
INDICE (RESIDENZA)
Residenza
Record
Bari
1, 3, 6, 7, 9
Cassano
2, 8
Molfetta
4, 5, 10
Parte I: Basi di Dati
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
6
Sistemi di archiviazione
Il software che permette di gestire archivi di dati a livello
di Sistema Operativo è il Sistema di archiviazione (Filing
System, File System) e consente le seguenti operazioni sui
file:
Creazione
Cancellazione
Organizzazione
Lettura
Scrittura
Modifica
generalmente dall’interno di un linguaggio di programmazione.
Parte I: Basi di Dati
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
7
Sistemi di archiviazione: limiti
• I dati sono organizzati in insiemi indipendenti senza
possibilità di definire relazioni fra di essi
• È possibile gestire una sola registrazione per volta
• Occorre scrivere un programma anche per effettuare
elaborazioni di tipo semplice
• La struttura logica dell’archivio deve essere dichiarata
in tutti i programmi che ne fanno uso. Ciò comporta
- difficoltà a tener traccia della struttura per i nuovi
programmi
- in caso di dati riservati (sottoinsieme) bisogna duplicare il file (inconsistenza)
• File secondari e strutture ausiliarie per agevolare
l’accesso sono specifiche per l’applicazione
• Ogni modifica della struttura di dati o dei meccanismi di
accesso comporta una modifica anche dell’applicazione
• I meccanismi di protezione e di recovery vanno realizzati
esplicitamente.
Parte I: Basi di Dati
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
8
Sistemi di archiviazione: necessità
• Integrazione dei dati
- rendere disponibili i dati per scopi generali e non
legati ad una applicazione specifica
- limitare la ridondanza
- aumentare l’efficienza (ottimizzazione globale)
• Flessibilità
- seguire l’evoluzione tecnologica
- seguire l’evoluzione delle specifiche
• Indipendenza fisica
- consentire la modifica delle strutture ausiliarie
- consentire il cambiamento dei dispositivi fisici
- consentire di cambiare la locazione dei dati nei
sistemi distribuiti
- …
• Indipendenza logica
- consentire modifiche dello schema logico
- la gestione dello schema deve dipendere dal sistema
e non dai programmi applicativi.
Parte I: Basi di Dati
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
9
Strutture informative: BASE DI DATI
Una base di dati è un insieme di dati (organizzati in archivi)
non duplicati, logicamente correlati, utilizzabili contemporaneamente da diversi utenti in linea.
Architettura a tre livelli di base di dati:
LIVELLO
ESTERNO
VISTA
ESTERNA
●●●
VISTA
ESTERNA
mapping
esterno/concettuale
LIVELLO
CONCETTUALE
SCHEMA LOGICO
(CONCETTUALE)
mapping interno/concettuale
LIVELLO
INTERNO
SCHEMA INTERNO
BASE DI DATI MEMORIZZATA
Parte I: Basi di Dati
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
10
Sistemi di Gestione di Basi di Dati
Il software responsabile della creazione, della gestione e
della cancellazione delle basi di dati è il
Sistema di Gestione di Basi di Dati o DBMS (DataBase
Management System).
Il DBMS non è parte del Sistema Operativo: utilizza le funzioni del Sistema di Archiviazione per gestire i propri file.
Il DBMS può essere utilizzato contemporaneamente sia da
utenti programmatori che da utenti finali
Il DBMS permette di manipolare i dati ad un alto livello
Il DBMS
• Consente la descrizione dei dati secondo un modello
• Consente visioni differenti dell’informazione
• L’utente accede ai soli dati di suo interesse specifico
• Assicura la consistenza dei dati anche in presenza di una
loro a volte necessaria duplicazione. La modifica dei dati
comporta l’aggiornamento automatico di tutte le copie
• Garantisce la sicurezza dei dati. L’accesso all’informazione è consentito solo all’utente autorizzato.
• Sincronizza l’accesso concorrente ai dati
• Provvede alle funzioni di recovery.
Parte I: Basi di Dati
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
Applicazione 1
Applicazione 2
Archivio 1
Archivio 2
Applicazione 1
Applicazione 2
Definizione dati 1
Definizione dati 2
11
#
Applicazione n
Archivio n
#
Applicazione n
Definizione dati n
Sistema Operativo
Archivio 1
Archivio 2
#
Archivio n
Applicazione 1
Applicazione 2
#
Applicazione n
Parte I: Basi di Dati
Sistema di Gestione
di Basi di Dati
(DBMS)
Definizione Dati
Sistema Operativo
Base di
Dati
(Schema)
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
12
Struttura dei DBMS
In un DBMS si individuano tre componenti principali:
• Il sistema di comunicazione
• Il sistema di gestione
• Il sistema di utilità
Il sistema di comunicazione definisce i linguaggi e le
interfacce del DBMS
Il sistema di gestione è l’insieme dei moduli che realizzano,
con l’ausilio del filing system, la definizione e la gestione
dei dati, l’accesso ad essi e la loro protezione
Il sistema di utilità è costituito da un insieme di programmi
di utilità di ausilio (al DBA) per gestire il sistema di basi di
dati
Parte I: Basi di Dati
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
13
Sistema di comunicazione: Linguaggi
Un linguaggio di Basi di Dati consente la
• definizione dei dati: DDL (Data Definition Language)
• definizione delle viste: VDL (View D L)
• definizione della memorizzazione: SDL (Storage D L)
• manipolazione dei dati: DML (Data Manipulation L)
o DDL, VDL e DML sono generalmente contenuti in un
unico linguaggio
o La definizione della memorizzazione è tenuta separata ed
utilizzata dallo staff del DBA (Data Base Administrator)
Parte I: Basi di Dati
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
14
Linguaggi di Basi di Dati: Tipi
Vi sono due tipi di DML:
• DML di alto livello o non procedurale
- set di istruzioni interattive
- linguaggio di interrogazione (query language)
- istruzioni incapsulate (embedded) in un linguaggio di
programmazione
• DML di basso livello o procedurale
- sempre incapsulato in un linguaggio di programmazione
o I DML di alto livello sono set-at-a-time o set-oriented
DML in quanto possono recuperare più record con una
singola istruzione
o I DML di basso livello sono record-at-a-time DML in
quanto recuperano ed elaborano un record per volta
Il linguaggio di programmazione viene detto
Linguaggio ospite (Host Language) e
il DML Linguaggio specializzato per la gestione dati.
Parte I: Basi di Dati
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
15
Sistema di comunicazione: Interfacce
Le interfacce fornite dal DBMS possono comprendere:
• Interfacce a menù per la navigazione (guidano l’utente
nella formulazione delle interrogazioni)
• Interfacce a moduli (mostrano un modulo all’utente con
i campi da compilare)
• Interfacce grafiche d’utente (mostrano all’utente un
diagramma che, con un dispositivo di puntamento, guida
l’utente a formulare l’interrogazione)
• Interfacce in linguaggio naturale
• Interfacce per utenti parametrici (mnemonico, per
abbreviare i tempi)
• Interfacce per il DBA (comandi privilegiati per creare
account e autorizzazioni)
Parte I: Basi di Dati
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
16
Sistema di utilità
I programmi che aiutano il DBA a gestire il sistema,
svolgono le seguenti funzioni
• Caricamento per caricare file esistenti e specificare le
conversioni di formato
• Backup
• Riorganizzazione dei file
• Monitoraggio delle prestazioni
Parte I: Basi di Dati
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
17
I modelli di dati
• Il Modello dei Dati caratterizza il livello concettuale
(logico) e quello esterno dei DBS (DataBase System)
• È definito da regole precise e permette di esprimere le
proprietà sia statiche che dinamiche dei dati
Evoluzione dei modelli
Modello Gerarchico
Modello Reticolare
Modello Relazionale
Modelli Object-Oriented
o I modelli gerarchico e reticolare sono più vicini alle
strutture fisiche di memorizzazione, mentre il modello
relazionale è più astratto:
- nel modello relazionale si rappresentano solo valori
—anche i riferimenti fra dati in strutture (relazioni)
diverse sono rappresentati per mezzo dei valori
stessi;
- nei modelli gerarchico e reticolare si utilizzano
riferimenti espliciti (puntatori) tra i record.
o Più recentemente, è stato introdotto il modello a oggetti.
Parte II: Il Modello Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
18
Il modello relazionale
Proposto da E. F. Codd nel 1970 per favorire l’indipendenza dei dati, è disponibile come modello logico nei DBMS
reali solo dal 1981 (non è facile implementare l’indipendenza con efficienza e affidabilità!).
Si fonda sul concetto matematico di relazione.
Nelle basi di dati, le relazioni sono rappresentate in modo
naturale tramite tabelle.
I principali concetti del modello relazionale sono
Dominio
Relazione
Attributo
Schema di relazione
Vincolo di integrità.
Parte II: Il Modello Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
19
Modello Relazionale: DOMINIO
Un dominio D è un insieme di valori atomici (indivisibili).
Esempio
Città = {Firenze, Matera, Sassari, Chieti, Terni, …},
Regione = {Basilicata, Toscana, Sardegna, Umbria, …}
Tel = l’insieme dei numeri telefonici a 10 cifre validi in Italia,
Cod# = valori compresi fra 1 e 20000,
Orario = l’insieme delle ore e dei minuti in un giorno.
Queste sono definizioni logiche dei domini.
Per ogni dominio, occorre specificare oltre al suo nome (D)
anche il tipo dei suoi elementi (e/o formato)
Esempio
Tipo del dominio Città: stringa di caratteri,
Regione: stringa di caratteri,
Tel: stringa di caratteri con formato
ddd/ddddddd dove d è una cifra decimale,
Cod#: numero intero fra 1 e 20000,
Orario: stringa di caratteri della forma oo,mm.
Parte II: Il Modello Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
20
Modello Relazionale: RELAZIONE
Siano D1, …, Dn domini (non necessariamente distinti).
• Il Prodotto Cartesiano D1 * D2 * # * Dn è l’insieme
delle n-ple (ennuple, tuple) ordinate
{(v1, v2, …, vn) | v1aD1, v2aD2, …, vnaDn}
• Una relazione r su D1, …, Dn è un sottoinsieme del prodotto cartesiano D1*#*Dn
r • D1*D2*#*Dn.
Esempio
D1 = {1,2},
D2 = {x, y, z},
D1*D2 = {(1, x), (l, y), (l, z), (2, x), (2, y), (2, z)},
r = {(1, x), (l, z), (2, y)} • D1*D2.
forma tabellare:
D1*D2:
Parte II: Il Modello Relazionale
1
1
1
2
2
2
x
y
z
x
y
z
r • D1*D2:
1
1
2
x
z
y
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
21
Relazione: definizioni
Ogni relazione definita su n domini D1, …, Dn ha grado n.
Il prodotto cartesiano di n domini ha grado n. (È una relazione di grado n.)
Sia R il nome di una relazione r su D1, D2, …, Dn.
Il numero N di n-ple della relazione R è la cardinalità della
relazione R:
card(R) = N
|R| = N
card(D1*#*Dn) = card(D1)*#*card(Dn)
card(R) \ card(D1*#*Dn).
Se i domini sono finiti, allora la cardinalità del prodotto
cartesiano è finita.
Se i domini sono finiti, allora ogni relazione su di essi
definita ha cardinalità finita.
Parte II: Il Modello Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
22
Relazione: proprietà
La relazione è un insieme di n-ple ordinate. Pertanto,
• non c’è ordinamento fra le n-ple
• le n-ple sono distinte fra di loro
• ciascuna n-pla è ordinata e ha n componenti (pari al
grado): l’i-esimo valore proviene dall’i-esimo dominio.
Nelle basi di dati,
per motivi pratici (le memorie sono finite),
ogni relazione memorizzata deve essere finita.
Esempio
relazione Treno • Cod# * Città * Città * Orario * Orario
200
Firenze
Chieti
08,36
17,50
500
Matera
Firenze
11,20
23,30
444
3333
Terni
Matera
Chieti
Terni
17,00
17,00
19,00
23,30
Vi sono domini con due (o più) ruoli diversi, distinguibili
attraverso la posizione:
La struttura di relazione è posizionale.
Parte II: Il Modello Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
23
Relazione: Attributo e Schema
Una formulazione più conveniente consiste nell’associare
un nome—detto nome di attributo—a ciascun dominio.
• La coppia {nome di attributo, dominio} è detto attributo.
• L’insieme degli attributi di una relazione R ne costituisce
lo schema.
• Data una relazione R con insieme di attributi X = {Al, A2
… , An}, lo schema della relazione è denotato R(X).
L’uso degli attributi permette di denotare le componenti di
ogni n-pla per nome invece che per posizione.
L’uso del termine “tupla” anziché “n-pla” evidenzia il fatto che
l’ordine degli attributi e dei loro valori non è realmente importante. Indicando con D l’insieme unione di tutti i domini di uno
schema X, la funzione totale dom: X → D che associa ad ogni
nome di attributo Ai il corrispondente dominio, consente di definire la relazione R come un insieme di trasformazioni {t1, t2, …, tN}
da R in D, dette tuple. Per ogni tupla t, risulta t[Ai]adom(Ai) per
1 § i § n.
Esempio
Treno(Cod#, CittaP, CittaA, OraP, OraA)
Cod#
200
500
444
3333
Parte II: Il Modello Relazionale
CittàP
Firenze
Matera
Terni
Matera
CittaA
Chieti
Firenze
Chieti
Terni
OraP
08,36
11,20
17,00
17,00
OraA
17,50
23,30
19,00
23,30
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
24
Nome degli Attributi
I nomi degli attributi debbono essere tutti distinti nello
schema di una relazione.
Tale limitazione non si applica nell’ambito dell’intera Base
di Dati. Cioè, due distinti attributi possono avere lo stesso
nome, purchè appartengano a relazioni differenti. In tal
caso, il riferimento all’attributo avviene utilizzando la
cosiddetta notazione punto (dot notation)
Esempio
R.A, S.A
IMPIEGATO (I#, Nome, Direttore, I#)
IMPIEGATO (I#, Nome, Direttore, ID#)
DIPARTIMENTO (D#, Nome, ID#)
IMPIEGATO.Nome, IMPIEGATO.ID#,
DIPARTIMENTO.Nome, DIPARTIMENTO.ID#
Parte II: Il Modello Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
25
Relazione: Vincolo di Integrità
Tipi di vincolo:
dominio
integrità semantica
chiave
integrità dell’entità
integrità referenziale
dipendenza funzionale
dipendenza multivalore
Vincolo di dominio
Il valore di ciascun attributo A deve
(a) essere atomico
(b) nel dominio di A dom(A).
Vincolo d’integrità semantica
a. Non tutte le combinazioni del prodotto cartesiano dei
domini hanno senso dal punto di vista semantico
Esempio
Treno(Cod#, CittaP, CittaA, OraP, OraA)
TRENO
Cod#
200
CittàP
Firenze
CittaA
Chieti
OraP
08,36
OraA
17,50
500
Matera
Firenze
11,20
23,30
444
Temi
Chieti
17,00
19,00
3333
Matera
Terni
17,00
23,30
400
200
Firenze
Matera
Firenze
Chieti
17,10
19,00
19,00
18,30
Parte II: Il Modello Relazionale
?
?
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
26
b. Può essere necessario imporre vincoli generali sulla base
di dati
Esempi
lo stipendio dell’impiegato non deve essere superiore a quello
del suo capo
il numero max di ore lavorative settimanali per l’impiegato è 40
sullo schema TRENO(Cod#, CittaP, CittaA, OraP, OraA)
deve essere CittaP ≠ CittaA e OraA > OraP
sullo schema PAGAMENTO(P#, Data, Importo, Iva, Netto)
deve essere Netto = Importo − Iva
Questi vincoli possono essere specificati e imposti usando
un linguaggio di specificazione dei vincoli (di uso generale,
espressioni booleane).
Possono essere utilizzati meccanismi particolari quali
trigger e asserzioni.
Parte II: Il Modello Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
27
Vincolo di chiave
Identificazione delle tuple
Esempio
IMPIEGATO
I#
Cognome
Nome
Reparto
201
500
333
400
200
Primo
Primo
Secondo
Secondo
Primo
Aldo
Aldo
Maria
Aldo
Ugo
Produzione
Produzione
Vendite
Produzione
Magazzino
Data
Assunzione
05/12/1998
06/05/1999
10/03/1997
06/05/1999
05/12/1998
a. I# identifica gli impiegati
b. L’insieme degli attributi (attributo composto)
{Cognome, Nome, Assunzione} identifica gli impiegati
Siano R una relazione con schema R(X) e K un sottoinsieme degli attributi di R, cioè K¥X.
K è superchiave per R se ß t1, t2aR, con t1≠t2, risulta
tl[K]≠t2[K], (cioè, R non contiene tuple distinte che abbiano
gli stessi valori in K—vincolo di univocità)
K è chiave per R se è una superchiave minimale (cioè, non
contiene un’altra superchiave)
Pertanto, la superchiave K è una chiave di R se ßAaK ®t1,
t2aR, con t1≠t2, risulta t1[K−A] = t2[K−A].
Parte II: Il Modello Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
28
La chiave è una proprietà (invariante rispetto al tempo)
dello schema di relazione che deve sussistere in ogni stato
di relazione
Esistenza delle chiavi
Le relazioni sono insiemi e pertanto non possono contenere
tuple uguali. Ogni relazione R(X) ha come superchiave X.
Poiché il grado della relazione R è finito, ogni relazione ha
(almeno) una chiave.
Chiavi candidate
Chiave primaria.
Integrità dell’entità
Il vincolo di integrità dell’entità stabilisce che nessun
valore di chiave primaria può essere nullo.
L’esistenza della chiave garantisce l’accessibilità a ciascun
dato (nome della relazione, valore della chiave, nome
dell’attributo).
Le relazioni sono associabili fra loro tramite i valori delle
chiavi (associazione basata sui valori).
Parte II: Il Modello Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
29
Vincolo di Integrità Referenziale
VINCOLI INTERRELAZIONALI (con Violazione)
IMPIEGATO
Imp# Nome
101
Rossi
102
Bianchi
103
Verdi
104
Neri
112
Bianchi
PROGETTO
Prog# Nome Capo_prog#
p21
Libri
102
p28
Riviste
110
p33 Tecniche
102
p38
Collane
NULL
p43
Hobby
102
RAPPRESENTAZIONE DIAGRAMMATICA:
IMPIEGATO
Imp#
RELAZIONE RIFERITA
ASSEGNAZIONE
Nome
Imp#
Prog#
ASSEGNAZIONE
Imp# Prog#
101
p43
103
p34
104
p33
104
p21
106
p21
NULL
p28
103
p33
←
RELAZIONE REFERENTE
PROGETTO
Prog#
Nome
Capo_prog#
VINCOLI INTRARELAZIONALI
IMPIEGATO
Imp# Nome
101
Rossi
102
Bianchi
103
Verdi
104
Neri
112
Bianchi
Direttore#
102
102
103
104
102
IMPIEGATO
Imp#
Nome
Direttore#
ALTRO ESEMPIO
PERSONA
CF
Nome
Parte II: Il Modello Relazionale
Indirizzo
NomePadre
CFpadre
NomeMadre
CFmadre
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
30
Violazione dei vincoli d’integrità
Le istanze delle relazioni debbono essere coerenti con i
vincoli (integrità). Quest’ultimi vengono verificati dal DBMS
dopo ogni operazione di
• Inserimento
• Cancellazione
• Aggiornamento
Inserimento
• Possibili violazioni per tutti i tipi di vincolo. Se un
inserimento viola uno o più vincoli, l’opzione di default
consiste nel rifiutare l’inserimento
Cancellazione
• Può violare solo l’integrità referenziale (solo se la cancel-
lazione viene effettuata nella relazione riferita)
Aggiornamento (Modifica)
• Equivale ad una cancellazione seguita da un inserimento.
Parte II: Il Modello Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
31
Politiche di reazione alle violazioni
dell’integrità referenziale
operazione sull’attributo riferito
• Standard (RESTRICT, rifiutata)
• No Action (permessa, possibile non integrità)
conseguenza sull’attributo referente
• A cascata (propagazione)
• Valore di Default
• Valore Null
Esempio
PROGETTO
Prog# Nome Capo_prog#
p21
Libri
102
p28
Riviste
110
p33 Tecniche
102
p34
Astri
110
p43
Hobby
102
ASSEGNAZIONE
Imp# Prog#
101
p43
103
p34
104
p33
104
p21
106
p21
107
p28
103
p33
Parte II: Il Modello Relazionale
cancellazione
propagazione
cancellazione
a cascata
PROGETTO
Prog# Nome Capo_prog#
p28
Riviste
110
p33 Tecniche
102
p34
Astri
110
p43
Hobby
102
ASSEGNAZIONE
Imp# Prog#
101
p43
103
p34
104
p33
107
p28
103
p33
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
32
Operatori Relazionali
Insieme di operatori
• su relazioni
• che producono relazioni
• e quindi possono essere la base per espressioni complesse
Operatori dell’algebra relazionale:
• unione, intersezione, differenza
• ridenominazione
• selezione
• proiezione
• join (join naturale, prodotto cartesiano, theta-join)
Parte III: Algebra Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
33
Operatori insiemistici (´ ,¨ ,-)
• le relazioni sono insiemi
i risultati debbono essere relazioni (insiemi di ennuple
omogenee)
Quindi
• è possibile applicare gli operatori insiemistici (unione,
intersezione, differenza) solo a coppie di relazioni definite
sugli stessi attributi.
Parte III: Algebra Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
34
Esempi
LAUREATI
Matricola
7274
7432
Cognome
Rossi
Neri
Età
37
39
9824
Verdi
38
Matricola
7274
7432
Cognome
Rossi
Neri
Età
37
39
Matricola
9297
7432
9824
Cognome
Neri
Neri
Verdi
Età
56
39
38
9824
9297
Verdi
Neri
38
56
LAUREATI
Matricola
7274
7432
9824
Cognome
Rossi
Neri
Verdi
Età
37
39
38
QUADRI
QUADRI
Matricola
9297
7432
9824
Cognome
Neri
Neri
Verdi
Età
56
39
38
Cognome
Rossi
Neri
Verdi
Età
37
39
38
LAUREATI ´ QUADRI
LAUREATI ¨ QUADRI
Matricola
Cognome
7432
Neri
9824
Verdi
Età
39
38
LAUREATI - QUADRI
Matricola
Cognome
7274
Rossi
Età
37
LAUREATI
Matricola
7274
7432
9824
QUADRI
Matricola
9297
7432
9824
Parte III: Algebra Relazionale
Cognome
Neri
Neri
Verdi
Età
56
39
38
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
35
Ridenominazione (r)
Esempio
PATERNITÀ
Padre
Adamo
Adamo
Abramo
Abramo
Figlio
Caino
Abele
Isacco
Ismaele
MATERNITÀ
Madre
Eva
Eva
Sara
Agar
Figlio
Caino
Set
Isacco
Ismaele
PATERNITÀ ? MATERNITÀ ??
Ridenominazione
• operatore monadico (“unario”)
• intuitivamente, “modifica lo schema” lasciando inalterata
l’istanza dell’operando
• permette di superare le limitazioni imposte agli operatori
insiemistici
Esempio
PATERNITÀ
Padre
Adamo
Adamo
Abramo
Abramo
Parte III: Algebra Relazionale
rGENITORE _ PADRE (PATERNITÀ)
Figlio
Caino
Abele
Isacco
Ismaele
Genitore
Adamo
Adamo
Abramo
Abramo
Figlio
Caino
Abele
Isacco
Ismaele
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
36
Esempio
PATERNITÀ
Padre
Adamo
Adamo
Abramo
Abramo
MATERNITÀ
Madre
Eva
Eva
Sara
Agar
Figlio
Caino
Abele
Isacco
Ismaele
Figlio
Caino
Set
Isacco
Ismaele
rGENITORE _ PADRE (PATERNITÀ) ´ rGENITORE _ MADRE (MATERNITÀ)
Genitore
Adamo
Adamo
Abramo
Abramo
Eva
Eva
Sara
Agar
Figlio
Caino
Abele
Isacco
Ismaele
Caino
Set
Isacco
Ismaele
Esempio
IMPIEGATO
Cognome
Rossi
Neri
Agenzia
Roma
Milano
OPERAIO
Cognome
Verdi
Bruni
Stipendio
45
53
Fabbrica
Latina
Monza
Salario
33
32
rSEDE, RETRIBUZIONE _ AGENZIA, STIPENDIO (IMPIEGATO) ´ rSEDE, RETRIBUZIONE _ FABBRICA, SALARIO (OPERAIO)
Cognome
Rossi
Neri
Verdi
Bruni
Parte III: Algebra Relazionale
Sede
Roma
Milano
Latina
Monza
Retribuzione
45
53
33
32
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
37
Selezione (s)
• operatore monadico
• produce un risultato che
-
ha lo stesso schema dell’operando
-
contiene un sottoinsieme delle ennuple dell’operando
Esempio
IMPIEGATO
Cognome
Rossi
Neri
Verdi
Rossi
Nome
Mario
Luca
Nico
Marco
Età
25
40
36
40
Stipendio
2.000,00
3.000,00
4.500,00
3.900,00
sEtà < 30 v Stipendio > 4.000,00 (IMPIEGATO)
Cognome
Rossi
Verdi
Nome
Mario
Nico
Età
25
36
Stipendio
2.000,00
4.500,00
Il risultato di una selezione contiene le ennuple dell’operando che soddisfano la condizione di selezione.
card(sF (R)) \ card(R)
La selezione produce decomposizioni orizzontali
Parte III: Algebra Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
38
Formula proposizionale
• r(X) : relazione
• F : formula proposizionale su X:
- ottenuta combinando con i connettivi logici
º(OR), ª (AND), æ (NOT),
- atomi del tipo AqB o Aqc con
o A e B attributi in X
o c costante “compatibile” con dom(A), e
o q operatore di confronto (=, ], >, <, ^, \).
È definito un valore di verità per F su ciascuna ennupla t:
- AqB è vera su t se t[A] è in relazione q con t[B]
- Aqc è vera su t se t[A] è in relazione q con c
- Fl º F2, Fl ª F2 e æFl hanno l’usuale significato
la selezione sF (r) di r rispetto a F
contiene le ennuple t di r su cui F è vera
Parte III: Algebra Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
39
Proiezione (p)
Dati
- una relazione con schema R(X) e
- un sottoinsieme di attributi Y ¥ X
la proiezione di R su Y è così definita
pY (R) = {t[Y] | taR}
Esempio
IMPIEGATO
Matricola
7309
5998
9553
5698
Cognome
Neri
Neri
Rossi
Rossi
Filiale
Napoli
Milano
Roma
Roma
Stipendio
55,00 K
64,00 K
44,00 K
64,00 K
Per tutti gli impiegati:
- Matricola e Cognome
- Cognome e Filiale
p Matricola, Cognome (IMPIEGATO)
Matricola
7309
5998
9553
5698
Cognome
Neri
Neri
Rossi
Rossi
p Cognome, Filiale (IMPIEGATO)
Cognome
Neri
Neri
Rossi
Filiale
Napoli
Milano
Roma
La proiezione produce decomposizioni verticali
Parte III: Algebra Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
40
card(pY(R)) \ card(R)
Se Y è superchiave di R, allora la proiezione su Y di R
contiene esattamente tante tuple quante ne ha R, cioè
card(pY (R)) = card(R)
Per la proiezione non vale la commutatività
pY (pZ (R)) = pY (R),
purché Y ¥ Z.
(La proiezione consente di determinare i domini attivi)
Parte III: Algebra Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
41
Selezione e Proiezione
Spesso utilizzate in combinazione nelle interrogazioni
Esempio
Q1: matricola e cognome degli impiegati che guadagnano più di 50K.
IMPIEGATO
Matricola
7309
5998
9553
5698
Cognome
Neri
Neri
Rossi
Rossi
Filiale
Napoli
Milano
Roma
Roma
Stipendio
55,00 K
64,00 K
44,00 K
64,00 K
pMatricola, Cognome(sStipendio>50 (IMPIEGATO))
Matricola
7309
5998
9553
5698
Cognome
Neri
Neri
Rossi
Rossi
Commutatività di selezione e proiezione
pY (sFZ (R)) = sFZ (pY (R)),
se Z¥Y
pY (sFZ (R)) = pY (sFZ (pZY (R))),
Parte III: Algebra Relazionale
altrimenti.
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
42
Prodotto (*)
Combinando selezione e proiezione, possiamo estrarre
informazioni da una relazione
Non possiamo però correlare informazioni presenti in
relazioni diverse
Siano R1(X1) e R2(X2) due schemi di relazioni.
Definiamo il Prodotto (Prodotto Cartesiano) di Rl e R2 la
relazione sullo schema X1X2 :
R1 * R2 = {t1 ) t2 su X1X2 | t1aR1 e t2aR2}
Esempio
R1
R2
A
a1
a2
B
b1
b1
C
c1
c2
D
d1
d1
E
e1
e2
c1
c2
d1
d1
e2
e1
D
d1
d1
d1
d1
d1
d1
d1
d1
E
e1
e2
e2
e1
e1
e2
e2
e1
R1*R2
A
a1
a1
a1
a1
a2
a2
a2
a2
Parte III: Algebra Relazionale
B
b1
b1
b1
b1
b1
b1
b1
b1
C
c1
c2
c1
c2
c1
c2
c1
c2
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
43
Se n e m sono rispettivamente il grado di Rl e R2, il grado di
Rl * R2 è n+m.
card(R1 * R2) = card(R1) * card(R2)
Il prodotto è commutativo e associativo
Rl * R2 = R2 * Rl
Rl * (R2 * R3) = (R1 * R2) * R3
n
R1 * R2 * … * Rn = *i=1 Ri
Parte III: Algebra Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
44
Join (ä)
Dal punto di vista semantico, il Prodotto Cartesiano di
relazioni non ha molto senso.
Esempio
STUDENTI
S#
s1
s1
s2
Nome
Rossi
Rossi
Bianchi
CORSI
C#
c1
c2
Esame#
c1
c2
c1
STUDENTI * CORSI
S#
Nome
s1
Rossi
s1
Rossi
s2
Bianchi
s1
Rossi
s1
Rossi
s2
Bianchi
Esame#
c1
c2
c1
c1
c2
c1
C#
c1
c2
c1
c2
c1
c2
NomeCorso
Chimica
Algebra
NomeCorso
Chimica
Algebra
Chimica
Algebra
Chimica
Algebra
Di solito, il Prodotto Cartesiano ha senso se seguito da una
selezione.
sEsame# = C# (STUDENTI * CORSI)
S#
s1
s1
s2
Parte III: Algebra Relazionale
Nome
Rossi
Rossi
Bianchi
Esame#
c1
c2
c1
C#
c1
c2
c1
NomeCorso
Chimica
Algebra
Chimica
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
45
Theta-Join (äF)
L’operazione viene chiamata theta-join ed è indicata con
R1 äF R2 = {t1 ) t2 su X1X2 | t1aR1ªt2aR2ªF(t1) t2)}
Equivalentemente
R1 äF R2 = sF (R1 * R2)
dove F (condizione di join) è una formula proposizionale di
condizioni del tipo Ai q Bj, con q operatore di confronto fra
gli attributi AiaXl e BjaX2.
Esempio
R1
R2
A
1
6
9
B
2
7
7
C
3
8
8
D
2
2
7
E
3
3
8
F
3
5
10
R1 äA < F R2
A
1
1
1
6
9
B
2
2
2
7
7
C
3
3
3
8
8
D
2
2
7
7
7
E
3
3
8
8
8
F
3
5
10
10
10
C
3
D
7
E
8
F
10
R1 ä(A < F)ª(B ] D) R2
A
1
Parte III: Algebra Relazionale
B
2
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
46
Equi-join (äF=)
Se nel theta-join l’operatore q è l’operatore di uguaglianza
“=” avremo un equi-join.
Esempio
STUDENTE
S#
s1
s1
s2
Nome
Rossi
Rossi
Bianchi
Esame#
c1
c2
c1
STUDENTE äESAME# = C# CORSO
S#
Nome
Esame#
s1
Rossi
c1
s1
Rossi
c2
s2
Bianchi
c1
Parte III: Algebra Relazionale
CORSO
C#
c1
c2
C#
c1
c2
c1
NomeCorso
Chimica
Algebra
NomeCorso
Chimica
Algebra
Chimica
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
47
Join naturale (&, ä)
Siano R e S relazioni con rispettivi schemi R(YX) e S(ZX)
con attributi X in comune.
Il join naturale RäS è la relazione sullo schema YXZ tale
che
taR ä S ¢ t[YX]aR ª t[ZX]aS
Esempio
STUDENTE
CORSO
S#
s1
s1
Nome
Rossi
Rossi
C#
c1
c2
s2
Bianchi
c1
STUDENTE ä CORSO
S#
Nome
s1
Rossi
s1
Rossi
s2
Bianchi
Parte III: Algebra Relazionale
C#
c1
c2
C#
c1
c2
c1
NomeCorso
Chimica
Algebra
NomeCorso
Chimica
Algebra
Chimica
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
48
Join completo
Esempio
STUDENTE
S#
s1
s1
s2
Nome
Rossi
Rossi
Bianchi
CORSO
C#
c1
c2
C#
c1
c2
c1
STUDENTE ä CORSO
S#
Nome
s1
Rossi
s1
Rossi
s2
Bianchi
C#
c1
c2
c1
NomeCorso
Chimica
Algebra
NomeCorso
Chimica
Algebra
Chimica
(Ogni tupla contribuisce al risultato)
Parte III: Algebra Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
49
Join non completo
Esempio
IMPIEGATO
I#
Nome
i1
Rossi
i2
Rossi
i3
Bianchi
Dipartimento
Informatica
Fisica
Informatica
DIPARTIMENTO
Dipartimento
Infomatica
Matematica
IMPIEGATO ä DIPARTIMENTO
I#
Nome
Dipartimento
i1
Rossi
Informatica
i3
Bianchi
Informatica
Direttore
Barca
Nave
Direttore
Barca
Barca
(Non tutte le tuple contribuiscono al risultato)
Parte III: Algebra Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
50
Join vuoto
Esempio
IMPIEGATO
I#
Nome
i1
Rossi
i2
Rossi
i3
Bianchi
Dipartimento
Informatica
Fisica
Informatica
DIPARTIMENTO
Dipartimento
Chimica
Matematica
IMPIEGATO ä DIPARTIMENTO
I#
Nome
Dipartimento
Direttore
Barca
Nave
Direttore
(Non ci sono tuple)
(Ma lo schema c’è!)
Parte III: Algebra Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
51
0 \ card (R1 ä R2) \ card (R1)*card (R2)
Se R1 ä R2 è completo
max(card(R1), card(R2)) \ card(R1 ä R2)
Se R1¨R2 è chiave per R1 (o R2) con vincolo referenziale
card(R1 ä R2) = card(R1)
(= card(R2))
Il join è commutativo e associativo
R1 ä R2 = R2 ä R1
R1 ä (R2 ä R3) = (R1 ä R2) ä R3
n
R1 ä R2 ä … ä Rn = *i=1 Ri
Parte III: Algebra Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
52
Join esterni (ï, , , ïF , …)
Estendono—con valori nulli—le tuple delle relazioni che
non contribuirebbero al risultato di join non completi.
Esempio
IMPIEGATO
I#
Nome
i1
Rossi
i2
Rossi
i3
Bianchi
Dipartimento
Informatica
Fisica
Informatica
DIPARTIMENTO
Dipartimento
Infomatica
Matematica
IMPIEGATO ï DIPARTIMENTO
I#
Nome
Dipartimento
i1
Rossi
Informatica
i2
Rossi
Fisica
i3
Bianchi
Informatica
NULL
NULL
Matematica
Direttore
Barca
Nave
Direttore
Barca
NULL
Barca
Nave
Join esterno
IMPIEGATO DIPARTIMENTO
I#
Nome
Dipartimento
i1
Rossi
Informatica
i2
Rossi
Fisica
i3
Bianchi
Informatica
Direttore
Barca
NULL
Barca
Join esterno sinistro
IMPIEGATO DIPARTIMENTO
I#
Nome
Dipartimento
i1
Rossi
Informatica
i3
Bianchi
Informatica
NULL
NULL
Matematica
Direttore
Barca
Barca
Nave
Join esterno destro
Parte III: Algebra Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
53
Semijoin ( , ,
F
,
F)
Date le relazioni con schemi R(XY) e S(XZ), con X
attributo composto eventualmente vuoto, il semijoin sinistro
( ) è definito
S ={ taR | t[X]apX(S)}
R
Equivalentemente,
R
S = pYX(R ä S).
Il semijoin destro ( ) è definito
R
S=S
R
Esempio
IMPIEGATO
DIPARTIMENTO
I#
i1
i2
Nome
Rossi
Rossi
Dipartimento
Informatica
Fisica
i3
Bianchi
Informatica
IMPIEGATO DIPARTIMENTO
I#
Nome
Dipartimento
i1
Rossi
Informatica
i3
Bianchi
Informatica
Parte III: Algebra Relazionale
Dipartimento
Infomatica
Matematica
Direttore
Barca
Nave
IMPIEGATO DIPARTIMENTO
Dipartimento
Direttore
Infomatica
Barca
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
54
Divisione (%, /)
Se
allora
T=R%S
quoziente
dividendo
divisore
T(X)
R(XY)
S(Y)
con schemi
Se
R=S*T
allora
R%S=T
e anche
R%T=S
Date le relazioni con schemi R(XY) e S(Y), con X attributo
composto non vuoto, la divisione è definita
R % S ={ t su X | (ß saS) t ) saR}.
Parte III: Algebra Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
55
Divisione
Esempio
Si abbia il dividendo (relazione) ASSORTIMENTO contenente
l’abbinamento (disponibilità) di misura e colore di prodotti
(ad es., magliette, scarpe, ecc.).
Quali taglie sono disponibili per i colori chiesti dal cliente ?
Quali colori sono disponibili per le taglie chieste dal cliente ?
Quali taglie sono disponibili per tutti i colori … ?
Quali colori sono disponibili per tutte le taglie … ?
Colore
bianco
Misura
32
34
36
38
40
42
Misura
32
34
Misura
32
34
Colore
grigio
rosa
Colore
grigio
rosa
pMisura(ASSORTIMENTO)
Colore
grigio
blue
bianco
rosa
Misura
32
34
36
38
40
42
divisore
pColore(ASSORTIMENTO)
ASSORTIMENTO%pMisura(ASSORTIMENTO)
Colore
grigio
quoziente
ASSORTIMENTO%pColore(ASSORTIMENTO)
Misura
32
34
Parte III: Algebra Relazionale
Misura
38
Misura
32
38
quoziente
dividendo
Colore
grigio
Colore
blue
bianco
divisore
ASSORTIMENTO
Misura Colore
32
grigio
32
blue
32
bianco
32
rosa
34
grigio
34
blue
34
bianco
34
rosa
36
grigio
36
blue
36
rosa
38
grigio
38
rosa
40
grigio
40
rosa
42
grigio
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
56
Divisione
Equivalentemente,
R % S = pX(R) - pX((pX(R) * S) - R).
Questa equivalenza è molto utile poiché l’operazione di
divisione non è direttamente disponibile nei linguaggi
commerciali per basi di dati.
Esempio
R
A
a
a
b
e
e
a
B
b
b
c
d
d
b
pX(R)
A
a
b
e
C
c
e
e
c
e
d
S
C
c
e
D
d
f
f
d
f
e
D
d
f
pX(R)*S
B
b
c
d
A
a
a
b
b
e
e
B
b
b
c
c
d
d
(pX(R)*S)-R
C
c
e
c
e
c
e
D
d
f
d
f
d
f
A
b
B
c
C
c
pX ((pX(R)*S)-R)
D
d
A
b
B
c
R%S
A
a
e
B
b
d
Parte III: Algebra Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
57
Riepilogo Operatori Relazionali
Operazione
Effetto
Notazione
SELEZIONE
Seleziona tutte le tuple di una relazione
R che soddisfano la condizione di
selezione.
s<condizione di selezione>(R)
PROIEZIONE
Produce una nuova relazione con solo
alcuni degli attributi di R ed elimina le
tuple duplicate.
p<lista di attributi>(R)
RIDENOMINAZIONE
Assegna/modifica il nome a una
relazione e/o a suoi attributi.
r<nome rel> / <lista attributi 2> ← <lista
attributi 1>(R)
THETA JOIN
Produce tutte le combinazioni di tuple di R1 ä <condizione di join> R2
R1 e di R2 che soddisfano la condizione
di join.
EQUIJOIN
Produce tutte le combinazioni di tuple
prelevate da R1 e R2 che soddisfano una
condizione di join che presenta solo
confronti di uguaglianza.
R1 ä <condizione di join> R2, oppure
R1 ä (<attributi di join 1>), (<attributi di join
2>) R2
JOIN NATURALE
Lo stesso dell’EQUIJOIN, se non per il
fatto che gli attributi di join di R2 non
sono inseriti nella relazione risultante;
se gli attributi di join hanno gli stessi
nome non è necessario specificarli.
R1 ä <condizione di join> R2, oppure
R1 ä (<attributi join 1>), (<attributi join 2>)
R2, oppure R1 ä R2 (R1 & R2)
UNIONE
Produce una relazione che contiene tutte
le tuple presenti in R1 o in R2, o in
entrambe; R1 e R2 devono essere
compatibili all’unione.
R1 ´ R2
INTERSEZIONE
Produce una relazione che contiene tutte
le tuple presenti sia in R1 sia in R2; R1 e
R2 devono essere compatibili all’unione.
R1 ¨ R2
DIFFERENZA
Produce una relazione che contiene tutte R1 - R2
le tuple di R1 che non sono in R2; R1 e R2
devono essere compatibili all’unione.
PRODOTTO
CARTESIANO
Produce una relazione che presenta gli
R1 * R2
attributi di R1 e R2 e contiene come tuple
tutte le possibili combinazioni di tuple
di R1 con tuple di R2.
DIVISIONE
Produce una relazione R(X) che
contiene tutte le tuple t[X] di R1(Z) che
in R1 si presentano in combinazione con
ogni tupla di R2(Y), dove Z = X ´ Y.
Parte III: Algebra Relazionale
R1 % R2 (R1 / R2)
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
58
esempi + complessi
PERSONE
Nome
Andrea
Aldo
Maria
Anna
Filippo
Luigi
Franco
Olga
Sergio
Luisa
Età
27
25
55
50
26
50
60
30
85
75
Reddito
21
15
42
35
30
40
20
41
35
87
MATERNITÀ
Madre
Luisa
Luisa
Anna
Anna
Maria
Maria
nome e reddito delle persone
con meno di 30 anni
pNome, Reddito (sEtà < 30 (PERSONE))
Nome
Andrea
Aldo
Filippo
Figlio
Maria
Luigi
Olga
Filippo
Andrea
Aldo
pPadre (PATERNITÀ)
-
pPadre (PATERNITÀ ä Figlio = Nome (sReddito ≤ 20 (PERSONE)))
Padre
Luigi
Parte III: Algebra Relazionale
Figlio
Franco
Andrea
Aldo
Olga
Filippo
padri delle persone
che guadagnano più di venti milioni
pPadre (PATERNITÀ ä Figlio = Nome (sReddito > 20 (PERSONE)))
Reddito
21
15
30
padri i cui figli guadagnano tutti più di venti milioni
PATERNITÀ
Padre
Sergio
Franco
Franco
Luigi
Luigi
Padre
Franco
Luigi
nome, reddito e nome del rispettivo padre delle
persone che guadagnano più dei propri padri
pN, R, P (sR > RP ((PERSONE ä N = F PATERNITÀ)
ä P = NP
(rNP, EP, RP ← N, E, R (PERSONE))))
Nome
Andrea
Olga
Reddito
21
41
Padre
Franco
Luigi
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
59
VISTE
(Relazioni Derivate)
ƒ Possono esistere rappresentazioni diverse per gli stessi
dati (architettura ANSI/SPARC a tre livelli)
ƒ Nelle basi di dati relazionali, le Relazioni Derivate sono
relazioni il cui contenuto è funzione del contenuto di altre
relazioni (definito per mezzo di interrogazioni)
ƒ Le Relazioni di Base hanno un contenuto autonomo
ƒ Le Relazioni Derivate possono essere definite su altre
Relazioni Derivate
Parte III: Algebra Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
60
Viste virtuali e materializzate
ƒ Due tipi di relazioni derivate:
ƒ viste materializzate: relazioni derivate memorizzate nella
base di dati;
vantaggi: immediata disponibilità per le interrogazioni
svantaggi: difficoltà degli aggiornamenti, non supportate
nei DBMS
ƒ relazioni virtuali ( o viste): supportate dai DBMS
ƒ una interrogazione
ricalcolando la vista
Parte III: Algebra Relazionale
su
una
vista
viene
eseguita
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
61
Viste: motivazioni
ƒ Schema esterno o vista utente:
Ogni utente vede solo ciò che gli interessa e nel modo in
cui gli interessa, senza essere distratto dal resto
Ogni utente vede solo ciò che autorizzato a vedere
(autorizzazioni sulle viste)
ƒ Strumento di programmazione: con le viste si può
semplificare la scrittura di interrogazioni; molto utile per
espressioni complesse o con sottoespressioni ripetute
ƒ Utilizzo di programmi esistenti su schemi strutturati
ƒ L’utilizzo delle viste non influisce sull’efficienza delle
interrogazioni
Parte III: Algebra Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
62
Viste
Esempio
AFFERENZA
Impiegato
Rossi
Neri
Bruni
Mori
Reparto
Vendite
Acquisti
Vendite
Personale
DIREZIONE
Reparto
Acquisti
Vendite
Personale
Direttore
Leoni
Falchi
Leoni
ƒ Vista
SUPERVISIONE := pImpiegato, Direttore (AFFERENZA ä DIREZIONE)
ƒ Interrogazione sulla vista
sDirettore = ‘Leoni’ (SUPERVISIONE)
ƒ Eseguita come
sDirettore = ‘Leoni’ (pImpiegato, Direttore (AFFERENZA ä DIREZIONE))
Parte III: Algebra Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
63
Viste come strumento di programmazione
Esempio
AFFERENZA
Impiegato
Rossi
Neri
Bruni
Mori
Reparto
Vendite
Acquisti
Vendite
Personale
DIREZIONE
Reparto
Acquisti
Vendite
Personale
Direttore
Leoni
Falchi
Leoni
Trovare gli impiegati che hanno lo stesso direttore di Rossi.
ƒ Senza la vista
pImpiegato ((AFFERENZA ä DIREZIONE)
ä
rImpR, RepR ← Impiegato, Reparto (sImpiegato = ‘Rossi’ (AFFERENZA) ä DIREZIONE))
ƒ Con la vista
pImpiegato (SUPERVISIONE ä
rImpR ← Impiegato (sImpiegato = ‘Rossi’ (SUPERVISIONE)))
Parte III: Algebra Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
64
Viste e aggiornamenti
Esempio
Relazioni di base:
AFFERENZA
Impiegato
Rossi
Neri
Bruni
Reparto
Vendite
Acquisti
Vendite
DIREZIONE
Reparto
Acquisti
Vendite
Personale
Direttore
Leoni
Falchi
Leoni
ƒ Vista:
SUPERVISIONE
Impiegato
Rossi
Neri
Direttore
Falchi
Leoni
ƒ Vogliamo inserire, nella vista, il fatto che Mori ha
come direttore Lupi.
ƒ Come fare ?
ƒ Gli aggiornamenti possibili sulle viste sono limitati.
Parte III: Algebra Relazionale
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
65
SQL
Structured Query Language
Contiene il DDL (Data Definition Language) e il DML
(Data Manipulation Language).
I sistemi commerciali spesso offrono una serie di strumenti
che estendono le funzionalità definite a livello di standard.
(Ad esempio: i trigger per la definizione di BD attive.)
QUERY LANGUAGE
SELECT
Estrae dati da una o più tabelle
DATA MANIPULATION LANGUAGE
INSERT
UPDATE
DELETE
Aggiunge una o più righe in una tabella esistente
Modifica dati in una o più righe di una tabella
Rimuove una o più righe da una tabella
DATA DEFINITION LANGUAGE
CREATE TABLE
DROP TABLE
ALTER TABLE
CREATE VIEW
DROP VIEW
CREATE INDEX
DROP INDEX
Definisce la struttura di una nuova tabella nella BD
Rimuove la definizione della tabella dal sistema
Aggiunge una colonna alla definizione di una tabella
Definisce una vista-utente di una porzione della BD
Rimuove la vista-utente dal sistema
Indicizza una o più colonne di una tabella
Rimuove l’indice dal sistema
Principali comandi di Query, Manipolazione e Definizione dei dati in SQL.
Parte IV: SQL
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
66
Sommario della sintassi di SQL (1)
CREATE TABLE
<nome tabella>
(<nome colonna> <tipo colonna> [<vincoli attributo>]
{, <nome colonna> <tipo colonna> [<vincoli attributo>] }
[<vincoli tabella> {, <vincoli tabella>} ] )
<nome tabella>
DROP TABLE
ALTER TABLE
<nome tabella> ADD <nome colonna> <tipo colonna>
SELECT [DISTINCT] <lista attributi>
FROM (<nome tabella> {<alias>} | <tabella di join>) {, (<nome tabella> {<alias>} | <
[WHERE <condizione>]
[GROUP BY <attributi raggruppamento> [HAVING <condizione selezione gruppi> ] ]
[ORDER BY <nome colonna> [<ordine>] {, <nome colonna> [<ordine>] } ]
tabella di join>)}
<lista attributi>::= (& | ( <nome colonna> | <funzione> ([DISTINCT] <nome colonna> | &)))
{, ( <nome colonna> | <funzione> ([DISTINCT] <nome colonna> | &) ) }
<attributi raggruppamento>::= <nome colonna> {, <nome colonna>}
<ordine>::= (ASC | DESC)
INSERT INTO <nome tabella> [( <nome colonna>{, <nome colonna>} ) ]
(VALUES ( <costante> {, <costante>}){, ( <costante> {, <costante>})} | <istruzione SELECT>)
DELETE FROM <nome tabella>
[WHERE <condizione selezione>]
UPDATE <nome tabella>
SET <nome colonna> = <espressione>
[WHERE <condizione selezione>]
CREATE VIEW <nome vista>
AS <istruzione SELECT>
DROP VIEW
{, <nome colonna> = <espressione> }
[ ( <nome colonna> {, <nome colonna> } ) ]
<nome vista>
CREATE [UNIQUE] INDEX <nome indice>
ON <nome tabella> ( <nome colonna>
[CLUSTER]
DROP INDEX
[ <ordine> ] {, <nome colonna> [ <ordine>] } )
<nome indice>
1
Si presenta un riassunto della sintassi (o struttura) delle varie istruzioni SQL. Il riepilogo è incompleto e non descrive
ogni possibile costrutto SQL, perché vuole servire da riferimento rapido ai principali tipi di costrutti disponibili. Viene
utilizzata la notazione BNF: i simboli non terminali sono tra parentesi angolari <…>, le parti opzionali sono riportate tra
parentesi quadre […], le ripetizioni tra parentesi graffe {…} e le alternative tra parentesi tonde (…|…|…).
Parte IV: SQL
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
67
DDL
SQL-2 consente la definizione dello schema di base di dati i
cui elementi sono:
• famiglie di caratteri
• i domini
• le tabelle
• gli indici
• le asserzioni
• le viste
• i privilegi.
La sintassi è la seguente:
CREATE SCHEMA [Nome]
[[AUTHORIZATION] Autorizzazione]
{DefElementoSchema}
Parte IV: SQL
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
68
SQL Domini Elementari
• Carattere: permette di rappresentare caratteri o stringhe.
Le stringhe possono essere a lunghezza fissa oppure
variabile.
CHARACTER [VARYING] [(Lunghezza)]
[CHARACTER SET NomeFamigliaCaratteri]
Forme compatte: CHAR e VARCHAR.
• Bit: (SQL-2) utilizzato per attributi che possono assumere
valori 0 e 1. Può essere usato per singoli flag ma anche
per stringhe di bit.
BIT [VARYING] [(Lunghezza)]
• Data e Ora: (SQL-2) per rappresentare istanti di tempo.
DATE
TIME
TIMESTAMP
record of {year, month, day}
record of {hour, minute, second}
date + time
• Intervalli temporali: (SQL-2)
INTERVAL PrimaUnitàTempo [TO UltimaUnitàTempo]
INTERVAL YEAR TO MONTH
indica che gli intervalli vanno misurati in numero di anni
e di mesi. La prima unità può essere caratterizzata da
precisione.
Parte IV: SQL
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
69
• Tipi numerici esatti: permettono di rappresentare valori
interi o valori in virgola fissa.
NUMERIC [(Precisione [, Scala])]
DECIMAL [(Precisione [, Scala])]
INTEGER
SMALLINT
o I domini INTEGER e SMALLINT si usano quando non serve
una parte frazionaria. L’unico vincolo implementativo è
che la precisione del dominio INTEGER sia non inferiore a
quella del dominio SMALLINT.
o Il dominio NUMERIC rappresenta numero in base decimale;
Precisione indica il numero di cifre, Scala il numero di
cifre dopo la virgola.
NUMERIC(6,3) [-999.999, 999.999]
La differenza tra NUMERIC e DECIMAL è che la Precisione
per NUMERIC è un requisito esatto, mentre per DECIMAL è
un requisito minimo.
• Tipi numerici approssimati: Numeri in virgola mobile.
FLOAT [(Precisione)]
DOUBLE PRECISION
REAL
Nel dominio FLOAT si può scegliere la precisione, intesa
come numero di cifre della mantissa.
La precisione di DOUBLE è doppia di quella di REAL.
Parte IV: SQL
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
70
Tabelle
CREATE TABLE NomeTabella
( NomeAttr Dominio [ValoreDefault] [vincoli]
{, NomeAttr Dominio [ValoreDefault] [vincoli]}
[, Altri Vincoli] );
Esempio
CREATE TABLE DIPARTIMENTO
( NOME
CHAR(20) PRIMARY KEY,
INDIRIZZO CHAR(50),
CITTÀ
CHAR(20) );
• Gli attributi possono appartenere ai domini predefiniti o,
in alcune implementazioni di SQL, essere creati tramite
CREATE DOMAIN Nomedom AS TipodiDato
[ValoreDiDefault] [Vincolo];
• Nella definizione delle tabelle e dei domini, i valori di
default rappresentano il valore che assume l’attributo
quando viene inserita una riga nella tabella senza specificare il valore per l’attributo stesso.
DEFAULT
{Valore|USER|NULL}
Esempio
NROFIGLI SMALLINT DEFAULT
Parte IV: SQL
0
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
71
Vincoli Intrarelazionali
• NOT NULL: indica che l’attributo deve essere specificato, a
meno che ad esso non sia associato un valore di default
diverso da NULL.
• UNIQUE: indica che nella tabella non possono esistere due
righe con valori identici su tale attributo.
MATRICOLA CHAR(6) UNIQUE
Può anche essere applicato a più attributi:
COGNOME CHAR(20), NOT NULL
NOME
CHAR(20), NOT NULL
UNIQUE (COGNOME, NOME)
per indicare che non devono esistere righe che abbiano
uguali sia il nome sia il cognome.
• PRIMARY KEY: è specificato per un solo attributo (eventual-
mente composto) nella tabella ed indica la chiave primaria della tabella. È, di solito, l’attributo (semplice o composto) più frequentemente utilizzato per accedere alle
righe della tabella. Gli attributi appartenenti alla chiave
non possono assumere valori nulli (integrità dell’entità).
NOME
COGNOME
DIPART
STIP
PRIMARY
Parte IV: SQL
CHAR(20),
CHAR(20),
CHAR(15),
NUMERIC(9) DEFAULT 0,
KEY (COGNOME, NOME)
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
72
Vincoli Interrelazionali
Il più diffuso è il vincolo di integrità referenziale realizzato
dal vincolo SQL di chiave esterna (FOREIGN KEY). La chiave
esterna è un attributo della tabella referente (interna) vincolato ad assumere o i valori di un attributo della tabella riferita (esterna) o il valore NULL. L’attributo riferito deve essere di tipo UNIQUE (tipicamente, è la chiave primaria della
tabella esterna).
Tabella e attributo riferiti vengono specificati dalla clausola
REFERENCES. La chiave esterna, se è un attributo composto,
deve essere specificata dalla clausola FOREIGN KEY.
Esempi
CREATE TABLE IMPIEGATO
( MATR
CHAR(6) PRIMARY KEY,
NOME
CHAR(20) NOT NULL,
COGNOME CHAR(20) NOT NULL,
DIPART CHAR(15)
REFERENCES DIPARTIMENTO(NOME),
STIP
NUMERIC(9) DEFAULT 0,
UNIQUE (COGNOME, NOME) );
CREATE TABLE IMPIEGATO
( MATR
CHAR(6) PRIMARY KEY,
NOME
CHAR(20) NOT NULL,
COGNOME CHAR(20) NOT NULL,
DIPART CHAR(15)
REFERENCES DIPARTIMENTO(NOME),
STIP
NUMERIC(9) DEFAULT 0,
FOREIGN KEY(NOME,COGNOME)
REFERENCES ANAGRAFE(NOME,COGNOME)
Parte IV: SQL
);
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
73
Violazione del vincolo FOREIGN KEY
Esempio
CREATE TABLE IMPIEGATO
( NOME
CHAR(20),
COGNOME CHAR(20),
DIPART CHAR(15),
REFERENCES DIPARTIMENTO(NOME)
);
Tabella interna: IMPIEGATO, tabella esterna: DIPARTIMENTO.
La violazione di vincoli può avvenire sia a livello di tabella
interna sia esterna.
• Tabella interna. Il vincolo potrebbe essere violato a causa
dell’inserimento o modifica di una riga in cui il valore
dell’attributo DIPART non sia presente nella colonna NOME
della tabella DIPARTIMENTO (e neanche sia NULL). In tal
caso, il DBMS rifiuta l’inserimento (o la modifica).
• Tabella esterna. Le operazioni che possono violare
l’integrità referenziale sono:
→ Modifica del valore dell’attributo riferito
→ Cancellazione di righe.
Possibili politiche di reazione alla violazione per
ciascuna operazione
o NO ACTION (default, operazione rifiutata)
o CASCADE (propaga l’operazione)
o SET NULL (assegna il valore null)
o SET DEFAULT (assegna il valore di default)
Parte IV: SQL
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
74
CREATE TABLE IMPIEGATO
( MATR
CHAR(9),
NOME
CHAR(20) NOT NULL,
COGNOME CHAR(20) NOT NULL,
DIPART CHAR(15),
STIP
NUMERIC(9) DEFAULT 0,
PRIMARY KEY (MATR),
FOREIGN KEY (DIPART)
REFERENCES DIPARTIMENTO(NOME)
ON DELETE SET NULL
ON UPDATE CASCADE,
UNIQUE (COGNOME, NOME) );
IMPIEGATO
Nome
Cognome
Mario
Rossi
Carlo
Bianchi
Giuseppe
Verdi
Franco
Neri
Carlo
Rossi
Lorenzo
Lanzi
Paola
Borroni
Marco
Franco
Dipart
Amministrazione
Produzione
Amministrazione
Distribuzione
Direzione
Direzione
Amministrazione
Produzione
DIPARTIMENTO
Nome
Amministrazione
Produzione
Distribuzione
Direzione
Ricerca
Parte IV: SQL
Ufficio Stipendio
Città
10
45
Milano
20
36
Torino
20
40
Roma
16
45
Napoli
14
80
Milano
7
73
Genova
75
40
Venezia
20
46
Roma
Indirizzo
Via Tito Livio, 27
P.le Laveter, 3
Via Segre, 9
Via Tito Livio, 27
Via Morone,6
Città
Milano
Torino
Roma
Milano
Milano
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
75
DML - Interrogazioni
• SQL consente di esprimere le interrogazioni in modo
dichiarativo attraverso il comando SELECT. L’interrogazione viene poi automaticamente tradotta in termini
procedurali ed eseguita.
• In alternativa, si possono utilizzare tradizionali linguaggi
di programmazione, in cui SQL è immerso (e compilato).
• Esistono vari modi per formulare una query. L’utente può
non occuparsi dell’efficienza nel formulare la query,
quanto piuttosto della sua leggibilità e modificabilità.
Struttura della query SQL
SELECT ListaAttributi
FROM ListaTabelle
[WHERE Condizione];
Parte IV: SQL
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
76
Esempio
Impiegato (Nome, Cognome, Dipart, Ufficio, Stipendio)
Dipartimento (Nome, Indirizzo, Città)
SELECT STIPENDIO AS SALARIO
FROM IMPIEGATO
WHERE COGNOME = 'ROSSI';
Ritorna una tabella con l’unico attributo Salario i cui valori
sono ottenuti dalla colonna Stipendio delle righe di
Impiegato aventi Cognome = Rossi.
SELECT &
FROM IMPIEGATO
WHERE COGNOME =
'ROSSI';
SELECT STIPENDIO/12 AS STIPMENSILE
FROM IMPIEGATO
WHERE COGNOME = 'ROSSI';
IMPIEGATO.NOME, DIPARTIMENTO.NOME,
DIPARTIMENTO.CITTÀ
FROM IMPIEGATO, DIPARTIMENTO
WHERE IMPIEGATO.DIPART = DIPARTIMENTO.NOME;
SELECT
Le righe vengono estratte dal prodotto cartesiano. Equivalentemente, e in forma più compatta:
SELECT I.NOME, D.NOME, D.CITTÀ
FROM IMPIEGATO AS I, DIPARTIMENTO AS
WHERE I.DIPART = D.NOME;
Parte IV: SQL
D
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
77
Nella clausola WHERE è possibile usare connettivi logici
(AND, OR, NOT) ed espressioni di confronto.
SELECT NOME, COGNOME
FROM IMPIEGATO
WHERE UFFICIO = 20 AND DIPART
= 'AMMINISTRAZIONE';
SELECT NOME, COGNOME
FROM IMPIEGATO
WHERE DIPART = 'PRODUZIONE' OR
DIPART = 'AMMINISTRAZIONE';
SELECT NOME
FROM IMPIEGATO
WHERE COGNOME = 'ROSSI' AND
(DIPART = 'PRODUZIONE' OR DIPART
= 'AMMINISTRAZIONE');
Operatore LIKE
Consente il confronto di stringhe e si basa su due caratteri
speciali:
“_” (rappresenta un qualsiasi carattere)
“%” (rappresenta una stringa, anche vuota)
SELECT NOME
FROM IMPIEGATO
WHERE COGNOME LIKE
'_O%I';
ritornerà tutti nomi di Impiegato il cui cognome ha una “o”
in seconda posizione e termina per “i” (Ad es., rossi, Rossi,
Mongelli, Loi).
Parte IV: SQL
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
78
Proiezione: duplicati
SELECT COGNOME
FROM IMPIEGATO;
SELECT DISTINCT COGNOME
FROM IMPIEGATO;
Cognome
Rossi
Bianchi
Verdi
Neri
Rossi
Lanzi
Borroni
Franco
Cognome
Rossi
Bianchi
Verdi
Neri
Lanzi
Borroni
Franco
Espressioni nella target list
SELECT STIPENDIO/12 AS STIPENDIOMENSILE
FROM IMPIEGATO
WHERE COGNOME = 'BIANCHI';
StipendioMensile
3.00
Ordinamento
Il risultato di una interrogazione è una relazione che, come
tale, non è ordinata. Nelle applicazioni può essere invece
utile avere risultati ordinati. Ciò è ottenibile con la clausola
[ASC|DESC]
{, AttrOrdinamento [ASC|DESC]}
ORDER BY AttrOrdinamento
SELECT &
FROM IMPIEGATO
ORDER BY STIPENDIO DESC, COGNOME, NOME;
Parte IV: SQL
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
79
Join
Una interrogazione su più tabelle agisce sul loro prodotto
cartesiano. La clausola WHERE consente di specificare gli
attributi e le condizioni per ottenere un join. È inoltre
possibile (e conveniente) distinguere le condizioni di
selezione delle righe (clausola WHERE) dalle condizioni di
join (clausola FROM) secondo la sintassi
FROM Tabella1 TIPOJOIN JOIN Tabella2 ON CondizioneJoin
TIPOJOIN può essere:
INNER, RIGHT[OUTER], LEFT[OUTER], FULL[OUTER]
inner join
È il theta-join. Vengono selezionate solo le righe del
prodotto cartesiano che soddisfano la condizione espressa
SELECT I.NOME, COGNOME, CITTÀ
FROM IMPIEGATO I INNER JOIN DIPARTIMENTO
ON DIPART = D.NOME;
D
outer join
Il join esterno mantiene tutte le righe della tabella di
sinistra (LEFT), della tabella di destra (RIGHT) oppure di
entrambe (FULL) e mette il valore NULL per indicare la
mancanza di valori corrispondenti nell’altra tabella.
Parte IV: SQL
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
80
Variabili (alias)
SELECT I.NOME, D.NOME, D.CITTÀ
FROM IMPIEGATO AS I, DIPARTIMENTO AS
WHERE I.DIPART = D.NOME;
D
Le variabili I e D (alias) fanno riferimento alle tabelle in
modo più compatto.
Gli alias consentono di far riferimento a più esemplari della
stessa tabella (equivalenti alla ridenominazione). L’interpretazione è che, per ogni alias, si introduce una variabile di
tipo tabella in cui viene copiato il contenuto della tabella di
cui è alias.
Esempio
Impiegati (Matr, Nome, Età, Stip)
Supervisione (Capo, Imp)
Trovare il nome e lo stipendio dei capi degli impiegati che
guadagnano più di 40.
SELECT I1.NOME, I1.STIP
FROM IMPIEGATO [AS] I1, SUPERVISIONE
IMPIEGATO I2
WHERE I1.MATR = S.CAPO AND
S.IMP = I2.MATR AND
I2.STIP > 40;
Parte IV: SQL
S,
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
81
Operatori aggregati
Costituiscono la principale estensione di SQL rispetto all’algebra relazionale. Agiscono non a livello di tupla ma a
livello di relazione.
• COUNT (<&|[DISTINCT|ALL] ListaAttributi>)
SELECT COUNT(&)
FROM IMPIEGATO
WHERE DIPART = 'PRODUZIONE';
SELECT COUNT (ALL NOME, COGNOME)
FROM IMPIEGATO;
• <SUM|AVG|MAX|MIN> ([DISTINCT|ALL] Attributo)
ƒ
ƒ
e AVG richiedono che l’attributo sia numerico o di
tipo intervallo temporale.
SUM
e MIN possono riferirsi a qualsiasi attributo sul cui
dominio sia definito un ordinamento.
MAX
SELECT MAX(STIP)
FROM IMPIEGATI
WHERE ETÀ < 35;
SELECT MATR , MAX(STIP)
FROM IMPIEGATI
WHERE ETÀ < 35;
corretta
errata
Parte IV: SQL
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
82
Interrogazioni con Raggruppamento
In alcune applicazioni sorge l’esigenza di applicare gli
operatori aggregati distintamente a sottoinsiemi di righe.
Esempio
PERSONALE
Nome
Rossi
Bianchi
Verdi
Neri
Gialli
Dipart
dip1
dip2
dip3
dip2
dip1
Uff
1
2
3
4
5
Stip
10
15
12
11
10
SELECT DIPART, SUM(STIP)
FROM PERSONALE
GROUP BY DIPART;
Step 1. Valutazione di
SELECT DIPART, STIP
FROM PERSONALE;
Step 2. Le righe della tabella ottenuta vengono raggruppate
in sottoinsiemi in funzione dei valori dell’attributo
di GROUP BY (DIPART).
Step 3. L’operatore aggregato (SUM) viene applicato a
ciascun sottoinsieme.
Dipart
dipl
dip2
dip3
Parte IV: SQL
Stip
20
26
12
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
83
SELECT UFFICIO
FROM IMPIEGATO
GROUP BY DIPART;
sintatticamente errata e priva di senso.
SELECT DIPART, COUNT(&), CITTÀ
FROM IMPIEGATO I INNER JOIN DIPARTIMENTO
ON I.DIPART = D.NOME
GROUP BY DIPART;
D
significativa ma sintatticamente errata.
SELECT DIPART, COUNT(&), CITTÀ
FROM IMPIEGATO I INNER JOIN DIPARTIMENTO
ON I.DIPART = D.NOME)
GROUP BY DIPART, CITTÀ;
D
Predicati sui Gruppi
La clausola GROUP BY consente di raggruppare in sottoinsiemi.
La clausola HAVING consente di selezionare i sottoinsiemi.
SELECT DIPART, SUM(STIP)
FROM IMPIEGATO
GROUP BY DIPART
HAVING SUM(STIP) >= 20;
Dipart
dip1
dip2
Parte IV: SQL
Stip
20
26
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
84
Interrogazioni Insiemistiche
SelectSQL {UNION|INTERSECT|EXCEPT> [ALL] SelectSQL}
• Possono essere solo al livello più esterno di una query, e
operano sul risultato di una SELECT.
• Eseguono sempre una eliminazione di duplicati (se non si
esplicita la keyword ALL).
• È richiesto che gli attributi siano compatibili.
SELECT COGNOME
FROM IMPIEGATO
UNION ALL
SELECT NOME
FROM IMPIEGATO;
Parte IV: SQL
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
85
Interrogazioni Nidificate
In SQL è possibile confrontare un valore con il risultato di
una SELECT.
• ANY
• ALL
SELECT &
FROM IMPIEGATO
WHERE DIPART = ANY
(SELECT NOME
FROM DIPARTIMENTO
WHERE CITTÀ = 'BARI');
SELECT &
FROM IMPIEGATO
WHERE DIPART <> ALL
SELECT NOME
FROM DIPARTIMENTO
WHERE NOME <> ALL
(SELECT NOME
FROM DIPARTIMENTO
WHERE CITTÀ = 'BARI');
(SELECT DIPART
FROM IMPIEGATO
WHERE COGNOME = 'ROSSI');
SELECT NOME
FROM DIPARTIMENTO
EXCEPT
SELECT DIPART
FROM IMPIEGATO
WHERE COGNOME = 'ROSSI';
Parte IV: SQL
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
SELECT DIPART
FROM IMPIEGATO
WHERE STIPENDIO
86
= MAX(STIPENDIO); _ errata
SELECT DIPART
FROM IMPIEGATO
WHERE STIPENDIO >= ALL
(SELECT STIPENDIO
FROM IMPIEGATO);
SELECT DIPART
FROM IMPIEGATO
WHERE STIPENDIO =
(SELECT MAX(STIPENDIO)
FROM IMPIEGATO);
• Interpretazione. L’interrogazione nidificata è eseguita
una sola volta prima di eseguire l’interrogazione esterna,
e il risultato viene salvato in una tabella temporanea.
• L’interrogazione interna può però fare riferimento a
quella esterna, rendendo questa interpretazione non più
valida. Bisogna allora reinterpretare il tutto in termini di
prodotto cartesiano di tabelle sul cui risultato si applicano
le condizioni espresse nel predicato WHERE. Poiché il
predicato WHERE è a sua volta una interrogazione, questa
dovrà essere valutata per ogni riga del prodotto stesso.
SELECT &
FROM PERSONA
WHERE EXISTS
Parte IV: SQL
P1
(SELECT &
FROM PERSONA P2
WHERE P2.NOME = P1.NOME AND
P2.COGN = P1.COGN AND
P2.CODFISC <> P1.CODFISC);
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
87
Asserzioni
Le asserzioni sono vincoli non associati a relazioni o attributi specifici, ma appartengono direttamente allo schema.
CREATE ASSERTION Nome CHECK
(Condizione)
CREATE ASSERTION ALMENOUNIMPIEGATO
CHECK (1 <= (SELECT COUNT(&)
FROM IMPIEGATO));
SET CONSTRAINTS
SET CONSTRAINTS
[Nome] IMMEDIATE (dopo modifica)
[Nome] DEFERRED (dopo transazione)
Viste (Virtuali)
In SQL le viste sono definite con:
CREATE VIEW Nome [(ListaAttributi)] AS SelectSQL
[WITH [LOCAL|CASCADED] CHECK OPTION]
È possibile utilizzare Viste nella definizione di altre Viste.
(Non sono però consentite dipendenze ricorsive né
immediate, né transitive).
Parte IV: SQL
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
CREATE VIEW IMPAMM(MATR,NOME,COGN,STIP) AS
SELECT MATR, NOME, COGNOME, STIP
FROM IMPIEGATO
WHERE DIPART = 'AMMINISTRAZIONE' AND STIP >
88
10;
CREATE VIEW IMPAMMPOV AS
SELECT &
FROM IMPAMM
WHERE STIP < 50
WITH LOCAL CHECK OPTION;
Le viste sono utilizzabili per scrivere interrogazioni. Tale
caratteristica è particolarmente utile quando è richiesto
l’utilizzo combinato di operatori aggregati.
Impiegato (Matr, Nome, Stip, Dip)
Trovare il dipartimento che spende il massimo in stipendi.
CREATE VIEW BUDGETSTIP(DIP; TOTALSTIP) AS
SELECT DIPART, SUM(STIP)
FROM IMPIEGATO
GROUP BY DIPART;
SELECT DIP FROM BUDGETSTIP
WHERE TOTALSTIP = (SELECT MAX(TOTALSTIP)
FROM BUDGETSTIP));
Parte IV: SQL
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
89
Ciclo di vita dei Sistemi Informativi
Studio di fattibilità
Definisce i costi delle possibili
alternative e le priorità di
realizzazione dei componenti
Raccolta e analisi
delle specifiche
Definisce le proprietà e le funzionalità del sistema: richiede
una interazione utente/progettista, produce una descrizione
informale dei dati, delle operazioni e i requisiti hw/sw
Progettazione
Prototipazione
Definisce in modo formale la
struttura e l’organizzazione dei
dati e le caratteristiche delle
applicazioni, con riferimento a
modelli specifici
Implementazione
Realizzazione del sistema
progettato. Popolamento della
BD e scrittura del codice
Validazione
e testing
Verifica del corretto funzionamento e della qualità del S.I.,
sotto tutte le condizioni operative
Operatività
Fase operativa con interventi
di gestione e manutenzione
Il processo di sviluppo non è quasi mai strettamente
sequenziale.
Parte V: Progettazione Concettuale dei Sistemi Informativi
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
90
Progettazione guidata dai dati
Specifiche
sui dati
Progetto
concettuale
Progettazione concettuale
Schema concettuale
Progetto
logico
Progettazione logica
Schema logico
Progetto
fisico
Progettazione fisica
Schema fisico
Parte V: Progettazione Concettuale dei Sistemi Informativi
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
91
Il modello E-R (Entità-Relationship)
• È il più diffuso modello concettuale
• Ne esistono varie versioni
I costrutti del modello E-R
• entità
• associazione (relazione, relationship)
• attributo
• identificatore
• generalizzazione e sottoinsieme
Parte V: Progettazione Concettuale dei Sistemi Informativi
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
92
Entità
entità: classe di oggetti (fatti, persone, …) del frammento
di interesse del mondo reale, aventi proprietà omogenee
ed esistenza autonoma: ad es. persone, città, aziende,
fatture, ordini.
occorrenza (istanza) di entità: ciascun elemento della
classe
•
nello schema concettuale sono presenti le entità e non
le singole istanze (astrazione)
•
ogni entità ha un nome che la identifica univocamente
nello schema
ø
nomi espressivi
ø
opportune convenzioni (ad es., nomi al singolare)
Diagramma di entità: rappresentazione grafica
IMPIEGATO
CITTÀ
Parte V: Progettazione Concettuale dei Sistemi Informativi
REGIONE
AZIENDA
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
93
Associazione (relationship)
relationship legame logico fra entità, di interesse per
l’applicazione; classe di fatti di interesse.
occorrenza (istanza) di relationship: n-pla (coppia, tripla,
…) di istanze di entità, una per ciascuna entità coinvolta
Diagramma di relationship: rappresentazione grafica
PERSONA
AULA
(0, n)
N
VIVE_IN
CORSO
(0, n)
LEZIONE
1
(0, n)
CITTÀ
GIORNO
(b) relazione LEZIONE
(a) relazione VIVE_IN
ORDINE
superiore_di
IMPIEGATO
DIRIGE
SPEDIZIONE
subordinato_a
(c) relazione DIRIGE
BOLLA_DI_
CONSEGNA
(d) relazione SPEDIZIONE
Parte V: Progettazione Concettuale dei Sistemi Informativi
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
94
Attributo
attributo: proprietà elementare di una entità o di una
relationship, di interesse ai fini dell’applicazione
• un attributo associa un valore a ciascuna occorrenza
dell’entità (o relationship) su cui è definito
Denominazione
Matricola Nome
Voto
STUDENTE
Codice
Nome
NASCE_A
PERSONA
Regione
CITTÀ
Grado
RISIEDE
Parte V: Progettazione Concettuale dei Sistemi Informativi
Docente
CORSO
ESAME
Mansione
CF
Nome
Data
Numero
_Abitanti
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
95
Attributo composto: Gruppo di attributi con affinità in
termini di significato ed impiego. Sono definibili le
cardinalità minima e massima.
civico
via
CF nome cognome
cap
indirizzo
tel (0,n)
PERSONA
Uno schema Entità-Relazione (E-R)
DIREZIONE
cognome
codice
telefono
età
IMPIEGATO
AFFERENZA
DIPARTIMENTO
nome
stipendio
data afferenza
UBICAZIONE
ASSEGNAZIONE
data inizio
budget
nome
PROGETTO
città
SEDE
data consegna
indirizzo
via
civico cap
Parte V: Progettazione Concettuale dei Sistemi Informativi
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
96
Cardinalità della relationship
• le cardinalità vanno specificate per ciascuna entità che
partecipa alla relationship
E1
(m,M)
R
(p,P)
E2
o ogni occorrenza di E1 partecipa ad almeno m e ad al
più M occorrenze di R
o ogni occorrenza di E2 partecipa ad almeno p e ad al più
P occorrenze di R
• di norma, si utilizza
o 0 e 1 per la cardinalità minima:
0 = “è opzionale”, 1 = “è obbligatoria”
(fare attenzione alle relationship obbligatoriaobbligatoria poiché sono talvolta non realistiche)
o 1 e “N” per la cardinalità massima:
“N” oppure “n” non pone limite superiore
• con riferimento alle cardinalità massime, si definiscono
relationship uno-a-uno, uno-a-molti, e molti-a-molti
o fare attenzione al “verso” delle relationship uno-a-molti
poiché in genere non vale la simmetria
Parte V: Progettazione Concettuale dei Sistemi Informativi
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
97
PERSONA
AULA
(0,40)
(1,1)
VIVE_IN
CORSO
(1,3)
LEZIONE
(0,n)
(0, n)
CITTÀ
GIORNO
(b) relazione LEZIONE
(a) relazione VIVE_IN
ORDINE
superiore_di
(0,n)
IMPIEGATO
(0,1)
DIRIGE
(1,1)
subordinato_a
(c) relazione DIRIGE
SPEDIZIONE
(1,1)
BOLLA_DI_
CONSEGNA
(d) relazione SPEDIZIONE
Parte V: Progettazione Concettuale dei Sistemi Informativi
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
98
Denominazione
Voto
Matricola Nome
STUDENTE
(0,n)
(0,1)
Mansione
CF
Nome
Codice
Data
ESAME
NASCE_A
(0,n)
(0,n)
Nome
CITTÀ
Grado
Parte V: Progettazione Concettuale dei Sistemi Informativi
CORSO
Regione
PERSONA
(0,n)
Docente
RISIEDE
(0,n)
Numero
_Abitanti
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
99
Tipi di relationship: molti-a-molti , uno-a-molti , uno-a-uno
PERSONA
MONTAGNA
TURISTA
MACCHINISTA
PERSONA
CINEMA
PERSONA
COMUNE
PROFESSORE
ORDINE
PROFESSORE DI RUOLO
PROFESSORE DI RUOLO
(0,n)
(0,n)
(1,n)
(1,n)
(0,1)
(0,1)
(1,1)
(1,1)
(0,1)
(0,1)
(1,1)
(1,1)
Parte V: Progettazione Concettuale dei Sistemi Informativi
AFFEZIONE
SCALATA
PRENOTAZIONE
ABILITAZIONE
IMPIEGO
UBICAZIONE
RESIDENZA
AFFERENZA
TITOLARITÀ
VENDITA
TITOLARITÀ
TITOLARITÀ
(0,n)
(1,n)
(0,n)
(1,n)
(0,n)
(1,n)
(0,n)
(1,n)
(0,1)
(1,1)
(0,1)
(1,1)
ANIMALE
ALPINISTA
VIAGGIO
LOCOMOTORE
SOCIETÀ
LOCALITÀ
CITTÀ
PROVINCIA
CATTEDRA
FATTURA
CATTEDRA
CATTEDRA COPERTA
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
100
Cardinalità dell’attributo
Si può specificare anche per gli attributi la cardinalità (min,
max), ovvero la numerosità di valori ammessa per ciascuna
istanza di entità. Se omessa, la specifica di default è (1,1).
Solitamente, la cardinalità viene specificata per indicare
• opzionalità (in corrispondenza di valori nulli)
• attributi multivalore
Nome
PERSONA
(0,1)
(0,n)
Numero patente
Targa automobile
Identificatore dell’entità
• consente l’identificazione univoca delle occorrenze
• è costituito da
Š (uno o più) attributi dell’entità:
identificatore interno
Š (attributi e) entità esterne (tramite relationship):
identificatore esterno
Parte V: Progettazione Concettuale dei Sistemi Informativi
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
AUTOMOBILE
Targa
Modello
Colore
101
STUDENTE
Anno iscrizione
Cognome
Matricola
(1,1)
ISCRIZIONE
(1,n)
PERSONA
Cognome
Nome
Data nascita
Indirizzo
UNIVERSITÀ
Nome
Città
Indirizzo
regole e vincoli per l’identificazione
o un identificatore può coinvolgere uno o più attributi,
ognuno dei quali deve avere cardinalità (1,1);
o una identificazione esterna può coinvolgere una o più
entità, ognuna delle quali deve essere membro di una
relazione alla quale l’entità da identificare partecipa con
cardinalità (1,1);
o una identificazione esterna può coinvolgere una entità che
sia a sua volta identificata esternamente, purchè non
vengano generati cicli di identificazioni esterne;
o ogni entità deve avere almeno un identificatore (interno o
esterno), ma ne può avere più di uno; nel caso di più
identificatori, gli attributi e le entità coinvolte in alcune
identificazioni (tranne una) possono essere opzionali
(cardinalità minima uguale a zero).
Parte V: Progettazione Concettuale dei Sistemi Informativi
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
102
Uno schema E-R con attributi e identificatori
(1,n)
DIREZIONE
cognome
(1,n)
codice
telefono
età
IMPIEGATO
(1,n)
AFFERENZA
(1,n)
(1,n)
DIPARTIMENTO
nome
stipendio
(1,n)
data afferenza
UBICAZIONE
ASSEGNAZIONE
budget
nome
(1,n)
(1,n)
data inizio
PROGETTO
(1,n)
città
SEDE
(0,1)
data consegna
indirizzo
via
civico cap
Parte V: Progettazione Concettuale dei Sistemi Informativi
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
103
Generalizzazione
→ “uomo” e “donna” sono casi particolari di “persona”
→ il concetto di “dipendente” è una generalizzazione dei
concetti di “funzionario”, “impiegato”, “dirigente”, …
Generalizzazione è una astrazione di una o più entità F1, F2,
…,—dette sottoclassi o figlie—in una entità G concettualmente più generale—detta superclasse o genitore o padre.
Sottoinsieme è un caso particolare della generalizzazione:
per un determinato concetto sul genitore G esiste una sola
sottoclasse S, che può avere propri attributi.
Proprietà di copertura della generalizzazione
• proprietà di completezza: totale o parziale
o totale (t) se ciascun elemento di G corrisponde ad
almeno una delle sottoclassi
o parziale (p) se almeno un elemento di G è privo di
corrispondenza
• proprietà di disgiunzione: esclusiva o sovrapposta
o esclusiva (e) se ogni elemento di G corrisponde ad al
più una sola sottoclasse
o sovrapposta (o) se almeno un elemento di G ha
corrispondenza in due o più sottoclassi
• la copertura di un sottoinsieme è parziale ed esclusiva.
Parte V: Progettazione Concettuale dei Sistemi Informativi
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
104
PERSONA: totale, esclusiva
PERSONA
FEMMINA
(t,e)
MASCHIO
PERSONA
FEMMINA
MASCHIO
PERSONA: totale, sovrapposta
PERSONA
(t,o)
FEMMINA
IMPIEGATO
MASCHIO
FEMMINA
MASCHIO
IMPIEGATO
PERSONA
PERSONA
(t,e)
FEMMINA
(p,e)
MASCHIO
IMPIEGATO
VEICOLO: parziale, esclusiva
VEICOLO
BICI
(p,e)
AUTO
VEICOLO
AUTO
BICI
SPORTIVO: parziale, sovrapposta
SPORTIVO
CALCIATORE
(p,o)
TENNISTA
SPORTIVO
Parte V: Progettazione Concettuale dei Sistemi Informativi
CALCIATORE
TENNISTA
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
105
LAVORATORE
Data di
assunzione
LAVORATORE
STABILE
Codice fiscale
Cognome
Nome
PERSONA
Pos_Militare
UOMO
DONNA
DIPENDENTE
IMPIEGATO
CITTÀ
FUNZIONARIO
NASCITA
Dipartimento
DIRIGENTE
Cognome
Nome
PERSONA
PROFESSORE
STUDENTE
ogni professore è una persona
ogni studente è una persona
nessun professore è studente, e viceversa
esistono persone che non sono né studenti né professori
ogni studente ha nome, cognome e citta di nascita (ereditati)
Parte V: Progettazione Concettuale dei Sistemi Informativi
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
106
Gerarchie di generalizzazione
Š Possono esistere gerarchie a più livelli e gerarchie
multiple allo stesso livello
Š Una classe (entità) può essere inclusa in più gerarchie,
come genitore e/o figlia
Š alcune configurazioni (combinazioni) non hanno senso
Š il genitore di una gerarchia totale può non avere un
identificatore (purchè lo abbiano tutte le classi figlie)
Codice fiscale
Cognome
Situazione
militare
PERSONA
Età
Matricola
Stipendio
UOMO
Orario
DONNA
IMPIEGATO
STUDENTE
SEGRETARIO
DIRETTORE
PROGETTISTA
RESPONSABILE DI
PROGETTO
PERSONA
(t,e)
MASCHIO
(p,e)
FEMMINA
MANAGER
SEGRETARIO
(p,o)
DIRETTORE
AMMINISTRATIVO
IMPIEGATO
(p,o)
DIRETTORE
TECNICO
Parte V: Progettazione Concettuale dei Sistemi Informativi
VENDITORE PROGRAMMATORE PUBBLICITARIO
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
107
Ereditarietà
PERSONA
stato
civile
MASCHIO
FEMMINA
PERSONA
nome
MASCHIO
Parte V: Progettazione Concettuale dei Sistemi Informativi
cognome
da nubile
indirizzo
stato civile (0,1)
cognome da nubile (0,1)
FEMMINA
MASCHIO
PERSONA
nome
indirizzo
stato civile
nome
indirizzo
nome
nome
indirizzo
FEMMINA
nome
indirizzo
cognome da nubile
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
108
Progettazione Logica
Obiettivo: “tradurre” lo schema concettuale in uno schema
logico equivalente (corretto ed efficiente)
Input:
ƒ schema concettuale
ƒ carico applicativo
ƒ modello logico
Output:
ƒ schema logico, con vincoli
ƒ documentazione associata.
Carico
applicativo
Schema
E-R
Ristrutturazione
dello schema E-R
Modello
logico
Schema E-R
ristrutturato
Traduzione nel
modello logico
Schema
logico
Parte VI: Progettazione Logica dei Sistemi Informativi
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
109
Ristrutturazione EER in E-R
A01
schema EER
A02
R1
E0
A11
E3
A21
E1
E2
(k,l)
R2
E4
accorpare le sottoclassi nella superclasse (collasso in alto)
A01
A11
A02
A21
(0,1)
E0
(0,1)
R1
E3
R2
E4
{tipo}
(0,l)
accorpare la superclasse nelle sottoclassi (collasso in basso)
E3
R12
R11
A11
A01
A01
A21
E1
E2
A02
(k,l)
R2
E4
A02
sostituire la generalizzazione con associazioni (mantenimento delle entità)
A01
A02
R1
E0
(0,1)
Rg1
A11
(1,1)
E1
E3
(0,1)
Rg1
(1,1)
E2
Parte VI: Progettazione Logica dei Sistemi Informativi
A21
(k,l)
R2
E4
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
110
selettore
stage
DIPLOMANDO
(1,1)
(1,1)
(0,n)
(0,n)
RELATORE
denom.
cod.
AZIENDA
diplomando (312, linguaggi)
SINDACATO
(t,e)
codice
(1,n)
OPERAIO
IMPIEGATO
(1,1)
DIRIGENTE
descrizione
PROGETTO
(0,1)
CAPO.O
CAPO.I
(0,n)
codice
(0,1)
(1,1)
DIRIGENTE
mesi
uomo
n.ro
schede
PROG_HW
(1,n)
USA
(0,n)
COMP_HW
Parte VI: Progettazione Logica dei Sistemi Informativi
mesi
uomo
SW
(1,1)
PROG_SW
CAPO.D
(0,n)
(0,n)
descrizione
PROGETTO
(0,1)
PROG_SW
ISCRIZ.D
(1,1)
classe
IMPIEGATO
(0,n)
ISCRIZ.I
cognome
job
CAPO
(0,1)
CF
(0,1)
cognome
qualifica
(0,1)
job
AZIENDA
(0,n)
ISCRIZ.O
DIPENDENTE
denom.
SINDACATO
ISCRIZ.
OPERAIO
(0,n)
(0,n)
(1,n)
qualifica
(0,n)
studente (123, rossi, L, database, NULL)
studente (218, neri, NULL, NULL, NULL)
studente (312, verdi, D, NULL, linguaggi)
selettore ® {L: laur.ndo, D: dipl.ndo}
laureando (123, database)
cognome
(0,1)
RELATORE
studente (123, rossi)
studente (218, neri)
studente (312, verdi)
CF
(0,1)
CF
cod.
(0,1)
cognome
classe
LAUREANDO
cognome
stage
STUDENTE
(0,1)
(p,e)
tesi
matr.
tesi
cognome
STUDENTE
CF
matr.
(0,1)
HW
(1,1)
n.ro
schede
PROG_HW
(1,n)
USA
(0,n)
COMP_HW
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
111
Attributi multivalore
nome indirizzo città
indirizzo
città
AGENZIA
indirizzo
(1,n)
nome
(1,n)
AGENZIA
UTENZA
telefono
(1,1)
TELEFONO
Parte VI: Progettazione Logica dei Sistemi Informativi
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
112
Traduzione ER vs Relazionale
Entità e associazioni molti-a-molti
matricola cognome stipendio
IMPIEGATO
(0,n)
codice
data inizio
PARTECIPAZIONE
(0,n)
nome
budget
PROGETTO
IMPIEGATO (Matricola, Cognome, Stipendio)
PROGETTO (Codice, Nome, Budget)
PARTECIPAZIONE (Matricolaó, Codiceó, DataInizio)
con vincoli di integrità referenziale (ó) tra gli attributi
Matricola e Codice di PARTECIPAZIONE e le chiavi di
IMPIEGATO e PROGETTO, rispettivamente.
Š Può essere utile ridenominare gli attributi della chiave
della relazione che rappresenta l’associazione
PARTECIPAZIONE (Impiegato, Progetto, DataInizio).
Š La ridenominazione è indispensabile per le associazioni
ricorsive
componente
codice
nome
prezzo
PARTE
(0,n)
quantità
COMPOSIZIONE
composto
(0,n)
PARTE (Codice, Nome, Prezzo)
COMPOSIZIONE (Compostoó, Componenteó, Quantità)
con i due vincoli di integrità referenziale CompostoóPARTE
e ComponenteóPARTE
Parte VI: Progettazione Logica dei Sistemi Informativi
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
113
Associazioni ternarie (molti-a-molti)
quantità
partita IVA
nome ditta
(0,n)
FORNITORE
FORNITURA
(1,n)
PRODOTTO
codice
genere
(1,n)
nome
telefono
DIPARTIMENTO
FORNITORE (PartitaIVA, NomeDitta)
PRODOTTO (Codice, Genere)
DIPARTIMENTO (Nome, Telefono)
FORNITURA (Fornitoreó, Prodottoó, Dipartimentoó,
Quantità)
con tre vincoli di integrità referenziale.
Associazioni uno-a-molti
ingaggio
cognome
data nascita
ruolo
GIOCATORE
(0,1)
CONTRATTO
(0,n)
SQUADRA
nome
città
colori sociali
Š traduzione standard:
SQUADRA (Nome, Città, ColoriSociali)
GIOCATORE (Cognome, DataNascita, Ruolo)
CONTRATTO (Giocatoreó, DataNascGiocatoreó, Squadraó, Ingaggio)
Š GIOCATORE e CONTRATTO hanno la stessa chiave: è
pertanto possibile fonderle
Parte VI: Progettazione Logica dei Sistemi Informativi
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
114
SQUADRA (Nome, Città, ColoriSociali)
GIOCATORE (Cognome, DataNascita, Ruolo, Squadra*ó,
Ingaggio*)
(*) attributo con possibili valori nulli
Entità con identificatore esterno
Š sono coinvolte in associazioni uno-a-molti
Š si traducono con relazioni che contengono (come parte
della chiave) anche la chiave della relazione identificante;
(in tal modo si rappresenta anche l’associazione)
matricola
cognome anno iscrizione
STUDENTE
nome
(1,n)
(1,1)
ISCRIZIONE
città
indirizzo
UNIVERSITÀ
UNIVERSITÀ (Nome, Città, Indirizzo)
STUDENTE (Matricola, NomeUniversitàó, Cognome, AnnoIscr)
con vincolo d’integrità referenziale tra l’attributo NomeUniversità e l’attributo Nome della relazione UNIVERSITÀ
Parte VI: Progettazione Logica dei Sistemi Informativi
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
115
Associazioni uno-a-uno
→ La traduzione dipende dalle cardinalità minime
obbligatoria-obbligatoria
codice cognome stipendio
(1,1)
DIRETTORE
nome telefono
data inizio
(1,1)
DIREZIONE
sede
DIPARTIMENTO
DIRETTORE (Codice, Cognome, Stipendio)
DIPARTIMENTO (Nome, Telefono, Sede, Direttoreó, InizioDirez)
¬
DIRETTORE (Codice, Cognome, Stipendio, DipDirettoó, InizioDirez)
DIPARTIMENTO (Nome, Telefono, Sede)
¬
DIRDIP (Codice, Cognome, Stipendio, Dipart, Telefono, Sede, InizioDirez)
¬
DIRETTORE (Codice, Cognome, Stipendio)
DIPARTIMENTO (Nome, Telefono, Sede)
DIRDIP (Direttoreó, Dipartimentoó, InizioDirez)
opzionale-obbligatoria
codice cognome stipendio
IMPIEGATO
nome telefono
data inizio
(0,1)
(1,1)
DIREZIONE
sede
DIPARTIMENTO
IMPIEGATO (Codice, Cognome, Stipendio)
DIPARTIMENTO (Nome, Telefono, Sede, Direttoreó, InizioDirez)
Parte VI: Progettazione Logica dei Sistemi Informativi
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
116
opzionale-opzionale
cognome datanascita curriculum
AMBASCIATORE
nazione capogoverno capostato
credenziali
(0,1)
(0,1)
ASSEGNAZIONE
SEDE
AMBASCIATORE (Cognome, DataNascita, Curriculum)
SEDE (Nazione, CapoGoverno, CapoStato,
Ambasc*ó, DataNascAmbasc*ó, Credenziali*)
¬
AMBASCIATORE (Cognome, DataNascita, Curriculum,
Nazione*ó, Credenziali*)
SEDE (Nazione, CapoGoverno, CapoStato)
¬
AMBASCIATORE (Cognome, DataNascita, Curriculum)
SEDE (Nazione, CapoGoverno, CapoStato)
ASSEGNAZIONE (Ambascó, DataNascAmbascó, Nazioneó, Credenziali)
Parte VI: Progettazione Logica dei Sistemi Informativi
a.a. 2010-11
ezio lefons: sistemi di basi di dati (appunti), 1.2ª ed.
117
(0,1)
DIREZIONE
codice
cognome
stipendio
età
IMPIEGATO
(0,1)
AFFERENZA
data afferenza
(0,n)
ASSEGNAZIONE
(1,1)
(1,n)
telefono (1,n)
DIPARTIMENTO
nome
(1,1)
UBICAZIONE
data inizio
(1,n)
nome
budget
data consegna
(0,1)
PROGETTO
(1,n)
SEDE
città
indirizzo
civico
via
cap
1. IMPIEGATO (Codice, Cognome, Stipendio, Età)
2. PROGETTO (Nome, Budget, DataConsegna*)
3. SEDE (Città, IndCivico, IndVia, IndCap)
4. DIPARTIMENTO (CittàDóSEDE, Nome)
5. ASSEGNAZIONE (NomePóPROG, CodiceIóIMP, DataInizio)
6. AFFERENZA (I#óIMP, CittàSóDIP, NomeDóDIP, DataAffer)
1+6. IMPIEGATO (I#, Cogn, Stip, Età, Città*óDIP, Nome*óDIP,
DataAff*)
7. DIREZIONE (CittàDóDIP, NomeóDIP, DirettoreóIMP)
8. UBICAZIONE à DIPARTIMENTO
4+7. DIPARTIMENTO (CittàóSEDE, Nome, DirettoreóIMP)
8. TELEFONI (CittàóDIP, NomeDóDIP, Telefono)
Parte VI: Progettazione Logica dei Sistemi Informativi
a.a. 2010-11