/*==============================================================*/
/* Nom de SGBD :  Microsoft SQL Server 2000                     */
/* Date de création :  23/04/2015 12:15:52                      */
/*==============================================================*/


if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('AVOIR') and o.name = 'FK_AVOIR_AVOIR_DESTINAT')
alter table AVOIR
   drop constraint FK_AVOIR_AVOIR_DESTINAT
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('AVOIR') and o.name = 'FK_AVOIR_AVOIR2_COURRIER')
alter table AVOIR
   drop constraint FK_AVOIR_AVOIR2_COURRIER
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CLASSEUR') and o.name = 'FK_CLASSEUR_CLASSER_COURRIER')
alter table CLASSEUR
   drop constraint FK_CLASSEUR_CLASSER_COURRIER
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('COURRIER') and o.name = 'FK_COURRIER_APPARTIEN_TYPECOUR')
alter table COURRIER
   drop constraint FK_COURRIER_APPARTIEN_TYPECOUR
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('COURRIER') and o.name = 'FK_COURRIER_CONCERNE_EXERCICE')
alter table COURRIER
   drop constraint FK_COURRIER_CONCERNE_EXERCICE
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('DISTRIBUER') and o.name = 'FK_DISTRIBU_DISTRIBUE_COURRIER')
alter table DISTRIBUER
   drop constraint FK_DISTRIBU_DISTRIBUE_COURRIER
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('DISTRIBUER') and o.name = 'FK_DISTRIBU_DISTRIBUE_SERVICE')
alter table DISTRIBUER
   drop constraint FK_DISTRIBU_DISTRIBUE_SERVICE
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('SERVICE') and o.name = 'FK_SERVICE_EXISTE_DIRECTIO')
alter table SERVICE
   drop constraint FK_SERVICE_EXISTE_DIRECTIO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('AVOIR')
            and   type = 'U')
   drop table AVOIR
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CLASSEUR')
            and   type = 'U')
   drop table CLASSEUR
go

if exists (select 1
            from  sysobjects
           where  id = object_id('COURRIER')
            and   type = 'U')
   drop table COURRIER
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DESTINATAIRE')
            and   type = 'U')
   drop table DESTINATAIRE
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DIRECTION')
            and   type = 'U')
   drop table DIRECTION
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DISTRIBUER')
            and   type = 'U')
   drop table DISTRIBUER
go

if exists (select 1
            from  sysobjects
           where  id = object_id('EXERCICE')
            and   type = 'U')
   drop table EXERCICE
go

if exists (select 1
            from  sysobjects
           where  id = object_id('SERVICE')
            and   type = 'U')
   drop table SERVICE
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TYPECOURRIER')
            and   type = 'U')
   drop table TYPECOURRIER
go

/*==============================================================*/
/* Table : AVOIR                                                */
/*==============================================================*/
create table AVOIR (
   NUMCOURRIER          numeric(999)         not null,
   NUMDESTINATAIRE      int                  not null,
   constraint PK_AVOIR primary key nonclustered (NUMCOURRIER, NUMDESTINATAIRE)
)
go

/*==============================================================*/
/* Table : CLASSEUR                                             */
/*==============================================================*/
create table CLASSEUR (
   CODECLASSEUR         int                  not null,
   NUMCOURRIER          numeric(999)         not null,
   NOMCLASSEUR          char(50)             null,
   DATE_CLASSEMNT       datetime             null,
   COMMENTAIRE          char(100)            null,
   constraint PK_CLASSEUR primary key nonclustered (CODECLASSEUR)
)
go

/*==============================================================*/
/* Table : COURRIER                                             */
/*==============================================================*/
create table COURRIER (
   NUMCOURRIER          numeric(999)         not null,
   CODEANNEE            int                  not null,
   NUMTYPE              int                  not null,
   DATERECEPTION        datetime             null,
   REF_COURRIER         char(20)             null,
   TITRE_COURRIER       char(100)            null,
   DATE_ENREGISTRE      datetime             null,
   DATE_EMISSION        datetime             null,
   DATE_ECHEANCE        datetime             null,
   DATE_TRAITEMENT      datetime             null,
   EXPEDITEUR           char(50)             null,
   CONTACTEXPEDITEUR    char(50)             null,
   constraint PK_COURRIER primary key nonclustered (NUMCOURRIER)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   'TABLE DES COURRIER ',
   'user', @CurrentUser, 'table', 'COURRIER'
go

/*==============================================================*/
/* Table : DESTINATAIRE                                         */
/*==============================================================*/
create table DESTINATAIRE (
   NUMDESTINATAIRE      int                  not null,
   ADRESSEDEST          char(100)            null,
   NOMDESTINATAIRE      char(100)            null,
   constraint PK_DESTINATAIRE primary key nonclustered (NUMDESTINATAIRE)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   'TABLE DESTINATAIRES (INTERNE/EXTERNE)',
   'user', @CurrentUser, 'table', 'DESTINATAIRE'
go

/*==============================================================*/
/* Table : DIRECTION                                            */
/*==============================================================*/
create table DIRECTION (
   CODEDIRECTION        int                  not null,
   NOMDIRECTION         char(100)            null,
   constraint PK_DIRECTION primary key nonclustered (CODEDIRECTION)
)
go

/*==============================================================*/
/* Table : DISTRIBUER                                           */
/*==============================================================*/
create table DISTRIBUER (
   CODESERVICE          int                  not null,
   NUMCOURRIER          numeric(999)         not null,
   constraint PK_DISTRIBUER primary key nonclustered (CODESERVICE, NUMCOURRIER)
)
go

/*==============================================================*/
/* Table : EXERCICE                                             */
/*==============================================================*/
create table EXERCICE (
   CODEANNEE            int                  not null,
   DATE_DEBUT           datetime             null,
   DATE_FIN             datetime             null,
   MOIS_EN_COURS        datetime             null,
   CLOTURE              char(1)              null,
   constraint PK_EXERCICE primary key nonclustered (CODEANNEE)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   'TABLE DES ANNEES ',
   'user', @CurrentUser, 'table', 'EXERCICE'
go

/*==============================================================*/
/* Table : SERVICE                                              */
/*==============================================================*/
create table SERVICE (
   CODESERVICE          int                  not null,
   CODEDIRECTION        int                  not null,
   NOMSERVICE           char(100)            null,
   constraint PK_SERVICE primary key nonclustered (CODESERVICE)
)
go

/*==============================================================*/
/* Table : TYPECOURRIER                                         */
/*==============================================================*/
create table TYPECOURRIER (
   NUMTYPE              int                  not null,
   DESIGNATIONTYPE      char(100)            null,
   constraint PK_TYPECOURRIER primary key nonclustered (NUMTYPE)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   'TABLE DES TYPES DE COURRIER',
   'user', @CurrentUser, 'table', 'TYPECOURRIER'
go

alter table AVOIR
   add constraint FK_AVOIR_AVOIR_DESTINAT foreign key (NUMDESTINATAIRE)
      references DESTINATAIRE (NUMDESTINATAIRE)
go

alter table AVOIR
   add constraint FK_AVOIR_AVOIR2_COURRIER foreign key (NUMCOURRIER)
      references COURRIER (NUMCOURRIER)
go

alter table CLASSEUR
   add constraint FK_CLASSEUR_CLASSER_COURRIER foreign key (NUMCOURRIER)
      references COURRIER (NUMCOURRIER)
go

alter table COURRIER
   add constraint FK_COURRIER_APPARTIEN_TYPECOUR foreign key (NUMTYPE)
      references TYPECOURRIER (NUMTYPE)
go

alter table COURRIER
   add constraint FK_COURRIER_CONCERNE_EXERCICE foreign key (CODEANNEE)
      references EXERCICE (CODEANNEE)
go

alter table DISTRIBUER
   add constraint FK_DISTRIBU_DISTRIBUE_COURRIER foreign key (NUMCOURRIER)
      references COURRIER (NUMCOURRIER)
go

alter table DISTRIBUER
   add constraint FK_DISTRIBU_DISTRIBUE_SERVICE foreign key (CODESERVICE)
      references SERVICE (CODESERVICE)
go

alter table SERVICE
   add constraint FK_SERVICE_EXISTE_DIRECTIO foreign key (CODEDIRECTION)
      references DIRECTION (CODEDIRECTION)
go