Developpement/23BDD/SAE/BDD-23.sql

45 lines
940 B
SQL

CREATE TABLE Gare (
IdGare INT Primary Key,
Ville varchar2(100),
NomGare varchar2(50)
);
CREATE TABLE PersonneP (
idPersonne INT Primary Key,
NomPers varchar2(50),
PrenomPers varchar2(50),
AdressePers varchar2(100),
TelPers varchar2(10)
);
CREATE TABLE Conducteur (
idPersonne INT Primary Key REFERENCES PersonneP,
telPro varchar2(10),
centreRattachement varchar2(50),
datePermis Date
);
CREATE TABLE Train (
idTrain INT Primary Key,
typeTrain varchar2(50),
duplex INT,
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 VoyageV(
IdVoyage INT PRIMARY KEY,
dateCreation DATE,
codeTarif INT,
idTrain INT,
DateDepart DATE,
heureDepart INT,
Foreign Key (idTrain,dateDepart, heureDepart) REFERENCES Trajet
);