DROP TABLE IF EXISTS LESTABLES ;
CREATE TABLE LESTABLES
(
	NUMTABLE numeric(2) NOT NULL primary key,
	NOMTABLE VARCHAR(15) NOT NULL ,
	NBPLACE	 numeric(2)

) TYPE = InnoDB;

DROP TABLE  IF EXISTS  SERVEUR;
CREATE TABLE SERVEUR
(
	NUMSERVEUR 	numeric NOT NULL PRIMARY KEY,
	NOMSERVEUR	CHAR(20) NOT NULL,
	RUESERVEUR	CHAR(20) NOT NULL,
	CPSERVEUR	NUMERIC(5) NOT NULL,
	VILLESERVEUR	CHAR(20) NOT NULL,
	DATENSERVEUR	DATE NOT NULL
) TYPE = InnoDB;

DROP TABLE  IF EXISTS  CONSOMMATION ;
CREATE TABLE CONSOMMATION
(
	NUMCONS		numeric(3) NOT NULL PRIMARY KEY,
	LIBCONS		CHAR(15) NOT NULL,
	PRIXCONS	DECIMAL(6,2) NOT NULL
) TYPE = InnoDB;

DROP TABLE  IF EXISTS FACTURE ;
CREATE TABLE FACTURE
(
	NUMFACTURE	NUMERIC NOT NULL,
	NUMTABLE 	NUMERIC(2),
	NUMSERVEUR	NUMERIC,
	DATEFACTURE	DATE NOT NULL,
PRIMARY KEY (NUMFACTURE),
INDEX(NUMTABLE),INDEX(NUMSERVEUR),
FOREIGN KEY (NUMTABLE) REFERENCES LESTABLES (NUMTABLE),
FOREIGN KEY (NUMSERVEUR) REFERENCES SERVEUR (NUMSERVEUR) 
) TYPE = InnoDB;

DROP TABLE IF EXISTS  COMPREND;
CREATE TABLE COMPREND
(
	NUMFACTURE	NUMERIC NOT NULL,
	NUMCONS		NUMERIC(3) NOT NULL,
	QTE		NUMERIC(2),
PRIMARY KEY (NUMFACTURE,NUMCONS),
INDEX(NUMFACTURE),INDEX(NUMCONS),
FOREIGN KEY (NUMFACTURE) REFERENCES FACTURE (NUMFACTURE) ,
FOREIGN KEY (NUMCONS) REFERENCES CONSOMMATION (NUMCONS) 
) TYPE = InnoDB;

