# Tema 3 · 9 · Ejercicios adicionales A y B: población del INE e índices con datos de aire

Presentación del Tema 3: «Ejercicio A» (datos del INE: claves ajenas y `JOIN`) y «Ejercicio B» (índices y `EXPLAIN`), de «Ejercicios adicionales». Duración: 20 a 30 minutos cada parte (la carga de `genera_aire.sql` tarda unos segundos; con Adminer, ~2 s medidos). Coste: ninguno propio (usas la RDS y Adminer que ya tienes); con los datos reales de Madrid, además, descargas ~20 MB a tu PC.

> **¿Dudas con un script?** Todos los scripts de esta carpeta traen su propia ayuda: `python3 limpia-ine.py --ayuda` · `python3 madrid-a-largo.py --ayuda` (también vale `--help` o `-h`) explica los pasos y las opciones. Este `README.md` es el paso a paso de la actividad: tenlo a mano y consúltalo antes de preguntar.

## Qué vas a hacer

**Parte A (INE):** cargar 8.132 municipios con su población y las 52 provincias en la base `territorio`, añadir la clave ajena que relaciona las dos tablas y hacer consultas con `JOIN`, `GROUP BY` y `HAVING`; después, comprobar que la integridad referencial impide borrar o insertar de forma incoherente. **Parte B (aire):** cargar 632.448 mediciones sintéticas de calidad del aire en la base `aire`, medir una consulta sin índice y con índice (`EXPLAIN`) y ver cuánto mejora.

```
Parte A: provincias.sql + municipios_ine.csv ──(Adminer)──> base territorio (provincias, municipios) + clave ajena
Parte B: genera_aire.sql ──(Adminer)──> base aire, tabla mediciones (632.448 filas) ──> CREATE INDEX + EXPLAIN
(opcional) limpia-ine.py / madrid-a-largo.py: generan los CSV desde datos reales (en tu PC)
```

**Qué se crea en AWS:** nada. Los ficheros de esta carpeta no crean nada en AWS: generan datos en tu PC o los cargan en la RDS que ya tienes.

## Qué contiene esta carpeta

| Fichero | Para qué sirve | En qué paso se usa |
|---|---|---|
| `ejercicios.txt` | SQL para copiar y pegar con el resultado esperado en comentarios: ejercicios adicionales A y B | Pasos 1 a 6 y 8 a 12 |
| `municipios_ine.csv` | 8.132 municipios con su población de 2025 (220 KB, sin cabecera: `cod_mun,nombre,cod_prov,poblacion`); es el resultado de `limpia-ine.py` | Parte A, paso 3 |
| `provincias.sql` | Las 52 provincias (`USE territorio;` + `INSERT`) | Parte A, paso 2 |
| `limpia-ine.py` | Descarga la tabla de población municipal del INE (27 MB) y deja `municipios_ine.csv` (opcional: el CSV ya viene hecho) | Parte A, paso 7 (opcional) |
| `genera_aire.sql` | Crea la base `aire`, la tabla `mediciones` y 632.448 mediciones sintéticas de 2024 | Parte B, paso 8 |
| `madrid-a-largo.py` | Pasa el CSV de calidad del aire de Madrid (formato ancho) al formato largo de `mediciones` (opcional: datos reales) | Parte B, paso 13 (opcional) |
| `README.md` | Esta guía | Siempre |

## Antes de empezar

- [ ] RDS MariaDB `Available` ([03.01](../03.01-RDSMariaDB.md)) y Adminer conectado ([03.02](../03.02-adminer/README.md)).
- [ ] Los ficheros están en tu PC; ábrelos con un editor de texto, no con Excel (Excel pierde los ceros de `01001`).
- [ ] Para los pasos opcionales (scripts `.py`, `LOAD DATA`): Python 3 en tu PC y, para `LOAD DATA`, el cliente `mariadb` o `mysql` (instálalo en tu PC, o ejecútalo desde una máquina EC2 de tu cuenta) y una RDS pública con el Security Group `BaseDeDatos` abierto a tu IP en el 3306.
- Las dos partes son independientes entre sí; esta actividad **no** necesita las demás.

## Cómo llevar los ficheros a donde toque

No hace falta CloudShell. Para cargar datos se llevan desde tu PC a Adminer (copiar y pegar el SQL en **SQL command**; el CSV con el enlace **Import** de la tabla). Los scripts `.py` opcionales se ejecutan en tu PC con Python 3, abriendo una terminal dentro de esta carpeta (`03.09-ineaire`).

### Ficheros grandes: el límite de subida de Adminer y `LOAD DATA LOCAL INFILE`

Adminer sube los ficheros por HTTP y PHP limita su tamaño. Los dos despliegues de [03.02](../03.02-adminer/README.md) dejan Adminer con `upload_max_filesize = 100M` y `post_max_size = 100M`, así que un Adminer creado con ellos admite ficheros de hasta 100 MB. Con un Adminer desplegado antes de esa corrección, o con cualquier otro que traiga el PHP por defecto, el límite es **2 MB** (`Unable to upload a file. Maximum allowed file size is 2MB.`) y, por encima de 8 MB, `The POST data is too large` (HTTP 413).

Qué cabe siempre: `provincias.sql`, `genera_aire.sql` y `municipios_ine.csv` (220 KB). Qué no cabe con el límite de 2 MB: el CSV original del INE (27 MB) y el CSV de calidad del aire de Madrid en formato largo (~30 MB). El Adminer de Docker (`adminer:latest`, ver [03.02](../03.02-adminer/README.md)) admite 128 MB.

Si tu Adminer admite menos de lo que pesa el fichero, usa el cliente de línea de comandos desde tu PC (no pasa por PHP):

```bash
mariadb -h <endpoint-de-tu-rds> -u admin -p --ssl --local-infile=1 <base> \
  -e "LOAD DATA LOCAL INFILE 'fichero.csv' INTO TABLE tabla CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'"
```

Requisitos: el cliente `mariadb` o `mysql` instalado en tu PC (o, mejor, en una máquina EC2 de tu cuenta con acceso a la RDS), que la RDS sea pública y que el Security Group `BaseDeDatos` deje pasar tu IP al puerto 3306 (*My IP*; desde CloudShell no valdría, porque su IP de salida es otra). La RDS MariaDB 10.11 trae `local_infile=ON`. `CHARACTER SET utf8mb4` evita que las tildes y la ñ salgan rotas (`València`) cuando el CSV es UTF-8 y el cliente usa otra codificación; con los datos numéricos de calidad del aire no hace falta, pero no estorba. La opción `--local-infile=1` es del lado del cliente: sin ella da `The used command is not allowed with this MariaDB version`. Si el CSV trae cabecera, añade `IGNORE 1 LINES`; si sus columnas no están en el orden de la tabla, indícalas entre paréntesis detrás del `TERMINATED BY` (por ejemplo `(estacion, magnitud, fecha, hora, valor)`). Tiempo medido: 1.076.939 filas en ~10 s contra un MariaDB local y alrededor de 1 minuto contra una RDS.

## Paso a paso

### Parte A. Ejercicio adicional A: INE

**Paso 1. Crear la base `territorio` y las dos tablas SIN clave ajena** (SQL y en el bloque `Ejercicio adicional A` de `ejercicios.txt`). El orden importa: con la clave ajena puesta antes de cargar los datos, la importación falla con `Error 1452 Cannot add or update a child row`.

Qué verás: la base `territorio` con las tablas `provincias` (`cod_prov`, `nombre`, `ccaa`) y `municipios` (`cod_mun`, `nombre`, `cod_prov`, `poblacion`), vacías.

**Paso 2. Cargar las 52 provincias.** Pega `provincias.sql` en **SQL command** y **Execute** (empieza con `USE territorio;`). Qué verás: sin errores; `SELECT COUNT(*) FROM provincias;` da 52.

**Paso 3. Importar `municipios_ine.csv`.** Base `territorio` > tabla `municipios` > **select data** > enlace **Import** > el fichero. Son 8.132 municipios (año 2025; columnas `cod_mun, nombre, cod_prov, poblacion`, sin cabecera). Qué verás: `SELECT COUNT(*) FROM municipios;` debe dar **8132**.

**Paso 4. Añadir la clave ajena.**

```sql
ALTER TABLE municipios ADD FOREIGN KEY (cod_prov) REFERENCES provincias(cod_prov);
```

Si falla con 1452, busca los huérfanos con `SELECT DISTINCT cod_prov FROM municipios WHERE cod_prov NOT IN (SELECT cod_prov FROM provincias);`. No sirve rellenar `provincias` con `INSERT ... SELECT DISTINCT` (el CSV de población no trae nombre ni comunidad y las dos columnas son `NOT NULL`): por eso se entrega `provincias.sql`.

**Paso 5. Consultas del ejercicio** (están en `ejercicios.txt` con su resultado esperado). Qué verás: los municipios más poblados son Madrid (3.506.730), Barcelona (1.731.649), València (840.792; cod_mun 46250, el CSV la escribe con el nombre en valenciano), Zaragoza (693.091) y Sevilla (689.423); por comunidad, Andalucía 8.666.412, Cataluña 8.146.265 y Comunidad de Madrid 7.137.031; la consulta 3 (más de 50 municipios de menos de 1.000 habitantes) devuelve **27 provincias** (Burgos 346, Salamanca 333, Guadalajara 254, Zaragoza 236...).

**Paso 6. Integridad referencial.** Las dos sentencias del final del bloque DEBEN dar error: `DELETE FROM provincias WHERE cod_prov = '28';` da `Error 1451` (hay municipios que dependen de ella) y un municipio de una provincia que no existe da `Error 1452`.

**Paso 7 (opcional). Regenerar `municipios_ine.csv` desde el INE.** El CSV original del INE (tabla 29005, `https://www.ine.es/jaxiT3/files/t/es/csv_bdsc/29005.csv`, 27 MB, columnas `Municipios;Sexo;Periodo;Total`) trae todos los años y los tres sexos y no cabe en un Adminer de 2 MB. `limpia-ine.py` lo descarga y lo convierte (solo `Sexo = Total`, el año más reciente con datos, código y nombre separados, sin el separador de miles). Desde una terminal en tu PC, dentro de esta carpeta:

```bash
python3 limpia-ine.py              # descarga el CSV y genera municipios_ine.csv
python3 limpia-ine.py ine.csv      # usa un CSV ya descargado
python3 limpia-ine.py --anio 2023  # otro año; con --sql genera además municipios_ine.sql
```

Qué verás: `N municipios (anio ...) escritos en municipios_ine.csv`. Esto **sobrescribe** `municipios_ine.csv` de la carpeta. Un fallo típico: abrir el CSV del INE en Excel y volver a guardarlo (Excel pierde los ceros de `01001`). El script no pasa por Excel.

### Parte B. Ejercicio adicional B: índices

**Paso 8. Cargar los datos.** Pega `genera_aire.sql` en **SQL command** y **Execute**. Crea la base `aire`, la tabla `mediciones` y la rellena con 632.448 filas sintéticas de 2024 (24 estaciones × 3 magnitudes × 366 días × 24 horas; la 28079008 es una de ellas y la magnitud 8 es NO₂). Todos los alumnos obtienen los mismos números. Qué verás: tarda unos segundos (con Adminer, ~2 s medidos) y al final muestra `632448` filas, del 2024-01-01 al 2024-12-31.

**Paso 9. Consulta de referencia.** Ejecútala dos veces y anota el tiempo de la segunda (está en `ejercicios.txt`). Qué verás: 366 filas (2024-01-01: 2,50; 2024-01-02: 5,01; 2024-01-03: 5,88...).

**Paso 10. `EXPLAIN` sin índice.** Pon `EXPLAIN` delante de la misma consulta. Qué verás: `type = ALL` y `rows` de unos 631.000 (con `ANALYZE`, `r_rows = 632448`).

**Paso 11. Crear el índice** (igualdad, igualdad, rango) y repetir:

```sql
CREATE INDEX idx_est_mag_fecha ON mediciones (estacion, magnitud, fecha);
```

Qué verás: repitiendo el `EXPLAIN`, `type = range`, `rows` de unos 17.700 y, con `ANALYZE`, `r_rows = 8784`. El tiempo baja de unos 0,2 s a unos 0,02 s. Tamaño medido: datos ~25,5 MB e índices ~14,5 MB con este índice (la consulta de `information_schema.tables` del fichero lo muestra).

**Paso 12. Reto: compara con otro índice.** Con el índice de la diapositiva original, `(estacion, fecha)`, la mejora es menor (`r_rows = 26352`). Con un rango de un mes en vez de un año el factor es mucho mayor (`r_rows = 744`). Las sentencias `DROP INDEX` / `CREATE INDEX` están comentadas al final del bloque.

**Paso 13 (opcional). Con datos reales de Madrid.** El portal `https://datos.madrid.es/dataset/201200-0-calidad-aire-horario` publica un CSV de unos 20 MB en **formato ancho** (una fila por estación, magnitud y día, con columnas `H01`/`V01`... `H24`/`V24`). Enlace directo (comprobado el 2026-10-03; el nombre del enlace es estable, el contenido se actualiza; las URL antiguas de ficheros anuales dan 404):

```bash
curl -L -o madrid.csv "https://datos.madrid.es/dataset/201200-0-calidad-aire-horario/resource/201200-1-calidad-aire-horario-csv/download/201200-1-calidad-aire-horario-csv.csv"
```

Si algún día ese enlace falla, entra en la página del dataset y copia el enlace del recurso «CSV» (pestaña de descargas). El fichero cubre un periodo reciente (el 2026-10-03, de 2025-01 a 2026-08): por eso se usa `--anio 2025`, el único año completo. `madrid-a-largo.py` lo pasa al formato de `mediciones`:

```bash
python3 madrid-a-largo.py madrid.csv --anio 2025 > mediciones_2025.csv
# 1.076.939 filas escritas (30 MB: no cabe en un Adminer de 2 MB, usa LOAD DATA, ver «Ficheros grandes»)
```

Para cargarlo con `LOAD DATA` hay que **nombrar las columnas**: la tabla `mediciones` tiene una primera columna `id` automática que el CSV no trae, y sin la lista el comando carga unas pocas filas y el resto se pierde con avisos de datos truncados (`Skipped` alto):

```bash
mariadb -h <endpoint-de-tu-rds> -u admin -p --ssl --local-infile=1 aire   -e "TRUNCATE TABLE mediciones; LOAD DATA LOCAL INFILE 'mediciones_2025.csv' INTO TABLE mediciones FIELDS TERMINATED BY ',' (estacion, magnitud, fecha, hora, valor)"
```

Debe terminar con `Records: 1076939  Deleted: 0  Skipped: 0` y `SELECT COUNT(*) FROM mediciones;` dar 1.076.939.

**Antes de cargar los datos reales** vacía la tabla (`TRUNCATE TABLE mediciones;`) o usa una base nueva: si ya cargaste `genera_aire.sql`, el `COUNT(*)` sumaría las 632.448 filas sintéticas a las 1.076.939 reales. Con esos datos hay que usar el año del fichero en la consulta (`BETWEEN '2025-01-01' AND '2025-12-31'`); medido con 2025: sin índice `r_rows = 1076939`, con `(estacion, magnitud, fecha)` `r_rows = 8609`.

## Errores frecuentes

| Síntoma | Causa | Solución |
|---|---|---|
| `Error 1452 Cannot add or update a child row` al importar `municipios_ine.csv` | La clave ajena se creó antes de cargar las provincias | Crea las tablas sin clave ajena, carga `provincias.sql`, importa el CSV y añade la clave ajena al final |
| `Error 1452` al añadir la clave ajena | Hay municipios con un `cod_prov` que no existe en `provincias` | Busca los huérfanos con la consulta del paso 4 |
| `Error 1451` al borrar la provincia 28 / `Error 1452` al insertar un municipio de la provincia 99 | Es el resultado esperado de la integridad referencial | No es un fallo |
| Códigos como `1001` en vez de `01001` | El CSV pasó por Excel | Usa `municipios_ine.csv` de la carpeta o regenera con `limpia-ine.py` |
| `Error 1064` al pegar un `EXPLAIN SELECT ...;` de la diapositiva | Los puntos suspensivos eran un marcador | Pega la consulta completa detrás de `EXPLAIN` (`ejercicios.txt`, ejercicio B) |
| `Unable to upload a file. Maximum allowed file size is 2MB.` o `The POST data is too large` (HTTP 413) | Este Adminer tiene los límites de PHP por defecto | Usa un Adminer de los scripts actuales ([03.02](../03.02-adminer/README.md)), el de Docker, o `LOAD DATA LOCAL INFILE` (apartado «Ficheros grandes») |
| `The used command is not allowed with this MariaDB version` con `LOAD DATA` | Falta `--local-infile=1` en el cliente | Añade `--local-infile=1` |
| `LOAD DATA` no conecta | La RDS no es pública o el Security Group no deja pasar tu IP por el 3306 (desde CloudShell no vale) | Abre el 3306 a *My IP* y ejecuta desde tu PC |
| El `COUNT(*)` de `mediciones` sale 1.709.387 (o más que 632.448) | Cargaste los datos reales encima de los sintéticos | `TRUNCATE TABLE mediciones;` antes de cargar los reales |
| `curl` descarga una página HTML en vez del CSV, o da 404 (sin probar) | Cambió el enlace del portal de Madrid | Copia el enlace del recurso «CSV» desde la página del dataset |

## Limpieza

Nada que borrar en AWS. Si quieres borrar las bases: `DROP DATABASE territorio;` y `DROP DATABASE aire;`; los ficheros que generaste en tu PC (`madrid.csv`, `mediciones_2025.csv`, `ine_29005.csv`, y `municipios_ine.sql` si usaste `--sql`) se pueden borrar a mano. Las bases desaparecen igualmente al borrar la RDS.

**Esta es la última actividad que usa Adminer y la RDS**: cuando la termines, borra Adminer (limpieza de [03.02](../03.02-adminer/README.md)) **y después** la RDS y el Security Group `BaseDeDatos` (limpieza de [03.01](../03.01-RDSMariaDB.md)). Antes de borrar la RDS, asegúrate de que tienes en tu PC lo que tengas que entregar.

## Cómo sabes que has terminado

- [ ] Parte A: `territorio.municipios` tiene 8132 filas, `provincias` 52, y la clave ajena impide borrar una provincia con municipios (error 1451) y insertar un municipio de una provincia inexistente (error 1452).
- [ ] Parte A: tus consultas dan Madrid 3.506.730 como municipio más poblado y 27 provincias en la consulta 3.
- [ ] Parte B: `aire.mediciones` tiene 632.448 filas, el `EXPLAIN` sin índice muestra `type = ALL` y con `idx_est_mag_fecha` muestra `type = range`.
- [ ] Sabes explicar por qué el orden de las columnas del índice es igualdad, igualdad, rango.

Anterior: [03.08 · Visor web de DynamoDB](../03.08-dynamodbvisor/README.md)  ·  Siguiente: [03.10 · Ejercicio adicional C (estaciones en DynamoDB)](../03.10-estacionesdynamodb/README.md)