Resumen del Proyecto de Gestión de Flota de Vehículo.

Este proyecto se centra en la gestión eficiente de la flota de vehículos. Mediante la implementación de un sistema de base de datos en PostgreSQL, se busca tener un control completo sobre diversos aspectos de la flota, como modelos de vehículos, marcas, grupos empresariales y más. Además, se abarcan detalles como el color del coche, matrícula, kilómetros recorridos y datos de la aseguradora. Un aspecto clave es el seguimiento de las revisiones de cada vehículo, incluyendo la fecha, kilómetros al momento y costos. El proyecto incluye un script SQL autónomo para crear y poblar la base de datos.

Índice

Resumen del Proyecto de Gestión de Flota de Vehículo
- Componentes Clave
Gestión de Flota de Vehículos
Adaptación del Modelo Entidad-Relación y SQL
- Entidades y Atributos
- Relaciones
Script SQL Adaptado
- Creación del Esquema
- Creación de Tablas
Inserción de Datos de Muestra
Consulta SQL Profesional para Listar Coches Activos
- Detalles de la Consulta
- Código SQL
Pantalla Final

Componentes Clave

Modelo Entidad-Relación (ER)
Identificación de entidades, relaciones y atributos relevantes.
Script SQL Autónomo
Creación de esquemas, tablas y relaciones entre ellas.
Consulta SQL
Listado de coches activos con diversos detalles.

Gestión de Flota de Vehículos.

La tarea tiene varios componentes clave que se deben abordar para una solución completa. Estos componentes son:

1. Modelo Entidad-Relación (ER)

El primer paso es crear un modelo Entidad-Relación (ER) normalizado. Este modelo servirá como la estructura subyacente para la base de datos. Se deben identificar las entidades relevantes, como Coche, Marca, Grupo y Aseguradora, así como sus relaciones y atributos.

2. Script SQL Autónomo

El segundo componente es un script SQL que es 100% autónomo. Esto significa que el script, cuando se ejecuta, debe:

Crear todas las tablas necesarias según el modelo ER.
Definir las claves primarias y claves foráneas para establecer las relaciones entre las tablas.
Insertar algunos datos de muestra en las tablas.

Este script debe ser ejecutable en una base de datos PostgreSQL y debe crear un nuevo esquema (también conocido como espacio de trabajo) para alojar todas las tablas. No se debe utilizar el esquema public por defecto.

3. Consulta SQL

El tercer componente es una consulta SQL que pueda listar todos los coches activos en KeepCoding. Esta consulta debería ser capaz de mostrar:

Modelo del coche
Marca del coche
Grupo al que pertenece la marca
Fecha de compra del coche
Matrícula del coche
Color del coche
Kilometraje total del coche
Nombre de la empresa aseguradora
Número de póliza de seguro

Adaptación del Modelo Entidad-Relación y SQL.

Esta tarea implica la creación de un modelo Entidad-Relación (ER) y un script SQL que se ajusten a los requisitos específicos de la tarea. A continuación, se describen los componentes clave.

Modelo Entidad-Relación Descriptivo para la Tarea

Entidades y Atributos

1. Grupo

ID_Grupo (PK)
Nombre_Grupo

2. Marca

ID_Marca (PK)
Nombre_Marca
ID_Grupo (FK)

3. Modelo

ID_Modelo (PK)
Nombre_Modelo
ID_Marca (FK)

4. Aseguradora

ID_Aseguradora (PK)
Nombre_Aseguradora

5. Divisa

ID_Divisa (PK)
Nombre_Divisa

6. Color

ID_Color (PK)
Nombre_Color

7. Coche

ID_Coche (PK)
ID_Modelo (FK)
ID_Color (FK)
Matricula
Kilometros
ID_Aseguradora (FK)
Numero_Poliza
Fecha_Compra

8. Revision

ID_Revision (PK)
ID_Coche (FK)
Kilometros_Revision
Fecha_Revision
Importe
ID_Divisa (FK)

9. Poliza

ID_Poliza (PK)
ID_Aseguradora (FK)
Tipo_Poliza
Fecha_Inicio
Fecha_Expiracion
Cobertura

Relaciones

Marca - Grupo: Una Marca pertenece a un Grupo, y un Grupo puede tener múltiples Marcas. (1:N)
Modelo - Marca: Un Modelo pertenece a una Marca, y una Marca puede tener múltiples Modelos. (1:N)
Coche - Modelo: Un Coche tiene un Modelo, y un Modelo puede estar asociado con múltiples Coches. (1:N)
Coche - Color: Un Coche tiene un Color, y un Color puede estar asociado con múltiples Coches. (1:N)
Coche - Aseguradora: Un Coche está asegurado por una Aseguradora, y una Aseguradora puede asegurar múltiples Coches. (1:N)
Revision - Coche: Una Revisión se realiza en un Coche, y un Coche puede tener múltiples Revisiones. (1:N)
Revision - Divisa: Una Revisión está en una Divisa específica (EURO, DÓLAR, etc.). (1:1)
Poliza - Aseguradora: Una Póliza está asociada con una única Aseguradora, y una Aseguradora puede emitir múltiples Pólizas. (1:N)

Modelo Entidad-Relación Descriptivo para la tabla Poliza

Entidades y Atributos:

Poliza

ID_Poliza: Identificador único para cada póliza de seguro. (Clave Primaria)
ID_Aseguradora: Identificador de la aseguradora que emite la póliza. (Clave Externa)
Tipo_Poliza: Tipo de la póliza de seguro, como "Responsabilidad Civil", "Todo Riesgo", etc.
Fecha_Inicio: Fecha en la cual la póliza de seguro entra en vigencia.
Fecha_Expiracion: Fecha en la cual la póliza de seguro expira.
Cobertura: Texto que describe la cobertura ofrecida por la póliza, como daño a terceros, robo, etc.

Relaciones:

Poliza - Aseguradora

Una póliza está asociada con una única aseguradora.
Una aseguradora puede emitir múltiples pólizas.
ID_Aseguradora en la tabla Poliza es una clave externa que referencia ID_Aseguradora en la tabla Aseguradora.

Restricciones:

ID_Poliza es la clave primaria y debe ser única.
ID_Aseguradora debe existir en la tabla Aseguradora.
Tipo_Poliza no puede ser nulo.
Fecha_Inicio y Fecha_Expiracion pueden ser opcionales dependiendo de la lógica del negocio.
Cobertura es un campo de texto y puede contener información detallada sobre la cobertura de la póliza.

Script SQL Adaptado

El segundo componente es un script SQL que es 100% autónomo. Este script debe:

Crear todas las tablas necesarias según el modelo ER.
Definir las claves primarias y claves foráneas para establecer las relaciones entre las tablas.
Aquí el enlace al script SQL

Consulta SQL para Obtener la Lista de Coches Activos

La consulta SQL al final del script tiene el propósito de obtener una lista de coches activos en KeepCoding, mostrando varios detalles de cada coche. A continuación, se explica cada parte de la consulta.

Script SQL para la Gestión de la Flota de Coches

El siguiente script SQL crea un nuevo esquema y varias tablas en PostgreSQL para gestionar una flota de coches. Además, incluye una consulta para obtener una lista de coches activos. A continuación, se detalla cada sección del script.

Creación del Esquema

-- Crear un nuevo esquema llamado KeepCoding
CREATE SCHEMA IF NOT EXISTS KeepCoding;

-- Cambiar al nuevo esquema
SET search_path TO KeepCoding;

Esta sección crea un nuevo esquema llamado KeepCoding y cambia el esquema de búsqueda al recién creado.

Creación de Tablas

Tabla Grupo

-- Crear la tabla Grupo
CREATE TABLE Grupo (
    ID_Grupo SERIAL PRIMARY KEY,
    Nombre_Grupo VARCHAR(50) NOT NULL
);

Esta tabla guarda la información sobre los diferentes grupos empresariales a los que pertenecen las marcas de coches.

Tabla Marca

-- Crear la tabla Marca
CREATE TABLE Marca (
    ID_Marca SERIAL PRIMARY KEY,
    Nombre_Marca VARCHAR(50) NOT NULL,
    ID_Grupo INT REFERENCES Grupo(ID_Grupo)
);

Tabla Modelo

-- Crear la tabla Modelo
CREATE TABLE Modelo (
    ID_Modelo SERIAL PRIMARY KEY,
    Nombre_Modelo VARCHAR(50) NOT NULL,
    ID_Marca INT REFERENCES Marca(ID_Marca)
);

Tabla Aseguradora

-- Crear la tabla Aseguradora
CREATE TABLE Aseguradora (
    ID_Aseguradora SERIAL PRIMARY KEY,
    Nombre_Aseguradora VARCHAR(50) NOT NULL
);

Tabla Poliza

-- Create Poliza table
CREATE TABLE IF NOT EXISTS Poliza (
    ID_Poliza varchar(50) PRIMARY KEY,
    ID_Aseguradora INT REFERENCES Aseguradora(ID_Aseguradora),
    Tipo_Poliza VARCHAR(50) NOT NULL,
    Fecha_Inicio DATE,
    Fecha_Expiracion DATE,
    Cobertura TEXT
);

Tabla Divisa

-- Crear la tabla Divisa
CREATE TABLE Divisa (
    ID_Divisa SERIAL PRIMARY KEY,
    Nombre_Divisa VARCHAR(10) NOT NULL
);

Tabla Color

-- Crear la tabla Color
CREATE TABLE Color (
    ID_Color SERIAL PRIMARY KEY,
    Nombre_Color VARCHAR(20) NOT NULL
);

Tabla Coche

-- Crear la tabla Coche
CREATE TABLE IF NOT EXISTS Coche (
    ID_Coche SERIAL PRIMARY KEY,
    ID_Modelo INT REFERENCES Modelo(ID_Modelo),
    ID_Color INT REFERENCES Color(ID_Color),
    Matricula VARCHAR(50) NOT NULL,
    Kilometros INT,
    id_poliza varchar(50) references poliza(id_poliza),
    Fecha_Compra DATE
);

Tabla Revision

-- Crear la tabla Revision
CREATE TABLE Revision (
    ID_Revision SERIAL PRIMARY KEY,
    ID_Coche INT REFERENCES Coche(ID_Coche),
    Kilometros_Revision INT,
    Fecha_Revision DATE,
    Importe FLOAT,
    ID_Divisa INT REFERENCES Divisa(ID_Divisa)
);

Inserción de Datos de Muestra

Esta sección detalla cómo insertar datos de muestra en las tablas para facilitar las pruebas y la validación del modelo de base de datos.

Insertar Datos en la Tabla Grupo Insertaremos 5 registros en la tabla Grupo. Cada grupo podría representar una corporación o conjunto de marcas de coches.

-- Insertar 5 registros en Grupo
INSERT INTO Grupo (Nombre_Grupo) VALUES
   ('Grupo A'),
   ('Grupo B'),
   ('Grupo C'),
   ('Grupo D'),
   ('Grupo E');

Insertar Datos en la Tabla Marca Insertaremos 10 registros en la tabla Marca. Cada marca estará asociada con un grupo específico.

-- Insertar 10 registros en Marca
INSERT INTO Marca (Nombre_Marca, ID_Grupo) VALUES
   ('Toyota', 1),
   ('Honda', 2),
   ('Ford', 3),
   ('Chevrolet', 4),
   ('Nissan', 5),
   ('Volkswagen', 1),
   ('Hyundai', 2),
   ('Subaru', 3),
   ('Kia', 4),
   ('Jeep', 5);

Insertar Datos en la Tabla Modelo Insertaremos 10 modelos de coches, cada uno asociado con una marca en específico.

-- Insertar 10 registros en Modelo
INSERT INTO Modelo (Nombre_Modelo, ID_Marca) VALUES
   ('Modelo 1', 1),
   ('Modelo 2', 2),
   ('Modelo 3', 3),
   ('Modelo 4', 4),
   ('Modelo 5', 5),
   ('Modelo 6', 6),
   ('Modelo 7', 7),
   ('Modelo 8', 8),
   ('Modelo 9', 9),
   ('Modelo 10', 10);

Insertar Datos en la Tabla Aseguradora Insertaremos 10 registros en la tabla Aseguradora, representando diferentes compañías de seguros.

-- Insertar 10 registros en Aseguradora
INSERT INTO Aseguradora (Nombre_Aseguradora) VALUES
   ('CoberturaTotal'),
   ('Seguros XYZ'),
   ('SeguroFácil'),
   ('AseguraTodo'),
   ('ProtegeYa'),
   ('SeguroMax'),
   ('Confiaseguro'),
   ('SeguroClaro'),
   ('AseguraYA'),
   ('SeguroTOP');

-- Insertar registros en Poliza (Asegúrate de que estos se inserten antes de la tabla Coche)
INSERT INTO Poliza (id_poliza, ID_Aseguradora, Tipo_Poliza, Fecha_Inicio, Fecha_Expiracion, Cobertura) VALUES  
    ('POL001', 1, 'Responsabilidad Civil', '2021-01-01', '2022-01-01', 'Daño a terceros hasta $10,000'),
    ('POL002', 2, 'Todo Riesgo', '2021-02-01', '2022-02-01', 'Cobertura completa, incluido robo y daño natural'),
    ('POL003', 3, 'Responsabilidad Civil', '2021-03-01', '2022-03-01', 'Daño a terceros hasta $15,000'),
    ('POL004', 4, 'Todo Riesgo', '2021-04-01', '2022-04-01', 'Cobertura completa, incluido robo y daño natural'),
    ('POL005', 5, 'Responsabilidad Civil', '2021-05-01', '2022-05-01', 'Daño a terceros hasta $5,000');

Insertar Datos en la Tabla Divisa Insertaremos 7 registros de diferentes divisas.

-- Insertar 7 registros en Divisa
INSERT INTO Divisa (Nombre_Divisa) VALUES
   ('EURO'),
   ('DOLLAR'),
   ('YEN'),
   ('POUND'),
   ('RUPEE'),
   ('YUAN'),
   ('PESO');

Insertar registros en la tabla divisas

-- Insert registros color
INSERT INTO divisa (nombre_divisa, id_divisa) VALUES
    ('UDS', 1), 
    ('EURO', 2);

Insertar Datos en la Tabla Color Insertaremos 10 registros de diferentes colores que pueden tener los coches.

-- Insert registros color
INSERT INTO color (nombre_color, id_color) VALUES
    ('Azul', 1), 
    ('Negro', 2), 
    ('Blanco', 3), 
    ('Amarillo', 4), 
    ('Verde', 5),
    ('Cafe', 6), 
    ('Rojo', 7);

Detalle Avanzado de la Consulta Principal

La consulta principal está diseñada para reunir datos de múltiples tablas en una sola vista. A continuación, se desglosan los componentes clave de la consulta SQL, permitiendo una comprensión completa de su funcionalidad.

Consulta Principal Detallada

SQL Code

-- Consulta SQL Profesional para obtener el listado completo de coches activos
SELECT 
    co.ID_Coche AS "ID del Coche", 
    mo.Nombre_Modelo AS "Modelo del Coche", 
    ma.Nombre_Marca AS "Marca del Coche", 
    g.Nombre_Grupo AS "Grupo Empresarial",
    TO_CHAR(co.Fecha_Compra, 'YYYY-MM-DD') AS "Fecha de Compra",
    co.Matricula AS "Matrícula",
    col.Nombre_Color AS "Color del Coche",
    co.Kilometros AS "Kilómetros Recorridos",
    a.Nombre_Aseguradora AS "Aseguradora",
    co.id_poliza AS "Número de Póliza",
    p.Tipo_Poliza AS "Tipo de Póliza",
    TO_CHAR(p.Fecha_Inicio, 'YYYY-MM-DD') AS "Fecha de Inicio de Póliza",
    TO_CHAR(p.Fecha_Expiracion, 'YYYY-MM-DD') AS "Fecha de Expiración de Póliza",
    (SELECT Nombre_Divisa FROM Divisa WHERE ID_Divisa = (SELECT ID_Divisa FROM Revision WHERE ID_Coche = co.ID_Coche LIMIT 1)) AS "Divisa de Pago del Servicio",
    (SELECT Importe FROM Revision WHERE ID_Coche = co.ID_Coche LIMIT 1) AS "Costo de Revisión"
FROM 
    Coche AS co
JOIN 
    Modelo AS mo ON co.ID_Modelo = mo.ID_Modelo
JOIN 
    Marca AS ma ON mo.ID_Marca = ma.ID_Marca
JOIN 
    Grupo AS g ON ma.ID_Grupo = g.ID_Grupo
JOIN 
    Color AS col ON co.ID_Color = col.ID_Color
JOIN 
    Poliza AS p ON co.id_poliza = p.id_poliza    
JOIN 
    Aseguradora AS a ON a.id_aseguradora = p.id_aseguradora
ORDER BY 
    "Marca del Coche", 
    "Modelo del Coche", 
    "Fecha de Compra" ASC;

Detalles de los Joins

Coche AS co JOIN Modelo AS mo: Se une la tabla Coche con la tabla Modelo a través del campo ID_Modelo.
Modelo AS mo JOIN Marca AS ma: Se une la tabla Modelo con la tabla Marca a través del campo ID_Marca.
Marca AS ma JOIN Grupo AS g: Se une la tabla Marca con la tabla Grupo a través del campo ID_Grupo.
Coche AS co JOIN Color AS col: Se une la tabla Coche con la tabla Color a través del campo ID_Color.
Coche AS co JOIN Aseguradora AS a: Se une la tabla Coche con la tabla Aseguradora a través del campo ID_Aseguradora.
Aseguradora AS a JOIN Poliza AS p: Se une la tabla Aseguradora con la tabla Poliza a través del campo ID_Aseguradora.

Detalles de las Subconsultas

Subconsulta para Divisa de Pago del Servicio: Utiliza una subconsulta para obtener la divisa utilizada para la revisión del coche. La subconsulta encuentra el ID_Divisa correspondiente al ID_Coche en la tabla Revision y luego utiliza este ID_Divisa para encontrar el Nombre_Divisa en la tabla Divisa.
Subconsulta para Costo de Revisión: Utiliza una subconsulta para encontrar el costo de la última revisión realizada al coche. La subconsulta busca el campo Importe en la tabla Revision donde el ID_Coche coincide.

Ordenación de los Resultados

Los resultados se ordenan por "Marca del Coche", seguido de "Modelo del Coche" y, finalmente, por "Fecha de Compra" en orden ascendente.

Name		Name	Last commit message	Last commit date
Latest commit History 108 Commits
Aseguradora.drawio		Aseguradora.drawio
EER_diagram.png		EER_diagram.png
LICENSE		LICENSE
README.md		README.md
keepcoding_flota.sql		keepcoding_flota.sql
pantalla_final.png		pantalla_final.png

License

ferkuellar/Modelado-SQL

Folders and files

Latest commit

History

Repository files navigation