Atzeni, Ceri, Paraboschi, Torlone Basi di dati McGraw-Hill, 1999

Atzeni, Ceri, Paraboschi, TorloneBasi di dati

McGraw-Hill, 1999

Capitolo 4:

SQLSQL

11/10/2001

11/10/2001 Atzeni-Ceri-Paraboschi-Torlone, Basi di dati, Capitolo 4

2

SQL

• Structured Query Language• è un linguaggio con varie funzionalità:

• contiene sia il DDL sia il DML• ne esistono varie versioni • vediamo gli aspetti essenziali, non i

dettagli


3

SQL: "storia"

• prima proposta SEQUEL (1974); • prime implementazioni in SQL/DS e

Oracle (1981)• dal 1983 ca. "standard di fatto" • standard (1986, poi 1989 e infine 1992,

1999) recepito in parte


4

Definizione dei dati in SQL

• Istruzione CREATE TABLE: • definisce uno schema di relazione e

ne crea un’istanza vuota• specifica attributi, domini e vincoli


5

CREATE TABLE, esempio

CREATE TABLE Impiegato(Matricola CHAR(6) PRIMARY KEY, Nome CHAR(20) NOT NULL,

Cognome CHAR(20) NOT NULL, Dipart CHAR(15),Stipendio NUMERIC(9) DEFAULT 0, FOREIGN KEY(Dipart) REFERENCES

Dipartimento(NomeDip), UNIQUE (Cognome,Nome) )


6

Domini

• Domini elementari (predefiniti)• Domini definiti dall'utente (semplici, ma

riutilizzabili)


7

Domini elementari

• Carattere: singoli caratteri o stringhe, anche di lunghezza variabile

• Bit: singoli booleani o stringhe• Numerici, esatti e approssimati• Data, ora, intervalli di tempo


8

Definizione di domini

• Istruzione CREATE DOMAIN: • definisce un dominio (semplice),

utilizzabile in definizioni di relazioni, anche con vincoli e valori di default


9

CREATE DOMAIN, esempio

CREATE DOMAIN Voto AS SMALLINT DEFAULT NULLCHECK ( value >=18 AND value <= 30 )


10

Vincoli intrarelazionali

• NOT NULL • UNIQUE definisce chiavi• PRIMARY KEY: chiave primaria (una

sola, implica NOT NULL)• CHECK, vedremo più avanti


11

UNIQUE e PRIMARY KEY

• due forme:• nella definzione di un attributo, se

forma da solo la chiave• come elemento separato


12






13

Matricola CHAR(6) PRIMARY KEY

Matricola CHAR(6),…,PRIMARY KEY (Matricola)

PRIMARY KEY, alternative


14






15

Chiavi su più attributi, attenzione

Nome CHAR(20) NOT NULL, Cognome CHAR(20) NOT NULL, UNIQUE (Cognome,Nome),

Nome CHAR(20) NOT NULL UNIQUE, Cognome CHAR(20) NOT NULL UNIQUE,

• Non è la stessa cosa!


16

Vincoli interrelazionali

• CHECK, vedremo più avanti • REFERENCES e FOREIGN KEY permettono di

definire vincoli di integrità referenziale • di nuovo due sintassi

• per singoli attributi• su più attributi

• E' possibile definire politiche di reazione alla violazione


17

Matricola

3987

3295

9345

Vigili Cognome

Rossi

Neri

Neri

Nome

Luca

Piero

Mario

Mori Gino7543

InfrazioniCodice

34321

73321

64521

53524

Data

1/2/95

4/3/95

5/4/96

5/2/98

Vigile

3987

3295

3295

9345

Prov Numero

MI

TO

PR

PR

39548K

E39548

839548

839548


18

InfrazioniCodice

34321

73321

64521

53524

Data

1/2/95

4/3/95

5/4/96

5/2/98

Vigile

3987

3295

3295

9345

Prov Numero

MI

TO

PR

PR

39548K

E39548

839548

839548

Auto Prov Numero

MI

TO

PR

39548K

E39548

839548

Cognome

Rossi

Rossi

Neri

Nome

Mario

Mario

Luca


19


CREATE TABLE Infrazioni(Codice CHAR(6) NOT NULL PRIMARY KEY,

Data DATE NOT NULL, Vigile INTEGER NOT NULL

REFERENCES Vigili(Matricola), Provincia CHAR(2), Numero CHAR(6) , FOREIGN KEY(Provincia, Numero)

REFERENCES Auto(Provincia, Numero) )


20

Modifiche degli schemi

ALTER DOMAINALTER TABLEDROP DOMAINDROP TABLE...


21

Definzione degli indici

• è rilevante dal punto di vista delle prestazioni

• ma è a livello fisico e non logico• in passato era importante perché in

alcuni sistemi era l'unico mezzo per definire chiavi

• CREATE INDEX


22

In pratica

• In molti sistemi si utilizzano strumenti diversi dal codice SQL per definire lo schema della base di dati


23

SQL, operazioni sui dati

• interrogazione: • SELECT

• modifica:• INSERT, DELETE, UPDATE


24

Istruzione SELECT

SELECT ListaAttributiFROM ListaTabelle[ WHERE Condizione ]

• "target list"• clausola FROM • clausola WHERE


25

Nome Età

Persone

RedditoAndrea 27

Maria 55Anna 50

Filippo 26Luigi 50

Franco 60Olga 30

Sergio 85Luisa 75

Aldo 2521

4235304020413587

15

MadreMaternità FiglioLuisa

AnnaAnnaMariaMaria

LuisaMaria

OlgaFilippoAndrea

Aldo

Luigi

PadrePaternità Figlio

LuigiLuigi

FrancoFranco

SergioOlga

FilippoAndrea

Aldo

Franco


26

Selezione e proiezione

• Nome e reddito delle persone con meno di trenta anni

PROJNome, Reddito(SELEta<30(Persone))

select nome, redditofrom personewhere eta < 30


27

Nome Età

Persone

RedditoAndrea 27 21

Maria 55 42Anna 50 35

Filippo 26 30Luigi 50 40

Franco 60 20Olga 30 41

Sergio 85 35Luisa 75 87

Aldo 25 15Andrea 27 21

Aldo 25 15

Filippo 26 30

Andrea 27 21Aldo 25 15

Filippo 26 30

211530

Reddito


28

SELECT, abbreviazioni


select p.nome as nome, p.reddito as reddito

from persone pwhere p.eta < 30


29

Selezione, senza proiezione

• Nome, età e reddito delle persone con meno di trenta anni

SELEta<30(Persone)

select *from personewhere eta < 30


30


select *from personewhere eta < 30

select nome, età, redditofrom personewhere eta < 30


31

Proiezione, senza selezione

• Nome e reddito di tutte le persone

PROJNome, Reddito(Persone)

select nome, redditofrom persone


32


• R(A,B)

select *from R

equivale (intutivamente) aselect X.A as A, X.B as Bfrom R Xwhere true


33

Espressioni nella target list

select Reddito/2 as redditoSemestralefrom Personewhere Nome = 'Luigi'


34

Condizione complessa

select *from personewhere reddito > 25

and (eta < 30 or eta > 60)


35

Condizione “LIKE”

• Le persone che hanno un nome che inizia per 'A' e ha una 'd' come terza lettera

select * from persone where nome like 'A_d%'


36

Cognome Filiale EtàMatricola

Neri Milano 455998Rossi Roma 327309

Bruni Milano NULL9553

Impiegati

Neri Milano 455998Bruni Milano NULL9553

SEL Età > 40 OR Età IS NULL (Impiegati)

Neri Milano 455998Bruni Milano NULL9553

Gestione dei valori nulli


37

• Gli impiegati la cui età è o potrebbe essere maggiore di 40

SEL Età > 40 OR Età IS NULL (Impiegati)

select *from impiegatiwhere eta > 40 or eta is null


38

SQL e algebra relazionale

• R1(A1,A2) R2(A3,A4)

select R1.A1, R2.A4 from R1, R2where R1.A2 = R2.A3

• prodotto cartesiano (FROM)• selezione (WHERE)• proiezione (SELECT)


39

SQL e algebra relazionale

• R1(A1,A2) R2(A3,A4)

select R1.A1, R2.A4 from R1, R2where R1.A2 = R2.A3

PROJ A1,A4 (SELA2=A3 (R1 JOIN R2))


40

• possono essere necessarie ridenominazioni• nel prodotto cartesiano• nella target list

select X.A1 AS B1, ...from R1 X, R2 Y, R1 Zwhere X.A2 = Y.A3 AND ...


41

select X.A1 AS B1, Y.A4 AS B2from R1 X, R2 Y, R1 Zwhere X.A2 = Y.A3 AND Y.A4 = Z.A1

REN B1,B2A1,A4 (PROJ A1,A4 (SEL A2 = A3 AND A4 = C1(

R1 JOIN R2 JOIN REN C1,C2 A1,A2 (R1))))


42

Cognome Filiale StipendioMatricola

Neri Milano 645998Neri Napoli 557309

Rossi Roma 645698Rossi Roma 449553

• cognome e filiale di tutti gli impiegati

PROJ Cognome, Filiale (Impiegati)


43

Cognome Filiale

Neri MilanoNeri Napoli

Rossi Roma

Cognome Filiale

Neri MilanoNeri Napoli

Rossi RomaRossi Roma

selectcognome, filiale

from impiegati

select distinctcognome, filiale

from impiegati


44

Nome Età

Persone

RedditoAndrea 27

Maria 55Anna 50

Filippo 26Luigi 50

Franco 60Olga 30

Sergio 85Luisa 75

Aldo 2521

4235304020413587

15

MadreMaternità FiglioLuisa

AnnaAnnaMariaMaria

LuisaMaria

OlgaFilippoAndrea

Aldo

Luigi


LuigiLuigi

FrancoFranco

SergioOlga

FilippoAndrea

Aldo

Franco


45

Selezione, proiezione e join

• I padri di persone che guadagnano più di venti milioni

PROJPadre(paternita JOIN Figlio =Nome

SELReddito>20 (persone))

select distinct padrefrom persone, paternitawhere figlio = nome and

reddito > 20


46

Join naturale

• Padre e madre di ogni persona

paternita JOIN maternita

select paternita.figlio,padre, madrefrom maternita, paternitawhere paternita.figlio = maternita.figlio


47

• Le persone che guadagnano più dei rispettivi padri; mostrare nome, reddito e reddito del padre

PROJNome, Reddito, RP (SELReddito>RP

(RENNP,EP,RP Nome,Eta,Reddito(persone)JOINNP=Padre

(paternita JOIN Figlio =Nome persone)))

select f.nome, f.reddito, p.redditofrom persone p, paternita, persone fwhere p.nome = padre and

figlio = f.nome andf.reddito > p.reddito


48

SELECT, con ridenominazione del risultato

select figlio, f.reddito as reddito, p.reddito as redditoPadre

from persone p, paternita, persone fwhere p.nome = padre and figlio = f.nome

and .reddito > p.reddito


49

Join esplicito

• Padre e madre di ogni persona

select paternita.figlio,padre, madrefrom maternita, paternitawhere paternita.figlio = maternita.figlio

select madre, paternita.figlio, padre from maternita join paternita on

paternita.figlio = maternita.figlio


50

SELECT con join esplicito, sintassi

SELECT …FROM Tabella { … JOIN Tabella ON CondDiJoin }, …[ WHERE AltraCondizione ]


51

• Le persone che guadagnano più dei rispettivi padri; mostrare nome, reddito e reddito del padre

select f.nome, f.reddito, p.redditofrom persone p, paternita, persone fwhere p.nome = padre and

figlio = f.nome andf.reddito > p.reddito

select f.nome, f.reddito, p.redditofrom persone p join paternita on p.nome = padre

join persone f on figlio = f.nome where f.reddito > p.reddito


52

Ulteriore estensione: join naturale(meno diffuso)

PROJFiglio,Padre,Madre(paternita JOIN Figlio = Nome REN Nome=Figlio(maternita))

paternita JOIN maternita

select madre, paternita.figlio, padre from maternita join paternita on

paternita.figlio = maternita.figlio

select madre, paternita.figlio, padre from maternita natural join paternita


53

Join esterno: "outer join"

• Padre e, se nota, madre di ogni persona

select paternita.figlio, padre, madrefrom paternita left join maternita

on paternita.figlio = maternita.figlio

select paternita.figlio, padre, madrefrom paternita left outer join maternita

on paternita.figlio = maternita.figlio

• outer e' opzionale


54

Outer join

select paternita.figlio, padre, madrefrom maternita join paternita

on maternita.figlio = paternita.figlio

select paternita.figlio, padre, madrefrom maternita left outer join paternita


select paternita.figlio, padre, madrefrom maternita full outer join paternita



55

Ordinamento del risultato

• Nome e reddito delle persone con meno di trenta anni in ordine alfabetico

select nome, redditofrom personewhere eta < 30order by nome


56

Persone

Nome RedditoAndrea 21

Aldo 15Filippo 30

Persone

Nome Reddito

Andrea 21Aldo 15

Filippo 30


select nome, redditofrom personewhere eta < 30order by nome


57

Operatori aggregati

• Nelle espressioni della target list possiamo avere anche espressioni che calcolano valori a partire da insiemi di ennuple:• conteggio, minimo, massimo, media, totale• sintassi base (semplificata):

Funzione ( [ DISTINCT ] * )Funzione ( [ DISTINCT ] Attributo )


58

Operatori aggregati: COUNT

• Il numero di figli di Franco

select count(*) as NumFigliDiFrancofrom Paternitawhere Padre = 'Franco'

• l’operatore aggregato (count) viene applicato al risultato dell’interrogazione:

select * from Paternitawhere Padre = 'Franco'


59


LuigiLuigi

SergioOlga

FilippoFrancoFranco

AndreaAldo

Franco

FrancoFranco

AndreaAldo

NumFigliDiFranco2


60

COUNT e valori nulli

select count(*) from persone

select count(reddito) from persone

select count(distinct reddito) from persone

Nome EtàPersone RedditoAndrea 27

Maria 55Anna 50

Aldo 2521

2135

NULL


61

Altri operatori aggregati

• SUM, AVG, MAX, MIN

• Media dei redditi dei figli di Franco

select avg(reddito) from persone join paternita on nome=figlio where padre='Franco'


62

Operatori aggregati e valori nulli

select avg(reddito) as redditomediofrom persone

Nome EtàPersone RedditoAndrea 27

Maria 55Anna 50

Aldo 2530

3636

NULL


63

Operatori aggregati e target list

• un’interrogazione scorretta:

select nome, max(reddito)from persone

• di chi sarebbe il nome? La target list deve essere omogenea

select min(eta), avg(reddito)from persone


64

• Le funzioni possono essere applicate a partizioni delle relazioni

• Clausola GROUP BY:GROUP BY listaAttributi

Operatori aggregati e raggruppamenti


65

• Il numero di figli di ciascun padre

select padre, count(*) AS NumFiglifrom paternitagroup by Padre

Operatori aggregati e raggruppamenti

Padrepaternita Figlio

LuigiLuigi

SergioOlga

FilippoFrancoFranco

AndreaAldo

Franco Padre NumFigli

LuigiSergio

2Franco 2

1


66

Semantica di interrogazioni con operatori aggregati e

raggruppamenti

1. interrogazione senza group by e senza operatori aggregatiselect *from paternita

2. si raggruppa e si applica l’operatore aggregato a ciascun gruppo


67

Raggruppamenti e target list

scorrettaselect padre, avg(f.reddito), p.redditofrom persone f join paternita on figlio = nome join

persone p on padre =p.nomegroup by padre

correttaselect padre, avg(f.reddito), p.redditofrom persone f join paternita on figlio = nome join

persone p on padre =p.nomegroup by padre, p.reddito


68

Condizioni sui gruppi

• I padri i cui figli hanno un reddito medio maggiore di 25

select padre, avg(f.reddito)from persone f join paternita on figlio = nome group by padre having avg(f.reddito) > 25


69

WHERE o HAVING?

• I padri i cui figli sotto i 30 anni hanno un reddito medio maggiore di 20

select padre, avg(f.reddito)from persone f join paternita on figlio = nomewhere eta < 30 group by padre having avg(f.reddito) > 25


70

Sintassi, riassumiamo

SelectSQL ::= select ListaAttributiOEspressionifrom ListaTabelle[ where CondizioniSemplici ][ group by

ListaAttributiDiRaggruppamento ][ having CondizioniAggregate ][ order by ListaAttributiDiOrdinamento ]


71

Unione, intersezione e differenza

• La select da sola non permette di fare unioni; serve un costrutto esplicito:

select … union [all]select ...

• i duplicati vengono eliminati (a meno che si usi all); anche dalle proiezioni!


72

Notazione posizionale!

select padre from paternitaunionselect madrefrom maternita

• quali nomi per gli attributi del risultato?• nessuno • quelli del primo operando• …


73

Luisa

AnnaAnnaMariaMaria

LuisaMaria

OlgaFilippoAndrea

Aldo

Luigi

Figlio

LuigiLuigi

FrancoFranco

SergioOlga

FilippoAndrea

Aldo

Franco

Luisa

AnnaAnnaMariaMaria

LuisaMaria

OlgaFilippoAndrea

Aldo

Luigi

Padre Figlio

LuigiLuigi

FrancoFranco

SergioOlga

FilippoAndrea

Aldo

Franco


74

Notazione posizionale, 2

select padre, figliofrom paternitaunionselect figlio, madrefrom maternita

select padre, figliofrom paternitaunionselect madre, figliofrom maternita


75

Notazione posizionale, 3

• Anche con le ridenominazioni non cambia niente:select padre as genitore, figliofrom paternitaunionselect figlio, madre as genitorefrom maternita

• Corretta:select padre as genitore, figliofrom paternitaunionselect madre as genitore, figlio from maternita


76

Differenza

select Nomefrom Impiegatoexceptselect Cognome as Nomefrom Impiegato

• vedremo che si può esprimere con select nidificate


77

Intersezione

select Nomefrom Impiegatointersectselect Cognome as Nomefrom Impiegato

• equivale a

select I.Nomefrom Impiegato I, Impiegato Jwhere I.Nome = J.Cognome


78

Interrogazioni nidificate

• le condizioni atomiche permettono anche • il confronto fra un attributo (o più,

vedremo poi) e il risultato di una sottointerrogazione

• quantificazioni esistenziali


79

• nome e reddito del padre di Franco

select Nome, Redditofrom Persone, Paternitawhere Nome = Padre and Figlio = 'Franco'

select Nome, Redditofrom Personewhere Nome = ( select Padre

from Paternita where Figlio = 'Franco')


80

Interrogazioni nidificate, commenti

• La forma nidificata è “meno dichiarativa”, ma talvolta più leggibile (richiede meno variabili)

• La forma piana e quella nidificata possono essere combinate

• Le sottointerrogazioni non possono contenere operatori insiemistici (“l’unione si fa solo al livello esterno”); la limitazione non è significativa


81

• Nome e reddito dei padri di persone che guadagnano più di 20 milioni

select distinct P.Nome, P.Redditofrom Persone P, Paternita, Persone Fwhere P.Nome = Padre and Figlio = F.Nome

and F.Reddito > 20

select Nome, Redditofrom Personewhere Nome in (select Padre

from Paternitawhere Figlio = any (select Nome

from Personewhere Reddito > 20))


82

• Nome e reddito dei padri di persone che guadagnano più di 20 milioni

select distinct P.Nome, P.Redditofrom Persone P, Paternita, Persone Fwhere P.Nome = Padre and Figlio = F.Nome

and F.Reddito > 20

select Nome, Redditofrom Personewhere Nome in (select Padre

from Paternita, Persone where Figlio = Nomeand Reddito > 20)


83

Interrogazioni nidificate, commenti, 2

• La prima versione di SQL prevedeva solo la forma nidificata (o strutturata), con una sola relazione in ogni clausola FROM. Il che è insoddisfacente:• la dichiaratività è limitata• non si possono includere nella target

list attributi di relazioni nei blocchi interni


84

• Nome e reddito dei padri di persone che guadagnano più di 20 milioni, con indicazione del reddito del figlio

select distinct P.Nome, P.Reddito, F.Redditofrom Persone P, Paternita, Persone Fwhere P.Nome = Padre and Figlio = F.Nome

and F.Reddito > 20

select Nome, Reddito, ???? from Personewhere Nome in (select Padre

from Paternitawhere Figlio = any (select Nome

from Personewhere Reddito > 20))


85

Interrogazioni nidificate, commenti, 3

• regole di visibilità:• non è possibile fare riferimenti a variabili

definite in blocchi più interni• se un nome di variabile è omesso, si

assume riferimento alla variabile più “vicina”

• in un blocco si può fare riferimento a variabili definite in blocchi più esterni; la semantica base (prodotto cartesiano, selezione, proiezione) non funziona più, vedremo presto


86

Quantificazione esistenziale

• Ulteriore tipo di condizione• EXISTS ( Sottoespressione )


87

• Le persone che hanno almeno un figlio

select *from Personewhere exists ( select *

from Paternitawhere Padre = Nome)

orexists ( select *

from Maternitawhere Madre = Nome)


88

• I padri i cui figli guadagnano tutti più di venti milioni

select distinct Padrefrom Paternita Zwhere not exists (

select *from Paternita W, Personewhere W.Padre = Z.Padre

and W.Figlio = Nome and Reddito <= 20)


89

Semantica delle espressioni “correlate”

• L’interrogazione interna viene eseguita una volta per ciascuna ennupla dell’interrogazione esterna


90

Visibilità

• scorretta:

select *from Impiegatowhere Dipart in (select Nome from Dipartimento D1 where Nome = 'Produzione') or Dipart in (select Nome from Dipartimento D2 where D2.Citta = D1.Citta)


91

Disgiunzione e unione (ma non sempre)select * from Persone where Reddito > 30

union select F.*from Persone F, Paternita, Persone Pwhere F.Nome = Figlio and Padre = P.Nome

and P.Reddito > 30

select *from Persone Fwhere Reddito > 30 or

exists (select *from Paternita, Persone Pwhere F.Nome = Figlio and Padre =

P.Nomeand P.Reddito > 30)


92

Differenza e nidificazione

select Nome from Impiegatoexceptselect Cognome as Nome from Impiegato

select Nomefrom Impiegato Iwhere not exists (select *

from Impiegatowhere Cognome = I.Nome)


93

Massimo e nidificazione

• La persona (o le persone) con il reddito massimo

select *from personewhere reddito = ( select max(reddito)

from persone)


94

Operazioni di aggiornamento

• operazioni di• inserimento: insert• eliminazione: delete• modifica: update

• di una o più ennuple di una relazione• sulla base di una condizione che può

coinvolgere anche altre relazioni


95

Inserimento

INSERT INTO Tabella [ ( Attributi ) ] VALUES( Valori )

oppure

INSERT INTO Tabella [ ( Attributi )] SELECT ...


96

INSERT INTO Persone VALUES ('Mario',25,52)

INSERT INTO Persone(Nome, Eta, Reddito) VALUES('Pino',25,52)

INSERT INTO Persone(Nome, Reddito) VALUES('Lino',55)

INSERT INTO Persone ( Nome )SELECT PadreFROM PaternitaWHERE Padre NOT IN (SELECT Nome FROM Persone)


97

Inserimento , commenti

• l’ordinamento degli attributi (se presente) e dei valori è significativo

• le due liste debbono avere lo stesso numero di elementi

• se la lista di attributi è omessa, si fa riferimento a tutti gli attributi della relazione, secondo l’ordine con cui sono stati definiti

• se la lista di attributi non contiene tutti gli attributi della relazione, per gli altri viene inserito un valore nullo (che deve essere permesso) o un valore di default


98

Eliminazione di ennuple

DELETE FROM Tabella [ WHERE Condizione ]


99

DELETE FROM PersoneWHERE Eta < 35

DELETE FROM PaternitaWHERE Figlio NOT in ( SELECT Nome FROM Persone)

DELETE FROM Paternita


100

Eliminazione, commenti

• elimina le ennuple che soddisfano la condizione

• può causare (se i vincoli di integrità referenziale sono definiti con politiche di reazione cascade) eliminiazioni da altre relazioni

• ricordare: se la where viene omessa, si intende where true


101

Modifica di ennuple

UPDATE NomeTabellaSET Attributo = < Espressione |

SELECT … | NULL | DEFAULT >

[ WHERE Condizione ]


102

UPDATE Persone SET Reddito = 45WHERE Nome = 'Piero'

UPDATE Persone SET Reddito = Reddito * 1.1WHERE Eta < 30


103

Vincoli di integrità generici: check

• Specifica di vincoli di ennupla (e anche vincoli più complessi)

check ( Condizione )


104

Check, esempio

create table Impiegato(Matricola character(6),Cognome character(20),Nome character(20),Sesso character not null check (sesso in (‘M’,‘F’))Stipendio integer,Superiore character(6), check (Stipendio <= (select Stipendio from Impiegato J where Superiore = J.Matricola) )


105

Vincoli di integrità generici: asserzioni

• Specifica vincoli a livello di schema

create assertion NomeAss check ( Condizione )

create assertion AlmenoUnImpiegato check (1 <= ( select count(*) from Impiegato ))


106

Viste

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

create view ImpiegatiAmmin (Matricola, Nome, Cognome, Stipendio) as

select Matricola, Nome, Cognome, Stipendiofrom Impiegatowhere Dipart = 'Amministrazione' and Stipendio > 10


107

create view ImpiegatiAmminPoveri asselect *from ImpiegatiAmminwhere Stipendio < 50with check option


108

Un’interrogazione non standard

• La nidificazione nella having non è ammessa

select Dipartfrom Impiegatogroup by Diparthaving sum(Stipendio) >= all (select sum(Stipendio) from Impiegato group by Dipart)


109

Soluzione con le viste

create view BudgetStipendi(Dip,TotaleStipendi) asselect Dipart, sum(Stipendio)from Impiegatogroup by Dipart

select Dipfrom BudgetStipendiwhere TotaleStipendi =(select max(TotaleStipendi) from BudgetStipendi)


110

Ancora sulle viste

• Interrogazione scorrettaselect avg(count(distinct Ufficio))from Impiegatogroup by Dipart

• Con una vistacreate view DipartUffici(NomeDip,NroUffici) as

select Dipart, count(distinct Ufficio)from Impiegatogroup by Dipart;

select avg(NroUffici)from DipartUffici


111

Access control

• Every element of the DB can be protected (tables, attributes, views, domains, etc.)

• The creator of a resource assigns privileges to others • A predefined user _system represents the database

administrator and has complete access to all the resources

• A privilege is characterized by:• the resource• the user who grants the privilege• the user who receives the privilege• the action that is allowed on the resource• whether or not the privilege can be passed on to

other users


112

Types of privilege

• SQL offers six types of privilege• insert: to insert a new object into the resource• update: to modify the resource content• delete: to remove an object from the resource• select: to access the resource content in a query• references: to build a referential integrity

constraint with the resource (may limit the ability to modify the resource)

• usage: to use the resource in a schema definition (e.g., a domain)


113

grant and revoke

• To grant a privilege to a user:grant < Privileges | all privileges > on Resource

to Users [ with grant option ]• grant option specifies whether the privilege of

propagating the privilege to other users must be granted

grant select on Department to Stefano• To take away privileges:

revoke Privileges on Resource from Users [ restrict | cascade ]


114

SQL immerso in linguaggio ospite "embedded SQL"

• in applicazioni complesse, è spesso necessario “immergere” istruzioni SQL in programmi tradizionali (Pascal, COBOL, C, C++, Java)

• i programmi con SQL immerso sono analizzati da precompilatori che traducono le istruzioni SQL

• le variabili del programma possono essere usate come parametri nelle istruzioni SQL (precedute da ':')


115

Embedded SQL

• select che producano una sola ennupla e operazioni di aggiornamento possono essere immerse senza problemi (con opportuno meccanismo di trasmissione dei risultati)

• esiste di solito una variabile di sistema sqlcode che dopo l'esecuzione di un'operazione assume valore zero se essa ha avuto successo e valore diverso altrimenti


116

Relazioni e record: cursori

• problema fondamentale: “disadattamento di impedenza”• i linguaggi tradizionali gestiscono record

uno alla volta• SQL produce insiemi di ennuple

• soluzione: cursore• accede a tutte le ennuple di una

interrogazione in modo globale• le trasmette al programma una alla volta


117

Operazioni sui cursori

Definizione del cursoredeclare NomeCursore [ scroll ] cursor for Select …

Esecuzione dell'interrogazioneopen NomeCursore

Utilizzo dei risultati (una ennupla alla volta)fetch NomeCursore into ListaVariabili

Disabilitazione del cursoreclose cursor NomeCursore

Accesso alla ennupla corrente (di un cursore su singola relazione a fini di aggiornamento)current of NomeCursore

nella clausola where


118

write('nome della citta''?');readln(citta);EXEC SQL DECLARE P CURSOR FOR

SELECT NOME, REDDITOFROM PERSONEWHERE CITTA = :citta ;

EXEC SQL OPEN P ;EXEC SQL FETCH P INTO :nome, :reddito ;while SQLCODE = 0do begin

write('nome della persona:', nome, 'aumento?');readln(aumento);EXEC SQL UPDATE PERSONE

SET REDDITO = REDDITO + :aumentoWHERE CURRENT OF P

EXEC SQL FETCH P INTO :nome, :redditoend;

EXEC SQL CLOSE CURSOR P


119

void VisualizzaStipendiDipart(char NomeDip[]){

char Nome[20], Cognome[20];long int Stipendio;$ declare ImpDip cursor for

select Nome, Cognome, Stipendiofrom Impiegatowhere Dipart = :NomeDip;

$ open ImpDip;$ fetch ImpDip into :Nome, :Cognome, :Stipendio;printf("Dipartimento %s\n",NomeDip);while (sqlcode == 0){

printf("Nome e cognome dell'impiegato: %s %s",Nome,Cognome);

printf("Attuale stipendio: %d\n",Stipendio);$ fetch ImpDip into :Nome, :Cognome, :Stipendio;

}$ close cursor ImpDip;

}


120

Dynamic SQL

• When applications do not know at compile-time the SQL statement to execute, they need dynamic SQL

• Major problem: managing the transfer of parameters between the program and the SQL environment

• For direct execution:execute immediate SQLStatement

• For execution preceded by the analysis of the statement:

prepare CommandName from SQLStatement• followed by:execute CommandName [ into TargetList ] [ using ParameterList ]


121

Procedures

• SQL-2 allows for the definition of procedures, also known as stored procedures

• Stored procedures are part of the schemaprocedure AssignCity(:Dep char(20), :City char(20))

update Departmentset City = :Citywhere Name = :Dep

• SQL-2 does not handle the writing of complex procedures

• Most systems offer SQL extensions that permit to write complex procedures (e.g., Oracle PL/SQL)


122

Procedure in Oracle PL/SQLProcedure Debit(ClientAccount char(5),Withdrawal integer) is OldAmount integer; NewAmount integer; Threshold integer;begin select Amount, Overdraft into OldAmount, Threshold from BankAccount where AccountNo = ClientAccount for update of Amount; NewAmount := OldAmount - WithDrawal; if NewAmount > Threshold then update BankAccount set Amount = NewAmount where AccountNo = ClientAccount; else insert into OverDraftExceeded values(ClientAccount,Withdrawal,sysdate); end if;end Debit;

Date post:	03-Jan-2016
Category:	Documents
Upload:	sylvia-ware
View:	28 times
Download:	0 times

Atzeni, Ceri, Paraboschi, Torlone Basi di dati McGraw-Hill, 1999

Documents