490 likes | 671 Views
SQL (III). Data Definition Language/ Data Manipulation Language. Riprendiamo SQL…. Abbiamo visto parte del DML (Data Manipulation Language), le query Linguaggio di interrogazione per DBMS relazionali
E N D
SQL (III) Data Definition Language/ Data Manipulation Language
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
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
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
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
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
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
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
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
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
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
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
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
Definizione delle tabelle • Una tabella è una collezione di attributi e può avere vincoli create tableNomeTabella (NomeAttrDominio [ValoreDiDefault][Vincoli] {,NomeAttr Dominio[ValoreDiDefault][Vincoli]} AltriVincoli )
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
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)
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)
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,…)
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
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
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
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
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
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!
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>
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)
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
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)
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)
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
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
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
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)
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
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
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%
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
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)))
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à
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)
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))
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>
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
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
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)
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
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
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
Controllo dell’accesso (III) Per propagare i priv grantPrivilegionRisorse toUtenti [with grant option] revokePrivilegionRisorse fromUtenti [restrict | cascade] Solo chi dà può revocare