Riassunto esame Sistemi informativi T-1, prof. Grandi

(SELECT

FROM ORDINE query interna (sottoquery)

WHERE IMPORTO > 10000)

Lo stesso risultato potevo ottenerlo con un join normale:

SELECT NOME, INDIRIZZO

FROM CLIENTE JOIN ORDINE

ON CLIENTE.COD-CLI = ORDINE.COD-LI

WHERE IMPORTO > 10000 Ι

Sistemi informativi - Parte - modulo 6 Interrogazioni complesse - Benassi Simone 9

Esempio:

Selezionare nome e indirizzo dei clienti che hanno emesso qualche ordine i cui dettagli comprendono il

prodotto “pneumatico”.

SELECT NOME, INDIRIZZO

FROM CLIENTE

COD-CLI

WHERE IN

(SELECT COD-CLI

FROM ORDINE

COD-ORD

WHERE IN

(SELECT COD.ORD

FROM DETTAGLIO

COD-PROD

WHERE IN

(SELECT COD-PROD

FROM PRODOTTO

WHERE NOME = ‘Pneumatico’)))

Lo stesso risultato si può ottenere con:

SELECT NOME, INDIRIZZO

FROM CLIENTE AD C, ORDINE AS O, DETTAGLIO AS D, PRODOTTO AS P

WHERE C.COD-CLI = O.COD-CLI

AND O.COD.ORD = D.COD-ORD

AND D.COD-PROD = P.COD PROD

AND NOME = ‘Pneumatico’

Query annidate correlate (con variabile)

In queste query la query interna dipende dalla query esterna: c’è quindi una correlazione fra le query.

Per questo motivo si usano le variabili relazionali.

SELECT NOME

FROM IMPIEGATI AS X

SALARIO

WHERE >

SALARIO

(SELECT

FROM IMPIEGATI

WHERE MATRICOLA = X.MANAGER) la X nella query interna denoterà una alla volta tutte

à le tuple di impiegati.

Lo stesso risultato si può ottenere con:

SELECT X.NOME

FROM IMPEGATI AS X, IMPIEGATI AS Y

WHERE X.SALARIO > Y.SALARIO self-join

à

AND Y.MATRICOLA = X.MANAGER

Ι

Sistemi informativi - Parte - modulo 6 Interrogazioni complesse - Benassi Simone 10

Esempio:

IN può essere usato anche per le modifiche.

Aumentare di L. 5000 l’importo di tutti gli ordini che comprendono il prodotto 456.

UPDATE ORDINE

SET IMPORTO = IMPORTO + 5000

COD-ORD

WHERE IN

COD-ORD

(SELECT

FROM DETTAGLIO

WHERE COS-PROD = ‘456’)

Esempio:

Selezionare nome e indirizzo dei clienti che non hanno emesso nessun ordine di importo superiore a

10000.

SELECT NOME, INDIRIZZO

FROM CLIENTE

COD-ORD

WHERE NOT IN

COD-ORD

(SELECT

FROM ORDINE

WHERE IMPORTO > 10000)

Esempio: IN equivale ad = ANY

ordine

COD-ORD IMPORTO

1 50000

2 300000

3 90000

SELECT COD-ORD SELECT COD-ORD

FROM ORDINE FROM ORDINE

IMPORTO IMPORTO

WHERE > ANY WHERE >= ALL

IMPORTO IMPORTO

(SELECT (SELECT

FROM ORDINE) FROM ORDINE)

50000 > ANY (50000, 300000, 9000) 50000 >= ALL (50000, 300000, 9000)

300000 > ANY (50000, 300000, 90000) 300000 >= ALL (50000, 300000, 90000)

90000 > ANY (50000, 300000, 90000) 90000 >= ALL (50000, 300000, 90000)

F F

V V

V F

Le variabili definite nelle query esterne sono visibili nelle query interne ma non viceversa.

Le query annidate si possono utilizzare con le istruzioni di cancellazione, modifica e inserimento.

query estremale query particolari che trovano MINIMI e MASSIMI.

à

Una funzione aggregata non può essere utilizzata nella clausola WHERE, ma solo nella SELECT e

nell’HAVING in alternativa si possono utilizzare query annidate.

à

La clasuola WHERE è utilizzata solo per condizioni di confronto.

Ι

Sistemi informativi - Parte - modulo 6 Interrogazioni complesse - Benassi Simone 11

@Query universali

Queste query verificano che una proprietà valga per tutti gli oggetti.

Bisogna ribaltare la logica delle query, inserendo una doppia negazione.

Esempio: “tutti voi siete studenti = nessuno di voi non è uno studente”

In questo caso si userebbe sia EXISTS, sia NOT EXISTS

Esempio: selezionare gli ordini che contengono tutti i prodotti.

Equivale a dire: selezionare gli ordini per cui non esiste alcun prodotto che non sia compreso

nelle loro righe di dettaglio.

SELECT*

FROM ORDINE AS ORD

EHERE NOT EXISTS

(SELECT FROM PRODOTTI

WHERE COD-PROD NOT IN

(SELECT COD-PROD

FROM DETTAGLIO AS DET

WHERE DET.COD-ORD = ORD.COD-ORD))

Ι

Sistemi informativi - Parte - modulo 6 Interrogazioni complesse - Benassi Simone 12

7.ASPETTI AVANZATI NELLA DEFINIZIONE DEGLI

SCHEMI

Gli aspetti avanzati nella definizione degli schemi (DDL) possono essere:

-creazione di indici;

-modifica degli schemi;

-gestione di viste;

-autorizzazioni di accesso;

-vincoli di integrità;

-procedure e regole attive.

@Creazione di indici

È possibile creare indici per l’accesso rapido ed efficiente ai dati.

La sintassi per creare un indice è : CREATE INDEX

Esempio: CREATE INDEX DATA-IX

ON ORDINI (DATA)

Oppure la sintassi è: CREATE UNIQUE INDEX

Esempio: CREATE UNIQUE INDEX ORD-KEY

ON ORDINI (ORD-COD)

@Modifica degli schemi

Al pari delle operazioni di modifica, inserimento e cancellazione delle istanze (parte estensionale) di una

relazione, è possibile anche modificare gli schemi (parte intensionale) della stessa per garantire

l’evoluzione della base di dati a fronte di nuove esigenze.

È possibile quindi:

-creare nuovi oggetti comando CREATE

à

-modificare oggetti preesistenti comando ALTER

à

-cancellare oggetti comando DROP

à

comando DROP

Con il è possibile cancellare domini, tabelle, indici, viste, asserzioni, procedure, trigger.

DROP TABLE ORDINI

DROP INDEX DATA-IX

È possibile però imporre dei vincoli ai comandi di cancellazione:

-RESTRICT impedisce la cancellazione se gli oggetti contengono istanze.

à

-CASCADE la cancellazione viene applicata agli oggetti ccollegati.

à

comando ALTER

Con il è possibile modificare domini e tabelle.

ALTER TABLE ORDINI

ADD COLUMN NUM-FATT CHAR (6) viene aggiunta una colonna di nome NUM-FATT con tutti

à valori nulli.

ALTER TABLE ORDINI

ALTER COLUMN IMPORTO

ADD DEFAULT 0 la colonna IMPORTO viene modificata, inserendo un valore di default (0).

à Ι

Sistemi informativi - Parte - modulo 7 Aspetti avanzati nella definizione degli schemi - Benassi Simone 1

ALTER TABLE ORDINI

DROP COLUMN DATA viene eliminata, dalla tabella ORDINI, la colonna DATA.

à

@Viste relazionali

Permettono la “visione” di tabelle virtuali (schemi esterni).

Sono classificate in:

semplici

-viste selezione e proiezione su una sola tabella;

à

complesse

-viste join, raggruppamento, …

à

La sintassi per creare le viste, rispetttivamente semplice e complesse, è la seguente:

CREATE WIEW <nome vista> AS <query>

CREATE WIEW <nome vista> (<nome attributi>) AS <query>

Le query possono includere al loro interno viste che sono state definite precedentemente.

Una volta che vengono definite le viste, esse possono essere utilizzate come vere e proprie relazioni.

Non è possibile modificare le tabelle di base mediante una vista.

Esempio: creazione di una vista che contenga gli ordini di importo superiori a 10000.

CREATE WIEW ORDINI-PRINCIPALI AS

SELECT*

FROM ORDINI

WHERE IMPORTO > 10000

Virtualmente esiste una relazione (vista) di nome ORDINI-PRINCIPALI che possiede lo stesso schema

della relazione originaria ORDINI con le tuple che soddisfano il predicato di selezione.

Esempio: ORDINI-PRINCIPALI

CREATE WIEW AS

SELECT* vista

FROM ORDINI

WHERE IMPORTO > 10000

SELECT NUM-CLI

ORDINI-PRINCIPALI

FROM viene effettuata un’interrogazione sulla vista.

à

Particolare attenzione va posta quando si effettuano modifiche tramite delle viste.

ORDINI-PRINCIPALI

CREATE WIEW AS

SELECT* vista

FROM ORDINI

WHERE IMPORTO > 10000

UPDATE ORDINI-PRINCIPALI modifica

SET IMPORTO = IMPORTO * 1,05

WHERE NUM-CLI = 3

In questo caso modifico la vista ORDINI-PRINCIPALI per gli ordini con NUM-CLI uguale a 3: in questo

modo viene modificata anche la relazione di partenza ORDINI.

Ι

Sistemi informativi - Parte - modulo 7 Aspetti avanzati nella definizione degli schemi - Benassi Simone 2

Esempio: STUDENTI-BO

CREATE WIEW AS

SELECT*

FROM STUDENTE

WHERE CITTA’ = ‘Bologna’

SELECT NOME

STUDENTI-BO

FROM

WHERE C-DIP =’Log’

INSERT INTO STUDENTE

VALUES (‘657’, ‘Anna’, ‘Bologna’, ‘Log’)

Inserendo un nuovo studente, in questo caso Anna, viene modificata anche la vista che è collegata alla

relazione.

Se eseguo nell’ordine queste righe di codice nella seconda query non viene vista Anna, poiché inserita

dopo nella relazione STUDENTE e conseguentemente nella vista STUDENTI-BO.

Se invece inverto le ultime due porzioni di codice Anna viene vista dalla query (ora eseguita per ultima).

Esempio: vista complessa con raggruppamento

CRETAE WIEW TOT-ORDINI (CLIENTE, TOTALE) AS

SELECT NUM-CLI, SUM(IMPORTO)

FROM ORDINE

GROUP BY NUM-CLI PRODOTTO)

CREATE WIEW CLI-PRO (CLIENTE, ASS

COD-CLI, COD-PROD

SELECT

FROM ORDINE JOIN DETTAGLIO

ORDINE.COD-ORD DETTAGLIO.COD-ORD

ON =

CLIENTE PRODOTTO

12 45

JOIN

ordine dettaglio

COD-CLI COD-ORD … COD- COD- …

12 33 ORD PROD

33 45

Non è possibile modificare ke rìtabelle di base (ossia le tabelle da cui prendono i dati le viste) perché

l’interpretazione è ambigua.

Ι

Sistemi informativi - Parte - modulo 7 Aspetti avanzati nella definizione degli schemi - Benassi Simone 3

@Autorizzazioni d’accesso

le autorizzazioni di accesso sono utilizzate per proteggere la base di dati e consentire l’accesso alla stessa

solo da parte di utenti autorizzati.

I meccanismi di identificazione dell’utente tramite la password possono avvenire quando lo stesso si

collega al sistema informatico a quando accede al DBMS. privilegi di accesso alle risorse.

Attraverso l’autorizzazione di accesso si attribuiscono agli utenti dei

Le istruzioni di interesse sono:

-GRANT privilegi

à

-ON risorse (selezione, proiezione, modifica, ecc)

à

-TO utenti

à

principali privilegi di accesso

I sono:

-SELECT attributi

-UPDATE attributi

-INSERT

-DELETE

-ALL PRIVILEGES

Esempi:

GRANT ALL PRIVILEGES

ON ORDINE

TO USER1 vengono concessi all’utente 1 tutti i privilegi sulla relazione ORDINE.

à

GRANT UPDATE(IMPORTO)

ON ORDINE

TO USER2 viene concesso all’uten