UF2214 - Implementación y uso de una BD (80h)

☰ Main Menú

1. Diseño de BD

1.1 Etapas del Diseño de Bases de Datos

  • Requisitos: Análisis de necesidades del sistema y usuarios.
  • Conceptual: Modelo ER/UML independiente de tecnología.
  • Lógico: Mapeo a modelo relacional con normalización.
  • Físico: Implementación específica con optimizaciones.

1.2 Teoría de la Normalización

Objetivo principal:

Eliminar redundancias y anomalías en los datos mediante formas normales progresivas:

  • 1FN (Primera Forma Normal):
    • Atributos atómicos (sin valores repetidos o compuestos)
    • Tablas sin grupos repetitivos
  • 2FN (Segunda Forma Normal):
    • Cumple 1FN
    • Atributos no clave dependen de toda la clave primaria
  • 3FN (Tercera Forma Normal):
    • Cumple 2FN
    • Atributos no clave no dependen transitivamente de la clave primaria
  • FNBC (Forma Normal de Boyce-Codd):
    • Versión reforzada de 3FN
    • Para claves candidatas compuestas con dependencias complejas
  • 4FN (Cuarta Forma Normal):
    • Elimina dependencias multivaluadas no funcionales
  • 5FN (Quinta Forma Normal):
    • Descompone dependencias de reunión sin pérdida

    1.3 Aplicación al Diseño Relacional

    Metodología recomendada:

  1. Partir de un modelo conceptual completo
  2. Normalizar progresivamente hasta 3FN/FNBC
  3. Validar con ejemplos de datos reales
  4. Documentar decisiones de diseño

Ejemplo: En un sistema académico:

Estudiante(ID, Nombre, {Teléfonos}) → Violación 1FN
Solucionado: Estudiante(ID, Nombre) + Teléfono(ID_Estudiante, Número)

1.4 Desnormalización Controlada

Casos justificados:

  • Optimización de consultas frecuentes
  • Reducción de joins en tablas históricas
  • Sistemas OLAP con patrones de lectura predecibles

⚠️ Precaución: Documentar explícitamente las desnormalizaciones y mantener consistencia con triggers/procedimientos.

↑ Proceso de Diseño
↑ Inicio de Página

2. Métodos de Acceso a una BD

    2.1 Implementación de Accesos por Posición

  • Direccionamiento físico: Acceso directo mediante RID (Record ID)
  • Mapeo posicional: Cálculo de offset en archivos de datos
  • Casos de uso: Recuperación rápida cuando se conoce la ubicación exacta

    2.2 Implementación de Accesos por Valor

    Mecanismos clave:

  • Búsqueda lineal: Escaneo completo de registros (O(n))
  • Tablas hash auxiliares: Conversión valor → dirección física
  • Limitaciones: Costoso en operaciones de actualización

    2.3 Índices

    Tipología fundamental:

  • Estructuras ordenadas: Árboles B/B+ (equilibrado para rangos)
  • Hashing estático/dinámico: Ideal para igualdades exactas
  • Índices bitmap: Para atributos con baja cardinalidad

    • Ejemplo SQL:

    CREATE INDEX idx_clientes_apellido ON clientes(apellido);

    2.4 Árboles B+

  • Estructura multinivel: Nodos internos (guías) + hojas (datos)
  • Ventajas:
    • Balanceo automático
    • Acceso secuencial eficiente (punteros en hojas)
    • Alto factor de ramificación
  • Operaciones: Búsqueda O(log n), inserción con splits

    2.5 Dispersión

    Enfoques principales:

  • Hashing estático: Número fijo de cubetas (módulo simple)
  • Hashing dinámico: Cubetas ajustables (extensible/lineal)
  • Función ideal: Distribución uniforme, mínimo colisiones

    2.6 Índices Agrupados

  • Reorganización física: Los registros se ordenan según el índice
  • Beneficio: Máximo 1 por tabla, óptimo para consultas por rango
  • Trade-off: Alto costo en inserciones/actualizaciones

    2.7 Implementación de Accesos Multivalor

  • Índices compuestos: Combinación de atributos (ej: (departamento, salario))
  • Covering indexes: Incluyen columnas adicionales para evitar accesos a tabla

2.8 Accesos Directos y Secuenciales

Tipo Ventajas Desventajas
Directo O(1) con hashing perfecto Sensibilidad a colisiones
Secuencial Simple de implementar O(n) en peor caso
Mixto Balance entre rendimiento y flexibilidad Overhead de mantenimiento
↑ Test
↑ Inicio de Página

1. Características Generales

Los lenguajes de consulta permiten interactuar con bases de datos para recuperar, insertar, actualizar y eliminar información. Comparten características comunes:

  • Sintaxis declarativa (describe qué datos se necesitan, no cómo obtenerlos)
  • Operaciones CRUD (Create, Read, Update, Delete)
  • Capacidad de filtrado y ordenación
  • Soporte para joins y relaciones entre datos
  • Funciones de agregación (SUM, COUNT, AVG, etc.)
  • Mecanismos de agrupación
  • Optimización para el motor de base de datos

2. SQL (Structured Query Language)

El lenguaje estándar para bases de datos relacionales. Ejemplo básico:

SELECT nombre, salario
FROM empleados
WHERE departamento = 'Ventas'
ORDER BY salario DESC;

Características destacadas:

  • Estándar ANSI/ISO con variantes por proveedor (MySQL, PostgreSQL, SQL Server)
  • Soporte transaccional (COMMIT, ROLLBACK)
  • Vistas, procedimientos almacenados y triggers
  • Índices para optimización

3. OQL (Object Query Language)

Lenguaje para consultar bases de datos orientadas a objetos. Ejemplo:

SELECT e.nombre, e.salario
FROM Empleados e
WHERE e.departamento.nombre = 'Ventas'
AND e.salario > avg(SELECT emp.salario FROM Empleados emp);

Diferencias clave con SQL:

  • Consulta objetos en lugar de tablas
  • Soporte para herencia y polimorfismo
  • Navegación directa entre objetos (e.departamento.nombre)
  • Usado en sistemas como ObjectDB y db4o

4. JPQL (Java Persistence Query Language)

Lenguaje de consulta para JPA (Java Persistence API). Ejemplo:

SELECT e FROM Empleado e
WHERE e.fechaContratacion BETWEEN :inicio AND :fin
AND e.activo = true
ORDER BY e.apellido ASC;

Características principales:

  • Consulta entidades JPA en lugar de tablas directas
  • Soporte para parámetros nombrados (:inicio, :fin)
  • Integración con el contexto de persistencia
  • Puede traducirse a SQL nativo del proveedor

5. XMLQL (XML Query Language)

Lenguaje para consultar documentos XML. Ejemplo:

<WHERE>
  <ELEMENT empleado>
    <nombre>Juan</nombre>
    <salario>5000</salario>
  </ELEMENT>
</WHERE>
<CONSTRUCT>
  <empleado>
    <nombre>{@nombre}</nombre>
    <salario_anual>{@salario*12}</salario_anual>
  </empleado>
</CONSTRUCT>

Aspectos importantes:

  • Diseñado específicamente para datos XML
  • Permite transformaciones complejas de datos
  • Predecesor de XQuery
  • Usado en sistemas de integración de datos

6. Otros Lenguajes de Consulta

HTSQL (Hyper Text Structured Query Language)

Lenguaje que traduce URLs en consultas SQL. Ejemplo:

/empleados?salario>3000&departamento=Ventas&sort=-salario,nombre

Traduce a: SELECT * FROM empleados WHERE salario > 3000 AND departamento = 'Ventas' ORDER BY salario DESC, nombre ASC

LINQ (Language Integrated Query)

Extensión de .NET para consultar colecciones. Ejemplo en C#:

var empleadosVentas = from e in db.Empleados
               where e.Departamento == "Ventas"
               orderby e.Salario descending
               select new { e.Nombre, e.Salario };

Comparativa de Lenguajes de Consulta

Lenguaje Tipo de BD Paradigma Ventajas Limitaciones
SQL Relacional Declarativo Estándar ampliamente adoptado Rígido para datos no relacionales
OQL Orientada a Objetos Orientado a Objetos Consulta objetos directamente Menor adopción que SQL
JPQL ORM/JPA Orientado a Objetos Integración con Java Limitado a ecosistema Java
XMLQL XML Funcional Potente para transformaciones XML Sintaxis compleja
HTSQL Relacional Basado en URLs Muy simple para APIs Consultas complejas difíciles
LINQ Varias Integrado en .NET Tipado fuerte, integrado en C# Limitado a .NET

Demo Interactivo: Consultas Equivalentes

SELECT e.nombre, e.salario * 12 AS salario_anual
FROM empleados e
WHERE e.departamento = 'Ventas'
AND e.fecha_contratacion > '2020-01-01'
ORDER BY salario_anual DESC;

Resultado esperado: Lista de empleados de ventas contratados después de 2020 con su salario anual.

Actividad Práctica: SQL

Base de datos de ejemplo: empleados(id, nombre, salario, departamento, fecha_contratacion)

Ejercicios:

  1. Consulta básica: Seleccionar todos los empleados del departamento "Ventas".
    2. SELECT * FROM empleados WHERE departamento = 'Ventas';
  2. Ordenamiento: Mostrar empleados ordenados por salario (de mayor a menor).
    3. SELECT nombre, salario FROM empleados ORDER BY salario DESC;
  3. Agregación: Calcular el salario promedio por departamento.
    4. SELECT departamento, AVG(salario) AS salario_promedio
    FROM empleados
    GROUP BY departamento;

Reto adicional:

Escribe una consulta que muestre los empleados contratados después del 2020 con salario mayor a $3000.

Actividad Práctica: XML

Documento XML de ejemplo: empleados.xml

<empleados>
  <empleado id="101">
    <nombre>Ana Gómez</nombre>
    <salario>3200</salario>
    <departamento>IT</departamento>
  </empleado>
  <empleado id="102">
    <nombre>Luis Martínez</nombre>
    <salario>2800</salario>
    <departamento>Ventas</departamento>
  </empleado>
</empleados>

Ejercicios:

  1. XPath básico: Seleccionar todos los nombres de empleados.
    2. /empleados/empleado/nombre
  2. Filtrado: Seleccionar empleados con salario mayor a 3000.
    3. /empleados/empleado[salario > 3000]
  3. Transformación XSLT: Crear una tabla HTML con los datos.
    4. <xsl:template match="/">
      <html>
        <table border="1">
          <tr>
            <th>Nombre</th>
            <th>Salario</th>
          </tr>
          <xsl:for-each select="empleados/empleado">
            <tr>
              <td><xsl:value-of select="nombre"/></td>
              <td><xsl:value-of select="salario"/></td>
            </tr>
          </xsl:for-each>
        </table>
      </html>
    </xsl:template>

Reto adicional:

Escribe una consulta XPath para seleccionar empleados del departamento "Ventas" con salario menor a 3000.

↑ Inicio de Página

4. Análisis del Lenguaje de Consulta para BD Relacionales

    4.1 Sentencias de Definición de Datos (DDL)

  • CREATE: Define objetos de la base de datos 
    CREATE TABLE empleados (
    id INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    salario DECIMAL(10,2) CHECK (salario > 0),
    departamento_id INT REFERENCES departamentos(id)
);
  • ALTER: Modifica estructuras existentes 
    ALTER TABLE empleados ADD COLUMN email VARCHAR(255) UNIQUE;
  • DROP: Elimina objetos 
    DROP VIEW vista_empleados_junior;
  • TRUNCATE: Vacía tablas preservando estructura

    4.2 Sentencias de Manipulación de Datos (DML)

  • SELECT (Consultas): 
    SELECT d.nombre, AVG(e.salario) as salario_promedio
FROM departamentos d
JOIN empleados e ON d.id = e.departamento_id
GROUP BY d.id
HAVING COUNT(e.id) > 5
ORDER BY salario_promedio DESC;
  • INSERT (Inserción): 
    INSERT INTO empleados (id, nombre, salario) 
VALUES (1001, 'Ana López', 45000.00);
  • UPDATE (Actualización): 
    UPDATE empleados 
SET salario = salario * 1.05 
WHERE departamento_id = 10;
  • DELETE (Eliminación): 
    DELETE FROM empleados 
WHERE fecha_contratacion < '2020-01-01';

    4.3 Sentencias de Concesión y Revocación de Privilegios

  • GRANT: Otorga permisos 
    GRANT SELECT, INSERT ON empleados TO analista_rh;
GRANT ALL PRIVILEGES ON DATABASE hr TO admin_hr;
  • REVOKE: Retira permisos 
    REVOKE DELETE ON empleados FROM asistente_rh;
  • Niveles de acceso:
    • Nivel tabla
    • Nivel columna (ej: GRANT UPDATE(nombre) ON empleados)
    • Nivel fila (mediante VISTAS con WHERE)

    4.4 Procedimientos Almacenados

  • Lógica encapsulada en el servidor: 
    CREATE PROCEDURE aumentar_salarios(IN depto_id INT, IN porcentaje DECIMAL)
BEGIN
    UPDATE empleados 
    SET salario = salario * (1 + porcentaje/100)
    WHERE departamento_id = depto_id;
    
    SELECT COUNT(*) AS empleados_afectados 
    FROM empleados 
    WHERE departamento_id = depto_id;
END;
  • Ventajas:
    • Mejor rendimiento (menos tráfico red)
    • Centralización de reglas de negocio
    • Mayor seguridad (control de accesos)

    4.5 Disparadores (Triggers)

  • Ejecución automática ante eventos: 
    CREATE TRIGGER auditoria_empleados
AFTER UPDATE ON empleados
FOR EACH ROW
BEGIN
    INSERT INTO auditoria_empleados
    VALUES (OLD.id, OLD.salario, NEW.salario, CURRENT_USER(), NOW());
END;
  • Tipos de triggers:
    Tipo Momento Ejemplo
    BEFORE Antes de la operación Validación de datos
    AFTER Después de la operación Auditoría
    INSTEAD OF Sustituye la operación Para vistas actualizables

    Ejercicio: Diseño de Esquema de Seguridad

    Para un sistema hospitalario:

  1. Crear 3 roles (administrador, medico, recepcionista) con privilegios diferenciados
  2. Implementar un trigger que registre accesos a datos sensibles
  3. Desarrollar un procedimiento para backup automático de tablas críticas

Herramienta: Ejecutar en MySQL Workbench o pgAdmin.

↑ Inicio de Página

4.Análisis del Lenguaje SQL para Bases de Datos Relacionales

1. Sentencias de Definición de Datos (DDL)

Las sentencias DDL (Data Definition Language) permiten crear, modificar y eliminar estructuras de la base de datos.

Principales comandos:

CREATE TABLE empleados ( id INT PRIMARY KEY, nombre VARCHAR(100) NOT NULL, salario DECIMAL(10,2), departamento_id INT, FOREIGN KEY (departamento_id) REFERENCES departamentos(id) ); -- Modificar estructura de tabla ALTER TABLE empleados ADD COLUMN fecha_contratacion DATE; -- Eliminar tabla DROP TABLE empleados;

Práctica guiada:

1.- Crea una tabla llamada productos con los siguientes campos:

  • id (entero, clave primaria)
  • nombre (cadena de hasta 100 caracteres, no nulo)
  • precio (decimal con 2 decimales)
  • stock (entero)

Añade un campo categoria (cadena de 50 caracteres) a la tabla productos.

Elimina la tabla productos que creaste anteriormente.

2. Sentencias de Manipulación de Datos (DML)

Las sentencias DML (Data Manipulation Language) permiten insertar, modificar, eliminar y consultar datos.

Principales comandos:

-- Insertar datos INSERT INTO empleados (id, nombre, salario) VALUES (1, 'Juan Pérez', 2500.00); -- Actualizar datos UPDATE empleados SET salario = 2800.00 WHERE id = 1; -- Eliminar datos DELETE FROM empleados WHERE id = 1; -- Consultar datos SELECT nombre, salario FROM empleados WHERE salario > 2000 ORDER BY salario DESC;

Ejercicio práctico:

Dada la siguiente tabla clientes:

id nombre email fecha_registro
1 Ana Gómez ana@example.com 2023-01-15
2 Carlos Ruiz carlos@example.com 2023-02-20
  1. Inserta un nuevo cliente con ID 3, nombre "Luisa Martínez" y email "luisa@example.com"
  2. Actualiza el email de Carlos Ruiz a "carlos.ruiz@example.com"
  3. Selecciona todos los clientes registrados después del 1 de febrero de 2023

3. Sentencias de Control de Privilegios (DCL)

Las sentencias DCL (Data Control Language) gestionan permisos y acceso a la base de datos.

Principales comandos:

-- Conceder privilegios GRANT SELECT, INSERT ON empleados TO usuario1; -- Revocar privilegios REVOKE DELETE ON empleados FROM usuario2; -- Crear rol y asignar permisos CREATE ROLE editor; GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO editor;

Escenario práctico:

Tienes una base de datos con tres usuarios:

  • admin (todos los privilegios)
  • reportes (solo lectura)
  • asistente (lectura e inserción)

Implementa los permisos necesarios para:

  1. Dar acceso completo al usuario admin
  2. Permitir solo SELECT al usuario reportes
  3. Permitir SELECT e INSERT al usuario asistente
  4. Revocar todos los permisos si un usuario abandona la empresa

4. Procedimientos Almacenados

Los procedimientos almacenados son conjuntos de sentencias SQL almacenados en el servidor.

Ejemplo básico:

CREATE OR REPLACE PROCEDURE aumentar_salarios( porcentaje DECIMAL(5,2), depto_id INT ) LANGUAGE SQL AS $$ BEGIN UPDATE empleados SET salario = salario * (1 + porcentaje / 100) WHERE departamento_id = depto_id; COMMIT; END; $$; -- Ejecutar el procedimiento CALL aumentar_salarios(5.0, 10);

Desafío:

Crea un procedimiento almacenado llamado registrar_venta que:

  1. Acepte como parámetros: producto_id, cantidad y cliente_id
  2. Actualice el stock del producto (restando la cantidad vendida)
  3. Registre la venta en una tabla ventas
  4. Retorne el ID de la venta registrada

5. Disparadores (Triggers)

Los triggers son procedimientos que se ejecutan automáticamente cuando ocurren eventos específicos.

Ejemplo de trigger:

-- Crear función para el trigger CREATE OR REPLACE FUNCTION registrar_cambio_salario() RETURNS TRIGGER AS $$ BEGIN IF NEW.salario <> OLD.salario THEN INSERT INTO historico_salarios ( empleado_id, salario_anterior, salario_nuevo, fecha_cambio ) VALUES ( NEW.id, OLD.salario, NEW.salario, CURRENT_TIMESTAMP ); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; -- Crear trigger CREATE TRIGGER trg_cambio_salario BEFORE UPDATE OF salario ON empleados FOR EACH ROW EXECUTE FUNCTION registrar_cambio_salario();

Actividad:

Crea un trigger que:

  1. Se active después de insertar un nuevo producto
  2. Registre en una tabla log_productos:
    • ID del producto
    • Acción realizada ("INSERT")
    • Fecha y hora del evento
    • Usuario que realizó la acción
Ejemplos y Actividades sobre Consultas SQL
↑ Inicio de Página
Actividades
↑ Inicio de Página