¿Por qué modelamos?

• Tenemos que resolver un problema: realizar un desarrollo, sea una aplicación, un servicio o un análisis
• Ese desarrollo necesita guardar datos, y esos datos se relacionan entre sí
• Los datos tienen que servir al problema: responder a sus preguntas, respetar sus reglas y seguir siendo útiles cuando el desarrollo cambie

¿Cómo modelamos los datos para que se ajusten al problema que queremos resolver?

El problema: un sitio de preguntas y respuestas

Queremos desarrollar un sitio web como Stack Overflow:

• Los usuarios publican preguntas y responden a las preguntas de otros
• Cualquier usuario puede comentar preguntas y respuestas
• Las preguntas y las respuestas reciben votos a favor o en contra
• Quien pregunta puede aceptar una de las respuestas
• Los votos y las respuestas aceptadas dan puntuación a su autor

De esta especificación sacaremos las entidades y las relaciones del modelo

Un caso concreto

El 3 de marzo, Ana preguntó cómo ordenar resultados por fecha. Luis respondió al día siguiente y su respuesta recibió 12 votos. Ana la marcó como aceptada

• ¿Qué entidades intervienen y qué relaciones las unen?
• ¿«Aceptada» es atributo de la respuesta, de la pregunta o de la relación entre ambas?
• ¿Qué ocurre con la pregunta si Ana borra su cuenta?
• Modelar es decidir esto antes de escribir la primera consulta

Qué aporta un buen modelo

• Describe el problema en el nivel de abstracción adecuado y descarta lo accesorio
• Decide qué consultas quedan optimizadas y cuáles resultan costosas: es una decisión de rendimiento, no sólo de orden
• Hace explícitas las reglas del dominio para que el sistema impida los estados imposibles
• Permite que varias aplicaciones usen los mismos datos sin negociar cada consulta
• Los datos sobreviven a las aplicaciones que los crearon: el modelo es lo que queda cuando cambian el lenguaje, el producto y la moda

«Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won't usually need your flowcharts; they'll be obvious.»

— Fred Brooks, The Mythical Man-Month, 1975

Cincuenta años después sigue siendo el mejor resumen de por qué empezamos por el modelo

¿Qué es un modelo de datos?

Todo modelo de datos define tres componentes:

1. Estructuras para albergar los datos
2. Restricciones que deben cumplir
3. Operaciones que se pueden realizar

Ejemplos de los tres componentes

Haremos estas tres preguntas a cada sistema del curso

Los tres componentes en el ejemplo

Si Ana borra su cuenta, el modelo decide: borrar sus preguntas, conservarlas sin autor o impedirlo. Stack Overflow las conserva

Tres niveles de decisión

En el modelo relacional el nivel físico cambia sin tocar las consultas: es la independencia física

Una relación es una tabla

• Una relación se ve como una tabla de dos dimensiones, con columnas y filas
• Todas las filas tienen las mismas columnas
• Cada fila se llama también tupla, y representa un elemento: un usuario, una pregunta
• Cada columna o atributo tiene un nombre y un tipo de datos sencillo (escalar): un número, un texto, una fecha. Nunca una lista ni otra tabla
• Los nombres de las columnas y sus tipos forman el esquema de la relación

Clave primaria: identificar cada fila

• Cada entidad tiene identidad propia, así que cada fila debe poder distinguirse de las demás
• La clave primaria es una o varias columnas cuyo valor identifica de forma única cada fila
• Su valor no se repite en dos filas y no admite NULL
• Simple, de una columna: un usuario, por UserId
• Compuesta, de varias: un préstamo de la biblioteca, por (SocioId, LibroId, Fecha)

Clave ajena: relacionar entidades

• Las entidades se relacionan entre sí: cada post lo escribe un usuario
• En el modelo relacional esa conexión se expresa con una clave ajena: una o varias columnas de una tabla que apuntan a una fila de otra tabla
• Para apuntar a esa fila, la clave ajena usa las mismas columnas (mismo número, mismos tipos) que la clave primaria de la otra tabla, y guarda sus valores
• Ojo con el nombre: en el modelo relacional, relación es la tabla, no la conexión entre entidades

Clave primaria y clave ajena

Posts.UserId apunta a Users.UserId: no debería existir un post cuyo usuario no exista

El nombre del usuario no se repite en cada post: cada dato está en un solo sitio y se referencia desde los demás (normalización), lo que evita redundancias y anomalías

Restricciones de integridad

Álgebra relacional

• El modelo relacional se definió como un lenguaje formal para manipular relaciones (álgebra y lógica de predicados)
• Toda búsqueda o conexión entre datos se expresa con selección, proyección, producto cartesiano, unión y reunión (join)
• Cada operación recibe relaciones y produce otra relación: se pueden componer y reordenar según sus propiedades algebraicas
• Nos abstrae de la representación física

Consultas equivalentes, costes diferentes

El optimizador puede reordenar operaciones equivalentes para reducir el volumen de datos intermedio

$$\sigma_{p=\text{conoce}}(R) \bowtie_{o=s} \bigl(\sigma_{p=\text{tieneCuenta}}(R) \bowtie_{o=s} \sigma_{p=\text{web}}(R)\bigr)$$

$$\bigl(\sigma_{p=\text{conoce}}(R) \bowtie_{o=s} \sigma_{p=\text{tieneCuenta}}(R)\bigr) \bowtie_{o=s} \sigma_{p=\text{web}}(R)$$

$$\sigma_{p_1=\text{conoce} \,\wedge\, p_2=\text{tieneCuenta} \,\wedge\, p_3=\text{web}}(R \times R \times R)$$

No todas tienen el mismo coste, aunque describan el mismo resultado

Herramientas de la asignatura

En la asignatura utilizaremos diferentes implementaciones de SQL con pequeñas variaciones pero que tienen usos concretos

Los ejemplos deben ejecutarse con el motor indicado: la sintaxis y, sobre todo, los planes no son idénticos

Modo de trabajo

• El objetivo es manejar SQL con comodidad, no memorizar un catálogo de funciones
• Trabajaremos sobre una instantánea de datos de Stack Overflow cargada por los notebooks de la asignatura
• El mismo conjunto permite estudiar esquema, consultas, calidad de datos, relaciones, agregación y rendimiento
• El cuaderno prepara los datos desde Parquet; DuckDB puede consultarlos directamente y MySQL permite estudiarlos como base de datos de servidor

Primer contacto con SQLite

SQLite almacena una base de datos relacional en un fichero y añade comandos de administración al intérprete:

$ sqlite3 ejemplo.db
sqlite> .tables
sqlite> .schema Posts
sqlite> .open otro-fichero.db
sqlite> .help

Estos comandos no son SQL: son ayudas de la consola de SQLite

Descarga de los datos de prueba

Podéis descargar los datos de Stackoverflow para probar con sqlite3 (aquí):

$ wget https://github.com/dsevilla/bd2-data/releases/download/sqlite-backup-26-27/es.stackoverflow.db.gz
$ gunzip es.stackoverflow.db.gz
$ sqlite3 es.stackoverflow.db
sqlite>

Esquema de Stack Overflow

• Posts: preguntas y respuestas; PostTypeId distingue ambos tipos (1 = pregunta, 2 = respuesta) y ParentId enlaza una respuesta con su pregunta
  • Se ha tomado la decisión de albergar tanto las preguntas como las respuestas en la misma tabla Posts
• Users: usuarios y reputación
• Comments y Votes: actividad relacionada con un post y, cuando procede, un usuario
• Tags y PostTags: relación muchos-a-muchos entre preguntas y etiquetas

SELECT: forma general

SELECT lista_de_columnas
FROM lista_de_tablas
WHERE condicion;

Primero se seleccionan las filas que cumplen la condición y después se proyectan las columnas solicitadas

Elegir columnas

SELECT * FROM Tags;

SELECT Id, Title FROM Posts;

• * es útil para explorar, pero en consultas reales conviene pedir solo las columnas necesarias
• Las comillas de identificadores no son portables: evitamos depender de ellas usando nombres sencillos y sin palabras reservadas

DISTINCT, alias y LIMIT

SELECT DISTINCT PostTypeId FROM Posts;

SELECT DisplayName AS nombre FROM Users;

SELECT DisplayName FROM Users LIMIT 2;

• DISTINCT elimina duplicados del resultado
• AS nombra columnas o expresiones
• LIMIT facilita la exploración; no sustituye a un orden explícito

Ordenar resultados

SELECT Id, DisplayName, Reputation
FROM Users
ORDER BY Reputation DESC, Id ASC;

Sin ORDER BY, SQL no garantiza ningún orden de salida

Selección y proyección

SELECT Id, DisplayName
FROM Users
WHERE Reputation > 100;

El optimizador puede adelantar proyecciones, pero debe conservar las columnas necesarias para evaluar los filtros y las uniones

Filtrar con WHERE

SELECT Id, DisplayName, Reputation
FROM Users
WHERE DisplayName = 'Elenasys';

Patrones y condiciones compuestas

SELECT DisplayName
FROM Users
WHERE Reputation > 100
  AND Location LIKE '%Spain%';

SELECT DisplayName
FROM Users
WHERE DisplayName LIKE '%sys%';

AND tiene mayor prioridad que OR; usar paréntesis hace la intención inequívoca

Expresiones y alias

SELECT DisplayName,
       UpVotes + DownVotes AS votos_totales
FROM Users
WHERE UpVotes + DownVotes > 10;

El alias de SELECT no se puede usar de forma portable en WHERE: en esta fase de la consulta todavía no existe. Para reutilizarlo, se usa una subconsulta, una CTE o se repite la expresión

NULL no es un valor ordinario

• NULL significa desconocido, ausente o no aplicable; no equivale a 0 ni a una cadena vacía
• col = NULL no encuentra filas: se usa col IS NULL o col IS NOT NULL
• Las comparaciones con NULL producen «desconocido», que WHERE no conserva

SELECT Id, ParentId
FROM Posts
WHERE ParentId IS NULL;

Funciones de agregación

SELECT AVG(Score) AS puntuacion_media,
       MAX(Score) AS puntuacion_maxima,
       COUNT(*) AS numero_posts
FROM Posts;

• COUNT(*) cuenta filas; COUNT(columna) solo cuenta valores no nulos
• AVG, SUM, MIN y MAX ignoran los valores nulos
• COUNT(DISTINCT OwnerUserId) cuenta valores distintos no nulos

Funciones: núcleo y dialectos

CONCAT, funciones de desviación típica, aleatoriedad y fechas presentan diferencias entre motores; se ven en el notebook. Por ejemplo, la concatenación de cadenas es || en SQLite y DuckDB, y CONCAT() en MySQL

CASE y COALESCE

SELECT DisplayName,
       CASE
         WHEN Reputation > 100 THEN 'Veterano'
         ELSE 'Rookie'
       END AS tipo,
       COALESCE(UpVotes, 0) AS votos_positivos
FROM Users;

COALESCE(a, b, ...) devuelve el primer argumento que no es nulo

Fechas: filtrar sin perder el índice

Evitar, cuando se busca rendimiento:

WHERE YEAR(CreationDate) = 2025

Preferir un rango semicerrado:

WHERE CreationDate >= '2025-01-01'
  AND CreationDate <  '2026-01-01'

El rango es un predicado sargable: no envuelve la columna, y el motor puede traducirlo a un intervalo de búsqueda en el índice. Las funciones de fecha dependen del motor, pero el principio es general; lo retomaremos con índices y EXPLAIN en la sesión 2

El producto cartesiano en el FROM

SELECT c.Id, p.Title
FROM Comments AS c, Posts AS p
WHERE p.Id = c.PostId
  AND p.Score > 10;

• Dos tablas en el FROM, separadas por comas, son el producto cartesiano $\text{Comments} \times \text{Posts}$: cada comentario con cada post
• p.Id = c.PostId en el WHERE es la selección $\sigma$ que deja sólo las combinaciones que coinciden: el equi-join
• Sin esa condición saldrían todas las combinaciones, tengan o no que ver entre sí

JOIN explícito

La misma consulta, con la condición de unión en su sitio:

SELECT c.Id, p.Title
FROM Comments AS c
JOIN Posts AS p ON p.Id = c.PostId
WHERE p.Score > 10;

• El JOIN ... ON hace visible la condición de unión, separada de los filtros del WHERE
• Evitamos la sintaxis antigua con tablas separadas por comas: es más fácil olvidar una condición y crear un producto cartesiano accidental

USING: cuando la columna se llama igual

SELECT PostId, c.UserId, v.VoteTypeId
FROM Comments AS c
JOIN Votes AS v USING (PostId);

• USING (col) abrevia ON a.col = b.col, y sólo vale si la columna se llama igual en las dos tablas
• La columna aparece una vez en el resultado y se cita sin cualificar; con ON saldría dos veces y SELECT PostId sería ambiguo
• Si los nombres difieren, como Posts.Id frente a Comments.PostId, hay que seguir usando ON

Es la misma reunión, escrita sin repetir el nombre de la clave

Preguntas y quien las hizo

SELECT u.DisplayName, p.Title
FROM Posts AS p
JOIN Users AS u ON u.Id = p.OwnerUserId
WHERE p.PostTypeId = 1;

La misma idea se reutiliza para enlazar comentarios, votos, etiquetas y el historial con los posts

Recuerda que OwnerUserId guarda, en cada Post, el autor (clave ajena a Users.Id)

Varias funciones de agregación

SELECT ParentId,
       COUNT(*)   AS respuestas,
       MAX(Score) AS mejor_puntuacion,
       AVG(Score) AS puntuacion_media
FROM Posts
WHERE PostTypeId = 2
GROUP BY ParentId;

• Una fila por pregunta respondida, con su número de respuestas y la puntuación de la mejor y la media
• COUNT, SUM, AVG, MIN y MAX resumen cada grupo en un valor
• Las preguntas sin respuestas no salen: no hay ninguna fila con su ParentId

GROUP BY por dos columnas y HAVING

SELECT OwnerUserId, PostTypeId, COUNT(*) AS numero_posts
FROM Posts
GROUP BY OwnerUserId, PostTypeId
HAVING COUNT(*) > 10
ORDER BY OwnerUserId ASC, numero_posts DESC;

• WHERE filtra filas antes de agrupar
• HAVING filtra grupos después de agregar
• En SQL estricto, cada expresión seleccionada debe estar agrupada, agregada o ser funcionalmente dependiente de las columnas agrupadas

LEFT JOIN para contar respuestas

SELECT p.Id, COUNT(a.Id) AS respuestas
FROM Posts AS p
LEFT JOIN Posts AS a
  ON a.ParentId = p.Id
 AND a.PostTypeId = 2
WHERE p.PostTypeId = 1
GROUP BY p.Id;

• Partiendo de las preguntas, las que no tienen respuestas siguen apareciendo, con 0. Agrupando sólo las respuestas por ParentId, o con un JOIN, desaparecerían
• Se cuenta COUNT(a.Id), no COUNT(*): la fila de una pregunta sin respuestas existe, pero su a.Id es NULL y no se cuenta

Tablas como conjuntos

SELECT UserId FROM Comments
UNION
SELECT OwnerUserId FROM Posts;

• UNION elimina duplicados; UNION ALL los conserva
• INTERSECT y EXCEPT están disponibles en muchos motores, con diferencias de versión
• IN expresa pertenencia a un conjunto; con NULL hay que revisar cuidadosamente su semántica

El riesgo: identificadores que chocan

SELECT 'alta_usuario' AS Tipo, Id FROM Users
UNION ALL
SELECT 'pregunta', Id FROM Posts WHERE PostTypeId = 1;

• Users.Id y Posts.Id son espacios de identificadores independientes, y ambos empiezan en 1
• El usuario 5 y la pregunta 5 son hechos distintos que comparten el valor de Id
• PRIMARY KEY (Id) fallaría: la clave tiene que ser (Tipo, Id)
• Preguntas y respuestas, en cambio, salen ambas de Posts y nunca chocan entre sí

Subconsultas y tablas derivadas

SELECT u.DisplayName, c.numero_comentarios
FROM Users AS u
JOIN (
  SELECT UserId, COUNT(*) AS numero_comentarios
  FROM Comments
  GROUP BY UserId
) AS c ON c.UserId = u.Id;

Una subconsulta en FROM produce una tabla derivada que se puede unir y consultar como las demás

Consultas correlacionadas

SELECT u.Id, u.DisplayName
FROM Users AS u
WHERE 100 < (
  SELECT COUNT(*)
  FROM Posts AS p
  WHERE p.OwnerUserId = u.Id
    AND p.PostTypeId = 1
);

La subconsulta depende de la fila externa. Un optimizador puede transformarla, pero debemos comprobar el plan y los índices antes de asumir que tendrá un buen coste

Promedio de respuestas: una sutileza de NULL

SELECT AVG(COALESCE(a.respuestas, 0)) AS media_respuestas
FROM Posts AS p
LEFT JOIN (
  SELECT ParentId, COUNT(*) AS respuestas
  FROM Posts
  WHERE PostTypeId = 2
  GROUP BY ParentId
) AS a ON a.ParentId = p.Id
WHERE p.PostTypeId = 1;

Las preguntas sin respuesta salen del LEFT JOIN con a.respuestas a NULL, y AVG ignora los NULL: sin COALESCE sólo promedia las preguntas respondidas y la media queda sesgada al alza

Leer un plan de MySQL

EXPLAIN
SELECT PostId
FROM Votes
WHERE PostId NOT IN (SELECT Id FROM Posts);

Empezaremos por preguntas simples:

• ¿Se recorre toda la tabla o se usa un índice?
• ¿Cuántas filas estima el optimizador?
• ¿Qué unión y qué orden de acceso ha elegido?

EXPLAIN ANALYZE añade medidas reales y ejecuta la consulta

Para continuar

• Definir esquemas con DDL y restricciones
• Insertar, actualizar y borrar datos de forma segura
• Diseñar índices y contrastar planes con EXPLAIN
• Trabajar con JSON y decidir qué parte debe seguir siendo relacional