--
--	Database Studenti
--

--
--	Eliminazione delle tabelle, se esistono
--
drop table if exists esame;
drop table if exists corso;
drop table if exists docente;
drop table if exists titolo_studio;
drop table if exists scuola;
drop table if exists conoscenza_lingua;
drop table if exists linguastraniera;
drop table if exists studente;
drop table if exists corso_laurea;
drop table if exists dipartimento;

--
--	Tabella: dipartimento
--
create table dipartimento (
	id int not null auto_increment,
	nome varchar(50),
	primary key (id)
) engine=innodb;

--
--	Tabella: docente
--
create table docente (
	id int not null auto_increment,
	nome varchar(20),
	cognome varchar(30) not null,
	email varchar(50),
	dipartimento int not null,
	primary key (id),
	foreign key (dipartimento) references dipartimento(id)
) engine=innodb;

--
--	Tabella: scuola
--
create table scuola (
	id int not null auto_increment,
	nome varchar(50),
	citta varchar(50),
	titolo varchar(50),
	primary key (id)
) engine=innodb;

--
--	Tabella: lingua
--
create table linguastraniera (
	id int not null auto_increment,
	nome varchar(50),
	primary key (id)
) engine=innodb;

--
--	Tabella: corso
--
create table corso (
	sigla varchar(10) not null,
	aa varchar(10) not null,
	titolo varchar(50) not null,
	id_docente int not null,
	ssd varchar(10) not null,
	cfu int not null,
	primary key (sigla, aa),
	foreign key (id_docente) references docente(id)
) engine=innodb;

--
--	Tabella: corso_laurea
--
create table corso_laurea (
	id int not null auto_increment,
	nome varchar(50) not null,
	id_dipartimento int not null,
	primary key (id),
	foreign key (id_dipartimento) references dipartimento(id)
) engine=innodb;

--
--	Tabella: studente
--
create table studente (
	matricola varchar(10) not null,
	nome varchar(30),
	cognome varchar(30) not null,
	email varchar(50),
	data_nascita datetime,
	comune_nascita varchar(50),
	telefono varchar(20),
	id_laurea int not null,
	primary key (matricola),
	foreign key (id_laurea) references corso_laurea(id)
) engine=innodb;

--
--	Tabella: titolo_studio
--
create table titolo_studio (
	matricola_studente varchar(10) not null,
	id_scuola int not null,
	voto int,
	primary key (matricola_studente, id_scuola),
	foreign key (matricola_studente) references studente(matricola),
	foreign key (id_scuola) references scuola(id)
) engine=innodb;

--
--	Tabella: conoscenza_lingua
--
create table conoscenza_lingua (
	matricola_studente varchar(10) not null,
	id_lingua int not null,
	scritto int not null,
	orale int not null,
	primary key (matricola_studente, id_lingua),
	foreign key (matricola_studente) references studente(matricola),
	foreign key (id_lingua) references linguastraniera(id)
) engine=innodb;

--
--	Tabella: esame
--
create table esame (
	matricola_studente varchar(10) not null,
	sigla_corso varchar(10) not null,
	anno int not null,
	voto int not null,
	primary key (matricola_studente, sigla_corso),
	foreign key (matricola_studente) references studente(matricola),
	foreign key (sigla_corso) references corso(sigla)
) engine=innodb;

--
--	Inserimento dati nella tabella "dipartimento"
--
insert into dipartimento (nome) values ('Matematica e Fisica');
insert into dipartimento (nome) values ('Lettere e Filosofia');
insert into dipartimento (nome) values ('Ingegneria');
insert into dipartimento (nome) values ('Scienze della Formazione');
insert into dipartimento (nome) values ('Architettura');

--
--	Inserimento dati nella tabella "docente"
--
insert into docente (nome, cognome, email, dipartimento) values ('Marco', 'Liverani', 'liverani@mat.uniroma3.it', 1);
insert into docente (nome, cognome, email, dipartimento) values ('Marco', 'Pedicini', 'pedicini@mat.uniroma3.it', 1);
insert into docente (nome, cognome, email, dipartimento) values ('Francesca', 'Tartarone', 'tfrance@mat.uniroma3.it', 1);
insert into docente (nome, cognome, email, dipartimento) values ('Luigi', 'Chierchia', 'luigi@mat.uniroma3.it', 1);
insert into docente (nome, cognome, email, dipartimento) values ('Angelo Felice', 'Lopez', 'lopez@mat.uniroma3.it', 1);
insert into docente (nome, cognome, email, dipartimento) values ('Marco', 'Carli', 'marco.carli@uniroma3.it', 3);
insert into docente (nome, cognome, email, dipartimento) values ('Roberto', 'Maieli', 'maieli@mat.uniroma3.it', 1);
insert into docente (nome, cognome, email, dipartimento) values ('Ana', 'Millan Gasca', 'ana.millan@uniroma3.it', 4);
insert into docente (nome, cognome, email, dipartimento) values ('Paola', 'Magrone', 'magrone@mat.uniroma3.it', 5);
insert into docente (nome, cognome, email, dipartimento) values ('Corrado', 'Falcolini', 'falco@mat.uniroma3.it', 5);

--
--	Inserimento dati nella tabella "corso"
--
insert into corso (sigla, aa, titolo, id_docente, ssd, cfu) values ('IN110', '2019/2020', 'Informatica 1', 1, 'INF/01', 10);
insert into corso (sigla, aa, titolo, id_docente, ssd, cfu) values ('IN110', '2018/2019', 'Informatica 1', 7, 'INF/01', 10);
insert into corso (sigla, aa, titolo, id_docente, ssd, cfu) values ('IN440', '2018/2019', 'Ottimizzazione combinatoria', 1, 'INF/01', 7);
insert into corso (sigla, aa, titolo, id_docente, ssd, cfu) values ('IN440', '2019/2020', 'Ottimizzazione combinatoria', 1, 'INF/01', 7);
insert into corso (sigla, aa, titolo, id_docente, ssd, cfu) values ('IN530', '2018/2019', 'Sist. Elab. Informazioni', 1, 'INF/01', 7);
insert into corso (sigla, aa, titolo, id_docente, ssd, cfu) values ('IN410', '2018/2019', 'Calcolabilita'' e complessita''', 2, 'INF/01', 7);
insert into corso (sigla, aa, titolo, id_docente, ssd, cfu) values ('IN450', '2018/2019', 'Algoritmi per la crittografia', 2, 'INF/01', 7);
insert into corso (sigla, aa, titolo, id_docente, ssd, cfu) values ('AL110', '2018/2019', 'Algebra 1', 3, 'MAT/02', 10);
insert into corso (sigla, aa, titolo, id_docente, ssd, cfu) values ('AM110', '2018/2019', 'Analisi matematica 1', 4, 'MAT/05', 10);
insert into corso (sigla, aa, titolo, id_docente, ssd, cfu) values ('GE210', '2018/2019', 'Geometria e algebra lineare 2', 5, 'MAT/03', 10);
insert into corso (sigla, aa, titolo, id_docente, ssd, cfu) values ('IN520', '2018/2019', 'Sicurezza delle telecomunicazioni', 6, 'ING-INF/03', 6);

--
--	Inserimento dati nella tabella "linguastraniera"
--
insert into linguastraniera (nome) values ('inglese');
insert into linguastraniera (nome) values ('francese');
insert into linguastraniera (nome) values ('spagnolo');
insert into linguastraniera (nome) values ('tedesco');
insert into linguastraniera (nome) values ('portoghese');
insert into linguastraniera (nome) values ('russo');
insert into linguastraniera (nome) values ('cinese');

--
--	Inserimento dati nella tabella "scuola"
--
insert into scuola (nome, citta, titolo) values ('Liceo Scientifico Statale "Peano"', 'Roma', 'Diploma Maturita'' Scientifica');
insert into scuola (nome, citta, titolo) values ('Liceo Scientifico Statale "Morgagni"', 'Roma', 'Diploma Maturita'' Scientifica');
insert into scuola (nome, citta, titolo) values ('Liceo Scientifico Statale "Kennedy"', 'Roma', 'Diploma Maturita'' Scientifica');
insert into scuola (nome, citta, titolo) values ('Liceo Scientifico Statale "Keplero"', 'Roma', 'Diploma Maturita'' Scientifica');
insert into scuola (nome, citta, titolo) values ('Liceo Classico Statale "Manara"', 'Roma', 'Diploma Maturita'' Classica');
insert into scuola (nome, citta, titolo) values ('Liceo Classico Statale "Visconti"', 'Roma', 'Diploma Maturita'' Classica');
insert into scuola (nome, citta, titolo) values ('Liceo Classico Statale "Virgilio"', 'Roma', 'Diploma Maturita'' Classica');
insert into scuola (nome, citta, titolo) values ('Liceo Classico Statale "Vivona"', 'Roma', 'Diploma Maturita'' Classica');
insert into scuola (nome, citta, titolo) values ('Istituto Tecnico Industriale "Armellini"', 'Roma', 'Perito Industriale');
insert into scuola (nome, citta, titolo) values ('Istituto Tecnico Industriale "Volta"', 'Roma', 'Perito Industriale');
insert into scuola (nome, citta, titolo) values ('Istituto Tecnico Commerciale "Ruiz"', 'Roma', 'Perito Aziendale');

--
--	Inserimento dati nella tabella "corso_laurea"
--
insert into corso_laurea (nome, id_dipartimento) values ('Matematica', 1);
insert into corso_laurea (nome, id_dipartimento) values ('Fisica', 1);
insert into corso_laurea (nome, id_dipartimento) values ('Scienze Computazionali', 1);
insert into corso_laurea (nome, id_dipartimento) values ('Lettere classiche', 2);
insert into corso_laurea (nome, id_dipartimento) values ('Lettere moderne', 2);
insert into corso_laurea (nome, id_dipartimento) values ('Storia', 2);
insert into corso_laurea (nome, id_dipartimento) values ('Ingegneria informatica', 3);
insert into corso_laurea (nome, id_dipartimento) values ('Ingegneria civile', 3);
insert into corso_laurea (nome, id_dipartimento) values ('Ingegneria gestionale', 3);

--
--	Inserimento dati nella tabella "studente"
--
insert into studente (matricola, nome, cognome, email, data_nascita, comune_nascita, telefono, id_laurea) values ('0987', 'Andrea', 'Bianchi', 'a.bianchi@libero.it', '1998-04-21', 'Roma', '063845185', 1);
insert into studente (matricola, nome, cognome, email, data_nascita, comune_nascita, telefono, id_laurea) values ('1234', 'Beatrice', 'Danteschi', 'bea_d@gmail.com', '1998-07-01', 'Firenze', '069374651', 1);
insert into studente (matricola, nome, cognome, email, data_nascita, comune_nascita, telefono, id_laurea) values ('9876', 'Franco', 'D''Amico', 'fda98@gmail.com', '1998-02-15', 'Roma', '064466771', 1);
insert into studente (matricola, nome, cognome, email, data_nascita, comune_nascita, telefono, id_laurea) values ('9884', 'Francesca', 'D''Amico', 'francesca_damico@hotmail.com', '1999-11-01', 'Roma', '064466771', 1);
insert into studente (matricola, nome, cognome, email, data_nascita, comune_nascita, telefono, id_laurea) values ('6501', 'Francesca', 'Rotondi', 'rfrancy@libero.it', '1998-03-21', 'Napoli', '063971203', 1);

--
--	Inserimento dati nella tabella "titolo_studio"
--
insert into titolo_studio (matricola_studente, id_scuola, voto) values ('0987', 1, 100);
insert into titolo_studio (matricola_studente, id_scuola, voto) values ('1234', 6, 90);
insert into titolo_studio (matricola_studente, id_scuola, voto) values ('9876', 2, 96);
insert into titolo_studio (matricola_studente, id_scuola, voto) values ('9884', 1, 82);
insert into titolo_studio (matricola_studente, id_scuola, voto) values ('6501', 9, 100);

--
--	Inserimento dati nella tabella "conoscenza_lingua"
--
insert into conoscenza_lingua (matricola_studente, id_lingua, scritto, orale) values ('0987', 1, 10, 10);
insert into conoscenza_lingua (matricola_studente, id_lingua, scritto, orale) values ('1234', 1, 7, 6);
insert into conoscenza_lingua (matricola_studente, id_lingua, scritto, orale) values ('1234', 2, 8, 8);
insert into conoscenza_lingua (matricola_studente, id_lingua, scritto, orale) values ('6501', 1, 8, 8);
insert into conoscenza_lingua (matricola_studente, id_lingua, scritto, orale) values ('6501', 3, 6, 6);
insert into conoscenza_lingua (matricola_studente, id_lingua, scritto, orale) values ('6501', 4, 6, 7);
insert into conoscenza_lingua (matricola_studente, id_lingua, scritto, orale) values ('9876', 1, 7, 7);
insert into conoscenza_lingua (matricola_studente, id_lingua, scritto, orale) values ('9876', 2, 6, 6);
insert into conoscenza_lingua (matricola_studente, id_lingua, scritto, orale) values ('9884', 1, 8, 9);
insert into conoscenza_lingua (matricola_studente, id_lingua, scritto, orale) values ('9884', 3, 7, 7);

--
--	Inserimento dati nella tabella "esame"
--
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('0987', 'AL110', 2017, 26);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('0987', 'IN110', 2017, 28);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('0987', 'GE210', 2018, 23);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('0987', 'IN410', 2018, 28);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('0987', 'IN440', 2018, 30);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('1234', 'AL110', 2017, 30);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('1234', 'AM110', 2017, 29);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('1234', 'IN110', 2017, 28);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('1234', 'GE210', 2018, 28);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('6501', 'AL110', 2017, 24);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('6501', 'AM110', 2017, 29);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('6501', 'IN110', 2017, 28);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('6501', 'GE210', 2018, 30);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('6501', 'IN450', 2018, 29);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('6501', 'IN440', 2018, 26);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('9876', 'AL110', 2017, 21);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('9876', 'IN110', 2018, 23);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('9884', 'IN110', 2017, 26);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('9884', 'AL110', 2017, 20);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('9884', 'GE210', 2018, 28);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('9884', 'IN440', 2018, 28);
insert into esame (matricola_studente, sigla_corso, anno, voto) values ('9884', 'IN410', 2018, 29);
