Laboratorio 4: De la Planilla al Modelo

Semana 4 · W04 · Unidad II · Modelamiento de Datos · Universidad San Sebastián

1 Contexto

El equipo de TI de TechStyle lleva años registrando sus ventas en una sola hoja de Excel. El archivo techstyle_ventas_planas.csv contiene toda la información de ventas, clientes y productos en una única tabla plana — exactamente como lo haría alguien que nunca diseñó una base de datos.

Roberto, el CEO, acaba de contratar a Juan como analista de datos. La primera tarea de Juan es diseñar el modelo de datos que permitirá migrar esta información a una base de datos relacional en MySQL (que construirán en las semanas 6–7).

Tu tarea como equipo de analistas: revisar la planilla, identificar sus problemas, diseñar el modelo entidad-relación y documentar la estructura de tablas lista para ser implementada.

2 Instrucciones generales

  • Trabajarás en los grupos asignados.
  • Duración total: 2 horas 30 minutos.
  • Guarda tu trabajo frecuentemente.
  • Archivo de datos: labs/data/techstyle_ventas_planas.csv.

Herramientas necesarias:

Herramienta Uso Dónde obtenerla
Cualquier editor de texto / Excel / Calc Abrir el CSV Ya instalado
draw.io (online, gratuito) Diagrama MER Solo navegador, sin instalación
Cualquier editor de texto Diccionario de datos y SQL Bloc de notas, VS Code, etc.

Estructura del laboratorio:

Parte Actividad Tiempo
1 Auditar la planilla plana 25 min
2 Normalizar: de la planilla a las tablas 30 min
3 Diseñar el diagrama MER en draw.io 35 min
4 Construir el Diccionario de Datos 20 min
5 Escribir sentencias CREATE TABLE en SQL 20 min
6 Síntesis y reflexión 20 min

3 Parte 1 — Auditar la Planilla Plana

Duración: 25 minutos

3.1 Paso 1: Conocer el archivo

Abre techstyle_ventas_planas.csv y observa su estructura. La tabla tiene las siguientes columnas:

Columna Descripción
id_venta Identificador de la venta
fecha_venta Fecha de la transacción
cliente_id Código del cliente
cliente_nombre Nombre del cliente
cliente_apellido Apellido del cliente
cliente_email Correo electrónico
cliente_region Región del cliente
cliente_segmento Segmento: Premium / Estándar / Básico
producto_id Código del producto
producto_nombre Nombre del producto
categoria Categoría del producto
precio_unitario Precio de venta unitario
costo_unitario Costo del producto
cantidad Unidades vendidas
descuento_pct Porcentaje de descuento aplicado
venta_total Monto total de la venta

3.2 Paso 2: Identificar anomalías

Busca en los datos evidencia de cada tipo de anomalía. Completa la tabla:

Tipo de anomalía ¿Presente? Ejemplo concreto del archivo ¿Qué problema causa?
Actualización Sí / No
Inserción Sí / No
Eliminación Sí / No
Tip

Pista para anomalía de actualización: filtra por cliente_id = C001 y observa si todos sus atributos (email, región) son consistentes en todas las filas.

3.3 Paso 3: Identificar violaciones de forma normal

Completa el siguiente diagnóstico:

Pregunta Respuesta
¿La tabla está en 1FN? ¿Por qué sí o no?
¿Si estuviera en 1FN, estaría en 2FN? Identifica una dependencia parcial.
¿Si estuviera en 2FN, estaría en 3FN? Identifica una dependencia transitiva.
Note

Recordatorio: Una dependencia parcial ocurre cuando un atributo no-clave depende solo de parte de la clave compuesta. Una dependencia transitiva ocurre cuando un atributo no-clave depende de otro atributo no-clave.

3.4 Paso 4: Contar la redundancia

Responde las siguientes preguntas mirando los datos:

  1. ¿Cuántas veces aparece el nombre “Nike Air Max” (o el producto más frecuente) en la tabla?
  2. ¿Cuántas filas tendría que modificar si el cliente con más compras cambia su email?
  3. Si eliminamos todas las ventas de un cliente, ¿qué información perdemos que no debería perderse?

4 Parte 2 — Normalizar: de la Planilla a las Tablas

Duración: 30 minutos

El objetivo de esta parte es pasar la planilla plana por las tres formas normales y llegar a las tablas finales que irán al diagrama MER.

4.1 Paso 5: Aplicar 1FN

La tabla original ya tiene un valor por celda (no hay grupos repetidos tipo Item1, Item2…), por lo que técnicamente ya está en 1FN.

Sin embargo, identifica y registra:

  • ¿Cuál sería la clave primaria de esta tabla plana? (puede ser simple o compuesta)
  • ¿Esa clave identifica unívocamente cada fila?

4.2 Paso 6: Aplicar 2FN — Separar entidades con dependencias parciales

Asumiendo la clave primaria compuesta (id_venta, producto_id):

6.1 Identifica qué columnas dependen solo de producto_id (no de id_venta):

Columna ¿Depende solo de producto_id? ¿A qué tabla moverla?
producto_nombre
categoria
precio_unitario
costo_unitario
cantidad
descuento_pct
cantidad

6.2 Identifica qué columnas dependen solo de id_venta (no de producto_id):

Columna ¿Depende solo de id_venta? ¿A qué tabla moverla?
fecha_venta
cliente_id
cliente_nombre
venta_total

6.3 Define las tres tablas que resultan de aplicar 2FN:

Tabla Clave primaria Columnas que incluye
ventas
productos
detalle_ventas (compuesta)

4.3 Paso 7: Aplicar 3FN — Eliminar dependencias transitivas

En la tabla ventas del paso anterior quedan columnas del cliente (cliente_nombre, cliente_apellido, cliente_email, cliente_region, cliente_segmento).

7.1 ¿De qué columna dependen esos atributos del cliente? ¿De id_venta o de cliente_id?

7.2 Crea la tabla clientes con los atributos correspondientes:

Tabla Clave primaria Columnas que incluye
clientes
ventas (actualizada)

7.3 ¿Quedan dependencias transitivas en alguna de las 4 tablas resultantes? Verifica cada una.

Important

Al terminar este paso deberías tener 4 tablas: clientes, ventas, productos y detalle_ventas. Estas son las entidades del modelo relacional de TechStyle.

5 Parte 3 — Diseñar el Diagrama MER en draw.io

Duración: 35 minutos

5.1 Paso 8: Configurar draw.io

  1. Abre un navegador y ve a draw.io.
  2. Selecciona Crear nuevo diagrama → Diagrama en blanco.
  3. En el menú Buscar formas, escribe “entity” para encontrar las formas de Entidad-Relación.
Tip

También puedes usar MySQL Workbench si está instalado: File → New Model → Add Diagram. La ventaja de Workbench es que puede generar el SQL directamente desde el diagrama.

5.2 Paso 9: Crear las entidades

Para cada una de las 4 tablas definidas en la Parte 2, crea un rectángulo con:

  • Nombre de la entidad en la parte superior (en mayúsculas).
  • Lista de atributos dentro del rectángulo.
  • Clave primaria subrayada (o marcada con PK).
  • Claves foráneas marcadas con FK.

Entidad CLIENTES:

Atributo Rol
cliente_id PK
nombre
apellido
email
region
segmento

Entidad PRODUCTOS:

Atributo Rol
producto_id PK
nombre_producto
categoria
precio_unitario
costo_unitario

Entidad VENTAS:

Atributo Rol
venta_id PK
fecha
cliente_id FK → CLIENTES

Entidad DETALLE_VENTAS:

Atributo Rol
venta_id PK, FK → VENTAS
producto_id PK, FK → PRODUCTOS
cantidad
descuento_pct
venta_total

5.3 Paso 10: Dibujar las relaciones

Conecta las entidades con líneas de relación y anota la cardinalidad:

Relación Cardinalidad Semántica
CLIENTES → VENTAS 1 : N Un cliente realiza muchas ventas
VENTAS → DETALLE_VENTAS 1 : N Una venta contiene muchos ítems
PRODUCTOS → DETALLE_VENTAS 1 : N Un producto aparece en muchos ítems
Note

Usa la notación crow’s foot (pata de gallo) en draw.io: en el menú de líneas, selecciona el conector con las marcas de cardinalidad. El lado “uno” lleva || y el lado “muchos” lleva o{ o |{.

5.4 Paso 11: Verificar el diagrama

Antes de continuar, verifica:

Exporta el diagrama: Archivo → Exportar como → PNG. Guárdalo como W04_diagrama_MER.png.

6 Parte 4 — Diccionario de Datos

Duración: 20 minutos

El Diccionario de Datos documenta cada atributo: su nombre, tipo de dato SQL, si acepta nulos y su descripción. Es el documento que recibe el desarrollador para implementar la base de datos.

6.1 Paso 12: Completar el diccionario

Completa el diccionario para las 4 tablas. Usa los tipos SQL estándar:

Tipo SQL Uso
INT Números enteros (IDs, cantidades, stock)
VARCHAR(n) Texto de longitud variable (nombres, emails, categorías)
DECIMAL(p,s) Números con decimales (precios, montos)
DATE Fechas (sin hora)
BOOLEAN Verdadero / Falso

Tabla: CLIENTES

Atributo Tipo SQL NOT NULL Restricciones Descripción
cliente_id INT Sí PK Identificador único del cliente
nombre VARCHAR(50) Sí Nombre de pila
apellido VARCHAR(50) Sí Apellido
email VARCHAR(100) Sí UNIQUE Correo electrónico (único en el sistema)
region VARCHAR(50) Sí Región de residencia
segmento VARCHAR(20) No CHECK: Premium / Estándar / Básico Segmento comercial del cliente

Tabla: PRODUCTOS

Completa los campos en blanco:

Atributo Tipo SQL NOT NULL Restricciones Descripción
producto_id Sí PK
nombre_producto Sí Nombre comercial del producto
categoria Sí Ej: Calzado, Ropa, Accesorios
precio_unitario DECIMAL(10,2) Sí CHECK > 0 Precio de venta al público
costo_unitario Sí Costo de producción

Tabla: VENTAS

Atributo Tipo SQL NOT NULL Restricciones Descripción
venta_id Sí PK
fecha Sí Fecha de la transacción
cliente_id Sí FK → CLIENTES

Tabla: DETALLE_VENTAS

Atributo Tipo SQL NOT NULL Restricciones Descripción
venta_id INT Sí PK, FK → VENTAS
producto_id INT Sí PK, FK → PRODUCTOS
cantidad Sí CHECK > 0
descuento_pct No CHECK 0–100, DEFAULT 0 Porcentaje de descuento aplicado
venta_total DECIMAL(10,2) Sí Venta total (depende de la cantidad y el precio unitario)
Important

¿Por qué precio_unitario está en DETALLE_VENTAS y no solo en PRODUCTOS? El precio de un producto puede cambiar con el tiempo. Si una venta de hace 6 meses registró un precio de $89.990 y hoy el producto vale $99.990, necesitamos guardar el precio histórico al momento de la venta. Por eso precio_unitario vive en el detalle, no en la tabla de productos.

7 Parte 5 — Sentencias CREATE TABLE en SQL

Duración: 20 minutos

7.1 Paso 13: Escribir el SQL de creación

Con base en el diccionario de datos, escribe las sentencias CREATE TABLE para las 4 tablas. El orden importa: primero las tablas sin dependencias foráneas.

Orden correcto: CLIENTES → PRODUCTOS → VENTAS → DETALLE_VENTAS.

Tabla CLIENTES (ya resuelta — úsala como referencia):

CREATE TABLE clientes (
    cliente_id      INT           PRIMARY KEY,
    nombre          VARCHAR(50)   NOT NULL,
    apellido        VARCHAR(50)   NOT NULL,
    email           VARCHAR(100)  UNIQUE NOT NULL,
    region          VARCHAR(50)   NOT NULL,
    segmento        VARCHAR(20)   CHECK (segmento IN ('Premium', 'Estándar', 'Básico')),
    fecha_registro  DATE          NOT NULL
);

Tabla PRODUCTOS (completa los espacios en blanco):

CREATE TABLE productos (
    producto_id      ___________   PRIMARY KEY,
    nombre_producto  ___________   NOT NULL,
    categoria        ___________   NOT NULL,
    precio_venta     DECIMAL(10,2) NOT NULL CHECK (precio_venta > 0),
    costo_unitario   ___________   NOT NULL CHECK (costo_unitario > 0),
    stock            ___________   NOT NULL DEFAULT 0
);

Tabla VENTAS (escríbela desde cero):

-- Escribe aquí la sentencia CREATE TABLE ventas
-- Recuerda: incluir la FOREIGN KEY hacia clientes

Tabla DETALLE_VENTAS (escríbela desde cero):

-- Escribe aquí la sentencia CREATE TABLE detalle_ventas
-- Recuerda:
--   - Clave primaria COMPUESTA: (venta_id, producto_id)
--   - Dos FOREIGN KEYs: hacia ventas y hacia productos
--   - CHECK en cantidad y descuento_pct

7.2 Paso 14: Verificar la integridad referencial

Responde:

  1. ¿Por qué DETALLE_VENTAS se crea después de VENTAS y PRODUCTOS?
  2. ¿Qué ocurre en MySQL si intentas insertar una fila en DETALLE_VENTAS con un venta_id que no existe en VENTAS?
  3. ¿Qué ocurre si intentas eliminar un cliente de CLIENTES que tiene ventas asociadas en VENTAS?
Note

Esto es la integridad referencial garantizada por las FOREIGN KEY. MySQL no permite insertar datos huérfanos ni eliminar datos que otros registros referencian — exactamente el tipo de protección que Excel no puede ofrecer.

8 Parte 6 — Síntesis y Reflexión

Duración: 20 minutos

8.1 Paso 15: Comparar el antes y el después

Dimensión Planilla Excel plana Modelo relacional en 3FN
¿Dónde vive el email de un cliente? En cada fila de sus ventas
¿Cómo se actualiza el precio de un producto?
¿Qué pasa si eliminas todas las ventas de un cliente?
¿Cuántas tablas necesitas para consultar ventas por región?
¿Puede haber dos productos con el mismo código?

8.2 Paso 16: Reporte ejecutivo para Roberto

Redacta un párrafo breve (6–8 líneas) como si fueras Juan presentando el modelo de datos a Roberto:

Para: Roberto (CEO)

Asunto: Diseño del modelo de datos para migración de Excel a MySQL

El modelo propuesto organiza la información de TechStyle en 4 tablas normalizadas… (continúa)

Incluye: - Por qué el diseño actual en Excel genera riesgos. - Qué garantiza el nuevo modelo que Excel no puede garantizar. - Cuántas tablas tiene el nuevo modelo y qué representa cada una.

8.3 Paso 17: Preguntas de reflexión

Responde en conjunto con tu compañero:

Pregunta Respuesta
¿En qué parte de la normalización fue más difícil decidir qué tabla pertenece a qué entidad?
¿Podría haber una 4FN o 5FN? ¿Cuándo se usarían?
Si TechStyle agrega un módulo de despacho (con dirección de entrega, repartidor, hora de entrega), ¿qué nuevas entidades agregarías al modelo?
¿Por qué venta_total no se almacena en ninguna tabla del modelo relacional, si aparece en la planilla plana?

9 Entregable

Sube a la plataforma del curso los siguientes archivos comprimidos en una carpeta W04_lab_[apellido1]_[apellido2].zip:

Archivo Contenido
W04_diagrama_MER.png Diagrama MER exportado desde draw.io
W04_diccionario_datos.pdf o .docx Tablas del diccionario de datos completas
W04_modelo.sql Las 4 sentencias CREATE TABLE completas
W04_reflexion.pdf o .docx Reporte ejecutivo + respuestas de reflexión

10 Criterios de Evaluación

Criterio Puntaje
Identificación correcta de anomalías y violaciones de FN (Partes 1–2) 20 pts
Diagrama MER: entidades, atributos, relaciones y cardinalidades correctas (Parte 3) 25 pts
Diccionario de datos: tipos SQL correctos y restricciones apropiadas (Parte 4) 20 pts
Sentencias CREATE TABLE con PK, FK y CHECK correctos (Parte 5) 25 pts
Reporte ejecutivo y reflexión (Parte 6) 10 pts
Total 100 pts