Proceso de Diseño básico de BD

Introducción

El diseño de bases de datos es un proceso fundamental en el desarrollo de sistemas de información. Según estudios, el 70% de los problemas de rendimiento en aplicaciones se originan en un diseño deficiente de la base de datos.

Conceptos Básicos

Figura 1: Ejemplo de diagrama E-R para un sistema de ventas 1

Diagrama que muestra las entidades con relaciones

Figura 2: Ejemplo de diagrama E-R para un sistema de ventas 2

Diagrama que muestra las entidades con relaciones

Características de un Diseño Óptimo

Actividad Completa: Diseño de BD para Hospital

Paso 1: Requisitos del Sistema

Necesitamos almacenar información sobre:

• Pacientes (historial médico)
• Doctores (especialidades)
• Citas médicas
• Medicamentos recetados

Paso 2: Diagrama E-R para sistema hospitalario

Modelo con entidades Paciente, Doctor, Cita y Medicamento

Paso 3: Transformación a Esquema Relacional

-- Tabla Pacientes
CREATE TABLE Pacientes (
    id_paciente INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    fecha_nacimiento DATE,
    tipo_sangre VARCHAR(3)
);

-- Tabla Doctores
CREATE TABLE Doctores (
    id_doctor INT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    especialidad VARCHAR(50),
    telefono VARCHAR(15)
);

-- Tabla Citas
CREATE TABLE Citas (
    id_cita INT PRIMARY KEY,
    id_paciente INT,
    id_doctor INT,
    fecha_hora DATETIME,
    motivo VARCHAR(200),
    FOREIGN KEY (id_paciente) REFERENCES Pacientes(id_paciente),
    FOREIGN KEY (id_doctor) REFERENCES Doctores(id_doctor)
);
.......
.......
.......

Paso 4: Normalización

Modelo Estrella (Data Warehouse)

Estructura para análisis con tablas de hechos y dimensiones

Diferencias en estructura y escalabilidad

Resumen Final

Introducción

La normalización es un proceso sistemático para organizar datos en una base de datos relacional, con dos objetivos principales:

Conceptos Fundamentales

Jerarquía de Formas Normales

Formas Normales en Profundidad

Actividad Práctica: Normalización Paso a Paso

Paso 1: Tabla No Normalizada

CREATE TABLE Pedidos (
    id_pedido INT,
    fecha DATE,
    id_cliente INT,
    nombre_cliente VARCHAR(100),
    direccion_cliente VARCHAR(200),
    id_producto INT,
    nombre_producto VARCHAR(100),
    categoria_producto VARCHAR(50),
    precio DECIMAL(10,2),
    cantidad INT
);

🔴 Problemas detectados: Redundancia de datos, atributos no atómicos, dependencias parciales.

Paso 2: Aplicar 1FN

-- Separar direcciones compuestas
CREATE TABLE Pedidos_1FN (
    id_pedido INT,
    fecha DATE,
    id_cliente INT,
    nombre_cliente VARCHAR(100),
    calle_cliente VARCHAR(100),
    ciudad_cliente VARCHAR(50),
    cp_cliente VARCHAR(10),
    -- ... otros campos
);

Paso 3: Aplicar 2FN

-- Crear tablas separadas
CREATE TABLE Clientes (
    id_cliente INT PRIMARY KEY,
    nombre VARCHAR(100),
    calle VARCHAR(100),
    ciudad VARCHAR(50),
    cp VARCHAR(10)
);

CREATE TABLE Productos (
    id_producto INT PRIMARY KEY,
    nombre VARCHAR(100),
    categoria VARCHAR(50),
    precio DECIMAL(10,2)
);

CREATE TABLE Pedidos_2FN (
    id_pedido INT PRIMARY KEY,
    fecha DATE,
    id_cliente INT,
    FOREIGN KEY (id_cliente) REFERENCES Clientes(id_cliente)
);

Paso 4: Aplicar 3FN

-- Eliminar dependencia transitiva categoría→precio
CREATE TABLE Categorias (
    id_categoria INT PRIMARY KEY,
    nombre VARCHAR(50),
    margen_ganancia DECIMAL(5,2)
);

ALTER TABLE Productos
ADD COLUMN id_categoria INT,
ADD FOREIGN KEY (id_categoria) REFERENCES Categorias(id_categoria);

Resultado Final Normalizado

Resumen y Buenas Prácticas

Ampliación: Formas Normales Avanzadas

Evolución del Proceso

1FN: Primera Forma Normal

-- Estructura 1FN
CREATE TABLE Estudiantes (
    ID_Estudiante VARCHAR(10) PRIMARY KEY,
    Nombre VARCHAR(100)
);

CREATE TABLE Telefonos (
    ID_Estudiante VARCHAR(10),
    Telefono VARCHAR(15),
    PRIMARY KEY (ID_Estudiante, Telefono),
    FOREIGN KEY (ID_Estudiante) REFERENCES Estudiantes(ID_Estudiante)
);

CREATE TABLE Calificaciones (
    ID_Estudiante VARCHAR(10),
    Curso VARCHAR(50),
    Nota DECIMAL(3,1),
    PRIMARY KEY (ID_Estudiante, Curso),
    FOREIGN KEY (ID_Estudiante) REFERENCES Estudiantes(ID_Estudiante)
);

2FN: Segunda Forma Normal

-- Estructura 2FN
CREATE TABLE Pedidos (
    ID_Pedido VARCHAR(10) PRIMARY KEY,
    Fecha DATE
);

CREATE TABLE Productos (
    ID_Producto VARCHAR(10) PRIMARY KEY,
    Nombre VARCHAR(100),
    Categoría VARCHAR(50)
);

CREATE TABLE Detalle_Pedidos (
    ID_Pedido VARCHAR(10),
    ID_Producto VARCHAR(10),
    Cantidad INT,
    PRIMARY KEY (ID_Pedido, ID_Producto),
    FOREIGN KEY (ID_Pedido) REFERENCES Pedidos(ID_Pedido),
    FOREIGN KEY (ID_Producto) REFERENCES Productos(ID_Producto)
);

3FN: Tercera Forma Normal

-- Estructura 3FN
CREATE TABLE Jefes (
    ID_Jefe VARCHAR(10) PRIMARY KEY,
    Nombre VARCHAR(100),
    Teléfono VARCHAR(15)
);

CREATE TABLE Departamentos (
    ID_Departamento VARCHAR(10) PRIMARY KEY,
    Nombre VARCHAR(50),
    ID_Jefe VARCHAR(10),
    FOREIGN KEY (ID_Jefe) REFERENCES Jefes(ID_Jefe)
);

CREATE TABLE Empleados (
    ID_Empleado VARCHAR(10) PRIMARY KEY,
    Nombre VARCHAR(100),
    ID_Departamento VARCHAR(10),
    FOREIGN KEY (ID_Departamento) REFERENCES Departamentos(ID_Departamento)
);

Resumen Comparativo

Contexto: Sistema de Biblioteca

SELECT 
  l.titulo, 
  a.nombre AS autor, 
  e.nombre AS editorial,
  COUNT(ej.id) AS copias_disponibles
FROM libros l
JOIN autores a ON l.autor_id = a.id
JOIN editoriales e ON l.editorial_id = e.id
LEFT JOIN ejemplares ej ON l.id = ej.libro_id 
WHERE ej.disponible = true
GROUP BY l.id, a.nombre, e.nombre;

Ejercicio: Aplicar a Sistema de Pedidos

-- Solución Propuesta
CREATE TABLE reporte_pedidos (
  pedido_id INT PRIMARY KEY,
  fecha DATE,
  cliente_id INT,
  cliente_nombre VARCHAR(100),
  total_productos INT,
  monto_total DECIMAL(10,2),
  ultima_actualizacion TIMESTAMP
);

-- Actualización con:
-- 1. Trigger al crear pedido
-- 2. Job nocturno para recálculos
-- 3. Eventos para clientes VIP