Vamos a analizar el código del segundo examen línea por línea para entender qué hace cada parte.
Aquí
están las consultas y soluciones explicadas:
1. Mostrar pedidos de septiembre, empleado y cliente
Consulta 1 (Solución 1):
SELECT E.NOM_EMP, E.APE_EMP, C.NOM_CLI, C.APE_CLI
FROM PEDIDO AS P
JOIN EMPLEADO AS E ON P.CI_EMP = E.CI_EMP
JOIN CLIENTE AS C ON P.CI_CLI = C.CI_CLI
WHERE P.FECHA_PED BETWEEN '2024-09-01' AND '2024-09-30';
• SELECT: Recupera los nombres y apellidos de los empleados (NOM_EMP, APE_EMP) y clientes
(NOM_CLI, APE_CLI).
• FROM PEDIDO: Usa la tabla PEDIDO como principal.
• JOIN EMPLEADO: Conecta PEDIDO con EMPLEADO mediante CI_EMP.
• JOIN CLIENTE: Conecta PEDIDO con CLIENTE mediante CI_CLI.
• WHERE FECHA_PED BETWEEN: Filtra pedidos hechos entre el 1 y el 30 de septiembre de 2024.
Consulta 1 (Solución 2):
SELECT P.COD_PED, PR.DESC_PROD, E.NOM_EMP, E.APE_EMP,
C.NOM_CLI, C.APE_CLI
FROM PEDIDO P, PRODUCTO PR, EMPLEADO E, CLIENTE C, DETALLE_PEDIDO_PRODUCTO DP
WHERE P.FECHA_PED < '2024/10/01'
AND P.COD_PED = DP.COD_PED
AND DP.COD_PROD = PR.COD_PROD
AND E.CI_EMP = P.CI_EMP
AND P.CI_CLI = C.CI_CLI;
• Recupera detalles adicionales como el producto (DESC_PROD) asociado al pedido.
• Combina múltiples tablas (PRODUCTO, DETALLE_PEDIDO_PRODUCTO).
• Cambia el rango de fechas usando < '2024/10/01'.
2. Productos de frutas y verduras en pedidos
Consulta 2 (Solución 1):
SELECT DISTINCT P.COD_PROD, P.DESC_PROD, C.NOM_CAT,
CL.NOM_CLI, CL.APE_CLI, E.NOM_EMP, E.APE_EMP
FROM PRODUCTO P
JOIN CATEGORIA C ON P.COD_CATEGORIA = C.COD_CAT
JOIN DETALLE_PEDIDO_PRODUCTO DPP ON P.COD_PROD = DPP.COD_PROD
JOIN PEDIDO PE ON DPP.COD_PED = PE.COD_PED
JOIN CLIENTE CL ON PE.CI_CLI = CL.CI_CLI
JOIN EMPLEADO E ON PE.CI_EMP = E.CI_EMP
WHERE C.NOM_CAT='FRUTAS' OR C.NOM_CAT='VERDURAS';
• DISTINCT: Evita duplicados en el resultado.
• Combina las tablas para mostrar productos, categorías, empleados y clientes.
• WHERE C.NOM_CAT IN ('FRUTAS', 'VERDURAS'): Filtra productos que pertenezcan a estas
categorías.
3. Productos pedidos más de una vez
Consulta 3 (Solución 1):
SELECT DISTINCT P.COD_PROD, P.DESC_PROD, PR.NOM_PROV, CAT.NOM_CAT
FROM PRODUCTO P
JOIN PROVEEDOR PR ON P.COD_PROV = PR.CI_PROV
JOIN CATEGORIA CAT ON P.COD_CATEGORIA = CAT.COD_CAT
JOIN DETALLE_PEDIDO_PRODUCTO DPP ON P.COD_PROD = DPP.COD_PROD
WHERE P.COD_PROD IN (
SELECT COD_PROD
FROM DETALLE_PEDIDO_PRODUCTO
GROUP BY COD_PROD
HAVING COUNT(DISTINCT COD_PED) > 1
);
• Combina PRODUCTO, PROVEEDOR, CATEGORIA y DETALLE_PEDIDO_PRODUCTO.
• Subconsulta:
o GROUP BY COD_PROD: Agrupa por producto.
o HAVING COUNT(DISTINCT COD_PED) > 1: Selecciona productos pedidos más de una vez.
4. Productos cárnicos con precio mayor a 40
SELECT P.COD_PROD, P.DESC_PROD, P.PRECIOUNIT_PROD,
PR.CI_PROV, PR.NOM_PROV, CAT.NOM_CAT
FROM PRODUCTO P
JOIN PROVEEDOR PR ON P.COD_PROV = PR.CI_PROV
JOIN CATEGORIA CAT ON P.COD_CATEGORIA = CAT.COD_CAT
WHERE CAT.NOM_CAT = 'CARNICOS' AND P.PRECIOUNIT_PROD > 40;
• Filtra productos cárnicos (NOM_CAT = 'CARNICOS') con precio unitario mayor a 40.
• Recupera información del proveedor y la categoría.
5. Crear vista de ventas
CREATE VIEW Vista_Ventas AS
SELECT C.CI_CLI, C.NOM_CLI, C.APE_CLI, P.COD_PED,
P.FECHA_PED, PR.COD_PROD, PR.DESC_PROD, PR.PRECIOUNIT_PROD,
DP.CANTIDAD_PEDIDO, CAT.NOM_CAT
FROM CLIENTE C
JOIN PEDIDO P ON C.CI_CLI = P.CI_CLI
JOIN DETALLE_PEDIDO_PRODUCTO DP ON P.COD_PED = DP.COD_PED
JOIN PRODUCTO PR ON DP.COD_PROD = PR.COD_PROD
JOIN CATEGORIA CAT ON PR.COD_CATEGORIA = CAT.COD_CAT;
• CREATE VIEW: Crea una vista llamada Vista_Ventas.
• Combina tablas relevantes para obtener información completa de clientes, pedidos, productos y
categorías.
• Los datos quedan almacenados en la vista para consultas rápidas.
Voy a explicarte el código línea por línea para que entiendas cada parte de lo que hace en este script
para pgAdmin (PostgreSQL). Este archivo contiene consultas SQL y un trigger.
1. Consulta 1
Objetivo: Listar pedidos entregados al menos dos días antes de la fecha esperada.
Esta parte no está en el código completo, pero el enunciado dice:
SELECT COD_PEDIDO, NOM_CLIENTE, FECHA_ESPERADA, FECHA_ENTREGA
FROM PEDIDOS
WHERE FECHA_ENTREGA <= FECHA_ESPERADA - INTERVAL '2 DAYS';
• SELECT: Extrae las columnas COD_PEDIDO, NOM_CLIENTE, FECHA_ESPERADA, y
FECHA_ENTREGA.
• FROM PEDIDOS: Especifica la tabla de donde se obtendrán los datos.
• WHERE: Filtra los pedidos cuya FECHA_ENTREGA sea al menos dos días antes de la
FECHA_ESPERADA (se usa INTERVAL '2 DAYS' para restar dos días).
2. Consulta 2
Objetivo: Listar empleados que no tienen clientes asociados y datos de su oficina.
SELECT E.COD_EMP, E.NOM_EMP, OF.COD_OFI, OF.CIUDAD_OFI, OF.PAIS_OFI
FROM EMPLEADO E, OFICINA OF
WHERE OF.COD_OFI = E.COD_OFI
AND NOT EXISTS (
SELECT COD_EMP
FROM CLIENTE
WHERE COD_EMP = E.COD_EMP
);
• SELECT: Recupera los códigos y nombres de los empleados, junto con información de su oficina.
• FROM EMPLEADO E, OFICINA OF: Combina las tablas EMPLEADO y OFICINA.
• WHERE OF.COD_OFI = E.COD_OFI: Relaciona las tablas mediante el código de oficina.
• AND NOT EXISTS: Excluye empleados que aparecen en la tabla CLIENTE.
El subquery dentro de NOT EXISTS verifica si el código de empleado existe en CLIENTE.
3. Consulta 3
Objetivo: Listar los 5 productos más vendidos.
SELECT P.COD_PROD, P.NOM_PROD, P.GAMA_PROD, P.DESC_PROD,
SUM(DP.CANTIDAD_PEDIDO) AS CANTIDAD_PEDIDO
FROM PRODUCTO P
JOIN DETALLE_PEDIDO DP ON P.COD_PROD = DP.COD_PROD
GROUP BY P.COD_PROD, P.NOM_PROD, P.GAMA_PROD
ORDER BY CANTIDAD_PEDIDO DESC
LIMIT 5;
• SELECT: Recupera información de los productos, incluyendo el total de unidades vendidas.
• FROM PRODUCTO P JOIN DETALLE_PEDIDO DP: Une PRODUCTO con DETALLE_PEDIDO para
relacionar productos con pedidos.
• ON P.COD_PROD = DP.COD_PROD: Establece la clave para combinar ambas tablas.
• SUM(DP.CANTIDAD_PEDIDO): Suma la cantidad de productos vendidos por cada producto.
• GROUP BY: Agrupa los resultados por los atributos seleccionados.
• ORDER BY CANTIDAD_PEDIDO DESC: Ordena de mayor a menor por la cantidad total de
productos vendidos.
• LIMIT 5: Limita el resultado a los 5 productos más vendidos.
4. Consulta 4
Objetivo: Listar productos, su gama, los clientes y empleados asociados a clientes con más de un pedido.
SELECT P.COD_PROD, P.NOM_PROD, P.GAMA_PROD,
C.NOM_CLIENTE, C.APE1_CLIENTE, E.NOM_EMP, E.APE1_EMP
FROM DETALLE_PEDIDO DP
JOIN PRODUCTO P ON DP.COD_PROD = P.COD_PROD
JOIN PEDIDO PE ON DP.COD_PEDIDO = PE.COD_PEDIDO
JOIN CLIENTE C ON PE.COD_CLIENTE = C.COD_CLIENTE
JOIN EMPLEADO E ON C.COD_EMP = E.COD_EMP
WHERE C.COD_CLIENTE IN (
SELECT COD_CLIENTE
FROM PEDIDO
GROUP BY COD_CLIENTE
HAVING COUNT(COD_PEDIDO) > 1
);
• SELECT: Muestra información del producto, cliente y empleado.
• Uniones (JOIN):
o Conecta DETALLE_PEDIDO, PRODUCTO, PEDIDO, CLIENTE y EMPLEADO para obtener
todos los datos relacionados.
• WHERE C.COD_CLIENTE IN: Filtra clientes con más de un pedido.
o Subquery:
▪ SELECT COD_CLIENTE: Obtiene clientes.
▪ GROUP BY COD_CLIENTE: Agrupa por cliente.
▪ HAVING COUNT(COD_PEDIDO) > 1: Solo incluye clientes con más de un pedido.
5. Trigger
Objetivo: Verificar que la cantidad pedida no exceda el stock disponible antes de insertar o actualizar.
Crear la función:
CREATE OR REPLACE FUNCTION VERIFICAR_CANTIDAD()
RETURNS TRIGGER AS $$
BEGIN
IF (SELECT CANTIDAD_PROD FROM PRODUCTO WHERE COD_PROD = NEW.COD_PROD) >=
NEW.CANTIDAD_PEDIDO THEN
RETURN NEW;
END IF;
RAISE EXCEPTION 'NO HAY SUFICIENTE STOCK DISPONIBLE PARA ESTE PRODUCTO';
END;
$$ LANGUAGE PLPGSQL;
1. CREATE OR REPLACE FUNCTION: Define o reemplaza una función llamada
VERIFICAR_CANTIDAD.
2. RETURNS TRIGGER: Indica que esta función actuará como un trigger.
3. BEGIN y END: Bloque donde se escribe la lógica.
4. IF:
o Verifica si el stock del producto (CANTIDAD_PROD) es mayor o igual a lo pedido
(NEW.CANTIDAD_PEDIDO).
o Si es válido, RETURN NEW continúa la operación.
5. RAISE EXCEPTION: Lanza un error si no hay suficiente stock.
Crear el trigger:
CREATE TRIGGER VERIFICAR_CANTIDAD_TRIGGER
BEFORE INSERT OR UPDATE ON DETALLE_PEDIDO
FOR EACH ROW
EXECUTE FUNCTION VERIFICAR_CANTIDAD();
1. CREATE TRIGGER: Define un trigger llamado VERIFICAR_CANTIDAD_TRIGGER.
2. BEFORE INSERT OR UPDATE ON DETALLE_PEDIDO: Se ejecuta antes de insertar o actualizar en
DETALLE_PEDIDO.
3. FOR EACH ROW: Aplica la validación a cada fila afectada.
4. EXECUTE FUNCTION VERIFICAR_CANTIDAD(): Llama a la función definida anteriormente.
¡Por supuesto! La cardinalidad es fundamental en el modelo entidad-relación porque define cómo se
relacionan las entidades en un sistema. Te lo explico detalladamente.
¿Qué es la cardinalidad?
La cardinalidad describe cuántas instancias de una entidad pueden estar asociadas con cuántas
instancias de otra entidad en una relación.
• Máxima: Representa el número máximo de instancias permitidas.
• Mínima: Indica si una relación es obligatoria (mínimo 1) o opcional (mínimo 0).
Se suele representar con números al lado de las líneas en los diagramas ER:
• 1:1, 1:N, N:M, etc.
Tipos principales de cardinalidad
1. Uno a uno (1:1)
o Definición: Una instancia de la entidad A se asocia con exactamente una instancia de la
entidad B, y viceversa.
o Ejemplo: Cada país tiene un solo presidente, y cada presidente gobierna un solo país.
o Representación:
o País (1) ---- (1) Presidente
2. Uno a muchos (1:N)
o Definición: Una instancia de la entidad A puede estar asociada con muchas instancias de
la entidad B, pero cada instancia de B está asociada con una sola instancia de A.
o Ejemplo: Un cliente puede realizar muchos pedidos, pero cada pedido pertenece a un
solo cliente.
o Representación:
o Cliente (1) ---- (N) Pedido
3. Muchos a uno (N:1)
o Es simplemente el inverso de 1:N, donde cada instancia de la entidad B puede estar
asociada con muchas de la entidad A.
o Ejemplo: Muchos empleados trabajan en un solo departamento.
4. Muchos a muchos (N:M)
o Definición: Una instancia de la entidad A puede estar asociada con muchas instancias de
la entidad B, y viceversa.
o Ejemplo: Los estudiantes pueden inscribirse en muchos cursos, y cada curso puede tener
muchos estudiantes.
o Representación:
o Estudiante (N) ---- (M) Curso
Cómo identificar la cardinalidad en un problema
1. Lee cuidadosamente el enunciado.
o Busca palabras clave como uno, varios, muchos, exactamente, etc.
2. Ejemplo práctico:
"Una empresa desea guardar información sobre empleados y departamentos. Cada empleado
trabaja en un solo departamento, pero un departamento puede tener varios empleados."
o Entidades: Empleado, Departamento.
o Relación: "Trabaja en".
o Cardinalidad:
▪ Un empleado → un solo departamento (1:1).
▪ Un departamento → muchos empleados (1:N).
3. Prueba preguntas:
o Para cada entidad en la relación, pregunta:
"¿Cuántas veces puede ocurrir esta relación para una instancia de esta entidad?"
Tipos avanzados de cardinalidad
1. Cardinalidad mínima (0 o 1):
o Definición: Indica si una relación es opcional (0) o siempre obligatoria (1).
o Ejemplo:
▪ Un empleado puede o no estar asignado a un proyecto (0:N).
▪ Un pedido siempre debe pertenecer a un cliente (1:N).
2. Relaciones reflexivas (1:N o N:M consigo misma):
o Definición: Una entidad se relaciona consigo misma.
o Ejemplo:
▪ Un empleado supervisa a otros empleados (1:N).
3. Relaciones ternarias (3 entidades involucradas):
o Definición: Relacionan tres entidades en un solo rombo.
o Ejemplo:
▪ Un contrato conecta a un empleado, un cliente y un proyecto.
Cómo se aplican en los diagramas ER
• Dibuja las entidades como rectángulos.
• Usa rombos para las relaciones y conéctalos con líneas.
• Añade la cardinalidad al lado de las líneas.
Ejemplo visual:
Relación 1:N entre Cliente y Pedido
Cliente (1) ---- (N) Pedido
Ejemplo de relación N:M con tabla intermedia:
Estudiante y Curso
Estudiante (N) ---- (M) Curso
Se crea una tabla intermedia:
Estudiante_Curso: id_estudiante (FK), id_curso (FK)
¿Te gustaría que te ayude a dibujar un ejemplo o resolver un caso práctico específico?
Con base en los documentos proporcionados, puedo explicarte cómo aplicar el modelo entidad-relación
utilizando algunos ejemplos y pasos clave de los materiales. Veamos:
1. Conceptos básicos del Modelo Entidad-Relación
• Entidades: Objetos del mundo real sobre los que queremos guardar información. Se representan
con rectángulos.
• Atributos: Características de las entidades, representados como elipses unidas a las entidades.
• Relaciones: Conexiones entre entidades, representadas con rombos.
• Cardinalidad: Define cómo se relacionan las entidades (1:1, 1:N, N:M).
2. Cómo diseñar un diagrama ER
Ejemplo práctico: Empresa de Transporte
Descripción del problema (resumida):
Una empresa de transporte guarda información sobre:
• Camioneros: CI, nombre, teléfono, dirección, salario, ciudad.
• Camiones: placa, modelo, tipo, potencia.
• Paquetes: código, descripción, destinatario, dirección.
• Ciudades: código y nombre.
Relaciones:
• Un camionero transporta varios paquetes (1:N).
• Un paquete es transportado por un camionero (1:1).
• Un camión puede ser conducido por varios camioneros y viceversa (N:M).
Paso 1: Identifica las entidades
• Entidades: Camionero, Camión, Paquete, Ciudad.
Paso 2: Añade atributos
• Camionero: CI, nombre, teléfono, dirección, salario, ciudad.
• Camión: placa, modelo, tipo, potencia.
• Paquete: código, descripción, destinatario, dirección.
• Ciudad: código, nombre.
Paso 3: Define las relaciones
• Relación "Transporta" entre Camionero y Paquete (1:N).
• Relación "Conduce" entre Camionero y Camión (N:M).
• Relación "Entrega" entre Paquete y Ciudad (1:N).
Paso 4: Dibuja el diagrama
1. Dibuja rectángulos para cada entidad y conéctalos con rombos para representar las relaciones.
2. Especifica la cardinalidad en las líneas de conexión (1:N, N:M, etc.).
3. Transformación al modelo relacional
Siguiendo las reglas en tus documentos【9†source】【10†source】:
1. Entidades fuertes (como Camionero, Camión, etc.) se convierten en tablas:
o Tabla Camionero: CI (PK), nombre, teléfono, dirección, salario, ciudad.
o Tabla Camión: placa (PK), modelo, tipo, potencia.
2. Relaciones con cardinalidad N:M (como "Conduce") generan una nueva tabla:
o Tabla Conduce: CI_camionero (FK), placa_camión (FK), fecha.
3. Relaciones 1:N (como "Transporta") añaden la clave primaria de la entidad con cardinalidad 1 a
la tabla de la entidad con cardinalidad N:
o Tabla Paquete: código (PK), descripción, destinatario, dirección, CI_camionero (FK).
4. Relaciones 1:1 pueden incluirse en una de las tablas relacionadas, dependiendo de la
implementación.
4. Ejemplo transformado a tablas
Tabla Camionero
CI Nombre Teléfono Dirección Salario Ciudad
Tabla Camión
Placa Modelo Tipo Potencia
Tabla Paquete
Código Descripción Destinatario Dirección CI_camionero (FK)
Tabla Conduce
CI_camionero (FK) Placa_camión (FK) Fecha
Tabla Ciudad
Código Nombre
¿Te gustaría que elabore un diagrama o complemente este diseño con algo más?
