Fundamentos de Bases de Datos: Evolución, Modelo Relacional y Consultas SQL

Ingeniería del software, , , , ,

Evolución y Modelos de Bases de Datos

1. Repaso histórico de la evolución de las bases de datos

Al principio, los programas se enfocaban principalmente en los algoritmos y los datos tenían un papel secundario. Con el crecimiento del uso de las computadoras, surgió la necesidad de organizar y administrar mejor la información. La evolución puede dividirse en tres generaciones:

  • Primera generación: Incluye modelos planos, listas invertidas, el modelo jerárquico y el modelo de red.
  • Segunda generación: Introduce el modelo relacional.
  • Tercera generación: Incorpora bases relacionales extendidas y bases orientadas a objetos.

2. El modelo jerárquico: Características y limitaciones

El modelo jerárquico organiza los datos mediante una estructura de árbol. Existe una raíz de la cual dependen distintos nodos o subárboles. Sus características principales son:

  • Las relaciones son principalmente uno a muchos (1:N) y en un único sentido.
  • Las relaciones se realizan mediante punteros, no mediante valores.

Limitación principal: La organización física y lógica está codificada en los programas, por lo que modificar la estructura resulta sumamente difícil.

3. El modelo de red

El modelo de red parte del modelo jerárquico pero permite relaciones más complejas. Además de relaciones 1:N, permite relaciones muchos a muchos (N:M), donde un nodo hijo puede tener más de un padre.

Ejemplo: Se podría representar una red donde un vendedor se relaciona simultáneamente con empleados, clientes, productos y ventas realizadas.

4. Modelo de listas invertidas vs. Modelo relacional

Las listas invertidas utilizan archivos ordenados físicamente según algún criterio. Se pueden agregar distintos índices y definir múltiples claves de búsqueda. Los registros son accedidos mediante métodos predefinidos y las operaciones dependen de la ubicación física de los datos.

El modelo relacional, en cambio, organiza los datos mediante tablas y se basa en la teoría de conjuntos. Sus mejoras incluyen:

  • Acceso mediante valores.
  • Mayor independencia entre la estructura física y la lógica.
  • Implementación de reglas de integridad y el lenguaje SQL.

Comparativa de Generaciones y el Modelo de Codd

5. Diferencias entre la primera y segunda generación

La primera generación presenta un bajo nivel de abstracción; los programas deben «navegar» por los datos y existe una fuerte dependencia de su organización física (modelos planos, jerárquicos, de red). La segunda generación corresponde al modelo relacional, basado en conjuntos y tablas, donde se accede a los datos por valor y existe independencia lógica/física.

6. El problema fundamental de Codd

Edgar F. Codd propuso el modelo relacional en 1970 buscando reducir la dependencia entre los programas y la forma física de almacenar los datos. Los modelos anteriores dependían de punteros y caminos de navegación rígidos. Codd consideraba inadecuados los modelos jerárquicos y de red porque cualquier cambio en la organización de los datos obligaba a modificar las aplicaciones.

7. Conceptos de bases de datos orientadas a objetos

Los pilares de este modelo son:

  • Objeto: Contiene un estado (atributos) y un comportamiento (métodos).
  • Método: Función que opera sobre un objeto.
  • Clase: Conjunto de objetos que comparten atributos y métodos.
  • Instancia: Una ocurrencia concreta de una clase.
  • Encapsulación: Permite ocultar la información interna del objeto.

Profundización en el Modelo Relacional

8. Bases y características del modelo relacional

Se basa en la teoría de conjuntos y la lógica de predicados. Representa los datos mediante tablas, campos, restricciones y relaciones. Sus reglas básicas son:

  • Las tablas constan de filas (tuplas) y columnas (atributos).
  • Cada atributo posee un dominio y cada celda un único valor.
  • No existen tuplas duplicadas.
  • El orden de filas y columnas es irrelevante.

9. La relación en el modelo de Codd

Una relación se representa como una tabla. Su esquema se define como R(A1, A2, ..., An), donde R es el nombre y A los atributos. Está formada por un conjunto de tuplas donde cada valor pertenece al dominio de su atributo o puede ser NULL.

10. Independencia de los datos

La gran ventaja es la independencia lógica y física. Los programas no necesitan conocer cómo están almacenados los registros. Además, permite vistas personalizadas para diferentes usuarios, control de autorización y el uso de SQL.

11. Acceso por valores vs. Punteros

Significa que para relacionar información se utilizan los valores de los atributos (claves), en lugar de direcciones físicas. Ejemplo: Para buscar un alumno se usa su Cédula de Identidad (CI), sin importar su ubicación física en el disco.

12. Representación de relaciones entre entidades

Se representan mediante claves. Por ejemplo:

  • CARRERA(CodCarrera, Nombre)
  • ALUMNO(CI, Nombre, CodCarrera)

Aquí, CodCarrera es Primary Key en CARRERA y Foreign Key en ALUMNO.

13. Primary Key y Foreign Key

  • Primary Key (PK): Atributo que identifica de forma única cada tupla. No puede ser NULL ni repetirse.
  • Foreign Key (FK): Atributo que referencia a la PK de otra tabla, estableciendo un vínculo entre ambas.

14. Clave candidata y Superclave

Una superclave es cualquier conjunto de atributos que identifica unívocamente a una tupla. Una clave candidata es una superclave mínima (sin atributos redundantes).

Ejemplo: En EMPLEADO(CI, nombre, apellido):

  • {CI} es clave candidata.
  • {CI, apellido} es superclave, pero no candidata (el apellido sobra para la identificación).

15. Condiciones para una Foreign Key

Para que una FK en la tabla B referencie a la tabla A:

  1. Los atributos de la FK deben tener el mismo dominio que la PK referenciada.
  2. El valor de la FK debe existir en la PK de A (Integridad Referencial).

16. Opciones de borrado y modificación (Referential Actions)

  • RESTRICT: Impide la operación si hay registros relacionados.
  • CASCADE: Propaga el cambio o eliminación.
  • SET NULL: Establece la FK como nula.
  • SET DEFAULT: Asigna un valor por defecto.

Lenguaje de Consultas Estructurado (SQL)

17. Definición de SQL

SQL (Structured Query Language) es el estándar para interactuar con bases de datos relacionales. Permite definir estructuras, manipular datos y controlar accesos.

18. Categorías: DDL, DML y DCL

  • DDL (Data Definition Language): Define la estructura (CREATE, ALTER, DROP).
  • DML (Data Manipulation Language): Manipula los datos (INSERT, UPDATE, DELETE, SELECT).
  • DCL (Data Control Language): Administra permisos (GRANT, REVOKE).

19. Operaciones CRUD

Representan las cuatro funciones básicas:

  • Create: INSERT
  • Read: SELECT
  • Update: UPDATE
  • Delete: DELETE

20. El valor NULL

Representa un valor desconocido o inexistente. No es equivalente a cero ni a una cadena vacía. Se consulta mediante IS NULL.

21. Diferencia entre DELETE y DROP

DELETE elimina las filas de una tabla (la estructura permanece). DROP elimina el objeto completo (la tabla y su definición desaparecen de la base de datos).

22. Vistas (VIEW)

Una VIEW es una tabla virtual derivada de una consulta. No almacena datos físicamente. Ejemplo:

CREATE VIEW empleados_activos AS 
SELECT nombre, apellido FROM empleado;

23. Operaciones JOIN

Permiten combinar tablas. Tipos comunes: INNER JOIN, LEFT JOIN, RIGHT JOIN y FULL OUTER JOIN.

24. GROUP BY y 25. WHERE vs. HAVING

GROUP BY agrupa filas con valores comunes para funciones de agregado. WHERE filtra filas antes de agrupar; HAVING filtra los grupos resultantes.

Seguridad: SQL Injection

26. ¿Qué es SQL Injection?

Es una vulnerabilidad que ocurre cuando se inserta entrada de usuario directamente en una consulta SQL sin saneamiento. Permite a un atacante alterar la lógica de la consulta para robar datos o evadir autenticación.

27. Ejemplo de ataque

Si un login usa: WHERE usuario = '" + user + "' AND password = '" + pass + "', un atacante puede ingresar ' OR '1'='1' -- para forzar que la condición siempre sea verdadera.

28. Prevención

La defensa principal es el uso de consultas parametrizadas (prepared statements). También se recomienda validar entradas y aplicar el principio de mínimos privilegios.

Ejercicios Prácticos de Consultas SQL

Caso A: Sistema de Infracciones de Tránsito

c) Listar vehículos de color ‘rojo’:

SELECT marca, modelo FROM Vehiculo WHERE color = 'rojo';

d) Infracciones en Zona ‘Rambla’ (Año 2026):

SELECT i.fecha_hora 
FROM Infraccion i 
JOIN Camara c ON i.id_camara = c.id_camara 
JOIN Zona z ON c.id_zona = z.id_zona 
WHERE z.nombre = 'Rambla' AND i.fecha_hora BETWEEN '2026-01-01' AND '2026-12-31';

e) Recaudación por tipo de falta (pagadas):

SELECT tipo_falta, SUM(importe) AS monto_total 
FROM Infraccion 
WHERE pagada = TRUE 
GROUP BY tipo_falta 
ORDER BY monto_total DESC;

f) Infracciones en zonas con velocidad < 45 km/h:

SELECT i.fecha_hora, i.tipo_falta, i.matricula 
FROM Infraccion i 
JOIN Camara c ON i.id_camara = c.id_camara 
JOIN Zona z ON c.id_zona = z.id_zona 
WHERE z.velocidad_maxima < 45;

g) Vehículo con multa de mayor importe (2020-2025):

SELECT v.marca, v.modelo 
FROM Vehiculo v 
JOIN Infraccion i ON v.matricula = i.matricula 
JOIN Vehiculo_Operativo vo ON v.matricula = vo.matricula 
JOIN Operativo o ON vo.id_operativo = o.id_operativo 
WHERE o.fecha BETWEEN '2020-01-01' AND '2025-12-31' 
ORDER BY i.importe DESC LIMIT 1;

h) Top 5 inspectores con más operativos liderados:

SELECT ins.id_inspector, ins.nombre, ins.apellido, COUNT(o.id_operativo) AS cantidad_operativos 
FROM Inspector ins 
LEFT JOIN Operativo o ON ins.id_inspector = o.id_inspector_a_cargo 
GROUP BY ins.id_inspector, ins.nombre, ins.apellido 
ORDER BY cantidad_operativos DESC, ins.apellido ASC, ins.nombre ASC LIMIT 5;

i) Infracciones por encima del promedio:

SELECT * FROM Infraccion 
WHERE importe > (SELECT AVG(importe) FROM Infraccion);

j) Inspectores con espirometría positiva y > 5 operativos:

SELECT ins.id_inspector, ins.nombre, ins.apellido 
FROM Inspector ins 
JOIN Operativo o ON ins.id_inspector = o.id_inspector_a_cargo 
JOIN Vehiculo_Operativo vo ON o.id_operativo = vo.id_operativo 
WHERE vo.resultado_test_alcohol = 'Espirometría Positiva' 
GROUP BY ins.id_inspector, ins.nombre, ins.apellido 
HAVING COUNT(DISTINCT o.id_operativo) > 5;

k) Cámaras y cantidad de infracciones (incluyendo cero):

SELECT c.id_camara, c.modelo, z.nombre AS zona, COUNT(i.id_infraccion) AS cantidad_infracciones 
FROM Camara c 
JOIN Zona z ON c.id_zona = z.id_zona 
LEFT JOIN Infraccion i ON c.id_camara = i.id_camara 
GROUP BY c.id_camara, c.modelo, z.nombre;

l) Cambio estructural para múltiples inspectores por operativo:

Se debe crear una tabla intermedia para resolver la relación N:M:

CREATE TABLE Inspector_Operativo (
    id_operativo INTEGER NOT NULL,
    id_inspector INTEGER NOT NULL,
    rol VARCHAR(80) NOT NULL,
    PRIMARY KEY (id_operativo, id_inspector),
    FOREIGN KEY (id_operativo) REFERENCES Operativo(id_operativo),
    FOREIGN KEY (id_inspector) REFERENCES Inspector(id_inspector)
);

Caso B: Campeonato Mundial de Fútbol

c) Jugadores de Uruguay:

SELECT j.* FROM jugador j 
JOIN seleccion s ON j.id_seleccion = s.id_seleccion 
WHERE s.nombre = 'Uruguay';

d) Partidos de Uruguay como local en 2026:

SELECT p.fecha FROM partido p 
JOIN seleccion s ON p.id_local = s.id_seleccion 
WHERE s.nombre = 'Uruguay' AND p.fecha BETWEEN '2026-01-01' AND '2026-12-31';

e) Entradas vendidas (Uruguay, Argentina, Brasil):

SELECT p.id_partido, p.fecha, local.nombre AS local, visitante.nombre AS visitante, p.entradas_vendidas 
FROM partido p 
JOIN seleccion local ON p.id_local = local.id_seleccion 
JOIN seleccion visitante ON p.id_visitante = visitante.id_seleccion 
WHERE local.nombre IN ('Uruguay', 'Argentina', 'Brasil') 
   OR visitante.nombre IN ('Uruguay', 'Argentina', 'Brasil') 
ORDER BY p.entradas_vendidas DESC;

f) Partidos con estadio lleno:

SELECT p.fecha, p.fase, local.nombre AS local, visitante.nombre AS visitante 
FROM partido p 
JOIN estadio e ON p.id_estadio = e.id_estadio 
JOIN seleccion local ON p.id_local = local.id_seleccion 
JOIN seleccion visitante ON p.id_visitante = visitante.id_seleccion 
WHERE p.entradas_vendidas = e.capacidad;

g) Estadio de mayor capacidad con partidos (2020-2025):

SELECT e.nombre, e.pais FROM estadio e 
JOIN partido p ON e.id_estadio = p.id_estadio 
WHERE p.fecha BETWEEN '2020-01-01' AND '2025-12-31' 
ORDER BY e.capacidad DESC LIMIT 1;

h) Top 5 árbitros:

SELECT a.id_arbitro, a.nombre, a.apellido, COUNT(p.id_partido) AS cantidad_partidos 
FROM arbitro a 
JOIN partido p ON a.id_arbitro = p.id_arbitro 
GROUP BY a.id_arbitro, a.nombre, a.apellido 
ORDER BY cantidad_partidos DESC, a.apellido ASC, a.nombre ASC LIMIT 5;

i) Partidos con ventas superiores al promedio:

SELECT * FROM partido 
WHERE entradas_vendidas > (SELECT AVG(entradas_vendidas) FROM partido);

j) Árbitros en ‘Final’ y con más de un partido:

SELECT a.id_arbitro, a.nombre, a.apellido 
FROM arbitro a 
JOIN partido p ON a.id_arbitro = p.id_arbitro 
GROUP BY a.id_arbitro, a.nombre, a.apellido 
HAVING COUNT(p.id_partido) > 1 
AND SUM(CASE WHEN p.fase = 'Final' THEN 1 ELSE 0 END) >= 1;

k) Estadios y cantidad de partidos:

SELECT e.nombre, e.pais, COUNT(p.id_partido) AS cantidad_partidos 
FROM estadio e 
LEFT JOIN partido p ON e.id_estadio = p.id_estadio 
GROUP BY e.id_estadio, e.nombre, e.pais 
ORDER BY cantidad_partidos DESC;

l) Cambio estructural para equipo arbitral:

Eliminar id_arbitro de partido y crear tablas de roles y relación:

ALTER TABLE partido DROP COLUMN id_arbitro;

CREATE TABLE rol_arbitral (
    id_rol INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE partido_arbitro (
    id_partido INT NOT NULL,
    id_arbitro INT NOT NULL,
    id_rol INT NOT NULL,
    PRIMARY KEY (id_partido, id_arbitro),
    FOREIGN KEY (id_partido) REFERENCES partido(id_partido),
    FOREIGN KEY (id_arbitro) REFERENCES arbitro(id_arbitro),
    FOREIGN KEY (id_rol) REFERENCES rol_arbitral(id_rol)
);

Definición de Esquemas e Inserciones

CREATE TABLE seleccion (
    id_seleccion INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL UNIQUE,
    pais VARCHAR(100) NOT NULL
);

CREATE TABLE jugador (
    id_jugador INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    apellido VARCHAR(100) NOT NULL,
    fecha_nacimiento DATE,
    posicion VARCHAR(50),
    id_seleccion INT NOT NULL,
    FOREIGN KEY (id_seleccion) REFERENCES seleccion(id_seleccion)
);

CREATE TABLE partido (
    id_partido INT PRIMARY KEY,
    fecha DATE NOT NULL,
    fase VARCHAR(50) NOT NULL,
    id_local INT NOT NULL,
    id_visitante INT NOT NULL,
    id_estadio INT NOT NULL,
    id_arbitro INT NOT NULL,
    entradas_vendidas INT NOT NULL,
    FOREIGN KEY (id_local) REFERENCES seleccion(id_seleccion),
    FOREIGN KEY (id_visitante) REFERENCES seleccion(id_seleccion),
    FOREIGN KEY (id_estadio) REFERENCES estadio(id_estadio),
    FOREIGN KEY (id_arbitro) REFERENCES arbitro(id_arbitro),
    CHECK (id_local <> id_visitante),
    CHECK (entradas_vendidas >= 0)
);

-- Inserciones de ejemplo
INSERT INTO seleccion VALUES
(1, 'Uruguay', 'Uruguay'),
(2, 'Argentina', 'Argentina'),
(3, 'Brasil', 'Brasil');

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.