MariaDB [(none)]> create database EmpresaBuses;
Query OK, 1 row affected (0.002 sec)

MariaDB [(none)]> use EmpresaBuses;
Database changed
MariaDB [EmpresaBuses]> create table bus (
    -> id_bus int auto_increment primary key,
    -> placa varchar(10) unique,
    -> modelo varchar(50),
    -> capacidad int,
    -> estado enum('activo', 'en mantenimiento', 'inactivo') default 'activo'
    -> );
Query OK, 0 rows affected (0.064 sec)

MariaDB [EmpresaBuses]> create table conductor (
    -> id_conductor int auto_increment primary key,
    -> nombre varchar(100),
    -> licencia varchar(20) unique,
    -> telefono varchar(15),
    -> fecha_nacimiento date
    -> );
Query OK, 0 rows affected (0.053 sec)

MariaDB [EmpresaBuses]> create table ruta (
    -> id_ruta int auto_increment primary key,
    -> origen varchar(100),
    -> destino varchar(100),
    -> duracion_estimada time
    -> );
Query OK, 0 rows affected (0.021 sec)

MariaDB [EmpresaBuses]> create table viaje (
    -> id_viaje int auto_increment primary key,
    -> id_bus int,
    -> id_conductor int,
    -> id_ruta int,
    -> fecha date,
    -> hora_salida time,
    -> hora_llegada time,
    -> foreign key (id_bus) references bus(id_bus),
    -> foreign key (id_conductor) references conductor(id_conductor),
    -> foreign key (id_ruta) references ruta(id_ruta),
    -> );
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 12
MariaDB [EmpresaBuses]> create table viaje (
    -> id_viaje INT AUTO_INCREMENT PRIMARY KEY,
    -> id_bus INT,
    -> id_conductor INT,
    -> id_ruta INT,
    -> fecha DATE,
    -> hora_salida TIME,
    -> hora_llegada TIME,
    -> FOREIGN KEY (id_bus) REFERENCES bus(id_bus),
    -> FOREIGN KEY (id_conductor) REFERENCES conductor(id_conductor),
    -> FOREIGN KEY (id_ruta) REFERENCES ruta(id_ruta)
    -> );
Query OK, 0 rows affected (0.053 sec)

MariaDB [EmpresaBuses]> insert into bus(placa, modelo, capacidad, estado) values
    -> ('ABC123', 'Mercedes Sprinter', 20, 'Activo'),
    -> ('XYZ789', 'Volkswagen Crafter', 15, 'En Mantenimiento'),
    -> ('LMN456', 'Ford Transit', 18, 'Activo');
Query OK, 3 rows affected (0.076 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [EmpresaBuses]> insert into conductor (nombre, lincencia, telefono, fecha_nacimiento) values
    -> ('Juan P‚rez', 'LIC123456', '3001234567', '1985-06-15'),
    -> ('Marˇa G˘mez', 'LIC789012', '3109876543', '1990-04-20'),
    -> ('Carlos Rodrˇguez', 'LIC345678', '3207654321', '1982-12-10');
ERROR 1054 (42S22): Unknown column 'lincencia' in 'field list'
MariaDB [EmpresaBuses]> insert into conductor (nombre, licencia, telefono, fecha_nacimiento) values
    -> ('Juan P‚rez', 'LIC123456', '3001234567', '1985-06-15'),
    -> ('Marˇa G˘mez', 'LIC789012', '3109876543', '1990-04-20'),
    -> ('Carlos Rodrˇguez', 'LIC345678', '3207654321', '1982-12-10');
Query OK, 3 rows affected (0.012 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [EmpresaBuses]> insert into ruta (origen, destino, duracion_estimada) values
    -> ('Medellˇn', 'Bogot ', '08:00:00'),
    -> ('Cali', 'Pereira', '04:30:00'),
    -> ('Barranquilla', 'Cartagena', '02:15:00');
Query OK, 3 rows affected (0.016 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [EmpresaBuses]> insert into viaje (id_bus, id_conductor, id_ruta, fecha, hora_salida, hora_llegada) values
    -> (1, 1, 1, '2025-04-10', '06:00:00', '14:00:00'),
    -> (2, 2, 2, '2025-04-11', '08:30:00', '13:00:00');
Query OK, 2 rows affected (0.016 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [EmpresaBuses]> DELIMITER //
MariaDB [EmpresaBuses]> create procedure rigistrar_viaje (
    -> in bus_id int,
    -> in conductor_id int,
    -> in ruta_id int,
    -> in fecha_viaje date,
    -> in hora_salida time,
    -> in hora_llegada time
    -> )
    -> begin
    -> insert into viaje (id_bus, id_conductor, id_ruta, fecha, hora_salida, hora_llegada)
    -> values (bus_id, conductor_id, ruta_id, fecha_viaje, hora_salida, hora_llegada);
    -> end //
Query OK, 0 rows affected (0.037 sec)

MariaDB [EmpresaBuses]> DELIMITER;
    -> end //
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 'DELIMITER;end' at line 1
MariaDB [EmpresaBuses]> delimiter;
    -> //
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 'delimiter' at line 1
MariaDB [EmpresaBuses]> delimiter //
MariaDB [EmpresaBuses]> create trigger verificar_viaje_bus
    -> before insert on viaje
    -> for each row
    -> begin
    -> declare bus_ocupado int;
    -> select count(*) into bus_ocupado from viaje where id_bus = new.id_bus and fecha = new.fecha;
    -> if bus_ocupado > 0 then
    -> signal sqlstate '45000' set message_text = 'el bus ya tiene un viaje programado para esta fecha';
    -> end if;
    -> end //
Query OK, 0 rows affected (0.019 sec)

MariaDB [EmpresaBuses]> delimiter ;
MariaDB [EmpresaBuses]> //
    -> end//
    -> end //
    -> delimiter;
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 '//
end//
end //
delimiter' at line 1
MariaDB [EmpresaBuses]> call registrar_viaje (3, 3, 3, '2025-04-12', '07:00:00', '09:15:00');
ERROR 1305 (42000): PROCEDURE empresabuses.registrar_viaje does not exist
MariaDB [EmpresaBuses]> CALL registrar_viaje(3, 3, 3, '2025-04-12', '07:00:00', '09:15:00');
ERROR 1305 (42000): PROCEDURE empresabuses.registrar_viaje does not exist
MariaDB [EmpresaBuses]> INSERT INTO viaje (id_bus, id_conductor, id_ruta, fecha, hora_salida, hora_llegada)
    -> values (1, 2, 2, '2025-04-10', '10:00:00', '15:00:00');
ERROR 1644 (45000): el bus ya tiene un viaje programado para esta fecha
MariaDB [EmpresaBuses]> exit