Post on 15-Apr-2017
Progetto Grouppone 3 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
15
Realizzazione di un sistema web based per
implementare dei gruppi di acquisto: un
insieme di persone che possono realizzare
l’acquisto di un bene in un’unica
transazione finanziaria.
Progetto Grouppone Esame di Basi di Dati 2011-2012
Prof. Corrado Aaron Visaggio
Francesco Cardinale - 195001566 Paolo Bongo - 195001568 Antonio Pagliaro – 863000120
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
1
Indice
1 Introduzione .............................................................................................................................................. 2
1.1 Progettazione di una Base di Dati: Metodologie ............................................................................... 2
1.2 Specifica dei requisiti ......................................................................................................................... 3
1.3 Glossario dei Termini ......................................................................................................................... 4
1.4 Specifica delle operazioni .................................................................................................................. 5
2 Progettazione Concettuale ....................................................................................................................... 6
2.1 Schema Concettuale – Strategie di progetto..................................................................................... 6
2.2 Considerazioni sullo schema concettuale ......................................................................................... 6
2.3 Passi per la costruzione dello schema E-R ......................................................................................... 7
2.4 Schema E-R Concettuale .................................................................................................................. 11
2.5 Dizionario dei Dati ........................................................................................................................... 12
2.6 Regole di Business ........................................................................................................................... 13
3 Progettazione Logica ............................................................................................................................... 14
3.1 Schema Concettuale – Obiettivi e Strategie .................................................................................... 14
3.2 Analisi delle prestazioni sullo schema E-R ....................................................................................... 14
Tavola dei Volumi .................................................................................................................................... 15
Tavola delle Operazioni ........................................................................................................................... 15
3.3 Ristrutturazione dello schema E-R .................................................................................................. 16
3.4 Tavole degli Accessi ......................................................................................................................... 18
3.5 Analisi di Normalizzazione ............................................................................................................... 21
3.6 Traduzione verso il modello relazionale .......................................................................................... 21
Schema Relazionale ................................................................................................................................. 21
Schema Logico ......................................................................................................................................... 22
4 Progettazione Fisica ................................................................................................................................ 23
4.1 Creazione del Database in SQL ........................................................................................................ 23
Script SQL per la definizione del Database Grouppone........................................................................... 23
Script SQL per la definizione delle Tavole ............................................................................................... 23
Implementazione delle regole di Business .............................................................................................. 26
4.2 Interrogazioni in Algebra Relazionale .............................................................................................. 32
4.3 Script SQL per le operazioni ............................................................................................................. 36
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
2
1 Introduzione
1.1 Progettazione di una Base di Dati: Metodologie
Metodologie e modelli per il progetto
La metodologia di progetto per la realizzazione di basi di dati prevede la separazione netta tra le decisioni
relative a “cosa” rappresentare in una base di dati (progettazione concettuale), e quelle relative a “come”
farlo (progettazione logica e progettazione fisica).
Progettazione Concettuale
Scopo della progettazione concettuale è quello di rappresentare le specifiche informali della realtà di
interesse in termini di una descrizione formale e completa, ma indipendente dai criteri di rappresentazione
utilizzati nei sistemi di gestione di basi di dati. Il prodotto di questa fase viene chiamato schema concettuale
e fa riferimento ad un modello concettuale dei dati.
Il modello concettuale ci consente di descrivere l’organizzazione dei dati ad un alto livello di astrazione,
senza tener conto degli aspetti implementativi.
Progettazione Logica
Consiste nella traduzione dello schema concettuale, definito durante la progettazione concettuale, nel
modello di rappresentazione dei dati adottato dal sistema di gestione di base di dati a disposizione. Il
prodotto di questa fase viene denominato schema logico e fa riferimento ad un modello logico dei dati.
Il modello logico permette di rappresentare i dati secondo una rappresentazione ancora indipendente da
dettagli fisici, ma concreta perché disponibile nei sistemi di gestione di basi di dati.
Progettazione Fisica
Durante la progettazione fisica lo schema logico viene completato con la specifica dei parametri fisici di
memorizzazione dei dati (organizzazione dei files e degli indici). Il prodotto di questa fase viene denominato
schema fisico e fa riferimento ad un modello fisico dei dati.
Il modello fisico dipende dallo specifico sistema di gestione di basi di dati scelto e si basa sui criteri di
organizzazione fisica dei dati in quel sistema.
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
3
1.2 Specifica dei requisiti
Frasi di Carattere generale
Si intende realizzare un sistema web based per la implementazione dei gruppi di acquisto.
Frasi relative ad Acquirente
Ogni Acquirente è definito da: Nome, Cognome, e-mail, indirizzo dell’abitazione, indirizzo di fatturazione,
Telefono. Ogni acquirente può acquistare una certa quantità di un bene, che deve essere specificata, solo
attraverso un gruppo di acquisto. Un acquirente non può vendere beni. Gli acquirenti possono scambiarsi
messaggi di posta privati. Ogni acquirente, rigorosamente dopo aver effettuato un acquisto, può rilasciare
(al massimo) un feedback sul venditore relativo alla transazione.
Frasi relative a Venditore
Ogni venditore è definito da: Ragione Sociale (Nome Ditta), Categoria1 e Categoria2 (di vendita), e-mail,
indirizzo fisico dell’azienda, URL del sito web aziendale, Telefono Cellulare, Telefono azienda, Partita IVA,
Contratto di rapporto con Grouppone (stipulato e controfirmato dalle parti).
Sulla pagina del venditore verranno pubblicati i feedback espressi dagli acquirenti in relazione ad ogni
transazione.
Un venditore può vendere al massimo due categorie di beni. Il venditore non può effettuare acquisti.
Frasi relative a Gruppo di Acquisto
Ogni Gruppo di Acquisto è definito da: lista degli acquirenti. Per il gruppo di acquisto inoltre
rappresentiamo la data di creazione e la quantità del bene desiderata da ogni acquirente.
Frasi relative a Centro di Raccolta
Ogni Centro di raccolta (nel quale verrà consegnata l’offerta al termine della transazione) è definito da:
Indirizzo, Responsabile.
Frasi relative a Transazione
Ogni Transazione è definita da: Venditore, Offerta, Centro di raccolta, Gruppo di acquisto.
Frasi relative a Offerta
Ogni Offerta è definita da: Bene offerto, Quantità del bene offerto, Unità di misura del bene, Prezzo
unitario, Scadenza dell’offerta, Data di consegna del bene (presso il centro di raccolta), Centro di raccolta.
Frasi relative a Messaggio
Ogni Messaggio è definito da: Destinatari, Mittente, Data, Ora, Oggetto, Corpo del messaggio.
Gli acquirenti possono scambiarsi messaggi (privati).
Frasi relative a Feedback
Ogni Feedback è definito da: Voto Numerico e Giudizio testuale.
Il feedback è composto da un voto numerico da 1 (molto scarso) a 5 (ottimo) e un giudizio testuale.
Ogni utente non può esprimere più di feedback circa un venditore a seguito di una transazione.
Frasi relative a Responsabile
Ogni Responsabile (del centro di raccolta) è definito da: Nome, Cognome, Indirizzo, Telefono.
Frasi relative a Categoria
Ogni Venditore può vendere al massimo due categorie di beni.
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
4
1.3 Glossario dei Termini
TERMINE DESCRIZIONE SINONIMI COLLEGAMENTI
Acquirente
Colui che effettua l’acquisto di una certa quantità dell’offerta, attraverso un gruppo di acquisto
Persona, Utente Gruppo di Acquisto, Messaggio, Offerta, Venditore, Feedback
Venditore
Rappresenta colui che, in qualità di azienda o società, propone un’offerta.
Azienda, Ditta Offerta, Acquirente,
Feedback
Gruppo di Acquisto Lista di Acquirenti interessati alla stessa offerta.
- Transazione, Acquirente,
Offerta
Centro di Raccolta Il luogo in cui verrà consegnata l’offerta acquistata.
Luogo di consegna Transazione, Responsabile
Transazione
Operazione finanziaria con la quale un gruppo di acquisto compra un’offerta
- Centro di raccolta,
Gruppo di Acquisto, Offerta, Venditore
Offerta
Offerta presentata dal Venditore e che può essere comprata da un gruppo di acquisto. Rappresenta una certa quantità di un bene.
- Transazione, Venditore, Acquirente, Gruppo di
Acquisto
Messaggio
Si tratta di un messaggio privato (stringa di caratteri) che può essere scambiato dagli acquirenti.
Messaggio di posta (privato)
Acquirente
Feedback
Rappresenta una valutazione (testuale e numerica) che l’acquirente può esprimere sul venditore a seguito di una transazione.
Valutazione Acquirente, Venditore,
Transazione
Responsabile Colui che gestisce il centro di raccolta
- Centro di raccolta
Bene
Il bene che compone l’offerta, messo in vendita dal venditore e appartenente ad una categoria specifica
Merce Acquirente, Offerta
Categoria Rappresenta la categoria di vendita del venditore.
Tipologia di Bene Venditore, Offerta
Ragione Sociale Denominazione di una società o azienda
Nome Ditta Venditore
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
5
Partita IVA
Sequenza di cifre (11) che identifica univocamente un soggetto che esercita un’attività rilevante ai fini dell’imposizione fiscale indiretta
- Venditore
1.4 Specifica delle operazioni
Operazione 1 Trovare tutti gli acquirenti che hanno effettuato transazioni di beni appartenenti alla
stessa categoria e che hanno espresso valutazioni negative sul venditore (voto < 3).
Operazione 2 Trovare tutti i venditori che hanno ricevuto valutazioni negative dagli stessi acquirenti.
Operazione 3 Trovare tutti i venditori che hanno ricevuto valutazioni positive dagli acquirenti che
vivono nella stessa città.
Operazione 4 Trovare tutti le coppie di acquirenti che hanno comperato un bene dallo stesso venditore
e che si sono scambiati almeno un messaggio di posta privata.
Operazione 5 Trovare tutti gli acquirenti che hanno comperato un bene dallo stesso venditore e che
hanno pubblicato un messaggio sulla sua pagina personale.
Operazione 6 Trovare tutti gli acquirenti che nell’ultimo anno solare (es. 29/11/2011 – 29/11/2012) hanno acquistato beni da un solo venditore.
Operazione 7 Trovare tutte le offerte della categoria FOTOGRAFIA per le quali la città del venditore sia Avellino.
Operazione 8 Trovare tutti i venditori per i quali almeno una transazione è stata valutata positivamente da tutti gli acquirenti che hanno lasciato il feedback.
Operazione 9 Trovare tutte le offerte della categoria INFORMATICA per le quali il prezzo unitario sia inferiore a 100 €.
Operazione 10
Trovare tutti i venditori che nell’ultimo anno solare (es. 29/11/2011 – 29/11/2012)
hanno proposto un’offerta che non ha raggiunto la quantità minima necessaria a
completare la transazione.
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
6
2 Progettazione Concettuale
2.1 Schema Concettuale – Strategie di progetto
Per descrivere in modo sintetico le specifiche sui dati della nostra applicazione fin ora raccolte, costruiamo
uno schema Entità-Relazione. Lo sviluppo di tale schema concettuale può avvenire seguendo diverse
strategie:
Strategia top-down
Strategia bottom-up
Strategia inside-out
Strategia mista
Ognuna di queste strategie ha vantaggi e svantaggi, la scelta di quella più appropriata dipende da vari
fattori quali la complessità dell’applicazione e il numero di sviluppatori che lavorano su tale applicazione.
Data la complessità della nostra applicazione abbiamo preferito adottare un strategia mista in quanto essa
è la più flessibile tra la varie strategie perché si adatta bene a esigenze contrapposte; cioè quella di
suddividere un problema complesso in sotto problemi e quella di procedere per raffinamenti successivi.
Essa ci consente una visione unitaria, anche se astratta, dell’intero progetto e può guidare la fase di
integrazione dei sottoschemi. La strategia mista, infatti, prevede la suddivisione dei requisiti in componenti
separate (come nella strategia bottom-up), nel contempo fornisce uno schema scheletro, cioè uno schema
astratto contenente i concetti principali dell’applicazione (proprio di una strategia top-down).
2.2 Considerazioni sullo schema concettuale
Si considera come acquirente un utente che ha presentato almeno un’offerta.
Per quanto riguarda il venditore, le specifiche di progetto ci suggeriscono che il venditore per poter
vendere sul portale Grouppone deve aver stipulato e controfirmato dalle parti un contratto di rapporto.
Questa condizione di accettazione è di tipo booleano e può assumere o il valore vero [1] o il valore falso
[0]. Ma affinché il venditore risulti registrato, questa condizione deve essere per forza accettata. Da
questo si deduce che è inutile un attributo Contratto per il venditore; quindi possiamo affermare che
nella base di dati esisterà un venditore se e solo se ha stipulato e firmato il contratto con Grouppone.
Per quanto riguarda il gruppo di acquisto bisogna precisare che: secondo le specifiche date, una
transazione viene effettuata se la domanda del gruppo di acquisto coincide esattamente con l’offerta
del venditore; questo implica che quando un acquirente acquista un certo numero di pezzi di un
offerta, egli entra implicitamente a far parte del gruppo di acquisto relativo a quella determinata
offerta. Quindi definiamo Acquisto in Gruppo la relazione che lega le offerte agli acquirenti e tiene
conto del numero di pezzi acquistati da ogni acquirente, dalla data di acquisto di un certo numero di
pezzi da parte di ogni singolo acquirente.
Per quanto riguarda la data di creazione del gruppo di acquisto si suppone che essa coincida con la
data del primo acquisto di un certo numero di pezzi dell’offerta da parte di un acquirente.
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
7
2.3 Passi per la costruzione dello schema E-R
Schema scheletro
Da una semplice ispezione delle nostre specifiche strutturate dei requisiti emergono tre concetti principali
che possono essere rappresentati naturalmente da entità: i Venditori, le Offerte e gli Acquirenti. Tra queste
entità esistono delle relazioni che possono essere la Vendita di un offerta da parte di un venditore e
l’acquisto di un offerta, tramite Acquisto in Gruppo, da parte di uno o più acquirenti.
Le specifiche impongono che:
L’entità Venditore deve a vere i seguenti attributi:
Ragione Sociale (o Nome Ditta)
Categoria 1
Categoria 2
Indirizzo di posta elettronica (E-mail)
Indirizzo fisico dell’azienda e Città.
URL del sito web aziendale (URL_web)
Recapito telefonico cellulare
Recapito telefonico azienda
Partita IVA
L’entità Acquirente deve avere i seguenti attributi:
Nome
Cognome
Indirizzo di posta elettronica (E-mail)
Indirizzo abitativo e Città.
Indirizzo di fatturazione
Recapito telefonico, sia cellulare che fisso
L’entità Offerta deve avere i seguenti attributi:
ID_Offerta
Categoria (a cui appartiene il bene proposto nell’offerta)
Descrizione
Data di Scadenza dell’offerta
Prezzo unitario
Quantità
Unità di misura del bene (può essere quella del sistema metrico decimale o il pezzo)
Giorni previsti per la consegna (dopo che la transazione sia andata a buon fine)
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
8
Relativamente all’indirizzo fisico dell’azienda del Venditore e l’indirizzo abitativo dell’acquirente, abbiamo
deciso di rappresentarlo mediante due attributi, ossia Città e Indirizzo (rappresentante Via e Numero
civico), non includendo il CAP e altre informazioni non essenziali per soddisfare le specifiche della nostra
base di dati.
L’attributo Data, nella relazione Acquisto in gruppo, rappresenta la data di acquisto di ciascun acquirente.
Infatti, successivamente supporremo che la data di creazione del Gruppo di acquisto coincida con la data di
acquisto del primo acquirente del gruppo.
Inoltre, nell’entità Offerta abbiamo inserito l’attributo Giorni_alla_consegna, che specifica il numero dei
giorni minimi necessari alla consegna dell’offerta presso il centro di raccolta, una volta che la transazione
sia andata a buon fine. Con tale attributo, successivamente potremmo ricavarci la Data effettiva di
consegna, aggiungendo, alla data in cui si verifica effettivamente la transazione, il numero dei giorni previsti
alla consegna.
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
9
Relazione Centro di Raccolta – Responsabile
Anche in questo caso, come per il Venditore e l’Acquirente, abbiamo deciso di rappresentare l’Indirizzo del
Centro di raccolta mediante due attributi, quali Città e Indirizzo. Diversamente, per il Responsabile abbiamo
deciso di rappresentare l’indirizzo come un’unica stringa, rappresentante sia la Via e il Numero Civico, sia la
Città, dato che ai fini del progetto non è essenziale dividere queste informazioni.
Inoltre, abbiamo supposto che un Responsabile possa gestire uno o più Centri di Raccolta.
Relazioni con Transazione
L’entità Transazione rappresenta un’offerta che è andata a buon fine, e quindi acquistata da un unico
gruppo di acquisto (formato da uno o più acquirenti).
Ogni transazione, e quindi ogni offerta acquistata, sarà consegnata in un unico centro di raccolta.
L’attributo Data, nella relazione Consegna, rappresenta la data di consegna e sarà generato aggiungendo
alla Data in cui si verifica la Transazione, il numero dei giorni previsti alla consegna.
Ogni Transazione, inoltre, afferisce ad un unico Venditore.
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
10
Relazioni tra Acquirente e Messaggio
Ogni acquirente può inviare un solo messaggio per volta, che sarà ricevuto da un solo destinatario, che
ovviamente è un altro acquirente. L’attributo data, nelle due relazioni, rappresenta la data di invio e
ricezione del messaggio.
Relazione di Feedback
Ogni Acquirente può lasciare al massimo un solo Feedback (Voto e Giudizio) relativo alla Transazione. Ogni
Venditore può ricevere da zero a N (pari al numero degli acquirenti del gruppo di acquisto) feedback
relativamente ad una transazione.
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
11
2.4 Schema E-R Concettuale
Combinando lo schema scheletro con i vari pezzi ricavati singolarmente otteniamo il seguente schema E-R:
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
12
2.5 Dizionario dei Dati
Relativo alle Entità
ENTITA’ DESCRIZIONE ATTRIBUTI IDENTIFICATORE
Acquirente
Colui che effettua l’acquisto di una certa quantità dell’offerta, attraverso un gruppo di acquisto
E-mail, Nome, Cognome, Città, Indirizzo,
Indirizzo_fatturazione, Telefono, Cellulare
Venditore
Rappresenta colui che, in qualità di azienda o società, propone un’offerta.
Partita_iva, Ragione_sociale, E-mail,
Città, Indirizzo, URL_web, Telefono, Cellulare,
Categoria1, Categoria2,
Partita_iva
Offerta
Offerta presentata dal venditore e che può essere comprata da un gruppo di acquisto. Rappresenta una certa quantità di un bene.
ID_Offerta, Scadenza, Categoria, Quantità,
Unità_misura, Prezzo_unitario,
Descrizione, Giorni_alla_consegna
ID_Offerta
Transazione
Operazione finanziaria con la quale un gruppo di acquirenti acquista un’offerta
ID_Transazione, Data ID_Transazione
Messaggio
Si tratta di un messaggio privato (stringa di caratteri) che può essere scambiato dagli acquirenti.
ID_mex, Mittente, Destinatario, Oggetto,
Testo ID_mex
Centro Raccolta Il luogo in cui verrà consegnata l’offerta acquistata.
ID_Centro, Nome, Città, Indirizzo
ID_Centro
Responsabile Colui che gestisce il centro di raccolta.
ID_Responsabile, Cognome, Nome, Telefono, Indirizzo
ID_Resposabile
Relativo alle Relazioni
RELAZIONE DESCRIZIONE ATTRIBUTI ENTITA’ COINVOLTE
Acquisto in gruppo Associa un’acquirente con l’offerta e la successiva transazione.
Quantità, Data Acquirente (1,N)
Offerta (0,N) Transazione (1,1)
Invio Associa un acquirente, in qualità di mittente, ad un messaggio.
Data Acquirente (0,N) Messaggio (1,1)
Ricezione Associa un acquirente, in qualità di destinatario, ad un messaggio.
Data Acquirente(0,N) Messaggio (1,1)
Feedback
Associa ad ogni acquirente, la possibilità di lasciare un feedback al venditore, in relazione alla transazione.
Voto, Giudizio Acquirente (0,1)
Transazione (0,N) Venditore (0,N)
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
13
Vendita Associa un’offerta al relativo venditore.
- Venditore (1,N)
Offerta (1,1)
Afferenza Associa una transazione al venditore che ha proposto l’offerta.
- Venditore (0,N)
Transazione (1,1)
Riferimento Associa l’offerta alla successiva transazione, se acquistata.
- Offerta (0,1)
Transazione (1,1)
Consegna
Associa la transazione, e quindi l’offerta acquistata, al centro di raccolta dove verrà consegnata la merce.
Data Transazione (1,1)
Centro Raccolta (1,N)
Gestione Associa un centro di raccolta al diretto responsabile
- Centro Raccolta (1,1)
Responsabile (1,N)
2.6 Regole di Business Dato che il modello E-R è molto efficiente dal punto di vista della rappresentazione dei dati, ma risulta
meno adatto a rappresentare vincoli complessi su di essi, risulta indispensabile corredare il nostro schema
E-R con una documentazione di supporto.
Uno degli strumenti più usati per descrivere le proprietà di un applicazione che non si riescono a
rappresentare direttamente con il modello concettuale è quello delle Regole Aziendali o Business Rules.
REGOLE DI VINCOLO
RV1 Il bene venduto deve afferire alle categorie di vendita del corrispondente venditore.
RV2 Il venditore può vendere al massimo due categorie di bene.
RV3 Il voto numerico deve essere un numero compreso tra 1 e 5.
RV4 L’acquirente può esprimere un solo feedback sul venditore a seguito di una transazione.
RV5 L’acquirente può acquistare un numero di pezzi dell’offerta che va da 1 al numero massimo di pezzi disponibili.
RV6 La transazione deve essere eseguita solo quando la quantità totale di un bene nella domanda coincide perfettamente con quella dell’offerta.
REGOLE DI DERIVAZIONE
RD1 Il prezzo complessivo dell’intera offerta si calcola moltiplicando il prezzo unitario per il numero di pezzi dell’offerta.
RD2 La data di consegna è calcolata aggiungendo alla data della transazione i giorni previsti per la consegna (specificati in offerta).
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
14
3 Progettazione Logica
3.1 Schema Concettuale – Obiettivi e Strategie
L’obiettivo della progettazione logica è quello di costruire uno schema logico in grado di descrivere, in
maniera corretta ed efficiente, tutte le informazioni contenute nello schema E-R prodotto nella fase di
progettazione concettuale. A Tal proposito, lo schema E-R costruito nella precedente sezione del progetto
va opportunamente ristrutturato per soddisfare due esigenze: quella di “semplificare” la traduzione e
quella di “ottimizzare” il progetto.
Poiché la ristrutturazione può essere in buona misura discussa indipendentemente dal modello logico, è
utile di solito articolare la progettazione logica in due fasi:
Ristrutturazione dello schema Entità-Relazione.
Traduzione verso il modello logico
3.2 Analisi delle prestazioni sullo schema E-R Uno schema E-R può essere modificato per ottimizzare alcuni indici di prestazione del progetto. Si parla di
indici di prestazioni e non di prestazioni in senso stretto poiché le prestazioni di una base di dati non sono
valutabili in maniera precisa in sede di progettazione logica, in quanto dipendenti anche da parametri fisici:
dal sistema di gestione di basi di dati che verrà utilizzato e da altri fattori difficilmente prevedibili in questa
fase. E’ comunque possibile, facendo uso di alcune schematizzazioni, effettuare studi di massima dei due
parametri che generalmente regolano le prestazioni dei due sistemi software:
Costo di una operazione: Valutato in termini di numero di occorrenze di entità e associazioni che
mediamente vanno visitate per rispondere ad un’operazione sulla base di dati.
Traduzione verso il modello logico: Valutato in termini dello spazio di memoria necessario per
memorizzare i dati descritti dallo schema.
Per studiare questi parametri abbiamo costruito, oltre allo schema, delle opportune Tavole - Tavola dei
volumi e Tavola delle operazioni - che riassumono le seguenti informazioni:
Volume dei dati: il numero di occorrenze di ogni entità e relazione dello schema e le dimensioni di
ciascun attributo (di entità o relazione).
Caratteristiche delle operazioni: il tipo dell’operazione (interattiva o batch), la frequenza (numero
media di esecuzioni in un certo intervallo di tempo) e dati coinvolti (entità e/o relazioni).
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
15
Tavola dei Volumi
Concetto Tipo Volume
Venditore E 150.000
Acquirente E 3.000.000
Offerta E 450.000
Transazione E 300.000
Centro di raccolta E 300
Responsabile E 200
Messaggio E 5.000
Vendita R 450.000
Acquisto in gruppo R 22.500.000
Afferenza R 300.000
Riferimento R 300.000
Feedback R 12.000.000
Consegna R 300.000
Gestione R 200
Invia R 5.000
Ricezione R 5.000
Tavola delle Operazioni
Considerazioni sulla Tavola dei Volumi e sulla Tavola delle operazioni
Per quanto riguarda la Tavola dei volumi, abbiamo assunto che, a regime, i Venditori presenti nella base di
dati siano 150.000. Gli Acquirenti, invece, sono 3.000.000. Il numero delle Offerte è pari a 450.000, che
coincide quindi con il volume della relazione Vendita. Il numero dei Centri di raccolta è pari a 300,
supponendo di avere anche più centri di raccolta nella stessa città; tali centri sono gestiti da un numero
totale di Responsabili pari a 200, ipotizzando che un responsabile possa gestire uno o più centri di raccolta.
L’entità Transazione è caratterizzata da un Volume pari a 300.000, e quindi inferiore di un rapporto pari a
2/3 rispetto ad Offerta, indicante che solo una porzione di Offerte pari a poco più della metà viene
realmente acquistata dal Gruppo di Acquisto, diventando una Transazione. Per quanto riguarda la relazione
Acquisto in gruppo, stimando 50 acquirenti per ogni Offerta, abbiamo un Volume pari al prodotto degli
acquirenti (50) per il Numero di Offerte (450.000), ovvero 22.500.000. Ovviamente le relazioni Afferenza,
Riferimento e Consegna sono caratterizzate da un Volume pari al Numero delle Transazioni.
La relazione Feedback, stimando 50 acquirenti per transazione e che l’80 % di questi lasciano il Feedback
(voto e giudizio), è caratterizzata dal volume pari al prodotto degli acquirenti che lasciano il Feedback (40)
per il numero di Transazioni (300.000), ossia 12.000.000.
Abbiamo supposto, inoltre, che il numero dei Messaggi sia pari a 5.000, per questo le relazioni Invio e
Ricezione presentano lo stesso Volume.
Per quanto riguarda la Tavole delle operazioni, è indicato per ogni operazione, il tipo (interattiva o batch) e
la frequenza con cui ipoteticamente potrebbe presentarsi.
Operazione Tipo Frequenza
Op1 I 1/giorno
Op2 B 1/settimana
Op3 B 1/settimana
Op4 B 1/mese
Op5 I 1/giorno
Op6 B 1/mese
Op7 I 50/giorno
Op8 B 1/mese
Op9 I 10.000/giorno
Op10 B 1/mese
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
16
3.3 Ristrutturazione dello schema E-R
Analisi delle Ridondanze
Una ridondanza in uno schema concettuale corrisponde alla presenza di un dato che può essere derivato
(cioè ottenuto attraverso una serie di operazioni) da altri dati.
Allo scopo di evitare tale ridondanza, abbiamo deciso in fase di analisi di non includere nello schema
concettuale un attributo che tenesse conto del Prezzo complessivo dell’Offerta, in quanto deducibile dagli
altri attributi della stessa entità, quali Prezzo e Quantità, attraverso un’operazione di moltiplicazione.
Inoltre, abbiamo tralasciato volontariamente un eventuale attributo che avrebbe tenuto conto del numero
degli acquirenti di un gruppo di acquisto, in quanto può essere derivato contando le occorrenze della
relazione Acquisto in gruppo. Analogamente, abbiamo tralasciato un attributo che avrebbe conteggiato la
quantità totale dei pezzi comprata da un gruppo di acquisto, in quanto avrebbe presentato anche anomalie
di aggiornamento, oltre alla ridondanza.
Dal nostro schema concettuale possiamo notare che l’attributo Data della relazione Consegna genera
ridondanza, in quanto ricavabile dall’attributo Giorni alla consegna dell’entità Offerta e da data dell’entità
Transazione, e considerando che la sua presenza non migliora le prestazioni della nostra base di dati in
quanto non interessa nessun operazione, abbiamo deciso di eliminarlo.
Inoltre, la relazione Afferenza, tra le entità Venditore e Transazione, genera cicli e ridondanza, in quanto il
Venditore viene associato alla Transazione attraverso le relazione Feedback, e dato che non aggiunge
nessun informazione aggiuntiva allo schema, dal punto di vista dei dati, verrà eliminata. La stessa
ridondanza la ritroviamo nelle relazioni Invio e Ricezione, le quali presenterebbero anche le stesse
informazioni. Per questo motivo unifichiamo le due relazioni in un’unica relazione Invio.
Infine, abbiamo eliminato il collegamento tra la relazione Acquisto in gruppo e l’entità Transazione, in
quanto aggiunge ridondanza e maggiore complessità allo schema. Le stesse informazioni possono essere
ottenute attraverso composizione di altre relazioni.
Scelta degli identificatori primari
Di seguito viene riportata una tabella con tutti gli identificatori primari scelti; per entità identificabili
tramite più attributi si è scelto di identificarli con un ID (codice numerico).
ENTITA’ IDENTIFICATORE PRIMARIO
Venditore Partita_iva
Acquirente E-mail
Offerta ID_Offerta
Transazione ID_Transazione
Centro Raccolta ID_Centro
Responsabile ID_Responsabile
Messaggio ID_mex
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
17
Di seguito lo Schema E-R ristrutturato sulla base delle precedenti analisi.
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
18
3.4 Tavole degli Accessi
Operazione 1: Trovare tutti gli acquirenti che hanno effettuato transazioni di beni appartenenti alla
stessa categoria e che hanno espresso valutazioni negative sul venditore (voto < 3).
Concetto Costrutto Accessi Tipo
Offerta E 9.000 L
Riferimento R 6.000 L
Transazione E 6.000 L
Feedback R 12.000 L
Acquirente E 12.000 L
Supponendo di avere circa 50 Categorie totali, gli accessi all’entità Offerta per una data categoria si riducono a 9.000 (450.000/50). Questa Offerte fanno Riferimento a 6.000 Transazioni, dato dal rapporto tra i Volumi di Offerte/Transazioni. Supponendo 50 Acquirenti per ogni transazione, dei quali l’80 % lascia un feedback, abbiamo 240.000 Feedback (sia positivi che negativi). Avendo stimato un 5% di feedback negativo, gli accessi alla relazione Feedback si riducono a 12.000. I successivi accessi all’entità Acquirente saranno minori o uguali a 12.000.
Operazione 2: Trovare tutti i venditori che hanno ricevuto valutazioni negative dagli stessi acquirenti.
Concetto Costrutto Accessi Tipo
Feedback R 600.000 L
Venditore E 7.500 L
Abbiamo 600.000 accessi alla relazione Feedback, in quanto abbiamo supposto che il 5% dei feedback totali siano negativi. Successivamente dobbiamo valutare tutti gli acquirenti che hanno lasciato valutazioni negative su almeno due venditori diversi e accedere ad un massimo di 7.500 (150.000*5%) relativi Venditori.
Operazione 3: Trovare tutti i venditori che hanno ricevuto valutazioni positive dagli acquirenti che
vivono nella stessa città.
Concetto Costrutto Accessi Tipo
Feedback R 11.400.000 L
Acquirente E 2.400.000 L
Venditore E 150.000 L
I Feedback totali sono 12.000.000. Supponiamo che di questi il 95% sia positivo, e quindi 11.400.000. Gli acquirenti che lasciano il feedback sono pari al 80% degli Acquirenti totali, per cui sono richiesti 2.400.000 accessi nell’entità Acquirente per cercare le relative Città. A questo punto dobbiamo trovare le relative Città dei Venditori, per effettuarne il confronto.
Operazione 4: Trovare tutte le coppie di acquirenti che hanno comperato un bene dallo stesso
venditore e che si sono scambiati almeno un messaggio di posta privata.
Concetto Costrutto Accessi Tipo
Messaggio E 5.000 L
Invio R 5.000 L
Acquirente E 5.000 L
Acquisto in gruppo R 5.000 L
Offerta E 100 L
Transazione E 66 L
Avendo supposto un carico di 5.000 messaggi scambiati tra utenti, si devono fare altrettanti accessi alla
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
19
relazione Invio, all’entità Acquirente ed alla relazione acquisto_in_gruppo. Sono necessari 100 accessi ad offerta (5.000 / 50) e dal rapporto Offerta/Transazione = 1,5 si ricava che gli accessi a transazione saranno 66.
Operazione 5: Trovare tutti gli acquirenti che hanno comperato un bene dallo stesso venditore e che
hanno pubblicato un messaggio sulla sua pagina personale.
Concetto Costrutto Accessi Tipo
Feedback R 12.000.000 L
Acquirente E 2.400.000 L
Abbiamo supposto che per ogni transazione, gli acquirenti che lasciano il feedback siano 40. Per cui abbiamo che, per la stessa transazione, 40 acquirenti hanno comperato un bene dallo stesso venditore, e hanno lasciato il giudizio sulla pagina del venditore. Avendo stimato che l’80 % degli acquirenti lascia il feedback, avremo un totale di 2.400.000 accessi all’entità Acquirente.
Operazione 6: Trovare tutti gli acquirenti che nell’ultimo anno solare (es. 29/11/2011 –
29/11/2012) hanno acquistato beni da un solo venditore.
Concetto Costrutto Accessi Tipo
Transazione E 300.000 L
Riferimento R 300.000 L
Offerta E 300.000 L
Acquisto in gruppo R 15.000.000 L
Supponendo un volume di 300.000 transazioni l’anno a cui corrispondono altrettante 300.000 offerte, il numero di accessi ad acquisto in gruppo è dato dalla media di 50 acquirenti per ogni transazione.
Operazione 7: Trovare tutte le offerte della categoria FOTOGRAFIA per le quali la città del venditore
sia Avellino.
Concetto Costrutto Accessi Tipo
Venditore E 450 L
Vendita R 1350 L
Offerta E 27 L
Supponiamo che lo 0,3% dei Venditori sia di Avellino, e quindi avremmo 450 (150.000*0,3%) accessi all’entità Venditore. Sappiamo, dalla tavola dei volumi, che ad ogni Venditore sono associate circa 3 Offerte, per cui servono 1350 (450*3) accessi alla relazione Vendita, per ricavarci le relative Offerte. Infine, supponendo di avere 50 categorie totali, le offerte relative ad una particolare categoria, quale Fotografia, richiedono circa 27 (1.350/50) accessi all’entità Offerta.
Operazione 8: Trovare tutti i venditori per i quali almeno una transazione è stata valutata
positivamente da tutti gli acquirenti che hanno lasciato il feedback.
Concetto Costrutto Accessi Tipo
Feedback R 6.000.000 L
Venditore E 75.000 L
Supponiamo che il 50% delle transazioni totali sono valutate positivamente da tutti gli acquirenti che lasciano il feedback. Per cui abbiamo circa 6.000.000 di accessi alla relazione Feedback. Successivamente dobbiamo valutare tutti i venditori che hanno ricevuto solo valutazioni positive in relazione ad almeno una transazione. Supponiamo un numero massimo di accessi all’entità Venditore pari a 75.000, dato che ogni venditore in media ha venduto 2 offerte, e quindi 2 transazioni.
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
20
Operazione 9: Trovare tutte le offerte della categoria INFORMATICA per le quali il prezzo unitario sia
inferiore a 100€.
Concetto Costrutto Accessi Tipo
Offerta E 3600 L
Supponiamo che il 40% delle Offerte (450.000*40%=180.000) abbia un prezzo unitario inferiore a 100 € e che le categorie siano in tutto pari a 50. Per cui avremmo 3600 (180.000/50) accessi all’entità Offerta.
Operazione 10: Trovare tutti i venditori che nell’ultimo anno solare (es. 29/11/2011 – 29/11/2012)
hanno proposto un’offerta che non ha raggiunto la quantità minima necessaria a completare la
transazione.
Concetto Costrutto Accessi Tipo
Transazione E 300.000 L
Riferimento R 300.000 L
Offerte E 150.000 L
Venditore E 50.000 L
Supponiamo, considerando anche la tavola dei volumi, che 150.000 Offerte su 450.000 non siano state acquistate, e che quindi non facciano riferimento a nessuna Transazione. Per cui, dobbiamo accedere all’entità Transazione, successivamente attraverso la relazione Riferimento, dobbiamo selezionare nell’entità Offerta solo le righe che non fanno riferimento a nessun Transazione, attraverso un operazione di differenza. Successivamente, sapendo che il rapporto tra Venditore/Offerta è pari a 1/3, stimiamo un numero di accessi all’entità Venditore pari a 50.000 (150.000*1/3). In questo caso non abbiamo tenuto in considerazione l’attributo Data (di scadenza), che potrebbe diminuire il numero degli accessi ad ogni singola entità e relazione considerata.
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
21
3.5 Analisi di Normalizzazione La normalizzazione è un procedimento volto all’eliminazione della ridondanza e del rischio di incoerenza del
database. Esistono vari livelli di normalizzazione (forme normali) che certificano la qualità dello schema del
database.
Questo processo si fonda su un semplice criterio: se una relazione presenta più concetti tra loro
indipendenti, la si decompone in relazioni più piccole, una per ogni concetto.
Nel nostro caso, gli attributi Categoria, Descrizione e Unità_misura nell’entità Offerta, possono riferirsi allo
stesso Bene. Questa condizione causerebbe ridondanza, in quanto se uno stesso Bene fosse presente in N
offerte, gli attributi in questione si ripeterebbero N volte.
Per questo motivo abbiamo deciso di scomporre questi concetti in due entità separate, quali Bene e
Offerta.
3.6 Traduzione verso il modello relazionale La seconda fase della progettazione logica corrisponde a una traduzione tra modelli di dati diversi: a partire
dal nostro schema E-R ristrutturato e le successive analisi, costruiamo il nostro schema logico equivalente,
in grado cioè di rappresentare le stesse informazioni.
Schema Relazionale
VENDITORE (Partita_iva, Ragione_sociale,E_mail, Citta, indirizzo, URL_web, Categoria1, Categoria2,
Telefono, Cellulare)
OFFERTA (ID_offerta, Scadenza, Quantita_totale, Prezzo_unitario, Giorni_consegna, Bene, Venditore)
ACQUIRENTE (E_mail, Nome, Cognome, Citta, Indirizzo, Indirizzo_fatturazione, Telefono, Cellulare)
MESSAGGIO (ID_messaggio, Oggetto, Testo, Data, Mittente, Destinatario)
TRANSAZIONE (ID_transazione, Centro_raccolta, Rif_offerta, Data_transazione)
CENTRO_RACCOLTA (ID_centro, Nome, Citta, Indirizzo, Responsabile)
RESPONSABILE (ID_responsabile, Nome, Cognome, Indirizzo, Telefono)
FEEDBACK (Transazione, Votante, Votato, Voto, Giudizio)
ACQUISTO_IN_GRUPPO (Acquirente, Offerta, Quantita, Data_acquisto)
BENE (ID_bene, Categoria, Descrizione, Unita_misura)
Nella traduzione del nostro schema E-R nell’equivalente schema logico, sono stati introdotti i seguenti
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
22
vincoli di integrità referenziale:
Attributo 1 Tabella 1 Attributo 2 Tabella 2
Partita_iva VENDITORE Venditore OFFERTA
Partita_iva VENDITORE Votato FEEDBACK
ID_offerta OFFERTA Offerta ACQUISTO_IN_GRUPPO
ID_offerta OFFERTA Rif_offerta TRANSAZIONE
ID_bene BENE Bene OFFERTA
E_mail ACQUIRENTE Acquirente ACQUISTO_IN_GRUPPO
E_mail ACQUIRENTE Mittente MESSAGGIO
E_mail ACQUIRENTE Destinatario MESSAGGIO
E_mail ACQUIRENTE Votante FEEDBACK
ID_transazione TRANSAZIONE Transazione FEEDBACK
ID_centro CENTRO_RACCOLTA Centro_raccolta TRANSAZIONE
ID_responsabile RESPONSABILE Responsabile CENTRO_RACCOLTA
Schema Logico
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
23
4 Progettazione Fisica
Nell’ambito del processo di progettazione di una base di dati, la fase finale è quella della progettazione
fisica, che, ricevendo in ingresso lo schema logico della base di dati, le caratteristiche del sistema scelto e le
previsioni sul carico applicativo, produce in uscita lo schema fisico della base di dati, costituito dalle
effettive definizioni delle relazioni in forma di istruzioni in SQL e soprattutto dalle strutture fisiche di
accesso utilizzate, con i relativi parametri.
4.1 Creazione del Database in SQL
Script SQL per la definizione del Database Grouppone
Script SQL per la definizione delle Tavole
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
24
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
25
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
26
Implementazione delle regole di Business
Regole di vincolo
[RV1] Il bene venduto deve afferire alle categorie di vendita del corrispondente venditore.
Questo vincolo viene implementato con un trigger - VerificaCategoria - sulla tavola offerta.
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
27
[RV2] Il venditore può vendere al massimo due categorie di bene.
Questo vincolo è stato implementato considerando due attributi distinti (Categoria1 e Categoria2) nella
tabella impiegato.
L’attributo Categoria1 non può essere NULL, mentre Categoria2 può assumere valore NULL.
Inoltre, abbiamo aggiunto un trigger che verifica che le due categorie del venditore, e quindi i due attributi
Categoria1 e Categoria2 della tabella venditore, siano distinti. Questo sia per quanto riguarda la fase di
aggiornamento - ControllaCategoriaUP -che la fase di inserimento - ControllaCategoriaIN -di una nuova
tupla della tabella venditore.
[RV3] Il voto numerico deve essere un numero compreso tra 1 e 5.
Questo vincolo è soddisfatto avendo considerato l’attributo voto di tipo ENUM(‘1’, ‘2’, ‘3’, ‘4’, ‘5’), quindi
potrà assumere solo questi 5 valori.
[RV4] L’acquirente può esprimere un solo feedback sul venditore a seguito di una transazione.
In parte questo vincolo è automaticamente soddisfatto avendo considerato come chiave della tavola
feedback gli attributi che tengono traccia della transazione e dell’acquirente che rilascia il voto; quindi nelle
tabella Feedback, la coppia Transazione, Acquirente è unica quindi non può esserci più di un feedback dello
stesso acquirente per la stessa transazione. Bisogna però assicurare che data una transazione solo gli
acquirenti di tale transazione siano abilitati a rilasciare un feedback su di essa, per far rispettare questo
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
28
vincolo utilizziamo un Trigger - ConvalidaFeedback - sulla tavola feedback.
Al fine di evitare di inserire ogni volta il codice che identifica il venditore (ossia Partita_iva) nella tabella
feedback, abbiamo implementato un trigger aggiuntivo - CreaFeedback - che recupera automaticamente il
codice del venditore dalla tabella offerta.
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
29
[RV5] L’acquirente può acquistare un numero di pezzi dell’offerta che va da 1 al numero massimo di
pezzi disponibili.
Questo vincolo viene implementato tramite un apposito Trigger associato alla tavola Acquisto_in_gruppo.
Se viene inserita una tupla nella tavola acquisto_in_gruppo, con l’attributo quantita maggiore del numero
di pezzi disponibili dell’offerta o minore di 1, allora tale tupla viene eliminata.
Oltre a far rispettare la RV5 questo trigger non consente di effettuare un acquisto qualora l’offerta sia già scaduta. Anche in tal caso la tupla (che identifica l’acquisto, da parte dell’acquirente, di un certo numero di pezzi dell’offerta) viene eliminata.
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
30
[RV6] La transazione deve essere eseguita solo quando la quantità totale di un bene nella domanda
coincide perfettamente con quella dell’offerta.
Un Trigger - VerificaTransazione - associato alla tavola Transazione ci consente di soddisfare il vincolo.
Inoltre, per quanto riguarda la transazione, abbiamo considerato che sia generata manualmente dal
venditore e non in maniera automatica. Quando il gruppo di acquisto ha realmente acquistato l’offerta, il
venditore inserisce le informazioni riguardanti il centro di raccolta corrispondente. La data della transazione
(Timestamp) coincide con il momento in cui il venditore inserisce tali informazioni nella base di dati.
Regole di Derivazione
[RD1] Il prezzo complessivo dell’intera offerta si calcola moltiplicando il prezzo unitario per il numero
di pezzi dell’offerta.
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
31
[RD2] La data di consegna è calcolata aggiungendo alla data della transazione i giorni previsti per la
consegna (specificati in offerta).
Procedure aggiuntive riguardanti il Gruppo di acquisto
Sono state implementate le seguenti procedure che permettono di ottenere informazioni aggiuntive circa il
Gruppo di acquisto.
La seguente procedura - VisualizzaGruppo – permette di ricavare, data la transazione, informazioni sugli
acquirenti facenti parte del gruppo di acquisto e sulla Data di creazione del gruppo.
Da notare che questa procedura chiama la procedura TrovaDataCreazioneGruppo, presentata
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
32
successivamente, che data l’offerta restituisce la Data di creazione del Gruppo.
4.2 Interrogazioni in Algebra Relazionale
Operazione 1: Trovare tutti gli acquirenti che hanno effettuato transazioni di beni appartenenti alla
stessa categoria e che hanno espresso valutazioni negative sul venditore (voto < 3).
FEEDBACK (Transazione, Votante, Votato, Voto, Giudizio)
TRANSAZIONE (ID_transazione, Centro_raccolta, Rif_offerta, Data_transazione)
OFFERTA (ID_offerta, Scadenza, Quantita_totale, Prezzo_unitario, Giorni_consegna, Bene, Venditore)
BENE (ID_bene, Categoria, Descrizione, Unita_misura)
( ( ))
(( ( )) )
(( ( )) )
(( ( )) )
(
( ))
( ( ( )))
Considerazioni: sono elencati tutti gli Acquirenti e la rispettiva Categoria. Per ogni Categoria abbiamo i relativi acquirenti (se più di uno) che hanno acquistato una certa quantità di un bene ed espresso una valutazione negativa.
Operazione 2: Trovare tutti i venditori che hanno ricevuto valutazioni negative dagli stessi acquirenti.
FEEDBACK (Transazione, Votante, Votato, Voto, Giudizio)
( ( ))
(
( ))
( )
( ( ))
Considerazioni: sono elencati tutti gli Acquirenti che hanno lasciato una valutazione negativa per più di un
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
33
venditore, e i rispettivi Venditori.
Operazione 3: Trovare tutti i venditori che hanno ricevuto valutazioni positive dagli acquirenti che
vivono nella stessa città.
VENDITORE (Partita_iva, Ragione_sociale,E_mail, Citta, indirizzo, URL_web, Categoria1, Categoria2,
Telefono, Cellulare)
ACQUIRENTE (E_mail, Nome, Cognome, Citta, Indirizzo, Indirizzo_fatturazione, Telefono, Cellulare)
FEEDBACK (Transazione, Votante, Votato, Voto, Giudizio)
( ( ))
( )
( )
( ( ))
Considerazioni: sono elencati tutti i Venditori, le relative Città e i rispettivi Acquirenti che vivono nella stessa città del venditore e per il quale hanno lasciato un feedback positivo.
Operazione 4: Trovare tutti le coppie di acquirenti che hanno comperato un bene dallo stesso
venditore e che si sono scambiati almeno un messaggio di posta privata.
OFFERTA (ID_offerta, Scadenza, Quantita_totale, Prezzo_unitario, Giorni_consegna, Bene, Venditore)
MESSAGGIO (ID_messaggio, Oggetto, Testo, Data, Mittente, Destinatario)
TRANSAZIONE (ID_transazione, Centro_raccolta, Rif_offerta, Data_transazione)
ACQUISTO_IN_GRUPPO (Acquirente, Offerta, Quantita, Data_acquisto)
(
( )) ( ( ))
((( ( )) )
( )
( )
( )
( ) ( )
( ( ))
Considerazioni: ogni riga della tabella finale contiene la coppia di Acquirenti, nella colonna Mittente e Destinatario, che si sono scambiati almeno un messaggio di posta privata, e il relativo Venditore dal quale hanno comperato entrambi un bene.
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
34
Operazione 5: Trovare tutti gli acquirenti che hanno comperato un bene dallo stesso venditore e che
hanno pubblicato un messaggio sulla sua pagina personale.
ACQUIRENTE (E_mail, Nome, Cognome, Citta, Indirizzo, Indirizzo_fatturazione, Telefono, Cellulare)
FEEDBACK (Transazione, Votante, Votato, Voto, Giudizio)
( ( ))
(
( ))
( ( ))
( )
Considerazioni: per ogni Venditore sono elencati i rispettivi clienti che hanno acquistato un bene (anche per diverse transazioni) e che hanno anche lasciato il feedback. Ogni riga contiene le informazioni sull’Acquirente (E_mail, Nome, Cognome) e la Partita Iva del rispettivo venditore, nella colonna denominata Venditore.
Operazione 6: Trovare tutti gli acquirenti che nell’ultimo anno solare (es. 29/11/2011 – 29/11/2012)
hanno acquistato beni da un solo venditore.
OFFERTA (ID_offerta, Scadenza, Quantita_totale, Prezzo_unitario, Giorni_consegna, Bene, Venditore)
TRANSAZIONE (ID_transazione, Centro_raccolta, Rif_offerta, Data_transazione)
ACQUISTO_IN_GRUPPO (Acquirente, Offerta, Quantita, Data_acquisto)
( ( ))
( )
( ( ))
(
( ))
( ( ))
( )
Considerazioni: dall’insieme totale degli Acquirenti che hanno effettuato un acquisto nell’ultimo anno, sottraiamo gli Acquirenti che hanno effettuato gli acquisti da più di un venditore, e stampiamo le loro E_mail (Acquirente).
Operazione 7: Trovare tutte le offerte della categoria FOTOGRAFIA per le quali la città del venditore
sia Avellino.
VENDITORE (Partita_iva, Ragione_sociale,E_mail, Citta, indirizzo, URL_web, Categoria1, Categoria2,
Telefono, Cellulare)
OFFERTA (ID_offerta, Scadenza, Quantita_totale, Prezzo_unitario, Giorni_consegna, Bene, Venditore)
BENE (ID_bene, Categoria, Descrizione, Unita_misura)
( ( ))
( ( ))
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
35
( ) ( )
( )
Considerazioni: sono elencate le informazioni su tutte le offerte della Categoria FOTOGRAFIA proposte da Venditori di Avellino.
Operazione 8: Trovare tutti i venditori per i quali almeno una transazione è stata valutata
positivamente da tutti gli acquirenti che hanno lasciato il feedback.
VENDITORE (Partita_iva, Ragione_sociale,E_mail, Citta, indirizzo, URL_web, Categoria1, Categoria2,
Telefono, Cellulare)
FEEDBACK (Transazione, Votante, Votato, Voto, Giudizio)
( ( ))
( )
( )
Considerazioni: sono elencate le informazioni su tutti i venditori per i quali sono stati lasciati solo feedback positivi in relazione ad almeno una transazione.
Operazione 9: Trovare tutte le offerte della categoria INFORMATICA per le quali il prezzo unitario sia
inferiore a 100 €.
OFFERTA (ID_offerta, Scadenza, Quantita_totale, Prezzo_unitario, Giorni_consegna, Bene, Venditore)
BENE (ID_bene, Categoria, Descrizione, Unita_misura)
( ( ))
( )
Considerazioni: sono elencate le informazioni su tutte le offerte di INFORMATICA con prezzo unitario minore di 100 €.
Operazione 10: Trovare tutti i venditori che nell’ultimo anno solare (es. 29/11/2011 – 29/11/2012)
hanno proposto un’offerta che non ha raggiunto la quantità minima necessaria a completare la
transazione.
VENDITORE (Partita_iva, Ragione_sociale,E_mail, Citta, indirizzo, URL_web, Categoria1, Categoria2,
Telefono, Cellulare)
OFFERTA (ID_offerta, Scadenza, Quantita_totale, Prezzo_unitario, Giorni_consegna, Bene, Venditore)
TRANSAZIONE (ID_transazione, Centro_raccolta, Rif_offerta, Data_transazione)
( )
( )
( )
( )
Considerazioni: sono elencate tutti i Venditori le cui offerte, nell’ultimo anno solare, non sono state acquistate. Sono state considerate le date di scadenza per non includere, nel risultato, offerte ancora attive.
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
36
4.3 Script SQL per le operazioni
Operazione 1: Trovare tutti gli acquirenti che hanno effettuato transazioni di beni appartenenti alla
stessa categoria e che hanno espresso valutazioni negative sul venditore (voto < 3).
Considerazioni: il risultato della query è una tabella composta da due colonne (Acquirente e Categoria), per ogni categoria vengono elencati tutti gli acquirenti che hanno acquistato almeno un bene afferente a tale categoria ed hanno espresso un voto negativo sul venditore.
Operazione 2: Trovare tutti i venditori che hanno ricevuto valutazioni negative dagli stessi acquirenti.
Considerazioni: si considerano tutti gli acquirenti che hanno lasciato valutazioni negative su almeno due venditori diversi e quindi si estraggono le informazioni relative a tali venditori.
Operazione 3: Trovare tutti i venditori che hanno ricevuto valutazioni positive dagli acquirenti che
vivono nella stessa città.
Considerazioni: il risultato della query è ottenuto estraendo dalla tabella feedback solo i venditori che hanno ricevuto almeno un feedback positivo da un acquirente che vive nella stessa città del venditore.
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
37
Operazione 4: Trovare tutti le coppie di acquirenti che hanno comperato un bene dallo stesso
venditore e che si sono scambiati almeno un messaggio di posta privata.
Considerazioni: questa query fa uso di due viste mostrate sotto. Si possono avere coppie di acquirenti duplicate (ma caratterizzate da diverso venditore) qualora entrambi gli acquirenti abbiano acquistato beni da due o più venditori.
Vista - Acquirenti-Venditori - che restituisce tutti gli acquirenti e i relativi venditori dai quali hanno comperato un bene.
Vista -interazioniAcquirenti- che restituisce tutte le coppie di acquirenti che si sono scambiate un messaggio.
Operazione 5: Trovare tutti gli acquirenti che hanno comperato un bene dallo stesso venditore e che
hanno pubblicato un messaggio sulla sua pagina personale.
Considerazioni: per ogni venditore si verifica se nella tabella feedback ci sono almeno due acquirenti che hanno rilasciato un giudizio testuale su tale venditore (anche in merito a transazioni diverse). La query da come risultato gli acquirenti che soddisfano la suddetta condizione.
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
38
Operazione 6: Trovare tutti gli acquirenti che nell’ultimo anno solare (es. 29/11/2011 – 29/11/2012)
hanno acquistato beni da un solo venditore.
Considerazioni: il risultato della query è ottenuto considerando tutti gli acquirenti che nell’ultimo anno hanno effettuato transazioni ad accezione di quelli che hanno acquistato (sempre nell’ultimo anno) beni da più di un venditore. La soluzione utilizza una vista, di seguito presentata, che restituisce una tabella rappresentate tutti i Venditori e i relativi Acquirenti dell’ultimo anno solare.
Operazione 7: Trovare tutte le offerte della categoria FOTOGRAFIA per le quali la città del venditore
sia Avellino.
Considerazioni: sono elencate le informazioni su tutte le offerte della Categoria FOTOGRAFIA proposte da Venditori di Avellino.
Operazione 8: Trovare tutti i venditori per i quali almeno una transazione è stata valutata
positivamente da tutti gli acquirenti che hanno lasciato il feedback.
Considerazioni: Per ottenere il risultato della query si considerano tutte le coppie transazione-venditore che hanno ricevuto almeno un feedback meno le coppie per la quali si ha almeno un feedback negativo.
Progetto Grouppone 29 novembre 2012
Francesco Cardinale – Paolo Bongo – Antonio Pagliaro
39
Operazione 9: Trovare tutte le offerte della categoria INFORMATICA per le quali il prezzo unitario sia
inferiore a 100 €.
Considerazioni: sono elencate le informazioni su tutte le offerte di INFORMATICA con prezzo unitario minore di 100 €.
Operazione 10: Trovare tutti i venditori che nell’ultimo anno solare (es. 29/11/2011 – 29/11/2012)
hanno proposto un’offerta che non ha raggiunto la quantità minima necessaria a completare la
transazione.
Considerazioni: Al risultato partecipano solo le offerta che sono già scadute e quindi con data di scadenza minore della data corrente e maggiore della (data corrente – 365).