SQL (III)

Data Definition Language/Data Manipulation Language

2

Riprendiamo SQL…

Abbiamo visto parte del DML (Data Manipulation Language), le query Linguaggio di interrogazione per DBMS

relazionali

Vedremo brevemente il DDL (Data Declaration Language) e l’altra parte del DML (per fare modifiche), diventerà interessante quando farete le transazioniRicordiamo: non esiste una versione di SQL che sia veramente lo standard

3

Notazione

<x>: isola il termine x[x]: x è opzionale{x}: x ripetuto da 0 a numero arbitrario di voltex | y: x e y sono in alternativa( , ) appartengono a SQL

schema relazione tabellariga tupla

4

Domini elementari per gli attributi

SQL offre 6 famiglie di domini elementari, che possono essere usati anche per costruire domini compositi Carattere Bit Tipi numerici esatti Tipi numerici approssimati Data e ora Intervalli temporali

5

Character

Per rappresentare caratteri singoli e stringhe a lunghezza fissa o variabileSi può scegliere tra diversi alfabeticharacter [varying][(lunghezza)]

[character set Famiglia]

Forme abbreviate: char, varcharPer esempio:

character (20) char varying (1000) character set Greek

6

Bit

Per rappresentare bit (0,1) e stringhe di bit a lunghezza fissa o variabileUtili per rappresentare in modo compatto insiemi di valori booleanibit [varying][(lunghezza)]

Forme abbreviate: varbit (per bit varying)Per esempio:

varbit(20), stringhe di bit lunghe al massimo 20

7

Tipi numerici esatti

Domini (4) per rappresentare valori esatti, interi o con parte decimale di lunghezza prefissata

numeric [(precisione [, scala])] precisione esatta

decimal [(precisione [, scala])] precisione minima

precisione = numero cifre significative (se non si indica, si usa quella specifica del DBMS)

scala = numero cifre dopo la virgola

8

Tipi numerici esatti

Per esempio: decimal(4) tra –9.999 e +9.999Per esempio: numeric(6,3) tra -999,999 e +999,999

Altri due domini: integer smallint

9

Tipi numerici approssimati

Domini (3) per rappresentare valori reali float[(Precisione)] double precision real

Per tutti questi domini: Rappresentazione mantissa e esponente

(precisione indica cifre della mantissa) Per esempio: 0.17E16 = 0,17 * 1016

10

Altri domini

Data e ora date time [(Precisione)][with time zone] Ecc.

Intervalli temporali interval year to month: la durata dell’intervallo di

tempo deve essere misurata in numero di anni e di mesi

interval hour to second:la durata dell’intervallo di tempo deve essere misurata in numero di ore e di secondi

11

Valori di default

Per attributi di tabelle e dominiValore che viene assegnato se non ne viene specificato un altro

default <GenericoVal | user | null> GenericoVal: valore o espressione con tipo

compatibile con il dominio user: identificativo utente null: …

Per esempio: NumeroFigli smallint default 0

12

Definizione di domini

Un dominio è un insieme di valori ammissibili per un attributo (concetto simile a quello di “tipo”)Si possono creare domini nuovi a partire da quelli di base

create domain NomeDominio as TipoDiDato [ValoreDiDefault][Vincolo]

Per esempio: create domain anniCorso as smallint default 1 not null

13

Definizione di schemi di DB

Schema di DB come collezione di oggetti: domini, tabelle, asserzioni, viste, privilegi

create schema [NomeSchema]

[[authorization] Autorizzazione]

{DefElementoSchema} Autorizzazione: se omessa, proprietario colui

che ha lanciato il comando NomeSchema: se omesso, nome =

identificatore del proprietario

Uno schema può essere definito incrementalmente

14

Definizione delle tabelle

Una tabella è una collezione di attributi e può avere vincoli

create table NomeTabella

(NomeAttr Dominio [ValoreDiDefault][Vincoli]

{,NomeAttr Dominio[ValoreDiDefault][Vincoli]}

AltriVincoli

)

15

Per esempio:

create table Persona

(Nome char varying(20) not null unique,

Cogn char varying(20) primary key,

NumFigli smallint default 0)

Inizialmente tabella vuota e il creatore ha tutti i privilegi

Definizione delle tabelle

16

Vincoli intrarelazionali

Su tabelle e domini: specificano proprietà che ogni istanza del DB deve verificareVincoli predefiniti:

not null: non può assumere valore nullo unique: attributo (oppure insieme di attributi unique(attr {,attr})) è superchiave

primary key: attributo (oppure insieme di attributi) come chiave primaria (implica il not null)

Vincoli non predefiniti: con check (vedremo dopo)

17

Vincoli interrelazionali (I)

foreign key: chiave esterna, impone che su ogni riga della tabella corrente (interna) il valore degli attributi specificati, se diverso da null, sia presente nelle tuple della tabella esterna tra i valori dei corrispondenti attributi La sintassi impone che l’insieme di attributi della tabella esterna sia unique, cioè superchiave (vincolo di integrità referenziale)

18

Vincoli interrelazionali (II)

references: foreign key su singolo attributoEsempio 1 create table Impiegato

( …

Dip char(15)

references

Dipartimento(NomeDip)

…)

Impiegato(Matr,Nome,Cogn,Dip,Stip)

Dipartimento(NomeDip,…)

19

Vincoli interrelazionali (III)Esempio 2 create table Impiegati (Matr character(6) primary key, Cogn character(20) not null, Nome character(20) not null, Dip character(15) references Dipartimento(NomeDip), Stip numeric(6) default 0, unique(Nome,Cogn), foreign key(Nome,Cogn) references Anagrafica(Nome,Cognome))

NB Conta l’ordine degli attributi nella foreign key

20

Gestione vincoli

Vincoli intrarelazionali: DBMS verifica che per ogni istanza (inserimento/modifica) i vincoli non siano violati Se violazione, aggiunta/modifica rifiutata e

messaggio di errore

Vincoli interrelazionali: non tutte le violazioni vengono trattate nello stesso modo politiche di reazione alle violazioni

21

Gestione vincoli integrità referenziale (I)

Violazione su tabella interna (inserimento/modifica riga) DBMS rifiuta l’operazioneViolazione su tabella esterna (cancellazione/modifica riga a cui fa riferimento tabella interna) l’esterna è la tabella principale politiche specifiche

22

Gestione vincoli integrità referenziale (II): modifica

cascade: valore di tabella esterna riportato su tutte tabella interneset null: inserisce null come valore degli attributi corrispondenti nelle tabella interneset default: inserisce il valore di defaultno action: non si permette la modifica alla tabella esterna

23

Gestione vincoli integrità referenziale (III): cancellazione

cascade: cancellazione delle righe corrispondenti anche dalle tabella interneset null: inserisce null come valore degli attributi nelle tabella interne corrispondenti ai valori delle righe cancellate nella tabella esternaset default: inserisce il valore di defaultno action: non si permette la cancellazione nella tabella esterna

24

Gestione vincoli integrità referenziale (IV)

cascade prevede stretta dipendenza tra tabelle esterna ed interneSi possono adottare politiche diverse per cancellare/modificareSi possono innescare reazioni a catena!

25

Specifica di politica di reazione

La politica di reazione viene specificata immediamente dopo il vincolo di integrità:

on <delete | update>

<cascade | set null |

set default | no action>

26

Esempio 2:create table Impiegati (Matr character(6) primary key, Cogn character(20) not null, Nome character(20) not null, Dip character(15), Stip numeric(6) default 0, unique(Cogn,Nome) foreign key(Dip) references Dipartimento(NomeDip) on delete set null on update cascade)

Specifica di politica di reazione

27

Modifica schemi DB (I)

• Per modificare domini già definiti:

alter domain NomeDominio< set default ValoreDefault | drop default | add constraint DefVincolo | drop constraint NomeVincolo >

Quando si mette un nuovo vincolo i dati lo

debbono soddisfar

e

28

Modifica schemi DB (II)• Per modificare schemi già definiti:

alter table NomeTabella< alter column NomeAttr

< set default NuovoDefault | drop default>

add constraint DefVincolo | drop constraint NomeVincolo |

add column DefAttr | drop column NomeAttr >

• Per esempio:

alter table Dipartimento add column Nuff numeric(4)

29

Modifica schemi DB (III)

Per rimuovere componentidrop <schema | domain | table | view |

assertion > NomeElemento [restrict | cascade]

restrict: il comando non deve essere eseguito in presenza di componenti non vuote o usate (è opzione di default)cascade: si rimuove tutto (esempio non banale: LongString:char(100) char(100) sostituito)

30

Modifica schemi DB (IV)

Come per la modifica, si possono avere reazioni a catena con la cascadedrop cascade molto potente, potrebbe avere conseguenze indesiderate se non si conosco tutte le dipendenze del DBMolti sistemi permettono di controllare il risultato prima di fare la drop cascade

31

Inserire (I)insert into NomeTabella [(ListaAttr)] <values (ListaDiValori) | SelectSQL>

• Esempio 1:insert into Dipart (Nome,Citta) values (‘produzione’, ‘Torino’)

• Esempio 2:insert into Prodotti-Milanesi (select Cod,Descr from Prodotto where Luogo = ‘Milano’)

query

32

Inserire (II)

Conta l’ordine con cui si elencano gli attributi (anche nella query) e i valoriSe 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

33

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)

34

Delete (II)

Se non c’è nessuna condizione, cancella tutta la NomeTabella (attenzione ai cascade!!!)

1. delete from Dipart diverso da2. drop table Dipart cascade diverso da3. 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 tabella3. non cancella la tabella se ha tuple

35

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

36

Update (II)• Esempio 2:update Impiegato set Stip = Stip*1.1 where Stip <= 30

update Impiegato set Stip = Stip*1.15 where Stip > 30

Occorre invertirle, sennò non funziona, infatti SQL è set-oriented e non tuple-oriented (come alternativa paraziale uso dei cursori…)

15%

10%

37

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

38

Vincoli (II)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)))

39

Vincoli (II)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)))

Vincolo di

integrità

Un solo impiegat

o con stesso nome/

cognome

40

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)

41

Asserzioni (I)

Asserzione = vincolo su schema di relazione e non su istanza (tabella) o attributo che permettono di avere DB consistenteLa drop può cancellare anche le asserzioni

create assertion NomeAsserzione check (Condizione)

Per esempio: create assertion Alm-Un-Imp (check (1 <= (select count(*) from Impiegato))

42

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>

43

Check e Asserzioni

Se i dati non soddisfano i vincoli:1. Roll-back parziale: si disfa la

modifica2. 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

44

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

45

Viste (II)

Utili per interrogazioni complesseLe 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)

46

Viste (III)

Su certe viste possibile fare modifiche che si ripercuotono sulle tabelle di base (“reali”)In generale, problemi se ci sono join molto complessiRegola: 1 tupla vista = 1 tupla tabella di baseNei sistemi commerciali viste definite su una sola tabella reale e l’insieme attributi della vista contengono chiave primaria della tabella

47

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

48

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

49

Controllo dell’accesso (III)

grant Privilegi on Risorseto Utenti [with grant option]

revoke Privilegi on Risorsefrom Utenti [restrict | cascade]

Per propagare

i priv

Solo chi dà può

revocare