UA1. Sistemas de Gestión de Bases de Datos (SGBD)

Solución Paso a Paso:

1. MySQL:
   • Modelo relacional, soporta JSON
   • Autenticación por usuarios/roles, SSL, cifrado de datos
   • MySQL Workbench (gratis)
   • Gratis (Community), de pago (Enterprise)
2. PostgreSQL:
   • Relacional + objetos, soporte avanzado para tipos personalizados
   • Roles complejos, Row-Level Security, cifrado
   • pgAdmin (gratis)
   • Completamente open source
3. SQL Server:
   • Relacional, soporta XML y JSON
   • Integración con Active Directory, Always Encrypted
   • SQL Server Management Studio (gratis)
   • Licencia por núcleo (costoso)

Conclusión: PostgreSQL ofrece más características avanzadas gratis, MySQL es más ligero para aplicaciones web, SQL Server es ideal para entornos empresariales Windows.

Solución para MySQL:

-- 1. Creación de usuarios
CREATE USER 'lectura'@'localhost' IDENTIFIED BY 'pass_lectura';
CREATE USER 'escritura'@'localhost' IDENTIFIED BY 'pass_escritura';
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'pass_admin';

-- 2. Asignación de permisos
GRANT SELECT ON base_datos.* TO 'lectura'@'localhost';
GRANT SELECT, INSERT, UPDATE ON base_datos.* TO 'escritura'@'localhost';
GRANT ALL PRIVILEGES ON base_datos.* TO 'admin'@'localhost';

-- 3. Verificación (ejecutar como cada usuario)
-- Como 'lectura' intentar SELECT (debería funcionar) e INSERT (debería fallar)

-- 4. Configuración de auditoría (requiere activar el log general)
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';

Solución para PostgreSQL:

-- 1. Creación de usuarios
CREATE USER lectura WITH PASSWORD 'pass_lectura';
CREATE USER escritura WITH PASSWORD 'pass_escritura';
CREATE USER admin WITH PASSWORD 'pass_admin';

-- 2. Asignación de permisos
GRANT CONNECT ON DATABASE base_datos TO lectura;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO lectura;

GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO escritura;

GRANT ALL PRIVILEGES ON DATABASE base_datos TO admin;

-- 3. Auditoría (requiere extensión pgAudit)
CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'ddl';
SELECT pg_reload_conf();

Solución Completa:

-- Ejemplo en SQL (funciona en MySQL, PostgreSQL, SQL Server)
BEGIN TRANSACTION;

-- Actualización en la tabla de clientes
UPDATE clientes SET saldo = saldo - 100 WHERE id = 1;

-- Punto de guardado
SAVEPOINT antes_actualizar_producto;

-- Actualización en la tabla de productos
UPDATE productos SET stock = stock - 1 WHERE id = 10;

-- Verificamos si hay suficiente stock
DECLARE stock_actual INT;
SELECT stock INTO stock_actual FROM productos WHERE id = 10;

IF stock_actual < 0 THEN
    -- Si no hay stock, revertimos hasta el savepoint
    ROLLBACK TO SAVEPOINT antes_actualizar_producto;
    -- Podemos registrar el error
    INSERT INTO errores VALUES (NOW(), 'Stock insuficiente');
ELSE
    -- Si todo está bien, continuamos
    INSERT INTO ventas VALUES (1, 10, NOW(), 100);
END IF;

-- Confirmamos la transacción
COMMIT;

Explicación paso a paso:

1. Iniciamos la transacción con BEGIN TRANSACTION
2. Actualizamos el saldo del cliente
3. Creamos un savepoint como "punto de retorno"
4. Actualizamos el stock del producto
5. Verificamos si hay suficiente stock
6. Si no hay stock, volvemos al savepoint (deshace solo la actualización del producto)
7. Si todo está bien, registramos la venta
8. Finalmente confirmamos toda la transacción con COMMIT

Soluciones de Optimización:

1. Consulta con JOINs ineficientes:

-- Consulta original (lenta)
SELECT * 
FROM clientes c
JOIN pedidos p ON c.id = p.cliente_id
JOIN productos pr ON p.producto_id = pr.id
WHERE c.pais = 'España';

-- Solución optimizada:
-- Añadir índices:
CREATE INDEX idx_clientes_pais ON clientes(pais);
CREATE INDEX idx_pedidos_cliente ON pedidos(cliente_id);
CREATE INDEX idx_pedidos_producto ON pedidos(producto_id);

-- Reescribir consulta seleccionando solo columnas necesarias:
SELECT c.nombre, p.fecha, pr.nombre_producto, p.cantidad
FROM clientes c
JOIN pedidos p ON c.id = p.cliente_id
JOIN productos pr ON p.producto_id = pr.id
WHERE c.pais = 'España';

2. Consulta con subconsultas redundantes:

-- Consulta original (ineficiente)
SELECT nombre 
FROM productos
WHERE id IN (
    SELECT producto_id 
    FROM pedidos 
    WHERE cantidad > 10
    AND fecha BETWEEN '2023-01-01' AND '2023-12-31'
)
AND id IN (
    SELECT producto_id 
    FROM inventario 
    WHERE stock > 0
);

-- Solución optimizada con JOIN:
SELECT DISTINCT p.nombre
FROM productos p
JOIN pedidos pd ON p.id = pd.producto_id
JOIN inventario i ON p.id = i.producto_id
WHERE pd.cantidad > 10
AND pd.fecha BETWEEN '2023-01-01' AND '2023-12-31'
AND i.stock > 0;

3. Consulta con WHERE costoso:

-- Consulta original (lenta por el cálculo en WHERE)
SELECT * 
FROM ventas
WHERE YEAR(fecha) = 2023 AND MONTH(fecha) = 5;

-- Solución optimizada:
SELECT *
FROM ventas
WHERE fecha BETWEEN '2023-05-01' AND '2023-05-31';

-- Índice recomendado:
CREATE INDEX idx_ventas_fecha ON ventas(fecha);