/*==============================================================*/
/* Nom de SGBD :  MySQL 5.0                                     */
/* Date de créatiON :  2012-07-06 09:21:50                      */
/*==============================================================*/


DROP TABLE IF EXISTS BackEnd; 

DROP TABLE IF EXISTS DataType;

DROP TABLE IF EXISTS Indicators; 

DROP TABLE IF EXISTS IndicatorFamily; 

DROP TABLE IF EXISTS IndicatorParameter; 

DROP TABLE IF EXISTS IndicatorParamValueNum; 

DROP TABLE IF EXISTS IndicatorParamValuePJ; 

DROP TABLE IF EXISTS IndicatorParamValueText;

DROP TABLE IF EXISTS Product; 

DROP TABLE IF EXISTS RefIndicatorProduct;

DROP TABLE IF EXISTS Source;

/*==============================================================*/
/* Table : BackEnd                                              */
/*==============================================================*/
CREATE TABLE BackEnd
(
   idIndicator          INT NOT NULL,
   idSource             INT NOT NULL,
   PRIMARY KEY (idIndicator, idSource)
);

/*==============================================================*/
/* Table : DataType                                             */
/*==============================================================*/
CREATE TABLE DataType 
(
   idData               INT NOT NULL AUTO_INCREMENT,
   idIndicator          INT NOT NULL,
   labelData            VARCHAR(256),
   formatData           VARCHAR(256),
   descData             VARCHAR(256),
   formulData           VARCHAR(256),
   PRIMARY KEY (idData)
);

/*==============================================================*/
/* Table : Indicators                                            */
/*==============================================================*/
CREATE TABLE Indicators
(
   idIndicator          INT NOT NULL AUTO_INCREMENT,
   idFamily             INT NOT NULL,
   nameIndicator        VARCHAR(256),
   descIndicator        VARCHAR(256),
   PRIMARY KEY (idIndicator)
);

/*==============================================================*/
/* Table : IndicatorFamily                                      */
/*==============================================================*/
CREATE TABLE IndicatorFamily
(
   idFamily             INT NOT NULL AUTO_INCREMENT,
   nameFamily           VARCHAR(256),
   refFamily            VARCHAR(5),
   PRIMARY KEY (idFamily)
);

/*==============================================================*/
/* Table : IndicatorParameter                                   */
/*==============================================================*/
create table IndicatorParameter
(
   idParam              int not null AUTO_INCREMENT,
   nameParam            VARCHAR(256),
   primary KEY (idParam)
);

/*==============================================================*/
/* Table : IndicatorParamValueNum                               */
/*==============================================================*/
CREATE TABLE IndicatorParamValueNum
(
   idParam              INT NOT NULL,
   idIndicator          INT NOT NULL,
   PARAMVALUENUM        DOUBLE,
   PRIMARY KEY (idParam, idIndicator)
);

/*==============================================================*/
/* Table : IndicatorParamValuePJ                                */
/*==============================================================*/
CREATE TABLE IndicatorParamValuePJ
(
   idParam              INT NOT NULL,
   idIndicator          INT NOT NULL,
   paramValuePJ         VARCHAR(256),
   PRIMARY KEY (idParam, idIndicator)
);

/*==============================================================*/
/* Table : IndicatorParamValueText                              */
/*==============================================================*/
CREATE TABLE IndicatorParamValueText
(
   idParam              INT NOT NULL,
   idIndicator          INT NOT NULL,
   paramvalueText       VARCHAR(256),
   PRIMARY KEY (idParam, idIndicator)
);

/*==============================================================*/
/* Table : Product                                              */
/*==============================================================*/
CREATE TABLE Product
(
   idProduct            INT NOT NULL AUTO_INCREMENT,
   nameProduct          VARCHAR(256),
   descProduct          VARCHAR(256),
   PRIMARY KEY (IDProduct)
);

/*==============================================================*/
/* Table : RefIndicatorProduct                                  */
/*==============================================================*/
CREATE TABLE RefIndicatorProduct
(
   IDProduct            INT NOT NULL,
   idIndicator          INT NOT NULL,
   PRIMARY KEY (IDProduct, idIndicator)
);

/*==============================================================*/
/* Table : Source                                               */
/*==============================================================*/
CREATE TABLE Source
(
   idSource             INT NOT NULL AUTO_INCREMENT,
   niveauSource         INT,
   nameSource           VARCHAR(256),
   instanceSource       VARCHAR(256),
   PRIMARY KEY (idSource)
);

ALTER TABLE BackEnd ADD CONSTRAINT FK_BackEnd FOREIGN KEY (idIndicator)
      REFERENCES Indicators (idIndicator) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE BackEnd ADD CONSTRAINT FK_BackEnd2 FOREIGN KEY (idSource)
      REFERENCES Source (idSource) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER table DataType ADD CONSTRAINT FK_RELATION_2 FOREIGN KEY (idIndicator)
      REFERENCES Indicators (idIndicator) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER table Indicators ADD CONSTRAINT FK_RELATION_1 FOREIGN KEY (idFamily)
      REFERENCES IndicatorFamily (idFamily) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER table IndicatorParamValueNum ADD CONSTRAINT FK_IndicatorParamValueNum FOREIGN KEY (idParam)
      REFERENCES IndicatorParameter (idParam) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER table IndicatorParamValueNum ADD CONSTRAINT FK_IndicatorParamValueNum2 FOREIGN KEY (idIndicator)
      REFERENCES Indicators (idIndicator) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER table IndicatorParamValuePJ ADD CONSTRAINT FK_IndicatorParamValuePJ FOREIGN KEY (idParam)
      REFERENCES IndicatorParameter (idParam) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER table IndicatorParamValuePJ ADD CONSTRAINT FK_IndicatorParamValuePJ2 FOREIGN KEY (idIndicator)
      REFERENCES Indicators (idIndicator) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER table IndicatorParamValueText ADD CONSTRAINT FK_IndicatorParamValueText FOREIGN KEY (idParam)
      REFERENCES IndicatorParameter (idParam) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER table IndicatorParamValueText ADD CONSTRAINT FK_IndicatorParamValueText2 FOREIGN KEY (idIndicator)
      REFERENCES Indicators (idIndicator) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER table RefIndicatorProduct ADD CONSTRAINT FK_RefIndicatorProduct FOREIGN KEY (IDProduct)
      REFERENCES Product (IDProduct) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER table RefIndicatorProduct ADD CONSTRAINT FK_RefIndicatorProduct2 FOREIGN KEY (idIndicator)
      REFERENCES Indicators (idIndicator) ON DELETE CASCADE ON UPDATE CASCADE;