#------------------------------------------------------------
#        Script MySQL.
#------------------------------------------------------------


#------------------------------------------------------------
# Table: Circuit
#------------------------------------------------------------

CREATE TABLE Circuit(
        IDCircuit         int (11) Auto_increment  NOT NULL ,
        Description       Varchar (25) ,
        VilleDepart       Varchar (25) ,
        PaysDepart        Varchar (25) ,
        PaysArrivee       Varchar (25) ,
        VilleArrivee      Varchar (25) ,
        DateDepart        Date NOT NULL ,
        NbPlaceDisponible Int ,
        Duree             Int ,
        PrixInscription   Float ,
        PRIMARY KEY (IDCircuit )
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: Etape
#------------------------------------------------------------

CREATE TABLE Etape(
        Ordre     Int NOT NULL ,
        DateEtape Date NOT NULL ,
        Duree     Int ,
        IDCircuit Int NOT NULL ,
        NomLieu   Varchar (25) NOT NULL ,
        Ville     Varchar (25) NOT NULL ,
        Pays      Varchar (25) ,
        PRIMARY KEY (Ordre )
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: LieuVisiter
#------------------------------------------------------------

CREATE TABLE LieuVisiter(
        NomLieu    Varchar (25) NOT NULL ,
        Ville      Varchar (25) NOT NULL ,
        Pays       Varchar (25) NOT NULL ,
        Descriptif Varchar (25) ,
        PRIMARY KEY (NomLieu ,Ville ,Pays )
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: Reservation
#------------------------------------------------------------

CREATE TABLE Reservation(
        IDReservation   int (11) Auto_increment  NOT NULL ,
        DateReservation Date NOT NULL ,
        NombrePlace     Int NOT NULL ,
        IdClient        Int NOT NULL ,
        IDCircuit       Int NOT NULL ,
        PRIMARY KEY (IDReservation )
)ENGINE=InnoDB;


#------------------------------------------------------------
# Table: Client
#------------------------------------------------------------

CREATE TABLE Client(
        IdClient        int (11) Auto_increment  NOT NULL ,
        Nom             Varchar (25) NOT NULL ,
        Prenom          Varchar (25) NOT NULL ,
        DateDeNaissance Date NOT NULL ,
        PRIMARY KEY (IdClient )
)ENGINE=InnoDB;

ALTER TABLE Etape ADD CONSTRAINT FK_Etape_IDCircuit FOREIGN KEY (IDCircuit) REFERENCES Circuit(IDCircuit);
ALTER TABLE Etape ADD CONSTRAINT FK_Etape_NomLieu FOREIGN KEY (NomLieu) REFERENCES LieuVisiter(NomLieu);
ALTER TABLE Etape ADD CONSTRAINT FK_Etape_Ville FOREIGN KEY (Ville) REFERENCES LieuVisiter(Ville);
ALTER TABLE Etape ADD CONSTRAINT FK_Etape_Pays FOREIGN KEY (Pays) REFERENCES LieuVisiter(Pays);
ALTER TABLE Reservation ADD CONSTRAINT FK_Reservation_IdClient FOREIGN KEY (IdClient) REFERENCES Client(IdClient);
ALTER TABLE Reservation ADD CONSTRAINT FK_Reservation_IDCircuit FOREIGN KEY (IDCircuit) REFERENCES Circuit(IDCircuit);