--
--	DATABASE BIBLIOTECA
--
--	Script SQL per la creazione delle tabelle del DB.
--

--
--	Eliminazione delle tabelle se esistono
--
drop table if exists autore;
drop table if exists prestito;
drop table if exists libro;
drop table if exists genere;
drop table if exists lingua;
drop table if exists scrittore;
drop table if exists utente;

--
--	Tabella: genere
--	
--	Generi dei libri presenti in biblioteca.
--
create table genere (
	id int not null auto_increment,
	nome varchar(100) not null,
	primary key (id)
) engine=innodb;

--
--	Tabella: lingua
--
--	Lingua in cui e' scritto il libro.
--
create table lingua (
	id char(2) not null,
	nome varchar(50) not null,
	primary key (id)
) engine=innodb;

--
--	Tabella: scrittore
--
--	Entita' anagrafica degli scrittori che hanno contribuito
--	come autori di libri.
--
create table scrittore (
	id int not null auto_increment,
	nome varchar(45),
	cognome varchar(45) not null,
	primary key (id)
) engine=innodb;

--
--	Tabella: utente
--
--	Anagrafica degli utenti della biblioteca che possono effettuare
--	prelievi di libri in prestito.
--
create table utente (
	tessera varchar(10) not null,
	nome varchar(45),
	cognome varchar(45) not null,
	email varchar(100),
	telefono varchar(20),
	primary key (tessera)
) engine=innodb;

--
--	Tabella: libro
--
--	Volumi presenti in biblioteca.
--
create table libro (
	id int not null auto_increment,
	titolo varchar(255) not null,
	editore varchar(100),
	anno int,
	id_genere int not null,
	id_lingua char(2) not null,
	primary key (id),
	foreign key (id_genere) references genere(id) on update cascade on delete restrict,
	foreign key (id_lingua) references lingua(id) on update cascade on delete restrict
) engine innodb;

--
--	Tabella: autore
--
--	Relazione tra libri e scrittori per definire gli autori di ciascun libro
--
create table autore (
	id_libro int not null,
	id_scrittore int not null,
	primary key (id_libro, id_scrittore),
	foreign key (id_libro) references libro(id) on delete cascade on update cascade,
	foreign key (id_scrittore) references scrittore(id) on delete cascade on update cascade
) engine=innodb;

--
--	Tabella: prestito
--
--	Relazione tra libri e utenti per definire il prestito (o la restituzione) di un libro
--	da parte di un utente della biblioteca
--
create table prestito (
	id_libro int not null,
	tessera varchar(10) not null,
	data_prestito datetime not null,
	data_restituzione datetime,
	foreign key (id_libro) references libro(id) on delete restrict on update cascade,
	foreign key (tessera) references utente(tessera) on delete restrict on update cascade
) engine=innodb;

--
--	Inserimento generi nella tabella "genere"
--
insert into genere (nome) values ('Narrativa italiana');
insert into genere (nome) values ('Narrativa straniera');
insert into genere (nome) values ('Saggio');
insert into genere (nome) values ('Matematica');
insert into genere (nome) values ('Informatica');
insert into genere (nome) values ('Fisica');
insert into genere (nome) values ('Fumetti');
insert into genere (nome) values ('Poesia');

--
--	Inserimento lingue nella tabella "lingua"
--
insert into lingua (id, nome) values ('IT', 'Italiano');
insert into lingua (id, nome) values ('EN', 'Inglese');
insert into lingua (id, nome) values ('FR', 'Francese');
insert into lingua (id, nome) values ('DE', 'Tedesco');
insert into lingua (id, nome) values ('RU', 'Russo');
insert into lingua (id, nome) values ('ES', 'Spagnolo');
insert into lingua (id, nome) values ('PT', 'Portoghese');
--
--	Inserimento anagrafiche degli utenti della biblioteca nella tabella "utente"
--
insert into utente (tessera, nome, cognome, email, telefono) values ('0001', 'Marco', 'Liverani', 'liverani@mat.uniroma3.it', '061234567');
insert into utente (tessera, nome, cognome, email, telefono) values ('0002', 'Maria', 'Rossi', 'redmary@gmail.com', '368123098');
insert into utente (tessera, nome, cognome, email, telefono) values ('0003', 'Francesco', 'Dell''Acqua', 'fda84@libero.it', '3351029384');
insert into utente (tessera, nome, cognome, email, telefono) values ('0004', 'Maria', 'Filippetti', 'ma.filippe@yahoo.com', '3399182736');

--
--	Inserimento di scrittori nella tabella "scrittore"
--
insert into scrittore (nome, cognome) values ('Leonardo', 'Sciascia');
insert into scrittore (nome, cognome) values ('Andrea', 'Camilleri');
insert into scrittore (nome, cognome) values ('Alessandro', 'Barbero');
insert into scrittore (nome, cognome) values ('Romain', 'Gary');
insert into scrittore (nome, cognome) values ('Boris', 'Pahor');
insert into scrittore (nome, cognome) values ('Melania G.', 'Mazzucco');
insert into scrittore (nome, cognome) values ('Irene', 'Nemirovsky');
insert into scrittore (nome, cognome) values ('Thomas H.', 'Cormen');
insert into scrittore (nome, cognome) values ('Charles E.', 'Leiserson');
insert into scrittore (nome, cognome) values ('Ronald L.', 'Rivest');
insert into scrittore (nome, cognome) values ('Clifford', 'Stein');
insert into scrittore (nome, cognome) values ('Alfred V.', 'Aho');
insert into scrittore (nome, cognome) values ('John E.', 'Hopcroft');
insert into scrittore (nome, cognome) values ('Jeffrey D.', 'Ullman');
insert into scrittore (nome, cognome) values ('Rajeev', 'Motwani');
insert into scrittore (nome, cognome) values ('Rene''', 'Goscinny');
insert into scrittore (nome, cognome) values ('Albert', 'Uderzo');
insert into scrittore (nome, cognome) values ('Nigel J.', 'Cutland');
insert into scrittore (nome, cognome) values ('Frank', 'Harary');
insert into scrittore (nome, cognome) values ('Craig', 'Hunt');
insert into scrittore (nome, cognome) values ('Georges', 'Simenon');
insert into scrittore (nome, cognome) values ('Jose''', 'Saramago');
insert into scrittore (nome, cognome) values ('Jorge Luis', 'Borges');
insert into scrittore (nome, cognome) values ('Umberto', 'Eco');
insert into scrittore (nome, cognome) values ('Jean-Michel', 'Guenassia');
insert into scrittore (nome, cognome) values ('Ian', 'McEwan');
insert into scrittore (nome, cognome) values ('Sebastiano', 'Vassalli');

--
--	Inserimento libri nella tabella "libro"
--
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Asterix il Gallico', 'Mondadori', 1968, 7, 'IT');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Asterix e Cleopatra', 'Mondadori', 1999, 7, 'IT');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Suite francese', 'Adelphi', 2005, 2, 'IT');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Le ateniesi', 'Mondadori', 2015, 1, 'IT');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Il giorno della civetta', 'Adelphi', 1961, 1, 'IT');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Una storia semplice', 'Adelphi', 1989, 1, 'IT');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Porte aperte', 'Adelphi', 1987, 1, 'IT');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Todo modo', 'Adelphi', 1974, 1, 'IT');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('La concessione del telefono', 'Sellerio', 1998, 1, 'IT');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('La voce del violino', 'Sellerio', 1997, 1, 'IT');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('La mossa del cavallo', 'Rizzoli', 1999, 1, 'IT');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Educazione europea', 'Neri Pozza', 2017, 2, 'IT');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Necropoli', 'Fazi', 2008, 1, 'IT');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('La lunga attesa dell''angelo', 'Rizzoli', 2008, 1, 'IT');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Automi, linguaggi e calcolabilita''', 'Addison-Wesley', 2003, 5, 'IT');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Data Structures and Algorithms', 'Addison-Wesley', 1987, 5, 'EN');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Introduzione agli algoritmi e strutture dati', 'McGraw-Hill', 2010, 5, 'IT');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('TCP/IP Network Administration', 'O''Reilly & Associates', 1994, 5, 'EN');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Graph Theory', 'Perseus Books', 1969, 4, 'EN');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Computability', 'Cambridge University Press', 1980, 4, 'EN');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Maigret a Vichy', 'Omnibus', 2012, 2, 'FR');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Les nouvelles enquetes de Maigret', 'Omnibus', 2014, 2, 'FR');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Maigret et la jeune morte', 'Omnibus', 2012, 2, 'FR');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Ficciones', 'Random Spagna', 2011, 2, 'ES');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('El Aleph', 'Random Spagna', 2011, 2, 'ES');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Ensaio sobre a Cegueira', 'Editora Porto', 2014, 2, 'PT');
insert into libro (titolo, editore, anno, id_genere, id_lingua) values ('Memorial do convento', 'Editora Porto', 1982, 2, 'PT');

--
--	Inserimento relazione autore tra libro e scrittore
--
insert into autore (id_libro, id_scrittore) values (1, 16);
insert into autore (id_libro, id_scrittore) values (1, 17);
insert into autore (id_libro, id_scrittore) values (2, 16);
insert into autore (id_libro, id_scrittore) values (2, 17);
insert into autore (id_libro, id_scrittore) values (3, 7);
insert into autore (id_libro, id_scrittore) values (4, 3);
insert into autore (id_libro, id_scrittore) values (5, 1);
insert into autore (id_libro, id_scrittore) values (6, 1);
insert into autore (id_libro, id_scrittore) values (7, 1);
insert into autore (id_libro, id_scrittore) values (8, 1);
insert into autore (id_libro, id_scrittore) values (9, 2);
insert into autore (id_libro, id_scrittore) values (10, 2);
insert into autore (id_libro, id_scrittore) values (11, 2);
insert into autore (id_libro, id_scrittore) values (12, 4);
insert into autore (id_libro, id_scrittore) values (13, 5);
insert into autore (id_libro, id_scrittore) values (14, 6);
insert into autore (id_libro, id_scrittore) values (15, 13);
insert into autore (id_libro, id_scrittore) values (15, 14);
insert into autore (id_libro, id_scrittore) values (15, 15);
insert into autore (id_libro, id_scrittore) values (16, 12);
insert into autore (id_libro, id_scrittore) values (16, 13);
insert into autore (id_libro, id_scrittore) values (16, 14);
insert into autore (id_libro, id_scrittore) values (17, 8);
insert into autore (id_libro, id_scrittore) values (17, 9);
insert into autore (id_libro, id_scrittore) values (17, 10);
insert into autore (id_libro, id_scrittore) values (17, 11);
insert into autore (id_libro, id_scrittore) values (18, 20);
insert into autore (id_libro, id_scrittore) values (19, 19);
insert into autore (id_libro, id_scrittore) values (20, 18);
insert into autore (id_libro, id_scrittore) values (21, 21);
insert into autore (id_libro, id_scrittore) values (22, 21);
insert into autore (id_libro, id_scrittore) values (23, 21);
insert into autore (id_libro, id_scrittore) values (24, 23);
insert into autore (id_libro, id_scrittore) values (25, 23);
insert into autore (id_libro, id_scrittore) values (26, 22);
insert into autore (id_libro, id_scrittore) values (27, 22);

--
--	Inserimento prestiti
--
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (3, '0002', '2017-06-18', '2017-07-30');
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (12, '0001', '2017-06-20', '2017-08-03');
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (27, '0001', '2017-07-12', '2017-08-24');
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (19, '0002', '2017-08-10', '2017-08-31');
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (15, '0001', '2017-08-15', '2017-10-20');
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (14, '0002', '2017-09-05', '2017-10-27');
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (13, '0001', '2017-09-10', '2017-10-31');
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (4, '0001', '2017-10-10', '2017-11-15');
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (8, '0002', '2017-11-20', '2017-12-13');
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (6, '0001', '2017-12-13', '2018-01-07');
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (7, '0002', '2017-12-20', '2018-01-14');
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (19, '0001', '2018-05-01', NULL);
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (3, '0001', '2018-02-01', '2018-03-09');
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (20, '0001', '2018-06-04', NULL);
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (1, '0002', '2018-02-07', '2018-03-20');
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (2, '0002', '2018-03-20', '2018-04-02');
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (18, '0004', '2018-07-01', '2018-09-09');
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (3, '0002', '2018-08-26', NULL);
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (17, '0004', '2018-09-01', '2018-10-15');
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (27, '0003', '2018-09-03', '2018-10-10');
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (12, '0004', '2018-10-05', NULL);
insert into prestito (id_libro, tessera, data_prestito, data_restituzione) values (13, '0004', '2018-10-25', NULL);
