DROP TABLE Sejour;
DROP TABLE Client;
DROP TABLE Activite;
DROP TABLE Station;
 
CREATE TABLE Station (nomStation VARCHAR2 (30),
                      capacite   NUMBER (10) NOT NULL,
                      lieu       VARCHAR2(30) NOT NULL,
                      region     VARCHAR2 (30),
                      tarif      NUMBER (10,2) DEFAULT 0,
                      CONSTRAINT cle_station PRIMARY KEY (nomStation),
                      CONSTRAINT cle_lieu_region UNIQUE (lieu, region),
                      CONSTRAINT nom_region
                         CHECK (region IN ('Ocean Indien',
                                        'Antilles', 'Europe',
                                        'Ameriques', 'Extreme Orient'))
                    );
 
CREATE TABLE Activite (nomStation   VARCHAR2 (30),
                       libelle      VARCHAR2(30),
                       prix         NUMBER (10,2) DEFAULT 0,
                       PRIMARY KEY (nomStation, libelle),
                       FOREIGN KEY (nomStation) REFERENCES Station
                         ON DELETE CASCADE
                       );

CREATE TABLE Client (id NUMBER (10),
                      nom VARCHAR2(30) NOT NULL,
                      prenom VARCHAR2 (30),
                       ville VARCHAR2 (30) NOT NULL,
                       region VARCHAR2(30),
                        solde NUMBER (10,2) DEFAULT 0 NOT NULL,
                         PRIMARY KEY (id)
                   );
 
CREATE TABLE Sejour  (idClient NUMBER (10),
                       station VARCHAR2 (30),
                       debut NUMBER (10),
                       nbPlaces  NUMBER (4) NOT NULL,
                        PRIMARY KEY (idClient, station, debut),
                        FOREIGN KEY (idClient) REFERENCES Client,
                        FOREIGN KEY (station) REFERENCES Station
                             ON DELETE CASCADE);
