/* Script sql che crea lo schema Universita`, con le relazioni Studenti, 
   Docenti, Esami visto a lezione 

   Puo` essere utilizzato nel modo seguente:
   - connettersi al server MySQL con il comando
     mysql -h basidati.studenti.math.unipd.it -u [login] -p
   
   - quindi eseguire dal prompt di mysql

     mysql> use [login]-ES
     mysql> source creaUniv.sql

     dove [login] va sostituita dalla vostra login e se 'creaUniv.sql'
     non e` nella directory corrente, dovente indicare il path per
     raggiungerlo.

     A questo punto lo schema e` stato creato e popolato. Potete
     quindi provare le query che desiderate.
*/

/* Ripulisce, eliminando le tabelle qualora esistessero gia` */


DROP TABLE IF EXISTS Esami;

DROP TABLE IF EXISTS Studenti;

DROP TABLE IF EXISTS Docenti;


/* Crea la tabella degli studenti */

CREATE TABLE Studenti (
       Nome       VARCHAR(10) NOT NULL,
       Cognome 	  VARCHAR(10) NOT NULL,
       Matricola  CHAR(5) PRIMARY KEY,
       Nascita	  YEAR,
       Provincia  CHAR(2) DEFAULT 'PD',
       Tutor 	  CHAR(5),
       FOREIGN KEY (Tutor) REFERENCES Studenti(Matricola)
       	                   ON UPDATE CASCADE
                           ON DELETE SET NULL
) ENGINE=InnoDB;


/* Crea la tabella dei Docenti */

CREATE TABLE Docenti (
       CodDoc	  CHAR(3) PRIMARY KEY,
       Nome    	  VARCHAR(8),
       Cognome	  VARCHAR(8)
) ENGINE=InnoDB;

/* Crea la tabella degli Esami */

CREATE TABLE Esami (
       Codice	  CHAR(4) PRIMARY KEY,
       Materia 	  CHAR(3),
       Candidato  CHAR(5) NOT NULL,
       Data 	  DATE,
       Voto 	  INT,
       Lode 	  CHAR(1),
       CodDoc 	  CHAR(3) NOT NULL,
       /* chiavi esterne verso Studenti e Docenti. Se aggiornate le 
          tabelle riferite, verra` aggiornata anche la chiave esterna */
       FOREIGN KEY (Candidato) REFERENCES Studenti(Matricola) ON UPDATE CASCADE,
       FOREIGN KEY (CodDoc) REFERENCES Docenti(CodDoc) ON UPDATE CASCADE,
       /* si assume che non ci possano essere due esami nella stessa
       /* materia per lo stesso candidato */
       UNIQUE KEY (Materia,Candidato)
) ENGINE=InnoDB;



/* Popola le tabelle */

INSERT INTO Docenti 
       VALUES ('AM1','Aleandro','Minti'),
              ('GL1','Ginevra','Lani'),
              ('NG2','Nuto','Gini');

INSERT INTO Studenti 
       VALUES ('Giorgio','Zeri','71347',1987,'PD',NULL),
	      ('Paolo','Verdi','71523',1986,'PD',NULL),
	      ('Paolo','Poli','71576',1988,'VE','71523'),
	      ('Anna','Rossi','76366',1987,'VE','71347'),
	      ('Chiara','Scuri','71346',1985,'PD','71347');


INSERT INTO Esami 
       VALUES ('B112','BD1','71523','2006-07-08',27,'N','AM1'),
              ('B247','BD2','71523','2006-12-28',30,'S','NG2'),
	      ('B248','BD1','76366','2007-07-18',30,'N','AM1'),
	      ('B249','BD2','71576','2007-07-19',22,'N','NG2'),
	      ('F313','FIS','76366','2007-07-08',30,'N','GL1'),
	      ('F314','FIS','71576','2007-07-29',22,'N','GL1');