Progetto d'esame del corso di “Basi di Dati e Sistemi Informativi”
Realizzazione del database del sito: Frieund.com
Università degli Studi di Urbino “Carlo Bo”
Facoltà di Scienze e Tecnologie
Corso di Laurea in Informatica Applicata
Anno Accademico 2008/2009
MONACCHI ANDREA - Matricola 227377
“L'ignoranza è l'origine di tutti i mali” Socrate
Progettazione database del sito Frieund.com
Indice:
1. Introduzione............................................................................................................................p.5
L'Europa Il sito frieund.com e la necessità del database
2. Analisi dei requisiti..................................................................................................................p.6
Definizione dei target
Descrizione del problema
Analisi dei problemi
Correzione ambiguità
Glossario dei concetti principali
3. Progettazione concettuale........................................................................................................p.14
sviluppo del modello E/R sui vari blocchi
Integrazione dei blocchi in un unico schema
4. Progettazione logica.................................................................................................................p.19
• ottimizzazione dello schema E/R in base a:
4.1.1. volume dei dati
4.1.2. descrizione delle operazioni
4.1.3. analisi dei costi
•
•
•
•
semplificazione
traduzione
normalizzazione
MySQL:
4.5.1. realizzazione dello schema di database relazionale
4.5.2. definizione delle operazioni
5. Progettazione fisica.................................................................................................................p.61
• analisi del costo di accesso ai dati
6. Implementazione interfaccia....................................................................................................p.72
• descrizione applicazione
• elenco allegati
7. Bibliografia..............................................................................................................................p.80
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 3 di 80
Progettazione database del sito Frieund.com
“Se in un primo momento l'idea non è assurda, allora non c'è nessuna speranza che si realizzi”.
Albert Einstein
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 4 di 80
Progettazione database del sito Frieund.com
1. Introduzione
In questo paragrafo descriveremo velocemente le motivazioni che hanno portato all'implementazione di una base di dati e sui principali obiettivi e vincoli anteposti nella realizzazione di tale progetto.
1.1 L'Europa
Siamo nel 2009, ormai da tempo portiamo nelle nostre tasche spiccioli particolari che richiamano il nome di un continente, da anni non siamo più chiusi ai nostri piccoli confini ma viviamo in un mercato comune, monete che portano i segni di anni di tensioni e di un passato non molto lontano in cui siamo stati nemici almeno una volta, un tentativo di integrazione in un avvenire sempre meno rassicurante per il genere umano che tenta la carta della collaborazione, un integrazione tra milioni di persone, multiculturale e interreligiosa, una sfida del nuovo millennio che ai più scettici può apparire utopica ma che crea stabilità e sicurezza.
Su questo entusiasmo nasce l'idea di Frieund, un sito internet che dovrebbe farsi partecipe proprio di questa voglia di riscatto e di pubblicità della propria terra, seguendo quelle che sono le linee e i principi guida dell'integrazione europea, nel rispetto delle diversità e delle tradizioni che sono la vera ricchezza e il vero patrimonio da custodire gelosamente e costituiscono un passato e una base per il nostro futuro.
In un Europa di 27 paesi, è sempre più attuale il problema e la necessità di un confronto attivo che porti a conoscenza delle problematiche comuni e permetta l'insegnamento di certi principi nelle nuove generazioni. Per tali motivi è stato scelto come target primario l'adolescente, abituato alle nuove tecnologie, creativo e interattivo. Il sito prenderà la forma di un social network mantenendo quello che è il suo scopo di creare scambio di persone e idee.
1.2 La necessità di raccogliere informazioni
Per poter gestire tali servizi è necessario poter memorizzare i dati inerenti le persone e le azioni coinvolte, da qui nasce la necessità di una base di dati informatica (database) che permetta un accesso veloce e semplice a ogni utente remoto che ne faccia richiesta, sia esso in Finlandia o a Cipro.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 5 di 80
Progettazione database del sito Frieund.com
2. Analisi dei requisiti
In questo capitolo definiremo quali sono i nostri target e in quale modo vogliamo ottenere il raggiungimento del loro bisogno emozionale.
2.1 Definizione dei target
Target
Primario(adolescente) Secondario (universitario)
Terziario (famiglia)
Bisogno emozionale
Socializzazione, appartenenza ad un gruppo ed espressione di se stessi
Curiosità, innovazione, moda, alternatività, originalità, Passatempo, relax
esclusività
Stile del servizio
Giovanile, informale, aggregante, un po' aggressivo, ribelle
Dinamico, veloce, esclusivo, Familiare, rassicurante, alla moda, efficiente, semplice ed economico, innovativo
calmo e rilassante
età
11­25 anni
20­30 anni
30­60 anni
Livello reddito
basso
Medio basso
medio
Livello istruzione
Medio / medio basso
Alto
Medio/ medio basso
Occupazione
studente
Studente / ricercatore
Lavoratore (medio)
Frequenza uso internet alta
Medio alta
Medio bassa
Punti di accesso
Casa /scuola
Casa / scuola / wifi campus
casa
Veloc. Connessione
adsl
adsl
Pstn /adsl
Tipo computer
windows
Mac os/ linux / windows
windows
Browser
IE explorer / firefox
Safari/ firefox /IExplorer
IE explorer
Portatile (1280x800)
Fisso (1024x768)
community
Ricerche web
Viaggi, notizie, ricerche
Interessi
Community, messaggistica
Scambi all'estero, community, Ricette, notizie su europa scambio informazioni
e integrazione, normative
Personalità
Curiosa e dinamica, creativa e informale
Ambiziosa e potenzialmente ricca per la creazione di contenuti di buon livello
Profilo demografico
Rapporto con le tecnologie
Risoluz. Monitor
Comportamento online
Portatile/fisso 1280x800 | 1024x768
Attitudini
ANDREA MONACCHI ­ A.A. 2008/2009
Interessata e curiosa ma anche poco informata sulle nuove tecnologie
Pagina 6 di 80
Progettazione database del sito Frieund.com
Target
Primario(adolescente) Secondario (universitario)
Terziario (famiglia)
Stile di vita
Divertimento e tecnologia, giovanile
Relativamente frenetico, internet come passatempo, per Frenetico e impegnato, alcuni come fonte di notizie e internet come passatempo
stile di vita
Comportamento sul sito
Partecipe, dinamico, crea e scambia contenuti
Lettore e talvolta creatore di contenuti
Esperienza on line
Blog, social network, Ricerche web, tecnologia, mail e istant messaging informazioni accademiche
spettatore
Ricerche web, mail, notizie
Dai tre target si intuisce che il sito dovrà:
Rendere l'esperienza del visitatore la più facile, unica ed emotivamente coinvolgente possibile; dovrà dare una sensazione di accoglienza allo studente adolescente, le funzionalità dovranno essere chiare e rapide da raggiungere per l'universitario, che mirerà al conseguimento dell'obiettivo in modo efficace; infine dovrà divenire zona calma e tranquillizzante dove muoversi con serenità nel caso delle famiglie.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 7 di 80
Progettazione database del sito Frieund.com
2.2 Descrizione del problema
Per una maggiore intuitività elenchiamo i servizi offerti sotto forma di tabella:
Servizio / canale associato
Travelling
Hosting (scambi culturali e/o gemellaggi)
Ricette
Specialità dei paesi europei
Interesse
Target 1 e 2
Target 3
Eu!
Eu! City con la visualizzazione delle città
Storia integrazione europea
Le lingue
Le caratteristiche degli stati membri
Sondaggi e valutazione sull'interesse del sito
Interesse generale
Community
Profili degli utenti iscritti (stile Blog)
Target 1 e 2
Pannello di controllo personale
Wow
Foto documentative dei paesi europei
Interesse generale
Riassumendo le sezioni che necessiteranno di un utilizzo del database saranno:
­ Hosting per i dati relativi agli scambi culturali
­ Ricette che conterrà tutti i piatti inseriti dagli utenti
­ Eu! City per la memorizzazione delle città europee e la loro posizione sulla cartina
­ Community che visualizzerà i profili e darà modo di gestire i propri dati
­ Un archivio degli utenti iscritti e dei loro privilegi
­ Wow che conterrà tutte le informazioni relative agli scatti fotografici
Per motivi di carico dei dati è stato deciso di staccare il problema delle foto e affrontarlo in seguito, la nostra base di dati quindi sarà atta all'archiviazione degli utenti e della loro esperienza on line.
Vista la complessità e la partecipazione di componenti di natura diversa nel problema è stata decisa la scomposizione dello stesso in più parti che verranno poi ricomposte in seguito nello schema concettuale complessivo.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 8 di 80
Progettazione database del sito Frieund.com
2.3 Analisi dei problemi
Hosting
* Un utente può ospitare un altro utente
* Ogni utente che ospita ha una sua abitazione
* Ogni abitazione ha un suo indirizzo (che ne permetta la geolocalizzazione), una foto, un numero di posti, un costo per persona al giorno e informazioni aggiuntive
* Ogni abitazione si trova in una città, ogni città è contenuta in una nazione
* Ogni città ha un suo nome, ma città omonime possono trovarsi in nazioni diverse
* Ogni nazione ha un suo codice (vedi codifica nazioni)
* Ogni utente ospite può lasciare una valutazione riferita alla sua esperienza in una abitazione
* Ogni valutazione si compone di un voto (in decimi), un commento e una data di viaggio (arrivo) che permettano la tracciabilità delle esperienze europee degli utenti iscritti.
* Ogni valutazione deve rimanere memorizzata anche se l'utente che la scritta è eliminato
Ricette
* Ogni utente iscritto può inserire un piatto
* Ogni piatto è descritto da un nome, ingredienti ed un procedimento
* Un piatto è tipico di una città.
* Ogni città ha un nome ed è contenuta in una nazione, identificata da un codice (vedi codifica nazioni)
* Ogni piatto può appartenere ad una tra queste categorie: dolci, carne, pesce, pasta, verdure
* Ogni piatto è scritto da un utente identificato dal suo Username
* Ogni piatto è scritto in una lingua
* Ogni lingua ha un suo codice (lo stesso delle nazioni se supponiamo di avere una lingua per nazione)
Community
* Ogni utente (iscritto) ha un suo profilo
* Ogni profilo si compone di un messaggio personale, uno stile, una foto del contatto (vedi paragrafo sui tipi di dato) e un intervista (informazioni aggiuntive)
Utenti iscritti
In questa parte dobbiamo parlare di come raccogliere le informazioni sugli utenti iscritti. *
*
*
*
*
*
Sappiamo che dobbiamo raccogliere utenti di diverse nazioni europee, ogni nazione identificata dalla sua codifica internazionale.
Le credenziali che saranno richieste all'accesso (login) sono Username e Password
I dati personali sono il nome, l'email, la data di nascita, il sesso.
Ogni utente vive in una determinata città, che si trova in una nazione
Ogni utente parla una lingua, ogni lingua è identificata da un codice
Ogni utente ha un diverso privilegio di accesso, distingueremo un Amministratore, un Amministratore Locale e un utente semplice e in base a tale tipologia distingueremo in seguito le operazioni sui dati effettuabili
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 9 di 80
Progettazione database del sito Frieund.com
Città
*
*
*
*
Ogni città ha un nome,
Ogni città è posizionata in una nazione, ogni nazione è identificata da un codice
Posso avere città omonime in nazioni diverse
Ogni città ha un vettore (posx, posy) che permettono di localizzarla su una cartina
2.4 Correzione delle ambiguità intrablocco
In questo paragrafo effettiamo un controllo delle eventuali ambiguità creatisi internamente ogni blocco di problema. Problematica di cui si è comunque tenuto conto anche in fase di analisi dei requisiti, tentando di fornire elementi di base in modo preciso e il più univoco possibile.
Utente: Per Utente si intende un individuo correttamente iscritto e quindi presente nella sezione del database a esso dedicata.
Codice: Per codice si intende un sistema di identificazione univoco, che crei una biezione tra elementi nominati secondo un linguaggio reale e elementi nominati secondo un linguaggio costruito e quindi funzione di quello di partenza.
Nazione: Per nazione si intende un entità politica e fisica che accomuna più utenti, ogni nazione è quindi una partizione dell'insieme utenti iscritti. Per la codifica di tale concetto utilizzeremo le sigle internazionali (vedi sezione sui tipi di dato)
Abitazione
Per foto si intende la posizione sul file system del server di un immagine, puntatore che ne permetta il recupero e la visualizzazione ( vedi operazioni associate)
Per numero di posti si intende un valore intero che esprima la capacità ricettiva e le possibilità di intraprendere rapporti di scambio culturale tra utenti (iscritti).
Con costo per persona al giorno si intende un valore numerico intero che esprima il corrispettivo richiesto affinchè il rapporto di scambio sia possibile (valutato su una sola persona).
Ricette
Per piatto si intende un processo di creazione e composizione di alimenti che porta alla creazione di un unico prodotto.
Per nome di un piatto si intende una stringa di caratteri alfanumerici che serva a riconoscerlo da un altro (piatti con stesso nome differiscono per lingua), è quindi un concetto assunto in maniera diversa se confrontato con quello di nome di utente che non lo identifica univocamente in quanto ripetibile su più individui.
Per categoria di ricette si intende un sottoinsieme dell'insieme dei piatti per cui si attuano simili misure di manipolazione alimentare, piatti quindi accomunati da ingredienti simili o comunque surrogati, che possono essere visti anche identificando la famiglia di piatti.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 10 di 80
Progettazione database del sito Frieund.com
2.5 Glossario dei concetti principali
Hosting
Concetti base
Descrizione
Associazioni
ABITAZIONE
Indirizzo, numero posti disponibili, costo per persona al giorno, foto, informazioni aggiuntive
Utente proprietario (possesso)
Utente ospite (la valuta)
Città (posizione)
CITTA'
nome
Nazione (inserimento)
Abitazione (posizione)
NAZIONE
codice
Città (contenimento)
UTENTE
Username
Abitazione (possesso o ospite)
VALUTAZIONE
Voto, commento, data viaggio
Abitazione (riferimento)
Utente ospite (valutatore)
Ricette
Concetti base
Descrizione
Associazioni
PIATTO
Nome, ingredienti, procedimento
Utente (inseritore)
Paese (tipicità)
Categoria (appartenza)
CITTA'
nome
Piatto (tipicità)
Nazione (inserimento)
NAZIONE
codice
Città (contenimento)
UTENTE
Username
Piatto (inserimento)
LINGUA
codice
Piatto (lingua utilizzata)
Community (profilo utenti)
Concetti base
Descrizione
Associazioni
UTENTE
Username
Profilo (possesso)
PROFILO
Messaggio personale, stile, foto contatto, intervista
Utente (possesso)
Utenti iscritti
Concetti base
Descrizione
Associazioni
UTENTE
Nome, sesso, Username, Password, e­mail, data nascita
Città (residenza)
CITTA'
nome
Utente (luogo di residenza)
Nazione (appartenenza)
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 11 di 80
Progettazione database del sito Frieund.com
Utenti iscritti
NAZIONE
codice
Città (contenimento)
LINGUA
codice
Utente
Città
Concetti base
Descrizione
Associazioni
CITTA'
nome
Contenuta in una nazione
NAZIONE
codice
Contiene le città
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 12 di 80
Progettazione database del sito Frieund.com
2.6 Correzione delle ambiguità interblocco
Nella determinazione dei concetti principali dei blocchi indipendenti è possibile che siano stati inserite delle :
➢
➢
➢
sinonimie e omonimie
concetti troppo astratti
molteplicità di interpretazioni
Ambiguità
Un ambiguità la si riscontra nel concetto di codice, in particolare per quello delle nazioni e delle lingue in cui si suppone ci sia una lingua ufficiale utilizzata per ogni paese, anche se non è detto che un individuo (utente) di un dato paese debba necessariamente inserire contenuti nella lingua ufficiale del suo paese ma gli è in tal modo data possibilità di inserire contenuti ripetuti in diverse lingue o comunque differenti. Il concetto di codice corrisponde per i concetti che ne fanno uso essendo lo stesso tipo di dato.
Sinonimie e omonimie
Una sinonimia sta nel concetto di nome (come già definito trattando le ambiguità intrablocco) dove si fa differenza dal nome di un piatto dal nome di un utente
Un altra sinonimia è il concetto di foto, (foto utente e foto abitazione) entrambi tentano di rappresentare lo stesso concetto, la differenza è nella modalità con cui vi fanno riferimento.
Molteplicità di interpretazioni
Nella fase di analisi è stata evitata la rappresentazione diversa di concetti simili per facilitare le successive fasi di lavoro sui concetti.
Legami interblocco tra concetti base
L' utente è il concetto centrale, inserisce piatti, ospita altri utenti inserendo abitazioni, è ospitato e lascia valutazioni riguardo abitazioni di altri utenti, ha un suo profilo che può modificare in qualsiasi momento e visualizza quelli degli altri.
Tale relazione ci sarà utile nel momento della fusione dei vari schemi concettuali.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 13 di 80
Progettazione database del sito Frieund.com
3. Progettazione concettuale
In questa parte proveremo ad unire i concetti analizzati in precedenza creando legami e costruendo uno schema che descriva graficamente in maniera completa (cioè in modo sufficientemente espressivo nella rappresentazione) i requisiti.
3.1 Sviluppo dello schema E/R sui vari blocchi
Le strategie di sviluppo dello schema sono principalmente quattro:
➢
➢
➢
➢
Top­down, Bottom­up, Inside­out,
Strategia mista
Hosting
Utilizzando la strategia mista costruiamo lo schema E/R
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 14 di 80
Progettazione database del sito Frieund.com
Ricette
Tramite la strategia mista costruiamo lo schema anche per la sezione ricette
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 15 di 80
Progettazione database del sito Frieund.com
Community (profilo utenti)
Allo stesso modo con la strategia mista nel caso del profilo utente
Città
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 16 di 80
Progettazione database del sito Frieund.com
Utenti iscritti
Utilizzando la strategia mista costruiamo lo schema E/R
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 17 di 80
Progettazione database del sito Frieund.com
3.2 Integrazione dei vari blocchi in un unico schema
E' giunto il momento di fondere i schemi creati in un unico modello concettuale, tenendo conto in questa operazione delle problematiche e dei conflitti che potrebbero generarsi
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 18 di 80
Progettazione database del sito Frieund.com
4. Progettazione Logica
La progettazione logica è la serie di operazioni che ci permetterà di ricavare dal nostro modello concettuale (schema E/R) un modello logico basato su una particolare struttura, (reticolare, gerarchica, relazionale, ad oggetti).
La progettazione logica quindi ci seguirà nella realizzazione effettiva della nostra base di dati, che per la scelta del linguaggio di interrogazione sarà di tipo relazionale. Avremo quindi un effettiva traduzione del modello concettuale che passerà in quattro fasi distinte:
➢
Ottimizzazione
Dove valuteremo le prestazioni tenendo conto del volume dei dati e delle operazioni su essi
➢
Semplificazione
Dove tenteremo di eliminare i costrutti che rendono diversi i due modelli
➢
Traduzione
Che ci permetterà di passare dal modello E/R a quello relazionale associato
➢
Normalizzazione
Dove analizzeremo le dipendenze funzionali e tenteremo di prevenire eventuali anomalie e ridondanze dei dati
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 19 di 80
Progettazione database del sito Frieund.com
4.1 Ottimizzazione
Tentiamo di valorizzare il nostro schema concettuale quantificando il volume dei dati che lo popoleranno e le operazioni che agiranno su di essi.
4.1.1 Volume dei dati
Stime:
* ipotizzo 1.000.000 di utenti, ognuno con un profilo,
* 1% degli utenti dispone di un abitazione per il servizio di hosting
* lo 0,5 % degli utenti inserisce ricette
* il 5% degli utenti è ospitato da un altro
* Esistono circa 80.000 città in Europa (8.104 solo in Italia)
Stime di accesso dei relativi target:
Target 1
Spesso on line
1 accesso al giorno
Target 2
Target 3
Ogni tanto on line
1 accesso ogni 3 giorni
On line raramente
1 accesso ogni 15 giorni
La popolazione è suddivisa nel modo:
65% al target 1
20% al target 2
15% al target 3
Nel caso di 1.000.000 di utenti stimati avremo:
650.000 * 1 +
200.000 * 0,33 +
150.000 * 0,066 =
650.000 + 66.000 + 9900 = 725.900 accessi/giorno
Di questi considereremo un 20% inattivo, cioè 200.000 persone inattive su 1.000.000, persone regolarmente iscritte ma che non usufruiscono in modo continuo (e protratto nel tempo cosi che la frequenza ne dia un significativo apporto) del servizio.
Avremo:
520.000 utenti del target 1
160.000 utenti del target 2
120.000 utenti del target 3
Tale stima diminuisce il calcolo degli accessi giornalieri rispetto a quella precedentemente elaborata.
520.000 + (160.000 * 0,33) + (120.000 * 0,066) = 520.000 + 52.800 + 7.920 = 580.720 acc./giorno
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 20 di 80
Progettazione database del sito Frieund.com
Ora riportiamo le stime effettuate sul nostro schema concettuale: ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 21 di 80
Progettazione database del sito Frieund.com
4.1.2 descrizione delle operazioni
Stima della frequenza delle operazioni
Il calcolo è effettuato su 1.000.000 di utenti e 580.720 accessi giornalieri (vedi volume dati)
Utilizzando il numero di accessi costruisco una serie di schemi (con l'idea delle applicazioni che gestiranno i dati) come percorso dell'utente da una operazione (pagina) all'altra.
La figura sopra è un esempio della metodologia utilizzata per il calcolo (prima in percentuale e poi sugli accessi) della frequenza di attivazione.
Tipo di operazione
Stima attivazione
Visualizzazione abitazioni presenti in una data città
Visualizzazione valutazioni su una data abitazione
Visualizzazione abitazione di un dato utente proprietario
25% visualizzazione città
70% visualizzazione abitazione
30% visualizzazione città
Inserimento piatto tra ricette
Visualizzazione piatti di una certa città
Visualizzazione piatti inseriti da un utente
0,001% accessi
15% visualizzazione città
3% visualizzazione profilo utente
Visualizzazione del profilo di un utente
Visualizzazione utenti di una certa città
Aggiornamento del proprio profilo
3 * numero accessi al giorno
60% visualizzazione città
30% accessi
Login all'area riservata
Iscrizione al sito (inserimento nuovo utente)
Rimozione di un utente e aggiornamento del database
100% accessi
Visualizzazione di una città di una nazione sulla cartina
60% accessi
ANDREA MONACCHI ­ A.A. 2008/2009
1/1.000.000 accessi
Pagina 22 di 80
Progettazione database del sito Frieund.com
Elenco delle operazioni suddivise per area tematica:
Hosting
Descrizione operazione
Tipo accesso
Freq. attivazione
Visualizzazione abitazioni presenti in una data città
Visualizzazione valutazioni su una data abitazione
Visualizzazione abitazione di un dato utente proprietario
R
R
R
87.108
73.170
104.529
Tipo accesso
Freq. accesso
R/W
R
R
5,80
52.264,8
52.264,8
Tipo accesso
Freq. accesso
R
R
R/W
1.742.160
209.059,2
174.216
Tipo accesso
Freq. accesso
R
R/W
R/W
580.720
*
0,58
Descrizione operazione
Tipo accesso
Freq. accesso
Visualizzazione di una città di una nazione sulla cartina
R
348.432
Ricette
Descrizione operazione
Inserimento piatto tra ricette
Visualizzazione piatti di una certa città
Visualizzazione piatti inseriti da un utente
Community (profilo utente)
Descrizione operazione
Visualizzazione del profilo di un utente
Visualizzazione utenti di una certa città
Aggiornamento del proprio profilo
Utenti iscritti
Descrizione operazione
Login all'area riservata
Iscrizione al sito
Rimozione di un utente e aggiornamento del database
Città
*Non è possibile stabilire a priori il numero di ripetizioni di tale funzione dato che non si può prevedere il successo o l'insuccesso di un sito. Si può però immaginare che esso assuma una legge esponenziale che all'aumentare degli iscritti comporta un aumento di nuovi utenti aggregati alla community.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 23 di 80
Progettazione database del sito Frieund.com
4.1.3 Analisi dei costi
Prima di passare alla quantificazione del costo delle operazioni definite possiamo provare a ridurre la complessità di alcune porzioni del nostro schema.
Nel caso delle associazioni quali ad esempio la lingua (di cardinalità 1:1) è possibile semplificare il modello concettuale presentato sostituendo tale concetto (rappresentato come entità) con un attributo, riducendo cosi il costo di attraversamento e recupero dei dati.
Tale semplificazione non comporta modifiche sostanziali in quanto le cardinalità delle associazioni sono 1:1 e non sono stati posti vincoli sulla permanenza di tali dati nel database durante la fase di analisi dei requisiti, vincolo che sarebbe comunque rispettabile introducendo opportuni accorgimenti nelle applicazioni di gestione dei dati.
Osservando lo schema notiamo come tale situazione si presenta in modo simile per il concetto di città e nazione.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 24 di 80
Progettazione database del sito Frieund.com
Presentiamo ora di seguito le operazioni con i relativi costi
Hosting
Operazioni
Schema di navigazione
Visualizzazione abitazioni presenti in una data città
Tipo accesso
CITTA'
1 r
locata
0,125 r
ABITAZIONE
0,125 r
Totale
1,25 r
Totale * frequenza attivazione
108.885 r / giorno
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 25 di 80
Progettazione database del sito Frieund.com
Operazioni
Schema di navigazione
Visualizzazione valutazioni su una data abitazione
Tipo accesso
UTENTE
1 r
ha
1 r
ABITAZIONE
1 r
riferita
4 r
VALUTAZIONE
4 r
Totale
11 r
Totale * frequenza attivazione
804.870 r / giorno
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 26 di 80
Progettazione database del sito Frieund.com
Operazioni
Schema di navigazione
Visualizzazione abitazione di un dato utente proprietario
Tipo accesso
UTENTE
1 r
ha
1 r
ABITAZIONE
1 r
locata
1 r
CITTA'
1 r
Totale
5 r
Totale * frequenza di attivazione
522.645 r / giorno
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 27 di 80
Progettazione database del sito Frieund.com
Ricette
Operazioni
Schema di navigazione
Inserimento piatto tra ricette
Tipo accesso
PIATTO
1 w
CITTA'
1 r
tipico
1 w
inserisce
1 w
Totale
7 r
Totale * frequenza attivazione
7 * 5,80 = 40,6 r / giorno
Visualizzazione piatti di una certa città
Tipo accesso
CITTA'
1 r
tipico
0,0625 r
PIATTO
0,0625 r
Totale
1,125 r
Totale * frequenza di attivazione
58.797,9 r / giorno
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 28 di 80
Progettazione database del sito Frieund.com
Operazioni
Schema di navigazione
Visualizzazione piatti inseriti da un utente
Tipo accesso
UTENTE
1 r
inserisce
0,005 r
PIATTO
0,005 r
Totale
1,01 r
Totale * frequenza di attivazione
52.787,448 r / giorno
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 29 di 80
Progettazione database del sito Frieund.com
Community (profilo utente)
Operazioni
Schema di navigazione
Visualizzazione del profilo di un utente
Tipo accesso
UTENTE
1 r
vive
1 r
CITTA'
1 r
ha
1 r
PROFILO
1 r
Totale
5 r
Totale * frequenza attivazione
8.710.800 r / giorno
Visualizzazione utenti di una certa città
Tipo accesso
CITTA'
1 r
vive
12,5 r
UTENTE
12,5 r
Totale
26 r
Totale * frequenza di attivazione
5.435.539,2 r / giorno
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 30 di 80
Progettazione database del sito Frieund.com
Operazioni
Schema di navigazione
Aggiornamento del proprio profilo
Tipo accesso
UTENTE
1 r
ha
1 r
PROFILO
1 r
1 w
Totale
5 r
Totale * frequenza di attivazione
871.080 r / giorno
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 31 di 80
Progettazione database del sito Frieund.com
Utenti
Operazioni
Schema di navigazione
Login all'area riservata
Tipo accesso
UTENTE
1 r
Totale
1 r
Totale * frequenza attivazione
580.720 r / giorno
Rimozione utente dal sito
Tipo accesso
UTENTE
1 r + 1 w
ha
1 r
PROFILO
1 r + 1 w
vive
1 r + 1 w
ha
1 r + 1 w
ABITAZIONE
1 r + 1 w
Totale
16 r
Totale * frequenza attivazione
9,28 r / giorno
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 32 di 80
Progettazione database del sito Frieund.com
Operazioni
Schema di navigazione
Iscrizione al sito (inserimento nuovo utente)
Tipo accesso
Disponibilità
username
UTENTE
1 r
Inserimento
utente
UTENTE
1 w
vive
1 w
Inserimento
profilo
ha
1 w
PROFILO
1 w
Totale
9 r
Il numero di letture nel caso della disponibilità dello username dipende fortemente dal numero di tentativi.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 33 di 80
Progettazione database del sito Frieund.com
Eu! City
Operazioni
Schema di navigazione
Visualizzazione di una città sulla cartina
Tipo accesso
CITTA'
1 r
Totale
1 r
Totale * frequenza attivazione
1 * 348.432 = 348.432 r / giorno
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 34 di 80
Progettazione database del sito Frieund.com
Valutazione sul partizionamento dell'entità utente
Operazioni
Visualizza abitazioni di un dato utente
Visualizza piatti inseriti da un utente
Visualizza il profilo di un utente
Aggiornamento del profilo di un utente
Visualizza utenti di una certa città
Login all'area riservata
Iscrizione al sito
Rimozione di un utente e aggiornamento database
Note
Non fanno uso dell'attributo password ma si servono del campo username e nel caso della visualizzazione del profilo utente anche di alcuni dati personali.
Fanno uso del campo password
Possiamo pensare di scorrelare le due entità dati personali e dati accesso in quanto solo le operazioni di iscrizione al sito e di rimozione utente iscritto fanno uso degli attributi di entrambe le entità, operazioni che non sono invocate frequentemente. ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 35 di 80
Progettazione database del sito Frieund.com
Tale modifica però non fa che peggiorare la nostra (comunque frequente) operazione di login all'area riservata. Operazione
Login all'area riservata
Senza partizionamento
UTENTE 1 r
Con partizionamento
DATI PERSONALI
1 r
e
1 r
DATI ACCESSO
1 r
580.720 *
3 r
totale
1.742.160 r/day
totale: 580.720 r / day
Nota:
Tale modifica nel mio schema comporta un peggioramento delle prestazioni in quanto devo comunque leggere la chiave tra i dati personali
Dove sta la convenienza del nostro partizionamento se non fa che peggiorare il costo di accesso ai dati interessati dalle operazioni?
Per risolvere questo problema possiamo pensare ad un attributo derivato e ridondato nell'entità dati accesso, in sostituzione della chiave importata in modo da eliminare l'associazione e la foreign key e riducendo in tal modo i costi di accesso in modo drastico.
Quanto assunto comporta quindi modifiche importanti al costo di accesso ai dati.
Ovviamente le operazioni che risulteranno avere un incremento significativo nelle performance saranno quelle che fanno uso del campo password che troveranno una tabella più snella su cui agire.
Login all'area riservata
DATI ACCESSO
1 r
580.720 * 1
580.720 r/day
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 36 di 80
Progettazione database del sito Frieund.com
Iscrizione utente
Disponibilità username
Inserimento utente
DATI ACCESSO
1 r
DATI ACCESSO
1 w
identità
1 w
DATI PERSONALI
1 w
vive
1 w
ha
1 w
PROFILO
1 w
Inserimento profilo
13 r
Rimozione utente
Nel caso della rimozione di un utente i dati di accesso sono comunque mantenuti memorizzati per dare possibilità di poter riaccedere al sito in futuro e riattivare i propri dati personali. Quindi in questo caso il costo dell'operazione di rimozione rimane invariato.
Per concludere, il partizionamento e scorrelazione dell'entità utente apporta un lieve e trascurabile aumento di accessi al disco solo nel caso di un operazione poco frequente quale è quella di iscrizione. Un prezzo da pagare se rapportato con il miglioramento prestazionale raggiunto nell'operazione di login.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 37 di 80
Progettazione database del sito Frieund.com
4.2 Semplificazione
Il processo di semplificazione comprende:
➢ semplificazione delle gerarchie
➢ semplificazione degli attributi composti
➢ semplificazione degli attributi ripetuti
➢ semplificazione degli identificatori esterni
Semplificazione delle gerarchie
Hosting
Tornando allo schema E/R pre­ottimizzazione notiamo come sia stata introdotta una gerarchia.
Tale accorgimento è stato pensato per dare maggiore espressività al nostro modello ma non era richiesto da alcun requisito. Nella fase di semplificazione notiamo come questo possa causare un inutile aumento del costo dei dati. La gerarchia è inoltre parziale e sovrapposta. Proviamo a semplificare tali concetti collassandoli nell'entità padre, non introdurremo alcun selettore in quanto le specializzazioni sono sovrapposte e hanno le stesse caratteristiche
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 38 di 80
Progettazione database del sito Frieund.com
Utente
La gerarchia è completa ed esclusiva posso quindi pensare di rimuovere l'entità padre scorrelando le specializzazioni.
Questo garantirebbe una suddivisione del carico dei dati su più porzioni dello schema.
Visto però che il numero di “amministratori” e “amministratori locali” sarà molto basso ci ritroveremo comunque con l'entità utente semplice con un numero di istanze pressochè uguale a quello di “dati accesso”.
L'intuizione ci spinge ad un altra possibilità, collassare la gerarchia in una singola entità, introducendo un attributo selettore finalizzato alla diversificazione e al riconoscimento dei diversi concetti. Chiameremo tale attributo “privilegio”.
Questa modifica non comporta alcun peggioramento prestazionale del nostro schema E/R (a parte il peso dell'entità che aumenta a causa della memorizzazioni dei dati relativi all'attributo privilegio). Non è necessario ricalcolare perciò il costo di accesso per le varie operazioni che fanno uso di tale porzione di schema.
Ricette
La gerarchia è totale ed esclusiva e questo ci permette di usare una qualsiasi delle 3 soluzioni proposte.
Per la scelta dovremo valutare la differenza di prestazioni che la differente configurazione comporta. Scartiamo la 3° soluzione che prevede la sostituzione della gerarchia con associazioni e risulta costosissima nelle operazioni che attraversano tutte le specializzazioni.
Visto che le nostre operazioni fanno uso di tutte le specializzazioni risulta costosa anche la soluzione che prevede la rimozione dell'entità padre. La soluzione meno costosa sotto il profilo dell'ottimizzazione risulta perciò la 1°, collassiamo la gerarchia in una sola entità dove introdurremo l'attributo “categoria” come selettore.
Questa analisi non tiene conto della dimensione di ogni istanza dell'entità per cui in tale calcolo non ci è possibile capire se è conveniente l'utilizzo della prima soluzione o se a discapito del numero di accessi possiamo alleggerire la dimensione dell'entità suddividendo le istanze nelle varie specializzazioni.
Seguendo la prima soluzione, come è stato fatto, non avremo cambiamenti sul costo delle operazioni calcolate precedentemente
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 39 di 80
Progettazione database del sito Frieund.com
Semplificazione degli identificatori esterni
La seconda operazione comporta una modifica sulle operazioni di accesso. Dobbiamo perciò rivalutarne i costi:
Visualizzazione abitazione di un utente proprietario
Visualizzazione valutazioni su una abitazione
ABITAZIONE
1 r
ABITAZIONE
1 r
locata
1 r
riferita
4 r
CITTA'
1 r
VALUTAZIONE
4 r
3 r
Costo pre­semplificazione
5 r
9 r
Costo pre­semplificazione
11 r
Totale * frequenza di attivazione
Totale * frequenza di attivazione
5 * 104.529 = 522.645 r /day
9 * 73.170 = 658.530 r /day
Tale modifica comporta quindi una riduzione del costo delle operazioni.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 40 di 80
Progettazione database del sito Frieund.com
4.3 Traduzione
I processi di ottimizzazione e traduzione ci hanno portato al seguente schema concettuale:
Procediamo con il processo di traduzione:
Città(nome, nazione, posx, posy)
Dati_accesso(username, password, privilegio)
Utente(username, nome, sesso, lingua, data_nascita, mail, nome_città, nazione)
Piatto(nome, lingua, categoria, procedimento, ingredienti, nome_città, nazione, utente)
Profilo(utente, messaggio_personale, intervista, foto, stile)
Valutazione(ospite, data, voto, commento, proprietario, indirizzo)
Abitazione(proprietario, indirizzo, costo_giorno, numero_posti, informazioni_aggiuntive)
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 41 di 80
Progettazione database del sito Frieund.com
4.4 Normalizzazione
Analizzando gli schemi di relazione notiamo essi siano già in 3NF:
Città(nome, nazione, posx, posy)
Dati_accesso(username, password, privilegio)
Utente(username, nome, sesso, lingua, data_nascita, mail, nome_città, nazione)
Abitazione(proprietario, indirizzo, foto, costo_giorno, numero_posti, informazioni_aggiuntive, nome_città, nazione)
Piatto(nome, lingua, categoria, ingredienti, procedimento, nome_città, nazione, utente)
Profilo(utente, messaggio_personale, intervista, foto, stile)
Valutazione(ospite, data, voto, commento, proprietario, indirizzo)
Profilo, utente, dati_accesso sono intrinsecamente in seconda forma normale in quanto la chiave è attributo semplice. Nel caso degli altri schemi di relazione è necessario controllare le dipendenze funzionali e la presenza di eventuali determinanti sottoinsieme della chiave.
La terza forma normale tenta di risolvere la condizione limite della seconda forma normale per cui anche in presenza di identificatore semplice possiamo avere determinanti che comportano dipendenze di attributi da altri attributi non chiave. (dipendenza transitiva)
Analizzando proprio tali dipendenze è stato verificato proprio come gli schemi siano in 3NF, questo anche grazie ad un attenta pianificazione e progettazione nelle fasi precedenti che hanno permesso di evitare ridondanze o situazioni anomale che si sarebbero potute presentare in fase di aggiornamento dei dati.
Possiamo anche verificare se i nostri schemi sono in forma normale di Boyce Codd, per raggiungere tale condizione dovremo garantire che ogni determinante nel nostro schema possa svolgere anche funzione di chiave (chiave candidata). Scorrendo i vari schemi si può notare come essi non abbiano determinanti tra i vari attributi. Gli schemi sono quindi anche in forma normale di Boyce­Codd.
La procedura di normalizzazione quindi non ha comportato modifiche al nostro schema relazionale.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 42 di 80
Progettazione database del sito Frieund.com
4.5 MySQL
MySql è un RDBMS (sistema di gestione di database relazionali) che prevede l'utilizzo del linguaggio di interrogazione, definizione e manipolazione Sql per la gestione dei dati. Dagli schemi di relazione ottenuti in fase di traduzione e normalizzazione dovremo implementare tali concetti in tabelle.
Prima di tutto dobbiamo però definire domini e tipi di dato che andranno a popolare i campi della nostra tabella.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 43 di 80
Progettazione database del sito Frieund.com
4.5.1 Realizzazione dello schema di database relazionale
In questa parte definiamo la struttura delle nostre tabelle, con molta accortezza per evitare inutili sprechi di memoria
Città
Attributo
Tipo
Proprietà
nome
Varchar(40)
PRIMARY KEY
nazione
Char(3)
PRIMARY KEY
posx
Unsigned smallint(4)
NOT NULL
posy
Unsigned smallint(4)
NOT NULL
Attributo
Tipo
Proprietà
username
Varchar(30)
PRIMARY KEY
password
Char(40)
NOT NULL
privilegio
Enum('A','L','S')
NOT NULL
Attributo
Tipo
Proprietà
username
Varchar(30)
PRIMARY KEY
nome
Varchar(30)
NOT NULL
sesso
Enum('M','F')
NOT NULL
lingua
Char(3)
NOT NULL
data_nascita
date
NOT NULL
mail
Varchar(40)
NOT NULL
nome_città
Varchar(40)
NOT NULL
nazione
Char(3)
NOT NULL
Attributo
Tipo
Proprietà
utente
Varchar(30)
PRIMARY KEY
messaggio_personale
text
NOT NULL
intervista
Unsigned smallint(4)
NOT NULL
foto
Unsigned smallint(1)
NOT NULL
stile
Unsigned tinyint(2)
NOT NULL
dati_accesso
utente
profilo
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 44 di 80
Progettazione database del sito Frieund.com
abitazione
Attributo
Tipo
Proprietà
pro prietario
Varchar(30)
PRIMARY KEY
ind irizzo
Varchar(60)
PRIMARY KEY
foto
Char(14)
NOT NULL
costo_giorno
Unsigned smallint(3)
NOT NULL
numero_posti
Unsigned tinyint(2)
NOT NULL
informazioni_aggiuntive
Unsigned smallint(4)
NOT NULL
nome_città
Varchar(40)
NOT NULL
nazione
Char(3)
NOT NULL
Attributo
Tipo
Proprietà
nome
Varchar(40)
PRIMARY KEY
lin gua
Char(3)
PRIMARY KEY
categoria
Enum('V','D','C','F','P')
NOT NULL
ingredienti
text
NOT NULL
procedimento
text
NOT NULL
nome_città
Varchar(40)
NOT NULL
nazione
Char(3)
NOT NULL
utente
Varchar(30)
NOT NULL
Attributo
Tipo
Proprietà
ospite
Varchar(30)
PRIMARY KEY
data
date
PRIMARY KEY
voto
Unsigned tinyint(2)
NOT NULL
commento
text
NOT NULL
proprietario
Varchar(30)
NOT NULL
indirizzo
Varchar(60)
NOT NULL
piatto
valutazione
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 45 di 80
Progettazione database del sito Frieund.com
Note sulla struttura e tipi di dato utilizzati
Codice nazione, lingua ­> char(3)
B
DK
D
EL
E
F
IRL
I
L
NL
A
P
FIN
S
UK
BG
CY
CZ
EE
HU
LV
LT
MT
PL
RO
SK
SI
Profilo.foto ­> unsigned smallint(1)
'0'
'1'
'2'
Foto non caricata, contatto maschile
Foto non caricata, contatto femminile
ESEMPIO
Foto caricata dal contatto
Valutazione.voto ­> Unsigned tinyint(2)
è un valore numerico da 0 a 10 che esprime il grado di qualità del rapporto di hosting
abitazione.costo_giorno ­> Unsigned smallint(3)
valore numerico intero(si pone che non siano i centesimi di euro a fare la differenza in un prezzo), il prezzo massimo è quindi 999 euro (cifra comunque teorica e fuori dalla portata dei target assunti)
profilo.stile ­> Unsigned tinyint(2)
valore numerico (fino a 99) che richiama l'identificativo dello stile del profilo di un utente
piatto.categoria ­> Enum('V','D','C','F','P')
richiama le tipologie verdure, dolci, carne, pesce, pasta
intervista ­> Unsigned smallint(4), informazioni_aggiuntive ­> Unsigned smallint(4)
Rappresentano in modo analogo la stessa tipologia di dato, valori numerici di 4 cifre decimali.
Tale valore è poi convertito in codice binario di 2^4=16 cifre e stampato cifra per cifra per valorizzare le domande poste nel profilo utente o nella pagina della abitazione.
Abitazione.foto ­> Char(14)
Mentre nel caso del profilo avevamo una sola foto corrispondente a ogni utente, in questo caso possiamo avere utenti con più abitazioni, il campo foto deve mantenere la relazione con la abitazione ma deve essere necessariamente univoco. È introdotto perciò un codice di identificazione che tiene traccia anche del timestamp di inserimento nel database.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 46 di 80
Progettazione database del sito Frieund.com
Comandi Sql per l'implementazione del database
Tramite la sintassi Sql (structured query language) abbiamo potuto descrivere la struttura del nostro schema relazionale.
Per poter capire tutta la fase di creazione riportiamo il Dump della nostra base di dati.
--MySQL Dump
--- Host: 62.149.150.72
-- Generato il: 22 apr, 2009 at 12:02 AM
-- Versione MySQL: 5.0.68
-- Versione PHP: 5.2.9
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
/*!40101
/*!40101
/*!40101
/*!40101
SET
SET
SET
SET
@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
NAMES utf8 */;
--- Database: `Sql180545_1`
--- ---------------------------------------------------------- Struttura della tabella `abitazione`
-CREATE TABLE IF NOT EXISTS `abitazione` (
`proprietario` varchar(30) collate utf8_bin NOT NULL,
`indirizzo` varchar(60) collate utf8_bin NOT NULL,
`foto` char(14) collate utf8_bin NOT NULL,
`costo_giorno` smallint(3) unsigned NOT NULL,
`numero_posti` tinyint(3) unsigned NOT NULL,
`informazioni_aggiuntive` smallint(4) unsigned NOT NULL,
`nome_citta` varchar(40) collate utf8_bin NOT NULL default '',
`nazione` char(3) collate utf8_bin NOT NULL,
PRIMARY KEY (`proprietario`,`indirizzo`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--- Dump dei dati per la tabella `abitazione`
--- ---------------------------------------------------------- Struttura della tabella `citta`
-CREATE TABLE IF NOT EXISTS `citta` (
`nome` varchar(40) collate utf8_bin NOT NULL,
`nazione` char(3) collate utf8_bin NOT NULL,
`posx` smallint(4) unsigned NOT NULL,
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 47 di 80
Progettazione database del sito Frieund.com
`posy` smallint(4) unsigned NOT NULL,
PRIMARY KEY (`nome`,`nazione`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--- Dump dei dati per la tabella `citta`
--- ---------------------------------------------------------- Struttura della tabella `dati_accesso`
-CREATE TABLE IF NOT EXISTS `dati_accesso` (
`username` varchar(30) collate utf8_bin NOT NULL,
`password` char(40) collate utf8_bin NOT NULL,
`privilegio` enum('A','L','S') collate utf8_bin NOT NULL,
PRIMARY KEY (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--- Dump dei dati per la tabella `dati_accesso`
--- ---------------------------------------------------------- Struttura della tabella `piatto`
-CREATE TABLE IF NOT EXISTS `piatto` (
`nome` varchar(40) collate utf8_bin NOT NULL,
`lingua` char(3) collate utf8_bin NOT NULL,
`categoria` enum('V','D','C','F','P') collate utf8_bin NOT NULL,
`ingredienti` text collate utf8_bin NOT NULL,
`procedimento` text collate utf8_bin NOT NULL,
`nome_citta` varchar(40) collate utf8_bin NOT NULL,
`nazione` char(3) collate utf8_bin NOT NULL,
`utente` varchar(30) collate utf8_bin NOT NULL,
PRIMARY KEY (`nome`,`lingua`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--- Dump dei dati per la tabella `piatto`
--- ---------------------------------------------------------- Struttura della tabella `profilo`
-CREATE TABLE IF NOT EXISTS `profilo` (
`utente` varchar(30) collate utf8_bin NOT NULL,
`messaggio_personale` text collate utf8_bin NOT NULL,
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 48 di 80
Progettazione database del sito Frieund.com
`intervista` smallint(4) unsigned NOT NULL,
`foto` tinyint(1) unsigned NOT NULL,
`stile` tinyint(2) unsigned NOT NULL,
PRIMARY KEY (`utente`),
KEY `utente` (`utente`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--- Dump dei dati per la tabella `profilo`
--- ---------------------------------------------------------- Struttura della tabella `utente`
-CREATE TABLE IF NOT EXISTS `utente` (
`username` varchar(30) collate utf8_bin NOT NULL,
`nome` varchar(30) collate utf8_bin NOT NULL,
`sesso` enum('M','F') collate utf8_bin NOT NULL,
`lingua` char(3) collate utf8_bin NOT NULL,
`data_nascita` date NOT NULL,
`mail` varchar(40) collate utf8_bin NOT NULL,
`nome_citta` varchar(40) collate utf8_bin NOT NULL,
`nazione` char(3) collate utf8_bin NOT NULL,
PRIMARY KEY (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--- Dump dei dati per la tabella `utente`
--- ---------------------------------------------------------- Struttura della tabella `valutazione`
-CREATE TABLE IF NOT EXISTS `valutazione` (
`ospite` varchar(30) collate utf8_bin NOT NULL,
`data` date NOT NULL,
`voto` tinyint(2) unsigned NOT NULL,
`commento` text collate utf8_bin NOT NULL,
`proprietario` varchar(30) collate utf8_bin NOT NULL,
`indirizzo` varchar(60) collate utf8_bin NOT NULL,
PRIMARY KEY (`ospite`,`data`),
KEY `ospite` (`ospite`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--- Dump dei dati per la tabella `valutazione`
--
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 49 di 80
Progettazione database del sito Frieund.com
Definizione dei vincoli di integrità referenziale
I vincoli d' integrità referenziale sono proprietà che debbono essere soddisfatte dalle istanze della base di dati. Possiamo vederli come predicati che possono assumere un valore booleano e possono agire sulle istanze del DB che sono considerabili corrette se rispettano tutti i vincoli associati. I vincoli definibili possono essere di tipo intra­relazionale e inter­relazionali.
I primi comprendono la primary key, la unique (che prevede l'unicità di un attributo), e i vincoli di tupla tra cui i vincoli di dominio (es: not null). I vincoli inter­relazionali invece legano più tabelle. Quello più usato è sicuramente il vincolo di integrità referenziale dove gli attributi di una data tabella possono assumere soltanto valori specificati in un altra tabella. In SQL tale proprietà è implementabile con il costrutto foreign key (chiave esterna).
Le foreign key
Riportiamo i vincoli relazionali seguendo quanto visto nelle fasi di semplificazione e traduzione del modello logico.
(figura): Le frecce indicano i campi a cui si fa riferimento
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 50 di 80
Progettazione database del sito Frieund.com
Aggiungere le foreign key sulle tabelle esistenti
Nella creazione della tabella possiamo indicare tali vincoli inter­relazionali. Nel nostro caso ciò non è avvenuto, dovremo quindi provvedere al loro inserimento.
tabella valutazione:
ALTER TABLE `valutazione`
ADD CONSTRAINT `val_ospite` FOREIGN KEY (`ospite`)
REFERENCES `utente` (`username`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `val_indirizzo` FOREIGN KEY (`indirizzo`)
REFERENCES `abitazione` (`indirizzo`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `val_proprietario` FOREIGN KEY (`proprietario`)
REFERENCES `abitazione` (`proprietario`)
ON DELETE CASCADE ON UPDATE CASCADE;
tabella abitazione:
ALTER TABLE `abitazione`
ADD CONSTRAINT `ab_proprietario` FOREIGN KEY (`proprietario`)
REFERENCES `utente` (`username`) ON DELETE NO ACTION ON UPDATE CASCADE,
ADD CONSTRAINT `ab_citta` FOREIGN KEY (`nome_citta`)
REFERENCES `citta` (`nome`) ON DELETE CASCADE ON UPDATE CASCADE;
ADD CONSTRAINT `ab_nazione` FOREIGN KEY (`nazione`)
REFERENCES `citta` (`nazione`) ON DELETE CASCADE ON UPDATE CASCADE;
tabella dati_accesso:
ALTER TABLE `dati_accesso`
ADD CONSTRAINT `da_utente` FOREIGN KEY (`username`)
REFERENCES `utente` (`username`) ON DELETE CASCADE ON UPDATE CASCADE;
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 51 di 80
Progettazione database del sito Frieund.com
tabella profilo:
ALTER TABLE `profilo`
ADD CONSTRAINT `profilo_utente` FOREIGN KEY (`utente`)
REFERENCES `utente` (`username`) ON DELETE CASCADE ON UPDATE CASCADE;
tabella piatto:
ALTER TABLE `piatto`
ADD CONSTRAINT `piatto_utente` FOREIGN KEY (`utente`)
REFERENCES `utente` (`username`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `piatto_citta` FOREIGN KEY (`nome_citta`)
REFERENCES `citta` (`nome`) ON DELETE CASCADE ON UPDATE CASCADE;
ADD CONSTRAINT `piatto_nazione` FOREIGN KEY (`nazione`)
REFERENCES `citta` (`nazione`) ON DELETE CASCADE ON UPDATE CASCADE;
tabella utente:
ALTER TABLE `utente`
ADD CONSTRAINT `utente_citta` FOREIGN KEY (`nome_citta`)
REFERENCES `citta` (`nome`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `utente_nazione` FOREIGN KEY (`nazione`)
REFERENCES `citta` (`nazione`) ON DELETE CASCADE ON UPDATE CASCADE;
Nota: Come possiamo notare molti dei riferimenti vanno verso il campo utente.username, tale attributo per scelta non è modificabile né eliminabile. Ogni utente è infatti riconosciuto da tale dato e nessuno può riutilizzare un username di un altro utente (anche se inattivo). Questa scelta progettuale ci evita molte problematiche legate a inconsistenze di vario genere nonché evita ambiguità agli utenti utilizzatori.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 52 di 80
Progettazione database del sito Frieund.com
4.5.2 Definizione delle operazioni
In questa sezione riportiamo il codice SQL utilizzato per le operazioni trattate precedentemente. Visto che nelle applicazioni useremo una sprintf per realizzare la stringa da inviare al RDBMS è lasciato il segnaposto %s come indicatore di variabile (tipo stringa).
Visualizzazione abitazioni presenti in una data città
SELECT proprietario, indirizzo, costo_giorno, numero_posti
FROM abitazione
WHERE nome_citta = '%s' and nazione = '%s'
In base alla scelta potremmo decidere di effettuare un ordinamento per costo o numero posti
aggiungendo la semplice clausola ORDER BY
Visualizzazione valutazioni su una data abitazione
SELECT ospite, data, voto, commento
FROM valutazione
WHERE proprietario = '%s' and indirizzo = '%s'
ORDER BY data DESC
In questo modo visualizzeremo tutte le valutazioni in ordine cronologico inverso dando così maggiore priorità a quelle più recenti
Visualizzazione abitazione di un dato utente proprietario
SELECT *
FROM abitazione
WHERE proprietario = '%s' and indirizzo = '%s'
Inserimento piatto tra ricette
INSERT INTO piatto
values('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')
INSERT INTO `Sql180545_1`.`piatto` (`nome`, `lingua`, `categoria`, `ingredienti`, `procedimento`, `nome_citta`, `nazione`, `utente`) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'); Visualizzazione piatti di una certa città
SELECT *
FROM piatto
WHERE nome_citta = '%s' and nazione = '%s'
Anche in questo caso nel listing dei risultati potremmo decidere di ordinare i risultati tramite un ORDER BY applicandolo all'attributo piatto.nome
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 53 di 80
Progettazione database del sito Frieund.com
Visualizzazione piatti inseriti da un utente
SELECT *
FROM piatto
WHERE utente = '%s'
ORDER BY nome
Visualizzazione del profilo di un utente
SELECT utente.*, profilo.*
FROM utente JOIN profilo ON (utente.username = profilo.utente)
WHERE utente.username = '%s'
Visualizzazione utenti di una certa città (vedere anche la variante che raffina la ricerca tramite l'età)
SELECT username
FROM utente
WHERE nome_citta = '%s' and nazione = '%s'
ORDER BY username ASC
Aggiornamento del proprio profilo
UPDATE database.profilo
SET messaggio_personale = '%s',
intervista = '%s',
stile = '%s'
WHERE profilo.utente = '%s'
LIMIT 1
Login all'area riservata
SELECT username, privilegio
FROM dati_accesso
WHERE username='%s' and password = '%s'
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 54 di 80
Progettazione database del sito Frieund.com
Visualizzazione di una città di una nazione sulla cartina
SELECT *
FROM citta
WHERE nome='%s' and nazione= '%s'
In realtà tale query è utilizzata dal motore di ricerca per listare le varie città e le operazioni ad esse associate (ricette, utenti, abitazioni, Eu!city). Dati i risultati è scelta l'operazione da compiere su di essa tramite il click su un form che permette di inviare la posizione della città alla pagina php che si occupa di creare l'immagine della cartina.
Iscrizione al sito
Inserimento utente
INSERT INTO utente values(username, nome, sesso, lingua, data_nascita, mail, nome_citta, nazione)
Creazione profilo associato
INSERT INTO profilo values(utente, “new user”, 1023, 1 o 2 in base al sesso, 0 lo stile predefinito)
Inserimento dati di accesso
INSERT INTO dati_accesso values(username, password, 'S')
Per una maggiore chiarezza sono stati riportati in questo caso i nomi dei campi.
'S' forza il privilegio iniziale a quello di utente semplice, sarà poi l'amministratore a poter eventualmente modificarne il valore in seguito. La foto potrà essere presente (0) o assente (1 o 2), nel secondo caso visualizzeremo in base al sesso specificato una diversa immagine alternativa. Per scelta quindi al momento della creazione del profilo è impostata una foto di default che sarà possibile modificare in seguito dal pannello di controllo della propria area personale. Allo stesso modo 0.css rappresenta il foglio di stile predefinito costruito con i colori del sito. Tale valore è comunque modificabile in seguito scegliendo un altro tra i stili proposti e caricati dagli amministratori (locali e totale) nella cartella dedicata.
New user è il messaggio_personale mentre 1023 rappresenta il valore decimale dell'intervista che convertita in binario (bindec(), decbin()) valorizzerà tutte le risposte del profilo appena creato a vero.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 55 di 80
Progettazione database del sito Frieund.com
Per diminuire la complessità delle applicazioni, che trovandosi in un ambiente client/server ne sarebbero molto velocizzate è stato pensato di utilizzare le stored procedures. In particolare per le query più complesse quali la procedura di iscrizione e quella di rimozione dell'utente.
Per invocare la procedura non dovremo far altro che richiamarla e passargli i parametri
Esempio:
mysql_query(sprintf(“CALL iscrizione('%s', '%s', '%s', '%s', '%s', '%s', '%s',
'%s', '%s')”, $username, $nome, $sesso, $lingua, $data_nascita, $mail,
$nome_citta, $nazione, $password));
Iscrizione al sito
create procedure iscrizione (IN username CHAR(30),
IN nome CHAR(30),
IN sesso CHAR(1),
IN lingua CHAR(3),
IN data_nascita CHAR(8),
IN mail CHAR(40),
IN nome_citta CHAR(40),
IN nazione CHAR(3),
IN pwd CHAR(40))
BEGIN
DECLARE foto int;
INSERT INTO utente values(username, nome, sesso, lingua, data_nascita, mail,
nome_citta, nazione);
IF sesso = 'M' THEN SET foto = 1;
ELSE SET foto = '2';
END IF;
INSERT INTO profilo values(username, 'new user', '1023', foto, '0');
INSERT INTO dati_accesso values(username, pwd, 'S');
END //
(usando come separatore il doppio slash)
Ovviamente essendo dichiarato un vincolo di chiave importata sulla città l'inserimento dell'utente sarà bloccato nel caso fosse indicata una città diversa da quelle presenti nella tabella. Nella fase di analisi e progettazione è stato deciso di inserire tutte le città prima del lancio del sito in modo che all'arrivo i vari utenti trovino le varie città inserite. Ciò garantisce consistenza e univocità nella rappresentazione dei vari enti locali e migliora quindi le applicazioni di ricerca e selezione basate su essi. Potremmo altresì definire una versione alternativa che inizialmente ci aiuti nell'inserimento di tale onere in modo che ogni utente in fase di iscrizione controlli se la città indicata esista o meno e in caso negativo provvedere all'inserimento. In tal caso non avremo alcun problema legato ai vincoli di integrità che aggireremo semplicemente.
Tale soluzione può essere utile anche nelle nostre applicazioni presentate in sede di esame dove non si sarà per ovvi motivi provveduto all'inserimento di tutte le città europee.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 56 di 80
Progettazione database del sito Frieund.com
Iscrizione al sito e inserimento citta (per mantenere la consistenza)
CREATE PROCEDURE `iscrizione`(IN username CHAR(30), IN nm CHAR(30), IN sesso
CHAR(1), IN lingua CHAR(3), IN data_nascita CHAR(8), IN mail CHAR(40), IN
nome_citta CHAR(40), IN nation CHAR(3), IN pwd CHAR(40))
BEGIN
DECLARE foto int;
DECLARE esiste int;
SELECT count(*) INTO esiste FROM citta WHERE nome = nome_citta and nazione =
nation;
START TRANSACTION;
IF (esiste = 0) THEN
INSERT INTO citta VALUES(nome_citta, nation, '0', '0');
END IF;
INSERT INTO utente values(username, nm, sesso, lingua, data_nascita, mail,
nome_citta, nation);
IF sesso = 'M' THEN SET foto = 1;
ELSE SET foto = '2';
END IF;
INSERT INTO profilo values(username, 'new user', '1023', foto, '0');
INSERT INTO dati_accesso values(username, pwd, 'S');
COMMIT;
END
questa è la procedura usata nel testing delle nostre applicazioni
Rimozione dal sito
create procedure rimuovi(IN usr CHAR(30))
BEGIN
/* rendo irraggiungibili le abitazioni dell'utente */
UPDATE abitazione
SET
foto
=
'home',
costo_giorno
=
'0',
numero_posti
=
informazioni_aggiuntive = '1023', nome_citta = 'frieund', nazione = 'eu'
WHERE proprietario = usr;
'0',
UPDATE profilo
SET messaggio_personale = 'The user was deleted',
intervista = '1023', /* rimette le risposte al valore di default */
foto = '3',
/* mette a default la foto dell'utente rimosso */
stile = '0'
/* imposta lo stile di default */
WHERE utente = usr;
UPDATE utente
SET nome = 'user deleted',
/* elimina qualsiasi riferimento alla persona */
mail = 'nomail',
/* fa in modo che non gli siano inviabili email */
nome_citta = 'frieund', /* lo toglie dai risultati di ricerca di qualsiasi
città */
WHERE username = usr;
END;
non posso cancellare le abitazioni perchè renderei inconsistente la tabella valutazioni. Per far permanere nel DB tali dati lasciamo proprietario e indirizzo inalterati ma il profilo della abitazione verrà “resettato”.
L'applicazione oltre all'invocazione della procedura si dovrà ovviamente incaricare anche dell'eliminazione delle foto delle abitazioni e del profilo utente.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 57 di 80
Progettazione database del sito Frieund.com
Operazioni aggiuntive
Alcune operazioni, più complesse e spesso usate per ricerche più raffinate sono utilizzate in casi particolari.
Per completezza è stato deciso di riportarle anche se non trattate nell'analisi dei costi.
Visualizzazione abitazioni di una certa città e relativo voto medio SELECT abitazione.proprietario, abitazione.indirizzo, abitazione.costo_giorno,
abitazione.foto, (select avg(voto) from valutazione where valutazione.indirizzo
= abitazione.indirizzo and abitazione.proprietario = valutazione.proprietario)
as voto_medio
FROM abitazione
WHERE abitazione.nome_citta = '%s' and abitazione.nazione = '%s'
Visualizzazione utenti di una città e nazione con una certa età
SELECT utente.username, utente.sesso, profilo.foto
FROM utente JOIN profilo ON utente.username = profilo.utente
WHERE utente.nome_citta = '%s' and utente.nazione = '%s'
year(utente.data_nascita) = year(curdate())-'%d'
and
Visualizzazione piatti di una determinata nazione e categoria
SELECT nome, lingua
FROM piatto
WHERE nome_citta = '%s' and nazione = '%s' and categoria = '%s'
visualizzazione contenuti di un utente (usate nella pagina del profilo utente):
lista viaggi (valutazioni su abitazioni)
SELECT valutazione.data, abitazione.proprietario, abitazione.indirizzo,
abitazione.nome_citta, abitazione.nazione, valutazione.commento,valutazione.voto
FROM valutazione JOIN abitazione ON ((valutazione.proprietario =
abitazione.proprietario) and (valutazione.indirizzo = abitazione.indirizzo))
WHERE valutazione.ospite = '%s'
lista abitazioni
SELECT indirizzo, nome_citta, nazione
FROM abitazione
WHERE proprietario = '%s'
lista piatti (è la stessa che restituisce i piatti di un utente)
SELECT nome, lingua
FROM piatto
WHERE utente = '%s'
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 58 di 80
Progettazione database del sito Frieund.com
Definizione delle transazioni
MySQL è impostato a default per trattare ogni operazione come atomica (caratteristica modificabile cambiando il valore della variabile auto commit). Questo significa che ogni operazione che sottoporremo al DBMS avrà effetti singoli e indipendenti rispetto alle altre.
In alcuni casi però è utile poter sottoporre una serie di operazioni legate tra loro come un unica transazione per fare in modo che l'esito di una influenzi anche la conclusione delle altre poiché una modalità di impiego diversa potrebbe portare ad un inconsistenza nei nostri dati.
MySQL mette a disposizione appositi comandi di inclusione di codice all'interno di un unica operazione atomica, un gruppo di operazioni può quindi essere racchiuso all'interno di tale azione risultando così come atomico.
Nel nostro caso è importante provvedere alla progettazione adeguata delle procedure di iscrizione e rimozione utente, unici casi di gruppi di accessi correlati al DBMS che se non opportunamente trattati potrebbero generare errori nel nostro DB e nelle nostre applicazioni che ne fanno uso.
In particolare ogni utente dovrà avere un suo profilo e suoi dati di accesso, se qualcosa va male nell'inserimento di tali dati, tutte le operazioni coinvolte dovranno essere abortite, i dati quindi non saranno permanenti fino alla segnalazione di commit alla fine della transazione.
Allo stesso modo per la rimozione dell'utente dovremmo poter modificare e/o rimuovere i dati interessati (vedi operazioni) senza poterci permettere di evitare alcuna di queste.
A questo proposito il codice delle nostre procedure può essere racchiuso all'interno dei tag di inizio e fine transazione. Nel momento in cui il DBMS trova tale segnalatore saprà (senza dover necessariamente modificare manualmente la variabile auto commit) della atomicità di tale gruppo di operazioni.
Iscrizione al sito
create procedure iscrizione (IN username CHAR(30), IN nome CHAR(30), IN sesso
CHAR(1), IN lingua CHAR(3), IN data_nascita CHAR(8), IN mail CHAR(40), IN
nome_citta CHAR(40), IN nazione CHAR(3), IN pwd CHAR(40))
BEGIN
DECLARE foto int;
START TRANSACTION;
INSERT INTO utente values(username, nome, sesso, lingua, data_nascita, mail,
nome_citta, nazione);
IF sesso = 'M' THEN SET foto = 1;
ELSE SET foto = '2';
END IF;
INSERT INTO profilo values(username, 'new user', '1023', foto, '0');
INSERT INTO dati_accesso values(username, pwd, 'S');
COMMIT;
END //
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 59 di 80
Progettazione database del sito Frieund.com
Rimozione utente (Set as default settings)
create procedure rimuovi(IN usr CHAR(30))
BEGIN
START TRANSACTION;
UPDATE abitazione
SET foto = 'home', costo_giorno = '0', numero_posti = '0',
informazioni_aggiuntive = '1023', nome_citta = 'frieund', nazione = 'eu'
WHERE proprietario = usr;
UPDATE profilo
SET messaggio_personale = 'The user was deleted', intervista = '1023', foto =
'3', stile = '0'
WHERE utente = usr;
UPDATE utente
SET nome = 'user deleted',mail = 'nomail', nome_citta = 'frieund'
WHERE username = usr;
COMMIT;
END
Per poter utilizzare questa funzionalità è però necessario utilizzare uno storage engine transazionale, in caso contrario i nostri utili accorgimenti saranno ignorati.
Questo argomento sarà affrontato meglio in fase di progettazione fisica. ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 60 di 80
Progettazione database del sito Frieund.com
5. Progettazione fisica
La progettazione fisica di una base di dati riceve in ingresso lo schema relazionale della realtà di interesse e si occupa di adattarlo al DBMS e alla piattaforma software a disposizione.
La progettazione fisica tenta di ottimizzare la struttura di memorizzazione dei dati in modo che le operazioni di accesso risultino efficienti. Essa deve essere guidata quindi dalla natura dei nostri dati e dall'uso che è stato pensato per essi. Tale fase può essere suddivisa in più passi, la prima di adattamento dello schema logico al DBMS prescelto in cui si acquisiscono conoscenze sulle funzionalità messe a disposizione. Nel secondo passo si analizzano le transazioni insieme alla frequenza di attivazione; da esse sono identificate le porzioni del DB che potrebbero causare problemi di performance.
In particolare sono osservati gli attributi maggiormente colpiti dalle nostre operazioni e ciò ci sarà utile per la scelta dell'organizzazione dei dati e l'eventuale uso di indici.
La versione di MySQL su cui costruiremo la nostra base di dati è la 5.0.68 ­log, i motori a nostra disposizione sono MyISAM, MEMORY e MRG_MyISAM.
La mancanza di InnoDB ci preclude il supporto transazionale e l'utilizzo delle foreign key e dell'integrità referenziale tra tabelle cui saremo costretti a provvedere in applicazioni più complesse.
MyISAM mette a disposizione organizzazioni di tipo B­tree cosi come MRG­MyISAM. MEMORY permette la scelta tra entrambe le soluzioni utilizzando a default organizzazioni di tipo hash. Queste risultano utili nel momento in cui è necessario individuare tuple che hanno un attributo a valore univoco e sono quindi utilizzabili solo per confronti che includono l'operatore di uguaglianza , i B­tree invece sono consigliati per le operazioni di individuazione di tuple il cui attributo è compreso in un range di valori. La progettazione fisica deve fare in modo che la gestione effettiva dei dati sia efficiente, se in una operazione vogliamo ad esempio le ricette ordinate per nome è una buona scelta ordinare il file in base all'attributo nome.
Abbiamo scelto la modalità di organizzazione del nostro file. Il compito del passo successivo è quello della discussione di un eventuale utilizzo di indici, strutture ausiliare associate alla nostra base di dati che possono essere utilizzate per evitare di scorrere il file in modo sequenziale ogni volta che vogliamo ritrovare un informazione. Essi possono quindi migliorare decisamente le performance a discapito di un utilizzo di memoria maggiore dovuto al mantenimento del servizio. Ovviamente abbiamo necessità di mantenere aggiornato l'indice è ogni modifica dei dati dovrà provocare una modifica in tale supporto, e ciò è un costo aggiuntivo se si decide di utilizzarne.
Partiamo innanzitutto dal calcolo del caso pessimo per ogni schema di relazione, lo scorrimento di ogni tupla in modo sequenziale.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 61 di 80
Progettazione database del sito Frieund.com
Dimensione dei file e costo di accesso sequenziale
Ponendo che:
Un blocco in mysql è grande 1K cioè 1024 byte
NP = numero pagine = dimensione blocco * numero tuple * dimensione tuple
NP * dimblocco = numero tuple * dimensione tuple
Calcoliamo il costo di accesso sequenziale ai nostri schemi di relazione semplicemente calcolando il numero di pagine associate.
citta(nome, nazione, posx, posy)
varchar(40) + char(3) + unsigned smallint(4) + unsigned smallint(4)
(40+1) + 3 + (4*2) + (4*2) = 44 + 16 = 60 byte = DR
NP = (80000*60) / 1024 = 4.687,5
dati_accesso(username, password, privilegio)
varchar(30) + char(40) + enum
(30+1) + 40 + 1 = 72 byte = DR
NP = (1.000.000 * 72) / 1024 = 70.312,5
utente(username, nome, sesso, lingua, data_nascita, mail, nome_citta, nazione)
varchar(30) + varchar(30) + enum + char(3) + date + varchar(40) + varchar(40) + char(3)
(30+1) + (30+1) + 1 + 3 + 3 + (40+1) + (40+1) + 3 = 62 + 10 + 82 = 154 byte = DR
NP = (1.000.000 * 154) / 1024 = 150.390,625
piatto(nome, lingua, categoria, procedimento, ingredienti, nome_citta, nazione, utente)
varchar(40) + char(3) + enum + text + text + varchar(40) +char(3) +varchar(30)
(40+1) + 3 + 1 + (2+n) + (2+n) + (40+1) + 3 + (30+1) = DR
nelle applicazioni abbiamo deciso di permettere al massimo 300 caratteri per gli ingredienti e 600 per il procedimento. Sostituendo abbiamo 1024 byte nel caso pessimo.
NP = (5000*1024)/1024 = 5000
profilo(utente, messaggio_personale, intervista, foto, stile)
varchar(30) + text + unsigned smallint(4) + unsigned smallint(1) + unsigned tinyint(2)
(30+1) + (n+2) + (4*2) + (2*1) + 2 = 31 + (n+2) + 12 = 45 + n Per scelta abbiamo vincolato il campo messaggio alla dimensione di un SMS (160 char)
il totale in questo caso è 45 + 160 = 205 byte = DR
NP = (1.000.000 * 205) / 1024 = 200.195,3125
valutazione(ospite, data, voto, commento, proprietario, indirizzo)
varchar(30) + date + unsigned tinyint(2) + text + varchar(30) + varchar(60)
(30+1) + 3 + 2 + (n+2) + (30+1) + (60+1) = 120 + 10 + n = 130 + n
Se n = 160, 130 + 160 = 290 byte = DR
NP = (40.000 * 290) / 1024 = 11.328,125
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 62 di 80
Progettazione database del sito Frieund.com
abitazione(proprietario, indirizzo, costo_giorno, numero_posti, informazioni_aggiuntive, nome_citta, nazione)
varchar(30) + varchar(60) + char(14) + unsigned smallint(3) + unsigned tinyint(2) + unsigned smallint(4) + varchar(40) + char(3)
(30+1) + (60+1) + 14 + (2*3) + 2 + (4*2) + (40+1) + 3 = 19 + 92 + 55 = 166 byte = DR
NP = (10.000 * 166) / 1024 = 1.621,09375
Ora abbiamo il costo di accesso nella situazione peggiore.
Analizzando le nostre operazioni (escluse quelle di inserimento) potremo capire se in qualche modo sarà possibile migliorarne le performance.
Visualizzazione abitazioni presenti in una data città
SELECT proprietario, indirizzo, costo_giorno,
numero_posti
FROM abitazione
WHERE nome_citta = '%s' and nazione = '%s'
Visualizzazione valutazioni su una data abitazione
SELECT ospite, data, voto, commento
FROM valutazione
WHERE proprietario = '%s' and indirizzo = '%s'
ORDER BY data DESC
Visualizzazione abitazione di un dato utente proprietario
SELECT *
FROM abitazione
WHERE proprietario = '%s' and indirizzo = '%s'
Visualizzazione dei piatti di una determinata città
SELECT *
FROM piatto
WHERE nome_citta = '%s' and nazione = '%s'
ORDER BY piatto.nome
Visualizzazione dei piatti inseriti da un utente
SELECT *
FROM piatto
WHERE utente = '%s'
ORDER BY nome
ANDREA MONACCHI ­ A.A. 2008/2009
R
87.108 / day
Chiave primaria tabella:
proprietario, indirizzo
R
73.170 / day
Chiave primaria tabella:
ospite, data
R
104.529 / day
Chiave primaria tabella:
proprietario, indirizzo
R
52.264,8 / day
Chiave primaria tabella:
nome, lingua
R
52.264,8 / day
Chiave primaria tabella:
nome, lingua
Pagina 63 di 80
Progettazione database del sito Frieund.com
Visualizzazione utenti di una determinata città
SELECT username
FROM utente
WHERE nome_citta = '%s' and nazione = '%s'
ORDER BY username ASC
Login all'area riservata
SELECT username, privilegio
FROM dati_accesso
WHERE username='%s' and password = '%s'
Visualizzazione del profilo di un utente
SELECT utente.*, profilo.*
FROM utente JOIN profilo ON (utente.username =
profilo.utente)
WHERE utente.username = '%s'
Disattivazione di un utente dal sito
R
209.059,2 / day
Chiave primaria tabella:
username
R
580.720 / day
Chiave primaria tabella:
username
R
1.742.160 / day
Chiave primaria:
utente.username
profilo.utente
W
0,58 / day
UPDATE abitazione
SET foto = 'home',
costo_giorno = '0',
numero_posti = '0',
informazioni_aggiuntive = '1023',
nome_citta = 'frieund',
nazione = 'eu'
WHERE proprietario = usr;
UPDATE profilo
SET messaggio_personale = 'The user was deleted',
intervista = '1023',
foto = '3',
stile = '0'
WHERE utente = usr;
UPDATE utente
SET nome = 'user deleted',mail = 'nomail', nome_citta = 'frieund'
WHERE username = usr;
Visualizzazione di una città di una nazione sulla cartina
R
348.432 / day
SELECT *
FROM citta
WHERE nome='%s' and nazione= '%s'
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 64 di 80
Progettazione database del sito Frieund.com
Criteri di scelta degli indici
Costruiamo una lista di attributi candidati su cui saranno costruiti indici. ➢
➢
➢
➢
Indicizzare la chiave primaria di ogni relazione Aggiungere un indice secondario per gli attributi frequentemente coinvolti da operazioni di selezione e join, ordinamento e raggruppamento
Non indicizzare tabelle troppo piccole dove il costo aggiuntivo dell'indice risulta inutile né quelle in cui le interrogazioni restituiscono un numero significativo di tuple e l'utilizzo dell'indice non migliora l'accesso ai dati rispetto alla scansione sequenziale
Non indicizzare attributi che consistono in lunghe stringhe di caratteri né attributi legati da clausole OR in criteri di ricerca (gli indici risultano inutili in quanto sarà comunque necessaria una ricerca sequenziale)
Procediamo a costruire la nostra lista di indici candidati tenendo conto delle operazioni riportate precedentemente
Schema di relazione
Indice primario
Indice secondario
dati_accesso
username
/
Utente
username
nome_citta, nazione
Abitazione
Proprietario, indirizzo
nome_citta, nazione
Piatto
Nome, lingua
Profilo
username
/
Valutazione
Ospite, data
Proprietario, indirizzo
Citta
Nome, nazione
/
a) nome_citta, nazione
b) utente
Nell'ipotesi di poter usufruire di qualsiasi tipo di organizzazione nel nostro DBMS opteremmo per organizzazioni primarie di tipo hash che soprattutto nella ricerca dell'username (login e visualizzazione profili) migliorerebbe decisamente le performance di accesso. ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 65 di 80
Progettazione database del sito Frieund.com
Rimozione degli indici candidati troppo costosi
Il mantenimento dell'indice potrebbe gravare sulle operazioni di aggiornamento, è quindi bene valutare se rimuoverlo dalla lista creata. Alcuni DBMS consentono agli utenti di valutare l'esecuzione ripetuta delle query in un vero e proprio banco di prova o di assistere alle strategie intraprese dall'ottimizzatore durante l'esecuzione di una query, in particolare MySql permette l'utilizzo dello strumento benchmark e del prefisso explain che permette di “spiegare” l'interrogazione. Tale funzionalità può essere usata anche nel caso si riscontri una certa lentezza nell'esecuzione.
Prima di procedere alla analisi vera e propria ricapitoliamo le leggi valide nei nostri calcoli di accesso
Tipo accesso
Costo
Scansione sequenziale
NPr
Ricerca binaria
log2 NPr [ + ⎡fp(A) ⋅ NPr⎤ ]
Accesso con indice clustered
(hA – 1) + ⎡fp(A) ⋅ NLA⎤ + ⎡fp(A) ⋅ NPr⎤
Accesso con indice unclustered
(hA – 1) + ⎡fp(A) ⋅ NLA⎤ + ⎡Φ(fp(A) ⋅ NTr, NPr)⎤
Accesso hashed
Costo = 1 + [fattore che dipende dall’hashing]
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 66 di 80
Progettazione database del sito Frieund.com
Progettazione fisica utilizzando InnoDB
Nel caso ottimo, avremo comunque bisogno di un supporto transazionale, caratteristica che come abbiamo visto solo InnoDB ci mette per ora a disposizione (tra i motori affrontati).
Visto che esso indicizza automaticamente le chiavi primarie e le chiavi importate possiamo decidere di evitare l'analisi per tali accessi.
Visualizzazione abitazioni presenti in una data città: criterio di ricerca sulla chiave importata
Visualizzazione valutazioni su una data abitazione: criterio di ricerca agisce sulla chiave importata
Visualizzazione abitazione di un dato utente proprietario: il criterio di ricerca agisce sulla chiave primaria
Visualizzazione dei piatti di una determinata città: il criterio di ricerca agisce sulla chiave importata
Visualizzazione piatti inseriti da un utente: il criterio di ricerca agisce sulla chiave importata
Visualizzazione utenti di una determinata città: il criterio di ricerca agisce sulla chiave importata
Login all'area riservata: il criterio di ricerca agisce sulla chiave primaria e sul campo password
Visualizzazione del profilo di un utente: operazione di join più frequente ma agisce sulle chiavi primarie delle tabelle
Rimozione utente dal sito: poco frequente e agisce sul campo username che è indicizzato o PK
Visualizzazione di una città di una nazione sulla cartina: citta e nazione sono le chiavi primarie
Le operazioni più importanti viste in fase di ottimizzazione sono esenti da accessi su campi diversi da quelli già indicizzati. Questo significa che su di essi non dovremo operare ulteriormente con i mezzi a nostra disposizione. (Se avessimo avuto la disponibilità delle org. Hash in certi casi avremmo sicuramente saputo abbassare i costi)
Gli unici casi in cui potremmo pensare di migliorare il piano di accesso sono le query meno frequenti che abbiamo aggiunto come esercizio e come applicazioni di maggior raffinamento della selezione ovvero come ausilio ed estensione delle operazioni principali.
visualizzazione abitazioni di una certa città e relativo voto medio: agisce sulle chiavi primarie e importate (proprietario, indirizzo e nome_citta, nazione)
visualizzazione utenti di una città e nazione con una certa età: il predicato di ricerca agisce sull'username (PrimaryKey) e sulla città e nazione (ForeignKey) e sulla data di nascita (anno)
visualizzazione piatti di una determinata città e nazione e categoria: città e nazione sono chiavi importate mentre il campo categoria risulta non indicizzato.
Lista piatti: già trattata come visualizzazione piatti di un utente.
Lista viaggi (valutazioni): agisce su parte della primarykey ovvero ospite invece che ospite, data
lista abitazioni: agisce su proprietario invece che su proprietario, indirizzo
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 67 di 80
Progettazione database del sito Frieund.com
Questo porta ad una nuova tabella di indici candidati visto che quella vecchia è stata già indicizzata automaticamente dal DBMS.
operazione
campo
Tipo indice
Visualizzazione utenti di una città e nazione con una certa età
Utente.data_nascita
Secondary unclustered
Visualizzazione piatti di una città e nazione di una determinata categoria
Piatto.categoria
Secondary unclustered
Valutazione.ospite
Secondary unclustered
Abitazione.proprietario
Secondary unclustered
Lista viaggi (valutazioni)
Lista abitazioni
Visto che la visualizzazione del profilo di un utente è l'operazione più costosa e frequente di tutto l'applicativo web, le operazioni di listing dei viaggi e delle abitazioni che sono legate ad esso saranno quelle su cui dovremo puntare maggiormente in quanto sebbene secondarie saranno comunque attivate dalla pagina più vista e quindi transitivamente anche esse avranno una frequenza rilevante.
Lista viaggi
NT = 40.000
NP = 11.329
NK: 50.000 ospiti ma solo 8 su 10 lasciano valutazioni e quindi le 40.000 valutazioni sono in media ogniuna di un utente. f = 1/40.000
Ospite è un campo varchar(30) quindi nel caso pessimo occuperà 31 byte
NL = NR∗len pNK ∗len k
= D∗u
= 1981,43
costo accesso = (hA – 1) + ⎡fp(A) ⋅ NLA⎤ + ⎡Φ(fp(A) ⋅ NTr, Npr)⎤ = 5 + 1 + 1 = 7
lista abitazioni
NT = 10.000
NP = 1621
NK = 10.000 proprietari ogniuno con una abitazione
NR∗len pNK ∗len k
NL =
= D∗u
= 495,357
costo accesso = (hA – 1) + ⎡fp(A) ⋅ NLA⎤ + ⎡Φ(fp(A) ⋅ NTr, Npr)⎤ = 4 + 1 + 1 = 6
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 68 di 80
Progettazione database del sito Frieund.com
Ottimizzazione
L'ottimizzatore del DBMS valuterà se effettuare un accesso sequenziale o utilizzare gli indici preposti, nel caso le tuple siano un numero molto minore di quello dichiarato infatti esso ne eviterà l'utilizzo. Dimostriamolo verificando i tempi di accesso sulle relazioni prima e dopo dell'indice, a parità di tuple.
Lista viaggi utente
riportiamo l'explain dei viaggi dell'utente tester
id select_type
Table
Type possible_keys
1
SIMPLE
Valutazione
ref
1
SIMPLE
Abitazione eq_ref
key
key_len
ref
rows
extra
PRIMARY, PRIMARY
proprietario
92
const
2
Using where
PRIMARY
274
Valutazione.proprietario valutazione.indirizzo
1
PRIMARY
vediamone le performance ripetendo la query 100.000.000 di volte per valutare meglio i tempi
Tempo: tentativo 1 – 0,8132 secondi
tentativo 2 – 0,8059 secondi
tentativo 3 – 0,8051 secondi
ora creiamo l'indice su ospite riportando i tempi
tempo: tentativo 1: 0,8060 secondi
tentativo 2: 0,8079 secondi
tentativo 3: 0,8048 secondi
e ne vediamo nuovamente le scelte di esecuzione notando come la situazione non sia cambiata (poche tuple)
id select_type
Table
Type possible_keys
1
SIMPLE
Valutazione
ref
1
SIMPLE
Abitazione eq_ref
key
key_len
ref
rows
extra
PRIMARY, proprietario, PRIMARY
ospite
92
const
2
Using where
PRIMARY
274
Valutazione.proprietario valutazione.indirizzo
1
PRIMARY
Lista abitazioni utente
Vediamo le performance eseguendo la query sulle abitazioni dell'utente local (20 tuple)
id
select_type
Table
Type
possible_keys
key
key_len
ref
rows
extra
1
SIMPLE
abitazione
ref
PRIMARY
PRIMARY
92
const
20
Using where
Ripetendo la query 100.000.000 di volte tramite il comando benchmark otteniamo
tentativo 1: 0,8065 secondi
tentativo 2: 0,8051 secondi
tentativo 3: 0,8130 secondi
inserendo l'indice sull'attributo proprietario vediamo come esso sia subito utilizzato dal DBMS
id
select_type
Table
Type
possible_keys
key
key_len
ref
rows
extra
1
SIMPLE
abitazione
ref
PRIMARY, proprietario
proprietario
92
const
20
Using where
Tempi di accesso: tentativo 1: 0,81 secondi
tentativo 2: 0,8096 secondi
tentativo 3: 0,8059 secondi
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 69 di 80
Progettazione database del sito Frieund.com
Progettazione fisica usando motori alternativi
Quando non è possibile usare InnoDB, come nel nostro caso, è necessario poter prevedere valide alternative insieme a soluzioni che ne permettano un uso adatto alle applicazioni che ne andranno a fare uso.
MEMORY
La prima alternativa presentata è lo storage engine memory (conosciuta anche come heap), le cui tabelle sono conservate in memoria centrale (volatile), ciò garantisce altissima velocità in quanto si evitano gli accessi al disco ma non garantisce consistenza e durabilità dei dati che vedremo persi alla chiusura della sessione. Al restart del server avremo così tabelle vuote. Le tabelle memory non sono mai convertite in tabelle su disco. L'unica memorizzazione è effettuata per la struttura della tabella che risiede sul file system in un file con estensione .frm.
Memory nasce principalmente per sopperire all'assenza di una tipologia di tabelle temporanee in mysql.
L'unica soluzione utilizzabile può essere quella del caricamento delle tuple nella fase di avvio da un file di testo che permetta ad ogni chiusura di poter memorizzare in modo stabile lo stato del database. Ciò può avvenire ad opera di un comando INSERT (vedi anche prepared statement php) o di un LOAD DATA INFILE.
Vantaggi:
Alta velocità di risposta. Uso di organizzazioni hash in modo molto elastico (è possibile indicizzare anche campi a valori non unici e null cosi come utilizzare campi autoincrement)
Limitazioni:
Le tabelle usano un formato a lunghezza fissa e quindi tipi come varchar non permettono risparmio di memoria rispetto ai char.
I tipi BLOB e TEXT non sono supportati.
Ogni tabella può avere al massimo 32 indici, ognuno composto al massimo di 16 colonne e di massimo 500k per riga.
La dimensione massima di una tabella è settata a default a 16 MB (ma comunque modificabile con un set) nella variabile di sistema max_heap_table_size.
L'eliminazione di tuple non garantisce la disallocazione dello spazio dedicato ad esse; ciò significa che si continuerà ad avere una tabella di stessa dimensione in memoria che sarà liberabile con un istruzione truncate table o eliminando e ricreando la tabella.
Progettazione fisica
L'unica caratteristica che potrebbe farci scegliere questo tipo di motore è sicuramente il supporto per le organizzazioni di tipo hash. In particolare le tabelle come dati_accesso, utente e profilo hanno associate operazioni molto frequenti che fanno capo al campo username. Le limitazioni viste però non sono trascurabili, il caricamento di 1.000.000 di tuple per ogniuna di queste tabelle può diventare un limite, oltre al fatto che il campo messaggio_personale è un txt e non sarebbe implementabile se non con un char da 160 caratteri. L'inaffidabilità e la necessità di possedere un file di testo con migliaia di tuple ci ha spinto ad abbandonare anche solo l'ipotesi di un implementazione di tabelle di questo genere. L'utilità potrebbe essere nel caso di script come ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 70 di 80
Progettazione database del sito Frieund.com
“numero utenti on line” o semplici chat che non hanno necessità di affidabilità ne di memorizzazione e la velocità di tale supporto lo rende la più valida alternativa al semplice file di testo.
MyISAM
MyIsam è un motore non transazionale che si trova in modo predefinito sulle attuali versioni di mysql. Esso deriva dalla struttura ISAM da cui prende anche parte del nome. É molto veloce e il limite principale è il mancato supporto alle transazioni.
Ogni tabella di questo tipo è memorizzata in 3 file distinti, uno .frm che ne descrive la struttura, uno .MYI che contiene gli indici e uno di tipo MYD che contiene i dati veri e propri.
Le tabelle di tipo MyISAM dal punto di vista dell'implementazione da comando SQL è analoga a quella delle tabelle InnoDB. Possiamo usare gli stessi comandi, semplicemente le funzioni aggiuntive introdotte da InnoDB come le transazioni e le foreign key saranno ignorate. Questo significa che possiamo riutilizzare gli indici calcolati per gli schemi di relazione InnoDB ma dovremo inserire manualmente quelli sulle chiavi importate in quanto MyISAM non lo fa automaticamente.
Le buone caratteristiche prestazionali ci hanno fatto optare per questa valida alternativa nella realizzazione delle nostre tabelle. Una soluzione che non ci garantirà la consistenza dei nostri dati se non da applicazioni più sicure e pesanti.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 71 di 80
Progettazione database del sito Frieund.com
6. Implementazione interfaccia
Come introdotto inizialmente, la base di dati dovrà poter essere fruibile da un sito web. Il linguaggio server side scelto è php, open source e con sintassi C­like, ci permetterà di scrivere applicazioni complesse in modo semplice e veloce. In molte parti si fa uso anche della tecnologia AJAX cioè dell'uso di javascript per il recupero di pagine evitando del reload della pagina.
6.1 descrizione applicazione
Evitando di introdurre codice sorgente in questa relazione, il cui scopo è sicuramente diverso, rimandiamo al sito vero e proprio dove potranno essere provate le operazioni considerate.
Non troveremo una pagina contenente le varie query ma un sito pronto all'uso. Per alcune operazioni come il login o l'inserimento ricette sarà quindi necessario sostenere la procedura di iscrizione (in caso la si voglia saltare si forniscono le credenziali di testing, User: tester, Pass: tester che ha privilegi di utente semplice, per accedere come local o admin contattare l'amministratore di sistema)
Digitando come URL http://www.frieund.com troveremo una pagina del tipo:
Tale applicazioni sono state testate con esito positivo su un browser Google Chrome, sia il template che il foglio di style sono stati validati con successo dagli standard W3C.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 72 di 80
Progettazione database del sito Frieund.com
Riportiamo alcune delle operazioni per guidare il visitatore nella sua prima esperienza on line.
Iscrizione al sito:
login all'area riservata: visualizzazione del profilo di un utente: all'interno di tale applicazione è possibile richiamare le query:
visualizza piatti di un dato utente, visualizza abitazioni di un utente, visualizza viaggi di un utente che listeranno il loro risultato in un div nascosto da cui sarà possibile puntare alla visualizzazione specifica e completa del singolo risultato selezionato.
In particolare: visualizza abitazione (pagina profilo) di un dato utente proprietario
visualizza abitazione (pagina profilo) di un dato utente ospite
visualizza piatto
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 73 di 80
Progettazione database del sito Frieund.com
aggiornamento del proprio profilo
aggiornamento dei propri dati e possibilità di rimozione dell'utente:
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 74 di 80
Progettazione database del sito Frieund.com
visualizzazione abitazioni di una certa città e relativo voto medio: visualizzazione utenti di una città e nazione con una certa età: visualizzazione piatti di una determinata città e nazione e categoria: ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 75 di 80
Progettazione database del sito Frieund.com
7. Appendice
In questa sezione trovano spazio gli argomenti di supporto alla nostra progettazione che non hanno avuto spazio durante le fasi di analisi e implementazione.
Esempio di connessione ad un database MySql tramite le API funzionali di PHP
Riportiamo un frammento di codice sorgente della pagina view.php incaricata della visualizzazione del profilo di un utente
<?php // apro la connessione dal database
require_once($_SERVER['DOCUMENT_ROOT'].'xyz.php');
// seleziono il database
$db = @mysql_select_db("Sql180545_1", // nome del database
$connessione) // nome della connessione
or die ("Impossible to select database.");
// eseguo la query
$result = @mysql_query(sprintf("SELECT * FROM utente JOIN profilo ON utente.username =
profilo.utente WHERE utente.username = '%s'", $_POST['user']));
// controllo che esista almeno un record
if(mysql_num_rows($result) > 0)
{
// prelevo una riga della tabella risultante come array associativo
$riga = mysql_fetch_assoc($result);
}
else
{
// l'utente non è stato trovato
$messaggio = "The user <strong>".$_POST['user']."</strong> cannot be loaded";
}
// chiudo la connessione con il database
mysql_close($connessione);
?>
in questo caso il risultato dovrebbe essere una sola tupla e per accedere ai suoi valori ci basta la notazione $riga['nome_campo'] essendo stato richiesto un array associativo.
Note: ­ xyz.php è un file contentente la stringa di connessione al DB:
// connessione al database frieund
$connessione = @mysql_connect("62.149.150.72", // server database
"", // username
"") // password
or die ("impossibile instaurare una connessione");
@ è l'operatore silence che evita la visualizzazione di errori del DBMS che potrebbero far trapelare ad utenti non autorizzati eventuali bug o caratteristiche del sistema.
I tipi di connessione al DBMS in php sono principalmente due quella persistente e quella non ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 76 di 80
Progettazione database del sito Frieund.com
persistente, nel nostro caso come intuitibile è stata utilizzata la connessione non persistente, che è chiusa dalla funzione mysql_close al termine della query (sarebbe stata comunque chiusa al termine dello script)
Estensione mysqli (improved)
Con l'avvento della versione 5 di php è stata inclusa una nuova estensione per l'accesso ai database mysql, MySQLi. Tale strumento è stato introdotto per supportare le nuove API Client MySQL 4.1 e 5.0 che hanno introdotto nuove funzionalità. Tali strumenti sono fruibili con un interfaccia di gestione ad oggetti che ricalca molto (anche mnemonicamente) quella procedurale precedente. Molte delle funzioni utilizzate precedentemente infatti sono riportate e modificate dal semplice inserimento di una i (improved).
Riportiamo per una maggiore comprensione una connessione al DBMS
<?php
$connessione = mysqli_connect(“localhost”, // host name
“”, // username
“”, // password
“”); // database name
// se la connessione fallisce la connect restituisce FALSE
if(empty($connessione)) {
die(“Connessione non riuscita”.mysqli_connect_error());
}
?>
Ora siamo connessi al nostro DBMS. Potremmo voler effettuare un interrogazione
<?php
$risultato = $connessione->query(“Query SQL”);
while($riga = $risultato->fetch_row()) {
print $riga[0].”\n”;
}
$risultato->free();
$connessione->close();
?>
Come per l'estensione mysql abbiamo tre sistemi diversi di recupero record dai risultati, un array numerato, un array associativo oppure come oggetto. (fetch_row(), fetch_assoc(), fetch_object())
Nota:
L'estensione MySQLi consente anche di inviare dichiarazioni SQL multiple in un unica chiamata di funzione separando le varie stringhe da un punto e virgola. Il recupero dei set di risultati dalle dichiarazioni multiple risulterà ovviamente più complesso.
Quanto visto risulta molto simile a quanto fatto nel caso dell'estensione mysql. Uno dei vantaggi offerti dall'estensione mysqli rispetto ad essa è la possibilità di utilizzare le prepared statement ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 77 di 80
Progettazione database del sito Frieund.com
(dichiarazioni preparate) che permettono agli sviluppatori di realizzare query più sicure e prestazioni migliori. È necessario creare un modello di query e inviarlo al server MySQL, esso ne controllerà la validità per garantirne il funzionamento e lo acquisirà. Al client restituirà un puntatore che potrà in seguito essere usato come riferimento alla dichiarazione preparata.
Le variabili implicate nella query saranno di due tipi: input (collegate alla dichiarazioni) o output (collegate al set di risultati). Il segnaposto utilizzato al posto dei valori reali è il punto interrogativo.
Esempio: inserimento di un piatto tra le ricette
<?php
// mi connetto al DBMS
$connessione = mysqli_connect(“”, ””, “”, “”);
// creo la prepared statement
$dichiarazione = $connessione­>prepare(“INSERT INTO ricette VALUES(?, ?, ?, ?, ?, ?, ?, ?)”);
// collego le variabili di input
$dichiarazione­>bind_param($nome, $lingua, $categoria, $ingredienti, $procedimento, nome_citta, nazione, utente);
// ora inserisco le tuple
$nome = 'piadina';
$lingua = 'I';
$categoria = 'P';
$ingredienti = 'acqua, sale,....';
$procedimento = 'prendere.....';
$nome_citta = 'urbino';
$nazione = 'I';
$utente = 'tester';
$dichiarazione­>execute();
$nome = 'torta paradiso';
$lingua = 'I';
$categoria = 'D';
$ingredienti = 'latte, farina,....';
$procedimento = 'prendere.....';
$nome_citta = 'roma';
$nazione = 'I';
$utente = 'tester';
$dichiarazione­>execute();
?>
Tale sistema può risultare utile nel caso si debbano inserire un gran numero di dati come ad esempio il momento della popolazione del DB.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 78 di 80
Progettazione database del sito Frieund.com
Sicurezza
Riportiamo in questa breve sezione alcuni dei parametri utilizzati per garantire una sicurezza dei dati memorizzati nella nostra base di dati e delle applicazioni che ne fanno uso ad eventuali accessi non autorizzati.
SQL_INJECTION (query injection)
La funzioni addslashes è in disuso mentre mysql_escape_string è stata deprecata. Al loro posto si applica la funzione mysql_real_escape_string() che permette di risolvere il problema delle query injection con l'escaping dei caratteri speciali di sql. VARIABLE INJECTION
Per evitare questo problema è stato deciso di non utilizzare variabili GET né COOKIE, il passaggio di valori avviene tramite l'array globale POST e l'autenticazione è gestita con il meccanismo delle sessioni cui è stato espressamente indicato di non usare in nessun caso cookie. Le variabili POST non sono mai recuperate dall'array $_REQUEST e su di esse sono effettuati controlli di esistenza e consistenza nonché in determinati casi il casting forzato.
XSS (Cross Site Script)
Per risolvere il problema dell'inclusione di codice malevolo è stato pensato di filtrare i dati immessi nei nostri campi.
strip_tags(stringa) si occupa di eliminare eventuali tag html e php presenti
htmlentities(stringa) converte caratteri speciali in testo html
L'utilizzo congiunto di queste due funzioni ci permettono una protezione sufficiente a tale problematica
Password
Per la protezione delle password è stato deciso l'utilizzo della funzione hash sha­1.
La lunghezza di tale campo è stata vincolata tra 8 e 14 caratteri per garantire una sicurezza minima.
Visibilità dei parametri di connessione al DBMS
Il file contenente i parametri di connessione, nonché la password in chiaro è stato memorizzato in una cartella protetta da lettura e scrittura.
Accesso al DBMS
Per la connessione al DBMS è preferibile accedere da un utente non amministratore in modo da poterne limitare i privilegi. Un utente è creabile e gestibile tramite la sintassi GRANT di MySql.
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 79 di 80
Progettazione database del sito Frieund.com
8. Bibliografia
Riportiamo le fonti che abbiamo consultato nella realizzazione del nostro elaborato.
Leonardo Bellini, “Fare business con il web, scacco alla rete in 7 mosse”, Lupetti 2006
Azteni, Ceri, Paraboschi, Torlone, “Basi di dati, modelli e linguaggi di interrogazione”, McGraw Hill, 2002
Rasmus Lerdorf, “PHP Pocket Reference”, O'Reilly 1999
Andi Gutmans, Stig S. Bakken, Derick Rethans, “PHP 5 guida completa”, APOGEO 2005
Kevin Yank, “PHP e MySQL creare siti web”, Mondadori informatica 2005
Sitografia
http://www.php.net
http://www.mysql.it
ANDREA MONACCHI ­ A.A. 2008/2009
Pagina 80 di 80
