GERARCHIE RICORSIVE - SQL SERVER 2008
DISPENSE
http://dbgroup.unimo.it/SIA/GerarchieRicorsive/
L’obiettivo è quello di realizzare la tabella di navigazione tramite una query ricorsiva utilizzando SQL
SERVER 2008
Per utilizzare SQL SERVER 2008 (Express Edition) ci sono due possibilità
1) scaricarlo dalla rete ed installarlo, secondo le istruzioni riportate in “INSTALLAZIONE SQL
SERVER 2008”; funziona con tutti i sistemi operativi Microsoft
(si consiglia di seguire questa strada)
2) usare la macchina virtuale secondo le istruzioni riportate in “MACCHINA VIRTUALE CON SQL
SERVER 2008”;
SQL SERVER 2008
INSTALLAZIONE SQL SERVER 2008
oppure in alternativa
MACCHINA VIRTUALE CON SQL SERVER 2008
UTILIZZO SQL SERVER 2008
GERARCHIE RICORSIVE :TABELLA DI NAVIGAZIONE
1
SQL SERVER 2008
INSTALLAZIONE SQL SERVER 2008
oppure in alternativa
MACCHINA VIRTUALE CON SQL SERVER 2008
2
INSTALLAZIONE SQL SERVER 2008
1) download dell’installazione
Dal sito Microsoft
http://www.microsoft.com/express/Database/InstallOptions.aspx
scaricare l’installazione relativa all’opzione (terza colonna)
Database with Management Tools
3
2) Installazione: selezionare New Installation …. Come in figura
4
3) Installazione: selezionare Default Instance…. Come in figura
5
4) Installazione: lasciare le configurazioni di default Come in figura
6
5) Installazione: Selezionare WINDOWS AUTHENTICATION MODE … Come in figura
Verificare che ci sia l’utente corrente nel riquadro “Specify SQL Server Administrator”
7
6) Fine Installazione (Close)
8
MACCHINA VIRTUALE CON SQL SERVER 2008
Si utilizza Virtual BOX
A) Scaricare ed installare VIRTUAL BOX
http://www.virtualbox.org/wiki/Downloads
B) Contattare il docente per avere la macchina virtuale :
XPTest_onlyXP.vdi
C) Attivare la macchina virtuale :
XPTest_onlyXP.vdi
Nel seguito le indicazioni per attivare la macchina virtuale
9
Aprire Virtual Box
quindi ciccare NUOVA
10
11
Inserire Nome, ad esempio SIA
12
Assegnare Memoria
13
Selezionare USA DISCO FISSO ESISTENTE
quindi cliccare sulla freccia verde
14
cliccare su aggiungi
15
selezionare il file XPTest_onlyXP.vdi
16
quindi Seleziona
17
quindi avanti
18
Quindi fine
19
La macchina virtuale è stata creata
20
Selezionare IMPOSTAZIONI
21
Selezionare SISTEMA
22
ABILITARE IO APIC
23
ORA LA MACCHINA VIRTUALE E’ PRONTA PER ESSERE AVVIATA
24
25
UTILIZZO SQL SERVER 2008
Per aprire SQL SERVER : SQL SERVER Management Studio
Quindi connettersi al server
26
Si ottiene
Per creare un nuovo DB oppure fare il restore di uno esistente
27
E’ POSSIBILE FARE IL RESTORE DI UN DATABASE SQL SERVER 2000:
1) Inserire il nome del DB in “TO DATABASE”
2) Selezionare il file di Backup in “FROM DEVICE”
3) Accertarsi di aver selezionato il DB da ripristinare nella finestra centrale
28
Se non “si vede” il vostro backup (questo avviene quando il file non ha estensione .bak) selezionare ALL FILE
e quindi il backup
29
Il restore di un DB fatto con SQL SERVER 2000 non crea automaticamente i diagrammi.
I diagrammi non sono indispensabili.
Nelle prossime due pagine vengono date istruzioni su come creare i diagrammi.
SE NELLA CREAZIONE DEL DIAGRAMMA VIENE QUESTO ERRORE
CON NEW QUERY
QUI è POSSIBILE SCRIVERE QUERY SQL
Scrivere ed eseguire
ALTER AUTHORIZATION ON DATABASE::RITARDI TO [DB-PC\DB]
Sostituendo a [DB-PC\DB] il vostro nome utente (usare le []); E’ quello che si vede al punto 5)
dell’installazione, nel riquadro “Specify SQL Server Administrator”
30
Altro esempio (su un altro PC):
si noti che il nome dell’utente specificato in TO è anche disponibile come Login name nel riquadro laterale
destro.
Quindi creare il Diagramma
31
GERARCHIE RICORSIVE :
TABELLA DI NAVIGAZIONE
DISPENSE
http://dbgroup.unimo.it/SIA/GerarchieRicorsive/
L’obiettivo è quello di realizzare la tabella di navigazione tramite una query ricorsiva utilizzando SQL
SERVER 2008
PASSO 1) DB operazionale in SQL SERVER 2000
PASSO 2) DB operazionale in SQL SERVER 2008
PASSO 3) Creazione della TABELLA DI NAVIGAZIONE IN SQL SERVER 2008
PASSO 4) RIPORTARE IL CONTENUTO DELLA TABELLA DI NAVIGAZIONE NEL DATAMART
32
PASSO 1) DB operazionale in SQL SERVER 2000
DB operazionale (PAG 21 delle dispense)
E’ un DB SQL SERVER 2000 il cui BackUp è disponibile in
http://dbgroup.unimo.it/SIA/GerarchieRicorsive/EsempioRicorsione2010
http://dbgroup.unimo.it/SIA/GerarchieRicorsive/EsempioRicorsione2010.zip
http://dbgroup.unimo.it/SIA/GerarchieRicorsive/EsempioRicorsione2010.bak
Aprirlo con SQL SERVER 2000 e verificarne il contenuto
33
PASSO 2) DB operazionale in SQL SERVER 2008
Effettuare il restore in SQL SERVER 2008
Non è necessario creare il diagramma in quanto già disponibile in SQL SERVER 2000.
Visualizzare il contenuto delle table tramite “SELECT TOP 1000 ROWS” (la query viene scritta
automaticamente dal sistema)
34
PASSO 3) Creazione della TABELLA DI NAVIGAZIONE IN SQL SERVER 2008
Si eseguono tutti i passaggi descritti a pag 18 delle dispense (Calcolo della Tabella di Navigazione:SQL-99)
Dopo aver aperto un nuovo riquadro per le query (NUOVA QUERY : ATTENZIONE selezionare il
database giusto!)
Eseguo
WITH CAPO (NOME, NOMECAPO,LIVELLO) AS (
SELECT NOME, NOMECAPO, 1 AS LIVELLO
FROM IMPIEGATO
UNION
SELECT CAPO.NOME, IMPIEGATO.NOMECAPO, LIVELLO + 1
FROM CAPO, IMPIEGATO
WHERE CAPO.NOMECAPO = IMPIEGATO.NOME )
SELECT * FROM CAPO;
Msg 252, Level 16, State 1, Line 1 Recursive common table expression 'CAPO' does not contain a
top-level UNION ALL operator.
INFATTI
La precedente interrogazione può essere eseguita in SQL-SERVER 2005 con la seguente variazione
sintattica : Sostituire UNION con UNION ALL
WITH CAPO (NOME, NOMECAPO,LIVELLO) AS (
SELECT NOME, NOMECAPO, 1 AS LIVELLO
FROM IMPIEGATO
UNION ALL
SELECT CAPO.NOME, IMPIEGATO.NOMECAPO, LIVELLO + 1
FROM CAPO, IMPIEGATO
WHERE CAPO.NOMECAPO = IMPIEGATO.NOME )
SELECT * FROM CAPO;
35
36
1)Riportare la query nella vista CAPO_V1
CREATE VIEW CAPO_V1
AS
WITH CAPO (NOME, NOMECAPO,LIVELLO) AS (
SELECT NOME, NOMECAPO, 1 AS LIVELLO
FROM IMPIEGATO
UNION ALL
SELECT CAPO.NOME, IMPIEGATO.NOMECAPO, LIVELLO + 1
FROM CAPO, IMPIEGATO
WHERE CAPO.NOMECAPO = IMPIEGATO.NOME )
SELECT * FROM CAPO;
37
2)L’interrogazione ricorsiva restituisce anche tutte le tuple di IMPIEGATO, quindi anche le tuple che hanno
NULL come NOMECAPO: queste tuple non devono comparire nella tabella di navigazione e quindi vengono
eliminate
CREATE VIEW CAPO_V2 AS
SELECT * FROM CAPO_V1 WHERE NOMECAPO is not null
38
3)Per definizione l’interrogazione ricorsiva restituisce la sola chiusura transitiva di IMPIEGATO: per
aggiungere anche la chiusura riflessiva di IMPIEGATO, con un livello pari a 0, si effettua una UNION
CREATE VIEW CAPO_V3 AS
SELECT * FROM CAPO_V2
UNION
SELECT CAPO AS CAPO,CAPO AS NOMECAPO, 0 AS LIVELLO
FROM IMPIEGATO
L’errore è nella seconda parte della UNION ,nella chiusura riflessiva che si deve ottenere come
39
Quindi
CREATE VIEW CAPO_V3 AS
SELECT * FROM CAPO_V2
UNION
SELECT NOME, NOME AS NOMECAPO, 0 AS LIVELLO
FROM IMPIEGATO
40
4)Per avere corrispondenza con la tabella di Navigazione occorre effettuare la seguente rinomina dei campi:
NOME come FIGLIO e NOMECAPO come PADRE
CREATE VIEW CAPO_V4 AS
SELECT NOMECAPO AS PADRE, NOME AS FIGLIO, LIVELLO
FROM CAPO_V3
41
5)Ultima cosa è l’aggiunta della colonna Foglia: è facile verificare che
FIGLIO → FOGLIA (infatti FOGLIA è true se il FIGLIO non è a sua volta PADRE, ovvero se non compare
nella colonna PADRE). Per semplicità effettuiamo il calcolo del valore FOGLIA tramite una nuova vista
CREATE VIEW FOGLIA(FIGLIO,FOGLIA) AS
SELECT FIGLIO, 'TRUE' AS FOGLIA FROM CAPO_V4
WHERE FIGLIO NOT IN (SELECT PADRE FROM CAPO_V4)
UNION
SELECT FIGLIO, ’FALSE' AS FOGLIA FROM CAPO_V4
WHERE FIGLIO IN (SELECT PADRE FROM CAPO_V4)
42
6)La tabella di navigazione si ottiene con un semplice join tra CAPO_V4 e FOGLIA.
Essendo un join, per comodità creiamo questa vista tramite interfaccia grafica
43
IL join deve essere fatto tra FIGLIO e FOGLIA
44
Si esegue per verificare il contenuto della vista
e quindi si salva la vista chiamandola TABELLA_DI_NAVIGAZIONE
45
PASSO 4) RIPORTARE IL CONTENUTO DELLA TABELLA DI NAVIGAZIONE NEL DATAMART
IL DATAMART è UN DB GESTITO IN SQL SERVER 2000:
SI DEVE QUINDI
FARE UN TRASFERIMENTO DI DATI
DA SQL SERVER 2008 A SQL SERVER 2000
Il modo più semplice è usare gli script
IN SQL SERVER 2008 il contenuto della vista TABELLA_DI_NAVIGAZIONE viene riportato in una tabella
che chiameremo TABLE_TABELLA_DI_NAVIGAZIONE
A) Si crea la tabella con il seguente create table: si noti che a destra vedo le colonne della vista
TABELLA_DI_NAVIGAZIONE in modo da usare nella create table la stessa tipologia di dati
46
B) Inserisco e verifico
Generazione script per TABLE_TABELLA_DI_NAVIGAZIONE
Seleziono Generate Script
47
48
49
IMPORTANTE : SELEZIONARE SCRIPT DATA = TRUE
50
51
52
53
54
55
Con CLOSE viene quindi aperta una nuova finestra di query con all’interno lo script
56
Salvare questo script (dal menu File o usando l’icona)
57
Ora occorre eseguire lo script nel datamart di SQL SERVER 2000
Si apre SQL QUERY ANALIZER
Si apre quindi il file di script
58
SI ottiene
59
IMPORTANTE : SI CANCELLA LA PRIMA RIGA CON
USE EsempioRicorsione
E si esegue lo script
quindi si verifica la creazione della tabella
60
61
