Diagrama Entidad-Relación en Sistemas de Renta de Películas

Clase 18 de 34 • Curso de PostgreSQL Aplicado a Ciencia de Datos

Resumen

En el proceso de aprendizaje y desarrollo de habilidades en el manejo de bases de datos, comprender la estructura y el uso efectivo de las mismas es fundamental. Una forma práctica de aplicar los conocimientos adquiridos es mediante la realización de un proyecto real que implique manipulación y consulta de datos. Para ello, se utilizan bases de datos de ejemplo, que permiten poner en práctica lo aprendido.

¿Cómo está estructurada la base de datos de ejemplo?

La base de datos de ejemplo está diseñada para simular un sistema de renta de películas, conteniendo entidades y relaciones que reflejan situaciones del mundo real. Está compuesta por varias tablas que contienen datos sobre películas, actores, idiomas, categorías, rentas, inventarios, pagos, clientes, empleados y más, donde cada entidad tiene atributos específicos que la definen.

¿Cuáles son las entidades principales?

Las entidades principales de la base de datos son:

Películas: con atributos como identificador único, título, descripción, año de lanzamiento, idiomas, duración de la renta, precio, costo de reemplazo, clasificación, última actualización, características especiales y el texto completo de la película.
Actores: la cual tiene una relación de muchos a muchos con las películas, gestionadas a través de una tabla intermedia.
Idiomas: definen el idioma o idiomas de una película, con su respectivo nombre y fecha de actualización.
Categorías: se refieren a las agrupaciones temáticas en las que se pueden organizar las películas.

¿Cómo interactúan las tablas de rentas e inventarios?

Las tablas de Rentas e Inventarios están interconectadas y desempeñan roles clave en el funcionamiento del sistema:

Inventario: refleja las existencias de películas que una tienda tiene disponibles.
Rentas: registra los detalles de la transacción de alquiler, como la película rentada, la fecha, el cliente y el empleado que realizó la renta.

¿Qué información contienen las tablas de pagos, empleados y clientes?

Las tablas de pagos, empleados y clientes son fundamentales para comprender las transacciones y la interacción con el sistema:

Pagos: relaciona las rentas con los montos abonados, vinculando también al cliente que realizó el pago.
Empleados: almacena datos del personal que atiende las tiendas y gestiona las rentas.
Clientes: registra la información de quienes alquilan películas, incluyendo datos personales y de contacto.

¿Cuál es la importancia de las tablas geográficas y de tipo de cambio?

Las tablas geográficas y de tipo de cambio proporcionan contexto y flexibilidad en la gestión de datos:

Direcciones, Ciudades y Países: ofrecen información precisa sobre la ubicación de tiendas, clientes y empleados.
Tipo de Cambio: establece la equivalencia de precios en diferentes monedas para ajustar los costos y pagos de acuerdo al país de operación.

¿Cómo se puede utilizar la base de datos para generar información relevante?

Utilizar esta base de datos abre posibilidades casi infinitas para generar informes y realizar consultas estratégicas:

Analizar patrones de renta y preferencias de películas según categorías o idiomas.
Evaluar el desempeño de ventas en diferentes tiendas o regiones.
Monitorear el inventario de películas y predecir necesidades de reabastecimiento.

Reflexión final

Ahora que conoces la estructura detallada de la base de datos y las posibles interacciones entre sus entidades, te invito a reflexionar sobre las consultas y reportes que podrías realizar para extraer información valiosa. Imagina las múltiples posibilidades y cómo podrías aplicar lo que has aprendido en el curso para obtener datos que puedan contribuir efectivamente a decisiones estratégicas en un negocio de renta de películas. Comparte tus ideas y sugerencias de consultas en los comentarios y sigue explorando el apasionante mundo de las bases de datos. ¡Sigamos aprendiendo juntos para construir proyectos aún más impactantes!

Jordan Sobrino Santos

student•

Querys:

-Top 10 de películas con mayor facturación. -Categorías con mayor facturación. -Empleado que mas venta realiza en $. -Ticket promedio por cliente en $. -Contribución por categoría (Margen).

Erick Rosero

student•

Hola, resolví tus querys, aquí la solución a cada uno:

--Top 10 de películas con mayor facturación.

SELECT DISTINCT pelicula_id, titulo, SUM(cantidad) OVER(PARTITION BY titulo) AS total
FROM(
	SELECT p.pelicula_id, p.titulo, pa.cantidad 
	FROM peliculas AS p
	JOIN inventarios AS i ON p.pelicula_id= i.pelicula_id 
	JOIN rentas AS r ON i.inventario_id = r.inventario_id
	JOIN pagos AS pa ON r.renta_id = pa.renta_id
	GROUP BY p.pelicula_id, pa.cantidad
	ORDER BY p.pelicula_id
	) AS tabla_facturacion
ORDER BY total DESC
LIMIT 10

--Categorías con mayor facturación.
SELECT DISTINCT categoria_id, nombre, SUM(cantidad) OVER(PARTITION BY nombre) AS total
FROM(
	SELECT c.categoria_id, c.nombre, pa.cantidad 
	FROM peliculas AS p
	JOIN inventarios AS i ON p.pelicula_id= i.pelicula_id 
	JOIN rentas AS r ON i.inventario_id = r.inventario_id
	JOIN pagos AS pa ON r.renta_id = pa.renta_id
	JOIN peliculas_categorias AS pc ON p.pelicula_id =pc.pelicula_id
	JOIN categorias AS c ON pc.categoria_id = c.categoria_id
	GROUP BY c.categoria_id, pa.cantidad
	) AS tabla_facturacion
ORDER BY total DESC
LIMIT 10

--Empleado que más venta realiza en $.
SELECT DISTINCT empleado_id, empleado, SUM(cantidad) OVER(PARTITION BY empleado)
FROM(
	SELECT e.empleado_id, CONCAT(e.nombre,' ', e.apellido) AS empleado,p.cantidad
	FROM empleados AS e
	JOIN rentas AS r ON r.empleado_id= e.empleado_id
	JOIN pagos AS p ON p.renta_id = r.renta_id
	GROUP BY e.empleado_id, p.cantidad
	) AS lista_empleados
	
--Ticket promedio por cliente en $.
SELECT DISTINCT cliente_id, cliente, ROUND(AVG(cantidad) OVER(PARTITION BY cliente),2) AS promedio_venta
FROM(
	SELECT c.cliente_id, CONCAT(c.nombre,' ', c.apellido) AS cliente, p.cantidad
	FROM clientes AS c
	JOIN rentas AS r ON c.cliente_id = r.cliente_id
	JOIN pagos AS p ON r.renta_id = p.renta_id
	GROUP BY c.cliente_id, p.cantidad
)AS facturacion_cliente	

--Contribución por categoría (Margen).
SELECT DISTINCT categoria_id, nombre, SUM(cantidad) OVER(PARTITION BY nombre) AS facturado, 
	SUM(precio_renta) OVER(PARTITION BY nombre) AS precio, 
	(SUM(cantidad) OVER(PARTITION BY nombre) - SUM(precio_renta) OVER(PARTITION BY nombre)) AS utilidad
FROM(
	SELECT c.categoria_id, c.nombre, pa.cantidad, p.precio_renta
	FROM peliculas AS p
	JOIN inventarios AS i ON p.pelicula_id= i.pelicula_id 
	JOIN rentas AS r ON i.inventario_id = r.inventario_id
	JOIN pagos AS pa ON r.renta_id = pa.renta_id
	JOIN peliculas_categorias AS pc ON p.pelicula_id =pc.pelicula_id
	JOIN categorias AS c ON pc.categoria_id = c.categoria_id
	GROUP BY c.categoria_id, pa.cantidad, p.precio_renta
	) AS tabla_facturacion
ORDER BY utilidad DESC

Breiner Álvarez

student•

@erickros45 tienes errores en las consultas. No es necesario agrupar por cantidad en los subqueries, dado que, por ejemplo, una sola película se puede rentar en multiples ocasiones, y lo que necesitas como tal es el agregado (SUM) del dinero que ingresa por cada renta. A continuación una forma más simple de hacer los cálculos:

-- SET search_path='[nombre del esquema]';

-- Top 10 de películas con mayor facturación:
SELECT titulo, SUM(cantidad) AS ingreso_por_renta FROM pagos INNER JOIN rentas USING(renta_id) 
	INNER JOIN inventarios USING(inventario_id) INNER JOIN peliculas USING(pelicula_id) 
	GROUP BY titulo ORDER BY ingreso_por_renta DESC LIMIT 10;

-- Categorías con mayor facturación:
SELECT nombre, SUM(cantidad) AS ingreso_por_categoria FROM categorias 
	LEFT JOIN peliculas_categorias USING(categoria_id)
	INNER JOIN peliculas USING(pelicula_id) INNER JOIN inventarios USING(pelicula_id)
	INNER JOIN rentas USING(inventario_id) INNER JOIN pagos USING(renta_id) GROUP BY nombre ORDER BY ingreso_por_categoria DESC;

-- Empleados que más ventas realizan en $:
SELECT CONCAT(nombre, ' ', apellido) AS nombre_completo, SUM(cantidad) AS ventas FROM empleados 
	LEFT JOIN rentas USING(empleado_id) INNER JOIN pagos USING(renta_id)
	GROUP BY empleados.empleado_id, nombre_completo ORDER BY ventas DESC;
	
-- Ticket promedio por cliente en $:
SELECT CONCAT(nombre, ' ', apellido) AS nombre_completo, ROUND(AVG(cantidad), 2) AS ticket_promedio FROM clientes 
	LEFT JOIN pagos USING(cliente_id) GROUP BY cliente_id, nombre_completo ORDER BY ticket_promedio DESC;
	
-- Contribución por categoría (Margen):

-- SELECT SUM(cantidad) FROM pagos; --> 61312.04 (Total vendido)
SELECT nombre, ROUND((SUM(cantidad)/(SELECT SUM(cantidad) FROM pagos))*100, 3) AS margen_porcentual_por_categoria FROM categorias
	LEFT JOIN peliculas_categorias USING(categoria_id)
	INNER JOIN peliculas USING(pelicula_id) INNER JOIN inventarios USING(pelicula_id)
	INNER JOIN rentas USING(inventario_id) INNER JOIN pagos USING(renta_id) GROUP BY nombre ORDER BY margen_porcentual_por_categoria DESC;

Abril Darynka Tapia Sosa

student•

Hice esta pequeña consulta con los datos de la BD para saber que actores han participado en más películas

SELECT COUNT(ac.actor_id) as peliculas_x_actor, CONCAT(ac.nombre, ' ', ac.apellido)
FROM peliculas_actores pa INNER JOIN actores ac ON pa.actor_id=ac.actor_id  
GROUP BY pa.actor_id, ac.nombre, ac.apellido
ORDER BY  peliculas_x_actor DESC

Creck Brayan Mauri Pastor Paredes

student•

genial :)

Ciro Villafraz

student•

Las 10 películas más rentadas

SELECT p.titulo, COUNT(p.pelicula_id) as c FROM peliculas as P
JOIN inventarios as i ON i.pelicula_id = p.pelicula_id
JOIN rentas as r on r.inventario_id = i.inventario_id
GROUP BY p.titulo, i.pelicula_id
ORDER BY c DESC LIMIT 10

Mario Alexander Vargas Celis

student•

Estructura de una Base de Datos de Ejemplo

Cuando se diseña una base de datos de ejemplo, generalmente se busca representar un escenario realista que permita a los usuarios aprender o trabajar con conceptos clave. Aquí te presento una descripción común de cómo estructurar una base de datos de ejemplo, junto con un ejemplo basado en un sistema de gestión de películas.

Componentes principales de la estructura

Tablas
- Representan entidades o conceptos del sistema.
- Cada tabla contiene filas (registros) y columnas (atributos).
Llaves primarias y foráneas
- Llave primaria (Primary Key): Identifica de manera única cada fila en una tabla.
- Llave foránea (Foreign Key): Representa relaciones entre tablas.
Relaciones
- Uno a uno (1:1): Cada registro de una tabla está asociado con un único registro de otra tabla.
- Uno a muchos (1:N): Un registro de una tabla está relacionado con varios registros de otra.
- Muchos a muchos (N:M): Una relación compleja entre dos tablas, que generalmente se maneja con una tabla intermedia.
Índices
- Mejoran el rendimiento de las consultas al optimizar la búsqueda de datos.
Vistas
- Consultas predefinidas que actúan como tablas virtuales para facilitar la consulta de datos complejos.
Procedimientos almacenados
- Fragmentos de lógica SQL que se pueden reutilizar.

Ejemplo: Sistema de Gestión de Películas

A continuación, una estructura simplificada de base de datos:

Tablas principales

Películas (peliculas**)**CREATE TABLE peliculas ( id SERIAL PRIMARY KEY, titulo TEXT NOT NULL, clasificacion VARCHAR(5), duracion INTEGER, -- En minutos anio_publicacion INTEGER, precio_renta NUMERIC(5,2) );
- Representa las películas disponibles en el sistema.
Actores (actores**)**CREATE TABLE actores ( id SERIAL PRIMARY KEY, nombre_completo TEXT NOT NULL, fecha_nacimiento DATE, nacionalidad TEXT );
- Contiene información de los actores.
Géneros (generos**)**CREATE TABLE generos ( id SERIAL PRIMARY KEY, nombre TEXT NOT NULL UNIQUE );
- Define los géneros de las películas.
Relación Películas-Actores (peliculas_actores**)**CREATE TABLE peliculas_actores ( pelicula_id INTEGER REFERENCES peliculas(id) ON DELETE CASCADE, actor_id INTEGER REFERENCES actores(id) ON DELETE CASCADE, PRIMARY KEY (pelicula_id, actor_id) );
- Representa la relación "Muchos a Muchos" entre películas y actores.
Relación Películas-Géneros (peliculas_generos**)**CREATE TABLE peliculas_generos ( pelicula_id INTEGER REFERENCES peliculas(id) ON DELETE CASCADE, genero_id INTEGER REFERENCES generos(id) ON DELETE CASCADE, PRIMARY KEY (pelicula_id, genero_id) );
- Relación "Muchos a Muchos" para asociar géneros con películas.
Usuarios (usuarios**)**CREATE TABLE usuarios ( id SERIAL PRIMARY KEY, nombre TEXT NOT NULL, correo TEXT UNIQUE NOT NULL, fecha_registro DATE DEFAULT CURRENT_DATE );
- Representa a los usuarios que alquilan las películas.
Rentas (rentas**)**CREATE TABLE rentas ( id SERIAL PRIMARY KEY, usuario_id INTEGER REFERENCES usuarios(id), pelicula_id INTEGER REFERENCES peliculas(id), fecha_renta DATE DEFAULT CURRENT_DATE, fecha_devolucion DATE );
- Registra las películas rentadas por los usuarios.

Relaciones del modelo

Películas y Actores: Relación Muchos a Muchos, gestionada por peliculas_actores.
Películas y Géneros: Relación Muchos a Muchos, gestionada por peliculas_generos.
Usuarios y Rentas: Relación Uno a Muchos, donde un usuario puede tener varias rentas.
Películas y Rentas: Relación Uno a Muchos, donde cada renta está asociada a una película.

Ejemplo de consultas comunes

**Obtener todas las películas de un género específico:**SELECT p.titulo FROM peliculas p JOIN peliculas_generos pg ON p.id = pg.pelicula_id JOIN generos g ON g.id = pg.genero_id WHERE g.nombre = 'Acción';
**Listar los actores de una película:**SELECT a.nombre_completo FROM actores a JOIN peliculas_actores pa ON a.id = pa.actor_id WHERE pa.pelicula_id = 1; -- ID de la película
**Películas rentadas por un usuario específico:**SELECT p.titulo, r.fecha_renta FROM rentas r JOIN peliculas p ON r.pelicula_id = p.id WHERE r.usuario_id = 5; -- ID del usuario
**Insertar una nueva renta:**INSERT INTO rentas (usuario_id, pelicula_id) VALUES (5, 2); -- Usuario 5 renta la película 2

Ventajas de esta estructura

Modularidad: Las tablas se dividen en entidades específicas, lo que facilita el mantenimiento.
Escalabilidad: Es fácil añadir más tablas o columnas si el sistema crece.
Consistencia: Uso de claves primarias y foráneas para mantener integridad referencial.

Sergio Carbajal Romero

student•

Sinceramente el Diagrama ERD generado por pgadmin es un asco

Luis Quiroz Prada

student•

si una pelicular tiene varios idiomas y un idioma puede estar en varias películas, por que no se crea también la tabla relacional de ellas?

Christian Mahonry Colorado Bulbarela

student•

Es un error en el diseño, afirmativamente tendría que existir una tabla relacional

JAVIER SANTIAGO SALGADO

student•

Si quedo como si cada película solo pudiera tener un lenguaje.

Mauricio Anaya Pineda

student•

¿El campo “direccion2” de la tabla direcciones es correcto ? Pienso que esa tabla surgio de la normalización de las “direcciones” y poner el campo "direcciones2" le quita un poco de sentido a la normalización de esa tabla.

Joel Sierra Mendoza

student•

Hay veces que se piden 2 direcciones por si hay problemas con una, por ejemplo si tienes otra dirección de confianza para dejar algo en caso que nadie atienda en la dirección principal.

Angel Vega

student•

Reto

Me interesaria saber que peliculas, existen en cada tienda, haría un select a inventarios y haría un inner join donde pelicula_id de inventarios es igual a pelicula_id de peliculas

Felipe Camacho Hurtado

student•

Considero que la siguiente información a ser extraida podría ser util:

Pelicula mas rentada.
Actor con mas peliculas.
Usuario con mayor rentas.
Usuario con mayor pagos.
Tiendas con mayor trabajadores.
Ciudades con menos usuarios.

Osvaldo Olguín

student•

Los actores con las categorías de películas en que más participan.

SELECT a.nombre, a.apellido, c.nombre, COUNT(p.pelicula_id)
FROM actores a
JOIN peliculas_actores pa ON a.actor_id=pa.actor_id
JOIN peliculas p ON pa.pelicula_id=p.pelicula_id
JOIN peliculas_categorias pc ON p.pelicula_id=pc.pelicula_id
JOIN categorias c ON pc.categoria_id=c.categoria_id
GROUP BY a.nombre, a.apellido, c.nombre
ORDER BY COUNT(p.pelicula_id) DESC;

Fue harta prueba y error pero di con el resultado.

Francisco Garcia [C6]

student•

Excelente, el manejo de cursores

![](

Jordan Sobrino Santos

student•

¿Alguien sabe en PgAdmin como se puede hacer ingeniería inversa para reproducir el esquema o la estructura del cual nos basamos para crear nuestras tablas? tal cual como muestra el modelado de datos el Prof. Israel.

Eduardo Lujan

student•

No entiendo bien a que te refieres

Ivan Acosta

student•

Quizás te sirve esta aplicación DBeaver. Puedes descargarlo aquí

José Rodrigo Arana Hi

student•

Una duda que me atormenta: como en la tabla de "pagos" incluye a clientes_id, empleado_id y renta_id, ¿No bastaría con solo poner la renta_id, pues en tabla de "rentas" ya incluye información de el cliente y el empleado?

Jimmy Naranjo

student•

No hace falta el campo cantidad_disponibles en la tabla inventarios?

Oscar David Tizol

student•

Asumo que cada película tiene su propio ID, aunque sea la misma película como tal. Posiblemente es la razón

Jose Larsen

student•

súper cool todo esto :3

Carla Carrasco

student•

Me gustaría saber : -Zona con más ventas. -Top ten de tiendas con mayores ventas. -Las tres tiendas con menores ventas.

Vendedores destacados por zona. -Edad promedio de los clientes. -Los 3 géneros más rentables. -Actores más populares.

Cesar Augusto Herrera Rosales

student•

Se me ocurre listar las tiendas TOP en base a cuánto dinero recaudó por el alquiler. También se me ocurre listar las películas TOP alquiladas, sin importar la tienda, luego se podría buscar las tiendas TOP en donde se alquiló poco las películas TOP, es decir, sería interesante ver por qué en algunas tiendas no se alquiló mucho las películas top.

Alejandro Jose Ysea Gonzalez

student•

Este es mi Query para calcular el Top 10 de peliculas con mas alta facturacion.

SELECT pelicula_id, titulo, SUM(cantidad)
FROM (
	SELECT pe.pelicula_id, pe.titulo, pa.cantidad FROM public.peliculas AS pe
INNER JOIN public.inventarios AS i  ON pe.pelicula_id = i.pelicula_id
INNER JOIN public.rentas AS r ON i.inventario_id = r.inventario_id
INNER JOIN public.pagos AS pa ON r.renta_id = pa.renta_id
) AS Tabla_facturacion
GROUP BY pelicula_id, titulo
ORDER BY SUM(cantidad) desc
LIMIT 10;

Ronny Jair Mora Rojas

student•

Por si eres principiante, como yo, aquí van algunas consultas de nivel básico (MUY BÁSICO) para calentar:

Película con mayor precio de renta

Top 5 de peliculas con mayor precio de renta

Top 3 de películas con mayor duración donde incluya la clasificación

Jesús Rafael Hernández Montero

student•

¿Qué empleado ha registrado el mayor numero de pagos?

SELECT COUNT(pagos.pago_id) as pagos_x_empleado, CONCAT(empleados.nombre, ' ', empleados.apellido) as trabajador
FROM pagos
INNER JOIN empleados ON pagos.empleado_id = empleados.empleado_id
GROUP BY empleados.empleado_id
ORDER BY pagos_x_empleado DESC;

Diagrama Entidad-Relación en Sistemas de Renta de Películas

Introdución a las Bases de Datos

Aplicación de PostgreSQL en Ciencia de Datos

Importación de Bases de Datos en PgAdmin 4

Historia y Evolución de las Bases de Datos Relacionales

Fundamentos de Bases de Datos Relacionales para Científicos de Datos

Conceptos Fundamentales de Bases de Datos Relacionales

Sentencias SQL: Select, Where, Group By y Order By

Ciencia de Datos

Rol y Funciones del Científico de Datos en Empresas

Responsabilidades del Científico de Datos en la Toma de Decisiones

Roles y Perfiles en Equipos de Ciencia de Datos

Diferencias y similitudes entre Machine Learning y Data Science

Particularidades de PostgreSQL

Diferencias entre PostgreSQL y otros gestores de bases de datos

Fundamentos de la Programación en Python

Procedimientos y Funciones en PostgreSQL: Creación y Uso

Creación y uso de funciones y triggers en bases de datos SQL

Funciones SQL para Ciencia de Datos: Consultas y Reportes Prácticos

Lenguajes Procedurales en PostgreSQL: PL/pgSQL y Extensiones Python

Definición de Tipos de Datos Personalizados en Bases de Datos

Casos Prácticos

Diagrama Entidad-Relación en Sistemas de Renta de Películas

Agregación de Datos en SQL: Max, Min, Suma y Promedio

Planeación y Presentación Efectiva de Datos para Científicos de Datos

Manipulación de Datos JSON en PostgreSQL

Manipulación de Datos JSON en Bases de Datos Relacionales

Tablas Recursivas e Interactivas en SQL con Common Table Expressions

Funciones de Ventana en SQL para Ordenamiento y Rango de Datos

Manejo de Particiones en Bases de Datos: Ventajas y Desventajas

Platzi movies dashboard

Creación de Dashboards con SQL para Análisis de Negocios

Top 10 Películas Más Rentadas: Consulta SQL Paso a Paso

Actualización de Precios de Películas con Tipos de Cambio en SQL

Rangos y Percentiles con Funciones de Ventana en SQL

Agrupación de Datos Geográficos por Ciudades en SQL

Análisis de Datos con Líneas de Tiempo en SQL

Visualización de Datos con Tableau para Científicos de Datos

Siguientes pasos

Herramientas Esenciales para Ciencia de Datos y SQL

Compartu tu proyecto de Platzi Movies Dashboard y certifícate