Base de Datos II

Introduce un texto aquí...

FINAL BASE DE DATOS II

1. Definir el enunciado del problema a sistematizar según las necesidades detectadas. (Debe ser Claro y tener mínimo 8 tablas) (Entrega 1)

Una tienda minorista tiene dificultades para mantener un control de su inventario. El sistema actual basado en registros manuales en hojas de cálculo y manejo de información por sistemas no adecuados como WhatsApp, ha generado problemas como pérdida de información, dificultad para gestionar productos de baja existencia, errores en los pedidos a proveedores, y falta de control sobre las devoluciones. Estos problemas han resultado en productos fuera de inventario, exceso de inventario de productos no deseados, y pérdidas financieras debido a la mala gestión de las devoluciones.

Las necesidades principales:

• Gestión efectiva de productos y proveedores.
• Registro y control de pedidos.
• Gestión detallada de las ventas, incluyendo productos vendidos y clientes.
• Manejo de devoluciones.
• Generación de reportes sobre productos más vendidos, existencias bajas.

2 REALIZAR LO SIGUIENTE:

2.1. Darle un nombre a la base de datos.

R\\: Inventario

2.2. Listado de tablas que llevara la BD. (Cuales son referenciales y cuales son de movimiento)

R\\:

Tablas referenciales:

• Productos- Catálogo de productos
• Proveedores
• Clientes

Nuevas Tablas: 

• alertas_inventario

Tablas de movimiento:

• Ventas
• Detalles_Venta
• Pedidos_Proveedores
• Detalles_Pedido

Nuevas Tablas:

• devoluciones

• detalles_devolucion

• inventario_historico

• resumen_alertas 

Relaciones:

Productos:

• Un producto puede aparecer en muchos detalles de ventas y en muchos detalles de pedidos.

  • Relación con Detalles_Venta: 1 producto → N detalles de venta.
  • Relación con Detalles_Pedido: 1 producto → N detalles de pedido.

Proveedores:

Un proveedor puede tener muchos pedidos.

• Relación con Pedidos_Proveedores: 1 proveedor → N pedidos.

Clientes:

• Un cliente puede realizar muchas ventas.

  • Relación con Ventas: 1 cliente → N ventas (opcional).

Ventas:

• Una venta puede tener muchos detalles de venta.

  • Relación con Detalles_Venta: 1 venta → N detalles de venta.

Detalles_Venta:

• Detalles_Venta pertenecen a una venta y a un producto.

  • Relación con Ventas: N detalles → 1 venta.
  • Relación con Productos: N detalles → 1 producto.

Pedidos_Proveedores:

• Un pedido puede tener muchos detalles de pedido.

  • Relación con Detalles_Pedido: 1 pedido → N detalles de pedido

Detalles_Pedido:

• Detalles_Pedido pertenecen a un pedido y a un producto.

  • Relación con Pedidos_Proveedores: N detalles → 1 pedido.
  • Relación con Productos: N detalles → 1 producto.

Nuevas Tablas (4)

 devoluciones (Tabla de Movimiento)

• Función: Registrar devoluciones de productos por parte de clientes

• Relaciones:

• N devoluciones → 1 venta (cada devolución está asociada a una venta original)

detalles_devolucion (Tabla de Movimiento)

• Función: Detallar los productos específicos devueltos en cada devolución

• Relaciones:

• N detalles → 1 devolución (pertenece a una devolución)

• N detalles → 1 producto (referencia al producto devuelto)

inventario_historico (Tabla de Movimiento)

• Función: Auditoría de todos los movimientos de inventario

• Relaciones:

• N registros → 1 producto (cada registro afecta a un producto)

alertas_inventario (Tabla Referencial)

• Función: Registrar alertas automáticas del sistema

• Relaciones:

• N alertas → 1 producto (cada alerta está asociada a un producto)

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.

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)

CREAR UNA TABLA APARTIR DE OTRA:

1. ¿Qué desea mostrar?
El número total de devoluciones realizadas por cada fecha registrada en la tabla devoluciones.

• devoluciones 

3. Relaciones:
No hay.

4.Funciones a utilizar:

• COUNT(*): Para contar cuántas devoluciones se registraron en cada fecha.

• GROUP BY: Para agrupar los resultados por fecha.

• CREATE TABLE AS SELECT: Para crear una nueva tabla con los resultados de la consulta.

5. Código: 

CREATE TABLE cantidad_devoluciones_por_fecha AS

SELECT Fecha, COUNT(*) AS total_devoluciones

FROM devoluciones

GROUP BY Fecha;

• COUNT(*): Para contar cuántas devoluciones se registraron en cada fecha.

• GROUP BY: Para agrupar los resultados por fecha.

• CREATE TABLE AS SELECT: Para crear una nueva tabla con los resultados de la consulta.

Código:

CREATE TABLE resumen_alertas_octubre AS

SELECT Tipo_alerta, COUNT(*) AS total_alertas

FROM alertas_inventario

WHERE Fecha BETWEEN '2024-10-25' AND '2024-10-31'

GROUP BY Tipo_alerta;

1. ¿Qué desea hacer?
    contabiliza cuántas alertas de inventario se generan por tipo y por fecha, cada vez que se inserta una nueva alerta en alertas_inventario.

En pocas palabras nos muestra la cantidad de alertas que se generan en un día.

1. Tablas involucradas:

• alertas_inventario 

• resumen_alertas

1. Funciones y estructuras utilizadas:

• AFTER INSERT: tipo de trigger que se ejecuta después de cada inserción.

• NEW: referencia a los valores de la fila recién insertada.

• IF...ELSE:  actualiza o se inserta.

• UPDATE y INSERT:  agregan registros en resumen_alertas.

• SELECT INTO: para verificar si ya existe un registro previo.

• PRIMARY KEY compuesta: (Fecha, Tipo_alerta) para evitar duplicados en la tabla resumen.

Código:

DELIMITER //CREATE TRIGGER trg_actualizar_resumen_alertasAFTER INSERT ON alertas_inventarioFOR EACH ROWBEGIN DECLARE existe INT; SELECT COUNT(*) INTO existe FROM resumen_alertas WHERE Fecha = NEW.Fecha AND Tipo_alerta = NEW.Tipo_alerta; IF existe > 0 THEN UPDATE resumen_alertas SET total_alertas = total_alertas + 1 WHERE Fecha = NEW.Fecha AND Tipo_alerta = NEW.Tipo_alerta; ELSE INSERT INTO resumen_alertas (Fecha, Tipo_alerta, total_alertas) VALUES (NEW.Fecha, NEW.Tipo_alerta, 1); END IF;END //DELIMITER ;

• venta.FK_cliente = cliente.id_cliente

Esto significa que cada venta tiene un cliente asociado.  

• detalles_venta.FK_venta = venta.id_venta

Esto indica que cada venta puede tener varios productos vendidos (detalle por producto) 

• detalles_venta.FK_producto = producto.id_producto

Esto dice qué producto fue vendido en cada línea de la venta. 

• JOIN: para unir tablas.

• AS: para alias de campos.

• (opcional) ORDER BY: para ordenar los resultados.

Código:

SELECT

v.id_venta,

v.Fecha,

c.Nombre AS Cliente,

p.Nombre AS Producto,

dv.Cantidad_vendida

FROM venta v

JOIN cliente c ON v.FK_cliente = c.id_cliente

JOIN detalles_venta dv ON v.id_venta = dv.FK_venta

JOIN producto p ON dv.FK_producto = p.id_producto

ORDER BY v.Fecha;

Creación de Usuarios:

Usuario sin permisos:

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

Para visualizar: 

SHOW GRANTS FOR 'usuario_sin_permisos'@'localhost';

Usuario con permisos limitados:

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

Permisos:

SELECT = Leer datos 

INSERT = Insertar nuevos registros 

GRANT SELECT, INSERT ON inventario.cliente TO 'usuario_limitado'@'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_admin'@'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_admin'@'localhost';

FLUSH PRIVILEGES;

 Para visualizar:

SHOW GRANTS FOR 'usuario_admin'@'localhost';

TXT BD:

BASE DE DATOS: