drop table Hotel;
drop table Consommation;
drop table Client;
drop table Sejour;
drop table Propose_consommation;
drop table Consommer_par;

create table Hotel (
	id NUMBER CONSTRAINT pk_hotel PRIMARY KEY,
	nom VARCHAR2(30));

create table Consommation (
	id NUMBER CONSTRAINT pk_consommation PRIMARY KEY,
	designation VARCHAR2(30),
	volume NUMBER(5,2));

create table Client (
	id NUMBER CONSTRAINT pk_client PRIMARY KEY,
	nom VARCHAR2(30),
	prenom VARCHAR2(30),
	numero NUMBER(3),
	rue VARCHAR2(100),
	cp NUMBER(5) CHECK(cp BETWEEN 00001 AND 99999),
	ville VARCHAR2(100));

create table Sejour (
	id NUMBER CONSTRAINT pk_sejour PRIMARY KEY,
	duree NUMBER(2),
	date_debut NUMBER(8));

create table Propose_consommation (
	idHotel NUMBER,
	idConsommation NUMBER,
	tarif NUMBER (3,2),
	CONSTRAINT pk_propose_conso PRIMARY KEY (idHotel,idConsommation));

create table Consommer_par (
	idConsommation NUMBER,
	idSejour NUMBER,
	quantite NUMBER (3,2),
	CONSTRAINT pk_consommer_par PRIMARY KEY (idConsommation,idSejour));


