Basi di dati: Controllo dell'accesso: GRANT

Modello di Autorizzazione del System R (GRANT)

E' simile a quello implementato nella maggior parte dei BBMS

E' un sistema chiuso (di default nessun permesso concesso) e discrezionale (a discrezione dell'amministratore concedere i privilegi)

La gestione dei privilegi è delegata agli owner e a quanti essi delegano mediante GRANT. Quando un utente riceve dei privilegi WITH GRANT OPTION può anche delegarli a sua volta.

I privilegi delegabili sono SELECT, INSERT, UPDATE e DELETE

Esempi:

GRANT UPDATE ON
Tabella
TO
Utente (da diritto di UPDATE a Utente su Tabella)

GRANT UPDATE (Campo1, Campo2) ON
Tabella
TO
Utente (da diritto di UPDATE a Utente su campo1 e Campo2 di Tabella)

GRANT SELECT ON
Tabella
TO
Utente
WITH GRANT OPTION (da diritto di SELECT a Utente su Tabella. Utente può a sua volta delegare tale diritto)

GRANT ALL PRIVILEGES ON
Tabella
TO
Utente (da tutti i diritti a Utente su Tabella)

Le informazioni delle GRANT stanno nel CATALOG, precisamente nelle tabelle SYSAUTH e SYSCOLAUTH (suppongo in postgreSQL).

Esempio di SYSOUT:

La colonna UPDATE può avere valori: N, SOME, ALL. Se è SOME nella tabella SYSCOLAUTH devono essere specificati i campi per cui ha diritto di UPDATE.

Nella colonna go è specificato se l'utente ha la GRANT OPTION

Eliminare una GRANT: REVOKE

Solo chi ha concesso privilegi può rimuoverli

REVOKE
Privilege
ON
Tabella
FROM
Utente

Se un utente ha ricevuto GRANT da più utenti è necessario che tutti questi gli facciano una REVOKE affinché vengano effettivamente rimossi (BELLO SCHIFO!)

La revoca è ricorsiva (se faccio la REVOKE su un utente che a sua volta tramite GRANT OPTION ha concesso ad altri vengono revocati anche a quest'ultimi)

Aggiungo il timestamp (s) del momento in cui la GRNT viene concessa nella SYSAUTH: così posso fare la revoca ricorsiva

Posso ridurre ad un grafo:

Se un privilegio è dato in modalità non delegabile viene barrato l'arco di congiungimento

Quando revoco un privilegio che aveva GRANT OPTION per la ricorsione valuta il timestamp:

Se A da SELECT a B nell'istante 1

B da SELECT a C nell'istante 2

D da SELECT a B nell'istante 3

B da SELECT a E nell'istante 4

Se A revoca a B allora verrà revocato anche il permesso a C, ma non a E

Sistemi commerciali

Altri permessi concedibili/revocabili:

USAGE -> (per domini o insiemi di caratteri) Abilita l'uso del nuovo dominio (tipo di dati)

Anche INSERT è applicabile a singole colonne

REFERENCES -> Abilita delle colonne a essere inserite in vincoli di integrità referenziale

 
 

Gestioni degli utenti per gruppi

 
 

Comando di REVOKE aggiunge delle clausole opzionali:

• Per rimuovere solo la GRANT OPTION
  
• RESTRICT / CASCADE (default) -> non viene messa la revoca a cascata
  

Contenuti gestiti da Michele Bernardi. Non mi assumo alcuna responsabilità sulla correttezza di questi dati, usateli a vostro rischio e pericolo.

Basi di dati: Sicurezza di Basi di dati

Divisibile in 3 capitoli:

1. Segretezza: proteggere da letture non consentite
   
2. Integrità: proteggere da scritture non consentite
   
3. Disponibilità: garantire il giusto accesso agli utenti legittimamente autorizzati (proteggersi da attacchi DOS: Denied Of Service)
   

Tecniche di protezione:

1. Autenticazione: verificare di dare accesso solo a chi consentito
   
2. Controllo dell'accesso: validare richieste di accesso (verificare cosa può fare l'utente autenticato)
   
   1. Crittografia: offuscare i dati per renderli non leggibili a terzi
      

    
    

   Innanzitutto devono essere definite delle politiche di sicurezza
   

   Le politiche di sicurezza vengono trasformate in Regole di autorizzazione comprensibili al database
   

   Il motore del database ha una parte adibita alla gestione delle autorizzazioni, il Reference Monitor che concede, nega o concede parzialmente l'accesso
   

    
    

   Due famiglie politiche di amministrazione della sicurezza (stabiliscono chi può concedere/revocare i permessi di sicurezza):
   

• Centralizzato: un unico amministratore concede tutti i permessi di sicurezza
  
• Distribuito: più di un amministratore. Di solito chi è abilitato a creare oggetti sul DB diventa proprietario degli oggetti che crea e ne è amministratore
  

 
 

 
 

Due famiglie di politiche di controllo d'accesso:

• Politica del minimo privilegio. Le autorizzazioni di accesso ai dati vengono concesse solo per il minimo dei dati che l'utente userà. L'accesso è di default negato.
  
• Politica della massima condivisione. L'accesso è di default consentito (deve essere negato esplicitamente)!
  

Controllo d'accesso dipendente dal nome: imposto le autorizzazione sui diversi oggetti del DB

Controllo accessi dipendente dal contenuto: creo viste e stored procedure e poi do le autorizzazioni su questi

Controllo accessi dipendente dal contesto (ad esempio dall'orario): mediante stored procedure

Controllo dipendenti dalla storia degli accessi (es: non di n accessi). Mediante stored procedures/triggers

Le politiche di controllo degli accessi si dividono in:

• Discrezionali: DAC
  Richiedono che vengano specificati i diritti che ogni soggetto possiede sugli oggetti del sistema. Molto flessibili, ma di limitata portata. I DBMS normali sono DAC (ci sono anche i trusted DBMS che sono MAC)
  
• Mandatorie: MAC
  Politiche basate su classificazione di dati e utenti (diversi livelli di sicurezza: Top Secret, Secret, Classified, Unclassified). I sistemi mandatori vietano che dati di livelli elevati possano spostarsi in livelli più bassi. No-Read-Up: Un utente con permessi per un livello può leggere quello e i sottostanti, non i superiori. No-Write-Down: non scrivere dati di livello alto in fonti raggiungibili dal basso
  

Attacco basato su cavallo di Troia

Un utente privo dei diritti su particolari dati riesce a modificare un programma di utilità generale. Il programma viene lanciato da un utente a privilegi alti e copia i dati in una posizione raggiungibile dall'hacker

Contenuti gestiti da Michele Bernardi. Non mi assumo alcuna responsabilità sulla correttezza di questi dati, usateli a vostro rischio e pericolo.

Basi di dati: Progettazione fisica di un database

Occorre ottimizzare molti parametri, alcuni specifici del DBMS

Ad esempio cache, suddivisione in file, ecc.

Creazione di INDICI

Gli indici sono un B-TREE che ordina alcuni campi di una tabella.

Possono essere UNIQUE (con vincolo di unicità) o no

Si creano con CREATE INDEX NomeIndice ON NomeTabella(ElencoCampiIndicizzati)

Si elimina con DROP INDEX NomeIndice

Vanno mantenuti (un indice non usato è un peso per il DB)

E' necessario inserire indici per le query che vengono fatte più spesso (per ottimizzarle)

L'accesso tramite indice è molto performante

Gli indici si dividono in vari tipi:

• CLUSTERED (sono l'ordine fisico della tabella stessa) e UNCLUSTERED (solitamente residenti in una parte diversa della tabella)
  
• DENSE (hanno un entry per ogni tupla della tabella), SPARSE (opposto)
  
• SINGLE LEVEL (indice "flat"), MULTI LEVEL INDEX (indice organizzato ad albero)
  

Il DBMS definisce automaticamente un INDICE su ogni chiave primaria (di solito CLUSTERED)

E' abbastanza ragionevole mettere indici sulle chiavi esterne

Basi di dati: Schema logico di un database

Lo schema logico (o schema relazionale) non è indipendente ed astratto come lo schema concettuale.

Praticamente partendo dallo schema concettuale lo impoverisco per essere adattato alla realtà relazionale che è fatta solo di entità ed attributi e non ha legami di classi (superclassi, sottoclassi).

Analisi dei dati derivati

Ovvero i dati "derivabili da altri dati" (ad esempio numero di posti liberi in una sala da cinema (derivabile dal n° di prenotazioni)

Il progettista per decidere se tenere un dato derivato deve paragonare i "costi" fra averlo e non averlo. Ovvero la differenza di costo tra mantenere il dato derivato o calcolarlo quando serve.

I tipi di dati derivati sono ridondanti (appunto derivabili da un'altra fonte) possono essere di varia natura:

• direttamente derivabili (es. durata sapendo ora inizio e ora fine)
  
• derivabili tramite conteggio attraverso la relazione tra due entità (es: importo totale dell'acquisto se ho ho già gli oggetti acquistati ed il loro singolo prezzo)
  
• derivabili tramite conteggio attraverso una relationship (es. n° di posti prenotati/liberi per le varie sale di un cinema)
  
• ridondanti per ciclo di associazioni: potrebbe esserci addirittura una relationship ridonandante (ad esempio: avendo già gli studenti iscritti ad un corso e il docente che tiene il corso sarebbe ridondante avere una relationship diretta tra studente e professore del tipo "insegna a").
  

Gerarchie di generalizzazione

Ovvero la rappresentazione di una gerarchia

Possono essere ristrutturate in 3 modi:

1. Mantenimento delle sole entità superclasse (generica)
   Esempio: Tengo impiegato al posto di avere impiegato, ingegnere, segretario, direttore. Gli attributi "extra" diventano opzionali in impiegato
   
2. Mantenimento delle sole entità sottoclasse (specifica)
   Esempio: tengo le classi impiegato, ingegnere, segretario, direttore e gli attributi comuni (impiegato) li metto in ciascuna sottoclasse
   
3. Mantenimento di entrambe le classi
   Ad esempio realizzerò la classe impiegato e le varie sottoclassi le realizzerò come entità "differenziali": ovvero munite dei soli attributi che estendono il concetto di impiegato, oltre, ovviamente, ad una relazione verso l'entità impiegato che viene effettuato mediante l'unica chiave primaria (unico gruppo di attributi ripetuto in ogni tabella. Ad es. codice fiscale). Nella entità impiegato ci sarà un campo booleano che indica l'appartenenza ad una sottoclasse (ad esempio attributi come ingegnere, segretario, direttore). Dovrò implementare un vincolo che non permetta di avere più di un attributo di sottoclasse attivo nel DB.
   

Normalmente la alternativa 3 è consigliabile.

Se ho (quasi) solo accessi che mi chiedono TUTTI gli attributi indipendentemente dalla sottoclasse di appartenenza conviene la 1 perché riduce gli accessi al DB (che altrimenti dovrebbero essere 1 per la superclasse e 1 per la sottoclasse).

Se invece ho (quasi) solo query che accedono a tutti gli attributi DIPENDENTEMENTE dalla sottoclasse di appartenenza (es.: non voglio mai vedere l'elenco di tutto il personale, ma solo separatamente quello di ingegneri, direttori, segretari ed impiegati) allora l'alternativa 2 è conveniente.

Scelta dell'identificatore primario

Se ci sono più identificatori che possono essere chiavi candidate devo scegliere.

• Escludere quelli che possono avere valori nulli (ad es. se non sempre inserisco subito il codice fiscale lo devo evitare)
  
• Evitare le chiavi composte
  
• Scegliere una chiave che abbia un valore semantico se possibile (non un ID creato apposta)
  

Togliere attributi multi valore e attributi complessi

Trasformarli in entità associata (ad esempio il n° di telefono) che conserva la stessa cardinalità (ad es. 1-n)

Attributi composti possono essere "espansi" sull'entità stessa o possono essere messi in una nuova entità collegata (ad es. indirizzo come composito da via, CAP, città). Potrebbero anche essere tenuti e collassati in un solo attributi (ad es. indirizzo stringa unica).

Trasformazione delle relazioni

Molti a molti: si devono necessariamente trasformare in relazioni. Le relazioni così formate avranno la chiave primaria costituita dall'insieme degli attributi di PK delle due relazioni che associa più tutti i campi necessari a descrivere gli attributi di associazione.

Uno a uno: inserisco all'interno dell'una o dell'altra relazione la chiave esterna che riferisce all'altra entità. Dove definisco la chiave esterna metto anche gli attributi di relazione. Di solito bisogna pensare al tipo di query che verrà effettuato in modo da minimizzare il numero di join. Bisognerebbe cercare di evitare valori nulli (quindi se una relazioni ha cardinalità (0;1) (1;1) conviene mettere la chiave sterna a destra).

Uno a molti: inserisco all'interno della relazione che sta dalla parte con cardinalità massima 1 la chiave esterna (tra persona e indirizzi su indirizzi).

Ina caso di associazione tra più di 2 tabelle è necessario ricorrere al caso molti-a-molti illustrato qui sopra.

 
 

 
 

 
 

 
 

Contenuti gestiti da Michele Bernardi. Non mi assumo alcuna responsabilità sulla correttezza di questi dati, usateli a vostro rischio e pericolo.

Basi di dati: PL SQL e PLpgSQL

Esistono due sistemi per estendere le funzionalità dei RDBMS:

1. Estensioni native al linguaggio SQl (caso di PL SQL (Oracle) e PLpgSQL (Postgre))
   
2. Linguaggio "ospitato" (ad esempio un linguaggio come PYTON che viene interpretato dal motore del DB a cui viene aggiunta la possibilità di avere statement SQL
   

PLSQL

SELECT INTO

: per scrivere il valore di una select che ritorna un solo record in variabili utilizzabili:
SELECT
Campo1, Campo2
INTO
Variabile1, variabile2 FROM….

CURSORE

: Una struttura dati che ci permette di navigare tra i record ritornati dalla SELECT (navigare solo in avanti).
DECLARE CURSOR
NomeCursore
FOR SELECT ….
OPEN
NomeCursore -> Apre il cursore e lo posiziona sul primo record
FETCH
NomeCursore
INTO
Var1, Var2, ecc.-> Scrive il record attuale dentro a delle variabili e passa al record successivo
CLOSE
NomeCursore -> Chiude il cursore (e dealloca le risorse)
Una variabile mostra lo stato del cursore (SQLCODE)

Programmi PLpgSQL

Un programma PL SQL è diviso in blocchi

Un header (con dichiarazione variabili, ecc.)

Un corpo con i comandi PL SQL e SQL-DML (SELECT INSERT UPDATE DELETE (CRUD)). Ha una gestione degli errori

DECLARE
Header
BEGIN
Corpo del programma
EXCEPTION
Gestione dell'eccezione
END;

Gestione Eccezioni

La gestione dell'errore può essere puntuale per tipo di errore:
BEGIN
Istruzioni
EXCEPTION
WHEN
TipoException
THEN
IstruzioniDiGestioneErrore
[WHEN AltroTipoException
THEN
IstruzioniDiGestioneErrore]
END

Per avviare una funzione posso usare RUN
nomeFunzione oppure SELECT
nomeFunzione

Il PLSQL è organizzato a blocchi

Sono ammessi tutti i tipi di dati SQL + tipi estesi

Sono ammessi tutti i comandi DML (DDL no, ma posso farli come DML sul catalog)

Posso dichiarare una variabile "come un certo attributo di una certa tabella" (il tipo sarà: NomeTabella.NomeCampo%TYPE) oppure come un record di una certa tabella (il tipo sarà: NomeTabella%ROWTYPE)

Esempi:

DECLARE
x NUMBER;
y VARCHAR(20);

oppure:

DECLARE
x tabella.campo%TYPE;

DECLARE
x tabella%ROWTYPE; (tipo di dati "riga": come riga della tabella specificata)

Le variabili dichiarate sono nulle fino ad un'assegnazione. L'operatore di assegnazione è :=

Cicli di controllo

IF
condizione1
THEN
istruzioni
[ELSEIF
condizione2
THEN
istruzioni]
[ELSE
istruzioni]
END IF

LOOP
istruzioni
EXIT WHEN
condizione (uscita dal loop)
END LOOP

WHILE
condizione
LOOP
istruzioni
END LOOP

FOR
var
IN
inizio fine
LOOP
istruzioni
END LOOP

Esempio (con tabella T1):

DECLARE

a T1.e%TYPE;
b T1.f%TYPE;
CURSOR
T1Cursor
IS
SELECT
e, f
FROM
T1
WHERE
e < f
FOR UPDATE; /*Con FOR UPDATE il cursore é aggiornabile*/

BEGIN

OPEN
T1Cursor;

LOOP
FETCH
T1Cursor
INTO
a, b;
EXIT WHEN
T1Cursor%NOTFOUND;
DELETE FROM
T1Cursor
WHERE CURRENT OF
T1Cursor; /*permesso solo se ho FOR UPDATE nel CURSORE*/
INSERT INTO
T1
VALUES (b, a); /*Li ho ri-scritti invertiti*/
END LOOP;

CLOSE
T1Cursor;

END;
.
run;

Posso incapsulare in procedure o funzioni (le funzioni hanno un valore di ritorno)

Posso passare parametri (anche con IN o OUT (o INOUT default) per definire se sono parametri passati o solo che ritornano)

Posso non specificare nome per i parametri (in tal caso si chiameranno $1, $2, …, $n)

Posso associare le procedure a dei trigger

CREATE OR REPLACE PROCEDURE
NomeProcedure (
param1 tipoParam1,
…
paramN tipoParamN
) AS
BEGIN
istruzioni
END

Per richiamarla:

BEGIN
NomeProdecure(param1,…paramN);
END;

Le funzioni hanno valore di ritorno: CREATE FUNCTION
NomeFunzione
RETURN
Tipo
AS…

Si può fare l'OVERLOAD di funzioni, non si può dare valori di default ai parametri

Operatore di concatenamento di stringhe é: ||

TRIGGER

Le funzioni invocate da un trigger hanno uno specifico tipo di ritorno (TRIGGER). Es:
CREATE OR REPLACE FUNCTION
NomeProcedure
RETURN TRIGGER AS …
Questo mi permette di avere all'interno del corpo le due variabili di tipo riga NEW (in caso di "ON INSERT" o "ON UPDATE") e OLD (in caso di UPDATE o DELETE). Se la funzione è per INSERT o UPDATE alla fine dovrò fare "RETURN NEW" (oppure "RETURN OLD" oppure "RETURN AltraRiga" oppure "RETURN NULL" (=nessuna riga (in caso di insert non inserisce niente!))

Per attivare la funzione come un trigger basterà poi la sintassi già vista. Es di trigger per riga su INSERT:
CREATE TRIGGER
NomeTrigger
BEFORE INSERT ON NomeTabella
FOR EACH ROW EXECUTE PROCEDURE
NomeProcedure();

Al posto di BEFORE INSERT posso mettere UPDATE o DELETE (oppure INSERT OR UPDATE, ecc.)

Dopo AS dovrei specificare il linguaggio. Es.: CREATE PROCEDURE
NomeProcedure
AS
$$
BEGIN
…
END $$ LANGUAGE plpgsql

La lezione bdd_2005112401 perde audio dal 33° minuto. Si vede un esempio d'uso di refcursor che non approfondisco.

Contenuti gestiti da Michele Bernardi. Non mi assumo alcuna responsabilità sulla correttezza di questi dati, usateli a vostro rischio e pericolo.

Basi di dati: Postgre SQL

Tutorial su http://www.postresql.org

 

SQL 99 compliant (tra cui transazioni)

Supporto ad estensioni (funzioni, operatori, funzioni aggregate, index methods, tipi di dati estesi)

Gestione del partizionamento (fisico sui dischi), gestioni di tipi di dato complessi

Gestione delle sequenze: campi che prendono automaticamente un valore in base ad un'espressione (tipo campo contatore, ma più flessibile). Il tipo di dato è serial (di default somma 1, ma posso definire come voglio: CREATE SEQUENCE SequenceName INCREMENT BY increment, più altre opzioni come [MINVALUE MinValue] [MAXVALUE MaxValue] [START StartValue] [CYCLE] (ripropone a ciclo i valori)). In realtà usa un campo int il suo default value è il valore di una funzione

Gestione degli array (SCONSIGLIATO: viola i principi dei DB relazionali!)

 

Il server è un servizio (postmaster)

Il servizio viene avviato con un utente apposito (che non deve essere un amministratore di sistema)

Basato sul concetto di cluster: uno storage fisico dei database (solo uno alla volta).

Per creare un nuovo cluster:

1. Creare una cartella (utente PostgreSQL deve avere permessi di lett/scr)
   
2. Initdb –D cartella
   

 

 

Comandi

Comandi per il server iniziano con "\"

\? help sui comandi

\h help su SQL

\l Lista dei DB

\d Descrittore (\dt elenco tabelle, \df elenco delle funzioni)

\z lista di accesso a tabelle, viste e sequenze con privilegi di accesso

 

Comandi SQL si scrivono normali

 

Ereditarietà

Potrei ereditare una tabella da un'altra (lo fa solo Postgre, però ammazza la portabilità): CREATE TABLE (campi diversi) INHERITS TabellaBase;

Esempio Capitali eredità Città. Inserendo una capitale automaticamente sarà presente anche la città corrispondente automaticamente (ed eliminandola viene eliminata anche dalla classe base, sembra scontato, ma non lo è!)!

 

 

 

IN PRATICA

Connessione dal client:

psql TemplateName [-h host] [-U utente]

 

Appena lanciato lo script di inizializzazione crea 2 database: Template0 e Template1

Ogni DB in PostgreSQL viene creato a partire da un Template (che può essere personalizzato aggiungendo delle tabelle di uso comune ad esempio)

Crea postgresql.conf -> Configurazione generale del cluster. pg_hba.conf -> gestione della politica degli accessi (funziona in maniera simile alla configurazione di un firewall)

 

(fare partire il server)

Script di creazione degli utenti (createuser)

Script di creazione di un database (createdb)

 

Manipolare una sequenza:

Nextval(sequenceName)

Currval(sequenceName)

Setval(sequenceName, value[, bool]) Il valore bool indica se il valore inserito influenza nextval

 

Manipolare le connessioni

Editando il file pg_hba.conf

Ho le connessioni di tipo: local (locali), host (via rete), hostssl (via rete, ma solo con SLL), hostnossl

Sono definite a livello di database e utente (posso definirle in generale per un utente e/o DB)

Posso anche ulteriormente filtrare per un Ip e una mask

 

Dump/Restore di un DB

Pg_dump [opzioni] nomeDB > filename

Pg_dumpall consente il dump dell'intero cluster

Restore: Psql nomeDB < filename

 

Basi di dati: Progettazione di schemi E/R

Approcci:

top-down: Parto da un concetto generale e lo sviluppo verso uno più particolare (es.: base di dati per l'università -> entità prof. e studenti in relazione -> entità studenti, corsi e prof. In relazione, ecc.), evolvo le specializzazioni gerarchiche e infine metto gli attributi.

bottom-up: opposto di top-down. Parto dagli attribute e li raggruppo in entità, pio creo le relazioni tra le entità… Lo schema NON evolve, ma si completa a differenza di top-down.

inside-out: Caso specific di bottom-up. Si allarga a macchia d'olio: parto da un particolare ed espando da quel punto. Personalmente mi sembra la più logica.

mixed: Appliazione mista delle 3 precedenti…

Di solito si usano mixed o inside-out.

Caratteristiche:

Leggibilità: uno schema é completato solo quando può essere definito leggibile. Evitare o minimizzare le intersezioni di linee (ad esempio delle relazioni). Scegliere nomi significativi.

Minimalità: Lo schema non dovrebbe contenere informazioni ridondanti o calcolabili. Se per ragioni di prestazioni o altro si decide di lasciare un dato derivato va segnalato!

Completezza: Uno schema deve contenere tutti i requisiti dei dati desiderati

Correttezza: evitare errori sintattici (violare le regole dei modelli E/R), oppure semantici (sbagliare l'uso di elementi (es: esprimere un'associazione con un'entità))

Come agire: selezionare dal testo in linguaggio naturale le parole chiave (diventeranno entità o attributi). Se in una frase compare più di un antità valutare dei legami (associazioni o generalizzazioni).

Basi di dati: MySQL

Estensioni allo standard SQL 99:

• REPLACE (DELETE + INSERT)
  
• Tipi di dato: medium int, set, enum, text, varchar
  
• Vincoli di attributo: AUTO_INCREMENT, BINARY, NULL, UNSIGNED, ZEROFILL
  
• Supporto a espressioni regolari e altro nelle SELECT
  
• || e && come alternativa a OR e AND
  

Mancanze:

• INTERSECT
  
• EXCEPT
  
• E' necessario prima rimuove i permessi ad un tabella prima di cancellarla (REVOKE)
  
• Vincoli di integrità SOLO con InnoDB
  

Le componenti di MySQL (per mia esperienza simili agli altri db):

Syntax parser: parser delle query
Query optimizer: ottimizzatore
Execution Component: Esecutore delle query
Storage manager: Gestisce l'IO
Recovery manager: gestisce la parte volatile (risultato di un'interrogazione i.e.)
Process manager: gestione Thread e connessioni di rete

Funzioni di librerie

Default porta 3306

DB di sistema:

mysql (informazioni del DB, tipo utenti e permessi)
information_schema: database del catalog (informazioni su database, tabelle, viste, ecc.)

Username administrator: root, nessuna password di default (da localhost)

Case INSENSITIVE. Comandi terminati da ;

Connessione al server: Mysql –u root –p

Comando SHOW (DATABASES / TABLES)

Comando DESCRIBE TABLE

Domando: USE databaseName

PRIVILEGI

memorizzati in mysql.users
Privilegi globali: *.*
A livello di database: dbname.*
A livello di tabella: dbname.tableName

Creando un privilegio creo un utente

Posso usare wildcard

SET PASSWORD nomeutente@nomehost = PASSWORD('NewPassword')

Creare un utente: GRANT
Privilege
ON
NomeDatabase.* TO
Utente@PuntoDiAccesso
IDENTIFIED BY
Password

MySQL differenzia i privilegi a seconda del computer di provenienza della richiesta: Utente@% indica l'utente da qualsiasi punto di accesso.

Contenuti gestiti da Michele Bernardi. Non mi assumo alcuna responsabilità sulla correttezza di questi dati, usateli a vostro rischio e pericolo.

Basi di dati: Raccolta e Analisi dei Requisiti

Raccolta dei requisiti:

usare quanto esiste: moduli, precedenti versioni

intervistare gli utenti principali

Raccolta documentazione

Identificazione di utenti e aree

Ordinamento dei requisiti:

Saranno informali (in linguaggio naturale), vanno organizzati

Costruire un glossario dei termini (risolvere omonimi (cambiare nome se due cose sono chiamate con lo stesso nome) e sinonimi (scegliere un solo modo per chiamare un entità/attributo))

Basi di dati: Modello Entity - Relationship

Tre principi fondamentali: Entity, Relationship, Attributo

Entity

Tipo entità: rappresenta una classe.
Ha un nome e si rappresenta con un rettangolo

 

Relationship (associazione)

Relazione tra due tipi di identità
Si rappresenta come un rombo con nome e due linee

Ha un grado che equivale al numero di entità coinvolte (binarie di solito)

Due entità possono essere connesse da svariate associazioni

Associazioni binarie possono essere "ricorsive" ("ad anello"): legano un'entità a se stessa, in tal caso specifico i ruoli (etichette sulle braccia dell'associazione):

Attenzione si tratta comunque di una relazione BINARIA (impiegato nell'esempio ha valenza doppia)

Attributo

E' una caratteristica di un entity... Ha dei valori

Si indica con in pallino collegato:

E' univoco per entità o relazione.

 

Altri 5 principi "secondari":

Cardinalità

Ovvero la coppia di numeri che legano un'entità ad relazione (cardinalità minima mc e massima MC):

Ovviamente da ogni lato della relazione è specificata una coppia di mc/MC (vedi più avanti)
Per indicare che non c'é limite metto n
Se le cardinalità massime sono 1 e 1 si chiama associazione "uno a uno". Se uno a più di uno allora sarà "uno a molti", se più di uno a più di uno: "molti a molti"

Cardinalità degli attributi:

Se la cardinalità é omessa si intende (1, 1)
Attenzione il modello relazionale non consente più di un valore (a meno di splittare in tabelle diverse)
Nel modello relazionale la cardinalità degli attributi può essere solo (0,1) o (1,1) perché deve osservare la prima forma normale

Identificatori

Sono l'equivalente del concetto di chiave primaria.
Permette di identificare l'entità E.
Negli schemi vengono rappresentati come un attributo con un pallino "pieno" (a differenza degli attributi normali che hanno un pallino "vuoto"

Gli identificatori possono essere semplici (composti da un solo attributo) o composti

Identificatori interni hanno solo attributi interni, esterni hanno anche degli attributi esterni (nel modello relazionale hanno delle foreign key nella propria chiave primaria). Da notare che se l'entità E1 ha l'entità E2 come parte del proprio identificatore devono essere unite da una relazione e dalla parte di E1 la cardinalità di tale relazione deve essere (1,1)

Se l'identificatore é esterno le entità sono dette deboli (espresse con pallino bianco e riferimento in identificatore:

Attributi composti

Attributi complessi costruiti da più "sottoattributi" vengono rappresentati all'interno di un piccolo ovale collegato all'entità (vedi schema dell'attributo qui sopra)

Gerarchia (=ereditarietà)

Rappresentano i legami tra tipi di entità diversi per indicare legami di sottoclasse/superclasse
Si esprimono con una freccia dalla sottoclasse alla superclasse:

Ovviamente esiste il concetto di ereditarietà anche in questi schemi ER, le proprietà ereditate sono: attributi identificatori associazioni

Le gerarchie possono essere trovate in due modi:
Generalizzazione: generalizzazione di un certo numero di sottoclassi in una superclasse comune (partendo dalle sottoclassi cane, gatto, ecc. generalizzo la superclasse animale)
Specializzazione: specializza la superclasse in sottoclasse (partendo dalla superclasse pane specializzo in "pane all'olio", "pane alle olive", ecc.)

Le gerarchie sono sottoposte a vincoli di totalità e disgiunzione.
Una gerarchia é parziale (P) se é possibile avere istanze del supertipo (che non non siano del sottotipo) = istanziabile, altrimenti é totale (T)
Disgiunzione: se un'istanza può essere può appartenere ad più di una sottoclasse é sovrapposta (O = overlapped) altrimenti é disgiunta (E)

Di default é totale esclusiva (TE)

Descrizione

Possono essere specificati dei vincoli o degli altri commenti
Per maggior chiarezza:
http://blacklight.gotdns.org/wiki/index.php/Introduzione_ai_database_e_modellazione_E/R

Contenuti gestiti da Michele Bernardi. Non mi assumo alcuna responsabilità sulla correttezza di questi dati, usateli a vostro rischio e pericolo.

Basi di dati: Progettazione concettuale di una base dati

3 livelli di progettazione:

concettuale (Entity / Relationship): indipendente dal DBMS che verrà scelta. Produce schema concettuale
logica: dipendente dal modello logico scelto (es. Rdbms per relazionale). Produce uno schema logico
fisica: dipendente dal motore di DB scelto (ad esempio se avessi scelto MySQL ci sono caratteristiche diverse da Oracle. Poduce la base di dati vera e propria

1. Raccolta e analisi dei requisiti -> produce un documento in linguaggio naturale
   
2. 
   

Modelli concettuali:

Utilizzati nella fase di progettazione, di solito sono grafici.

Il più famoso é Entity-Relationship

Si parte astraendo dalla realtà le caratteristiche
interessate dei dati che sappiamo di dover raccogliere.
Es. se voglio descrivere "studente" sceglierò caratteristiche come anno e matricola e trascurerò altre caratteristiche che, seppure proprie di ogni studente non importano alla figura di studente stesso (es. colore degli occhi).

I tre metodi di astrazione più usati sono:

Classificazione

Definire un concetto come classe degli oggetti che voglio descrivere caratterizzati da proprietà comuni.
Trovo le proprietà comuni tra le informazioni che devo classificare.
Es.= Presi 3 elementi (es. VW Golf, Fiat punto e Audi A3) troviamo le caratteristiche comuni per definire la classe a cui appartengono (es. automobile)

Relazione: is member of

Aggregazione

Vedo le parti (già classi, non caratteristiche!) che compongono gli oggetti che voglio descrivere e aggregando ottengo la classe che le contiene tutte.
Es.: Prendo carrozzeria, motore sono 3 classi con le proprie proprietà e le aggrego nell'unica classe automobile.

Relazione: Part of

Generalizzazione

Ricerca delle proprietà comuni per definire una forma di ereditarietà tra un sottoinsieme di informazioni (base) e un sovrainsieme (derivate)
Es. Prese le classi uomo e donna generalizzo le proprietà comuni nella superclasse individuo e lascio nelle sottoclassi uomo e donna solo quelle specifiche.
Es2. Presa la classe dipendente specifico (che è il contrario di generalizzo!) nelle sottoclassi programmatore e sistemista

Relazione: is a

Basi di dati: SQL: Trigger

Il trigger é un elemento dello schema che corrisponde ad una reazione attiva ad una azione sul database

Le Dasi di dati che comprendono i trigger sono dette ATTIVE

Evento-Condizione-Azione

Gli eventi possono essere scatenati da statement di aggiornamento della base dati (UPDATE, INSERT, DELETE)

Le condizioni sono delle "riduzioni" agli eventi

Le azioni sono statement SQL oppure statement tipici della BD

Un trigger risponde ad eventi su una sola tabella

SINTASSI ORACLE

CREATE TRIGGER
TriggerName
BEFORE/AFTER
INSERT/UPDATE/DELETE
ON [CampoCambiato
OF] TabellaCambiata

[FOR EACH ROW]

[WHEN (Condizione)]

BEGIN

[NEW] [OLD]…

END

NEW è una parola chiave: corrisponde alla nuova tupla, OLD alla vecchia

Nell'esempio Viene modificato il valore di Campo1 all'occorrenza del TRIGGER: … BEGIN NEW.Campo1 = Valore
END

Granularità

FOR EACH ROW = Granularità di tupla

(default ovvero per statement) = Granularità di statement SQL (1 evento per ogni statement sql anche se aggiorna più record)

Velocità di valutazione del trigger

Immediata: BEFORE o AFTER

Il TRIGGER viene eseguito nella stessa transazione della modifica che l'ha scatenato

Differita (non trovo corrispondenze in Oracle): valutata dopo la chiusura della transazione che lo solleva

Detached (non trovo corrispondenze in Oracle): gestione in una transazione separata

Inoltre c'é la possibilità che il trigger non venga chiamato immediatamente, ma in un momento spearato rispetto alla valutazione della condizione che lo solleva

Un TRIGGER potrebbe avere una doppia attivazione: es. "AFTER INSERT, UPDATE)

Quando usare un TRIGGER:

• Specificare vincoli di integrità complessi
  
• Gestione di dati derivati
  
• Mantenimento di consistenza delle viste materializzate
  

Qui c'é la guida ufficiale alla CREATE TRIGGER di ORACLE!

Qui Wikipedia Italia tratta il PL/SQL (linguaggio per scrivere le azioni dei trigger)

Basi di dati: SQL, Viste

Sono delle "tabelle virtuali" con un nome

Non é memorizzata: é una selezione di una tabella

Posso creare delle "viste materializzate": ovvero copio delle viste in una tabella, ma poi devo gestirne l'allineamento

CREATE VIEW

CREATE VIEW
NomeVista (Campo1, Campo2) AS SELECT ... [WITH [LOCAL|CASCADED] CHECK OPTION]

Può servire anche per limitare l'accesso a certi dati da parte di certi utenti (permessi solo sulla vista e non sulla tabella)

Una View può accedere ad un'altra View in cascata

Se non metto l'elenco degli attributi verrà usato il nome posizionale degli attributi della SELECT

AGGIORNABILITA':

In View con JOIN potrei avere Update problematici

In genere posso aggiornare solo viste definite su una sola tabella (e dovrebbe essere specificata anche la chiave primaria)

Alcune implementazioni di Database potrebbero essere più permissivi

La CHECK OPTION:

Se specificata posso aggiornare la vista solo se le righe aggiornate faranno ancora parte della vista

LOCAL: Controlla solo per la vista attuale

CASCADED (default): Controlla anche per le eventuali viste che compongono la vista corrente

Basi di dati: SQL: INSERT, UPDATE, DELETE

INSERT INTO

INSERT INTO
Tabella (Campo1, Campo2) VALUES (Valore1, Valore2)

Per specificare valori stringa devo metterli tra apici. Se voglio inserire un apice lo metto doppio: 'un po'' di fortuna'

Oppure posso usare il risultato di una SELECT:

INSERT INTO
Tabella (Campo1, Campo2) SELECT
Campo3, Campo4
FROM
Tabella2

Attenzione: l'inserimento é posizionale non per nome attributo

UPDATE

UPDATE
Tabella
SET
Campo = Valore [WHERE
Condizione]

Come argomento del SET posso anche mettere un'espressione. Ad esempio Campo = Campo + 1

Analogamente posso mettere una SELECT:

UPDATE
Tabella
SET
Campo = (SELECT .... WHERE
Condizione)

Posso anche settare al valore di default:

UPDATE
Tabella
SET
Campo = DEFAULT
WHERE
Condizione

DELETE

DELETE FROM Tabella WHERE Condizione

Attenzione cancella un record non un dato!

Basi di dati: SQL: Query annidate (e implementazione della divisione)

ANY

Usata per mettere in clausola WHERE il risultato di un'altra query o di un elenco.

Es.= SELECT
Campo1
FROM
Tabella1
WHERE
Tabella1.CampoX = ANY (SELECT ……..)

Es.= SELECT
Campo1
FROM
Tabella1
WHERE
Tabella1.CampoX = ANY (valore1, valore2)

IN

Stesso significato diversa sintassi

Es.= SELECT
Campo1
FROM
Tabella1
WHERE
Tabella1.Campo2
IN (SELECT ……..)

SUBQUERY CORRELATE

Mediante alias di tabelle/query posso passare parametri all'interno/esterno delle subquery

EXISTS

True se la subquery ritorna almeno 1 valore

NOT EXISTS

Opposto di EXISTS

Implementazione della DIVISIONE

Regola del controesempio: doppia negazione con NOT EXISTS

Es:

Gli impiegati che partecipano a tutti i progetti con budget > 50k

=

Tutti gli impiegati esclusi (quelli a cui manca almeno un progetto > 50k)

=

Tutti gli impiegati esclusi (gli impiegati a cui manca la partecipazione (ad un progetto da 50k))

Soluzione prof:

SELECT ID FROM Impiegato I WHERE NOT EXISTS

(

SELECT * FROM Progetto Y WHERE Budget > 50000 AND NOT EXISTS

(

SELECT * FROM Partecipazione P WHERE I.ID = P.ID AND Y.Prog# = P.Prog#

)

)

Basi di dati: SQL: operazioni insiemistiche

UNION

DEFAULT = Elimina i duplicati

ALL = Mantiene i duplicati

Es.:

SELECT Campo1 FROM Tabella1 UNION SELECT Campo2 FROM Tabella2

Se avessi messo "UNION ALL" avrebbe preso anche eventuali duplicati

INTERSECT

= Intersezione

Ritorna solo i record comuni alle 2 tabelle

EXCEPT

= Differenza

Except ritorna solo i record presenti nella prima tabella, ma non nella seconda

Basi di Dati: SQL, SELECT

L'istruzione per leggere dati da delle tabelle é. SELECT ed ha la seguente sintassi:

SELECT [DISTINCT] Campo1 [AS
AliasCampo1], Campo2 [AS
AliasCampo2] FROM
Tabella1 AliasTabella1, Tabella2
AliasTabella2 [WHERE Condizioni] [GROUP BY
Campo1, Campo2, … [HAVING
CondizioneAggregata]] [ORDER BY Campo1 [DESC], Campo2 [DESC, …]

DISTINCT Specifica che non vanno prese le ennuple duplicate nel risultato

Nella clausola FROM é possibile anche specificare delle clausole di JOIN:

[INNER] JOIN (corrisponde a Teta JOIN

Es: FROM Tabella1 INNER JOIN Tabella2 ON Tabella1.Campo1 = Tabella2.Campo2

NATURAL JOIN

Es: FROM Tabella1 NATURAL JOIN Tabella2

RIGHT, LEFT, [FULL] OUTER JOIN: Join esterni

OPERATORI AGGREGATI

COUNT: Numero di elementi

SELECT COUNT (*) FROM -> numero di record

SELECT COUNT (DISTINCT Campo1) FROM -> numero di valori diversi assunto da Campo1

SUM: Somma di valori (es. SELECT SUM(Campo1) FROM)

MAX: Valore più alto

MIN: Valore più basso

AVG: Valore medio

GROUP BY

Raggruppa i record secondo certi valori. I campi aggregati verranno calcolati per ciascuno di quei valori.
L'esempio seguente estrae lo stipendio più basso per ogni citta:

SELECT MIN(Stipendio) FROM
Dipendenti
GROUP BY
Citta

Se uso una GROUP BY nella select posso mettere solo gli attributi del GROUP BY e le aggregazioni. Es:
Sì: SELECT SELECT Citta, MIN(Stipendio) FROM
Dipendenti
GROUP BY
Citta

No: SELECT SELECT Nome, MIN(Stipendio) FROM
Dipendenti
GROUP BY
Citta

WHERE

Esprime una condizione della query

HAVING

Permette di esprimere condizioni sui valori aggregati. Praticamente é come una WHERE effettuata dopo il raggruppamento

Es.: La query seguente filtra i dirigenti, li raggruppa per dipartimento, prende la somma degli stipenti dei dirigenti per dipartimento e ritorna solo i record la cui somma degli stipenti é maggiore di 10000

SELECT Dipartimento, SUM(Stipendio) AS Totale FROM Dipendenti WHERE Grado = 'Dirigente' HAVING SUM(Stipendio) > 10000

ORDER BY

Ordina per uno o più campi il risultato.

Si può specificare le parole chiave ASC (default) per ordine crescente oppure DESC per descrescente.

Se si specificano più campi (separati da virgole) l'ordine sarà valutato da sinistra a destra (esattamente come ci si aspetterebbe!)

Basi di dati: SQL: DDL

Domini Elementari:

TIPI NUMERICI:

Esatti: integer, smallint, numeric, decimal (virgola fissa)
Approssimati (virgola mobile): real, double precision, float

STRINGA:

char (numero fisso di caratteri), varchar (numero variabile di caratteri). Si può associare la lunghezza massima

Bit: strighe composte di 0 o 1
Boolean: memorizza true o false

DATE:

Date: Data (passata in formato yyyyMMdd
Time: Ora, passata in formato hh:mm:ss
Timestamp: Date + Time

OGGETTI GRANDI:

Blob, Clob

 

TRE FAMIGLIE DI COMANDI: CREATE (creazione), DROP (cancellazione), ALTER (modifica)

CREATE

CREATE SCHEMA (implementato spesso come CREATE DATABASE)

CREATESCHEMA NomeSchema AUTHORIZATION Owner {ElementoSchema}

CREATE TABLE

CREATE TABLE
NomeTabella (
NomeAttributo
Dominio [DEFAULT
ValoreDefault] [Vincoli],
NomeAttributo
Dominio [DEFAULT
ValoreDefault] [Vincoli],
…)

Dominio può essere composto anche da tipo(dimensione). Es.: char(20).

Vincoli:
UNIQUE: Non possono esserci duplicati (ma potrebbero esserci nulli)
NOT NULL: Valori nulli non ammessi
PRIMARY KEY: Se metto questo vincolo su più campi faranno tutti parte dell'unica primary key

Per mettere un vincolo su un maggior numero di attributi posso metterli alla fine (es. primary con 2 campi):
CREATE TABLE
MyTable (campo1 tipo1, campo2 tipo2,
PRIMARY KEY (campo1, campo2))

VINCOLI DI INTEGRITÀ (INTRA-RELAZIONALI)

Posso applicare un vincolo "custom" mettendo dopo l'elenco dei campi (come per primary key qui sopra):
[CONSTRAINT
ConstraintName] CHECK condizione
Es: CONSTRAINT MioVincolo CHECK campo IN ('value1', 'value2')
Es2: CONSTRAINT MioVincolo2 CHECK campo2 > 10

I vincoli CONSTRAINT sono rigorosamente INTRArelazionali. Se si desidera specificare un vincolo CHECK interrelazionale è necessario creare un ASSERTION (vedi più avanti). Per quanto ne so io le ASSERTION comunque non sono supportate dalla maggior parte dei motori relazionali, al massimo creare un trigger.

E' possibile specificare altri vincoli mediante CONSTRAINT, come ad esempio il vincolo UNIQUE.
Nell'esempio seguente non sono Campo1 e Campo2 a dover essere UNIQUE, ma la loro combinazione:
[CONSTRAINT
MyConstraint] UNIQUE (Campo1, Campo2)

Perché specificare la parte opzionale "CONSTRAINT
ConstraintName"? Perché successivamente sarà possibile fare un ALTER o un DROP riferendosi a tale nome.

VINCOLI INTER-RELAZIONALI

FOREIGN KEY: Integrità referenziale

Definito tra due tabelle: tabella che riferisce (es.Indirizzo, con IDPersona) e riferita (es. Persona con campo ID)
La tabella riferita dovrebbe avere sui propri attributi il vincolo UNIQUE

[CONSTRAINT
ConstraintName] [FOREIGN KEY
MioCampo] REFERENCES altroCampo(altraTabella))
Se lo appongo dopo la dichiarazione di un campo basta REFERENCES (es: mioCampo mioTipo REFERENCES altroCampo(altraTabella))
Se la chiave è multi-valore sono costretto a metterlo alla fine della tabella: FOREIGN KEY (campi) REFERENCES tabella (altricampi)

Posso specificare delle azioni tipo:
REFERENCES altroCampo(altraTabella) [ON DELETE/UPDATE] CASCADE/SET NULL/SET DEFAULT/NO ACTION

ON DELETE: alla cancellazione del record nella tabella principale cancello anche i record riferiti
ON UPDATE: all'aggiornamento del campo primary key della tabella riferita
    CASCADE: Il cambio della chiave primaria cambia anche le chiavi esterne (o cancello)
    SET NULL: Se cancello o cambio imposta a NULL le foreign keys
    SET DEFAULT: Imposto le foreign key al valore di default
    NO ACTION: (default) Rifiuto il cambiamento/cancellazione

La parte ON DELETE/UPDATE è opzionale: se tralasciata si intendono entrambe.

CREATE ASSERTION

Del tutti analogo a mettere un CONSTRAINT all'interno di una CREATE TABLE, ma con la possibilità di specificare vincoli inter-relazionali:
CREATE ASSERTION
AssertionName …
Es: CREATE ASSERTION
MyAssertion
CHECK
Tab1.Campo1 > SELECT MAX(Campo2) FROM Tab2

CREATE DOMAIN

Permette di creare un dominio partendo da uno esistente
CREATE DOMAIN
NewDomainName
AS
tipoBase [DEFAULT
valore1] [CHECK (condizione)]

Potrei creare un dominio intero compreso tra 10 e 100 con valore di default 20:
CREATE DOMAIN
MyDomain
AS int DEFAULT
20
CHECK (VALUE >= 10 AND VALUE <= 100)

ALTER

ALTER TABLE
MyTable
ALTER COLUMN MyColumn
CAMBIAMENTO

Esempi di possibili cambiamenti:
tipo di dati di una colonna ALTER TABLE
MyTable
ALTER COLUMN MyColumn NewType
default di una colonna ALTER TABLE
MyTable
ALTER COLUMN MyColumn
SET DEFAULT
NuovoDefault
eliminazione valore di default: ALTER TABLE
MyTable
ALTER COLUMN MyColumn
DROP DEFAULT
aggiunta/rimozione di una colonna: … ADD/DROP COLUMN ColumnName (se ADD specificare tipo, eventuali vincoli, ecc.)
aggiunta/rimozione di un vincolo (CON NOME!) ADD/DROP CONSTRANT
ConstraintName (se ADD definizone del vincolo)

DROP

DROP SCHEMA (DROP DATABASE) DatabaseName
DROP TABLE
TableName
DROP DOMAIN
DomainName
DROP VIEW
ViewName
DROP ASSERTION
AssertionName
DROP CONSTRAINT
AssertionName

Con opzioni:
RESTRICT (default): Se ci sono oggetti che ne dipendono (es una vista sulla tabella che voglio cancellare) NON li cancella
CASCADE: Cancella gli oggetti che ne dipendono

CATALOG

Le informazioni sugli oggetti di un database vengono raccolte all'interno di una BD relazionale detta CATALOG (riflessività)
Maggiori informazione con la definizione di trigger e viste

Basi di dati: SQL, Storia

Storia:

Nasce da una base iniziale chiamata: SEQUEL (leggi: sicuell) = Structured English QUEry Language

'70-'80: Sviluppato per il DBMS relazionale System R (IBM)

'86: primo standard di SQL: ANSI SQL: buone funzionalità DML, scarse DDL

'89: Estensione dello standard (integrità referenziale)

'92: Aggiunte molte funzionalità DDL (detto anche SQL 2)

Duemilaequalcosa: Nuova versione (SQL 99 o SQL 3): trigger, tipi composti, viste ricorsive, supporto per oggetti di grandi dimensioni

 

Non tutti i DBMS coprono tutti gli standard (o non completamente)

Basi di dati: Equivalenze ed Ottimizzazioni di espressioni algebriche

Atomizzazione delle selezioni: posso suddividere una selezione con due condizioni in AND nella selezione di una selezione con ciascuna avente una delle condizioni iniziali.

Selezione commutativa: l'operatore di selezione gode di proprietà commutativa.

Idempotenza delle proiezioni: posso ottenere una qualsiasi proiezione di una relazione come una proiezione di una proiezione della relazione stessa (a patto che comunque contenga tutti i valori.

Ottimizzazioni:

Anticipazione delle selezioni rispetto al join. Fare il join tra selezioni piuttosto che la selezione di un join!

Conversione di una sequenza (σ, x) in un join: ovvero evitare prodotti cartesiani! Piuttosto cercare di unire una selezione con il prodotto cartesiano in una join!

Anticipazione della proiezione rispetto al join: facile: "portarsi dietro" meno attributi é più "leggero"

L'intersezione insiemistica si può realizzare come una join di una tabella su se stessa!!!

Basi di dati: Algebra relazionale

Linguaggi procedurali, operazioni su insiemi e su relazioni

Il risultato di un'interrogazione é sempre una relazione

Gli operatori si distinguono in fondamentali e derivati (derivabili dalla composizione di più operatori fondamentali)

Operatori Insiemistici

Unione (fondamentale): funziona se le due relazioni hanno schemi con lo stesso grado, stesso nome e dominio degli attributi

Compatibilità all'unione: viene accettato solo se le relazioni hanno schemi con lo stesso grado e dominio

In caso di valori presenti in entrambe le relazioni vengono prese una volta sola

Differenza (fondamentale): NON COMMUTATIVA!

r-s Il risultato é una relazione che contiene le ennuple di r ad eccezione di quelle presenti in s

Intersezione (derivato): che potrei esprimere come differenza intersezione tra r e s = r-(r-s)

Unari

Selezione (σ) (fondamentale): rende una relazione contenente solo le ennuple che soddisfano i criteri richiesti con gli attributi richiesti

I criteri vengono espressi mediante una formula proposizionale

Nelle formule proposizionali posso usare nomi di attributo, costanti, operatori di confronto e operatori logici (AND OR NOT)

Ridenominazione (ρ): Cambia nome ad un attributo.

Esempio: ρ_{Nuovonome←Nome}(Tabella)

Proiezione (π) (fondamentale): riduce il numero di attributi della relazione operando senza alterare le ennuple

La cardinalità potrebbe variare (potrebbero esserci duplicati presi una sola volta se non é compresa una sottochiave)

Binari

Prodotto Cartesiano (fondamentale): consente di combinare ennuple in relazioni diverse

Le due relazioni operando non devono avere attributi comuni schemi disgiunti)

Contiene tutte le possibili combinazioni di tutte le ennuple

Cardinalità: prodotto delle cardinalità, grado = somma degli gradi

Teta Join (θ) (derivato): Come prodotto cartesiano, ma nel risultato ci sono solo le ennuple che hanno un significato dal punto di vista della combinazione logica

Se l'operatore per la combinazione logica é l'uguale allora prende il nome di equijoin

Esempio: tesista JOIN professore ON Relatore = CognomeP AND EtaT > EtaP

Sono joinable le ennuple che partecipano al join (hanno un record dall'altra parte), sono dangling (pendenti) le altre. Un join si dice completo se non ha record dangling

Natural Join (derivato): Come Teta Join, ma prende solo una volta gli attributi comuni (se ho Nome da entrambe le parti lo prende da una sola parte). Ovviamente gli attributi con lo stesso nome si intendono automaticamente parte di una Equijoin

Divisione (derivato):

Una delle relazioni deve avere gli attributi che siano un sottoinsieme dell'altra... Simbolo: ÷

Il risultato é una relazione che contiene gli attributi NON comuni. Le ennuple prese sono quelle che hanno un riferimento per tutte le righe della relazione-sottoinsieme

Outer Join (derivato):

Come la teta join, ma prende anche ennuple dangling mettendo null degli altri valori.

Left outer join: prende tutte le ennuple della prima relazione ed eentuali corrispondenze (oppure NULL per quei valori)

Right outer join: prende tutte le ennuple della seconda relazione

Full outer join: prende le ennuple di entrambe le relazioni

Riepilogo simboli:

Basi di dati: Forme Normali

Tipi di attributi:

semplici: contengono una sola informazione

multivalore: possono contenere un insieme di valori

strutturati: contengono un insieme di valori

 

Prima forma normale

Ogni attributo deve essere semplice

(la tabella é flat: non multidimensionale)

Deve esistere una chiave primaria che ha valori univoci per ogni record

Seconda forma normale

Se è in prima forma normale e…

Tutti i campi devono dipendere dall'intera chiave primaria e non solo da una sua parte

Quindi se nella chiave di Ordine ci sono IDProdotto e IDCliente non ci potrà anche essere un campo con la descrizione del prodotto (dipenderebbe solo da IDProdotto)

Dipendenza funzionale

Il campo A campo dipende funzionalmente dal campo B se a parità di valore in A so che dovrò aspettarmi lo stesso valore di B

Si scrive: B -> A

Terza forma normale

Se è in seconda forma normale e…

Tutti i campi dipendono SOLO dalla chiave primaria

La forma normale di Boyce e Codd

Uno schema si trova in forma normale di Boyce e Codd se non esiste una dipendenza funzionale tra i campi nella cui parte sinistra c'è una parte della chiave. In altre parole se una parte della chiave può essere dedotta da campi non-chiave

Normalizzazione

Per normalizzare bisogna tenere presente due principi, la decomposizione:

1. Non si deve causare perdita di informazioni
   
2. Bisogna conservare le dipendenze
   

Basi di dati: Vincoli

Vincoli di integrità

Servono a garantire la consistenza della BD

I vincoli di integrità sono una funzione booleana sulla ennupla

Intrarelazionali: Vincoli all'interno della stessa relazione

Interrelazionali: insistono tra più relazioni

Vincoli di chiave

Un insieme k é superchiave di una relazione R. Se R non contiene 2 ennuple che hanno valori uguali su quegli attributi.

K é chiave se: é superchiave e non contiene nessun'altra superchiave.

Una delle chiavi viene scelta per identificare le ennuple ed é detta Chiave Primaria (Primary Key)

La chiave primaria non può contenere valori nulli in alcun campo.

Esistono chiavi primarie naturali (campi che possono già fare da chiave primaria). Es. matricola, codice fiscale

Nelle rappresentazioni tabellari la chiave primaria é sottolineata, la chiave esterna di solito sottolineata con linea tratteggiata.

Vincoli interrelazionali

Vincoli di integrità referenziale

Un vincolo di chiave esterna obbliga i campi interessati ad assumere valori appartenenti alla chiave primaria della tabella collegata

Basi di dati: Il modello relazionale

Proposto da E.F. Codd nel 1970

Basato sulla relazione matematica sottoinsieme del prodotto cartesiano tra più domini (insiemi di dati)

Definisce dei vincoli sui dati e un algebra relazionale (che viene implementata in SQL)

La nozione di tabella é l'implementazione del modello relazionale

 

Una BD é una "collezione di tabelle" sulle quali insistono dei vincoli

Ogni riga rappresenta un oggetto (le righe sono ennuple di valori ordinati)

Ogni colonna prende i valori da un dominio

Ogni tabella contiene un sottoinsieme del prodotto cartesiano dei possibili valori

Dominio = insieme dei valori possibili per una colonna

Una relazione (tabella) su n domini ha grado n

Il numero di righe é la cardinalità della relazione

Un attributo descrive il ruolo del dominio nella relazione (alias ...)

 

Ogni tabella ha un nome univoco

Non ci sono mai due righe identiche (orientamento ai valori)

L'assenza di un valore si esprime con un valore fuori dal dominio che é NULL

NULL non ha semantica (non ha un significato intrinseco)

Basi di dati: Modelli dei dati

Insieme dei costrutti utilizzati per definire la BD

Notazione per interrogare i dati

L'uso di un DBMS é il mezzo attraverso cui si realizza l'alterazione dei dati

 

Tabelle: insieme di record (righe) il cui ordine non é importante.

Relazioni: basate su collegamento di indici

Il modello relazionale é completamente orientato ai valori (si usano quelli per le relazioni)

 

Modelli concettuali (in fase di progettazione é l'astrazione della realtà per rappresentarla in un DB)

Entità – Relazione (entità associazione)

 

Modelli logici (riflette una particolare organizzazione dei dati, é un modello formale)

Relazionale

A Oggetti

Reticolare

Gerarchico

 

Schema (descrizione delle classi degli oggetti che rientrano nella BD)

Intenzionale: descrizione delle caratteristiche

Lo schema cambia raramente

 

Istanza

Estensione: descrizione mediante l'elenco degli oggetti

Ovvero i record

 

Stato

Fotografia della BD in un certo momento

Il DBMS valida le istanze contro lo schema

 

Il DBMS gestisce la memorizzazione fisica, ma garantisce la stessa rappresentazione logica

 

Una vista é una porzione della BD

 

Operazioni fondamentali:

DDL: Data Definition Language

DML: Data Manipulation Language

VDL: View Definition Language (sottoinsieme di DDL)

 

STORIA

Anni '60: primi DBMS gerarchici e reticolari

Anni '70: introdotto il modello relazionale

Anni '70-'80: Si diffondono i RDBMS

Anni '80-'90: Nascono i DB a oggetti

1990: RDBMS diventano uno standard aziendale

 

Evoluzioni:

Molto piccolo: supportare dispositivi piccoli (cellulari)

Molto grandi: Terabyte e terabyte

Architetture Client Server e Multi-tier

Gestione di informazioni non testuali (multimediali)

Integrazione di informazioni (integrazione fra sistemi diversi ed eterogenee)

Basi di dati: Premesse

Dato é la registrazione di un informazione.

Il dato va interpretato in un contesto interpretativo per diventare informazione (esempio: 10 é un dato; mancano 10 minuti per la fine della lezione é un'informazione)

La dase di dati é una rappresentazione dell' Universo del discorso (UoD)

DBMS = Sistema di gestione delle basi di dati (DataBase Management System)

Il ciclo di vita di una base di dati ha 4 attività primarie:

1. Definizione
   Ossia capire quali dati fanno parte dell' Universo del discorso (quali dati e di che tipo vanno nella BD (base di dati))
   
2. Costruzione
   Popolare la dbb (caricare i dati)
   
3. Manipolazione
   Accesso alla base di dati: letture, scritture, cancellazione e la generazione di report sui dati
   
4. Condivisione
   Gestione degli accessi concorrenti da più utenti e programmi diversi
   

DataWareHouse (magazzini di dati) = particolari basi di dati costruiti per attività di analisi dei dati nel tempo (ad esempio trend di un'azione nel tempo)

 

Primi DBMS erano Gerarchici

Poi Reticolari

Relazionali = RDBMS attuale standard

 

Il DBMS deve garantire accesso efficiente alla BD (implementata mediante definizione di Indici)

Gestione delle transazioni (controllo sulla Concorrenza, Backup e Recovery (salvataggio e ripristino)

Gestione delle interrogazioni (di ricostruire informazioni tra più tabelle)

Protezione dei dati Differenziazione degli accessi (alcuni possono leggere, scrivere, altri no)

 

Fa parte del sistema di BD anche la descrizione della struttura e dei vincoli sui dati che sono contenuti (il Catalogo).

 

L'approccio "base di dati" semplifica la gestione dei dati rispetto a soluzioni custom (gestione concorrenza, archiviazione standard, ecc. ecc.)

Viste utente: possibilità di personalizzare la visualizzazione dei dati a seconda dell'utente connesso

 

Figure professionali:

DBA = Database Administrator

DB Designer

Basi di dati: Introduzione

Libro di testo: R. Elmasri, S.B. Navathe, Sistemi di basi di dati - Fondamenti (5 ed.), edizione italiana a cura di Silvana Castano; Pearson-Addison Wesley, 2007.

Estensione sulla sicurezza disponibile in copisteria di viale Umbria

Sito del corso: http://islab.dico.unimi.it/bdlab2/

Sito delle video-lezioni:

Durata delle video lezioni: (stimata: 45 ore)