Realizzazione di una base di dati

Si vuole creare una base di dati che memorizzi una collezione di persone e le informazioni relative alle relazioni genitori-figli esistenti.

1. Creare e cancellare tabelle
Definire la tabella Persone, con i seguenti attributi

    * Id (stringa di 2 caratteri, chiave primaria);
    * Nome (stringa di 20 caratteri), non può essere null;
    * Reddito (intero), in migliaia di euro, 0 per default;
    * Eta (intero < 200);
    * Sesso ('M' oppure 'F');

e la tabella Genitori, con i seguenti attributi, ambedue parte della chiave primaria:

    * Figlio (stringa di 2 caratteri, chiave esterna su Persone);
    * Genitore (stringa di 2 caratteri, chiave esterna su Persone).

Nella definizione delle chiavi esterne si tenga conto dei fatti seguenti:

    * Quando si cancella una persona devono essere eliminati i corrispondenti rapporti di parentela nella tabella Genitori.
    * Non è previsto che l'identificatore di una persona cambi.

Le tabelle devono essere di tipo InnoDB (in modo da supportare la gestione delle chiavi esterne).


Visualizzazione di informazioni sulle tabelle

Per verificare di aver effettivamente creato le tabelle, eseguite l'interrogazione:

    show tables;

che mostra le tabelle esistenti nel database.

Mediante

    describe table_name;

potete vedere gli attributi della tabella e con

    show create table table name;

potete vedere informazioni più dettagliate.


Popolare la base di dati

Popolare la base di dati di modo che contenga le ennuple indicate in nei file Persone.txt e Genitori.txt (usare il cut-and-paste per costruire le query e tabelle usate per il popolamento).

Provare a caricare la tabella Persone con il comando

    INSERT INTO tabella VALUES ....

mentre per la tabella Genitori usare il comando

    LOAD DATA LOCAL INFILE 'GenTable.txt' INTO TABLE Genitori
    FIELDS TERMINATED BY ',';

dove GenTable.txt è un file preventivamente creato che contiene le ennuple da inserire in Genitori, campi separati da ','. (Nota: Per poter usare tale comando occorre aver avviato il client con il flag '--local-infile=1')

3. Effettuare interrogazioni
Fornire le interrogazioni SQL per rispondere alle seguenti domande. Ogni query è accompagnata dal risultato che si otterrebbe rispetto alla popolazione indicata sopra.

   1. Trovare id, nome e reddito delle donne con meno di 35 anni. [output]
   2. Trovare id, nome e reddito dei genitori di Antonino (Id='A3'). [output]
   3. Restituire per ogni persona, il nome della persona e quello dei nonni. [output]
   4. Trovare il reddito minimo, medio e massimo di donne e uomini [output]
   5. Trovare il reddito medio dei padri per età. [output]
   6. Fornire le coppie di fratelli (due persone sono fratelli se hanno un genitore in comune), indicandone i nomi. [output]

   7. Nomi di tutti i fratelli delle persone di nome Aldo [output] 
   8. Nomi delle persone che sono genitori di almeno 2 figli. [output] 
 9. Mostrare per ciascun figlio i rispettivi genitori (Madre, Padre, Figlio), indicando, per ciascuno, il nome. [output] 
  10. Modificare la query precedente in modo da riportare tutte le persone, con campo NULL per padre o madre quando questi siano assenti. [output] 
  11. Trovare l'elenco dei genitori i cui figli guadagnano tutti più di 20000 euro. Mostrare identificatore e nome, ordinando la lista per nome. [output] 
  12. Definire una vista EtaMediaFigli che restituisce per ogni persona (id, nome) l'età media dei suoi figli. Visualizzare il contenuto della vista.  [output] 
  13. Utilizzare la vista EtaMediaFigli per trovare tra tutti i genitori quelli i cui figli hanno la massima età media. [output] 
  14. Cancellare le Persone con reddito maggiore di 50000 euro. Mostrare le tabelle Persone e Genitori risultanti. [output] 
  15. Aumentare del 20% tutti i redditi inferiori a 30000 euro. Mostrare la tabella Persone risultante.  [output] 
  


Per scrivere ed eseguire le istruzioni SQL relative alle interrogazioni si può procedere come al punto precedente, creando un file query_n.sql per ciascuna query ed eseguendolo

    * con source query_n.sql, dal client MySQL
    * con mysql -t -h server -u login -ppwd db < query_n.sql, da shell

Dal client MySQL può essere anche utile utilizzare il comando

    \e

che permette di editare la query corrente (o quella appena eseguita) in un editor a vostra scelta, così come impostato nella variabile d'ambiente EDITOR. Qualora questa non fosse impostata, potete definirla inserendo nel file .bash_profile nella vostra home le righe

    EDITOR=emacs
    export EDITOR

Se il file non esistesse potete crearlo con il contenuto sopra indicato.