PreViaje SQL Database Analysis

Proyecto de diseño, normalización, consulta y auditoría de una base de datos relacional construida a partir de registros de viajes del programa PreViaje.

El trabajo integra data modeling, consultas SQL, una función parametrizada y triggers para conservar evidencia de modificaciones y eliminaciones realizadas sobre la tabla principal.

Resumen

La fuente contiene 12.107 registros correspondientes al período enero de 2021 - octubre de 2023, con información sobre:

• mes de inicio;
• provincia de origen;
• provincia de destino;
• cantidad de viajes;
• cantidad de viajeros;
• edición del programa.

La estructura original se normalizó en tres tablas relacionadas:

• DATOS_PREVIAJE: tabla de hechos con período, viajes, viajeros y claves relacionadas;
• PROVINCIA_PREVIAJE: catálogo de provincias utilizado para origen y destino;
• EDICION_PREVIAJE: catálogo de ediciones del programa.

Objetivos

• Diseñar una estructura relacional normalizada.
• Aplicar PRIMARY KEY y FOREIGN KEY para garantizar integridad referencial.
• Analizar viajes por destino, edición, período y región.
• Crear una función que calcule viajes con parámetros opcionales.
• Implementar triggers de auditoría para operaciones UPDATE y DELETE.

Principales resultados

• La edición PreViaje 2 concentra la mayor cantidad de viajes del dataset.
• Buenos Aires y Río Negro encabezan los destinos con mayor volumen de viajes dentro de una misma edición.
• Las regiones Centro y Patagonia reúnen la mayor participación de viajes.
• Buenos Aires presenta la mayor cantidad de viajes con origen y destino dentro de la misma provincia.
• La función VIAJES() reproduce los resultados de las consultas agregadas para diferentes combinaciones de provincia, período y edición.
• Los triggers almacenan los valores anteriores de los registros modificados y eliminados, junto con la fecha y hora de cada operación.

Estructura del repositorio

previaje-sql-database-analysis/
├── assets/
│   └── figures/        # Diagramas y visualizaciones utilizadas en el informe
├── data/               # Dataset completo en CSV y JSON
├── reports/            # Informe profesional en PDF y Word
├── results/            # Resultados de consultas y tablas de auditoría
├── scripts/            # Consultas, función y triggers en MySQL
├── .gitignore
└── README.md

Datos

Los archivos incluidos en data/ contienen el dataset completo en dos formatos:

• viajes_origen_destino_mes.csv
• viajes_origen_destino_mes.json

Variables principales:

Variable	Descripción
mes_inicio	Mes de inicio del viaje
provincia_origen	Provincia desde la que se origina el viaje
provincia_destino	Provincia de destino
viajes	Cantidad de viajes registrados
viajeros	Cantidad de personas que viajaron
edicion	Edición del programa PreViaje

Desarrollo SQL

El archivo scripts/previaje_analysis.sql contiene:

1. Ranking de destinos y ediciones por cantidad de viajes.
2. Distribución de viajes por período y edición.
3. Ranking de viajes realizados dentro de la misma provincia.
4. Distribución de viajes por región de destino.
5. Función parametrizada VIAJES().
6. Trigger de auditoría para modificaciones.
7. Trigger de auditoría para eliminaciones.

Ejecución

1. Crear la base TF_BASES en MySQL.
2. Importar el dataset inicial.
3. Crear y cargar las tablas normalizadas descriptas en el informe.
4. Ejecutar scripts/previaje_analysis.sql por bloques.
5. Revisar los resultados en las tablas y archivos incluidos en results/.

Las pruebas de triggers modifican o eliminan registros. Para repetir el análisis completo desde el estado inicial, se recomienda volver a importar el dataset antes de ejecutar esas pruebas.

Herramientas

• MySQL
• MySQL Workbench
• SQL relacional
• Data normalization
• Stored functions
• Database triggers

Autora

Lea Lambrecht
LinkedIn · GitHub