Archivi categoria: Basi di dati

Guide, Esempi, Video, Sorgenti sulle Basi di Dati, SQL e uso dei principali DBMS.

Stored Procedure in Mysql

Stored Procedure in Mysql

Il linguaggio SQL da principio non era un linguaggio di programmazione, ma solo un linguaggio semantico per gestire le basi di dati.
Con l'utilizzo sempre maggiore dei database in molti ambiti professionali, i software DBMS con l'avanzamento delle versioni hanno cominciato a supportare una serie di clausole sql che permettono al linguaggio SQL di possedere alcune istruzioni base per lo svolgimento procedurale.
Parlando di approccio procedurale non si può non definirlo come il sistema di risoluzioni dei problemi mediante l'algoritmo. Anche mysql permette in modo abbastanza sofisticato di gestire dei semplici algoritmi da essere eseguiti magari su tabelle per svolgere delle funzioni specifiche.
Nasce in Mysql a partire dalla versione 5, la possibilità di scrivere programmi per gestire le tabelle. Tale approccio risulta molto comodo per la gestione di aggiornamenti sui dati, per le interrogazioni, ecc.
Giusto parlando di interrogazioni risulta importante precisare che se ad esempio si vuole interrogare una tabella con un valore inserito dall'utente e a più riprese è opportuno utilizzare delle query con parametri che possono essere implementate solo mediante le stored procedure.
Poiché abbiamo detto che in SQL con le procedure si traducono degli algoritmi è utile ricordare quali sono le componenti dell'algoritmo:
1- Istruzioni di terminazione Inizio e Fine;
2- Istruzioni di assegnazione e calcolo ad esempio X=3, Nome='Andrea' sono due istruzioni di assegnazione, mentre ad esempio Z=x*2 e Nominativo ="Sig" + Nome + Cognome sono istruzioni di calcolo;
3- Istruzioni di condizione (semplice, nidificata, multipla)
Istruzione di condizione semplice

Se condizione allora
istruzione 1
altrimenti
istruzione 2
Fine Se

Istruzione nidificata (combinazione di più istruzioni di condizioni semplici)

Se condizione 1 allora
Se condizione 2 allora
istruzione 1
altrimenti
istruzione 2
Fine Se
altrimenti
Se condizione 3 allora
istruzione 3
altrimenti
istruzione 4
Fine se
Fine se

Ciao devi sottoscrivere un abbonamento per poter visualizzare l'intero articolo.

Approfitta dei numerosi vantaggi per gli iscritti ! Test, Video Lezioni esclusive senza pubblicità oltre tanti articoli sull'informatica dalla programmazione alla sistemistica.
Sei già registrato devi solo sottoscrivere un abbonamento
Buon proseguimento !

 

Linguaggio SQL – 1

Linguaggio SQL - Lezione 1

In questo articolo sono trattati molti argomenti in merito ai Database con particolare riferimento al linguaggio SQL.

introduzione

Il linguaggio SQL "Structered Query Language", è stato ideato negli anni settanta, da un ricercatore americano nei laboratori della IBM nel 1974.
La sua ispirazione, venne dall'introduzione dalla necessità di rappresentare, e operare con i dati logici ovvero le informazioni, senza avere elevati skill informatici.
In seguito il matematico Cood, che ideo il modello "relazionale" per i database, la diffusione di SQL ebbe una diffusione enorme e venne adottato da tutti i software RDBMS "Relation Database Managment System".
La sua potenza, è legata alla possibilità di lavorare in modo astratto sui dati e non sprecare tempo per organizzare, gestire i meccanismi di memorizzazione degli stessi che sono delegati al software DBMS. L'indipendenza della struttura logica del Database dalla struttura fisica come per altro teorizzato dalla regole di Cood, e affermato anche nel modello ANSI/SPARC consente ad operatori informatici di manipolare una grande quantità di dati in tempo reale e senza particolare difficoltà.
In questo articolo, illustro la struttura fondamentale del linguaggio SQL in modo sintetico.
Il linguaggio SQL secondo la teoria dei Database è costituito da un lessico e da una semantica non ambigua che attraverso la scrittura di frasi contenenti la rappresentazione astratta o logica dei dati, consente in poche istruzioni di realizzare operazioni di creazione del database e della sua struttura, di inserire e manipolare i dati, interrogare il database per operazioni di consultazione, e di amministrare il database.
Tali istruzioni sono raggruppate in istruzioni DDL "Data Definition Language", DML "Data Manipulation Language", QL "Query Language", e DCL "Database Control Language". Alcune di queste operazioni non sono permesse a tutti gli utenti ma solo ad utenti che possiedono privilegi sufficienti.

Ciao devi sottoscrivere un abbonamento per poter visualizzare l'intero articolo.

Approfitta dei numerosi vantaggi per gli iscritti ! Test, Video Lezioni esclusive senza pubblicità oltre tanti articoli sull'informatica dalla programmazione alla sistemistica.
Per poter sotto scrivere un abbonamento devi prima registrati e poi sotto scrivere un piano di abbonamento.
Buon proseguimento !

Le basi di dati – Modelli Logici

Basi di Dati – Lezione 1 – Modelli Logici e Fasi della progettazione

Indice dei contenuti

1 – Modelli Logico

In informatica quando è necessario gestire una grande quantità di informazioni omogenee ovvero della stessa tipologia, come elenco delle persone di un comune, elenco dei libri di una biblioteca, ecc due sono le possibili modalità di gestione o con i file o con i database. La differenza nella realizzazione e risoluzione di problemi connessi alla gestione di enormi quanttià di dati risiede nel fatto che con la gestione con i file la velocità di esecuzione e di consultazione delle operazioni sui file sono molto più rapide che nella gestione con i database in quanto si sfruttano attraverso un linguaggio di programmazione istruzioni che agisocono direttamente sui dati, mentre con i database si passa attraverso un gestore chiamato DBMS. Per poter gestire le grandi quantità di dati nella prima modalità gli utenti devono saper programmare e realizzare quindi le procedure che gestiscono i dati. Invece nella gestione a livello di database l’utente deve conoscere al più ma non in modo obbligatorio un linguaggio semantico e informativo ovvero che svolge delle semplici operazioni sulle informazioni. Parlano di informazione e dati si deve però precisare una differenza.
Per dato si intende un fatto raccolto mediante osservazione diretta misurazione. Per informazione si intende una raccolta di dati che si riferiscono in modo univoco e non ambiguo ad un oggetto o ad un soggetto. Un aggregato di dati relativi ad un’informazione si chiama record logico e le sue componenti che possono essere di tipo diverso si chiamano campi. Un insieme di record logici possono essere memorizzate in una tabella che risiede in memoria centrale (vettore di record) oppure essere memorizzati su una memoria permanente come un’unità a disco e in questo caso si parla di file. Nella gestione a livello di programmazione il programma si deve interessare della memorizzazione dei dati sui supporti e quindi del passaggio da una struttura logica ad una fisica. Nella gestione mediante Database l’utente si disinteressa di questo passaggio occupandosi solo del livello logico del problema da risolvere. Inoltre nel primo caso l’utente si deve interessare anche dei meccanismo di accesso ai file da parte di più applicazioni e utenti andando incontro alle problematiche di condivisione dei file. Tutto questo se gestito dai database viene automaticamente soddisfatto dal DBMS.
La definizione di Database è pertanto una collezione di dati strutturati e organizzati alla quale accedono diversi utenti da differenti applicazioni.
Nella gestione a database la situazione che si presenta schematicamente può essere rappresentata dal diagramma sottostante.

Nello schema si evidenzia il fatto che ogni utente interagisce con la base di dati tramite un’applicazione e tale applicazione passa le richieste dell’utente al DBMS che trasforma le richieste delle applicazioni di tipo logiche in operazioni di i/o tramite il sistema operativo sull’archivio fisico. In questo modo l’utente si disinteressa del tutto di quest’ultima gestione. Lo schema proposto non è l’unico possibile anche perché le applicazioni possono interagire fra di loro per scambiarsi dati. Tale scambio però avviene sempre passando attraverso il DBMS. Lo svantaggio di questo approccio risiede nel fatto che sull’elaboratore su cui girano le applicazioni deve essere presente il software di DBMS. Nella realizzazione invece di programmi diretti per la gestione degli archivi invece viene garantita una buona portabilità da elaboratori differenti a sistemi differenti mentre però la flessibilità ovvero la possibilità di modificare parte del programma comporta un grosso sforzo in termini d tempo e di ridefinizione delle strutture dati. Le peculiarità principali di un DBMS e quindi dei database che essi gestiscono sono:

  1. Garantisce l’integrità dei dati, la consistenza, e elimina la ridondanza degli stessi
  2. Gestisce in modo sicuro gli accessi contemporanei ai dati
  3. Gestisce le utenze in livelli di credenziali
  4. Permette all’utente di eliminare le ridondanze
  5. Facilità la gestione di archivi distribuiti
  6. Permette la realizzazione di basi dati relazionali

Per ridondanza si intende la duplicazione di dati in più parti dell’archivio ad esempio se si deve gestire l’archivio degli elettori di un comune in questo archivio possono essere presenti i dati anagrafici degli stessi che però sono anche ripetuti nell’archivio anagrafico del comune. Eliminare le ridondanze non significa però cancellare del tutto i dati duplicati ma tenere solo quelli strettamente necessari. Per eliminare queste ridondanze nel database si ricorre alla creazione di relazioni fra le grandezze informative in gioco. La consistenza è la caratteristica che garantisce ai dati che si propagano nelle varie grandezze informative di avere lo stesso valore. L’integrità nei database garantisce che i dati che sono inseriti all’inertno degli archivi sono coerenti ad esempio nell’inserimento di campi obbligatori e nel corretto formato degli stessi.
La gestione sicura dell’accesso ai dati riguarda la protezione dei dati. In generale non è possibile effettuare delle modifiche contemporanee ai dati e quindi il DBMS implementa dei meccanismi di protezione dell’accesso in scrittura sui dati inserendo durante un operazione di scrittura il bit di locking un valore che indica ad altre applicazione che vogliono nello stesso momento accedere agli stessi dati che è possibile effettuare solo operazioni di lettura.
La gestione degli utenti attraverso un sistema di credenziali indica diversi livelli di accesso al database infatti si definiscono gli utenti amministratori che creano e modificano i permessi degli utenti del livello inferiore, modificano le strutture dei dati, dei database nello specifico, effettuano operazioni di backup (copia di sicurezza), ecc.

Fasi di progettazione di un Database (Modellazione dei Dati)

Per poter progettare e realizzare concretamente un Database si deve prima realizzare la modellazione dei dati che si vuole gestire e poi scegliere un software DBMS che consenta di tradurre in procedure e applicativi la gestione e la realizzazione del modello proposto. All’uopo si deve dire che la modellazione dei dati passa attraverso tre passi:

  • Progettazione concettuale o esterna in questa fase chi deve organizzare il database e realizzarlo esegue un’indagine sulle informazioni che dovrà contenere il database e quali saranno le funzioni richieste. In questa fase si deve avvalere della collaborazione dei lavoratori dell’azienda che decide di realizzare il database e di tutti i settori coinvolti. Il progettista organizza il modello secondo contenuti informativi e sceglie uno dei modelli concettuali, Di modelli concettuali ne esistono alcuni i più importanti sono il modello Entità-Relazioni,
  • Progettazione logica in questa fase si deve decidere la realizzazione logica del modello concettuale creato in precedenza e si realizzano e si costruiscono le tabelle che conterranno le informazioni secondo i contenuti informativi descritti nella fase concettuale; Inoltre qui si definiscono le relazioni logiche fra i dati e i vincoli di integrità a cui essi devono soddisfare.La progettazione logica può prevedere anche l’aoozione di altri modelli quali il modello gerarchico, il modello reticolare e il modello a grafi che a breve illustreremo.
  • Nella progettazione fisica si deicde la dislocazione della base di dati, la sua distribuibità e i supporti di memorizzazione richiesti.

Modelli Logici

In ogni modello è opportuno definire le grandezze informative in gioco e le relazione che intercorrono fra esse evidenziando anche il significato della relaziioe..

Modello Gerarchico

In questo modello si assegna una gerarchia alle grandezze informative in gioco organizzandola  in una struttura ad albero in cui ogni grandezza informativa che chiameremo d’ora in poi entità può accedere alle informazioni dei livelli sottostanti ma non di quelle sopra di esse. Un esempio è rappresentato dallo schema sottostante.

In questo esempio si vuole evidenziare l’organizzazione del patrimonio informativo di un’azienda. Ogni grandezza informativa ad esempio il Dipartimento Tecnico accede alle informazioni del settore Produzione e Progettazione ma non alle informazioni dell’Azienda (livello superiore potrebbero essere i dati sulle vendite, sul personale ecc.). In questo modo le applicazioni e il Database che ne scaturisce da questo modello sarà molto vincolato alla non comunicazione ( o non accesso) alle informazioni dei livelli superiori.

Modello a grafo

In questo modello logico  le informazioni sono collegate attraverso delle relazioni di dipendenza con percorso obbligato. In altri termini le informazioni sono organizzate in strutture a lista dove ciascun elemento può accedere all’elemento precedente e successivo della lista ma non a quello di altra lista. Tale modello si presta bene per la realizzazioni di viste specifiche sui dati. Un esempio è riportato nello schema sottostante.

In questo esempio si vogliono organizzare le informazioni relative ai cantanti e ai loro dischi con un criterio di raggruppamento dei cantanti per casa discografica. Ogni rettangolo rappresentano dati sulle case discografiche, i loro cantanti e i dischi che hanno inciso e quindi venduto. In questo modello a grafo la casa discografica consulta i dati dei cantanti e dei loro dischi secondo una struttura a lista ovvero una volta scelto un cantante potrò visualizzare solo i dati dei dischi che esso ha prodotto.

Modello Relazionale

Il modello logico più utilizzato è il modello relazionale implementato nella maggior parte dei DBMS moderni.
I concetti fondamentali sono legati ad un aspetto fondamentale che fu ideato e progettato da Edgar Cood nel 1970. Cood ha ideato un modello logico basato su tabelle. Le tabelle hanno delle caratteristiche precise derivate dal modello Entità Relazionale con delle regole rigorose.
Le regole di Cood consentono, la derivazione a partire da qualunque modello Entità Relazioni del modello logico.
Enunciamo le regole di Cood per derivare il modello relazionale:

  • Regola 0: un sistema è definito relazionale quando il
    RDBMS, la gestione delle basi di dati è relazionale.
  • Regola 1: Le informazioni sono rappresentate sotto forma di tabelle, costituite da righe e colonne che rappresentano le informazioni.
  • Regola 2:Tutti i dati devono essere accessibili senza ambiguità. Ogni riga della tabella deve essere individuata mediante un attributo o insieme di attributi che identificano univocamente un’istanza sulla tabella.
    E’ definito il concetto di chiave primaria
  • Regola 4 Trattamento dei valori NULL: il DBMS deve consentire la gestione in modo corretto dei valori NULL o vuoti nei campi. Inoltre deve gestire nello stesso modo sia le informazioni mancanti o isolate. Deve gestire i valori dei campi distinti da ogni valore consentito.
  • Regola 5 dizionario del modello relazionale: la descrizione degli oggetti che compongono il database deve avvenire a livello logico. Il linguaggio utilizzato per gestire il database deve utilizzare matadati identici per ogni utente. Il dizionario deve essere unico per tutti gli utenti, cambiano solo gli oggetti del database,
  • Regola 6 Tutti i dati devono essere accessibili mediante un linguaggio relazionale, come SQL.
    In particolare deve supportare: istruzioni di creazione e definizione delle strutture dati e della loro manipolazione. Deve essere possibile utilizzare tale linguaggio in modo interattivo che nelle applicaizoni
    Deve inoltre possedere una sintassi lineare, ovvero una semantica delle frasi che possa essere interpretata in modo unico dal DBMS.
  • Regola 7 aggiornamento delle viste di dati: le viste sono tabelle virtuali per dare ai dati una visualizzazione (vista) secondo dei criteri prefissati dal progettista. Ogni aggiornamento effettivo alle tabelle deve propagare il suo effetto anche alle viste.
  • Regola 8 manipolazione ad alto livello: Da un database deve essere possibile reperire i dati da più righe e tabelle, e le stesse informazioni devono essere poter essere inserite, modificate e cancellate.
  • Regola 9 indipendenza dalla struttura fisica: il Database a livello logico deve essere indipendente dalla struttura fisica e dall’organizzazione fisica dei dati stessi.
  • Regola 10 indipendenza della struttura logica: le componenti logiche del database dal punto di vista logico devono essere modificate in modo indipendente dalla struttura fisica dei dati e della relativa memorizzazione.
  • Regola 11 I vincoli logici dei dati devono essere memorizzati nel database: le relazioni, i vincoli di dominio, le regole di sicurezza di accesso alla base di dati devono essere definite all’interno del daatbase stesso.
  • Regola 12 indipendenza di localizzazione: il database o porzioni di essa possono essere memorizzati in locazioni diverse anche geograficamente distanti, ma devono essere invisibili all’utente.
  • Regola 13 regola di non sovversione: Gli strumenti di accesso ai dati non devono poter annullare le restrizione sul database. pur potendo aggiornare, modificare vincoli, relazioni.

    In sintesi alcune indicazioni sintetiche delle regole di Cood indicano la procedura operativa per creare a partire da un modello concettuale entità – relazioni, del modello relazionale.
    La procedura è così riassunta:
    ad ogni entità corrisponde una tabella denominata relazione che è composta da un numero di colonne pari al numerop delle colonne. Le associazioni fra le Entità sono definite mediante le chiavi esterne che devono essere inserite nella tabella che deriva da una entità che ha il ruolo N nell’associazione.
    Ogni tabella deve possedere una chiave primaria.
    Su queste semplici regole è possibile ottenere il modello relazionale.

Caso di studio

Definire un modello Entità Relazioni e in seguito un modello relazionale per il seguente problema: gestione dei versamenti eseguiti da un’azienda ai dipendenti, ai fornitori, ed altri enti (agenzia fiscale, previdenziale, ecc.).
Le entità che possono essere definite sono: Azienda, Dipendente, Versamento.
Le associazioni sono: Azienda lavora Dipendente (1,N), Azienda esegue Versamento (N,M).
Graficamente il Modello Entità Relazioni è:

Questo è un primo modello Entità Relazioni, che deve essere semplificato.
In questa prima versione del modello Entità – Relazioni è evidenziata l’associazione M a N, è opportuna una semplificazione di tale modello.
Il nuovo modello E-R prevede un’entità di passaggio che decompone l’associazione M a N in modo da semplificare la gestione. La nuova Entità la chiameremo “Tipologia Versamento” e conterrà l’attributo “tiplogia_versamento” chiave primaria, la partita iva (p_iva) dell’azienda che può effettuare più versamenti della stessa tipologia. Ad esempio per ogni dipendente ogni mese l’azienda deve versare i contributi, le tasse con dati diversi ma della stessa tipologia.
Il modello E-R è sotto riportato nello schema.

Lo schema E-R sopra è completo con gli attributi; la sigla (PK) indica che l’attributo è chiave primaria, la sigla (FK) indica che l’attributo è chiave esterna.
Adottiamo ora le regole di derivazione per arrivare al modello relazionale. Ad ogni entità corrisponde una relazione (Tabella) che contiene un numero di colonne pari al numero degli attributi dell’entità, più tante colonne quante sono le chiavi esterne previste nella progettazione.
In sintesi:
Relazione (Dipendente)

Nome CampoTipoDimensioneSpeciale
codice_fiscalechar16 byte a lunghezza fissachiave primaria
nomevarchar30 byte a lunghezza variabilenon nullo
cognomevarchar30 byte a lunghezza variabilenon nullo
data_nascitadataformato gg/mm/aaaa 10 bytenon nullo
luogo_nascitavarchar50 byte a lunghezza variabilenon nullo
data_assunzionedataformato gg/mm/aaaanon nullo
p_ivachar11 byte a lunghezza fissachiave esterna

La relazione (tabella) azienda

Nome CampoTipo CampoDimensioneSpeciale
partiva_ivachar11 byte a lunghezza fissachiave primaria
ragione_socialevarchar50 byte a lunghezza variabilenon nullo
indirizzovarchar50 byte a lunghezza variabilenon nullo
settorecvarchar50 byte a lunghezza variabilenon nullo

passiamo alla relazione (tabella) tipologia_versamento

Nome CampoTipo CampoDimensioneSpeciale
p_ivachar11 byte a lunghezza fissachiave esterna
tipologia_versamnetochar5 byte a lunghezza fissachiave primaria

L’ultima relazione è lasciata per esercizio.

Espressioni regolari in Mysql

Come scrivere un espressione regolari in Mysql

Quando in una tabella si devono cercate dei dati spesso si ricorre alle espressioni regolari che permettono di filtrare le informazioni secondo criteri sofisticati.

Le espressioni regolari possono essere codificate da SQL mediante la parola REGEXP che indica che l’espressione che segue dopo è un’espressione regolare.

Per comprendere il loro significato vengono riportati alcuni esempi

a. indica che deve essere trovato una qualunque stringa che abbia come lettera iniziale il carattere “a” come secondo e successivi qualunque carattere.

L’espressione regolare “.a” indica una qualunque stringa che termina con la “a”.

Il punto assume la funzione di carattere jolly all’interno dell’espressione regolare.

Se invece si inserisce la stringa [0-9]{lunghezza} viene cercata una qualunque sequenza di cifre da 0 a 9 di massimo tre caratteri minimo.

Quindi all’interno delle parentesi quadre si inseriscono l’intervallo di valori da cercare e nelle graffe la loro lunghezza minima.

Se si vuole cercare ad esempio la sequenza “.00” poiché come detto il punto è un carattere speciale delle espressioni regolari allora è opportuno utilizzare il meta carattere “\\” anteposto al punto per verificare che nella sequenza ci sia un punto. Quindi si ha che l’espressione regolare è “\\.00”.

Le espressioni regolari possono essere molto utili per controllare la correttezza di stringhe come da esempio un indirizzo email.

Una regola per controllare se un indirizzi email è corretto potrebbe essere:

[0-9]\\-\\_\\.[a-z]@[a-z][0-9]\\.[a-z][0-9]\\.[a-z][0-9]

Questa regola ci dice che nell’indirizzo email sono ammessi i caratteri alfabetici, i numeri, il punto e il trattino, il trattino basso e la chiocciola.

Tutte le regole viste sono applicate in modo indipendente a caratteri maiuscoli e minuscoli.