Introduzione a SQL Server. Giorno I zIntroduzione allarchitettura di un DBMS zArchitettura di SQL...

Introduzione a SQL Server

Giorno I

Introduzione all’architettura di un DBMS Architettura di SQL Server Componenti di base di un database in SQL Server

DDL da Enterprise Manager Cenni DDL da Query Analyzer

Manipolazione di un database in SQL Server interrogazioni modifiche

esercitazione

Giorno II

Componenti avanzate Stored procedures triggers

Full-text search (cenni architetturali) esercitazione

Introduzione all’architettura di un DBMS

Che cos’è un DBMS?

Un DBMS è un sistema software che permette di gestire grosse quantità di dati in modo: efficiente sicuro concorrente affidabile

Il modello dei dati

Un DBMS permette di descrivere I dati in base ad un modello dei dati

il modello supportato da Access è il modello relazionale, in base al quale I dati vengono rappresentati in tabelle, ciascuna composta da tuple - o record -

un insieme di dati rappresentati secondo un determinato modello prende il nome di schema

I dati contenuti nelle tabelle possono poi venire collegati attraverso il meccanismo delle chiavi

Esempio

Imp#Nomedip#mansionedata_astipendio

Dip# Nome_dipUfficiodivisioneattività dirigente n

n

11

I linguaggi

Un DBMS in genere supporta tre tipi di linguaggi Data Definition Language (DDL)

permette di descrivere lo schema Data Manipulation Language (DML)

permette di interrogare I dati (Query Language) e di modificarli

Data Storage Language (DSL) permette di influenzare la memorizzazione dei dati prevista

dal sistema

SQL (Structured Query Language) è un linguaggio standard per l’accesso a basi di dati relazionali che supporta tutte le funzionalità sopra elencate

Funzionalità di un DBMS Il modello dei dati permette di rappresentare dati a livello

logico, che è il livello corretto per gli utenti del DB Affinché però un sistema software possa essere definito

DBMS, è necessario che I dati possano essere utilizzati in modo: efficiente

strutture di memorizzazione, strutture di accesso, ottimizzazione di interrogazioni

concorrente transazioni

affidabile transazioni

sicuro accesso dipendente dal contenuto

Efficienza

In un DBMS l’efficienza è garantita dalle strutture dati utilizzate per rappresentare lo schema logico e dall’efficienza del sistema nell’operare su tali strutture

Esistono varie strutture alternative per implementare un modello dei dati

La scelta dalla strutture più efficienti dipende dal tipo di accessi che si eseguono sui dati

Normalmente un DBMS ha le proprie strategie di implementazione di un modello dei dati; tuttavia l'utente (esperto) può influenzare le scelte fatte dal sistema

Supporti di memorizzazione I dati memorizzati in una base di dati devono essere

fisicamente memorizzati su un supporto fisico di memorizzazione

Basi di dati in genere memorizzate su memoria secondaria (dischi magnetici) troppo grosse per risiedere in memoria principale maggiori garanzie di persistenza dei dati costo per unita' di memorizzazione decisamente inferiore

I dati sono trasferiti tra il disco e la memoria principale in unita' chiamate blocchi un blocco e' una sequenza di byte contigui la dimensione del blocco dipende dal sistema operativo

Organizzazione di file

I dati sono generalmente memorizzati in forma di record

un record è costituito da un insieme di valori (campi) collegati

un file e' una sequenza di record Poiche' i dati sono trasferiti in blocchi tra la MS e la MM,

e' importante assegnare i record ai blocchi in modo tale che uno stesso blocco contenga record tra loro interrelati

Se si riesce a memorizzare sullo stesso blocco record che sono spesso richiesti insieme si risparmiano accessi a disco

Organizzazione di file

Per DBMS di piccole dimensioni (es. per PC) una soluzione spesso adottata e' di memorizzare ogni relazione in un file separato

Per DBMS large scale una strategia frequente e' di allocare per il DBMS un unico grosso file, in cui sono memorizzate tutte le relazioni la gestione di questo file e' lasciata al DBMS es. Access

Strutture ausiliarie di accesso Spesso le interrogazioni accedono solo un piccolo

sottoinsieme dei dati

Per risolvere efficientemente le interrogazioni puo' essere utile allocare delle strutture ausiliarie che permettano di determinare direttamente i record che verificano una data query (senza scandire tutti i dati) indici

Esempio: se voglio determinare lo stipendio del Sig. Rossi, un indice permette di recuperare direttamente la tupla che memorizza le informazioni relative al Sig. Rossi, senza scandire tutta la tabella

Esecuzione di interrogazioni

Quindi quando una query e' presentata al sistema occorre determinare il modo piu' efficiente per eseguirla usando le strutture disponibili

Per interrogazioni complesse esistono piu' strategie possibili

La scelta della strategia e' fatta principalmente in base al numero di accessi a disco

Anche se il costo di determinare la strategia ottima puo' essere alto, il vantaggio in termini di efficienza che se ne ricava e' tale che in genere conviene eseguire l'ottimizzazione

Transazioni

Per mantenere le informazioni consistenti e' necessario controllare opportunamente le sequenze di accessi e aggiornamenti ai dati

Tali sequenze sono dette transazioni Ogni transazione e’ eseguita o completamente (cioe' effettua il

commit), oppure per nulla (cioe' effettua l'abort) se si verifica un qualche errore (hardware o software) durante l'esecuzione

Necessita' di garantire che le transazioni eseguite concorrentemente si comportino come se fossero state eseguite in sequenza correttezza concorrenza

Necessita' di tecniche per ripristinare uno stato corretto della base di dati a fronte di malfunzionamenti di sistema affidabilità

Sicurezza

Il controllo dell’accesso regola le operazioni che si possono compiere sulle informazioni e le risorse in una base di dati

Scopo: limitare e controllare le operazioni che gli utenti effettuano, prevenendo accidentali o deliberate azioni che potrebbero compromettere la correttezza e la sicurezza dei dati

Nel controllo dell’accesso: oggetti: risorse a cui si vuole garantire protezione soggetti: agenti che richiedono di potere esercitare

privilegi sui dati

Sicurezza

I modelli di controllo dell’accesso per basi di dati differiscono da quelli definiti per sistemi operativi: una BD è caratterizzata da un numero maggiore di oggetti

rispetto ad un SO deve potere avvenire a diversi livelli di granularità:

relazione, tupla, attributo risorse reali e logiche (es. Viste) deve tenere conto anche della semantica dei dati

diversi DBMS supportano diverse politiche di controllo dell’accesso

SQL permette di specificare alcune di queste politiche

Architettura

Dalla discussione precedente, segue che un DBMS contiene diverse strutture dati che includono: i file con i dati (cioe' i file per memorizzare il DB stesso) file dei dati di sistema (che includono il dizionario dei

dati e le autorizzazioni) indici (esempio B tree o tabelle hash) dati statistici (esempio il numero di tuple in una

relazione) che sono usati dallo strategy selector per determinare la strategia ottima di esecuzione

Architetture per l’utilizzo di un DBMS

Architetture client-server

Modello generale di interazione tra processi software, in cui I processi che interagiscono si suddividono in: client: richiedono servizi

ruolo attivo server: forniscono servizi

ruolo reattivo

I client richiedono un insieme limitato di servizi ad uno o più server

I server rispondono a molte richieste provenienti da molti processi client


Nel contesto delle basi di dati sono ormai diffuse portano ad una chiara separazione tra le

funzionalità di client e server separazione tra progettazione e gestione

caratteristiche macchine client/server: client: adatti ad interazione con l’utente (per ad

esempio, interrogazione dati) server: adatti alla gestione dati (memorizzazione e

gestione, elevata efficienza)


SQL offre un paradigma di programmazione ideale per l’identificazione dell’interfaccia di servizi: l’interrogazione sono formulate dal client e inviate al

server I risultati delle interrogazioni sono calcolati dal server e

inviati al client grazie alla standardizzazione di SQL, è possibile

progettare applicazioni che interagiscono con diversi server

Esempi

Alcuni DBMS supportano sia la versione Desktop (Oracle, SQL Server) che la versione client/server

Access può essere visto come un Desktop database

non può essere definito un DBMS in quanto non supporta tutte le funzionalità elencate in precedenza

Architettura di SQL Server

Architettura

SQL Server è un DBMS relazionale, progettato per funzionare efficientemente in due ambienti:

client/server desktop

Client/server architecture

Desktop architecture

Architettura

La versione Desktop supporta qualche funzionalità in meno in termini di ottimizzazione

noi utilizzeremo la versione Desktop

I tool supportati sono gli stessi

Architettura

SQL Server Engine

SQL ServerEnterpriseManager

SQL Server Engine

É un motore basato su SQL compatibile con SQL-92, entry-level il dialetto SQL implementato da SQL Server è

chiamato Transact-SQL (T-SQL) T-SQL estende SQL, fornendo versioni

semplificate di alcune operazioni supporta inoltre costrutti tipici dei linguaggi di

programmazione (es. IF-THEN-ELSE) e permette di organizzare sequenze di statement SQL in procedure (stored procedure)

SQL Server Enterprise Manager

Interfaccia di amministrazione permette di amministrare server multipli dalla

stessa console permette di gestire ciascun server

gestione database: creazione, modifica, cancellazione, mantenimento

gestione oggetti contenuti in un database: tabelle, viste, vincoli, ecc.

gestione server replicazione sicurezza

SQL Query Analyzer

Permette di eseguire interattivamente statement T-SQL

è possibile visualizzare il piano di esecuzione prescelto per lo statement specificato

è possibile determinare quali indici associare alle tabelle considerate in una query (Index Tuning Wizard)

è possibile visualizzare statistiche sulle query eseguite

Componenti di base di un database in SQL Server

Architettura

In SQL Server 2000, I dati sono organizzati in database

esistono due tipi di organizzazione: logica: schema relazionale fisica: strumenti di memorizzazione

l’utente interagisce solo con l’organizzazione logica

l’amministratore può intervenire anche sull’organizzazione fisica

Architettura

Database Ogni istanza di SQL Server contiene 4 database di sistema e uno o più database creati dall’utente

Master: contiene I dati di sistema (cataloghi) tempdb: contiene dati temporanei msdb: utilizzato da SQL Server Agent, per schedulare attività come backup e replicazioni model: modello dal quale vengono creati tutti I database

Database

Ogni utente del sistema è associato ad un database di default

quindi quando l’utente si connette, può direttamente lavorare su tale database

tale database può comunque essere cambiato é comunque possibile cambiare database (se si

hanno I diritti)

Database

ogni database contiene tabelle e altri oggetti ogni database viene mappato in un insieme di

filegroups uno primario, che contiene informazioni su startup e tabelle uno secondario, utilizzato per motivi di efficienza transaction log, utilizzato per gestire le transazioni

l’amministratore può intervenire nell’organizzazione di tali file

possono anche essere read-only utilizzati ad esempio per memorizzare dati storici, non più

operativi

Gestione database da Enterprise Manager Enterprise Manager permette di eseguire tramite

interfaccia molte operazioni sui database: Creazione modifica proprietà cancellazione creazione oggetti associati condivisione con altri server definizione piani di mantenimento (integrità, backup,

statistiche,…) import/export dati generazione script backup ...

Gestione Database tramite SQL

La generazione script da Enterprise Manager permette di generare gli script SQL per la creazione e la cancellazione degli oggetti contenuti nel database

questi sono comandi DDL, perché permettono di modificare lo schema del database

gli stessi comandi SQL possono essere specificati ed eseguiti dalla finestra del Query Analyzer

Gestione Database tramite SQL CREATE DATABASE <nome database>; DROP DATABASE <nome database>; USE <nome database>; Esempio

CREATE DATABASE prova;DROP DATABASE prova;

; non è obbligatorio, se manca viene aggiunto automaticamente

I messaggi indicano la creazione del filegroup primario e del transaction log

Elementi di un DB Componenti:

Tabelle Viste

Stored Procedures Trigger ...

Ogni componente può essere creata direttamente da SQL Server Enterprise Manager

Nel seguito: analizzeremo le componenti principali vedremo come crearle da SQL Enterprise Manager e da SQL Query

Analyzer tramite DDL

Tabelle Ogni database è composto da insieme di tabelle,

come in Access vengono supportate due tipi di tabelle:

persistenti temporanee

le tabelle temporanee vengono memorizzate nel db tempdb e cancellate quando l’utente si disconnette dal sistema

due tipi di tabelle temporanee: locali: iniziano con # e sono visibili solo alla connessione che

le ha create globali: iniziano con ## e sono visibili a tutte le connessioni

Principali tipi di dato supportati

Binary data types: permettono di memorizzare dati in formato esadecimale binary: dati a lunghezza fissa (fino a 8Kb) varbinary: dati a lunghezza variabile (fino a 8Kb) image: lunghezza arbitraria, fino ad un max di 2Gb

utile per memorizzare ad esempio documenti word, immagini, ecc.

character data types: permettono di memorizzare informazioni testuali, rappresentate con un set di caratteri stabilito dal sistema (quello della tastiera) char: dati a lunghezza fissa (fino a 8Kb) varchar: dati a lunghezza variabile (fino a 8Kb) text: dati a lunghezza variabile, fino ad un max di 2Gb

ad esempio documenti HTML o XML


Unicode Data types:permettono di memorizzare caratteri provenienti da set diversi (es. Alfabero giapponese, russo) nchar: lunghezza fissa, fino a 4000 caratteri nvarchar: lunghezza variabile, fino a 4000 caratteri ntext: lunghezza variabile, maggiore di 4000 caratteri,

fino ad un max di 2Gb preceduti da N

Date and time data datetime, smalldatetime ...


numeric data int, smallint, numeric, float, real, money,smallmoney

special data timestamp: numero crescente, in formato binario, che

rappresenta l’istante in cui la riga è stata modificata per l’ultima volta

bit uniqueidentifier sql_variant: colonna che memorizza valori di vario tipo,

ad eccezione di text, ntext, timestamp, image, sql_variant

Gestione tabelle da Enterprise Manager

Le tabelle possono essere create e manipolate direttamente da Enterprise Manager

interfaccia simile a quella di Access ma estesa a supporto di aspetti presenti in SQL Server e non in Access permessi constraint indici trigger

Gestione tabelle da SQL

CREATE TABLE <nome tabella>( <nome campo_1> <tipo_1>,

…<nome campo_n> <tipo_n> )

Esempio: CREATE TABLE Impiegati (Imp# numeric(4) PRIMARY KEY, Nome VarChar(20), Mansione VarChar(20), Data_A Datetime, Stipendio Numeric(7,2), Premio_P Numeric(7,2), Dip# Numeric(2));

Generazione automatica di valori

È possibile generare in modo automatico valori univoci da assegnare ad un campo

l’unicità può essere locale, all’interno di una singola tabella globale, all’interno del database

Identificatore locale

CREATE TABLE prova(campo1 int IDENTITY(1,2));

valore iniziale: 1incremento: 2

quando si inserisce una tupla non si devono specificare valori per campo1 vengono gestiti direttamente dal sistema

Identificatore globale

CREATE TABLE prova(campo1 uniqueidentifier);

oppure

CREATE TABLE prova(campo1 int ROWGUIDCOL);

Vincoli di integrità (constraints)

I vincoli di integrità rappresentano condizioni che I dati devono soddisfare per essere coerenti con la realtà che rappresentano

I vincoli possono essere strutturali: rappresentano relazioni tra I dati

memorizzati nel database semantici: rappresentano condizioni semantiche sui dati

Vincoli strutturali

PRIMARY KEY: vincolo di chiave primaria FOREIGN KEY: vincolo di chiave esterna UNIQUE constraint: valori unici all’interno di una

tabella DEFAULT: permette di specificare un valore di

default per un attributo NULL, NOT NULL: permette di specificare se I

valori di un attributo possono o meno essere nulli CHECK: permette di specificare il range di valori

ammissibili per un certo attributo

Vincoli strutturali

Per I vincoli di FOREIGN KEY è possibile specificare il comportamento da adottare in caso di cancellazione e/o aggiornamento

ON DELETE CASCADEON DELETE NO ACTION

ON UPDATE CASCADEON UPDATE NO ACTION

Esempio

CREATE TABLE Docente (Dno Char(7), Dnome Varchar(20) NOT NULL, Residenza Varchar(15), PRIMARY KEY (Dno));

CREATE TABLE Relatore (Dno Char(7), Sno Char(6) DEFAULT 23, PRIMARY KEY (Sno), FOREIGN KEY (Dno) REFERENCES Docente ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (Sno) REFERENCES Studente(Sno)ON DELETE CASCADE, ON UPDATE CASCADE);

CREATE TABLE Studente (Sno Char(6), Sname Varchar(20), Residenza Varchar(15), Birthdate Date, PRIMARY KEY (Sno));

Vincoli semantici

Possono essere specificati tramite I trigger

li vedremo più avanti

Diagrammi

I diagrammi sono oggetti di database che permettono di rappresentare le relazioni, in termini di chiavi esterne, esistenti tra tabelle

permettono inoltre di definire tramite interfaccia grafica tali vincoli

analogo alle relazioni di Access

Esempio

Nel seguito utilizzeremo le seguenti tabelle negli esempi:

Impiegati(Imp#, Nome, Mansione, Data_A, Stipendio, Premio_P, Dip#)

Dipartimenti(Dip#, Nome_Dip, Ufficio, Divisione, Attività, Dirigente)

Modifica e cancellazione

Tutte le proprietà delle tabelle possono essere modificate e cancellate

da Enterprise Manager con T-SQL

non lo vediamo

Manipolazione di un database in SQL Server

Manipolazione dei dati

Dopo avere progettato il database, è necessario potere interrogarlo e modificarlo

SQL Server permette di interrogare e aggiornare I dati in due modi differenti: tramite SQL dal Query Analyzer tramite interfaccia grafica simile a quella di Access,

dall’Enterprise Manager

Interrogazioni in SQL

SELECT Ri1.C1 , Ri2.C2 , ....., Rin.Cn FROM R1 , R2 , ....., Rk WHERE F;

dove

R1 , R2 , ....., Rk e' una lista di nomi distinti di relazioni; Ri1.C1 , Ri2.C2 , ....., Rin.Cn e' una lista di nomi di colonne la notazione R.C indica la colonna di nome C nella relazione R se una sola relazione nella lista di relazioni nella clausola

FROM ha una colonna di nome C, si puo' usare C invece di R.C F e' un predicato

Significato interrogazione

Genera il prodotto Cartesiano d R1 , R2 , ....., Rk (cioè combina in tutti I modi possibili le tuple di R1 , R2 , ....., Rk)

Applica alla relazione risultante la selezione F

Restituisci solo le componenti Ri1.C1 , Ri2.C2 , ....., Rin.Cn

Esempio

Q1: selezionare gli impiegati che hanno uno stipendio

maggiore di 2000

SELECT * FROM Impiegati WHERE Stipendio>2000;

Il simbolo * nella clausola di proiezione indica che tutte le colonne delle relazione devono essere ritrovate

Esempio

Q2: selezionare il nome e il numero di dipartimento degli impiegati che hanno uno stipendio maggiore di 2000 e hanno mansione di ingegnere

SELECT Nome, Dip# FROM Impiegati WHERE Stipendio>2000 AND Mansione = 'ingegnere';

Esempio

Q3: selezionare il numero degli impiegati che lavorano nel dipartimento 30 e sono ingegneri o tecnici

SELECT Imp# FROM Impiegati WHERE Dip#=30 AND (Mansione = 'ingegnere' OR Mansione = 'tecnico');

Operatori: Between

condizioni su intervalli di valori: l'operatore BETWEEN permette di determinare le tuple che contengono in un dato attributo valori in un intervallo dato; C BETWEEN v1 AND v2 forma negata C NOT BETWEEN v1 AND v2

Esempio

SELECT Nome, Stipendio FROM Impiegati WHERE Stipendio BETWEEN 1100 AND 1400;

Operatori: LIKE

confronto tra stringhe di caratteri: l'operatore LIKE permette di eseguire alcune semplici operazioni di pattern matching su colonne di tipo stringa

un predicato di confronto espresso con l'operatore LIKE ha il seguente formato

C LIKE pattern dove pattern e' una stringa di caratteri che puo' contenere i caratteri speciali % e _ il carattere % denota una sequenza di caratteri arbitrari di

lunghezza qualsiasi (anche zero) il carattere _ denota esattamente un carattere

.

Esempio LIKE

Determinare tutti gli impiegati che hanno 'R' come terza lettera del cognome

SELECT Nome FROM Impiegati WHERE Nome LIKE '_ _ R%';

Ordinamento

Negli esempi visti, l'ordine delle tuple risultato di una interrogazione e' determinato dal sistema (dipende dalla strategia usata per eseguire l'interrogazione)

e' possibile specificare un ordinamento diverso aggiungendo alla fine dell'interrogazione la clausola ORDER BY , ORDER BY DESC

Esempio

SELECT Stipendio, Mansione, Nome FROM Impiegati WHERE Dip#=30 ORDER BY Stipendio;

SELECT Mansione, Stipendio, Nome FROM Impiegati ORDER BY Mansione, Stipendio DESC;

DISTINCT

Una query può anche restituire duplicati per eliminarli: clausola DISTINCT

SELECT Mansione FROM Impiegati; SELECT DISTINCT Mansione FROM Impiegati;

Join

L'operazione di join rappresenta un'importante operazione in quanto permette di correlare dati rappresentati da relazioni diverse

In genere il join e' espresso in SQL tramite un prodotto Cartesiano a cui sono applicati uno o piu' predicati di join

un predicato di join esprime una relazione che deve essere verificata dalle tuple risultato dell'interrogazione

Esempio

Esempio: determinare il nome del dipartimento in cui lavora l'impiegato Rossi

SELECT Nome_Dip FROM Impiegati, Dipartimenti WHERE Nome = 'Rossi' AND Impiegati.Dip# = Dipartimenti.Dip#;

il predicato di join e'

Impiegati.Dip# = Dipartimenti.Dip#

Espressioni e funzioni aritmetiche

i predicati usati nelle interrogazioni possono coinvolgere, oltre a nomi di colonna, anche espressioni aritmetiche

tali espressioni sono formulate applicando gli operatori aritmetici (+, , *, /) ai valori delle colonne delle tuple

le espressioni artimetiche possono comparire nella clausola di proiezione e nelle espressioni di assegnamenti del comando di UPDATE

Esempio

trovare il nome, lo stipendio, il premio di produzione, e la somma dello stipendio e del premio di produzione di tutti gli ingegneri per cui la somma dello stipendio e del premio di produzione e' maggiore di 2000

SELECT Nome, Stipendio, Premio_P, Stipendio+Premio_P FROM Impiegati WHERE Mansione = 'ingegnere' AND Stipendio+Premio_P > 2000;

Funzioni per stringhe

Concatenazione:SELECT Cognome + ' ' + Nome + ' ' + Indirizzo FROM Persone;

Funzioni: len(str): calcola la lunghezza di un stringa

WHERE len(Cognome) > 3 substring(str, m, n) (m ed n sono interi)

estrae dalla stringa 'str' la sottostringa dal carattere di posizione m per una lunghezza n

Funzioni per date

Le date possono essere sommate e sottratte

DATEADD(day, 21, GETDATE())

DATEDIFF(day,date, GETDATE())

GETDATE() restituisce la data corrente

Funzioni di gruppo

Una funzione di gruppo permette di estrarre informazioni da gruppi di tuple di una relazione

Le funzioni di gruppo si basano su due concetti fondamentali: partizionamento delle tuple di una relazione in base al valore di

una o piu' colonne della relazione le colonne da usare sono specificate tramite la clausola GROUP BY

calcolo della funzione di gruppo per ogni gruppo ottenuto dal partizionamento

una funzione di gruppo ha come argomento una colonna e si applica all'insieme di valori di questa colonna, estratti dalle tuple che appartengono allo stesso gruppo

Funzioni di gruppo le funzioni di gruppo comunemente presenti sono: MAX,

MIN, SUM, AVG, COUNT tutte le funzioni di gruppo, ad accezione di COUNT,

possono essere applicate solo su insiemi che consistono di valori semplici e non su insiemi di tuple

la funzione COUNT puo' avere due tipi di argomenti un nome di colonna in tal caso nello standard SQL2 e'

obbligatorio l'uso del qualificatore DISTINCT esempio: COUNT (DISTINCT Stipendio)

il carattere speciale '*' la funzione restituisce il numero di tuple presenti in un dato

gruppo esempio: COUNT(*)

Esempio

si vuole raggruppare gli impiegati in base al numero di dipartimento e si vuole determinare il massimo stipendio di ogni gruppo

SELECT Dip#, MAX(Stipendio) FROM Impiegati GROUP BY Dip#;

Esempio

supponiamo di voler raggruppare gli impiegati sulla base del dipartimento e della mansione; per ogni gruppo si vuole determinare il nome del dipartimento, la somma degli stipendi, quanti impiegati appartengono ad ogni gruppo, e la media degli stipendi

SELECT Nome_Dip, Mansione, SUM(Stipendio), COUNT(*), AVG(Stipendio) FROM Dipartimenti, Impiegati WHERE Dipartimenti.Dip#=Impiegati.Dip# GROUP BY Nome_Dip, Mansione;

Funzioni di gruppo

importante restrizione: una clausola di proiezione di una query contenente la clausola GROUP BY puo' solo includere: una o piu' colonne tra le colonne che compaiono nella

clausola GROUP BY

le funzioni di gruppo possono apparire in espressioni aritmetiche esempio: SUM(Stipendio) + SUM(Premio_p)

Having

e' possibile specificare condizioni di ricerca su gruppi di tuple

Esempio: supponiamo di voler eseguire una query come la precedente ma di essere interessati solo ai gruppi che contengono almento due impiegati

SELECT Nome_Dip, Mansione, SUM(Stipendio), COUNT(*), AVG(Stipendio) FROM Dipartimenti, Impiegati WHERE Dipartimenti.Dip#=Impiegati.Dip# GROUP BY Nome_Dip, Mansione HAVING COUNT(*) > 2;

Un modello di esecuzione

si applica la condizione di ricerca specificata nella clausola WHERE a tutte le tuple della relazione oggetto della query la valutazione avviene tupla per tupla

alle tuple ottenute al passo precedente, si applica il partizionamento specificato dalla clausola GROUP BY

ad ogni gruppo di tuple ottenuto al passo precedente, si applica la condizione di ricerca specificata dalla clausola HAVING

i gruppi ottenuti al passo precedente sono i gruppi di tuple che verificano la query

per tali gruppi, vengono calcolate le funzioni di gruppo specificate nella clausola di proiezione della query

i valori restituiti da tali funzioni costituiscono il risultato della query

Valori nulli

SQL usa una logica a tre valori per valutare il valore di verita' di una condizione di ricerca (clausola where)

True (T), False (F), Unknown (?) un predicato semplice valutato su un attributo a valore nullo da'

come risultato della valutazione ? il valore di verita' di un predicato complesso viene calcolato in

base alle seguenti tabelle di verita' AND OR T F ? T F ? T T F ? T T T T F F F F F T F ? ? ? F ? ? T ? ?

una tupla per cui il valore di verita' e' ? non viene restituita dalla query

NOT T F F T ? ?

IS NULL

il predicato IS NULL applicato ad un dato attributo di una tupla restituisce True se la tupla ha valore nullo per l'attributo

il predicato IS NOT NULL applicato ad un dato attributo di una tupla restituisce True se la tupla ha valore non nullo per l'attributo

Viste

Una vista è una tabella virtuale il contenuto della tabella non è infatti memorizzato

su disco ma definito tramite una query, al cui risultato viene assegnato un nome

tale nome identifica la vista una vista può essere utilizzata in ogni contesto in

cui può essere utilizzata una tabella In genere vengono utilizzate per semplificate la

percezione che l’utente ha del database possono essere utilizzate come base per definire

politiche di sicurezza

Gestione viste da Enterprise Manager

L’interfaccia fornita è del tutto simile a quella di Access per definire le query

La stessa interfaccia può essere utilizzata per definire la vista e per eseguire la query corrispondente, in modo da verificarne il risultato

Gestione viste da SQL

CREATE VIEW <nome vista>

AS

<query SQL che definisce la vista>

EsempioCREATE VIEW High_salaryASSELECT Name, salaryFROM EmployeeWHERE salary >2000;

Esempio

si vuole creare una vista costituita da un sottoinsieme delle tuple della relazione Impiegati; piu' precisamente la vista deve elencare le colonne Imp#, Nome e Mansione degli impiegati del dipartimento 10

CREATE VIEW Imp10 AS SELECT Imp#, Nome, Mansione FROM Impiegati WHERE Dip#=10;

View resolution

Le viste possono essere utilizzate nelle query ogni volta che ci si riferisce ad una vista, il nome della

vista viene rimpiazzato dalla sua definizione (query) quindi la query di partenza viene composta con la

query che definisce la vista, ottenendo una nuova query, che viene eseguita

le viste possono anche essere definite in termini di altre viste, in questo caso si itera il procedimento precedente

Esempio: selezionare le tuple della vista Imp10

SELECT * FROM Imp10;

Aggiornamenti in SQL

Tre possibili aggiornamenti: inserimento cancellazione modifica

eseguibili da Enterprise Manager tramite interfaccia grafica, simile a quella di Access oppure da Query Analyzer con SQL

Inserimento in SQL Tre possibilità:

si inserisce una nuova tupla

INSERT INTO Dipartimenti VALUES (40, 'Edilizia Industriale', 6100, 'D2',7698);

si selezionano tuple dal database e si inseriscono in una tabella esistente

INSERT INTO Promozioni (Nome, Stipendio, Premio_P) SELECT Nome, Stipendio, Premio_P FROM Impiegati WHERE Premio_P > 0.25*Stipendio AND Mansione = 'ingegnere';

Inserimento in SQL

si selezionano tuple dal database e si inseriscono in una nuova tabella

SELECT Nome, Sipendio, Premio_P INTO PromozioniFROM Impiegati WHERE Premio_P > 0.25*Stipendio AND Mansione = 'ingegnere';

Cancellazione in SQL

Si specifica la condizione che devono soddisfare le tuple da cancellare

DELETE FROM Dipartimenti WHERE Dip# = 40;

Modifica in SQL

Si deve specificare quali tuple devono essere modificate e come

UPDATE Impiegati SET Mansione = 'dirigente', Stipendio = 1.10*Stipendio WHERE Nome = 'Gianni';

Aggiornamento di viste Ogni volta che vengono modificate le tabelle di base di una view,

cambia implicitamente anche il contenuto della view è comunque possibile aggiornare direttamente una view sotto

alcune condizioni, tra cui: e' possibile eseguire l'operazione di DELETE se l'interrogazione di

definizione della vista soddisfa le seguenti condizioni: e' su una sola relazione non contiene la clausola GROUP BY, la clausola DISTINCT, o una funzione di

gruppo e' possibile eseguire l'operazione di UPDATE se l'interrogazione di

definizione della vista soddisfa le due condizioni precedenti ed inoltre la colonna modificata non e' definita da un'espressione

e' possibile eseguire l'operazione di INSERT se l'interrogazione di definizione della vista soddisfa le tre condizioni precedenti ed inoltre qualsiasi colonna per cui valga il vincolo NOT NULL sia presente nella vista

Esercitazione proposta

Utilizzare l’utente “giunti”, passwd: corsodbms Creare da Enterprise Manager un database corsi,

che mantenga le informazioni relative ai corsi on-line che si intendono gestire. In particolare, creare: database tabelle vincoli una vista per una specifica operazione

inserire opportuni dati nella tabella da Enterprise Manager e/o da Query Analyzer (provare entrambe le modalità)


da Query Analyzer, eseguire una decina di interrogazioni, ritenute significative in SQL e salvarne il testo in un file

modificare un corso (provare da Enterprise Manager e da SQL)

cancellare un corso (provare da Enterprise Manager e da SQL)

Componenti avanzate di un database in SQL Server

Statement multipli

Per eseguire operazioni che processano più statement SQL, SQL Server fornisce 4 possibilità: batches script stored procedure trigger

Batch

Gruppi di statement T-SQL inviati ed eseguiti contemporanemante all’SQL Engine

vengono compilati in un singolo piano di esecuzione

se si verifica errore di compilazione il piano non viene generato

se si verifica un errore in esecuzione gli statement seguenti non vengono eseguiti

per speficare un batch da SQL Query Analyzer: GO

EsempioCREATE TABLE Impiegati (Imp# numeric(4) PRIMARY KEY, Nome VarChar(20), Mansione VarChar(20), Data_A Datetime, Stipendio Numeric(7,2), Premio_P Numeric(7,2), Dip# Numeric(2));

SELECT Nome, Dip# FROM Impiegati WHERE Stipendio>2000 AND Mansione = 'ingegnere';

GO

Script

Sequenza di statement T-SQL memorizzati in un file e quindi eseguiti, utilizzando una funzionalità particolare di SQL Server

possono essere eseguiti dalla shell del DOS mediante il comando: osql

osql -U <nome utente> -i <nome file input> -o <nome file risultato>

viene chiesta la password

Stored Procedures Le stored procedure sono simili al concetto di procedura (o

funzione) presente nei linguaggi di programmazione come vedremo le applicazioni possono poi utilizzare le

stored procedure per interagire con il DBMS funzionamento di base:

accettano parametri di input usano T-SQL per elaborare I dati contenuti nel DB settano parametri di output restituiscono valori di stato per indicare se l’esecuzione ha avuto

successo

Possono essere create sia da Enterprise Manager che da Query Analyzer, ma è sempre necessario specificare il codice T-SQL

Store procedures

Esistono stored procedure predefinite, associate al database Master

I nomi di tali procedure iniziano con sp_ nel seguito ne vedremo alcune

Creazione

Come per le tabelle anche le stored procedure possono essere temporanee

valgono le stesse convenzioni viste per le tabelle CREATE PROCEDURE <nome>

<parametri>AS<codice>

EXECUTE <nome> CREATE PROCEDURE deve essere l’unico

comando all’interno di un batch

Dichiarazioni

In T-SQL è possibile dichiarare variabili locali, valide all’interno del batch eseguito e assegnare valori per ogni tipo supportato da T-SQL

I nomi di variabili devono essere preceduti da @

DECLARE @ImpID int

SET @ImpID = 1234

Esempio

DECLARE @ImpID INTSET @ImpID = 1234GOCREATE PROCEDURE ImpSelectASSELECT * FROM ImpiegatiWhere #Imp = @ImpID;GO

Parametri

I parametri sono utilizzati per scambiare valori tra la procedura e l’applicazioni o il tool che la richiama

Tipi di parametri: input output valore di ritorno (se non specificato si assume 0)

Parametri di input

CREATE PROCEDURE ImpSelect @ImpID INTASSELECT * FROM ImpiegatiWhere Imp# = @ImpID;GOEXEC ImpSelect @ImpID = 1234GO

Parametri di input É possibile specificare un valore di default in questo caso, non sarà necessario passare un valore per

il parametro

CREATE PROCEDURE ImpSelect @ImpID INT = 1234ASSELECT * FROM ImpiegatiWhere Imp# = @ImpID;GOEXEC ImpSelect GO

EXEC ImpSelect

Parametri di Output Per restituire valori all’ambiente chiamante, è possibile

utilizzare parametri di output

CREATE PROCEDURE AvgSal@Dip int, @Avg int OUTPUTAS SELECT @Avg = avg(stipendio)FROM ImpiegatiWHERE Dip# = @Dip;GODECLARE @AVGex intEXEC AvgSal @Dip = 1, @Avg = @AVGex OUTPUTPRINT @AVGexGO

Valori di ritorno

L’istruzione RETURN permette di restituire un valore all’ambiente chiamanete

Per default 0 indica che l’esecuzione è andata a buon fine 1 indica che l’esecuzione ha generato errori

Esempio

CREATE PROCEDURE AvgSal@Dip int, @Avg int OUTPUTAS DECLARE @ErrorSave INTSET @ErrorSave = 0SELECT avg(stipendio)FROM ImpiegatiWHERE Dip# = @Dip;IF (@@ERROR <>0)

SET @ErrorSave = @@ERRORRETURN @ErrorSaveGO

DECLARE @AVGex intDECLARE @ReturnStatus INTEXEC @ReturnStatus = AvgSal

@Dip = 1, @Avg = @AVGex OUTPUTPRINT 'Return Status= ' + CAST(@ReturnStatus AS CHAR(10))PRINT @AVGexGO

Costrutti di controllo Classici costrutti imperativi per alterare il flusso

sequenziale di esecuzione degli statement specificati BEGIN END

stesso ruolo {} in Java

IF ELSE classico costrutto di scelta

WAITFOR WAITFOR DELAY ‘00:00:02’ aspetta due secondi WAITFOR TIME ‘22:00’ riparte alle 22

WHILE come in Java

Costrutti di controllo CASE SELECT name, CASE state

WHEN 'CA' THEN 'California'

WHEN 'KS' THEN 'Kansas'

WHEN 'TN' THEN 'Tennessee'

WHEN 'OR' THEN 'Oregon'

WHEN 'MI' THEN 'Michigan'

WHEN 'IN' THEN 'Indiana’

WHEN 'MD' THEN 'Maryland'

WHEN 'UT' THEN 'Utah'

END AS StateName

FROM Authors

Cursori

Gli statement SQL restituiscono un insieme di tuple può capitare di dovere analizzare le tuple una per una in questo caso è necessario associare al risultato un

cursore, cioè un puntatore che permette di muoversi all’interno di un insieme di tuple risultato

un cursore deve: essere dichiarato aperto utilizzato per muoversi sulle tuple chiuso

è possibile dichiarare una variabile di tipo cursore

Cursori Dichiarazione

DECLARE <nome cursore> CURSOR FOR<select statement>

Apertura OPEN <nome cursore>

Recupero tupla successivaFETCH NEXT FROM <nome cursore> INTO <lista variabili>

ChiusuraCLOSE <nome cursore>

DeallocazioneDEALLOCATE <nome cursore>

Cursori

@@FETCH_STATUSVariabile di sistema, è uguale a 0 se la tupla è stata letta, è < 0 se si è verificato qualche problema (ad esempio la tupla non esiste, siamo arrivati alla fine del result set)

Esempio

DECLARE ImpCursor CURSOR FOR SELECT Nome FROM Impiegati

OPEN ImpCursorDECLARE @NomeImp VARCHAR(10)FETCH NEXT FROM ImpCursor INTO @NomeImpWHILE (@@FETCH_STATUS = 0)

BEGINPRINT @NomeImpFETCH NEXT FROM ImpCursor INTO @NomeImp

ENDCLOSE ImpCursorDEALLOCATE ImpCursor

Creazione statement a runtime

É possibile creare a run time stringhe che rappresentano statement SQL

tali statement devono essere rappresentati come stringhe Unicode

vengono eseguiti utilizzando la stored procedure di sistema sp_executesql

ogni batch eseguito con sp_executesql non può accedere le variabili definite nel batch a cui appartiene la chiamata di sp_executesql e viceversa

Esempio

DECLARE @SQLString NVARCHAR(100)SET @SQLSTRING = N'SELECT * FROM Impiegati'EXEC sp_executesql @SQLString

ProblemaDECLARE @SQLString NVARCHAR(100)DECLARE @MyImp =12SET @SQLSTRING = N'SELECT * FROM Impiegati’ +

‘WHERE Imp# = @MyImp’EXEC sp_executesql @SQLString

DECLARE @MyImp =14SET @SQLSTRING = N'SELECT * FROM Impiegati’ +

‘WHERE Imp# = @MyImp’EXEC sp_executesql @SQLString

La stessa stringa viene compilata due volte

Soluzione: uso di parametri

SET @SQLSTRING = N'SELECT * FROM Impiegati’ +‘WHERE Imp# = @MyImp’

SET @ParamDef = N’@MyImp INT’

EXEC sp_executesql @SQLString, @ParamDef, @MyImp = 12EXEC sp_executesql @SQLString, @ParamDef, @MyImp = 24

in questo caso alla seconda esecuzione il sistema si accorge di avere già compilato lo statement e riutilizza il piano generato

Trigger

I trigger possono essere visti come un tipo particolare di stored procedure che viene attivata automaticamente quando I dati di una certa tabella vengono inseriti, modificati, cancellati

Vengono spesso utilizzati per: forzare vincoli di integrità semantici, che coinvolgono

più di una relazione per aggiornare viste per attivare azioni esterne

Esempio

Supponiamo di avere un campo n_imp nella tabella Dipartimenti che tiene conto di quanti impiegati lavorano in un dato dipartimento

un trigger potrebbe essere utilizzato per aggiornare automaticamente questo valore quando si inserisce o si cancella un impiegati

Trigger

In base ai vincoli fissati su una relazione, ogni operazione di aggiornamento richiede una verifica dei vincoli di integrità come side effect

In SQL Server esistono due tipi di trigger, classificati in base al loro comportamento rispetto al side effect delle operazioni di aggiornamento: INSTEAD OF: eseguiti prima di side effect AFTER: eseguiti dopo side effect

ogni tabella può avere al più un INSTEAD OF trigger per ogni operazione di aggiornamento ma più AFTER trigger

Trigger

Prima vengono eseguiti INSTEAD OF trigger (una sola volta, non possono essere attivati ricorsivamente)

poi vengono eseguiti AFTER trigger trigger INSTEAD OF sono soggetti a limitazioni

Non possono essere definiti su tabelle che hanno almeno una chiave esterna associata ad un vincolo per l’operazione di cancellazione e/o aggiornamento

Condizioni speciali

Test per stabilire se INSERT e UPDATE hanno aggiornato I valori contenuti in una certa colonna

CREATE TABLE my_table(a int NULL, b int NULL)

GO

CREATE TRIGGER my_trig ON my_table

FOR INSERT

AS

IF UPDATE(b)

PRINT 'Column b Modified'

GO

Tabelle speciali

Un’operazione di UPDATE può essere vista come un’operazione di DELETE seguita da un’operazione di INSERT

SQL Server 2000 mantiene automaticamente, per ogni tabella, due ulteriori tabelle: INSERTED: contiene tutte le tuple inserite con INSERT o

UPDATE DELETED: contiene tutte le tuple cancellate con DELETE

o UPDATE

Queste tabelle, temporanee, possono essere utilizzate nel corpo dei trigger

Esempio Se lo stipendio di Rossi è stato modificato e supera

2000 allora Rossi passa al dipartimento 4

CREATE TRIGGER updateRossi ON ImpiegatiAFTER UPDATE, INSERTASDECLARE @StipRossi INTSELECT @StipRossi = Stipendio FROM INSERTED WHERE Nome = ‘Rossi’IF @StipRossi > 2000

BEGINUPDATE ImpiegatiSET Dip# = 4WHERE Nome = ‘Rossi’

END


Creare una stored procedure per eseguire le seguenti operazioni: prende in input un argomento e un costo determina tutti I corsi relativi a quell’argomento e con

costo minore a quello dato stampa I corsi determinati in ordine crescente rispetto

al costo

provare ad eseguire la stored procedure attribuendo diversi valori ai parametri


Creare un trigger per la tabella corsi in modo che, quando viene inserito un nuovo corso, stampi il numero totale di corsi esistenti nel database

Full-text search

Problema

Le tabelle possono contenere campi contenenti informazioni testuali Char Varchar Text Image

Nasce il problema di come effettuare ricerche su tali campi

Esempio

Supponiamo che la tabella DIPARTIMENTI contenga un campo ATTIVITA’, che contiene una descrizione testuale delle attivita’ portate avanti dal dipartimento

Il campo ATTIVITA’ puo’ essere definito come VARCHAR(200) Supponiamo di volere determinare tutti I dipartimenti che si

occupano di ricerca In questo caso, ragionevolmente il campo ATTIVITA’ dovra’

contenere parole come “ricerca”, “ricerche”, ma anche magari “attivita’ scientifiche”, in quanto identificano ricerche

La ricerca potrebbe essere eseguita utilizzando il predicato LIKE, ma le possibiita’ a nostra disposizione in questo caso sarebbero ridotte rispetto a quelle supportate, come vedremo, da ricerche full-text

Full-text search

SQL Server permette di eseguire ricerche di questo tipo su campi testuali, mediante un procedimento in due passi: Indicizzazione: durante questa fase, si istruisce il

sistema sul contenuto delle colonne testuali da interrogare

Interrogazione: durante questa fase, si possono eseguire le interrogazioni sulle colonne testuali

Permette inoltre di indicizzare e interrogare Documenti binari (file WORD, ecc.) File memorizzati su file system

Full-text index

Per potere interrogare campi testuali e’ necessario istruire il sistema sul contenuto di tali campi

Cioe’ il sistema deve processare il contenuto di tali campi ed identificare le parole significative, da utilizzare poi in fase di ricerca

Esempio di informazione: La colonna 3 della tabella DIPARTIMENTI contiene nel campo

ATTIVITA’ la parola “ricerca” a partire dalla posizione 25, come parola numero 5

Non tutte le parole sono significative Noise word (es. articoli)

Le parole non significative non vengono considerate nella fase di indicizzazione

Full-text index

Se si vogliono utilizzare ricerche testuali sui campi di determinate tabelle, per ciascuno di questi campi e’ necessario: Creare un indice (da SQL Server Enterprise Manager) Popolare l’indice, cioe’ fare in modo che contenga informazioni

circa il contenuto della colonna considerata Solo dopo avere eseguito I passi precedenti, sara’ possibile

eseguire full-text query sulla colonna considerata La popolazione dell’indice puo’ essere automatica o

manuale e’ automatica ma deve essere fatta manualmente, o schedulata

E’ possibile definire un solo indice per tabella (l’indice puo’ coinvolgere piu’ colonne)

Query Full-text

T-SQL supporta due predicati per interrogare campi testuali: CONTAINS FREETEXT

Due funzioni: CONTAINSTABLE FREETEXTTABLE

Questi predicati possono essereutilizzati nella clausola WHERE degli statement di interrogazione

Contains

Alcune delle ricerche che possono essere espresse con CONTAINS possono anche essere espresse con LIKE, ma non tutte

CONTAINS e’ case insensitive, al contrario di LIKE Nel seguito, per gli esempi useremo le seguenti

strutture: DB: Northwind, Tabella: Categories, Campo:

Description(ntext(16)) DB: pubs, Tabella: titles, Camp1: notes (varchar(80)),

title (varchar(200))

Esempio di base

SELECT Description FROM Categories WHERE Description LIKE '%bean curd%'

SELECT Description FROM Categories WHERE CONTAINS(Description, ' "bean curd" ')

FRASE

Ricerche esprimibili

Una o piu’ parole e/o frasi Inflectional forms di una parola (drive, drives,

drove, droven Parole con un certo prefisso: auto* Parole o frasi pesate, in relazione all’importanza Parole o frasi simili ad altre (proximity search) Ricerche combinate con OR, AND, NOT

Ricerche per specifiche parole e/o frasi

SELECT title_id, title, notes FROM titles WHERE CONTAINS(notes, 'business')

SELECT title_id, title, notes FROM titles WHERE CONTAINS(notes, ' "common business

applications" ')

FRASE

PAROLA

Ricerche per specifiche parole e/o frasi

Si possono utilizzare anche piu’ campi nella ricerca

SELECT title_id, title, price FROM titles WHERE CONTAINS( *, ' "French gourmet" ' )

La ricerca viene effettuata in tutte le colonne indicizzate

Ricerche combinate

SELECT title, notes FROM titles WHERE CONTAINS( notes, ' "favorite recipes" OR "gourmet recipes"

' )

SELECT title_id, title, ytd_sales FROM titles WHERE CONTAINS( title, ' cooking AND NOT ("computer*" )' )

SELECT CategoryName, Description FROM Categories WHERE CONTAINS( Description, ' beers AND ales ' )

Prefissi

SELECT Description, CategoryName FROM Categories WHERE CONTAINS (Description, ' "ice*" ' )

SELECT Description, CategoryName FROM Categories WHERE CONTAINS (Description, ' "light bread*" ' )

Inflection form

SELECT Description, CategoryName FROM Categories WHERE CONTAINS (Description,

'FORMSOF(INFLECTIONAL, "dry")')

Ricerche pesate

Ogni parola o frase puo’ essere pesata Peso minimo 0 Peso massimo 1

SELECT CompanyName, ContactName, AddressFROM Customers WHERE CONTAINS(Address, 'ISABOUT ("*des*", Rue WEIGHT(0.5),

Bouchers WEIGHT(0.9) ) ' )

NON PESATO

Proximity terms

Permette di trovare sequenze di parole vicine nel testo

SELECT title, notes FROM titles WHERE CONTAINS (notes, 'user NEAR computers')

Equivalente a

SELECT title, notes FROM titles WHERE CONTAINS (notes, 'user ~ computers')

SELECT title, notes FROM titlesWHERE CONTAINS(Description, ' "wheat*" ~ "bread mix" ')

Freetext

Implementa una query “by example” Forniamo una parola o una frase e il sistema trova tutti I testi

simili Idea:

Il sistema costruisce una query dalla parola o frase specificata Usa CONTAINS per risolvere la nuova query

SELECT Description, CategoryName FROM Categories WHERE FREETEXT (description, ' "The Fulton County Grand Jury

said Friday an investigation of Atlanta recent primary election produced no evidence that any irregularities took place." ')

Combinazione predicati full-text con altri predicati

I predicati full-text possono essere combinati nella clausola WHERE con altri predicati non full-text

SELECT Description, CategoryName FROM Categories WHERE CONTAINS (Description, ' "ice*" ' )

AND Category_name = “Beverage”

Containstable e Freetexttable

CONTAINS e FREETEXT sono predicati, quindi per ogni tupla restituiscono vero o falso Utilizzati nella clausola WHERE

CONTAINSTABLE e FREETEXTTABLE sono funzioni che restituiscono tabelle Utilizzati nella clausola FROM La tabella restituita ha due attributi:

KEY: identificatore tupla (chiave) RANK: valore da 0 a 1000 che rappresenta la similarita’ del

testo con la query specificata, piu’ e’ alto, piu’ e’ simile

Containstable e Freetexttable

Per utilizzare il campo RANK, e’ necessario fare il join della tabella CONTAINSTABLE o FREETEXTTABLE con la tabella considerata nella query

SELECT C.Description, C.CategoryName, K.RANK

FROM Categories AS C, CONTAINSTABLE (Categories, Description, '("sweet and savory" NEAR sauces) OR ("sweet and savory" NEAR candies)' , 10 ) AS K

WHERE C.CategoryID = K.[KEY]

Esempio

SELECT K.RANK, CompanyName, ContactName, Address FROM Customers AS C,

CONTAINSTABLE(Customers,Address, 'ISABOUT ("des*", Rue WEIGHT(0.5), Bouchers

WEIGHT(0.9) ) ' ) AS K WHERE C.CustomerID = K.[KEY]

Ranked queries

Permettono di ritrovare I k testi che maggiormente assomigliano alla query

Si estende CONTAINSTABLE e FREETEXTTABLE con una parametro che indica quanto vale k

SELECT K.RANK, CompanyName, ContactName, Address FROM Customers AS C,

CONTAINSTABLE(Customers,Address, 'ISABOUT ("des*", Rue WEIGHT(0.5),

Bouchers WEIGHT(0.9))', 3) AS KWHERE C.CustomerID = K.[KEY]


Creare un full-text index su un campo testuale della tabella corsi

Modificare la stored procedure definita in modo che prenda in input una stringa ed esegua un’operazione full-text

provare vari operatori full text e analizzarne il risultato

provare ad eseguire la procedura con vari input

Alcuni cenni architetturali

Cosa vedremo

Indici (cenni) transazioni (cenni) sicurezza (cenni)

Strutture ausiliarie di accesso

Spesso le interrogazioni accedono solo un piccolo sottoinsieme dei dati

Per risolvere efficientemente le interrogazioni puo' essere utile allocare delle strutture ausiliarie che permettano di determinare direttamente i record che verificano una data query (senza scandire tutti i dati)

tali strutture sono chiamate indici

Strutture ausiliarie di accesso

Una ricerca puo' essere effettuata per: chiave primaria: il valore della chiave identifica un unico record

il contribuente con codice fiscale GRRGNN69R48 chiave secondaria: il valore della chiave puo' identificare piu' record

(es. i contribuenti di Genova) intervallo di valori (sia per chiave primaria che per secondaria)

(es. i contribuenti con reddito compreso tra 60 e 90 milioni) combinazioni delle precedenti

(es. i contribuenti di Genova e La Spezia con reddito compreso tra 60 e 90 milioni)

Per effettuare la ricerca in modo piu' efficiente si puo' pensare di mantenere il file ordinato secondo il valore di una chiave di ricerca

il costo di ricerca e' lineare nel numero di blocchi del file la ricerca su altri campi e' inefficiente

Indici

Idea base: associare al file dei dati una ``tabella''nella quale l'entrata i esima memorizza una coppia (ki ,ri ) dove: ki e'un valore di chiave del campo su cui l'indice e'

costruito ri e'un riferimento al record (eventualmente il solo) con

valore di chiave ki il riferimento puo' essere un indirizzo (logico o fisico) di

record o di blocco questa tabella può poi essere memorizzata in vari modi spesso viene rappresentata come albero (B-tree)

Indici

Indici

Ordinamento dei record nel file dei dati indice clusterizzato (o indice primario): indice sull'attributo

secondo i cui valori il file dei dati e' mantenuto ordinato indice non clusterizzato (o indice secondario): indice su un

attributo secondo i cui valori il file dei dati non e' mantenuto ordinato

l'uso di piu' indici secondari rende l'esecuzione delle interrogazioni piu' efficiente, ma rende piu' costosi gli aggiornamenti quando si esegue l'inserzione o la cancellazione di un

record e' necessario modificare tutti gli indici allocati sul file

Indici

Data un’interrogazione, il sistema determina tutti I possibili modi con cui la stessa può essere eseguita: senza usare indice usando indici, in vari modi (potrebbero essercene più di

uno)

ogni modalità = piano di esecuzione viene determinato il piano di esecuzione con il

minor costo (in termini di numero di accesso a disco)

si esegue il piano con il minor costo

Perché ottimizzare? Studenti(MatrS,Nome,Ind,AltreInfo) Esami(Corso,MatrS,Voto,Data)

Supponiamo di voler trovare il nome degli studenti e la data degli esami per gli studenti che hanno sostenuto BD con 30

SELECT Nome,Data FROM Studenti NATURAL JOIN Esami WHERE Corso ='BD' AND Voto =30

Perché ottimizzare?

Consideriamo un database con 2.000 studenti e 20.000 esami, di cui 500 di BD e di questi solo 50 con 30 (consideriamo solo la scansione sequenziale delle relazioni)

Se si fa il prodotto cartesiano delle due relazioni, si ottiene una relazione temporanea con 40.000.000 tuple, da queste si estraggono poi le 50 tuple desiderate (costo proporzionale a 120.000.000 accessi)

Se si selezionano i 50 esami di BD con 30 e poi si fa il join di questa relazione temporanea con Studenti si ha un costo proporzionale a 120.050

Indici in SQL Server

Clusterizzati/non clusterizzati valori unici/valori non unici colonna singola/colonna multipla coprenti/non coprenti

Clusterizzazione SQL Server Supporta sia indici clusterizzati che non

clusterizzati viene automaticamente creato un indice clusterizzato sulla

chiave primaria della tabella gli indici possono essere creati da Enterprise Manager o con

SQL poiché un indice clusterizzato stabilisce l’ordine con cui I

dati vengono memorizzati su file, una tabella può essere associata ad un solo indice clusterizzato

è possibile stabilire se I dati devono essere organizzati secondo l’ordinamento crescente o decrescente

utile su colonne utilizzate per range queries o ricerca valore

Clusterizzazione

Gli indici non clusterizzati sono utili per ricerche su singolo valore

una tabella può essere associata a più indici non clusterizzati

Unicità

Unique index: la colonna indicizzata non contiene duplicati

Non-Unique index: la colonna indicizzata può contenere duplicati

necessari vincoli UNIQUE o PRIMARY KEY per garantire l’unicità dei dati

Numero colonne

Un indice può essere costruito su una singola colonna o su una combinazione di colonne

in questo ultimo caso, vengono indicizzate le tuple ottenute dalla combinazione

Fill factor Ogni volta che si inseriscono nuove tuple, l’indice deve essere

aggiornato questo aggiornamento permette di inserire I nuovi valori in

una pagina dell’indice se la pagina è piena, è necessario dividerla in due pagine

(split) questa operazione è piuttosto costosa soluzione: lasciare parzialmente vuote le pagine in modo da

ridurre il costo in fase di inserimento fill factor: stabilisce la percentuale delle pagine che deve

essere occupata al momento della creazione dell’indice viene considerato solo al momento della creazione dell’indice

Operazioni sugli indici

Gli indici possono essere: creati ricalcolati cancellati modificati (per quanto riguarda le proprietà)

con Enterprise Manager possiamo solo: crearli modificarli cancellarlima NON possiamo ricalcolarli (non lo vediamo)

Transazioni Gli utenti interagiscono con la base di dati attraverso

programmi applicativi ai quali viene dato il nome di transazioni

Una transazione e' un insieme parzialmente ordinato di operazioni di lettura e scrittura

L'insieme di operazioni che costituiscono una transazione deve soddisfare alcune proprieta', note come proprieta' ACID: Atomicita' Consistenza Isolamento Durabilità

Transazioni

Atomicita' proprieta' tutto o niente tutte le operazioni di una transazione devono

essere trattate come una singola unita': o vengono eseguite tutte, oppure non ne viene eseguita alcuna

l'atomicita' delle transazioni e' assicurata dal sottosistema di ripristino (recovery)

Transazioni

Consistenza una transazione deve agire sulla base di dati in modo

corretto se viene eseguita su una base di dati in assenza di altre

transazioni, la transazione trasforma la base di dati da uno stato consistente (cioe' che riflette lo stato reale del mondo che la base di dati deve modellare) ad un altro stato ancora consistente

l'esecuzione di un insieme di transazioni corrette e concorrenti deve a sua volta mantenere consistente la base di dati il sottosistema di controllo della concorrenza (concurrency control) sincronizzale transazioni concorrenti in modo da assicurare esecuzioni concorrenti libere da interferenze

Transazioni

Isolamento ogni transazione deve sempre osservare una base di

dati consistente, cioe', non puo' leggere risultati intermedi di altre transazioni

la proprieta' di isolamento e' assicurata dal sottosistema di controllo della concorrenza che isola gli effetti di una transazione fino alla sua terminazione

Durabilita' (persistenza) i risultati di una transazione terminata con successo

devono essere resi permanenti nella base di dati nonostante possibili malfunzionamenti del sistema

la persistenza e' assicurata dal sottosistema di ripristino

Controllo di transazioni Facciamo riferimento al modello di transazioni piu' semplice

(transazioni flat), che prevede un solo livello di controllo a cui appartengono tutte le transazioni eseguite (e' il modello usato nei DBMS commerciali)

Tutte le istruzioni eseguite devono essere contenute tra le istruzioni BeginWork e CommitWork l'istruzione BeginWork dichiara l'inizio di una transazione flat l'istruzione CommitWork e' invocata per indicare che il sistema ha

raggiunto un nuovo stato consistente La transazione puo’:

terminare la propria esecuzione con successo (commit) e rendere definitivi i cambiamenti prodotti sulla base di dati dalle istruzioni eseguite tra BeginWork e CommitWork

oppure sara' disfatta (cioe' i suoi effetti saranno annullati) e tutti gli aggiornamenti eseguiti andranno persi (abort)

In questo caso, si dice che viene eseguito il rollback della transazione

Tipi di transazioni

Esistono vari modelli per rappresentare e gestire transazioni

il modello più semplice è quello delle flat transaction per definire una flat transaction è necessario specificare:

l'inizio di una transazione flat la fine della transazione e il risultato transazionale

La transazione puo’: terminare la propria esecuzione con successo (commit) e rendere

definitivi i cambiamenti prodotti sulla base di dati dalle istruzioni eseguite tra BeginWork e CommitWork

oppure sara' disfatta (cioe' i suoi effetti saranno annullati) e tutti gli aggiornamenti eseguiti andranno persi (abort)

In questo caso, si dice che viene eseguito il rollback della transazione

Transazioni in SQL Server SQL Server supporta flat transaction e transazioni con

savepoints (non le vediamo) Diversi modi per iniziare una transazione:

transazioni esplicite transazioni autocommit transazioni implicite

due possibili risultati transazionali: commit: tutte le modifiche effettuate dalla transazione vengono

rese permanenti rollback: nessuna modifica viene eseguita a causa di errori

il rollback viene effettuato automaticamente nel caso di gravi errori, non gestiti a livello di applicazione (es. Si rompe il disco)

Transazioni esplicite

Vengono utilizzati comandi T-SQL per specificare l’inizio e la fine della transazione

Inizio: BEGIN TRANSACTION

Fine: COMMIT TRANSACTION ROLLBACK TRANSACTION

EsempioBEGIN TRANSACTIONUPDATE ImpiegatiSET Stipendio = Stipendio * 1.2;UPDATE DipartimentiSET Dirigente = 3IF (@@ERROR = 0)

BEGINPRINT 'Transazione eseguita'COMMIT TRANSACTION

ENDELSE

BEGINPRINT 'Errore'ROLLBACK TRANSACTION

END

Transazioni autocommit

Default viene effettuato il commit di ogni statement T-SQL

che non genera errori se viene generato almeno un errore, lo statement

viene abortito in presenza di batch, l’autocommit rimane a livello

di singolo statement l’autocommit viene annullato quando si comincia in

modo esplicito una transazione viene ripristinato quando la transazione esplicita

termina

Transazioni implicite

Simili alle esplicite, ma non si deve specificare l’inizio delle transazioni

al termine di ogni transazione (COMMIT o ROLLBACK) viene automaticamente iniziata una nuova transazione

Per attivare le transazioni implicite: SET IMPLICIT_TRANSACTION ON

Per disattivarle: SET IMPLICIT_TRANSACTION OFF

Sicurezza (cenni)

Sicurezza

Utenti (Windows, SQL Server) gruppi (Windows): insieme di gruppi e/o utenti ruoli (SQL Server): gruppi di utenti, organizzati

per scopi

Livelli di sicurezza

Due fasi: autenticazione

stabilisce se un utente si può connettere ad una istanza di SQL Server

validazione permessi: dopo la validazione, vengono analizzati I permessi per

stabilire cosa l’utentepuò fare sull’istanza

Modi di autenticazione

Due possibilità (in NT e 2000): Autenticazione Windows

autenticazione basata su login e password Windows autenticazione mista

uso di account Windows e account creati direttamente in SQL Server

In Windows 9x e ME: solo autenticazione mista (ma in realtà si considerano

solo gli account SQL Server)

Modi di autenticazione

Validazione permessi Un utente autenticato ha bisogno di un account per ogni database che deve accedere ogni account specifica quali oggetti possono essere acceduti dall’utente e in che modo

per ogni statement T-SQL eseguito da un utente, si verifica che l’utente abbia I permessi per poterlo eseguire, altrimenti viene rilevato un errore

Quindi ...

Per ogni SQL Server, un insieme di account di autenticazione: login (utenti/gruppi Windows, login SQL Server) ruoli (SQL Server)

per ogni database: un insieme di utenti e ruoli ai quali è garantito l’accesso

al database per ogni accesso, si devono specificare I permessi

particolari per ogni oggetto

Database owner

dbo è un utente che implicitamente ha il permesso di eseguire ogni possibile operazione su un database

ogni membro del ruolo sysadmin che utilizza un database viene mappato nell’utente speciale dbo

ogni oggetto creato da un membro del ruolo sysadmin ha come owner dbo

Database owner

Quando un utente crea un oggetto ne diventa proprietario

ha permessi totali sull’oggetto e può delegare I permessi ad altri utenti anche tramite Enterprise Manager

Introduzione a SQL Server. Giorno I zIntroduzione allarchitettura di un DBMS zArchitettura di SQL...

Documents

Transcript of Introduzione a SQL Server. Giorno I zIntroduzione allarchitettura di un DBMS zArchitettura di SQL...