DROP TABLE hotel;
DROP CONSTRAINT pk_hotel;
CREATE TABLE Hotel(id NUMBER CONSTRAINT pk_hotel PRIMARY KEY,nom VARCHAR2(30));
DROP TABLE consommation;
DROP CONSTRAINT pk_consommation;
CREATE TABLE Consommation(id NUMBER CONSTRAINT pk_consommation PRIMARY KEY,designation VARCHAR2(30),volume VARCHAR2(30));
DROP TABLE propose_consommation;
DROP CONSTRAINT pk_propsoe_conso;
CREATE TABLE Propose_Consommation(id_hotel CONSTRAINT fk1_propose_conso REFERENCES hotel(id),id_conso CONSTRAINT fk2_propose_conso REFERENCES consommation(id),tarif NUMBER(5,2),CONSTRAINT pk_propose_conso PRIMARY KEY (id_hotel,id_conso));
DROP TABLE sejour;
DROP CONSTRAINT pk_sejour;
CREATE TABLE Sejour(id NUMBER CONSTRAINT pk_sejour PRIMARY KEY,id_client CONSTRAINT fk1_sejour REFERENCES client(id),id_hotel CONSTRAINT fk2_sejour REFERENCES hotel(id));
DROP TABLE consomme_par;
DROP CONSTRAINT pk_consomme_par;
CREATE TABLE Consomme_Par(id_conso CONSTRAINT fk1_consomme_par REFERENCES consommation(id),id_sejour CONSTRAINT fk2_consomme_par REFERENCES sejour(id),CONSTRAINT pk_consomme_par PRIMARY KEY (id_conso,id_sejour),quantite NUMBER(3,2));
DROP TABLE client;
DROP CONSTRAINT pk_client;
CREATE TABLE Client(id NUMBER CONSTRAINT pk_client PRIMARY KEY,nom VARCHAR2(30),prenom VARCHAR2(30),adresse1 VARCHAR2(30),adresse2 VARCHAR2(30),code_postal NUMBER(5,0),ville VARCHAR2(30));
DROP TABLE effectue_sejour;
DROP CONSTRAINT pk_effectue_sejour;
CREATE TABLE Effectue_Sejour(id_sejour CONSTRAINT fk1_effectue_sejour REFERENCES sejour(id),id_client CONSTRAINT fk2_effectue_sejour REFERENCES client(id),CONSTRAINT pk_effectue_sejour PRIMARY KEY,duree NUMBER(2,0),date_debut DATE);