avLGvuq1

1
2SET NAMES utf8;
3SET time_zone = '+00:00';
4SET foreign_key_checks = 0;
5SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
6
7SET NAMES utf8mb4;
8
9DROP TABLE IF EXISTS `libro`;
10CREATE TABLE `libro` (
11  `npaginas` int(11) NOT NULL,
12  `id_libro` bigint(20) NOT NULL AUTO_INCREMENT,
13  `editorial` varchar(20) NOT NULL,
14  `titulo` varchar(20) NOT NULL,
15  `f_publicacion` date NOT NULL,
16  `ISBN` varchar(13) NOT NULL,
17  `id` bigint(20) NOT NULL,
18  PRIMARY KEY (`id_libro`)
19) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
20
21INSERT INTO `libro` (`npaginas`, `id_libro`, `editorial`, `titulo`, `f_publicacion`, `ISBN`, `id`) VALUES
22(3124,	12,	'Amarillo',	'Paco tenia un coche',	'2019-12-13',	'3235235',	0),
23(123,	13,	'Illo',	'Librillo',	'2019-12-05',	'23423542',	0);
24
25DROP TABLE IF EXISTS `prestamo`;
26CREATE TABLE `prestamo` (
27  `id_prestamo` bigint(20) NOT NULL AUTO_INCREMENT,
28  `id_libro` bigint(20) NOT NULL,
29  `id_user` bigint(20) NOT NULL,
30  `prorroga` tinyint(4) NOT NULL,
31  `f_prestamo` date NOT NULL,
32  `f_devolucion` date NOT NULL,
33  `estado` varchar(13) NOT NULL,
34  `id` bigint(20) NOT NULL,
35  `libro` bigint(20) NOT NULL,
36  `usuario` bigint(20) NOT NULL,
37  PRIMARY KEY (`id_prestamo`),
38  KEY `id_user` (`id_user`),
39  KEY `prestamo_ibfk_1` (`id_libro`),
40  CONSTRAINT `prestamo_ibfk_1` FOREIGN KEY (`id_libro`) REFERENCES `libro` (`id_libro`) ON DELETE CASCADE,
41  CONSTRAINT `prestamo_ibfk_2` FOREIGN KEY (`id_user`) REFERENCES `usuario` (`id_user`) ON DELETE CASCADE
42) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
43
44
45DROP TABLE IF EXISTS `usuario`;
46CREATE TABLE `usuario` (
47  `id_user` bigint(20) NOT NULL AUTO_INCREMENT,
48  `username` varchar(20) NOT NULL,
49  `password` varchar(20) NOT NULL,
50  `rol` varchar(20) NOT NULL,
51  `name` varchar(20) DEFAULT NULL,
52  `surname` varchar(50) DEFAULT NULL,
53  `email` varchar(50) DEFAULT NULL,
54  `dni` varchar(9) DEFAULT NULL,
55  `telefono` varchar(15) DEFAULT NULL,
56  `id` bigint(20) NOT NULL,
57  PRIMARY KEY (`id_user`)
58) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
59
60INSERT INTO `usuario` (`id_user`, `username`, `password`, `rol`, `name`, `surname`, `email`, `dni`, `telefono`, `id`) VALUES
61(8,	'prrr',	'1234',	'ROLE_PROFESOR',	'good bunny',	'bunny',	'bad@bunny',	'12352325',	'523523523',	0);