Proyecto Final

Enunciado del problema:

La empresa XYZ S.A. enfrenta múltiples desafíos en la gestión de sus operaciones debido a la ausencia de un sistema centralizado de control de inventario y ventas. Actualmente, el uso de hojas de cálculo y aplicaciones de mensajería como WhatsApp para el manejo de pedidos, registros de ventas y compras ha generado numerosos problemas, incluyendo:

Pérdida de información crítica.
Dificultad para identificar productos con baja existencia.
Errores frecuentes en los pedidos a proveedores.
Falta de control sobre las devoluciones.
Pérdidas financieras por falta de visibilidad en el inventario.

Objetivos del sistema a desarrollar:

Controlar el inventario en tiempo real para reducir pérdidas.
Gestionar eficientemente las ventas, compras y devoluciones.
Mantener un historial detallado de transacciones para análisis financiero.
Facilitar la emisión de informes sobre el rendimiento del negocio.
Optimizar las relaciones con clientes y proveedores mediante registros precisos.
Automatizar alertas para productos en baja existencia.

2.1. Nombre de la base de datos:

Inventario_XYZ

2.2.  Tablas Referenciales (Maestras) - 4

1. Clientes

   • id_cliente, nombre, direccion, telefono, email, fecha_registro

   • Información básica de los clientes.

2. Productos

   • id_producto, nombre_producto, descripcion, precio_unitario, stock_actual, fecha_entrada

   • Detalles de cada producto disponible para la venta.

3. Proveedores

   • id_proveedor, nombre_proveedor, contacto, telefono, email, direccion

   • Datos de los proveedores que suministran los productos.

4. Usuarios

   • id_usuario, nombre_usuario, rol, email, password, fecha_registro

   • Información de los empleados que gestionan el sistema.

5. Alertas_Inventario

   • id_alerta, id_producto, tipo_alerta, fecha_alerta, descripcion

   • Registro de alertas automáticas para productos con baja existencia.

Tablas de Movimiento (Transaccionales) -10

1. Ventas

   • id_venta, id_cliente, fecha_venta, total_venta, método_pago

   • Registro de cada venta realizada.

2. Detalles_Venta

   • id_detalle_venta, id_venta, id_producto, cantidad, precio_unitario, subtotal

   • Detalles de los productos incluidos en cada venta.

3. Compras

   • id_compra, id_proveedor, fecha_compra, total_compra, método_pago

   • Registro de compras realizadas a proveedores.

4. Detalles_Compra

   • id_detalle_compra, id_compra, id_producto, cantidad, precio_unitario, subtotal

   • Detalles de los productos adquiridos en cada compra.

5. Devoluciones

   • id_devolucion, id_venta, fecha_devolucion, motivo, total_devolucion

   • Registro de devoluciones realizadas por los clientes.

6. Detalles_Devolucion

   • id_detalle_devolucion, id_devolucion, id_producto, cantidad, precio_unitario, subtotal

   • Detalles de los productos devueltos.

7. Inventario_Historico

   • id_inventario_historico, id_producto, cantidad_entrante, cantidad_salida, stock_final, fecha_actualizacion

   • Historial de movimientos de inventario para auditoría.

8. Historial_Precios

   • id_historial_precio, id_producto, precio_antiguo, precio_nuevo, fecha_cambio

   • Registro de cambios en los precios de los productos.

9. Pagos

   • id_pago, id_cliente, id_compra, fecha_pago, monto_pago, tipo_pago

   • Registro de pagos realizados tanto por clientes como a proveedores.

10. Informe_Ventas

• id_informe, fecha_inicio, fecha_fin, total_ventas, productos_vendidos, reporte_completo

• Informes generados sobre las ventas para análisis financiero.

Relaciones Principales entre Tablas:

• Productos:

  • 1 producto → N detalles de venta (Detalles_Venta)

  • 1 producto → N detalles de compra (Detalles_Compra)

  • 1 producto → N detalles de devolución (Detalles_Devolucion)

  • 1 producto → N registros de inventario histórico (Inventario_Historico)

  • 1 producto → N alertas (Alertas_Inventario)

  • 1 producto → N cambios de precio (Historial_Precios)

• Proveedores:

  • 1 proveedor → N compras (Compras)

• Clientes:

  • 1 cliente → N ventas (Ventas)

  • 1 cliente → N pagos (Pagos)

• Ventas:

  • 1 venta → N detalles de venta (Detalles_Venta)

  • 1 venta → N devoluciones (Devoluciones)

• Compras:

  • 1 compra → N detalles de compra (Detalles_Compra)

  • 1 compra → N pagos (Pagos)

2.3. Diseñar el diccionario de datos de cada tabla (campo, nombre el campo, tipo, tamaño y descripción).  

2.4. Montar o diseñar el modelo entidad relación y dibujar el Diagrama relacional. 

Mr

Mer

3. Crear la BD y las tablas respectivas en MySQL (guardar evidencias del código MySQL con el que resuelva dicha actividad) (Entrega 2)

4. Diseñar ejercicios en los que se apliquen los diferentes comandos que se utilizaron durante el semestre con su respectivo análisis y resultados. (Entrega 3)

1. ¿Qué vamos a mostrar?
El total gastado por cada cliente (sumando todas sus ventas) y cuántas compras ha realizado, usando como base la tabla ventas y clientes.

2. Tablas involucradas:

• ventas

• clientes

• detalles_venta

3. Relaciones:

• ventas.id_cliente → clientes.id_cliente

• ventas.id_venta → detalles_venta.id_venta

4. Funciones a utilizar:

• SUM(): Para sumar el total gastado.

• COUNT(): Para contar las compras.

• JOIN: Para unir tablas.

• GROUP BY: Para agrupar por cliente.

• CREATE TABLE AS SELECT: Para crear la tabla resultante.

5. Código épico:

CREATE TABLE resumen_ventas_clientes AS

SELECT

c.id_cliente,

c.nombre AS nombre_cliente,

COUNT(v.id_venta) AS total_compras,

SUM(v.total_venta) AS total_gastado,

MAX(v.fecha_venta) AS ultima_compra

FROM

clientes c

LEFT JOIN

ventas v ON c.id_cliente = v.id_cliente

GROUP BY

c.id_cliente, c.nombre;

1. ¿Qué vamos a lograr?

Crear un trigger que:

1. Actualice el inventario histórico cada vez que se modifique el stock en productos.

2. Genere una alerta automática si el stock queda bajo (sin borrar tablas existentes).

2. Tablas Involucradas

• productos (contiene stock_actual).

• inventario_historico (registra movimientos).

• alertas_inventario (almacena alertas).

3. Relaciones Clave

• productos.id_producto → inventario_historico.id_producto

• productos.id_producto → alertas_inventario.id_producto

4. Funciones Utilizadas

Función Uso
BEFORE UPDATE Activa el trigger antes de actualizar productos.
NEW / OLD Accede a los valores nuevos y antiguos del registro.
IF Condicional para lanzar alertas solo si el stock es crítico.
INSERT INTO Registra datos en tablas secundarias.

5. Código del Trigger:

DELIMITER //

CREATE TRIGGER tr_actualizar_inventario_y_alerta

BEFORE UPDATE ON productos

FOR EACH ROW

BEGIN

-- 1. Registra el movimiento en inventario_historico

INSERT INTO inventario_historico (

id_producto,

cantidad_entrante,

cantidad_salida,

stock_final,

fecha_actualizacion

) VALUES (

NEW.id_producto,

IF(NEW.stock_actual > OLD.stock_actual, NEW.stock_actual - OLD.stock_actual, 0),

IF(NEW.stock_actual < OLD.stock_actual, OLD.stock_actual - NEW.stock_actual, 0),

NEW.stock_actual,

NOW()

);

-- 2. Genera alerta si el stock es menor a 5 unidades

IF NEW.stock_actual < 5 AND OLD.stock_actual >= 5 THEN

INSERT INTO alertas_inventario (

id_producto,

tipo_alerta,

fecha_alerta,

descripcion

) VALUES (

NEW.id_producto,

'STOCK BAJO',

CURDATE(),

CONCAT('Alerta: Stock de "', NEW.nombre_producto, '" bajo (', NEW.stock_actual, ' unidades).')

);

END IF;

END//

DELIMITER ;

6. Análisis del Trigger

• ¿Cuándo se activa?
  Cada vez que se actualiza stock_actual en productos.

• ¿Qué registra?

  • En inventario_historico: Entradas/salidas y stock final.

  • En alertas_inventario: Solo si el stock cruza el umbral de 5 unidades.

1. ¿Qué Vamos a Lograr? 

1. Agrupar ventas por mes/trimestre para identificar temporadas altas/bajas.

2. Calcular el crecimiento porcentual de ventas entre períodos.

3. Crear una tabla de resumen sin borrar datos existentes.

2. Tablas Involucradas

• ventas (contiene fecha_venta y total_venta).

3. Relaciones Clave

• Ninguna (solo usamos datos de ventas).

4. Funciones Mágicas

Función Uso
DATE_FORMAT() Extraer mes/año de la fecha.
CONCAT() Formatear nombres de períodos (ej: "Enero-2024").
SUM() Sumar ventas por período.
LAG() Comparar con el período anterior (crecimiento).
CREATE TABLE IF NOT EXISTS Evitar errores si la tabla ya existe.

5. Código Épico:

-- Crear tabla de resumen (si no existe)

CREATE TABLE IF NOT EXISTS resumen_ventas_temporadas (

id INT AUTO_INCREMENT PRIMARY KEY,

periodo VARCHAR(20),

total_ventas DECIMAL(10,2),

crecimiento DECIMAL(5,2)

);

-- Insertar/Actualizar datos con análisis mensual

INSERT INTO resumen_ventas_temporadas (periodo, total_ventas, crecimiento)

WITH ventas_mensuales AS (

SELECT

CONCAT(DATE_FORMAT(fecha_venta, '%M-%Y')) AS periodo,

SUM(total_venta) AS total_ventas

FROM

ventas

GROUP BY

DATE_FORMAT(fecha_venta, '%Y-%m')

)

SELECT

periodo,

total_ventas,

ROUND(

((total_ventas - LAG(total_ventas) OVER (ORDER BY periodo)) /

LAG(total_ventas) OVER (ORDER BY periodo)) * 100, 2

) AS crecimiento

FROM

ventas_mensuales;

¿Qué desea mostrar?

Un reporte de ventas que incluya:

• Nombre del cliente

• Nombre del producto

• Cantidad vendida

Tablas involucradas

Tabla Descripción
ventas Contiene la información general de cada venta
clientes Datos del cliente que realizó la compra
detalles_venta Detalles por producto de cada venta
productos Catálogo de productos con su nombre, precio, etc.

Relaciones entre tablas

Relación SQL Descripción
ventas.id_cliente = clientes.id_cliente Una venta pertenece a un cliente
detalles_venta.id_venta = ventas.id_venta Una venta puede tener varios productos
detalles_venta.id_producto = productos.id_producto Cada detalle se asocia a un producto específico

Funciones SQL utilizadas

• JOIN: unir tablas relacionadas

• AS: asignar alias a columnas para claridad

• ORDER BY: ordenar por fecha de venta (opcional)

Código:

SELECT

v.id_venta,

v.fecha_venta,

c.nombre AS cliente,

p.nombre_producto AS producto,

dv.cantidad AS cantidad_vendida

FROM ventas v

JOIN clientes c ON v.id_cliente = c.id_cliente

JOIN detalles_venta dv ON v.id_venta = dv.id_venta

JOIN productos p ON dv.id_producto = p.id_producto

ORDER BY v.fecha_venta;

Creación de Usuarios:

Usuario sin permisos:

CREATE USER 'sin_permisos'@'localhost' IDENTIFIED BY 'tu_contraseña';

Para visualizar:

SHOW GRANTS FOR 'sin_permisos'@'localhost';

Usuario con permisos limitados:

CREATE USER 'usuario_limit'@'localhost' IDENTIFIED BY '123';

Permisos:

SELECT = Leer datos

INSERT = Insertar nuevos registros

GRANT SELECT, INSERT ON inventario_xyz.clientes TO 'usuario_limit'@'localhost';

De la BD inventario y la tabla cliente.

Para visualizar:

SHOW GRANTS FOR 'usuario_limitado'@'localhost';

Usuario con todos los permisos en una base de datos:

CREATE USER 'usuario_full'@'localhost' IDENTIFIED BY '123';

Permisos:

PRIVILEGES = este comando da todos los permisos en una base de datos en especifico

GRANT ALL PRIVILEGES ON mi_basedatos.* TO 'usuario_full'@'localhost';

FLUSH PRIVILEGES;

Para visualizar:

SHOW GRANTS FOR 'usuario_full'@'localhost';

Base de Datos: