Basi di Dati
Alan Giacomin
Maggio 2009
Alan Giacomin Basi di Dati Maggio 2009 1 / 54
Basi di dati
1 Raccolta e analisi requisitiRaccolta requisitiRequisiti strutturatiSchema ER
2 RistrutturazioneGeneralizzazioniRidondanzeSchema ER ristrutturatoSchema logico
3 Implementazione SQLCreazione tabelleInterrogazioni
Alan Giacomin Basi di Dati Maggio 2009 2 / 54
Raccolta e analisi requisiti Raccolta requisiti
Raccolta requisiti
Mini-mondoColloquiQuestionariDati raccolti a livello informale
SinonimiConcetti non chiari
Alan Giacomin Basi di Dati Maggio 2009 4 / 54
Raccolta e analisi requisiti Raccolta requisiti
Esempio - Raccolta requisiti
Una società vuole gestire l’attivitàdi videonoleggio. I video noleg-giabili possono essere cd o dvde sono identificabili attraverso uncodice, oltre ad avere un propriotitolo e dati relativi alla durata equalità. Possono noleggiare filmsolamente i possessori di tesseraricaricabile dalla quale alla con-segna viene scalato un importo inbase al tempo passato dal ritiro.
Le tessere sono personali dell’u-tente che ne richiede una. Deiclienti si desidera tenere tracciadei suoi dati anagrafici (cf, cogno-me, nome). Di ciascun video no-leggiato memorizziamo un codi-ce, l’utente noleggiante e l’impor-to pagato. Per tutti i video si vuo-le conoscere il numero di noleg-gi effettuati. Un video non puòessere noleggiato da più utenticontemporaneamente.
Alan Giacomin Basi di Dati Maggio 2009 5 / 54
Raccolta e analisi requisiti Requisiti strutturati
Requisiti strutturati
Suddivisione per concettiRaggruppamento per frasiStruttura frasi standard
“Per <concetto> rappresentiamo ...”
Alan Giacomin Basi di Dati Maggio 2009 7 / 54
Raccolta e analisi requisiti Requisiti strutturati
Esempio - Requisiti strutturati
Per video rappresentiamoun codice identificatore,titolo, durata e qualità. Sivuole conoscere anche ilnumero di volte che è statonoleggiato. Possonoessere cd o dvd. Più utentinon possono noleggiare lostesso video assieme.Per utente rappresentiamocf, cognome, nome. Ogniutente ha una tessera.
Per noleggiorappresentiamo un codice,il video, l’utente, data e oradel ritiro e consegna.Per tesserarappresentiamo un codiceidentificativo, l’utentepossessore ed il creditoresiduo.
Alan Giacomin Basi di Dati Maggio 2009 8 / 54
Raccolta e analisi requisiti Schema ER
Schema ER
Spiegazione generale
Dai concetti alle entitàBuon punto di inizio
Prime associazioniImmediateCardinalità ricavate dairequisiti
Analizzare bene i requisitistrutturati e terminare loschema
Documentare quanto nonrappresentabile
VincoliDerivazioni/ridondanze
Documentare scelte perpunti non espressi neirequisitiAttenersi a quel che vienerichiesto, non si aggiungeniente (ad esempio gestionericariche)
Alan Giacomin Basi di Dati Maggio 2009 10 / 54
Raccolta e analisi requisiti Schema ER
Esempio - Schema ER (1)
Per video rappresentiamo un codice identificatore, titolo, durata equalità. Si vuole conoscere anche il numero di volte che è statonoleggiato. Possono essere cd o dvd.
Alan Giacomin Basi di Dati Maggio 2009 11 / 54
Raccolta e analisi requisiti Schema ER
Esempio - Schema ER (2)
Per utente rappresentiamo cf, cognome, nome. Ogni utente ha unatessera. Per tessera rappresentiamo un codice identificativo, l’utentepossessore ed il credito residuo.
Alan Giacomin Basi di Dati Maggio 2009 12 / 54
Raccolta e analisi requisiti Schema ER
Esempio - Schema ER (3)
Per noleggio rappresentiamo un codice, il video, l’utente, data e ora delritiro e consegna.
Alan Giacomin Basi di Dati Maggio 2009 13 / 54
Raccolta e analisi requisiti Schema ER
Esempio - Schema ER (4)
Non è specificato ma si suppone che nel tempo lo stesso video vienenoleggiato più volte. Analogamente si suppone che un utente effettuipiù noleggi.
Alan Giacomin Basi di Dati Maggio 2009 14 / 54
Raccolta e analisi requisiti Schema ER
Esempio - Schema ER (5)
Alan Giacomin Basi di Dati Maggio 2009 15 / 54
Raccolta e analisi requisiti Schema ER
Esempio - Schema ER (6)
Regole di vincolo
Un video non deve essere noleggiato se un utente lo ha già noleggiatoed ancora non consegnato
Regole di derivazioneIl numero di noleggi per un video si ottiene contando il numero dioccorrenze di Noleggio a cui Video partecipa.
Alan Giacomin Basi di Dati Maggio 2009 16 / 54
Ristrutturazione Generalizzazioni
Generalizzazioni
Ristrutturazione necessaria in quanto non sono traducibili a livellologicoSi possono rimuovere adottando più tecniche
Accorpamento entità figlie nel padreAccorpamento entità padre nelle figlieSostituzione generalizzazione con associazioniComposizione delle precedenti
Alan Giacomin Basi di Dati Maggio 2009 18 / 54
Ristrutturazione Generalizzazioni
Generalizzazioni
Alan Giacomin Basi di Dati Maggio 2009 19 / 54
Ristrutturazione Generalizzazioni
Generalizzazioni (Figlie -> Padre)
Si sceglie quando l’applicazione accede maggiormente alconcetto generale anzichè ai singoli sottotipi
+ minor numero di accessi- presenza di valori nulli- aggiunta attributo (tipo)- aggiunta regola di vincolo
Alan Giacomin Basi di Dati Maggio 2009 20 / 54
Ristrutturazione Generalizzazioni
Generalizzazioni (Figlie -> Padre)
Alan Giacomin Basi di Dati Maggio 2009 21 / 54
Ristrutturazione Generalizzazioni
Generalizzazioni (Padre -> Figlie)
Si sceglie quando si accede spesso ad un dettaglio rispettoall’intero concettoPorre attenzione se la generalizzazione non è totale
+ assenza valori nulli+ minor numero di accessi- duplicazione associazioni legate al padre
Alan Giacomin Basi di Dati Maggio 2009 22 / 54
Ristrutturazione Generalizzazioni
Generalizzazioni (Padre -> Figlie)
Alan Giacomin Basi di Dati Maggio 2009 23 / 54
Ristrutturazione Generalizzazioni
Generalizzazioni (Associazioni)
Si sceglie quando l’applicazione accede equamente al padre edalle figlie
+ assenza valori nulli- maggior numero di accessi- aggiunta attributo (tipo)- aggiunta regola di vincolo
Alan Giacomin Basi di Dati Maggio 2009 24 / 54
Ristrutturazione Generalizzazioni
Generalizzazioni (Associazioni)
Alan Giacomin Basi di Dati Maggio 2009 25 / 54
Ristrutturazione Generalizzazioni
Generalizzazioni (Composizione)
Si sceglie per combinare pro e contro di tecniche differenti
Ad esempio accorpare una figlia nel padre e creareun’associazione tra la seconda figlia ed il padre
Alan Giacomin Basi di Dati Maggio 2009 26 / 54
Ristrutturazione Generalizzazioni
Generalizzazioni (Composizione)
Alan Giacomin Basi di Dati Maggio 2009 27 / 54
Ristrutturazione Generalizzazioni
Esempio - Generalizzazione
Nel nostro esempio abbiamo una generalizzazione di Video che sisuddivide in cd e dvdLe entità figlie non sono particolarmente interessatedall’applicazioneLe entità figlie non hanno attributi propri
Si sceglie quindi di accorpare le figlie nel padre
Alan Giacomin Basi di Dati Maggio 2009 28 / 54
Ristrutturazione Generalizzazioni
Esempio - Generalizzazione
Alan Giacomin Basi di Dati Maggio 2009 29 / 54
Ristrutturazione Ridondanze
Ridondanze
Sono utili perchè in fase di visualizzazione un dato è facilmenteaccessibile senza tanti contiIn fase di inserimento dati è necessario ricordare di aggiornare talivalori
Tenere o lasciare le ridondanze?
Alan Giacomin Basi di Dati Maggio 2009 31 / 54
Ristrutturazione Ridondanze
Tavole volumi
Stima grandezza base di dati
Aiutano l’analisi
Entità Volume
AAA 10BBB 20CCC 15
Alan Giacomin Basi di Dati Maggio 2009 32 / 54
Ristrutturazione Ridondanze
Esempio - Tavole volumi
Supponiamo di avere 500 video disponibili e 200 utenti. Ogni utentenoleggia una media di 5 video.
Entità Volume
Utente 200Video 500Tessera 200Noleggio 1000
Alan Giacomin Basi di Dati Maggio 2009 33 / 54
Ristrutturazione Ridondanze
Tavole delle operazioni
Evidenzia le operazioni più frequenti
Permette di trovare le operazioni che incidono sulle ridondanze
Descrizione Frequenza
OP1 Memorizzare un dato in AAA 1/giornoOP2 Visualizzare tutti i dati di BBB 1/mese
Alan Giacomin Basi di Dati Maggio 2009 34 / 54
Ristrutturazione Ridondanze
Esempio - Tavole delle operazioni
Nel nostro esempio possiamo ipotizzare le seguenti operazioni.
Descrizione Frequenza
OP1 Inserire nuovo utente 1/giornoOP2 Inserire nuovo video 1/meseOP3 Noleggiare un video 3/giornoOP4 Visualizzare info video 1/mese
Alan Giacomin Basi di Dati Maggio 2009 35 / 54
Ristrutturazione Ridondanze
Tavola degli accessi
Utili nelle analisi delle ridondanze nelle singole operazioni
Vengono utilizzate per ricavare il numero totale di accessi
Gli accessi in scrittura vengono contati doppi
Alan Giacomin Basi di Dati Maggio 2009 36 / 54
Ristrutturazione Ridondanze
Tavola degli accessi
Ipotizziamo un inserimento in AAA con un codice preso in BBB (20elementi, 3/giorno)
Concetto Tipo Accessi
BBB L 1AAA S 20
Operazione: 41Totale: 123/giorno
Alan Giacomin Basi di Dati Maggio 2009 37 / 54
Ristrutturazione Ridondanze
Esempio - Tavole degli accessi
Tra le nostre operazioni solo due agisono sulla ridondanza presente
Descrizione Frequenza
OP3 Noleggiare un video 3/giornoOP4 Visualizzare info video 1/mese
Alan Giacomin Basi di Dati Maggio 2009 38 / 54
Ristrutturazione Ridondanze
Esempio - Tavole degli accessi
OP3 con ridondanza OP3 senza ridondanza
Concetto Tipo Accessi
Video L 1Utente L 1Noleggio S 1Video S 1
Operazione: 6Totale: 6570/anno
Concetto Tipo Accessi
Video L 1Utente L 1Noleggio S 1
Operazione: 4Totale: 4380/anno
Alan Giacomin Basi di Dati Maggio 2009 39 / 54
Ristrutturazione Ridondanze
Esempio - Tavole degli accessi
OP4 con ridondanza OP4 senza ridondanza
Concetto Tipo Accessi
Video L 500Operazione: 500
Totale: 6000/anno
Concetto Tipo Accessi
Video L 500Noleggio L 1000
Operazione: 1500Totale: 18000/anno
Alan Giacomin Basi di Dati Maggio 2009 40 / 54
Ristrutturazione Ridondanze
Esempio - Tavole degli accessi
Riassumendo...
Con ridondanza Senza ridondanza
OP3 6570OP4 6000
Totale 12570
OP3 4380OP4 18000
Totale 22380
Decisione: Teniamo la ridondanza
Alan Giacomin Basi di Dati Maggio 2009 41 / 54
Ristrutturazione Schema ER ristrutturato
Schema ER ristrutturato
Alan Giacomin Basi di Dati Maggio 2009 43 / 54
Ristrutturazione Schema logico
Schema logico
Alan Giacomin Basi di Dati Maggio 2009 45 / 54
Implementazione SQL Creazione tabelle
Tabella Video
CREATE TABLE video (codice INTEGER,titolo VARCHAR(255),durata INTEGER,qualita VARCHAR(255),noleggi INTEGER,tipo VARCHAR(5),PRIMARY KEY (codice)
);
Alan Giacomin Basi di Dati Maggio 2009 47 / 54
Implementazione SQL Creazione tabelle
Tabella Tessera
CREATE TABLE tessera (codice INTEGER,importo FLOAT,PRIMARY KEY (codice)
);
Alan Giacomin Basi di Dati Maggio 2009 48 / 54
Implementazione SQL Creazione tabelle
Tabella Utente
CREATE TABLE utente (cf CHAR(15),cognome VARCHAR(255),nome VARCHAR(255),PRIMARY KEY (cf),FOREIGN KEY (tessera) REFERENCES tessera (codice)
);
Alan Giacomin Basi di Dati Maggio 2009 49 / 54
Implementazione SQL Creazione tabelle
Tabella Noleggio
CREATE TABLE noleggio (codice INTEGER,ritiro DATE,consegna DATE,utente CHAR(15),video INTEGER,PRIMARY KEY (codice),FOREIGN KEY (utente) REFERENCES utente (cf),FOREIGN KEY (video) REFERENCES video (codice)
);
Alan Giacomin Basi di Dati Maggio 2009 50 / 54
Implementazione SQL Interrogazioni
Interrogazioni
Visualizzazione informazioni videoSELECT v.codice, titolo, COUNT(*) AS noleggiFROM video AS v
JOIN noleggio AS n ON n.video=v.codiceGROUP BY v.codice, titoloORDER BY titolo;
Alan Giacomin Basi di Dati Maggio 2009 52 / 54
Implementazione SQL Interrogazioni
Interrogazioni
Visualizzazione noleggi di Bianchi Silvio
SELECT DISTINCT titoloFROM video AS v
JOIN noleggio AS n ON n.video=v.codiceJOIN utente AS u ON u.cf=n.utente
WHERE u.cognome=’Bianchi’AND u.nome=’Silvio’
ORDER BY titolo;
Alan Giacomin Basi di Dati Maggio 2009 53 / 54
Implementazione SQL Interrogazioni
FINE
AutoreAlan Giacomin
Contattiemail: [email protected]: http://easyprog.altervista.org
Chatmsn: [email protected]: alan.giacomin
Alan Giacomin Basi di Dati Maggio 2009 54 / 54