Indice generale Teoria dei database - Digilander
annuncio pubblicitario
Indice generale PREMESSA.....................................................................................................................................1 INTRODUZIONE...........................................................................................................................1 CRITERI DI PROGETTAZIONE DELL' ORGANIZZAZIONE FISICA.....................................5 L 'INFORMAZIONE IN UNA BASE DATI...................................................................................6 MODELLI DI DATI........................................................................................................................9 I LINGUAGGI PER LA MANIPOLAZIONE DEI DATI E IL MODELO RELAZIONALE.....13 SCHEMI RELAZIONALI DI BASI DI DATI..............................................................................14 PROGETTAZIONE MODELLI DI BASE DATI.........................................................................19 PROGETTAZIONE.......................................................................................................................22 PROGETTAZIONE CONCETTUALE....................................................................................23 PROGETTAZIONE LOGICA..................................................................................................24 PROGETTAZIONE FISICA ....................................................................................................25 ESEMPI DI RPOGETTAZIONE..................................................................................................26 PRENOTAZIONI AD ALBERGHI..........................................................................................26 PROGETTAZIONE DI UN DATABASE AZIENDA..............................................................30 Teoria dei database PREMESSA Questa relazione vuole esporre i concetti alla base dei quali sono realizzati e progettati i database relazionali. Non è un documento che tratta l' argomento in tutta la sua complessità ma può essere utile per chiarire le idee per poi approfondire lo studio della teoria dei database con dei testi dedicati. Si è deciso di realizzare il testo in modo schematico per aumentare la chiarezza e renderlo più sintetico. INTRODUZIONE In questa prima parte si introdurranno la definizione di archivio e la nomenclatura relativa. Inoltre sono descritte anche le operazioni che un utente può eseguire su un archivio. Si definisce archivio o file l'insieme di tutte le informazioni Ogni insieme unitario di informazione è detto record. Un record è costituito da un insieme di campi ciascuno dei quali contiene una parte dell'informazione. Ad ogni campo è associato un nome che serve per indicare la porzione d' informazione contenuta in quel campo . Un archivio è un insieme di record. La struttura di un record è la specificazione dei campi che lo costituiscono. L'insieme dei campi utilizzati per identificare un record di un file è detto chiave. Si definisce metodo della chiave implicita utilizza per chiave una parte dell'informazione. Si definisce metodo della chiave mediante tabella quel metodo che utilizza per determinare la chiave del record una tabella che associa all'informazione un codice. Teoria dei DataBase Ing. Antonio Nicolazzo Si definisce metodo della chiave mediante regole logiche quel metodo che determina la chiave mediante l'applicazione di regole predefinite ad una parte dell'informazione. I record di un file si dicono omogenei se contengono tutti le stesse informazioni nello stesso ordine altrimenti si dicono non omogenei. Nel caso si utilizzano record non omogenei è necessario inserire un campo per l'identificazione del tipo di record. Si definisce lunghezza di un informazione il numero di caratteri che la compongono. Si definisce lunghezza di un record il numero di byte utilizzati per rappresentare l' informazione che esso contiene. Un record può essere a lunghezza fissa o variabile. In caso di record a lunghezza variabile si devono utilizzare degli indicatori di inizio e fine e questo rende l' utilizzo di questi tipi di record più oneroso. Si definiscono in un sistema gestione degli archivi le seguenti operazioni: – – – – creazione di un file operazioni di aggiornamento di un file ricerca di un record operazioni globali sui record L' organizzazione di un file determina la realizzazione del sistema gestione. L' insieme dei file e delle informazioni necessarie alla loro elaborazione è detta directory. Le directory sono utilizzate dal sistema in modo trasparente all'utente ma quest'ultimo può avere informazioni sulle directory mediante particolari interrogazioni del archivio. Si definisce ricerca per posizione l' operazione con cui si individua un record specificandone il numero d' ordine all'interno del file. Si definisce ricerca per chiave l'operazione mediante cui si individua il record specificando il valore della chiave del record da ricercare. Si definiscono operazioni di aggiornamento di un file le seguenti: – – – inserzione cancellazione modifica la modifica del file è l' operazione con cui si cambia il contenuto di uno o più campi di un record. La modifica avviene in memoria centrale e poi si trasferisce il file modificato nella memoria ausiliaria nuovamente(Hard Disk). La cancellazione si effettua logicamente associando ad ogni record cancellato un tag detto di cancellazione. Quindi l' operazione di cancellazione non comporta una reale perdita dell'informazione e conseguente liberazione dello spazio di memoria. L' inserimento può avvenire secondo la modalità di sovrapposizione o ampliamento. Il metodo di sovrapposizione sostituisce un record cancellato con il record che si vuole inserire, mentre il metodo di ampliamento inserisce il record alla fine del file facendo aumentare le dimensioni del file. La modifica di un record se non comporta la variazione del campo chiave è condotta con la sostituzione del record da modificare con quello modificato, mentre se la modifica coinvolge anche il valore del campo chiave si cancella Teoria dei DataBase Ing. Antonio Nicolazzo il record iniziale e si inserisce il record modificato. Si definisce struttura di un file l' insieme dei seguenti parametri: – specificazione delle informazioni memorizzate – scelta dell'organizzazione fisica L'organizzazione fisica dei file deve essere determinata in funzione del tipo di elaborazioni che devono essere effettuate su quelle informazioni. Le organizzazioni fisiche sono: – sequenziali – casuali – miste Organizzazione sequenziale – E' adatta a file che richiedono la stessa elaborazione su tutti i record. – Permette la ricerca sia per posizione – La ricerca richiede tempi lunghi e variabili a seconda di dove e collocata l' informazione Organizzazione casuale – Permette la ricerca sia per posizione che per chiave – I tempi di ricerca sono ridotti – Prevede una procedura per l'identificazione del record in modo univoco in funzione del valore della chiave Organizzazione mista – Permette la ricerca sequenziale dei file così consentendo le operazioni globali – Consente mediante un indice di norma l'accesso diretto alla posizione dei file che contiene il record così la ricerca non è veloce come quella casuale ma più veloce di quella sequenziale. Il frazionamento dell'informazione risponde ad un esigenza di miglioramento in quanto riduce il tempo di trasferimento dell'informazione e un ottimizzazione dello spazio di memoria occupato. Di seguito si definisco i metodi di frazionamento dell'informazione: Metodo della suddivisione delle informazioni: Il metodo prevede la divisione delle informazioni in tanti file quante sono le elaborazioni possibili. Se una stessa informazione è richiesta in diverse elaborazioni essa deve essere ripetuta nei diversi file. Le informazioni ripetute possono rappresentare la chiave per tutti i file così da individuare i record logicamente uniti. Metodo del collegamento fra informazioni Il metodo definisce un file principale con tutte le informazioni necessarie per tutte l' elaborazioni. In seconda istanza si definiscono un numero di file secondari pari al numero di elaborazioni possibili. Ad ogni record del file principale è associato un record dei file secondari Per il metodo del collegamento è necessario definire una procedura per associare ad ogni record del file principale uno dei file secondari o se necessario si aggiunge nel file principale un campo che contiene un riferimento ai record dei file Teoria dei DataBase Ing. Antonio Nicolazzo secondari. Caratteristiche dell'informazione I parametri che caratterizzano un informazione per essere memorizzata sono: – definibilità – individuabile senza ambiguità – rappresentabile – codificabilità ossia deve poter essere associata ad un codice alfanumerico L'esattezza delle informazioni acquisite in un archivio sono garantite dalle seguenti operazioni: – acquisizione – rappresentazione – immissione e controllo – validazione L' operazione d'immissione può comportare i seguenti errori: – errori impliciti o errori di misura – errori di rappresentazione – errori di trascrizione Validazione Si definisce validazione il controllo finale dei dati per la verifica del loro significato. Si possono definire degli algoritmi auto controllanti per verificare l' esattezza delle informazioni contenute nei campi . Teoria dei DataBase Ing. Antonio Nicolazzo CRITERI DI PROGETTAZIONE DELL' ORGANIZZAZIONE FISICA. L' organizzazione fisica di un archivio considera le tecniche di memorizzazione dell'informazione. In questo capitolo sono presi in considerazioni gli aspetti che distinguono e permettono di migliorare l'organizzazione fisica di un archivio. I criteri di valutazione delle performance di un database sono legati al tempo impiegato per la ricerca dell'informazione. I criteri di valutazione dell'occupazione di memoria si dividono in due tipologie : - compattamento dell'informazione - collegamento dell'informazioni a dati comuni (riduzione della ridondanza dei dati) I Criteri di compattamento dell'informazione si distinguono in: 1. Riduzione dello spazio riservato ad ogni informazione 2. Eliminazione delle informazioni non utili o opzionali Un campo è detto opzionale se può contenere informazioni nulle. La gestione dei campi opzionali può avvenire tramite le “tabelle di bits” o le “tabelle di codici”. Le tabelle di bits sono delle tabelle in cui si memorizzano un bit per ogni campo opzionale. Il bit che indica il campo opzionale è posto a uno se il corrispondente campo contiene informazione. 0 1 1 Campo 1 opzionale vuoto Campo 2 opzionale Campo 3 opzionale con informazione con informazione Corrispondenza bit /campo Il metodo delle tabelle dei codici definisce un codice per ogni campo opzionale. In caso di presenza del campo opzionale il campo in questione è preceduto dal codice corrispondente “010” campo2 “010” campo 1 “011” campo 3 Il criterio di collegamento a dati comuni è utilizzato per ridurre lo spazio impiegato per la memorizzazione di dati comuni a più record. Per la riduzione dei dati comuni si utilizzano due le seguenti metodologie: - Collegamento tramite rinvio - Collegamento tramite gerarchia Il collegamento tramite rinvio utilizza una tabella per associare ad ogni valore del dato comune un codice. I records che utilizzano il dato comune riporteranno al posto del dato il codice ad esso associato. Il collegamento tramite gerarchia raggruppa i record che utilizzano una stessa informazione e si memorizza il dato comune seguito da tutte le informazioni contenute nei record raggruppati. Teoria dei DataBase Ing. Antonio Nicolazzo Teoria dei DataBase Ing. Antonio Nicolazzo L 'INFORMAZIONE IN UNA BASE DATI Un dato è la rappresentazione di una informazione ottenuta per mezzo di alcune caratteristiche dell'informazione stessa. Una corrispondenza tra due insiemi A e B di dati è una legge che associa ad ogni dato dell'insieme A un sottoinsieme, anche vuoto, dei dati dell'insieme B. Una base dati può essere vista come un magazzino d' informazioni memorizzate su un elaboratore e utilizzata per mezzo di un sistema per la gestione di una base dati. Una transazione su una base dati è l'esecuzione, da parte del sistema di gestione della base dati, di una richiesta dell'utente. Un sistema per la gestione di una base dati deve permettere ad un utente di definire, secondo regole ben precise e tipiche del sistema stesso, le caratteristiche delle informazioni e delle corrispondenze che costituiscono la base dati che l' utente vuole utilizzare. Una base dati deve garantire le seguenti caratteristiche: L' indipendenza dei dati deve permettere all'utente di considerare la base dati in termini astratti ossia deve svincolare l 'utente dalla conoscenza di come e dove i dati sono memorizzati nell'elaboratore. La Sicurezza di una base dati deve permettere l 'accesso ad una informazione solo agli utenti abilitati e deve impedire la consultazione e modifica delle informazioni agli utenti non abilitati. L' integrità è assicurata dalla definizione dei vincoli di consistenza. I vincoli di consistenza sono le proprietà che devono essere rispettate dai dati memorizzati nella base dati per non comportare perdita o deformazione dell'informazione. Un sistema di gestione di base dati deve presentare sincronizzazione in quanto l'esecuzione di alcune operazioni di aggiornamento simultaneo non devono mai generare inconsistenza della base dati. Un sistema per la gestione della base deve prevedere delle operazioni di ripristino dati quindi deve prevedere le procedure per la realizzazione di backup periodici della base dati e procedure per il ripristino della base dati partendo dal backup. Il sistema per assicurare il perfetto ripristino deve memorizzare le transizioni che hanno modificato la base dati dall'ultimo backup. Livelli d' astrazione. Si definiscono per la gestione di un sistema di base dati i livelli d' astrazione fisico, logico ed esterno. Il livello fisico è il più vicino all'elaboratore ed è il livello che gestisce la reale disposizione dei dati in memoria. Lo schema fisico di una base dati è dato dalla descrizione delle caratteristiche e dei files usati per rappresentare la base dati logica. Il livello esterno è il livello più vicino all'utente e gestisce le viste dei dati. Si definiscono viste la descrizione astratta di una parte dello schema logico di una base di dati. Il livello logico è il livello d'astrazione collocato tra il livello fisico e quello esterno. Il livello logico gestisce l'organizzazione dei dati. Si distinguono tra i modelli logici i modelli relazionali, modelli gerarchici e modelli reticolari. I modelli relazionali permettono di descrivere sia i dati che le corrispondenze fra i dati sotto forma di un unica struttura di dati chiamata relazione. Nel modello gerarchico la struttura di dati usata per rappresentare l' informazione è un albero, dove un nodo rappresenta una informazione e i figli di un nodo sono associati al loro padre da una corrispondenza esistente nella base dati. Nei modelli reticolari vengono utilizzati records per rappresentare le informazioni e Teoria dei DataBase Ing. Antonio Nicolazzo connettori per rappresentare le corrispondenze tra le informazioni. Teoria dei DataBase Ing. Antonio Nicolazzo Per ogni vista esiste una corrispondenza schema esterno(vista)/schema logico e tale corrispondenza permette l' indipendenza logica dei dati. Esiste una corrispondenza schema logico/schema fisico in modo da specificare a quali informazioni si fa riferimento a livello fisico per rappresentare le informazioni a livello logico. Vantaggi dell'indipendenza L'indipendenza fisica dei dati implica che i l 'amministratore può modificare lo schema fisico della base dati senza alterare lo schema logico e le viste. L'indipendenza logica implica che lo schema logico può essere modificato senza modificare le viste degli utenti L'indipendenza fisica e logica permettono di realizzare dei programmi che non tengano conto dell'organizzazione fisica dei dati e delle tecniche d'accesso ai dati. Vista 1 Base di dati fisica Base di dati logica Vista 2 La progettazione delle viste, dello schema logico e dello schema fisico di una base dati è fatta dall'amministratore della base dati. L 'amministratore definisce: • Lo schema logico della base dati e i sottoschemi logici ai quali dovranno fare riferimento gli utenti • Lo schema fisico che è utilizzato per implementare lo schema logico • Le corrispondenze che esistono tra gli oggetti definiti a livello logico e gli oggetti definiti a livello fisico. Una volta che la base dati è realizzata l' amministratore è responsabile delle attività di gestione della base dati: • Contenuto informativo della base dati. L' amministratore della base dati deve identificare le entità che sono interessanti per l' applicazione e determinare le informazioni relative a queste entità che devono essere memorizzate. • Collegamento con gli utenti. Deve essere sempre assicurata la disponibilità dei dati per gli utenti. • Controllo di autorizzazione e procedure di validazione. L' accesso ai dati deve essere consentito solo ad alcuni utenti. I dati prima di essere memorizzati devono essere sottoposti ad un azione di controllo. • Strategia di sostegno e ripristino. Devono essere definite delle procedure per il ripristino e il recupero del contenuto della base dati anche in caso di danneggiamento della base dati. • Controllo delle prestazioni. Devono essere definite degli strumenti per il controllo in modo automatico delle prestazioni del sistema e per il miglioramento dell'efficienza dell'organizzazione adottata. • Strumenti d'amministrazione. Il sistema per la gestione di una base dati deve fornire all'amministratore i seguenti strumenti: 1. programmi di ripristino per ricreare la base dati in caso di crash 2. programmi di riorganizzazione per l' ottimizzazione dell'occupazione di memoria 3. programmi di diario per la memorizzazione di tutte le transizioni eseguite e dell'utente che le ha compiute Teoria dei DataBase Ing. Antonio Nicolazzo 4. programmi di analisi statistica per poter monitorare le prestazioni della base dati 5. dizionario dei dati che è una base dati che contiene la descrizione sugli oggetti su cui opera la base dati Concludendo l' utilizzo di una base dati consente: • • • • • La riduzione della ridondanza (ripetizione degli stessi dati in più punti) Controllo dell'inconsistenza dei dati Spartizione dei dati in modo che una nuova applicazione possa visualizzare le informazioni senza creare nuovi files Rafforzamento degli standars in virtù della centralizzazione delle informazioni Applicazione delle norme di sicurezza Teoria dei DataBase Ing. Antonio Nicolazzo MODELLI DI DATI un modello di dati è un formalismo utilizzato da ogni utente per la gestione dei dati e per descrivere la realtà con cui vuole interagire. esistono tre tipi di modelli per la gestione dei dati: – modello relazionale – modello gerarchico – modello reticolare Modello gerarchico Nel modello gerarchico le corrispondenze sono rappresentate da una struttura ad albero. Gli alberi sono detti occorrenze gerarchiche. Il record che appare sopra ad altri record dell'albero è detto di tipo ascendente o superiore. Il record che appare nella parte inferiore ad altri record è detto discendente o inferiore o dipendente. Il record situato nella parte superiore di un albero è detto radice. Una radice può avere un numero indeterminati di records dipendenti di diverso tipo. Esempio di gerarchia radice Nodo 1 Nodo 2 Un record può essere contemporaneamente ascendente e discendente. Le operazioni in un modello gerarchico sono complesse in quanto non è sufficiente individuare la corrispondenza tra i records ma occorre anche indicare il livello del record con le parole under o upper. Per localizzare un record quindi occorre specificare la corrispondenza con la condizione where e il contesto ascendente o discendente. Get <ascendente / discendente> where <condizione> In un modello gerarchico non è possibile inserire un record di tipo discendente se non si inserisce prima un record di tipo ascendente ad esso collegato. In un modello gerarchico se si cancella una corrispondenza è necessario cancellare anche i record collegati. In un modello gerarchico la cancellazione di un record ascendente impone la cancellazione di tutti i record dipendenti del record cancellato. Teoria dei DataBase Ing. Antonio Nicolazzo In un modello gerarchico quando si eseguono delle modifiche in un record discendente è opportuno cercare ogni occorrenza delle informazioni modificate nell'albero per non creare inconsistenza. Teoria dei DataBase Ing. Antonio Nicolazzo Modello Reticolare In un modello reticolare le corrispondenze sono rappresentate da records connettori che collegano i records con i dati. Una rete è una struttura più complessa di una gerarchia in quanto un record può avere un numero indeterminato di records superiori e inferiori. Il modello reticolare permette di rappresentare le corrispondenze molti a molti cosa che non è possibile nel modello gerarchico e in quello relazionale. Esempio: Fornitore1 Fornitore2 Fornitore3 Fornitura 1 Fornitura 2 Fornitura 3 Prezzo 1 Prezzo 2 Prezzo 3 Il modello gerarchico permette una maggiore simmetria rispetto a quello reticolare ma a costo di una maggiore complicazione delle interrogazioni del modello. La selezione di un record necessita oltre all'individuazione del connettore anche di un ulteriore parametro che indichi se ci interessa il record superiore o inferiore. Get <tipo record> over <tipo di connettore> Modello relazionale Le tabelle nel modello relazionale sono rappresentate da dei files sequenziali dove le righe corrispondono ai recors e le colonne ai campi dei records. Le tabelle sono dette relazioni, le righe sono dette tuple e le colonne sono gli attributi. Si definisce dominio l 'insieme di valori che costituiscono una colonne di una relazione. In un modello relazionale la corrispondenza tra le tuple è rappresentata solo per mezzo degli attributi (valori) contenuti nello stesso dominio (colonna). In un modello relazionale tutte le informazioni sia dati che corrispondenze sono rappresentate in modo uniforme cioè sotto forma di relazioni (tabelle). Esempio di modello relazionale Prezzo Fornitore Forniture Teoria dei DataBase Ing. Antonio Nicolazzo poiché in un modello relazionale sia dati che corrispondenze sono rappresentati in una ed una sola forma è sufficiente avere uno ed un solo operatore per le operazioni fondamentali (inserzione, cancellazione, selezione) che si vogliono eseguire Operazione di ricerca get <nome relazione> where <condizione> Operazioni di aggiornamento insert <tupla> into <relazione> delete from <relazion> where <condizione> modify <relazione> where <condizione> Un operatore relazionale è un operatore che agisce su relazioni e produce ancora delle relazioni. Quindi la ricerca in una base dati relazionale è un procedimento di costruzione di relazioni che soddisfano le condizioni imposte dalla ricerca. Si definiscono i seguenti operatori relazionale: – SELEZIONE – PROIEZIONE – JOIN NATURALE La selezione costruisce una nuova relazione estraendo un sottoinsieme orizzontale da una relazione esistente. ossia si definisce una tuple costituito da alcuni campi delle tuple della tabella (relazione) originaria. La proiezione costruisce una relazione estraendo un sottoinsieme verticale da una relazione esistente ossia si selezionano un certo numero di tuple della tabella (relazione) considerata. La join naturale è una nuova relazione nella quale ciascuna tupla è realizzata concatenando due tuple delle relazioni tra cui si realizza la join. Per le interrogazioni dei modelli reticolari si possono utilizzare i linguaggi : – ALPHA e QUEL detti di calcolo relazionale – SEQUEL(SQL) e SQUARE detti di mapping – QBE e CUPID che sono linguaggi grafici. Teoria dei DataBase Ing. Antonio Nicolazzo I LINGUAGGI PER LA MANIPOLAZIONE DEI DATI E IL MODELLO RELAZIONALE Ogni utente di un sistema di gestione di una base dati può interagire, interrogare,modificare le informazioni in esso memorizzate esclusivamente utilizzando il linguaggio fornito dal sistema. I linguaggi per la modifica della base dati si distinguono nei seguenti tipi: – Calcolo relazionale dove si specifica un predicato che deve essere soddisfatto dalle tuple che costituiscono la relazione desiderata. – algebrici o procedurali dove la domanda è espressa mediante espressioni costituite da operatori. I linguaggi algebrici permettono di definire le interrogazioni al sistema utilizzando degli operatori (unione, intersezione, differenza, prodotto cartesiano, proiezione, selezione, join, join naturale, divisione e ridenominazione)che agiscono su un insieme specifico di relazioni in modo da ottenere le informazione desiderate. Una interrogazione (query) Q può essere considerata una funzione che applicata ad un insieme di relazioni R produce ancora una relazione D Q:R→D Analizziamo gli operatori utilizzati nei linguaggi algebrici. Si definisce unione date due relazioni R e S con lo stesso schema la relazione costituita da tutte le tuple che appartengono in R o in S o in entrambe. Si definisce intersezione date due relazioni R e S con lo stesso schema la relazione costituita da tutte le tuple che appartengono sia in R che in S. Si definisce differenza date le due relazioni R e S con lo stesso schema la relazione costituita da tutte le tuple di R che non appartengono in S. Si definisce prodotto cartesiano date le due tuple R e S la relazione costituita da tutte le possibili coppie (r,s) tali che r è una tupla di R e s è una tupla di S. Si definisce concatenazione di una tupla r con una tupla s la tupla t = (r,s). Si definisce proiezione data una relazione R e X un sottoinsieme degli attributi dello schema di R. la relazione T che ha lo schema T(X) ossia le tuple di T che sono costituiti dai valori che appaiono nelle tuple di R in corrispondenza degli attributi contenuti in X. Le tuple uguali ottenute dall'operazione di proiezione non sono considerate. Si definisce selezione data una relazione R e F un predicato la relazione che ha lo schema di R e costituita dalle tuple che soddisfano il predicato F. Il predicato è composto da uno o più predicati semplici collegati tra loro dagli operatori logici and, or ,not e tali che ogni predicato opera su un attributo di R. Si definisce join date le due relazioni R e S e dato P un predicato che opera una relazione tra un attributo di R e uno di S mediante un operatore di confronto, la relazione costituita dalle tuple (r,s) del prodotto cartesiano RxS tali che il predicato P sia soddisfatto. Si definisce join naturale date due relazioni R e S con schema R(a1, a2, ..ai,....an) e S(b1, b2, ...bi,...bm) e tali che gli attributi a1, a2, ..ai siano uguali a b1, b2, ...bi la relazione T costituita dalle tuple concatenate di R e di S (r,s) che hanno gli stessi valori negli attributi analoghi a1=b1 a2=b2 ai=bi. Le tuple della join naturale hanno lo schema T(a1, a2, ...ai, ai+1, ai+2,...bi+1, bi+2, ...bm) Si definisce divisione date le relazioni R e S tali che tutti gli attributi di S siano anche attributi di R la relazione costituita dalle tuple t tali che r = (t,s) ossia dalle tuple costituite dagli attributi di R che non appartengono a S. Si definisce ridenominazione l' operazione con cui si cambia il nome agli attributi di una relazione Teoria dei DataBase Ing. Antonio Nicolazzo SCHEMI RELAZIONALI DI BASI DI DATI La progettazione di una base di dati inizia con l 'osservazione del processo che si vuol rappresentare. Dall'analisi del processo si ottiene l 'individuazione delle entità e degli attributi. Si definiscono entità gli oggetti principali che costituiscono un database (tabelle). L'entità rappresenta un gruppo omogeneo d' informazioni. Le singole informazioni sono gli attributi dell'entità. Dopo avere determinato l 'entità e gli attributi che ci consentono di descrivere il processo analizzato occorre definire le relazioni che costituiranno la base dati ossia lo schema della base dati. Lo schema relazionale di una base di dati è la lista degli schemi di tutte le relazioni che costituiscono la base di dati stessa. Lo schema di una base di dati relazionale è la descrizione delle informazioni memorizzate in essa. Data una realtà esistono infiniti schemi che permettono la sua rappresentazione ma occorre individuare gli schemi che non presentano anomalie. La progettazione dello schema di una base di dati è il processo che permette la scelta di uno schema adeguato. La progettazione dello schema inizia con l' individuazione delle dipendenze funzionali (vincoli) imposti dalle relazioni tra le informazioni della base dati e successivamente si procede con il processo di normalizzazione. Sia R(A) uno schema di una relazione e siano X e Y due sottoinsiemi dell'insieme degli attributi A, si dice che Y dipende funzionalmente da X se per ogni relazione r che abbia lo schema R(A) non è possibile che siano presenti due tuple che hanno gli stessi valori degli attributi appartenenti all' insieme X e che abbiano valori differenti in una o più degli attributi appartenenti all' insieme Y. Una chiave costituisce sempre una dipendenza funzionale con gli attributi della relazione. Data una relazione R(A) e F un insieme di dipendenze funzionali per lo schema R(A) si definisce dipendenza derivata ogni dipendenza di X su Y non contenuta in F. Si definisce chiusura di un insieme di dipendenze F l'insieme di tutte le dipendenze funzionali implicate da F. L'insieme delle dipendenze F si dice completo se coincide con l 'insieme delle chiusure ossia contiene tutte le dipendenze implicate da essa. Si definisce attributo semplice un attributo costituito da un valore atomico ossia unico e indivisibile. Si definisce attributo strutturato un attributo costituito da un insieme di valori. Si definisce attributo multivalore un attributo strutturato e non semplice. La ridondanza dei dati in un database è dovuta sostanzialmente alla ripetizione d' informazioni. La ridondanza può essere la conseguenza di attributi ripetuti ad esempio considerando una libreria l' autore di un libro non può variare indipendentemente da dove è collocato il libro. La ridondanza può essere presente anche in un unico attributo infatti ricorrendo ancora all'esempio della libreria non è corretto che nell'attributo utilizzato per indicare il libro si riportino anche l'autore del libro in quanto è sufficiente il nome di quest'ultimo per individuare in modo univoco anche l 'autore. In ultimo è presente anche la ridondanza dovuta ad attributi con lo stesso dominio ad esempio, dovendo memorizzare i voti degli esami superati da un ragazzo non è Teoria dei DataBase Ing. Antonio Nicolazzo opportuno prevedere gli attributi esame1, esame2,......esame n. La situazione descritta si risolve meglio realizzando una tabella con un riferimento ai dati del ragazzo (chiave) e un campo esame. In tale tabella si riporterà una tupla in corrispondenza di ogni esame superato. La soluzione proposta presenta uno schema indipendente dal numero di esami conseguiti dal ragazzo. esempio con ridondanza Nome Cognome Esame1 Esame2 Esame3 Conversione senza ridondanza Id Nome cognome Id Esame Una relazione è un insieme ordinato di zero o più tuple e ogni tupla della relazione deve essere individuabile in modo univoco. Per ogni tupla deve esistere un attributo o un insieme di attributi che la identificano e questo insieme è detto chiave. Se una chiave è formata da un unico attributo si definisce chiave semplice altrimenti si dice composta. Una chiave deve essere irriducibile ossia non deve essere possibile eliminare dagli insiemi degli attributi che la compongono un singolo attributo senza che la chiave perda validità. La normalizzazione è l'operazione che permette di decomporre uno schema che presenta anomalie in uno schema il più possibile immune da anomalie di gestione dei dati. Si definisce schema in prima forma normale uno schema della relazione R(A), con A insieme degli attributi, costituito da attributi semplici. Quindi una forma normale evita che si verifichi una ridondanza di dati. La decomposizione di uno schema R(A) in uno schema corrispondente in prima forma normale consiste nel sostituire la relazione originale R(A) con una collezione di relazioni S= [R1(A1) R2(A2) … Rk(Ak)] tali che A1 U A2 U..Ak =A. che presentino solo attributi semplici. Si dimostra che se uno schema R(A) è decomposto nei due schemi R1(A1) e R2(A2) allora la decomposizione gode delle proprietà del join senza perdita se e solo se gli attributi comuni ai due insiemi A1 e A2 della composizione sono chiavi per una delle due relazioni. Decomposizione in prima forma normale. Normalizzare una relazione il cui schema R(A) non sia in prima forma normale significa riportare la relazione ad una equivalente in prima forma normale. Le procedura di normalizzazione sono quelle riportate di seguito – 1 Tutti gli attributi strutturati devono essere sostituiti da tanti attributi quanti sono i valori che appaiono nell'attributo. – 2 tutti gli attributi strutturati diventano attributi semplici e la tupla è rappresentata per mezzo di tante tuple quanti sono i valori contenuti nell'attributo. La prima forma normale elimina le seguenti anomalie: Teoria dei DataBase Ing. Antonio Nicolazzo – 1 Ridondanza dei dati – 2 Anomalia da modifica. La modifica di un valore deve essere altrimenti riportata in ogni attributo che contiene quel valore – 3 Anomalia da inserzione. L' impossibilità di inserire un attributo fin tanto non si è in possesso di tutti i valori che costituiscono quel attributo. – 4 Anomalia da cancellazione. La cancellazione di un valore comporta la cancellazione di tutto l' attributo quindi anche di altre informazioni. Una dipendenza funzionale X → Y è detta completa se per lo schema R(A) non esiste alcuna dipendenza funzionale Z→ Y dove Z è un qualunque sottoinsieme di X. Una dipendenza funzionale è detta parziale se non è completa. Un attributo di una relazione R(A) è detto primo se fa parte di una chiave di R(A) altrimenti è detto non primo. Teoria dei DataBase Ing. Antonio Nicolazzo Una relazione è in seconda forma normale se è in prima forma normale e se tutti gli attributi sono dipendenti dall'intera chiave ossia non sono presenti dipendenze parziali degli attributi non primi dalle chiavi. Una dipendenza Z si definisce dipendente transitivamente da X se si verificano le seguenti condizioni dove → indicano le dipendenze funzionali : – X→YeY→X – Y→ZeZ→Y – X→Z Z non appartiene ne a X ne a Y Una relazione è in terza forma normale se è in seconda forma normale e se tutti gli attributi che non sono chiavi sono mutuamente indipendenti ossia ogni attributo di A non dipende in modo transitivo da ogni chiave di A Si definisce forma normale Boyce – Codd lo schema R(A) se è in terza forma normale e se ogni dipendenza funzionale X → Y e tale che X contiene una superchiave di R(A). Tutti gli attributi primi sono dipendenti solo dagli attributi primi e le uniche dipendenze funzionali sono quelle generate dalle chiavi verso uno o più attributi. Una dipendenza è detta multivalore X → Y se prese due tuple di una relazione R(A) che abbiano gli stessi valori associati agli attributi di A t(X) = s(X) è possibile scambiare alcuni valori di t e s ed ottenere altre due tuple che stanno ancora in R. Si definisce quarta forma normale quando per ogni relazione di dipendenza funzionale molti a molti X → Y, X sia una superchiave. Decomposizione in seconda forma normale. Riporto di seguito le fasi che permettono di scomporre uno schema in seconda forma normale. – 1 Si individua il sottoinsieme Z di X per il quale esiste la dipendenza funzionale Z → Y che rende X → Y parziale. – 2 Si definisce un nuovo schema S(U) dove U è costituito dagli attributi di Z e dagli attributi di Y e gli attributi di Z costituiscono chiave di S= Y U Z – Si eliminano dallo schema R(A) gli attributi di Y Esempio: FORNITURE [Nome Forniture, Indirizzo, Città, Nome Pezzi, Peso, Data, Quantità, Prezzo] DIPENDENZE FUNZIONALI nome fornitura → Indirizzo Città Nome fornitura, Nome Pezzo, Data → Indirizzo, Città Nome Pezzo → Peso DIPENDENZE CHE RENDONO LA DIPENDENZA X → Y PARZIALE Nome Fornitura → Indirizzo, Città Nome fornitura, Nome Pezzo, Data → Indirizzo, Città Nome Pezzo → Peso Teoria dei DataBase Ing. Antonio Nicolazzo (1) (2) (3) Nome fornitura, Nome Pezzo, Data → Peso (4) DECOMPOSIZIONE Per eliminare la dipendenza parziale (1) e (2) si considera il nuovo schema [Nome fornitura, Indirizzo, Città] e si cancellano gli attributi Indirizzo e Città dallo schema iniziale. In modo analogo per eliminare la dipendenza parziale (3) e (4) si definisce lo schema [Nome Pezzo, Peso]e si cancellano l' attributo Peso dallo schema iniziale. Lo schema equivalente decomposto in seconda forma normale è il seguente [Nome Fornitura, Nome Pezzo, Data, Qualità, Prezzo] [Nome Fornitura, Indirizzo, Città] [Nome Pezzo, Peso] Decomposizione in terza forma normale. Riporto di seguito le fasi che permettono di scomporre uno schema in terza forma normale. – 1 Dato lo schema R(A) per ogni dipendenza transitiva si determina una dipendenza funzionale X → Y, Y → Z dove X è una chiave di R(A) e Y è costituito da attributi non primi. – 2 Si definisce uno schema S(U) dove U è costituito dagli attributi di Y e dagli attributi di Z e gli attributi di Y costituiscono la chiave di S – 3 Si eliminano dallo schema R(A) gli attributi di Z ESEMPIO: Consideriamo lo schema AUOTOMBILI [Targa, Marca, Data, Colore, Modello, Cilindrata, Freni_Anteriori, Freni_Posteriori] INDIVIDUIAMO LE SEGUENTI DIPENDENZE TRANSITIVE Targa → Modello (1) X → Y Modello → Cilindrata, Freni_Anteriori, Freni_Posteriori (2) Y → Z Targa → Cilindrata, Freni_Anteriori, Freni_Posteriori (3) X → Z DECOMPOSIZIONE Si definisce la nuova relazione S(U) [Modello, Cilindrata, Freni_Anteriori, Freni_Posteriori] = Y U Z e la relazione ottenuta da R(A) eliminando gli attributi di Z [Targa, Marca, Data, Colore, Modello] = A – Z Teoria dei DataBase Ing. Antonio Nicolazzo Esempi: Scomposizione in prima forma normale La relazione che segue non è in prima forma normale: NOME INDIRIZZO Rossi & C. s.p.a Via Verdi, 5 Roma Martini s.n.c. Via Centrale, 4 Milano Paoli s.a.s. P.za Libertà, 12 Pisa In quanto l’attributo INDIRIZZO non è atomico: può essere suddiviso in Indirizzo e Città. La relazione si normalizza trasformandola in: NOME INDIRIZZO CITTA' Rossi & C. s.p.a Via Verdi, 5 Roma Martini s.n.c Via Centrale, 4 Milano Paoli s.a.s. P.za Libertà, 12 Pisa Scomposizione in seconda forma normale Abbiamo una relazione che rappresenta delle scrivanie da ufficio prodotte da una azienda industriale, la chiave primaria è data Tipo_tavolo e Tipo_legno. Tipo_tavolo Tipo_legno Tipo_finitura Manager Mogano Ottone Manager Ciliegio Acciaio inox Montecarlo Noce Sughero Montecarlo Mogano Ottone Top Noce Sughero Tipo_finitura è dipendente funzionalmente da un sottoinsieme della chiave primaria, ovvero da Tipo_legno, dunque la tabella non è in 2NF. La relazione funzionale è Z → Y dove: Z =[Tipo_legno] e Y=[Tipo_finitura] da cui per le decomposizioni viste la relazione decomposta risulta la seguente: S= Y U Z =[Tipo_finitura, Tipo_legno] A = X – Y =[Tipo_tavolo, Tipo_legno] Tipo_tavolo Tipo_legno Manager Mogano Manager Ciliegio Montecarlo Noce Montecarlo Mogano Teoria dei DataBase Ing. Antonio Nicolazzo Top Noce Tipo_legno Tipo_finitura Mogano Ottone Ciliegio Acciaio inox Noce Sughero Teoria dei DataBase Ing. Antonio Nicolazzo Scomposizione in terza forma normale: Consideriamo la seguente relazione: Fattura= [codice_fattura, data, importo, codice_cliente, nome_cliente] La relazione è in prima forma normale in quanto non è composta da attributi non semplici. È in seconda forma normale perché non contiene dipendenze parziali degli attributi non primi dalle chiavi. La relazione Fattura non è in terza forma normale perchè contiene la dipendenza transitiva seguente: codice_fattura → codice cliente codice_cliente → nome_cliente codice_fattura → codice_cliente → nome_cliente X→Y Y→Z X→Z Per ottenere la nuova relazione definiamo gli insiemi: S = Y U Z = [codice_cliente, nome_cliente] S2 = A – Z =[ codice_fattura, data, importo, codice_cliente] Possiamo quindi definire l' equivalente relazione in terza forma normale Fattura = [ codice_fattura, data, importo, codice_cliente]; e Cliente =[ codice_fattura, data, importo, codice_cliente]; Teoria dei DataBase Ing. Antonio Nicolazzo PROGETTAZIONE MODELLI DI BASE DATI La progettazione di una base dati si divide in due fasi. La prima fase di progetto riguarda l' analisi del processo che si vuol rappresentare e la definizione di uno schema concettuale della base dati, mentre la seconda fase riguarda la realizzazione della base dati quindi la definizione dello schema della base dati e la realizzazione fisica della base dati. Schema concettuale La definizione della rappresentazione dello schema concettuale della base dati può essere ottenuta con “Il modello semantico” o le “metodologie di progettazione di basi dati”. Ci soffermeremo alla descrizione del modello semantico in quanto è il procedimento per l' analisi concettuale più utilizzato. Modelli Semantici Un modello semantico è un insieme di regole che permette di definire lo schema concettuale di una base di dati ad un livello intermedio tra la realtà e il modello di dati utilizzato per implementare la base di dati. Il modello semantico permette di ottenere prima una descrizione formale della realtà e poi converte tale descrizione nel modello di dati utilizzato dal sistema di gestione della base dati che si ha a disposizione. Il Modello semantico utilizza per la rappresentazione del processo studiato le entità, gli attributi e le corrispondenze. Un entità come già visto è un oggetto che esiste e che è indistinguibile (tabella) mentre gli attributi sono le proprietà che distinguono quell'entità dalle altre (campi). Le proprietà d'altro canto descrivono il significato dei dati che corrispondono all'entità. il primo step della progettazione dello schema della base dati è la scelta dell'entità e degli attributi che costituiscono ciascuna entità. Durante la fase di progettazione si devono anche determinare per ogni entità le chiavi che permette d' individuare l' entità in modo univoco. Spesso si ricorre alla definizione di un attributo che non ha informazioni ma ha l 'unico compito di realizzare la chiave. La chiave esterna è quella chiave che non ha un evidente rapporto concettuale con l' entità, ma ha motivo d' esistenza unicamente per permettere l 'associazione tra entità distinte. Le entità tra cui esiste una relazione si dicono partecipanti, ed il numero di partecipanti indica il grado della corrispondenza unaria, binaria, ternaria,.... La partecipazione di un entità ad un associazione ( corrispondenza) può essere classificata totale o parziale a seconda del fatto che l'entità possa esistere o meno senza partecipare all'associazione stessa. Si indica con la cardinalità il numero massimo di istanze della stessa entità che possono partecipare con una o più istanze di un' altra entità. – Uno a Uno – Uno a Molti – Molti a Molti Più semplicemente la cardinalità indica il numero di records di una tabella che corrispondono ai records di un altra tabella quando tra le due tabelle esiste una corrispondenza. Teoria dei DataBase Ing. Antonio Nicolazzo Corrispondenza Uno a Uno Si definisce una corrispondenza uno a uno se ad ogni elemento dell'insieme di entità A1 x A2 x Ai è associato al più un elemento dell'insieme di entità B1 x B2 x ..Bj e se ad ogni entità dell'insieme B1 x B2 x ..Bj è associato al più un elemento dell'insieme di entità A1 x A2 x Ai. Ricordiamo che l' insieme di entità sono le tabelle. Corrispondenza Uno a Molti Si definisce una corrispondenza uno a molti se ad ogni elemento dell'insieme A1 x A2 x Ai è associato un insieme , anche vuoto, di elementi di B1 x B2 x ..Bj e se ad ogni elemento dell'insieme B1 x B2 x ..Bj è associato al più un elemento di A1 x A2 x Ai Corrispondenza Molti a Molti Si definisce una corrispondenza molti a molti se ad ogni elemento di A1 x A2 x Ai è associato un insieme, anche se vuoto, di elementi di B1 x B2 x ..Bj e se ad ogni elemento di B1 x B2 x ..Bj è associato un insieme, anche se vuoto, di elementi di A1 x A2 x Ai Corrispondenza Molti a Uno Si definisce una corrispondenza molte a uno se in una corrispondenza molti a molti ad ogni elemento di B1 x B2 x ..Bj è associato al più un elemento di A1 x A2 x Ai Corrispondenza is a Una corrispondenza si definisce is a se A è un tipo particolare di B Entità deboli Un insieme di entità B si dice debole se esiste una corrispondenza da un altro insieme di entità A e l'esistenza di ogni entità B è legata all'esistenza di una entità di A. – In un entità debole si possono aggiungere entità solo se esistono le corrispondenti entità dell'insieme A – Se si cancella un entità da un insieme si devono cancellare anche tutte le entità deboli ad essa collegate. Teoria dei DataBase Ing. Antonio Nicolazzo Diagramma Entità corrispondenze il diagramma entità – corrispondenza è la rappresentazione dello schema di una base dati realizzato con il modello entità-corrispondenza il diagramma per la rappresentazione utilizza le seguenti convenzioni Entità Nome entità Entità deboli Nome entità Attributi semplici Nome Attr. Nome entità Attributi non semplici Nome Attr. Nome entità Corrispondenze Nome entità Nome entità La cardinalità della corrispondenza è indicata con un numero nel punto in cui la linea che individua la corrispondenza incontra l' entità. Corrispondenza 1 a 1 1 Corrispondenza 1 a Molte 1 1 M Teoria dei DataBase Ing. Antonio Nicolazzo Corrispondenza molte a molte M Corrispondenza zero a molte 0 N N PROGETTAZIONE Per realizzare una progettazione del sistema di gestione della base dati occorre realizzare le seguenti analisi preliminari: – Determinazione della specifica informale. La specifica informale è un documento che raggruppa la descrizione dei dati e delle proprietà dei processi che devono utilizzare la base dati e delle esigenze degli utenti. – Definizione del sistema di gestione della base dati. Progetto dello schema. – Configurazione hardware e del sistema informatico per la gestione del sistema. Specifica informale Definizione del sistema Di gestione della base dati Configurazione hardware del sistema informatico Schema logico Progetto Struttura di memorizzazione Sottoschemi utente Specifica dei programmi applicativi Teoria dei DataBase Ing. Antonio Nicolazzo La progettazione della base dati è realizzata seguendo a sua volta le quattro fasi sotto riportate: Analisi dei requisiti Progettazione concettuale Progettazione logica Progettazione fisica – L' analisi dei requisiti è l' operazione di determinazione delle richieste degli utenti del sistema che ci accingiamo a progettare. – La progettazione concettuale è la descrizione integrata dei dati e delle operazioni d'interesse per i diversi sottoinsiemi – La progettazione logica è la definizione dello schema logico della base dati e degli sottoschemi. In questa fase si traducono le operazioni di estrazione delle informazioni in funzione dello schema definito. – La progettazione fisica è la scelta della struttura di memorizzazione dei dati PROGETTAZIONE CONCETTUALE La progettazione concettuale è la fase in cui si crea uno schema concettuale ossia una descrizione sotto forma di dati, processi e vincoli. La realizzazione della progettazione concettuale è effettuata mediante il modello entità – corrispondenze. La progettazione concettuale è divisa in due fasi: – modellizzazione degli schemi utente. – Analisi e integrità degli schemi La modellizzazione degli schemi di utente è la progettazione di tanti schemi utente. Lo schema utente è ottenuto come descrizione limitata e parziale del complesso d' informazioni che costituiscono la base di dati senza considerare eventuali relazioni o dipendenze tra essi. L' analisi ed integrità degli schemi utente è la fase in cui è definito uno schema concettuale unico risultato dei diversi schemi utente. In questa fase si individuano eventuali conflitti tra i diversi schemi utente. Conflitti tra gli schemi utente: Si riporta di seguito una descrizione delle cause che determinano conflitti tra gli schemi Teoria dei DataBase Ing. Antonio Nicolazzo utente. Teoria dei DataBase Ing. Antonio Nicolazzo Conflitti espliciti sono quei conflitti di tipo o di proprietà dei dati. a Si generano conflitti di tipo se esistono attributi di tipo differente con lo stesso nome b Si generano conflitti di proprietà dei dati si generano quando la corrispondenza tra due entità è differente a seconda dello schema utente considerato. I conflitti espliciti in generale si sono determinati da due utenti che hanno esigenze differenti sugli stessi dati che si traducono in differenti formalizzazioni dei dati o delle corrispondenze. I Conflitti impliciti sono i sinonimi o gli omonimi. – I sinonimi si determina quando una stessa informazione è rappresentata con nomi differenti nella base dati – Gli omonimi si generano quando con lo stesso nome si indicano informazioni differenti della stessa realtà Solo dopo che sono stati risolti tutti i conflitti si procede con l 'integrazione degli schemi utente per ottenere lo schema concettuale nella forma di modello semantico. In questa fase si procede anche alla determinazione di uno schema concettuale delle operazioni che si ipotizza saranno effettuate sulla base dati. Dopo avere completata la progettazione dello schema concettuale si procede con le verifiche. Le verifiche che si attuano sullo schema concettuale sono le seguenti: – Verifica di completezza con cui si dimostra che gli schemi progettati sono capaci di soddisfare le esigenze delle applicazioni. – Verifica sulla frequenza d' utilizzo dei dati permette di controllare l' efficienza dello schema – Progetto iniziale dei programmi applicativi PROGETTAZIONE LOGICA Con la progettazione logica lo schema concettuale dei dati e quello utente sono tradotti in uno schema logico (capitolo 3) espresso in termini del modello di dati al quale fa riferimento il sistema di gestione della base dati che si realizza. Durante la fase di progettazione logica si studiano anche proprietà che caratterizzeranno il funzionamento del sistema di gestione dei dati. – L' integrità è garantita se i dati contenuti nella base di dati rispettano in ogni momento i vincoli esposti nella fase di analisi dei requisiti (vincoli desiderati dagli utenti) – La consistenza è garantita se ogni utente riceve le stesse informazioni se interroga nello stesso modo la base dati a patto che non siano stati modificati i dati Teoria dei DataBase Ing. Antonio Nicolazzo – Le procedure di ripristino devono garantire gli utenti da qualunque tipo di malfunzionamento – Il sistema deve impedire accessi non autorizzati per il rispetto delle norme di sicurezza. La progettazione logica deve essere realizzata mediante le tre fasi di seguito riportate: Traduzione canonica Progettazione sottoschemi Prima progettazione Di principio dei programmi – Traduzione canonica è la traduzione dello schema concettuale in uno schema logico equivalente in termini del modello di dati utilizzato ( relazionale, gerarchico, reticolare) – Progettazione dei sottoschemi equivalenti agli schemi utente. – Progettazione delle specifiche dei programmi applicativi ossia delle operazioni che permetteranno ai programmi di accedere alle informazioni di loro interesse. PROGETTAZIONE FISICA La progettazione fisica riguarda gli aspetti più hardware e realizzativi del sistema di gestione della base di dati. Di seguito si riportano alcuni degli aspetti analizzati e definiti durante la progettazione fisica. – Tecniche di organizzazione degli archivi – Dimensionamento delle aree di overflow – Tecniche di allocazione degli archivi sulle memorie di massa – Scelta del fattore di blocco per i diversi archivi – Definizione dei puntatori utilizzati negli archivi – Scelta degli indici di manutenzione e efficienza per il monitoraggio del sistema. Teoria dei DataBase Ing. Antonio Nicolazzo ESEMPI DI RPOGETTAZIONE PRENOTAZIONI AD ALBERGHI Descrizione informale del processo • Il database deve rappresentare le prenotazioni fatte da clienti ad un albergo. • Si vogliono mantenere informazioni sui clienti e sulle prenotazioni. • Dei clienti si vogliono mantenere dati identificativi. • Si vogliono mantenere dati identificativi dell'albergo, il numero di camere (suddivise in singole, doppie, triple) • Delle prenotazioni si vogliono mantenere le informazioni riguardo a chi l'ha fatta e per quale albergo, la data di arrivo e quella presunta di partenza (o il numero presunto dei giorni prenotati), quale tipo di stanza un cliente ha prenotato. Ogni cliente può' prenotare più' stanze nello stesso periodo. • Si voglia mantenere dati riguardo lo stato di occupazione reale dell'albergo mediante registrazioni Progettazione concettuale mediante i modelli entità-corrispondenze utente. Schema Utente 1 Schema Utente 2 cliente 1 cliente cliente 1 1 1 N prenotazione N prenotazione 1 1 1 Registrazione Schema Logico 1 Registrazione 1 1 Registrazione Nei due schemi utente ottenuti abbiamo definito tre entità: – Clienti – Prenotazioni – Registrazioni Dettaglio delle entità: Clienti[Nome, Cognome, Indirizzo, Recapito] Prenotazione[Data, giorni, nome_Cliente, Recapito_Cliente, Stanza, Tipo, Prezzo] Registrazione[Data_registrazione, Cliente, recapito_Cliente, Camera] Teoria dei DataBase Ing. Antonio Nicolazzo Progettazione Logica Analizziamo le entità per verificarne la normalizzazione. Clienti si presenta come una entità in 3 Forma normale perché gli attributi sono semplici e non si presentano dipendenze funzionali al di eccetto quella dalla chiave Id_Cliente Clienti[Id_Cliente,Nome, Cognome, Indirizzo, Recapito] L'entità Prenotazione è ancora in prima forma normale in quanto i suoi attributi sono semplici, non è in seconda forma normale in quanto i suoi attributi non primi presentano delle dipendenze funzionali tra di essi, non è in terza forma normale dato che esiste una dipendenza funzionale transitive tra gli attributi non primi. Decomposizione: Entità Prenotazione[Id_Prenotazione, Data, nome_Cliente, Recapito_Cliente, Stanza, Tipo, Prezzo, Numero_Prenotati] Dipendenze funzionali (1) Numero Stanza → Tipo, numero Letti, posizione nell'albergo (2) Tipo, numero Letti, posizione nell'albergo → Prezzo (3) Numero Stanza → Prezzo (4) Nome Cliente → Recapito Cliente (5) Per eliminare la dipendenza funzionale (5) si crea un connessione con l' entità clienti sostituendo i campi Nome-Cliente, Recapito_Cliente con il campo Id_Cliente che fa riferimento alla tabella Clienti. Per correggere le dipendenze transitive (1), (2), (3) si definisce l'entità Camere [Numero_Stanza ,Tipo, numero Letti, posizione nell'albergo, Prezzo] e l'entità Prenotazioni[Id_Prenotazioni, Data, Id_Cliente, Numero_Stanza, Numero_Prenotati] L'entità Registrazione presenta delle dipendenze funzionali dovute alla presenza dei dati inerenti il Cliente e la stanza occupata. Tali dipendenze possono essere risolte utilizzando le chiavi Id_Cliente e Numero_Stanza. In caso di prenotazione in realtà esiste anche una dipendenza tra il Cliente e La stanza occupata. Il problema può essere superato o creando due tabelle di registrazione una per i clienti prenotati e una per i clienti senza prenotazione. La registrazione dei Clienti prenotati elimina la dipendenza funzionale tra Id_Cliente e il numero della Stanza semplicemente riportando il campo Id_Prenotazione. Registrazione[Data_registrazione, Id_Cliente, Numero_Camera, Numero_Prenotati] Registrazione_Prenotazione[Data_Registrazione, Id_Prenotazione, Numero_Prenotati] Un altro modo per ottimizzare le tabelle e di realizzare per tutti i clienti che alloggiano nell'albergo una specie di prenotazione e in tal caso la tabella registrazione farà sempre riferimento alla prenotazione reale o no. Dall'analisi compiuta il database risulta composto dalle seguenti entità: CLIENTI[Id_Cliente,Nome, Cognome, Indirizzo, Recapito]; CAMERE [Numero_Stanza ,Tipo, numero Letti, posizione nell'albergo, Prezzo]; PRENOTAZIONI[Id_Prenotazioni, Data_Inizio, Id_Cliente, Numero_Stanza]; REGISTRAZIONI[Data_registrazione, Id_Cliente, Numero_Camera, Numero_Prenotati]; Teoria dei DataBase Ing. Antonio Nicolazzo Progettazione degli schemi delle operazioni. Dall'analisi descrittiva del processo che si vuol modellare con il database evince che l' utente vuole ottenere dal sistema della base dati le seguenti informazioni: – – – – – – – numero e tipo delle camere dell'albergo prenotazioni in corso numero clienti presenti nell'albergo guadagno lordo annuo dell'albergo importo annuo da ogni cliente frequenza annua di utilizzo di ogni stanza stato di occupazione di ogni stanza Il quesito (1) può essere soddisfatto semplicemente visualizzando il contenuto della tabella Camere. Interrogazione espressa in SQL: SELECT * FROM Camere Il quesito (2) non può essere risolto con le informazioni riportate nel database pensato perché sappiamo quali sono le prenotazioni e la data d' inizio ma non la data di termine. Per ottemperare al quesito (2) dobbiamo aggiungere il campo giorni_prenotazioni nella relazione Prenotazioni. Il quesito (2) è risolto dall'interrogazione della tabella Prenotazioni selezionando le tuple che hanno data_fine prenotazione inferiore alla data attuale. La data di fine prenotazione è data dalla data inizio prenotazione sommata ai giorni di prenotazione. Interrogazione espressa in SQL: SELECT (Data_Inizio + giorni) as Data_Fine FROM PRENOTAZIONI WHERE Data_Fine >= Data_Attuale L'espressione SQL riportata non tiene conto dell'eventuale formato delle data Data_Inizio e giorni per semplicità di esposizione. Il quesito (3) ossia i clienti in albergo non si può ottenere dalle prenotazioni in corso in quanto non sappiamo quante delle prenotazioni sono effettivamente realizzate pertanto dovremo dedurlo dallo stato dell'albergo ossia dall'analisi delle registrazioni. Per individuare le camere occupate dovremo selezionare le registrazioni che si riferiscono alle prenotazioni non ancora terminate. Il numero di clienti in albergo è data dalla somma degli ospiti di ogni camera occupata nella data attuale. Per definire l' interrogazione in SQL notiamo che il data di nostro interesse è in realtà il numero di clienti per ogni prenotazione ancora non terminata e registrata. Poiché l' interrogazione coinvolge due tabelle utilizzeremo un query composta. Interrogazione SQL: SELECT ADD(Numero_Prenotanti) FROM PRENOTAZIONI WHRE (Data_Inizio + giorni) >= Data_Attuale 'Prenotazioni in corso AND Id_Prenotazioni = (SELECT Id_Prenotazioni FROM REGISTRAZIONI) 'Prenotazioni presenti Teoria dei DataBase Ing. Antonio Nicolazzo Il quesito (4) è calcolato sommando l' importo corrispondente ad ogni prenotazione realizzata. L' importo di ogni prenotazione è determinabile dalla camera prenotata ed è memorizzato nella tabella Camere. Quindi riassumendo si ottiene la risposta al quesito (4) sommando i prezzi delle camere prenotate ed occupate in nell'anno considerato SELECT ADD(Prezzo) FROM CAMERE inner join PRENOTAZIONI on PRENOTAZIONI.Numero_Camera = CAMERE.Numero_Camera WHERE PRENOTAZIONI .Data_Inizio >= (Data Inizio Anno) AND PRENOTAZIONI.(Data_Inizio + Giorni) <= (Data fine Anno) il quesito (5) può essere soddisfatto da un interrogazione analoga a quella del quesito (4) con la differenza che gli importi devono essere raggruppati per Cliente Interrogazione SQL: SELECT ADD(Prezzo) FROM CAMERE inner join PRENOTAZIONI on PRENOTAZIONI.Numero_Camera = CAMERE.Numero_Camera WHERE PRENOTAZIONI .Data_Inizio >= (Data Inizio Anno) AND PRENOTAZIONI.(Data_Inizio + Giorni) <= (Data fine Anno) GROUP BY PRENOTAZIONI .Id_Cliente La query mostrata non ci permette di risalire al nome del cliente. Per ottenere come risultato della query anche il nome cliente è necessario sostituire la tabella Prenotazioni con una tabella allargata Prenotazioni_E_DatiClienti che oltre alle prenotazioni consideri i dati dei clienti. La tabella Prenotazioni_E_DatiClienti si ottiene con la seguente join Prenotazioni inner join Clienti on Clienti.Id_Cliente = Prenotazioni.Id_Cliente sostituiamo nella query sopra riportata la tabella Prenotazioni_E-DatiClienti otterremo la seguente espressione SQL: SELECT ADD(PRENOTAZIONI_CLIENTI.Prezzo) , PRENOTAZIONI_CLIENTI.Nome_Cliente FROM CAMERE inner join (Prenotazioni inner join Clienti on Clienti.Id_Cliente = Prenotazioni.Id_Cliente) as PRENOTAZIONI_CLIENTI on PRENOTAZIONI_CLIENTI.Numero_Camera = CAMERE.Numero_Camera WHERE PRENOTAZIONI_CLIENTI .Data_Inizio >= (Data Inizio Anno) AND PRENOTAZIONI_CLIENTI.(Data_Inizio + Giorni) <= (Data fine Anno) GROUP BY PRENOTAZIONI_CLIENTI .Id_Cliente Conclusioni: Considerando le correzioni apportate alla tabella Prenotazioni per rendere possibili tutte le operazioni desiderate sulla base di dati lo schema logico finale del database è il seguente: CLIENTI[Id_Cliente,Nome, Cognome, Indirizzo, Recapito]; CAMERE [Numero_Stanza ,Tipo, numero Letti, posizione nell'albergo, Prezzo]; PRENOTAZIONI[Id_Prenotazioni, Data_Inizio, Id_Cliente, Numero_Stanza, Giorni]; REGISTRAZIONI[Data_registrazione, Id_Cliente, Numero_Camera, Numero_Prenotati]; Teoria dei DataBase Ing. Antonio Nicolazzo PROGETTAZIONE DI UN DATABASE AZIENDA Supponiamo di creare un database che memorizzi le attività o mansioni svolte dai vari dipendenti di un azienda. Il database che vogliamo creare ha il seguente schema concettuale: Addetti N 1 N CategoriaAddetti N Attività Progettazione Logica Ogni addetto è correlato ad una sola CategoriaAddetti mentre ad una CategoriaAddetti possono appartenere più addetti. La relazione è uno a molti, CategoriaAddetti è la tabella primaria. Si duplica la chiave primaria di tale tabella nella tabella Addetti. Ogni addetto può svolgere più mansioni o attività e un attività può essere svolta da più addetti. La relazione è molti a molti, si crea quindi una tabella supplementare, AddettiAttività, che contiene le chiavi esterne, duplicati delle chiavi primarie delle tabelle Attività e Addetti. Entità: CategoriaAddetti; Addetti; Attività; Modello entità -corrispondenze Addetti N 1 1 CategoriaAddetti N AddettiAttività N 1 Attività Schema entità: Addetti [Id_Addetti, Nome, Cognome, recapito, Id_Categoria] CategoriaAddetti[Id_Categoria, Livello, tipologia] AddettiAttività[Id_Addetti, Id_Attività] Attività[Id_Attività, Data_Inizio, Data_fine, obiettivi, Nome_Attività] Teoria dei DataBase Ing. Antonio Nicolazzo Teoria dei DataBase Ing. Antonio Nicolazzo Progettazione degli schemi delle operazioni. L'utente vuole ottenere dal sistema della base dati le seguenti informazioni: – – – – 1 Quanti dipendenti sono attualmente impiegati 2 Quali sono i dipendenti impiegati in una determinata attività 3 Quante e quali attività sono attualmente in cantiere 4 Quale attività richiede una numero maggiore di dipendenti per essere eseguita Il quesito (1) è risolubile facilmente interrogando la tabella ADDETTI e selezionando i dipendenti riportati in tale tabella Interrogazione SQL: SELECT Nome, Cognome FROM ADDETTI Il quesito (2) è soddisfatto determinando id_Attività dalla tupla tabella ATTIVITÀ corrispondente alla attività considerata. Si utilizza Id_Attività selezionato per determinare gli Id_Addetti corrispondenti riportati nella tabella ADDETTIATTIVITÀ e con tali valori si legge il nome dei dipendenti dalla tabella Addetti. Interrogazione SQL: SELECT Addetti .Nome, Addetti .Cognome FROM ADDETTI rigth join ADDETTIATTIVITÀ ON ADDETTI.Id_Addetti = ADDETTIATTIVITÀ .Id_Addetti WHERE ADDETTIATTIVITÀ Id_Attività = (SELECT Id_Attività FROM ATTIVITÀ WHERE Nome_Attività = 'Attività considerata') Il quesito (3) è soddisfatto selezionando le attività contenute nella tabella Attività con data_fine prevista successiva alla data attuale. Interrogazione SQL: SELECT Nome_Attività FROM ATTIVITÀ WHERE Data_Fine > 'Data Attuale' Il quesito (4) richiede di selezionare l' attività con il maggior numero di dipendenti. Il numero di dipendenti impiegati in una Attività lo possiamo determinare dalla tabella ADDETTIATTIVITÀ raggruppando per attività differenti e contando gli Id_Addetti corrispondenti per ogni attività. Dopo avere selezionato il numero di dipendenti impiegato in ogni attività dovremo individuare il numero massimo. Interrogazione SQL: SELECT MAX(COUNT(Id_Addetti)) FROM ADDETTIATTIVITÀ GROUP BY Teoria dei DataBase Ing. Antonio Nicolazzo Id_Attività
