Progettiamo un Database con MySQL Workbench

Nel tutorial di oggi, imparerete come utilizzare l'utility per la progettazione di un databese utilizzata per disegnare un diagramma di database e generare automaticamente il codice SQL. In particolare, esamineremo come utilizzare MySQL Workbench, uno strumento visivo di progettazione di database multipiattaforma.

Che cos'è MySQL Workbench?

MySQL Workbench è un potete strumento sviluppato da MySQL con tre aree principali di funzionalità:

• Sviluppo di SQL: Sostituisce il MySQL query browser. Consente agli utenti di connettersi ad un database esistente per scrivere ed eseguire query SQL.
• Modellazione dei dati: Esegue il Disegno e la Modellazione visiva del database: 
• Amministrazione del Database: Sostiuisce l'amministratore MySQL. Interfaccia grafica per avviare/fermare i server, creare utenti e profili, e modificare i file di configurazione.

In questo tutorial, focalizzeremo l'attenzione sulla Modellazione dei dati e la creazione di un database da zero, e poi daremo una rapida occhiata all'editor SQL per eseguire gli script SQL genarati da noi e creare i Database con MySQL.

MySQL Workbench è disponibile per Windows, Linux e Mac OSX. Esistono due versioni differenti: La Community OSS Edition  e la commercial Standard Edition. La community edition è Open Source con licenza GPL, come ci si aspetterebbe. E' completamente funzionante ed è quella che useremo in questo articolo. La Commercial edition aggiunge molte altre funzionalità, come la validazione dello schema e del modello o il generatore per la documentazione.

Nota: questo tutorial si basa sulla versione 5.2 (5.2.16) della Community OSS Edition, attualmente in beta release al momento della scrittura di questo articolo (Aprile 2010).

Pianificazione del nostro Database

Per imparare come utilizzare MySQL Workbench, utilizzeremo come esempio un database molto semplice per un corso online. Supponiamo che un gruppo d'insegnanti, vogliano offrire dei corsi online per diverse materie, utilizzando Skype o altri software di videoconferenza. Per il nostro piccolo progetto, abbiamo deciso che sarà necessario archiviale le seguenti informazioni:

Quando disegneremo il nostro diagramma, dovemmo conoscere anche le relazioni tra questi gruppi di dati  ; Per cui dovemmo pensarci già da adesso!

• Un insegnante può insegnare diverse materie
• Una materia potrebbe essere insegnata da divesi insegnanti
• Ogni classe ha solo un insegnante
• Un insegnante può insegnare molte classi
• Uno studente può frequentare molte classi
• Una classe ha molti studenti
• Una classe può avere diverse ore (in una settimana)
• A un determinato giorno e ora, ci possono essere diverse classi
• Una classe è su una materia
• Una materia può essere insegnata in molte classi

A questo punto abbiamo tutte le informazioni che ci servono per incontrare la star dello show ....

Invia a MySQL Workbench

E' giunto il momento di lanciare Workbench. In parte della schermata iniziale di modellazione dati, clicchiamo 'Crea nuovo modello di EER' e apparirà la seguente schermata:

Quando creiamo un nuovo modello di database, questo contiene lo schema del mydb predefinito. Possiamo rinominarlo e usarlo come nostro schema del DB. Un modello di database può avere diversi schemi.

Il catalogo sulla destra mostrerà ogni elemento nel nostro schema e ci permette o di trascinare e rilasciare gli elementi nei diagrammi se necessario.

Avere le sezioni separate di Schermata Fisica e Diagrammi EER e la possibilità di includere diversi schemi in un modello di database può essere fonte di confusione. Nella sezione successiva descriveremo questi concetti e come sono correlati.

Chiarire i concetti

Lo schema fisico contiene tutti i pezzi necessari per definire il database: tabelle, colonne, tipi, indici, vincoli, ecc. Questo è ciò che realmente stiamo definendo. Ogni oggetto aggiunto nel modello grafico si rivela anche nello schema fisico. È, infatti, un modo visivo per definire il nostro schema.

Possiamo avere diversi schemi per lo stesso modello di database nello stesso modo che possiamo avere più database in un server MySQL. Ogni schema sarà un database MySQL. Ad esempio, nella schermata successiva, abbiamo due schede dello schema:

Se generiamo lo script SQL, avremo due istruzioni separate di CREATE DATABASE - in realtà abbiamo CREATE SCHEMA, che è solo un sinonimo.

1

CREATE SCHEMA IF NOT EXISTS `schema1`;

2

CREATE SCHEMA IF NOT EXISTS `schema2`;

"EER sta per  (Extended (or Enhanced) Entity-Relationship) Entità-Relazione Estesa (o Avanzata). Diagrammi di EER sono solo un modo per modellare i dati e le relazioni tra i dati utilizzando simboli standard"

Verranno elencati come database all'interno del Server Host MySQL quando si utilizzerà il comando SHOW DATABASE.

Ora, cos'è un diagramma EER? EER sta per (Extended (or Enhanced) Entity-Relationship) Entità-Relazione Estesa (o Avanzata). Diagrammi di EER sono solo un modo per modellare i dati e le relazioni tra i dati utilizzando simboli standard. Il modello EER può essere complesso, ma MySQL Workbench utilizza solo un sottoinsieme di tutti i possibili elementi grafici, perchè lo scopo di questo diagramma (in questo tool) è di ottenere la mappa di tutti gli elementi dello schema fisico.

Possiamo utilizzare un diagramma EER per definire l'intero database , o solo una piccola parte. Ad esempio, possiamo avere uno schema definito con cinque tabelle e poi creaiamo un nuovo diagramma per definire due nuove tabelle utilizzando l'editor visuale. Il diagramma conterrà solo due tabelle, ma queste due tabelle saranno anche incluse nello schema, insieme alle precedenti cinque.

Creaiamo le nostre Tabelle

Torniamo al nostro esempio iniziale.; dobbiamo rinominare lo schema predefinito con un doppio click sul nome. A questo punto, abbiamo due possibilità: possiamo iniziare aggiungendo le tabelle al nostro schema fisico utilizzando l'icona aggiungi tabella, oppure, possiamo avviare un Diagramma EER ed aggiungerci tutte le tabelle.

Personalmente, preferisco aggiungere un nuovo diagramma dall'inizio e creare il mio schema visivamente; tuttavia, al fine di mostrare come fare in entrambi i metodi, creeremo le prime due tabelle nel tab schema, e poi continueremo con il Diagramma EER.

Quando si fa clic sull'icona Aggiungi tabella, verrà aperto l'editor di tabella come una scheda qui sotto:

Utilizzando l'editor di tabella, cambiamo il nome della tabella e passare alla scheda colonne (nelle schede sotto l'editor) per immettere le nostre colonne. Possiamo scegliere il tipo di dati (c'è un elenco a discesa con tutti i tipi di dati di MySQL), assegnare il valore predefinito, se necessario, ed abbiamo sette caselle di controllo per contrassegnare le proprietà seguenti:

• PK - Primary key
• NN - Not null
• UQ - Unique
• BIN - Binary 
• UN - Unsigned
• ZF - Zero fill
• AI - Autoincrement

Vai su Visual

Questo è un modo per aggiungere le nostre tavole, anche se possiamo crearle anche utilizzando i diagrammi. Se clicchiamo sull'icona Aggiungi diagramma ora, iniziamo un nuovo diagramma vuoto e che non è quello che vogliamo. Vogliamo le due tabelle che abbiamo appena creato per essere nel diagramma.

Se andiamo dal menu, selezionate Modello/Creazione diagramma da catalogo oggetti, ora abbiamo il nostro diagramma e siamo pronti per continuare.

Selezionate l'icona della tabella a sinistra; il puntatore diventa una mano con un piccola tavola. Fare clic in un punto qualsiasi sulla tela per creare una nuova tabella.

Adesso dovete solo cliccare due volte sulla tabella, e apparirà l'editor dove potrete scrivere il nome della tabella, le colonne e definire i tipi di campi etc - nello stesso modo in cui abbiamo fatto prima.

Dopo aver inserito i dettagli delle colonne per le nuove tabelle, potrete inizare a disegnare le relazioni.

Disegno delle Relazioni

Nella barra degli strumenti verticale sulla sinistra, abbiamo sei strumenti disponibili per creare relazioni.

Non preoccuparti dell' ultimo, lo spiegheremo più tardi. Per i rapporti 1:1 e 1: n, abbiamo due diversi tipi di simboli: identificabile e non identificabile. Cosa significa?

Una relazione è considerata identificabile quando l'esistenza di una tabella è interamente dipendente da un'altra.

Un rapporto è considerato identificare quando una tabella è interamente dipendente da altra di esistere. Una riga della tabella è dipende da una riga nell'altra tabella. Un esempio comune è di avere una tabella separata per memorizzare telefoni per gli utenti. Potrebbe essere necessario disporre di esso in un'altra tabella, poiché possono esistere diversi telefoni per un utente, ma ogni riga della tabella è interamente dipendente dall'utente - appartiene all'utente.

Si dovrebbe essere consapevoli del fatto che le relazioni hanno alcune implicazioni. Se vogliamo creare le tabelle fisiche in MySQL, relazioni devono essere mappate in qualche modo. Ci sono alcune regole per mappare le relazioni nelle tabelle:

• relazioni 1:1. Chiave primaria per una delle tabelle è incluso come chiave esterna di altra tabella.
• relazioni 1: n. Chiave primaria della tabella sul lato '1' viene aggiunta come chiave esterna nella tabella la ' n' lato.
• relazioni n:m. Viene creata una nuova tabella (tabella di join). La chiave primaria è composta dalle chiavi primarie delle due tabelle originali.

Identificazione relazioni vengono in genere utilizzate per le tabelle di join create da una relazione molti-a-molti. Queste nuove tabelle dipendono totalmente dalle due tabelle originali.

Inoltre, nel caso di 1:1 e identificare relazioni 1: n, la chiave esterna introdotta sarà parte della chiave primaria per la tabella, formando una chiave primaria composta.

La buona notizia è che MySQL Workbench conosce queste regole meglio della maggior parte di noi. Dobbiamo solo disegnare le nostre linee, e le chiavi esterne o join tra le tabelle verranno create automaticamente. Possiamo anche scegliere di farlo manualmente, come vedremo tra breve.

Per disegnare una relazione, fate clic sull'icona e quindi scegliere le due tabelle che devono relazionarsi. Per le relazioni uno-a-molti, fare clic su tabella sul lato "molti" prima e poi sulla tabella lato "uno". Vediamo come farlo per il rapporto insegnanti-materie n:m e per insegnanti-classi 1: n.

Il nome predefinito assegnato per le chiavi esterne e per le tabelle di join può essere modificato a livello globale nella scheda Modifica/Preferenze/modello, o solo per il presente progetto in opzioni di modello/modello.

Se non vogliamo tabelle e chiavi esterne deve essere generato in questo modo, possiamo usare il misterioso "sesto simbolo."

Il "sesto simbolo" crea una relazione utilizzando colonne esistenti, significa che si hanno già incluso le necessarie chiavi esterne nelle tabelle e creato le tabelle di join necessari (tabelle di mappatura n:m). Dal momento che abbiamo già creato queste tabelle di Join, non abbiamo bisogno di relazioni n:m; sarà disponibile solo la relazione 1: n .

Quando avremo definito tutte le nostre relazioni, il nostro diagramma dovrebbe somigliare a questo:

Dobbiamo essere consapevoli del fatto che usiamo la notazione di MySQL Workbench predefinita per i diagrammi, ma si può cambiare in notazione oggetto/modello e modello/rapporto notazione. Questo è un esempio del nostro modello in notazione Classica:

A questo punto, il nostro modello è pronto e possiamo generare il codice SQL per creare il database MySQL.

Generazione dell' SQL

Selezionare File/Export/Forward Engineer SQL creare Script. Siamo solo a tre schermate della procedura guidata dalla generazione i nostri file!

Abbiamo anche la possibilità di rivedere e modificare il codice SQL generato prima di salvarlo:

E questo è quanto. Facendo clic su fine, gli script  in codice SQL verranno generati e salvati. Ora, possiamo usarlo in qualsiasi modo si volgia. Possiamo caricare utilizzando il client di mysql da riga comando:

MySQL > fonte scriptName.sql

In alternativa, possiamo usare MySQL Workbench per finire il lavoro, in connessione al nostro server MySQL e l'esecuzione dello script.

Connessione a un Server MySQL

Selezionare Gestisci/Database connessioni dal menu e fare clic su NEW.

Se non si desidera impostare la password qui, viene richiesto per quando necessario. Fare clic su "Test connessione" per verificare se i parametri sono corretti e quindi fare clic su Chiudi.

Ora, per caricare lo script, useremo l'editor SQL. Nel menu principale selezionare Database/Query Database; una finestra vi chiederà di selezionare una connessione e quindi si apre la scheda editor SQL.

Ora cliccate sull'icona del fulmine per eseguire lo script SQL, e il database verrà generato!

Noi potremmo anche aver generato il database MySQL direttamente dal modello, senza fare riferimento al file effettivo, utilizzando Database/Forward Engineer dal menu; Tuttavia, lo trovo utile per generare lo script e quindi utilizzarlo come vorrei.

Conclusione

MySQL Workbench è uno strumento impressionante. Abbiamo visto solo poche possibilità in base alla parte di modellazione dei dati e solo dato un'occhiata all'editor SQL nella seconda metà di questo tutorial. Abbiamo imparato a creare un database visivamente e disegnare diagrammi che possono essere tenuti come documentazione. È possibile esportare i diagrammi come file PNG, SVg, PDF o PostScript. Grazie per la lettura e fatemi sapere cosa ne pensate!