UA1-SGBD: Ejercicios Teórico-Prácticos

Ejercicio 1: Configuración Básica de Seguridad

Conceptos Fundamentales

La seguridad en SGBD se basa en tres principios fundamentales:

Herramientas y Comandos Clave:

CREATE USER GRANT/REVOKE ROLES VALIDATE_PASSWORD AUDIT LOGS

Los sistemas modernos implementan seguridad a nivel de:

Objetivo: Implementar medidas básicas de seguridad en un SGBD.

Enunciado:

  1. Crea tres usuarios con diferentes roles: administrador, escritura y solo lectura
  2. Asigna permisos adecuados a cada rol
  3. Configura políticas de contraseñas seguras
  4. Habilita el registro de auditoría para operaciones DDL

Solución Paso a Paso

1. Creación de usuarios en MySQL
-- Conectarse como root o administrador
CREATE USER 'admin_db'@'localhost' IDENTIFIED BY 'Admin123*';
CREATE USER 'editor_db'@'localhost' IDENTIFIED BY 'Editor123*';
CREATE USER 'lector_db'@'localhost' IDENTIFIED BY 'Lector123*';
2. Asignación de roles y permisos
-- Para el administrador
GRANT ALL PRIVILEGES ON *.* TO 'admin_db'@'localhost' WITH GRANT OPTION;

-- Para el editor (puede leer y modificar datos)
GRANT SELECT, INSERT, UPDATE, DELETE ON mi_base_datos.* TO 'editor_db'@'localhost';

-- Para el lector (solo consultas)
GRANT SELECT ON mi_base_datos.* TO 'lector_db'@'localhost';

-- Aplicar los cambios
FLUSH PRIVILEGES;
3. Configuración de políticas de contraseñas
-- Instalar el plugin de validación (MySQL 5.6+)
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- Configurar políticas (ejemplo)
SET GLOBAL validate_password.policy = 1;  -- MEDIUM
SET GLOBAL validate_password.length = 8;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.special_char_count = 1;
4. Habilitar auditoría de operaciones DDL
-- Para MySQL (requiere versión Enterprise o MariaDB)
INSTALL PLUGIN server_audit SONAME 'server_audit.so';
SET GLOBAL server_audit_events='QUERY_DDL';
SET GLOBAL server_audit_logging=ON;

-- Alternativa para versiones sin plugin (log general)
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';

Nota: En PostgreSQL puedes usar la extensión pgAudit y en SQL Server utiliza la Auditoría incorporada.

Ejercicio 2: Optimización de Consultas SQL

Conceptos Fundamentales

La optimización de consultas opera en tres niveles:

Técnicas de Optimización:

Herramientas de Análisis:

EXPLAIN / EXPLAIN ANALYZE Performance Schema Query Profiler Visual Explain

Objetivo: Analizar y optimizar consultas SQL ineficientes.

Enunciado: Dada la siguiente consulta, identifica los problemas y optimízala:

SELECT * 
FROM clientes c
JOIN pedidos p ON c.id = p.cliente_id
JOIN productos pr ON p.producto_id = pr.id
WHERE YEAR(p.fecha) = 2023
AND c.pais = 'España'
ORDER BY p.fecha DESC;

Solución Paso a Paso

Problemas identificados:
  1. Uso de SELECT * cuando no se necesitan todas las columnas
  2. Función YEAR() en la cláusula WHERE impide uso de índices
  3. Falta de índices en columnas de JOIN y WHERE
  4. Consulta podría beneficiarse de una estructura de datos denormalizada para reporting
1. Optimización sintáctica
-- Seleccionar solo columnas necesarias
SELECT c.nombre, c.email, p.id AS pedido_id, p.fecha, pr.nombre AS producto, p.cantidad, p.total
FROM clientes c
JOIN pedidos p ON c.id = p.cliente_id
JOIN productos pr ON p.producto_id = pr.id
WHERE p.fecha BETWEEN '2023-01-01' AND '2023-12-31'
AND c.pais = 'España'
ORDER BY p.fecha DESC;
2. Optimización física (índices)
-- Crear índices para mejorar el rendimiento
CREATE INDEX idx_pedidos_cliente ON pedidos(cliente_id);
CREATE INDEX idx_pedidos_producto ON pedidos(producto_id);
CREATE INDEX idx_pedidos_fecha ON pedidos(fecha);
CREATE INDEX idx_clientes_pais ON clientes(pais);
3. Optimización semántica (estructura)
-- Crear vista materializada para reporting
CREATE MATERIALIZED VIEW reporte_ventas_2023 AS
SELECT c.id AS cliente_id, c.nombre, c.pais, p.id AS pedido_id, 
       p.fecha, pr.id AS producto_id, pr.nombre AS producto, 
       p.cantidad, p.total
FROM clientes c
JOIN pedidos p ON c.id = p.cliente_id
JOIN productos pr ON p.producto_id = pr.id
WHERE p.fecha BETWEEN '2023-01-01' AND '2023-12-31'
AND c.pais = 'España';

-- Crear índice para la vista
CREATE INDEX idx_reporte_fecha ON reporte_ventas_2023(fecha);
4. Uso de EXPLAIN para análisis
-- Analizar el plan de ejecución
EXPLAIN 
SELECT c.nombre, c.email, p.id AS pedido_id, p.fecha, pr.nombre AS producto
FROM clientes c
JOIN pedidos p ON c.id = p.cliente_id
JOIN productos pr ON p.producto_id = pr.id
WHERE p.fecha BETWEEN '2023-01-01' AND '2023-12-31'
AND c.pais = 'España';

Nota: El comando EXPLAIN muestra el plan de ejecución, permitiendo identificar cuellos de botella.

Ejercicio 3: Gestión de Transacciones

Conceptos Fundamentales

Las transacciones garantizan el principio ACID:

Niveles de Aislamiento:

Patrones de Diseño:

Objetivo: Implementar una transacción segura para un proceso de venta.

Enunciado: Crea un script SQL que implemente una transacción para:

  1. Registrar una nueva venta
  2. Actualizar el stock del producto
  3. Actualizar el saldo del cliente
  4. Manejar posibles errores y confirmar/revertir la transacción según corresponda

Solución Paso a Paso

1. Estructura básica de la transacción en MySQL
START TRANSACTION;

BEGIN
    -- Variables para manejar errores
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    -- 1. Registrar la venta
    INSERT INTO ventas (cliente_id, producto_id, cantidad, total, fecha)
    VALUES (123, 456, 2, 199.98, CURRENT_DATE);
    
    -- 2. Actualizar stock del producto
    UPDATE productos 
    SET stock = stock - 2 
    WHERE id = 456 AND stock >= 2;
    
    -- Verificar si se actualizó el stock
    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Stock insuficiente';
    END IF;
    
    -- 3. Actualizar saldo del cliente (si aplica)
    UPDATE clientes 
    SET saldo = saldo + 199.98 
    WHERE id = 123;
    
    -- Confirmar la transacción si todo está bien
    COMMIT;
END;
2. Versión para PostgreSQL
BEGIN;
    
    -- 1. Registrar la venta
    INSERT INTO ventas (cliente_id, producto_id, cantidad, total, fecha)
    VALUES (123, 456, 2, 199.98, CURRENT_DATE);
    
    -- 2. Actualizar stock con verificación
    UPDATE productos 
    SET stock = stock - 2 
    WHERE id = 456 AND stock >= 2;
    
    -- Verificar stock
    IF NOT FOUND THEN
        ROLLBACK;
        RAISE EXCEPTION 'Stock insuficiente';
    END IF;
    
    -- 3. Actualizar saldo del cliente
    UPDATE clientes 
    SET saldo = saldo + 199.98 
    WHERE id = 123;
    
COMMIT;
3. Versión para SQL Server
BEGIN TRY
    BEGIN TRANSACTION;
        
        -- 1. Registrar la venta
        INSERT INTO ventas (cliente_id, producto_id, cantidad, total, fecha)
        VALUES (123, 456, 2, 199.98, GETDATE());
        
        -- 2. Actualizar stock con verificación
        UPDATE productos 
        SET stock = stock - 2 
        WHERE id = 456 AND stock >= 2;
        
        IF @@ROWCOUNT = 0
        BEGIN
            THROW 51000, 'Stock insuficiente', 1;
        END
        
        -- 3. Actualizar saldo del cliente
        UPDATE clientes 
        SET saldo = saldo + 199.98 
        WHERE id = 123;
        
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    
    THROW;
END CATCH
4. Buenas prácticas observadas
  • Manejo explícito de errores con ROLLBACK
  • Verificación de condiciones antes de confirmar
  • Uso de bloques TRY-CATCH donde está disponible
  • Operaciones atómicas que cumplen con ACID

Ejercicio 4: Importación/Exportación Masiva de Datos

Conceptos Fundamentales

Las operaciones masivas requieren considerar:

Enfoques para migración de datos:

Herramientas Especializadas:

MySQL: mysqldump/mysqlimport PostgreSQL: pg_dump/pg_restore SQL Server: bcp/BULK INSERT Oracle: SQL*Loader/Data Pump Herramientas ETL: Informatica, Talend, SSIS

Objetivo: Realizar operaciones de importación y exportación masiva de datos.

Enunciado:

  1. Exporta los datos de una tabla a un archivo CSV
  2. Importa datos desde un archivo CSV a una tabla
  3. Realiza una carga masiva de datos usando herramientas nativas del SGBD

Solución Paso a Paso

1. Exportación a CSV en MySQL
-- Usando SELECT INTO OUTFILE
SELECT id, nombre, email, fecha_registro
FROM clientes
WHERE activo = 1
INTO OUTFILE '/tmp/clientes_activos.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Nota: Requiere privilegios FILE en MySQL y que el directorio sea accesible.

2. Importación desde CSV en MySQL
-- Usando LOAD DATA INFILE
LOAD DATA INFILE '/tmp/nuevos_clientes.csv'
INTO TABLE clientes
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS  -- Si el CSV tiene encabezados
(id, nombre, email, @fecha_registro)
SET fecha_registro = STR_TO_DATE(@fecha_registro, '%Y-%m-%d');
3. Exportación en PostgreSQL
-- Usando COPY
COPY (SELECT id, nombre, email, fecha_registro 
      FROM clientes WHERE activo = true)
TO '/tmp/clientes_activos.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');
4. Importación en PostgreSQL
-- Usando COPY
COPY clientes(id, nombre, email, fecha_registro)
FROM '/tmp/nuevos_clientes.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');
5. Herramientas para carga masiva
MySQL: mysqldump PostgreSQL: pg_dump SQL Server: bcp utility Oracle: SQL*Loader Herramientas ETL: Talend, Pentaho

Ejemplo con mysqldump:

# Exportar toda la base de datos
mysqldump -u usuario -p mi_base_datos > backup_completo.sql

# Exportar solo estructura
mysqldump -u usuario -p --no-data mi_base_datos > estructura.sql

# Exportar datos específicos
mysqldump -u usuario -p --where="activo=1" mi_base_datos clientes > clientes_activos.sql

Ejercicio 5: Monitoreo y Rendimiento

Conceptos Fundamentales

El monitoreo de SGBD cubre cuatro áreas clave:

Métricas Clave a Monitorear:

Enfoques para Tuning:

Objetivo: Implementar un sistema básico de monitoreo para identificar cuellos de botella.

Enunciado:

  1. Identifica las consultas más lentas en tu SGBD
  2. Configura un sistema de logging para consultas que superen un umbral de tiempo
  3. Genera un reporte básico de rendimiento
  4. Propone mejoras basadas en los hallazgos

Solución Paso a Paso

1. Identificar consultas lentas en MySQL
-- Habilitar log de consultas lentas
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 2 segundos
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

-- Consultar el log de consultas lentas
SELECT * FROM mysql.slow_log;

-- Alternativa: performance_schema
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
2. Configuración en PostgreSQL
-- Editar postgresql.conf
log_min_duration_statement = 2000  -- 2 segundos
log_statement = 'none'             -- No registrar todas las consultas
log_duration = off

-- Consultar el log
SELECT pg_read_file('/var/log/postgresql/postgresql.log');
3. Generar reporte de rendimiento
-- MySQL: Reporte de índices no utilizados
SELECT * FROM sys.schema_unused_indexes;

-- PostgreSQL: Consultas más frecuentes
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- SQL Server: Uso de índices
SELECT OBJECT_NAME(i.object_id) AS TableName,
       i.name AS IndexName,
       s.user_seeks, s.user_scans, s.user_lookups
FROM sys.indexes i
INNER JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY (s.user_seeks + s.user_scans + s.user_lookups) ASC;
4. Herramientas de monitoreo
MySQL: Performance Schema PostgreSQL: pg_stat_statements SQL Server: DMVs Oracle: AWR Reports Herramientas externas: Prometheus, Grafana

Ejemplo de mejora propuesta:

-- Basado en el análisis, crear un índice faltante
CREATE INDEX idx_clientes_activos ON clientes(pais, activo)
WHERE activo = 1;

-- Optimizar una consulta frecuente
-- Original:
SELECT * FROM pedidos WHERE YEAR(fecha) = 2023;

-- Optimizada:
SELECT * FROM pedidos 
WHERE fecha BETWEEN '2023-01-01' AND '2023-12-31';

-- Crear índice para la consulta optimizada
CREATE INDEX idx_pedidos_fecha ON pedidos(fecha);
▲   volver