SQL (IV)
Data Definition Language/
Data Manipulation Language
Inserire (I)
insert into NomeTabella [(ListaAttr)]
<values (ListaDiValori) |
SelectSQL>
query
• Esempio 1:
insert into Dipart (Nome,Citta)
values (‘produzione’, ‘Torino’)
• Esempio 2:
insert into Prodotti-Milanesi
(select Cod,Descr
from Prodotto
where Luogo = ‘Milano’)
2
Inserire (II)
Conta l’ordine con cui si elencano gli attributi
(anche nella query) e i valori
Se i valori non sono specificati: o null o
valore di default (a seconda di come è stata
creata la tabella, per esempio se c’è vincolo
not null)
Di solito vengono fornite delle form che
nascondono le insert
3
Delete (I)
delete from NomeTabella [where Condizione]
• Esempio 1:
delete from Dipart
where Nome = ‘produzione’
• Esempio 2:
delete from Dipart
where Nome not in
(select Dip
from Impiegato)
4
Delete (II)
Se non c’è nessuna condizione, cancella
tutta la NomeTabella (attenzione ai
cascade!!!)
1. delete from Dipart diverso da
2. drop table Dipart cascade diverso da
3. drop table Dipart restrict
1. cancella le righe della tabella (se c’era cascade,
cancella righe di altre tabella legate da vincoli
integrità referenziale)
2. rimuove la tabella
3. non cancella la tabella se ha tuple
5
Update (I)
update NomeTabella
set Attributo = <Espressione |
SelectSQL
|
null | default>
{,Attributo = <…>}
[where Condizione]
• Esempio 1:
update Dipendente
set Stip = StipBase + 5
where matr = ‘666’
Senza condizione, si modificano tutte le tuple
6
Update (II)
• Esempio 2:
update Impiegato
set Stip = Stip*1.1
where Stip <= 30
10
%
update Impiegato
set Stip = Stip*1.15
where Stip > 30
15
%
Occorre invertirle, sennò non funziona, infatti SQL è
set-oriented e non tuple-oriented (come alternativa
paraziale uso dei cursori…)
7
Vincoli (I)
Ci sono i vincoli predefiniti (unique,
primary key, not null, foreign
key…)
In SQL2 si ha anche check
check Condizione
Serve per dare condizioni di correttezza della
BD ed è molto potente
8
Esempio:
create table Impiegati
(Matr character(6)
check (Matr is not null and
1 = (select count(*)
from Impiegato I
where Matr = I.Matr)),
Cogn character(20)
check (Cogn is not null and
2 > (select count(*)
from Impiegato I
where Nome = I.Nome
and Cogn = I.Cogn)),
Nome character(20)
check (Nome is not null and
2 > (…)),
Dip character(5)
check (Dip in
(select NomeD
from Dipartimento)))
Vincoli (II)
9
Esempio:
create table Impiegati
(Matr character(6)
check (Matr is not null and
1 = (select count(*)
from Impiegato I
where Matr = I.Matr)),
Cogn character(20)
check (Cogn is not null and
2 > (select count(*)
from Impiegato I
where Nome = I.Nome
and Cogn = I.Cogn)),
Nome character(20)
check (Nome is not null and
2 > (…)),
Dip character(5)
check (Dip in
(select NomeD
from Dipartimento)))
Vincoli (II)
Un solo
impiegato
con
stesso
nome/
cognome
Vincolo di
integrità
10
Vincoli (III)
•Esempio:
create table Impiegati
(Matr …
primary key,
Cogn …
not null,
Nome …
not null,
Dip …
references Dipartimento(NomeD),
unique(Cogn,Nome))
Così è più semplice (e più efficiente dal punto di vista
dell’implementazione) ma check utile per vincoli complessi
(per esempio, libro pagina 138)
11
Asserzioni (I)
Asserzione = vincolo su schema di relazione e non
su istanza (tabella) o attributo che permettono di
avere DB consistente
La drop può cancellare anche le asserzioni
create assertion NomeAsserzione
check (Condizione)
Per esempio:
create assertion Alm-Un-Imp
(check (1 <= (select count(*)
from Impiegato))
12
Check e Asserzioni
1. Immediati: verificate dopo ogni modifica
(per esempio, i vincoli predefiniti)
2. Differiti: solo dopo una serie di operazioni
(transazione)
set constraint NomeVincolo
<immediate | deferred>
13
Check e Asserzioni
Se i dati non soddisfano i vincoli:
1. Roll-back parziale: si disfa la modifica
2. Roll-back: si disfa tutta la transazione
•
Quando si rileva una violazione di un vincolo
differito al termine di una transazione, non c’è
modo di individuare l’operazione che ha causato
la violazione  si disfa la transazione
14
Viste (tabelle virtuali) (I)
create view NomeVista[(ListaAttr)]
as SelectSQL [with [local | cascaded] |
check option]
• Esempio 1:
create view ImpAmm(Matr,Nome,Cogn,Stip)
as select Matr,Nome,Cogn,Stip
from Impiegato
where Dip = ‘Amm’ and Stip > 10
possibile
modificare
15
Viste (II)
Utili per interrogazioni complesse
Le viste possono usare altre viste, ma
non possono esserci dipendenze
ricorsive immediate (esempio: V1 usa
V1) o transitive (esempio: V1 usa V2
che usa V3… che usa V1)
16
Viste (III)
Su certe viste possibile fare modifiche che si
ripercuotono sulle tabelle di base (“reali”)
In generale, problemi se ci sono join molto
complessi
Regola: 1 tupla vista = 1 tupla tabella di base
Nei sistemi commerciali viste definite su una
sola tabella reale e l’insieme attributi della
vista contengono chiave primaria della tabella
17
Controllo dell’accesso (I)
Utente identificato in modo univoco
(per esempio, il system administrator,
predefinito e molto potente)
Si da il controllo e l’accesso a risorse
tramite “privilegi”
grant e revoke per dare e revocare i
privilegi
18
Controllo dell’accesso (II)
I privilegi sono:






insert (es. tabelle, viste, asserzioni),
update (es. tabelle, viste, attributi),
delete (es. tabelle, viste, asserzioni),
select (es. tabelle, viste, attributi nelle
interrogazioni),
reference (es. tabelle usate per vincoli di
integrità referenziali),
usage (es. domini nella definizione di schemi),
drop/alter: solo il creatore può usarli
19
Controllo dell’accesso (III)
Per
propagare i
priv
grant Privilegi on Risorse
to Utenti [with grant option]
revoke Privilegi on Risorse
from Utenti [restrict | cascade]
Solo chi dà
può
revocare
20
Metodologie e modelli per il
progetto
Introduzione alla
progettazione
Il problema: progettare una base di
base di dati a partire dai suoi requisiti
Progettare: definire la struttura,
caratteristiche e contenuto
22
Il ciclo di vita dei sistemi
informativi
La progettazione costituisce solo una
delle componenti del processo di
sviluppo

Va inquadrato in un contesto più ampio: il
ciclo di vita dei sistemi informativi
23
Il ciclo di vita dei sistemi
informativi
Studio di fattibilità
Raccolta e analisi dei requisiti
Progettazione
Implementazione
Validazione e collaudo
Funzionamento
24
Il ciclo di vita dei sistemi
informativi
Studio di fattibilità: definire i costi delle varie
alternative possibili
Raccolta e analisi dei requisiti: individuazione
delle proprietà e delle funzionalità che il
sistema dovrà avere
Progettazione: dei dati (la struttura e
l’organizzazione che i dati dovranno avere) e
delle applicazioni (le caratteristiche dei
programmi applicativi)
25
Il ciclo di vita dei sistemi
informativi
Implementazione: realizzazione del
sistema informativo
Validazione e collaudo: serve a
verificare il corretto funzionamento e la
qualità del sistema informativo
Funzionamento: il sistema informativo
diventa operativo
26
Il ciclo di vita dei sistemi
informativi
Il processo non è quasi mai
strettamente sequenziale  ciclo
Focalizzeremo attenzione sulla terza
fase del ciclo di vita: progettazione (dei
dati)
27
Metodologie di progettazione
Nell’ambito delle basi di dati: separare
in maniera netta le decisioni relative a
“cosa” rappresentare in una base di dati
da quelle relative a “come” farlo


Cosa: prima fase (progettazione
concettuale)
Come: seconda e terza fase (progettazione
logica e fisica)
28
Metodologie di progettazione
Progettazione concettuale


Fa riferimento a un modello concettuale dei
dati
I modelli concettuali ci consentono di
descrivere l’organizzazione dei dati a un
alto livello di astrazione
29
Metodologie di progettazione
Progettazione logica



Traduzione dello schema concettuale nel modello
di rappresentazione dei dati
Fa riferimento a un modello logico dei dati
Modello logico: indipendente dagli dettagli fisici,
ma concreta
Progettazione fisica


Fa riferimento a un modello fisico dei dati
Modello fisico: dipende dallo specifico sistema di
gestione di basi di dati scelto
30
Guyguyguyguygu
Hvvvuvuvuv
Fvvvuvuvuvu
Vvyuvuyvuvu
Vyuvuyvuyvu
Vyuvuyvuo
Progettazione
concettuale
Modello
Entità-Relazionale
Progettazione
logica
Relazioni/
tabelle
Progettazione
fisica
Livello fisica
(memorizzazione)
31
Modello Entità-Relazione
Il modello Entità-Relazione è un
modello concettuale dei dati
Fornisce una serie di strutture
(costrutti) atte a descrivere la realtà di
interesse
32
Modello Entità-Relazione
Entità: rappresentano classi di oggetti che
hanno proprietà comuni ed esistenza
“autonoma” ai fini dell’applicazione di
interesse



Città, Dipartimento, Impiegato, Acquisto e Vendita
(nel contesto di un’applicazione aziendale)
Una occorrenza di una entità è un oggetto della
classe che l’entità rappresenta
Per esempio: Torino è un esempio di occorrenze
dell’entità Città
33
Modello Entità-Relazione
Entità:


Una occorrenza di entità non è un valore
che identifica un oggetto (p.e. nome,
codice fiscale, …) ma l’oggetto stesso
Questa è una differenza rispetto al modello
relazionale (nel quale non possiamo
rappresentare un oggetto senza conoscere
alcune sue proprietà)
34
Modello Entità-Relazione
Entità:

Ogni entità ha un nome che la identifica
univocamente
Impiegato
Dipartimento
Città
35
Modello Entità-Relazione
Relazione (o associazioni):


Rappresentano legami logici tra due o più entità
Per esempio:
 Residenza: tra le entità Città e Impiegato
 Esame: tra le entità Studente e Corso


Un occorrenza di relazione è una n-upla costituita
da occorrenza di entità
Per esempio:
 Residenza: Bologna, Rossi; oppure Firenze, Verdi
36
Modello Entità-Relazione
Relazione (o associazioni):


Ogni relazione ha un nome che la identifica
univocamente
Graficamente: un rombo, e linee che
connettono la relazione con ciascuna delle
sue componenti
Studente
Esame
Corso
37
Modello Entità-Relazione
Relazione (o associazioni):

Possono esistere relazioni diverse che
coinvolgono le stesse entità
Sede di
lavoro
Impiegato
Residenza
Città
38
Modello Entità-Relazione
Relazione (o associazioni):

È possibile avere relazione tra una entità e
se stessa
Collega
Successione
Impiegato
Sovrano
Predecessore
Successore
39
Modello Entità-Relazione
Relazione (o associazioni):

È possibile avere relazione che coinvolgono
più di due entità
Fornitore
Fornitura
Prodotto
Dipartimento
40
Modello Entità-Relazione
Attributi:


Descrivono le proprietà elementari di entità
o relazioni che sono di interesse ai fini
dell’applicazione
Per esempio:
 Cognome, Stipendio e Età sono possibili
attributi dell’entità Impiegato
 Data e Voto sono possibili attributi della
relazione Esame tra Studente e Corso
41
Modello Entità-Relazione
Attributi:

Un attributo associa a ciascuna occorrenza
di entità o di relazione un valore
appartenente a il dominio del attributo
 Dominio: i valori ammissibili per l’attributo
42
Modello Entità-Relazione
Attributi:
Voto
Data esame
Matricola
Studente
Anno di
iscrizione
Nome
Esame
Corso
Anno di
corso
43