Trabajo Práctico – Base de Datos II

A continuación se presentan las instrucciones y el script de creación de la base de datos. Resuelva cada uno de los seis ejercicios implementando los objetos solicitados (funciones definidas por el usuario, vistas y procedimientos almacenados) utilizando exactamente los nombres indicados en cada consigna. Respete el manejo de errores y la auditoría especificada.

Instrucción general sobre manejo de errores: En todos los procedimientos que utilicen RESIGNAL, el error debe ser relanzado después de realizar el registro en la tabla de auditoría, de modo que el entorno que invocó al procedimiento reciba la notificación del error y pueda manejarlo si corresponde. Cuando se utilice SIGNAL para generar un error controlado, no es necesario relanzar nada adicional, ya que el error se produce en ese punto.

Script de base de datos

Ejecute el siguiente script para crear las tablas y cargar los datos de ejemplo.

CREATE DATABASE bdd2_integradores;
USE bdd2_integradores;

-- Tabla Clientes
CREATE TABLE Clientes (
    cliente_id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50),
    apellido VARCHAR(50),
    ciudad VARCHAR(50),
    email VARCHAR(50)
);

-- Tabla Productos
CREATE TABLE Productos (
    producto_id INT PRIMARY KEY,
    nombre_producto VARCHAR(50),
    categoria VARCHAR(50),
    precio DECIMAL(10, 2),
    stock INT NOT NULL
);

-- Tabla Pedidos
CREATE TABLE Pedidos (
    pedido_id INT PRIMARY KEY,
    cliente_id INT,
    fecha_pedido DATE,
    FOREIGN KEY (cliente_id) REFERENCES Clientes(cliente_id)
);

-- Tabla Detalle_Pedido
CREATE TABLE Detalle_Pedido (
    detalle_id INT PRIMARY KEY,
    pedido_id INT,
    producto_id INT,
    cantidad INT,
    FOREIGN KEY (pedido_id) REFERENCES Pedidos(pedido_id),
    FOREIGN KEY (producto_id) REFERENCES Productos(producto_id)
);

-- Tabla Auditoria_Errores
CREATE TABLE Auditoria_Errores (
    auditoria_id INT AUTO_INCREMENT PRIMARY KEY,
    procedimiento VARCHAR(100),
    operacion VARCHAR(100),
    mensaje_error VARCHAR(255),
    fecha_error DATETIME
);

-- Tabla para auditoría de operaciones masivas (Ejercicio 5)
CREATE TABLE Auditoria_Operaciones (
    auditoria_op_id INT AUTO_INCREMENT PRIMARY KEY,
    categoria VARCHAR(50),
    fecha_operacion DATETIME,
    cantidad_productos_actualizados INT
);

-- Tabla para alertas de stock (Ejercicio 6)
CREATE TABLE Alertas_Stock (
    alerta_id INT AUTO_INCREMENT PRIMARY KEY,
    producto_id INT,
    stock_actual INT,
    fecha_alerta DATETIME,
    FOREIGN KEY (producto_id) REFERENCES Productos(producto_id)
);

-- Datos de ejemplo
INSERT INTO Clientes (nombre, apellido, ciudad, email) VALUES
('Ana', 'García', 'Madrid', 'ana.garcia@email.com'),
('Juan', 'Pérez', 'Barcelona', 'juan.perez@email.com'),
('María', 'López', 'Madrid', 'maria.lopez@email.com'),
('Carlos', 'Ruiz', 'Valencia', 'carlos.ruiz@email.com');

INSERT INTO Productos (producto_id, nombre_producto, categoria, precio, stock) VALUES
(1, 'Laptop', 'Electrónicos', 1200.00, 5),
(2, 'Tablet', 'Electrónicos', 300.00, 10),
(3, 'Libro', 'Libros', 25.00, 20),
(4, 'Smartphone', 'Electrónicos', 800.00, 8),
(5, 'Auriculares', 'Electrónicos', 150.00, 15),
(6, 'Monitor', 'Electrónicos', 400.00, 7);

INSERT INTO Pedidos (pedido_id, cliente_id, fecha_pedido) VALUES
(1, 1, '2023-10-26'),
(2, 1, '2023-11-10'),
(3, 2, '2023-11-05'),
(4, 3, '2023-10-28'),
(5, 4, '2023-11-15');

INSERT INTO Detalle_Pedido (detalle_id, pedido_id, producto_id, cantidad) VALUES
(1, 1, 1, 1),
(2, 1, 2, 2),
(3, 2, 4, 1),
(4, 3, 3, 3),
(5, 4, 1, 1),
(6, 5, 2, 2),
(7, 5, 4, 1);

Ejercicio 1 – Reposición de stock

Desarrollar un procedimiento almacenado que permita reponer el stock de un producto. El procedimiento deberá recibir un producto_id y una cantidad, verificar que el producto exista, obtener el stock actual, calcular el nuevo stock luego de la reposición y actualizar el valor en la tabla Productos.

Objetos a crear:

Función (UDF): calcularNuevoStock que reciba el stock actual y la cantidad a reponer, y retorne el nuevo stock.
Procedimiento almacenado: reponerStockProducto con los parámetros indicados.

Si la operación se realiza correctamente, el procedimiento deberá informar un mensaje de éxito.

En caso de producirse un error, deberá generarse un error controlado mediante SIGNAL SQLSTATE '45000'. El procedimiento deberá incluir un HANDLER que capture dicho error, registre el motivo en la tabla Auditoria_Errores y luego no relance el error (el error ya ha sido manejado y el procedimiento termina normalmente con el mensaje de error).

Ejercicio 2 – Actualización de precios

Desarrollar un procedimiento almacenado que permita actualizar el precio de un producto aplicando un porcentaje de aumento. El procedimiento deberá recibir un producto_id y un porcentaje, verificar que el producto exista, obtener su precio actual, calcular el nuevo valor y actualizarlo en la tabla Productos.

Objetos a crear:

Función (UDF): calcularNuevoPrecio que reciba el precio actual y el porcentaje de aumento, y retorne el nuevo precio.
Procedimiento almacenado: actualizarPrecioProducto con los parámetros indicados.

Si la operación se realiza correctamente, el procedimiento deberá informar un mensaje de éxito.

Ejercicio 3 – Información completa de un pedido

Desarrollar un procedimiento almacenado que permita mostrar la información completa de un pedido. El procedimiento deberá recibir un pedido_id, verificar que dicho pedido exista y, en caso afirmativo, mostrar su identificador, la fecha en la que fue realizado, el nombre completo del cliente y el total del pedido. Si el pedido existe pero no tiene detalles asociados, el total debe mostrarse como 0 (cero).

Objetos a crear:

Vista: vista_total_pedidos que calcule el total de cada pedido a partir de las tablas Detalle_Pedido y Productos.
Función (UDF): obtenerTotalPedido que, dado un pedido_id, consulte la vista y retorne el total (0 si no tiene detalles).
Procedimiento almacenado: mostrarInfoPedido que reciba el pedido_id y muestre los datos requeridos.

En caso de que el pedido no exista, deberá generarse un error controlado mediante SIGNAL SQLSTATE '45000'. El procedimiento deberá incluir un HANDLER que capture dicho error, registre el motivo en la tabla Auditoria_Errores y luego no relance el error (el error ya ha sido manejado y el procedimiento termina normalmente con el mensaje de error).

Ejercicio 4 – Registro de detalle con control de stock

Desarrollar un procedimiento almacenado que permita registrar un nuevo detalle de pedido controlando que el stock disponible del producto sea suficiente. El procedimiento deberá recibir detalle_id, pedido_id, producto_id y cantidad, verificar que el pedido y el producto existan, obtener el stock actual, validar que haya unidades suficientes, insertar el detalle en la tabla Detalle_Pedido y actualizar el stock del producto.

Objetos a crear:

Función (UDF): calcularStockRestante que reciba el stock actual y la cantidad pedida, y retorne el stock restante.
Procedimiento almacenado: registrarDetallePedido con los cuatro parámetros indicados.

Si la operación se realiza correctamente, el procedimiento deberá informar un mensaje de éxito.

Ejercicio 5 – Actualización masiva de precios por categoría

Desarrollar un procedimiento almacenado que aplique un descuento del 10% a todos los productos de una categoría determinada. El procedimiento deberá recibir el nombre de la categoría.

Objetos a crear:

Procedimiento almacenado: aplicarDescuentoCategoria con un parámetro p_categoria VARCHAR(50).

Se solicita además:

Verificar que la categoría exista (al menos un producto con esa categoría). En caso contrario, generar un error controlado con SIGNAL SQLSTATE '45000'.
Actualizar el precio de cada producto de esa categoría aplicando el descuento. Para el cálculo del nuevo precio, utilizar la función calcularNuevoPrecio desarrollada en el ejercicio 2.
Después de la actualización, insertar un registro en la tabla Auditoria_Operaciones con la siguiente información: categoria, fecha_operacion y cantidad_productos_actualizados.
Incluir un manejador de errores que capture cualquier excepción (SQLEXCEPTION), registre el error en Auditoria_Errores y luego utilice RESIGNAL para relanzar el mismo error. El error relanzado debe propagarse al entorno que llamó al procedimiento.

El procedimiento debe informar un mensaje de éxito si la operación se completa correctamente.

Ejercicio 6 – Consulta de stock con manejo de NOT FOUND

Desarrollar un procedimiento almacenado que reciba un producto_id y muestre su nombre y stock actual.

Objetos a crear:

Procedimiento almacenado principal: consultarStockProducto con un parámetro p_producto_id INT.
Procedimiento almacenado secundario: notificarStockBajo que reciba producto_id y stock_actual, y registre una alerta en la tabla Alertas_Stock.

Se solicita además:

Utilizar una variable local con SELECT ... INTO para obtener el nombre y stock del producto.
Declarar un manejador HANDLER FOR NOT FOUND que, en caso de que el producto no exista (el SELECT ... INTO no encuentre filas), inserte un registro en Auditoria_Errores indicando "Producto no encontrado" y luego lance un error controlado con SIGNAL SQLSTATE '45000'. Este error no debe ser relanzado adicionalmente, ya que el SIGNAL ya produce la excepción.
Si el producto existe, mostrar por pantalla el nombre y el stock.
Además, si el stock es menor a 5 unidades, llamar al procedimiento notificarStockBajo.
El procedimiento principal debe incluir también un manejador para errores generales (SQLEXCEPTION) que registre en Auditoria_Errores y luego use RESIGNAL para relanzar el error al entorno invocante.

Nota: La tabla Alertas_Stock ya fue creada en el script inicial.