Tablas y Esquemas en PostgreSQL: Administrar Datos de Transporte

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

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

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

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

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");

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.

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í)

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.

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

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

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. 🙃🤯

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);

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

Entendido.

Cuando usar diferentes esquemas? Es decir acá quedó todo en el public pero supongo que también se pueden crear otros

Elabore un modelo de datos físico con la herramienta Power Designer

Y posteriormente genere su script con la herramienta en PostreSQL, espero les sirva c:
//
/ DBMS name: PostgreSQL 8 /
/ Created on: 29/03/2024 20:52:54 /
//

drop index ESTACION_PK;

drop table ESTACION;

drop index PASAJERO_PK;

drop table PASAJERO;

drop index TRAYECTO_PK;

drop table TRAYECTO;

drop index TREN_PK;

drop table TREN;

drop index VIAJE_PK;

drop table VIAJE;

//
/ Table: ESTACION /
//
create table ESTACION (
IDESTACION SERIAL not null,
NOMBRE VARCHAR(50) null,
DIRECCION VARCHAR(50) null,
constraint PK_ESTACION primary key (IDESTACION)
);

//
/ Index: ESTACION_PK /
//
create unique index ESTACION_PK on ESTACION (
IDESTACION
);

//
/ Table: PASAJERO /
//
create table PASAJERO (
IDPASAJERO SERIAL not null,
NOMBRE VARCHAR(50) null,
DIRECCION_RESIDENCIA VARCHAR(100) null,
FECHA_NACIMIENTO DATE null,
constraint PK_PASAJERO primary key (IDPASAJERO)
);

//
/ Index: PASAJERO_PK /
//
create unique index PASAJERO_PK on PASAJERO (
IDPASAJERO
);

//
/ Table: TRAYECTO /
//
create table TRAYECTO (
IDTRAYECTO SERIAL not null,
IDESTACION INT4 not null,
IDTREN INT4 not null,
constraint PK_TRAYECTO primary key (IDTRAYECTO)
);

//
/ Index: TRAYECTO_PK /
//
create unique index TRAYECTO_PK on TRAYECTO (
IDTRAYECTO
);

//
/ Table: TREN /
//
create table TREN (
IDTREN SERIAL not null,
MODELO VARCHAR(50) null,
CAPACIDAD INT4 null,
constraint PK_TREN primary key (IDTREN)
);

//
/ Index: TREN_PK /
//
create unique index TREN_PK on TREN (
IDTREN
);

//
/ Table: VIAJE /
//
create table VIAJE (
IDVIAJE SERIAL not null,
IDTRAYECTO INT4 not null,
IDPASAJERO INT4 not null,
INICIO DATE null,
FIN DATE null,
constraint PK_VIAJE primary key (IDVIAJE)
);

//
/ Index: VIAJE_PK /
//
create unique index VIAJE_PK on VIAJE (
IDVIAJE
);

alter table TRAYECTO
add constraint FK_TRAYECTO_ADQUIERE_TREN foreign key (IDTREN)
references TREN (IDTREN)
on delete restrict on update restrict;

alter table TRAYECTO
add constraint FK_TRAYECTO_TIENE_ESTACION foreign key (IDESTACION)
references ESTACION (IDESTACION)
on delete restrict on update restrict;

alter table VIAJE
add constraint FK_VIAJE_COMPRA_PASAJERO foreign key (IDPASAJERO)
references PASAJERO (IDPASAJERO)
on delete restrict on update restrict;

alter table VIAJE
add constraint FK_VIAJE_ENGLOBA_TRAYECTO foreign key (IDTRAYECTO)
references TRAYECTO (IDTRAYECTO)
on delete restrict on update restrict;

Pienso que cada video/clase de platzi debería las puntuaciones de 0 a 5 estrellas que salen en la evaluación final.

BORRADO DE TABLAS

-- Eliminación de las relaciones
ALTER TABLE trip DROP CONSTRAINT IF EXISTS trip_id_pax_fkey;
ALTER TABLE trip DROP CONSTRAINT IF EXISTS trip_id_schedule_fkey;
ALTER TABLE schedule_route DROP CONSTRAINT IF EXISTS schedule_route_id_route_fkey;
ALTER TABLE schedule_route DROP CONSTRAINT IF EXISTS schedule_route_id_train_fkey;
ALTER TABLE route DROP CONSTRAINT IF EXISTS route_id_station_origin_fkey;
ALTER TABLE route DROP CONSTRAINT IF EXISTS route_id_station_destination_fkey;

-- Eliminación de las tablas
DROP TABLE IF EXISTS trip;
DROP TABLE IF EXISTS schedule_route;
DROP TABLE IF EXISTS route;
DROP TABLE IF EXISTS station;
DROP TABLE IF EXISTS train;
DROP TABLE IF EXISTS pax;

CREACION DE TABLAS

-- Creación de las tablas
CREATE TABLE PAX (
  id_pax SERIAL PRIMARY KEY,
  name_pax VARCHAR(255) NOT NULL,
  type_document VARCHAR(255) NOT NULL,
  document VARCHAR(255) NOT NULL
);

CREATE TABLE TRAIN (
  id_train SERIAL PRIMARY KEY,
  pax_capacity INTEGER NOT NULL,
  type_of_train VARCHAR(255) NOT NULL,
  model VARCHAR(255)
);

CREATE TABLE TRIP (
  id_trip SERIAL PRIMARY KEY,
  id_pax INTEGER REFERENCES PAX(id_pax),
  id_schedule INTEGER REFERENCES SCHEDULE_JOURNEY(id_schedule),
  departure_time TIMESTAMP NOT NULL
);

CREATE TABLE STATION (
  id_station SERIAL PRIMARY KEY,
  name_station VARCHAR(255) NOT NULL,
  address_station VARCHAR(255) NOT NULL,
  capacity INTEGER NOT NULL
);

CREATE TABLE JOURNEY (
  id_journey SERIAL PRIMARY KEY,
  id_station_origin INTEGER REFERENCES STATION(id_station),
  id_station_destination INTEGER REFERENCES STATION(id_station),
  name VARCHAR(255)
);

CREATE TABLE SCHEDULE_JOURNEY (
  id_schedule SERIAL PRIMARY KEY,
  id_journey INTEGER REFERENCES JOURNEY(id_journey),
  id_train INTEGER REFERENCES TRAIN(id_train),
  start_date DATE
);

-- Creación de las relaciones
ALTER TABLE TRIP ADD FOREIGN KEY (id_pax) REFERENCES PAX(id_pax);
ALTER TABLE TRIP ADD FOREIGN KEY (id_schedule) REFERENCES SCHEDULE_JOURNEY(id_schedule);
ALTER TABLE JOURNEY ADD FOREIGN KEY (id_station_origin) REFERENCES STATION(id_station);
ALTER TABLE JOURNEY ADD FOREIGN KEY (id_station_destination) REFERENCES STATION(id_station);
ALTER TABLE SCHEDULE_JOURNEY ADD FOREIGN KEY (id_journey) REFERENCES JOURNEY(id_journey);
ALTER TABLE SCHEDULE_JOURNEY ADD FOREIGN KEY (id_train) REFERENCES TRAIN(id_train);

Que tal, después de un buen rato, el modelo me ha quedado de la siguiente manera

Según el curso de fundamentos de base de datos, de SQL , en las relaciones varios a varios, lo mas inteligente es hacer una tabla transitiva

aca mi diagrama

La clave para entender este modelo es pensar las relaciones sin la variable tiempo, tan solo registra las asociaciones de las entidades fuertes.

Información resumida de esta clase
#EstudiantesDePlatzi

Existe una jerarquía en las bases de datos

• Servidor de base de datos
• Motor de base de datos
• Base de datos
• Esquema de base de datos en PostgreSQL
• Tabla de base de datos

Osea las bases de datos son todos los datos pero el Esquema le da relación y orden a estos datos mediante los objetos como las tablas y relaciones

Los schemas podrian ser la forma en que organizamos nuestros registros en la BBDD

Muy buena síntesis. Tener este tipo de resumen antes de empezar a trabajar en la implementación ayuda mucho. Organiza las tareas que uno tiene que desarrollar

Este vendría a ser un esqueleto del problema y ahora hay que ponerlo en marcha.

Listo tablas creadas! 😃

Muy buen material

Muy bien explicado. Me hacía falta ver un curso así con esta dinámica.

Hay algún motivo por el cual las tablas se pongan en singular? generalmente las encuentro con plurales

Aun no tengo muy claro como hacer la relación desde la interfaz gráfica 😦

Hasta aquí, todo bien. 😃

muy buena la explicación pa que, super bien

Buenos Días.

Saben por qué me aparece este error por favor?
Gracias.

Quedo muy claro con este Articulo

excelente

Muy claro todo, estoy ancioso para seguir con el curso. Es bueno recordad otra vex como se crea una base de datos

Muy practico gracias

de verdad muy practico

Muy detallada la explicación

Buena Explicación

muy buena explicacion para la construccion del MER y sobre todo un buen analiis del reto.

TABLAS DE CATÁLOGO:
Corresponden a Info. Princ. del Proyecto
1.- RUTA (TRAYECTO)*
2.- ESTACION
3.- TREN
4.- TARJETA(PASAJERO)*

TABLA DE DETALLE:
VIAJES <- Aquí se guarda la relación de las tablas de catálogo 1:N

Aunque es un ejemplo sencillo para manejar una base de datos, también es recomendable agregar campos de fecha de alta, baja o modificación, así como, campo de Estatus, si esta vigente o dado de baja para un mejor control de la información al requerir información sobre las consultas.

Muy buen explicado, Es muy útil mezclar los videos con textos para cosas específicas para ayudar a la comprensión.