Basi di Dati - statistica.unimib.itdellavedova/didattica/BasiDati/lucidi_DB... · Chiave Corso Non...

Basi di Dati

Gianluca Della Vedova

28 febbraio 2012

Gianluca Della Vedova Basi di Dati

Gianluca Della Vedova

Basi di DatiUfficio U7-244http://[email protected] 6ac2fc1del 28 febbraio 2012


Obiettivi

Concetti fondamentali di basi di datiBasi di dati relazionaliProgettazione concettualeProgettazione logicaSQL


Dati - Informazioni

Dati: elementi di informazione che devono essere elaboratiDati strutturati: elementi aggregati in maniera ordinataInformazione: elemento che consente di avere conoscenza di fatti.


Dati

Memorizzare dati in file: problemidimensionecondivisionepersistenza, affidabilitàprivatezza,efficienza, efficacia


Basi di dati

Basi di dati (database): dati organizzati secondo un modellopredeterminatoDBMS (Data Base Management System), strumenti che gestisconole basi di dati.


Schemi - Istanze

Istanze: dati memorizzati nel databaseSchemi: caratteristiche dell’istanza che usualmente non cambiano neltempo.

Matricola Cognome ⇐= Schema123456 Rossi123457 Verdi ⇐= Istanza


Schemi

schema logico: si utilizza il modello logico per descrivere l’intero DB.schema interno: si specificano le strutture datischema esterno: riguarda la specifica di porzioni del DB.Indipendenza fisica


Progettazione concettuale

Obiettivi: costruzione di uno schema di DBPartenza: specifiche informali, elenco delle richieste.Problemi: ottenere un giusto livello di astrazione ed essere aderentealle specifiche di partenza.


Raccolta informazioni

Indagine conoscitivaPassaggio da descrizione informale a descrizione precisa e nonambiguaEsplicitazione degli utilizzi massimi e medi previsti.


Schema concettuale

Desiderata:CorrettezzaCompletezzaChiarezzaMinimalità


Esempio 1: DB Universitario

Obiettivo: disegnare un DB che permetta di gestire tutte le informazionirilevanti per un’università. In particolare anagrafica studenti, docenti epersonale non docente, gestione esami e crediti, gestione stipendi.


Esempio 2: e-commerce

Obiettivo: disegnare un DB che permetta di gestire tutte le informazionirilevanti per un’azienda che riceve le ordinazioni esclusivamente viaInternet. Ad esempio www.amazon.com, www.bn.com.


Esempio 3: trasporti locali

Obiettivo: disegnare un DB che permetta di gestire tutte le informazionirilevanti per un’azienda che gestisce un sistema di trasporti terrestrida/per aereoporti.Ad esempio dovranno essere memorizzati i mezzi a disposizione, gli autistie tutti i viaggi effettuati.


Richieste

Necessità degli utentiCercare di adattarsi alla situazione preesistentePrincipio della minima sorpresa


Primi passi

Scegliere il livello di astrazioneScegliere uno standardSemplificare il più possibilema non troppoPorre le basi per una documentazione chiara e duratura.


Principi della progettazione

Semplicità: il disegno deve essere semplice, sia l’implementazione chel’interfaccia.Correttezza: il disegno deve esser corretto in tutti i suoi aspetti. Nonsono ammessi errori.Completezza: il disegno deve coprire tutti i casi ragionevolmenteattesi. La semplicità non può influenzare troppo la completezza.



Consistenza: il disegno deve essere consistente. Il disegno può essereleggermente più complesso o meno completo al fine di evitareincosistenze. La consistenza è importante come la correttezza.

[Richard Gabriel]



Se un concetto ha proprietà significative o descrive oggetti conesistenza autonoma, allora deve essere chiaramente distintoSe possibile descrivere un concetto semplice come attributo di unaltro concettoSe un concetto lega due concetti distinti, si crea una associazione.


Tecniche di progettazione

Top-down = dal generale al particolareBottom-up = dal particolare al generale


Top-down

Notare che un concetto (complesso) è formato da più concetti (piùsemplici) legati fra loro.Notare che un concetto (complesso) è formato da più concetti (piùsemplici) distinti.


Top-down

Notare che un’associazione è formata da più associazioni.Notare che un’associazione è meglio descritta da un concettoautonomo.Individuare nuove caratteristiche dei concetti o delle associazioni.


Bottom-up

Notare che alcuni oggetti condividono un concetto.Notare che esiste un’associazione fra alcune classi di oggetti.


Bottom-up

Notare che alcune classi di oggetti distinti condividono dellecaratteristiche comuni, ma continuano ad averne altre non condivise.Notare che alcune caratteristiche possono essere raggruppate in unconcetto oppure in un’associazione.


Confronto

Top-down: si può iniziare anche se non si conoscono tutti i dettagli,ma è necessaria una visione d’insieme.Bottom-up: può essere fatto anche da più persone.


Metodologia

Analisi dei requisitiIndividuare i concetti più importanti.Decomporre i concetti sulla base delle specifiche raccolte.Aggiungere nuovi concetti.Assicurarsi che le modifiche siano consistenti con lo schema.Verificare la qualità del disegno ottenuto.


Modello logico

Gerarchico: esiste una gerarchia fra i vari concetti da rappresentare.RelazionaleOrientato agli oggetti: ogni singolo fatto da rappresentare è unoggetto, che gode di proprietà specifiche e sui cui possono agirealcune azioni.


Progettazione

Progettazione concettuale: modello formale del problema,indipendente dalla soluzione computazionaleProgettazione logica: soluzione computazionale indipendente dalmodello fisico dei dati.Progettazione fisica: schema fisico dei dati.


Entità-Relazione

È modello concettuale dei datiPermette il progetto logicoEntità = oggettiRelazioni = associazioni fra oggetti


Entità

Classi di oggettiOccorrenza di una entità = singolo oggettoEntità


Relazioni

Associazioni concettuali fra classi di oggettiOccorrenza di una relazione = tupla contenente un oggetto per ognientità coinvolta

!!!!!!!!aaaaaaaa!!!!!!!!aaaaaaaa Relazione


Relazioni

CorsoStudente Esame


Attributi

Componenti elementari di un’entità.

Corso


Attibuti composti

Componenti non elementari di un’entità.

CorsoAttributo Composto


Generalizzazioni

totale se ogni occorrenza dell’entità generale è occorrenza di unadelle entità particolari.esclusiva se ogni occorrenza dell’entità generale è occorrenza di alpiù una delle entità particolari.


Generalizzazioni

Particolare 1

Particolare 1

Generale


Esercizio 1: versione 1

Studente

Docente

Corso

sostiene

tiene

Bisogna distinguere il concetto di corso da quello di esame. Si nota che docente estudente sono generalizzate da persone.



Studente

Docente Esame

Corso

Persona

Sostiene

registra

tiene

Iniziamo a mettere gli attributi. Ogni corso viene ripetuto negli anni, ma con dellemodifiche.



Studente

Docente Esame

Corso

Corso di studio

Occerrenza del corsoAnno

Aula

Ufficio

Orario ricevimento

Codice

Cognome

Nome

Matricola

Orario

Nome

Durata

Data

Voto

Persona

Data di nascita

Nome

Crediti

Iscritto

Sostiene

frequenta

tiene

registra

si riferisce a

Introduciamo il concetto di Dipartimento e Facoltà, oltre a quello di prova finaleper l’ottenimento del titolo.



Studente

Docente Esame

Corso

Tesi

Stage

Corso di studio

Titolo

Occerrenza del corsoAnno

Aula

VotoResponsabile

Titolo

Ufficio

Orario ricevimento

Codice

Cognome

Nome

Matricola

TitoloDipartimento Facolta’

Nome Nome

Orario

Nome

Durata

Data

Data

Voto

Persona

Data di nascita

Nome

Crediti

afferisce

locata in

organizzaha portato a

ha portato a

afferisce

si riferisce a

si riferisce arelatore

Relatore

correlato

re

Iscritto

Sostiene

frequenta

tiene

registra

po

rta

a

si riferisce a


Chiave

Corso

Non chiave

Chiave


Chiave con più attibuti

CorsoChiave 1

Chiave 2


Chiave esterna

Chiave che coinvolge un’altra entità, collegata tramite una relazione.

Anno

crediti

Nome

Orario

AulaOcc. corso

Corso


Cardinalità delle relazioni

Una relazione può coinvolgere un numero prestabilito di occorrenze diuna entità.Un docente afferisce ad un dipartimento e insegna almeno un corso.Un dipartimento è formato da almeno 15 docenti.



Afferisce DipartimentoDocente(1,1) (15,N)



Relazione uno ad uno (1, 1) (1, 1)Relazione uno a molti (1, 1) (1,N)

Relazione molti a molti (0,N) (0,N)



vendita

Fattura Vendita

LibroCliente


Commenti

Potrebbe essere interessante effettuare delle interrogazioni cheriguardino l’autore (o gli autori) dei libri.Devono essere tenuti in considerazione anche gli acquisti



acquisto

vendita

Fornitore

Cliente Autore

Persone

Libro

Fattura Acquisto

Fattura Vendita


Commenti

Determinare gli attributi.Scegliere i nomi delle relazioni.



Casa editrice

Codice

Nome

Cognome

Coidce

Dati

ISBNha scritto

Data

Num. Fattura

acquistato da (1,1)

Q.ta’

Costo unit.

Q.ta’

Costo unit.

acquisto

vendita

Num. Fattura

Data

Consegna

venduto a (1,1)

Fornitore

Cliente Autore

Persone

Libro

Fattura Acquisto

Fattura Vendita



Automezzi Viaggiatori

percorre viaggiaPercorsi


Commenti

Un viaggio inizia o termina in un aereoporto, ed è formato da unasequenza di luoghi di raccolta (o rilascio).Autisti diversi hanno abilitazioni diverse



Viaggiatori

percorre viaggiaTappe

Automezzi

Modello Autista

autorizzato

adiacente


Commenti

Mancano gli attributi.


Relazione

Associazione fra oggettiRelazione matematicaSiano A, B due insiemi, una relazione binaria è R ⊆ A× B.A, B: domini della relazione.


Esempio di relazione

A={Bianchi, Verdi, Rossi}B={8 Settembre, 9 Settembre, 10 Settembre}R={(Bianchi, 8 Settembre), (Bianchi, 9 Settembre), (Bianchi, 10Settembre), (Verdi, 9 Settembre), (Rossi, 8 Settembre)}


Tabelle

Tabelle: dati organizzati su righe e colonne.Bianchi 8 SettembreBianchi 9 SettembreBianchi 10 SettembreVerdi 9 SettembreRossi 8 Settembre


Tabella

Una tabella è una relazione se non ci sono righe ripetute.Una riga viene detta tuplaUna colonna viene detta attributo.I nomi degli attributi individuano lo schema della relazione.


Tabelle

Persona Data AppuntamentoBianchi 8 SettembreBianchi 9 SettembreBianchi 10 SettembreVerdi 9 SettembreRossi 8 Settembre


Modello relazionale

Schema di relazione = nome della relazione + nomi di attributiSchema di DB = schemi di relazioni distinteIstanza di relazione = insieme di tuple conformi allo schemaIstanza di DB = insieme di istanze di relazioni conformi allo schemadi DB


Schema flessibile

Il modello relazionale consente solo schemi fissati a priori.Talvolta sarebbe preferibile uno schema più flessibile.Valori nulli o mancanti NULL


Chiavi

Chiave = insieme di attributi che permettono di identificareunivocamente le tuple della relazione.Motivazione: indicizzazione delle tabelleChiave banale: l’insieme di tutti gli attributi.


Chiave - Superchiave

Superchiave (o chiave candidata) = insieme K di attributi tale che∀t1, t2, t1[K ] = t2[K ]⇒ t1 = t2.Chiave = insieme K di attributi tale che:K è superchiave.Ogni insieme K1 ⊂ K non è superchiave.


Chiavi

Ogni tabella deve avere una chiave.Definire una chiave per ogni tabella è parte del disegno di un DB.Tale chiave viene detta primaria e non sono ammessi valori nulli.


Vincoli

intrarelazionali: riguardano una sola relazione.Su un singolo valore.Su una singola tupla.Interessano l’intera relazione (es. esistenza di una chiave primaria).


Vincoli

interrelazionali: coinvolgono più relazioni.Vincoli di integrità: riguardano due relazioni R1, R2, ed un insiemeX di attributi di R1. Tale vincolo è soddisfatto se ∀t1[X ] ∈ R1,∃t2 ∈ R2, t.c. t1[X ] = t2[K ] con K chiave di R2.


Esempio di Vincolo

StudenteMatricola Anagrafica

EsameCorso Data Voto Matr. Studente Docente

Esame=R1, Studente=R2,Matr. Studente=X , Matricola=K .


Normalizzazione

Il disegno di un database non è unicoMa alcuni disegni sono meglio di altri


Ridondanza

Corso Data Voto Matr Docente2 22.06.2000 27 2 41 20.06.2000 24 2 22 23.06.2000 30 3 4

Dipendenza funzionale fra Corso e Docente


Problemi

Più tuple devono essere aggiornate per cambiare una informazione.Maggiore rischio di fallimento della transizione.Alcune operazioni non sono possibili singolarmente


Dipendenza funzionale

Sia R una relazione, e siano X ,Y due sottoinsiemi degli attributi diR, con X ∩ Y = ∅ e X ,Y 6= ∅,X → Yse t1(X ) = t2(X )⇒ t1(Y ) = t2(Y ).


Proprietà

Sia R una relazione, K una chiave di R e Y un sottoinsieme degliattributi di R con Y ∩ K = ∅, Y 6= ∅, alloraK → Y


Forme normali

Sia R una relazione e siano Xi → Yi le sue dipendenze funzionali conXi ∩ Yi = ∅R è in forma normale di Boyce e Codd se ogni Xi è superchiave diR.


Normalizzazione

È il processo con cui si ottiene un database equivalente, ma dovetutte le relazioni sono in forma normale.Idea: ogni dipendenza funzionale suggerisce una nuova relazione.Realizzazione: spezzare una relazione in più relazioni, in modo dagarantire che per ogni dipendenza funzionale Xi → Yi , Xi siasuperchiave.


Decomposizione senza perdita

Supponendo di decomporre R in R1 e R2

Inoltre X , X1, X2 sono i rispettivi insiemi di nomi degli attributi dellerelazioni.Sia A = X1 ∩ X2, allora la decomposizione è senza perdita seR = R1(A) on R2(A).


Conservazione delle dipendenze

Per ogni dipendenza funzionale X → Ydeve esistere una relazione che contenga (come schema) X ∪ Y


Decomposizione Impossibile

Sia R una relazione e sia X → Y .Se lo schema di R è X ∪ Y allora R non è decomponibile senzaperdita e mantenendo le dipendenze.


Forme normali

Sia R una relazione e siano Xi → Yi le sue dipendenze funzionali conXi ∩ Yi = ∅R è in terza forma normale se, per ogni iXi è superchiave di R oppure ogni attributo di Yi è contenuto in unachiave di R.È sempre possibile decomporre un insieme di relazioni in terza formanormale.


Algebra relazionale

Linguaggio imperativo (procedurale) di interrogazioniArgomenti delle operazioni: relazioniRisultati delle operazioni: relazioniComposizione delle interrogazioni


Proiezione

Permette di isolare degli attributi di una relazionePermette di isolare delle colonne di una tabella.Sintassi: πA(R), ProA(R)


Proiezione

Persona Data AppuntamentoBianchi 8 SettembreBianchi 9 SettembreBianchi 10 SettembreVerdi 9 SettembreRossi 8 SettembreπPersona(R)


Selezione

Permette di isolare delle tuple di una relazionePermette di isolare delle righe di una tabella.Sintassi: σA=valore(R), SelA=valore(R)

dove A è un attributo di R (a livello di schema) e valore è uno deipossibili valori di A.


Selezione

Persona Data AppuntamentoBianchi 8 SettembreBianchi 9 SettembreBianchi 10 SettembreVerdi 9 SettembreRossi 8 SettembreσPersona=Rossi(R)


Operazioni insiemistiche

unione di due insiemiintersezione di due insiemidifferenza di due insiemi


Unione

Permette di fondere due relazioni conformiDue relazioni sono conformi se hanno stesso schemaIl risultato ha stesso schema degli argomenti, e come tuple l’unione(insiemistica) delle tuple degli argomentiSintassi R1 ∪ R2


Unione

R1Persona Data AppuntamentoBianchi 8 SettembreBianchi 9 Settembre

R2Persona Data AppuntamentoBianchi 9 SettembreVerdi 9 Settembre


Unione

R1 ∪ R2Persona Data AppuntamentoBianchi 8 SettembreBianchi 9 SettembreVerdi 9 Settembre


Intersezione

Permette di estrarre l’informazione comune a due relazioni conformiDue relazioni sono conformi se hanno stesso schemaIl risultato ha stesso schema degli argomenti, e come tuplel’intersezione (insiemistica) delle tuple degli argomentiSintassi R1 ∩ R2


Intersezione




Intersezione

R1 ∩ R2Persona Data AppuntamentoBianchi 9 Settembre


Differenza

Permette di estrarre l’informazione unicamente presente nella prima didue relazioni conformiDue relazioni sono conformi se hanno stesso schemaIl risultato ha stesso schema degli argomenti, e come tuple ladifferenza (insiemistica) delle tuple degli argomenti


Differenza

Sintassi R1 − R2

La differenza non è commutativa.


Differenza




Differenza

R1 − R2Persona Data AppuntamentoBianchi 8 Settembre


Ridenominazione

Permette di cambiare un nome di attributo.Sintassi ρA2→A1(R), RidenomA2→A1(R).


Join

Permette di esaminare due tabelle, sfruttando informazioni ivicontenute ed eventuali vincoli di integrità.Operatore più importante (e computazionalmente costoso) per ladescrizione di interrogazioni.


Join

Join interno o naturale on (attributi con stesso nome)Join esterno: sinistro, destro, bilateraleEqui Join interno o naturale on (attributi scelti)


Join

Date due relazioni R1,R2 ed un attributo A1,A2 per ogni relazione, siottiene una nuova relazione ottenuta fondendo R1 e R2 dove A1 e A2hanno stesso valore.Sintassi R1(A1) on R2(A2)

R1(A1) on R2(A2) = {t1t2 : t1 ∈ R1, t2 ∈ R2, t1(A1) = t2(A2)}


Join

Schema di R1(A1) on R2(A2)

Schema di R1 ∪ Schema di R2 - {A1,A2} ∪ {A}


Join

R1Matricola Anagrafica1 Rossi Mario2 Verdi Giovanni3 Bianchi Annalisa

R2Corso Data Voto Matr Docente2 22.06.2000 27 2 41 20.06.2000 24 2 22 23.06.2000 30 3 4


Join

R1(Matricola)on R2(Matr)M Anagrafica Corso Data Voto Docente2 Verdi Giovanni 2 22.06.2000 27 42 Verdi Giovanni 1 20.06.2000 24 23 Bianchi Annalisa 2 23.06.2000 30 4


Join Esterno

R1 onR R2

R1 onL R2

R1 onF R2

Simile al join naturale, ma vengono tenute tutte le tuple di unarelazione (o di entrambe).


Join

Join esterno sinistro: si tengono tutte le tuple di R1.Join esterno destro: si tengono tutte le tuple di R2.Join esterno completo: si tengono tutte le tuple di R1 e R2.


Join Esterno Sinistro

R1(Matricola)on R2(Matr)M Anagrafica Corso Data Voto Docente1 Rossi Mario NULL NULL NULL NULL2 Verdi Giovanni 2 22.06.2000 27 42 Verdi Giovanni 1 20.06.2000 24 23 Bianchi Annalisa 2 23.06.2000 30 4


Join

è commutativaè associativa


Interrogazione

Formula del calcolo relazionaleEspressione dell’algebra relazionale.


Passaggio alle relazioni

Entità = Tabella con stessi attributiRelazione = Tabella con chiavi delle entità coinvolte


Esempio

CorsoStudente Esame

matricola

cognome

nome

datavoto codice

nome

STUDENTE (Matricola, Nome, Cognome)CORSO (Codice, Nome)ESAME (Studente, Corso, Data, Voto)


Raffinamento schemi

Rimozione delle ridondanzeRiclassificazione dei concetti (unire o dividere entità/relazioni)Scelta chiave primaria


Riclassificazione

Unire delle entità (legate da generalizzazione)Trasformare generalizzazione in relazioneEliminazione attributi multivalore


Scelta delle chiavi

Attributi che non ammettono valori nulliUn attributo che si presume venga utilizzato frequentemente.


Progettazione logica

Schema ERSchema ER raffinatoRelazioni


Passaggio alle relazioni

Entità = Tabella con stessi attributiRelazione = Tabella con chiavi delle entità coinvolteRidenominazione per evitare ambiguitàRelazione uno a molti = unica tabella con la prima entitàRelazione uno a uno = come uno a molti, ma può essere unita sia allaprima che alla seconda entità.


SQL

Linguaggio di definizione dei dati (DDL): operazioni su schemiLinguaggio di manipolazione dati (DML): operazioni su istanze,interrogazioni


SQL

Linguaggio procedurale (o imperativo)Vari standard, SQL-89, SQL-92, SQL-99


Domini elementari

Carattere:character [varying] (lunghezza)

Bit (0/1):bit [varying] (lunghezza)

Numeri: numeric, decimal, integer, float


Domini elementari

Tempo: date, time

Dati arbitrari: blob, clob

Vero/Falso: boolean


Definizione schema

Schema: create schema

Tabella: create table

Dominio: create domain


Creazione tabella

create table persona (ID integer primary key,nome char(20),cognome char(40)

)


Vincoli intrarelazionali

variabile not null,

variabile unique,

unique (variabile1,variabile2)

primary key ⇒ not null, unique


Creazione tabella: default

create table persona (ID integer primary key,nome char(20),secondonome char(20) default null,cognome char(40)

)


Creazione tabella: not null

create table persona (ID integer primary key,nome char(20),secondonome char(20) default null,cognome char(40) not null

)


Vincoli integrità

Se non è NULL allora lo stesso valore deve apparire in attributi uniquedi un’altra tabella.Un attributo: references

Più attributi: foreign key


Vincoli integrità

on update

on delete

cascade, set null, set default, no action

variabile references var2 on delete cascade


Esercizio

Creare le tabelle che seguono, scegliendo opportunamente i domini ed ivincoli:

Esame: ID corso, ID studente, ID docente, voto, dataCorsi: ID corso, nome corso


Soluzione

create table corsi (ID integer primary key,nome varchar(100) not null

)


Soluzione

create table esame (ID_corso integer not null references corsi(ID),ID_studente integer not null

references studente(ID),ID_docente integer not null

references docente(ID),voto integer not null,data date not null,primary key (ID_corso,ID_studente)

)


Vincoli intrarelazionali

create table esame (ID_corso integer not null references corsi(ID),ID_studente integer not null

references studente(ID),ID_docente integer not null

references docente(ID),voto integer not null,data date not null,primary key (ID_corso,ID_studente),check (voto >=18 AND voto<=30)

)


Inserimento dati

INSERT INTO tabella (lista campi)VALUES (lista valori)

INSERT INTO corsi (ID, nome)VALUES (1, ’Inf. Gen.’)


Aggiornamento dati

Modifica: update

Aggiunta: insert into

Rimozione: delete from


Aggiornamento schemi

Modifica: alter domain, alter table

Aggiunta: add column

Rimozione: drop column, drop table


Inserimento dati

ALTER TABLE corsi ADD COLUMN anno integer not null

ALTER TABLE corsi ALTER COLUMN anno integerSET DEFAULT 2000

ALTER TABLE corsi RENAME COLUMN anno annoaccademico


Indice

CREATE INDEX "nome_indice" ON "tabella(campo)"

CREATE INDEX "anagrafica_indice" ON"persone(cognome, nome)"


Interrogazioni in SQL

La definizione dello schema e dei dati è stata imperativa.La definizione delle interrogazioni è dichiarativa.


Interrogazioni in SQL: 1 tabella

select attributifrom tabellawhere condizione


Interrogazioni: esempi

create table esami (matricola integer,nome varchar(20),cognome varchar(20),corso varchar(20),voto integer,data date not null,primary key (corso,matricola,data)

)

Il voto è NULL se l’esame è stato fallito.



Tutte le tuple (e tutti i campi) della tabella:

SELECT *FROM esami



Tutte le tuple, ma solo nome e cognome, della tabella:

SELECT nome, cognomeFROM esami

Sono possibili righe ripetute




SELECT DISTINCT nome, cognomeFROM esami



Tutte le tuple, ma solo nome e cognome e voto su 110:

SELECT nome, cognome, voto*11/3 AS voto110FROM esami




SELECT DISTINCT nome, cognomeFROM esami



Nome e cognome di chi ha sostenuto Inf. Gen.

SELECT nome, cognomeFROM esamiWHERE corso = ’Inf. Gen.’



Quanti hanno sostenuto Inf. Gen.

SELECT COUNT(*)FROM esamiWHERE corso = ’Inf. Gen.’

Altre funzioni sono: MIN, MAX, SUM, AVG



Nome e cognome di chi ha preso almeno 27 in Inf. Gen.

SELECT nome, cognomeFROM esamiWHERE corso=’Inf. Gen.’ AND voto>=27



Nome e cognome di chi ha fallito almeno una volta Inf. Gen.

SELECT nome, cognomeFROM esamiWHERE corso=’Inf. Gen.’ AND

voto IS NULL


Viste

Una vista è una tabella virtuale.

CREATE VIEWvista_esami(nome, cognome) ASSELECT nome, cognomeFROM esamiWHERE corso = ’Inf. Gen.’

AND voto>=27


Interrogazioni: 2 tabelle

create table studenti (matricola integer primary key,nome varchar(20),cognome varchar(20)

)

create table esami (ID_studente integer references studenti(matricola),corso varchar(20),voto integer,data date not null,primary key (corso,matricola,data)

)


Interrogazione su 2 tabelle

Nome e cognome di chi ha sostenuto Inf. Gen.

SELECT nome, cognomeFROM studenti JOIN esamiON studenti.matricola=esami.ID_studenteWHERE corso = ’Inf. Gen.’


Interrogazione su 2 tabelle

Situazione di ogni studente per quanto riguarda Inf. Gen.

SELECT nome, cognomeFROM studenti LEFT JOIN esamiON studenti.matricola=esami.ID_studenteWHERE corso = ’Inf. Gen.’


Tipi Join

Interno: JOIN

Esterno sinistro: LEFT JOIN

Esterno destro: RIGHT JOIN

Esterno completo: FULL JOIN


Opzioni aggiuntive query

GROUP BY permette di raggruppare rispetto ad un insieme di attributiHAVING simile a clausola WHERE


Esempio

Quanti esami ha sostenuto ogni studente?

SELECT matricola, nome, cognome, count(*)FROM studenti JOIN esamiON studenti.matricola=esami.ID_studenteGROUP BY matricola


Esempio

Chi ha superato almeno 10 esami?

SELECT matricola, nome, cognomeFROM studenti JOIN esamiON studenti.matricola=esami.ID_studenteWHERE voto IS NOT NULLGROUP BY matricolaHAVING count(*)>=10


Subselect

La clausola where contiene una o più condizioni.Un membro della condizione può contenere una SELECTCostrutti ANY, NOT IN, EXISTS


Subselect: any

Mostrare ogni esame sostenuto da chi si chiama Rossi

SELECT ID_studente, voto, corsoFROM studenti, esamiWHERE ID_studente = ANY

(SELECT studenti.matricolaFROM studentiWHERE cognome=’Rossi’)


Subselect: all

Chi ha ottenuto il voto massimo in Inf. Gen.?

SELECT nome, cognomeFROM studenti JOIN esami ON

esami.ID_studente = studenti.matricolaHAVING esami.voto >= ALL

(SELECT max (voto)FROM esamiWHERE corso=’Inf.Gen.’)


Subselect: not in

Chi ha superato Inf. Gen., ma non Matematica I?

SELECT nome, cognomeFROM studenti JOIN esami ON

esami.ID_studente = studenti.matricolaWHERE corso=’Matematica I’ AND voto

IS NOT NULL AND esami.ID_studente NOT IN(SELECT ID_studenteFROM esamiWHERE corso=’Matematica I’ AND voto IS NOT NULL)


Alias

FROM Tabella T1, Tabella T2

T1, T2 sono due copie distinte di Tabella


Select con variabili

Chi ha superato Inf. Gen., ma non Matematica I?

SELECT nome, cognomeFROM studenti S JOIN esami E ON

E.ID_studente = S.matricolaWHERE corso=’Matematica I’ AND voto

IS NOT NULL AND E.ID_studente NOT IN(SELECT ID_studenteFROM esamiWHERE corso=’Matematica I’ AND voto IS NOT NULL)


Esercizio

Chi è l’autore più venduto dello scorso mese di Novembre?

CREATE VIEWvista_autori (id_persona,nome,cognome, copie) AS

SELECT DISTINCT id_persona,nome,cognome, sum(numero)

FROM autore, ha_scritto, libro, vendite, fatture_venditeWHERE autore.id_persona=ha_scritto.autore AND

ha_scritto.libro = libro.ISBN ANDlibro.ISBN=vendita.libro ANDvendita.fattura=fattura.numero ANDfattura.data>=’ 01/11/2001’ AND

fattura.data<=’ 30/11/2001’


Esercizio

Chi è l’autore più venduto dello scorso mese di Novembre?

SELECT id_persona,nome,cognome, copieFROM vista_autoriWHERE copie >= ALL(SELECT copieFROM vista_autori)


Ordinamento - Ricerca

L’opzione ORDER BY ordinaL’operatore LIKE permette una ricerca testuale


Trigger

Evento: modifica, inserimento o cancellazioneCondizioneAzione: da intraprendere sulla base di dati


Sintassi Trigger

CREATE TRIGGER nomeAFTER|BEFORE azioneON tabellaFOR EACH ROW|STATEMENTistruzioni da eseguire


Trigger: sintassi II

AFTER|BEFORE: indica se l’azione del trigger venga prima o dopol’evento.FOR EACH ROW|STATEMENT: l’azione del trigger deve essere eseguitauna sola volta o per ogni tupla interessataazione può essere INSERT, UPDATE o DELETE.


Trigger: variabili

Alcune variabili sono predefinite:old: la riga interessata, prima dell’evento.new: la riga interessata, dopo l’evento.old_table: la tabella interessata, prima dell’evento.new_table: la tabella interessata, dopo l’evento.


Sicurezza

Più utenti possono accedere alla base di dati.Il DBMS deve avere il concetto di utenteCREATE USER, DROP USER


Controllo accesso

Concedere la possibilità di scrivere o leggere tabelle o database:GRANT

Togliere la possibilità di scrivere o leggere tabelle o database: REVOKE

Ogni azione considere i permessi di lettura (READ), inserimento(INSERT), aggiornamento (UPDATE), cancellazione (DELETE)


Transazioni

Sequenza di operazioni: una operazione virtualeACID: Atomicità, Consistenza, Isolazione, Persistenza.BEGIN

COMMIT

Ogni transazione completa con successo o fallisce


Transazione

Una transazione è una operazione elementare (lettura/scrittura)sulla base di datiUna transazione deve essere Atomica, Consistente, Isolata, Definitiva(Persistente)


Locking

Si evitano accessi (in scrittura) contemporanei ammettendo un solo clientalla scrittura dei dati interessanti.

Table locking: ogni volta che bisogna modificare un dato, tutta latabella viene riservata.Row locking: quando si modifica un dato, la tupla interessata vieneriservata.


Contronto Lock

Table locking: per scrivere potrebbe essere necessario attendere piùa lungo.Row locking: interrrogazioni complesse in lettura potrebbero essereinfluenzate da una scrittura (es. somma dei valori in una colonna).Due alternative, modificare il risultato dell’interrogazione o ritardarela scrittura.


Stati della transazione

Begin: segnala l’inizio della transizioneCommit: la transazione è stata completata con successoAbort: la transazione non è completabile


Gestione transazioni

La transazione può fallire a causa di conflitti o problemi esterniFile di log (registro operazioni)Scrivere sul registro prima di iniziare l’operazione.Scrivere sul registro prima di completare l’operazione.Protocollo di commit a due (o più) fasi


Commit a 2 fasi

Si elegge un coordinatore che:1 invia a tutti i partecipanti VOTE REQUEST2 attende i voti dei partecipanti3 se tutti i voti sono YES allora invia COMMIT a tutti4 altrimenti invia ABORT a tutti5 Se si incorre in timeout invia ABORT a tutti


Commit a 2 fasi

Ogni partecipante:1 attende VOTE REQUEST2 invia il proprio voto al coordinatore3 se il voto è NO allora ABORT4 altrimenti attende la decisione dal coordinatore5 esegue la decisione ABORT6 Se si incorre in timeout ABORT


OLTP - OLAP

OLTP On Line Transaction Processing: i dati sono aggiornaticontinuamente, stato attuale dei datiOLAP On Line Analytical Processing: dedicato all’analisi dei dati,utilizzo dei dati storici


Data Warehouse

Dati integrati, provenienza eterogeneaDati StoriciDati aggregati, più tuple del DB vengono accorpateSeparato dal DB, su un’altra macchina.Aggiornamento periodico, non in sincronia col DB


Data Warehouse

1 Sorgente dei dati, i dati ottenuti devono essere puliti2 Data Warehouse server3 ETL, Extraction, Transformation, Loading. Come passare dalla

sorgente dei dati al DW server4 Strumenti di analisi


Modello multidimensionale

Fatto, cosa si vuole misurareDimensione, una variabile analizzataMisura, valutazione di un fatto. Numero


Esempio

Dati di vendita di un supermercato, aggregati per punto di vendita egiorno.

Fatto: venditeDimensioni: Giorno, punto di vendita, prodotto, marca, . . .Misura, valutazione di un fatto. Numero


DW nel modello relazionale

Schema a stellatabella dei fatti, centrale e normalizzata, chiave = insiemi vincoli diintegritàtabelle delle dimensioni, periferiche, non normalizzate, chiave di unattributocostruzione di tutti gli indici possibili


Licenza d’uso

Quest’opera è soggetta alla licenza Creative Commons: Attribuzione-Condividiallo stesso modo 3.0.http://creativecommons.org/licenses/by-sa/3.0/Sei libero di riprodurre, distribuire, comunicare al pubblico, esporre in pubblico,rappresentare, eseguire, recitare e modificare quest’opera alle seguenti condizioni:

Attribuzione — Devi attribuire la paternità dell’opera nei modi indicatidall’autore o da chi ti ha dato l’opera in licenza e in modo tale da nonsuggerire che essi avallino te o il modo in cui tu usi l’opera.

Condividi allo stesso modo — Se alteri o trasformi quest’opera, o se la usiper crearne un’altra, puoi distribuire l’opera risultante solo con una licenzaidentica o equivalente a questa.


Basi di Dati - statistica.unimib.itdellavedova/didattica/BasiDati/lucidi_DB... · Chiave Corso Non...

Documents

Transcript of Basi di Dati - statistica.unimib.itdellavedova/didattica/BasiDati/lucidi_DB... · Chiave Corso Non...