Basi di dati Claudia Raibulet [email protected] Gestione delle informazioni Le informazioni possono essere gestite (registrate e scambiate) in forme diverse: • Idee informali • Linguaggio naturale (scritto e parlato, formale o colloquale, in diverse lingue, ecc.) • Disegni, grafici, schemi, • Numeri, codici, … E su vari supporti: • Memoria umana, carta, dispositivi elettronici, … Gestione delle informazioni Nelle attivita’ standardizzate dei sistemi informativi complessi sono state introdotte col tempo forme di organizzazione e codifica delle informazioni via via piu’ precise Ad esempio, nei servizi anagrafici si e’ iniziato con registrazioni discorsive e poi: • • • • Nome e cognome Estremi anagrafici Codice fiscale … Informazioni e dati Nei sistemi informatici, le informazioni sono rappresentate in un modo essenziale attraverso i dati Definizioni: • Informazione: notizia, dato o elemento che consente di avere conoscenza piu’ o meno esatta di fatti, situazioni, modi di essere, … • Dato: cio’ che e’ immediatamente presente alla conoscenza prima di ogni elaborazione Informazioni e dati I dati hanno bisogno di essere interpretati Esempio: • “Mario”, “6756” su un foglio di carta sono due dati • Se il foglio di carta rappresenta la risposta alla domanda “A chi mi devo rivolgere per risolvere il problema X e qual’e’ il suo numero di telefono?” allora i dati vengono interpretati per fornire informazioni e arricchire la conoscenza Basi di dati L’elemento chiave e’ il dato I dati rappresentano spesso una risorsa strategica perche’ piu’ stabili nel tempo di altri componenti (processi, tecnologie, …) I dati rimangono gli stessi nella migrazione da un sistema al successivo Basi di dati Basi di dati (database) rappresentano un insieme di dati, raccolti in base a precise regole, e organizzate in strutture rigide ben definite Basi di dati - gestite da un DBMS (DataBase Management System) che: • Opera al di sopra del sistema operativo • Offre linguaggi specifici per l’organizzazione e la gestione dei dati • Offre meccanismi efficienti per l’accesso ai dati Basi di dati Ancora una definizione: • Basi di dati = insieme di archivi in cui un dato e’ rappresentato logicamente una sola volta e puo’ essere utilizzato da un insieme di applicazioni da diversi utenti secondo opportuni criteri di riservatezza Gestione di basi di dati Tramite software scritti in un linguaggio di programmazione come per esempio Java (JDBC – Java DataBase Connectivity) Esempi di DBMS: • • • • • • • • Access DB2 Oracle Informix Sybase SQLServer MySQL … DBMS – Gestione di basi di dati Sono applicazioni software in grado di gestire collezioni di dati che siano: • Grandi – di dimensioni molto maggiori della memoria centrale dei sistemi di calcolo utilizzati • Persistenti – con un periodo di vita indipendente dalle singole esecuzioni dei programmi che le utilizzano • Condivise – utilizzate da applicazioni diverse Garantiscono: • Affidabilita’ – resistenza a malfunzionamenti HW e SW • Privatezza – con una disciplica e un controllo degli accessi I DBMS sono: • Efficienti – utilizzano al meglio le risorse di spazio e tempo del sistema • Efficaci - rendono produttiva l’attivita’ dei utilizzatori Basi di dati – risorsa condivisa Una basi di dati e’ una risorsa integrata e condivisa fra le varie applicazioni Conseguenze: • Attivita’ diverse su dati in parte condivisi -> richiesti meccanismi di autorizzazione o L’utente A e’ autorizzato a leggere tutti i dati e a modificare quelli sul ricevimento • Attivita’ multi-utente su dati condivisi -> controllo della concorrenza o l’effetto di transizioni concorrenti deve essere coerente Efficienza e Efficacia Efficienza • Si misura in termini di tempo di esecuzione (tempo di risposta) e spazio di memoria • A causa della varieta’ delle funzioni i DBMS non sono necessariamente piu’ efficienti del file system • L’efficienza e’ il risultato della qualita’ del DBMS e delle applicazioni che lo utilizzano Efficacia • Difficile da quantificare • Una applicazione e efficace se risponde agli obiettivi per cui e’ stata progettata Descrizione dei dati nel DBMS I dati sono descritti e rappresentati a livelli diversi che permettono l’indipendenza dai dati dalla rappresentazione fisica I programmi fanno riferimento alla struttura di livello piu’ alto e le rappresentazioni sottostanti possono essere modificate senza necessita’ di modifica dei programmi Concetto di modello dei dati Modello dei dati Insieme di costrutti utilizzati per organizzare i dati e descrivere la dinamica Componente fondamentale: meccanismi di strutturazione (o costrutti di tipo) Come nei linguaggi di programmazione esistono meccanismi che permettono di definire nuovi tipi, cosi ogni modello dei dati prevede alcuni contruttori Esempio: il modello relazionale prevede il costruttore relazione, che permette di definire insiemi di record omogenei Schemi e istanze In ogni base di dati esistono: • Lo schema, sostanzialmente invariante nel tempo, che ne descrive la struttura, il significato • L’istanza, i valori attuali, che possono cambiare nel tempo anche molto rapidamente Due tipi principali di modelli Modelli logici che sono utilizzati nei DBMS esistenti per l’organizzazione dei dati • Utilizzati dai programmi • Indipendenti dalle strutture fisiche • Esempi: relazionale, gerarchico, a oggetti, … Modelli concettuali che permettono di rappresentare i dati in modo indipendente da ogni sistema • Descrivono i concetti del mondo reale • Utilizzati nelle fasi preliminari di progettazione • Esempio: entity-relationship Architettura di un DBMS Schema logico – descrive la base dati nel modello logico (ad esempio, la struttura di una tabella) Schema interno – rappresenta lo schema logico per mezzo di strutture di memorizzazione (ad esempio, file) Schema esterno – descrive una parte della base di dati in un modello logico (viste parziali, …) Schema esterno Schema logico Schema interno DB Indipendenza dei dati Il modello logico e il modello esterno sono indipendenti dal modello fisico -> una tabella e’ utilizzata nello stesso modo qualunque sia la sua rappresentazione fisica (che puo’ anche cambiare nel tempo) Linguaggi per basi di dati Linguaggio per definire i dati • Data Definition Language – DDL • Usati per definire gli schemi logici, fisici e le autorizzazioni di accesso Linguaggio per manipolare i dati • Data Manipulation Lanuage – DML • Usati per interrogare e aggiornare la basi di dati SQL – Structured Query Language – fornisce funzioni per entrambe le categorie Sviluppatori e utenti di DBMS Progettisti e realizzatori di un DBMS Progettisti della base di dati Amministratori della base di dati (DBA) Progettisti e programmatori di applicazioni Utenti finali – eseguono applicazioni predefinite (transazioni) Utenti casuali – eseguono operazioni non previste a priori utilizzando linguaggi interattivi Trasazioni Programmi che realizzano attivita’ frequenti e predefinite, con poche eccezioni, previste a priori Esempi: • Versamento presso uno sportello bancario • Emissione di certificato anagrafico • Prenotazione aerea Osservazione: il termine transazione ha anche un altro significato: sequenza indivisibile di operazioni (o vengono eseguite tutte o nessuna) Transazioni – l’altro significato Transazioni: non lasciano mai la base di dati in uno stato inconsistente Proprietà delle transazioni: • Atomicità: vi sono solo due possibili terminazioni o o Successo: tutte le attività della transazione sono andate a buon fine, la base di dati è aggiornata (commit) Fallimento: qualche attività della transazione non va a buon fine, la transazione fallisce tutta, la base di dati non viene modificata (rollback) • Consistenza: i vincoli di integrità dei dati non sono mai violati • Isolamento: transazioni concorrenti sono isolate le une dalle altre • Durabilità: una volta completata con successo, l’effetto della transazione sulla base di dati è permanente Basi di dati Una base di dati e’ una collezione di dati (rappresentati in un forma di tabella se e’ relazionale) che rappresentano informazioni di interessa per un’organizzazione Progettare una base dati significa travare una soluzione ottima per strutturare i dati, che rispetti i requisiti propri del sistema informativo dell’organizzazione sia i vincoli che sui dati vengono imposti Un DBMS e’ un software che gestisce una base dati Per eseguire operazioni su una base di dati si scrivono istruzioni in un linguaggio supportato dal DBMS (esempio: SQL) DBMS Vantaggi: • Gestione centralizzata dei dati come risorsa comune con opportune forme di controllo • Riduzione di ridondanze e inconsistenze • Indipendenza dei dati Svantaggi • Costo dei prodotti • Non scorporabilita’; delle funzionalita’ Basi di dati relazionali Relazione, una tabella caratterizzata da: • Un numero fisso di colonne (dette attributi); ciascuna colonna assume valori estratti da uno stesso dominio • Un numero variabile di righe (dette tuple) • Grado di una relazione: il numero di colonne • Cardinalità di una relazione: il numero di righe • Schema di una relazione: il nome della relazione seguito dai nomi dei suoi attributi; a ciascun attributo è associato un dominio • Istanza di una relazione: insieme delle tuple presenti nella base di dati in un determinato istante (quindi, varia nel tempo) Base di dati relazionale, un insieme di relazioni: • Schema di una base di dati: l’elenco delle relazioni • Istanza di una base di dati: insieme delle istanze delle relazioni Esempio schema base dati Relation CONTO-CORRENTE (NUMERO-CC: integer, NOME: char (20), INDIRIZZO: char (20), SALDO: decimal (14, 2)). Relation MOVIMENTO (NUMERO-CC: integer, DATA-MOV: date, NUMERO-MOV: integer, IMPORTO: decimal (14, 2), CAUSALE: char (1)). Esempio istanza base dati CONTO-CORRENTE NUMERO-CC NOME INDIRIZZO SALDO 1 Rossi v. Anemoni 5 3,678.00 2 Bianchi v. Bolla 64 3 Brunelli v. Po 41 6,777.50 4 Grandi v. Romolo 3 3,400.00 DATA-MOV NUMERO-MOV 664.00 MOVIMENTO NUMERO-CC IMPORTO CAUSALE 1 14-01-03 1 +200.00 V 1 14-01-03 2 -500.00 P 1 27-01-03 1 +2,700.00 S 4 27-01-03 1 +1,850.40 S 3 25-01-03 1 -650.00 A SQL Structured Query Language SQL Definizione: Il linguaggio SQL è un linguaggio per la definizione e la manipolazione dei dati in database relazionali, sviluppato originariamente presso il laboratorio IBM a San Jose’ (California). SQL E‘ un linguaggio con varie funzionalità che contiene: • DDL: definizione di domini, tabelle, autorizzazioni, vincoli, procedure, ecc. • DML: linguaggio di query, modifica, comandi transizionali SQL è un linguaggio dichiarativo (non-procedurale) • Non specifica la sequenza di operazioni da compiere per ottenere il risultato Definizioni di schemi Uno schema è una collezione di oggetti: • domini, tabelle, indici, asserzioni, viste, privilegi Uno schema ha un nome e un proprietario CREATE SCHEMA [ NomeSchema ] [ [ authorization ] Autorizzazione ] { DefinizioneElementoSchema } Dedinizioni di tabelle L‘istruzione CREATE TABLE definisce uno schema di relazione e ne crea un’istanza vuota Per ogni attributo va specificato il dominio, un eventuale valore di default ed eventuali vincoli Possono essere espressi altri vincoli a livello di tabella CREATE TABLE NomeTabella ( NomeAttributo Dominio [ Default ] [ Constraints ] {,NomeAttributo Dominio [ Default ] [ Constraints ] } [ AltriConstraints ] ) Definizione di tabelle Esempio: CREATE TABLE Impiegato ( Matricola CHAR(6) PRIMARY KEY, Nome CHAR(20) NOT NULL, Cognome CHAR(20) NOT NULL, Dipart CHAR(15) REFERENCES Dipartimento(NomeDip), Stipendio NUMERIC(9) DEFAULT 0, UNIQUE (Cognome,Nome) ) Domini I domini specificano i valori ammissibili per gli attributi di una relazione: Domini elementari: • • • • • • Bit Carattere Numerico Esatto Numerico Approssimato Data/Ora Intervallo Temporale Il tipo BIT Corrisponde ad attributi che possono assumere solo due valori (0,1). Attributi di questo tipo (flag) indicano se l’oggetto rappresentato possiede o meno una certa proprietà bit bit(lunghezza) varbit (lunghezza) Esempio: L’attributo Lavoratore nella relazione STUDENTI indicase lo studente è o meno lavoratore: Lavoratore bit Il tipo CHAR Rappresenta singoli caratteri alfanumerici oppure stringhe di lunghezza fissa o variabile char char(lunghezza) varchar(lunghezza) Esempio: L’attributo Nome della relazione IMPIEGATI rappresenta sequenza di caratteri di lunghezza massima 20 Nome char(20) -> Paolo Rossi_ _ _ _ _ _ _ _ _ Nome varchar(20) -> Paolo Rossi Tipi numerici esatti Rappresentano numeri interi o numeri decimali in virgola fissa (con un numero prefissato di decimali) integer numeric numeric(precisione,scala) decimal smallint numeric(precisione) decimal(precisione) decimal(precisione,scala Esempi: L’attributo Eta nella relazione IMPIEGATI: Eta decimal(2) Rappresenta tutti i numeri fra -99 e +99 L’attributo Cambio nella relazione PAGAMENTO indica il valore del cambio del dollaro preciso al centesimo di lira: Cambio numeric(6,2) Rappresenta tutti i numeri fra -9999,99 e +9999,99 Tipi numerici approssimati Sono utili per rappresentare ad esempio grandezze fisiche (rappresentazione in virgola mobile). float float(precisione) double precision real L’attributo Massa nella relazione ASTEROIDI: Massa real 0,17E16 = 1,7 1015 I tipi Date/Time Permettono di rappresentare istanti di tempo date time timestamp time(precisione) timestamp(precisione) Ciascuno di questi domini è decomponibile in un insieme di campi (anno, mese, giorno, ora, minuti, secondi): DataDiNascita date 18/09/99 OraDiConsegna time 19.24.16 Arrivo timestamp 18/09/00 21.15.20 I tipi BLOB e CLOB Permettono di includere direttamente nel database oggetti molto grandi: • Binary Large Object (BLOB) • Character Large Object (CLOB) Figure e documenti descrittivi fotografia BLOB(10M) descrizione CLOB(100k) Valori di DEFAULT per un dominio Definiscono il valore che deve assumere l’attributo quando non viene specificato un valore durante l’inserimento di una tupla DEFAULT < ValoreGenerico | user | null > ValoreGenerico rappresenta un valore compatibile con il dominio, rappresentato come una costante o come un’espressione. User è la login dell’utente che effettua il comando Domini definiti dagli utenti Paragonabile alla definizione dei tipi nei linguaggi di programmazione Un nuovo dominio è caratterizzato dalle seguenti informazioni: nome, dominio elementare, valore di default, insieme di vincoli (constraints) CREATE DOMAIN NomeDominio as DominioElementare [ ValoreDefault ] [ Constraints ] Esempio: CREATE DOMAIN CREATE DOMAIN Voto AS SMALLINT DEFAULT 0 NOT NULL) Il nuovo dominio Voto è definito come uno SMALLINT con valore di default e che non deve essere nullo. I “nuovi domini” sono utili perché, ad esempio, modifiche alla definizione di Voto si ripercuotono in tutte le occorrenze di questo dominio nello schema del Database. Il valore NULL E’ un valore polimorfico (che appartiene a tutti i domini) col significato di valore non noto: 1) il valore esiste in realtà ma è ignoto al database (es.: data di nascita) 2) il valore è inapplicabile (es.: numero patente per minorenni) 3) non si sa se il valore è inapplicabile o meno (es.: numero patente per un maggiorenne) Vincoli/Contraints Un vincolo è una regola che specifica delle condizioni sui valori di un elemento dello schema del database. Un vincolo può essere associato ad una tabella, ad un attributo, ad un dominio. I vincoli possono essere di due tipi: • Vincoli Intrarelazionali o Si applicano all’interno di una relazione • Vincoli Interrelazionali o Si applicano tra relazioni diverse Vincoli intrarelazionali NOT NULL UNIQUE PRIMARY KEY (Il valore non deve essere nullo) (I valori non devono essere ripetuti) (Chiave primaria) CHECK (Condizioni complesse) Esempio CREATE TABLE Impiegato ( Matricola CHAR(6) PRIMARY KEY, Nome CHAR(20) NOT NULL, Cognome CHAR(20) NOT NULL, Stipendio NUMERIC(9) DEFAULT 0, UNIQUE (Cognome, Nome) ) Vincolo su più attributi Vincolo su un attributo Vincoli intrarelazionali CREATE TABLE Impiegato ( Matricola CHAR(6) PRIMARY KEY, Nome CHAR(20) NOT NULL, Cognome CHAR(20) NOT NULL, Stipendio NUMERIC(9) DEFAULT 0, UNIQUE (Cognome, Nome) ) L’attributo o la n-pla di attributi su cui è definito il vincolo UNIQUE non possono avere istanze uguali ripetute. Vincoli intrarelazionali CREATE TABLE Impiegato ( Nome CHAR(20) NOT NULL, Cognome CHAR(20) NOT NULL, Stipendio NUMERIC(9) DEFAULT 0, PRIMARY KEY (Cognome, Nome) In alcune implementazioni di SQL potrebbe essere necessario specificare comunque anche il vincolo NOT NULL per tutti gli attributi coinvolti. OSSERVAZIONE Nome CHAR(20) NOT NULL, Cognome CHAR(20) NOT NULL, UNIQUE (Cognome, Nome), Nome Cognome Luca Rossi Giovanni Bianchi Emilio Verdi Emilio Rossi Nome Cognome Luca Neri Giovanni Bianchi Emilio Verdi Francesca Rossi Non è la stessa cosa di: Nome Cognome CHAR(20) NOT NULL UNIQUE, CHAR(20) NOT NULL UNIQUE, Vincoli intrarelazionali CREATE DOMAIN Voto AS SMALLINT DEFAULT 0 NOT NULL) CREATE DOMAIN Voto AS SMALLINT DEFAULT 0 CHECK (Voto>=18 AND VOTO<=30) Vincoli interrelazionali Coinvolgono più relazioni / tabelle. Definiamo tre tabelle con le informazioni degli esami sostenuti dagli studenti: • • • Tabella Studente Tabella Esame Tabella Corso Tabella Studente CREATE TABLE Studente ( Matr CHAR(6) Nome VARCHAR(30) Città VARCHAR(20), CDip CHAR(3) ) Matr 34321 Nome Luca PRIMARY KEY, NOT NULL, Città CDip Mi Inf 53524 Giovanni To Mat 64521 Emilio Ge Ing 73321 Francesca Vr Mat Tabella Esame CREATE TABLE Esame ( Matr CHAR(6), CodCorso CHAR(6), Data DATE NOT NULL, Voto Voto, PRIMARY KEY (Matr, CodCorso) ) Matr CodCorso Data Voto 34321 1 25/02/01 28 34321 2 14/12/01 30 64521 1 12/03/02 24 73321 4 18/01/02 18 Tabella Corso CREATE TABLE Corso ( CodCorso CHAR(6) Titolo VARCHAR(30) Docente VARCHAR(20) ) PRIMARY KEY, NOT NULL, CodCorso Titolo Docente 1 matematica Barozzi 2 informatica Meo 3 ingegneria Neri 4 Matematica Bianchi Vincoli interrelazionali REFERENCES Permettono di definire vincoli di integrità FOREIGN KEY referenziale CHECK (Vincoli complessi) Si hanno due sintassi • per singoli attributi • su più attributi E' possibile definire politiche di reazione alle violazioni. Vincoli interrelazionali Matr Nome Città CDip 34321 Luca Mi Inf 53524 Giovanni To Mat 64521 Emilio Ge Ing 73321 Francesca Vr Mat Studente Esame Matr CodCorso Data Voto 34321 1 25/02/01 28 34321 2 14/12/01 30 64521 1 12/03/02 24 73321 4 18/01/02 18 CodCorso Titolo Docente 1 matematica Barozzi 2 informatica Meo 3 ingegneria Neri 4 Matematica Bianchi Corso Vincoli interrelazionali CREATE TABLE Esame ( Matr CHAR(6), CodCorso CHAR(6), Data DATE NOT NULL, Voto Voto, PRIMARY KEY (Matr, CodCorso) FOREIGN KEY (Matr) REFERENCES Studente FOREIGN KEY (CodCorso) REFERENCES Corso ) Vincoli interrelazionali CREATE TABLE Esame ( Matr CHAR(6) REFERENCES Studente, CodCorso CHAR(6) REFERENCES Corso, Data DATE NOT NULL, Voto Voto, PRIMARY KEY (Matr, CodCorso) ) Vincoli interrelazionali Matr Nome Città CDip Esame 34321 Luca Mi Inf Matr CodCorso Data Voto 53524 Giovanni To Mat 34321 1 25/02/01 28 64521 Emilio Ge Ing 34321 2 14/12/01 30 73321 Francesca Vr Mat 64521 1 12/03/02 24 73321 4 18/01/02 18 Studente Se eliminiamo da Studente la tupla con matricola 34321, cosa succede alla tabella Esame? Problema degli orfani! Vincoli interrelazionali: reazioni alle violazioni Le reazioni operano sulla tabella figlio (es Esami), in seguito a modifiche alla tabella padre (es Studente) Reazioni previste: CASCADE : propaga la modifica SET NULL: annulla l’attributo che fa riferimento SET DEFAULT: assegna il valore di default all’attributo NO ACTION : impedisce che la modifica possa avvenire Le violazioni possono essere introdotte: 1. da modifica (update) dell’attributo cui si fa riferimento 2. da cancellazioni di tuple Vincoli interrelazionali Matr Nome Città CDip Una istanza scorretta 123 456 789 120 Esame Studente Viola la chiave Viola il NULL Viola integrità referenziale Matr CodCorso Data Voto 123 1 25/02/01 28 123 2 14/12/01 30 123 1 12/03/02 24 120 4 18/01/02 25 456 1 12/03/02 NULL 555 4 18/01/02 23 Vincoli interrelazionali Matr 123 Nome Città CDip Una istanza corretta 456 789 120 Esame Studente Matr CodCorso Data Voto 123 1 25/02/01 28 123 2 14/12/01 30 120 4 18/01/02 25 Modifiche degli schemi Necessarie per garantire l’evoluzione della base di dati a fronte di nuove esigenze. Ci sono due comandi SQL appositi: ALTER: DROP: modifica oggetti persistenti cancella oggetti dallo schema Modifiche degli schemi Si applica su domini e tabelle: ALTER TABLE NomeTabella < ALTER COLUMN NomeAttributo | DROP COLUMN NomeAttributo | ADD COLUMN DefAttributo | DROP CONSTRAINT NomeVincolo ADD CONSTRAINT DefVincolo > Modifiche degli schemi Cancella oggetti DDL, si applica su domini, tabelle, indici, view, asserzioni, procedure,... DROP < schema, domain, table, view, ...> NomeElemento [ RESTRICT | CASCADE ] Opzioni: RESTRICT Impedisce drop se gli oggetti comprendono istanze CASCADE Applica drop agli oggetti collegati Esercizio Si consideri il seguente schema relazionale: Implementare in SQL lo schema relazionale dell’esercizio con gli opportuni vincoli di integrità Esercizio CREATE TABLE PRODOTTO ( cod-prod char(3) PRIMARY KEY, descrizione char(25), prezzo-unitario integer CHECK (prezzo-unitario > 0) ) CREATE TABLE FATTURA ( cod-fatt char(3), cliente char(25), cod-prod char(3) REFERENCES Prodotto (cod-prod), quantità smallint CHECK (quantità > 0), PRIMARY KEY (cod-fatt, cod-prod) )