# SQL

## Script

CREATE TABLE Gare
(
    nom VARCHAR NOT NULL,
    ville VARCHAR NOT NULL,
    adresse VARCHAR NOT NULL,
    horaire VARCHAR NOT NULL,
    PRIMARY KEY (nom, ville)
);

CREATE TABLE Ligne (
                        code INTEGER PRIMARY KEY,
                        nom VARCHAR NOT NULL
);

CREATE TABLE Hotel (
                        adresse VARCHAR PRIMARY KEY,
                        nom VARCHAR NOT NULL,
                        nb_etoiles INTEGER NOT NULL
                        
);

CREATE TABLE Taxi (
                        numero INTEGER PRIMARY KEY,
                        marque VARCHAR NOT NULL,
                        tarif INT NOT NULL,
                        tel INT NOT NULL       
);

CREATE TYPE type_de_transport AS ENUM(
                                          'bus',
                                          'velo',
                                          'tramway'
                                          
);

CREATE TABLE Transport (
                            numero INTEGER PRIMARY KEY,
                            compagnie VARCHAR NOT NULL,
                            tarif INTEGER NOT NULL,
                            type type_de_transport NOT NULL
                            
);


CREATE TYPE type_de_statut AS ENUM(
                                          'voyageur',
                                          'grandVoyageur',
                                          'grandVoyageurPlus'

                                          
);

CREATE TABLE Voyageur (
                            id SERIAL PRIMARY KEY,
                            nom VARCHAR NOT NULL,
                            prenom VARCHAR NOT NULL,
                            adresse VARCHAR NOT NULL,
                            num_tel VARCHAR(10) NOT NULL,
                            num_carte VARCHAR(16) UNIQUE,
                            statut type_de_statut,
                            regulier BOOLEAN NOT NULL,
                            CHECK(NOT(regulier AND num_carte IS NULL)OR(statut IS NULL)),
                            CHECK(NOT(regulier AND (num_carte IS NULL OR statut IS NULL)))
);

CREATE TYPE type_de_train AS ENUM(
  'RER',
  'TER',
  'TGV'
);

CREATE TABLE Train(
    num INTEGER PRIMARY KEY, 
    placemax INTEGER,
    classe1 BOOL,
    vitessemax INTEGER,
    type type_de_train,
    CHECK((type='RER' AND NOT classe1 AND vitessemax=90) OR (type='TER' AND classe1 AND vitessemax=200) OR (type='TGV' AND classe1 AND vitessemax=350))
);

CREATE TABLE RegulierC(
    id SERIAL PRIMARY KEY,
    lundi BOOLEAN,
    mardi BOOLEAN,
    mercredi BOOLEAN,
    jeudi BOOLEAN,
    vendredi BOOLEAN,
    samedi BOOLEAN,
    dimanche BOOLEAN
);

CREATE TABLE ExceptionnelC(
    id SERIAL PRIMARY KEY,
    ajout BOOLEAN,
    jour_debut DATE,
    jour_fin DATE,
    
    CHECK (jour_debut <= jour_fin)
);


CREATE TABLE Arret(
    rang INTEGER,
    code_ligne INTEGER,
    nom_gare VARCHAR,
    ville VARCHAR,
    PRIMARY KEY(rang, code_ligne),
    FOREIGN KEY (code_ligne) REFERENCES Ligne (code),
    FOREIGN KEY (nom_gare, ville) REFERENCES Gare(nom, ville )
);

CREATE TABLE Horaire(
    id SERIAL PRIMARY KEY,
    arrivee TIME,
    depart TIME,
    train INTEGER REFERENCES Train(num),
    rang INTEGER,
    code_ligne INTEGER,
    FOREIGN KEY(rang, code_ligne) REFERENCES Arret(rang, code_ligne),
    regulierC INTEGER REFERENCES RegulierC(id)

);

CREATE TABLE Billet (
    id SERIAL PRIMARY KEY,
    gare_depart VARCHAR,
    depart TIME,
    gare_arrivee VARCHAR,
    arrivee TIME,
    assurance BOOLEAN,
    prix INTEGER
);


CREATE TABLE Trajet(
    id INTEGER,
    place INTEGER,
    horaire_depart INTEGER,
    horaire_arrivee INTEGER,
    PRIMARY KEY(place, id),
    FOREIGN KEY (id) REFERENCES Billet(id),
    FOREIGN KEY (horaire_depart) REFERENCES Horaire(id),
    FOREIGN KEY (horaire_arrivee) REFERENCES Horaire(id)
);

CREATE TYPE type_de_paiement AS ENUM(
  'espece',
  'CB',
  'cheque'
);


CREATE TABLE Reservation(
    id_billet INTEGER,
    id_voyageur INTEGER,
    paiement type_de_paiement,
    PRIMARY KEY(id_billet, id_voyageur),
    FOREIGN KEY (id_billet, id_voyageur) REFERENCES Reservation(id_billet, id_voyageur)
);

CREATE TABLE Propose_taxi (
    num_taxi INTEGER,
    id_billet INTEGER,
    id_voyageur INTEGER,
    PRIMARY KEY (num_taxi, id_billet, id_voyageur),
    FOREIGN KEY (num_taxi) REFERENCES Taxi(numero),
    FOREIGN KEY (id_billet, id_voyageur) REFERENCES Reservation(id_billet, id_voyageur)
);


CREATE TABLE Propose_hotel(
    adresse VARCHAR,
    id_billet INTEGER,
    id_voyageur INTEGER,
    PRIMARY KEY(adresse, id_billet, id_voyageur),
    FOREIGN KEY (adresse) REFERENCES Hotel(adresse),
    FOREIGN KEY (id_billet, id_voyageur) REFERENCES Reservation(id_billet, id_voyageur)
);

CREATE TABLE Propose_transport (
    numero INTEGER,
    id_billet INTEGER,
    id_voyageur INTEGER,
    PRIMARY KEY (numero, id_billet, id_voyageur),
    FOREIGN KEY (numero) REFERENCES Transport(numero),
    FOREIGN KEY (id_billet, id_voyageur) REFERENCES Reservation(id_billet, id_voyageur)
);


CREATE TABLE ExceptionnelC_Horaire(
    id_horaire INTEGER,
    exceptionnelC INTEGER,
    FOREIGN KEY (id_horaire) REFERENCES Horaire(id),
    FOREIGN KEY (exceptionnelC) REFERENCES ExceptionnelC(id)
);


CREATE VIEW verifLigne (code_ligne) AS
SELECT Horaire.code_ligne
FROM Horaire
JOIN Train ON Horaire.train = Train.num
WHERE Horaire.code_ligne <> Train.code_ligne

Cette requête ne doit rien retourner (={}).

CREATE VIEW verifTrain (train) AS
SELECT H.train
FROM Trajet T,Horaire H
WHERE T.horaire_depart = H.id
Ces deux résultats doivent être égaux.

=> Ces 2 contraintes complexes doivent être traitées dans la partie applicative avec des vues.

## Insertion de données

INSERT INTO Gare VALUES ('Gare de Compiègne', 'Creil', '7 rue de la Paix', 'UTC + 12');

INSERT INTO Gare VALUES('Gare Saint-Lussier', 'Le Havre', '107 rue de la jointure', 'UTC+12');

INSERT INTO Gare VALUES('Gare de Youcef', 'Compi', '18 avenue de la clé primaire', 'UTC+12');

INSERT INTO Ligne VALUES(12, 'Ligne de Compi');

INSERT INTO Hotel VALUES('12 rue de la Villette', 'Ibis budget', 2);

INSERT INTO Taxi VALUES (206318, 'tesla', 50, 0678903617);

INSERT INTO Transport VALUES (3, 'Ville de Compiegne', 4, 'bus');

INSERT INTO Transport VALUES (2, 'Vélotic', 5, 'velo');

INSERT INTO Voyageur(nom,prenom,adresse,num_tel,num_carte,regulier) VALUES('Jean','AIMARE','5 avenue Foch Paris','066666666', 1234563263526373, false );

INSERT INTO Train VALUES (212, 378, true, 200, 'TER');

INSERT INTO RegulierC VALUES (1,false,true,false,true,false,false,false);

INSERT INTO ExceptionnelC VALUES (1, true, '2021-10-23', '2021-11-04');

INSERT INTO Arret VALUES(1,12,'Gare de Compiègne','Creil');
INSERT INTO Arret VALUES(7,12,'Gare de Youcef','Compi');

INSERT INTO Horaire(arrivee, depart, train, rang,code_ligne) VALUES ('03:07:00','07:09:00', 212, 1, 12);

INSERT INTO Horaire(arrivee, depart, train, rang, code_ligne) VALUES ('11:00:00','11:00:30', 212, 7, 12);



INSERT INTO Billet VALUES(1, 'Gare de Compiègne','00:11:00','Gare Saint-Lussier','00:13:03',false,1);

INSERT INTO Trajet VALUES(1,58,1,2);

INSERT INTO Reservation VALUES(1, 1, 'CB');

INSERT INTO Propose_taxi VALUES(206318, 1, 1);

INSERT INTO Propose_hotel VALUES('12 rue de la Villette', 1,1);

INSERT INTO Propose_transport VALUES(2,1,1);

INSERT INTO ExceptionnelC_Horaire VALUES(1, 1);