S Q L

1. S Q L breve presentazione curata da Aldo Pappalepore

2. Cosa è l ' SQL ? L’SQL (Structured Query Language) è il linguaggio per l’accesso ai database (base di dati), sia per l’interrogazione che per la creazione, la modifica e la gestione degli stessi. SQL non è un programma ma uno standard definito dall’ANSI (Ame- rican National Standards Institute) e dall’ISO (International Standards Organization). Lo standard SQL viene invece implementato in speciali programmi chiamati DBMS (Data Base Management System) o sistemi per la ge- stione di basi di dati, di cui i più famosi sono Oracle, Microsoft SQL Server, Sybase e Mysql. Il fondamento formale di SQL è il modello relazionale basato sulla teo- ria matematica di relazioni tra insiemi. sql: introduzione

3. Cosa è un database? • Un database (base di dati) è l’insieme dei dati utilizzati in uno specifico • sistema informativo, di tipo aziendale, scientifico, gestionale, ammini- • strativo, etc. • Un database è composto da due diversi tipi di informazione, appartene- • ti a distinti livelli di astrazione: • i dati, che rappresentano le entità del sistema da modellare e che si esprimono in termini di valori (di tipo numerico, alfanumerico, etc.). I dati sono raggruppati in categorie in base alla loro struttura comune (come Libri, Autori dell’esempio 1). • le strutture (metadati), che descrivono le caratteristiche comuni delle varie categorie di dati quali i nomi ed i tipi dei valori con cui si espri- mono. sql: database

4. Esempio 1 Il database utilizzato in una biblioteca può contenere dati relativi aiLibri ed agli Autori raggruppati nelle due tabelle che seguono: Libri sql: database

5. Esempio 1....... Autori I dati sono quelli scritti in bluLe strutture, composte dai nomi (in rosso) dei dati e dal loro tipo,può essere descritto nel modo seguente: sql: database

6. Esempio 1....... Libri:Codice_Libro: numericoTitolo: testo(50)Collocazione: testo(10) AutoriCodice_Autore: numericoCognome: testo(30)Nome: testo(30) sql: database

7. Cosa è un database......... Un database, oltre ai dati veri e propri, deve rappresentare anche lerelazioni fra i dati, ovvero le connessioni logiche presenti tra le varie categorie di dati. Per esempio deve essere rappresentata l’associazione che lega ciascunautore ai propri libri e viceversa. sql: database

8. Cosa è un database......... • Il database deve inoltre rispondere ai seguenti requisiti: • i dati devono essere organizzati con ridondanza minima, ossia non essere inutilmente duplicati e ciò sia per ridurre la memoria impegna- ta sia per evitare la gestione di copie multiple. • i dati devono essere utilizzabili contemporaneamente da più utenti. Bisogna cioè evitare che ogni utente lavori su una propria copia dei dati (vedi sopra); deve esistere una sola versione dei dati, cui tutti gli utenti possano accedere senza che si generino conflitti per l’eventua- le uso contemporaneo dei dati. • i dati devono essere permanenti e cioè devono essere preservati in caso di malfunzionamento del sistema. sql: database

9. I DBMS Il DataBase Management System (DBMS), o sistema per la gestione di basi di dati, è il programma che consente la gestione di uno o piùdatabase secondo i requisiti prima indicati. Prima dei DBMS l’archi-viazione dei dati avveniva attraverso l’utilizzo diretto delle strutturedel file system.Nell’approccio file system ogni applicazione accede direttamente agliarchivi dei dati con la conseguenza che deve conoscere la struttura in-terna degli archivi, deve farsi carico di rappresentare le relazioni tra i dati, deve consentire l’utilizzo contemporaneo degli archivi da parte dipiù applicazioni, etc..Nell’approccio DBMS invece le applicazioni rivolgono le proprie ri-chieste di accesso al DBMS, che gestisce i dati svincolando le applica-zioni da questo onere (figura 1). sql: DBMS

10. Figura 1 appl. 1 appl. 2 appl. 3 appl. 1 DBMS BaseDati appl. 2 appl. 3 sql: DBMS

11. MODELLI DEI DATI Per passare da una realtà di interesse alla realizzazione di un databasesi fa riferimento ai modelli da utilizzare nelle varie fasi di questo per-corso.Un modello stabilisce le convenzioni per esprimere i diversi aspettidella realtà d’interesse e costituisce un supporto alla sua rappresenta-zione che viene definita schema.Il processo di progettazione di un database si articola in tre fasi fonda-mentali, ciascuna delle quali si riferisce ad un diverso livello di astra-zione. Le tre fasi sono: progetto concettuale, progetto logico e progettofisico (figura 2).La scomposizione in fasi del processo di progettazione ha lo scopo diseparare le diverse risoluzioni dei problemi e poter modificare le solu-zioni dei livelli inferiori senza toccare quelle dei livelli superiori.A ciascuna fase di progettazione coorispondono specifici modelli perla rappresentazione dei dati. sql: modelli dei dati

12. Figura 2 realtà d’interesse modello concettuale Schema concettuale modello logico Schema logico modello fisico Schema fisico sql: modelli dei dati

13. MODELLI DEI DATI........ • progettazione concettuale:Obiettivo della fase di progettazione concettuale è la rappresentazionecompleta ed efficace della realtà d’interesse ai fini informativi, in ma-niera indipendente da qualsiasi specifico DBMS. Tale rappresentazio-ne, detta schema concettuale, è la rappresentazione più astratta, ovveropiù vicina alla logica umana, nella definizione di dati e relazioni.Tra i modelli dei dati usati nella progettazione concettuale il più diffu-so è il modello Entity-Relationship (E-R, Entità-Relazione) che intro-duce una rappresentazione grafica dello schema concettuale.Il modello E-R prevede come prime attività della progettazione concet-tuale 1) l’individuazione di oggetti concreti o astratti rilevanti per il si-stema informativo, e 2) la loro classificazione in insiemi omogenei det-ti entità rappresentati mediante rettangoli. sql: modelli dei dati

14. MODELLI DEI DATI........ • progettazione concettuale:Le proprietà caratteristiche di ciascuna entità, e quindi di ciascun og-getto che ne fa parte, vengono descritte mediante gli attributi.Per esempio attributi dell’entità Veicolo possono essere:Targa, Cilin-drata, Combustibile, Cavalli Fiscali, Velocità, Posti, Immatricolazione. sql: modelli dei dati

15. MODELLI DEI DATI........ • progettazione concettuale:Ciascun attributo è caratterizzato da un nome e da un dominio, che rappresenta l’insieme dei valori che può assumere; per esempio l’attributo Posti potrebbe assumere valori nel dominio {1,2,3,4,5}.Il modello prevede la rappresentazione di vincoli di integrità i quali descrivono le regole che soddisfano gli oggetti della realtà. Per esempio nel rettangolo dell’entità Veicolo è sottolineato l’attributo Targa sta rappresentare il vincolo d’integrità per cui ad ogni targa corrisponde uno ed un solo veicolo.Le dipendenze logiche o associazioni tra i dati da rappresentare ven-gono espresse nello schema E-R mediante relazioni tra le corrispon-denti entità. Graficamente una relazione viene rappresentata da un arco che collega i rettangoli che raffigurano le due entità correlate, interrotto da un ovale che contiene il nome della relazione. sql: modelli dei dati

16. MODELLI DEI DATI........ • progettazione concettuale:Una relazione R tra due insiemi di entità E1 ed E2 viene classificata inbase alla sua cardinalità. • R ha cardinalità 1:1 (uno a uno) se ad un elemento di E1 può corri- spondere un solo elemento di E2 e viceversa.Un esempio di relazione 1:1 è quella tra nazioni e capitali: 1 1 R-1 sql: modelli dei dati

17. MODELLI DEI DATI........ • progettazione concettuale: • R ha cardinalità 1:N (uno a molti) se ad ogni elemento di E1 posso- no corrispondere più elementi di E2, mentre ad ogni elemento di E2 corrisponde al massimo un elemento di E1. Un esempio di relazione 1:N è quella tra ordini e righe_ordine: 1 N R-2 sql: modelli dei dati

18. MODELLI DEI DATI........ • progettazione concettuale: • R ha cardinalità N:N (molti a molti) se ad ogni elemento di E1 pos- sono corrispondere più elementi di E2 e viceversa. Un esempio di relazione N:N è quella tra libri ed autori: N N R-3 Il modelle E-R prevede che anche le relazioni possano avere degli attributi che ne specifichino le caratteristiche.Vediamo ora un esempio (esempio 4) di schema concettuale. sql: modelli dei dati

19. Esempio 4 • In un ufficio di Motorizzazione bisogna realizzare, nell’ambito del sistema informativo, il database Registro Automobilistico in grado di rappresentare la seguente realtà d’interesse: • di ciascun veicolo interessa registrare la targa, la cilindrata, i cavalli fiscali, la velocità, il numero di posti, la data di immatricolazione; • i veicoli sono classificati in categorie (automobili, ciclomotori, ca- mion, rimorchi,ecc.); • ciascun veicolo appartiene a uno specifico modello; • tra i dati relativi ai veicoli vi è anche il tipo di combustibile usato; • di ciascun modello di veicolo va registrata la fabbrica di produzione ed il numero delle versioni prodotte; • ciascun veicolo può avere uno o più proprietari, che si succedono nel corso della durata del veicolo e di cui interessa la data di acquisto e la data di cessione del veicolo; di ciascun proprietario interessa regi- strare cognome, nome ed indirizzo di residenza. sql: modelli dei dati

20. Esempio 4....... Nel progetto concettuale vengono individuate le seguenti entità: sql: modelli dei dati

21. Esempio 4....... sql: modelli dei dati

22. Esempio 4....... • Tra tali entità sussistono le seguenti relazioni: • Categoria e Veicolo sono in relazione 1:N, perché ciascuna categoria è comune a più veicoli, mentre un veicolo può appartenere ad 1 cat; • Modello e Veicolo sono in relazione 1:N, perché ciascun modello è comune a più veicoli e ciascun veicolo è di un solo modello; • Fabbrica e Modello sono in relazione 1:N, perché una fabbrica può produrre più modelli, mentre un determinato modello viene prodot- to da una sola fabbrica; • Combustibile e Veicolo sono in relazione 1:N, perché un tipo di com- bustibile è comune a più veicoli, mentre ciascun veicolo utilizza un solo tipo di combustibile; • Veicolo e Proprietario sono in relazione N:N, in quanto una persona può possedere più veicoli e più persone possono succedersi nella proprietà di un veicolo; la relazione ha come attributi la data di acquisto e la data di cessione del veicolo. sql: modelli dei dati

23. Figura 3 1 1 R - 3 R - 1 N N N R - 2 1 N N R - 5 R - 4 1 Data_AcquistoData_Cessione N sql: modelli dei dati

24. PROGETTAZIONE LOGICA La fase di progettazione logica del database consiste nel convertire loschema concettuale di base in un modello logico (schema logico) dei dati. Un modello logico dei dati è la tecnica di organizzazione ed accessoai dati utilizzata dai singoli DBMS. Lo schema logico allora dipende strettamente dal tipo di DBMS utilizzato ed in particolare dal suo mo-dello logico dei dati. Un ulteriore compito della progettazione logica è di individuare allointerno dello schema logico del database le parti rilevanti per le appli-cazioni quali le viste. sql: modelli dei dati

25. IL MODELLO RELAZIONALE.... Per comodità di rappresentazione è conveniente descrivere una relazionealgebrica in forma di tabella come in figura: In base a questa rappresentazione i concetti di relazioni algebriche, di n-uple e di attributi vengono indicati con i termini più familiari di ta-belle, di righe e di colonne. Nel seguito il termine “tabella” sostituirà quello di “relazione algebri-ca”, mentre il termine “relazione” indicherà, come nel modello Entità-Relazione, un’associazione tra dati. sql: database relazionali

26. IL MODELLO RELAZIONALE.... • Dalla definizione di tabella (relazione) come insieme di righe (e-nuple) derivano due conseguenze fondamentali: • in una tabella non possone esistere due righe uguali; • l’ordine tra le righe di una tabella non è significativo. • Da ciò consegue che è possibile e/o necessario individuare in ciascuna ta-bella uno o più attributi (colonne) in base ai quali possono venire identi-ficate le singole righe e che rappresentano quindi una chiave di accesso u-nivoca alle informazioni contenute nella tabella stessa. • Questa singola (o questo insieme di) colonna/e, che va definita/o in fase di creazione dello schema logico, è detto chiave primaria (primary key) della tabella. sql: database relazionali

27. IL MODELLO RELAZIONALE.... • Nel passaggio dalla progettazione concettuale a quella logica, per la crea-zione dello schema logico relazionale, partendo dallo schema concettualedefinito in base al modello Entità-Relazione, è necessario applicare le re-gole seguenti: • le entità (entity set) dello schema concettuale divengono tabelle nello schema logico; • le relazioni (relation set) dello schema concettuale vengono rappresenta-te nello schema logico facendo uso delle cosidette chiavi esterne. Una chiave esterna (foreign key) di una tabella è un insieme di attributi che corrispondono a quelli che costituiscono la chiave primaria di un’altra ta-bella, e stabiliscono quindi un riferimento tra le righe delle due tabelle. • Nella rappresentazione di una relazione tra le tabelle T1 e T2 bisogna di-stinguere tra le cardinalità 1:1, 1:N, N:N individuate nello schema concet-tuale. sql: database relazionali

28. IL MODELLO RELAZIONALE.... Relazione 1:1 Agli attributi di T1 vanno aggiunti, come chiave esterna, gli attributi che costituiscono la chiave primaria di T2, o alternativamente a T2 vanno ag-giunti, come chiave esterna, gli attributi che costituiscono la chiave pri-maria di T1. Le due soluzioni sono del tutto equivalenti. Per esempio,considerate le tabelle: Nazioni: Capitali: sql: database relazionali

29. IL MODELLO RELAZIONALE.... la relazione tra esse viene rappresentata estendendo la tabella Nazioni nelmodo seguente: Nazioni: Oppure in alternativa estendendo la tabella Capitali nel modo seguente:Capitali: sql: database relazionali

30. IL MODELLO RELAZIONALE.... Relazione 1:N Agli attributi di T2 vanno aggiunti, come chiave esterna, gli attributi che costituiscono la chiave primaria di T1 (il viceversa in questo caso non èequivalente). Per esempio, considerate le tabelle: Ordini: Righe_Ordine: sql: database relazionali

31. IL MODELLO RELAZIONALE.... la relazione tra esse viene rappresentata estendendo la tabella Righe_Or-dine nel modo seguente: Righe_Ordine: sql: database relazionali

32. IL MODELLO RELAZIONALE.... Relazione N:N In questo caso va definita una nuova tabella T3 che contiene, come chiaviesterne, le chiavi primarie sia di T1 che di T2; è da notare come in questo caso la chiave primaria della tabella T3 possa essere costituita dalla totali-tà dei suoi attributi. Per esempio, considerate le tabelle: Libri: ed sql: database relazionali

33. IL MODELLO RELAZIONALE.... Autori: la relazione tra esse viene rappresentatanel modo seguente: Libri-Autori: Come previsto dal modello Entità-Relazione, anche una relazione puòpossedere degli attributi, che vengono inclusi come attributi della tabellain cui è rappresentata la relazione, che contiene le chiavi esterne sql: database relazionali

34. Dallo schema concettuale allo schema logico • Realizziamo ora lo schema logico relazionale del database Registro • Automobilistico di cui già abbiamo costruito lo schema concettuale. • I passi per tradurre lo schema concettuale nello schema logico corri- • spondente sono i seguenti. • Per ciascuna entità dello schema concettuale viene definita una ta-bella nello schema logico: Veicoli, Categorie, Combustibili, Mo-delli, Fabbriche e Proprietari (figura ). sql: database relazionali

35. Tabelle corrispondenti alle Entità sql: database relazionali

36. Dallo schema concettuale allo schema logico.... 2) Per ciascuna tabella viene definito un insieme di attributi con fun-zione di chiave primaria che ne identifichi univocamente le righe.Se tale insieme non è individuabile tra gli attributi della tabella, vaaggiunto un nuovo attributo finalizzato a questo scopo. sql: database relazionali

37. Dallo schema concettuale allo schema logico.... 3) Vengono definite le chiavi esterne (oltre alle interne già presenti)per la rappresentazione delle relazioni 1:N tra Categorie e Veicoli,tra Combustibili e Veicoli, tra Modelli e Veicoli e tra Fabbriche e Modelli. sql: database relazionali

38. Dallo schema concettuale allo schema logico.... 4) Viene definita la nuova tabella Proprietà per la rappresentazione della relazione N:N tra Veicoli e Proprietari. Questa contiene, co-me chiave primaria, le chiavi primarie di Veicoli e Proprietari, egli attributi Data_Acquisto e Data_Cessione, che costituiscono gliattributi della relazione. In figura 4 viene presentata infine una rappresentazione dello sche-ma logico relazionale del database sql: database relazionali

39. Figura 4 1 1 1 N N N N 1 N 1 1 N sql: database relazionali

40. I DBMS • Modalità d’uso: • In un DBMS vengono distinti tre tipi di linguaggi in base alle funzionieseguite sui dati: • DDL (Data Description Language), per la definizione dello schema lo-gico del database; • DML (Data Manipulation Language), per le operazioni di interrogazio-ne e di aggiornamento dei dati quali inserimento, modifica, etc.; • DCL (Data Control Language), per operazioni di controllo dei dati, ge-stione degli utenti, assegnazione dei diritti di accesso, etc. sql: database relazionali

41. ESERCIZIO 1 • Definire lo schema concettuale del database Mobili_Componibili. • La realtà da rappresentare è costituita dai dati, appartenenti al siste-ma informativo di un mobilificio, relativi alla vendita di mobili com- ponibili. In particolare: • degli articoli, che sono suddivisi in categorie, interessa archiviare la descrizione, il prezzo, l’aliquota IVA e le spese di trasporto; • ciascun articolo è costituito da una serie di componenti, di cui vengono archiviati la descrizione e il costo; • i componenti vengono prodotti da laboratori, di ciascuno dei quali vengono memorizzati l’indirizzo, la città ed il telefono; sql: esercitazioni

42. ESERCIZIO 2 Costruire lo schema logico del database Mobili_Componibili aven-do come base lo schema concettuale definito nell’esercizio prece- dente. sql: esercitazioni

43. ESERCIZIO 1 bis • Definire lo schema concettuale del database Archivio_Biblioteca. • La realtà da rappresentare è costituita dai dati, appartenenti al siste-ma informativo di una biblioteca universitaria, relativi alla gestione del suo archivio di libri. In particolare: • dei libri, che sono suddivisi per argomenti, hanno una certa collo-cazione, sono stati scritti da uno o più autori e pubblicati da una casa editrice, interessa archiviare il titolo, la descrizione, il prezzo, l’anno di pubblicazione; • ogni collocazione è individuata da un numero di mobile ed un nu- mero di stanza; • di ogni autore e di ogni casa editrice occorre archiviare l’indirizzo, la città ed il telefono; sql: esercitazioni

44. ESERCIZIO 2 bis Costruire lo schema logico del database Archivio_Biblioteca aven-do come base lo schema concettuale definito nell’esercizio prece- dente. sql: esercitazioni