/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     21/03/2006 18:11:39                          */
/*==============================================================*/


drop table if exists ABESOINDETYPECONTRAT;

drop table if exists ACOMMEOPTION;

drop table if exists ASSUREUR;

drop table if exists CLASSETARIFICATION;

drop table if exists CLIENT;

drop table if exists CONTRATASSURANCE;

drop table if exists FACTURE;

drop table if exists INDISPONIBILITE;

drop table if exists LOCATION;

drop table if exists MODELE;

drop table if exists MODELOCATION;

drop table if exists OPTIONMODELE;

drop table if exists RESERVATION;

drop table if exists TYPECONTRATASSURANCE;

drop table if exists VEHICULE;

/*==============================================================*/
/* Table: ABESOINDETYPECONTRAT                                  */
/*==============================================================*/
create table ABESOINDETYPECONTRAT
(
   NUMEROCLASSE         int not null,
   NUMEROTYPECONTRAT    int not null,
   primary key (NUMEROCLASSE, NUMEROTYPECONTRAT)
)
type = InnoDB;

/*==============================================================*/
/* Table: ACOMMEOPTION                                          */
/*==============================================================*/
create table ACOMMEOPTION
(
   CODEOPTION           int not null,
   MARQUE               varchar(255) not null,
   TYPEMODELE           varchar(255) not null,
   PUISSANCE            int not null,
   primary key (CODEOPTION, MARQUE, TYPEMODELE, PUISSANCE)
)
type = InnoDB;

/*==============================================================*/
/* Table: ASSUREUR                                              */
/*==============================================================*/
create table ASSUREUR
(
   NOMASSUREUR          varchar(255) not null,
   ADRESSEASSUREUR      varchar(255) not null,
   primary key (NOMASSUREUR, ADRESSEASSUREUR)
)
type = InnoDB;

/*==============================================================*/
/* Table: CLASSETARIFICATION                                    */
/*==============================================================*/
create table CLASSETARIFICATION
(
   PRIXKM               int,
   NUMEROCLASSE         int not null auto_increment,
   AMENDEJOURNALIERE    float(8,2),
   primary key (NUMEROCLASSE)
)
type = InnoDB;

/*==============================================================*/
/* Table: CLIENT                                                */
/*==============================================================*/
create table CLIENT
(
   NUMEROCLIENT         int not null auto_increment,
   NOMCLIENT            varchar(255),
   PRENOMCLIENT         varchar(255),
   ADRESSECLIENT        varchar(255),
   primary key (NUMEROCLIENT)
)
type = InnoDB;

/*==============================================================*/
/* Table: CONTRATASSURANCE                                      */
/*==============================================================*/
create table CONTRATASSURANCE
(
   NUMEROCONTRATASSURANCE int not null auto_increment,
   NUMEROTYPECONTRAT    int not null,
   NUMEROVEHICULE       int not null,
   primary key (NUMEROCONTRATASSURANCE)
)
type = InnoDB;

/*==============================================================*/
/* Table: FACTURE                                               */
/*==============================================================*/
create table FACTURE
(
   PAYEE                bool,
   NUMEROFACTURE        int not null auto_increment,
   NUMEROLOCATION       int not null,
   primary key (NUMEROFACTURE)
)
type = InnoDB;

/*==============================================================*/
/* Table: INDISPONIBILITE                                       */
/*==============================================================*/
create table INDISPONIBILITE
(
   NUMEROINDISPONOBILITE int not null auto_increment,
   DATEINDISPONIBILITE  date,
   DATERETOURINDISPONIBILITE date,
   TYPEINDISPONIBILITE  int,
   primary key (NUMEROINDISPONOBILITE)
)
type = InnoDB;

/*==============================================================*/
/* Table: LOCATION                                              */
/*==============================================================*/
create table LOCATION
(
   CAUTION              float(8,2),
   NUMEROPERMIS         varchar(255),
   KMFINAL              int,
   KMINITIAL            int,
   DATERETOUR           date,
   NUMEROLOCATION       int not null auto_increment,
   NUMERORESERVATION    int not null,
   primary key (NUMEROLOCATION)
)
type = InnoDB;

/*==============================================================*/
/* Table: MODELE                                                */
/*==============================================================*/
create table MODELE
(
   MARQUE               varchar(255) not null,
   TYPEMODELE           varchar(255) not null,
   PUISSANCE            int not null,
   NUMEROCLASSE         int not null,
   primary key (MARQUE, TYPEMODELE, PUISSANCE)
)
type = InnoDB;

/*==============================================================*/
/* Table: MODELOCATION                                          */
/*==============================================================*/
create table MODELOCATION
(
   KMFORFAITAIRE        int not null,
   DESCRIPTIONMODELOCATION varchar(255),
   DUREELOCATION        int not null,
   primary key (KMFORFAITAIRE, DUREELOCATION)
)
type = InnoDB;

/*==============================================================*/
/* Table: OPTIONMODELE                                          */
/*==============================================================*/
create table OPTIONMODELE
(
   CODEOPTION           int not null auto_increment,
   LIBELLE              varchar(255),
   primary key (CODEOPTION)
)
type = InnoDB;

/*==============================================================*/
/* Table: RESERVATION                                           */
/*==============================================================*/
create table RESERVATION
(
   DATERETRAIT          date,
   ETATRESERVATION      varchar(255),
   NUMERORESERVATION    int not null auto_increment,
   KMFORFAITAIRE        int not null,
   DUREELOCATION        int not null,
   NUMEROCLIENT         int not null,
   RES_NUMERORESERVATION int,
   NUMEROVEHICULE       int not null,
   DATERETOURPREVUE     date,
   primary key (NUMERORESERVATION)
)
type = InnoDB;

/*==============================================================*/
/* Table: TYPECONTRATASSURANCE                                  */
/*==============================================================*/
create table TYPECONTRATASSURANCE
(
   NUMEROTYPECONTRAT    int not null auto_increment,
   NOMASSUREUR          varchar(255) not null,
   ADRESSEASSUREUR      varchar(255) not null,
   DESCRIPTIONCONTRAT   varchar(255),
   primary key (NUMEROTYPECONTRAT)
)
type = InnoDB;

/*==============================================================*/
/* Table: VEHICULE                                              */
/*==============================================================*/
create table VEHICULE
(
   NUMEROVEHICULE       int not null auto_increment,
   MARQUE               varchar(255) not null,
   TYPEMODELE           varchar(255) not null,
   PUISSANCE            int not null,
   NUMEROINDISPONOBILITE int,
   DATEACHAT            date not null,
   PRIXACHAT            float(8,2) not null,
   primary key (NUMEROVEHICULE)
)
type = InnoDB;

alter table ABESOINDETYPECONTRAT add constraint FK_ABESOINDETYPECONTRAT foreign key (NUMEROCLASSE)
      references CLASSETARIFICATION (NUMEROCLASSE) on delete restrict on update restrict;

alter table ABESOINDETYPECONTRAT add constraint FK_ABESOINDETYPECONTRAT2 foreign key (NUMEROTYPECONTRAT)
      references TYPECONTRATASSURANCE (NUMEROTYPECONTRAT) on delete restrict on update restrict;

alter table ACOMMEOPTION add constraint FK_ACOMMEOPTION foreign key (CODEOPTION)
      references OPTIONMODELE (CODEOPTION) on delete restrict on update restrict;

alter table ACOMMEOPTION add constraint FK_ACOMMEOPTION2 foreign key (MARQUE, TYPEMODELE, PUISSANCE)
      references MODELE (MARQUE, TYPEMODELE, PUISSANCE) on delete restrict on update restrict;

alter table CONTRATASSURANCE add constraint FK_ASSUREVEHICULE foreign key (NUMEROVEHICULE)
      references VEHICULE (NUMEROVEHICULE) on delete restrict on update restrict;

alter table CONTRATASSURANCE add constraint FK_ESTTYPECONTRAT foreign key (NUMEROTYPECONTRAT)
      references TYPECONTRATASSURANCE (NUMEROTYPECONTRAT) on delete restrict on update restrict;

alter table FACTURE add constraint FK_FACTURELOCATION foreign key (NUMEROLOCATION)
      references LOCATION (NUMEROLOCATION) on delete restrict on update restrict;

alter table LOCATION add constraint FK_RESERVELOCATION foreign key (NUMERORESERVATION)
      references RESERVATION (NUMERORESERVATION) on delete restrict on update restrict;

alter table MODELE add constraint FK_GROUPEDANS foreign key (NUMEROCLASSE)
      references CLASSETARIFICATION (NUMEROCLASSE) on delete restrict on update restrict;

alter table RESERVATION add constraint FK_ANCIENNERESERVATION foreign key (RES_NUMERORESERVATION)
      references RESERVATION (NUMERORESERVATION) on delete restrict on update restrict;

alter table RESERVATION add constraint FK_MODELOCATIONRESERVATION foreign key (KMFORFAITAIRE, DUREELOCATION)
      references MODELOCATION (KMFORFAITAIRE, DUREELOCATION) on delete restrict on update restrict;

alter table RESERVATION add constraint FK_RESERVATIONCLIENT foreign key (NUMEROCLIENT)
      references CLIENT (NUMEROCLIENT) on delete restrict on update restrict;

alter table RESERVATION add constraint FK_RESERVEVEHICULE foreign key (NUMEROVEHICULE)
      references VEHICULE (NUMEROVEHICULE) on delete restrict on update restrict;

alter table TYPECONTRATASSURANCE add constraint FK_ASSUREPAR foreign key (NOMASSUREUR, ADRESSEASSUREUR)
      references ASSUREUR (NOMASSUREUR, ADRESSEASSUREUR) on delete restrict on update restrict;

alter table VEHICULE add constraint FK_ESTMODELE foreign key (MARQUE, TYPEMODELE, PUISSANCE)
      references MODELE (MARQUE, TYPEMODELE, PUISSANCE) on delete restrict on update restrict;

alter table VEHICULE add constraint FK_INDISPONIBLE foreign key (NUMEROINDISPONOBILITE)
      references INDISPONIBILITE (NUMEROINDISPONOBILITE) on delete restrict on update restrict;