SQL: Lezione 7 Nataliya Rassadko rassadko@disi.unitn.it.

transcript

SQL: Lezione 7

Nataliya Rassadkorassadko@disi.unitn.it

Agenda

User-defined dominiDEFAULTVincoli intra-relazionali

NOT NULL, UNIQUE, PRIMARY KEY Vincoli sui valori delle tuple

Vincoli inter-relazionali Integrità referenziale

Vincoli genericiModifica degli schemi

ALTER, DROPViste, Indici, Asserzioni

DEFINIZIONE DEI DOMINI

Nella definizione delle tabelle si può far riferimento ai domini predefiniti del linguaggio o a domini definiti dall’utente a partire dai domini predefiniti.

Definiscono i valori accetabili

Come nei linguaggi ad alto livello (es. C) è possibile definire nuovi domini (tipi di dati) a partire da quelli predefiniti, anche se il costruttore è più limitato.

Non si possono creare array o strutture poiché il modello relazionale impone attributi definiti su un dominio elementare.

E’ però possibile associare dei vincoli ad un dominio definito dall’utente. Se si modifica la definizione di un dominio, la modifica si propaga a tutte le tabelle.

La sintassi per la creazione di nuovi domini è la seguente:

CREATE DOMAIN NomeDominio AS TipoDiDominioElementare [ValorePredefinito] [Vincoli]

Un dominio è caratterizzato: dal proprio nome, da un dominio elementare (che può essere

predefinito o definito in precedenza dall’utente), da un eventuale valore di default, e infine da un insieme di vincoli (eventualmente vuoto)

che rappresenta un insieme di condizioni che devono rispettare i valori del dominio

ESERCIZIO

Creare un dominio che permetta di assegnare ad un attributo un valore intero, non nullo, compreso tra 18 e 30

Altri domini

CREATE DOMAIN Voto AS SMALLINT

NOT NULL

CHECK ( value >=18 AND value <= 30 )

CREATE DOMAIN NumberOfChildren AS SMALLINT Default 0

CREATE DOMAIN persons_name CHAR(30)

CREATE DOMAIN street_address CHAR(35)

VALORI PREDEFINITI

Specifica il valore che deve assumere l’attributo quando viene inserita una riga nella tabella senza che sia specificato un valore per l’attributo stesso

Quando il valore di default non è specificato, si assume come default il valore Null.

La sintassi per la specifica dei valori di default è:

default (GenericoValore | user| null)

VALORI DEFAULT

GenericoValore rappresenta un valore compatibile con il dominio, rappresentato direttamente come valore costante o, più in generale, ottenuto come risultato della valutazione di un espressione.

L’opzione user impone come valore di default l’identificativo dell’utente (login) che esegue il comando di aggiornamento della tabella.

L’opzione null corrisponde al valore di default di base.

VINCOLI

Sia nella definizione di domini che nella definizione delle tabelle è possibile definire dei vicoli.

Delle proprietà che devono essere verificate da ogni istanza della base di dati.

I vincoli si dividono in due categorie: vincoli intrarelazionali (che coinvolgono una sola tabella) e vincoli interrelazionali (in cui il predicato considera diverse relazioni)

VINCOLI INTRARELAZIONALI

I più semplici vincoli di tipo intrarelazionale sono i vincoli

NOT NULL,Unique,Primary Key.

NOT NULL

Il vincolo NOT NULL indica che il valore nullo non è ammesso come valore per quel specifico attributo.

Quando viene assegnato questo vincolo, l’attributo deve essere sempre specificato, tipicamente in fase di inserimento.

Se all’attributo è però associato un valore di default diverso dal valore nullo, allora diventa possibile effettuare l’inserimento di una riga per la tabella senza fornire un valore per l’attributo.

NOT NULL: Esempio

Il vincolo viene specificato facendo seguire alla definizione dell’attributo le parole chiave NOT NULL:

MovieTitle VARCHAR(50) NOT NULL;

UNIQUE

Un vincolo UNIQUE si applica ad un attributo o un insieme di attributi.

Impone che i valore dell’attributo siano una (super)chiave: cioè righe differenti della tabella non possano avere gli stesi valori.

Viene fatta un’eccezione per il valore NULL , il quale può comparire su diverse righe senza violare il vincolo.

Si assume che i valori NULL siano tutti i diversi tra loro.

UNIQUE

La definizione di questo vincolo può avvenire in due modi

La prima alternativa può essere usata unicamente quando bisogna definire il vincolo su un solo attributo.

Si fa seguire la specifica dell’attributo dalla parola chiave unique:StudioName VARCHAR(50) NOT NULL UNIQUE;

UNIQUE

La seconda alternativa è invece necessaria quando bisogna definire il vincolo per un insieme di attributi.

Dopo aver definito gli attributi della tabella, si usa la seguente espressione

Un esempio d’uso della sintassi è il seguente:

Unique (Attributo {,Attributo})

Nome character(20) not null

Cognome character(20) not null

Unique (Cognome,Nome)

UNIQUE

Si noti che la precedente definizine è ben diversa da una definizione come:

Nel primo caso si impone che non ci siano due righe che abbiano uguali sia il nome che il cognome, nel secondo si ha una violazione se nelle righe compaiono due volte lo stesso nome o lo stesso cognome.

Nome character(20) not null Unique

Cognome character(20) not null Unique

PRIMARY KEY

SQL permette di specificare il vincolo primary key una sola volta per ogni tabella.

E’ possibile utilizzare un numero arbitrario di volte i vincoli unique e not null.

Come per il vincolo unique, il vincolo primary key può essere definito direttamente su di un singolo attributo, oppure essere definito elencando più attributi che costituiscono l’identificatore.

PRIMARY KEY

Gli attributi che fanno parte della chiave primaria non possono assumere il valore nullo.

La definizione di una primary key implica per tutti gli attributi della chiave primaria una definizione di not null, che può quindi essere omessa.

PRIMARY KEY

La definizione impone che la coppia di attributi Nome e Cognome costituiscano la chiave primaria.

Nome character(20)

Cognome character(20)

Dipart character(16)

Stipendio numeric(9) default 0,

Primary Key (Cognome,Nome)

VINCOLI INTRARELAZIONALI Cont’d

Un vincolo è intrarelazionale se il suo soddisfacimento è definito rispetto singole relazioni della base di dati.

È un vincolo che può essere valutato su ciascuna tupla indipendententemente dalle altre.

Esistono due categorie principali: Vincoli di tupla Vincoli di valore

VINCOLI DI VALORE

Un vincolo definito con riferimento a singoli valori viene detto vincolo su valori o vincolo di dominio in quanto impone una restrizione sul dominio dell'attributo.

Un esempio è il vincolo not null, usato per dichiarare che un dato attributo non può assumere il valore nullo. Solitamente questo attributo viene corredato da un valore di default, che viene assegnato automaticamente all'atto della creazione di una nuova tupla

VINCOLI DI VALORE e TUPLA

Il primo vincolo (vincolo di valore in quanto coinvolge un solo attributo) indica che l’attributo VOTO può assumere solo valori compresi tra 18 e 20.

Il secondo vincolo indica che è amissibile la lode solo se il voto è pari a 30.

(Voto >= 18) AND (Voto <= 30)

(NOT (Lode = ‘lode’) AND (Voto = 30)

VINCOLI DI TUPLA

I vincoli di tupla ammettono anche espressioni piu complesse, purchè definite sui valori delle singole tuple.

Ad esempio, su una relazione

E’ possibile definire il vincolo che impone, che il netto sia pari alla differenza fra l’importo originario e le ritenute

PAGAMENTI(Data,Importo,Ritenute,Netto)

Netto=Import-Ritenute

ESERCIZIO

Ricreare, all’interno dello schema work, le tabelle presenti all’interno dello scema imdb, fissando i vincoli necessari.(Cancellare e ricreare la tabella Movie (nel db di prova) appena creata: Per cancellare una tabella DROP nome_tabella)

VINCOLI INTERRELAZIONALI

I più diffusi e significativi sono i vincoli di INTEGRITA’ REFERENZIALE.

In SQL per la loro definizione si usa l’apposito vincolo di FOREIGN KEY (chiave esterna)

Crea un legame tra i valori di un attributo della tabella corrente (interna) e i valori di un altra tabella (esterna)

INTEGRITA’ REFERENZIALE

Il vincolo impone che per ogni riga della tabella il valore dell’attributo specificato, se diverso dal valore nullo, sia presente nelle righe della tabella esterna tra i valori del corrispondente attributo

MovieExec Studio

name address cert#

Kubrick 45 ave. 3565

… … …

name presC#

Paramount Pictures 3565

… …

MovieExec Studio

ESEMPIO:E’ possibile forzare il db a controllare che per ogni valore di presC# nella relazione Studio, ci sia una righa con lo stesso valore per l’attributo cert# nella relazione MovieExec

Il Db blocchera’ tutti i tentativi di inserimento di una riga in Studio con un presidente “unknown”

In SQL e’ possibile dichiarare un attributo (o piu’) di una relazione (R1) come una foreign key, riferendosi agli attributi di una altra relazione (R2), cosi’ che:

L’attributo di R2 riferito deve essere dichiarato come UNIQUE o come PRIMARY KEY.

I valori per la chave esterna che appaiono in R1 devono apparire anche negli attributi di riferimento nella tabella R2

In modo simile al caso delle Primary Keys, le Foreign Keys possono essere definite nell’istruzione CREATE TABLE in uno dei seguenti modi:

CREATE TABLE nome_table {attributo1 attributo2 attributoN REFERENCES “Tabella Esterna“ (“attributo") }

CREATE TABLE nome_table {attributo1 attributo2 attributoN foreign key (attributo1, attributo2) REFERENCES “Tabella Esterna“ (lista attributi)}

Per gli altri vincoli visti fino ad ora, si assume che quando il sistema rileva una violazione, il comando di aggiornamento venga rifiutato, segnalando l’errore all’utente.

Per i vincoli di integrità referenziale invece è possibile scegliere le azioni da adottare quando viene rilevata una violazione.

In particolare, per le operazioni di modifica, è possibile reagire in uno dei seguenti modi: Cascade: il nuovo valore dell’attributo della tabella esterna

viene riportato su tutte le corrispondenti righe della tabella interna

Set null: all’attributo referente viene assegnato il valore nullo al posto del valore modificato nella tabella esterna.

Set default: all’attributo referente viene assegnato il valore di default al posto del valore modificato nella tabella esterna

No action: l’azione di modifica non viene consentita, senza che il sistema provi a riparare la violazione.

In particolare, per le operazioni di cancellazione, è possibile reagiere in uno dei seguenti modi: Cascade: tutte le righe della tabella interna corrispondenti

alla riga cancellata vengono cancellate. Set null: all’attributo referente viene assegnato il valore

nullo al posto del valore cancellato nella tabella esterna. Set default: all’attributo referente viene assegnato il

valore di default al posto del valore cancellato nella tabella esterna.

No action: la cancellazione non viene consentita.

Le politiche possono essere definite , in maniera indipendente per cancellazione e modifica

Le politiche da adottare sono definite durante la dichiarazione della chiave esterna sulla cancellazione o sull‘aggiornamento. CREATE TABLE "Studio"

( "name" CHAR(30) PRIMARY KEY, "address" VARCHAR(255), "presC#" INT REFERENCES "MovieExec"("cert#") ON DELETE SET NULL ON UPDATE CASCADE );

VINCOLI GENERICI

• La clausola check permette di specificare dei nuovi vincoli sulle tabelle rispetto a quelli fino ad ora visti.

Le condizioni che si possono essere applicate con il vincolo CHECK sono le stesse che possono apparire come clausola where di un interrogazione SQL.

Check (Condizione)

La condizione deve essere sempre verificata affinchè la base di dati sia corretta.

E’ possibile definire un vincolo sul valore di un attributo specificando una numerazione legale, una condizione aritmetica o una subquery.

"netWorth" INT CHECK ("netWorth">=100000)

E’ possibile definire un vincolo sulla riga :

Ogni volta che una riga e’ inserita o modifcata, viene controllato che il vincolo sia rispettato dalla nuova riga inserita.

Se il vincolo non e’ rispettato l’inserimento viene rifiutato.

CREATE TABLE “nameTable"( “att1" tipo vincolo, “att2” tipo vincolo,

CHECK (condizione sulla riga));

ESERCIZIO

Ridefinire lo schema MovieStar imponendo che il nome dell’attore non abbia il prefisso Ms. se il sesso è diverso da ‘F’.

CREATE TABLE "MovieStar"( "name" CHAR(30) PRIMARY KEY, "address" VARCHAR(255), "gender" char(1), "birthday" DATE, CHECK ("gender"='F' OR "name" NOT LIKE 'Ms.%'));

Movie(title,year,length,inColor,studioName,producerC#)StarsIn(movieTitle,movieYear,starName) MovieStar(name,address,gender,birthdate) MovieExec(name,address,cert#,netWorth) Studio(name,address,presC#)

ESERCIZIO

Ridefinire lo schema Movie imponendo che l’attributo inColor non possa valere ‘true’ se l’anno di produzione del film è minore di 1940.Movie(title,year,length,inColor,studioName,producerC#)StarsIn(movieTitle,movieYear,starName) MovieStar(name,address,gender,birthdate) MovieExec(name,address,cert#,netWorth) Studio(name,address,presC#)

CREATE TABLE "Movie"( “title” VARCHAR(255) PRIMARY KEY, “year” INT PRIMARY KEY, …

CHECK (“year"<‘1940' OR “inCOLOR" NOT LIKE ‘true'));

VINCOLI

E’ possibile definire un nome per i vincoli

E’ possibile aggiungere dei vincoli ad uno schema gia definito

"name" CHAR(30) CONSTRAINT "NameIsKey" PRIMARY KEY

CONSTRAINT "RightTitle" CHECK ("gender"='F' OR "name" NOT LIKE 'Ms.%'));

ALTER TABLE imdb."MovieStar" ADD CONSTRAINT "NameIsKey" PRIMARY KEY("name");

MODIFICA DEGLI SCHEMI

SQL fornisce primitive per la manipolazione degli schemi delle basi di dati.

Queste primitive permettono di modificare le definizioni di tabelle precedentemente introdotte.

I comandi che vengono utilizzati a questo fine sono ALTER e DROP

Il comando ALTER permette di modificare domini e schemi di tabelle.

Il comando può assumere varie forme:

Alter domain NomeDominio (set default ValoreDefault| drop default | add constraint DefVincolo | drop constraint NomeVincolo)

ALTER: Esempio

ALTER TABLE MovieStar ADD COLUMN phone CHAR(16);

Tramite alter domain e alter table è possibile aggiungere e rimuovere vincoli e modificare i valori di default.

È possibile aggiungere e eliminare attributi e vincoli sullo schema di una tabella.

Quando si definisce un nuovo vincolo, questo deve essere soddisfatto dai dati già presenti, se l’istanza contiene delle violazioni per il nuovo vincolo l’inserimento viene rifiutato.

ALTER: Esempio

ALTER TABLE MovieStar ADD CONSTRAINT NameIsKey PRIMARY KEY (name);

ALTER TABLE MovieStar ADD CONSTRAINT NoAndro CHECK (gender IN (‘M’, ‘F’));

ALTER TABLE MovieStar ADD CONSTRAINT RightTitle CHECK (gender=‘F’ OR name NOT LIKE ‘MS.%’);

Mentre il comando alter effettua delle modifiche sui domini o sullo schema delle tabelle.

Il comando drop permette di rimuovere dei componenti, siano essi schemi, domini, tabelle, viste o asserzioni.

L’opzione restrict spefica che il comando non deve essere eseguito in presenza di oggetti non vuoti: uno schema non è rimosso se contiene tabelle o altri

oggetti Una tabella non è rimossa se possiede delle righe o se

è presente in qualche definizione di tabella o vista. Una vista non è rimossa se è utilizzata nella

definizione di altre viste o tabelle.

Con l’opzione cascade invece, tutti gli oggetti specificati devono essere rimossi.

Quando si rimuove uno schema non vuoto, anche tutti gli oggetti che fanno parte dello schema vengono eliminati.

In generale, l’opzione cascade attiva una reazione a catena, per cui tutti gli elementi che dipendono da un elemento rimosso vengono rimossi

DROP: Esempio

ALTER TABLE MovieStar DROP COLUMN birthdate;

ALTER TABLE MovieStar DROP CONSTRAINT NameIsKey;

ALTER TABLE MovieStar DROP CONSTRAINT NoAndro;

ALTER TABLE MovieStar DROP CONSTRAINT RightTitle;

Sono tabelle virtuali il cui contenuto dipende dal contenuto delle altre tabelle presenti nel db.

Esistono anche le viste materializzateLe viste vengono definite associando un nome

ed una lista di attributi al risultato dell’esecuzione di una interrogazione.

Nell’interrogazione che definisce la vista possono comparire anche altre viste

La sintassi SQL non permette dipendenze ricorsive, né immediate: definendo una vista in termini di se stessa.

né transitive: situazioni in cui una vista V1 è definita usando un vista V2, V2 usando V3 e cosi via, infine Vn usando V1.

Si definisce una vista utilizzando il comando:

L’interrogazione SQL deve restituire un insieme di attributi pari a quelli contenuti nello schema

L’ordine nella target list deve corrispondere all’ordine degli attributi dello schema della vista.

Create view NomeVista [(ListaAttributi)] as SelectSQL [with[local|cascaded]check option]

ESERCIZIO

Definire una vista EXECREACH che contenga tutti i produttori con un networth superiore a 10 milioni.Movie(title,year,length,inColor,studioName,producerC#)StarsIn(movieTitle,movieYear,starName) MovieStar(name,address,gender,birthdate) MovieExec(name,address,cert#,netWorth) Studio(name,address,presC#)

Create view EXECREACH (name,address,cert#,netWorth) as Select name,address,cert#,netWorth from MovieExec where netWort>’10.000.000’

ESERCIZIO

Definire una vista MovieOld in cui sono contenuti tutti i film in bianco e nero prodotti prima del 1950Movie(title,year,length,inColor,studioName,producerC#)StarsIn(movieTitle,movieYear,starName) MovieStar(name,address,gender,birthdate) MovieExec(name,address,cert#,netWorth) Studio(name,address,presC#)

Create view ParamauntMovie as

Select title,year

from Movie

where studioName=‘Paramaount’

VISTE VIRTUALI

Possiamo creare SELECT su viste

i query saranno rescritte

Select title,year

from Movie

where studioName=‘Paramount’

SELECT title FROM ParamountMovie WHERE year=1979

AND studioName=‘Paramount’

Su alcune viste è permesso effettuare operazioni di modifica

Le modifiche verranno tradotte negli opportuni comandi di modifica a livello delle tabellle di base da cui la vista dipende.

Non è sempre possibile determinare un modo univoco in cui la modifica sulla vista possa essere riportata sulle tabelle di base

Si incontrano grossi problemi soprattutto quando la vista è definita tramite una join tra più tabelle.

Lo standard SQL permette che una vista sia aggiornabile solo quando una sola riga di ciascuna tabella di base corrisponde a una riga della vista.

I sistemi commerciali tipicamente considerano una vista aggiornabile solo se è definita su una sola tabella.

VISTE: Esempio di inserimento

Create view ParamauntMovie as Select title,year

from Movie where studioName=‘Paramount’

INSERT INTO ParamountMovie VALUES (‘Star Treck’, 1979)

Movie(title,year,length,inColor,studioName,producerC#)

‘Star Treck’, 1979, 0, NULL, ?, NULL

Select studioName, title,year

from Movie where studioName=‘Paramount’

Le viste in SQL possono servire per formulare delle interrogazioni che non sarebbero altrimenti esprimibili, aumentando il potere espressivo del linguaggio.

Mediante la definizione di opportune viste, è possibile definire in SQL interrogazioni che richiedono: di utilizzare diversi operatori aggregati in cascata. Che fanno un uso sofisticato dell’operatore unione.

In generale, le viste possono essere considerate come uno strumento che permette di estendere la possibilità di nidificare le interrogazioni.

ESERCIZIO

Data la tabella IMPIEGATO, determinare il dipartimento che spende la cifra massima in stipendi (utilizzando le viste)

Impiegato(matricola,nome,cognome,dipart,ufficio,stipendio,citta)

create view BudgetStipendi(dipart,totaleStipendi) as Select dipart, sum(stipendio) from Impiegato

group by dipart

Select Dip from BudgetStipendi where TotaleStipendi= (Select max(TotaleStipendi) from BugdgetStipendi)

ESERCIZIO

Formulare l’interrogazione precedente senza l’uso delle viste.Impiegato(matricola,nome,cognome,dipart,ufficio,stipendio,citta)

Select Dipart from Impiegato group by Dipart having sum(Stipendio) >= all(select sum(Stipendio) from Impiegato group by Dipart)

ESERCIZIO

Scrivere una vista che restituisca il numero di uffici per ciascun dipartimento

In questo caso, la vista può servire per restrizione dell’accesso ai dati

Create view DipartUffici(nomeDip,nrUffici) as Select dipart, count(distinct ufficio) from Impiegato

group by dipart

Impiegato(matricola,nome,cognome,dipart,ufficio,stipendio,citta)

INDICI

Gli indici permettono ad un DBMS di accedere ai dati più rapidamente.

Il sistema crea della strutture dati interne (gli indici) per la selezione veloce di determinate righe.

La struttura indica al DBMS dove si trova una certa riga su una tabella con colonne indicizzate, più o meno come il glossario di un libro indica la pagina in cui appare una determinata parola.

INDICI

Consideriamo la seguente query:

Possono esserci più di 10000 film prodotti dalla Disney ma, solo 200, prodotti nel 1990

Risulta molto piu’ efficiente per prima cosa cercare i film prodotti nel 1990 e dopo controllare il valore contenuto nel campo studio.

SELECT * FROM imdb.”Movie” WHERE “studioName”=‘Disney’ AND “year”=1990

INDICI

La sintassi per la creazione degli indici è la seguente

La sintassi per l’eliminazione di indici è la seguente

CREATE INDEX nomeIndice ON NomeTabella(nomeCampo)

DROP INDEX nomeIndice

INDICI

La selezione degli indici richiede una riflessione da parte del database designer. Due importanti fattori da considerare sono:

Un indice su un attributo accellera notevolmente le query in cui quell’attributo e’ specificato.

Le inserzioni, cancellazioni e modifiche sulle tabelle sulle quali sono stati creati degli indici sono piu’ complesse e richiedono maggior tempo.

INDICI

Una strategia generale:

Se su una tabella vengono effettuate query più frequentemente di modifiche, allora specificare degli indici su questa tabella ha senso.

Se le modifiche sono predominanti rispetto alle query, dovremo porre molta attenzione alla creazione degli indici

SQL: Lezione 7 Nataliya Rassadko rassadko@disi.unitn.it.

Documents