Fundamentos y Optimización de Bases de Datos Relacionales con SQL

Modelado y Gestión Avanzada de Bases de Datos Relacionales (SQL)

Definición de la Estructura Base: Tabla Animales

A continuación, se presenta la definición inicial de la tabla animales, utilizada para gestionar información sobre los animales en un refugio.

CREATE TABLE animales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50),
    especie VARCHAR(50),
    edad INT,
    adoptado BOOLEAN
);

Implementación de Restricciones y Valores por Defecto

Se realizan modificaciones sobre la tabla animales para asegurar la integridad de los datos mediante restricciones (CHECK) y la definición de valores por defecto (DEFAULT).

• La edad debe ser un valor no negativo.
• El campo adoptado debe ser un valor booleano (0 o 1) y por defecto se establece en 0 (No adoptado).

ALTER TABLE animales
ADD CONSTRAINT chk_edad_positive CHECK (edad >= 0),
ADD CONSTRAINT chk_adoptado_boolean CHECK (adoptado IN (0, 1)),
MODIFY adoptado BOOLEAN DEFAULT 0;

Desarrollo de Lógica de Negocio: Funciones, Triggers y Procedimientos Almacenados

Función para Calcular la Edad Promedio

Esta función calcula la edad promedio de los animales que han sido adoptados, filtrando por una especie específica.

DELIMITER //
CREATE FUNCTION calcular_edad_promedio(esp VARCHAR(50)) RETURNS DECIMAL(10,2)
BEGIN
    DECLARE promedio DECIMAL(10,2);
    SELECT AVG(edad) INTO promedio FROM animales WHERE especie = esp AND adoptado = TRUE;
    RETURN promedio;
END //
DELIMITER ;

Trigger para Automatizar el Estado de Adopción

Se crea un trigger que se activa después de la inserción en una hipotética tabla adopciones. Su propósito es actualizar automáticamente el estado adoptado del animal correspondiente en la tabla animales.

DELIMITER //
CREATE TRIGGER marcar_adoptado_trigger
AFTER INSERT ON adopciones
FOR EACH ROW
BEGIN
    UPDATE animales SET adoptado = TRUE WHERE id = NEW.id_animal;
END //
DELIMITER ;

Procedimiento para Listar Animales Adoptados y Estadísticas

Este procedimiento almacenado recibe la especie como parámetro y realiza tres consultas: lista los animales adoptados, cuenta el total de adoptados y calcula su edad media.

DELIMITER //
CREATE PROCEDURE listar_animales_adoptados(especie_param VARCHAR(50))
BEGIN
    -- 1. Listado de animales adoptados
    SELECT * FROM animales WHERE especie = especie_param AND adoptado = TRUE;
    
    -- 2. Conteo total de animales adoptados
    SELECT COUNT(*) AS total_animales_adoptados FROM animales WHERE especie = especie_param AND adoptado = TRUE;
    
    -- 3. Cálculo de la edad media
    SELECT AVG(edad) AS edad_media FROM animales WHERE especie = especie_param AND adoptado = TRUE;
END //
DELIMITER ;

Ejemplo Práctico: Cálculo de Ventas Totales

Se define la tabla ventas y se implementa un procedimiento almacenado para calcular el total de ingresos generados por un producto específico.

Definición e Inserción de Datos de Ventas

-- Crear la tabla ventas
CREATE TABLE ventas (
    id_producto INT,
    cantidad INT,
    precio_unitario DECIMAL(10, 2)
);

-- Insertar algunos datos de ejemplo (Corregido: 'venta' a 'ventas')
INSERT INTO ventas (id_producto, cantidad, precio_unitario) VALUES
(1, 5, 10.50),
(2, 3, 15.75),
(1, 2, 8.99);

Procedimiento Almacenado calcular_total_ventas_producto

Este procedimiento calcula el total de ventas para un producto_id_param dado y muestra el resultado.

DELIMITER //
CREATE PROCEDURE calcular_total_ventas_producto(IN producto_id_param INT)
BEGIN
    DECLARE total_ventas DECIMAL(10, 2);
    
    -- Inicializar la variable total_ventas
    SET total_ventas = 0;
    
    -- Calcular el total de ventas para el producto específico
    SELECT SUM(cantidad * precio_unitario) INTO total_ventas FROM ventas WHERE id_producto = producto_id_param;
    
    -- Mostrar el total de ventas para el producto específico
    SELECT total_ventas AS total_ventas_producto;
END //
DELIMITER ;

Administración y Optimización de Bases de Datos

Gestión de Usuarios y Seguridad

La gestión de usuarios es fundamental para la seguridad y el control de acceso a la base de datos.

Creación y Modificación de Usuarios

-- Creación de usuario 'alberto'
CREATE USER 'alberto'@'localhost' IDENTIFIED BY '12345';

-- Modificación de la contraseña
SET PASSWORD FOR 'alberto'@'localhost' = '54321';

Control de Transacciones

Las transacciones aseguran que un conjunto de operaciones se ejecute de forma atómica, garantizando la consistencia de los datos (ACID).

START TRANSACTION;

-- Retiro de 2000 de la cuenta 14-235
UPDATE cuentacorriente
SET saldo = saldo - 2000
WHERE id = '14-235';

-- Depósito de 2000 en la cuenta 15-220
UPDATE cuentacorriente
SET saldo = saldo + 2000
WHERE id = '15-220';

COMMIT; -- Confirma los cambios si ambas operaciones fueron exitosas

Optimización mediante Índices

Los índices mejoran significativamente la velocidad de las consultas SELECT, aunque pueden ralentizar las operaciones de inserción y actualización.

Creación y Eliminación de Índices

-- Creación de índice (UNIQUE, FULLTEXT o SPATIAL son opcionales)
CREATE INDEX idx_nombre_clientes ON Clientes (nombre_clientes);

-- Eliminación de índice
DROP INDEX idx_nombre_clientes ON Clientes;

Motores de Almacenamiento (Storage Engines)

El motor de almacenamiento define cómo se almacenan, recuperan y gestionan los datos. Los más comunes son InnoDB (transaccional) y MyISAM (no transaccional).

CREATE TABLE Clientes (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(50),
apellido VARCHAR(70),
fecha_nac DATE
) ENGINE = InnoDB; 

-- Ejemplo alternativo con MyISAM
CREATE TABLE Clientes_MyISAM (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(50),
apellido VARCHAR(70),
fecha_nac DATE
) ENGINE = MyISAM;

Vistas (Views)

Las vistas son tablas virtuales basadas en el resultado de una consulta, simplificando consultas complejas y gestionando permisos de acceso.

-- Creación de una vista para productos con precio superior a 300
CREATE VIEW vista_productos_caros AS 
SELECT id_producto, nombre, precio
FROM productos 
WHERE precio > 300;

-- Otorgar permiso de lectura sobre la vista al usuario 'alberto'
GRANT SELECT ON vista_productos_caros TO 'alberto'@'localhost';

Gestión de Permisos y Bloqueos

El control de permisos (GRANT/REVOKE) y el bloqueo de tablas (LOCK TABLES) son esenciales para la concurrencia y la seguridad.

Otorgamiento y Revocación de Permisos

-- Otorgar permisos específicos (SELECT, INSERT, UPDATE, DELETE)
GRANT SELECT, INSERT, UPDATE, DELETE ON Tienda.Clientes TO 'alberto'@'localhost';

-- Otorgar todos los permisos sobre múltiples objetos
GRANT ALL ON Tienda.Clientes, Tienda.Productos TO 'alberto'@'localhost';

-- Verificar los permisos de un usuario
SHOW GRANTS FOR 'alberto'@'localhost';

-- Revocar un permiso específico
REVOKE INSERT ON Tienda.Clientes FROM 'alberto'@'localhost';

Bloqueo de Tablas

El bloqueo de tablas se utiliza para garantizar el acceso exclusivo durante operaciones críticas.

-- Bloqueo de lectura (permite múltiples lectores, impide escritores)
LOCK TABLE Clientes READ;

-- Bloqueo de escritura (permite un solo escritor exclusivo)
LOCK TABLE Clientes WRITE;

-- Liberar bloqueos
UNLOCK TABLE Clientes;
UNLOCK TABLES;