Demo con MySQL
In questa demo vediamo come creare mediante l'interfaccia web al DBMS MySQL una tabella persona
con 4 colonne:
- id che funge da chiave primaria
- nome ovvero il nome della persona
- padre e madre che sono chiavi esterne per id, ed identificano il padre e la madre della persona in questione
Tale tabella corrisponde al seguente schema concettuale
- una entita' persona con due attributi (id e nome), dove id e' una chiave
- due relazioni binarie padre e madre con cardinalita' uno-molti: una persona puo' avere al piu' un padre o una madre noti, ma puo' avere piu' figli
Uno schema concettuale piu' corrispondente alla realta' dovrebbe prevedere due specializzazioni (uomo e donna) della entita' persona, in modo che la relazione padre e' tra le entita' persona e uomo e quella madre tra le intita' persona e donna.
Inoltre vediamo come esportare un database (in formato SQL), e
esempi di interrogazioni SQL.
La connessione al server
Dovete connettervi con un browser (p.e. Internet Explorer) alla URL http://speedy.educ.disi.unige.it/phpmyadmin/
dell' interfaccia web phpMyAdmin per MySQL. Dopo essersi autenticati come utente stan (password stan), comparira' la seguente schermata. Le frecce verdi indicano
- il menu language dove potete selezionare il linguaggio
- ed il menu database, potete modificare solo il database labostan, il database test potete solo leggerlo
Creazione di una tabella
All'inizio di questa demo Il database labostan e' vuoto, ma potete creare una nuova tabella specificando il nome (persona) ed il numero dei campi/attributi (4) e facendo click su esegui
Apparira' una maschera con 4 righe (una per campo), dove potete specificare le caratteristiche di ogni campo,
selezionando dai menu' a tendina o inserendo dei valori.
Tali caratteristiche possono essere modificate successivamente. Mentre invece e' importante fissare da subito il tipo della tabella. Consigliamo di usare Inno DB, perche' permette di specifica (con qualche limitazione) le chiavi esterne
Con le specifiche riportate di seguito abbiamo deciso
- il nome dei campi (id, nome, padre, madre)
- il loro tipo: nome deve essere una sequenza di al piu' 10 caratteri, gli altri sono interi
- che i valori di padre e madre possono essere indefiniti (null)
- che id e' la chiave primaria (bottono evidenziato in verde)
Dopo aver premuto Salva comparira' la seguente schermata
- la prima riga (Server:...) dice che stiamo visualizzando la tabella persona del database labostan
- sotto e' riportato l'istruzione SQL appena eseguita (per creare la tabella persona)
- la seconda riga contiene una serie di bottoni (Struttura, Mostra, ...)
- Struttura permette di vedere la dichiarazione della tabella (ovvero quello che e' riportato sotto)
- Mostra permette di vedere/modificare/cancellare i record contenuti nella tabella (se la tabella e' vuota il bottone non e' attivo)
- SQL permette di scrivere comandi SQL (tipicamente una interrogazione relativa alla tabella)
- Inserisci permette di inserire nuovi record
- Esporta permette di generare un file (tipicamente in SQL) con informazioni estratte dallo schema logico o dal contenuto del database
- Svuota rende la tabella vuota
- Elimina oltre a svuotare la tabella la elimina anche dallo schema logico.
- infine viene viene visualizzata l'informazione sulla tabella persona e data la possibilita' di modificare la definizione della tabella
Chiavi esterne
Adesso vogliamo aggiungere allo schema logico il fatto che gli attributi padre e madre sono chiavi esterne per
la chiave primaria id (della tabella persona). A causa di peculiarita' implementative (nel caso di Access non ci sono queste complicazioni), prima occorre dichiarare gli attributi padre e madre di tipo indice (questo lo si fa' cliccando sulle icone
evidenziate in verde nella precedente schermata).
Dopo aver fatto cio' si avra' la seguente schermata, che si differenzia dalla precedente perche' abbiamo 3 indici per la tabella
persona (invece del solo indice PRIMARY presente nella schermata precedente).
Adesso possiamo dichiarare padre e madre come chiavi esterne, si deve cliccare sulla icona
. Quindi (vedi sotto) scegliere dal menu associato a padre e madre persona->id, e premere Esegui
Esportazione di codice SQL
Poiche' il database e' accessibile a tutti gli studenti, se volete salvare lo stato del database (sia schema logico che dati) sul vostro computer dovete usare la operazione Esporta. Prima dovete selezionare dal menu' a tendina il database da esportare,
quindi fate clic sul bottone Esporta.
Comparira' la seguente schermata.
A questo punto basta seleziona Salva con nome e premere il tasto Esegui, e potrete salvare sul computer un file con estensione sql (si tratta di un semplice file testuale editabile con notepad). Per esempio questo e' quello che contiene
(si noti che viene salvato anche il contenuto della tabella, usando i comandi INSERT).
-- phpMyAdmin SQL Dump
-- version 2.6.0-pl3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generato il: 14 Mag, 2005 at 05:14 PM
-- Versione MySQL: 4.0.24
-- Versione PHP: 4.3.10-10
--
-- Database: `labostan`
--
-- --------------------------------------------------------
--
-- Struttura della tabella `persona`
--
CREATE TABLE `persona` (
`id` int(11) NOT NULL auto_increment,
`nome` varchar(10) NOT NULL default '',
`padre` int(11) default NULL,
`madre` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `padre` (`padre`),
KEY `madre` (`madre`)
) TYPE=InnoDB AUTO_INCREMENT=7 ;
--
-- Dump dei dati per la tabella `persona`
--
INSERT INTO `persona` VALUES (1, 'adamo', NULL, NULL);
INSERT INTO `persona` VALUES (2, 'eva', NULL, NULL);
INSERT INTO `persona` VALUES (3, 'caino', 1, 2);
INSERT INTO `persona` VALUES (4, 'abele', 1, 2);
INSERT INTO `persona` VALUES (5, 'clonecaino', 3, 3);
INSERT INTO `persona` VALUES (6, 'autocreato', 6, 6);
--
-- Limiti per le tabelle scaricate
--
--
-- Limiti per la tabella `persona`
--
ALTER TABLE `persona`
ADD CONSTRAINT `persona_ibfk_3` FOREIGN KEY (`padre`) REFERENCES `persona` (`id`),
ADD CONSTRAINT `persona_ibfk_4` FOREIGN KEY (`madre`) REFERENCES `persona` (`id`);
Esempi di query
La seguente interrogazione puo' essere effettuata sul database contente una tabella persona (come quella creata precedentemente)
SELECT persona.nome AS persona, padre.nome AS padre
FROM persona, persona AS padre
WHERE persona.padre = padre.id;
Descriviamo brevemente il significa della interrogazione (per maggiori dettagli si rimanda al capitolo 4 del libro "Basi di Dati")
- la clausola FROM introduce 2 copie della tabella persona, la seconda e' ridenominata padre
- la clausola SELECT dice che l'interrogazione produrra' una tabella con due colonne chiamate persona e padre:
persona corrisponde all'attributo nome della tabella persona, padre all'attributo nome della tabella padre
- la clausola WHERE dice che consideriamo solo le coppie di record (r,rp) con r in tabella persona e rp in tabella padre tali che r.padre = rp.id
l'uso di AS nella clausola FROM e' essenziale per poter fare il prodotto cartesiano di una tabella con se' stessa,
mentre l'uso di AS nella clausola SELECT permette di dare nomi piu' significativi alle colonne della tabella prodotta dalla interrogazione.
Consideriamo alcune varianti della precedente interrogazione.
SELECT padre.nome AS padre, COUNT(*) AS numero_figli
FROM persona, persona AS padre
WHERE persona.padre = padre.id
GROUP BY padre.id, padre.nome
in questo caso facciamo uso di una funzione aggregante COUNT e della clausola GROUP BY
- la clausola SELECT dice che l'interrogazione produrra' una tabella con due colonne chiamate padre e numero_figli.
- la clausola GROUP BY ci permette di partizionare la tabella precedente in sottotabelle con lo stesso valore di padre (che quindi possiamo usare nella SELECT)
- la funzione aggregante COUNT restituisce il numero di righe nella sottotabella (non vuota) corrispondente ad un certe padre, quindi una persona senza figli non comparira' nella tabella.
SELECT persona.nome AS persona, nonno.nome AS nonno
FROM persona, persona AS padre, persona AS nonno
WHERE persona.padre = padre.id AND padre.padre = nonno.id
In questo caso la tabella associa ad ogni persona il (nome del) nonno
paterno. In questo caso la tabella padre e' usata solo nella clausola WHERE.