Relaciones entre Tablas y Normalización en MySQL

Tipos de Relaciones en Bases de Datos

En el diseño de bases de datos relacionales, las interacciones entre tablas se clasifican principalmente en tres categorías: Uno a Uno (1:1), Uno a Muchos (1:N) y Muchos a Muchos (M:N).

Identificación de Relaciones

Para determinar qué tipo de relación existe entre dos entidades, se debe analizar la correspondencia de sus registros:

Uno a Muchos (1:N): Si un registro de la Tabla A se asocia con múltiples registros de la Tabla B, pero un registro de la Tabla B solo pertenece a un registro de la Tabla A. La resrticción de integridad referencial se aplica añadiendo una clave foránea (foreign key) en la tabla del lado "Muchos" que apunte a la clave primaria de la tabla del lado "Uno".
Uno a Uno (1:1): Si un registro de la Tabla A corresponde exactamente a un registro de la Tabla B y viceversa. Para implementar esto, se coloca una clave foránea en una de las tablas y se le añade una restricción de unicidad (UNIQUE) para garantizar la correspondencia biyectiva.
Muchos a Muchos (M:N): Si un registro de la Tabla A se relaciona con múltiples registros de la Tabla B, y un registro de la Tabla B también se relaciona con múltiples registros de la Tabla A. Esta relación no se puede implementar directamente con claves foráneas en las tablas originales; requiere la creación de una tabla intermedia (o tabla pivote) cuyas claves foráneas compongan una clave primaria conjunta.

Implementación de Relaciones

1. Relación Uno a Muchos

Ejemplo: Un propietario puede poseer múltiples vehículos, pero cada vehículo pertenece a un único propietario.

La solución consiste en mantener la tabla de propietarios intacta y añadir una clave foránea en la tabla de vehículos que referencie al propietario.

CREATE TABLE propietario (
    propietario_id INT PRIMARY KEY AUTO_INCREMENT,
    nombre_completo VARCHAR(100),
    edad INT,
    genero CHAR(1)
);

CREATE TABLE vehiculo (
    vehiculo_id INT PRIMARY KEY AUTO_INCREMENT,
    marca VARCHAR(50),
    precio DECIMAL(10,2),
    prop_id INT,
    CONSTRAINT fk_propietario_vehiculo FOREIGN KEY (prop_id) REFERENCES propietario(propietario_id)
);

2. Relación Uno a Uno

Ejemplo: Un cliente puede tener únicamente un perfil de credenciales de acceso.

Esta relación es una variación del Uno a Muchos. Se implementa añadiendo una clave foránea junto con una restricción UNIQUE en la tabla dependiente para asegurar que no se repitan las asociaciones.

CREATE TABLE credencial (
    credencial_id INT PRIMARY KEY AUTO_INCREMENT,
    codigo_hash VARCHAR(64) UNIQUE
);

CREATE TABLE cliente (
    cliente_id INT PRIMARY KEY AUTO_INCREMENT,
    alias_usuario VARCHAR(50),
    cred_id INT UNIQUE,
    CONSTRAINT fk_cliente_credencial FOREIGN KEY (cred_id) REFERENCES credencial(credencial_id)
);

3. Relación Muchos a Muchos

Ejemplo: Un empleado puede dominar múltiples habilidades, y cada habilidad puede ser dominada por múltiples empleados.

El enfoque correcto es desacoplar las entidades creando tres tablas: la tabla de empleados, la tabla de habilidades y una tabla intermedia que almacene la relación mediante una clave primaria compuesta.

CREATE TABLE empleado (
    emp_id INT PRIMARY KEY,
    nombre VARCHAR(50),
    antiguedad INT
);

CREATE TABLE habilidad (
    hab_id INT PRIMARY KEY,
    titulo VARCHAR(50),
    complejidad DECIMAL(3,1)
);

CREATE TABLE empleado_habilidad (
    emp_id INT,
    hab_id INT,
    PRIMARY KEY (emp_id, hab_id),
    CONSTRAINT fk_emp FOREIGN KEY (emp_id) REFERENCES empleado(emp_id),
    CONSTRAINT fk_hab FOREIGN KEY (hab_id) REFERENCES habilidad(hab_id)
);

Formas Normales en Bases de Datos

Primera Forma Normal (1FN)

Asegura que cada columna de una tabla contenga valores atómicos (indivisibles). Por ejemplo, un campo "dirección" que contenga ciudad, calle y código postal viola la 1FN si el sistema necesita consultar la ciudad de forma aislada. La solución es dividir la columna en múltiples campos atómicos.

Segunda Forma Normal (2FN)

Se cumple si la tabla está en 1FN y cada columna no primaria depende completamente de la clave primaria, no solo de una parte de ella (aplicible a claves primarias compuestas). Una tabla no debe mezclar atributos de diferentes entidades. Si se tiene una tabla de detalles de pedido con una clave compuesta (pedido_id, producto_id), los atributos del proudcto (como su nombre o precio base) no dependen completamente de la clave, por lo que deben separarse en su propia tabla.

Tercera Forma Normal (3FN)

Se cumple si la tabla está en 2FN y sus columnas no primarias dependen directamente de la clave primaria, sin dependencias transitivas (una columna no primaria no puede depender de otra columna no primaria). Por ejemplo, en una tabla de ventas, si se incluye el identificador del cliente y su nombre, el nombre depende del identificador del cliente y no directamente de la venta, generando redundancia. El nombre debe almacenarse exclusivamente en la tabla de clientes.

Casos Prácticos de Consultas SQL

Para los siguientes ejercicios, utilizaremos el siguiente esquema de base de datos sobre certificaciones tecnológicas:

CREATE TABLE organizacion (
  org_id INT PRIMARY KEY,
  org_nombre VARCHAR(50)
);

CREATE TABLE tecnologia (
  tech_id INT PRIMARY KEY,
  tech_nombre VARCHAR(50),
  org_id INT,
  FOREIGN KEY (org_id) REFERENCES organizacion(org_id)
);

CREATE TABLE desarrollador (
  dev_id INT PRIMARY KEY,
  dev_nombre VARCHAR(50),
  dev_edad INT,
  dev_genero CHAR(1)
);

CREATE TABLE certificacion (
  cert_id INT PRIMARY KEY AUTO_INCREMENT,
  dev_id INT,
  tech_id INT,
  puntaje DECIMAL(5,2),
  FOREIGN KEY (dev_id) REFERENCES desarrollador(dev_id),
  FOREIGN KEY (tech_id) REFERENCES tecnologia(tech_id)
);

1. Desarrolladores con mejor puntaje en Python que en Java

Mediante la combinación de la tabla de certificaciones con la de tecnologías, se aíslan los puntajes de ambos lenguajes y se comparan para un mismo desarrollador.

SELECT d.dev_id, d.dev_nombre
FROM desarrollador d
JOIN certificacion c1 ON d.dev_id = c1.dev_id
JOIN tecnologia t1 ON c1.tech_id = t1.tech_id AND t1.tech_nombre = 'Python'
JOIN certificacion c2 ON d.dev_id = c2.dev_id
JOIN tecnologia t2 ON c2.tech_id = t2.tech_id AND t2.tech_nombre = 'Java'
WHERE c1.puntaje > c2.puntaje;

2. Desarrolladores con puntaje promedio superior a 65

Se agrupan las certificaciones por desarrollador y se filtra utilizando la cláusula HAVING sobre el promedio.

SELECT d.dev_nombre, ROUND(AVG(c.puntaje), 2) AS promedio
FROM desarrollador d
JOIN certificacion c ON d.dev_id = c.dev_id
GROUP BY d.dev_id
HAVING AVG(c.puntaje) > 65;

3. Nombre de desarrolladores, cantidad de certificaciones y puntaje total

SELECT d.dev_nombre, COUNT(c.tech_id) AS total_certs, SUM(c.puntaje) AS puntaje_total
FROM desarrollador d
JOIN certificacion c ON d.dev_id = c.dev_id
GROUP BY d.dev_id;

4. Nombre de cada tecnología y su organización evaluadora

SELECT t.tech_nombre, o.org_nombre
FROM tecnologia t
JOIN organizacion o ON t.org_id = o.org_id;

5. Desarrolladores que no han sido evaluados por la organización "DevHub"

Se buscan las tecnologías de "DevHub", luego los desarrolladores que las tienen, y finalmente se excluyen dichos desarrolladores.

SELECT dev_nombre
FROM desarrollador
WHERE dev_id NOT IN (
    SELECT DISTINCT c.dev_id
    FROM certificacion c
    JOIN tecnologia t ON c.tech_id = t.tech_id
    JOIN organizacion o ON t.org_id = o.org_id
    WHERE o.org_nombre = 'DevHub'
);

6. Desarrolladores certificados tanto en Python como en Java

Filtramos las certificaciones por los nombres de las tecnologías, agrupamos por desarrollador y verificamos que el conteo de lenguajes distintos sea al menos 2.

SELECT d.dev_nombre
FROM desarrollador d
JOIN certificacion c ON d.dev_id = c.dev_id
JOIN tecnologia t ON c.tech_id = t.tech_id
WHERE t.tech_nombre IN ('Python', 'Java')
GROUP BY d.dev_id
HAVING COUNT(DISTINCT t.tech_id) = 2;

7. Desarrolladores con dos o más certificaciones reprobadas (puntaje < 70)

SELECT d.dev_nombre
FROM desarrollador d
JOIN certificacion c ON d.dev_id = c.dev_id
WHERE c.puntaje < 70
GROUP BY d.dev_id
HAVING COUNT(c.cert_id) >= 2;

8. Desarrolladores que han obtenido certificaciones en todas las tecnologías disponibles

Se compara la cantidad de tecnologías distintas en las que un desarrollador tiene certificación contra el total de tecnologías registradas en el sistema.

SELECT d.dev_nombre
FROM desarrollador d
JOIN certificacion c ON d.dev_id = c.dev_id
GROUP BY d.dev_id
HAVING COUNT(DISTINCT c.tech_id) = (SELECT COUNT(*) FROM tecnologia);

9. Desarrolladores que comparten al menos una tecnología certificada con "Carlos"

Se obtienen los identificadores de las tecnologías de Carlos y se buscan otros desarrolladores que las tengan.

SELECT DISTINCT d.dev_nombre
FROM desarrollador d
JOIN certificacion c ON d.dev_id = c.dev_id
WHERE c.tech_id IN (
    SELECT c2.tech_id
    FROM certificacion c2
    JOIN desarrollador d2 ON c2.dev_id = d2.dev_id
    WHERE d2.dev_nombre = 'Carlos'
)
AND d.dev_nombre != 'Carlos';

Etiquetas: MySQL SQL Normalización BasesDeDatos ClaveForánea

Publicado el 10-15 18:33

Friki Work