Esercitazione Autoguidata

OBIETTIVI DELL'ESERCITAZIONE:

Familiarizzare con il DBMS MySQL 5 (server) e con l'interprete di comandi mysql (client)
Utilizzare mysql per:

creare e cancellare tabelle;
effettuare interrogazioni.

1. CREAZIONE DI UNA BASE DI DATI E CONNESSIONE

Per attivare il server MySQL 5 e il client mysql come indicato nel tutorial su MySQL sul sito del corso.

Per creare una nuova base di dati, operate da mysql. Se, ad esempio, volete creare una base di dati chiamata persone digitate la stringa

mysql> create database persone;

In questo modo viene creato il "database" persone. A questo punto, è possibile "usare" al database persone attraverso il comando

mysql> use persone

2. CREARE E CANCELLARE TABELLE

Si consideri la tabella PERSONE, contenente i seguenti attributi

Nome (stringa di 20 caratteri, chiave primaria);
Reddito (intero di 10 caratteri);
Eta (intero di 3 cifre);
Sesso ('M' oppure 'F');

e la tabella GENITORI, contenente i seguenti attributi

Figlio (stringa di 20 caratteri, chiave esterna su PERSONE);
Genitore (stringa di 20 caratteri, chiave esterna su PERSONE).

Si forniscano le istruzioni SQL per creare le due tabelle.

Per scrivere le istruzioni SQL, potete procedere direttamente da riga di comando (digitando Invio per sottomettere comandi SQL al DBMS), oppure utilizzare un qualsiasi editor di testo (ad esempio Notepad). Se scegliete la seconda opzione, una volta scritto il codice salvatelo su un file, ad esempio persone.sql, e richiamate lo script così creato da MySQL tramite il comando

source persone.sql

Se il file non si trova direttamente nella posizione da cui avete lanciato mysql, sostituite il nome del file con il path relativo. Si noti che Ogni volta che viene usato il comando source, vengono eseguite tutte le istruzioni contenute nel file.

Per verificare di aver effettivamente creato le tabelle, eseguite la seguente interrogazione

select table_name, table_schema from information_schema.tables;

che interroga (sugli attributi table_name e table_schema) la tabella predefinita tables del catalogo (information_schema) che memorizza informazioni su tutte le tabelle della base di dati. Inoltre, dato il nome di una tabella è possibile ottenere i suoi attributi (nome e tipo) con il comando

describe <nome-tabella>;

Nota: Si ricorda che per default MySQL esegue il commit delle istruzioni SQL in modo automatico (autocommit=true).

3. EFFETTUARE INTERROGAZIONI

Fornite le interrogazioni SQL per rispondere alle seguenti domande.

Trovare nome e reddito dei maschi con meno di 30 anni;
Trovare nome e reddito dei genitori di 'Ezechiele';
Trovare la relazione che mostra per ogni persona i nonni;
Trovare il reddito medio dei padri per età;
Trovare la relazione che mostra le coppie di fratelli (due persone sono fratelli se hanno un genitore in comune)
Trovare le persone che sono genitori di almeno 2 figli;
Trovare la relazione che mostra per ciascun figlio i rispettivi genitori (Padre, Madre, Figlio);
Trovare l'elenco ordinato dei genitori i cui figli guadagnano tutti piu` di 20 milioni.
Definire una vista EtaMediaFigli che restituisce per ogni persona l'età media dei suoi figli.
Visualizzare il contenuto della vista;
Utilizzare la vista EtaMediaFigli per trovare tra tutti i genitori quello i cui figli hanno la massima età media.

Per scrivere ed eseguire le istruzioni SQL relative alle interrogazioni procedete come al punto precedente.

Per verificare la correttezza delle proprie soluzioni, popolate la base di dati con il file eserSQL-db.sql, e confrontate i risultati ottenuti con quelli contenuti nel file eserSQL-3-ris.txt. Per popolare la base di dati, copiate il file eserSQL-db.sql nell'unità C, ed eseguite il comando

source eserSQL-db.sql

4. ULTERIORI INTERROGAZIONI

Fornite le interrogazioni SQL per rispondere alle seguenti domande.

Trovare le madri di persone che guadagnano piu` di 20 milioni;
Trovare i Nonni di Aldo e di Andrea;
Trovare i figli (in comune) di Franco e Maria;
Trovare i fratelli di Maria;
Trovare il reddito complessivo dei figli di Anna e Luigi;
Trovare la relazione che mostra le coppie fratello-sorella.
Trovare l'età media dei geniori di Olga;
Trovare per ogni persona il numero dei suoi figli;
Trovare il reddito delle persone che sono genitori di almeno 2 figli.
Fornire una soluzione senza usare raggruppamento, ed una usando reggruppamento.

Confrontate i risultati ottenuti con quelli contenuti nel file eserSQL-4-ris.txt.

Le soluzioni degli esercizi 2, 3 e 4 saranno disponibili fra una settimana all'indirizzo www.dis.uniroma1.it/~degiacomo/didattica/basidati/EsercitazioneSQL-2/soluzioni.

Questo documento è basato su un documento analogo redatto da Domenico Lembo per una esercitazione in laboratorio nell'ambito del Corso di Basi di Dati, Corso di Laurea in Ingegneria Informatica A.A. 2004/05.