Portafolio - Juan Carlos M. de la Cruz

Descripción general del proyecto

Este repositorio contiene el código y los recursos necesarios para modelar una base de datos transaccional utilizando SQL Server. El objetivo principal es estructurar las entidades y relaciones a partir de un archivo de texto descargado de Kaggle, luego poblar las tablas correspondientes mediante un procedimiento almacenado y realizar un análisis de datos con SQL Server. El objetivo es proporcionar una estructura completa para el almacenamiento y análisis de datos. En este proyecto, se han seguido los siguientes pasos para llevar a cabo el modelado y la carga de datos:

Recolección de Requisitos

Identificación de los datos y necesidades del negocio basándonos en el archivo de texto descargado de Kaggle.

Diseño Conceptual

Creación del modelo entidad-relación (ER) para definir las entidades y sus relaciones.

Diseño Lógico

Transformación del modelo ER en un esquema lógico compatible con SQL Server.

Diseño Físico

Implementación del esquema lógico en SQL Server, definiendo tablas, índices y restricciones.

Implementación

Desarrollo de scripts SQL para crear las tablas y el procedimiento almacenado necesario para poblarlas.

Pruebas y Validación

Ejecución de pruebas unitarias e integradas para asegurar la correcta creación y población de las tablas.

Recolección y Análisis de Datos

Para modelar una base de datos transaccional basada en los datos de los archivos .txt descargados de Kaggle, podemos seguir un proceso estructurado, desde la recolección y análisis de los datos hasta el diseño y creación de la base de datos. Entender los Datos Es crucial comenzar por comprender la estructura y el contenido de los datos. Esto implica revisar las cabeceras y el formato de los archivos para identificar las columnas y tipos de datos que se manejarán. A continuación se muestra un ejemplo de cómo se ven las cabeceras de los datos en el archivo: Cabeceras de los Datos Al revisar las cabeceras, podemos determinar qué tipo de información está contenida en cada columna y cómo se relacionan entre sí. Este paso es fundamental para planificar cómo los datos se modelarán en la base de datos. La siguiente imagen muestra un ejemplo detallado de las cabeceras y algunos datos de muestra: Las columnas identificadas en los datos son las siguientes:

País de Proveniencia

Aduana de Ingreso

Fecha de la Poliza

Partida Arancelaria

Modelo del Vehiculo

Marca

Linea

Centimetros Cubicos

Distintivo

Tipo de Vehiculo

Tipo de Importador

Tipo Combustible

Asientos

Puertas

Tonelaje

Valor CIF

Impuesto

Otros

Características de los Datos:

Datos textuales y numéricos.

Información relacionada con importaciones de vehículos.

Modelo Físico OLTP

Creación de la Base de Datos

En esta sección se describen los comandos utilizados para la creación de la base de datos en SQL Server. Estos comandos se aplican a lo largo del desarrollo de la base de datos para definir la creación de la base de datos, tablas, restricciones y otras estructuras necesarias. Esta documentación sirve como referencia para entender y reproducir el proceso de creación de la base de datos.

Comandos Utilizados

CREATE DATABASE: Se utiliza para crear un nuevo esquema en la base de datos, que sirve como un contenedor para las tablas y otros objetos. IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'Importaciones'):

Esta línea verifica si ya existe una base de datos llamada 'Importaciones' en el servidor de SQL Server. sys.databases es una vista del sistema que contiene información sobre todas las bases de datos en el servidor.

BEGIN ... END:

Define un bloque de código que se ejecutará si la condición anterior (IF NOT EXISTS ...) es verdadera. En este caso, si no existe la base de datos 'Importaciones', se procederá con la creación de la base de datos.

CREATE DATABASE Importaciones: Este comando crea la base de datos llamada 'Importaciones'. ON PRIMARY:

Define la ubicación y el nombre del archivo de datos primario (Importaciones.mdf). Esto especifica dónde se almacenarán los datos principales de la base de datos.

LOG ON:

Define la ubicación y el nombre del archivo de registro (Importaciones.ldf). Esto especifica dónde se almacenarán los registros de transacciones de la base de datos.

USE Importaciones:

Cambia el contexto actual de la base de datos al recién creada 'Importaciones'. Todos los comandos SQL que se ejecuten después de esta línea afectarán a la base de datos 'Importaciones'.

GO:

Es un delimitador de lotes en SQL Server. Indica que el bloque de código anterior debe ejecutarse como un solo lote de instrucciones.

Este script asegura que la base de datos 'Importaciones' se cree solo si no existe previamente en el servidor de SQL Server. Una vez creada, cambia el contexto para que todos los comandos posteriores se ejecuten dentro de esta base de datos.

CREATE TABLE: Se utiliza para crear una nueva tabla dentro del esquema especificado. Define la estructura de la tabla, incluyendo las columnas y sus tipos de datos. IF OBJECT_ID('marca', 'U') IS NOT NULL:

Verifica si la tabla 'marca' existe en la base de datos. El parámetro 'U' indica que se está buscando una tabla (User Table).

TRUNCATE TABLE marca:

Si la tabla 'marca' existe, el comando TRUNCATE TABLE elimina todos los registros de la tabla, pero conserva la estructura de la tabla.

CREATE TABLE marca:

Si la tabla 'marca' no existe (la condición ELSE), se procede a crearla con las columnas definidas (id_marca y nombre_marca) junto con las restricciones de clave primaria (PK_marca) y unicidad (UQ_marca_nombre).

Este enfoque garantiza que la tabla 'marca' esté vacía antes de continuar, si ya existe, o que se cree si no existe previamente. Es importante tener en cuenta que TRUNCATE TABLE elimina todos los registros de la tabla sin registrar el cambio en el log de transacciones, por lo que es más rápido que DELETE.

IDENTITY:

Se utiliza para definir una columna de tipo entero que se auto incrementa automáticamente en SQL Server. Es útil para crear identificadores únicos y autoincrementales para las filas de una tabla. Al especificar IDENTITY, se puede definir el valor inicial y el incremento para la columna.

id_marca INT IDENTITY(1,1) NOT NULL

En este ejemplo:

id_marca: Es el nombre de la columna.

INT: Define que la columna es de tipo entero.

IDENTITY(1,1): Indica que el valor inicial será 1 y el incremento será de 1 para cada nueva fila insertada.

NOT NULL: Indica que la columna no puede contener valores nulos.

La columna id_marca se llenará automáticamente con valores únicos y secuenciales a medida que se inserten nuevas filas en la tabla, sin necesidad de especificar manualmente el valor para esta columna.

PRIMARY KEY: Se utiliza para definir una clave primaria en una tabla, que asegura la unicidad y no nulidad de los valores en una columna o conjunto de columnas. UNIQUE: Se utiliza para asegurar que los valores en una columna o conjunto de columnas sean únicos en la tabla. FOREIGN KEY: Se utiliza para definir una clave foránea en una tabla, que crea una relación entre columnas de diferentes tablas. Asegura la integridad referencial entre las tablas. Tipos de Datos Utilizados en el Modelamiento En el modelamiento de la base de datos, se han utilizado diferentes tipos de datos para representar adecuadamente las características y restricciones de los datos que se van a almacenar. Los tipos de datos seleccionados se basan en un análisis detallado de los datos proporcionados y en las necesidades específicas del sistema. A continuación, se describen algunos de los tipos de datos clave utilizados:

INTEGER: Se utiliza para columnas que almacenan números enteros. Este tipo de datos es adecuado para campos como identificadores, contadores y otros valores que no requieren decimales.

NUMERIC: Se utiliza para columnas que necesitan almacenar valores numéricos con precisión decimal, como precios, cantidades monetarias y otras métricas que requieren un manejo preciso de decimales

VARCHAR(45): Se utiliza para columnas que almacenan cadenas de texto de longitud variable, hasta un máximo de 45 caracteres. Este tipo de datos es útil para nombres, descripciones cortas y otros textos que tienen una longitud limitada.

DATE: Se utiliza para columnas que almacenan fechas. Es ideal para campos que requieren almacenamiento de información temporal como fechas de registro, fechas de transacciones, etc.

CHAR(1): Se utiliza para columnas que almacenan un solo carácter, como indicadores o banderas.

El uso adecuado de estos tipos de datos garantiza que los datos se almacenen de manera eficiente y que las operaciones sobre ellos sean rápidas y precisas. Además, se asegura la integridad de los datos al definir restricciones y reglas específicas para cada columna.

Store Procedure para la Carga de Información En el procedimiento almacenado creado para la carga de información, se utilizan diversos comandos y técnicas para asegurar una limpieza y una carga adecuada de los datos. A continuación, se describen los principales elementos y comandos utilizados: Comandos Utilizados UPPER: Se utiliza para convertir todos los datos a mayúsculas, asegurando así una homologación de la información. LTRIM y RTRIM: Se utilizan para eliminar espacios en blanco al inicio y al final de los datos. UPDATE: Se utiliza para realizar una limpieza y actualización de los datos. Por ejemplo, en casos donde se detectó que la marca "GREAT DANE" estaba escrita incorrectamente como "RATE DANE". DELETE: Se utiliza para eliminar la información de las tablas antes de la ejecución del stored procedure, evitando así errores al momento de la población. INSERT INTO: Se utiliza para eliminar la información de las tablas antes de la ejecución del stored procedure, evitando así errores al momento de la población. LEFT JOIN e INNER JOIN: Se utilizan para poblar tablas que dependen de otras tablas de catálogo previamente pobladas, de manera que se puedan obtener sus llaves primarias. Bucle WHILE: Se utiliza para recorrer los registros de la tabla temporal y, en la recursividad, extraer las llaves de cada catálogo para poblar la tabla base que contiene los registros de las importaciones. GROUP BY: Se utiliza para agrupar los datos de los catálogos al momento de la carga. ORDER BY: Se utiliza para ordenar los datos al momento de insertarlos, asegurando que las claves automáticas se generen en el orden correcto.

Ver proyecto en GitHub