Creación de una base OLTP en MySQL

Guía práctica para crear una base de datos transaccional de un caso hipotético de Amazon.

En este blog mostraremos los fundamentos para crear operaciones transaccionales OLTP en una base de datos de MySQL. Asímismo, se reforzarán los conceptos básicos con un ejemplo práctico sobre un catálogo de Amazon que relaciona el producto, las categorías y los proveedores.

Una base de datos OLTP (Online Transaction Processing) es un sistema de gestión de bases de datos optimizado para manejar transacciones en línea de manera eficiente y rápida. Estas bases de datos se utilizan principalmente en aplicaciones operativas que requieren procesar grandes volúmenes de transacciones pequeñas y frecuentes, como en sistemas bancarios, comercio electrónico, gestión de inventarios, o sistemas de punto de venta.

Características de un OLTP

1. Alta concurrencia:

• Soporta múltiples usuarios simultáneamente.
• Maneja gran cantidad de transacciones en tiempo real.

2. Operaciones básicas CRUD:

• Realiza operaciones de Crear, Leer, Actualizar y Borrar (Create, Read, Update, Delete).

3. Datos normalizados:

• Generalmente, los datos se estructuran de manera que minimicen la redundancia (alta normalización).

4. Tiempo de respuesta rápido:

• Diseñada para ofrecer tiempos de respuesta cortos a los usuarios finales.

5. Integridad de los datos:

• Usa mecanismos como transacciones ACID (Atomicidad, Consistencia, Aislamiento y Durabilidad) para garantizar la exactitud y seguridad de las operaciones.

6. Escalabilidad horizontal:

• Puede adaptarse para soportar más usuarios o transacciones mediante la adición de más recursos o instancias.

Operaciones CRUD en MySQL

Ante de empezar a trabajar en MySQL es necesario descargar MySQL server y preferiblemente MySQL workbench por si se quiere evitar escribir por lla línea de comandos. Eso puede ser consultado en la página oficial:

[embed]MySQL :: MySQL Community Downloads Edit descriptiondev.mysql.com

Una vez descargado eso, hablaremos un poco de la gestión de MySQL.

El tipo de bases de datos que se gestionan en un OLTP son del tipo relacional.

Las bases de datos relacionales (RDBMS, por sus siglas en inglés: Relational Database Management System) son sistemas de gestión de bases de datos que almacenan los datos en tablas estructuradas, las cuales están organizadas en filas y columnas. Estas bases de datos están basadas en el modelo relacional, que organiza los datos en un conjunto de relaciones (o tablas) interconectadas por claves. Este modelo permite representar y gestionar grandes volúmenes de datos de manera organizada y eficiente.

Para crear una base de datos desde el Workbench se escribe el comando:

CREATE DATABASE database_name;

En caso de que tengamos múltiples bases de datos, es conveniente decirle a MySQL que nos fijaremos en una base de datos en específico. Eso se logra con el comando USE.

USE database_name;

Como se menciono anteriormente, existen 4 operaciones básicas en un OLTP conocido con el acrónimo de CRUD (create, read, update, delete).

La primera operación básica en MySQL es la de “CREATE” que como su nombre dice sirve para crear o bases de datos o tablas. Una base de datos es una colección de tablas y éstas últimas son un compendio de filas y columnas. En MySQL reciben un nombre diferente, las filas se conocen como registros o tuplas, y las columnas como campos o atributos. Para crear una tabla se utiliza el siguiente comando.

CREATE TABLE nombre_tabla (
 nombre_columna1 tipo_de_dato restricciones,
 nombre_columna2 tipo_de_dato restricciones,
 …
 PRIMARY KEY (columna_principal) #En caso de que exista una primary key
);

Para llenar una tabla se utiliza el comando INSERT INTO con la siguiente syntaxis

INSERT INTO nombre_tabla (columna1, columna2, …) 
VALUES (valor1, valor2, …);

La segunda operación es READ. Que práctictamente es la parte donde le pides a MySQL realizar las consultas de datos de todas la tablas que tienes en tu base de datos. El comando para ello es SELECT, que puede ser descrito como sigue:

SELECT columnas
FROM nombre_tabla
[WHERE condiciones]
[ORDER BY columnas [ASC|DESC]]
[LIMIT cantidad];

La tercera operación es UPDATE. Que sirve para actualizar/modificar los registros de una tabla dadas ciertas condiciones. La sintaxis es la siguiente:

UPDATE nombre_tabla
SET columna1 = valor1, columna2 = valor2, …
WHERE condiciones;

La cuarta operación es DELETE. Esta opción sirve para eliminar registros que satisfacen ciertas condiciones.

DELETE FROM nombre_tabla
WHERE condiciones;

PRIMARY KEY Y FOREIGN KEY

Primary Key (Clave Primaria):

• Es un atributo o conjunto de atributos que identifica de manera única cada registro en una tabla.
• Características principales:
• No permite valores duplicados.
• No permite valores nulos.
• Es esencial para garantizar que cada fila de la tabla sea única y pueda ser referenciada de manera precisa.

Ejemplo: En una tabla Clientes, el campo ID_Cliente podría ser la clave primaria porque identifica de forma única a cada cliente.

Foreign Key (Clave Foránea):

• Es un atributo en una tabla que establece una relación con la clave primaria de otra tabla.
• Características principales:
• Garantiza la integridad referencial entre tablas.
• Permite que una tabla (hija) haga referencia a otra tabla (padre).
• Ayuda a conectar tablas para modelar relaciones como uno a muchos o muchos a muchos.

Ejemplo: En una tabla Pedidos, el campo ID_Cliente podría ser una clave foránea que hace referencia a la clave primaria ID_Cliente en la tabla Clientes.

Para conectar la tabla clientes y la tabla pedidos, podemos hacer el llamado de la tabla padre Clientes con la tabla hijo a partir de la Foreign key.

 — Crear tabla Clientes
CREATE TABLE Clientes (
 ID_Cliente INT PRIMARY KEY, #Primay Key de la tabla Clientes
 Nombre VARCHAR(100),
 Correo VARCHAR(100)
);

 — Crear tabla Pedidos
CREATE TABLE Pedidos (
 ID_Pedido INT PRIMARY KEY,
 Fecha DATE,
 ID_Cliente INT,
 #Referencia a la tabla padre con una foreign key
 FOREIGN KEY (ID_Cliente) REFERENCES Clientes(ID_Cliente) 
);

NORMALIZACIÓN

La normalización es el proceso de organizar y estructurar los datos en una base de datos relacional para evitar la redundancia y las dependencias no deseadas, asegurando que los datos se almacenen de manera eficiente y consistente.

Este proceso se logra mediante la división de grandes tablas en tablas más pequeñas y la creación de relaciones entre ellas, lo cual permite que los datos sean más fáciles de manejar, actualizar y consultar.

“La normalización de bases de datos es un proceso que consiste en designar y aplicar una serie de reglas a las relaciones obtenidas tras el paso del modelo entidad-relación al modelo relacional con objeto de minimizar la redundancia de datos.” (datapath.com)

Existen varias formas en las que se puede normalizar una base de datos relacional. Son 3 las más comunes.

Primera forma Normal (1NF)

Una tabla en su primera forma normal debe cumplir con las siguientes condiciones:

● Valores Atómicos: Cada columna de la tabla debe contener valores indivisibles (o atómicos), lo que significa que no deben haber listas o conjuntos de valores en una única columna.

● Unicidad: Cada columna debe tener un nombre único.

● Mismo Dominio: Cada columna debe almacenar datos del mismo tipo.

● Orden No Significativo: El orden en que se almacenan los datos no debe afectar la integridad de la base de datos.

Segunda forma normal (2NF)

Para alcanzar la 2NF, una tabla ya debe estar en 1NF y además debe cumplir con:

● Eliminación de la Dependencia Parcial: No debe existir dependencia entre las claves no primarias y una parte de la clave primaria en una tabla. Esto aplica solo a las tablas con claves primarias compuestas. Si una clave no primaria depende solo de parte de la clave compuesta, entonces la tabla no está en 2NF.

Tercera forma normal (3NF)

Para estar en 3NF, una tabla ya debe cumplir con 1NF y 2NF, y además:

● Eliminación de las Dependencias Transitivas: Ningún atributo no clave debe depender de otro atributo no clave. Cada atributo no clave debe depender solo de la clave primaria.

Modelo de Entidad-Relación

Los diagramas entidad-relación (ER) son una herramienta fundamental en el diseño de bases de datos.

La metodología ER se centra en identificar las entidades relevantes, sus atributos, y las relaciones entre estas entidades.

Facilita el entendimiento de cómo se relacionan entre sí los diferentes elementos de una base de datos.

GUÍA PRÁCTICA PARA LA CREACIÓN DE UN OLTP EN MySQL

Nuestro proceso estructurado de un OLTP puede dividirse en 4 partes:

1. Entendimiento del negocio: que es donde se determinan el tipo de transacciones entre las partes involucradas en el negocio.
2. Modelo conceptual: Es donde se crea el modelo de entidad-relación. Eligiendo entidades, atributos y las relaciones entre entidades.
3. Modelo Lógico: En este modelo a las entidades se les asigna dentro de una misma tabla sus atributos y se crean tablas que permitan expresar las relaciones entre entidades. Las tablas que expresan relaciones suelen tener las foreign keys y las tablas de las entidades contienen primary keys.
4. Modelo Físico: En este modelo las tablas de entidades y las tablas de relaciones contienen los campos y el tipo de dato que engloban: entero, string, etc.

Para esta guía práctica propondremos un ejemplo de prueba con un caso hipotético de Amazon.

Entendimiento del negocio

Supongamos que Amazon busca crear una base de datos para organizar su catálogo de productos en la plataforma de comercio electrónico.

Cada producto tiene información única, como su ID de producto (productID), nombre (productName), fecha de lanzamiento (releaseDate), idioma de descripción (descriptionLanguage) y un enlace al producto (link).

Las categorías de productos se registran con un ID de categoría (categoryID) y un nombre de categoría (categoryName). Un producto puede pertenecer a varias categorías (por ejemplo, un dispositivo de tecnología puede pertenecer a “Electrónica” y “Ofertas”), y una categoría puede incluir múltiples productos.

En cuanto a los proveedores (Supplier), se registran con un ID de proveedor (supplierID), nombre (name) y país de origen (countryOfOrigin). Los productos pueden tener múltiples proveedores, y un proveedor puede distribuir varios productos, potencialmente en diferentes categorías.

En este caso se han identificado 3 entidades (producto, categoría y proveedores) y sus respectivos atributos.

Modelo conceptual

En este modelo buscamos identificar el tipo de relaciones entre entidades, esto es, si las conexio son de 1 a muchos (1:N) o de muchos a muchos (N:N). Esto se representa con un esquema gráfico donde las entidades se representan en rectángulos, los atributos en elipses y las relaciones con rombos. En nuestro caso selecto de Amazo quedaría de la siguiente manera.

Modelo Lógico

En este modelo el esquema gráfico anterior se transforma de tal modo que las entidades y relaciones se conviertan en tablas. Nuestras tablas creadas de relaciones deben contener foreign keys que permitan la conexión con las primary keys de nuestras entidades. Con nuestro ejemplo el resultado sería el siguiente:

Modelo Físico

Como resultado de este proceso, tendremos un conjunto de tablas (entidades y relaciones) con sus respetivos atributos que, además se les ha asignado un tipo de dato: int, string, float, etc. En este modelo también se resaltan quienes con las primary key y quienes son las foreign key.

Código para la creación de nuestro OLTP en nuestro ejemplo de Amazon

1. • Creamos la documentación correspondiente explicando el objetivo de nuestro script.

/*
 Nombre del archivo: tarea1_Amazon_OLTP.sql
 Autor: Adrián Alejandro Valencia Martínez
 Fecha de creación: 2024–11–10
 Propósito: Creamos la base de datos que refleja las transacciones de Amazon entre los proveedores, 
 el objeto y sus clasificaciones para ser promocionado dentro de un catálogo
 Versión: 1.0
 Notas: Transacción OLTP
*/

2.- Creamos la base de datos con el nombre sugerido “db_amazon_transact” y la fijamos con el uso de use.

create database db_amazon_transact;
use db_amazon_transact;

3.- Creación de las tablas de entidades y relaciones.

En nuestro caso tenemos:

3 entidades: Category, Product, Supplier.

Las primary keys correspondientes son

categoryID, productID, SupplierID

2 relaciones: Product_category, Arriva_date_product

Las foreign keys correspondientes son

Para Product_category: productID, categoryID

Para Arrival_date_product: productID, supplierID

3.1 Creación de y llenado de la tabla Product

 — Tabla: Product
 — Descripción: Contiene la información básica de los productos.
 — Campos importantes:
 — — productID: Identificador único del producto.
 — — productName: Nombre completo del producto.
 — — releadeDate: Fecha de lanzamiento.
 — — descriptionLanguage: Idioma en el que se describe.
 — — link: Link de acceso al producto en la página.
create table Product(
 productID varchar(8) primary key not null,
 productName varchar(100) not null,
 releaseDate DATE not null,
 descriptionLanguage varchar(20) default null,
 link varchar(100) default null 
);

#Insertamos valores en la tabla Product
INSERT INTO Product (productID, productName, releaseDate, descriptionLanguage, link)
VALUES 
 (‘P001’, ‘Smartphone X’, ‘2023–01–10’, ‘English’, ‘https://amazon.com/smartphoneX'),
 (‘P002’, ‘Laptop Pro’, ‘2022–08–05’, ‘English’, ‘https://amazon.com/laptopPro'),
 (‘P003’, ‘Wireless Earbuds’, ‘2023–05–15’, ‘English’, ‘https://amazon.com/wirelessEarbuds'),
 (‘P004’, ‘Smartwatch S’, ‘2023–03–12’, ‘Spanish’, ‘https://amazon.com/smartwatchS'),
 (‘P005’, ‘Tablet Z’, ‘2021–11–20’, ‘French’, ‘https://amazon.com/tabletZ'),
 (‘P006’, ‘Gaming Console’, ‘2023–02–18’, ‘German’, ‘https://amazon.com/gamingConsole'),
 (‘P007’, ‘Bluetooth Speaker’, ‘2020–07–30’, ‘English’, ‘https://amazon.com/bluetoothSpeaker'),
 (‘P008’, ‘4K Television’, ‘2019–09–15’, ‘Spanish’, ‘https://amazon.com/4KTelevision');

3.2 Creación y llenado de la tabla Supplier

/* Creamos la tabla de proveedores */
 — Tabla: Supplier
 — Descripción: Contiene información sobre los proveedores.
 — Campos importantes:
 — — supplierID: Identificador único del proveedor.
 — — supplierName: Nombre completo del proveedor
 — — CountryOfOrigin: Pais de Origen del proveedor.
create table Supplier(
 supplierID varchar(8) primary key not null,
 supplierName varchar(100),
 CountryOfOrigin varchar(20)
);

#Insertamos valores en la tabla de proveedores
INSERT INTO Supplier (supplierID, supplierName, countryOfOrigin)
VALUES 
 (‘S001’, ‘TechSupply Co.’, ‘USA’),
 (‘S002’, ‘ElectroWorld Ltd.’, ‘Germany’),
 (‘S003’, ‘Global Gadgets’, ‘China’),
 (‘S004’, ‘DigitalHub’, ‘Japan’),
 (‘S005’, ‘HomeTech Suppliers’, ‘Canada’),
 (‘S006’, ‘Smart Solutions’, ‘UK’),
 (‘S007’, ‘AudioMasters’, ‘South Korea’),
 (‘S008’, ‘Visionary Electronics’, ‘France’);

3.3 Creación y llenado de la tabla de relación Arriva_date_product que enlaza la entidad de producto (prodcut) con la de proveedor (supplier).

/*Creamos la tabla supplier_product para saber la fecha de llegada del producto */
/*Definimos las llaves foranea que conectan la tabla de productos y proveedores*/create table Arrival_date_product(
 productID varchar(8) not null,
 supplierID varchar(8) not null,
 Arrival_date date not null,

 CONSTRAINT fk_product_arrival foreign key (productID) references Product(productID),
 CONSTRAINT fk_product_supplier foreign key (supplierID) references Supplier(supplierID)
);

#Insertamos valores en la tabla de fecha de de llegada de productos
INSERT INTO Arrival_date_product (supplierID, productID, Arrival_date)
VALUES 
 (‘S001’, ‘P001’, ‘2023–02–01’),
 (‘S002’, ‘P002’, ‘2023–02–05’),
 (‘S003’, ‘P003’, ‘2023–03–12’),
 (‘S004’, ‘P004’, ‘2023–04–15’),
 (‘S005’, ‘P005’, ‘2023–05–20’),
 (‘S006’, ‘P006’, ‘2023–06–10’),
 (‘S007’, ‘P007’, ‘2023–07–30’),
 (‘S008’, ‘P008’, ‘2023–08–25’);

3.4 Creación y llenado de la tabla Category

drop table if exists Category;
create table Category(
 categoryID integer primary key not null,
 categoryName varchar(20),
 Descript varchar(100)
);

#Insertamos valores en la tabla de categoria
INSERT INTO Category (categoryID, categoryName, Descript)
VALUES 
 (1, ‘Electronics’, ‘Electronic products and gadgets for various uses’),
 (2, ‘Home Appliances’, ‘Household appliances and tools for everyday needs’),
 (3, ‘Gaming’, ‘Gaming consoles, accessories, and video games’),
 (4, ‘Health & Fitness’, ‘Products for health, wellness, and fitness activities’),
 (5, ‘Mobile Devices’, ‘Mobile phones, tablets, and related accessories’),
 (6, ‘Audio’, ‘Audio equipment, speakers, and headphones’),
 (7, ‘Computers’, ‘Computers, laptops, and accessories’),
 (8, ‘Televisions’, ‘Televisions, projectors, and viewing accessories’);

3.5 Creación y llenado de la tabla Product_category

/*Crea la tabla que asigna una categoria a los productos*/
/*Definimos las llaves foráneas para conectar la tabla de productos con sus categorias*/
drop table if exists Product_category ;
create table Product_category(
 productID varchar(8) primary key not null,
 categoryID integer,
 Descript varchar(255),

 CONSTRAINT fk_product_product foreign key (productID) references Product(productID),
 CONSTRAINT fk_product_category foreign key (categoryID) references Category(categoryID)
);

#Insertamos valores en la tabla product-category
INSERT INTO Product_category (productID, categoryID, Descript)
VALUES 
 (‘P001’, 1, ‘Smartphone in Electronics category’),
 (‘P002’, 7, ‘Laptop included in Computers category’),
 (‘P003’, 6, ‘Wireless earbuds in Audio category’),
 (‘P004’, 4, ‘Smartwatch under Health & Fitness’),
 (‘P005’, 5, ‘Tablet categorized as Mobile Device’),
 (‘P006’, 3, ‘Gaming Console in Gaming category’),
 (‘P007’, 6, ‘Bluetooth Speaker under Audio category’),
 (‘P008’, 8, ‘4K Television listed in Televisions’);

4.- El paso restante es leer cada tabla para asegurar que los registros se canalizaron adecuadamente.

/* Consultamos el contenido de cada tabla para verificar los valores */
SELECT * FROM db_amazon_transact.product;
SELECT * FROM db_amazon_transact.arrival_date_product;
SELECT * FROM db_amazon_transact.supplier;
SELECT * FROM db_amazon_transact.product_category;
SELECT * FROM db_amazon_transact.category;

Para leer de manera continua, el código, adjuntamos el script completo

[embed]

Conclusión

En este blog se detalló el proceso de diseño e implementación de una base de datos transaccional utilizando MySQL. A lo largo del mismo, se abordan los pasos esenciales para establecer una base de datos OLTP eficiente, desde la planificación inicial hasta la ejecución final haciendo uso de algnas operaciones básicas CRUD. Se detallaron los procesos de normalización que pemiten la formación de tablas útiles para bases de datos transaccionales y se fundamento el modelo de Entidad-Relación para integrar la creación de un OLTP. Después de ello, se realizó un ejemplo práctico de un caso hipotético de Amazon para implementar el: entendimiento del negocio; el modelo conceptual; el modelo lógico y el modelo físico de la base de datos. Estos recursos son valiosos para desarrolladores y administradores de bases de datos que buscan una guía práctica en la implementación de sistemas OLTP con MySQL.