Ejercicio 28: Normalización - De CSV a Base de Datos Relacional

Este ejercicio tiene como objetivo guiar a los estudiantes en el proceso de convertir un dataset desnormalizado en una base de datos relacional bien estructurada. Se trabajará con datos del Censo 2010 de Argentina, disponibles en BigQuery, explorando problemas de redundancia y estructuración.

Objetivos

1. Explorar y analizar un dataset desnormalizado en BigQuery.
2. Aplicar el proceso de normalización hasta la tercera forma normal (3FN).
3. Diseñar un modelo de base de datos relacional utilizando claves primarias y foráneas.
4. Implementar el esquema normalizado en SQL y poblar las tablas con datos.

Entrada de Datos

• Dataset: census_data.censo_2010_geo en BigQuery.

Parte 1: Exploración del Dataset y Análisis de Redundancias

Tareas

1. Acceder a BigQuery y ejecutar consultas para analizar la estructura del dataset.
2. Obtener el número total de filas y la cantidad de valores únicos por columna.
3. Detectar redundancias en los identificadores geográficos.

Ejemplo de consultas en SQL

-- Contar filas totales en el dataset
SELECT COUNT(*) AS total_rows
FROM `argentinianpovertyatlas.census_data.censo_2010_geo`;

-- Contar valores únicos por nivel geográfico
SELECT 
  COUNT(DISTINCT PROV_REF_ID) AS unique_provinces,
  COUNT(DISTINCT DPTO_REF_ID) AS unique_departments,
  COUNT(DISTINCT FRAC_REF_ID) AS unique_fractions,
  COUNT(DISTINCT RADIO_REF_ID) AS unique_radios
FROM `argentinianpovertyatlas.census_data.censo_2010_geo`;

Parte 2: Diseño Relacional Normalizado

Tareas

1. Aplicar normalización en tres fases (1FN, 2FN, 3FN).
2. Separar los niveles geográficos en tablas independientes con claves primarias y foráneas.
3. Elegir entre el esquema de IDs referenciales o el sistema de IDs únicos de corrido.

Normalización por Niveles Geográficos

Parte 3: Creación del Esquema Normalizado en SQL

Tareas

1. Escribir el código SQL para crear las tablas normalizadas.
2. Poblarlas con los datos desde BigQuery, asegurando integridad referencial.

Código SQL

CREATE TABLE Provincia (
  PROV_REF_ID INT PRIMARY KEY,
  IDPROV INT UNIQUE,
  NOMPROV VARCHAR(100)
);

CREATE TABLE Departamento (
  DPTO_REF_ID INT PRIMARY KEY,
  IDDPTO INT UNIQUE,
  PROV_REF_ID INT,
  NOMDPTO VARCHAR(100),
  FOREIGN KEY (PROV_REF_ID) REFERENCES Provincia(PROV_REF_ID)
);

CREATE TABLE Fraccion (
  FRAC_REF_ID INT PRIMARY KEY,
  IDFRAC INT UNIQUE,
  DPTO_REF_ID INT,
  FOREIGN KEY (DPTO_REF_ID) REFERENCES Departamento(DPTO_REF_ID)
);

CREATE TABLE Radio (
  RADIO_REF_ID INT PRIMARY KEY,
  IDRADIO INT UNIQUE,
  FRAC_REF_ID INT,
  FOREIGN KEY (FRAC_REF_ID) REFERENCES Fraccion(FRAC_REF_ID)
);

Parte 4: Inserción de Datos y Verificación

Tareas

1. Insertar datos extraídos de BigQuery en las nuevas tablas.
2. Ejecutar consultas de prueba para asegurar que la estructura funciona correctamente.

Ejemplo de consulta para verificar la normalización

-- Contar cuántos departamentos tiene cada provincia
SELECT p.NOMPROV, COUNT(d.DPTO_REF_ID) AS total_departamentos
FROM Provincia p
JOIN Departamento d ON p.PROV_REF_ID = d.PROV_REF_ID
GROUP BY p.NOMPROV;

-- Verificar la estructura de fracciones dentro de un departamento específico
SELECT d.NOMDPTO, COUNT(f.FRAC_REF_ID) AS total_fracciones
FROM Departamento d
JOIN Fraccion f ON d.DPTO_REF_ID = f.DPTO_REF_ID
WHERE d.NOMDPTO = 'Buenos Aires'
GROUP BY d.NOMDPTO;

Reflexión y Preguntas Finales

1. ¿Cómo mejora la normalización la eficiencia de almacenamiento y consulta?
2. ¿Cuál es la ventaja de usar claves foráneas en lugar de IDs de corrido?
3. ¿Cómo se puede optimizar el modelo si se necesitan realizar consultas rápidas por región?

Entrega Esperada

• Esquema en DBML o en Draw.io con el modelo normalizado.
• Código SQL para crear y poblar las tablas.
• Consultas en BigQuery que justifiquen las decisiones de normalización.

Este ejercicio fortalece habilidades de modelado, SQL y análisis de datos en BigQuery, preparando a los estudiantes para trabajar con bases de datos normalizadas a nivel profesional.