Download - Esercizi Svolti Datawarehouse

Transcript

ESERCIZI – Data Warehousing 1 ESERCIZI - PROGETTAZIONE DI UN DW ................................................................................................... 2

1.1 Esercizio: Spedizione ............................................................................................................................. 2

1.1.1 Soluzione .......................................................................................................................................... 4

1.1.2 Variante 1 ....................................................................................................................................... 11

1.1.3 Variante 2 (misura derivata) .......................................................................................................... 11

1.1.4 Variante 3: (schema transazionale) ................................................................................................ 12

1.1.5 Soluzioni possibili per la misura NUMERO (conteggio eventi primari) ........................................... 14

1.1.6 Soluzioni possibili per la misura COSTO (misura con AVG in schema transazionale) .................. 15

1.2 Esercizio: Dettaglio Ordine .................................................................................................................. 19

1.2.1 Soluzione ........................................................................................................................................ 19

1.3 Esercizio: Biglietto ................................................................................................................................. 22

1.3.1 Soluzione ........................................................................................................................................ 24

1.3.2 Variante .......................................................................................................................................... 26

1.3.3 Variante (dimensione derivante da discretizzazione) .................................................................... 27

1.4 Esercizio : Esame ................................................................................................................................. 29

1.4.1 Soluzione ........................................................................................................................................ 30

1.5 Esercizio: Vendita ................................................................................................................................. 32

1.5.1 Soluzione ........................................................................................................................................ 32

1.6 Esercizio (19/12/2012) .......................................................................................................................... 34

1.7 Esercizio (14/01/2013 ) ......................................................................................................................... 35

1.8 Esercizio (16/04/2013 ) ......................................................................................................................... 36

1.9 Esercizio (10/09/2013 ) ......................................................................................................................... 37

Sistemi Informativi Avanzati Anno Accademico 2013/2014 Prof. Domenico Beneventano

1 ESERCIZI - Progettazione di un DW Per alcuni esercizi viene anche riportato il codice SQL per provare le soluzioni date. La Fact Table e le dimension table sono definite come viste nel DBO dato (architettura ad un livello)

1.1 Esercizio: Spedizione Consideriamo un DBO con il seguente schema E/R ed il corrispondente schema relazionale (nello schema relazionale ci possono essere vincoli di integrità aggiuntivi):

CITTA(CITTA,REGIONE,STATO) DF: REGIONEà STATO DATA(DATA,MESE,ANNO) DF: MESE à ANNO ORDINE(ORDINE,CLIENTE:CLIENTE,DATA:DATA) REPARTO(REPARTO,MAGAZZINO:MAGAZZINO) MAGAZZINO(MAGAZZINO,CITTA:CITTA) CLIENTE(CLIENTE,CITTA:CITTA) SPEDIZIONE(NRIGA,ORDINE:ORDINE, PRODOTTO, REPARTO:REPARTO DATASPEDIZIONE:DATA, COSTO) AK : { ORDINE,PRODOTTO, REPARTO,DATASPEDIZIONE }

Viene richiesto di:

A) Progettazione concettuale : Progettazione dello schema di fatto SPEDIZIONE con dimensioni {PRODOTTO,MAGAZZINO,CLIENTE,DATASPED} e misure COSTO: è il costo medio di DETTAGLIO.COSTO

NUMERO: è il numero complessivo di spedizioni

NUMERO_ORDINI: è il numero complessivo di ordini

B) Progettazione logica : Progettazione dello STAR SCHEMA e SNOWFLAKE SCHEMA

C) Alimentazione : Scrivere in SQL l’alimentazione della fact-table.

ORDINE

DATA (0,N)

(1,1)

DETTAGLIO (1,N)(1,1)

NRIGA

(1,N)

(1,1)

REPARTO

(1,N)

(1,1)

MAGAZZINO

DEL

(1,N)

(1,1)

CLIENTE

CITTAIN (0,N)(1,1)

INDIRIZZO

(0,N)

(1,1)

DATA

REPARTO

MAGAZZINO

COSTO

MESE

ANNO

CITTA

STATO

PRODOTTO

(0,N)

(1,1)

DATASPEDIZIONE

CLIENTE

REGIONE

ORDINE

DATAORDINE

SPEDIZIONE

Esempio di istanza del DBO e corrispondente istanza del fatto (eventi primari)

Eventi Primari del Fatto SPEDIZIONE con dimensioni

PRODOTTO, MAGAZZINO, CLIENTE, DATASPED

1.1.1 Soluzione Progettazione Concettuale

Nella progettazione concettuale occorre riportare uno schema di fatto con tutte le dimensioni richieste e, per ciascuna dimensione, con tutta la gerarchia derivante dallo schema del DBO.

Albero degli attributi iniziale

INNESTO su REPARTO ed ORDINE

Anche DATA dell’ORDINE viene POTATA

Si aggiungono le seguenti dipendenze funzionali, non presenti nello schema ER:

REGIONE à STATO

MESE à ANNO

Si ottiene

Viene POTATA anche NRIGA

Scelta delle dimensioni

Viene quindi creato lo schema di fatto, considerando una condivisione su CITTA.

Fatto: SPEDIZIONE(NRIGA,ORDINE,PRODOTTO,REPARTO, DATASPEDIZIONE,COSTO) AK : { ORDINE,PRODOTTO, REPARTO,DATASPEDIZIONE }

Dimensioni: {PRODOTTO,MAGAZZINO,CLIENTE,DATASPED}

Granularità: Temporale Dipendenze funzionale tra le dimensioni: Nessuna Misure normali

NUMERO= COUNT(*), additiva NUMERO_ORDINI = count(distinct ORDINE), additiva rispetto a CLIENTE, in quanto ORDINE à CLIENTE Quindi NUMERO_ORDINI è aggregabile solo rispetto a CLIENTE, pertanto l’insieme delle dimensioni rispetto alle quali non è aggregabile è NA: {PRODOTTO,MAGAZZINO,DATASPED}

Misure calcolate COSTO = COSTO_SUM/COSTO_COUNT dove COSTO_SUM = SUM(COSTO), additiva COSTO_COUNT = COUNT(COSTO), additiva Fact Table FACT_TABLE(PRODOTTO,MAGAZZINO,CLIENTE,DATASPED,

NUMERO,COSTO_SUM,COSTO_COUNT, NUMERO_ORDINI) In questa fase viene anche indicata una Fact Table con struttura semplificata (senza riferimenti alle Dimension Table) per riassumere quali sono le dimensioni del fatto e le misure che si devono considerare. Riassumere quali sono tutte le misure è utile anche per controllare se tra tutte queste misure ce ne sono alcune equivalenti. Ad esempio, in questo caso sono state introdotte e riportate nella Fact Table due misure

NUMERO= COUNT(*), additiva COSTO_COUNT = COUNT(COSTO), additiva Sono queste due misure equivalenti ? Se la risposta è positiva, nella Fact Table verrà inserita una sola misura.

CLIENTE

SPEDIZIONENUMERO(C) COSTO (AVG)NUMERO_ORDINI

CITTAREGIONE

DATASPED

MAGAZZINO

STATO

MESE

PRODOTTOANNO

Progettazione Logica

Nella Progettazione logica viene richiesto di delineare sia lo STAR SCHEMA che lo SNOWFLAKE SCHEMA; La Fact Table in questi due tipologie di schemi coincide (mentre quelle che cambiano sono ovviamente le Dimension Table) quindi possiamo riportare la Fact Table una sola volta.

La Fact Table ha la seguente struttura

FACT_TABLE(PRODOTTO,MAGAZZINO,CLIENTE,DATASPED, NUMERO,COSTO_SUM,COSTO_COUNT, NUMERO_ORDINI)

Come detto in precedenza, dobbiamo chiederci se le due misure

NUMERO= COUNT(*), additiva COSTO_COUNT = COUNT(COSTO), additiva

sono equivalenti, ovvero se il loro valore coincide. Quindi si dovrebbe controllare se COUNT(*) e COUNT(COSTO) restituiscono lo stesso valore: la risposta è negativa se nel DBO il campo COSTO può assumere dei valori NULL, in quanto per definizione un valore NULL viene conteggiato da COUNT(*) ma non viene conteggiato da COUNT(COSTO). Nei nostri esercizi si suppone il caso generale della presenza di NULL pertanto nella Fact Table verranno sempre tenute entrambe le misure.

La Fact Table sarà quindi

FACT_TABLE(CLIENTE:dtCLIENTE,MAGAZZINO:dtMAGAZZINO,DATASPED:dtDATA,PRODOTTO, NUMERO,COSTO_SUM,COSTO_COUNT, NUMERO_ORDINI)

Si noti che la dimensione PRODOTTO è degenere, quindi non viene inserita la relativa Dimension Table.

Star Schema dtDATA(DATA,MESE,ANNO)

dtCLIENTE(CLIENTE,CITTA,REGIONE,STATO)

dtMAGAZZINO(MAGAZZINO,CITTA,REGIONE,STATO)

SnowFlake Schema

dtDATA(DATA,MESE:dtMESE)

dtMESE(MESE,ANNO)

dtCLIENTE(CLIENTE,CITTA:dtCITTA)

dtMAGAZZINO(MAGAZZINO,CITTA:dtCITTA)

dtCITTA(CITTA,REGIONE:dtREGIONE)

dtREGIONE(REGIONE,STATO)

Alimentazione

La query di alimentazione della fact table è costituita come segue: FROM: oltre alla tabella SPEDIZIONE, si deve considerare la tabella REPARTO (che contiene MAGAZZINO) e la tabella ORDINE (che contiene CLIENTE). GROUP BY: banale, contiene le dimensioni del fatto SELECT: oltre alle dimensioni del fatto si riporta il calcolo delle misure sulla base di quanto specificato nel glossario CREATE VIEW FACT_TABLE AS SELECT

SPEDIZIONE.PRODOTTO, REPARTO.MAGAZZINO, ORDINE.CLIENTE, SPEDIZIONE.DATASPEDIZIONE AS DATASPED, COSTO_SUM =SUM(COSTO), COSTO_COUNT =COUNT(COSTO), NUMERO =COUNT(*) NUMERO_ORDINI =COUNT(DISTINCT ORDINE.ORDINE)

FROM SPEDIZIONE NATURAL JOIN REPARTO NATURAL JOIN

ORDINE GROUP BY

SPEDIZIONE.PRODOTTO, SPEDIZIONE.DATASPEDIZIONE, ORDINE.CLIENTE, REPARTO.MAGAZZINO

Per provare la query, dovendola eseguire su un DBMS che non supporta il NATURAL JOIN:

FROM SPEDIZIONE JOIN REPARTO ON REPARTO.REPARTO = SPEDIZIONE.REPARTO JOIN

ORDINE ON ORDINE.ORDINE = SPEDIZIONE.ORDINE

1.1.2 Variante 1 Viene richiesto di discutere come cambia l’aggregabilità della misura NUMERO_ORDINI considerando come dimensione MESE_ORDINE invece di DATASPED.

Dimensioni: {PRODOTTO,MAGAZZINO,CLIENTE,MESE_ORDINE}

Le FD che riguardano ORDINE sono (ricordiamo che NUMERO_ORDINI=SELECT (DISTINCT ORDINE))

ORDINE à CLIENTE

ORDINE à MESE_ORDINE

Quindi ora NUMERO_ORDINI è aggregabile sia rispetto a CLIENTE che MESE_ORDINE, pertanto

NA: {PRODOTTO,MAGAZZINO }

1.1.3 Variante 2 (misura derivata) Viene richiesto di discutere come cambia l’aggregabilità della misura NUMERO_ORDINI considerando come dimensione ORDINE invece di CLIENTE.

Dimensioni: {PRODOTTO,MAGAZZINO, ORDINE,DATASPED }

Rispetto all’esempio precedente, si usa come dimensione ORDINE al posto di CLIENTE, pertanto il nuovo fatto è ad un maggiore livello di dettaglio. L’aspetto principale riguarda la misura NUMERO_ORDINI=COUNT(DISTINCT ORDINE): grazie alla presenza della dimensione ORDINE, adesso NUMERO_ORDINI è una misura derivata da una dimensione, quindi non verrà inserita nella fact table!

Lo schema di fatto è simile a quello precedente: l’aggiunta dell’attributo dimensionale ORDINE fa si che DATA sia ora un attributo dimensionale in comune tra la dimensione ORDINE (infatti un ORDINE ha una DATA) e la dimensione DATASPED. Si noti che per questo attributo dimensionale in comune si utilizza il termine più generale DATA e non DATA_SPED (altrimenti verrebbe indicato che un ORDINE ha una DATA_SPED, cosa non vera). Però DATA_SPED deve restare come nome della dimensione, pertanto si mette tale nome sul arco (come avveniva ad esempio nel caso del Fatto Chiamata discusso sulle dispense).

Fatto: SPEDIZIONE(NRIGA,ORDINE,PRODOTTO,REPARTO, DATASPEDIZIONE,COSTO) AK : { ORDINE,PRODOTTO, REPARTO,DATASPEDIZIONE }

Dimensioni: {PRODOTTO,MAGAZZINO,ORDINE,DATASPED}

Granularità: Temporale

ORDINE

CITTA

REGIONE

DATASPED

STATO

MESE

PRODOTTO ANNOCLIENTE

SPEDIZIONENUMERO

MAGAZZINO

DATA

Dipendenze funzionale tra le dimensioni: Nessuna

Misure normali NUMERO= COUNT(*), additiva

Misure calcolate COSTOMEDIO = COSTO_SUM/COSTO_COUNT dove

COSTO_SUM = SUM(COSTO), additiva

COSTO_COUNT = COUNT(COSTO), additiva

Misure derivate NUMERO_ORDINI=COUNT(DISTINCT ORDINE)

Fact Table FACT_TABLE(PRODOTTO,MAGAZZINO,ORDINE,DATASPED, NUMERO,COSTO_SUM,COSTO_COUNT)

Ora la misura derivata NUMERO_ORDINI è semple calcolabile in base alla sua definizione.

1.1.4 Variante 3: (schema transazionale) Come terza e ultima variante si considera il fatto SPEDIZIONE con dimensioni

{PRODOTTO,REPARTO,ORDINE,DATASPED}

Quindi si usa come dimensione REPARTO al posto di MAGAZZINO, pertanto – come nella precedente variante - il nuovo fatto è ad un maggiore livello di dettaglio , cioè il nuovo fatto ha una granularità più fine. La caratteristica fondamentale di questo caso è che le dimensioni coincidono (e quindi contengono) una chiave del fatto, ovvero le dimensioni coincidono con la chiave alternativa

AK : { ORDINE,PRODOTTO, REPARTO,DATASPEDIZIONE }

Quindi il nuovo fatto SPEDIZIONE è transazionale; gli eventi primari del nuovo fatto SPEDIZIONE saranno in numero pari al numero di istanze (la cardinalità) della relazione SPEDIZIONE del DBO:

Istanza di SPEDIZIONE Eventi Primari del Fatto SPEDIZIONE

Nel seguito viene svolto (interamente) questo nuovo caso, mettendo in evidenza come per uno schema di fatto transazionale sia differente la query di alimentazione della fact table ed il calcolo delle misure, in particolare quelle aggregate tramite media.

Progettazione Concettuale

Fatto SPEDIZIONE(NRIGA,ORDINE,PRODOTTO,REPARTO, DATASPEDIZIONE,COSTO) AK : { ORDINE,PRODOTTO, REPARTO,DATASPEDIZIONE } Dimensioni = {PRODOTTO,REPARTO,ORDINE,DATASPED}

Granularità: Transazionale Dipendenze funzionale tra le dimensioni: Nessuna

Progettazione Logica FACT_TABLE(ORDINE:dtORDINE,REPARTO: dtREPARTO,DATASPED:dtDATA,PRODOTTO, <verrà completato dopo la discussione sulle misure>)

Star Schema

dtDATA(DATA,MESE,ANNO)

dtORDINE(ORDINE,DATA,MESE,ANNO,CLIENTE,CITTA,REGIONE,STATO)

dtREPARTO(REPARTO, MAGAZZINO,CITTA,REGIONE,STATO)

SnowFlake Schema

dtDATA(DATA,MESE:dtMESE)

dtMESE(MESE,ANNO)

dtORDINE(ORDINE,DATA:dtDATA,CLIENTE:dtCLIENTE)

dtCLIENTE(CLIENTE,CITTA:dtCITTA)

dtCITTA(CITTA,REGIONE:dtREGIONE)

dtREGIONE(REGIONE,STATO)

dtREPARTO(REPARTO,MAGAZZINO:dtMAGAZZINO)

dtMAGAZZINO(MAGAZZINO,CITTA:dtCITTA)

ORDINE

CITTA

REGIONE

DATASPED

STATO

MESE

PRODOTTO ANNOCLIENTE

SPEDIZIONENUMERO

MAGAZZINO

DATA

REPARTO

1.1.5 Soluzioni possibili per la misura NUMERO (conteggio eventi primari) La misura NUMERO nel caso di schema transazionale corrisponde a quanto discusso nel caso di “Schemi di fatto vuoti”, ovvero NUMERO è una misura che serve per il conteggio degli eventi primari. Come era stato già fatto notare e come evidenzieremo nell’esempio, il nome “Schemi di fatto vuoto” deriva dal fatto che in questo caso una possibile soluzione è quella di non inserire esplicitamente nessuna misura nello schema di fatto per il conteggio degli eventi primari, ovvero non memorizzare nessun valore : questo concetto di non inserire nessuna misura e non memorizzare nessun valore comporta che nella fact table corrispondente non ci sia nessun attributo per questa misura. D’altra parte, lo schema di fatto e quindi la fact table possono contenere altre misure (come capita in questo caso per la misura COSTO)

Prima Soluzione : misura conteggio per il conteggio degli eventi primari

Misure normali

(COUNT) à per indicare appunto una misura vuota per il conteggio degli eventi primari Fact Table FACT_TABLE(PRODOTTO,REPARTO,ORDINE,DATASPED) Alimentazione

CREATE VIEW FACT_TABLE AS SELECT PRODOTTO, REPARTO,ORDINE, DATASPEDIZIONE AS DATASPED FROM SPEDIZIONE

Seconda Soluzione : misura booleana per il conteggio degli eventi primari

Un altro modo di rappresentare il verificarsi di un evento è attraverso una misura di tipo booleana, additiva: normalmente quando si tratta di effettuare un semplice conteggio degli eventi primari questa misura assume il solo valore 1 (evento che si è verificato) e non il valore 0 (nello schema di fatto non si rappresentano gli eventi che non si verificano). Vedremo comunque in un prossimo esercizio che a volte è necessario contare solo determinati eventi (ad esempio, nel caso dell’esercizio sul fatto BIGLIETTO, si vogliono rappresentare tutti i biglietti ma si vogliono contare solo quelli che hanno fatto anche il check-in dei bagagli); in questo caso la misura di conteggio booleana assume entrambi i valori 1 e 0.

Misure normali NUMERO = 1, additiva

Fact Table FACT_TABLE (PRODOTTO,REPARTO,ORDINE,DATASPED,

NUMERO)

NUMERO è una misura normale : tale misura ha un valore per ogni evento primario – in questo caso costante pari a 1 – e un operatore di aggregazione per determinarne il valore per gli eventi secondari

Se lo riportassi nella fact table, poi per le aggregazioni dovrei utilizzare ovviamente

NUMERO = SUM(NUMERO)

Ma essendo NUMERO=1, questo equivale a fare

NUMERO = SUM(1)

Pertanto non è necessario riportare NUMERO come attributo della fact table.

Come esempio di utilizzo di questa misura, calcoliamo il pattern {MAGAZZINO_CITTA,CLIENTE_CITTA} nello SNOW-FLAKE schema:

SELECT M.CITTA AS MAGAZZINO_CITTA,C.CITTA AS CLIENTE_CITTA, NUMERO = SUM(1)

FROM FACT_TABLE F JOIN dtREPARTO R ON R.REPARTO = F.REPARTO JOIN dtMAGAZZINO M ON R.MAGAZZINO = M.MAGAZZINO JOIN dtORDINE O ON F.ORDINE = O.ORDINE JOIN dtCLIENTE C ON O.CLIENTE = C.CLIENTE GROUP BY M.CITTA, C.CITTA

Si può verificare che il risultato è corretto, confrontandolo con quanto ottenuto negli schemi di fatto precedenti.

In definitiva, abbiamo due soluzioni simili, in entrambi i casi non si riporta niente nella fact table per la misura numero (è questo giustifica il termine Schema di Fatto vuoto utilizzato in questi casi). Quello che cambia è l’operatore di aggregazione, nel senso che possiamo usare due operazioni equivalenti

NUMERO = COUNT(*) oppure

NUMERO = SUM(1)

1.1.6 Soluzioni possibili per la misura COSTO (misura con AVG in schema transazionale) In uno schema transazionale, per una misura quale COSTO che deve essere aggregata tramite media ci sono due soluzioni possibili

1) misura normale con operatore di aggregazione AVG 2) misura calcolata COSTO = COSTO_SUM/COSTO_COUNT dove

COSTO_SUM = COSTO, additiva COSTO_COUNT = COUNT(COSTO), additiva La soluzione seguita normalmente è la seconda; nel seguito comunque presentiamo entrambe le soluzioni, verificando che sono equivalenti.

Prima Soluzione : misura normale con operatore di aggregazione AVG

Fact Table : FACT_TABLE(PRODOTTO,REPARTO,ORDINE,DATASPED,COSTO)

Alimentazione

CREATE VIEW FACT_TABLE AS SELECT PRODOTTO, REPARTO,ORDINE, DATASPEDIZIONE, COSTO AS DATASPED FROM SPEDIZIONE

Come esempio di utilizzo di questa misura, calcoliamo il pattern {MAGAZZINO_CITTA,CLIENTE_CITTA} e tutti i suoi sub-pattern nello SNOW-FLAKE schema:

SELECT M.CITTA AS MAGAZZINO_CITTA, C.CITTA AS CLIENTE_CITTA,

COSTO = AVG(COSTO), FROM <come prima>

Si può verificare che il risultato è corretto, confrontandolo con quanto ottenuto negli schemi di fatto precedenti.

Seconda Soluzione : misura calcolata COSTO = COSTO_SUM/COSTO_COUNT In questo caso abbiamo due misure normali

COSTO_SUM = COSTO, additiva COSTO_COUNT = COUNT(COSTO), additiva Che verranno poi usate per il calcolo della misura calcolata COSTO.

In questo caso, essendo lo schema transazionale, gli eventi primari verranno calcolati senza raggruppare e quindi non ha ovviamente senso definire una misura attraverso un operatore di aggregazione: in COSTO_COUNT = COUNT(COSTO) è stato indicato COUNT corsivato proprio per indicare che concettualmente devo fare un conteggio ma questo conteggio è unitario, cioè COSTO_COUNT=1. Di conseguenza questa misura COSTO_COUNT coincide con la misura NUMERO e pertanto può essere definita e calcolata come già discusso in precedenza: COUNT(*) oppure SUM(1).

Fact Table FACT_TABLE(PRODOTTO,REPARTO,ORDINE,DATASPED,COSTO_SUM)

Alimentazione CREATE VIEW FACT_TABLE AS SELECT PRODOTTO, REPARTO,ORDINE, DATASPEDIZIONE AS DATASPED, COSTO FROM SPEDIZIONE

Come esempio si calcola il pattern {MAGAZZINO_CITTA,CLIENTE_CITTA} e tutti i suoi sub-pattern nello SNOW-FLAKE schema utilizzando per COSTO_COUNT le due possibilità SELECT

M.CITTA AS MAGAZZINO_CITTA, C.CITTA AS CLIENTE_CITTA,

COSTO = SUM(COSTO_SUM)/ COUNT(*) oppure COSTO = SUM(COSTO_SUM)/ SUM(1) FROM <come prima> Si può verificare che il risultato è corretto, confrontandolo con quanto ottenuto negli schemi di fatto precedenti.

Le due soluzioni per il calcolo di COSTO come valore medio coincidono nell’ipotesi che nello schema operazionale il valore di COSTO non sia NULL; infatti un valore NULL di COSTO non viene considerato, cioè non partecipa al calcolo, nella prima soluzione in quanto, per definizione la funzione AVG viene calcolata sui valori non nulli. Nella seconda soluzione invece il valore NULL di COSTO viene considerato in quanto non viene sommato (per definizione anche la funzione SUM sui valori non nulli) ma viene conteggiato, sia attraverso COUNT(*), in quanto per definizione COUNT(*) conta anche i valori nulli, sia attraverso SUM(1) .

Modifichiamo la tabella SPEDIZIONE con alcuni NULL su COSTO, quindi verifichiamo la differenza per un semplice pattern {ORDINE}

DBO: tabella SPEDIZIONE COSTO calcolato come COSTO=AVG(COSTO)

COSTO calcolato come COSTO=SUM(COSTO_SUM)/SUM(1)

Nel caso di COSTO come misura calcolata, per considerare che COSTO può essere nullo e quindi non deve partecipare alla media, non possiamo usare più come COSTO_COUNT l’espressione SUM(1) (stesso discorso per l’espressione COUNT(*)) ma dobbiamo conteggiare in maniera diversa i valori NULL, pertanto si inserisce una misura normale COSTO_COUNT che sarà 1 se COSTO è non NULL, 0 altrimenti. Fact Table FACT_TABLE(PRODOTTO,REPARTO,ORDINE,DATASPED,COSTO_SUM,COSTO_COUNT)

Alimentazione

CREATE VIEW FACT_TABLE AS SELECT PRODOTTO, REPARTO,ORDINE, DATASPEDIZIONE AS DATASPED, COSTO_SUM=COSTO, COSTO_COUNT= CASE WHEN COSTO IS NULL THEN 0 ELSE 1 END FROM SPEDIZIONE

Verifichiamo nell’esempio della pagina precedente:

DBO: tabella SPEDIZIONE COSTO calcolato come COSTO=AVG(COSTO)

COSTO calcolato come COSTO=SUM(COSTO_SUM)/ SUM(COSTO_COUNT)

Codice SQL

Creazione del DBO (verificare, può non essere completamente aggiornato rispetto all’esempio)

CREATE TABLE ORDINE(ORDINE INT,CLIENTE INT, DATA INT); CREATE TABLE REPARTO(REPARTO INT,MAGAZZINO INT); CREATE TABLE MAGAZZINO(MAGAZZINO INT,CITTA INT); CREATE TABLE CLIENTE( CLIENTE INT,CITTA INT); CREATE TABLE CITTA(CITTA INT,REGIONE INT,STATO INT); CREATE TABLE DATA( DATA INT, MESE INT, ANNO INT); CREATE TABLE SPEDIZIONE(NRIGA INT,ORDINE INT,PRODOTTO INT,REPARTO INT,DATASPEDIZIONE INT,COSTO FLOAT); INSERT CITTA ( CITTA,REGIONE,STATO ) SELECT 10,10,10 INSERT CITTA ( CITTA,REGIONE,STATO ) SELECT 20,20,10 INSERT CITTA ( CITTA,REGIONE,STATO ) SELECT 30,20,10 INSERT MAGAZZINO ( MAGAZZINO,CITTA ) SELECT 10,10 INSERT MAGAZZINO ( MAGAZZINO,CITTA ) SELECT 20,30 INSERT CLIENTE ( CLIENTE,CITTA ) SELECT 10,10 INSERT CLIENTE ( CLIENTE,CITTA ) SELECT 20,20 INSERT REPARTO ( REPARTO,MAGAZZINO ) SELECT 10,10 INSERT REPARTO ( REPARTO,MAGAZZINO ) SELECT 20,20 INSERT REPARTO ( REPARTO,MAGAZZINO ) SELECT 30,20 INSERT ORDINE ( ORDINE,CLIENTE,DATA ) SELECT 1,10,30 INSERT ORDINE ( ORDINE,CLIENTE,DATA ) SELECT 2,20,30 INSERT ORDINE ( ORDINE,CLIENTE,DATA ) SELECT 3,20,30 INSERT SPEDIZIONE ( NRIGA,ORDINE,PRODOTTO,REPARTO,DATASPEDIZIONE,COSTO ) SELECT 1,1,1000,10,10,10 INSERT SPEDIZIONE ( NRIGA,ORDINE,PRODOTTO,REPARTO,DATASPEDIZIONE,COSTO ) SELECT 2,1,1000,10,10,21 INSERT SPEDIZIONE ( NRIGA,ORDINE,PRODOTTO,REPARTO,DATASPEDIZIONE,COSTO ) SELECT 3,1,1000,10,10,11 INSERT SPEDIZIONE ( NRIGA,ORDINE,PRODOTTO,REPARTO,DATASPEDIZIONE,COSTO ) SELECT 4,1,3000,10,10,11 INSERT SPEDIZIONE ( NRIGA,ORDINE,PRODOTTO,REPARTO,DATASPEDIZIONE,COSTO ) SELECT 1,2,3000,20,30,11 INSERT SPEDIZIONE ( NRIGA,ORDINE,PRODOTTO,REPARTO,DATASPEDIZIONE,COSTO ) SELECT 2,2,3000,30,30,11 INSERT SPEDIZIONE ( NRIGA,ORDINE,PRODOTTO,REPARTO,DATASPEDIZIONE,COSTO ) SELECT 3,2,1000,20,10,10 INSERT SPEDIZIONE ( NRIGA,ORDINE,PRODOTTO,REPARTO,DATASPEDIZIONE,COSTO ) SELECT 4,2,1000,30,10,11 INSERT SPEDIZIONE ( NRIGA,ORDINE,PRODOTTO,REPARTO,DATASPEDIZIONE,COSTO ) SELECT 5,2,1000,20,10,11 INSERT SPEDIZIONE ( NRIGA,ORDINE,PRODOTTO,REPARTO,DATASPEDIZIONE,COSTO ) SELECT 1,3,3000,20,10,11 INSERT SPEDIZIONE ( NRIGA,ORDINE,PRODOTTO,REPARTO,DATASPEDIZIONE,COSTO ) SELECT 2,3,3000,30,10,21 INSERT SPEDIZIONE ( NRIGA,ORDINE,PRODOTTO,REPARTO,DATASPEDIZIONE,COSTO ) SELECT 3,3,3000,20,30,11 INSERT DATA ( DATA,MESE,ANNO ) SELECT 10,10,10 INSERT DATA ( DATA,MESE,ANNO ) SELECT 20,20,10 INSERT DATA ( DATA,MESE,ANNO ) SELECT 30,20,10

1.2 Esercizio: Dettaglio Ordine Consideriamo un DBO con il seguente schema E/R ed il corrispondente schema relazionale

CITTA(CITTA,REGIONE,STATO) DF: REGIONEà STATO DATA(DATA,MESE,ANNO) DF: MESE à ANNO ORDINE(ORDINE,CLIENTE:CLIENTE,DATA:DATA) PRODOTTO(PRODOTTO,AZIENDA:AZIENDA) AZIENDA(AZIENDA,CITTA:CITTA) CLIENTE(CLIENTE,CITTA:CITTA, PREFER:PRODOTTO) DETTAGLIO(NRIGA,ORDINE:ORDINE, PRODOTTO:PRODOTTO, DATASPEDIZIONE:DATA, COSTO)

Viene richiesto di

A) Progettazione concettuale : Progettazione dello schema di fatto DETTAGLIO con dimensioni {PRODOTTO,ORDINE,DATASPED} e misure {COSTO,NUMERO} dove

COSTO: è il costo medio NUMERO: è il numero complessivo di spedizioni

B) Progettazione logica : Progettazione dello STAR SCHEMA e SNOWFLAKE SCHEMA C) Alimentazione : Scrivere in SQL l’alimentazione della fact-table.

1.2.1 Soluzione Lo schema di fatto è simile a quello del fatto Spedizione – seconda variante: l’unica aggiunta è l’associazione PREFER (un CLIENTE ha un PRODOTTO preferito) che collega CLIENTE a PRODOTTO. Questo comporta che l’attributo dimensionale PRODOTTO sia ora comune alle due dimensioni ORDINE e PRODOTTO. In questo caso - a differenza di quanto discusso con DATA e DATA_SPED - non essendoci ambiguità possiamo usare lo stesso nome PRODOTTO sia per la dimensione che per l’attributo comune.

Con la nuova associazione PREFER, CITTA è raggiungibile anche con il percorso CLIENTE_PRODOTTO.

ORDINE

DATA (0,N)

(1,1)

DETTAGLIO (1,N)(1,1)

NRIGA

(0,N)

(1,1)

PRODOTTO

(1,N)

(1,1)

AZIENDA

DEL

(1,N)

(1,1)

CLIENTE

CITTAIN (0,N)(1,1)

INDIRIZZO

(0,N)

(1,1)

DATA

PRODOTTO

AZIENDA

COSTO

MESE

ANNO

CITTA

STATO

(0,N)

(1,1)

DATASPEDIZIONE

PREFER

REGIONE

ORDINE

DATAORDINE

(1,1)(0,N)

CLIENTE

ORDINE

CITTA

REGIONE

DATASPED

STATO

MESE

PRODOTTO

ANNOCLIENTE

DETTAGLIONUMERO

AZIENDA

DATA

Fatto: DETTAGLIO(NRIGA,ORDINE, PRODOTTO, DATASPEDIZIONE,COSTO) Dimensioni: {PRODOTTO,ORDINE,DATASPED}

Granularità: Temporale Dipendenze funzionale tra le dimensioni: Nessuna Misure normali

NUMERO= COUNT(*), additiva Misure calcolate COSTOMEDIO = COSTO_SUM/COSTO_COUNT dove COSTO_SUM = SUM(COSTO), additiva COSTO_COUNT = COUNT(COSTO), additiva Fact Table FACT_TABLE(PRODOTTO,MAGAZZINO,CLIENTE,DATASPED,

NUMERO,COSTO_SUM,COSTO_COUNT) Progettazione Logica

FACT_TABLE(ORDINE:dtORDINE,PRODOTTO:dtPRODOTTO,DATASPED:dtDATA, NUMERO, COSTO_SUM,COSTO_COUNT)

Star Schema

dtDATA(DATA,MESE,ANNO)

dtORDINE(ORDINE,DATA,MESE,ANNO, CLIENTE, CLIENTE_CITTA, CLIENTE_REGIONE, CLIENTE_STATO, PROD_P_CLIENTE, PROD_P_CLIENTE_AZIENDA, PROD_P_CLIENTE_AZIENDA_CITTA PROD_P_CLIENTE_AZIENDA_REGIONE, PROD_P_CLIENTE_AZIENDA_STATO )

dtPRODOTTO(PRODOTTO,AZIENDA,AZIENDA_CITTA,AZIENDA_REGIONE,AZIENDA_STATO)

SnowFlake Schema

dtDATA(DATA,MESE:dtMESE) dtMESE(MESE,ANNO)

dtORDINE(ORDINE,CLIENTE:dtCLIENTE,DATA:dtDATA) dtPRODOTTO(PRODOTTO,AZIENDA:dtAZIENDA) dtAZIENDA(AZIENDA,CITTA:CITTA) dtCITTA(CITTA,REGIONE:dtREGIONE)

dtREGIONE(REGIONE,STATO) dtCLIENTE(CLIENTE, CITTA:dtCITTA,PREFER:dtPRODOTTO)

Alimentazione

CREATE VIEW FACT_TABLE AS SELECT

PRODOTTO, ORDINE, DATASPED COSTO_SUM =SUM(COSTO), COSTO_COUNT =COUNT(COSTO), NUMERO =COUNT(*)

FROM DETTAGLIO

GROUP BY PRODOTTO, ORDINE, DATASPED

Codice SQL

Creazione del DBO

CREATE TABLE ORDINE ( ORDINE INT, CLIENTE INT, DATA INT); CREATE TABLE PRODOTTO ( PRODOTTO INT, AZIENDA INT); CREATE TABLE AZIENDA ( AZIENDA INT, CITTA INT); CREATE TABLE CLIENTE ( CLIENTE INT, CITTA INT, PREFER INT); CREATE TABLE CITTA ( CITTA INT, REGIONE INT, STATO INT); CREATE TABLE DATA ( DATA INT, MESE INT, ANNO INT); CREATE TABLE DETTAGLIO ( NRIGA INT, ORDINE INT, PRODOTTO INT, DATASPED INT, COSTO FLOAT ); INSERT CITTA ( CITTA,REGIONE,STATO ) SELECT 10,10,10 INSERT CITTA ( CITTA,REGIONE,STATO ) SELECT 20,20,10 INSERT CITTA ( CITTA,REGIONE,STATO ) SELECT 30,20,10 INSERT AZIENDA ( AZIENDA,CITTA ) SELECT 10,10 INSERT AZIENDA ( AZIENDA,CITTA ) SELECT 20,30 INSERT CLIENTE ( CLIENTE,CITTA,PREFER ) SELECT 10,10,10 INSERT CLIENTE ( CLIENTE,CITTA,PREFER ) SELECT 20,20,30 INSERT CLIENTE ( CLIENTE,CITTA,PREFER ) SELECT 30,20,30 INSERT PRODOTTO ( PRODOTTO,AZIENDA ) SELECT 10,10 INSERT PRODOTTO ( PRODOTTO,AZIENDA ) SELECT 20,20 INSERT PRODOTTO ( PRODOTTO,AZIENDA ) SELECT 30,20 INSERT ORDINE ( ORDINE,CLIENTE,DATA ) SELECT 1,10,30 INSERT ORDINE ( ORDINE,CLIENTE,DATA ) SELECT 2,20,30 INSERT ORDINE ( ORDINE,CLIENTE,DATA ) SELECT 3,20,30 INSERT ORDINE ( ORDINE,CLIENTE,DATA ) SELECT 4,30,30 INSERT DATA ( DATA,MESE,ANNO ) SELECT 10,10,10 INSERT DATA ( DATA,MESE,ANNO ) SELECT 20,20,10 INSERT DATA ( DATA,MESE,ANNO ) SELECT 30,20,10 INSERT DETTAGLIO ( NRIGA,ORDINE,PRODOTTO,DATASPED,COSTO ) SELECT 1,4,10,30,22 INSERT DETTAGLIO ( NRIGA,ORDINE,PRODOTTO,DATASPED,COSTO ) SELECT 2,4,20,30,22 INSERT DETTAGLIO ( NRIGA,ORDINE,PRODOTTO,DATASPED,COSTO ) SELECT 3,4,30,30,22 INSERT DETTAGLIO ( NRIGA,ORDINE,PRODOTTO,DATASPED,COSTO ) SELECT 1,1,10,30,16 INSERT DETTAGLIO ( NRIGA,ORDINE,PRODOTTO,DATASPED,COSTO ) SELECT 2,1,20,20,15 INSERT DETTAGLIO ( NRIGA,ORDINE,PRODOTTO,DATASPED,COSTO ) SELECT 3,1,30,30,13 INSERT DETTAGLIO ( NRIGA,ORDINE,PRODOTTO,DATASPED,COSTO ) SELECT 1,2,10,20,13 INSERT DETTAGLIO ( NRIGA,ORDINE,PRODOTTO,DATASPED,COSTO ) SELECT 2,2,20,30,13 INSERT DETTAGLIO ( NRIGA,ORDINE,PRODOTTO,DATASPED,COSTO ) SELECT 3,2,30,20,12 INSERT DETTAGLIO ( NRIGA,ORDINE,PRODOTTO,DATASPED,COSTO ) SELECT 1,3,10,20,11 INSERT DETTAGLIO ( NRIGA,ORDINE,PRODOTTO,DATASPED,COSTO ) SELECT 2,3,30,30,22

Creazione dello StarSchema

CREATE VIEW [DTDATA] AS SELECT * FROM DATA CREATE VIEW [DTORDINE] AS SELECT ORDINE.ORDINE, ORDINE.CLIENTE, ORDINE.DATA, DATA.MESE, DATA.ANNO, CLIENTE.CITTA AS CLIENTE_CITTA, CITTA.REGIONE AS CLIENTE_REGIONE, CITTA.STATO AS CLIENTE_STATO, PRODOTTO.PRODOTTO AS PROD_P_CLIENTE, PRODOTTO.AZIENDA AS PROD_P_CLIENTE_AZIENDA, AZIENDA.CITTA AS PROD_P_CLIENTE_AZIENDA_CITTA, CITTA_1.REGIONE AS PROD_P_CLIENTE_AZIENDA_REGIONE, CITTA_1.STATO AS PROD_P_CLIENTE_AZIENDA_STATO FROM AZIENDA JOIN ORDINE INNER JOIN DATA ON ORDINE.DATA = DATA.DATA INNER JOIN CLIENTE ON ORDINE.CLIENTE = CLIENTE.CLIENTE INNER JOIN CITTA ON CLIENTE.CITTA = CITTA.CITTA INNER JOIN PRODOTTO ON CLIENTE.PREFER = PRODOTTO.PRODOTTO ON AZIENDA.AZIENDA = PRODOTTO.AZIENDA INNER JOIN CITTA AS CITTA_1 ON AZIENDA.CITTA = CITTA_1.CITTA CREATE VIEW [DTPRODOTTO] AS SELECT PRODOTTO.PRODOTTO, PRODOTTO.AZIENDA, AZIENDA.CITTA AS AZIENDA_CITTA, CITTA_1.REGIONE AS AZIENDA_REGIONE, CITTA_1.STATO AS AZIENDA_STATO FROM PRODOTTO JOIN AZIENDA ON AZIENDA.AZIENDA = PRODOTTO.AZIENDA JOIN CITTA AS CITTA_1 ON CITTA_1.CITTA = AZIENDA.CITTA

1.3 Esercizio: Biglietto Consideriamo un DBO con il seguente schema relazionale:

VOLO(CODVOLO,DATA,RITARDO,COMPAGNIA)

BIGLIETTO(POSTO,[CODVOLO,DATA]:VOLO,COSTO )

CHECK-IN([POSTO,CODVOLO,DATA]: BIGLIETTO, NCOLLI )

Viene richiesto di

A) Progettazione concettuale : schema di fatto BIGLIETTO con dimensioni {VOLO,NPOSTO} e misure {COSTO,NBIGLIETTI,NBIGLIETTI_CHECK-IN} dove

COSTO: costo medio del biglietto NBIGLIETTI: è valutato con il conteggio dei biglietti NBIBLIETTICHECK-IN: è valutato con il conteggio dei biglietti che hanno anche il check-in

B) Progettazione logica : Star schema

C) Alimentazione : Scrivere in SQL l’alimentazione della fact-table.

Osservazioni: La caratteristica di questo esercizio è la presenza di due misure di conteggio:

NBIGLIETTI: valutato con il conteggio dei biglietti

NBIBLIETTICHECK-IN: valutato con il conteggio dei biglietti che hanno anche il check-in

In sezione 1.1.5 abbiamo visto due soluzioni per tali misure. In questo esercizio le useremo entrambe, e quindi

NBIGLIETTI : misura conteggio per il conteggio degli eventi primari

NBIBLIETTICHECK-IN : misura booleana per il conteggio degli eventi primari

Infatti, si vogliono rappresentare tutti i biglietti (contando il loro numero totale tramite NBIGLIETTI e il costo medio tramite COSTO) e si vogliono contare quelli che hanno fatto anche il check-in: NBIBLIETTICHECK-IN sarà una misura di conteggio booleana che assume il valore 1 (c’è stato check-in) ed il valore 0 (no check-in).

Un’altra caratteristica è nell’alimentazione: infatti la dimensione VOLO deriva da una coppia di attributi (DATA e CODVOLO) del DBO.

Esempio di istanza del DBO e corrispondente istanza del fatto (eventi primari)

VOLO

CHECK-IN

BIGLIETTO

Eventi Primari del Fatto BIGLIETTO

Con dimensioni {VOLO,NPOSTO }

CREATE TABLE [DBO].[VOLO] ([DATA] [DATETIME],[CODVOLO] [INT],[RITARDO] [INT],[COMPAGNIA] [INT] ) CREATE TABLE [BIGLIETTO] ( [DATA] [DATETIME],[CODVOLO] [INT],[POSTO] [INT],[COSTO] [FLOAT]) CREATE TABLE [CHECK-IN] ([DATA] [DATETIME],[CODVOLO] [INT],[POSTO] [INT],[NCOLLI] [INT]) INSERT VOLO ( DATA,CODVOLO,RITARDO,COMPAGNIA ) SELECT 'DEC 12 2010 12:00AM',123,25,100 INSERT VOLO ( DATA,CODVOLO,RITARDO,COMPAGNIA ) SELECT 'DEC 13 2010 ',123,15,100 INSERT VOLO ( DATA,CODVOLO,RITARDO,COMPAGNIA ) SELECT 'DEC 12 2010 ',124,0,200 INSERT VOLO ( DATA,CODVOLO,RITARDO,COMPAGNIA ) SELECT 'DEC 13 2010 ',124,5,200 INSERT BIGLIETTO ( DATA,CODVOLO,POSTO,COSTO ) SELECT 'DEC 12 2010 ',123,1,100 INSERT BIGLIETTO ( DATA,CODVOLO,POSTO,COSTO ) SELECT 'DEC 12 2010 ',123,2,159 INSERT BIGLIETTO ( DATA,CODVOLO,POSTO,COSTO ) SELECT 'DEC 12 2010 ',123,3,200 INSERT BIGLIETTO ( DATA,CODVOLO,POSTO,COSTO ) SELECT 'DEC 13 2010 ',123,1,50 INSERT BIGLIETTO ( DATA,CODVOLO,POSTO,COSTO ) SELECT 'DEC 13 2010 ',123,2,50 INSERT BIGLIETTO ( DATA,CODVOLO,POSTO,COSTO ) SELECT 'DEC 13 2010 ',123,3,150 INSERT BIGLIETTO ( DATA,CODVOLO,POSTO,COSTO ) SELECT 'DEC 13 2010 ',124,1,150 INSERT BIGLIETTO ( DATA,CODVOLO,POSTO,COSTO ) SELECT 'DEC 13 2010 ',124,2,50 INSERT [CHECK-IN] ( DATA,CODVOLO,POSTO,NCOLLI ) SELECT 'DEC 12 2010 ',123,2,2 INSERT [CHECK-IN] ( DATA,CODVOLO,POSTO,NCOLLI ) SELECT 'DEC 12 2010 ',123,3,3 INSERT [CHECK-IN] ( DATA,CODVOLO,POSTO,NCOLLI ) SELECT 'DEC 13 2010 ',123,1,2 INSERT [CHECK-IN] ( DATA,CODVOLO,POSTO,NCOLLI ) SELECT 'DEC 13 2010 ',124,2,4

1.3.1 Soluzione Progettazione Concettuale

Per semplicità, si procede effettuando prima il reverse engineering dello schema relazionale

Si inizia da VOLO(CODVOLO,DATA,RITARDO,COMPAGNIA)

Che corrisponde ad un’entità identificata da DATA+CODVOLO. Quindi BIGLIETTO

BIGLIETTO(POSTO,[CODVOLO,DATA]:VOLO,COSTO ) Identificata da entità VOLO + NPOSTO; infine

CHECK-IN([POSTO,CODVOLO,DATA]: BIGLIETTO, NCOLLI )

Essendo la foreign key sulla sua primary key: è un subset.

Lo schema di fatto BIGLIETTO con dimensioni {VOLO,NPOSTO} è molto semplice; si noti che con VOLO si intende l’attributo dimensionale derivante da CODVOLO + VOLO (come nel caso di DISTRETTO dell’esempio VENDITA). L’attributo dimensionale CODVOLO è un figlio di VOLO (da CODVOLO si ricavano informazioni quali partenza, destinazione non considerati nell’esercizio) NB=NBIGLIETTI NBC=NBIGLIETTI_CHECK-IN Fatto BIGLIETTO(VOLO,NPOSTO,COSTO) Dimensioni = {VOLO,NPOSTO}

Granularità: Transazionale (infatti VOLO è CODVOLO + VOLO, quindi si ottiene CODVOLO, VOLO,DATA) Dipendenze funzionale tra le dimensioni: Nessuna Misure normali

NB=1, additiva

NBC = IF <BIGLIETTO CON CHECK-IN> THEN 1 ELSE 0, additiva

Si noti che a questo punto viene solo indicato, ad alto livello, come calcolare NBC; il calcolo effettivo verrà esplicitato in fase di alimentazione

Misure calcolate COSTO= COSTO_SUM/COSTO_COUNT dove COSTO_SUM = SUM(COSTO), additiva COSTO_COUNT = COUNT(COSTO), additiva Fact Table FACT_TABLE(VOLO,NPOSTO,NBC, COSTO_SUM,COSTO_COUNT) Si noti che sono già state fatte due scelte progettuali:

1) per la misura NB: non viene introdotta nella Fact Table, verrà aggregata tramite COUNT(*)

VOLO

DEL

(1,1)

(1,N)

BIGLIETTO

COSTO

CHECK-INNPOSTO

DATA

CODVOLO

RITARDOCOMPAGNIA

NCOLLI

NPOSTO

CODVOLO

BIGLIETTO(C) COSTO (AVG)NBCNB

DATA RITARDO

COMPAGNIA

VOLO

2) per la misura COSTO: lo schema è transazionale, si sono due possibilità per definire COSTO (vedere sezione 1.1.4) , viene scelto di considerare COSTO come misura calcolata

Progettazione Logica

FACT_TABLE(NPOSTO,VOLO:dtVOLO, NBC, COSTO_SUM,COSTO_COUNT)

Star Schema (lo SnowFlake schema coincide con lo star-schema)

dtVOLO(VOLO,DATA,CODVOLO,COMPAGNIA,RITARDO)

Alimentazione

Lo schema è transazionale, quindi la vista di alimentazione non deve raggruppare. Si deve calcolare

NBC = IF <BIGLIETTO CON CHECK-IN> THEN 1 ELSE 0, additiva

Per controllare se il BIGLIETTO è anche in CHECK-IN : si effettua un left-join

SELECT … NBC=CASE WHEN C.[CODVOLO] IS NULL THEN 0 ELSE 1 END FROM BIGLIETTO B LEFT JOIN [CHECK-IN] C ON ( B.[DATA]=C.[DATA] AND B.[CODVOLO]=C.[CODVOLO] AND B.[POSTO] = C.[POSTO])

E’ importante (anche senza eseguirla effettivamente) sapere che questa query restituisce in output lo stesso numero di tuple di BIGLIETTO: infatti BIGLIETTO è nel lato left del join e BIGLIETTO e CHECK-IN condividono la chiave. Pertanto se nella select si mette BIGLIETTO.*, NBC ottengo la tabella BIGLIETTO con in più la colonna NBC e quindi ho tutto quello che mi serve per calcolare la Fact Table.

CREATE VIEW FACT_TABLE AS SELECT B.POSTO AS NPOSTO,

VOLO= B.DATA + CODVOLO, NBC=CASE WHEN C.[CODVOLO] IS NULL THEN 0 ELSE 1 END, COSTO_SUM=COSTO, COSTO_COUNT =CASE WHEN COSTO IS NULL THEN 0 ELSE 1 END

FROM BIGLIETTO B LEFT JOIN [CHECK-IN] C ON ( B.[DATA]=C.[DATA] AND B.[CODVOLO]=C.[CODVOLO] AND B.[POSTO]=C.[POSTO])

In realtà per effettuare la concatenazione VOLO= B.DATA + CODVOLO occorre convertirli in stringhe VOLO=CONVERT(CHAR(20),B.DATA) + '__' +CONVERT(CHAR(20),B.CODVOLO) .

Anche se non richiesto dal testo, si riporta l’alimentazione della dtVOLO:

CREATE VIEW dtVOLO AS SELECT VOLO=CONVERT(CHAR(20),DATA) + '__' +CONVERT(CHAR(20),CODVOLO),

CODVOLO,DATA,COMPAGNIA, RITARDO FROM VOLO

1.3.2 Variante Nello schema di fatto precedente (transazionale) si introduce la misura

GUADAGNO, definita come SUM(COSTO)

Cioè il GUADAGNO è definita a partire dall’attributo COSTO, così come la misura COSTO, ma è una misura additiva. Si introduce inoltre una misura additiva NCOLLI. Gli eventi primari sono:

Fatto: BIGLIETTO(VOLO,POSTO,COSTO)

Dimensioni = {VOLO,NPOSTO}

Granularità: Transazionale

Dipendenze funzionale tra le dimensioni: Nessuna

Misure normali NB=COUNT(*), additiva

NBC = SUM(IF <BIGLIETTO CON CHECK-IN> THEN 1 ELSE 0), additiva

NCOLLI = SUM(IF <BIGLIETTO CON CHECK-IN> THEN NCOLLI ELSE 0), additiva

GUADAGNO=COSTO, additiva

Misure calcolate COSTO= COSTO_SUM/COSTO_COUNT dove COSTO_SUM = SUM(COSTO), additiva COSTO_COUNT = COUNT(COSTO), additiva Progettazione Logica

Naturalmente GUADAGNO viene calcolata a partire da COSTO_SUM, quindi non serve un altro attributo:

FACT_TABLE(NPOSTO,VOLO:dtVOLO, NBC, COSTO_SUM,COSTO_COUNT,NCOLLI)

Alimentazione: si aggiunge il calcolo della misura normale NCOLLI

NCOLLI=ISNULL(NCOLLI,0)

1.3.3 Variante (dimensione derivante da discretizzazione) Si considera un’altra variante in cui la precedente dimensione NPOSTO viene discretizzata nell’attributo dimensionale CLASSE in questo modo:

se NPOSTO <= 1 allora ‘PRIMA’ altrimenti ‘SECONDA’

Schema di fatto con dimensioni {VOLO,CLASSE}

e misure {COSTO,NBIGLIETTI,NBIGLIETTI_CHECK-IN,GUADAGNO,NCOLLI}.

GUADAGNO è ancora definita come SUM(COSTO)

Esempio di istanza del DBO e corrispondente istanza del fatto (eventi primari)

Eventi Primari del Fatto BIGLIETTO

Lo schema di fatto BIGLIETTO con dimensioni {VOLO,CLASSE} si ottiene dal precedente sostituendo NPOSTO con CLASSE (che costituisce una sua discretizzazione) Fatto BIGLIETTO(VOLO,POSTO,COSTO) Dimensioni = {VOLO,CLASSE} Granularità: Temporale Dipendenze funzionale tra le dimensioni: Nessuna

Misure normali NB=COUNT(*), additiva

NBC = SUM(IF <BIGLIETTO CON CHECK-IN> THEN 1 ELSE 0), additive NCOLLI = SUM(IF <BIGLIETTO CON CHECK-IN> THEN NCOLLI ELSE 0), additiva GUADAGNO : SUM(COSTO), additiva

Misure calcolate COSTO= COSTO_SUM/COSTO_COUNT dove COSTO_SUM = SUM(COSTO), additiva COSTO_COUNT = COUNT(COSTO), additiva

CLASSE

CODVOLO

BIGLIETTO(C) COSTO (AVG)NBCNBGUADAGNONCOLLI

DATA RITARDO

COMPAGNIA

VOLO

Progettazione Logica Star Schema (lo SnowFlake schema coincide con lo star-schema)

FACT_TABLE(CLASSE,VOLO:dtVOLO,NB,NBC,NCOLLI, COSTO_SUM,COSTO_COUNT) dtVOLO(VOLO,DATA,COMPAGNIA,FASCIA_RITARDO)

Alimentazione: Essendo lo schema temporale, l’alimentazione della Fact Table richiede di raggruppare sulle dimensioni. Però in questo caso entrambe le dimensioni sono calcolate

VOLO = DATA + CODVOLO CLASSE = IF POSTO <=1 THEN 'PRIMA' ELSE 'SECONDA' END

In SQL il raggruppamento GROUP BY è possibile anche su espressioni generiche quindi

CREATE VIEW FACT_TABLE AS SELECT

VOLO = CONVERT(CHAR(11), B.DATA) + '__' + CONVERT(CHAR(3), B.CODVOLO), CLASSE = CASE WHEN B.POSTO <=1 THEN 'PRIMA' ELSE 'SECONDA' END, COSTO_SUM =SUM(COSTO), COSTO_COUNT=COUNT(COSTO), NB=COUNT(*), NBC=SUM(CASE WHEN C.[CODVOLO] IS NULL THEN 0 ELSE 1 END), NCOLLI= SUM(CASE WHEN C.[CODVOLO] IS NULL THEN 0 ELSE NCOLLI END)

FROM BIGLIETTO B LEFT JOIN [CHECK-IN] C ON (B.[DATA] = C.[DATA] AND B.[CODVOLO] = C.[CODVOLO] AND B.[POSTO] = C.[POSTO]) GROUP BY

CONVERT(CHAR(11), B.DATA) + '__' + CONVERT(CHAR(3), B.CODVOLO), CASE WHEN B.POSTO <=1 THEN 'PRIMA' ELSE 'SECONDA' END

Anche se non richiesto, per ottenere gli eventi primari e la dimension table dtVOLO

SELECT VOLO, CLASSE, COSTO=COSTO_SUM/COSTO_COUNT, NB, NBC, NCOLLI, GUADAGNO_C = (COSTO_SUM/COSTO_COUNT)*NB, -- misura calcolata GUADAGNO_D = COSTO_SUM *1) -- misura derivata aggregata con SUM

FROM FACT_TABLE CREATE VIEW dtVOLO AS SELECT VOLO = CONVERT(CHAR(11), DATA) + '__' + CONVERT(CHAR(3), CODVOLO), DATA,COMPAGNIA, RITARDO FROM VOLO

1.4 Esercizio : Esame

SEDE: un CDS (CorsoDiStudio) ha sede in una FACOLTA

DEL tra DOCENTE e CDS: un DOCENTE è di un CDS

CON tra APPELLO e DOCENTE: un APPELLO è con DOCENTE che lo tiene

Attributo TIPO_ESAME di ESAME : assume il valore STUD se è un esame di uno studente ed il valore GRUP se è un esame di un gruppo

NUMES è un identificatore di ESAME (chiave alternativa nello schema relazionale): è un numero progressivo univoco dell’esame.

FACOLTA(FACOLTA,REGIONE) CDS(CDS,FACOLTA:FACOLTA,STUDENTE:STUDENTE)

DOCENTE(DOCENTE,FACOLTA:FACOLTA,CDL:CDL) STUDENTE(STUDENTE, FACOLTA:FACOLTA) APPELLO(APPELLO,DOCENTE:DOCENTE, GENERE)

ESAME(NUMES, APPELLO:APPELLO, DATA, TIPO_ESAME, VOTO) AK: NUMES ESAMEGRUPPO(NUMES:ESAME, GRUPPO:GRUPPO) ESAMESTUDENTE(NUMES:ESAME, STUDENTE:STUDENTE) GRUPPO(GRUPPO,TIPO)

Viene richiesto:

A) Progettazione concettuale :Progettazione dello schema di fatto ESAME con dimensioni { GRUPPO, STUDENTE, TIPOESAME,DATA, DOCENTE } dove STUDENTE è lo STUDENTE che ha sostenuto l’esame GRUPPO è il GRUPPO che ha sostenuto l’esame misure VOTO_MEDIO: è il voto medio dell’esame NUM_APPELLI: è il conteggio distinto degli appelli B) Progettazione logica :Progettazione dello STARSCHEMA

C) Alimentazione : Scrivere in SQL l’alimentazione della fact-table.

Esempio di istanze:

DBO: relazione ESAME

DW: Eventi Primari del Fatto ESAME

APPELLOESAME DI (1,N)(1,1)

DATA

CDS

RAPPRESENTANTE

(0,N)

(1,1)

STUDENTE

CON

(1,N)

(1,1)

DOCENTE

FACOLTAISCRITTO (0,N)

AFFERENZA

(0,N)

(1,1)

VOTO

REGIONE

ESAMESTUDENTE

(1,1)

(0,N) (1,1)

ESAME GRUPPO

(1,1)(1,N)

(T,E)GENERE

SEDE

(0,N)

(1,1)

DEL

(1,N)

(1,1)

GRUPPO

TIPO_ESAME(STUD/GRUP)

NUMES

TIPO

1.4.1 Soluzione Progettazione Concettuale

Condivisione su STUDENTE: STUDENTE che ha sostenuto l’esame STUDENTE rappresentante del CDS del DOCENTE

Condivisione su FACOLTA: FACOLTA del DOCENTE FACOLTA dello STUDENTE FACOLTA dello STUDENTE rappresentante

Dipendenze funzionali tra le dimensioni: STUDENTE à TIPOESAME GRUPPO à TIPOESAME

Pattern primario = {STUDENTE,DATA,GRUPPO}

Per le FD tra le dimensioni, nello schema equivalente si ha (si riporta solo la parte interessata)

Si noti la convergenza su TIPO_ESAME

Fatto ESAME(NUMES, APPELLO:APPELLO, DATA, TIPO_ESAME, MESE,ANNO,VOTO) Dimensioni = { DOCENTE,STUDENTE,GRUPPO,TIPOESAME,DATA }

Misure normali COUNT(DISTINCT ESAME.APPELLO), additiva con NA = {STUDENTE,DATA,GRUPPO} Misure calcolate

VOTO_MEDIO = VOTO_TOT/ VOTO_COUNT dove

VOTO_TOT = SUM(VOTO) VOTO_COUNT = COUNT(*)

Fact Table FACT_TABLE(DATA,GRUPPO,STUDENTE,DOCENTE,TIPOESAME,

VOTO_TOT, VOTO_COUNT,NUM_APPELLI)

Progettazione Logica STARSCHEMA:

FACT_TABLE(DATA,GRUPPO:dtGRUPPO,STUDENTE:dtSTUDENTE,DOCENTE:dtDOCENTE TIPOESAME,VOTO_TOT, VOTO_COUNT,NUM_APPELLI) dtGRUPPO(GRUPPO,TIPO)

dtSTUDENTE(STUDENTE, FACOLTA, FACOLTA_REGIONE, FACOLTA_STATO) dtDOCENTE(DOCENTE,FACOLTA, FACOLTA_REGIONE,

CDS,CDS_FACOLTA,CDS_FACOLTA_REGIONE, CDS_STUDENTE,CDS_STUDENTE_FACOLTA,CDS_STUDENTE_FACOLTA_REGIONE )

STUDENTEESAME(C) VOTO_MEDIO (AVG)

NUM_APPELLI

FACOLTA

REGIONE

DATA

TIPO_ESAME

DOCENTE

CDS

GRUPPOTIPO

T,E

STUDENTEESAME(C) VOTO_MEDIO (AVG)

NUM_APPELLIGRUPPO

TIPO

T,E

TIPO_ESAME

Alimentazione

L’aspetto caratteristico è la presenza di dimensioni opzionali, il cui valore nullo viene opportunamente codificato (si usa sempre 9999)

CREATE VIEW VIEW1 AS SELECT ESAME.*, ISNULL(ESAMESTUDENTE.STUDENTE,9999) AS STUDENTE, ISNULL(ESAMEGRUPPO.GRUPPO,9999) AS GRUPPO FROM ESAME LEFT OUTER JOIN ESAMEGRUPPO ON ESAME.NUMES = ESAMEGRUPPO.NUMES LEFT OUTER JOIN ESAMESTUDENTE ON ESAME.NUMES = ESAMESTUDENTE.NUMES

CREATE VIEW FACT_TABLE AS SELECT GRUPPO, STUDENTE, TIPOESAME, DATA,DOCENTE, COUNT(*) AS NUM_TOT, SUM(VOTO) AS VOTO_TOT, COUNT(VOTO) AS VOTO_COUNT, COUNT(DISTINCT APPELLO.APPELLO) AS NUM_APPELLI FROM VIEW1 NATURAL JOIN APPELLO GROUP BY GRUPPO, STUDENTE, TIPOESAME, DATA,DOCENTE

Per semplicità si usa il NATURAL JOIN (in modo da evitare di scrivere la condizione di join).

E’ possibile usare il NATURAL JOIN anche per i join esterni, ovvero la scrittura della VIEW1 si può semplificare scrivendo:

CREATE VIEW VIEW1 AS SELECT ESAME.*, ISNULL(ESAMESTUDENTE.STUDENTE,9999) AS STUDENTE, ISNULL(ESAMEGRUPPO.GRUPPO,9999) AS GRUPPO FROM ESAME LEFT NATURAL JOIN ESAMEGRUPPO LEFT NATURAL JOIN ESAMESTUDENTE

1.5 Esercizio: Vendita Sia dato il seguente schema relazionale del DBO

VENDITA(VENDITA,PRODOTTO,CASSA, COMMESSO,NUMERO_SCONTRINO,DATA) {PRODOTTO,DATA} à CASSA NUMERO_SCONTRINO à DATA

PRODOTTO(PRODOTTO,TIPO:TIPO) TIPO(TIPO, CATEGEGORIA,GRUPPO)

Viene richiesto: 1) Reverse Engineering: Schema E/R equivalente 2) Progettazione Concettuale: Schema di Fatto con

Dimensioni = {PRODOTTO,CASSA,COMMESSO,DATA} e Misure i. NUMVENDITE = count(*) ii. NUMCLIENTI = count(DISTINCT NUMERO_SCONTRINO)

3) Progettazione logica: SNOWFLAKE SCHEMA 4) Supponendo che l’attributo COMMESSO assume valore NULL per le vendite senza COMMESSO e

un valore NON NULLO per le vendite con commesso, considerare lo Schema di Fatto con Dimensioni = {PRODOTTO,CASSA, DATA} e discutere la definizione e l’aggregabilità della misura NUMVENDITE_CONCOMMESSO : conteggio delle vendite con COMMESSO

1.5.1 Soluzione

Schema E/R:

Schema di Fatto VENDITA

Fatto VENDITA(VENDITA,PRODOTTO,CASSA, COMMESSO,NUMERO_SCONTRINO,DATA) Dimensioni = { PRODOTTO,CASSA, COMM,DATA } Granularità: Temporale Dipendenze funzionale tra le dimensioni: {DATA,PRODOTTO } à CASSA

COMM

PRODOTTO ALLACASSA(1,N)

CASSADI

(1,N)

DATA

CON(1,1)

(1,N)

VENDITA IN(1,1) (1,N) SCONTRINO

(1,1)

(1,N)

GRUPPO

TIPO HA(1,N) (1,1)

CATEGORIA

PRODOTTO

VENDITANUMVENDITENUMCLIENTI

DATACOMM

CASSA

TIPO

GRUPPO CAT

PRODOTTO

VENDITANUMVENDITENUMCLIENTI

DATACOMM

CASSA

TIPO

GRUPPOCAT

Misure normali NUMVENDITE= COUNT(*), additiva

NUMCLIENTI=COUNT(DISTINCT NUMERO_SCONTRINO), additiva con NA = { PRODOTTO, COMM } Per valutare quali sono i pattern per i quali il valore aggregato di NUMCLIENTI può essere calcolato: si considera lo schema equivalente senza FD tra le dimensioni: NA = { PRODOTTO, COMMESSO}.

SnowFlake Schema FACT_TABLE(PRODOTTO:dtPRODOTTO,DATA,COMMESSO,CASSA,NUMVENDITE,NUMCLIENTI)

dtPRODOTTO(PRODOTTO,TIPO:dtTIPO) dtTIPO(TIPO,GRUPPO, CATEGORIA)

E’ possibile limitare la chiave della Fact Table al solo pattern primario {PRODOTTO,DATA,COMMESSO} FACT_TABLE(PRODOTTO:dtPRODOTTO,DATA,COMMESSO,CASSA,NUMVENDITE,NUMCLIENTI)

E’ inoltre possibile normalizzare la Fact Table al solo pattern primario {PRODOTTO,DATA,COMMESSO}: FACT_TABLE(PRODOTTO:dtPRODOTTO, DATA,COMMESSO, NUMVENDITE,NUMCLIENTI)

dtPRODOTTO(PRODOTTO,TIPO:dtTIPO) dtTIPO(TIPO,GRUPPO, CATEGORIA) dtCASSA(PRODOTTO:dtPRODOTTO, DATA, CASSA)

1.6 Esercizio (19/12/2012) Dato il seguente Schema di Fatto

OPERAZIONI tramite BANCOMAT presso una FILIALE e su un CONTOCORRENTE; per le OPERAZIONI di versamento-assegno c’è la BANCA dell’ASSEGNO VERSATO e viene indicata la COMMISIONE che dipende da tale banca e dallo STATO della banca della filiale dell’operazione. Viene richiesto di:

1) Progettazione logica: Progettazione dello SNOWFLAKE SCHEMA;

2) Si consideri un arco multiplo tra CONTOCORRENTE e CLIENTE (un contocorrente è ora intestato a più clienti, con un certo PESO), con IMPORTO misura pesata e N_BANCOMAT misura d’impatto. Discutere cosa cambia nella Progettazione Logica (è sufficiente riportare solo le parti dello schema che risultano modificate).

Soluzione SNOWFLAKE SCHEMA

FACT_TABLE (MESE,FILIALE:DT_FILIALE,CC:DT_CC,BANCAASSEGNO:DT_BANCA, NBANKOMAT, IMPORTO_SUM, IMPORTO_COUNT) DT_BANCA(BANCA,STATO) DT_CC(CC,CLIENTE:DT_CLIENTE) DT_CLIENTE(CLIENTE,SESSO) DT_FILIALE(FILIALE,CITTA:DT_CITTA, BANCA:DT_BANCA) DT_CITTA(CITTA, REGIONE:DT_REGIONE) DT_REGIONE(REGIONE, STATO) DT_COMMISSIONE(BANCAASSEGNO:DT_BANCA,STATO,COMMISSIONE)

Arco multiplo tra CONTOCORRENTE e CLIENTE:

FACT_TABLE_PD(MESE,FILIALE:DT_FILIALE,CC:DT,BA:DT_BANCA, CLIENTE:DT_CLIENTE, NBANKOMAT_P, NBANKOMAT_NP,IMPORTO_SUM_P, IMPORTO_COUNT_P)

DT_CC(CC,CLIENTE:DT_CLIENTE)

Lo SNOWFLAKE SCHEMA è uguale al precedente, si toglie solo DT_CC in quanto ora è degenere.

OPERAZIONE(C) IMPORTO (AVG)

N_BANKOMAT

FILIALE

BANCACONTOCORRENTE

SESSOMESE

BANCA-ASSEGNO

CLIENTE

CITTA

STATO

COMMISSIONE

REGIONE

1.7 Esercizio (14/01/2013 ) Dato il seguente schema relazionale del DBO:

VIAGGIO(PERSONA:PERSONA,DATA, ITINERARIO:ITINERARIO) ITINERARIO(ITINERARIO,PARTENZA:CITTA,DESTINAZIONE:CITTA,AGENZIA,TIPO) FD: PARTENZA, DESTINAZIONE à AGENZIA PERSONA(PERSONA, RESIDENZA:CITTA) CITTA(CITTA, REGIONE:REGIONE, DISTRETTO) REGIONE(REGIONE, STATO)

Viene richiesto di:

A) Progettazione concettuale : schema di fatto VIAGGIO con dimensioni {RESIDENZA, PARTENZA, DESTINAZIONE_STATO, ANNO, AGENZIA} e misure

1. NumVIAGGI: numero di viaggi, ottenuto con un semplice conteggio 2. NumITINERARI: numero di itinerari, ottenuto come count(distinct ITINERARIO) 3. NumPERSONE: numero di persone, ottenuto come count(distinct PERSONA)

dove ANNO è un attributo dimensionale di DATA, quindi DATA à ANNO.

B) Progettazione logica : Star schema.

Soluzione

Schema di Fatto VIAGGIO

Fatto VIAGGIO(PERSONA:PERSONA,DATA:DATA, ITINERARIO:ITINERARIO)

Dimensioni = { RESIDENZA,PARTENZA, DESTINAZIONE_STATO, ANNO, AGENZIA }

Granularità: Temporale - Dipendenze funzionale tra le dimensioni: Nessuna Misure normali NumVIAGGI = COUNT(*), additiva

NumITINERARI = COUNT(DISTINCT ITINERARIO), additiva con NA = { RESIDENZA, ANNO } in quanto

ITINERARIOà {PARTENZA, DESTINAZIONE_STATO,AGENZIA} NumPERSONE =COUNT(DISTINCT PERSONA), additiva con NA = { PARTENZA,DESTINAZIONE_STATO,AGENZIA , ANNO } in quanto PERSONA à RESIDENZA

Star Schema FACT_TABLE(RESIDENZA:DTCITTARESID,PARTENZA:DTCITTAPART, DESTINAZIONE_STATO, ANNO, AGENZIA, NumPERSONE, NumVIAGGI, NumITINERARI)

DTCITTARESID(RESIDENZA,REGIONE,STATO,DISTRETTO)

DTCITTAPART(PARTENZA,REGIONE,STATO,DISTRETTO)

DISTRETTO

STATOCITTA

DESTINAZIONE_STATO

VIAGGIONumITINERARI

NumPERSONE

NumViaggi

PARTENZA

RESIDENZA REGIONE

AGENZIA

ANNO

1.8 Esercizio (16/04/2013 ) Dato il seguente schema relazionale del DBO:

TELEFONATA(NP,DATA,DA_CHIAMANTE:SIM, A_CHIAMATA:SIM,DURATA) SIM(SIM,PREFISSO:PREFISSO, UTENTE:UTENTE, TARIFFA) PREFISSO(PREFISSO, OPERATORE) UTENTE(UTENTE, CITTA)

Viene richiesto di: A) Progettazione concettuale : schema di fatto TELEFONATA

con dimensioni {DATA, SIM_CHIAMANTE, OPERATORE_CHIAMATO, UTENTE_CHIAMATO } e misure

DURATA_MEDIA: durata media delle telefonate NumSIM_CHIAMANTI: numero delle SIM chiamanti, count(distinct DA_CHIAMANTE) NumSIM_CHIAMATE: numero delle SIM chiamate, count(distinct A_CHIAMATA)

B) Progettazione logica : Progettazione dello STAR SCHEMA.

Soluzione Schema di Fatto TELEFONATA

Fatto TELEFONATA(NP,DATA,DA_CHIAMANTE:SIM, A_CHIAMATO:SIM,DURATA) Dimensioni = {DATA, SIM_CHIAMANTE, OPERATORE_CHIAMATO, UTENTE_CHIAMATO } Granularità: Temporale Dipendenze funzionale tra le dimensioni: Nessuna Misure normali NumSIM_CHIAMATE =COUNT(DISTINCT A_CHIAMATA), additiva con NA = {DATA, SIM_CHIAMANTE }

in quanto A_CHIAMATA à { OPERATORE_CHIAMATO, UTENTE_CHIAMATO } Misure calcolate

DURATA_MEDIA - misura calcolata DURATA_TOT/ DURATA_COUNT - con DURATA_TOT = SUM(DURATA) DURATA_COUNT = COUNT(DURATA)

Misure derivate NumSIM_CHIAMANTI = COUNT(DISTINCT DA_CHIAMANTE)

misura derivata dal valore di una dimensione; per gli eventi primari vale 1; per gli eventi secondari si usa COUNT(DISTINCT DA_CHIAMANTE).

Star Schema La dimensione OPERATORE_CHIAMATO è degenere quindi non richiede dimension table. La misura NumSIM_CHIAMANTI è derivata quindi non è nella FACT_TABLE.

FACT_TABLE(DATA, OPERATORE_CHIAMATO, SIM_CHIAMANTE:DT_SIM_CHIAMANTE, UTENTE_CHIAMATO: DT_UTENTE_CHIAMATO, DURATA_COUNT, DURATA_TOT, NumSIM_CHIAMATE)

DT_SIM_CHIAMANTE(SIM,TARIFFA,PREFISSO,OPERATORE,UTENTE,CITTA)

DT_UTENTE_CHIAMATO(UTENTE,CITTA)

TARIFFA

OPERATORE

SIM_CHIAMANTE

OPERATORE_CHIAMATO

TELEFONATANumSIM_CHIAMANTI

NumSIM_CHIAMATE

DURATA_MEDIAUTENTE

PREFISSO

DATA

UTENTE_CHIAMATOCITTA

1.9 Esercizio (10/09/2013 ) Dato il seguente schema relazionale del DBO:

CONVENZIONE(CONVENZIONE, GENERE, ISTITUZIONE:ISTITUZIONE) CONTRATTO(CONVENZIONE:CONVENZIONE, CONTRAENTE:PERSONA, COMMITTENTE:

PERSONA, ANNO, TIPO, DURATA,IMPORTO) ISTITUZIONE(ISTITUZIONE, DIRETTORE:PERSONA, SEDE_LEGALE:CITTA) PERSONA(PERSONA, RESIDENZA:CITTA, TITOLO_DI_STUDIO) CITTA(CITTA, STATO)

Viene richiesto di: A) Progettazione concettuale : schema di fatto CONTRATTO con cinque dimensioni

1) CONTRAENTE 2) COMMITTENTE_CITTA 3) CONVENZIONE 4) ANNO 5) TIPO

e tre misure

1) DURATA: è la durata media dei contratti 2) IMPORTO: è l’importo complessivo dei contratti 3) NUMERO: è il numero complessivo dei contratti

B) Progettazione logica : Star schema.

C) Si discuta l’aggregabilità delle seguenti misure :

1) NumCOMMITTENTI: ottenuto come count(distinct COMMITTENTE) 2) NumCONTRAENTI: ottenuto come count(distinct CONTRAENTE) 3) NumISTITUZIONI: ottenuto come count(distinct ISTITUZIONE)

Soluzione

Schema di Fatto TELEFONATA

DIMENSIONI ={CONTRAENTE, COMMITTENTE_CITTA, CONVENZIONE, ANNO, TIPO}

L’insieme delle dimensioni contiene la chiave {CONTRAENTE,CONVENZIONE}, quindi lo schema è transazionale e tra le dimensioni c’è la dipendenza funzionale

GENERE

CITTA

CONVENZIONE

COMMITTENTE_CITTA

CONTRATTODURATAIMPORTONUMERO

ISTITUZIONE

TIPO

ANNOCONTRAENTE

PERSONA

STATO

TITOLO

{CONTRAENTE,CONVENZIONE} à { COMMITTENTE_CITTA, ANNO, TIPO}

Misure normali

DURATA, additiva

IMPORTO, additiva

Essendo lo schema transazionale la misura NUMERO può essere considerata come misura vuota, da aggregare tramite count(*) e quindi non verrà riportata nella FACT_TABLE.

Star Schema

FACT_TABLE(CONVENZIONE:DTCONVENZIONE,CONTRAENTE:DTCONTRAENTE, COMMITTENTE_CITTA:DTCOMMITTENTE_CITTA, ANNO, TIPO, DURATA,IMPORTO)

DTCOMMITTENTE_CITTA(CITTA, STATO) DTCONVENZIONE(CONVENZIONE, GENERE,ISTITUZIONE,

DIRETTORE, DIRETTORE_TITOLO,DIRETTORE_CITTA, DIRETTORE_STATO, SEDE_CITTA, SEDE_STATO)

DTCONTRAENTE(PERSONA, TITOLO, CITTA, STATO,)

Aggregabilità delle misure :

1) NumCOMMITTENTI: ottenuto come count(distinct COMMITTENTE) E’ addittiva rispetto alla dimensione COMMITTENTE_CITTA (in quanto COMMITTENTE à COMMITTENTE_CITTA), non aggregabile rispetto alle altre dimensioni

2) NumCONTRAENTI: ottenuto come count(distinct CONTRAENTE) E’ una misura derivata (CONTRAENTE è nella FACT_TABLE); quindi aggregabile rispetto a tutte le dimensioni

3) NumISTITUZIONI: ottenuto come count(distinct ISTITUZIONE) E’ addittiva rispetto alla dimensione CONVENZIONE (in quanto ISTITUZIONEà CONVENZIONE), non aggregabile rispetto alle altre dimensioni.

Top Related