1 / 67

Basi di dati (modulo 2)

Basi di dati (modulo 2). Prof. Giovanni Giuffrida Stanza: 362 tel: 095 738 3051 e-mail: giovanni.giuffrida@dmi.unict.it. Programma. Progettazione di basi di dati Il modello entity-relationship (ER) Progettazione Logica, fisica e concettuale Oggetti SQL Vincoli, Viste, Procedure Trigger

Patman
Download Presentation

Basi di dati (modulo 2)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Basi di dati(modulo 2) Prof. Giovanni Giuffrida Stanza: 362 tel: 095 738 3051 e-mail: giovanni.giuffrida@dmi.unict.it Basi di dati II

  2. Programma • Progettazione di basi di dati • Il modello entity-relationship (ER) • Progettazione • Logica, fisica e concettuale • Oggetti SQL • Vincoli, Viste, Procedure • Trigger • Esempi su DB commerciali: Oracle e DB2 • Esercitazioni Basi di dati II

  3. Programma, cont. • Normalizzazione di schemi relazionali • Aspetti sistemistici dei DBMS • Cataloghi, schemi • Transazioni • Piani di esecuzione • SQL Avanzato • Sequenze • Viste materializzate • Query multidimensionali (OLAP) • Query ricorsive • Esercitazioni Basi di dati II

  4. Programma, cont. • Linguaggi procedurali e interfacce • PHP, JDBC2, QBE • Amministrazione di basi di dati • Controllo accessi • Monitoring • Tuning Basi di dati II

  5. Vincoli d’integrita’ • Riguardano i valori ammissibili degli attributi di una tupla • Vincoli Intrarelazionali: nell’ambito della stessa relazione • Vincoli Referenziali (o Interrelazionali): tra diverse relazioni • Vengono controllati durante le tre possibili operazioni di modifica SQL • INSERT,DELETE e UPDATE • Devono essere sempre soddisfatti altrimenti la transazione fallisce • Oppure, l’utente puo’ opzionalmente definire della azioni (correttive) da intraprendere per ripristinare l’integrita’ Basi di dati II

  6. A cosa servono i vincoli d’integrita’ • Migliorare la qualita’ dei dati • Arricchire semanticamente la base di dati • La loro definizione e’ parte del processo di progettazione del data base • Usati internamente dal sistema per ottimizzare l’esecuzione Basi di dati II

  7. Esami Studente Voto Lode Corso 32 01 30 e lode 02 276545 787643 03 276545 739430 24 04 27 27 e lode e lode Studenti Matricola Cognome Nome 276545 Rossi Mario Neri Piero 787643 787643 787643 Bianchi Luca 787643 Esempio di DB semanticamente errato 32 739430 Basi di dati II

  8. Vincoli sui valori della tupla • NOT NULL • implicito se l’attributo fa parte di una chiave primaria • Esempio: campo matricola nella tabella Studente • DEFAULT (Costante|NULL) • assegna un valore di default per ogni inserimento se non specificato • Esempio: DEFAULT CURRENT DATE • CHECK Condizione • Dove “Condizione” e’ un’espressione booleana per il controllo di attributi, costanti ed espressioni • Dev’essere valutata True per la corretta esecuzione della transazione • Vincoli sul dominio: • Esempio: specifica i valori ammissibili nell’attributo Voto della tabella Esami: • Voto NOT NULL • (18  Voto AND Voto  30) • Vincoli basati su piu’ attributi • (Lode <> ‘Si’) OR (Voto = 30) Basi di dati II

  9. Definizione vincoli intrarelazionali • PRIMARY KEY [Nome Chiave] “(”Attributo{,Attributo} “)” • gli attributi devono essere dichiarati tutti NOT NULL • Esempio: Attributo Matricola nella relazione Studenti • UNIQUE “(”Attributo{,Attributo} “)” • definisce una chiave con uno o piu’ attributi • Esempio: (Nome,Cognome,DataDiNascita) • Nota: • Nome not null unique,Cognome not null unique • E’ diverso da: • Nome not null,Cognome not null,UNIQUE (Nome, Cognome) Basi di dati II

  10. Vincoli d’integrita’ referenziali (o interrelazionali) • Tuple di relazioni diverse sono correlati per mezzo del valore di chiavi (primarie) • Servono a garantire che i valori in una certa tabella facciano riferimento a valori reali di un’altra tabella • Esempio: • Esami(...,Matricola), Studenti( Matricola,...) Basi di dati II

  11. Infrazioni Codice Data Vigile Prov Numero 34321 1/2/95 3987 3987 MI 39548K 3295 53524 4/3/95 3295 TO E39548 3295 64521 5/4/96 3295 PR 839548 73321 5/2/98 9345 9345 PR 839548 Vigili Matricola Cognome Nome 3987 Rossi Luca 3987 3295 Neri Piero 3295 9345 Neri Mario 9345 7543 Mori Gino 3987 3295 3295 9345 Basi di dati II

  12. Infrazioni Codice Data Vigile Prov Numero 34321 1/2/95 3987 MI MI 39548K 39548K 53524 4/3/95 3295 TO TO E39548 E39548 64521 5/4/96 3295 PR PR 839548 839548 73321 5/2/98 9345 PR PR 839548 839548 Auto Prov Numero Cognome Nome MI 39548K Rossi Mario TO E39548 Rossi Mario PR 839548 Neri Luca MI 39548K TO E39548 PR 839548 Basi di dati II

  13. Vincolo di integrità referenziale • Un vincolo di integrità referenziale(“foreignkey”) fra gli attributi X di una relazione R1 e un’altra relazione R2 impone ai valori su X in R1 di comparire come valori della chiave primaria di R2 • Nell‘esempio precedente: • vincoli di integrità referenziale fra: • l’attributo Vigile della relazione INFRAZIONI e la relazione VIGILI • gli attributi Prov e Numero di INFRAZIONI e la relazione AUTO • NULL per evitare il controllo del vincolo Basi di dati II

  14. Infrazioni Codice Data Vigile Prov Numero 34321 1/2/95 3987 MI 39548K 53524 4/3/95 3295 TO E39548 TO E39548 64521 5/4/96 3295 PR 839548 73321 5/2/98 9345 PR 839548 Auto Prov Numero Cognome Nome MI E39548 Rossi Mario TO F34268 Rossi Mario E39548 PR 839548 Neri Luca TO Violazione di vincolo di integrità referenziale Basi di dati II

  15. Integrità referenziale e valori nulli Impiegati Matricola Cognome Progetto 34321 Rossi IDEA 53524 Neri XYZ 64521 Verdi NULL 73032 Bianchi IDEA Progetti Codice Inizio Durata Costo IDEA 01/2000 36 200 XYZ 07/2001 24 120 BOH 09/2001 24 150 Basi di dati II

  16. Azioni compensative • Esempio: Viene eliminata una tupla causando cosi' una violazione • Tre possibili azioni • Rifiuto dell'operazione • ON DELETE NO ACTION (la piu’ diffusa nei DBMS) • Eliminazione in cascata • ON DELETE CASCADE: cancella tutte le tuple con valori della chiave esterna corrispondenti alla chiave primaria delle tuple cancellate • Introduzione di valori nulli • ON DELETE SET NULL assegna il valore NULL agli attributi della chiave esterna Basi di dati II

  17. Impiegati Matricola Cognome Progetto 53524 Neri XYZ 34321 Rossi IDEA 53524 Neri XYZ 64521 Verdi NULL 73032 Bianchi IDEA Progetti Codice Inizio Durata Costo IDEA 01/2000 36 200 XYZ 07/2001 24 120 XYZ 07/2001 24 120 BOH 09/2001 24 150 XYZ 07/2001 24 120 XYZ 07/2001 24 120 Rifiuto della cancellazione 53524 Neri XYZ • La transazione fallisce e XYZ non puo’ essere cancellato dalla relazione Progetti Basi di dati II

  18. Impiegati Matricola Cognome Progetto 53524 Neri XYZ 34321 Rossi IDEA 53524 Neri XYZ 64521 Verdi NULL 73032 Bianchi IDEA Progetti Codice Inizio Durata Costo IDEA 01/2000 36 200 XYZ 07/2001 24 120 XYZ 07/2001 24 120 BOH 09/2001 24 150 XYZ 07/2001 24 120 XYZ 07/2001 24 120 Eliminazione in cascata • La transazione termina e XYZ viene cancellato anche dalla relazione Impiegati Basi di dati II

  19. Impiegati Matricola Cognome Progetto 34321 Rossi IDEA 53524 Neri XYZ 64521 Verdi NULL 73032 Bianchi IDEA Progetti Codice Inizio Durata Costo IDEA 01/2000 36 200 XYZ 07/2001 24 120 XYZ 07/2001 24 120 BOH 09/2001 24 150 XYZ 07/2001 24 120 XYZ 07/2001 24 120 Introduzione di valori nulli NULL • La transazione termina e all’attributo Impiegati.Progetto viene assegnato NULL Basi di dati II

  20. Auto Prov Numero Cognome Nome MI 39548K Rossi Mario TO E39548 Rossi Mario PR 839548 Neri Luca Vincoli multipli su più attributi Incidenti Codice Data ProvA NumeroA ProvB NumeroB 34321 1/2/95 TO E39548 MI 39548K 64521 5/4/96 PR 839548 TO E39548 Basi di dati II

  21. Vincoli Interrelazionali, Sintassi • FOREIGN KEY [NomeChiaveEsterna]“(”Attributo{,Attributo} “)”REFERENCES TabellaRefON DELETE {NO ACTION,CASCADE,SET NULL} • dove per la TabellaRef e’ stata definita una chiave primaria • Quindi: impedisce l’inserimento di tuple con il valore della chiave esterna che non corrisponde ad un valore della chiave primaria della TabellaRef Basi di dati II

  22. Esempio Riassuntivo • CREATE TABLE Clienti ( CodiceCliente CHAR(3) UNIQUE NOT NULL, Nome CHAR(30) NOT NULL, Citta’ CHAR(30) NOT NULL, Sconto INTEGER NOT NULL CHECK(Sconto>0 AND Sconto<100), PRIMARY KEY pk_Clienti(CodiceCliente)) • CREATE TABLE Agenti ( CodiceAgente CHAR(3) UNIQUE NOT NULL, Nome CHAR(30) NOT NULL, Zona CHAR(8) NOT NULL, Supervisore CHAR(3), Commissione INTEGER) PRIMARY KEY pk_Agenti(CodiceAgente), CHECK (Supervisore  CodiceAgente OR Supervisore IS NULL) Basi di dati II

  23. Esempio Riassuntivo • CREATE TABLE Ordini( NumOrdine CHAR(3) NOT NULL, CodiceCliente CHAR(3) NOT NULL, CodiceAgente CHAR(3) NOT NULL, Data CHAR(8) NOT NULL, Prodotto CHAR(3) NOT NULL, Ammontare INTEGER NOT NULL CHECK (Ammontare > 100) PRIMARY KEY pk-Ordini (NumOrdine) FOREIGN KEY fk_ClienteOrdine (CodiceCliente) REFERENCES ClientiON DELETENO ACTION FOREIGN KEY fk_AgenteOrdine (CodiceAgente) REFERENCES Agenti ON DELETE NO ACTION Basi di dati II

  24. Viste (View) • Oltre alle tabelle di base che fanno parte dello schema si possono creare delle tabelle ausiliarie virtuali • Sono “virtuali” in quanto sembrano tabelle a tutti gli effetti ma sono delle relazioni “create al volo” • Utilizzate per vari scopi: • Semplificazione • Protezione dati • Scomposizione query complesse • Riorganizzazione dati secondo nuovi schemi • Etc. Basi di dati II

  25. Definizione VIEW • Sintassi creazione VIEW: CREATE VIEW NomeVista [“(” Attributo {,Attributo} “)”] AS Query-Select Basi di dati II

  26. Esempio definizione VIEW • CREATE VIEW MediaVoti (Matricola,Media)AS SELECT Matricola, AVG(Voto) FROM Esami GROUP BY Matricola • Esecuzione: SELECT * FROM MediaVoti Basi di dati II

  27. Le VIEW possono essere usate come tabelle • SELECT Nome, MediaFROM Studenti, MediaVotiWHERE Studenti.Matricola = MediaVoti.Matricola • Le VIEW possono essere distrutte alla pari di tabelle • DROP (TABLE | VIEW) Nome [RESTRICT|CASCADE] • Con RESTRICT non viene cancellata se e’ utilizzata in altre viste • Con CASCADE verranno rimosse tutte le viste che usano la View o la Tabella rimossa • Non tutti i sistemi permettono l’uso di RESTRICT e CASCADE • La distruzione di una VIEW non altera le tabelle su cui la VIEW si basa Basi di dati II

  28. Le VIEW possono essere usate come tabelle • Una VIEW puo’ essere definita sulla base di un’altra VIEW • Nelle prime versioni di SQL non era possibile modificare una VIEW tramite Insert, Delete, Update • Non piu’ vero nei nuovi DBMS (Vedremo dopo) • Che succede se una tabella usata in una VIEW viene alterata o cancellata (senza specificare RESTRICT o CASCADE)? • Dipende dal DBMS: • la VIEW viene marcata ‘inoperative’, oppure • La modifica/cancellazione viene negata • Etc. Basi di dati II

  29. Uso delle VIEW per query complesse • Semplificare query complesse • Esempio: non possiamo scrivere SELECT AVG(COUNT(*)) FROM AGENTI GROUP BY ZONE • AVG deve agire sui valori di un attributo. Basi di dati II

  30. Uso delle VIEW per query complesse • CREATE VIEW AgPerZona (Zona,NumAg)ASSELECT Zona,COUNT(*) FROM AGENTI GROUP BY Zona • SELECT AVG(NumAg)FROM AgPerZona • DROP AgPerZona Basi di dati II

  31. Uso delle VIEW per Sicurezza • CREATE VIEW EsamiPublici AS SELECT Corso,Voto FROM Esami • Data la tabella ClientiBanca(Nome,Indirizzo,Saldo) • CREATE VIEW ClientiInd AS SELECT Nome,Indirizzo FROM ClientiBanca Basi di dati II

  32. Mascherare l’organizzazione logica dei dati tramite VIEW • Immaginiamo la seguente tabella: • Agenti( CodiceAgente, Nome, Zona, Commissione, Supervisore) • Per riorganizzazione aziendale si decide di assegnare un Supervisore ad una zona intera invece del singolo agente 1) CREATE TABLE Zone (Zona CHAR(8), Supervisore CHAR(3)) AS SELECT DISTINCT Zona,Supervisore FROM Agenti 2) CREATE TABLE NuoviAgenti AS SELECT CodiceAgente,Nome,Zona,Commissione FROM Agenti 3) DROP Agenti 4) CREATE VIEW Agenti AS SELECT * FROM NuoviAgenti NATURAL JOIN Zone Basi di dati II

  33. Aggiornamento delle VIEW • Le operazioni INSERT/UPDATE/DELETE sulle VIEW non erano permesse nelle prime edizioni di SQL • I nuovi DBMS permettono di farlo con certe limitazioni dovute alla definizione della VIEW stessa • Che senso ha aggiornare una VIEW? Dopotutto si potrebbe aggiornare la tabella di base direttamente… Basi di dati II

  34. Aggiornamento delle VIEW, cont. • … utile nel caso di accesso dati controllato • Esempio: • Impiegato( Nome, Cognome, Dipart, Ufficio, Stipendio) • Il personale della segreteria non puo’ accedere ai dati sullo stipendio ma puo’ modificare gli altri campi della tabella, aggiungere e/o cancellare tuple • Si puo’ controllare l’accesso tramite la definizione della VIEW: • CREATE VIEW Impiegato2 ASSELECT Nome, Cognome, Dipart, UfficioFROM Impiegato • INSERT INTO Impiegato2 VALUES (…) • Stipendio verra’ inizializzato a Null • Se Null non e’ permesso per Stipendio l’operazione fallisce Basi di dati II

  35. Aggiornamento VIEW 2 • Immaginiamo la seguente VIEW:CREATE VIEW ImpiegatoRossiASSELECT * FROM Impiegato WHERE Cognome=‘Rossi’ • La seguente operazione ha senso: • INSERT INTO ImpiegatoRossi (…’Rossi’,…) Basi di dati II

  36. Aggiornamento VIEW 2, cont. • Ma che succede nel caso di: • INSERT INTO ImpiegatoRossi (…’Bianchi’,…) • In genere e’ permesso, finisce nella tabella base ma non e’ visibile dalla VIEW • Si puo’ controllare tramite l’opzione “WITH CHECK OPTION”:CREATE VIEW ImpiegatoRossiASSELECT * FROM Impiegato WHERE Cognome=‘Rossi’WITH CHECK OPTION • Adesso l’insert con ‘Bianchi’ fallisce, quella con ‘Rossi’ viene invece eseguita. Basi di dati II

  37. Aggiornamento VIEW 3 • Consideriamo il seguente caso: • Impiegato( Nome, Cognome, Dipart, Ufficio, Stipendio) • Dipartimenti( Dipart, Indirizzo) • CREATE VIEW IMP_IND ASSELECT Nome, Cognome, d.dipart, indirizzoFROM Impiegato i join Dipartimenti d ON i.Dipart=d.Dipart • Un INSERT sulla VIEW IMP_IND dovrebbe inserire su entrambe le tabelle base • In alcuni casi potrebbe inserire in una ma non nell’altra • In genere quest’operazione non e’ consentita • Alcuni DBMS consentirebbero l’INSERT se “Impiegati.Dipart” fosse una foreign key su “Dipartimenti.Dipart” e quest’ultima fosse chiave primaria Basi di dati II

  38. Aggiornamento VIEW, riepilogo • In genere una VIEW definita su una singola tabella e’ modificabile se gli attributi della VIEW contengono la chiave primaria (e altre chiavi) • In genere VIEW definite su piu’ tabelle non sono aggiornabili • Alcuni DBMS, come discusso prima, lo permettono nel caso certe condizioni, molto restrittive, siano rispettate • VIEW che usano funzioni di aggregazione non sono aggiornabili • PRINCIPIO di base per l’aggiornamento delle VIEW: • Ogni riga ed ogni colonna della VIEW deve corrispondere ad una ed una sola riga ed una ed una sola colonna della tabella base Basi di dati II

  39. Aspetti procedurali dei DBMS • Procedure: Programmi memorizzati nel DBMS che vengono eseguiti su esplicita richiesta degli utenti. • Trigger: Programmi memorizzati nel DBMS che vengono attivati automaticamente dopo le operazioni di modifica sulle tabelle Basi di dati II

  40. Procedure • Possono essere costituite da un unico comando SQL parametrizzato • I moderni DBMS offrono un linguaggio procedurale piu’ ricco • Oracle: PL/SQL • Sybase: Transact/SQL • IBM-DB2 • MySQL (Ver.5)?? • Possono anche essere scritte in linguaggi standard: • C/C++, Java • Compilate come oggetti esterni integrati dal DBMS • In alcuni casi possono eseguire azioni esterne: • Cancellare un file • Spedire un’email Basi di dati II

  41. Linguaggio procedurale • Complementano la natura dichiarativa di SQL • Costrutti tipo: FOR, WHILE, LOOP, IF, etc. • Scansione iterativa di tabelle Basi di dati II

  42. Esempio linguaggio procedurale DB2 • Comando IF/THEN BEGIN ATOMIC DECLARE cur INT; SET cur = MICROSECOND(CURRENT TIMESTAMP); IF cur > 600000 THEN UPDATE staff SET name = CHAR(cur) WHERE id = 10; ELSEIF cur > 300000 THEN UPDATE staff SET name = CHAR(cur) WHERE id = 20; ELSE UPDATE staff SET name = CHAR(cur) WHERE id = 30; END IF; END Basi di dati II

  43. Esempio linguaggio procedurale DB2 • Comando FOR per scansione tabella BEGIN ATOMIC FOR V1 AS SELECT dept AS dname, max(id) AS max_id FROM staff GROUP BY dept HAVING COUNT(*) > 1 ORDER BY dept DO UPDATE staff SET id = id * -1 WHERE id = v1.max_id; UPDATE staff SET dept = dept / 10 WHERE dept = v1.dname AND dept < 30; END FOR; END Basi di dati II

  44. Esempio linguaggio procedurale DB2 • Comando WHILE per scansione tabella BEGIN ATOMIC DECLARE c1, C2 INT DEFAULT 1; WHILE c1 < 10 DO WHILE c2 < 20 DO SET c2 = c2 + 1; END WHILE; SET c1 = c1 + 1; END WHILE; UPDATE staff SET salary = c1 ,comm = c2 WHERE id = 10; END Basi di dati II

  45. Vantaggi delle procedure • Consentono di condividere fra gli utenti delle attivita’ comuni, in modo da centralizzare la manutenzione, la modifica etc.. • Unificano la semantica di certe operazioni sul DB per ogni applicazione • Possono controllare in modo centralizzato certi vincoli d’integrita’ non esprimibili nelle tabelle. Basi di dati II

  46. Vantaggi delle procedure • Riducono il traffico sulla rete dovuto ad applicazioni remote, infatti invece di agire interattivamente con il DBMS l’utente spedisce una volta per tutte una chiamata alla procedura ricevendone la risposta. • Garantiscono la sicurezza dei dati consentendo a certi utenti di accedere ai dati attraverso certe procedure e non direttamente Basi di dati II

  47. Basi di dati “Attive” • Trigger: Regole basate sul paradigma Event-Condition-Action (ECA) incorporate nella base di dati • Struttura tipica di una regola/trigger:whenEventif Conditionthen Action • I DBMS attivi hanno un comportamento “reattivo” in contrasto col passivo della basi di dati tradizionali • Eseguono sia transazioni utente che trigger • I trigger sono simili alle procedure ma vengono invocati automaticamente in seguito alle operazioni di modifica della base di dati (INSERT/DELETE/UPDATE) • Fanno parte della definizione della base di dati • Arricchiscono semanticamente lo schema relazionale Basi di dati II

  48. Basi di dati “Attive”, cont. • La loro sintassi e’ stata standardizzata in SQL-1999 • Sistemi relazionali commerciali (e non) includono i trigger fin dagli anni 80 • Cio’ ha causato difformita’ di sintassi difficilmente riconciliabile • non esiste ancora un’implementazione “Standard” da un punto di vista sintattico • Estensione del “CHECK” in quanto permettono di operare (modificare) su altre tabelle • Possono anche scatenare azioni esterne al DB • Spedire email, cancellare file, etc. Basi di dati II

  49. Utilizzo trigger • Business rules, parte della procedura “di business” applicativa (normalmente eseguite in modo asincrono dall’applicazione) • Automazione magazzino con riordino automatico • Spedizione solleciti • Controllo attivita’ conti bancari/carte di credito • Acquisto/Vendita automatica strumenti finanziari • Auditing e Logging, memorizzazione eventi per controlli • Esempi: • Storico delle modifiche effettuate su una tabella per recupero dati • Elenco dei login effettuati (da chi e quando) • Controllo delle attivita’ (chi ha fatto cosa e quando) • Etc. • Version Management • Conservare varie versioni dello stato della base di dati nel tempo Basi di dati II

  50. Utilizzo Trigger • Duplicazione database • trasparente tramite l’uso dei trigger • Implementazione database distribuiti • Vincoli d’integrita’complessi non esprimibili con il comando “CHECK” • Workflow management • Esempio: Assegnare sostituto per chiamate d’urgenza Basi di dati II

More Related