# Proyecto circuito de motos

# Modelo E/R con Normalizacion


# Diagrama de el programa en dia(To do)


# Base de datos en SQL

drop database if exists circuito;
create database circuito;
USE circuito
create table eventos(
    cod_evento int primary KEY AUTO_INCREMENT,
    fecha_inicio datetime,
    fecha_fin datetime,
    categoria varchar(25),
    coste_participante dec(4,2),
    coste_espectadores dec(4,2)
);

create table clientes(
    cod_cliente int primary KEY AUTO_INCREMENT,
    nombre varchar(20),
    apellidos varchar(50),
    pago DECIMAL(5,2),
    email varchar(70),
    contrasena varchar(30),
    tipo_cliente varchar(10)
);

create table trabajadores(
    cod_trabajador int primary KEY AUTO_INCREMENT,
    nombre varchar(20),
    apellidos varchar(20),
    salario int,
    puesto varchar(20)
);

create table equipos(
    cod_equipo int primary KEY AUTO_INCREMENT,
    nombre varchar(20)
);

create table moto(
    cod_moto int primary KEY AUTO_INCREMENT,
    nombre varchar(20),
    tipo_moto varchar(20) not null,
    cilindrada varchar(8)
);


create table miembros_equipo(
    cod_miembro int primary KEY AUTO_INCREMENT,
    cod_equipo int not null,
    nombre varchar(20),
    apellidos varchar(30),
    ocupacion varchar(25),
    FOREIGN KEY (cod_equipo) REFERENCES equipos(cod_equipo)
);

create table espectador(
    cod_espectador int primary KEY AUTO_INCREMENT,
    cod_cliente int,
    miembro boolean default False,
    FOREIGN KEY (cod_cliente) REFERENCES clientes(cod_cliente)
);

create table espectadoresyeventos(
    cod_espectador int,
    cod_evento int,
    primary KEY (cod_espectador, cod_evento),
    FOREIGN KEY (cod_espectador) REFERENCES espectador(cod_espectador),
    FOREIGN KEY (cod_evento) REFERENCES eventos(cod_evento)
);


create table cliente_piloto_moto(
    cod_cliente int,
    cod_moto int,
    primary KEY(cod_moto, cod_cliente),
    FOREIGN KEY (cod_cliente) REFERENCES clientes(cod_cliente),
    FOREIGN KEY (cod_moto) REFERENCES moto(cod_moto)
);

create table miembros_equipo_piloto_moto(
    cod_miembro int,
    cod_moto int,
    primary KEY(cod_moto, cod_miembro),
    FOREIGN KEY (cod_miembro) REFERENCES miembros_equipo(cod_miembro),
    FOREIGN KEY (cod_moto) REFERENCES moto(cod_moto)
);

create table cliente_piloto_evento(
    cod_cliente int,
    cod_evento int,
    primary KEY(cod_cliente, cod_evento),
    FOREIGN KEY (cod_cliente) REFERENCES clientes(cod_cliente),
    FOREIGN KEY (cod_evento) REFERENCES eventos(cod_evento)
);

create table equipo_evento(
    cod_equipo int,
    cod_evento int,
    primary KEY(cod_equipo, cod_evento),
    FOREIGN KEY (cod_equipo) REFERENCES equipos(cod_equipo),
    FOREIGN KEY (cod_evento) REFERENCES eventos(cod_evento)
);

insert into eventos(fecha_inicio,fecha_fin,categoria,coste_participante,coste_espectadores) values
("2022-01-04 09:30","2022-01-05 21:00","amateur",15.00,1.00),
("2022-04-01 13:30","2022-04-04 20:00","profesional",40.00,10.00),
("2022-06-04 09:30","2022-06-06 21:00","amateur",15.00,1.00),
("2022-06-01 10:30","2022-06-04 21:00","profesional",80.00,30.00),
("2022-07-10 10:00","2022-07-12 20:00","profesional",40.00,10.00),
("2022-10-13 09:30","2022-10-17 21:00","profesional",80.00,30.00),
("2022-11-15 11:30","2022-11-16 19:00","amateur",15.00,1.00);

insert into clientes(nombre, apellidos, pago, email, contrasena, tipo_cliente) values
("Alfredo", "Molina", 15, "[e-mail]","Sinfrenosmejor1", "Piloto"),
("John", "Zamora", 10, "[e-mail]", "Terminator99laparte", "Espectador"),
("Sara", "Espinosa", 1,"[e-mail]", "6NoquedaParaEmpanadas9", "Espectador"),
("Marta", "Alvez", 80, "[e-mail]", "23012001MG", "Piloto"),
("Marcos", "Santana", 80,"[e-mail]", "467175bt26fewsjV", "Piloto"),
("Nuria", "Sanchez", 30,"[e-mail]", "NuriasinNoria223", "Espectador");

insert into trabajadores(nombre, apellidos, salario, puesto) values
("Alberto", "Salazar", 950, "Tendero"),
("Cristina", "Albez", 950, "Tendero"),
("Pablo", "Dominguez", 800, "Limpiador"),
("Yuleima", "Taisma", 800, "Limpiador"),
("Lola", "Segura", 1000, "Mecanico"),
("Paula", "Araya", 1000, "Mecanico"),
("Roberto", "Santana", 1000, "Mecanico"),
("Yerai", "Espinosa", 1200, "Ingeniero");

insert into equipos(nombre) values
("VinDieselFC"),
("HotWheels"),
("RayoMcTeam"),
("MotoBrothers");

insert into moto(nombre, tipo_moto, cilindrada) values
("Ducati panigale v4", "amateur", 1103),
("Suzuki gsx 128", "amateur", 124),
("Kawasaki z900", "amateur", 948),
("Honda CBR650R", "amateur", 649);

insert into miembros_equipo(cod_equipo, nombre, apellidos, ocupacion) values
(1,"Maria", "Garcia", "Piloto"),
(1,"Jerico", "Santana", "Piloto"),
(2,"Shakira", "Gonzalez", "Piloto"),
(2,"Adrian", "Henriquez", "Piloto"),
(4,"Sebastian", "Ramirez", "Mecanico"),
(1,"Raul", "Heredia", "Jefe de equipo"),
(3,"Sofia", "Collantes", "Piloto"),
(3,"Adrian", "Mejias", "Piloto"),
(3,"Paco", "Lopez", "Ingeniero"),
(3,"Adam", "Morales", "Jefe de equipo"),
(4,"Peter", "Parker", "Jefe de equipo"),
(2,"Kiko", "Rivera", "Jefe de equipo"),
(4,"Kike", "Montilla", "Piloto"),
(4,"Daniel", "Sanchez", "Piloto"),
(1,"Berta", "Robinson", "Mecanico");

insert into espectador (cod_cliente,miembro) values
(2,1),
(6,0),
(3,0);

insert into cliente_piloto_moto(cod_cliente, cod_moto) values
(2,1),
(4,2),
(6,3);

insert into miembros_equipo_piloto_moto(cod_miembro, cod_moto) values
(1,1),
(5,2),
(3,3);

insert into espectadoresyeventos(cod_espectador, cod_evento) values
(1,2),
(1,4),
(1,5),
(1,6),
(2,2),
(2,5),
(3,4),
(3,7);

insert into cliente_piloto_evento values
(1,7),
(4,3),
(5,1);

insert into equipo_evento values
(1,5),
(2,4),
(3,2),
(4,6);

### Consultas simples

1
--Consulta simple para ver los detalles de los miembros del equipo 1
select cod_miembro, nombre, apellidos, ocupacion from miembros_equipo where cod_equipo = 1;

2
--Consulta simple para ver las motos que poseen una cilindrada superior a 800:
select * from moto where cilindrada > 800

3
--Consulta simple para seleccionar el nombre y apellidos de los mecánicos:
select concat(nombre," ",apellidos) from trabajadores where puesto = "Mecanico";

4
--Consulta simple para ver los emails de los espectadores:
select email from clientes where tipo_cliente = "Espectador";

5
--Consulta simple para ver los datos de todos los eventos profesionales:
select * from eventos where categoria = "profesional";

### Inner Joins

1
--INNER JOIN PARA VER QUÉ MOTO USA CADA PILOTO AMATEUR--
SELECT clientes.nombre AS "Piloto", moto.nombre AS "Nombre Moto", moto.tipo_moto AS "Tipo de Moto", moto.cilindrada AS "Cilindrada" FROM moto
INNER JOIN cliente_piloto_moto 
ON moto.cod_moto = cliente_piloto_moto.cod_moto
INNER JOIN clientes
ON cliente_piloto_moto.cod_cliente = clientes.cod_cliente;

2
--INNER JOIN PARA VER LOS DATOS DE LOS EVENTOS PROFESIONALES (pues los equipos solo participan en eventos presionales)--
SELECT equipos.nombre AS "Nombre del equipo", eventos.cod_evento AS "Evento", eventos.fecha_inicio AS "Fecha de inicio", eventos.fecha_fin AS "Fecha de finalizacion", eventos.coste_espectadores AS "Precio de la entrada"
FROM equipos
INNER JOIN equipo_evento
ON equipos.cod_equipo = equipo_evento.cod_equipo
INNER JOIN eventos
ON equipo_evento.cod_evento = eventos.cod_evento;

3
--INNER JOIN PARA VER A QUE EVENTOS VA CADA ESPECTADOR--
SELECT clientes.nombre AS "Espectador", eventos.categoria AS "Categoria", eventos.cod_evento AS "Codigo de Evento"
FROM eventos
INNER JOIN espectadoresyeventos
ON eventos.cod_evento = espectadoresyeventos.cod_evento
INNER JOIN espectador
ON espectadoresyeventos.cod_espectador = espectador.cod_espectador
INNER JOIN clientes
ON espectador.cod_cliente = clientes.cod_cliente
order by eventos.categoria;

4
--INNER JOIN PARA VER LOS PILOTOS QUE SON MIEMBROS DE UN EQUIPO UN EQUIPO--
SELECT miembros_equipo.nombre AS "Piloto", equipos.nombre AS "Equipo"
FROM miembros_equipo
INNER JOIN equipos
ON miembros_equipo.cod_equipo = equipos.cod_equipo
WHERE miembros_equipo.ocupacion = "Piloto";

5
--INNER JOIN PARA VER LOS PARTICIPANTES DE LOS EVENTOS AMATEUR--
SELECT concat(clientes.nombre, " ", clientes.apellidos) AS "Piloto", eventos.cod_evento AS "Codigo de Evento", eventos.fecha_inicio AS "Fecha de inicio", eventos.fecha_fin AS "Fecha de finalizacion", eventos.coste_participante AS "Precio para participar"
FROM eventos
INNER JOIN cliente_piloto_evento
ON eventos.cod_evento = cliente_piloto_evento.cod_evento
INNER JOIN clientes
ON cliente_piloto_evento.cod_cliente = clientes.cod_cliente;

--function--

-- Función para comprobar el número de motos disponibles de un tipo de moto.
DROP FUNCTION IF EXISTS cantidad_motos_categoria;
DELIMITER $$
CREATE FUNCTION cantidad_motos_categoria(categoria_comprobar varchar(20))
returns varchar(100)
BEGIN
    DECLARE sum_motos int;
    SELECT count(moto.tipo_moto) into sum_motos from moto WHERE moto.tipo_moto = categoria_comprobar;
    if sum_motos = 1 then
        RETURN CONCAT("De la categroria ", categoria_comprobar, " se dispone de ", sum_motos, " moto.");
    elseif sum_motos < 1 then
        RETURN CONCAT("De la categroria ", categoria_comprobar, " no hay vehiculos disponibles.");
    else
    RETURN CONCAT("De la categroria ", categoria_comprobar, " se disponen de ", sum_motos, " motos.");
    end if;
END $$
DELIMITER ;


select cantidad_motos_categoria("amateur");
select cantidad_motos_categoria("profesional");

--procedure--

--Procedure para añadir una moto

DROP PROCEDURE IF EXISTS anadir_moto;
DELIMITER $$
CREATE PROCEDURE anadir_moto(nombre VARCHAR(20), tipo_moto VARCHAR(20), cilindrada int(8))
BEGIN
    INSERT INTO moto(nombre, tipo_moto, cilindrada) VALUES
    (nombre, tipo_moto, cilindrada);
END $$
DELIMITER ;

CALL anadir_moto("Toyota", "amateur",100);

--trigger--

--Establece un máximo y mínimo de cilindrada antes de realizar un insert

DROP TRIGGER IF EXISTS limite_cilindrada;
DELIMITER $$
CREATE TRIGGER limite_cilindrada
BEFORE INSERT
ON moto
FOR EACH ROW
BEGIN
    declare error condition for sqlstate "45000";
    if new.cilindrada > 1500 then
        signal error set MESSAGE_TEXT = "La cilindrada maxima permitida es de 1500cc";
    elseif new.cilindrada < 120 then
        signal error set MESSAGE_TEXT = "La cilindrada minima permitida es de 120cc";
    END IF;
END $$
DELIMITER ;

INSERT INTO moto(nombre, tipo_moto, cilindrada) VALUES
    ("KOE", "amateur", 1700);
    
INSERT INTO moto(nombre, tipo_moto, cilindrada) VALUES
    ("CCE", "amateur", 100);