MariaDB [(none)]> create database biblioteca; Query OK, 1 row affected (0,001 sec) MariaDB [(none)]> CREATE TABLE Autor ( -> id_autor INT NOT NULL PRIMARY KEY, -> nombre_autor VARCHAR(150) NOT NULL, -> nacimiento DATE NOT NULL, -> fallecimiento DATE -> ); ERROR 1046 (3D000): No database selected MariaDB [(none)]> use biblioteca; Database changed MariaDB [biblioteca]> CREATE TABLE Autor ( -> id_autor INT NOT NULL PRIMARY KEY, -> nombre_autor VARCHAR(150) NOT NULL, -> nacimiento DATE NOT NULL, -> fallecimiento DATE -> ); Query OK, 0 rows affected (0,010 sec) MariaDB [biblioteca]> show tables; +----------------------+ | Tables_in_biblioteca | +----------------------+ | Autor | +----------------------+ 1 row in set (0,001 sec) MariaDB [biblioteca]> describe autor; ERROR 1146 (42S02): Table 'biblioteca.autor' doesn't exist MariaDB [biblioteca]> describe Autor; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | id_autor | int(11) | NO | PRI | NULL | | | nombre_autor | varchar(150) | NO | | NULL | | | nacimiento | date | NO | | NULL | | | fallecimiento | date | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 4 rows in set (0,003 sec) MariaDB [biblioteca]> drop table Autor -> ; Query OK, 0 rows affected (0,022 sec) MariaDB [biblioteca]> show tables; Empty set (0,000 sec) MariaDB [biblioteca]> create table autor ( -> id_autor int not null primary key, -> nombre_autor varchar(150) not null, -> nacimiento date not null, -> fallecimiento date -> ); Query OK, 0 rows affected (0,011 sec) MariaDB [biblioteca]> describe autor; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | id_autor | int(11) | NO | PRI | NULL | | | nombre_autor | varchar(150) | NO | | NULL | | | nacimiento | date | NO | | NULL | | | fallecimiento | date | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 4 rows in set (0,003 sec) MariaDB [biblioteca]> create table estudiante ( -> id_estudiante int not null primary key, -> nombre_estudiante varchar(150) not null, -> carrera varchar(100) not null, -> correo varchar(150) not null -> ); Query OK, 0 rows affected (0,010 sec) MariaDB [biblioteca]> show tables; +----------------------+ | Tables_in_biblioteca | +----------------------+ | autor | | estudiante | +----------------------+ 2 rows in set (0,001 sec) MariaDB [biblioteca]> describe estudiante; +-------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+-------+ | id_estudiante | int(11) | NO | PRI | NULL | | | nombre_estudiante | varchar(150) | NO | | NULL | | | carrera | varchar(100) | NO | | NULL | | | correo | varchar(150) | NO | | NULL | | +-------------------+--------------+------+-----+---------+-------+ 4 rows in set (0,002 sec) MariaDB [biblioteca]> create table libro ( -> id_libro int not null primary key, -> titulo varchar(150) not null, -> categoria varchar(100) not null, -> year_publicacion date not null, -> id_autor int not null, -> foreign key (id_autor) references autor(id_autor) -> ); Query OK, 0 rows affected (0,015 sec) MariaDB [biblioteca]> describe libro; +------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+-------+ | id_libro | int(11) | NO | PRI | NULL | | | titulo | varchar(150) | NO | | NULL | | | categoria | varchar(100) | NO | | NULL | | | year_publicacion | date | NO | | NULL | | | id_autor | int(11) | NO | MUL | NULL | | +------------------+--------------+------+-----+---------+-------+ 5 rows in set (0,003 sec) MariaDB [biblioteca]> create table prestamo ( -> id_prestamo int not null auto_increment primary key, -> id_libro int not null, -> id_estudiante int not null, -> fecha_pres date not null, -> fecha_devo date not null, -> foreign key (id_libro) references libro(id_libro), -> foreign key (id_estudiante) references estudiante(id_estudiante) -> ); Query OK, 0 rows affected (0,017 sec) MariaDB [biblioteca]> describe prestamo; +---------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------+------+-----+---------+----------------+ | id_prestamo | int(11) | NO | PRI | NULL | auto_increment | | id_libro | int(11) | NO | MUL | NULL | | | id_estudiante | int(11) | NO | MUL | NULL | | | fecha_pres | date | NO | | NULL | | | fecha_devo | date | NO | | NULL | | +---------------+---------+------+-----+---------+----------------+ 5 rows in set (0,003 sec) MariaDB [biblioteca]> insert into autor (id_autor, nombre_autor, nacimiento, fallecimiento) values -> (1, 'gabriel garcía márquez', '1927-03-06', '2014-04-17'), -> (2, 'isabel allende', '1942-08-02', null), -> (3, 'j. k. rowling', '1965-07-31', null), -> (4, 'george orwell', '1903-06-25', '1950-01-21'), -> (5, 'mario vargas llosa', '1936-03-28', null), -> (6, 'julio verne', '1828-02-08', '1905-03-24'); Query OK, 6 rows affected (0,008 sec) Records: 6 Duplicates: 0 Warnings: 0 MariaDB [biblioteca]> select * from autor; +----------+--------------------------+------------+---------------+ | id_autor | nombre_autor | nacimiento | fallecimiento | +----------+--------------------------+------------+---------------+ | 1 | gabriel garcía márquez | 1927-03-06 | 2014-04-17 | | 2 | isabel allende | 1942-08-02 | NULL | | 3 | j. k. rowling | 1965-07-31 | NULL | | 4 | george orwell | 1903-06-25 | 1950-01-21 | | 5 | mario vargas llosa | 1936-03-28 | NULL | | 6 | julio verne | 1828-02-08 | 1905-03-24 | +----------+--------------------------+------------+---------------+ 6 rows in set (0,001 sec) MariaDB [biblioteca]> insert into libro (id_libro, titulo, categoria, year_publicacion, id_autor) values -> (1, 'cien años de soledad', 'novela', '1967-06-05', 1), -> (2, 'el amor en los tiempos del cólera', 'novela', '1985-09-05', 1), -> (3, 'la casa de los espíritus', 'novela', '1982-01-01', 2), -> (4, 'harry potter y la piedra filosofal', 'fantasía', '1997-06-26', 3), -> (5, 'harry potter y la cámara secreta', 'fantasía', '1998-07-02', 3), -> (6, '1984', 'distopía', '1949-06-08', 4), -> (7, 'rebelión en la granja', 'sátira', '1945-08-17', 4), -> (8, 'la ciudad y los perros', 'novela', '1963-01-01', 5), -> (9, 'la guerra del fin del mundo', 'novela histórica', '1981-01-01', 5), -> (10, 'viaje al centro de la tierra', 'ciencia ficción', '1864-11-25', 6); Query OK, 10 rows affected (0,008 sec) Records: 10 Duplicates: 0 Warnings: 0 MariaDB [biblioteca]> insert into estudiante (id_estudiante, nombre_estudiante, carrera, correo) values -> (1, 'laura gómez', 'ingeniería de sistemas', 'laura.gomez@uni.edu'), -> (2, 'andrés pérez', 'derecho', 'andres.perez@uni.edu'), -> (3, 'maría rodríguez', 'medicina', 'maria.rodriguez@uni.edu'), -> (4, 'carlos lópez', 'arquitectura', 'carlos.lopez@uni.edu'); Query OK, 4 rows affected (0,005 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [biblioteca]> MariaDB [biblioteca]> insert into prestamo (id_libro, id_estudiante, fecha_pres, fecha_devo) values -> (1, 1, '2025-08-01', '2025-08-15'), -- laura pidió "cien años de soledad" -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2 MariaDB [biblioteca]> insert into prestamo (id_libro, id_estudiante, fecha_pres, fecha_devo) values -> (1, 1, '2025-08-01', '2025-08-15'), -> (4, 2, '2025-08-05', '2025-08-20'); Query OK, 2 rows affected (0,007 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [biblioteca]> DELIMITER // MariaDB [biblioteca]> create trigger before_prestamo_insert -> before insert on prestamo -> for each row -> begin -> if exists ( -> select 1 -> from prestamo -> where id_libro = new.id_libro -> and fecha_devo >= curdate() -> ) then -> signal sqlstate '45000' -> set message_text = 'el libro ya está prestado y no está disponible.'; -> end if; -> end// Query OK, 0 rows affected (0,005 sec) MariaDB [biblioteca]> DELIMITER ; MariaDB [biblioteca]> show triggers; +------------------------+--------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +------------------------+--------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+ | before_prestamo_insert | INSERT | prestamo | begin if exists ( select 1 from prestamo where id_libro = new.id_libro and fecha_devo >= curdate() ) then signal sqlstate '45000' set message_text = 'el libro ya está prestado y no está disponible.'; end if; end | BEFORE | 2025-08-28 22:43:27.40 | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci | +------------------------+--------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+ 1 row in set (0,003 sec) MariaDB [biblioteca]> select * from prestamo; +-------------+----------+---------------+------------+------------+ | id_prestamo | id_libro | id_estudiante | fecha_pres | fecha_devo | +-------------+----------+---------------+------------+------------+ | 1 | 1 | 1 | 2025-08-01 | 2025-08-15 | | 2 | 4 | 2 | 2025-08-05 | 2025-08-20 | +-------------+----------+---------------+------------+------------+ 2 rows in set (0,002 sec) MariaDB [biblioteca]> insert into prestamo (id_libro, id_estudiante, fecha_pres, fecha_devo) values (1, 3, '2025-08-06', '2025-08-21'); Query OK, 1 row affected (0,010 sec) MariaDB [biblioteca]> select * from prestamo; +-------------+----------+---------------+------------+------------+ | id_prestamo | id_libro | id_estudiante | fecha_pres | fecha_devo | +-------------+----------+---------------+------------+------------+ | 1 | 1 | 1 | 2025-08-01 | 2025-08-15 | | 2 | 4 | 2 | 2025-08-05 | 2025-08-20 | | 3 | 1 | 3 | 2025-08-06 | 2025-08-21 | +-------------+----------+---------------+------------+------------+ 3 rows in set (0,001 sec) MariaDB [biblioteca]> drop trigger before_prestamo_insert; Query OK, 0 rows affected (0,001 sec) MariaDB [biblioteca]> show triggers; Empty set (0,002 sec) MariaDB [biblioteca]> DELIMITER // MariaDB [biblioteca]> create trigger before_prestamo_insert -> before insert on prestamo -> for each row -> begin -> -- si el libro ya está prestado en las fechas que pide el estudiante, no permitir -> if exists ( -> select 1 -> from prestamo -> where id_libro = new.id_libro -> and fecha_devo >= new.fecha_pres -> ) then -> signal sqlstate '45000' -> set message_text = 'el libro ya está prestado en ese periodo.'; -> end if; -> end// Query OK, 0 rows affected (0,005 sec) MariaDB [biblioteca]> DELIMITER ; MariaDB [biblioteca]> insert into prestamo (id_libro, id_estudiante, fecha_pres, fecha_devo) values (1, 2, '2025-08-09', '2025-08-25'); ERROR 1644 (45000): el libro ya está prestado en ese periodo. MariaDB [biblioteca]> create table prestamos_eliminados ( -> id_prestamo int, -> id_libro int, -> id_estudiante int, -> fecha_pres date, -> fecha_devo date, -> usuario varchar(50), -> fecha_modif datetime -> ); Query OK, 0 rows affected (0,015 sec) MariaDB [biblioteca]> describe prestamos_eliminados; +---------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | id_prestamo | int(11) | YES | | NULL | | | id_libro | int(11) | YES | | NULL | | | id_estudiante | int(11) | YES | | NULL | | | fecha_pres | date | YES | | NULL | | | fecha_devo | date | YES | | NULL | | | usuario | varchar(50) | YES | | NULL | | | fecha_modif | datetime | YES | | NULL | | +---------------+-------------+------+-----+---------+-------+ 7 rows in set (0,002 sec) MariaDB [biblioteca]> DELIMITER // MariaDB [biblioteca]> create trigger auditar_prestamo -> after delete on prestamo -> for each row -> begin -> insert into prestamos_eliminados -> (id_prestamo, id_libro, id_estudiante, fecha_pres, fecha_devo, usuario, fecha_modif) -> values -> (old.id_prestamo, old.id_libro, old.id_estudiante, old.fecha_pres, old.fecha_devo, current_user(), now()); -> end// Query OK, 0 rows affected (0,006 sec) MariaDB [biblioteca]> DELIMITER ; MariaDB [biblioteca]> select * from prestamos; ERROR 1146 (42S02): Table 'biblioteca.prestamos' doesn't exist MariaDB [biblioteca]> show tables; +----------------------+ | Tables_in_biblioteca | +----------------------+ | autor | | estudiante | | libro | | prestamo | | prestamos_eliminados | +----------------------+ 5 rows in set (0,001 sec) MariaDB [biblioteca]> select * from prestamo; +-------------+----------+---------------+------------+------------+ | id_prestamo | id_libro | id_estudiante | fecha_pres | fecha_devo | +-------------+----------+---------------+------------+------------+ | 1 | 1 | 1 | 2025-08-01 | 2025-08-15 | | 2 | 4 | 2 | 2025-08-05 | 2025-08-20 | | 3 | 1 | 3 | 2025-08-06 | 2025-08-21 | +-------------+----------+---------------+------------+------------+ 3 rows in set (0,002 sec) MariaDB [biblioteca]> delete from prestamo where id_prestamo = 2; Query OK, 1 row affected (0,009 sec) MariaDB [biblioteca]> select * from prestamo; +-------------+----------+---------------+------------+------------+ | id_prestamo | id_libro | id_estudiante | fecha_pres | fecha_devo | +-------------+----------+---------------+------------+------------+ | 1 | 1 | 1 | 2025-08-01 | 2025-08-15 | | 3 | 1 | 3 | 2025-08-06 | 2025-08-21 | +-------------+----------+---------------+------------+------------+ 2 rows in set (0,001 sec) MariaDB [biblioteca]> show tables; +----------------------+ | Tables_in_biblioteca | +----------------------+ | autor | | estudiante | | libro | | prestamo | | prestamos_eliminados | +----------------------+ 5 rows in set (0,001 sec) MariaDB [biblioteca]> select * from prestamos_eliminados; +-------------+----------+---------------+------------+------------+----------------+---------------------+ | id_prestamo | id_libro | id_estudiante | fecha_pres | fecha_devo | usuario | fecha_modif | +-------------+----------+---------------+------------+------------+----------------+---------------------+ | 2 | 4 | 2 | 2025-08-05 | 2025-08-20 | root@localhost | 2025-08-28 23:03:30 | +-------------+----------+---------------+------------+------------+----------------+---------------------+ 1 row in set (0,001 sec) MariaDB [biblioteca]> delete from prestamo where id_prestamo = 3; Query OK, 1 row affected (0,005 sec) MariaDB [biblioteca]> select * from prestamos_eliminados; +-------------+----------+---------------+------------+------------+----------------+---------------------+ | id_prestamo | id_libro | id_estudiante | fecha_pres | fecha_devo | usuario | fecha_modif | +-------------+----------+---------------+------------+------------+----------------+---------------------+ | 2 | 4 | 2 | 2025-08-05 | 2025-08-20 | root@localhost | 2025-08-28 23:03:30 | | 3 | 1 | 3 | 2025-08-06 | 2025-08-21 | root@localhost | 2025-08-28 23:04:33 | +-------------+----------+---------------+------------+------------+----------------+---------------------+ 2 rows in set (0,001 sec) MariaDB [biblioteca]> select * from prestamo; +-------------+----------+---------------+------------+------------+ | id_prestamo | id_libro | id_estudiante | fecha_pres | fecha_devo | +-------------+----------+---------------+------------+------------+ | 1 | 1 | 1 | 2025-08-01 | 2025-08-15 | +-------------+----------+---------------+------------+------------+ 1 row in set (0,001 sec) MariaDB [biblioteca]> note; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'note' at line 1 MariaDB [biblioteca]> notee;