Che materia stai cercando?

Anteprima

ESTRATTO DOCUMENTO

L’unica regola che vi dovete ricordare è che un attributo si può trovare sulla

relazione o può restare sulla relazione solo se la relazione è di tipo N-N, quindi

solo se è di tipo molti-molti . In generale un qualsiasi attributo può essere lasciato

sulla relazione solo se la relazione è molti-molti. Può esistere in qualsiasi tipo di

relazione, ma nel caso di 1-1 o 1-N , questo deve essere spostato su una delle entità

collegate.

Su 1-1 o 1-N potete inserire degli attributi, però poi questi vanno spostati .

, non solo lo potete inserire, ma potete anche lasciarlo li dov’è.

Se invece è N-N

Facciamo chiarezza sulla parte num. partecipanti gruppo. Ci sono 2 soluzioni:

soluzione più semplice , consideriamo questo come un attributo atomico . Quindi

come semplicemente un numero, lo vado ad inserire in conversazione e poi sarà un

utente ad aggiornare volta per volta il num di partecipanti.

Quindi il gestore del mio database dirà : in questo gruppo ci sono 10 persone, 15

persone e così via. In questo caso non devo fare nient’altro.

lo posso considerare un attributo derivato , in questo caso devo creare un

sottoprogramma ( noi lo chiameremo BR) che di volta in volta andrà a contare

automaticamente il num di partecipanti a quel gruppo. Possiamo dire tra le BR :

num di partecipanti gruppo, attributo derivabile.

Ultima precisazione. Quando abbiamo parlati di conversazioni che si dividevano

in:

gruppo o

chat privata

abbiamo visto come questa distinzione viene gestita attraverso una gerarchia .

O

Questa gerarchia l’abbiamo risolta con il 1 dei 3 metodi.

Mi hanno chiesto cosa sarebbe successo se anche questa seconda gerarchia fosse

stata risolta con lo stesso metodo. Vediamo nel dettaglio quali sono le regole

1O

generate risolvendo con il metodo. Quindi se risolviamo tutta questa seconda

1o metodo, ci ritroviamo un’entità messaggio che conterrà tutti i suoi

parte con il

attributi più tutti gli attributi delle figlie.

Quindi abbiamo preso tutti gli attributi delle figlie , queste sono state portate

nell’entità padre , in più ricordiamoci sempre di inserire l’attributo tipo che dovrà

assumere valori pari al nome , alle

etichette, delle figlie .

Quindi prima avevamo:

tipo conversazione→ gruppo - chat

privata ora avremo :

tipo messaggio → testo-

multimediale - GPS

Dobbiamo creare le BR per dire quali attributi sono NULL e quali NOT NULL e

in quale situazione NB. In questo caso la scrittura delle BR è particolarmente

noiosa, perché? Il linguaggio che ci permette di creare il DB vuole che per ogni

attributo ci sia la condizione di esistenza o meno.

Quindi non posso accorpare attributi che sono tutti nulli, ma devo dire :

dimensione IS NULL , formato IS NULL , durata IS NULL e così via...

Quindi li devo scrivere tutti e tutti singolarmente BR1) tipo Msg= Testo , multi,

gps

BR2) (tipo=TESTO allora testo IS NOT NULL e dimensione IS NULL ,e formato

IS NULL, durata IS NULL e lat IS NULL e long IS NULL)

oppure

(tipo = MULTIM allora testo IS NULL e dimensione IS NOT NULL e formato IS

NOT NULL , durata IS NOT NULL e long IS NULL e lat IS NOT NULL )

oppure

(tipo = GPS allora testo IS NULL e dimensione IS NULL e formato IS NULL e

durata IS NULL e long IS NOT NULL e lat IS NOT NULL )

Queste stringhe nella realtà vengono generate da programmi , da software , quindi

voi dovete soltanto andare a spuntare delle checkbox , dite soltanto quale è NULL

e NOT NULL e l’intera stringa viene generata .

Ultima cosa che ci resta da fare per prendere il punteggio massimo è scrivere le

tabelle corrispondenti a questo modello E-R .

Anche qui per fare il MAPPING esistono delle linee guida , l’ideale sarebbe

scrivere le tabelle iniziando da quelle che non presentano chiavi esterne . Quindi

solitamente queste sono le tabelle in cui ci sono delle relazioni o cardinalità pari a

1 o relazioni di tipo N-N.

Quindi in questo caso , sicuramente UTENTE è un’entità che non prevede al suo

interno chiavi esterne .

Molto spesso questa fase si riduce nello riscrivere la lista degli attributi che

ciascuna entità possiede, quindi in questo caso per UTENTE dobbiamo solo

riscrivere l’elenco dei suoi attributi.

UTENTE (cel,data iscr, data scad,nome)

CONVERSAZIONE ( cod,data in , titolo, tipo,N_part.gruppo)

nella tabella PARTECIPA cosa dobbiamo scrivere ? è una relazione N-N, quindi

ricordiamoci le chiavi delle entità collegate. Consiglio di scrivere : la chiave come

indicata nella tabella di partenza (cel utente) più la chiave dell’entità collegata (cod

conversazione) . Entrambe, insieme, compongono la chiave primaria di questa

tabella che include anche l’attributo booleano admin .

PARTECIPA ( cel utente , cod conv, admin) Passiamo alla tabella MESSAGGIO

MESSAGGIO ( cod conv, ID, stato, data ,ora, cell_utente)

Messaggio è debole rispetto a conversazione, quindi eredita da conversazione la

sua chiave ( cod conv) , in più insieme alla chiave parziale (ID) viene definita la

chiave primaria di messaggio. Inseriamo anche tutti gli altri attributi presenti in

Qui dobbiamo fare anche un’altra considerazione: messaggio è

messaggio.

collegato ad utente , la cardinalità però è lato N , quindi da utente devo ereditare

anche la chiave esterna ( cel utente ) . Per le chiavi esterne ( non è obbligatorio)

potete anche indicare con una freccia la chiave a cui questo attributo si collega.

NB: Il prof lo sconsiglia perché viene un mapping abbastanza incasinato , però se

per voi è più comodo siete liberi di farlo.

Dobbiamo solo scrivere le 3 entità figlie di messaggio.

NB: Vanno sottolineati solo gli elementi che compongono la chiave primaria, la

chiave esterna eventualmente si può indicare con le frecce.

Come gestisco queste entità figlie deboli: in realtà a tutti gli effetti come delle

entità deboli che ereditano dall’entità padre l’intera chiave. Questo elemento è sia

chiave primaria che chiave esterna perché permettono il collegamento con

messaggio.

Codice conv , chiave esterna, verso quale tabella? messaggio ID, chiave esterna,

verso quale tabella? messaggio

Insieme verso quale tabella? Messaggio a

Dovete ricordarvi che la risposta esattamente corretta è solo la 3 . Quindi

singolarmente è vero che si riferiscono alla tabella messaggio, ma prese

singolarmente quelle chiavi non significano nulla e l’intera chiave che rappresenta

chiave esterna , perché è l’intera chiave che si collega all’intera chiave primaria

la

di messaggio.

ERRORE TIPICO: creare singole chiavi esterne . RICORDATE che quando state

indirizzando una chiave primaria composta da N elementi , anche la chiave esterna

è composta dagli stessi N elementi.

TESTO ( cod comv,ID, testo) GPS ( cod conv, ID, lat,lon)

COME ESERCIZIO : provate a riscrivere semplicemente le tabelle legate alla

gerarchia utilizzando i due metodi alternativi , cercate di vedere materialmente

come cambia il mapping utilizzando un metodo piuttosto che un altro.

ULTIMA COSA: quando risolviamo l’esercizio non è detto che vedremo sempre il

metodo migliore , alcune volte vi mostrerò il caso peggiore durante l’esercizio , per

poi farvi capire come migliorerebbe la situazione utilizzando la tecnica più adatta.

ALGEBRA RELAZIONALE

L’algebra Relazionale è un linguaggio procedurale, viene esplicitata la sequenza degli

operatori utilizzati e richiesti per ottenere un risultato. Esplicitiamo il fatto che si tratta di un

linguaggio procedurale per differenziarlo dal linguaggio che utilizzeremo successivamente

che è un linguaggio di tipo dichiarativo, in cui le frasi del linguaggio dichiarano, non a

procedura con la quale ottenere il risultato, ma il risultato che vogliamo ottenere dalla nostra

operazione.

L’algebra relazionale è un’algebra chiusa è definita su relazioni (su tabelle) e produce

tabelle. Questa proprietà ci permette di definire l’algebra relazionale come un’algebra

composizionale, gli operatori che costituiscono l’algebra relazionale poiché vi è la

posso concatenare

sicurezza che essi sono definiti su relazioni ed hanno valori che corrispondono a delle

relazioni. Se eseguo

un’operazione di algebra relazionale su una relazione otterrò una nuova relazione, per cui

posso collegare gli operatori.

Nell’ambito dell’algebra relazionale si possono distinguere due differenti operatori:

OPERATORI UNARI: opera su una relazione e restituisce a sua volta una relazione.

OPERATORE di SELEZIONE

OPERAZION E di PROIEZIONE

OPRATORE di RIDENOMINAZIONE

OPERATORI BINARI: opera su due o più relazioni e restituisce una relazione.

UNIONE

INTERSEZIONE

DIFFERENZA

PRODOTTO CARTESIANO

Oltre al prodotto cartesiano si considera un operatore elementare, anche se non lo è,

l’operatore:

JOIN

Se vi è la presenza di una formula proposizionale parleremo di “TETA JOIN” altrimenti

posso considerarlo in termini di “EQUI JOIN”, dove la formula che viene utilizzata per il

confronto è esclusivamente una formula che contiene operatori di

uguaglianza o in fine posso considerare il “NATURAL JOIN” il quale è un tipo naturale di

“equi join”.

SELEZIONE

Rappresentazione:

: Si parte da una relazione ( R ) e si indica il risultato ( in termini del nuovo schema

relazionale (R ) il quale ha un nuovo nome ma la sua struttura relazionale (cioè i suoi

1

attributi) sono identici a quelli della relazione di partenza. Contiene tutte le tuple di R che

verificano lo specifico predicato di selezione;

Condizione di selezione: <PREDICATO_SELEZIONE> è una formula proposizionale

costituita da atomi proposizionali nella forma dove:

A è un attributo presente nella relazione in esame R

termini di dominio con l’attributo A B è un attributo della

C è una costante compatibile in

relazione R, avente dominio compatibile con A

è un operatore di confronto

Una condizione di selezione è formata minimo da un atomo proposizionale, l’atomo

proposizionale è la formula base proposizionale, è un confronto o tra un attributo ed una

costante o tra due attributi della relazione, in generale una formula proposizionale e quindi

condizione di selezione, è una condizione più complessa che raggruppa più atomi

proposizionali a formare una formula proposizionale. Gli atomi proposizionali possono essere

collegati usando i connettivi logici OR AND e NOT.

AND entrambi gli atomi stanno restituendo un risultato vero OR almeno uno degli atomi

restituisce un risultato vero

NOT uno degli atomi è falso ed è vero il suo contrario

ESEMPIO 1

La selezione è un operazione che opera su una relazione e restituisce una nuova relazione che

ha esattamente o stesso schema e la stessa struttura in termini di attributi della relazione di

partenza ed ha il medesimo grado della relazione di partenza.

L’operatore di selezione opera su ciascuna tupla dell’operatore di partenza (impiegato

studente) e tupla per tupla, pone la tupla in esame nella relazione risultante (nell’estensione

relazionale della nuova relazione) se e soltanto se tutta la formula proposizionale è verificata

per quella tupla. Im piegato

datan asc 01 01 60 dataass 05 03 93

LETTURA: Seleziona dalla tabella “impiegato” tutte le tuple che verificano il fatto che “data

nascita” sia > del “1/01/1960 “ E CONTEMPORANEAMENTE la “data dell’assunzione” sia

“3/05/1993”

< del Data di

Data di assunzione

nascita

Cognome Nome

Davolio Nancy 08-dic-48 01-mag-92

Fuller Andrew 19-feb-52 14-ago-92

Leverling Janet 30-ago-63 01-apr-92

Peacock Margare 19-set-37 03-mag-93

Buchanan Steven 04-mar-55 17-ott-93

Suyama Michael 02-lug-63 17-ott-93

King Robert 29-mag-60 02-gen-94

Callahan Laura 09-gen-58 05-mar-94

Dodswort Anne 27-gen-66 15-nov-94

Rossi mario

DESCRIZIONE: l’operatore prende la prima tupla e verifica la data di nascita di “nancy

Davolio”, la data di nascita (1948) non verifica la condizione => non è necessario verificare

la seconda condizione. data di nascita di “ Andrew Fuller” ed essendo nato nel 1952 non

Di seguito verifica la

verifica la prima condizione ed anche in questo caso non è necessario verificare la seconda;

“Janet Leverling” è nata il 30/8/63 verifica la prima condizione => sarà necessario verificare

la seconda, la “data di assunzione” è 1992 , in questo caso non è verificata la seconda

anche

condizione;

“Margaret Peacock” è nata il 19/9/1937, non verifica la prima condizione; “Steven Buchanan

“ e“Laura Callahan “ non verificano la prima condizione;

“Michael Suyama “ verifica entrambe le condizioni, data di nascita 2/7/1963 e data di

assunzione nel 17/10/1963;

“King Robert” e “Anne Dodsworth” verificano entrambe le condizioni;

RISULTATO FINALE

Cognome Nome Data di nascita Data di assunzione

Suyama Michae l 02-lug-63 17-ott-93

King Robert 29-mag-60 02-gen-94

Dodswort h Anne 27-gen-66 15-nov-94

ESEMPIO 2

Nota: “ing gest” è tra virgolette mentre anno_immatricolazione no, poiché ci sono attributi

hanno delle stringhe l’unico confronto che posso fare è ≠ e

che sono delle stringhe. Quando si

=. Se non inserisco le virgolette vuol dire che mi sto riferendo a numeri, nei numeri invece

posso considerare tutti gli operatori di confronto su citati.

“ing Gest” e anno_imm sono due atomi proposizionali del tipo dove in un caso c’è una

stringa e nell’altro c’è un numero.

Avrei avuto nel caso in cui avessi scritto

LETTURA

Seleziona, a partire dalla tabella “Studente” tutte le tuple che verificano contemporaneamente

“Corso di Laurea” sia uguale a “Ingegneria Gestionale” e contemporaneamente “l’anno

che il

di immatricolazione” sia uguale al 2016.

In troveremo tutte le tuple che corrispondono a studenti che si siano immatricolati nell’anno

2016 ad Ingegneria Gestionale.

GRADO

Il grado (il numero di attributi della relazione stessa) della relazione risultate, , sarà

esattamente il grado della relazione di partenza (lo schema rimane invariato)

CARDINALITA’

La cardinalità (il numero delle tuple che può variare nel tempo) della relazione risultante, in

generale, si ridurrà. Al più tutte le tuple possono verificare il predicato di selezione (o

formula proposizionale) ed in questo caso avremo la stessa cardinalità di partenza. È possibile

l’insieme vuoto.

avere come risultato

ESEMPIO 3

S (Nome, Matricola, Cognome, Cds, Anno_Immatricolazione, Città_Residenza,

Titolo_Conseguito, Titp_Laurea)

RICORDA: l’operatore END ha priorità rispetto all’OR.

A B

F F F F

F V V F

V F V F

V V V V

PROPRIETA’

La selezione è commutativa.

PROIEZIONE

Rappresentazione

La Selezione è un operatore unario che consente di selezionare, a partire da una relazione di

partenza, solamente un certo numero di attributi (o colonne) della stessa.

ESEMPIO

S (Nome, Matricola, Cognome, Cds, Anno_Immatricolazione, Città_Residenza,

Titolo_Conseguito, Titp_Laurea)

RICORDA: Il nome della relazione risultante è un nome completamente arbitrario. La tabella

risultante conterrà solamente 3 colonne (nome, cognome, Matricola).

S aveva 8 attributi per cui il suo grado era 8, il numero di attributi e quindi il grado della

relazione risultante è 3.

GRADO

In generale un’operazione di proiezione è un’operazione che riduce il grado, al più il grado si

manterrà. La lista degli attributi è un sottoinsieme degli attributi R

CARDINALITA’

Il numero di tuple risultanti sarà minore o uguale al numero di tuple di partenza poiché vi

possono essere delle tuple che , in seguito alle proiezioni, si riducono a tuple coincidenti; è il

caso delle tuple che si distinguono dalle altre solo per certi attributi che vengono eliminati

dalla proiezione, poiché non contenuti nella lista degli attributi specificata dall’operatore. Ciò

per rispettare la condizione che nel modello relazionale nelle tabelle ogni tupla debba essere

distinguibile dalle altre, non possono esserci duplicati anche nella relazione risultante e quelli

che dovrebbero essere duplicati corrispondono ad un'unica tupla.

ESEMPIO 2

STUDENTE

Nella realtà vi possono naturalmente essere studenti con lo stesso nome o con lo stesso

cognome.

L’operazione crea una relazione con cardinalità inferiore a quella della relazione di partenza

sulla quale è applicata l’operatore , poiché vi sono due istanze di studente con medesimo

nome!

Poiché l’algebra relazionale è un’algebra chiusa, ogni sua operazione restituirà sempre una

relazione in cui, per definizione, le tuple devono essere uniche.

RISULTATO

Note

Poiché si tratta di un sistema puramente matematico, non si può dire qual è la tupla che viene

che rimane nell’estensione, non esiste

eliminata e qual è quella ordinamento.

In un DBMS reale, le tuple uguali restano ela tabella risultante poiché sarebbe notevole il

costo algoritmico per eliminare le repliche, specialmente in caso di relazione con una

notevole estensione.

Se la lista degli attributi è SUPER CHIAVE di R, si manterrà in R la medesima cardinalità di

1

R; poiché la SUPER CHIAVE, per il fatto stesso di contenere una chiave primaria, mantiene

l’unicità della tupla. È una condizione sufficiente non necessaria.

Nel nostro esempio:

Cardinalità (studente) = Cardinalità ( S )

OPERAZIONI BINARIE

Unione, intersezione e differenza

Quando confrontiamo le relazioni di partenza con gli operatori insiemistici partiamo

dall’assunzione che si tratti di relazioni COMPATIBILI, con il medesimo grado (stesso

numero di attributi) e a due a due gli attributi devono essere identici o con dominio

compatibile (con lo stesso dominio).

UNIONE

: assumendo che siano operatori compatibili Sfg (Nome, Cognome, Matricola)

Sba (Nome, Cognome, Matricola)

(R unito a S restituisce una nuova relazione= T)

T Conterrà tutte le tuple presenti in R e o in S, essa avrà lo stesso schema di una delle due

relazioni di partenza e cardinalità pari al più alla somma delle cardinalità delle relazioni di

partenza.

Saranno uguali alla somma quando gli insieme originali sono disgiunti, altrimenti le tuple

presenti nell’intersezione saranno riportate una sola volta sulla base del principio di unicità.

INTERSEZIONE

Restituisce una relazione che avrà schema identico a una delle due relazioni di partenza e

conterrà tutte le tuple contemporaneamente presenti in entrambe le relazioni di partenza, il

grado è uguale al grado di una delle due relazioni di partenza essendo esse compatibili.

La cardinalità di T è al più pari al minimo fra cardinalità R e cardinalità S, condizione

estrema raggiunta se una delle 2 relazioni è completamente contenuta nell’altra.

DIFFERENZA

Restituisce una nuova relazione che avrà schema identico (stesso grado) e una delle due

relazioni di partenza ed in cui saranno presenti tutte e sole le tuple di R che non appartengono

(non sono presenti in ) a T.

PRODOTTO CARTESIANO

DEFINIZIONE FORMALE: Otteniamo il prodotto cartesiano, unendo ciascuna tupla

dell’estensione della relazione R con ciascuna tupla dell’estensione della relazione S.

Ottengo una tabella che ha grado pari alla somma dei gradi delle relazioni di partenza e

partenza. È un’operazione

cardinalità pari al prodotto delle cardinalità delle relazioni di

combinatoria.

ESEMPIO DI PRODOTTO CARTESIANO

NOME COGNOME MATRICOLA ID_CDS ID_CDS ID_CDS

Pippo Franco 22 3 1 ING. GEST

Diego Abat 27 3 3 ING. ELETTRI

Roberto Baggio 29 1 2 ING. CIVILE

STUDENTE (Nome, Cognome, Matricola, Chiave_cds) CORSO di LAUREA (ID_CDS,

Nome_CDS)

Creo una nuova relazione che chiamo arbitrariamente:

STUDENTE CORSO DI LAUREA (Nome, Cognome, Matricola, Stud. ID_Cds, ID_Cds,

Nome_Cds)

Ho rinominato ID_CDS della tabella studente in Studente.ID_CDS poiché ha lo stesso nome

della colonna della tabella Corso di Laurea.

NOME COGNOME MATRICOL STUD.ID_C ID_CDS NOME_CDS

A DS

Pippo Franco 22 3 1 Ing.Gestional

e

Pipp Franco 22 3 3 Ing. Elettrica

Pipp Franco 2 3 2 Ing. Civile

o 2

Dieg Abat 2 3 1 Ing.Gestional

o 7 e

Diego Abat 27 3 3 Ing.Elettrica

Diego Abat 27 3 2 Ing. Civile

Roberto Baggio 29 1 1 Ing.Gestional

e

Robert Bagg 2 1 3 Ing. Civile

o io 9

Robert Bagg 2 1 2 Ing. Elettrica

o io 9

SVOLGIMENTO:

Prendo ciascuna tupla e la congiungo a ciascuna tupla.

Guardando il risultato tupla per tupla possiamo osservare che alcune di queste tuple sono

semanticamente correlate, cioè hanno una correlazione reale, sulla base della conoscenza di

dominio. (STUDENTE.ID_CDS deve essere uguale a ID_CDS poiché stiamo parlando della

stessa cosa) il risultato matematicamente è corretto ma non lo è semanticamente!

ESERCITAZIONE

Appello 05-02-2016

La traccia parla di un sistema di accesso a contenuti video (si pensi a Netflix o Mediaset

Premium) al quale ci si può iscrivere con abbonamento.

Come prima cosa, la traccia ci chiede di memorizzare i dati relativi ai singoli utenti, quindi

l’utente che si sottoscrive ad un abbonamento del sistema diventa la nostra prima entità. Ogni

utente è caratterizzato da un indirizzo email (e la traccia ci dice che questo è univoco per ogni

utente), password e data d’iscrizione. Per la scelta della chiave non abbiamo dubbi, perché la

traccia ci dice che l’indirizzo email è univoco.

Ogni utente può sottoscrivere degli abbonamenti ed ogni abbonamento è caratterizzato da:

data di inizio dell’abbonamento, data di fine, costo e tipologia, la quale può essere di tipo

base o premium. Abbonamento costituisce una nuova entità. Per quest’entità dobbiamo fare

delle considerazioni:

La tipologia può essere base o premium. Questa diventa la nostra prima business rule.

Come colleghiamo utente ad abbonamento? Un utente può sottoscrivere N abbonamenti (qui

per abbonamento si intende la possibilità di avere più profili, come cinema, calcio, ecc).

Supponiamo che ci sia la possibilità di avere più abbonamenti differenti.

Normalmente ci verrebbe da pensare che un abbonamento possa essere sottoscritto da più

utenti (esempio: l’abbonamento calcio di sky lo possono acquistare più utenti). Ma in questo

caso abbiamo un problema: non abbiamo ancora identificato la chiave. Quindi prima di capire

questo problema relativo alla cardinalità, ci conviene definire la chiave di abbonamento.

Non abbiamo attributi che rendono quest’entità univoca, infatti la sola data di iscrizione non

riesce a rendere unico l’abbonamento. Quindi probabilmente dobbiamo rendere abbonamento

debole rispetto ad utente: in una particolare data, un utente può sottoscrivere un solo

abbonamento, questo consente all’utente di potersi iscrivere ad un solo abbonamento al

giorno. Quindi individuiamo data di inizio come chiave parziale. Questa però è solo

un’ipotesi, perché ad esempio potremmo utilizzare come chiave data inizio e data fine, per

rendere più generica questa situazione. O ancora potremmo rendere abbonamento debole

rispetto alla tipologia. Oppure una soluzione ancora più generica sarebbe quella di inserire

come chiave data inizio e tipologia. Noi abbiamo scelto data inizio solo perché è la più

semplice. Ma abbiamo la libertà di scegliere la chiave che per noi sembra la più corretta.

Una volta reso abbonamento debole rispetto ad utente, la scelta della cardinalità è abbastanza

immediata, perché non potremo mai avere N:N con un’entità debole, quindi siamo sicuri che

la cardinalità dovrà essere 1:N

Andando avanti, la traccia ci dice di memorizzare la lista di dispositivi associati a ciascun

abbonamento, quindi ogni utente può ad esempio associare ad un abbonamento il proprio pc,

tablet o telefono. Per ogni dispositivo si conosce il nome e l’indirizzo MAC, che lo identifica

univocamente. L’indirizzo MAC è un codice a 12 cifre che identifica ogni singolo dispositivo

sulla rete. Quindi ogni dispositivo che si può connettere ad internet ha questo codice ed è

diverso sempre per ogni dispositivo. Come modelliamo questa lista di dispositivi? O lo

identifichiamo come attributo multivalore, oppure più semplicemente modelliamo i

dispositivi come delle entità, perché comunque successivamente gli attributi multivalore

andrebbero risolti. Per il discorso fatto in precedenza, l’indirizzo MAC è la chiave di

dispositivo.

Un abbonamento può essere associato a più dispositivi, quindi la cardinalità dal lato

dispositivi è N. Dall’altro lato la scelta è libera. Se consideriamo un sistema che ci consente

di avere lo stesso dispositivo associato per più abbonamenti, mettiamo cardinalità N,

altrimenti se consideriamo un servizio che consente di associare un solo abbonamento allo

specifico dispositivo (meno probabile), inseriremo cardinalità 1. Ma la soluzione migliore è

di tipo N:N.

La traccia inoltre dice che, solo per gli abbonamenti di tipo base, il numero di dispositivi

associati deve essere al massimo 1. Quindi solo per la tipologia base, la cardinalità dal lato

dispositivo deve essere 1. Graficamente non cambia nulla, ma questa è soltanto una nuova

regola del nostro modello, che dovrà essere implementata successivamente.

Si conoscono anche le informazioni dei contenuti video, quindi le informazioni dei film, serie

tv, ecc. per ogni contenuto si conoscono codice, titolo e data di pubblicazione. Oltre a questi

dati si conoscono anche un giudizio dato dagli utenti compreso tra 1 e 5 (è un giudizio

complessivo medio) ed anche un attributo booleano che ci dice se quel contenuto è

consigliato o meno per i bambini. Se vogliamo (non è obbligatorio) possiamo scrivere una

BR in cui definiamo il tipo di attributo bambini, che è appunto booleano. Allo stesso modo

potremmo scrivere che l’indirizzo MAC del dispositivo è un attributo da 12 caratteri, ma

anche questa è solo un’indicazione sul tipo di dato. Quindi le ultime due BR non sono

obbligatorie perché, seppur imponendo delle imitazioni al sistema, queste riguardano solo la

tipologia di dato.

La traccia continua affermando che ogni contenuto può essere caratterizzato come film o

serie tv: questa per noi è una gerarchia. In caso di film si conosce durata e anno di

produzione, mentre in caso di serie tv conosciamo genere, descrizione e la lista delle stagioni

che la compongono. Stagione potrebbe essere un attributo multivalore, ma lo modelliamo più

correttamente come un’entità. Per ogni stagione si conosce un numero progressivo, univoco

solo rispetto alla serie. Questo ci fa capire che anche stagione è un’entità debole rispetto a

serie tv. Una serie tv è composta da più stagioni, con ID chiave parziale. Una stagione

apparterrà soltanto ad una serie tv (cardinalità 1:N). Ulteriore informazione: per ogni stagione

si conosce anche il numero complessivo di puntate.

Ultima cosa da fare: collegare contenuto ad abbonamento. Infatti la traccia dice che per ogni

abbonamento si vuole tener traccia dei contenuti visualizzati. Cardinalità: attraverso un

abbonamento è possibile visualizzare più contenuti ed un singolo contenuto può essere visto

con più abbonamenti. Dato che abbiamo una relazione N:N, la traccia ci chiede anche di

visualizzare la data e l’ora in cui ogni contenuto è stato visualizzato da ciascun abbonamento.

Queste informazioni le inseriamo direttamente sull’associazione logica.

Sembrerebbe tutto ok, ma in realtà quest’ultima relazione ha una limitazione.

Se con il mio abbonamento volessi vedere due volte lo stesso contenuto, il sistema lo

permette? Non lo permette, perché la chiave della tabella visualizza sarà costituita da codice

abbonamento (in realtà email utente e data inizio) e codice contenuto. Se tento di rivedere

quello stesso film con lo stesso abbonamento, il sistema dovrebbe ricreare nella tabella una

nuova tupla con esattamente le stese chiavi, ma il sistema non lo permetterebbe perché quella

coppia di elementi sarà già stata inserita precedentemente.

Per risolvere questo problema abbiamo due possibilità:

Prevedere che, in una situazione di questo tipo, il sistema si accorga di questo errore e vada

ad aggiornare data e ora. Ma in questo modo teniamo traccia soltanto dell’ultima

visualizzazione fatta del contenuto. È una soluzione accettabile, se si ha intenzione di non

tener traccia dello storico di tutte le visualizzazioni effettuate.

In alternativa potremmo utilizzare una modellazione di questo tipo:

entità visione. Non avrei più l’associazione N:N, ma a ciascuna delle due

Creiamo una nuova

entità abbonamento e contenuto deve essere collegata visione attraverso delle relazioni 1:N.

ogni volta che vedo qualcosa nel mio sistema, conosco sia l’abbonamento attraverso cui ho

l’accesso a quel contenuto, sia il particolare contenuto che sto visualizzando. La chiave di

questa nuova entità sarà composta dai due attributi data e ora come chiavi parziali ed inoltre

l’entità sarà debole rispetto ad entrambe le entità alle quali è collegata.

La prima alternativa è chiaramente la più semplice, ma quest’ultima ci consente di tenere

traccia di tutte le visualizzazioni.

Ora dobbiamo risolvere la gerarchia.

Qual è la soluzione ideale? Sicuramente portare il padre nelle figlie non ci conviene perché

abbiamo tanti attributi ed una relazione. Potremmo portare le figlie nel padre, ma abbiamo

quattro attributi ed una relazione e ciò ci porterebbe ad avere tante business rule. Allora

possiamo pensare di lasciare le figlie come entità deboli: eliminiamo la gerarchia e creiamo le

singole relazioni.

Le relazioni sono di tipo 1:1, deboli verso le figlie e senza chiavi parziali. Non ci resta che

realizzare il mapping relazionale.

Iniziamo dalle entità in cui non abbiamo chiavi esterne. Partiamo dunque da utente e

dispositivo.

Continuiamo con abbonamento: abbonamento è debole rispetto ad utente, quindi prende la

chiave di utente che, insieme a data di inizio, diventa chiave di questa tabella. Quindi email e

data di inizio diventano chiave primaria di abbonamento, ma email è anche chiave esterna

verso la tabella utente. Potremmo anche identificare la chiave esterna con emailUtente, in

modo da evitare il collegamento con la freccia.

Ora possiamo modellare la relazione N:N include. Abbiamo la chiave di abbonamento,

composta dalla coppia email e data inizio, più la chiave di dispositivo; insieme sono chiave

primaria di quest’entità. La coppia email e data inizio sono chiave esterna verso

abbonamento, mentre MACdisp è chiave esterna verso dispositivo.

Anche la tabella contenuto non ha chiavi esterne, quindi ci limitiamo a riportare i suoi

attributi.

A partire da contenuto definiamo le due entità figlie. Le due figlie sono deboli, quindi

ereditano da contenuto la chiave.

Creiamo ora la tabella stagione. Anch’essa è debole, quindi eredita la chiave di serie (la

chiave esterna codserie punta a serie e non a contenuto!).

Infine abbiamo la tabella visualizza. La chiave è costituita dalle chiavi delle entità collegate,

quindi la chiave di abbonamento email e dataInAbb e la chiave di contenuto codcont.

Inseriamo gli attributi della relazione, ovvero data e ora.

Abbiamo così terminato il mapping. Come ulteriore conferma, possiamo verificare che il

numero di tabelle sia pari al numero di entità più il numero di relazioni N:N. Qui abbiamo 7

entità più le due relazioni N:N, quindi in tutto dovrei avere 9 tabelle.

UTENTE (email, psw, dataIsc)

DISPOSITIVO (MAC, nome)

ABBONAMENTO (email, dataIn, dataFn, costo, tipolAbb)

INCLUDE (email, dataInAbb, MACdisp)

CONTENUTO (cod, titolo, dataPub, giudizio, bambini)

FILM (codcont, durata, anno)

SERIE TV (codcont, genere, descr)

STAGIONE (codserie, ID, Npunt )

VISUALIZZA (emailAbb, dataInAbb, codcont, data, ora)

Cosi come abbiamo modellato il sistema, quando con lo stesso abbonamento si visualizza più

volte lo stesso contenuto, non teniamo traccia di tutte le visualizzazioni effettuate, perché

l’unica cosa che il sistema potrebbe fare è aggiornare i campi data e ora. Infatti il sistema non

permette di inserire due tuple aventi la stessa chiave primaria.

Questa è un’ipotesi di modellazione (in un eventuale compito aggiungiamo una nota

dichiarativa).

Facciamo un esempio:

email dataIn codcont Data ora

prova@gmail.com 10/01/2016 F1 01/10/2016 20:00

02/10/2016 19:00

prova@gmail.com 10/01/2016 F1 02/10/2016 19:00

Supponiamo di effettuare una visualizzazione il giorno 1/10/2016 con un determinato

abbonamento, caratterizzato da una certa mail e da una data di inizio. Voglio vedere un

contenuto dal codice F1. Supponiamo di averlo visto il primo ottobre alle 20:00. Ricordiamo

che la chiave di questa tabella è data da email, dataIn e codcont; per il vincolo di chiave

primaria non possiamo avere nella stessa tabella due tuple con la stessa chiave primaria.

Supponiamo di aver lasciato a metà il film e di voler continuare a vederlo il giorno dopo alle

ore 19:00. In teoria il sistema dovrebbe inserire una nuova tupla con la stessa chiave primaria,

ma questo non è possibile, quindi il sistema genera un errore. Ma questa è una forte

limitazione, perché non mi permette di vedere più volte lo stesso contenuto.

Una soluzione potrebbe essere quella di effettuare un controllo preliminare, che permetta di

controllare se esiste già una tupla con una certa chiave primaria. Se si, anziché inserire un

nuovo elemento, il sistema aggiorna semplicemente data e ora. Questo sistema tiene quindi

traccia soltanto dell’ultima volta che un certo utente ha visualizzato un certo contenuto.

Ci eravamo fermati raccontando come funzionava il JOIN. Abbiamo imparato a fare il

prodotto cartesiano, che è un’operazione combinatoria cioè che ci restituisce, date due

tabelle, ciascuna riga con ciascuna riga dell’altra, cioè supposto di avere due relazioni,

abbiamo il prodotto delle cardinalità delle relazioni di partenza come numero di tuple e la

somma dei gradi come grado completo delle due relazioni originarie.

Questo fatto l’abbiamo fatto seguire dall’operazione di SELEZIONE che non casualmente è

stata fatta ponendo come condizione di selezione, una condizione di uguaglianza tra gli

relazioni e chiave esterna per l’altra ed erano

attributi che erano chiave per una delle

omologhi. Facendo seguire al prodotto cartesiano questa operazione di selezione abbiamo

scoperto che:

a quel punto il prodotto cartesiano conteneva soltanto informazioni sensate cioè tutti i valori

presenti su una tupla erano semanticamente correlati tra di loro

che non c’era niente in più quindi c’era un’informazione che era corretta e completa. C’erano

tutte quelle che ci dovevano essere e nessuna in più.

Quindi abbiamo scoperto un risultato importante: se mettiamo insieme attraverso un prodotto

cartesiano due tabelle che partono con una correlazione logica cioè uno degli attributi è una

chiave e l’altro è chiave esterna e facciamo seguire al prodotto cartesiano questa condizione

di selezione, otteniamo due risultati:

Informazione corretta

riusciamo a rimettere insieme senza ricadere nei rischi e nelle patologie viste

precedentemente ossia nelle cosiddette anomalie, informazioni che parte distinta però poii

attraverso questo processo diventa un’informazione completa in un’unica tabella.

Adesso so come progettare un database però questo database quando l’ho progettato contiene

delle cose che sono correlate logicamente ma stanno in tabelle diverse ma ora ho imparato

che posso metterle insieme in maniera LOSLESS= senza perdite di informazioni.

In generale possiamo pensare l’operazione di JOIN in questo modo:

Nella formula ha scritto “teta” perché la condizione di selezione che è una formula

proposizionale arbitraria applicata al prodotto cartesiano è definibile come TETAJOIN.

TETAJOIN= Prodotto cartesiano seguito da una selezione su una condizione arbitraria.

Condizione arbitraria significa che non c’è nessuna garanzia che il risultato abbia

logicamente senso. Cioè io faccio un’operazione, questa riesce, che questa abbia logicamente

un senso non è detto. Per esempio se io faccio 3x5 verrà sempre 15 però non vuol dire che

abbia un senso questa operazione che sto facendo.

EQUIJOIN=Eseguiamo il prodotto cartesiano e seguito da una selezione in cui imponiamo

condizioni di uguaglianza tra attributi omologhi delle relazioni. E’ condizione

soltanto

sufficiente (ma non lo dimostriamo) che questa condizione di uguaglianza venga posta su

attributi che siano per un lato chiave e per l’altro chiave esterna perché il risultato sia una

tabella LOSLESS JOIN cioè senza perdite rispetto all’informazione originaria ( cioè che

quando le ho rimesse insieme hanno senso).

NATURAL JOIN= è una diretta conseguenza dell’EQUIJOIN. Cioè vedendo l’ultimo

disegno che ha fatto l’ultima volta che è venuto, ho cambiato i nomi degli attributi per evitare

che si ripetessero. Perché l’ho fatto? Perché dopo il prodotto cartesiano avrei messo una

condizione di uguaglianza proprio su quegli attributi ma quegli attributi mi sarebbero risultati

nella stessa tabella. Quindi cosa ho fatto? Ho usato la notazione puntata, l’operatore

omonimi

“ro” e ridenomino.

Ma ero certo che quando avrei guardato i due attributi rinominati, i loro valori coppia a

coppia sulla stessa tupla, a seguito della condizione di uguaglianza, sono perfettamente

identici. Questo perché la costruisco così la tabella. Cioè faccio il confronto e faccio restare

soltanto le tuple che hanno quel valore identico.

Allora che senso ha riportarmi due volte nel risultato l’attributo di confronto e lo devo pure

nell’altro?

cambiare di nome, in un lato e

Pertanto un natural join è un equijoin nel quale gli attributi omonimi vengono riportati una

sola volta. Si chiama natural proprio perché è la cosa più semplice. Cioè trovo gli stessi

attributi, applico la condizione di join e me lo riporto una sola volta.

Viene così semplificata la tabella e risparmio sulla denominazione.

Scopriremo presto che buona parte della dimensione in termini di tabelle in un Database è

costituita da tabelle di transcodifica ovvero tabelle che servono a ridurre la probabilità di

scrivere infromazioni errate\ duplicate di cose che hanno la stessa semantica (esempio: se

penso a Cassano delle Murge, quel paese si chiama Cassano Murge o Cassano delle Murge?!

E’ chiaro che sono la stessa cosa però se io lo scrivo in un modo piuttosto che in un altro sono

due cose diverse? La tabella di transcodifica descrive univocamente come si chiamano tutti i

paesi. Il rischio di scrivere più volte la stessa cosa in modo diverso è altissimo e poi non ci

ritroviamo. Quindi con quelle tabelle standardizziamo una serie di contenuti che assumono

questa forma: “codice numerico – nome”.)

Questo serve al sistema per essere sicuri che non ci inventiamo un comune quindi affinché

l’unico nome noto è quello ufficiale.

ESEMPIO: (parola sottotitolata=

chiave)

STUDENTE (matr, CF, Nome, Cognome, ID_CDs) CORSO DI LAUREA (ID cds,

Nome_cds_id_prof_cord) ESAME SOSTENUTO (id_disc,matr, voto, data_esame)

DISCIPLINA(ID disc, nome_disc, CFU, ID_prof)

Voglio sapere tutti i nomi degli esami fatti e i voti ottenuti dallo studente avente matricola

570912.

Come si fa? Vado in ESAME SOSTENUTO e dico “trovami in ESAME SOSTENUTO

quelli che hanno la matricola 570912”

Per scoprire i nomi degli esami faccio un join naturale tra ESAME SOSTENUTO e

DISCIPLINA. Sotto il join naturale non devo scrivere nulla perché gli attributi sono identici.

Se non lo fossero stati avrei dovuto scrivere la condizione (?). Cioè nel nostro caso Id_disc è

proprio uguale a id_disc e

quindi si è capito di cosa stavo parlando. Altrimenti avrei dovuto scrivere qui sotto (?) la

condizione.

Se avessi detto: voglio sapere tutti i nomi degli esami sostenuti e i voti ottenuti dallo studente

CICCIO PASTICCIO. (Cioè metto il nome). Cosa dovevo fare? Avrei dovuto fare anche un

join con STUDENTE.

OSSERVAZIONE:

Immaginiamo di avere nella tabella in media 10\15 registrazioni per studente. Se contiamo

tutti gli studenti del Politecnico che sono circa 11000, la tabella esami avrà 165000

registrazioni. Per semplificare immagino di avere 10000 studenti e 150000 registrazioni.

(Registrazioni= esami sostenuti). Quando faccio il join naturale mi trovo 150 000 per 10000.

Quindi quante tuple abbiamo? 1 miliardo e 100000 tuple. Se facciamo altre operazioni il

numero cresce velocemente.

Nella realizzazione dei database che la differenza di performance tra un produttore ed un altro

capiremo che è determinata dalla velocità e dalle tecniche di ottimizzazione delle operazioni

da fare, che riesce a fare.

Però c’è un’operazione che va sempre fatta e che va sotto il nome di: PUSH DELLE

SELZIONI

Cioè se noi seguiamo la narrativa dell’interrogazione di prima noteremo che abbiamo fatto

bene. Ma se io cambio l’ordine delle parentesi avrò 15 tuple e non più 150 000 tuple. Quindi

io finché posso anticipo tutte le selezioni. Cioè con il PUSH DELLE SELEZIONI spingo in

avanti le selezioni.

ESEMPIO: Vogliamo il codice\id del professore che coordina il corso di laurea seguito da

Ciccio Pasticcio.

Usiamo le tabelle di prima.

Sicuramente avrò un join naturale tra STUDENTE e CORSO DI LAUREA. Uno è

sicuramente chiave, l’altro è sicuramente chiave esterna, hanno lo stesso nome. Ora devo

trovare lo studente Ciccio Pasticcio. Quindi devo selezionare gli studenti che hanno

nome=Ciccio AND cognome=Pasticcio (ovviamente facendo l’ipotesi che non ci siano

omonimi). A questo punto cerchiamo l’id del professore per cui proietteremo l’unico attributo

che realmente ci serve.

Per esempio al politecnico ci sono 21 corsi di laurea e 11 000 studenti quindi il risultato di

tutto ciò sarà 231 000. Ma ora se metto la parentesi rossa cioè se spingo la selezione in avanti,

mi cambia drasticamente “la vita”. Perché io ora faccio 1 tupla contro 21. Questo velocizza il

tutto. L’Algebra relazionale è realmente la forma interna che verrà usata nelle

OSSERVAZIONE:

operazioni dell’SQL. Noi studieremo l’SQL e delle sue estensioni (estenderemo gli

operatori). L’algebra relazionale lavora solo su tabelle. La forma interna di SQL è

l’algebra relazionale però noi studieremo una serie di estensioni dell’algebra

sostanzialmente

relazionale e dell’SQL che ci consentiranno di fare ad esempio delle operazioni che in

algebra relazionale non si possono fare perché l’algebra relazionale opera esclusivamente

L’

solo su relazioni. SQL rappresenterà quindi una forma esterna del lavoro del database, la

forma interna sarà comunque algebra relazionale.

NORMALIZZAZIONE DI UN DATABASE

Normalmente un database viene progettato seguendo delle linee informali (cioè si usa la

logica come abbiamo fatto fino ad ora) però è buona norma almeno verificare che il database

che noi creiamo sia comunque strutturato in modo da avere una ridondanza limitata e

controllata. Noi lo faremo imparando ad usare le tecniche di normalizzazione. Per

comprendere come funzionano le tecniche di normalizzazione dobbiamo partire dall’idea che

noi possiamo comunque costruire un database relazionale ipotizzando di partire da una

cosiddetta UNIVERSAL RELATION, una relazione universale contente tutti gli attributi del

mio dominio di interesse. Quindi la relazione universale contiene tutto quello che mi può

servire ma sicuramente è una relazione che conterrà molta ridondanza e avrà molte anomalie

(da inserimento, cancellazione ecc).

Quindi cosa potremmo fare? Potremo provare a DECOMPORLA cioè a ridurre

progressivamente il database da questa relazione universale, in tante tabelle a struttura

controllata. Per fare questo noi quindi NORMALIZZEREMO ossia decomporremo

progressivamente la nostra base di dati secondo delle regole. Nella realtà noi non faremo

questa storia però useremo queste tecniche per togliere eventuali ridondanze presenti cioè la

useremo come verifica a valle normalmente.

Per comprendere come funzionano le regole di decomposizione, è necessario fare un primo

passo cioè introdurre il concetto di dipendenza funzionale.

DIPENDENZA FUNZIONALE

Data una relazione R e due sottoinsiemi di attributi x ed y parte dello schema relazionale R,

diremo che x ed y sono in dipendenza funzionale e scriveremo x y ogni qual volta io

considero due tuple e le restringo su un sottoinsieme di attribuiti x, ciò implica

SICURAMENTE che la restrizione della stessa tupla sugli attributi di y coinciderà con la

y.”

restrizione della tupla T2 sugli attributi

Abbiamo una certezza.

Come faccio ad avere questa certezza? Quando si verifica una cosa di questo genere vuol dire

che si verifica una dipendenza funzionale. Diremo che y diremo funzionalmente da x.

ESEMPIO:

CORSO DI LAUREA (id-cds, nome-cds, id-cordinatore) Se io scrivo nome-cds=x e id-

cordinatore=y

Tra i due c’è una dipendenza funzionale? Sulla base della conoscenza del dominio, sono certo

che se capita uno capita anche l’altro ossia sono sicuro che c’è una dipendenza funzionale.

Cioè se nome-cds: ingengeria gestionale magistrale e id-cordinatore=Gorgoglione, tutte le

volte che incontro ingegneria gestionale magistrale sono sicuro che troverò in quella tupla

andando su un’altra restrizione esattamente: Gorgoglione.

Questo in generale non si verifica tutte le volte quando prendo un sottoinsieme di attributi.

Cioè se io prendo:

STUDENTE ( matricola, nome, cognome..)

Se x= nome e y= cognome posso dire che x y? NO! Cioè se trovo scritto Arturo non sono

certo che in un’altra tupla trovo il cognome Di Domenico.

La dipendenza funzionale implica il fatto che avvenga sempre.

ATTENZIONE: non c’è una riflessività. In generale può essere che x y ma in generale y

non implica x. L’implicazione è monodimensionale.

ESEMPIO:

7001 è il CAP di bari e provincia.

Bari è 70125? No perché bari è solo 7001. Quindi è un’implicazione su una parte non su

tutto.

Cioè 70125 Bari ma Bari non è implicato da 70125 perché anche 70122 è Bari, anche 70100

è Bari.

(ha detto così ma non è stato chiarissimo)

In generale noi dovremmo trovare la chiusura transitiva del database cioè tutto l’insieme delle

possibili dipendenze funzionali (ma noi faremo una versione più ragionevole di questo.)

ASSIOMI DI AMSTRONG

Esistono degli assiomi di Amstrong che ci servono per trovare nuove dipendenze funzionali a

partire da quelle note.

RIFLESSIVITA’= Se x è incluso dentro y, x implica y.

AUMENTO= Se ho 3 sottoinsiemi di attributi sempre appartenenti alla stessa tabella x, y e z

e si verifica che c’è una dipendenza funzionale tra x e y, allora è vera anche la dipendenza

funzionale x unito a zero e y unito a zero.

TRANSITIVITA’= se ho tre sottoinsiemi di attributi x, y, z, se è vero sia che y dipende da x e

sia z dipende da y, ciò implica che sia vero anche che z dipende da x. Z è transitivamente

dipendente da x.

Noi per costruzione abbiamo sempre delle dipendenze funzionali in una tabella perché

comunque ogni attributo dipende funzionalmente dalla chiave e quindi da un superchiave. Se

così non fosse gli attributi sarebbero liberi e non varrebbe più il concetto di unicità.

Questo ci servirà tra poco.

Ora numeriamo le forme normali.

PRIMA FORMA NORMALE= coincide con il concetto di relazione. Cioè ogni relazione

deve avere degli attributi che siano atomici ossia non ci possono essere attributi composti o

multi valore nella costruzione della relazione. La tabella è fatta da attributi atomici e per il

resto risponde alla definizione di relazione del modello.

SECONDA FORMA NORMALE= una tabella è in seconda forma normale se ogni attributo

non primo della tabella ha una dipendenza funzionale piena dalla chiave primaria.

Attributo primo= attributo che faccia parte di una generica chiave candidata.

Dipendenza funzionale piena= significa che essa è minima. Cioè supposto che questa sia una

dipendenza funzionale y dipende da x, supponiamo che io elimini anche un solo attributo e la

dipendenza funzionale cade.

Quindi diciamo che la dipendenza funzionale è piena se la parte a sinistra è minima cioè non

contiene attributi ridondanti rispetto alla dipendenza funzionale.

ESEMPIO: Ci sono 22 attributi nella parte sinistra e 23 nella parte destra. Da x io posso

togliere un attributo senza che cada la dipendenza funzionale? Se si la dipendenza funzionale

non è piena.

Se no e cioè se la dipendenza funzionale è piena, tutta la parte di x serve tutta insieme per

identificare la parte di y.

ESEMPIO:

ESAME SOSTENUTO (nome-disc, id-disc, voto, data-e, matricola)

La chiave è giusta.

Questa tabella è in prima forma normale? Tecnicamente no a meno che non accettiamo il

concetto di controllo di una parte degli attributi cioè la data è un attributo che nasce composto

quindi è quella che abbiamo definito una violazione controllata. Cioè io lo so che data è un

attributo composto fatto da giorno, mese ed anno ma lo so e lo gestisco come tale.

Quindi a questo punto possiamo definirla come tabella in prima forma normale perché siamo

certi che sapremo controllare la data.

E’ in seconda forma normale? Applichiamo la definizione. Vediamo quali sono gli attributi

primi. Gli attributi primi sono quelli che fanno parte della chiave. Gli attributi non primi

sono: nome-disc e voto. Ora vedo se hanno una dipendenza funzionale piena dalla chiave.

significa: “implicano tutte insieme”)

1 (id-disc, data-e, matricola) VOTO (

Voto ha una dipendenza funzionale piena dalla chiave.

Il fatto che ha una dipendenza funzionale piena dalla chiave vuol dire che se io non so

l’esame non posso sapere il voto, se io non so la matricola non posso sapere il voto e la stessa

cosa per la data dell’esame. Cioè ho bisogno di tutti e tre pienamente affinché io possa sapere

quello che non è parte della chiave cioè il voto.

Nel caso dell’attributo: nome-disc siamo in una situazione diversa. Perché se tolgo id-disc

cade la dipendenza funzionale ma se tolgo matricola il nome della disciplina lo so comunque

perché finché ho id-disc so il nome della disciplina.

Questo vuol dire che questa tabella non è in seconda forma normale. Ora quindi come

decompongo?

Se una tabella non è in seconda forma normale essa può essere decomposta in tabelle in

seconda forma normale cioè in due o più tabelle, individuando la parte della chiave da cui

l’attributo non primo ha una dipendenza funzionale piena e costruendo una nuova tabella in

cui copiamo la parte della chiave che costituisce la parte in dipendenza piena ed estraendo

prima dalla tabella originale e ricopiando nella nuova tabella l’attributo non primo che

violava la seconda forma normale.

RIEPILOGO:

1° PASSO: Ho una tabella, vedo se è in seconda forma normale cioè vedo se gli attributi che

non fanno parte della chiave dipendono interamente dalla chiave o no. Se dipendono

interamente dalla chiave ok festa grande: la tabella è in seconda forma normale. Nel nostro

caso noi abbiamo due attributi non primi di cui uno ha una dipendenza funzionale piena,

l’altro invece ha una dipendenza funzionale non piena cioè basta un pezzo della chiave ad

identificarlo univocamente. Quindi a quel punto dico che la tabella non è in seconda forma

normale.

2° PASSO: La tabella la posso portare in seconda forma normale. Come? Creo una nuova

tabella in cui copio la parte della chiave da cui la dipendenza funzionale era piena. Poi

estraggo l’attributo non primo su cui c’è la dipendenza funzionale. Chiave della nuova tabella

è proprio la parte della chiave che è stata copiata.

Decomponendo ottengo a meno di altri attributi la tabella con i NOMI DISCIPLINE. L’altra

è di nuovo ESAME SOSTENUTO. Per la prima tabella però ho scritto A invece che NOME

DISCIPLINA perché non so logicamente che cos’è Non so darle un nome perché in realtà io

la tabella la sto ottenendo per progressiva decomposizione cioè semplicemente guardano il

fatto che c’è ridondanza, tolgo i pezzi (mantenendoli logicamente correlati.) Prima invece

l’ho costruita sulla logica di come era fatta secondo me la base di dati e quindi ho scritto

questo è il nome della disciplina .

ESAME SOSTENUTO (nome-disc, ID-disc, voto, data-e, matr) A ( ID-disc, nome-disc)

Ora la tabella A è in seconda forma normale perché se una tabella ha una chiave costituita da

un attributo è per forza in seconda forma normale. Non saprei cosa togliere a questa tabella

quindi è in seconda forma normale.

Quindi se la chiave è un attributo la tabella è sicuramente in seconda forma normale.

TERZA FORMA NORMALE= Una tabella è in terza forma normale se dato uno schema

relazionale R, due sottoinsiemi di attributi x ed y, per ogni dipendenza funzionale y dipende

da x: x è superchiave per la relazione R oppure y è attributo primo della relazione R.

ESEMPI:

DISCIPLINA (ID-disc, nome-disc, CFU, ID-prof)

E’ in seconda forma normale. Per scoprire se è in terza dobbiamo guardare le dipendenze

funzionali.

Una volta che ho la chiave è normale che tutti gli attributi dipendono dalla chiave il problema

è se sono le uniche dipendenze. Che dipendono dalla chiave è nella costruzione del concetto

di chiave altrimenti se non dipendessero non ci sarebbe nessun vincolo di unicità.

Per essere quindi in terza forma normale ci devono essere altre dipendenze funzionali oltre

quella banale dalla chiave. Questa è in terza forma normale.

STUDENTE ( matricola, nome, cognome, ID-cds, nome-cds)

E’ in seconda forma normale perché ha un unico attributo.

E’ in terza forma normale? No perché esiste una dipendenza funzionale tra ID-cds (x) e

nome-cds

(y) ma x non è una superchiave e y non è un attributo primo. Quindi non è verificato nessuno

dei due.

Rispetto alla chiave matricola, nome non viola la terza forma normale.

FORMA ALTERNATIVA DELLA TERZA FORMA NORMALE:

Potremo dire anche che una tabella è in terza forma normale se non ci sono dipendenze

transitive sulla chiave. Cioè non posso applicare una forma di transitività per arrivare in

termini di dipendenza funzionale, alla chiave.

Se io guardo:

matricola ID-cds ma è anche vero che ID-cds- nome-cds e quindi è vero

matricola nome-cds

Cioè io arrivo all’ultimo per transitività. Se avviene ciò la tabella non è in terza forma

normale. La dipendenza deve essere diretta e non transitiva affinché la tabella sia in terza

forma normale.

Se aggiungiamo CF, codice fiscale, alla tabella STUDENTE, matricola e CF insieme non

violano la terza forma normale. Se faccio la dipendenza funzionale tra CF e matricola, CF

comunque sarebbe una superchiave.

Abbiamo quindi verificato che la tabella non è in terza forma normale. Come la portiamo in

terza forma normale?

PORTARE LA TABELLA IN TERZA FORMA NORMALE

Individuiamo gli attributi che violano la terza forma normale e creiamo una nuova tabella

nella quale riportiamo l’attributo da cui dipende l’altro attributo ossia quello che viola la terza

forma normale o quello per cui avviene la transitività. Quindi ottengo la tabella B. Estraggo

l’altro attributo su cui avveniva la dipendenza funzionale. Chiave della nuova relazione è

l’attributo che ho ricopiato. Un attributo fa la chiave e l’altro la chiave esterna

Quindi avrò:

STUDENTE (matricola, nome, cognome, ID-cds, nome-cds, CF) B ( id-cds, nome-cds)

Se faccio un join naturale sulla decomposizione riottengo esattamente la tabella originale

(anche nel caso precedente).

Quindi da un lato ora le tabelle ora sono più piccole e prive di ridondanze e di anomalie.

Dall’altro tutte le volte dovrò fare un join per metterle insieme.

FORMA NORMALE BCNF ( Boice cod, normal form)

Esiste anche una forma normale ulteriore, BCNF, la cui unica differenza rispetto alla terza

forma normale è che le uniche dipendenze funzionali ammesse sono quelle per cui se y

dipende funzionalmente da x, x è una superchiave. Non c’è la seconda parte: y è un attributo

primo.

La maggior parte delle tabelle in terza forma normale sono anche in forma normale di BC (

boice e cd)

Cosa si può verificare però? Che si abbia che x e y siano la chiave di questa tabella e che z

per esempio implichi soltanto x. Questa sarebbe una tabella in terza forma normale ma non in

forma normale di BC.

Comunque noi ci accontentiamo di tabelle in terza forma normale. Anche perché in generale

la decomposizione ulteriore è complicata e non aggiunge molto al lavoro di riduzione della

ridondanza e mantenimento delle dipendenze funzionali.

DECOMPOSIZIONE DI UN DATABASE

Vediamo cosa dobbiamo fare nel momento in cui sia necessario normalizzare ovvero ridurre

le ridondanze presenti in una tabella. Noi in generale partiremo nella vita reale usiamo le

tecniche di normalizzazione come verifica a posteriori degli effetti della progettazione che è

stata svolta.

Cioè noi useremo il modello entità relazioni, poi costruiremo un database e poi vediamo se in

se c’è una presenza di ridondanza.

quello che abbiamo costruito ci siamo sbagliati,

In generale però può capitare che per esempio siamo noi a vedere la base di dati fatta da un

altro e dovremo capire se questa base di dati è fatta bene o male. Teniamo presente che finché

si tratta di un progetto cioè stiamo ancora a scrivere o a disegnare la base di dati, poco male,

aver sbagliato la normalizzazione di un database non è grave.

Quando però lo iniziamo a riempire di dati diventa complicato. E diventa ancora più

complicato quando non ci accorgiamo del fatto che il database non è normalizzato e troviamo

le anomalie in corso di esercizio. Ecco perché è comunque opportuno fare questa verifica.

A livello esercitativo ed in previsione di uno degli esercizi che faremo, impareremo a

normalizzare database fatti con i piedi. Il nostro problema quindi sarà decomporre quella

tabella per creare un database che abbia senso.

ESEMPIO

Quindi ci diamo un database definito su un’unica tabella. Mettiamo in un’unica tabella tutti

gli attributi e poi decomponiamo. Il dominio a cui ci riferiamo è quello di una biblioteca

universitaria. Quindi gestiamo tutte le cose che hanno a che fare con una biblioteca

universitaria.

Faremo due semplificazioni:

considereremo gli autori singoli e non multi-valori

e non possiamo prendere e lasciare lo stesso libro più di una volta al giorno.

Quindi supponiamo di creare questa tabella che contiene informazioni appunto su una

biblioteca universitaria e al prestito.

TABELLA NON NORMALIZZATA

Ora data questa tabella non normalizzata, redatta da un inesperto progettista cerchiamo di

capire qual è la CHIAVE di questa tabella.

(Non dobbiamo vedere la chiave messa dall’inesperto progettista perché sarà sbagliata.)

Ragioneremo sulla base del fatto che l’intera tabella è una SUPERCHIAVE per costruzione.

Come faccio ora ad individuare una chiave o una chiave candidata?

Sfrutterò le dipendenze funzionali a me note.

Cioè io individuo una chiave dal fatto che da quel sottoinsieme di attributi dipende tutto il

resto. Questo ci dice il fatto di essere chiave. E dipende direttamente.

DOMANDA: Qual è la chiave? Cioè quali sono gli attributi da cui tutto dipende?

Individuiamo le dipendenze funzionali che osserviamo. A partire da quelle scopriremo tutto il

resto. E’ chiaro che nome, cognome, e mail, telefono dipendono da matricola.

L’ISBN identifica l’edizione specifica di un libro.

Possibili chiavi proposte dagli studenti: (tra queste ci sarà quella giusta) 1 (matricola, ISBN,

armadio_pos, ord_scaffale, libro, data prestito)

(matricola, titolo libro, data prestito)

(matricola, armadio, scaffale, libro, data prestito)

(armadio_pos, scaffale_pos, data prestito, ord_scaffale) 5 (matricola + 4)

SPIEGAZIONE PER CAPIRE QUAL’E’ LA VERA CHIAVE

Siccome siamo abituati al fatto che i libri vengono rubati, la prima cosa a cui pensiamo è la

matricola perché mi fa capire chi ha il libro. Invece noi creiamo questo database

tenendo presente che

manterremo le dipendenze funzionali esistenti il che vuol dire che quando ho finito di

decomporre è identificabile eccome chi si è preso il libro però non fa parte della chiave.

ISBN= Individua la stessa tipologia di libro. (Identifica in generale: il titolo, l’autore, l’anno

di edizione e l’editore.) Quindi una volta individuato il libro fisico, abbiamo l’ISBN. E quindi

contrario. Se io prendo il libro fisico e lo giro leggo l’ISBN. Noi abbiamo la

non il

collocazione fisica del libro il che vuol dire che esiste una dipendenza funzionale tra la

collocazione del libro e L’ISBN. ISBN= non fa parte della chiave

Perché la matricola non serve? Perché nel momento in cui so esattamente il libro e so

esattamente il momento in cui è stato prestato, so a chi è stato prestato. Non vuol dire che

c’è

deve stare nella chiave, vuol dire che una dipendenza funzionale. Ovviamente il libro verrà

prestato N volte nella sua vita ma una volta che so esattamente il libro e quando è stato

l’ha.

prestato so chi ce

Bisogna ragionare in termini di dipendenze funzionali. Matricola= non fa parte della chiave

c’è una dipendenza funzionale. Dipende

Data_restituzione non fa parte della CHIAVE perché

dal singolo prestito.

In generale quello si è riempito solo quando il prestito si è compiuto ?

Il titolo del libro non dice nulla. Non identifica il singolo libro e nemmeno l’edizione oltre al

fatto che in molti casi un titolo non è univoco. Se io scrivo: La seconda guerra mondiale avrò

tanti libri con quel titolo. Da cosa lo indentifico? Dall’autore, dall’editore, dall’anno di

edizione.

Titolo= non fa parte della chiave Quindi la chiave corretta è:

CHIAVE= (armadio_pos, scaffale_pos, ord_scaff, data_prestito, ora_prestito) ( è quella

sottolineata nella prima tabella)

Una persona non può riprendere un libro nello stesso giorno, ma in generale un libro può

essere lasciato e preso da un altro. Quindi può essere restituito e preso da un altro.

Data e ora= lo chiameremo sempre=TIME STAMP: marca temporale che rappresenta un

momento del tempo.

L’ora è ora, minuti, secondi che rappresenta un momenti nel tempo.

Ovviamente il momento nel tempo da solo non basta perché posso avere tante postazioni e

quindi posso prestare libri contemporaneamente ma non lo stesso libro contemporaneamente.

Domanda: Con le ipotesi fatte matricola, data prestito è equivalente a data prestito ora

prestito? No perché tu puoi prendere più di un libro. I vincoli sono quelli di prima più quello

naturale che sei sei una matricola non puoi avere 3 personalità. Quindi tu in generale puoi

arrivare in biblioteca chiedi 2 libri e te li prendi. Tu sei la stessa persona ma i libri sono

diversi.

2° PASSO= Decomponiamo la tabella

Identificata la chiave, vediamo se la tabella è in seconda forma normale. La chiave è grossa,

ha più di un attributo. Se troviamo una violazione sono certo che non è in seconda forma

normale. Per esempio titolo libro dipende soltanto da armadio, scaffale e ordine e non dal ?.

Quindi non è in seconda forma normale.

Vediamo se è in terza forma normale. (Se una tabella non è in terza forma normale potrebbe

essere in seconda ma in questo caso non è in seconda e non è quindi neanche in terza). Non è

in terza forma normale.

Matricola = dipende da tutta la chiave quindi è un attributo primo.

Nome, cognome, id_cds, nome_cds, e-mail, telefono, cordinatore_cds, dipendono tutte da

matricola. Quindi ora posso decomporre:

avrò una nuova tabella A in cui matricola sarà la chiave.

A=( matr, nome, cognome, id_cds,nome_cds, e mail, tel, cord_cds)

Questa tabella è sicuramente in seconda forma normale. Non è però in terza forma normale. Il

fatto che io abbia fatto una prima decomposizione questo non assicura che tutto quello che

resta sia già come la desideriamo. Finiamo di lavorare su quella originale.

Ci sono altre violazioni evidenti della terza forma normale e anche della seconda nella prima

tabella? Titolo, autore, anno, editore dipendono da ISBN il quale a sua volta dipende

parzialmente dalla chiave. Cioè ISBN e tutto il resto dipendono da una parte della chiave.

Quindi questa è un violazione della seconda forma normale. Cioè la data del prestito non

l’ISBN.

implica nulla. Invece se so il libro lo giro e so Quindi ora estraggo e copio ISBN che

diventa la nuova chiave e mi estraggo anche tutto il resto.

B= (ISBN, autore_libro, titolo_libro, anno_ed, editore) un’altra

Però non ho ancora risolto. A non è ancora in terza e nemmeno in seconda. Credo

tabella che chiamo C.

C=(armadio_pos, scaffale_pos, ord_scaff, ISBN)

Questo è lo stesso meccanismo che avrei usato per decomporre in seconda forma normale.

Devo togliere qualche altra cosa? Dobbiamo osservare che non tutti gli attributi che ci

sembrano importanti non implica che necessariamente facciano parte della chiave.

La Tabella A è in seconda forma normale ma non è in terza forma normale perché nome_cds

e cord_cds dipendono da id_cds. Allora creo la tabella D:

D=( id_cds, nome_cds, cord_cds) Ora quindi vedo che:

La tabella A corrisponde ai dati di UNO STUDENTE,

La tabella B corrisponde ai dati di UN LIBRO, un libro perché in generale ce ne saranno tanti

con lo stesso ISBN. Se io conosco il titolo del libro o l’autore del libro, scopro una delle sue

posizioni andando in biblioteca, dico una posizione perché può starci più volte. Quindi

sapendo ISBN so anche la sua posizione.

La tabella C ( sul mio quaderno ho scritto libro specifico ma non sono sicura) 4 La tabella D

corrisponde ad un CORSO DI LAUREA

Quello che rimane è la parte core del nostro problema ossia la: gestione del prestito e della

restituzione.

Biblio=( matr, armadio_pos, scaffale_pos, data_prestito, ord_prestito, data_restituzione)

Dentro Biblio è rimasta la chiave e tutto quello che dipende esclusivamente, pienamente da

tutta la chiave. Cioè la restituzione ovviamente dipende dal singolo prestito e anche la

matricola dipende dal singolo prestito, cioè attraverso il singolo presto ho identificato a chi

l’ho dato. Cioè matricola e restituzione posso farli dipendere se non dall’intera chiave. Così

funziona quando ce l’ho in terza forma l’attributo

normale. Ho cioè la chiave e dipende da tutta

l’attributo dipende transitivamente dalla chiave siamo già in

la chiave e solo da quella. Se

violazione della seconda forma normale.

Così quindi abbiamo un metodo per capire se il database è fatto bene o male. Ovviamente per

fare questo è necessario sempre conoscere il DOMINIO ossia la semantica, il significato

dire che:” autore dipende semanticamente da ecc”

degli attributi. Sono cioè in grado di

perchè conosciamo il significato degli attributi e delle tabelle su cui lavoriamo.

2 PARTE: ESERCIZI SULL’ALGEBRA RELAZIONALE

Questa parte non corrisponde ad un esercizio dell’esame ma è una parte introduttiva per

quanto riguarda gli esercizi sulle query. Vediamo qual è la tecnica per risolvere le

interrogazioni in un database.

In cosa consistono questi esercizi: noi vi diamo una serie di tabelle predefinite, solitamente

sono 3 e poche volte arrivano a 4 e poi vi diamo una serie di interrogazioni.

1° ESERCIZIO

LIBRO= ( cod-libro,autore, titolo) UTENTE= (cod-utente,nome,cogn)

PRESTITO=(cod-utente,cod-libro,data-prestito)

Ogni utente può chiedere in prestito uno o più libri ed al contrario un libro può essere prestato

uno o più utenti. E’ come se fosse la N ad N derivante dalla relazione libro-utente.

ad

Nell’ultima tabella abbiamo due chiavi: codice utente e codice libro e abbiamo anche un

attributo sulla relazione:data prestito.

In questo primo esercizio si parla di libri, utenti ed operazioni di prestito. Molto spesso queste

tabelle non sono completamente normalizzate. Quindi l’autore dovrebbe essere un’entità a

parte con una propria tabella ed una chiave esterna. Per semplificare autore si considera

direttamente come la chiave di autore come codice fiscale, o la sua matricola o si considera la

tabella non normalizzata ma giustificata dal rendere tutto più semplice.

PRIMA INTERROGAZIONE= trovare tutti i titoli dei libri presi in prestito il giorno 13 luglio

relazioni, anche qui non c’è un unico

2011. Così come abbiamo visto per il modello entità-

modo di risolvere questa interrogazione anzi solitamente in questi casi ci sono ancora più

possibilità rispetto al modello entità relazioni perché in questi casi noi possiamo partire da

una qualunque di queste tabelle per fare queste interrogazioni. Però in questi casi scegliere

una tecnica piuttosto che un’altra incide pesantemente sulle prestazioni. Ad esempio decidere

di fare più join porta ad avere una pesantezza della query oppure decidere di fare join con più

attributi, con più tuple rende la query più pesante.

Per l’esame considerano corrette tutte le possibili risoluzioni a patto che queste diano un

risultato corretto però nella realtà facciamo queste considerazioni quando risolviamo un

esercizio.

Da quale tabella partiamo per questa interrogazione? Ci servono i prestiti fatti in una precisa

data. L’attributo l’abbiamo

data ce in prestito. Quindi magari possiamo partire con una

tabella prestito. Quindi andiamo a creare questa relazione intermedia che

selezione fatta sulla

chiamiamo S

1.

Come detto andiamo a selezionare soltanto quelle tuple in cui la data prestito è uguale alla

data indicata.

Di questi libri prestati in questa data vogliamo anche i titoli. Il titolo però lo abbiamo nella

libro e quindi dobbiamo fare un join fra questa relazione S e libro.

tabella 1

Da questa relazione avremo tutti i libri prestati, per ogni libro avremo anche tutti i suoi

attributi. L’ultima operazione da fare è una proiezione e quindi ricavare unicamente i titoli.

Come potrebbe essere risolta anche questa interrogazione?

Ad esempio potrei scambiare S1 ed S2. Cioè fare prima un Join fra libro e prestito e poi sulla

tabella generata dal join, fare la selezione. Però il problema è che magari nel primo caso (

come ha detto prima) avremo 100 tuple e le informazioni dei libri nel secondo caso descritto

ora avremmo tutti i libri prestati e quindi potremmo avere 1000\2000, un numero grande di

più pesante. Il risultato è lo stesso però l’operazione non è

libro ed il join risulterebbe

ottimizzata.

INTERROGAZIONE= Selezionare gli autori dei libri presi in prestito dall’utente Paolo

l’utente

Bianchi. In questo caso sappiamo che è Paolo Bianchi e visto che nome e cognome

stanno nella tabella Utente iniziamo con una selezione su questa tabella.

Questa volta abbiamo due condizioni: nome e cognome devono essere Paolo Bianchi e quindi

mettiamo in and queste due condizioni.

Ora vogliamo i libri presi in prestito da questo utente. Quindi dobbiamo fare un join tra S1 e

PRESTITO.

A questo punto abbiamo tutti i libri presi in prestito da questo utente. Di questi libri vogliamo

gli autori. Autore è in LIBRO e quindi facciamo un altro join tra S2 e libro.

A questo punto abbiamo tutte le informazioni sui libri presi in prestito da questo utente.

Vogliamo soltanto gli autori e quindi facciamo una proiezione.

INTERROGAZIONE= Estrarre il codice utente di persone che hanno preso in prestito un

libro scritto da Camilleri o da De Luca.

Prima cosa che possiamo fare è estrarre i codici di tutti i libri scritti da Camilleri o da De

Luca.

Questi libri li utilizziamo per fare un join con PRESTITO per sapere tutte le operazioni di

prestito riguardanti questi libri. dell’utente

Questa volta non abbiamo bisogno di fare un ulteriore join con UTENTE perché

vogliamo solo il suo codice e questo codice già lo abbiamo nella tabella PRESTITO. Quindi

possiamo fare direttamente una proiezione sulla relazione S2.

Attenzione: quando abbiamo estratto i codici di tutti i libri abbiamo scelto di mettere in or le

due condizioni, autore Camilleri o autore De Luca, perché vogliamo che gli utenti abbiano

preso in prestito il libro di un autore o di un altro senza porci il problema se li hanno presi

entrambi o hanno preso in prestito il libro solo di un autore piuttosto che di un altro. Quindi

in questo caso non c’è questa condizione particolare Ora vedremo la differenza che si ha nella

query con la domanda n° 4 in cui chiediamo di:

INTERROGAZIONE= Estrarre il codice di persone che abbiano preso in prestito libri SOLO

di Camilleri.

Per risolvere questa interrogazione per prima cosa sicuramente devo prendere tutti gli utenti

che almeno una volta abbiano preso in prestito un libro di Camilleri. Quindi in maniera simile

al caso precedente estraggo tutti i codice utente però mettendo come unica condizione: autore

Camilleri.

Però chi mi assicura che quello stesso utente non abbia mai preso in prestito un libro di un

certezza devo applicare un’operazione differente:

autore diverso? Per avere questa

un’operazione di differenza tra due insiemi. Avrò un insieme che rappresenta tutti gli utenti

che hanno preso in prestito un libro di Camilleri ora mi serve un secondo insieme che

rappresenta tutti gli utenti che hanno preso in prestito un libro non di Camilleri. Di questi due

insiemi faccio la differenza. Quelli tratteggiati sono quelli che mi interessano.

Nell’intersezione ho gli utenti che hanno preso in prestito libri di Camilleri e anche da altri

autori. quell’autore,

Quindi quando ci sono interrogazioni del tipo: Solo da solo di un anno, solo di

una certa caratteristica, non mi basta più una query ma quasi sicuramente ho bisogno della

differenza tra insiemi.

Quindi mi servono in questo caso altre 3 operazioni\ relazioni. Parto dalla condizione che

l’autore sia diverso da Camilleri.

Poi vedremo come ognuna di queste operazioni avrà esattamente la controparte per

l’interrogazione di un database.

SQL: STRUCTURED QUERY LANGUAGE

E’ un linguaggio di interrogazione strutturato. E’ stato sviluppato originariamente per

utilizzare DBMS relazionali. Anche questo quindi è nato a casa di IBM. I principali

costruttori di questi linguaggio (1970) sono due signori che si chiamano CHABELAIN E

BOISE. In realtà è più che altro una famiglia di linguaggi.

Cominceremo dal

DDL=DATA DEFINITON LANGUAGE: il linguaggio per dichiarare la struttura della base

di dati. Quello che serve per definire le tabelle e i vincoli tra le tabelle.

Poi esiste il

DML=DATA MANIPULATION LANGUAGE. Linguaggio di manipolazione dei dati.

Presupponendo di avere la base di dati, ci consente di inserire, modificare e cancellare.

A seconda delle tradizioni nel DML si introduce anche il concetto di DML+QUERY

LANGUAGE , Cioè + il linguaggio di interrogazione vero e proprio. Quindi oltre che

modificare possiamo interrogare, ossia instanziare la base dei dati, chiediamo qualcosa alla

base di dati.

A questi due elementi fondamentali si aggiunge:

DCL= DATA CONTROL LANGUAGE. Gestisce le politiche di acceso. Cioè noi possiamo

accedere ad un database secondo delle regole, permessi, modalità di visione, parti a cui

possiamo accedere, parti che ps siamo modificare. In alcuni casi uno ha un accesso in

visualizzazione. Normalmente una base di dati è normalmente un oggetto che viene visto da

tanti utenti, alcuni di questi possono solo visualizzare, altri possono inserire, modificare e

cancellare.

DMCL ( DEVICE MEDIA CONTROL LANGUAGE) Si occupa di dispositivi di

immagazzinamento dei dati cioè della gestione ed ottimizzazione dei dispositivi ossia i dischi

che mantengono stabilmente la base dei dati.

SQL è un linguaggio dichiarativo cioè noi dichiariamo qual è il risultato che vogliamo

ottenere. Non è un linguaggio operativo o procedurale. Non è come un linguaggio standard in

cui noi scriviamo passo passo la procedura per ottenere il risultato. Ad esempio l’algebra

relazionale è un linguaggio procedurale in cui noi scriviamo i singoli operatori.

Nel linguaggio SQL avremo la possibilità, in alcuni momenti, la possibilità di interagire senza

vedere la forma interna.

La caratteristica di SQL è che sempre stato un linguaggio ben standardizzato cioè è un

che ha uno standard ISO 900075. E’ un linguaggio che ha avuto tante versioni.

linguaggio

L’ultia versione è l’SQL 2011. Sulla versione SQL 3 o SQL 99 si basa l’SQL in generale.

Tutte le altre versioni introducono altri elementi. Noi dobbiamo tener presente che abbiamo

tanti produttori di BMS relazionali: oracle, microsoft, postgress ecc…Qual è la differenza tra

questi? Il linguaggio base è sempre lo stesso ma ognuno di questi poi introduce delle

ottimizzazioni, estensioni del linguaggio (potremmo vederli come “accessori”) che servono a

far preferire un produttore piuttosto che un altro. A volte un accessorio diventa così

esempio l’ABS: adesso

comunemente utilizzati che diventano parte dello standard. Come per

ce l’hanno tutte le macchine invece un tempo non lo avevano tutte questo perché nel tempo

cambiano gli standard. L’SQL ha avuto questa fortuna: è stato sempre gestito con grande

attenzione alla standardizzazione. Questo serve anche “ad uno che impara SQL”, nel senso

che è un linguaggio che ancora oggi fa lavorare chiunque cioè se sei un bravo programmatore

o un bravo analista di database è impossibile che “stai a spasso”. Imparare il linguaggio SQL

è un investimento nel tempo: è difficile che cambi strutturalmente questo linguaggio cioè la

parte base rimarrà sempre la stessa. Altri linguaggi di programmazione sono più soggetti alle

mode.

DDL

Impareremo a descrivere un database in SQL. Abbiamo già imparato a progettarli. Abbiamo

imparato come passare dal modello concettuale al modello relazionale, quindi sappiamo

concettualmente costruire due tabelle e sempre concettualmente definire dei vincoli sulle

tabelle.

Adesso impariamo come si redige una base di dati in SQL. (A seconda del produttore di

DBMS cambieranno le interfacce. Quindi in molti casi noi non dovremo scrivere ma potremo

utilizzare un’interfaccia grafica che ci consentirà di scrivere quello che impareremo in forma

testuale, in una forma grafica più gradevole. Però le interfacce cambiano. Mentre noi oggi

impariamo quella che è la struttura del linguaggio che è indipendente dalla particolare

interfaccia.)

Abbiamo sei gruppi di domini elementari. Noi abbiamo definito gli attributi e ad ogni

attributo ora può essere assegnato\associato un dominio.

DOMINIO= un insieme di valori ammissibili ed implicitamente anche un insieme di

operazioni che su quei valori potranno essere eseguite.

1° DOMINIO ELEMENTARE: CARATTERE

E’ un dominio che consente di definire singoli caratteri, cioè attributi costituiti da singoli

caratteri o da stringhe. La struttura sintattica è la seguente. Attenzione che le parentesi quadre

un’istanza

[ ] corrispondono al significato che hanno nelle espressioni irregolari cioè 0 ho del

parentesi quadra. Adesso spiega il significato di quest’ultima

contenuto della cosa.

Questa è la forma sintattica della definizione di un attributo di tipo carattere:

character [varying] [ ( nro_char) ] [ character set nome_set ]

legenda: nro_char: n° caratteri

E’ necessario che in questa definizione di dominio ci sia scritto character.

La parentesi quadra dice che il testo che sta lì dentro è opzionale e può essere presente 0 o

volta. Quindi varying può non starci. Se non c’è nulla (nella parentesi quadra credo)

una

significa che stiamo dichiarando un attributo con un carattere e non con una stringa di

caratteri.

Le parentesi tonde invece sono parte delle frasi del linguaggio.

c’è

Poi ancora opzionale character set =tipologia di carattere che devi usare. Noi normalmente

dell’alfabeto “feta”

usiamo quello occidentale però in questo caso se vogliamo scrivere e lo

scrivere non secondo l’alfabeto latino ma secondo quello greco, lo possiamo fare

vogliamo

imponendo che il character set sia greco.

Vediamo per esempio una dichiarazione di attributo:

versione compatta: Codice_fiscale char (13)

prodotto_greco varchar(100) character set Greek

altrimenti dovremmo scrivere: character [ varying] [ ( nro_char) [ character set nome_set]

[varying] e [ character set nome_set] sono opzionali

Nessuno scrive: character. Tutti scrivono la forma corta: char

Nessuno scrive: character varying. Tutti scrivono la forma contratta varchar.

Che differenza c’è tra char e varchar?

Es: Quando dichiaro che CODICE FISCALE è character(13) presumo che sia sempre di

tredici caratteri. Questo significa che ad ogni tupla sono riservati per sempre 13 caratteri.

Es: Se io scrivo prodotto_greco character(100) presumo che siano riservati sempre 100

caratteri per ogni tupla per scrivere prodotto greco.

Se scrivo character varying o varchar il sistema a seconda dell’implementazione può decidere

in autonomia se allocare un numero di caratteri variabili per tupla a seconda del dato

effettivamente inserito.

Es: “feta” è four character sia in greco che in italiano quindi se scriviamo character o char

invece di varchar degli altri 96 caratteri non sappiamo che farcene. Se scriviamo invece

l’allocazione

varchar abbiamo la speranza (non è detto che lo faccia) che il DBMS ottimizzi

delle spazio, prendendo solo 4 caratteri e non 100, ossia quelli che realmente servono.

DOMANDA: Se io scrivo feta character(100), ci sono 4 caratteri occupati e negli altri 96 che

c’è scritto? In questi casi si usa sempre un carattere speciale di fine stringa valida. Esso è un

carattere riservato che un tempo si chiamava: no-printing character ossia un carattere che non

è stampabile e che servono a dirci che oltre quel punto c’è scritto qualcosa è invalido. Io non

lo so che sta scritto. Nessuno lo sa. Li dentro c’è l’ultimo contenuto che qualcuno ha scritto

precedentemente. A seconda di dove sta scritto cambierà ragionevolmente.

è un fine stringa ma dappertutto c’è.

Nel linguaggio c \0

RICORDA: Qualsiasi sia la rappresentazione, siccome c’è uno spazio utile che normalmente

non viene riempito fino all’ultimo, per sapere dove finisce c’è un carattere di fine stringa.

Analogamente:

DOMINI ELEMENTARI (2)

Bit: introdotto in SQL2 assume due valori 0 ed 1.

Bit [varying] [ (nro_bit)]

Es con uso della forma compatta:

Sequenza bit(5) è una stringa di 5 bit Codice varbit(16)

Esiste anche il tipo che si chiama: boolean che è analogo a bit. A seconda

dell’implementazione troveremo bit o boolean: non cambia nulla.

DOMINI ELEMENTARI (3)

TIPI NUMERICI ESATTI: consentono la rappresentazione di numeri interi e in virgola fissa.

Integer

Smallint

decimal [(precision[,scale])]

numeric[(precision[,scale])]

Ciò che è tra parentesi quadra è opzionale.

Precision= n° totale di cifre significative assegnate a questo attributo Scale= di tutte queste

cifre quante vanno considerate dopo la virgola.

: che vuol dire quest’espressione? Significa che io potrei scrivere

decimal [(precision[,scale])]

un attributo scrivendo soltanto decimal senza imporre nulla e in questo caso decide

l’implementazione del sistema oppure potrei scrivere soltanto precision.

[(precision[ = la seconda parentesi quadra ci fa capire che anche quello che viene dopo è

opzionale. Che differenza c’è tra: integer,smallint ebigint?

Lo spazio in bit assegnato e di conseguenza il numero massimo ed il numero minimo che può

essere

immagazzinato li dentro. In particolare tipicamente (ma anche questo è lasciato alle singole

implementazioni) un integer è fatto di 4 byte, uno smallint di 2 byte e un bigint di 8 byte.

Qual è il numero più grande che possiamo rappresentare con uno smallint di 2 byte? 2^16 che

è

64000. Non è tanto. Una volta che abbiamo dichiarato un dominio quello resta tutta la vita.

Quindi da un lato non bisogna farli troppo grandi perché potrebbe essere inutile.

Il numero più grande che potremo rappresentare con uno smallint non è in generale 64000

perché sono numeri con il segno. Quindi in realtà il numero più grande che possiamo

rappresentare è 32367 perché uno dei 16 bit se ne va per rappresentare il segno e quindi non

andiamo da 0 a 64000. A L’intervallo va da quant’è 4 byte?

-32367 A +32367. Per integer

2^32 è circa 2 miliardi.

E’ importate sapere la dimensione delle cose.

Differenze tra integer e smallint:

integer e smallint non consentono di controllare la precision (dettata dal sistema di calcolo),

ma precision di integer = di quella di smallint.

Numeric:ci consente di rappresentare i numeri in virgola fissa. Es.:

dato_vendita numeric(6,2)

Che vuol dire? Sto impegnando 6 cifre complessive e di queste 2 sono da considerarsi dopo la

–9999,99

virgola. Quindi posso andare da e +9999,99.

Differenza tra numeric e decimal:

la precision di numeric è un valore esatto ed è il minimo per decimal.

Questo vuol dire che se l’implementazione del sistema mette più cifre significative per i fatti

suoi, in decimal vedremo che come minimo ci sono le cifre indicate ma se il sistema ne mette

di più è accettato. Invece con numeric vuol dire che noi vogliamo esplicitamente ed

esclusivamente quella rappresentazione.

TIPI NUMERICI APPROSSIMATI: consentono la rappresentazione di numeri in virgola

mobile. Come funziona un numero in virgola mobile?

In una rappresentazione in virgola mobile io memorizzo queste due grandezze: mantissa ed

esponente. 10 non viene memorizzato.

Con i numeri in virgola mobile noi rappresentiamo sempre un sottoinsieme finito dei numeri

reali. Ed è importante ricordare che questo sottoinsieme in generale e quindi non solo per

SQL non è un sottoinsieme uniformemente distribuito. Di questo dovremmo accorgercene

ogni qualvolta usiamo una calcolatrice. Cioè la rappresentazione in virgola mobile concentra

il numero di campioni ossia il numero di elementi di questo sottoinsieme, verso lo zero 0.

Ci sono tre tipi di rappresentazioni di dominio:

Float [(precision)]

Real

Double precision

Con Float [(precision)] possiamo esplicitare la precision: il n° di cifre della mantissa oppure

possiamo assegnare, come fanno la maggior parte delle persone: Real o duble precision.

La differenza tra le rappresentazioni sta sempre nel numero di caratteri.

Un Real ossia un numero reale in virgola mobile è rappresentato con 4 byte nella forma:

l’esponente.

byte riservati alla mantissa ossia 24 bit e 1 byte per

Un tipo Duble precision occupa 8 byte che sono organizzati come segue: 7 byte per la mantissa

perché l’obiettivo è aumentare la mantissa e quindi la precisione, il numero di cifre

significative. Con 7 byte abbiamo numero molto grandi. Il numero più grande che possiamo

10^127

rappresentare con 1 byte è: o

10^(-127).

DOMINI ELEMENTARI (6)

Data e ora: consentono di rappresentare istanti di tempo.

[La prima violazione che ci ha anticipato di SQL rispetto al modello relazionale puro è che non

è vero che proprio tutti gli attributi sono atomici ma alcuni attributi sono strutturati. I primi

attributi strutturati che abbiamo inoltrato sono questi che diremo ora.]

Quindi avremo che la rappresentazione del tempo può essere fatta come:

Date

Time[(precision)] [with time zone]

Timestamp[(precision)] [with time zone]

[(precision)], [with time zone] sono opzionali.

[with time zone] : riferimento rispetto a GMT. Fa riferimento all’ora di

Greenwich.

Date nella forma: year-month-day (yyyy:mm:dd);

Time nella forma: hour-min-sec (HH:MM:SS); Abbiamo un defoult per precision. Default di

precision: 0 (s) per time. Però se scriviamo anche precision 2 possiamo misurare i centesimi

di secondo.

Per Timestamp la precision per default sono 6 cifre e quindi misuriamo il microsecondo:

10^(-6 secondi).

Timestamp vuol dire “marca temporale” ossia è una rappresentazione il più fedele possibile

del concetto di “istante nel tempo”, un momento nel tempo. Come misuro un momento nel

tempo, un timestamp? Un timestamp rappresenta insieme un attributo strutturato che include

l’intera data, l’ora e una rappresentazione al milionesimo di secondo e quindi è un attributo

strutturato grosso che però ci da una rappresentazione effettivamente del momento nel tempo.

DEFINIZIONE DI DOMINI ELEMENTARI (7)

Time intervals: consentono di rappresentare intervalli di tempo

E’ possibile rappresentare anche intervalli di tempo che possono avere due forme:

Year to month

Day to second

Anche a questi può essere associate una precision. Es:

durata interval year(5) to month

permette di rappresentare intervalli temporali fino a 99.999 anni e 11 mesi

durata interval day(4) to second (6)

permette di rappresentare intervalli temporali fino a 9.999 giorni, 23 ore, 59 minuti e

59,999999 secondi, con una precisione al milionesimo di sec.

DEFINIZIONE DI NUOVI DOMINI

A partire dai domini elementari che sono domini non indicizzabili in generale, è possibile

definire nuovi domini.

Esempi di domini non indicizzabili abbastanza diffusi, sono:

Blob = binary large object

Clob = character large object

Che vuol dire domini non indicizzabili? Vuol dire che è un dominio su cui non posso fare

delle ricerche. Se io faccio un dominio dei numeri interi posso chiedere: “ mid dici la tupla

ha per quel dominio il valore 3?” Cioè posso interrogare. Invece Bob e Clob sono domini

che

non indicizzabili cioè accompagnano una tupla, fanno parte di una tupla ma eventuali

ricerche vanno fatte su altri domini della tupla. Essi sono invece domini contenitori.

Un blob contiene tipicamente immagini, filmati la cui descrizione non sta nel blob ma sta da

un’altra parte.

Un clob contiene tipicamente testo, una pagina, un documento. Ma non indicizziamo il

contenuto del valore ma indicizziamo un’altra parte della tupla.

E’ possibile definire nuovi domini quindi nuovi tipi di dati come specializzazioni e vincolo

dei tipi esistenti. La forma sintattica è la seguente:

Create domain DomainName as DataType [DefaultValue][Constraint]

Quindi opzionalmente possiamo definire un valore di default [DefaultValue] cioè un valore

che viene assunto in una tupla in cui non sia stato esplicitamente definito un elemento di

quella tupla ed eventuali vincoli [Constraint] esplicitamente definiti sull’attributo. Poi

vedremo come si definiscono i vincoli.

ESEMPIO:

create domain copie_vendute as smallint default 0

“ Crea un nuovo dominio che si chiama copie_vedute con poche copie. Default 0 significa

che se non scrivo nulla non verrà il valore NULL ma verrà il valore 0 "

In genale in una tupla io posso avere degli elementi valorizzati ed altri che assumono valore

NULL. In quest’ultimo caso se scrivo default 0 quando definisco l’attributo gli do in caso di

mancanza di conoscenza esplicita un valore di default.

SPECIFICA DEI VALORI DI DEFAULT

Default: valore assunto da un attributo in assenza di specificazione Default

<Generic|user|null>

Generic è un valore scelto (purché nel dominio), user è l’ID dell’utente che inserisce l’update,

null è il default generico.

Che valore può assumere un default? Un valore generico, un valore definito corrispondente

all’utente che inserisce quella tupla quindi un riferimento allo user e NULL che è il vero

valore di default.

DEFINIZIONE DELLO SCHEMA

Creiamo il nostro primo database da SQL poi in un unico sistema di gestione di basi di dati

possiamo creare tante basi di dati, tanti contenuti ognuno autoconsistente. Quasi tutte le parti

che corrispondo alla creazione della base di dati cominciano con CREATE.

Quindi possiamo definire lo schema di una base di dati. Lo schema sarà costituito da un

insieme di domini, tabelle, indici, asserzioni, viste e autorizzazioni.

Create schema [SchemaName] [[authorization] Authorizedname]

{DefiningElements}

[[authorization] = Vuol dire che poiché si presume che i database siano sistemi multiutente, noi

dobbiamo definire chi è la persona autorizzata ad usare quel database. Il creatore potrà poi

assegnare ad altri il privilegio di accesso alla base di dati.

{= al posto della parentesi graffa ci possono 0, o n, o tanti elementi, rappresentazioni. Quindi

{DefiningElements} significa che all’interno ci possono stare elementi di definizione.

ESEMPIO: create schema azienda authorization antonio Antonio è l’amministratore di questo

database.

DEFINIZIONE DELLE TABELLE

Una tabella viene definita specificando una collezione ordinata di attributi e un insieme di

vincoli. Parola chiave: Create table. Poi si scrive il nome che associamo alla tabella seguito

tra parentesi tonde dagli elementi che definiscono la tabella in questa forma:

Create table RelationName

(AttributeName Domain [DefaultValue][constraints]

{, AttributeName Domain [DefaultValue][constraints]} FurtherConstraints)

[constraints]= eventuali vincoli ulteriori sull’attributo.

Poi possiamo aggiungere oltre ai vincoli direttamente assiciati a ciascun attributo, dei

FurtherConstraints = vincoli che non associo necessariamente e direttamente ad un attributo

ma possono essere associati all’intera tabella.

ESEMPIO:

create table Dipartimento (IdDip integer primary key, nome varchar(30) not null, indirizzo

varchar(50))

integer: sto dichiarando che è un integer. Nella maggior parte dei casi scriverò: int. Ho un

vincolo che conosciamo, esplicitamente detto. Scriviamo priamry key.

La virgola è un separatore di statement. Noi possiamo scrivere tutta la dichiarazione su

un’unica riga però li separiamo attraverso una virgola.

Not null: è un vincolo che io esplicito. In generale un attributo che non sia una chiave, può

assumere valore null. In questo caso invece stiamo dicendo che non vogliamo che sia

ammissibile un valore NULL.

Indirizzo invece può assumere valore null.

Io impongo che chi dovesse inserire una tupla con il dipartimento deve esplicitamente

L’intero comando viene rigettato. Cioè

indicare il nome del dipartiemento. E se non lo fa?

una violazione del vincolo corrisponde ad un rigetto dell’intero statement\comando.

Stessa cosa in caso di modifica cioè se noi ce l’abbiamo il nome del dipartimento e proviamo

a scrivere null, ossia lo cancelliamo, il sistema ce lo impedisce, vorrà sempre che esista quel

valore cioè un valore definito.

VINCOLI DI INTEGRITA’

Il database è basato sul concetto di vincolo. Cioè noi non abbiamo i dati messi così a

“tromba” ma abbiamo dei dati che hanno un senso perché sono correlati tra di loro, hanno dei

vincoli.

I vincoli di integrità descrivono le condizioni che ciascuna istanza di una relazione deve

sempre soddisfare.

Noi distinguiamo tra: vincoli che operano all’interno di una

Vincoli itra-relazionali: relazione;

Vincoli inter-relazionali: vincoli che operano su più di una relazione.

VICOLI INTRA-RELAZIONALI

Proprietà che devono essere verificate da ogni istanza del DB.

In ogni istante in un database valgono i Vincoli di domini: cioè se io ho definito un attributo

voto e a questo gli ho dato come tipo smallint, non posso scrivere 27 a lettere perché è una

violazione di dominio :l’intera tupla non verrà inserita. Quando definiamo un dominio,

definiamo anche le operazioni che sono ammesse: in questo caso su 27 come numero

possiamo fare 27+2, su 27 come stringa non possiamo fare un’operazione aritmetica.

Vincoli di dominio: i dati inseriti devono essere sempre del tipo corretto. per l’attributo

Not null: vincolo di non nullità. Indica che il valore null non è ammesso che

quindi va sempre specificato a meno che non sia previsto un default diverso da null.

ESEMPIO:

nome char(30) not null default pippo

Spiegazione: “ nome char(30) non può assumere valore null e al posto di null verrà “pippo”

tutte le volte. Cioè se non inserisco il nome lui mi scrive pippo in automatico.

VINCOLI INTRA-RELAZIONALI (2)

Primary key: specifica la chiave primaria che è necessariamente unica; i valori degli attributi

costituenti non possono, ovviamente, essere null, mai nel tempo.

In generale io posso scrivere o come nell’esempio visto prima : IdDip integer primary key,

in cui ho dichiarato l’attributo, il suo tipo e poi ho detto questa è chiave primaria. Questo si

purchè il vincolo

può sempre fare cioè esplicitare il vincolo direttamente sull’attributo

sussista solo su quell’attributo. Cioè se prendo la tabella ESAME SOSTENUTO questa ha

una chiave primaria che è fatta da 3 attributi : matricola, data e ID-esame.

Matricola integer primary key : è sbagliato in questo contensto. Perché se io poi provo a

scrivere:

data-esame date primary key questo mi va in errore perchè la chiave primaria è unica. Cioè il

sistema leggerà l’ultima cosa come un errore perché penserà che la chiave primaria è

matricola.

Quindi noi abbiamo due possibili rappresentazioni: 1 una se la chiave primaria è fatta da un

attributo:

IdDip integer primary key,

2 una seconda se la chiave primaria è costituita da n attributi.

Da un punto di vista sintattico è un further constrain cioè un vincolo non associato

direttamente ad un attributo.

Primary key(AttributeName{, AttributeName })

ESEMPIO:

Nome varchar(30) , Cognome varchar(30) , Dipart varchar(15),

Stipendio numeric(9) default 0, primary key (Cognome, Nome)

Abbiamo dichiarato nome, cognome, dipartimento, stipendio.

Infondo abbiamo scritto: primary key e la coppia di attributi che in questo caso costituiscono la

chiave primaria. Questa tabella corrisponde a :

IMPIEGATO ( nome, cognome, dipartimento, stipendio)

Se avessi scritto all’inizio nome varchar (30) primary key avrei inteso che la chiave fosse

Nome.

Ha senso che io scriva not null? No perché tanto è una chiave primaria. Se lo scrivo non mi da

errore. Non cambia niente.

VINCOLI INTRA-RELAZIONALI (3)

Il vincolo unique è un vincolo di unicità assimilabile ma non identico al concetto di chiave

candidata.

unique: impone l’unicità degli attributi cui il vincolo è applicato. In pratica definisce una

chiave candidata, ma non scelta come primaria.

Unique(AttributeName {, AttributeName })

ESEMPIO:

nome varchar(30) not null, cognome varchar(30) not null, CF char(13) primary key,

matricola int unique

CF char(13) primary key = Ho scelto il codice fiscale CF come chiave primaria però mi

voglio ricordare che comunque la matricola è una chiave candidata. Cioè se io trovassi due

stupendi con la stessa matricola il problema ha senso. Quindi scrivo:

matricola int unique = Matricola non è la chiave primaria ma resta unica. Impongo comunque

un vincolo di unicità.

Perché ho detto che non è identico al concetto di chiave candidata del modello relazionale?

Perché in generale unique consente i valori nulli. Per cui se voglio che questa dichiarazione

sia proprio identica al concetto di chiave candidata del modello relazionale dovrei scrivere:

matricola int unque not null. Cioè lo dico esplicitamente.

Come in primary key noi possiamo rappresentare come ulteriore vincolo una dichiarazione di

unique su due o più attributi. Ma c’è una differenza con primary key. Nel caso di primary key

è facile capire

se scrivo una seconda volta: primary key che quello è un errore ( come nell’esempio

precedente in ESAME SOSTENUTO). E’ un errore di sintassi.

Nel caso di unique invece queste sono due forme ambedue sintatticamente corrette e con una

semantica diversa.

nome char(30) not null, cognome char(30) not null, unique(nome,cognome)

In questo primo caso sto dicendo che non accetto omonimi ossia gente che abbia insieme lo

stesso nome e cognome. Io posso avere quindi Giovanni Delle Bande Nere e Giovanni

Pascoli. E sono ambedue disgiunti. Vale il vincolo di uncità.

nome char(30) not null unique, cognome char(30) not null unique

Qui sto dicendo che sia unico sia il nome che il cognome. In questo caso Giovanni deve essere

unico. Deve essere unico sia il nome che il cognome.

VINCOLI INTRA-RELAZIONALI (5)

Reazione alla violazione di vincoli intrarelazionali: il sistema che rilevi violazioni di vincoli

reagirà rifiutando il comando di aggiornamento e segnalando la violazione all’utente.

VINCOLI ITER-RELAZIONALI

Stabiliscono i vincoli di integrità referenziale.

Il più classico vincolo inter-relazionale è il vincolo di chiave esterna: foreign key

Vincolo che crea un legame tra i valori di un attributo della tabella corrente R (interna) e i

un attributo di un’altra tabella S (esterna).

valori di

foreign key (AttributeName{,AttributeName}) references

TableName(AttributeName{,AttributeName})

Spiegazione: “Scrivo esplicitamente foreign key: chiave esterna poi l’attributo della tabella

interna su cui sto lavorando in quel momento nella dichiarazione o gli attributi perché la

chiave esterna può essere fatta da più di un attributo insieme, poi references (parola chiave

della tabella esterna e nome dell’attributo che può essere in

ossia si riferisce), nome generale

diverso”

Non c’è nessun controllo sui nomi. Il che vuol dire che se abbiamo due attributi : ATT1 e

ATT2 che insieme formano la chiave esterna e noi diciamo:

foreign key ( ATT1 {,ATT2}) references Table2(ATT1{,ATT2}),

Va bene uguale. L’unico controllo che viene effettuato è sul fatto che il dominio sia

compatibile. Dei nomi non gliene frega niente.

ESEMPIO:

Create table lavora_su (id_imp int,

pnum int,

ore_lav decimal(3,1), primary key (id_imp,pnum)

foreign key(id_imp) references impiegato(imp_num), foreign key(pnum) references

progetto(id_prog)

) “

Spiegazione: Create table lavora su , poi dichiaro id_ impiegato come intero, il numero del

progetto lo dichiaro sempre come intero poi ci sono le ore lavorate che sono decimal fatte da 3

cifre di cui una è dopo la virgola(quindi posso lavorare 99 ore), poi dichiaro la chiave

primaria che è la coppia: id_impiegato, id-progetto. Ma ci dobbiamo ricordare come è fatta la

chiave: quegli attributi ce li siamo portati come chiavi esterne. Per esplicitare questo concetto

scrivo: foreign key(id_imp) si riferisce alla tabella impiegato e mi sto riferendo all’attributo

‘imp_num’. (I nomi come ci ha detto prima, non hanno nessun interesse. Possiamo

rinominarli, possono essere diversi o uguali ma quello che vale è la dichiarazione del

vincolo.)

Analogamente sto dichiarando il vincolo per la tabella progetto: foreign key(pnum)

references progetto(id_prog)”

In generale se gli attributi che costituiscono la chiave esterna sono più di uno, la forma che

dobbiamo usare è necessariamente la forma esplicita con foreign key scritto così:

foreign key (AttributeName{,AttributeName}) references

TableName(AttributeName{,AttributeName})

Se però ho un unico attributo a formare la chiave esterna posso usare anche in questo caso la

forma contratta nel corso della definizione dell’attributo stesso.

Quindi invece di scrivere come nella tabella di prima: id_imp int potrei completare queesta

descrizione scrivendo:

id_imp int references impiegato (imp_num),

cambia? Che non scrivo più foreign key ecc ma l’ho direttamente dichiarato come

Che

vincolo nella dichiarazione dell’attributo.

VINCOLI INTER-RELAZIONALI (3) che succede se un’azione prova a violare quel

Se definisco un vincolo inter-relazionale

vincolo?

seguito di violazioni nella tabella interna R la reazione è quella standard: l’azione viene

A

rifiutata (no action).

Noi però possiamo definire delle politiche di reazione esplicite: SQL consente di definire tipi

diversi di reazione a violazione di vincoli inter-relazionali quali cancellazione di una tupla

riferita o modifica di una primary key riferita. nell’ambito

A seguito di una azione nella tabella S esterna la reazione avviene della tabella R

interna. NOTA: L’idea base è fornire un modo per adeguare una tabella che sia in

associazione con un’altra a seguito di modifiche in quest’ultima.

ESEMPIO: Se consideriamo il nostro piccolo schema di azienda. Cosa accade quando viene

cancellata una tupla in dipartimento, alle tuple di impiegati che si riferiscono ad essa?

Noi definiamo il databese IMPIEGATO in cui abbiamo l’id del dipartimento a cui questo

impiegato appartiene. Ci sarà anche una tabella DIPARTIMENTO. Il vincolo di integrità

referenziale fa si che L’integrità si mantenga. Allora cosa succede se io cancello una tupla

nella tabella DIPARTIMENTO Corrispondente ad un dipartimento in cui ci sono ancora degli

Quest’azione viene rifiutata (no action). Io provo a cancellare ma

impiegati, cosa succede? c’è

siccome quello è un vincolo di integrità referenziale va a vedere se qualche impiegato che

c’è

lavora in quel dipartimento e se non nessuno la tupla di dipartimento è cancellabile e

quindi viene cancellata. Ma basta che ci sia un impiegato e quella richiesta viene respinta.

Noi però possiamo scegliere delle diverse politiche. Le politiche sono:

Aggiornamento on update

Cancellazione on delete

Su un aggiornamento (on update) o cancellazione (on delete) posso specificare le azioni da

intraprendere:

cascade

set null

set default

no action

no action è il corrispondente del defoult. Cioè se non scrivo niente corrisponde al no action.

VINCOLI INTER-RELAZIONALI (6)

Esplicitando le reazioni per la richiesta di cancellazione:

on delete < cascade| set null| set default| no action >

Cascade: tutte le tuple della tabella R interna corrispondenti alla tupla cancellata in S

vengono cancellate; nell’attributo

Set null: il valore cancellato nella tabella S viene sostituito con il valore null della

tabella R;

Set default: il valore cancellato nella tabella S viene sostituito con un valore di default

nell’attributo di R.

No action: la cancellazione viene inibita.

ESEMPIO: Supponiamo di avere IMPIEGATO e DIPARTIMENTO e proviamo a rispondere

alla situazione in cui provo a cancellare una tupla di DIPARTIMENTO presupponendo che ci

sono degli impiegati nella stessa.

IMPIEGATO ( matr, nome, cognome, id_dip)

DIPARTIMENTO ( id_dip, nome_dip, id_dir-dip)

No action: la cancellazione nella tupla di dipartimento viene inibita.

Cascade: la tupla del dipartimento viene cancellata e automaticamente in IMPIEGATO

vengono cancellati tutti gli impiegati che lavoravano in quel dipartimento ossia con lo stesso

riferimento. Qual è la logica? Si mantiene la correttezza del vincolo cioè non ci saranno

“riferimenti penzoloni” e nel

momento in cui non c’è più quel dipartimento non ci sono più gli impiegati. Ha senso?

Concettualmente non ha senso perché il fatto che io sto eliminando un dipartimento non vuol

dire che io licenzi automaticamente tutti gli impiegati. Però sintatticamente è corretto ed in

alcuni casi ha senso. Non esiste una regola generale ma dipende dal contesto.

Set default: se esiste un valore di default, invece del valore di riferimento vero introduco il

valore di default. Quindi perdo l’id del dipartimento che esisteva veramente e ci metto un

riferimento finto, di default.

Passo ora ad on update, al caso di aggiornamento.

On update < no action>: non succede nulla, la modifica viene inibita.

On update< cascade>: immaginiamo che stiamo di nuovo nella tabella dipartimento e non

voglio cancellare la tupla di dipartimento ma voglio modificare il codice assegnato al

dipartimento. Quindi se scrivo on update <cascade> succede che l’operazione di modifica è

ammessa nella tabella di DIPARTIMENTO e a cascata nella tabella IMPIEGATO tutti gli

impiegati che appartengono a quel dipartimento vedranno il loro valore di id-dip modificato.

On update <Set null>: modifico dentro la tabella di DIPARTIMENTO l’identificativo e alla

tabella IMPIEGATO quel valore assume valore null.

On update <set default>: modifico li dentro, la modifica non viene propagata e viene assunto

un valore di default.

VINCOLI INTER-RELAZIONALI (7)

Esplicitando le reazioni per la richiesta di modifica\aggiornamento:

on update < cascade| set null| set default| no action >

Cascade: il nuovo valore dell’attributo della tabella esterna S viene riportato su tutte le

corrispondenti righe della tabella interna

Set null: il valore modificato nella tabella S viene sostituito con il valore null nell’attributo di

R Set default: il valore modificato nella tabella S viene sostituito con un valore di default

nell’attributo di R

No action: la modifica viene inibita

Nella maggior parte dei casi ma non è una regola che vale per sempre si fa: on delete < set

null>

on update < cascade>

però questo vale sempre tenendo presente la sovrapposizione di effetti dei vincoli. Cioè se per

esempio un attributo è chiave esterna da un lato e chiave primaria nell’altro e io cerco di

mettere un set null ad un attributo che è parte di una chiave primaria quello viene inibito

anche se io li avevo scritto on delete <set null>. Questo perché i vincoli vengono sempre

vincoli come sovrapposti.

N.B.: Cosa succede se non specifico “on delete” o “on update”?

E’ come se scrivessi direttamente on delete o on update < no action> cioè Il default è sempre:

nessuna azione ossia viene respinta qualsiasi azione.

Esiste la possibilità di battezzare i vincoli. I vincoli espliciti possono essere battezzabili come

in questo caso:

ESEMPIO:

Spiegazione: Ho scritto: on delete set null. Quindi che significa? Se cancello un capo metto

null. On update cascade: in caso di aggiornamento cambia il capo, cambia il valore di

riferimento. chiave, dip_app è un nome, un’etichetta.

Constraint dip_app: constraint è parola

A che serve battezzare i vincoli? Se li battezzo esplicitamente li posso esplicitamente vedere e

quindi modificare o cancellare. Cioè diventano esplicitamente parte dello schema relazionale.

Quel vincolo: foreign key diventa esplicitamente una parte dello schema e quindi se domani

mattina non mi piace più posso fare: delete constraint dip_app ossia “cancella questo

vincolo”. Se invece l’ho dichiarato direttamente nella tabella devo modificare la tabella

perché quel vincolo non è visibile se non come parte della tabella stessa.

ULTERIORI SPECIFICAZIONI DI VINCOLI

La clausola check: check(Condition) è una clausola di controllo. E’ un vincolo di verifica.

(condition) : espressione boleana

Condition corrisponde alle condizioni che possono essere specificate;

La condizione deve essere sempre verificata per mantenere l’integrità del DB. ESEMPIO:

Create table esami_sup (….

Voto integer,

Check(voto>=18 and voto <=30)

) l’attributo l’ho

In questo esempio ho dichiarato come voto. Una volta che io dichiarato così

non posso scrivere 34. Non verrà accettata la tupla. Può essere tutto perfetto ma se il valore di

check, il valore da verificare non verifica l’espressione boleana, tutta la tupla verrà rifiutata.

nell’esame del linguaggio SQL.

Proseguiamo

Ieri abbiamo definito come si può costruire un database su SQL e come si èossano stabilire i

vincoli sullo stesso.

Ora vediamo come si possono modificare schemi relazionali nel linguaggio SQL.

Modifica degli schemi relazionali

Le modifiche possono consistere in alterazioni e cancellazioni di schemi e domini. Per

modifiche si usa il comando Alter

La sintassi è normalmente fatta in questo modo:

Alter domain

DomainName < set default DefaultValue| drop default |

add constraint ConstraintDefinition |

drop constraint ConstraintName >

Spiegazione: “prima frase: voglio modificare un dominio quindi scrivo: alter domain, poi

specifico il nome del dominio e poi scelgo che cosa voglio modificare. La sbarretta |

all’ “or”.

corrisponde

Cioè posso modificare questo dominio introducendo un valore di default.

Poi c’è drop default. Drop vuol dire far cadere quindi eliminare. Quindi lo metto perché

magari non mi piace più il valore di default che avevo.

Oppure posso aggiungere un vincolo sul dominio: add constraint. Per esempio supponiamo di

aver definito un dominio VOTO e ci ricordiamo che il voto deve essere compreso tra 18 e 30

e quindi andiamo ad aggiungere un constraint e scriviamo:

voto>=18 and voto <=30

Ho quindi aggiunto un altro vincolo.

Drop constraint è il duale: eliminiamo il vincolo.”

In maniera sostanzialmente analoga capisco come cambiare una tabella:

Alter table TableName < alter column ColumnName < set default DefaultValue| drop

default>|

add constraint ConstraintDefinition| drop constraint ConstraintName| add column

ColumnName |

drop column ColumnName >

Spiegazione: “Nella tabella posso cambiare un attributo. Nel gergo di SQL gli attributi si

chiamano column “colonne”. Cosa cambia rispetto al caso precedente? Prima mi riferivo ad

un dominio nominato esplicitamente. Qui invece questo è semplicemente un nome di

attributo.

Cosa posso fare a questo attributo? Posso mettergli un valore di deault quindi scrivo set default

oppure elimino un valore di default e quindi scrivo: drop default.

Poi posso aggiungere un vincolo, elimino un vincolo ma potrei anche voler aggiungere un

attributo. In quest’ultimo caso non vado a modificare l’intera tabella ma scrivo:

Alter table TableName < add column ColumnName

non mi piace più l’attributo scrivo invece: drop column. Per completezza le tuple in SQL

Se

si chiamano row. “

Es. Alter table Impiegato add column telefono char(20)

Alter table Dipartimento drop column città varchar(20)

Modifica degli schemi relazionali (3)

Per cancellazioni si usa il comando Drop.

drop schema|table|domain|view|assertion ItemName [restrict|cascade]

Spiegazione: “ drop schema significa che cancello l’intero database: la tabella, il dominio,

una vista, un’asserzione. Che cosa voglio cancellare? Il nome del database, il nome della

tabella ecc… ?

Le viste in questo momento per semplicità le chiameremo: query materializzate. Una vista la

possiamo vedere come un’interrogazione che viene mantenuta nel tempo e quindi è

query materializzata). Cioè dopo che l’ho fatta i dati me li trattengo,

fisicizzata ( ecc perché

me li memorizzo. Asserzione= è una specie di vincolo di ceck

Es.: Drop table Progetto cascade

Spiegazione: “chiedo di cancellare la tabella”

E’ importate capire il significato di restric e cascade.

Modifica degli schemi relazionali (4)

restrict = è l’opzione di default significa che cancello soltanto oggetti che sono già vuoti che

sono presenti esclusivamente come struttura e non hanno i dati dentro.

vuota, l’ho creata, non ha tuple al proprio interno e posso fare

Cioè io ho una tabella che è

solo il drop della tabella scrivere soltanto solo: drop table STUDENTE e presuppone che

dentro lo studente non abbia tuple ossia non abbia row.

•Per uno schema: richiede che lo schema sia vuoto. ( Cioè ho creato il database ma non ho

creato le tabelle dentro)

•Per una tabella: richiede che sia vuota e non abbia vincoli esterni. Ossia che non ci siano

vincoli esistenti rispetto alla tabella che voglio cancellare.

•Per un dominio che esso non sia presente in alcuna tabella. Cioè se io ho creato un dominio e

lo sto utilizzando cioè ho preso un attributo e l’ho specializzato in un dominio, per eliminare

il dominio devo essere certo che non lo sto utilizzando da qualche parte.

Se non scrivo niente è un defoult e quindi è come se scrivessi restrict.

Modifica degli schemi relazionali (5)

Cascade è un comando un po’ più drastico.

Cascade supera le limitazioni precedenti ed esegue una cancellazione in cascata (va quindi

usato con maggiore giudizio!!), tutti gli oggetti specificati devono essere rimossi.

•Per uno schema effettua una cancellazione completa;

•Per un dominio cancella la definizione, ma gli attributi rimangono definiti secondo il

dominio elementare di origine. Cioè in realtà le tabelle che dovessero utilizzare quel dominio

utilizzeranno semplicemente la definizione primitiva di cui quel dominio era una

specializzazione. Cioè se io prendo un dominio tipo intero e poi creo un dominio che si

chiama voto, faccio il :”drop domain voto” e non è che si rompono le tabelle che contenevano

quel dominio. Semplicemente al posto di quel dominio il sistema assume la definizione

dell’attributo originante quella specializzazione e te lo fa ridiventare intero.

Lo rispiega ancora:

Un dominio quando lo creo è una specializzazione. Ho il dominio intero. Poi creo un dominio

che si chiama votp che è una specializzazione di questo intero. Poi scrivo: drop domain voto

cascade.

Che succede se Voto era per esempio utilizzato in una tabella ESAMI SOSTENUTI? Mi

gioco gli esami sostenuti? No al posto di voto, cioè della specializzazione, ritorno al dominio

originale, elementare, all’intero.

Se voglio eliminare una tabella elimino la tabella e attenzione siccome la logica di un

manterrò l’integrità referenziale, ossia i vincoli di

database relazionale è che comunque

foreign key questo implica che cancello anche tutte le tabelle dove c’era il riferimento.

Quindi bisogna stare attenti.

Quindi Per una tabella tutte le righe vengono perse e se la tabella compariva in qualche

definizione di tabella o vista, anche queste vengono rimosse

•Per una vista elimina tutte le tabelle che compaiono nella definizione. Cioè se elimino la vista

elimino anche le tabelle originali.

Es. DoppioNome char(50) Drop domain DoppioNome cascade

Tutti gli attributi definiti su quel dominio assumeranno direttamente il dominio char(50)

Controllo degli accessi e sicurezza

Un DBMS, un sistema di basi di dati basato su SQL è tipicamente un sistema multiutente. Un

sistema al quale accedono nel tempo tanti utenti molte volte con necessità sostanzialmente

diverse. Per cui diventa fondamentale introdurre meccanismi di protezione dei dati e degli

accessi in modo tale che non è che chiunque possa fare quello che vuole su un database.

un’attività che può essere fatta in tanti modi: in consultazione, di

Accedere ad un database è

tutto o di una parte, in modifica, in modifica dello schema. Sono tante le possibilità che

abbiamo. Quindi si presume che ogni DBMS sia ospitato in un computer. Ossia che ci sia un

sistema operativo che ospita quel sistema di gestione dei dati. Quindi si presume che ogni

utente sia identificato con delle credenziali di accesso: username e password. Ogni utente è

identificato univocamente (account di S.O. o indipendente, proprio del DBMS).

Esiste un amministratore di database che è quello che assegna o riprende le credenziali di

accesso

Il DBA è il gestore del sistema e ha un account privilegiato; egli ha in genere le

responsabilità di concedere/revocare privilegi sia a livello di account che di dati.

In generale sussistono due meccanismi di sicurezza nell’accesso al database denominati:

Discrezionali: concedere e revocare privilegi, tipicamente la capacità di accedere a specifiche

risorse.

Obbligatori: implementare meccanismi multilivello mediante classificazione degli utenti e dei

dati (es.BellLaPadula).

Quelli obbligatori non sono tipicamente parte direttamente dell’SQL. Sono molte volte usati

per la gestione della sicurezza in sistemi documentali. Quindi non solo in DBMS ma anche in

sistemi di gestione dei documenti.

Invece l’SQL include al proprio interno i meccanismi di controllo discrezionali. Quando un

all’utilizzo

amministratore di database crea una risorsa questo ha tutti i privilegi associati del

database stesso. Poi costui può garantire una parte o tutta dei privilegi che egli ha nel

momento in cui crea il database oppure dopo averli assegnati li può revocare.

Vengono forniti due comandi per concedere o revocare diritti, grant e revoke. I privilegi di

creazione e distruzione non sono cedibili.

Da un punto di vista della sintassi, la struttura della sintassi è la seguente:

Grant Privileges on Resource to User_id [with grant option] Revoke Privileges on Resource

from User_id [restrict|cascade]

Spiegazione 1 : “ Fornisci i privilegi sulla risorsa oggetto della cessione del privilegio ad un

utente identificato con il proprio nome utente del sistema operativo di riferimento. Poi c’ è

l’opzione:[with grant option]= vuol dire che ho assegnato all’utente identificato con lo user

id, anche la possibilità di estendere I privilegi ricevuti.

Spiegazione 2: “ Revoca i privilegi sulla risorsa da questo signore con restrict o cascade.

Restrict o cascade si riferiscono al fatto che io abbia assegnato questo privilegio : with grant

option o meno.

ESEMPI: l’amministratore

1° ESEMPIO Spiegazione: io sono del database, so che esiste una tabella

Impiegato e io do la possibilità all’utente giacomo di effettuare degli aggiornamenti, di

aggiornare il contenuto del database.

2° ESEMPIO Spiegazione: qui consento di usare una select. Select è il comando base per fare

le query. Antonio non può modificare il database, può solo interrogarlo. Cioè è una persona

che può consultare la base di dati e neanche tutta. Può consultare: Impiegato. Però ad Antonio

abbiamo dato

la grant option. Vuol dire che Antonio a sua volta può scrivere: Grant select on Impiegato to

Francesco. Perché la grant option prevede che si dia il privilegio ed il privilegio è estendibile

ad altri. 3° ESEMPIO Spiegazione: All privileges funziona come un wind card ossia come un

asterisco, estende tutti i privilegi disponibili. Pietro e paolo possono fare quello che vogliono

sulla tabella però questi privilegi restano a loro, non possono darli.

ESEMPI:

1° ESEMPIO Spiegazione: Giacomo perde i privilegi e li fa perdere a tutti quelli a cui quei

privilegi erano stati da lui assegnati. Se invece uso: restrict succede che se Giacomo aveva

dato un privilegio a qualcun altro il comando risponde che non è valido. Quindi in questo

caso tu devi prima revocare la grant option a Giacomo e poi puoi cancellare il suo privilegio.

Cioè presupponendo che Giacomo avesse distribuito i suoi privilegi e non scrivo nulla

(perché se scrivo restrict è come se scrivessi il nulla) quel comando risponde con non

possibile. Non possibile perché lui aveva già assegnato ad altri quel privilegio e ne segue che

prima di toglierli a lui devi revocarli agli altri.

A che serve tutto questo? Immaginiamo un sistema complesso ad esempio una banca. Una

banca ha un amministratore di sistema poi ha molte volte centinaia di dipendenti ed egli ha la

necessità di gestire non direttamente tutti i privilegi. Deve avere la possibilità di delegare ad

altri parte della sua responsabilità. Ecco perché la grant option. Ma è anche importante che

egli possa gestire quali privilegi dare.

E’

Altro esempio: chiaro che se gestisco un portale in cui faccio vedere delle informazioni, un

utente che si collega li dee vedere delle informazioni, non deve poter modificare le

informazioni. Altri invece devono poterle modificare. Quindi devo poter distinguere. La

logica è esattamente questa.

In questo modo noi abbiamo la possibilità di gestire il controllo degli accessi in maniera

discrezionale. E’ c’è

una tecnica discrezionale cioè un amministratore che comanda e poi lui

più o meno liberamente fornisce o revoca privilegi.

Ce ne sono diversi ma noi ne studiamo soltanto uno: il Bell Lapadula

Il meccanismo di gestione degli accessi Bell-Lapadula è basato sulla classificazione di

soggetti (utenti) ossia quelli che accedono alle risorse ed oggetti (dati\ risorse).

Questo modello non viene usato solo per i database ma viene utilizzato per tutta la gestione

documentale in un’organizzazione strutturata.

Si stabiliscono livelli di sicurezza relativi a soggetti e oggetti, che una volta implementati

vanno comunque rispettati (non sono a discrezione dell’amministratore).

Esistono 4 livelli di sicurezza classici:

Unclassified

Classified

Secret

Top secret

Tale meccanismo di gestione degli accessi si basa su due proprietà:

–Simple property Un soggetto (utente) può leggere cioè consultare oggetti (ossia risorse siano

esse tabelle di un dabase, tuple di un database) purchè abbiano una clearance (livello di

dell’oggetto:

sicurezza) maggiore o uguale di quella

clearance (s) ≥ clearance (o)

N.B.: un soggetto s di tipo unclassified quali oggetti o può leggere? Può leggere solo oggetti

unclassified.

Un soggetto s di tipo top secret quali tipi di documenti può leggere? Un soggetto di tipo top

secret può leggere oggetti secret e a scendere.

Wikileaks è nato con le rivelazioni che Snoden aveva ricevuto da un soldato semplice il quale

era un addetto alla sicurezza della quarta divisione di fanteria. Loro usano un sistema

esattamente come questo. Lui aveva accesso al primo livello solo ai documenti classified.

Se hai un livello di clearance a classified, allora puoi leggere tutto quello che sta da classified

a giù.

Star property Posso accedere in scrittura ad oggetti (documenti) la cui clearance sia maggiore

uguale di quella del soggetto (utente):

clearance (o) ≥ clearance (s)

Questa è la logica violata da Hilary Clinton. Cioè se io sono entrato nel sistema con un livello

di clearance top secret posso scrivere e quindi posso trasmettere e propagare informazioni

solo ad altri livelli top secret. Gestisco questo sistema non solo per le spie ma anche per gli

errori che posso fare per sbaglio. Cioè io posso scrivere: ho scoperto dove sta osama bin

scrivere ad Obama ed invece la sto mandando al portiere di casa. L’idea è

laden pensando di

questa: io scrivo al livello in cui mi trovo dentro. Posso scrivere da secret a top secret. Cioè

posso propagare in su le informazioni ma non in giù.

Esistono dei buchi dei sink espliciti che possono essere utilizzati per far propagare

informazioni. E’ fondamentale il termine esplicito cioè ci deve essere una deliberata richiesta

di passaggio verso il basso.

Il problema della sicurezza informatica è uno dei più ampi problemi che sia oggi. Noi siamo

c’è

pervasi da dispositivi informatici e non ci rendiamo conto che un sacco di gente ed un

sacco di programmi automatici che passano il tempo a trovarci.

Oggi il problema che crescerà sempre di più è quello della gestione degli accessi ai

dispositivi a cui noi non pensiamo che qualcuno acceda.

Più ci connettiamo più siamo facilmente attaccabili. all’altra.

Stuks-net= è stato un worm ossia uno di quei virus che si propagano da una macchina

Questo virus è stato scoperto ma nessuno capiva a che cosa servisse. Una mattina il

programma nucleare iraniano è stato bloccato perché si sono rotte circa 13000 centrifughe

che servivano ad arricchire l’uranio. Queste centrifughe erano della siemens. Questo virus

non faceva un tubo fino a che per sbaglio non veniva caricato su una macchina che gestiva

una centrifuga di quel modello che avevano gli iraniani. A questo punto il virus ti metteva una

videata identica a quello dello stato normale aumentava a dismisura la frequenza dell’agirante

della centrifuga. Il virus si è distribuito su tutte le macchine e prima che gli iraniani capissero

cosa era successo il virus gli aveva rotto metà del parco centrifughe dell’intero sistema di

cosa brutta ma dall’altro ha

creazione di energia nucleare iraniano. Da un lato è stata una

fermato per due anni il programma di arricchimento dell’energia nucleare.

Fine

INTERROGAZIONI

SQL serve a creare schemi, tabelle, database ma non ci possiamo limitare ad immagazzinare i

dati. Studiamo come si fanno le INTERROGAZIONI.

La struttura base di un’interrogazione in SQL ( poi vedremo che si estende) è questa:

Spiegazione (per ora non guardiamo le parentesi): “Select= TROVIAMI l’elenco degli

attributi DALLA tabella DOVE la condizione…

Gergalmente un’espressione SQL è fatta da 3 parti:

Select targhet list

From from clause

Where where clause

Targhet list: list dell’obietto, cioè quello che voglio.

from clause : clausola from =a quali dati devo andare a trovare questa informazione where

clause: clausola dove= quali condizioni voglio

Spiega in ordine le parentesi:

[ [as] Alias] = io posso definire un attributo con il nome con il quale esso è definito nello

schema relazionale( per esempio matricola) oppure posso dargli un alias ossia un nome

durante la quey. E quindi scriverò “as” ed un altro nome.

valido

Select Cognome [as Surname] dell’attributo

Questo vuol dire che nella tabella il nome è Cognome. Immaginiamo che io

voglia fare una query e voglia far vedere nel risultato della query, perché il mio interlocutore

è britannico, che surname corrisponde a cognome. Quindi io troverò surname e poi troverò

l’elenco dei cognomi. Questo è l’uso banale. Quando andremo avanti scopriremo che in

alcuni casi l’alias ci serve per fare le query, ci deve stare per forza.

= questa è un’espressione regolare. Ricordiamo che in una targhet list possiamo scrivere un

nome di attributo ma ne possiamo scrivere anche tanti perché tutto quello che sta tra parentesi

graffe vuol dire 0 oppure n istanze di quello che sta dentro. Vuol dire che almeno un attributo

deve stare nella targhet list ma separati da virgola ne possiamo avere più di uno. Stessa cosa

per la clausola from: possiamo avere come riferimento un’unica tabella a cui per altro

possiamo dare un’alias, ma separati da virgola possiamo avere tante tabelle. E queste tabelle

come sono? Metterle nella clausola from vuol dire che stiamo dicendo per quelle tabelle di

fare il prodotto cartesiano. Quindi se io metto tabA,tabB vuol dire che prenderò i dati da una

tabella momentanea che fa parte della query che è costituita dal prodotto cartesiano di A e B.

La nostra prima interrogazione:

IMPIEGATO è la tabella a cui ci stiamo riferendo.

Spiegazione: Cosa mi restituisce questa tabella? Data la tabella impiegato, mi resitusce

esclusivamente il nome ed il cognome.

Ho fatto un’interrogazione che mi ricorda la proiezione. Se non metto la clausola where è

come se facessi una proiezione.

In questo caso è come se facessi una proiezione di tutti gli attributi. Mi faccio stampare la

tabella. L’asterisco è una wild car (?) cioè corrisponde a dire: tutti gli attributi. Cioè invece di

scrivere tutti gli attributi: nome cognome, stipendio mensile ecc scrivo asterisco che

corrisponde a tutti gli attributi presenti nelle tabelle della from clause.

Possiamo usare gli operatori aritmetici.

Campi di una tabella= attributi Valori numerici = valori delle tuple

Stipendio mensile*12 lo battezzo e infatti scrivo as stipendio_annuale. Quindi la mia tabella

nome, cognome e stipendio annuale. Non c’era l’attributo stipendio annuale e non ci

conterrà:

sta. I dati di partenza quelli restano. Nell’interrogazione io ho battezzato quell’espressione

come se fosse un nome.

Se uno stipendio_mensile fosse NULL?

Lo scopriremo successivamente.

L’SQL all’algebra

assomiglia relazionale tanto è vero che abbiamo detto se io non metto la

condizioni where la targhet list assomiglia ad una selezione. Se io guardo al from quello

corrisponde ad una selezione. Però l’SQL è

corrisponde ad un prodotto cartesiano. Il where

più espressivo dell’algebra relazionale e non è neanche esattamente identico al modello

in vari casi. Peraltro L’SQL, non

relazionale è un linguaggio turing completo. ( Turing è uno

dell’informatica

dei padri intesa come algoritmo. Esiste la medaglia turing. La macchina di

turing resta un benchmark per quanto riguarda la complessità computazionale.) Un linguaggio

l’if possibilità di scegliere. L’SQL è un linguaggio

si dice turing completo se ha cioè se ha la

dichiarativo non turing completo. Per cui poi studieremo le estensioni procedurali cioè la

possibilità di aggiungere all’SQL delle estensioni che lo rendono un linguaggio turing

completo. Cioè che ci mettono la selezione e di conseguenza la possibiltà di fare i cicli: for,

while ecc

L’altra differenza tra SQL e modello relazionale che salta agli occhi è in questo fatto. il

modello relazionale vede una relazione come un insieme quindi tuple sempre distinguibili,

non esiste il duplicato. Nel modello relazionale non li distinguiamo perché la tupla è virtuale.

Nella realtà però i duplicati noi li distinguiamo. In un DBMS una tupla occupa uno spazio

E’

fisico. memorizzata. La memorizzazione corrisponde ad una distinguibilità evidente. Cioè

c’è una cella di memoria, l’altra

un indirizzo esplicito per una tupla e tupla ha un altro

quando io faccio un’interrogazione in generale

indirizzo di memoria. Ne segue che

nell’interrogazione i duplicati mi ritornano perché addirittura da un punto di vista

implementativo mi conviene riportare i duplicati perché cancellare i duplicati è

un’operazione costosa. Perché siccome io scandisco tutte le tuple e verifico la veridicità

rispetto ad un’eventuale condizione della tupla, se la tupla verifica la metto nel risultato della

query. Siccome io le vado a prendere indirizzo per indirizzo, i duplicati staranno. Se voglio

che si comporti esattamente come il modello relazionale sono costretto a specificare

un’opzione distinct di default. Un’interrogazione in SQL introduce l’ALL .

ALL non c’è bisogno che lo mettiamo. ALL è il default e fa si che i duplicati se ci sono

vengano presentati nel risultato di un’interrogazione, se non ci sono non fa niente, se non li

dell’interrogazione.

voglio devo esplicitare distinct. Dinstinct è un post processing

Come si fa l’interrogazione 5? Scrivo distinct altrimenti mi vengono tutti i cognomi.

Se i duplicati fossero rappresentati da più campi (cognome e Stipendio_mensile per esempio)

??

Come si fa? Lo stesso. Se la tupla è identica vengono riportati tutti.

Cominciamo a fare qualcosa che non sia solo il riporto del contenuto ma cominciamo a

mettere le condizioni del where.

Il where corrisponde vagamente ad una selezione e quindi la condizione del where sarà una

formula proposizionale sarà cioè costituita da atomi proposizionali che sono: operando,

operatori di confronto, operando costante (?) eventualmente messi insieme.

Valori numerici o caratteri

Possiamo confrontare in questo modo: espressione, operatore, valore oppure: espressione,

operatore, espressione.

Possiamo verificare che un valore per una tupla sia NULL oppure sia NON NULL. Come si

fa? Domandandosi se l’espressione cioè l’attributo sia NULL. “ Questo is NULL? Se è vero

avrò nel risultato tutte le tuple il cui risultato contiene un NULL. Per il NOT NULL avrò tutte

le tuple in cui non c’è il NULL per quell’espressione.

Ovviamente come per la forma proposizionale io gli atomi li posso mettere insieme a formare

quindi ho : l’AND e L’OR.

la formula proposizonale e

Per quanto riguarda le stringhe di caratteri, cioè le parole esiste oltre che l’operatore di

confronto uguale e diverso esiste il like. Il like è inteso nel senso: assomiglia. Cioè io scrivo

un’spressione LIKE una stringa

scrivo modello. Una stringa modello è costituita da caratteri

e\o % e _ ( underscore).

Abbiamo operatori a valore multiplo e per cui abbiamo la possibilità di definire condizioni di

intervallo. BETWEEN e NOT BETWEEN sono rispettivamente intervallo chiuso ed

intervallo aperto. Con BETWEEN noi definiamo i valori ammessi cioè tutto quello che sta tra

e massimo è incluso. Con NOT BETWEEN noi vogliamo l’intervallo aperto

valore minimo nell’intervallo.

cioè tutto quello che non sta

Un carattere ascii è un numero, un codice.

IN corrisponde all’appartenenza insiemistica. Cioè ho un insieme esplicito di valori. Quindi

io esplicito: valore1, valore 2, valore n e posso domandarmi se il valore di un’espressione per

numerato. Se l’espressione è

una determinata tupla sia o non sia dentro questo insieme

verificata la tupla resterà nel risultato. Se l’espressione non è verificata la tupla non ci sarà

nel risultato.

Tutti gli attributi li prendo da impegato. Se ce ne sono27 di De Paperis me ne verranno 27.

Il mio attributo è uno stipendio mensile ma le operazioni aritmetiche le posso applicare sia

nella targhet list sia nella where clause.

8:Tutti quelli che hanno un valore non risultano.

9: Vogliamo due o di cui la prima come terzo carattere. Il % è una wild card su qualsiasi

sottoinsieme di caratteri. L’_ (underscore) è una wild card per un carattere.

Manca un esempio

2550 euro sono mensili.

E’ corretta l’interrogazione 11?

Questa mi darà tutti quelli del DEE e quelli del DIMEG che guadagno più i 2550 euro

mensili.

(suppongo che senza parentesi sia sbagliata quindi. Ma non l’ha detto)

L’IN è un operatore guarda all’insiemistica, guarda all’esistenza all’interno di un insieme

numerato. Quindi l’insieme che io enumero è costituito da marco, maria, marta e francesco e

troverò tutti gli impiegati che hanno un nome qua dentro.

I valori estremi dell’intervallo vanno nel BETWEEN di regola. (come in matematica).

Per fare il BETWEEN e il NOT BETWEEN posso usare anche gli operatori > e <. Sono

perfettamente equivalenti.

Appello 30-09-2010

Si vuole realizzare un database relativo alle gestione delle liste nozze in una catena di negozi

E’ stata a tal fine costruita, da un inesperto progettista, un’unica tabella

di oggettistica.

descritta dai seguenti attributi:

(PIVA_negozio, ragione_sociale, indirizzo, id_lista_nozze, nome_sposo, cognome_sposo,

nome_sposa, cognome_sposa, data_matrimonio, cod_prodotto, descrizione_prodotto,

prezzo_prodotto, quantita_richiesta, messaggio_augurale)

Considerando che ciascuna lista nozze si riferisce ad un negozio e che gli attributi

quantita_richiesta e messaggio_augurale si riferiscano al prodotto acquistato per una lista

nozze, se ne determini la chiave e si individuino, esplicitandole, le dipendenze funzionali.

Sulla base di queste si proceda alla normalizzazione in 3° forma normale, preservando le dip.

Funzionali.

IPOTESI 1: ogni lista si riferisce ad un negozio.

IPOTESI 2: quantità e messaggio non sono attributi propri del prodotto, ma si riferiscono al

prodotto inserito nella lista.

Come procediamo? Si consiglia di iniziare con l’individuazione delle relazioni più semplici,

più intuitive, che si hanno tra gli attributi.

Ad esempio dobbiamo individuare il concetto di negozio: qual è la chiave di negozio?

Potremmo utilizzare sia PIVA che ragione sociale. È indifferente, scegliamo di utilizzare

PIVA.

Dunque identifichiamo il concetto di negozio, la sua chiave è partita iva, quali sono gli

Sicuramente la ragione sociale e l’indirizzo.

attributi che dipendono da partita iva?

PIVA_negozio ragione_sociale, indirizzo

Prima di andare avanti, facciamo una considerazione: tutti gli attributi sono in forma

normale? No, perché indirizzo deve essere scomposto. Allo stesso modo con cui andiamo a

scomporre nell’esercizio del modello entità-relazione tutto ciò che contiene sottocampi,

facciamo anche qui, riportando i soli attributi atomici. Questo passaggio lo possiamo fare

esplicitamente, oppure se identifichiamo subito gli attributi non atomici, possiamo

direttamente trascrivere i campi in cui l’abbiamo suddiviso.

PIVA_negozio ragione_sociale, via, civico, CAP

Per il concetto di negozio, abbiamo terminato. Ora tutti gli attributi che dipendono da una

chiave, ovvero tutti gli attributi a destra, li possiamo cancellare dalla lista iniziale di attributi.

Quindi possiamo cancellare dalla lista iniziale ragione_sociale e indirizzo.

Il prossimo concetto da analizzare è quello di lista. La sua chiave è sicuramente ID_lista però,

per l’ipotesi 1, ogni lista è riferita al particolare negozio, quindi ogni particolare lista deve

essere

associata alla chiave del negozio a cui si riferisce. Quindi è come se fosse un’entità debole, la

sua chiave sarà: PIVA_negozio, ID_lista.

Quali sono gli attributi della lista? Se conosciamo la lista, conosceremo anche gli attributi

dello sposo, della sposa e la data del matrimonio

PIVA_negozio, ID_lista_nozze nome_sposo, cognome_sposo, nome_sposa,

cognome_sposa, data_matrimonio composto perché nel DDL c’è già un formato predefinito

Data matrimonio non è un attributo

per la data.

Anche qui abbiamo trovato attributi che dipendono dalla chiave, quindi li possiamo

cancellare dalla lista iniziale.

Continuiamo con il concetto di prodotto. Tra gli attributi che dipendono dalla chiave, non

inseriamo la quantità perché, per la seconda ipotesi, essa non è un attributo che si riferisce in

maniera univoca al prodotto.

Cod_prodotto descrizione_prodotto, prezzo_prodotto

Eliminiamo descrizione e prezzo dalla lista iniziale.

A questo punto ci rimane da collegare in qualche modo quantità e messaggio. Abbiamo detto

che in ogni lista è possibile comprare più prodotti, quindi è come se avessimo una relazione

N:N tra queste due entità. Quindi andiamo ad identificare una nuova dipendenza, che avrà

come chiave le chiavi delle due dipendenze precedenti ed ogni volta che inseriamo un

prodotto nella lista, andiamo anche a specificarne la quantità e il messaggio.

Cod_prodotto, PIVA_negozio, ID_lista_nozze quantità_richiesta, messaggio_augurale

Per capire se abbiamo terminato, è necessario che vengano soddisfatte due condizioni:

Tutte le ipotesi devono essere rispettate. E noi abbiamo rispettato tutte le ipotesi della traccia,

non servono ipotesi aggiuntive.

collegato tutte le relazioni tra loro. Si, perché l’ultima relazione mi permette

Dobbiamo aver

di risalire al prodotto, sempre dall’ultima risalgo al concetto di lista, e dalla lista risalgo al

negozio. Quindi tutte le dipendenze sono in qualche modo legate tra loro.

Se ho soddisfatto entrambe le condizioni, posso dire che gli attributi non cancellati dalla lista

iniziale, corrispondono alla chiave della mia tabella, o meglio possiamo dire che

rappresentano gli attributi chiave della tabella normalizzata che, inizialmente, conteneva tutti

gli attributi.

(PIVA_negozio, ragione_sociale, indirizzo, id_lista_nozze, nome_sposo, cognome_sposo,

nome_sposa, cognome_sposa, data_matrimonio, cod_prodotto, descrizione_prodotto,

prezzo_prodotto, quantita_richiesta, messaggio_augurale)

CHIAVE (cod_prodotto, PIVA_negozio, ID_lista_nozze )

Solitamente, la chiave della tabella coincide con la chiave dell’ultima relazione che ho

identificato, però questo non è sempre vero.

Come ulteriore conferma, ora dobbiamo anche classificare le dipendenze che abbiamo

individuato. Quindi:

Fase 1: cerchiamo le dipendenze

Fase 2: classifichiamo le dipendenze. Le dipendenze possono essere piene, parziali o

transitive.

Ho una dipendenza piena quando la chiave finale che abbiamo individuato corrisponde

completamente con la chiave di una delle relazioni. Si ha invece una dipendenza parziale

quando la chiave della relazione è costituita da una sottoinsieme della chiave finale

dell’esercizio.

La dipendenza numero 4 è sicuramente una dipendenza piena rispetto alla chiave. Le

relazioni 1,2 e 3 sono tutte dipendenze parziali.

Perché eseguiamo una classificazione di questo tipo? Ad esempio, nel caso della relazione 4,

possiamo dire che gli attributi quantità e messaggio dipendono completamente dalla chiave.

Quindi nel tabellone iniziale, gli unici due attributi che effettivamente dipendono dalla chiave

sono quantità e messaggio. Quindi sono i due che devono effettivamente stare nella tabella.

Gli altri invece possono essere spostati. Ad esempio le informazioni sulla lista, dipendono

solo da un sottoinsieme della chiave finale, ovvero PIVA_negozio e ID_lista_nozze, stessa

cosa per le informazioni sul negozio, quindi dovrebbero generare delle tabelle differenti.

Fase 3: scrittura del mapping. Dobbiamo generare le tabelle senza scrivere il codice Sql,

quindi come se fosse un mapping relazionale, delle dipendenze che abbiamo trovato.

Molto spesso questo si traduce nel trascrivere sottoforma di tabelle, le dipendenze trovate.

Quindi avremo le seguenti tabelle:

NEGOZIO (PIVA, ragione_sociale, via, civico, CAP)

LISTA (PIVA, ID_lista_nozze, nome_sposo, cognome_sposo, nome_sposa, cognome_sposa,

data_matrimonio)

PRODOTTO (cod, descrizione_prodotto, prezzo_prodotto)

LISTA_PROD (cod_prodotto, PIVA, ID_lista_nozze, quantità_richiesta, messaggio

_augurale)

Per queste tabelle valgono le stesse regole viste per l’esercizio numero 1: se riusciamo a

compattare i nomi è meglio (ad esempio non è necessario scrivere per intero PIVA_negozio

oppure cod_prodotto); più si è snelli nella scrittura, meglio è. Anche per quanto riguarda i

nomi da dare alle tabelle non ci sono vincoli, possiamo scegliere il nome che sembra più

semplice ed intuitivo.

Quindi, ricapitolando, l’esercizio corretto prevede le seguenti operazioni: trovare le

dipendenze, trovarne le chiavi e classificarle, scrivere le tabelle.

Appello 07-03-2011

Si vuole realizzare un database relativo alla programmazione degli interventi effettuati da

E’ stata a tal fine

diversi relatori durante le sessioni di una conferenza. costruita, da un

inesperto progettista, un’unica tabella descritta dai seguenti attributi:

(cf_relatore, nome_relatore, cognome_relatore, cf_capo_sessione, nome_capo_sessione,

cognome_ capo_sessione, nome_sessione, argomento_sessione, aula_sessione, data_sessione,

ora_inizio_sessione, ora_fine_sessione, ora_inizio_intervento, durata_intervento)

Nell'ipotesi che ogni sessione ha un unico capo sessione e che ogni intervento è realizzato da

un singolo relatore, se ne determini la chiave e si individuino, esplicitandole, le dipendenze

funzionali. Sulla base di queste si proceda alla normalizzazione in 3° forma normale,

preservando le dip. Funzionali.

In questo caso lo scenario è: programmazione degli interventi effettuati da diversi relatori

di sessioni di una conferenza. Abbiamo quindi una conferenza suddivisa in

all’interno

sessioni, all’interno di ogni sessione abbiamo degli interventi, per ciascuno dei quali

dobbiamo conoscere il relatore.

La traccia dice che vengono fornite informazioni su: relatori, capo sessione, sessione,

interventi. Se volessimo rappresentare graficamente questa situazione, avremmo:

La conferenza è divisa in sessioni, di cui si conosce ora di inizio e ora di fine, ogni sessione è

divisa in interventi, per ognuno dei quali conosciamo ora di inizio e durata.

La traccia fornisce due semplici ipotesi:

IPOTESI 1: ogni sessione ha un unico capo sessione. IPOTESI 2: ogni intervento è tenuto da

un solo relatore.

Iniziamo andando ad identificare le relazioni abbastanza semplici: sicuramente avremo il

concetto di relatore.

Cf_relatore nome_rel, cogn_rel

Allo stesso modo individuiamo il concetto di capo sessione:

Cf_cs nome_cs, cogn_cs

In questo caso, le ipotesi della traccia non ci bastano per definire tutte le relazioni, infatti ora

dobbiamo identificare il concetto di sessione: abbiamo una chiave univoca che ci permette di

farlo? Probabilmente no, perché le possibilità sono:

utilizzare il nome, ma potrebbero esserci due sessioni con lo stesso nome, quindi questa

soluzione non va bene.

Utilizzare l’argomento, ma anche qui potrei avere due sessioni con lo stesso argomento.

La data e l’ora, invece, potrebbero identificare tranquillamente una sessione. Ma se ci fossero

due sessioni in parallelo? Per risolvere questo problema, esistono più soluzioni, tutte corrette.

Aggiungere l’ipotesi secondo cui non possono esserci sessioni in parallelo sarebbe troppo

semplice. La prima ipotesi ci dice che per ogni sessione possiamo avere un solo capo

sessione, quindi se associamo data e ora ad un particolare capo sessione, sono sicuro che non

avrò mai sessioni in parallelo.

Un’altra soluzione potrebbe essere quella di legare la sessione ad una particolare aula,

dicendo ad esempio: in aula C, in questo giorno e in questa ora c’è questa particolare sessione

e non ce ne sono altre.

Ogni volta che ci troviamo di fronte a più possibili soluzioni, la scelta che effettuiamo

dobbiamo giustificarla. Inseriamo un’ipotesi aggiuntiva:

IPOTESI 3: non ci sono sessioni parallele nella stessa aula, nello stesso giorno, nella stessa

ora.

L’ipotesi deve essere coerente con la scelta della chiave: una volta definita la chiave di

sessione, vediamo quali attributi dipendono da essa. Per l’ipotesi 1, sicuramente il codice

di una sessione conosciamo anche il nome, l’argomento e l’ora

fiscale del capo sessione, poi

di fine sessione.

Aula, data_sessione, oraInizio_sessione cf_cs, nome_sessione, argomento_sessione,

oraFine_sessione

Tutti gli attributi a destra della chiave li possiamo cancellare dalla lista iniziale, anche il

codice fiscale del capo sessione perché, nonostante sia una chiave primaria, è un attributo che

dipende da un’altra informazione, o meglio da un’altra chiave. Quindi non è un attributo che

deve far parte della chiave finale.

Andiamo avanti, definendo il concetto di intervento. Vogliamo che, in una stessa sessione, ci

siano interventi in parallelo? Decidiamo di non averne (ma avremmo potuto scegliere anche

la soluzione contraria):

IPOTESI 4: nessun intervento in parallelo nella stessa sessione.

Se utilizziamo quest’ipotesi, possiamo utilizzare come chiave parziale l’ora di inizio

intervento che, insieme alla chiave della sessione, ci darà la chiave del preciso intervento. Per

l’ipotesi 2, fissato l’intervento, conosciamo anche il relatore e conosciamo anche la durata

Aula, data_sessione, oraInizio_sessione, oraInizio_intervento cf_relatore, durata

A questo punto controlliamo di aver rispettato tutte le ipotesi e di aver collegato tutte le

relazioni. La chiave finale sarà:

CHIAVE (aula, data_sessione, oraInizio_sessione, oraInizio_intervento)

Andiamo avanti classificando le dipendenze.

Relazione 4: dipendenza piena. Relazione 3: dipendenza parziale.

Relazioni 1 e 2: nella chiave finale non c’è il codice fiscale del capo sessione e neanche

quello del relatore, quindi non possono essere né dipendenze piene né parziali.

Ma possiamo affermare che: dalla chiave finale possiamo arrivare al concetto di sessione,

ovvero alla relazione 3, attraverso gli attributi aula, data_sessione e oraInizio_sessione e, in

maniera transitiva, come se ci fosse un salto, possiamo tornare agli attributi di capo sessione

attraverso una chiave esterna (cf_cs). In questo caso si parla di relazioni transitive.

Anche per il relatore vale la stessa cosa; in questo caso il salto è diretto perché, dalla

relazione piena (la numero 4), abbiamo la chiave esterna (cf_relatore), che ci permette di

tornare agli attributi di relatore.

Relazioni 1, 2: transitive.

Ricapitolando: ci sono alcune relazioni le cui chiavi contengono attributi che non sono

presenti nella chiave finale dell’esercizio, quindi queste sembrerebbero scollegate dalla

chiave finale stessa.

Questo però non è vero, perché si può ritornare ai relativi attributi utilizzando delle chiavi

esterne. Quindi gli attributi nome e cognome del capo sessione, sono collegati indirettamente

alla chiave finale attraverso una relazione transitiva. Allo stesso modo, ma con un “salto” in

meno, sono collegati nome e cognome del relatore alla chiave finale.

Nessuna delle tre tipologie di dipendenze è indispensabile in esercizio: potrei ad esempio non

avere dipendenze transitive, parziali o piene.

Il mapping è abbastanza semplice e diretto (esercizio da svolgere autonomamente). Passiamo

ora ad un esercizio in cui manca la dipendenza piena.

Appello 16-02-2012

Si vuole realizzare un database relativo alla gestione delle partecipazioni di attori a differenti

E’ stata a tal fine costruita, da un inesperto progettista, un’unica tabella descritta dai

serie tv.

seguenti attributi:

(cf_attore, nome_attore, cognome_attore, cf_regista, nome_regista, cognome_regista,

nome_serie, genere_serie, numero_stagione, anno_produzione_stagione,

num_totale_episodi, numero_episodio, titolo_episodio, descrizione_episodio)

Nell'ipotesi che ogni stagione di una serie tv è diretta da un solo regista e che ad un episodio

possono partecipare più attori, se ne determini la chiave e si individuino, esplicitandole, le

dipendenze funzionali. Sulla base di queste si proceda alla normalizzazione in 3° forma

normale, preservando le dip. Funzionali.

Quindi stiamo parlando di serie tv, composte da più stagioni, ognuna delle quali si compone

di più episodi. Per ognuno di essi vogliamo la lista di attori che vi partecipano.

Dopo aver individuato le informazioni su attori e registi, abbiamo quelle relative alle serie,

ovvero nome e genere. Ogni serie è composta da una o più stagioni e per ogni stagione si

conoscono

l’anno di produzione e il numero totale di episodi. Per ogni episodio si conosce numero, titolo

e descrizione.

IPOTESI 1: per ogni stagione si conosce il regista IPOTESI 2: in un episodio possono

partecipare più attori

Partiamo da attore: la chiave è il codice fiscale, da cui dipendono nome e cognome; stessa

cosa vale per il regista.

Cf_attore nome_attore, cognome_attore

Cf_regista nome_regista, cognome_regista

Identifichiamo ora la serie: per essa non abbiamo molti attributi, quindi nonostante non sia la

soluzione migliore, utilizziamo il nome come chiave. Qui sarebbe giusto avere un codice che

identifichi la serie, ma non è possibile inserire nell’esercizio attributi al di fuori di quelli

forniti dalla traccia. Fissata una serie con il suo nome, conosciamo il genere.

Nome_serie genere_serie

Identifichiamo ora il concetto di stagione. Per ognuna di esse conosciamo il numero, ma il

numero da solo non basta, infatti è necessario renderla debole rispetto alla serie. Quindi

avremo numero della stagione più il nome della serie come chiave di stagione. Associamo ad

una stagione l’anno di produzione, il numero totale di episodi e, per l’ipotesi 1, l’indicazione

del regista ed eliminiamo tutti gli attributi a destra della chiave.

Nome_serie, numero_stagione anno_produzione, numero_totale_episodi, cf_regista

Sempre allo stesso modo, partendo dal concetto di stagione, identifichiamo il concetto di

episodio: il numero episodio non ci basta, quindi lo rendiamo debole rispetto alla stagione

Nome_serie, numero_stagione, numero_episodio titolo_episodio, descrizione_episodio

Abbiamo collegato tutte le relazioni, ma non abbiamo rispettato tutte le ipotesi, in particolare

non abbiamo rispettato la seconda ipotesi perché non abbiamo detto che in un episodio ci

possono essere più attori o meglio, abbiamo creato il sistema affinché questo sia possibile,

però non abbiamo legato il concetto di attore al concetto di episodio. Non c’è nulla che ci

permette di conoscere quali sono gli attori che partecipano ad un episodio, una volta noto

l’episodio. l’episodio e l’attore, ma poi

Servirebbe una relazione N:N tra episodio ed attore. Indichiamo

ci chiediamo: cosa dipende da questa chiave?

Nome_serie, numero_stagione, numero_episodio, cf_attore ?

Ci sono attributi che hanno una dipendenza rispetto a questa chiave? In questo caso no,

quindi decade la definizione di dipendenza. Di conseguenza, questa non è una dipendenza e

non la dobbiamo scrivere (la cancelliamo).

Consideriamo ora la chiave finale dell’esercizio:

CHIAVE (nome_serie, numero_stagione, numero_episodio, cf_attore)

Ci accorgiamo che non esiste una dipendenza con la stessa chiave, non abbiamo cioè

dipendenze piene.

Classifichiamo le dipendenze:

relazioni 1,3,4,5 : dipendenze parziali,

relazione 2: dipendenza transitiva, perché ha una chiave che ritroviamo tra gli attributi

dipendenti dalla chiave di stagione.

Scriviamo ora le tabelle (mapping): ATTORE (cf, nome, cognome) REGISTA (cf, nome,

cognome) SERIE (nome, genere)

STAGIONE (nome_serie, num, anno, num_tot_episodi, cf_regista) EPISODIO (nome_serie,

num_stagione, num, titolo, descrizione)

Resta ancora in bilico il collegamento tra attore ed episodio. Abbiamo già detto che questa

non rappresenta una dipendenza, ma nulla ci vieta di farla diventare una tabella derivante

dalla N:N tra episodio e attore e di farne quindi il mapping. Quindi concettualmente non è

una dipendenza, ma è a tutti gli effetti una relazione, quindi va mappata opportunamente.

EPISODIO_ATTORE (cf_attore, nome_serie, num_stagione, num_episodio)

Molto spesso l’assenza di una dipendenza piena è legata alla presenza di una relazione N:N

senza attributi sulla relazione. Questa non è una regola, ma una consuetudine negli esercizi.

Se notiamo l’assenza di una dipendenza piena, ci conviene verificare la probabile presenza di

una relazione N:N senza attributi.

Appello 21-07-2010

Si vuole realizzare un database relativo alla gestione delle prenotazioni delle visite mediche

E’ stata a tal fine costruita, da un inesperto progettista,

in uno studio associato di medici.

un’unica tabella descritta dai seguenti attributi:

(CF_medico, nome_medico, cognome_medico, tel_medico, CF_paziente, nome_paziente,

cognome_paziente, data_nascita_paziente, data_prenotazione, ora_prenotazione ,

num_accettazione_prenotazione, data_visita, ora_visita, costo_visita, diagnosi_visita)

Nell’ipotesi che si possa prenotare una visita per volta se ne determini la chiave e si

individuino, esplicitandole, le dipendenze funzionali. Sulla base di queste si proceda alla

normalizzazione in 3° forma normale, preservando le dip. Funzionali.

Abbiamo informazioni sui medici, sui pazienti, sulle prenotazioni. Dobbiamo fare attenzione

a distinguere il concetto di visita prenotata da quello di visita effettuata. Quindi abbiamo

attributi legati alla prenotazione ed altri legati alla visita vera e propria. Il numero di

accettazione viene dato nel momento in cui la visita viene prenotata.

IPOTESI 1: un paziente può prenotare una visita per volta.

Quest’ipotesi è fatta in modo tale che, quando si fa una prenotazione, si può fare una visita

soltanto, non si possono fare prenotazioni che raggruppino più visite differenti.

Prime due relazioni: medico e paziente.

Cf_medico nome_medico, cognome_medico, telefono_medico

Cf_paziente nome_paziente, cognome_paziente, data_nascita

I problemi nascono quando sorge la necessità di decidere se modellare prima il concetto di

prenotazione o prima quello di visita. Verrebbe naturale modellare prima il concetto di

prenotazione, semplicemente perché precede la visita. Ma dobbiamo fare attenzione, perché

non sempre l’ordine con cui vengono forniti gli attributi nella traccia, corrisponde all’ordine

con cui analizzare le relazioni. Infatti, se manca il concetto di visita, mancherà

automaticamente anche quello di prenotazione. Quindi cominciamo ad analizzare la visita:

la chiave di visita potrebbe essere data e ora, ma siamo in uno studio con tanti medici, quindi

probabilmente in quella data e a quell’ora potrebbero esserci più visite contemporaneamente.

Una possibile ipotesi sarebbe quella di collegare la visita al paziente, ma il paziente

sicuramente ci servirà successivamente per collegarlo alla prenotazione, quindi utilizzarlo

adesso complicherebbe la scelta successiva. Quindi scegliamo di collegare la visita al medico.

alla data e all’ora.

Analizziamo il concetto di prenotazione: è sicuramente collegata all’utente,

Anche il numero di accettazione potremmo considerarlo univoco, ma è quasi impossibile che

uno studio medico tenga traccia di questi ID per anni. Oppure potremmo considerarlo un ID

che si resetta ogni giorno, quindi in questo caso la chiave è : numero di accettazione e data

prenotazione. Sono tutte scelte valide, a patto che ci sia un’ipotesi corrispondente. Noi

scegliamo la seguente ipotesi:

IPOTESI 2: ogni prenotazione è legata a paziente, data e ora (anche se questa ipotesi ricade

in qualche modo nella prima).

Cosa conosciamo oltre alla data, all’ora e al paziente? Per il momento, soltanto il numero di

accettazione. Chi ha ragionato in questo modo, è stato portato ad inserire già qui il codice

fiscale del medico. Questa cosa ha però reso difficile identificare il concetto di visita.

Data_prenotazione, ora_prenotazione, cf_paziente numero_accettazione

Continuiamo con il concetto di visita. Abbiamo già detto che la colleghiamo al concetto di

medico. Fissata la visita, conosco costo e diagnosi.

Cf_medico, data_visita, ora_visita costo, diagnosi

Ora dobbiamo collegare la visita alla prenotazione. La traccia dice che bisogna tener traccia

delle prenotazioni. Per come abbiamo modellato il sistema, per avere informazioni sulle

prenotazioni,

dobbiamo conoscere il concetto di visita. Invece per rispettare le richieste della

traccia, che chiede di modellare un sistema in grado di gestire le prenotazioni delle

visite mediche, dobbiamo fare in modo che il concetto di prenotazione contenga il

riferimento alla visita, in modo tale che di base siano note le informazioni sulle

prenotazioni e, solo se necessario o sotto autorizzazione, è possibile accedere ai

dati medici del paziente. Quindi aggiorniamo la relazione 3, aggiungendo la chiave

di visita.

3. Data_prenot, ora_prenot, cf_paziente num_accettazione, cf_medico,

data_visita, ora_visita

CHIAVE (cf_paziente, data_prenotazione, ora_prenotazione)

Classifichiamo le dipendenze:

1,4 : transitive

3: piena

2: parziale

Oltre agli utenti, si conoscono anche le info sulle auto utilizzabili per ciascun

viaggio.

IMPO: l’utente proprietario ha creato un po’ di indecisioni. Perché? c’è chi ha

pensato che l’utente proprietario fosse un caso particolare del concetto utente.

si differenzia in qualche modo dall’utente utilizzatore ( utente che

Il proprietario

partecipa ad un viaggio).

Questa cosa logicamente non è neanche sbagliata. Qual è il problema? La traccia

vi dà degli attributi che differenziano questi due utenti? No. Dice soltanto che

l’utente può essere entrambe le cose.

Quindi se volete fare questa suddivisione ok, però allo stesso tempo dovreste

subito accorgervi che questa cosa non serve. O quantomeno anche riportare le

figlie del padre, dato che le figlie non avrebbero attributi, alla fine è una cosa quasi

scontata.

Come si può gestire in maniera più semplice questa situazione: abbiamo un’auto,

ho un utente proprietario e questa cosa si trasforma in una semplice relazione.

Possiamo dire che un utente potrebbe possedere anche più auto.

Dall’altro lato della relazione, un’auto quanti proprietari potrebbe avere?

Teoricamente N.

In questo caso la traccia semplifica perché specifica che ogni auto ha uno ed un

solo proprietario. È la traccia che già vi vincola questa possibilità e dice che su

questo sito per ogni auto ricevono solo un utente di riferimentoDalla traccia..

Attraverso il numero più alto è possibile fare dei viaggi. Cosa caratterizza un

viaggio?

data di partenza

ora di partenza

auto utilizzata , un’auto può essere utilizzata per più viaggi. Sicuramente AUTO→

N viaggi

Un viaggio viene fatto con un’auto , quindi per ogni viaggio si conosce l’auto di

riferimento. Qual è la chiave di AUTO? la targa .

Passiamo a VIAGGIO. Quale potrebbe essere la chiave di viaggio? Sicuramente

data e ora.

Se lasciamo solo data e ora, non potremmo avere 2 viaggi in contemporanea che

iniziano nella stessa data e alla stessa ora. Probabilmente questa è un’entità debole

rispetto ad auto, perché con quell’auto a quella data , in quell’ora svolgono insieme

un solo viaggio.

Cos’altro sappiamo del VIAGGIO?

il num max di persone che possono partecipare

il costo del viaggio

il comune di partenza e di arrivo Altro PROBLEMA:

il comune di partenza e il comune di arrivo, sono 2 entità diverse? sono 2 elementi

diversi? In realtà no, perché il comune è un qualcosa di indipendente dal viaggio

ed è sempre un’unica entità.

Per ogni comune conosco:

codice di avviamento postale (CAP) , questo è un elemento univoco e lo posso

considerare come chiave.

nome

Dato che ho un unico comune, come distinguo che sia di arrivo o di partenza: devo

creare 2 relazioni distinte.

Tra VIAGGIO e COMUNE ho 2 relazioni , posso dire che il mio viaggio parte da

un solo comune. Quanti viaggi partono da questo comune? Tanti, potenzialmente

N.B.

Stessa cosa anche per il ritorno. Il mio viaggio arriva sempre in un solo comune,

ma quel comune può essere il luogo di arrivo di più viaggi. Questa è la soluzione

perfetta.

Però, come potrei anche risolvere questa doppia relazione: probabilmente si

potrebbe creare anche una relazione n ad n.

Qui c’è un doppio problema:

il viaggio non è detto che debba toccare N comuni, perché potrebbe essere un

viaggio diretto che parte da un comune e arriva in un altro .

Per come è fatta la traccia, voglio sapere esattamente la partenza ed esattamente

l’arrivo. Dovrei avere una relazione con cardinalità 2. Quindi è un vincolo che

devo aggiungere al sistema.

Come faccio a capire da questa relazione qual è il comune di partenza e qual è il

comune di arrivo. Dovrei anche inserire una relazione tipo in cui vado a specificare

o di partenza. C’è un modo più semplice per semplificare

se il comune è di arrivo

queste caratteristiche

Altra caratteristica di viaggio è la lista degli utenti passeggeri. Questo è un altro

PROBLEMA.

Se dovessimo rispettare al 100% la teoria, dovremmo fare una considerazione di

questo tipo: andare su viaggio e prendere un attributo multi valore passeggero.

Questo al pari di una gerarchia, al pari dell’esistenza di una entità figlia, non può

restare così quando vado a fare il mapping di questo modello e di tale relazione.

Quindi , o in questo momento o in una fase successiva, devo risolvere l’attributo

multivalore. Il mio CONSIGLIO è risolverlo già in questo momento.

Quindi che si fa? UTENTE , già esiste, e mi limito ad inserire una nuova

Serve creare un’entità

relazione che andrebbe a creare la lista dei passeggeri e dico che l’utente partecipa

in qualche modo a tanti viaggi e in un viaggio possono partecipare più persone.

Ma non posso ricavare la partecipazione ad un viaggio attraverso questa prima

relazione? La relazione possiede? Si, lo potete fare. Però con questa relazione voi

state soltanto recuperando il proprietario dell’auto, quindi teoricamente il

guidatore. Ma se ci sono altri utenti che partecipano , senza questa seconda

relazione, lo potete fare.

Quindi è giusto che ci sia questa parte chiusa del sistema perché queste 2 relazioni

hanno un significato differente.

Altra considerazione, torniamo un attimo indietro .

Se la metti tra UTENTE e AUTO, quindi partecipa, stai dicendo che il generico

utente potrebbe partecipare a più viaggi, però sempre con la stessa auto. O anche

con altre auto, però non c’è più un legame con il concetto VIAGGIO ma al

concetto di AUTO. Ultima considerazione:

perché non era una soluzione ideale distinguere proprietario e passeggero? perché

in quel caso, se andavamo ad unire partecipa all’utente passeggero stavamo

escludendo da quella parte tutti i proprietari.

Quindi era un’altra situazione non chiara che potevamo generare risolvendo in quel

modo il sistema.

Cos’altro dice la traccia: ogni volta che c’è una relazione → UTENTE partecipa ad

un VIAGGIO.

L’UTENTE esprime anche un parere attraverso un voto e una recensione. Questi

sono attributi della relazione. La traccia vi chiede anche un controllo per cui i

partecipanti devono essere in numero al num max indicato per ciascun viaggio .

I VIAGGI possono dividersi in: 1) Viaggi diretti ( parte da.., arriva a ..) :

conoscono direttamente la lunghezza , quindi conosco i km complessivi.

2) Viaggi con soste : conosco la lista dei comuni in cui la macchina si fermava.

LISTA COMUNI→ teoricamente è un attributo multivalore, per trasformarlo

dovrei inserire l’entità

COMUNE.

COMUNE è già presente, quindi faccio una semplice relazione.

Cosa possiamo dire? un COMUNE è un luogo di sosta e sosta per tanti viaggi. Un

VIAGGIO può prevedere tante soste, può prevedere tanti comuni in cui l’auto si è

fermata. Per ogni sosta conosco

anche la durata. Ultima cosa: dobbiamo risolvere la GERARCHIA.

In questo caso, le figlie hanno pochi attributi, hanno semplicemente un attributo

km_tot . Quindi, lasciarle come entità deboli non è la soluzione migliore.

Quindi portiamo le figlie nel padre: - il viaggio avrà:attributo Km_TOT

attributo TIPO

relazione con COMUNE, in quanto si avrà un viaggio con tante soste Dobbiamo

inserire alcune BUSINESS RULES:

NO

BR1) PER OGNI VIAGGIO PASSEGGERI AL NUMERO INDICATO

BR2) TIPO VIAGGIO IN (‘diretto’ , ‘con sosta’) Le altre volte abbiamo espresso

questa BR come: TIPO VIAGGIO = le due alternative.

Vediamo qui la prima versione in SQL, quando devo elencare delle possibili

soluzioni, la sintassi è : nome attributo IN( lista di valori).

Dato che qui i valori sono delle stringhe, ogni valore deve essere inserito tra

singoli apici e tutti i valori separati da virgole.

I Km_TOT vale soltanto nel caso di VIAGGIO DIRETTO.

BR3) ( TIPO VIAGGIO = ‘DIRETTO’ AND Km TOT IS NOT NULL) OR

( TIPO VIAGGIO = ‘CON SOSTE’ AND Km TOT IS NULL)

Come si scrive in SQL? ogni espressione è separata dagli operatori logici (OR).

Diciamo che la relazione è sosta (R ).

1

R vale soltanto se il viaggio è di tipo con sosta. Per ora, anche questa la lasciamo

1

in italiano: BR4) LA RELAZIONE TRA VIAGGIO E COMUNE ESISTE SOLO

SE TIPO VIAGGIO = ‘CON

SOSTA’ BUSINESS RULES:

NO

BR1) PER OGNI VIAGGIO PASSEGGERI AL NUMERO INDICATO

BR2) TIPO VIAGGIO IN (‘diretto’ , ‘con sosta’)

BR3) ( TIPO VIAGGIO = ‘DIRETTO’ AND Km TOT IS NOT NULL) OR (

TIPO VIAGGIO = ‘CON SOSTE’ AND Km TOT IS NULL)

BR4) LA RELAZIONE TRA VIAGGIO E COMUNE ESISTE SOLO SE TIPO

VIAGGIO =

‘CON SOSTA’

Regole di questo tipo cambiano in base alla cardinalità che esiste su una relazione

valida su una

tipologia di figlia. Vedremo , dopo aver scritto le tabelle, come si risolve.

Abbiamo finito con il

modello, facciamo il

MAPPING.

Dobbiamo gestire la

doppia relazione

PARTENZA- ARRIVO.

Questa è una doppia

relazione che si riferisce

sempre alla stessa entità

comune.

Quindi per ogni

relazione, dobbiamo

portare verso viaggio la

chiave primaria di

comune. Anche lo stesso

attributo, anche se è

uguale, và portato 2

volte.

Le altre relazioni di

viaggio sono n ad n,

VARCHAR (50)) quindi richiederanno

Come al solito: delle nuove tabelle.

dell’attributo

nome PUNTO b) SQL-DDL

tipo CREATE TABLE utente

eventualivincoli,eventualiCHEK In questo caso il ( CF CHAR (16)

CHECK è facile: PRIMARY

KEY, nome

VARCHAR (50),

cognome VARCHAR

(50) , mail

chiave primaria, composta da un unico elemento, possiamo usare la

- CF→

sintassi compatta

CF CHAR (16) PRIMARY KEY

parte SQL in MAIUSCOLO e la parte personalizzata in minuscolo . Potete

scrivere come volete, anche tutto grande o tutto piccolo.

DUBBIO: come faccio a dimensionare gli attributi (nome,cognome) dove non è

specificata la lunghezza. Mettete un numero ragionevole (50,100), un

numero approssimativo. CREATE TABLE Comune ( CAP CHAR (5) PRIMARY

KEY, nome VARCHAR (100))

CAP → normalmente è un numero da 5 cifre.

Ma anche se è un numero, è qualcosa dove non faremo mai operazioni numeriche.

Qui siamo liberi di usare o un formato numerico o uno classico tipo CHAR di CIP.

Le 2 scelte hanno dei pro e dei contro:

se utilizziamo VARCHAR , il sistema dovrebbe avere un controllo per cui voi non

potete scrivere dei caratteri

se lo fate come numero, ci dovrebbe essere un controllo per cui sempre e

comunque 5 cifre dovranno essere utilizzate.

CREATE TABLE Auto ( Targa CHAR (7) PRIMARY

KEY, modello VARCHAR (50),

cilindrata SMALL INT, km percorsi INTEGER,

CF proprietario CHAR (16) REFERENCES utente (CF))

Targa→ codice 7 caratteri (targhe italiane)

km percorsi → se consideriamo questo valore senza virgole, possiamo utilizzare

un INTEGER CF proprietario → CHIAVE ESTERNA

ci dobbiamo ricordare 2 cose:

utilizzare lo stesso tipo della chiave corrispondente ( CHAR (16)). Avete libertà

nella scelta della lunghezza, però ricordatevi che la lunghezza deve essere uguale

nel caso di chiave primaria e chiave esterna.

dobbiamo inserire il vincolo. Abbiamo un unico attributo e dobbiamo utilizzare la

sintassi compatta: REFERENCES utente (CF)

dove:

utente è il nome della tabella

CF è il nome dell’attributo nella tabella di partenza

CREATE TABLE viaggio (

targa CHAR (7) REFERENCES Auto (Targa), data DATE,

ora TIME,

PRIMARY KEY (

Targa,data,ora) , num max di persone SMALL INT, costo FLOAT,

tipo VARCHAR (9) CHECK (Tipo IN(‘diretto’, ‘con sosta’)),

Km tot SMALL INT CHECK (Tipo = ‘diretto’ AND Km tot IS NOT NULL) OR

(tipo= ‘con sosta’ AND Km tot IS NULL),

CAP_part CHAR(5) REFERENCES Comune (CAP) ,

CAP_arrivo CHAR(5) REFERENCES Comune (CAP))

Poiché il viaggio è debole , targa è una chiave esterna.

Poiché la PRIMARY KEY è formata da 3 elementi, non possiamo utilizzare la

sintassi compatta. In generale, la chiave primaria e la chiave esterna vanno messe

alla fine della tabella.

Per il costo usiamo un numero con la virgola, quindi FLOAT.

tipo VARCHAR (9) →il varchar deve essere pari alla lunghezza max delle

alternative (es.SOSTA → 9 caratteri) . all’interno

I CHECK sono tanto più semplici, quanto il controllo viene limitato

della stessa tabella.

In questo caso devo solo controllare il valore di un attributo che è presente nella

tabella in cui è presente il CHECK. Quindi devo soltanto valutare gli operatori di

confronto ( IN,=). Però potrebbero esserci dei CHECK in cui devo combinare

questi valori presenti all’esterno.

Quando mi serve fare questa cosa, allora devo usare le QUERY.

Dove va messo questo CHECK? Cosa dobbiamo fare? dobbiamo confrontare il num max di

persone ( un attributo di viaggio) con il num complessivo di persone che partecipano al

viaggio. Ovvero una info ( un num) che devo estrarre dalla tabella partecipa. Quindi

dall’altra.

abbiamo 2 tabelle , posso decidere se metterlo da una parte o

In un viaggio abbiamo detto che ci sono 5 persone , se ne metto 6 il viaggio cambia per me?

no, tutte le altre info del viaggio non cambiano. Il viaggio esiste e mi dovrà arrivare un

messaggio di errore. Questo errore a cosa è dovuto?

È dovuto ad un ulteriore collegamento PASSEGGERO-VIAGGIO che in realtà non deve

essere abilitato. Quindi quel vincolo serve ad evitare che nella tabella PARTECIPA ci siano

più relazioni del previsto. I CHECK vanno inseriti nella tabella dove devo impedire

l’inserimento dei dati.

Per ogni viaggio devo contare quante persone si sono già prenotate. Quindi quante persone

già ci sono associate a quel viaggio.

utilizzo l’operatore COUNT → devo contare nella tabella partecipa. Come fisso un

viaggio? lo fisso a partire dalla sua chiave primaria. Presa una particolare chiave

primaria, conto i partecipanti.

Come si realizza questa operazione?

rinomino la tabella partecipa attraverso l’operazione ALIAS (AS)

fisso una particolare chiave primaria ( composta da 3 elementi. Quindi devo fissare tutti e 3

gli elementi di questa chiave)

PARTECIPA

CF P TARGA DATA ORA

(partecipanti)

1 AB......DE 19/11/2016 15:00

2 AB....DE 19/11/2016 15:00

3

Suppongo di creare un viaggio in cui ci sono massimo 2 partecipanti.

sistema c’è il tentativo di creazione di

La terza volta che il passeggero tenta ad inserirsi, nel

una 3° riga con la stessa chiave di viaggio. Ma grazie a questo controllo, che si fa? fissa un

viaggio, quindi prende questi 3 elementi che in questa QUERY rappresentano i valori che

tento di inserire (targa, data, ora).

no max? c’è ancora spazio nella macchina per inserire

In questo caso, 2 è minore del

un’altra persona? No. Errore. Questa operazione non viene consentita. Questa prima parte

del CHECK effettua in conteggio delle persone attualmente presenti nel database.

Quindi

prima parte della QUERY:

controllo le relazioni già presenti in corrispondenza di una particolare chiave. Questo

numero deve essere minore del num max.

Qui abbiamo un secondo problema, perché? In questo caso, poiché c’è COUNT abbiamo un

risultato di tutto questo blocco che è un num e va confrontato con il num max.

Il num max non lo abbiamo in partenza, ma lo abbiamo in viaggio. Quindi ci serve un

secondo pezzo per recuperare da viaggio questo attributo. Serve per contare il num di

duple.

È vero che il num di persone che partecipa num max,ma nel database questo si trasforma in

un ragionamento di questo tipo: continua ad inserire persone finchè c’è spazio.

seconda parte:

recupero da viaggio il num max. In maniera identica vado a selezionare solo il viaggio di

interesse.

TRACCIA DEL 24-02-2015

Relativa ai comprensori sciistici italiani.

Ogni comprensorio è caratterizzato da un codice univoco di 8 caratteri e dal proprio nome. È

composto da una serie di impianti.

Leggendo ci rendiamo conto che abbiamo una classificazione identità dei compressori e una

classificazione identità degli impianti.

Per ciascun impianto si conosce un numero progressivo univoco solo rispetto al

comprensorio di riferimento. Nome, lunghezza, ora di apertura e di chiusura.

Gli impianti si dividono in:

piste da sci, di cui si conosce il dislivello e la difficoltà

le seggiovie, di cui si conosce il costo del biglietto, lo stato e la lista di piste raggiungibili

tramite la seggiovia.

Questo caratterizza gli impianti in 2 categorie, che dovremo indicare come gerarchia, cioè:

pista è figlio di impianto

seggiovia è figlio di impianto

Però dovremmo occuparci , solo successivamente, di stabilire che cosa fare con quella

gerarchia.

N.B. : La soluzione che mettete direttamente le figlie deboli dal padre in automatico NON

VA BENE , ci si arriva a quella soluzione perché fa parte di quella progettazione.

Ci aspettiamo che in questo modello ci sia:

una entità che racchiude il comprensorio ( al singolare perché ogni stanza sarà un

comprensorio)

avremo una entità che riassume gli impianti e sappiamo che questa entità si separerà in 2

possibili figli (una pista o una seggiovia)

Cosa stiamo dicendo con questo tipo di modellazione?

Stiamo dicendo che la pista è un impianto e che la seggiovia è un impianto.

Implicitamente, stiamo dicendo che una pista non è una seggiovia e che una seggiovia non è

una pista .

Dopo di che, stiamo dicendo che vengono organizzati dei corsi di sci , caratterizzati da una

serie di attributi. Quindi sicuramente avremo a che fare con un corso, perché una serie di

attributi non può che essere riassunta in identità.

Dalla traccia....

– una pista da sci utilizzata e da un istruttore di riferimento, di cui si conoscono i dati

avremo un’identità ISTRUTTORE.

anagrafici e gli anni di esperienza. Quindi

N.B.: Il senso di fare i rettangoli è lo stesso di quando sottolineate sulla traccia.

Sto simulando questo processo.

N.B.: Si tratta di un esercizio progressivo, quindi se mi dovessi accorgere che qualcosa è

sbagliata , possiamo cancellarlo e riprogettare.

L’ISTRUTTORE va tracciato come identità perché ha una serie di informazioni.

Viene detto che per ogni corso occorre tenere traccia degli iscritti con i relativi dati

anagrafici , iscrizione al corso che dovrà essere precedente alla data di inizio corso.

Si intuisce che anche gli iscritti non sono ad attributo secco, ma sono una serie di

informazioni che andrà catalogata in un’identità.

Quindi avremo un rettangolo o una sottolineatura relativa all’iscritto.

l’esercizio.

E qua finisce

Adesso dobbiamo risalire nella lettura e procedere.

Ogni comprensorio è caratterizzato da un codice univoco di 8 caratteri. Ci viene detto che

questo codice è UNIVOCO, cioè possiamo utilizzarlo come chiave.

Approfittiamo per scrivere le Business Rule ( BR) man mano che le troviamo , le scriviamo.

N.B.: BR ( spiegazione della prof, punto per punto)

1_ Codice comprensorio 8 caratteri

è composto da 1 nome e da una serie di impianti. pensate sono un’identità e sono

è un verbo e spesso quando in una frase due cose che

collegate da una qualche attività suggerita da un verbo , si traccia una relazione.

È necessario rappresentare in questo modello che il comprensorio è fatto di impianti.

Quindi traccio una relazione tra il comprensorio e gli N impianti.

Da quel lato sono sicura che sono N , dall’altro lato (in teoria) ci sarebbe il dubbio perché

non so se l’impianto appartiene ad un unico comprensorio oppure no.

Non azzardo ipotesi e continuo a leggere dalla traccia..

Per ciascun impianto si conosce un numero progressivo (univoco solo rispetto al

comprensorio di riferimento). Quindi ogni impianto è associato unicamente ad un

comprensorio.

Questo nel linguaggio del modello di correlazione significa che l’impianto è DEBOLE dal

comprensorio e che dovrò determinare una chiave parziale che distingua gli N impianti, ma

dovrò comunque fare riferimento al codice del comprensorio.

La chiave parziale è il numero progressivo. Per ciascun impianto si conosce:

num progressivo

lunghezza

ora di apertura ( apertura)

-ora di chiusura ( chiusura)

Perché ha brevi nomi negli attributi? perché poi lo devo scrivere nel punto b

La traccia dice che gli impianti sono divisi in:

piste da sci, di cui si conosce il dislivello e delle piste dovrò sapere la difficoltà dove deve

trovarsi nell’insieme ( alta, media, bassa)

le seggiovie, di cui si conosce il costo del biglietto , lo stato ( attributo booleano che indica

se sia aperta o chiusa)

È necessario sapere la lista delle piste raggiungibili tramite la seggiovia.

PROBLEMA GRAVE dell’esercizio è che la seggiovia necessita di tracciare informazioni

Il

relative alle piste raggiungibili.

Cosa sono le piste ? Sono dei tipi di impianto che hanno una difficoltà e un dislivello.

Quindi non possiamo mettere un attributo come piste multivalori.

Dobbiamo far riferimento all’entità già presente.

La seggiovia può raggiungere N piste e questa cosa va tracciata con una relazione.

Se decidiamo di mapparla verso il basso, non ho il minimo problema perché nella

mappatura sono due identità separate che possono essere correlate fra loro.

Se mappo verso l’alto , avrò una ricorsiva.

Se le mappate figlie DEBOLI rispetto al padre, ci troviamo 3 entità , ma 2 di quelle che ci

servono ( delle piste e delle seggiovie che possiamo collegare tra loro).

Quindi non c’è il problema di creare una relazione tra le figlie.

Questo per quanto riguarda lo smarrimento nei confronti del collegamento fra due figlie

deboli.

Per le cardinalità ragioniamoci.... dalla traccia . Dall’altro lato non c’è

Ogni seggiovia ha N piste e questo lo sappiamo

scritto niente.

In effetti, non è dato sapere se ogni pista è raggiungibile da 1 o da più seggiovie.

Se ho uno spazio per mettere tante seggiovie per ogni pista, sicuramente almeno una la

posso mettere.

A cosa vuole tendere questo discorso? Quando non siete sicuri su che cardinalità dare ad

una relazione perché non conoscete il dominio, mettetevi nel caso più cautelativo possibile.

Se metto che ogni pista è raggiungibile da N seggiovie, sbaglio? No, perché domani

mattina, ammesso che è sempre stato che ce n’è una sola di seggiovia per ogni pista,

possono sempre farne un’altra.

Quindi io do la possibilità più grande di tutte: quella di mettere quante seggiovie voglio per

ogni pista. Se ne voglio una sola, pazienza.

Nella traccia...

Sono organizzati dei corsi di sci che sono caratterizzati da:

un codice

un costo

data inizio ( inizio)

num di lezioni

pista da sci utilizzata ( qui non dobbiamo inventarci un attributo ma dobbiamo collegarlo

con le piste da sci. Il corso viene eseguito eseguito in una sola pista e , verosimilmente, si

svolgeranno N corsi )

istruttore di riferimento ( il corso ha un solo istruttore, mentre lo stesso istruttore farà più

corsi.

Di ogni istruttore si conoscono i dati anagrafici riassunti in CF [chiave]; nome, cognome,

data di nascita )

N.B. Quando ho detto:

Se avete nominato una pista, che è una cosa con delle caratteristiche, non potete utilizzare

un attributo pista ma dovete referenziare quell’entità. Non sto dicendo nulla di diverso dal

dire che quando un viaggio fa sosta in un comune, se il comune già citato nella traccia e ha

le sue caratteristiche , dovevate referenziare quel comune. Molti, in quella traccia, avevano

inventato un attributo comune che non aveva senso. All’istruttore viene

IMPORTANTE: non chiamate più volte le stesse cose con nomi diversi.

aggiunta anche l’esperienza che chiameremo ANNI. Dalla traccia...

per ogni corso bisogna tenere traccia degli iscritti ( con i relativi dati anagrafici). Anche qua:

CF (chiave)

nome, cognome, nascita

Deve esistere una relazione fra il corso e l’iscritto. Ogni corso ha N iscritti ( dalla traccia).

Dall’altro lato , dobbiamo dare la stessa possibilità allo stesso iscritto di iscriversi a più

corsi. Quindi anche i corsi sono N.

Questa decisione è supportata anche dal fatto che viene richiesta anche la definizione della

data di iscrizione. La data di iscrizione viene benissimo a cavallo della relazione, perché?

perché a seconda del corso la data di iscrizione cambierà e a seconda dell’iscritto.

Ogni iscritto si iscrive al corso in un determinato giorno ed ad altri corsi in altri giorni.

Quindi fa parte del bagaglio di informazioni che collegano gli iscritti ad un corso.

Viene detto che la data di inizio del corso deve superare la data di iscrizione. Mentre

leggevo ho saltato 2 cose:

quando parlava dell’istruttore diceva che occorreva verificare che i corsi effettuati su piste

con difficoltà alta, fossero tenuti da istruttori con almeno 5 anni di esperienza. Per

se introdurremo dei CHECK nell’esonero , saranno dei CHECK

prevenire qualsiasi critica,

banali.

Dobbiamo scrivere esattamente questo:

se la pista ha difficoltà alta, l’istruttore ha ANNI >= 5

BR:

1_Codice comprensorio 8 caratteri

2_ Pista.difficoltà IN (‘alta’ , ‘media’, ‘bassa’)

3_ Seggiovia.stato booleano IN (‘aperta’ o ‘chiusa’)

4_ Se la pista ha difficoltà alta, l’istruttore deve avere almeno 5 anni di esperienza. 5_ data

iscrizione < inizio (corso)

Vediamo come diventa questo modello.

Dobbiamo fare la mappatura logica per arrivare al modello da cui poi dobbiamo definire il

BDL.

Guardiamo la GERARCHIA. Questa va lasciata così come sta nella prima stesura del

modello. Però adesso, che dobbiamo creare il modello logico, ci dobbiamo porre il

problema di cosa fare in quella gerarchia.

Dipende da come sono incasinate tutte le entità coinvolte nelle gerarchia: l’impianto ha una

relazione con il comprensorio, ma è debole dal comprensorio.

In realtà l’impianto non interviene più di tanto a livello della gerarchia e, al limite, questa

avremmo potuta mapparla verso il basso. Però, siccome tutte e 3 le relazioni ( Impianto,

piste e seggiovia) risultano abbastanza coinvolte, questo è uno dei casi in cui conviene

lasciare tutte e 3 le entità e dichiarare piste e seggiovia deboli da impianto.

Se IMPIANTO fosse libera, ad es fosse l’ultima, non ci fosse un riferimento al comprensorio

e magari l’impianto comprendesse già tutto all’interno, avremmo potuto passare tutto

attraverso i figli ( ad es) . Ma per quella che è la situazione , conviene mantenere separate

quelle entità e procedere con una mappatura figlie deboli rispetto al padre.

Per fare questo:

o lo scrivete a parole

oppure riproponete sotto al modello, un nuovo modello relativo solo al pezzetto di

gerarchia. Scrivete la RISOLUZIONE GERARCHIA ( fate una rappresentazione di quello

che vi serve, tagliando tutto il resto)

Dobbiamo seguire un certo ordine:

comprensorio è slegato (cioè non mi serve definire delle tabelle prima di comprensorio) da

l’impianto .

tutti e da comprensorio dipende

Quindi se partiamo da comprensorio dovremmo riuscire a non far riferimento ad altri

attributi.

Procediamo nella MAPPATURA: (guardiamo solo il modello)

COMPRENSORIO (codice, nome)

dove codice è la chiave principale

IMPIANTO (cod_comp,numero,nome,lunghezza,apertura,chiusura)

dove cod_comp,numero è la chiave

NB: prima di chiudere mi devo chiedere se l’impianto è legato a qualcosa, di fatto sono le

figlie che sono collegate con l’impianto. Se vado a guardare che le figlie sono deboli

rispetto al padre, so che dovrò importare la chiave di impianto nelle figlie e non viceversa.

Questo mi assicura che le relazioni con cui l’impianto è collegato sono state tutte esaurite,

cioè l’unica era quella di comprensorio. Invece le altre le dovrò mappare dall’altro lato,

cioè dalla parte dei figli).

Devo procedere alla mappatura di pista e di seggiovia in un ordine che devo determinare. In

questo caso, poiché abbiamo scelto una N a N di pista e seggiovia, posso avere

l’indifferenza nell’ordine in cui le svolgo. Perché? perché evidentemente dovrò creare una

nuova tabella che coinvolge questi due.

Quindi facciamo:

PISTA (cod_comp,numero,difficoltà,dislivello)

dove cod_comp,numero è la chiave esterna e difficoltà,dislivello sono attributi semplici

NB: pista ha la maggiore difficoltà nella importazione della chiave. Abbiamo dichiarato che

pista e seggiovia sono dipendenti in maniera forte da impianto e che quindi le loro chiavi

saranno prese dalle chiavi di impianto. Questo significa che avrò sicuramente da mappare,

nel caso di piste, questo vincolo integrità referenziale con la gravante che si tratta di una

entità debole. Dovrò sicuramente portarmi la chiave dell’entità forte e quella chiave farà

anche da chiave della mia tabella.

SEGGIOVIA (cod_comp,numero,costo,stato)

dove cod_comp,numero è la chiave esterna

PISTA_SEGGIOVIA ( cod_comp_pista, numero_pista,cod_comp_seggiovia,

numero_seggiovia)

NB: PISTA_SEGGIOVIA è una relazione N a N, associa 1 pista ad 1 seggiovia. Devo

prendere la chiave di pista, devo prendere la chiave di seggiovia e devo dire che la

combinazione di queste due chiavi è chiave della tabella. Queste due tabelle hanno una

chiave doppia.

Tutto questo per le chiavi primarie.

Ora dobbiamo parlare delle chiavi esterne.

La coppia cod_comp_pista, numero_pista và presa da pista, ha un vincolo integrità

referenziale con la tabella pista.

La coppia cod_comp_seggiovia, numero_seggiovia và presa da seggiovia, ha un vincolo

integrità referenziale con la tabella seggiovia.

attenti, perché verrebbe da scrivere subito il corso, ma il

Quindi dobbiamo stare un po’

corso prevede che ci sia un istruttore.

Il PROBLEMA è che avrò bisogno nel corso di tracciare la relazione con 1 istruttore, quindi

quell’istruttore verrà accusato dell’integrità referenziale esterna ( come chiave esterna per il

corso).

Dovrò scrivere prima la tabella dell’istruttore e poi quella del corso. ISTRUTTORE ( CF,

nome, cognome, nascita, anni)

CORSO ( codice, costo, inizio, num.lezioni, CF_istr., cod_comp_pista, numero_pista)

caso l’integrità referenziale viene da pista e non da impianto.

In questo

IMPO: ricordatevi che, se pure quelle chiavi sono originarie di impianto, il legame è con

pista. Quindi la verifica che i valori vengano presi da un elenco specifico è fatta sull’elenco

valori di pista e non da impianto. Quindi l’integrità referenziale è con pista.

dei

Indipendentemente da quello che succede, la relazione è fra pista e corso.

ISCRITTO (CF, nome, cognome, nascita) CORSO_ISCRITTO (CF, codice,

data_iscrizione)

dove devo mettere la chiave di corso, la chiave di scritto e combinarli tra loro in una

relazione che ha come chiave la coppia delle chiavi più eventuali attributi della relazione.

Ora dobbiamo cambiare tutto in un punto b) procediamo....

L’ing.Loseto mi ha detto che solitamente vi scrive le parole del linguaggio Maiuscole e il

resto minuscolo. Quindi:

CREATE TABLE Comprensorio ( codice char (8) PRIMARY KEY, nome VARCHAR

(30))

dove Comprensorio è il nome della tabella

codice è la chiave della tabella vedo se ci sono delle business rule relative a comprensorio,

il codice deve essere di 8 caratteri quindi una stringa, caratteri vuol dire alfanumerici però

esattamente di 8. Per fare questo , in SQL si scrive

codice char (8) PRIMARY KEY

dove PRIMARY KEY lo posso scrivere in riga perché è una primary key singola

nome

dove, per quanto di natura stringa come il codice è una stringa di lunghezza variabile,

quindi nome VARCHAR(30) dove 30 è la lunghezza massima.

CREATE TABLE Impianto (

cod_comp CHAR (8) REFERENCES comprensorio (codice) numero SMALL INT,

nome VARCHAR (30),

lunghezza NUMERIC , apertura TIME , chiusura TIME,

PRIMARY KEY (cod_comp, numero))

devo ricordare che deve avere lo stesso dominio che è compatibile con il codice.

cod_comp CHAR , poiché si tratta di un attributo che ha integrità referenziale rispetto

all’altra tabella, ed è integrità referenziale singola (cioè di singolo attributo) posso scrivere

direttamente

numero (è progressivo) SMALL INT ( perché non c’è bisogno di predisporre un valore

particolare) lunghezza NUMERIC (può essere anche decimale )

CREATE TABLE Pista (

cod_comp CHAR (8), numero SMALL INT ,

difficoltà VARCHAR (5) CHECK (difficoltà IN(‘alta’, ‘media’, ‘bassa’)), dislivello

NUMERIC,

PRIMARY KEY (cod_comp,numero),

FOREIGN KEY(cod_comp, numero) REFERENCES Impianto ( cod_comp, numero))

dove la difficoltà devo ricordare che va in alta, media e bassa. Intanto è una stringa, cioè è

una sequenza variabile di caratteri. La variazione però è fra 3 possibili valori ( uno di 4

uno di 5 e l’altro di 5), quindi il massimo dove posso arrivare con quella stringa è 5

lettere,

caratteri.

In più non è un VARCHAR qualunque, ma devo dire io come deve essere fatto. I valori di

questo attributo difficoltà , quando il database andrà riempito, sono 1 fra questi 3 (alta,

media,bassa) . Quindi si scrive in SQL..

IMPO: concetto di condizione.

Io sto ponendo una condizione insiemistica, cioè sto dicendo che difficoltà che è un

attributo e quindi i valori di quell’attributo devono essere inclusi nell’insieme alta, media,

bassa.

Quindi questo CHECK, questa verifica verrà fatta esattamente in questo modo, qualsiasi

valore voi andare ad inserire per difficoltà verrà confrontato con uno dei 3 possibili valori.

Se è sbagliato , non avviene l’inserimento.

dislivello (dipende da se lo volete considerare un num reale o intero) NUMERIC

(considerato come numero reale)

FOREIGN KEY (coppia identical alla PRIMARY KEY perchè questa chiave la devo

prendere esattamente dalle chiavi di impianto)

Se fate le 2 FOREIGN KEY separate è un ERRORE, cioè se in corrispondenza di cod_comp

scriviamo REFERENCES Impianto, non è la stessa cosa di fare una FOREIGN KEY

doppia. Imponendo 2 vincoli singoli, stiamo solo dicendo che cod_comp và preso

dall’impianto e che il numero di impianto và preso dall’impianto. Non state dicendo che la

coppia và presa dalla stessa riga di impianto. Quindi non sono equivalenti espressioni che

separano questa chiave esterna sulle 2 righe. Non sono la stessa cosa.

CREATE TABLE Seggiovia ( cod_comp CHAR (8),

numero SMALL INT, costo NUMERIC, stato BOOLEAN ,

PRIMARY KEY (cod_comp,numero),

FOREIGN KEY (cod_comp,numero) REFERENCES Impianto (cod_comp,numero))

CREATE TABLE Pista_Seggiovia ( cod_comp_pista CHAR (8), numero_pista SMALL

INT, cod_comp_seggiovia CHAR (8), numero_seggiovia SMALL INT,

PRIMARY KEY (cod_comp_pista, numero_ pista, cod_comp_seggiovia,

numero_seggiovia) , FOREIGN KEY (cod_comp_pista, numero_pista) ,R EFERENCES

Pista ( cod_comp, numero),

FOREIGN KEY(cod_comp_seggiovia, numero_seggiovia) REFERENCES Seggiovia

(cod_comp,numero))

CREATE TABLE Istruttore (

CF CHAR(16) PRIMARY KEY , nome VARCHAR (30),

cognome VARCHAR (30), nascita DATE,

anni SMALL INT)

CREATE TABLE Corso (

codice NUMERIC PRIMARY KEY,

costo NUMERIC, inizio DATE , lezioni SMALL INT,

CF_istruttore CHAR (16) REFERENCES Istruttore (CF), cod_comp CHAR (8),

numero SMALL INT,

FOREIGN KEY (cod_comp,numero) REFERENCES Pista (cod_comp,numero),

CHECK((((cod_comp,numero) IN SELECT (cod_comp,numero)

FROM Pista

WHERE difficoltà < > ‘alta’) OR

(((cod_comp,numero) IN SELECT (cod_comp,numero ) FROM Pista

WHERE difficoltà = ‘alta’)) AND

(CF_istruttore) IN SELECT(CF) FROM Istruttore

WHERE anni >=5))))

il CHECK dice che se la pista ha difficoltà alta, l’istruttore deve avere anni di esperienza

>=5. Da dove andiamo a dedurre queste 2 info?

la pista contiene le info sulla difficoltà della pista

gli anni di esperienza si trovano nella tabella istruttori.

Quindi dobbiamo combinare le info dell’istruttore con quelle della pista e confrontare cose

tipo. L’unico punto dove posso fare questa cosa è il corso . Perché? il corso è il

di questo

legame fra la pista e l’istruttore. Solo che le info sulla difficoltà prevederanno un accesso

dalla tabella delle piste e le info sull’istruttore prevederanno un accesso dalla tabella

dell’istruttore. Quindi avrò 2 select differenti.

Cosa deve succedere? Io mi posso trovare in 2 casi disgiunti:

o

1 ) caso in cui la pista non è di difficoltà alta. Se io porto un CHECK difficoltà=alta e anni

esperienza >5, sto dicendo che il sistema dovrà controllare ad ogni immissione che sia

verificata quella condizione di astri. Questo significa che se uno va a mettere la pista di

difficoltà bassa, non funziona il CHECK.

Devo prevedere la condizione di funzionamento normale, cioè quando non è alta la

difficoltà.

CREATE TABLE Corso_Iscritto (

codice NUMERIC REFERENCES Corso(codice) , CF CHAR(16) REFERENCES Iscritto

(CF),

data_iscrizione DATE, PRIMARY KEY (codice,CF)

CHECK(data iscrizione < SELECT inizio FROM Corso

WHERE Corso.codice=codice)

Punto c)

Ricopio il tabellone.

L’unica ipotesi che viene fatta è che il campo note contenga info aggiuntive fornite al

momento della denuncia.

Dal dominio dei dati capiamo che si parla di denunce cittadini e sono coinvolti anche degli

ufficiali e dei reati.

In teoria, noi dovremmo riconoscere , all’interno di queste possibili chiavi:

gli ufficiali

i cittadini

la denuncia

i reati denunciati

L’ipotesi più plausibile andando a scorrere gli attributi in avanti è che da matricola ufficiale

derivino il nome dell’ufficiale e il cognome dell’ufficiale.

DIPENDENZE FUNZIONALI

matricola ufficiale→ nome_ufficiale, cognome_ufficiale P (dipendenza parziale)

CF cittadino → nome_cittadino, cognome_cittadino, data_di_nascita T(dipendenza

transitiva)

andando avanti, vedo che si parla di denuncia e di reato. La cosa che vedo subito però è

e più a destra vedo che l’ora del

che leggendo codice e reato ha appresso una descrizione

reato e la data del reato si trovano lontani.

Ipotesi che dobbiamo fare: probabilmente i reati hanno una codifica che non cambia a

seconda di chi li commette. Cioè la polizia avrà una certa codifica di reati che a seconda di

come vengono denunciati.

ESEMPIO:

scippo → codice n 3 furto → codice n 4

Esiste una codifica a monte dei reati che possiamo supporre.

codice_reato → descrizione del reato P( dipendenza parziale)

data e l’ora, però alla stessa data e ora

ci sono delle info temporali sulla denuncia, tipo la

potrebbero essere raccolte denunce temporanee. Perché più cittadini potrebbero recarsi ad

ufficiali differenti e denunciare cose diverse.

Così come cambiano anche i reati, perché a seconda della denuncia ci sono dei reati.

Ho 2 possibilità , una volta che decido che a quella data ora ci possono essere più denunce,

posso ragionare in 2 modi:

o mi metto dalla parte del cittadino. Se il cittadino deposita alla stessa data ora una

denuncia, non ne deposita più di 1 perché non esiste la molteplicità del cittadino. Al più,

nella stessa denuncia metterà più reati.

o dalla parte delle istituzioni. Se un ufficiale sta raccogliendo ad una certa data ora una

denuncia non ne raccoglie più di 1.

Mi metto dalla parte dell’ufficiale e decido che per identificare la denuncia ci sia l’ufficiale

come ulteriore chiave. Quindi non mi bastano la data e l’ora della denuncia, ma devo

aggiungere a quelle info sulla denuncia le info relative all’ufficiale.

matricola ufficiale→

data denuncia, ora denuncia, CF_cittadino, note P (dipendenza

parziale)

con queste 3 info posso determinare chi ha fatto la denuncia, cioè il cittadino mi devo

ricordare che il campo note è associato a tutta la denuncia.

Mi rimane il problema del reato, perché? in una denuncia io ho varie scelte:

o faccio in modo che ad ogni denuncia corrisponde 1 solo reato

oppure (caso più cautelativo) io posso prevedere che in una denuncia vengono elencati più

reati . Quindi mi devo preoccupare di tracciare per ogni denuncia un reato/vari reati.

Quindi in teoria dovrò prendere la chiave della denuncia.

data denuncia, ora denuncia, matricola ufficiale, codice reato → data_reato, ora_reato

P(dipendenza piena)

Così posso dire che ogni denuncia ha vari reati, che posso tutti memorizzarli.

Per ciascuna coppia denuncia reato, vado a scrivere la data del reato e l’ora del reato.

Oggi iniziamo a vedere come è fatto un DBMS da un punto di vista organizzativo.

Consideriamo un elemento. Fino ad ora abbiamo guardato alle basi di dati come dei depositi

organizzati e strutturati di dati.

Quando guardiamo al funzionamento di una base di dati, in realtà noi guardiamo a un

deposito di dati e a delle azioni che vengono eseguite sullo stesso . In pratica guardiamo ai

processi che accedono ai dati, eventualmente li modificano e poi riscrivono il risultato sulla

base di dati stessi .

Introduciamo il concetto di TRANSAZIONE.

Una transazione è un’attività elementare svolta su una base di dati. Le basi di dati vedono il

concetto di transazione in una maniera un po’ particolare.

Allora vediamo come una azione elementare, svolta su una base di dati , verifica 4 proprietà

, identificate con l’acronimo ACID ( per cui si dice che le transazioni sono acide ):

→ una transazione è una unità indivisibile . Vuol dire che la transazione è definita

Atomicità

come un tutto unico , non è considerabile come delle parti e anche se la riguardiamo

attraverso delle parti, queste parti o ottengono un risultato completo oppure non è possibile

avere dei risultati intermedi della transazione .

ESEMPIO BANCARIO: se pensiamo ad un Bonifico (prendo dei soldi da un conto corrente

e sposto i soldi su un altro conto corrente ) . In che cosa consiste una transazione da questo

punto di vista.

Ci sono due conti correnti Conto x Conto y faccio un Bonifico di 400

vuol dire che prendo x ( non lo abbiamo ancora fatto in SQL) ma esiste un’operazione che si

chiama Update (aggiornamento) e scrivo:

Update x = x 400 Update y = y + 400

L’ operazione può essere vista come 2 azioni elementari: primo aggiornamento ( tolgo dei

soldi ) e secondo aggiornamento (metto dei soldi) .

L’operazione ,perché abbia senso, deve essere un tutto unico ( indivisibile) .

Cosa si potrebbe verificare? se ad es. io sottragga questi soldi e non effettui la somma,

perché? perché ad es . nel frattempo se ne va la corrente.

Oppure potrebbe succedere che io non abbia 400 euro sul conto x . La transazione dovrebbe

concludersi con :

if (x 0 ) OK

Quindi se alla fine di questa transazione il conto ha ancora dei soldi, va bene . Altrimenti la

transazione non viene eseguita.

Quindi il non eseguita vuol dire disfare la prima e la seconda operazione . Entrambe le

operazioni non vengono eseguite .

L’atomicità rappresenta questo fatto, cioè quando guardo un DBMS transazionale

consideriamo il fatto che l’azione una volta che inizia può completarsi io uno tra due

segmenti .

COMMIT (WORK) → tutte le azioni intermedie svolte all’interno di questo nucleo di

operazioni elementari viene eseguita correttamente, tutte le azioni elementari vengono

eseguite.

ROLLBACK (WORK) detto anche ABORT → non solo l’ultima azione, ma tutte le azioni

non vengono eseguite . Si ripristina lo stato quo ante all’inizio della transazione .

Quindi potrei vedere in maniera atomica la transazione, scrivendo: START (or BEGIN )

TRANSITION –

Update x = x 400 Update y = y + 400 if (x 0 ) COMMIT else ROLLBACK END

TRANSITION

Cioè la transazione finisce in uno tra i 2 stati .

è atomica, diciamo che l’unità di elaborazione

Quindi quando diciamo che la transazione

resta indivisibile anche se è costituita da azioni intermedie.

Consistenza → una transazione rispetta le regole e i vincoli presenti nel DBMS

Cosa vuol dire? voi sapete che su una base di dati vengono definiti dei vincoli . Una

transazione rispetta i vincoli, cioè porta il data base da uno stato consistente ad un altro stato

consistente.

Isolamento → ogni transazione si comporta in maniera isolata dalle altre. Cioè non avviene

un “effetto domino” sulle transazioni.

Possiamo dire che ogni transazione è una monade, cioè vive isolata dalle altre.

Il domino non si verifica. Cioè l’abort di una transazione non implica l’abort di un’altra

transazione.

Durabilità (persistenza) → gli effetti di una transazione durano nel tempo. Una volta che la

transazione si è verificata ed ha esplicato i propri effetti, la memorizzazione resta

indefinitamente nel tempo. Questa è un’astrazione, dura nel tempo vuol dire che garantisco

un impegno affinchè esista una ridondanza nelle strutture di memorizzazione . Questo fa si

che nel tempo, una volta che la transazione sia verificata, non se ne perda il risultato. Anche

questo è un elemento fondamentale perché uno si fidi.

Incominciamo a capire come queste cose si realizzano in un sistema fisico, un database

fisico.

Ripartiamo dai dati . Voi sapete che un DBMS viene ospitato, cioè il DBMS per noi è un

applicativo top (un insieme di programmi) che:

accede alla memoria di massa

preleva dei dati

gli elabora eventualmente

li riscrive sulla memoria di massa seguendo dei criteri

Voi sapete che dal punto di vista dell’organizzazione nel laboratorio elettronico, la memoria

di massa è basata in generale sul concetto di memorizzazione su un disco magnetico. Come

funziona un disco magnetico?

Ognuno di questi li possiamo vedere come un’unità elementare.

Sapete che un disco magnetico si basa sulla proprietà di isteresi di un materiale

ferromagnetico.

Cosa succede? Noi definiamo un disco e lo suddividiamo logicamente in tracce e settori ,

definiamo i blocchi elementari.

Questa suddivisione avviene attraverso un processo che si chiama FORMATTAZIONE .

FORMATTAZIONE→ divisione di questo cerchio di materiale ferromagnetico in parti,

ciascuna individuabile singolarmente attraverso un indirizzario.

Ad ognuna di queste parti può essere associata una certa quantità di informazioni.

Logicamente un blocco è di 512 byte. Quindi la singola unità memorizzabile di memoria di

massa non è né 1 bit né 1 byte ma è 512 byte.

Un disco rigido è fatto in questo modo

Mentre gira abbiamo un pettine di testine magnetiche che si muove longitudinalmente. La

lettura e la scrittura avviene basandosi sulla Legge di Lenz , la differenza del flusso nel

tempo.

La determinazione di ciascuna cella è fatta individuando:

settore

traccia

cilindro→ è la corrispondenza della testina

Se mi chiedete, dove sta il disco rigido sul mio smartphone? Sullo smartphone c’è una

memoria elettronica (EPROM) che si comporta logicamente come questa, ma non è basata

su un materiale in ferromagnetico, è basata su semiconduttori.

La maggior parte dei grandi database comunque, alla fine, i dati li immagazzina su una

memoria magnetica. Perché? per un motivo di costo.

I dati , per poter essere elaborati, devono essere organizzati perché io possa trovarli in

memoria di massa . Ma ,quando li voglio utilizzare, i dati devono essere portati in memoria

centrale.

L’elaborazione vera e propria avviene sempre nella RAM (memoria elettronica

rappresentabile come una struttura in cui posso indicizzare alla singola cella , che

rappresenta un byte o una parola).

Quando il pc è spendo, dove stanno i dati? stanno tutti nella memoria di massa.

La memoria di massa è stabile (in generale), cioè anche se non è alimentata continua a

mantenere la propria memoria.

Buona parte della vita del DataBase , quindi anche dell’efficienza di un DB, è la velocità

con cui io riesco a:

prendere i dati dalla memoria di massa,

portarli in memoria centrale,

rielaborarli e

rimetterli in memoria di massa .

Quando noi valutiamo il costo di un’operazione, dovremmo valutare:

il costo di input/output (C ) tempo di prendere dei dati che stanno sulla memoria di

I/O

massa e portarli sulla memoria centrale

) → tempo di elaborazione vera e

il costo di CPU (C propria dove per costo intendiamo il

CPU

tempo.

Nessuno considera questo tempo in pratica in un DB, perché ci sono ordini di grandezza e di

è un’operazione

differenza. Spostare dei dati dalla memoria di massa alla memoria centrale

che va nell’ordine dei millisecondi. Fare un’operazione su un processore e con accesso alla

memoria centrale è un’operazione che va valutata in termini di nanosecondi.

-9

1 NANOSECONDO = 10 secondi

-3

MILLISECONDO = 10 secondi

Nessuno guarda a questo tempo che esiste , ma è trascurabile. Il nostro problema è gestire in

maniera efficiente lo scambio di dati tra la memoria di massa e la memoria centrale e

viceversa.

Tutti i DBMS usano delle funzioni PRIMITIVE messe a disposizione dal sistema operativo

per gestire più o meno direttamente l’accesso alla memoria di massa e portare i dati in

memoria centrale.

Esiste un modulo software che viene chiamato BUFFER MANAGER. Questo modulo

gestisce :

l’accesso

l’inserimento in memoria di massa

il riporto in memoria di massa di parti della memoria di massa stessa che chiameremo

pagine.

Per semplicità assoceremo un blocco ad una pagina.

Il BUFFER che cosa è? È un’area della memoria centrale (RAM) che viene allocata e

gestita per il DBMS.

Quindi voi avete che la memoria centrale (RAM) vede una parte della stessa che viene

gestita direttamente dal DBMS , chi in particolare gestisce questa area ( dove verranno

messi i dati) ? è il BUFFER MANAGER.

Quando una transazione ha bisogno di accedere a dei dati cosa fa? esistono delle tabelle

indice da dove prendere questo blocco di dati che le serve.

La prima funzione primitiva che svolge questa attività si chiama FIX → va a vedere se nel

BUFFER ( area di memoria che possiamo vedere come una tabella , in termini logici)

contiene questa pagina. Cioè l’area che serve per l’elaborazione.

La FIX è una funzione primitiva che serve a caricare e rendere disponibile ad una

transazione un’area di memoria di massa di interesse.

Riprendiamo il nostro esempio di transazione.

X è un record , cioè sono dei dati. Immaginate 20 byte, 30 byte. Dove stanno scritti

normalmente? C’è una tabella dove stanno scritti . Questi 30 byte non sono direttamente

individuabili, sono dentro una pagina ( per noi una pagina è un blocco elementare).

Voglio fare x-400 , x sta scritto in memoria di massa ( visto che il conto corrente si presume

che lo abbia memorizzato da qualche parte) . La transazione chiede che quel dato venga

messo in memoria centrale , perché? perché per fare una sottrazione (operazione aritmetiche

che viene fatta dal processore che viene fatta in memoria centrale ).

Quindi dobbiamo localizzare dove si trova quel record in una pagina della memoria di

massa. Come avviene questo fenomeno? attraverso la TABELLA DELLE

CORRISPONDENZE.

Il BUFFER MANAGER cosa fa?

la prima cosa che fa è vedere se questa pagina è già casualmente in memoria centrale,

perché magari c’è un altro conto corrente che sta nello stesso blocco e ha usato 30 sec fa

questa pagina.

CASO A :Quindi la pagina è già in memoria centrale. Se è già in memoria centrale ed è

libera , cioè non è in uso ad altre transazioni essa può essere allocata alla transazione

richiedente. CASO OTTIMO. Quanto tempo richiede? se la pagina è già in memoria

centrale, richiede qualche nanosecondo.

CASO B: la pagina non è in memoria centrale, ma è in memoria di massa . Cosa si fa in

questi casi? devo vedere se c’è spazio nell’area di BUFFER , questa viene ogni volta

caricata man mano che ci sono queste richieste con queste pagine. Potrebbe essere che

quell’area di BUFFER sia già stata riempita a seguito di altre richieste. In questo caso è

necessario individuare 1 o più pagine da riportare in memoria di massa.

CASO A : scopro che x sta già nella postazione ( in tabella) e la riservo per la transazione

1

CASO B : le slot ( y,z,v..) sono disponibili. Quindi che succede? x viene caricata e si ritorna

al caso A . A questo punto x viene allocata alla transazione.

: x non c’è, ma tutte le posizioni per pagine sono occupate. L’area di BUFFER è

2

CASO B

piena. Che cosa devo fare? devo scaricare 1 o più di queste pagine e portarle in memoria di

massa. Prendo questa e la ricopio in memoria di massa, adesso questa può essere utilizzata e

ci metto la x. Una volta che l’ho caricata ritorno al CASO A , cioè la pagina disponibile

allocata alla transazione.

PRIMITIVA : UNFIX

La transazione segnala al BUFFER MANAGER di aver completato l’utilizzo di quella

pagina, che vuol dire di un record all’interno di quella pagina, quindi la pagina è resa libera

e resta in memoria centrale.

PRIMITIVA: SET DIRTY ( segna lo sporco)

Marca una pagina che sia stata modificata nel corso della sua presenza in memoria centrale.

PRIMITIVA: FLUSH (sciacquone) e FORCE

Sono due primitive di scrittura dalla memoria centrale alla memoria di massa.

La FLUSH è una primitiva di scrittura che passa dal BUFFER in memoria di massa

asincrona con le transazioni, viene gestita direttamente dal BUFFER MANAGER.

La FORCE è una primitiva di scrittura dal BUFFER MANAGER alla memoria di massa

sincrona con le transazioni.

Qual è la differenza?

quando uso FORCE scrivo direttamente, e indipendentemente dall’utilizzo o meno dal resto

del sistema ( dal carico o meno dell’elaboratore), il contenuto di una pagina dalla memoria

centrale alla memoria di massa.

La FLUSH è una scrittura di più pagine , è fatta per scelta dal BUFFER MANAGER.

Riallinea tutte le pagine presenti tra la memoria centrale e la memoria di massa , scrivendo il

valore attuale dei dati. Quando io porto un blocco di dati in memoria centrale e lo modifico,

in quel momento c’è un disallineamento.

NB: SET DIRTY → a che serve? a dire che una pagina è sporca ( cioè è stata modificata

mentre era in memoria centrale).

Diventa importante marcare la pagina modificata, perché? se essa diventa identica a quella

originale in memoria di massa , io la posso sovrascrivere direttamente senza perdere tempo

a ricopiarla uguale .

Ogni volta che riallineo perdo un sacco di tempo. Il sistema è più efficiente riducendo il

numero di scritture .

Diventa anche importante scegliere le VITTIME ( cioè le pagine che devo scegliere di


ACQUISTATO

1 volte

PAGINE

320

PESO

5.94 MB

AUTORE

ValCan10

PUBBLICATO

7 mesi fa


DETTAGLI
Corso di laurea: Corso di laurea in ingegneria gestionale (BARI, FOGGIA)
SSD:
A.A.: 2018-2019

I contenuti di questa pagina costituiscono rielaborazioni personali del Publisher ValCan10 di informazioni apprese con la frequenza delle lezioni di Basi di dati e sistemi informativi 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 Bari - Poliba o del prof Colucci Simona.

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 Corso di laurea in ingegneria gestionale (bari, foggia)

Internet of things
Appunto
Metodi di Ottimizzazione
Appunto