SQL (III)

1. 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 transazioni • Ricordiamo: 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 volte • x | y: x e y sono in alternativa • ( , ) appartengono a SQL schema  relazione  tabella riga  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 variabile • Si può scegliere tra diversi alfabeti • character [varying][(lunghezza)] [character setFamiglia] • Forme abbreviate: char,varchar • Per esempio: • character (20) • char varying (1000) character set Greek

6. Bit • Per rappresentare bit (0,1) e stringhe di bit a lunghezza fissa o variabile • Utili per rappresentare in modo compatto insiemi di valori booleani • bit [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.999 • Per 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 domini • Valore 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 createdomainNomeDominioasTipoDiDato [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 tableNomeTabella (NomeAttrDominio [ValoreDiDefault][Vincoli] {,NomeAttr Dominio[ValoreDiDefault][Vincoli]} AltriVincoli )

15. Definizione delle tabelle • 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

16. Vincoli intrarelazionali • Su tabelle e domini: specificano proprietà che ogni istanza del DB deve verificare • Vincoli 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 attributo • Esempio 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’operazione • Violazione 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 interne • setnull: inserisce null come valore degli attributi corrispondenti nelle tabella interne • setdefault: inserisce il valore di default • no action: non si permette la modifica alla tabella esterna

23. Gestione vincoli integrità referenziale (III): cancellazione • cascade: cancellazione delle righe corrispondenti anche dalle tabella interne • set null: inserisce null come valore degli attributi nelle tabella interne corrispondenti ai valori delle righe cancellate nella tabella esterna • set default: inserisce il valore di default • no action: non si permette la cancellazione nella tabella esterna

24. Gestione vincoli integrità referenziale (IV) • cascade prevede stretta dipendenza tra tabelle esterna ed interne • Si possono adottare politiche diverse per cancellare/modificare • Si 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. Specifica di politica di reazione • 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)

27. Modifica schemi DB (I) • Per modificare domini già definiti: • alter domain NomeDominio • < set defaultValoreDefault | • dropdefault | • add constraintDefVincolo | • drop constraintNomeVincolo • > Quando si mette un nuovo vincolo i dati lo debbono soddisfare

28. Modifica schemi DB (II) • Per modificare schemi già definiti: • alter tableNomeTabella • < alter columnNomeAttr • < setdefaultNuovoDefault | • dropdefault> • add constraintDefVincolo | • dropconstraintNomeVincolo | • add columnDefAttr | • drop columnNomeAttr > • Per esempio: • alter table Dipartimento add column Nuff numeric(4)

29. Modifica schemi DB (III) • Per rimuovere componenti drop <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 cascade • drop cascade molto potente, potrebbe avere conseguenze indesiderate se non si conosco tutte le dipendenze del DB • Molti 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 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

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!!!) • delete from Dipartdiverso da • drop table Dipart cascadediverso da • drop table Dipart restrict cancella le righe della tabella(se c’eracascade, cancella righe di altre tabella legate da vincoli integrità referenziale) rimuove la tabella non cancella la tabella se ha tuple

35. Update (I) • updateNomeTabella • setAttributo = <Espressione | • SelectSQL | • null | default> • {,Attributo = <…>} • [whereCondizione] • 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…) 10% 15%

37. Vincoli (I) • Ci sono i vincoli predefiniti (unique, primary key, not null, foreign key…) • In SQL2 si ha anche check checkCondizione 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))) Un solo impiegato con stesso nome/ cognome Vincolo di integrità

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 consistente • La drop può cancellare anche le asserzioni create assertionNomeAsserzione check (Condizione) • Per esempio: create assertion Alm-Un-Imp (check (1 <= (select count(*) from Impiegato))

42. Check e Asserzioni • Immediati: verificate dopo ogni modifica (per esempio, i vincoli predefiniti) • Differiti: solo dopo una serie di operazioni (transazione) set constraintNomeVincolo <immediate | deferred>

43. Check e Asserzioni Se i dati non soddisfano i vincoli: • Roll-back parziale: si disfa la modifica • 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 viewNomeVista[(ListaAttr)] • asSelectSQL [with [local | cascaded] | • checkoption] • 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 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)

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

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” • granterevokeper 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) Per propagare i priv grantPrivilegionRisorse toUtenti [with grant option] revokePrivilegionRisorse fromUtenti [restrict | cascade] Solo chi dà può revocare