DROP TABLE Voyage;
DROP TABLE Trajet;
DROP TABLE Train;
DROP TABLE Conducteur;
DROP TABLE Personne;
DROP TABLE Gare;

CREATE TABLE Gare (
IdGare INT Primary Key,
Ville varchar2(100),
NomGare varchar2(50)
);
CREATE TABLE Personne (
idPersonne INT Primary Key,
NomPers varchar2(50),
PrenomPers varchar2(50),
AdressePers varchar2(100),
TelPers varchar2(10)
);
CREATE TABLE Conducteur (
idPersonne INT Primary Key REFERENCES Personne,
telPro varchar2(10),
centreRattachement varchar2(50),
datePermis Date
);
CREATE TABLE Train (
idTrain INT Primary Key,
typeTrain varchar2(50),
duplex Boolean,
idGareDep INT REFERENCES Gare,
idGareArrivee INT REFERENCES Gare,
idPersonne INT REFERENCES Conducteur
);
CREATE TABLE Trajet (
IdTrain INT REFERENCES Train,
dateDepart DATE,
heureDepart INT,
heureArrivee INT,
tarifBase INT,
Primary Key (IdTrain,dateDepart,heureDepart)
);
CREATE TABLE Voyage(
IdVoyage INT PRIMARY KEY,
dateCreation DATE,
codeTarif INT,
idTrain INT,
DateDepart DATE,
heureDepart INT,
Foreign Key (idTrain,dateDepart, heureDepart) REFERENCES Trajet
);