Estructura de Bases de Datos en PostgreSQL para Transporte

Clase 11 de 32 • Curso de PostgreSQL

Contenido del curso

Configurar Postgres

Gestión de la información en bases de datos

Generar consultas avanzadas

Integrar bases de datos con servicios externos

Implementar mejores prácticas

Tomar examen

Toda jerarquía de base de datos se basa en los siguientes elementos:

Servidor de base de datos: Computador que tiene un motor de base de datos instalado y en ejecución.
Motor de base de datos: Software que provee un conjunto de servicios encargados de administrar una base de datos.
Base de datos: Grupo de datos que pertenecen a un mismo contexto.
Esquemas de base de datos en PostgreSQL: Grupo de objetos de base de datos que guarda relación entre sí (tablas, funciones, relaciones, secuencias).
Tablas de base de datos: Estructura que organiza los datos en filas y columnas formando una matriz.

PostgreSQL es un motor de base de datos.

La estructura de la base de datos diseñada para el reto corresponde a los siguientes elementos:

La base de datos se llama transporte, usaremos su esquema predeterminado public.

El esquema public contiene las siguientes tablas:

Estación
Pasajero
Tren

Y las tablas de relaciones entre cada uno de los elementos anteriores son:

Trayecto
Viaje

El esquema relacional entre las tablas corresponde al siguiente diagrama:

Estación Contiene la información de las estaciones de nuestro sistema, incluye datos de nombre con tipo de dato texto y dirección con tipo de dato texto, junto con un número de identificación único por estación.

Tren Almacena la información de los trenes de nuestro sistema, cada tren tiene un modelo con tipo de dato texto y una capacidad con tipo de dato numérico que representa la cantidad de personas que puede llevar ese tren, también tiene un ID único por tren.

Trayecto Relaciona los trenes con las estaciones, simula ser las rutas que cada uno de los trenes pueden desarrollar entre las estaciones

Pasajero Es la tabla que contiene la información de las personas que viajan en nuestro sistema de transporte masivo, sus columnas son nombre tipo de dato texto con el nombre completo de la persona, direccion_residencia con tipo de dato texto que indica dónde vive la persona, fecha_nacimiento tipo de dato texto y un ID único tipo de dato numérico para identificar a cada persona.

Viaje Relaciona Trayecto con Pasajero ilustrando la dinámica entre los viajes que realizan las personas, los cuales parten de una estación y se hacen usando un tren.

Comentarios

Joaquin Fontela

student•

Pregunta: un trayecto no deberia tener la estacion de inicio y la estacion de fin?

Miguel Anselmo Carbajal Lévano

student•

Pienso lo mismo, tendría mas sentido.

Carlos Gutierrez

student•

Si debería tenerla tiene mas sentido a que solo contenga la estación de salida

Sergio Brandon De Lucio Chavero

student•

Voy a hacer todo desde la consola de postgresql, acompañenme en esta aventura :)

Reinaldo Mendoza

student•

jaja, esa es la actitud

Julián Cárdenas

student•

Con todos los powers!

Marlon Adrian González Garzona

student•

a esto se le llama normalizar verdad? al echo de dejar las entidades lo mas especifico posible aun utilizando mas tablas dejar la informacion lo mejor explicada posible

Mario David Herrera Vásquez

student•

Así es, son las formas normales que toda base de datos debe tener, se aplican las formas normales 1,2,3,4. entre mas normalices tu base de datos, mejor administrado sera tu BD

Miguel Angel Muñoz Pozos

student•

Así es pero si requieres reforzar estos conocimientos te recomiendo Fundamentos de Bases de Datos

Fernando Jorge Bardelli

student•

No me queda del todo claro la diferencia entre esquema y base datos, es como un paso intermedio que no comprendo y que no esta en otros motores de bases de datos como mysql o mssql

Sergio Duarte

student•

Buenas:

Un esquema relacional sería la imagen que se muestra arriba en la que aparecen distintas tablas como son viaje, tren, pasajeros y sus relaciones, y funciones entre ellas. Una tabla de base de datos sería pasajeros solamente. En cuanto a la diferencia entre esquema y base de datos, es sólo una forma de llamarlo distinta, pero son realmente lo mismo con distinto nombre. Es como llamar a un perro, perro o canino, es lo mismo pero con distinto nombre.

Fernando Jorge Bardelli

student•

Gracias

Liliana Londono Agudelo

student•

Hay algo que no me queda claro y es porque los trayectos solo pueden tener un tren y no al revés. Porque por ejemplo en mi ciudad el trayecto linea B tiene muchos trenes pero en diferentes estaciones en determinado momento. Alguien me puede explicar por favor.

Omar Daniel Centeno

student•

En este caso el profe se está basando en tomar un tren por cada tabla al igual que una sola persona al marcarlas como tangibles, quizá más adelante se modifique. Tu lógica también es válida y es lo que muchos pensamos. ¿Cómo te fue al terminar el curso?.

Irving Juárez

student•

Yo creo que se hace por simplicidad al momento de crear la base de datos

Bryan Herrera

student•

Si a alguien le sirve hoy en dia les dejo el modelo que yo realice con su respectivo codigo de creacion.

CREATE TABLE "Estaciones" (
  "id" int PRIMARY KEY,
  "nombre" varchar,
  "direccion" varchar
);

CREATE TABLE "Trenes" (
  "id" int PRIMARY KEY,
  "modelo" varchar,
  "capacidad" int
);

CREATE TABLE "Pasajeros" (
  "id" int PRIMARY KEY,
  "nombre" varchar,
  "residencia" int,
  "fecha" datetime
);

CREATE TABLE "trayectos" (
  "id" int PRIMARY KEY,
  "nombre_ruta" varchar,
  "id_estacion_inicio" int,
  "id_estacion_fin" int,
  "id_tren" int
);

CREATE TABLE "viajes" (
  "id" int PRIMARY KEY,
  "id_pasajero" int,
  "id_trayecto" int,
  "inicio" datetime,
  "fin" datetime
);

ALTER TABLE "trayectos" ADD FOREIGN KEY ("id_estacion_inicio") REFERENCES "Estaciones" ("id");

ALTER TABLE "trayectos" ADD FOREIGN KEY ("id_estacion_fin") REFERENCES "Estaciones" ("id");

ALTER TABLE "trayectos" ADD FOREIGN KEY ("id_tren") REFERENCES "Trenes" ("id");

ALTER TABLE "viajes" ADD FOREIGN KEY ("id_pasajero") REFERENCES "Pasajeros" ("id");

CREATE TABLE "trayectos_viajes" (
  "trayectos_id" int NOT NULL,
  "viajes_id_trayecto" int NOT NULL,
  PRIMARY KEY ("trayectos_id", "viajes_id_trayecto")
);

ALTER TABLE "trayectos_viajes" ADD FOREIGN KEY ("trayectos_id") REFERENCES "trayectos" ("id");

ALTER TABLE "trayectos_viajes" ADD FOREIGN KEY ("viajes_id_trayecto") REFERENCES "viajes" ("id_trayecto");

Julián Cárdenas

student•

BRUTAL!

Juan David Suarez

student•

Muy claro con esto y el video anterior. Veo que lo que siempre llega a ser más complicado es definir las cardinalidades (si es uno a muchos, muchos a muchos y así)

Omar Daniel Centeno

student•

Bastante, y a partir de las cardinalidades puedes también definir si separar los datos para que no se repitan. Es un trabajo que se debe hacer con detenimiento.

Deivy jesus lara munelo

student•

buena la documentación de la base de datos detallada y precisa, unos de los problemas que se comenten es no documentar lo cual es necesario para cuando se integran nuevas personas al proyecto.

Usuario anónimo

user•

cuando es una realcion de muchos a muchos es necesario siempre implementar una tabla intermedia que permita tener una comunicacion entre estas dos?

Giovanni Rugerio

student•

Según lo aprendido, siempre es recomendable solucionar las relaciones muchos a muchos, pero claro, dependerá de la situación y que tan normalizado quieras el diseño.

Luis Miguel Taque Diaz

student•

Hola Si, esto tiene impacto en la consistencia de la información que se almacena y a futuro un costo importante en el procesamiento de las consultas.....

Carlos Hernández

student•

En el curso de fundamentos de base se datos con el profesor Israel, resuelven la relación muchos a muchos con una tabla pivote, de esta manera cambian una relación de muchos a muchos, por una relación de uno a muchos de la primera entidad con la tabla pivote y otra relación de uno a muchos de la segunda entidad con la tabla pivote, ¿Cuál de las dos maneras sería más recomendable?

Oswaldo Rodríguez González

teacher•

La que te permita tener los datos normalizados.

Juan Pablo Sánchez

student•

para los que quieren hacer ejercicios prácticos, encontré esta pagina donde hay ejercicios para todos los temas de PostgreSQL. https://titiushko.github.io/Tutoriales-Ya/www.postgresqlya.com.ar/index2904.html?inicio=0

Christian Rangel

student•

Que bien Juan Pablo, gracias por compartir!

Jazmín Bazán

student•

Wilmer Ropero C

student•

Me queda la duda de si la la tabla viaje con la tabla persona no debería ser muchos a muchos a muchos (N;N)

Lo anterior porque si es un tren el que hace el viaje, lleva mucha mas gente, entonces un viaje esta relacionado con mas de una persona y una persona esta relacionada con varios viajes.

En la tabla viaje le pondría una etiqueta o estado que sea, viaje finalizado o abierto.

Quede un poco confundido, aunque si se que depende de la lógica del negocio y que esto es mas ilustrativo para aprender a usar la BD, además la clase de fundamentos de base de datos me ayudo mucho. Probablemente esto tenga diferentes maneras de pensarse. 🙃🤯

Romimaker

teacher•

Hola Wilmer, la relación muchos a muchos la tendría pasajero con trayecto, y por lo que entiendo, viaje es la tabla transitiva que rompe esa relación. La interpretación sería que en un momento determinado un pasajero sólo puede estar en un viaje con un determinado trayecto.

Royer Guerrero Pinilla

student•

Pues tienes razón pero en este caso nos interesa llevar control de los viajes que hace un pasajero, no los pasajeros que lleva el tren. Todo varia de que necesitas

Jhon Freddy Tavera Blandon

student•

creación de tablas como la adición de claves foráneas en un solo bloque para facilitar su ejecución.

-- Crear la tabla Pasajero
CREATE TABLE Pasajero (
    id_pasajero SERIAL PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    edad INT,
    correo VARCHAR(255)
);

-- Crear la tabla Trayecto
CREATE TABLE Trayecto (
    id_trayecto SERIAL PRIMARY KEY,
    origen VARCHAR(100) NOT NULL,
    destino VARCHAR(100) NOT NULL,
    distancia DECIMAL(10, 2) NOT NULL
);

-- Crear la tabla Estación
CREATE TABLE Estación (
    id_estacion SERIAL PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    ubicacion VARCHAR(255)
);

-- Crear la tabla Tren
CREATE TABLE Tren (
    id_tren SERIAL PRIMARY KEY,
    modelo VARCHAR(100) NOT NULL,
    capacidad INT NOT NULL,
    velocidad INT NOT NULL
);

-- Crear la tabla Viaje
CREATE TABLE Viaje (
    id_viaje SERIAL PRIMARY KEY,
    fecha DATE NOT NULL,
    hora_salida TIME NOT NULL,
    hora_llegada TIME NOT NULL,
    id_tren INT REFERENCES Tren(id_tren),
    id_trayecto INT REFERENCES Trayecto(id_trayecto)
);

-- Crear la tabla Pasajero_Viaje
CREATE TABLE Pasajero_Viaje (
    id_pasajero INT REFERENCES Pasajero(id_pasajero),
    id_viaje INT REFERENCES Viaje(id_viaje),
    asiento_numero INT,
    estado_pago VARCHAR(50),
    PRIMARY KEY (id_pasajero, id_viaje)
);

-- Crear la tabla Trayecto_Estación
CREATE TABLE Trayecto_Estación (
    id_trayecto INT REFERENCES Trayecto(id_trayecto),
    id_estacion INT REFERENCES Estación(id_estacion),
    orden_en_trayecto INT,
    PRIMARY KEY (id_trayecto, id_estacion)
);

-- Agregar claves foráneas a la tabla Pasajero_Viaje
ALTER TABLE Pasajero_Viaje
ADD CONSTRAINT fk_pasajero
FOREIGN KEY (id_pasajero)
REFERENCES Pasajero(id_pasajero);

ALTER TABLE Pasajero_Viaje
ADD CONSTRAINT fk_viaje_pasajero
FOREIGN KEY (id_viaje)
REFERENCES Viaje(id_viaje);

-- Agregar claves foráneas a la tabla Trayecto_Estación
ALTER TABLE Trayecto_Estación
ADD CONSTRAINT fk_trayecto
FOREIGN KEY (id_trayecto)
REFERENCES Trayecto(id_trayecto);

ALTER TABLE Trayecto_Estación
ADD CONSTRAINT fk_estacion
FOREIGN KEY (id_estacion)
REFERENCES Estación(id_estacion);

-- Agregar claves foráneas a la tabla Viaje
ALTER TABLE Viaje
ADD CONSTRAINT fk_tren
FOREIGN KEY (id_tren)
REFERENCES Tren(id_tren);


```ALTER TABLE Viaje
ADD CONSTRAINT fk_trayecto_viaje
FOREIGN KEY (id_trayecto)
REFERENCES Trayecto(id_trayecto);

-- Agregar claves foráneas a la tabla Pasajero_Viaje
ALTER TABLE Pasajero_Viaje
ADD CONSTRAINT fk_pasajero_viaje
FOREIGN KEY (id_pasajero, id_viaje)
REFERENCES Viaje(id_pasajero, id_viaje);

Hinder Adrian Alvarez Perlaza

student•

Por finn entiendo esto de los schemas usados en base de datos

Omar Daniel Centeno

student•

Lo hicieron bastante sencillo, que bueno.

Jazmín Bazán

student•

DAVID EDUARDO BAEZ SANCHEZ

student•

Entendido.

Carlos Daniel Agamez Palomino

student•

como se añaden las llaves foraneas desde la interfaz grafica ?

EMMANUEL DE JESUS VELASQUEZ MARTINEZ

student•

Alguien sabe en que software o web hicieron el diagrama ERD?

Aldo Mauricio Vital Gutierrez

student•

Pueden utilizar el siguiente programa web https://www.drawio.com a mi parecer es muy eficiente y es practico de utilizar.

Christian Rangel

student•

El esquema de base de datos es la base de datos persé con tablas y relaciones, en cambio base de datos son los datos agrupados sin tomar en cuenta las relaciones de las tablas. Estoy entendiendo bien o mal?

Juan Pablo Sánchez

student•

donde puedo encontrar mas ejercicios de estos con respuestas para ir validando lo que voy haciendo?

CREATE TABLE "Estaciones" (
  "id" int PRIMARY KEY,
  "nombre" varchar,
  "direccion" varchar
);

CREATE TABLE "Trenes" (
  "id" int PRIMARY KEY,
  "modelo" varchar,
  "capacidad" int
);

CREATE TABLE "Pasajeros" (
  "id" int PRIMARY KEY,
  "nombre" varchar,
  "residencia" int,
  "fecha" datetime
);

CREATE TABLE "trayectos" (
  "id" int PRIMARY KEY,
  "nombre_ruta" varchar,
  "id_estacion_inicio" int,
  "id_estacion_fin" int,
  "id_tren" int
);

CREATE TABLE "viajes" (
  "id" int PRIMARY KEY,
  "id_pasajero" int,
  "id_trayecto" int,
  "inicio" datetime,
  "fin" datetime
);

ALTER TABLE "trayectos" ADD FOREIGN KEY ("id_estacion_inicio") REFERENCES "Estaciones" ("id");

ALTER TABLE "trayectos" ADD FOREIGN KEY ("id_estacion_fin") REFERENCES "Estaciones" ("id");

ALTER TABLE "trayectos" ADD FOREIGN KEY ("id_tren") REFERENCES "Trenes" ("id");

ALTER TABLE "viajes" ADD FOREIGN KEY ("id_pasajero") REFERENCES "Pasajeros" ("id");

CREATE TABLE "trayectos_viajes" (
  "trayectos_id" int NOT NULL,
  "viajes_id_trayecto" int NOT NULL,
  PRIMARY KEY ("trayectos_id", "viajes_id_trayecto")
);

ALTER TABLE "trayectos_viajes" ADD FOREIGN KEY ("trayectos_id") REFERENCES "trayectos" ("id");

ALTER TABLE "trayectos_viajes" ADD FOREIGN KEY ("viajes_id_trayecto") REFERENCES "viajes" ("id_trayecto");

-- Crear la tabla Pasajero
CREATE TABLE Pasajero (
    id_pasajero SERIAL PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    edad INT,
    correo VARCHAR(255)
);

-- Crear la tabla Trayecto
CREATE TABLE Trayecto (
    id_trayecto SERIAL PRIMARY KEY,
    origen VARCHAR(100) NOT NULL,
    destino VARCHAR(100) NOT NULL,
    distancia DECIMAL(10, 2) NOT NULL
);

-- Crear la tabla Estación
CREATE TABLE Estación (
    id_estacion SERIAL PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    ubicacion VARCHAR(255)
);

-- Crear la tabla Tren
CREATE TABLE Tren (
    id_tren SERIAL PRIMARY KEY,
    modelo VARCHAR(100) NOT NULL,
    capacidad INT NOT NULL,
    velocidad INT NOT NULL
);

-- Crear la tabla Viaje
CREATE TABLE Viaje (
    id_viaje SERIAL PRIMARY KEY,
    fecha DATE NOT NULL,
    hora_salida TIME NOT NULL,
    hora_llegada TIME NOT NULL,
    id_tren INT REFERENCES Tren(id_tren),
    id_trayecto INT REFERENCES Trayecto(id_trayecto)
);

-- Crear la tabla Pasajero_Viaje
CREATE TABLE Pasajero_Viaje (
    id_pasajero INT REFERENCES Pasajero(id_pasajero),
    id_viaje INT REFERENCES Viaje(id_viaje),
    asiento_numero INT,
    estado_pago VARCHAR(50),
    PRIMARY KEY (id_pasajero, id_viaje)
);

-- Crear la tabla Trayecto_Estación
CREATE TABLE Trayecto_Estación (
    id_trayecto INT REFERENCES Trayecto(id_trayecto),
    id_estacion INT REFERENCES Estación(id_estacion),
    orden_en_trayecto INT,
    PRIMARY KEY (id_trayecto, id_estacion)
);

-- Agregar claves foráneas a la tabla Pasajero_Viaje
ALTER TABLE Pasajero_Viaje
ADD CONSTRAINT fk_pasajero
FOREIGN KEY (id_pasajero)
REFERENCES Pasajero(id_pasajero);

ALTER TABLE Pasajero_Viaje
ADD CONSTRAINT fk_viaje_pasajero
FOREIGN KEY (id_viaje)
REFERENCES Viaje(id_viaje);

-- Agregar claves foráneas a la tabla Trayecto_Estación
ALTER TABLE Trayecto_Estación
ADD CONSTRAINT fk_trayecto
FOREIGN KEY (id_trayecto)
REFERENCES Trayecto(id_trayecto);

ALTER TABLE Trayecto_Estación
ADD CONSTRAINT fk_estacion
FOREIGN KEY (id_estacion)
REFERENCES Estación(id_estacion);

-- Agregar claves foráneas a la tabla Viaje
ALTER TABLE Viaje
ADD CONSTRAINT fk_tren
FOREIGN KEY (id_tren)
REFERENCES Tren(id_tren);


```ALTER TABLE Viaje
ADD CONSTRAINT fk_trayecto_viaje
FOREIGN KEY (id_trayecto)
REFERENCES Trayecto(id_trayecto);

-- Agregar claves foráneas a la tabla Pasajero_Viaje
ALTER TABLE Pasajero_Viaje
ADD CONSTRAINT fk_pasajero_viaje
FOREIGN KEY (id_pasajero, id_viaje)
REFERENCES Viaje(id_pasajero, id_viaje);

Estructura de Bases de Datos en PostgreSQL para Transporte

Configurar Postgres

Instalación y Configuración de Podres para Bases de Datos

Fundamentos de Postgres: Motor, Lenguaje y Servidor de Bases de Datos

Instalación y Configuración de PostgreSQL en Windows

Acceso y uso de la consola de PostgreSQL

Uso de PGAdmin para gestionar bases de datos PostgreSQL

Configuración de Archivos Críticos en PostgreSQL

Comandos esenciales de consola en PostgreSQL

Modelado de un Sistema de Transporte Masivo en Bases de Datos

Tipos de Datos Soportados por PostgreSQL

Diseño de Base de Datos para Sistema de Transporte