Tutorial SQL: Bases de datos y tablas

Estructura relacional

Las bases de datos relacionales constituyen uno de los sistemas de almacenamiento de información más utilizados en el mundo. Su fundamento se basa en un modelo matemático que organiza los datos en estructuras bien definidas que se relacionan entre sí de manera lógica.

El modelo relacional, introducido por Edgar F. Codd en 1970, revolucionó la forma en que almacenamos y accedemos a la información. Este modelo organiza los datos en colecciones de tablas (formalmente llamadas "relaciones"), donde cada tabla representa una entidad o concepto del mundo real.

Componentes fundamentales

La estructura relacional se compone de varios elementos esenciales:

• Bases de datos: Es el contenedor principal que agrupa todas las tablas relacionadas con un sistema o aplicación específica. Por ejemplo, una base de datos llamada "biblioteca" podría contener toda la información necesaria para gestionar una biblioteca.

• Esquemas: Actúan como contenedores lógicos dentro de una base de datos que agrupan objetos relacionados (tablas, vistas, procedimientos). En MySQL, un esquema es equivalente a una base de datos, mientras que en PostgreSQL, una base de datos puede contener múltiples esquemas.

• Tablas: Son las estructuras fundamentales donde se almacenan los datos. Cada tabla representa una entidad específica (como "libros", "usuarios" o "préstamos").

• Columnas: Definen los atributos o características de cada entidad. Por ejemplo, una tabla "usuarios" podría tener columnas como "id", "nombre", "email" y "fecha_registro".

• Filas: También llamadas registros o tuplas, contienen los datos concretos de cada instancia de la entidad. Cada fila representa un elemento individual dentro de la tabla.

Organización de la información

La estructura relacional organiza la información siguiendo principios específicos:

Base de datos
  └── Esquema (en PostgreSQL, en MySQL es equivalente a la base de datos)
       └── Tabla 1
       │    ├── Columna 1
       │    ├── Columna 2
       │    └── ...
       └── Tabla 2
            ├── Columna 1
            ├── Columna 2
            └── ...

Esta organización jerárquica permite mantener los datos ordenados y accesibles. Veamos un ejemplo práctico:

Base de datos: tienda_online
  └── Esquema: public
       └── Tabla: productos
       │    ├── id (número entero)
       │    ├── nombre (texto)
       │    ├── precio (decimal)
       │    └── categoria_id (número entero)
       └── Tabla: categorias
            ├── id (número entero)
            ├── nombre (texto)
            └── descripcion (texto)

Integridad de los datos

Un aspecto fundamental del modelo relacional es la integridad de los datos, que se mantiene mediante reglas específicas:

• Integridad de entidad: Cada tabla debe tener una columna o conjunto de columnas que identifique de manera única cada fila (clave primaria).

• Integridad referencial: Cuando una tabla hace referencia a datos de otra tabla, debe existir una correspondencia válida entre ambas (mediante claves foráneas).

• Integridad de dominio: Los valores en cada columna deben cumplir con el tipo de dato definido y cualquier restricción adicional.

Normalización

La normalización es un proceso fundamental en el diseño de bases de datos relacionales que busca eliminar la redundancia y dependencias anómalas. Este proceso se divide en varias formas normales (1FN, 2FN, 3FN, etc.), cada una con reglas específicas para estructurar los datos de manera eficiente.

Por ejemplo, en lugar de tener una única tabla con información repetida:

Tabla: pedidos_productos
├── pedido_id
├── cliente_nombre
├── cliente_email
├── producto_id
├── producto_nombre
├── producto_precio
└── cantidad

La normalización nos llevaría a dividirla en tablas relacionadas:

Tabla: clientes
├── id
├── nombre
└── email

Tabla: productos
├── id
├── nombre
└── precio

Tabla: pedidos
├── id
├── cliente_id
└── fecha

Tabla: detalles_pedido
├── pedido_id
├── producto_id
└── cantidad

Ventajas de la estructura relacional

El modelo relacional ofrece numerosas ventajas que explican su popularidad:

• Simplicidad conceptual: La representación tabular es intuitiva y fácil de entender.
• Independencia física: Los cambios en el almacenamiento físico no afectan a la estructura lógica.
• Independencia lógica: Las aplicaciones no necesitan modificarse cuando se realizan cambios en la estructura de las tablas.
• Integridad de datos: Las reglas de integridad ayudan a mantener la consistencia de la información.
• Flexibilidad: Permite realizar consultas complejas y obtener información de múltiples tablas.
• Seguridad: Facilita la implementación de controles de acceso a nivel de tabla o columna.

Implementación en MySQL y PostgreSQL

Tanto MySQL como PostgreSQL implementan el modelo relacional, aunque con algunas diferencias en terminología y características:

• En MySQL, los términos "base de datos" y "esquema" se utilizan indistintamente. Cuando creas una base de datos, estás creando un esquema.

CREATE DATABASE biblioteca;
USE biblioteca;

• En PostgreSQL, una base de datos puede contener múltiples esquemas, lo que permite una organización más detallada:

CREATE DATABASE biblioteca;
\c biblioteca
CREATE SCHEMA catalogos;
CREATE SCHEMA usuarios;

A pesar de estas diferencias, ambos sistemas siguen los principios fundamentales del modelo relacional, permitiendo crear estructuras de datos robustas y eficientes.

Consideraciones de diseño

Al diseñar una estructura relacional, es importante considerar:

• Identificar entidades: Determinar qué objetos del mundo real necesitan ser representados.
• Definir atributos: Establecer qué características de cada entidad son relevantes.
• Establecer relaciones: Determinar cómo se conectan las diferentes entidades.
• Aplicar normalización: Eliminar redundancias y dependencias problemáticas.
• Optimizar para consultas: Considerar cómo se accederá a los datos más frecuentemente.

La estructura relacional proporciona un marco sólido para organizar datos de manera lógica y eficiente, facilitando su almacenamiento, recuperación y manipulación mediante el lenguaje SQL.

Anatomía de tablas

Las tablas son el componente fundamental de las bases de datos relacionales, funcionando como contenedores estructurados donde se almacena la información de manera organizada. Cada tabla representa una entidad específica del mundo real, como clientes, productos o transacciones.

Estructura física de una tabla

Una tabla se compone de elementos básicos que definen su estructura:

• Nombre de tabla: Identificador único que distingue a la tabla dentro de la base de datos o esquema. En MySQL y PostgreSQL, los nombres de tabla suelen seguir convenciones como usar minúsculas y guiones bajos para separar palabras (ejemplo: productos_electronicos).

• Columnas: También llamadas campos o atributos, definen las propiedades específicas que se almacenarán para cada registro. Cada columna tiene:

• Un nombre único dentro de la tabla

• Un tipo de dato específico

• Restricciones opcionales

• Filas: También conocidas como registros o tuplas, contienen los valores concretos para cada columna. Cada fila representa una instancia individual de la entidad que modela la tabla.

Visualmente, una tabla se representa como una estructura bidimensional:

Tabla: empleados
┌────┬───────────┬────────────┬──────────┬────────────────┐
│ id │  nombre   │  apellido  │  cargo   │ fecha_contrato │
├────┼───────────┼────────────┼──────────┼────────────────┤
│ 1  │ Ana       │ García     │ Analista │ 2022-03-15     │
│ 2  │ Carlos    │ Rodríguez  │ Gerente  │ 2020-11-05     │
│ 3  │ Elena     │ Martínez   │ Técnico  │ 2023-01-20     │
└────┴───────────┴────────────┴──────────┴────────────────┘

Definición de columnas

Cada columna en una tabla tiene características específicas:

• Nombre: Identificador único dentro de la tabla que describe el dato almacenado. Debe seguir las reglas de nomenclatura del sistema gestor de base de datos.

• Tipo de dato: Define qué clase de información puede almacenarse en la columna. Los tipos comunes incluyen:

• Numéricos: INTEGER, DECIMAL, FLOAT

• Texto: VARCHAR, TEXT, CHAR

• Fecha/hora: DATE, TIME, TIMESTAMP

• Booleanos: BOOLEAN

• Binarios: BLOB, BYTEA (PostgreSQL)

• Tamaño/precisión: Para algunos tipos de datos, se puede especificar un tamaño máximo (como VARCHAR(50)) o precisión (como DECIMAL(10,2)).

• Valor predeterminado: Valor que se asigna automáticamente cuando se inserta un nuevo registro sin especificar un valor para esa columna.

• Restricciones: Reglas que limitan qué datos pueden almacenarse en la columna:

• NOT NULL: La columna no puede contener valores nulos

• UNIQUE: Todos los valores en la columna deben ser únicos

• CHECK: Los valores deben cumplir una condición específica

Metadatos de tabla

Además de los datos almacenados, cada tabla contiene metadatos que describen su estructura y características:

• Nombre del esquema: Indica a qué esquema pertenece la tabla
• Propietario: Usuario que creó la tabla y tiene permisos completos sobre ella
• Espacio de tabla: Ubicación física donde se almacenan los datos (más relevante en PostgreSQL)
• Comentarios: Descripciones opcionales que documentan el propósito de la tabla o sus columnas
• Estadísticas: Información sobre el tamaño y distribución de datos (usada por el optimizador de consultas)

En PostgreSQL, puedes consultar estos metadatos con:

SELECT * FROM information_schema.tables WHERE table_name = 'empleados';
SELECT * FROM information_schema.columns WHERE table_name = 'empleados';

En MySQL:

SHOW TABLE STATUS WHERE Name = 'empleados';
SHOW FULL COLUMNS FROM empleados;

Almacenamiento físico

A nivel físico, las tablas se almacenan de manera diferente según el sistema gestor:

• MySQL: Utiliza diferentes motores de almacenamiento para las tablas:

• InnoDB: Motor predeterminado que soporta transacciones, claves foráneas y bloqueo a nivel de fila

• MyISAM: Motor más antiguo optimizado para lecturas pero sin soporte para transacciones

• MEMORY: Almacena datos en memoria para acceso ultrarrápido

• PostgreSQL: Utiliza un único motor de almacenamiento con un sistema de tablas heap y un sofisticado sistema de control de versiones de filas (MVCC).

Propiedades avanzadas de tablas

Las tablas pueden tener propiedades adicionales que afectan su comportamiento:

• Collation: Define cómo se comparan y ordenan los datos de texto (importante para ordenación y búsquedas sensibles a mayúsculas/minúsculas)

• Particionamiento: División de tablas grandes en segmentos más pequeños basados en rangos de valores, listas o hash de columnas específicas

• Compresión: Algunas tablas pueden configurarse para comprimir datos y reducir el espacio de almacenamiento

• Tablas temporales: Existen solo durante la sesión actual o transacción y se eliminan automáticamente al finalizar

Estructura de una tabla en SQL

La definición de una tabla en SQL muestra claramente su anatomía:

CREATE TABLE productos (
    id INTEGER PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    descripcion TEXT,
    precio DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    categoria_id INTEGER,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    disponible BOOLEAN DEFAULT TRUE,
    CONSTRAINT precio_positivo CHECK (precio >= 0)
);

Esta definición ilustra:

• Nombre de la tabla: productos
• Columnas con sus tipos de datos y restricciones
• Valor predeterminado para algunas columnas
• Restricción CHECK para validar datos

Visualización de la estructura

Para examinar la estructura de una tabla existente:

En MySQL:

DESCRIBE productos;

Resultado:

+---------------+---------------+------+-----+-------------------+-------+
| Field         | Type          | Null | Key | Default           | Extra |
+---------------+---------------+------+-----+-------------------+-------+
| id            | int           | NO   | PRI | NULL              |       |
| nombre        | varchar(100)  | NO   |     | NULL              |       |
| descripcion   | text          | YES  |     | NULL              |       |
| precio        | decimal(10,2) | NO   |     | 0.00              |       |
| categoria_id  | int           | YES  |     | NULL              |       |
| fecha_creacion| timestamp     | YES  |     | CURRENT_TIMESTAMP |       |
| disponible    | tinyint(1)    | YES  |     | 1                 |       |
+---------------+---------------+------+-----+-------------------+-------+

En PostgreSQL:

\d productos

Espacio y rendimiento

La anatomía de una tabla afecta directamente su rendimiento:

• Tamaño de fila: La suma del espacio requerido por todas las columnas determina el tamaño base de cada fila
• Factor de llenado: Porcentaje de espacio utilizado en cada página de datos (ajustable en PostgreSQL)
• Sobrecarga de almacenamiento: Metadatos adicionales almacenados con cada fila
• Alineación de datos: Los tipos de datos se alinean en límites específicos, lo que puede introducir relleno

La elección de tipos de datos apropiados es crucial para optimizar el almacenamiento:

-- Menos eficiente: usa más espacio del necesario
CREATE TABLE clientes_ineficiente (
    id BIGINT,               -- 8 bytes (excesivo para pocos clientes)
    nombre VARCHAR(255),     -- Espacio excesivo si los nombres son cortos
    activo CHAR(1)           -- Menos eficiente que BOOLEAN
);

-- Más eficiente: ajustado a las necesidades reales
CREATE TABLE clientes_eficiente (
    id INTEGER,              -- 4 bytes, suficiente para millones de registros
    nombre VARCHAR(50),      -- Tamaño más realista
    activo BOOLEAN           -- 1 byte
);

Límites prácticos

Las tablas tienen limitaciones prácticas que varían según el sistema:

• MySQL (InnoDB):

• Máximo 1017 columnas por tabla

• Tamaño máximo de fila: 65,535 bytes

• Tamaño máximo de tabla: 64TB

• PostgreSQL:

• Máximo 1600 columnas por tabla

• Tamaño máximo de tabla: ilimitado teóricamente (limitado por almacenamiento)

• Tamaño máximo de fila: 1.6TB

Estas limitaciones raramente son alcanzadas en aplicaciones típicas, pero es importante considerarlas en sistemas de gran escala.

La anatomía de las tablas constituye el fundamento sobre el cual se construyen las bases de datos relacionales, y su correcta comprensión es esencial para diseñar estructuras de datos eficientes y efectivas.

Claves y relaciones

Las bases de datos relacionales fundamentan su potencia en la capacidad de establecer conexiones lógicas entre diferentes tablas. Estas conexiones se implementan mediante un sistema de claves que permiten identificar registros de manera única y establecer vínculos entre ellos.

Tipos de claves

En el diseño de bases de datos relacionales existen varios tipos de claves, cada una con funciones específicas:

• Clave primaria (Primary Key): Identifica de manera única cada registro dentro de una tabla. No puede contener valores nulos y debe ser única para cada fila. Es el tipo de clave más fundamental en una base de datos relacional.

CREATE TABLE empleados (
    empleado_id INT PRIMARY KEY,
    nombre VARCHAR(50),
    apellido VARCHAR(50),
    email VARCHAR(100)
);

• Clave candidata: Cualquier columna o conjunto de columnas que podría servir como clave primaria, ya que cumple con los requisitos de unicidad y no nulidad. De todas las claves candidatas, se elige una como clave primaria.

• Clave compuesta: Una clave primaria formada por dos o más columnas combinadas. Se utiliza cuando ninguna columna individual puede garantizar la unicidad por sí sola.

CREATE TABLE matriculas (
    curso_id INT,
    estudiante_id INT,
    fecha_matricula DATE,
    PRIMARY KEY (curso_id, estudiante_id)
);

• Clave foránea (Foreign Key): Establece una relación entre dos tablas al referenciar la clave primaria de otra tabla. Garantiza la integridad referencial, asegurando que los valores en la columna de referencia existan en la tabla referenciada.

CREATE TABLE pedidos (
    pedido_id INT PRIMARY KEY,
    cliente_id INT,
    fecha_pedido DATE,
    FOREIGN KEY (cliente_id) REFERENCES clientes(cliente_id)
);

• Clave única (Unique Key): Similar a la clave primaria, garantiza que todos los valores en una columna o conjunto de columnas sean únicos, pero permite valores nulos (a menos que se especifique lo contrario).

CREATE TABLE usuarios (
    usuario_id INT PRIMARY KEY,
    nombre_usuario VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

• Clave natural vs. clave subrogada: Una clave natural utiliza atributos existentes del mundo real (como número de documento), mientras que una clave subrogada es un identificador artificial (como un número autoincremental) sin significado en el mundo real.

Implementación de claves primarias

Las claves primarias pueden implementarse de diferentes maneras:

• Autoincremento: El valor se genera automáticamente al insertar un nuevo registro. Es la opción más común para claves subrogadas.

En MySQL:

CREATE TABLE productos (
    producto_id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100),
    precio DECIMAL(10,2)
);

En PostgreSQL:

CREATE TABLE productos (
    producto_id SERIAL PRIMARY KEY,
    nombre VARCHAR(100),
    precio DECIMAL(10,2)
);

• UUID (Identificador Único Universal): Genera identificadores únicos globalmente, útiles en sistemas distribuidos.

En PostgreSQL:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE documentos (
    documento_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    titulo VARCHAR(200),
    contenido TEXT
);

Relaciones entre tablas

Las relaciones entre tablas definen cómo se conectan los datos entre diferentes entidades. Existen tres tipos principales:

• Relación uno a uno (1:1): Cada registro en la tabla A se relaciona con exactamente un registro en la tabla B, y viceversa. Este tipo de relación es menos común.

CREATE TABLE empleados (
    empleado_id INT PRIMARY KEY,
    nombre VARCHAR(100),
    apellido VARCHAR(100)
);

CREATE TABLE datos_contacto (
    empleado_id INT PRIMARY KEY,
    telefono VARCHAR(20),
    direccion VARCHAR(200),
    FOREIGN KEY (empleado_id) REFERENCES empleados(empleado_id)
);

• Relación uno a muchos (1:N): Cada registro en la tabla A puede relacionarse con múltiples registros en la tabla B, pero cada registro en B se relaciona con exactamente un registro en A. Es el tipo de relación más común.

CREATE TABLE departamentos (
    departamento_id INT PRIMARY KEY,
    nombre VARCHAR(100)
);

CREATE TABLE empleados (
    empleado_id INT PRIMARY KEY,
    nombre VARCHAR(100),
    departamento_id INT,
    FOREIGN KEY (departamento_id) REFERENCES departamentos(departamento_id)
);

• Relación muchos a muchos (N:M): Cada registro en la tabla A puede relacionarse con múltiples registros en la tabla B, y viceversa. Este tipo de relación requiere una tabla intermedia (también llamada tabla de unión o tabla pivote).

CREATE TABLE estudiantes (
    estudiante_id INT PRIMARY KEY,
    nombre VARCHAR(100)
);

CREATE TABLE cursos (
    curso_id INT PRIMARY KEY,
    nombre VARCHAR(100)
);

CREATE TABLE inscripciones (
    estudiante_id INT,
    curso_id INT,
    fecha_inscripcion DATE,
    PRIMARY KEY (estudiante_id, curso_id),
    FOREIGN KEY (estudiante_id) REFERENCES estudiantes(estudiante_id),
    FOREIGN KEY (curso_id) REFERENCES cursos(curso_id)
);

Integridad referencial

La integridad referencial garantiza que las relaciones entre tablas permanezcan válidas. Cuando se define una clave foránea, se pueden especificar acciones que ocurrirán cuando se intente modificar o eliminar un registro referenciado:

• RESTRICT: Impide la eliminación o modificación si existen registros relacionados.
• CASCADE: Propaga la eliminación o modificación a los registros relacionados.
• SET NULL: Establece como NULL los valores de la clave foránea en los registros relacionados.
• SET DEFAULT: Establece el valor predeterminado en la clave foránea de los registros relacionados.

CREATE TABLE pedidos (
    pedido_id INT PRIMARY KEY,
    cliente_id INT,
    fecha_pedido DATE,
    FOREIGN KEY (cliente_id) 
        REFERENCES clientes(cliente_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Índices y rendimiento

Las claves primarias y foráneas generan automáticamente índices en las columnas correspondientes, lo que mejora significativamente el rendimiento de las consultas que utilizan estas columnas para búsquedas, uniones o filtros.

-- Consulta que se beneficia del índice de clave primaria
SELECT * FROM productos WHERE producto_id = 1234;

-- Consulta que se beneficia del índice de clave foránea
SELECT p.* FROM pedidos p
JOIN clientes c ON p.cliente_id = c.cliente_id
WHERE c.cliente_id = 5678;

Restricciones de integridad

Además de las claves, existen otras restricciones que ayudan a mantener la integridad de los datos:

• NOT NULL: Garantiza que una columna no contenga valores nulos.
• CHECK: Verifica que los valores cumplan una condición específica.
• DEFAULT: Asigna un valor predeterminado cuando no se especifica uno.

CREATE TABLE productos (
    producto_id INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    precio DECIMAL(10,2) NOT NULL CHECK (precio > 0),
    stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

Visualización de relaciones

Para visualizar las claves y relaciones existentes en una base de datos:

En MySQL:

-- Ver claves foráneas de una tabla
SELECT * FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'pedidos' AND REFERENCED_TABLE_NAME IS NOT NULL;

En PostgreSQL:

-- Ver claves foráneas de una tabla
SELECT
    tc.constraint_name, 
    tc.table_name, 
    kcu.column_name, 
    ccu.table_name AS referenced_table,
    ccu.column_name AS referenced_column
FROM 
    information_schema.table_constraints AS tc 
JOIN 
    information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN 
    information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE 
    tc.constraint_type = 'FOREIGN KEY' AND tc.table_name = 'pedidos';

Consideraciones de diseño

Al diseñar claves y relaciones, es importante considerar:

• Elección de clave primaria: Preferir claves subrogadas (como IDs autoincrementales) sobre claves naturales para mayor flexibilidad.
• Granularidad de relaciones: Determinar el nivel adecuado de normalización para evitar redundancia sin sacrificar rendimiento.
• Cascada de operaciones: Configurar cuidadosamente las acciones ON DELETE y ON UPDATE para mantener la integridad de datos.
• Índices adicionales: Además de los índices automáticos en claves, considerar índices adicionales para columnas frecuentemente consultadas.

Las claves y relaciones son el fundamento que permite a las bases de datos relacionales modelar eficazmente estructuras de datos complejas, manteniendo la integridad y permitiendo consultas eficientes entre múltiples tablas.