Basi di Dati Relazionali Parte I - Politecnico di...

© Politecnico di Torino Data ultima revisione 28/11/2000Autore: Scarsi RiccardoAutore: Scarsi Riccardo

Politecnico di TorinoCeTeM

Basi di Dati

Pagina Pagina 11

Basi di DatiRelazionali

-Parte I

Basi di DatiBasi di DatiRelazionaliRelazionali

--Parte IParte I

../esercitazioni.html




Basi di Dati

Pagina Pagina 22

Basi di dati relazionali• Introduzione• Aspetti relativi al prodotto DB2 (IBM) basato

sul modello relazionale• Il linguaggio SQL:

• definizione dei dati• manipolazione dei dati




Basi di Dati

Pagina Pagina 33

DB prodotti-fornitori

S1S2S3S4S5

SmithJonesBlakeClarkAdams

2010302030

LondonParisParisLondonAthens

S# SNAME STATUS CITY

NutBoltScrewScrewCamCog

RedGreenBlueRedBlueRed

121717141219

P1P2P3P4P5P6

P# PNAME COLOR WEIGHT

LondonParisRomeLondonParisLondon

CITY

S1S1S1S1S1S1S2S2S3S4S4S4

S#

P1P2P3P4P5P6P1P2P2P2P4P5

P#

300200400200100100300400200200300400

QTYS

P

SP




Basi di Dati

Pagina Pagina 44

DB prodotti-fornitori• modello relazionale:

– tabella prodotti– tabella fornitori– tabella forniture, che mette in relazione

prodotti e fornitori che li forniscono•• chiave primariachiave primaria: identificatore del record

– prodotti: P#– fornitori: S#– forniture: (S#,P#)




Basi di Dati

Pagina Pagina 55

DB prodotti-fornitoriCaratteristiche delle tabelle:• valori atomici (non più di uno per riga/co-

lonna)• dati espliciti (no puntatori o link)

– La relazione tra la riga S1 e la riga P1 èespressa dalla riga P1S1 nella tabellaforniture (creata per rappresentare larelazione stessa).

– I puntatori e i link non sono visibiliall'utente (ma possono essere usati nellivello interno).




Basi di Dati

Pagina Pagina 66

Il linguaggio SQL• Linguaggio per definire la struttura di una

base di dati relazionale e per accedere emodificarne i dati.

• Il linguaggio è disponibile per operazioni:– interattive– compilate: un linguaggio ospite (host)

contiene le istruzioni SQL. Queste ultime sidistinguono dalle istruzioni del linguaggioospite per mezzo di opportuni artificisintattici.




Basi di Dati

Pagina Pagina 77

Definizione del DB parti-fornitoriCREATE TABLE S

( S# CHAR(5) NOT NULL,

SNAME CHAR(20) NOT NULL,

STATUS SMALLINT NOT NULL,

CITY CHAR(15)NOT NULL,

PRIMARY KEY (S#) );

CREATE TABLE P

( P# CHAR(6) NOT NULL,

PNAME CHAR(20)NOT NULL,

COLOR CHAR(6) NOT NULL,

WEIGHT SMALLINTNOT NULL,

CITY CHAR(15)NOT NULL

PRIMARY KEY (P#) );

CREATE TABLE SP

( S# CHAR(5) NOT NULL,

P# CHAR(6) NOT NULL,

QTY INTEGER NOT NULL

PRIMARY KEY (S#,P#),

FOREIGN KEY (S#) REFERENCES S,

FOREIGN KEY (P#) REFERENCES P);




Basi di Dati

Pagina Pagina 88

Esempi di interrogazioni• Interattiva (DB2I): SELECT CITY FROM S WHERE S# = 'S4';• Embedded in PL/1 (potrebbe essere COBOL,

FORTRAN, o Assembler):

EXEC SQL SELECT CITY INTO :XCIT FROM S WHERE S# = 'S4';

CITY

LondonRisultato :

XCIT

LondonRisultato :




Basi di Dati

Pagina Pagina 99

Percezione del DB da partedell'utente

Base TableB1

Base table B2

Base tableB3

Base tableB4

VSAM fileF1

VSAM file F2

VSAM fileF3

VSAM fileF4

SQL

Vista V1 Vista V2

Utente




Basi di Dati

Pagina Pagina 1010

Elementi costitutivi del DB• Utenti:

– SQL– Viste delle applicazioni (VIEW): indicano

tabelle non effettivamente presenti nel DB,ma derivate da una o più base tables

• Tabelle (BASE TABLE): corrispondono aglielementi effettivamente esistenti nel DB.

• Files (livello interno). Più files possono farparte della stessa base table

– tutti i DB relazionali supportano B-trees– INGRES opera anche con la tecnica hash




Basi di Dati

Pagina Pagina 1111

Manipolazione dei dati: esempio• SELECT S# FROM SP WHERE P# = 'P2';

• UPDATE S SET STATUS = 2 * STATUS WHERE CITY = 'London';

S1S2S3S4

S#Risultato :

Risultato : Stato raddoppiato per S1 e S4




Basi di Dati

Pagina Pagina 1212

Considerazioni generali• Il linguaggio SQL è un linguaggio a livello di

set: operazioni e risultati coinvolgonoinsiemi.

• Il linguaggio SQL è dichiarativo:– si pone ad un livello di astrazione

superiore rispetto ai linguaggi diprogrammazione di terza generazione

– dice cosa fare non come fare– SELECT non indica le modalità di accesso

al file, ma le caratteristiche dei dati daselezionare




Basi di Dati

Pagina Pagina 1313

DB2 - Struttura interna•• PrecompilerPrecompiler: elabora le istruzioni SQL, le

raccoglie nel modulo Database RequestModule (DBRM) e le sostituisce con chiamateal Runtime Supervisor (RS)

•• BindBind: compila i moduli DBMR per generare unpiano dell'applicazione (Application Plan) egenera istruzioni macchina che implementanoistruzioni SQL con chiamate allo Stored DataManager (SDM)

•• StoredStored Data Manager Data Manager: implementa le funzionidel “File Manager”, gestisce operazioni dilock, sort, recupero ed aggiornamento direcord nel DB fisico




Basi di Dati

Pagina Pagina 1414

DB2 - Struttura interna

•• Buffer ManagerBuffer Manager: gestisce il trasferimento deidati dal disco alla memoria centrale. E` il DiskManager; a volte è una componente delSistema Operativo.

•• RunRun Time Time Supervisor Supervisor (RS): controlla l'esecu-zione dei programmi SQL. Quando il pro-gramma richiede un'operazione sul DB ilcontrollo passa al RS in seguito all'impiegodell'istruzione CALL inserita dal precompila-tore, RS cede il controllo ad AP che invocaSDM per eseguire l'operazione sul DB (operaa livello interno)




Basi di Dati

Pagina Pagina 1515

Preparazione ed esecuzionedi un'applicazione

PL/I SourceModule P

DB

ModifiedSource Module

DBRM

Object Module

Load Module

ApplicationPlan

Precompiler

PL/I Compiler

Linkage Editor

Bind

(Load Module)

(Application Plan)

Runtime SupervisorStored Data Manager

(Other)Main storage




Basi di Dati

Pagina Pagina 1616

Preparazione di un'applicazioneFasi di preparazione per PP:• Precompilazione: le istruzioni SQL vengono

sostituite con istruzioni CALL; viene generatoil DBRM (codice intermedio delle istruzioniSQL)

• Compilazione: il programma P può esserecompilato, linkato e caricato

• Compilazione DB: Bind agisce come uncompilatore - trasforma le chiamate ad altolivello (interrogazioni SQL) in codicemacchina; l'uscita del Bind (Application Plan)è registrata nel catalogo delle applicazioni




Basi di Dati

Pagina Pagina 1717

Preparazione di un'applicazione

Dal programma P sono stati prodotti dueelementi:

– il piano dell'applicazione– il modulo caricabile;

Questi due elementi sono utilizzaticongiuntamente a tempo di esecuzione.




Basi di Dati

Pagina Pagina 1818

Tempo di Esecuzione• Il modulo eseguibile è caricato in memoria

centrale• Si attiva l'esecuzione• Ad ogni CALL il controllo passa al RS che

preleva il piano dell'applicazione (AP) dalcatalogo e lo carica in memoria cedendo ilcontrollo

• AP chiama SDM che esegue le operazionirestituendo il valore di ritorno dell'appli-cazione




Basi di Dati

Pagina Pagina 1919

Tempo di Esecuzione

--CALL-

RuntimeSupervisor

ApplicationPlan

(per P)

StoredData

Manager

Catalogo

Database

PL/I Load Module P

Alla prima call

Fetch dell'AP




Basi di Dati

Pagina Pagina 2020

OttimizzazioneE` la componente di BIND che individua unastrategia efficiente per l'istruzione SQL.SELECT non specifica il metodo di accesso perottenere i dati cercati: tale scelta competeall'ottimizzatore.Esempio: EXEC SQL SELECT CITY INTO :XCIT FROM S WHERE S# = 'S4'




Basi di Dati

Pagina Pagina 2121

OttimizzazioneEsempio EXEC SQL SELECT CITY INTO :XCIT FROM S WHERE S# = 'S4'può essere implementato in due modi:

• scansione sequenziale fisica di S fino a cheS4 viene individuato

• usare un indice sulla colonna S# della tabella




Basi di Dati

Pagina Pagina 2222

Operazioni dell'ottimizzatore• Genera il codice relativo alla strategia più

conveniente, considerando:– quali tabelle devono essere utilizzate– la grandezza di tali tabelle– gli indici esistenti– il tipo di clustering usato per i dati– la forma della condizione specificata da

WHERE




Basi di Dati

Pagina Pagina 2323

Compilazione e Ricompilazione

• La compilazione offre prestazioni miglioririspetto all'interpretazione.

• Problema:– Sia P compilato in data T con l'uso

dell'indice X per la strategia di accesso– Al tempo T+1 : DROP INDEX X– Al tempo T+2 viene eseguito P




Basi di Dati

Pagina Pagina 2424

Compilazione e Ricompilazione• Rimedio:

– In T+1 (DROP) RS controlla quali AP dipendono da Xe li marca 'non validi'

– In T+2 RS 'vede' il mark nel piano di P: chiama BINDper generare un nuovo piano con una diversastrategia di accesso.

• Il modulo Bind– ricompila gli statement SQL– ritarda l'esecuzione della prima istruzione SQL.

I programmi non devono essere modificatiI programmi non devono essere modificati = indipendenzadai dati.




Basi di Dati

Pagina Pagina 2525

Il linguaggio SQL

Linguaggio per gestire le basi di dati relazionali.• DDL (Data Definition Language) è il linguaggio

di definizione della struttura della base di dati• DML (Data Manipulation Language) è il

linguaggio di manipolazione dei dati




Basi di Dati

Pagina Pagina 2626

Tipi di dato• INTEGER• SMALLINT• DECIMAL (p,q)• FLOAT• CHAR(n)• VARCHAR(n)• LOGICAL• BIT• MONEY• DATE• TIME• TIMESTAMPS




Basi di Dati

Pagina Pagina 2727

Data Definition Language• CREATE TABLE: creazione di una tabella

• CREATE INDEX: creazione di un indice

• ALTER TABLE: modifica della struttura di unatabella

• DROP TABLE: cancellazione di una tabella

• DROP INDEX: cancellazione di un indice




Basi di Dati

Pagina Pagina 2828

CREATE TABLE

CREATE TABLE nome-tabella-base(definizione-colonna [,definizione-colonna]);

definizione-colonna::=nome-colonna tipo-dato [NOT NULL]




Basi di Dati

Pagina Pagina 2929

CREATE TABLE

CREATE TABLE S (S# CHAR(5) NOT NULL, SNAME CHAR(20), STATUS SMALLINT, CITY CHAR(15));

Esempio: creazione della tabella fornitori.




Basi di Dati

Pagina Pagina 3030

ALTER TABLE

ALTER TABLE base-table-nameADD column-name data-type;

Esempio: aggiungere la colonna DISCOUNT allatabella fornitori.

ALTER TABLE S ADD DISCOUNT SMALLINT;




Basi di Dati

Pagina Pagina 3131

DROP TABLE

DROP TABLE base-table-name;– sono eliminati anche tutti gli indici e le

viste definite su base-table-name.

Esempio: cancellare la tabella fornitori. DROP TABLE S;




Basi di Dati

Pagina Pagina 3232

INDICICreazione di un indice: CREATE [UNIQUE] INDEX nome-indice ON nome-tabella-base(nome-col [,nome-col]);

• l'opzione UNIQUE specifica che solo un recordnel file può assumere un dato valore

Esempi: CREATE UNIQUE INDEX XS ON S (S#); CREATE UNIQUE INDEX XP ON P (P#); CREATE UNIQUE INDEX XSP ON SP (S#,P#);La violazione dell'unicità impedisce l'operazione.




Basi di Dati

Pagina Pagina 3333

INDICI (cont.)

Esempio: CREATE INDEX XSC ON S (CITY);

UNIQUE non è specificato, poichè più recordpossono fare riferimento alla stessa città.

Cancellazione di un indice: DROP INDEX index-name;




Basi di Dati

Pagina Pagina 3434

Manipolazione dei datiEsempio: Ricavare i codici e lo stato dei fornitorinella città di Parigi. SELECT S#, STATUS FROM S WHERE CITY='Parigi';

Il risultato è una tabella.

S# STATUS

S2S3

1030

Risultato :




Basi di Dati

Pagina Pagina 3535

Manipolazione dei datiFormato generale dell'interrogazione: SELECT [DISTINCT] colonna(e) FROM tabella(e) [WHERE predicato] [GROUP BY colonna(e) [HAVING predicato]] [ORDER BY colonna(e)];

Esempio: SELECT P# FROM SP;I duplicati non vengono eliminati.

P#P1P2P3P1P2P2

Risultato :




Basi di Dati

Pagina Pagina 3636

SELECT con DISTINCT

Eliminazione dei duplicati: SELECT DISTINCT P# FROM SP;

P#P1P2P3P4P5P6

Risultato :




Basi di Dati

Pagina Pagina 3737

SELECT con espressioni

Ricerca con espressioni: SELECT P.P#, 'Weight in grams=',P.WEIGHT*454 FROM P;

P# P1 Weight in grams = 5448P2 Weight in grams = 7718P3 Weight in grams = 7718P4 Weight in grams = 6356P5 Weight in grams = 5448P6 Weight in grams = 8626

Risultato :




Basi di Dati

Pagina Pagina 3838

SELECT

Estrazione di tutte le informazioni: SELECT * FROM S;

Identificatori completamente specificati: SELECT S.S#, S.NAME, S.STATUS, S.CITY FROM S;




Basi di Dati

Pagina Pagina 3939

SELECT (cont.)Ricerca qualificata:Individuare i numeri dei fornitori di Parigi constato > 20

SELECT S# FROM S WHERE CITY='Paris' AND STATUS>20;

S#

S3

Risultato :




Basi di Dati

Pagina Pagina 4040

Ricerca con Ordinamento

Ricavare i codici e lo stato dei fornitori di Parigiin ordine decrescente rispetto allo stato:SELECT S#, STATUS FROM SWHERE CITY='Paris'ORDER BY STATUS DESC;

nome-colonna[ordine][,nome-colonna [ordine]]

Risultato : S#

S3S2

STATUS

3010




Basi di Dati

Pagina Pagina 4141

Ricerca con Ordinamento (cont.)La colonna dell'ordinamento può essereidentificata dal numero d'ordine dell'argomento. SELECT P.P#, 'Weight in grams=', P.WEIGHT*454 FROM P ORDER BY 3,P#;

P#P1 Weight in grams = 5448P5 Weight in grams = 5448P4 Weight in grams = 6356P2 Weight in grams = 7718P3 Weight in grams = 7718P6 Weight in grams = 8626

Risultato :




Basi di Dati

Pagina Pagina 4242

JOINInterrogazione che ricerca un insieme di dati indue o più tabelle.EquijoinEquijoin semplice: semplice:Ricavare tutte le combinazioni di informazioniriguardanti fornitori e parti in modo cheentrambi appartengano alla stessa città. SELECT S.*,P.* FROM S,P WHERE S.CITY=P.CITY;Identificatori completamente espressi per evita-re ambiguità.




Basi di Dati

Pagina Pagina 4343

JOIN

Esecuzione del prodotto cartesiano delle tabelleelencate: questo da origine ad una tabellarisultante composta da tutte le possibili righe rdove r r è la concatenazione di una riga dellaprima tabella, una della seconda ... una dellaennesima tabella; da tale tabella vengonoeliminate tutte le righe che non soddisfano lacondizione espressa in WHERE.




Basi di Dati

Pagina Pagina 4444

JOIN: Esempio

S# SNAME STATUS S.CITY P# PNAME COLOR WEIGHT P.CITY

S1S1S1S1S1S1S2...

S5

SmithSmithSmithSmithSmithSmithJones

.

.

.Adams

20202020202010

.

.

.30

LondonLondonLondonLondonLondonLondonParis...Athens

P1P2P3P4P5P6P1...

P6

NutBoltScrewScrewCamCogNut...Cog

RedGreenBlueRedBlueRedRed...Red

12171714121912

.

.

.19

LondonParisRomeLondonParisLondonLondon...London




Basi di Dati

Pagina Pagina 4545

JOIN: Risultato


S1S1S1S2S2S3S3S4S4S4

SmithSmithSmithJonesJonesBlakeBlakeClarkClarkClark

20202010103030202020

LondonLondonLondonParisParisParisParisLondonLondonLondon

P1P4P6P2P5P2P5P1P4P6

NutScrewCogBoltCamBoltCamNutScrewCog

RedRedRedGreenBlueGreenBlueRedRedRed

12141917121712121419

LondonLondonLondonParisParisParisParisLondonLondonLondon




Basi di Dati

Pagina Pagina 4646

JOIN: OsservazioniIl costrutto sintattico indica le due tabelle dacollegare e la condizione esprime laconnessione tra di esse (città uguali)S# SNAME STATUS CITYS1 Smith 20 London

P# PNAME COLOR WEIGHT CITY

S# SNAME STATUS CITY P# PNAME COLOR WEIGHT P.CITY

P1 Nut Red 12 London

S1 Smith 20 London P1 Nut Red 12 London




Basi di Dati

Pagina Pagina 4747

Natural JOIN

Natural join: provoca l'eliminazione di una delledue colonne 'CITY'.Equivale a: SELECT S#, SNAME, STATUS, S.CITY, P#, PNAME, COLOR, WEIGHT FROM S,P WHERE S.CITY=P.CITY;




Basi di Dati

Pagina Pagina 4848

Theta JOIN

Ricava tutte le combinazioni di fornitori eprodotti tali che la città del fornitore segue lacittà del prodotto in ordine alfabetico:

SELECT S.*,P.* FROM S,P WHERE S.CITY>P.CITY;




Basi di Dati

Pagina Pagina 4949

Theta JOIN: esempio

SELECT S.*,P.*FROM S,PWHERE S.CITY>P.CITY;


S2S2S2S3S3S3

JonesJonesJonesBlakeBlakeBlake

101010303030

ParisParisParisParisParisParis

P1P4P6P1P4P6

NutScrewCogNutScrewCog

RedRedRedRedRedRed

121419121419

LondonLondonLondonLondonLondonLondon

Risultato :




Basi di Dati

Pagina Pagina 5050

JOIN con predicati variRicavare tutte le combinazioni di fornitori eparti in cui i fornitori e le parti relative sonodella stessa città, tralasciando i fornitori constato=20

SELECT S.*,P.*FROM S,PWHERE S.CITY=P.CITY ANDS.STATUS<>20;




Basi di Dati

Pagina Pagina 5151

JOIN: esempio


S2S2S3S3

JonesJonesBlakeBlake

10103030

ParisParisParisParis

P2P5P2P5

BoltCamBoltCam

GreenBlueGreenBlue

17121712

ParisParisParisParis




Basi di Dati

Pagina Pagina 5252

JOIN di 3 tabelle

Ricavare tutte le coppie di nomi di città tali cheun fornitore della prima città fornisca una parteimmagazzinata nella seconda:SELECT DISTINCT S.CITY,P.CITYFROM S,SP,PWHERE S.S#=SP.S#AND SP.P#=P.P#;

Si noti l'uso di DISTINCT.

S.CITY P.CITY

LondonLondonLondonParisParis

LondonParisRomeLondonParis

Risultato :




Basi di Dati

Pagina Pagina 5353

JOIN: campi specifici

Individuare tutti i codici dei fornitori combinatiin modo tale che la parte ed il fornitoreappartengano alla stessa città.SELECT S.S#,P.P#FROM S,PWHERE S.CITY=P.CITY;

S# P#S1S1S1S2S2S3S3S4S4S4

P1P4P6P2P5P2P5P1P4P6

Risultato :




Basi di Dati

Pagina Pagina 5454

JOIN di una tabella con se stessaIndividuare tutte le coppie di fornitori tali che idue fornitori appartengano alla stessa città. SELECT FIRST.S#,SECOND.S# FROM S FIRST,S SECOND WHERE FIRST.CITY=SECOND.CITY AND FIRST.S#<SECOND.S#;

Questa operazione si basa sul join della tabellaS con se stessa (fatta sulle città uguali). Latabella S compare due volte in FROM.

S#S1S2

S#S4S3

Risultato :




Basi di Dati

Pagina Pagina 5555

Funzioni aggregate• COUNT: conta gli elementi in una colonna• SUM: somma dei valori in una colonna• AVG: media dei valori di una colonna• MAX: massimo valore in una colonna• MIN: minimo valore in una colonnaSUM e AVG agiscono su valori numerici.

– L'argomento della funzione può esserepreceduto da DISTINCT.

– COUNT(*) conta le righe di una tabella.




Basi di Dati

Pagina Pagina 5656

Funzioni aggregateEsempi:

– conteggio delle tuple in S: SELECT COUNT(*) FROM S;

– uso di DISTINCT: SELECT COUNT(DISTINCT S#) FROM SP;

– numero di forniture del prodotto P2: SELECT COUNT(*) FROM SP WHERE P#='P2';




Basi di Dati

Pagina Pagina 5757

Funzioni aggregate– Ricavare la quantità totale di prodotti P2

forniti. SELECT SUM(QTY) FROM SP WHERE P#='P2';

– Uso di GROUP BY: calcolare la quantitàfornita per ogni prodotto.

SELECT P#,SUM(QTY) FROM SP GROUP BY P#;

1000

P#

P1P2P3P5P5P6

....

6001000400500500100

Risultato :

Risultato :




Basi di Dati

Pagina Pagina 5858

HAVING

Ricavare i codici delle parti fornite da più di unfornitore. SELECT P# FROM SP GROUP BY P# HAVING COUNT(*)>1;

Having è per Group by ciò che Where è per lerighe della tabella

P#

P1P2P4P5

Risultato :




Basi di Dati

Pagina Pagina 5959

Ricerca testualeSi utilizza il costrutto LIKE: column-name LIKE char-string-const• il carattere _ sta per qualsiasi carattere• % sta per qualsiasi sequenza di n caratteriEsempio: ricavare le informazioni sui prodotti icui nomi iniziano con la lettera c: SELECT P.* FROM P WHERE P.PNAME LIKE 'c%';

P# PNAME COLOR WEIGHT CITYP5P6

CamCog

BlueRed

1219

ParisLondon




Basi di Dati

Pagina Pagina 6060

Ricerca testualeEsempi:

– ADDRESS contiene la stringa 'Berkeley' ADDRESS LIKE '%Berkley%'

– S# è esattamente di 3 caratteri con il primouguale a 'S'

S# LIKE 'S_ _'– PNAME è più lungo o uguale a 4 caratteri

con il quart'ultimo uguale a 'c' PNAME LIKE '%c_ _ _’

– CITY non contiene una 'E' CITY NOT LIKE '%E%'




Basi di Dati

Pagina Pagina 6161

Valori NULL

Esempio: ricavare i codici dei fornitori con statomaggiore di 25 (si supponga che S5 abbia statopari a NULL).

SELECT S# FROM S WHERE STATUS>25; S#

S3

Risultato :




Basi di Dati

Pagina Pagina 6262

Valori NULL

Il confronto di valori con NULL non da mairisultati corretti per qualunque operatore diconfronto.

– STATUS <=25 falso– STATUS = 25 falso– STATUS > 25 falso– STATUS <> 25 falso– STATUS = NULL errato– STATUS <> NULL errato




Basi di Dati

Pagina Pagina 6363

Valori NULL

Predicato di confronto per il valore NULL: nome-colonna IS [NOT] NULL

Esempio: trovare il codice di tutti i fornitori constato pari a NULL. SELECT S# FROM S WHERE STATUS IS NULL;




Basi di Dati

Pagina Pagina 6464

Interrogazione consottointerrogazione

Una sottointerrogazione è una interrogazioneannidata in un'altra interrogazione. Vieneintrodotta dal predicato IN. Prima si valutal'interrogazione interna, poi quella esterna.Esempio: trovare i nomi dei fornitori cheforniscono il prodotto P2. SELECT SNAME FROM S WHERE S# IN (SELECT S# FROM SP WHERE P#='P2');

SNAMESmithJonesBlakeClark

Risultato :




Basi di Dati

Pagina Pagina 6565

Interrogazione consottointerrogazione

– La precedente interrogazione è equivalenteal seguente join:

SELECT S.SNAME FROM S,SP WHERE S.S#=SP.S# AND SP.P#='P2':

– Per la base di dati dell'esempio, si puòanche scrivere l'interrogazione utilizzandoun set predefinito:

SELECT SNAME FROM S WHERE S# IN ('S1','S2','S3','S4');




Basi di Dati

Pagina Pagina 6666

Interrogazioni annidate

Trovare i nomi dei fornitori che fornisconoalmeno un prodotto rosso. SELECT SNAME FROM S WHERE S# IN (SELECT S# FROM SP WHERE P# IN (SELECT P# FROM P WHERE COLOR='Red'));

SNAMESmithJonesClark




Basi di Dati

Pagina Pagina 6767

Interrogazioni annidateTrovare il codice dei fornitori che operano nellastessa città di 'S1'. SELECT S# FROM S WHERE CITY = (SELECT CITY FROM S WHERE S#='S1');

Se è noto a priori che il valore restituito è unico,è possibile usare = o > ... al posto di IN.

S#S1S4




Basi di Dati

Pagina Pagina 6868

Interrogazioni annidate

Ricavare i codici dei fornitori il cui stato èminore del valore massimo attualmentepresente nella tabella. SELECT S# FROM S WHERE STATUS < (SELECT MAX(STATUS) FROM S);

S#

S1S2S4




Basi di Dati

Pagina Pagina 6969

Interrogazione con EXISTSRicavare i nomi dei fornitori che forniscono ilprodotto 'P2'. SELECT SNAME FROM S WHERE EXISTS (SELECT * FROM SP WHERE S# = S.S# AND P# = 'P2');

L'espressione EXISTS (SELECT * FROM ... ) èvera se e solo se il risultato della SELECT èdiverso dall'insieme vuoto.




Basi di Dati

Pagina Pagina 7070

Interrogazione con EXISTSEquivalente a: Selezionare i nomi dei fornitoriSelezionare i nomi dei fornitoriper i quali esiste una fornitura, qualunque essaper i quali esiste una fornitura, qualunque essasia, relativa al prodotto indicato.sia, relativa al prodotto indicato.Per valutare il funzionamento:Considerare ogni valore di SNAME a turno evalutare se il test successivo è vero o falso.Es. SNAME='Smith',(S#=S1): il set di record diSP, caratterizzati da S#=S1 e P#=P2 è vuoto?,Se no, ciò implica l'esistenza di un record conS#=S1 e P#=P2, pertanto Smith è uno dei valorida restituire. Parafrasando: selezionare i nomidei fornitori tali che esista una fornitura relativaal prodotto P2.




Basi di Dati

Pagina Pagina 7171

NOT EXISTS

Ricavare i nomi dei fornitori che non fornisconoil prodotto 'P2'.

SELECT SNAME FROM SWHERE NOT EXISTS (SELECT * FROM SP WHERE S# = S.S# AND P# = 'P2');

SNAME

Adams

Risultato :




Basi di Dati

Pagina Pagina 7272

NOT IN

Ricavare i nomi dei fornitori che non fornisconoil prodotto 'P2'. SELECT SNAME FROM S WHERE S# NOT IN (SELECT S# FROM SP WHERE P# = 'P2');




Basi di Dati

Pagina Pagina 7373

NOT EXISTSTrovare i nomi dei fornitori che forniscono tutti iprodotti:

SELECT SNAME FROM S WHERE NOT EXISTS (SELECT * FROM P

WHERE NOT EXISTS (SELECT * FROM SP WHERE S# = S.S# AND P# = P.P#));




Basi di Dati

Pagina Pagina 7474

NOT EXISTSTrovare i codici dei fornitori che fornisconoalmeno tutti i prodotti forniti da S2.La ricerca è frazionabile in passi successivi:1. Trovare tutti i codici dei prodotti forniti dal

fornitore S2. SELECT P# FROM SP WHERE S# = 'S2';2. Con CREATE TABLE e INSERT si possono

salvare tali dati in una tabella TEMP.2. Successivamente si cercano tutti i fornitori

che forniscono tali prodotti.




Basi di Dati

Pagina Pagina 7575

NOT EXISTS

SELECT DISTINCT S# FROM SP SPXWHERE NOT EXISTS (SELECT * FROM TEMP WHERE NOT EXISTS (SELECT * FROM SP SPZ WHERE SPZ.S# = SPX.S# AND SPZ.P# = TEMP.P#));




Basi di Dati

Pagina Pagina 7676

NOT EXISTS: Soluzione finale

SELECT DISTINCT S# FROM SP SPXWHERE NOT EXISTS (SELECT * FROM SP SPY WHERE S#=‘S2’ AND NOT EXISTS (SELECT * FROM SP SPZ WHERE SPZ.S# = SPX.S# AND SPZ.P# = SPY.P# ));




Basi di Dati

Pagina Pagina 7777

UNION

A UNION B, dove A e B sono insiemi è l'insiemedegli oggetti tali che se x appartiene ad A oappartiene a B o appartiene ad entrambi, xappartiene a (A UNION B). Le repliche vengonoeliminate.




Basi di Dati

Pagina Pagina 7878

UNION

Ricavare i codici dei prodotti che o pesano piùdi 16 o sono forniti dal fornitore S2, o entrambele cose: SELECT P# FROM P WHERE WEIGHT > 16 UNION SELECT P# FROM SP WHERE S# = 'S2';




Basi di Dati

Pagina Pagina 7979

Operatore di aggiornamento

UPDATE nome-tabella SET colonna = espressione [,colonna=espressione] [WHERE predicato];

Tutti i record della tabella nome-tabella chesoddisfano il predicato vengono modificati inbase all'assegnazione colonna=espressionenell'opzione SET.




Basi di Dati

Pagina Pagina 8080

Aggiornamento di unsolo record

UPDATE P SET COLOR = 'Yellow' , WEIGHT=WEIGHT+12, CITY = NULL, WHERE P# = 'P1';L'aggiornamento viene effettuato per il recordcorrispondente al codice P1.




Basi di Dati

Pagina Pagina 8181

Aggiornamento multiplo

Aggiornare lo stato al doppio del valore per tuttii fornitori di Londra. UPDATE S SET STATUS = 2 * STATUS WHERE CITY = 'London'

L'aggiornamento avviene per tutti i record chesoddisfano la condizione specificata.




Basi di Dati

Pagina Pagina 8282

Aggiornamento consottointerrogazione

Aggiornare a 0 la quantità fornita per tutti ifornitori di Londra.

UPDATE SP SET QTY = 0 WHERE 'London'= (SELECT CITY FROM S WHERE S.S# = SP.S#);




Basi di Dati

Pagina Pagina 8383

Aggiornamento di più tabelle

Modificare il codice del fornitore S2 a S9. UPDATE S SET S# = 'S9' WHERE S# = 'S2';

UPDATE SP SET S# = 'S9' WHERE S# = 'S2';




Basi di Dati

Pagina Pagina 8484

Aggiornamento di più tabelle

Una istruzione UPDATE può aggiornare unasola tabella. Si manifesta un problema diintegrità dopo aver modificato la tabella deifornitori. Per mantenere la coerenza ènecessario completare l'aggiornamento dientrambe le tabelle.




Basi di Dati

Pagina Pagina 8585

Operatore di cancellazione

DELETE FROM nome-tabella[WHERE predicato];Cancella dalla tabella nome-tabella tutti i recordche soddisfano il predicato.

Esempio: cancellare tutte le forniture. DELETE FROM SP;Svuota la tabella SP.




Basi di Dati

Pagina Pagina 8686

Cancellazione di recordCancellare il record corrispondente al fornitorecon codice S1. DELETE FROM S WHERE S# = 'S1';

Attenzione: se SP contiene dei riferimenti a S1 ildatabase perde la propria integrità.

Cancellare tutti i fornitori di Madrid.DELETE FROM S WHERE CITY = 'Madrid';




Basi di Dati

Pagina Pagina 8787

Cancellazione consottointerrogazione

Cancellare le vendite dei fornitori di Londra.

DELETE FROM SP WHERE 'London'= (SELECT CITY FROM S WHERE S.S# = SP.S#);




Basi di Dati

Pagina Pagina 8888

Operatore di inserimento

Due possibilità: INSERT INTO nome-tabella [(colonna[,colonna]...)] VALUES (costante[, costante] ...)

INSERT INTO nome-tabella [(colonna [,colonna] ...)] SELECT ... FROM ... WHERE ... ;




Basi di Dati

Pagina Pagina 8989

INSERT INTO P (P#,CITY,WEIGHT) VALUES ('P7','Athens',24);

Viene creato un nuovo record per il prodottoP7, NAME e COLOR sono inizializzati a NULL(Attenzione alla CREATE TABLE).

Inserimento di un solo record




Basi di Dati

Pagina Pagina 9090

Inserimento di un solo record

Inserire il prodotto P8 (name: Sprocket, colour:Pink, Weight: 12, city: Nice).INSERT INTO P VALUES ('P8','Sprocket','Pink',12,'Nice');

Omettere la lista dei campi equivale aspecificare tutti i campi secondo l'ordine dicreazione delle colonne nella tabella.




Basi di Dati

Pagina Pagina 9191

Inserimento di un solo recordInserire una nuova fornitura con fornitore S20,parte P20 e quantità 1000.INSERT INTO SP (S#, P#, QTY) VALUES ('S20','P20',1000);

Attenzione: in questo caso è necessario che P20e S20 esistano in S e P (problema di integritàreferenziale).




Basi di Dati

Pagina Pagina 9292

Inserimento di più record

Per ogni prodotto trovare il codice e lacorrispondente quantità totale di forniture,salvando poi il risultato nel database. CREATE TABLE TEMP (P# CHAR(6), TOTQTY INTEGER); INSERT INTO TEMP (P#, TOTQTY) (SELECT P#, SUM(QTY) FROM SP GROUP BY P#);




Basi di Dati

Pagina Pagina 9393

Inserimento di più record

– SELECT restituisce dati che vengonoimmediatamente inseriti nella tabellaTEMP.

– La tabella TEMP resta a disposizione persuccessive elaborazioni.

– Alla fine la tabella TEMP può essereeliminata:

DROP TABLE TEMP;




Basi di Dati

Pagina Pagina 9494

Interrogazione complessa

Per tutti i prodotti rossi o blu, tali che laquantità totale fornita sia superiore a 350(escludendo dal totale tutte le forniture per cuila quantità è minore di 200), ricavare il codicedei prodotti, il loro peso in grammi, il colore e laquantità massima fornita, ordinando il risultatoin ordine di valori crescenti della quantitàmassima e in ordine decrescente per codiceprodotto.




Basi di Dati

Pagina Pagina 9595


SELECT P.P#, 'Weight =', P.WEIGHT*454, P.COLOR,'Max qty =' MAX(SP.QTY)FROM P,SPWHERE P.P#=SP.P#AND (P.COLOR='Red' OR P.COLOR='Blue')AND SP.QTY>200GROUP BY P.P#,P.WEIGHT,P.COLORHAVING SUM(QTY) > 350ORDER BY 6, P.P# DESC;




Basi di Dati

Pagina Pagina 9696


a) FROM: dà origine al prodotto cartesiano delledue tabelle P e SP

b) WHERE: il risultato di (a) viene ridotto elimi-nando le righe che non soddisfano i predicati

c) GROUP BY: il risultato di (b) è raggruppatosecondo i valori dei campi indicati: P.P#,P.WEIGHT, P.COLOR

d) HAVING: i gruppi che non soddisfano la con-dizione SUM(QTY) sono eliminati dal risultatodi (c)




Basi di Dati

Pagina Pagina 9797


e) SELECT: ogni gruppo in (d) genera unasingola riga; viene estratto da ogni gruppo ilcodice della parte, il peso, il colore e la mas-sima quantità

f) ORDER BY: il risultato del quinto passoviene ordinato secondo la specifica


Basi di Dati Relazionali Parte I - Politecnico di...

Documents

Transcript of Basi di Dati Relazionali Parte I - Politecnico di...