Ejemplo de Gestión de Vehículos y Mantenimientos en PostgreSQL

Ejemplo : Control de Vehículos y Mantenimientos en PostgreSQL

Pregunta

Se desea diseñar una Base de Datos para llevar un cierto control de los vehículos de una empresa de transporte y sus mantenimientos.

Los datos significativos a tener en cuenta son:

  • Un vehículo se define por su código único, matrícula, marca y año de fabricación.
  • Los mantenimientos tienen un código de mantenimiento, tipo de mantenimiento y fecha de realización.
  • Un vehículo puede recibir varios mantenimientos, y un mantenimiento puede aplicarse a varios vehículos, siendo significativa la fecha de realización y el costo del mantenimiento.

Las preguntas a resolver son las siguientes:

  • A) Crear el diagrama SQL de las tablas.
  • B) Insertar y visualizar datos.
  • C) Visualizar datos de diferentes tablas (SELECT con INNER JOIN, WHERE).
  • D) Modificar todos los registros en una tabla y un registro específico.
  • E) Modificar un registro y eliminar registros de una tabla.

Descripción

Para implementar esta gestión de vehículos y mantenimientos, configuraremos una base de datos que permite:

  • Almacenar los datos de cada vehículo, como código único, matrícula, marca y año de fabricación.
  • Registrar los datos de cada mantenimiento, incluyendo el tipo y fecha de realización.
  • Asociar múltiples mantenimientos a un vehículo, y viceversa, registrando la fecha y el costo de cada mantenimiento.

A) Crear el Diagrama SQL (Tablas y Relaciones)

Para modelar esta relación muchos a muchos entre vehículos y mantenimientos, crearemos tres tablas:

  • Vehiculo: almacena información de cada vehículo.
  • Mantenimiento: almacena información de cada mantenimiento.
  • RegistroMantenimiento: una tabla de relación que asocia vehículos con mantenimientos, incluyendo información adicional como la fecha y el costo del mantenimiento.

-- Crear la tabla Vehiculo
CREATE TABLE Vehiculo (
    id_vehiculo SERIAL PRIMARY KEY,
    matricula VARCHAR(10) UNIQUE NOT NULL,
    marca VARCHAR(50) NOT NULL,
    ano INT NOT NULL CHECK (ano > 1900)
);

-- Crear la tabla Mantenimiento
CREATE TABLE Mantenimiento (
    id_mantenimiento SERIAL PRIMARY KEY,
    tipo_mantenimiento VARCHAR(100) NOT NULL,
    descripcion TEXT,
    fecha DATE NOT NULL
);

-- Crear la tabla RegistroMantenimiento (relación muchos a muchos)
CREATE TABLE RegistroMantenimiento (
    id_vehiculo INT REFERENCES Vehiculo(id_vehiculo),
    id_mantenimiento INT REFERENCES Mantenimiento(id_mantenimiento),
    fecha_realizacion DATE DEFAULT CURRENT_DATE,
    costo NUMERIC(10, 2),
    PRIMARY KEY (id_vehiculo, id_mantenimiento, fecha_realizacion)
);

B) Insertar y Visualizar Datos

Insertamos algunos datos de ejemplo para vehículos, mantenimientos y registros en RegistroMantenimiento, que muestra los mantenimientos realizados a cada vehículo.


-- Insertar datos en Vehiculo
INSERT INTO Vehiculo (matricula, marca, ano)
VALUES
    ('ABC123', 'Toyota', 2015),
    ('XYZ789', 'Ford', 2018),
    ('LMN456', 'Honda', 2020);

-- Insertar datos en Mantenimiento
INSERT INTO Mantenimiento (tipo_mantenimiento, descripcion, fecha)
VALUES
    ('Cambio de aceite', 'Cambio de aceite de motor', '2024-03-01'),
    ('Revisión general', 'Revisión completa del vehículo', '2024-03-10'),
    ('Cambio de frenos', 'Sustitución de pastillas de freno', '2024-04-05');

-- Registrar mantenimientos en la tabla RegistroMantenimiento
INSERT INTO RegistroMantenimiento (id_vehiculo, id_mantenimiento, fecha_realizacion, costo)
VALUES
    (1, 1, '2024-03-02', 50.00),  -- Toyota recibe Cambio de aceite
    (1, 2, '2024-03-11', 150.00), -- Toyota recibe Revisión general
    (2, 1, '2024-03-15', 60.00),  -- Ford recibe Cambio de aceite
    (3, 3, '2024-04-06', 200.00); -- Honda recibe Cambio de frenos

C) Visualizar Datos de Diferentes Tablas (INNER JOIN y WHERE)

Para ver qué mantenimientos ha recibido cada vehículo, hacemos una consulta con INNER JOIN entre las tablas Vehiculo, Mantenimiento, y RegistroMantenimiento:


SELECT 
    v.matricula AS vehiculo_matricula,
    v.marca AS marca_vehiculo,
    m.tipo_mantenimiento,
    r.fecha_realizacion,
    r.costo
FROM 
    RegistroMantenimiento r
JOIN 
    Vehiculo v ON r.id_vehiculo = v.id_vehiculo
JOIN 
    Mantenimiento m ON r.id_mantenimiento = m.id_mantenimiento
ORDER BY 
    v.matricula, r.fecha_realizacion;

D) Modificar Todos los Registros en una Tabla y un Registro Específico

Realizamos actualizaciones en los datos de las tablas:

1. Modificar todos los registros en una tabla

Ejemplo: Incrementar el costo de todos los mantenimientos en un 10%:


UPDATE RegistroMantenimiento
SET costo = costo * 1.1;

2. Modificar un registro específico

Ejemplo: Cambiar la marca de un vehículo específico:


UPDATE Vehiculo
SET marca = 'Toyota Motors'
WHERE matricula = 'ABC123';

E) Modificar un Registro y Eliminar Registros de una Tabla

1. Modificar un registro

Cambiar la descripción de un mantenimiento específico:


UPDATE Mantenimiento
SET descripcion = 'Revisión completa con diagnóstico'
WHERE tipo_mantenimiento = 'Revisión general';

2. Eliminar registros

Eliminar todos los registros de mantenimiento para un vehículo específico:


DELETE FROM RegistroMantenimiento
WHERE id_vehiculo = 1;

Resumen

Con estas consultas y el diseño de la base de datos, hemos cubierto las operaciones necesarias para gestionar la relación entre vehículos y mantenimientos en PostgreSQL, incluyendo inserciones, consultas con JOIN, y modificaciones.