DROP DATABASE IF EXISTS libros;
CREATE DATABASE libros;
USE libros;

DROP TABLE IF EXISTS `Socis`;
CREATE TABLE `Socis` (
	`Nom` varchar(15) NOT NULL,
	`Apellido` varchar(15) NOT NULL,
	`ID` int(4) NOT NULL AUTO_INCREMENT,
	`Fecha` DATE NOT NULL,
	`Sexo` varchar(6) NOT NULL,
	PRIMARY KEY (`ID`)
);

DROP TABLE IF EXISTS `Productos`;
CREATE TABLE `Productos` (
	`Nombre` varchar(60) NOT NULL,
	`Autor` varchar(30) NOT NULL,
	`Publicacion` DATE NOT NULL,
	`Cantidad` int(4) NOT NULL,
	`ID` int(6) NOT NULL AUTO_INCREMENT,
	`TematicaProducto` int(6) NOT NULL,
	PRIMARY KEY (`ID`)
);

DROP TABLE IF EXISTS `Tematica`;
CREATE TABLE `Tematica` (
	`Nombre` varchar(25) NOT NULL,
	`ID` int(6) NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`ID`)
);

DROP TABLE IF EXISTS `Prestec`;
CREATE TABLE `Prestec` (
	`ID Libro` int(6) NOT NULL,
	`ID Soci` int(6) NOT NULL,
	`Alquila` DATETIME NOT NULL,
	`Devuelve` DATETIME NOT NULL,
	PRIMARY KEY (`ID Libro`,`ID Soci`)
);

ALTER TABLE `Productos` ADD CONSTRAINT `Productos_fk0` FOREIGN KEY (`TematicaProducto`) REFERENCES `Tematica`(`ID`);

ALTER TABLE `Prestec` ADD CONSTRAINT `Prestec_fk0` FOREIGN KEY (`ID Libro`) REFERENCES `Productos`(`ID`);

ALTER TABLE `Prestec` ADD CONSTRAINT `Prestec_fk1` FOREIGN KEY (`ID Soci`) REFERENCES `Socis`(`ID`);


insert

SET AUTOCOMMIT=0;

INSERT INTO Socis(Nom, Apellido, Fecha, Sexo)
VALUES ("Pobedilla", "Hernandez", "1995-7-5", "Hombre"), ("Javier", "Cano", "1997-1-3", "Hombre"), ("Laura", "Lozano", "1994-3-17", "Mujer"), ("Patricia", "Lozano", "1996-3-19", "Mujer"), ("Ivan", "Villalba", "1995-3-20", "Mujer"), ("Carlos", "Lozano", "1990-9-7", "Hombre");
INSERT INTO Productos(Nombre, Autor, Publicacion, Cantidad, Tematica)
VALUES ("Alberto y sus amigos", "Fernandez", "2002-4-2", 10, "Terror"), ("El pozo aleman", "Corpas", "2005-10-5", 9, "Historia"), ("Los tentaculos de Adelita", "Xius", "2000-1-7", 2, "Tentaculos"), ("Minion Master", "Jarero", "1995-1-1", 300, "Novela Negra"), ("Como hacer crecer el pelo", "Snake", "1999-1-9", 5, "Documental"), ("Marina", "Kike", "2008-2-8", 36, "Cienca Ficcion");
INSERT INTO Prestec(ID Libro, ID Soci, Alquila, Devuelve)
VALUES (013510, 000123, "1990-1-1", "1998-2-1"), (013213, 004536, "1990-10-10", "1990-12-11"), (113510, 020123, "1990-3-6", "1990-5-19"), (012511, 070123, "1990-1-10", "1990-2-19"), (013525, 000127, "1990-12-2", "1991-1-15"), (004510, 000001, "1990-10-1", "1992-2-1");
EXPLAIN INSERT INTO Tematica(Nombre)
VALUES ("Terror"), ("Historia"), ("Tentaculos"), ("Novela Negra"), ("Documental"), ("Ciencia Ficcion"), ("Fantasia");