Che materia stai cercando?

Appunti completi corso Basi di dati

Appunti completi del corso Basi di dati tenuto dal prof Giuseppe Pozzi al Politecnico di Milano. Argomenti trattati:
- Modello relazionale
- Linguaggi formali (algebra relazionale, calcolo relazionale, Datalog)
- SQL (itnerrogazioni semplici, interrogazioni complesse con raggruppamento e ordinamento, query annidate e set queries, viste, trigger, integrazione con altri linguaggi di programmazione)
-... Vedi di più

Esame di Basi di dati docente Prof. G. Pozzi

Anteprima

ESTRATTO DOCUMENTO

Ci sono dei meccanismi che permettono di far vedere e modificare i dati a chi ne ha diritto,

ovvero implementano un CONTROLLO DELL'ACCESSO. L'utente _system è il

superutente che può fare tutto e accedere a tutto. Ogni privilegio ha cinque informazioni:

Risorsa concessa

– Utente che concede il privilegio

– Utente che riceve il privilegio

– Operazione consentita

– Se il nuovo utente può concedere privilegi ad altri utenti

L'SQL offre sei tipi di privilegi:

inserire nuovi oggetti nella risorsa

insert,

– modificare il contenuto della risorsa

update,

– rimuovere un oggetto dalla risorsa

delete,

– effettuare interrogazioni sulla risorsa

select,

– costruire un vincolo di integrità referenziale che coinvolge la risorsa

reference,

– usare la risorsa per definire un nuovo schema

usage,

– tutti i privilegi

all privileges,

Per concedere privilegi si usa il comando grant

< Privilegi | >

grant all privileges on

Risorsa Utenti [ ]

to with grant option

Il dice se l'utente può propagare i privilegi.

grant option

Per togliere privilegi si usa il comando revoke

Privilegi Risorsa Utenti

revoke on from

[ | ]

restrict cascade

Il toglie il privilegio solo agli utenti indicati, mentre il propaga la

restrict cascade

rimozione anche agli utenti che l'hanno ricevuto attraverso altri utenti.

Grazie alle viste possiamo dare a ciascuna categoria di utenti i giusti privilegi e la

possibilità di leggere solo una parte di tutta una tabella.

L'SQL mette a disposizione degli utenti delle PROCEDURE, ovvero in pratica le funzioni.

Tra l'altro le funzioni possono anche essere programmate, il problema però è che non

esiste un linguaggio universale per definire le procedure. Ogni DBMS ha il suo linguaggio

e bisogna impararselo. Per esempio Oracle ha il suo linguaggio Oracle PL/SQL.

Le basi di dati cosiddette ATTIVE permettono di definire delle procedure che vengono

attivate automaticamente quando si verificano certi eventi con certe condizioni. Queste

procedure si chiamano TRIGGER e seguono il paradigma ECA cioè EVENTO-

CONDIZIONE-AZIONE: quando si verifica un evento (insert, ed è

update, delete)

verificata una certa condizione, si esegue il trigger. La loro definizione è stata

standardizzata con SQL-3.

Il trigger può avere la GRANULARITÀ di tupla o di operazione. Ovvero se un'operazione

su 10 tuple attiva un trigger, esso può essere attivato dieci volte (granularità di tupla)

oppure una volta sola (granularità di operazione).

I comandi per creare trigger e regole non sono standard! Ogni DBMS ha un suo dialetto

SQL per definire le regole. Vediamo un esempio dalle slide:

create trigger GestioneRiordino

after update of QtaDisp on Magazzino

when (new.QtaDisp < new.QtaRiord)

for each row

X exception

begin

if new.QtaDisp < 0 then raise(X);

insert into Riordino

values(new.CodProd,sysdate,new.QtaRiord)

end

La parola chiave indica che prima si compie l'operazione e poi il trigger. Il contrario

after

si fa con Mentre la parola chiave è una variabile interna che fa riferimento al

before. new

alla tupla dopo l'operazione, mentre fa riferimento alla vecchia tupla (prima

old

dell'operazione).

I trigger si utilizzano quando dobbiamo modificare delle tuple per garantire dei vincoli,

mentre le asserzioni semplicemente permettono o fermano un'operazione. I trigger

possono fungere da asserzioni se come clausola inseriamo il vincolo violato e come

when

operazione usiamo la parola chiave rollback

Se violiamo dei vincoli possiamo fare due cose:

Abortire la transazione, quindi impedire l'operazione dando un'eccezione con raise

– Modificare il contenuto dei dati in modo che i vincoli vengano rispettati

Nel primo caso non dobbiamo preoccuparci se l'operazione viene abortita. Il database

rimane consistente con i dati precedenti all'operazione. Questo perché facciamo una

TRANSAZIONE, ovvero una serie di operazioni che si eseguono o nel loro insieme o non

si eseguono proprio. Nel secondo caso invece dobbiamo fare attenzione alle modifiche

automatiche, che noi non possiamo vedere direttamente. Dall'esempio sulle slide si vede

che un trigger può anche autoattivarsi facendo SELF-TRIGGERING. Ma peggio ancora un

trigger può attivarne un altro che ne attiva un altro ancora e si genera un PING PONG tra

due o più trigger, quindi bisogna porre attenzione alle possibili chiamate nidificate.

Risulta chiaro che i trigger potenziano molto il nostro database, ma rallentano il

programma (ogni operazione sui dati necessita il controllo delle condizioni di tutti i trigger

inseriti) e rendono il database non portabile su altri DBMS. È un po' come costruire un

sottomarino a idrogeno...

L'SQL mette a disposizione diverse funzioni built-in per semplificarci la vita, tra cui

e

coalesce, nullif case:

Lista ) seleziona il primo elemento non nullo da una lista

coalesce(

Input, Value ) restituisce il valore nullo se input = value, altrimenti ritorna input

nullif( Condizione Espr

case when then

{ Condizione Espr }

when then

Espr

else end

Similmente al case dei linguaggi programmazione, ritorna il risultato della prima

espressione vera della lista.

Esistono poi una caterba di funzioni matematiche (che non si sa dove possano servire in

una query), ma anche funzioni sulle stringhe e sulle date. Quest'ultime sono utili:

now

today

date_part()

date_trunc()

Argomenti per date_part() e date_trunc():

year, month, day, hour, minute, second, millisecond, microsecond,

dow, week, epoch, decade, century, millennium

Normalmente i comandi SQL non vengono utilizzati direttamente da un utente, ma

vengono inclusi all'interno di un'applicazione. Per fare questo ci serve SQL EMBEDDED,

ovvero l'integrazione dei comandi SQL nel codice di altri linguaggi di programmazione. In

C ogni comando è introdotto da e terminato con il punto e virgola. Ci pensa poi

exec sql

il preprocessore a trasformare questi comandi nel codice opportuno appoggiandosi ad una

libreria dipendente dal DBMS. Ecco un esempio qua sotto:

main() {

exec sql begin declare section;

char *NomeDip = "Manutenzione";

char *CittaDip = "Pisa";

int NumeroDip = 20;

exec sql end declare section;

exec sql connect to utente@librobd;

if (sqlca.sqlcode != 0) {

printf("Connessione al DB non riuscita\n"); }

else {

exec sql insert into Dipartimento

values(:NomeDip,:CittaDip,:NumeroDip);

exec sql disconnect all;

}

}

Prima di utilizzare i comandi SQL bisogna inserire un'area di dichiarazione delle variabili

che useremo nelle query. La variabile dice se il comando è andato a

sqlca.sqlcode

buon fine, e in quel caso vale zero. Le variabili del programma utilizzate nelle query

devono essere introdotte dai due punti.

I CURSORI cercano di risolvere il problema di IMPEDENCE MISMATCH, cioè i linguaggi

di programmazione gestiscono un record alla volta, mentre l'SQL è orientato agli insiemi.

Ci sono numerosi comandi e istruzioni (che non vediamo) per usare i cursori e se sei

curioso c'è un esempio in C sulle slide.

L'SQL DINAMICO contiene altre numerose istruzioni per gestire dinamicamente le query,

cioè quando non si conosce a priori quello che bisogna fare. Ci sono un casino di librerie

per integrare il DBMS nel codice dei programmi, per esempio ODBC e ADO su Visual

Basic e JDBC su Java.

Con questa lezione si inizia la seconda parte del corso, che riguarda la progettazione delle

basi di dati. Come nell'ingegneria del software, anche per i DB esistono una specifica dei

requisiti, una progettazione, un'implementazione, la validazione e il collaudo. La parte su

cui ci concentreremo di più è la progettazione.

La specifica dei requisiti per una buona progettazione segue le stesse regole di Sweng: si

ha un cliente che ci dice cosa vuole nel modo più chiaro possibile e si genera un

documento in linguaggio naturale (quindi ambiguo). Da questo si realizzano dei data

dictionary per specificare il significato dei termini, dei diagrammi di flusso e degli use case

diagram.

D'ora in poi faremo l'assunzione tecnologica che le applicazioni girano su un sistema

client-server dove il database è su un server apposito. Definiamo un database

MONOLITICO quando si trova su una sola macchina. Se si trova su più macchine può

essere DISTRIBUITO, cioè le macchine usano tutte lo stesso DBMS. Dei plug-in di Oracle

permettono di vedere il database come se fosse un tutt'uno anche se i dati sono su

macchine diverse. Oppure può essere FEDERATO, quando anche i DBMS sulle varie

macchine sono diversi.

Le fasi della progettazione sono tre:

Progettazione CONCETTUALE

– Progettazione LOGICA

– Progettazione FISICA

La progettazione concettuale parte da una descrizione in linguaggio naturale e ne

produce una descrizione formale, grafica, indipendente dal DBMS e costituisce lo schema

concettuale detto ER (entità relazioni).

La progettazione logica deve produrre lo schema logico in funzione del modello usato dal

DBMS (che può essere gerarchico, reticolare, relazionale, ad oggetti, XML). Lo schema

logico dipende dal modello usato ma non dal DBMS.

La progettazione fisica dipende dal DBMS utilizzato e dalla tecnologia di memorizzazione.

Ci dice per esempio che normalmente i dati stanno su un hard disk, ma esistono basi di

dati che memorizzano tutto sulla memoria centrale, con tutte le limitazioni del caso.

Quando costruiamo il modello concettuale dobbiamo astrarre alcune caratteristiche. Il

concetto principale è quello dell'astrazione, di cui ne esistono tre tipi:

CLASSIFICAZIONE: definire classi di oggetti

– AGGREGAZIONE: mettere insieme oggetti appartenenti ad una stessa classe

– GENERALIZZAZIONE: definire oggetti che sono specializzazioni di altri oggetti

Il modello ER – ENTITY RELATIONSHIP è il metodo standard per descrivere la struttura

di una base di dati. È stato ideato da un certo Chen che ora sta in pensione in Florida e si

fa la bella vita.

Le ENTITÀ sono le classi, quelle importanti e significative per l'applicazione. Graficamente

il concetto di entità è rappresentato con un rettangolo contenente il nome dell'entità.

Le ASSOCIAZIONI (nome corretto in italiano) sono collegamenti tra le entità.

Graficamente sono rombi con il nome dell'associazione e si uniscono tra le entità con linee

tratteggiate. È da notare che graficamente non si indica la direzione dell'associazione.

Sia le entità che le associazioni possono avere degli ATTRIBUTI (nel modello relazionale

sono proprio le colonne della tabella). Graficamente sono come le interfacce dell'UML,

cioè linee che escono fuori con un pallino alla fine. Gli attributi di un'associazione

appartengono ad entrambe le entità che partecipano nell'associazione.

Concetto significativo per il contesto applicativo → Entità

Concetto che è un dato elementare → Attributo

Concetto che è relazione tra oggetti → Associazione

La CARDINALITÀ delle associazioni è un numero che si inserisce in ogni associazione e

dice quante istanze possono essere coinvolte. Rispetto all'UML non abbiamo molte

alternative: è una coppia (min, max) dove min può essere 0 o 1 e max può essere 1 o N.

Come ci aveva detto Brambi, la posizione della cardinalità in basi di dati è al contrario

rispetto all'UML, però ha senso perché in UML è solo una freccia mentre qua abbiamo un

rombo gigante.

Ci sono anche le AUTOASSOCIAZIONI, cioè associazioni con se stessi, e pure le

ASSOCIAZIONI TERNARIE che mettono un po' di confusione. Per capire meglio come

funzionano devi pensare che le associazioni sono una fotografia di un istante nella base di

dati, così alcune cose si chiariscono meglio.

Anche gli attributi possono avere una loro cardinalità. Sono scalari se hanno un solo

valore perciò si indica con (1, 1) (ma non è d'obbligo perché è il default), oppure possono

avere più valori per cui lo indichiamo con (1, n). Ma un attributo può essere COMPOSTO

ovvero formato da sottoattributi (per esempio la data ha giorno, mese, anno). E se

vogliamo spingerci più in là possiamo pure fare un attributo composto con cardinalità

multipla. Anche per gli attributi vale l'opzionalità con (0, 1) oppure (0, n).

Gli IDENTIFICATORI sono attributi univoci e rappresentano in pratica la chiave primaria.

Si rappresentano con un pallino nero anziché bianco. Se gli identificatori sono più di uno si

usa un simbolo simile agli attributi composti.

Le ENTITÀ DEBOLI sono entità che non esistono da sole, possono esistere solo insieme

ad una entità forte. Se si cancella un forte si cancellano anche i deboli associati. Come

simbolo si usa il doppio rettangolo oppure con un identificatore che indica l'entità forte

associata. Per fare un esempio se cancelliamo un'università cancelliamo tutti gli studenti

associati. Se eliminiamo un albergo cancelliamo le camere associate.

Le entità sono simili alle classi della OOP: si possono fare l'ereditarietà e la

generalizzazione di entità, graficamente rappresentati con frecce in modo da creare una

gerarchia. Tuttavia la gerarchia può essere TOTALE o PARZIALE. Gerarchia totale

significa che un'entità generica è obbligata a essere anche una delle entità specializzate

(per esempio un impiegato è per forza un interno o un esterno), mentre parziale significa

che l'entità generica può specializzarsi ma non è obbligata a farlo (per esempio un

cittadino può essere un cacciatore, un pescatore, nessuno dei due o un cacciapescatore).

Inoltre la specializzazione può essere ESCLUSIVA o IN OVERLAPPING: nel primo caso

un'entità generica può specializzarsi solo su una entità figlia, mentre in overlapping

abbiamo che può specializzarsi in più entità figlie (vedi l'esempio precedente di cacciatori

e pescatori). Queste quattro proprietà sono indicate rispettivamente dalle lettere t, p, e, o.

Ogni specializzazione può essere una tra (t, e), (t, o), (p, e), (p, o) e queste etichette

vengono poste accanto alla freccia di generalizzazione.

La gerarchia può essere profonda quanto vuoi ed è ammessa pure l'ereditarietà multipla:

una entità può specializzare due entità padri contemporaneamente (per esempio un

cittadino che è sia studente che lavoratore).

La fase successiva del design è data dalla strategia di progetto utilizzata nella stesura di

una base di dati. Le tecniche di sviluppo principali sono il TOP DOWN e il BOTTOM UP.

La strategia top down prevede di partire da una visione globale del problema per poi

andare a raffinare il progetto in vari sottosistemi che comunicano fra loro. Per dire, se devo

fare un edificio dico quanto grande deve essere, quanti piani deve avere, dove metto scale

e ascensori ecc...

Nel bottom up si parte dai singoli sottoproblemi, si cerca di ingrandirli e poi di assemblarli.

In pratica faccio il muro e ci costruisco la casa intorno. Ok l'esempio della casa non è

molto intelligente visto che non puoi fare prima il muro e poi costruire il resto intorno, ma

nel mondo software si può fare così ed è un metodo funzionante. La strategia bottom up

poi si suddivide in due sottocategorie: A MACCHIA D'OLIO (in inglese INSIDE OUT) e

strategia MISTA.

Il metodo top down è più ordinato, sensato e razionale, perché si parte da una visione

d'insieme, però è più difficile da attuare proprio perché bisogna avere la capacità di vedere

ogni aspetto del sistema. Il bottom up invece viene preferito per sistemi di grosse

dimensioni, ma siccome parte da sottoproblemi risolti in modo indipendente, è inevitabile

che si creino conflitti tra i sottosistemi. Il progetto finale deve essere gestito e assemblato

da qualcuno che sappia ragionare in top down.

Il design in top down puro parte da uno schema iniziale molto semplice di entità

fondamentali, che poi per raffinamenti successivi vengono ampliante fino ad arrivare

all'insieme di tutte le entità necessarie. Quindi il top down permette di partire da idee

generiche da precisare gradualmente, ma è difficile da attuare per sistemi complessi a

causa dei problemi nell'avere una visione globale fin da subito.

Il disegno del cono secondo il prof è una delle slide più difficili da capire: il significato del

cono equilibrato è che il manager, grande capo che non capisce niente, vede le cose ad

alto livello, poi abbiamo gli utenti con una visione più grande del sistema e infine abbiamo

la visione tecnica riservata ai progettisti. In un progetto squilibrato abbiamo invece i diversi

livelli di astrazione che si intersecano e non va bene.

La tecnica bottom up a macchia d'olio è d'aiuto nel definire sistemi complessi: si definisce

nel dettaglio un sottoproblema e a partire da quello si definiscono gli altri. Nonostante sia

una strategia bottom up, i conflitti sono pochi perché creiamo entità nuove partendo da

quelle che abbiamo già.

Per progetti molto vasti, si usa la tecnica mista: si schierano più gruppi di progettisti

ognuno dei quali si occupa di un solo sottosistema in top down. Alla fine i vari pezzi

vengono riunificati in bottom up. Qui sì che i conflitti abbondano! Un modo per limitarli è

realizzare fin dall'inizio un sistema scheletro ad alto livello per identificare le entità più

importanti. I progettisti dovranno poi espandere lo scheletro attenendosi alle specifiche.

Facile a dirsi, a farsi con decine di gruppi che lavorano invece è un bordello.

Ai fini dell'esame il prof consiglia la strategia a macchia d'olio e soprattutto di

DOCUMENTARE il progetto in top down a posteriori!

La qualità di uno schema concettuale è data da queste proprietà:

COMPLETEZZA

– CORRETTEZZA

– LEGGIBILITÀ

– MINIMALITÀ

– AUTOESPLICATIVITÀ

Completezza e correttezza sono qualità chiare: ci sono tutte le informazioni necessarie e

sono corrette. I dati devono consentite il funzionamento delle applicazioni previste su di

essi. Per la correttezza deve essere possibile inserire dati anche incompleti quando la

base di dati è in fase di costruzione. Per esempio il criterio “almeno sempre uno” ha senso

dopo che ho inserito una tupla, perché all'inizio la tabella è per forza vuota. Sulla

completezza fa l'esempio di Como, per cui se la progettazione è sbagliata non puoi fare

l'interrogazione “cerca gli abitanti di Como” perché escono pure i milanesi di corso Como.

Anche la leggibilità è una qualità chiara: le entità devono essere comprensibili. Utile è fari

diagrammi su una griglia, infatti all'esame avremo fogli a quadretti.

Bisogna stare attenti alla ridondanza, cioè inserire entità e associazioni che non servono.

Si capisce che c'è una ridondanza quando abbiamo un anello, perché significa che

possiamo andare da un punto all'altro seguendo due percorsi. Ciò che bisogna fare è

togliere un'associazione alla volta per vedere se i vincoli rimangono rispettati e si possono

ricavare tutti i dati necessari (ovvero se da un punto puoi risalire a tutti gli altri in modo

univoco). L'esempio sulle slide di sale e macchine funziona bene.

L'autoesplicatività dice che lo schema deve essere fatto in modo che la maggior parte

delle informazioni siano comprensibili guardando direttamente lo schema senza tanti

giramenti di testa e lettura di 57381 pagine di documentazione.

L'ultimo passo è il POST PROCESSING: verificare alla fine che il lavoro fatto sia

interamente corretto, una checklist.

Tutte le entità devono avere un identificatore

– Tutte le associazioni devono avere una cardinalità definita

– Le entità devono essere significative, cioè devono rappresentare più di un attributo

– ed essere collegabili con altre entità per mezzo di associazioni

Le generalizzazioni devono essere utili per ereditare proprietà

Questa fase può essere un po' snobbata ma è importante, perché permette di semplificare

molto lo schema finale, per esempio trasformando entità inutili in semplici attributi.

Siamo giunti al grande momento del PROGETTO LOGICO, cioè la stesura delle tabelle.

Lo schema ER dà una descrizione sintetica e visiva. È astratto e non considera la parte

implementativa ed a questo scopo serve il progetto logico. Il DBMS non può capire

direttamente il linguaggio ER, invece alcuni strumenti automatici possono generare le

tabelle partendo dal progetto logico.

Il progetto produce trasformazioni e traduzioni dello schema ER con le seguenti fasi :

Eliminazione delle gerarchie ISA

– Selezione delle chiavi primarie ed eliminazione degli identificatori esterni (cioè

– l'entità forte da cui deriva l'entità debole)

Normalizzazione degli attributi composti o multipli (ovvero spacchettarli in attributi

– singoli)

Tradurre entità e associazioni in schemi di tabelle

– Verificare la normalizzazione

Il modello relazionale non prevede gerarchie, per cui vanno rimosse seguendo uno di

questi metodi:

Mantenimento delle entità con associazioni

– Collasso verso l'alto

– Collasso verso il basso

Nel caso del mantenimento delle entità, vengono tenute tutte le entità e la gerarchia è

sostituita da associazioni. È un metodo sempre utilizzabile.

Nel collasso verso l'alto si riuniscono le entità figlie nell'unica entità padre, che conterrà

un attributo SELETTORE per decidere quale entità figlia viene rappresentata da una

specifica tupla. Per le gerarchie totali esclusive il selettore ha N valori (uno per ogni entità

figlia), mentre per le gerarchie parziali totali il selettore ne ha N+1 dove il valore in più

indica istanze che non appartengono a nessuna sottoentità. Nel caso dell'overlapping ci

vuole un selettore per ogni valore booleano (nel caso di gerarchie parziali possono anche

essere tutti falsi). Questo metodo favorisce la presenza di tanti valori nulli, dato che gli

attributi obbligatori per i figli diventano opzionali per il padre.

Nel collasso verso il basso si elimina l'entità padre e i suoi attributi vengono replicati

sulle entità figlie. È utile quando ci sono numerosi attributi tipici delle entità figlie e si può

accedere ad esse direttamente. Tuttavia non è utilizzabile per gerarchie parziali (dove

metto gli attributi che non appartengono a nessuna specializzazione?) ed è ridondante per

l'overlapping (i dati vanno ripetuti su ogni entità).

Per la scelta della chiave primaria si preferiscono attributi semplici a quelli composti e

chiavi interne a quelle esterne. In generale la chiave primaria è l'attributo utilizzato più

frequentemente, a patto che rispetti i vincoli di unicità e minimalità. Per eliminare gli

identificatori esterni bisogna trasportare la chiave primaria dell'entità forte all'interno

dell'entità debole come chiave esterna.

Il modello relazionale non supporta gli attributi composti, che vanno riportati in attributi

semplici. Le soluzioni possibili sono spezzare l'attributo composto nelle sue componenti,

creando più attributi atomici, oppure eliminare le componenti e considerare l'attributo come

semplice.

Nel processo di traduzione l'idea base è che ogni entità è tradotta in una relazione con gli

stessi attributi e la stessa chiave primaria. Ogni associazione è una relazione con gli stessi

attributi e le chiavi delle entità associate.

La traduzione standard è sempre possibile e funziona sempre nelle associazioni N a M.

Altre forme di traduzione sono utili nel caso di cardinalità più ristrette, perché fondono in

una stessa relazione entità e associazioni. Per esempio in una associazione 1 a N l'entità

che partecipa con cardinalità (1, 1) può fondersi con l'associazione realizzando solo due

relazioni. Nel caso di cardinalità (1, 0) la relazione dell'entità può avere attributi nulli.

In una associazione 1 a 1 possiamo direttamente creare una relazione sola e usare come

chiave primaria quella che ci piace di più tra le due disponibili, ma solo se entrambe le

entità hanno cardinalità (1, 1). Se una delle due entità ha cardinalità (0, 1) siamo obbligati

a scegliere come chiave primaria quella dell'altra entità. Nel caso invece in cui entrambe le

entità abbiano cardinalità (0, 1) si è costretti a creare due relazioni separate.

Una autoassociazione N a M viene tradotta con una relazione per l'entità e un'altra per

l'associazione. Nell'associazione avremo due attributi che fanno riferimento alla stessa

entità, perciò bisognerà assegnare due nomi distinti.

Un'autoassociazione 1 a N si traduce con una sola relazione in cui la chiave viene

ripetuta due volte: una volta come chiave primaria della relazione, una seconda volta

come chiave esterna che fa riferimento alla relazione stessa.

Nel caso delle autoassociazioni 1 a 1 è meglio creare due relazioni: una per l'entità che

avrà una sola chiave e un'altra per la relazione, in cui ci saranno due chiavi esterne verso

l'entità e una di queste viene scelta come chiave primaria per l'associazione.

Le associazioni n-arie seguono la traduzione standard, tuttavia sono un po' rognose

perché dovremmo avere una chiave composta, ottenuta unendo le chiavi di tutte le entità

coinvolte. Se la base di dati non è stata progettata correttamente, la chiave composta non

è minimale, ovvero si può togliere una delle componenti della chiave composta in quanto

ridondante.

NON SI CAPISCE UN CAZZO

Dimentica la schifezza scritta qui sopra. Quando fai il progetto concettuale e logico segui

questi passaggi:

Individua le entità fondamentali con i loro attributi

– Individua le associazioni tra le entità e gli attributi

– Seleziona le cardinalità

Dopo aver controllato tutto bisogna trasformare il grafico in uno schema di tabelle. Segui

queste regole semplici:

Ogni entità ha una tabella con gli attributi necessari

– Le associazioni si traducono in modi diversi a seconda delle cardinalità

In una cardinalità UNO A MOLTI non c'è bisogno di fare una tabella in più: nell'entità a uno

metti un attributo in foreign key sull'entità a molti insieme agli attributi dell'associazione.

In una cardinalità MOLTI A MOLTI bisogna fare una tabella a parte per l'associazione:

essa avrà come chiavi primarie le chiavi delle due entità e i propri attributi.

L'opzionalità non è nulla di strano: semplicemente un attributo può essere null.

Per eliminare le gerarchie, puoi usare due tecniche:

Collasso verso l'alto: tutte le entità figlie collassano nell'entità generica. Quella

– prende tutti gli attributi dei figli più un attributo FLAG che dice quale figlio in

particolare stiamo usando. In questo modo possiamo dire quali attributi saranno

nulli e quali no.

Collasso verso il basso: facciamo una tabella per ogni figlio. Ognuno avrà la

– stessa chiave primaria (quella del padre) ma attributi diversi.

Questi metodi funzionano bene per le gerarchie TE. Per altri tipi bisogna ingegnarsi un po'.

La NORMALIZZAZIONE ha lo scopo di identificare ed eliminare le ridondanze e le

anomalie del progetto logico. La ridondanza è dovuta al fatto che nella stessa tabella si

ripetono alcune informazioni e questo per esempio genera problemi nell'aggiornamento

(se modifichi il dato in un punto lo devi cambiare dappertutto, altrimenti i dati sono

inconsistenti), oppure problemi di cancellazione (cancellando una tupla perdi informazioni

di altre entità) e di inserimento (non si può inserire una tupla se mancano degli attributi).

Insomma l'esempio fornito è un vero disastro ed è il risultato di quando uno fa una base di

dati senza prima costruire lo schema ER e farne la traduzione.

Quello che succede è una DIPENDENZA FUNZIONALE. Si parla di dipendenza

funzionale se, prese due tuple T e T all'interno di una tabella e due attributi X e Y che

1 2

non siano chiavi primarie, X = X implica Y = Y . Diciamo che una relazione è IN FORMA

1 2 1 2

NORMALE se ogni dipendenza X → Y si verifica con X che appartiene all'insieme delle

chiavi della relazione.

Se la relazione non è in forma normale, possiamo allora spezzarla in più tabelle che lo

sono semplicemente facendo delle project. Nell'esempio dato, se ogni impiegato ha

sempre lo stesso stipendio e ogni progetto ha sempre lo stesso budget, posso creare due

relazioni Impiegato e Progetto che si prendono rispettivamente gli attributi Stipendio e

Budget. Se rivogliamo il tabellone gigante con tutte le informazioni insieme basta fare un

join tra le tabelle. Ma questo è garantito se la decomposizione è stata effettuata in modo

corretto: se la decomposizione è errata il join fa perdere informazioni o addirittura le

inventa!

Una buona decomposizione deve prevedere che la ricostruzione delle informazioni

avvenga con il join su attributi CHIAVE. I join su relazioni N a M sono pericolosi perché

sono quelli che generano più facilmente informazioni sbagliate. Morale della favola:

progettare i dati in modo sensato è difficile. È importante il lavoro di gruppo e soprattutto

cercare di inserire conoscenza il più possibile nelle tabelle e meno vincoli dovremo

mettere, ma allo stesso tempo non bisogna eccedere facendo schemi poco leggibili. Per

questo anche la documentazione ha un ruolo importante.

Il mitico modello XML, che sta per EXTENSIBLE MARK-UP LANGUAGE, è un metodo

per memorizzare darti non strutturato, a differenza del modello relazionale. L'XML può

separare i dati dalla loro rappresentazione, cioè il modo in cui visualizzarli. Permette di

scambiare dati tra sistemi non compatibili. Ti ricordi quando dicevamo che i DBMS sono

diversi tra loro e farli comunicare diventa difficile se non usi plug-in appositi. Con l'XML

invece possiamo facilmente scambiare informazioni tra diversi sistemi informativi,

condividendo un semplice file di testo. La sintassi è facile e la conosci già perché è uguale

a quella dell'HTML: sai che ci sono i tag di apertura, i tag di chiusura, il contenuto, gli

attributi, gli elementi e i commenti.

Ogni documento deve iniziare con una riga che dichiara la versione XML e il charset

<?xml version="1.0" encoding="ISO-8859-1"?>

utilizzato mentre il resto del documento

deve essere contenuto in un tag radice.

Gli attributi non sono espandibili, non possono contenere valori multipli, non possono

descrivere strutture e non sono utilizzabili dai software. È bene usarli per informazioni

poco importanti o per metadata. Meglio usare dei tag per descrivere i dati.

I documenti XML possono essere accompagnati da un catalogo che fornisce già

informazioni sulla struttura dell'XML stesso. Possono essere di due tipi: il DTD

(DOCUMENT TYPE DEFINITION) o l'XSD (XML SCHEME DEFINITION). Se hai voglia

puoi sorbirti la loro sintassi nelle slide. Da sapere c'è che il DTD è uno schema semplice

nel senso che non fornisce tutte le potenzialità offerte dall'XML. Invece l'XSD è molto più

vasto e si definisce con l'XML stesso (è composto da tag tutti inizianti con xs: ).

Per quanto riguarda le query, XML ha un linguaggio proprio chiamato XPATH.

mysql -u root -p

mysqldump -u -p –databases >

utente password nomedb nomefile.sql

SOURCE pathname;

SHOW WARNINGS;

SHOW DATABASES;

CREATE DATABASE db;

USE db;

DROP DATABASE db;

SHOW TABLES;

DESCRIBE tabella;

SHOW CREATE TABLE tabella;

SELECT * FROM tabella;

INSERT INTO VALUES (valori);

tabella

UPDATE SET WHERE … ;

tabella attr = valore

DELETE FROM WHERE … ;

tabella

CREATE TABLE (attr );

nome tipo vincolo, …

ALTER TABLE ADD

tabella colonna tipo;

ALTER COLUMN colonna tipo;

DELETE COLUMN colonna;

DROP TABLE tabella;

CREATE VIEW (attributi) AS

nome query;

– commento monoriga

/* */

commento multiriga

MySQL non accetta: CHECK()

I vincoli generici con

– CREATE ASSERTION CHECK(NOT EXISTS (…))

Le asserzione

– INTERSECT, UNION, EXCEPT IN

Le query con che si sostituiscono con gli operatori

– EXISTS

e 'YYYY-MM-DD'

Le date si indicano con la sintassi BOOLEAN TINYINT

Il tipo booleano vero e proprio non esiste. viene mappato su

Impiegato(Nome, Titolo, Età, Salario, Dip)

Trovare il nome, il titolo e il dipartimento degli impiegati il cui titolo comincia con “Ing”

SELECT Nome, Titolo, Dip

FROM Impiegato

WHERE Titolo LIKE 'Ing%'

Trovare nome, titolo e salario di tutti coloro che hanno il titolo di programmatore e che

hanno un salario >= 50000

SELECT Nome, Titolo, Salario

FROM Impiegato

WHERE Titolo = 'Programmatore' AND Salario >= 50000

Selezionare tutte le possibili età degli impiegati

SELECT DISTINCT Età

FROM Impiegato

Selezionare lo stipendio medio degli impiegati il cui titolo comincia con “Ing”

SELECT AVG(Salario)

FROM Impiegato

WHERE Titolo LIKE 'Ing%'

Estrarre tutti i dati degli impiegati il cui nome NON è uno tra 'Hernandez', 'Jones',

'Roberts', 'Ruiz‘

SELECT *

FROM Impiegato

WHERE Nome NOT IN ('Hernandez', 'Jones', 'Roberts', 'Ruiz')

Selezionare il massimo stipendio di ogni dipartimento

SELECT Dip, MAX(Salario)

FROM Impiegato

GROUP BY Dip // Ogni attributo del group by DEVE comparire anche nella select !

Selezionare il massimo stipendio di ogni dipartimento, solo se esso supera i 20000 euro

SELECT Dip, MAX(Salario)

FROM Impiegato

GROUP BY Dip

HAVING MAX(Salario) >= 20000 // Having contiene le condizioni di funzioni aggregate

Aeroporto(Città, Nazione, NumPiste)

Volo(IdVolo, GiornoSett, CittàPart, OraPart, CittàArr, OraArr, TipoAereo)

Aereo(TipoAereo, NumPasseggeri, QtaMerci)

Trovare le città con un aeroporto di cui non è noto il numero di piste

SELECT Città

FROM Aeroporto

WHERE NumPiste IS NULL

Trovare le città da cui partono voli internazionali

SELECT DISTINCT CittàPart

FROM Aeroporto A JOIN Volo ON A.Città = CittàPart

JOIN Aeroporto B ON CittàArr = B.Città

WHERE A.Nazione <> B.Nazione

Trovare il numero di voli internazionali che partono il giovedì dalla Francia

SELECT COUNT(*)

FROM (Aeroporto A JOIN Volo ON A.Città = CittàPart)

JOIN Aeroporto B ON CittàArr = B.Città

WHERE A.Nazione = 'Francia' AND A.Nazione <> B.Nazione AND GiornoSett = 'Giovedì'

Trovare le città francesi da cui partono più di 20 voli alla settimana diretti in Germania

SELECT DISTINCT CittàPart

FROM Aeroporto A JOIN Volo ON A.Città = CittàPart

JOIN Aeroporto B ON CittàArr = B.Città

WHERE A.Nazione = 'Francia' AND B.Nazione = 'Germania'

GROUP BY CittàPart

HAVING COUNT(*) > 20

Trovare il numero di voli del giovedì di ogni aeroporto da cui partano almeno 100 voli a

settimana

SELECT CittàPart, COUNT(*)

FROM Volo

WHERE GiornoSett = 'Giovedì' AND CittàPart IN ( SELECT CittàPart

FROM Volo

GROUP BY CittàPart

HAVING COUNT(*) >= 100 )

GROUP BY CittàPart

// Qui non si poteva fare una query unica perché il count(*) della select e della having

contano la stessa quantità di tuple e non quantità differenti, non discriminando il numero

dei voli settimanali da quelli del solo giovedì

Trovare l’aeroporto italiano con il maggior numero di piste

SELECT Città, NumPiste

FROM Aeroporto

WHERE Nazione = 'Italia' AND NumPiste >= ALL ( SELECT NumPiste

FROM Aeroporto

WHERE Nazione = 'Italia' )

// il >= ALL è uno stratagemma per aggirare il fatto di non poter usare la funzione max()

nella clausola where

Orchestra(CodOrchestra, NomeO, NomeDirettore, NumElementi, AnnoFondata)

Concerto(CodC, Data, CodO, CodS, PrezzoBiglietto)

Sala(CodSala, NomeS, Città, Capienza)

Trovare il codice e il nome delle orchestre con più di 30 elementi che hanno tenuto

concerti sia a Genova, sia a Milano e non hanno mai tenuto concerti a Bologna.

SELECT CodOrchestra, NomeO

FROM Orchestra JOIN Concerto ON CodOrchestra = CodO

WHERE NumElementi > 30 AND CodOrchestra IN ( SELECT CodO

FROM Concerto JOIN Sala ON

CodS = CodSala

WHERE Città = 'Genova' )

AND CodOrchestra IN ( SELECT CodO

FROM Concerto JOIN Sala ON

CodS = CodSala

WHERE Città = 'Milano' )

AND CodOrchestra NOT IN ( SELECT Cod

FROM Concerto JOIN Sala ON

CodS = CodSala

WHERE Città = 'Bologna' )

Scrivere un comando SQL che aumenta del 10% il prezzo del biglietto dei concerti di

quelle orchestre che sono state fondate prima del 2000, che hanno tenuto in totale nella

loro vita più di 100 concerti e il cui prezzo medio dei biglietti sia inferiore ai 20 euro.

UPDATE Concerto

SET PrezzoBiglietto = PrezzoBiglietto*1.1

WHERE CodO IN ( SELECT CodOrchestra

FROM Orchestra JOIN Concerto ON CodOrchestra = CodO

WHERE AnnoFondata < 2000

GROUP BY CodOrchestra

HAVING COUNT(*) > 100 AND AVG(PrezzoBiglietto) < 20 )

Trovare il nome delle orchestre che hanno tenuto un concerto in tutte le sale

SELECT CodOrchestra, NomeO

FROM Orchestra JOIN Concerto ON CodOrchestra = CodO

GROUP BY CodOrchestra, NomeO

HAVING COUNT(DISTINCT CodS) = ALL ( SELECT COUNT(*)

FROM Sala )

Cliente(CodCliente, Nome, Indirizzo, MinutiTotali)

Telefonata(CodCliente, DataInizio, OraInizio, DurataInMinuti, TipoTariffa)

Tariffa(TipoTariffa, CostoAlMinuto)

Trovare il nome dei clienti che non hanno mai effettuato telefonate di durata superiore ai

10 minuti usando una tariffa con costo al minuto superiore ai 25 centesimi di euro al

minuto.

SELECT Nome

FROM Cliente

WHERE CodCliente NOT IN ( SELECT CodCliente

FROM Telefonata JOIN Tariffa ON

Telefonata.TipoTariffa = Tariffa.TipoTariffa

WHERE DurataInMinuti > 10 AND CostoAlMinuto > 25 )

Scrivere il comando SQL che seleziona il codice cliente, il nome e il numero di minuti totali

di telefonate urbane (TipoTariffa='URBANA') effettuate da ogni cliente, per i clienti che

hanno fatto almeno 20 telefonate urbane.

SELECT CodCliente, Nome, SUM(DurataInMinuti)

FROM Cliente JOIN Telefonata ON Cliente.CodCliente = Telefonata.CodCliente

WHERE TipoTariffa = 'Urbana'

GROUP BY CodCliente, Nome

HAVING COUNT(*) >= 20

Scrivere il comando SQL che trova per ogni cliente il costo, la data di inizio e l’ora di inizio

della sua telefonata più cara registrata nella base di dati.

CREATE VIEW CostoTelefonata (CodCliente, DataInizio, OraInizio, Costo) AS

SELECT CodCliente, DataInizio, OraInizio, DurataInMinuti*CostoAlMinuto

FROM Telefonata JOIN Tariffa ON Telefonata.TipoTariffa = Tariffa.TipoTariffa

SELECT *

FROM CostoTelefonata A

WHERE Costo = ANY ( SELECT MAX(Costo)

FROM CostoTelefonata B

WHERE A.CodCliente = B.CodCliente )

// In questa query assurda dovevamo calcolare il costo che non era un attributo. Per

questo abbiamo creato una vista e da quella abbiamo fatto i conti molto facilmente.

Ammissione(NTS, Data, Ora, Nome, Sesso, Reparto)

Dimissione(NTS, Data, Ora, Reparto)

PostiLetto(Reparto, Data, MattSera, LiberiMaschi, LiberiFemmine)

Calcolare il numero di pazienti attualmente ricoverati in ciascun reparto dell’ospedale (si

noti che i pazienti attualmente ricoverati sono quelli ammessi e non ancora dimessi, e si

badi a generare un risultato che abbia la struttura [<nome_reparto>, <numero_ricoverati>]

SELECT Reparto, COUNT(*) AS NumeroRicoveriAttuali

FROM Ammissione AS A

WHERE NOT EXISTS ( SELECT *

FROM Dimissione

WHERE (NTS = A.NTS AND Data > A.Data ) OR (NTS = A.NTS

AND Data = A.Data AND Ora > A.Ora) )

GROUP BY Reparto

Trovare il paziente (o i pazienti) con la degenza più lunga, misurata in giorni (si trascurino

cioè gli orari di ammissione e dimissione, e si ipotizzi che una operazione di differenza

sulle date restituisca un numero intero che rappresenta il numero di giorni, anziché un

valore di tipo interval)

CREATE VIEW DurataRicoveri (NTS, Giorni) AS

SELECT NTS, D.Data – A.Data

FROM Ammissione A JOIN Dimissione D ON A.NTS = D.NTS

WHERE A.Data < D.Data AND NOT EXISTS ( SELECT *

FROM Dimissione D2

WHERE A.NTS = D2.NTS AND D2.DATA

BETWEEN A.Data AND D.Data )

SELECT *

FROM DurataRicoveri

WHERE Giorni >= ALL ( SELECT Giorni

FROM DurataRicoveri )

Film(Titolo, NomeRegista, Anno)

Proiezione(NomeCin, CittàCin, TitoloFilm)

Interpreta(Attore, Film, Personaggio)

Modificare lo schema precedente, aggiungendo un attributo ‘Tipo’ alla tabella Film .

ALTER TABLE Film

ADD COLUMN Tipo VARCHAR(25) DEFAULT 'Normale'

Dare al nuovo attributo il valore “Flop” se il film è attualmente in proiezione in meno di 10

sale.

UPDATE Film

SET Tipo = 'Flop'

WHERE 10 > ALL ( SELECT COUNT(*)

FROM Proiezione

WHERE TitoloFilm = Titolo )

Nomi dei registi che hanno diretto nel 1993 più film di quanti ne avevano diretti nel 1992

SELECT NomeRegista

FROM Film F

WHERE Anno = 1993

GROUP BY NomeRegista

HAVING COUNT(*) > ALL ( SELECT COUNT(*)

FROM Film G

WHERE G.Anno = 1992 AND F.NomeRegista =

G.NomeRegista )


ACQUISTATO

1 volte

PAGINE

52

PESO

1.25 MB

AUTORE

fiorixf2

PUBBLICATO

+1 anno fa


DESCRIZIONE APPUNTO

Appunti completi del corso Basi di dati tenuto dal prof Giuseppe Pozzi al Politecnico di Milano. Argomenti trattati:
- Modello relazionale
- Linguaggi formali (algebra relazionale, calcolo relazionale, Datalog)
- SQL (itnerrogazioni semplici, interrogazioni complesse con raggruppamento e ordinamento, query annidate e set queries, viste, trigger, integrazione con altri linguaggi di programmazione)
- Progettazione concettuale di una base di dati con diagramma entità-relazione
- Progettazione logica di una base di dati tramite taduzione standard
- Normalizzazione
- Accenni di XML

Segue un riassunto dei comandi MySQL seguiti da numerosi esempi.


DETTAGLI
Esame: Basi di dati
Corso di laurea: Corso di laurea in ingegneria informatica (COMO - CREMONA - MILANO)
SSD:
A.A.: 2017-2018

I contenuti di questa pagina costituiscono rielaborazioni personali del Publisher fiorixf2 di informazioni apprese con la frequenza delle lezioni di Basi di dati e studio autonomo di eventuali libri di riferimento in preparazione dell'esame finale o della tesi. Non devono intendersi come materiale ufficiale dell'università Politecnico di Milano - Polimi o del prof Pozzi Giuseppe.

Acquista con carta o conto PayPal

Scarica il file tutte le volte che vuoi

Paga con un conto PayPal per usufruire della garanzia Soddisfatto o rimborsato

Recensioni
Ti è piaciuto questo appunto? Valutalo!

Altri appunti di Basi di dati

Esercizi SQL basi di dati
Esercitazione
Esercizi sulla programmazione in C
Esercitazione
Architettura dei calcolatori e sistemi operativi - Appunti
Appunto
Livello di linea
Appunto