MYSQL - Scriviamo una base dati

Il database di esempio

Il database di esempio, beer_store, verrà creato utilizzando normali istruzioni SQL, è comunque possibile utilizzare dei client grafici, come quelli indicati precedentemente. Per creare il db, scaricare beer_store.sql copiare il contenuto del file e incollarlo nel prompt dei comandi del client mysql.

X:\mysql\bin>mysql

mysql> + incolla script

Commento

la prima istruzione dello script entra nel db mysql, poichè come abbiamo già visto non è possibile eseguire comandi se non siamo all'interno di un db già esistente:
USE MYSQL;

Lo script inizia con l'inserimento di un nuovo utente nella tabella user del database mysql:

INSERT into user
(
    Host,
    User,
    Password,
    Select_priv,
    Insert_priv,
    Update_priv,
    Delete_priv,
    Alter_priv
)
Values
(
   'localhost',
   'hey_bulldog',
    password('get_back'),
    'Y',
    'Y',
    'Y',
    'Y',
    'Y'
);

FLUSH PRIVILEGES;
Il flush dei privilegi, evita di dover rientrare e uscire da mysql

GRANT SELECT,INSERT,UPDATE,DELETE,ALTER ON beer_store.* TO hey_bulldog@localhost
IDENTIFIEDBY 'let_it_be';
autorizzazione ai comandi SQL, SELECT,INSERT,UPDATE,DELETE e ALTER per tutte le tabelle del database beer_store per l'utente.

nell' ipotesi che esista già un database beer_store, dobbiamo cancellarlo con l'istruzione,
DROP DATABASE if exist beer_store;

Creazione del Database
CREATE DATABASE beer_store;

Collegamento con il nuovo DB creato
USE beer_store;

Creazione delle tabelle

Per la progettazione di un database il modello più diffuso è senz'altro E-R (Entity-Relationship Model) proposto da Chen nel 1976. Secondo questo modello la creazione del database avviene secondo tre fasi fondamentali:

Analisi delle entità

Analisi delle correlazioni

Analisi degli attributi

Il primo passo dell'analisi E-R consiste nell'individuazione delle entità. Ad esempio, in un database rivolto alla gestione delle vendite potranno considerarsi entità i Clienti, gli Ordini e i Prodotti. Il nostro database quindi avrà almeno tre tabelle, cliente, ordini e birra quest'ultima corrispondente all'unico prodotto venduto in un negozio di birra. Non sempre è facile individuare subito tutte le entità , spesso l'analisi si svolge per approssimazioni successive. Saranno l'analisi delle correlazioni e degli attributi a chiarire i dubbi iniziali.

Il secondo passo consiste nell'individuare le correlazioni che sussistono tra le entità. Le relazioni si dividono in:

uno a uno: date due entità A e B, esiste una cardinalità di tipo "1 a 1" se ad una occorrenza (riga o record) di A corrisponde una sola occorrenza di B e viceversa. Ad esempio, un marito ha solo una moglie e una moglie ha solo un marito. Questa relazione viene considerata più teorica che pratica

uno a molti, una cardinalità "1 a N" esiste se ad una occorrenza di A possono corrispondere più occorrenze di B, mentre ad una occorrenza di B corrisponde al massimo un'unica occorrenza di A. Ad esempio, un padre ha più figli, mentre ogni figlio ha un solo padre.

molti a molti, la cardinalità risulta "M a N" se ad una occorrenza di A possono corrispondere più occorrenze di B, e se per una occorrenza di B corrispondono più occorrenze di A. Ad esempio, un ordine di un cliente può richiedere vari prodotti, e ogni prodotto può essere richiesto in più ordini.

Le correlazioni "M a N" non sono direttamente rappresentabili. È quindi necessario utilizzare entità che fungono da intermediarie (tabelle di collegamento), che permettono di trasformare una correlazione "M-N" in due correlazioni "1-N".

La terza fase è l'individuazione degli attributi (colonne o campi) che qualificano le entità e i relativi tipi di dati

Esempio Tabella cliente:

CREATE TABLE cliente (
cliente_id int(5) NOT NULL auto_increment,
cognome varchar(50) DEFAULT '' NOT NULL,
nome varchar(50) DEFAULT '' NOT NULL,
titolo varchar(10),
indirizzo varchar(50) DEFAULT '' NOT NULL,
citta varchar(20) DEFAULT '' NOT NULL,
cap varchar(5),
paese varchar(20),
telefono varchar(15),
email varchar(30) DEFAULT '' NOT NULL,
PRIMARY KEY (cliente_id),
KEY nomi (cognome,nome) );

Ad esclusione dei campi cliente_id, PRIMARY KEY e KEY, tutti gli altri descrivono le informazioni che definiscono un cliente e poichè sono campi descrittivi il tipo di dati scelto è VARCHAR ,una stringa a lunghezza fissa contenente da uno a 255 caratteri.

Indici,Chiavi,Auto_Increment

L'aspetto fisico di un database riguarda il modo in cui i dati vengono memorizzati sui dischi. E' possibile migliorare le prestazioni tramite gli indici, che permettono un accesso più veloce ai record. Gli indici a differenza delle chiavi non sono un costrutto logico ma operano una associazione tra la posizione fisica del record sul disco e la chiave che li identifica. In mysql la creazione degli indici può essere fatta con due istruzioni: INDEX e KEY. Basta specificare il nome dell'indice con il nome dei campi da indicizzare.
Es:KEY nomi (cognome,nome)

I database relazionali prevedono che in ogni tabella esista una colonna o combinazione di colonne, di valore non nullo, che identifichi in modo univoco ogni riga di una tabella. Questa colonna è chiamata "Chiave Primaria" o "Primary Key". Nella tabella cliente la PRIMARY KEY è (cliente_id)

E' possibile utilizzare le Primary Key con i campi ad incremento automatico. In questo modo ad ogni record creato viene assegnato un ID predefinito:
cliente_id int(5) NOT NULL auto_increment ogni volta che si aggiunge un record il server assegna all' id un valore uguale al valore precedente +1.  

NULL

Tra i dati definiti in una tabella, esiste la possibilità che alcuni valori possano assumere un valore particolare detto "null". Questo valore non va confuso con lo zero ne con qualsiasi altro valore ma rappresenta la cosiddetta "inapplicabilità di una situazione". Facciamo un esempio:
tra i dipendenti di una data azienda possono esserci dei dipendenti a stipendio fisso e altri, come i venditori, la cui paga può variare in base alle provvigioni sulle vendite. E' possibile utilizzare una stessa colonna per tutti, impostando a "null" i valori dei dipendenti a paga fissa. Infatti un dipendente non ha zero provviggioni, semplicemente non le ha (null). In questo modo è possibile utilizzare le stesse tabelle anche quando esistono diversificazioni nella stessa entità.

Risorse per SQL: http://www.oracleportal.it