Mise en place d'un système automatique de gestion des flux de trésorerie

Chapitre 6 : rEalisation

6-1-script de la base

- -----------------------------------------------------------------------------

-- TABLE : ECHEANCE

-- -----------------------------------------------------------------------------

create table ECHEANCE

(

NUM_ECHEANCE char (32) not null,

MONTANT_ECHEANCE char (32) ,

DATE_ECHEANCE char (32)

);

-- -----------------------------------------------------------------------------

-- TABLE : POSTE_BUDGETAIRE

-- -----------------------------------------------------------------------------

create table POSTE_BUDGETAIRE

(

NUM_POSTE char (32) not null,

CATEGORIE_POSTE char (32) ,

LIBEL_POSTE char (32) ,

BUDGET_POSTE char (32)

);

-- -----------------------------------------------------------------------------

-- TABLE : EMPLOYE

-- -----------------------------------------------------------------------------

create table EMPLOYE

(

MATRICULE char (32) not null,

NUM_CATEGORIE char (32) not null,

NUM_SITMAT char (32) not null,

NOM char (32) ,

PRENOM char (32) ,

CIVILITE char (32) ,

TEL_EMP char (32) ,

ADRESSE_EMP char (32) ,

EMAIL_EMPL char (32) ,

FONCTION char (32) ,

NBRE_ENF char (32)

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE EMPLOYE

-- -----------------------------------------------------------------------------

create index I_FK_CATEGORIE_FAIRE_PARTIR

on EMPLOYE (NUM_CATEGORIE);

create index I_FK_SITMATRIMONIALE_DISPOSER

on EMPLOYE (NUM_SITMAT);

-- -----------------------------------------------------------------------------

-- TABLE : DMANDE

-- -----------------------------------------------------------------------------

create table DMANDE

(

NUM_DEM char (32) not null,

TYPE_DEM char (32) ,

JUSTIFICATION_DEM char (32) ,

DEM_THEOMOY char (32) ,

DATE_DEM char (32)

);

-- -----------------------------------------------------------------------------

-- TABLE : REGLEMENT

-- -----------------------------------------------------------------------------

create table REGLEMENT

(

NUM_REGLEMENT char (32) not null,

MONTANT_REGLE char (32)

);

-- -----------------------------------------------------------------------------

-- TABLE : BON_LIVRAISON

-- -----------------------------------------------------------------------------

create table BON_LIVRAISON

(

NUM_BONLIVRE char (32) not null,

NUM_FACT char (32) not null,

AVIS char (32)

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE BON_LIVRAISON

-- -----------------------------------------------------------------------------

create index I_FK_FACTURE_SE_RAPPORTER

on BON_LIVRAISON (NUM_FACT);

-- -----------------------------------------------------------------------------

-- TABLE : EMPRUNT

-- -----------------------------------------------------------------------------

create table EMPRUNT

(

NUM_EMPRUNT char (32) not null,

TYPE_EMPRUNT char (32) ,

DONNEUR char (32) ,

JUSTIFICATION char (32) ,

MONTANT_EMPRUNT char (32)

);

-- -----------------------------------------------------------------------------

-- TABLE : FOURNISSEUR

-- -----------------------------------------------------------------------------

create table FOURNISSEUR

(

NUM_FRS char (32) not null,

NUM_CMPTE char (32) not null,

CATEGORIE_FRS char (32) ,

CONTACT_FRS char (32) ,

ADRESSE_FRS char (32) ,

EMAIL_FRS char (32)

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE FOURNISSEUR

-- -----------------------------------------------------------------------------

create index I_FK_COMPTE_DISPOSER_DE

on FOURNISSEUR (NUM_CMPTE);

-- -----------------------------------------------------------------------------

-- TABLE : FACTURE

-- -----------------------------------------------------------------------------

create table FACTURE

(

NUM_FACT char (32) not null,

NUM_FRS char (32) not null,

NUM_SERIE char (32) ,

TYPE_FACT char (32) ,

TVA char (32) ,

CATEGORIE char (32) ,

DATE_FACT char (32)

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE FACTURE

-- -----------------------------------------------------------------------------

create index I_FK_FOURNISSEUR_ETABLIR

on FACTURE (NUM_FRS);

-- -----------------------------------------------------------------------------

-- TABLE : OPERATION

-- -----------------------------------------------------------------------------

create table OPERATION

(

NUM_OPERATION char (32) not null,

TYPE_OPERATION char (32) ,

LIBEL_OPERATION char (32) ,

BUDGET char (32)

);

-- -----------------------------------------------------------------------------

-- TABLE : PERSONNE_PHYSIQUE

-- -----------------------------------------------------------------------------

create table PERSONNE_PHYSIQUE

(

NUM_FRS char (32) not null,

NOM_FRS char (32) ,

PRENOM_FRS char (32) ,

CATEGORIE_FRS char (32) ,

CONTACT_FRS char (32) ,

ADRESSE_FRS char (32) ,

EMAIL_FRS char (32)

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE PERSONNE_PHYSIQUE

-- -----------------------------------------------------------------------------

create index I_FK_FOURNISSEUR_X

on PERSONNE_PHYSIQUE (NUM_FRS);

-- -----------------------------------------------------------------------------

-- TABLE : CLIENT

-- -----------------------------------------------------------------------------

create table CLIENT

(

COD_CLT char (32) not null,

NUM_CMPTE char (32) not null,

CONTACT_CLT char (32) ,

ADRESSE_CLT char (32) ,

EMAIL_CLT char (32)

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE CLIENT

-- -----------------------------------------------------------------------------

create index I_FK_COMPTE_DÉTENIR

on CLIENT (NUM_CMPTE);

-- -----------------------------------------------------------------------------

-- TABLE : PERSONNE_PHYSIQUE2

-- -----------------------------------------------------------------------------

create table PERSONNE_PHYSIQUE2

(

COD_CLT char (32) not null,

NOM_CLT char (32) ,

PRENOM_CLT char (32) ,

CONTACT_CLT char (32) ,

ADRESSE_CLT char (32) ,

EMAIL_CLT char (32)

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE PERSONNE_PHYSIQUE2

-- -----------------------------------------------------------------------------

create index I_FK_CLIENT_HER_2

on PERSONNE_PHYSIQUE2 (COD_CLT);

-- -----------------------------------------------------------------------------

-- TABLE : SERVICE

-- -----------------------------------------------------------------------------

create table SERVICE

(

NUM_SVCE char (32) not null,

LIBELLE_SVCE char (32) ,

TEL_SVCE char (32) ,

EMAIL_SVCE char (32) ,

NUM_BUREAU char (32)

);

-- -----------------------------------------------------------------------------

-- TABLE : DECAISSEMENT

-- -----------------------------------------------------------------------------

create table DECAISSEMENT

(

NUM_DECAIS char (32) not null,

NUM_CMPTE char (32) not null,

OBJET_DECAIS char (32) ,

TYPE_DÉCAIS char (32) ,

MONTANT_DECAIS char (32) ,

RYTHMMOY_DECAIS char (32) ,

TAUXMOY_DACAISCUMUL char (32)

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE DECAISSEMENT

-- -----------------------------------------------------------------------------

create index I_FK_COMPTE_S_APPLIQUER

on DECAISSEMENT (NUM_CMPTE);

-- -----------------------------------------------------------------------------

-- TABLE : SOCIETE

-- -----------------------------------------------------------------------------

create table SOCIETE

(

NUM_FRS char (32) not null,

LIBELLE_SCTE char (32) ,

CATEGORIE_FRS char (32) ,

CONTACT_FRS char (32) ,

ADRESSE_FRS char (32) ,

EMAIL_FRS char (32)

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE SOCIETE

-- -----------------------------------------------------------------------------

create index I_FK_FOURNISSEUR_X_2

on SOCIETE (NUM_FRS);

-- -----------------------------------------------------------------------------

-- TABLE : SITMATRIMONIALE

-- -----------------------------------------------------------------------------

create table SITMATRIMONIALE

(

NUM_SITMAT char (32) not null,

ETAT char (32)

);

-- -----------------------------------------------------------------------------

-- TABLE : SCIETE

-- -----------------------------------------------------------------------------

create table SCIETE

(

COD_CLT char (32) not null,

LIBELLE_CLT char (32) ,

CONTACT_CLT char (32) ,

ADRESSE_CLT char (32) ,

EMAIL_CLT char (32)

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE SCIETE

-- -----------------------------------------------------------------------------

create index I_FK_CLIENT_HER_2_2

on SCIETE (COD_CLT);

-- -----------------------------------------------------------------------------

-- TABLE : PAIE

-- -----------------------------------------------------------------------------

create table PAIE

(

NUMERO_PAIE char (32) not null,

TYPE_PAIE char (32) ,

DATE_PAIE char (32)

);

-- -----------------------------------------------------------------------------

-- TABLE : PRODUIT

-- -----------------------------------------------------------------------------

create table PRODUIT

(

REFERENCE_PROD char (32) not null,

TYPE_PRODUIT char (32) ,

PRIX_UNITAIRE char (32) ,

QTÉ char (32)

);

-- -----------------------------------------------------------------------------

-- TABLE : COMPTE

-- -----------------------------------------------------------------------------

create table COMPTE

(

NUM_CMPTE char (32) not null,

LIB_CPT char (32)

);

-- -----------------------------------------------------------------------------

-- TABLE : REMBOURSEMENT

-- -----------------------------------------------------------------------------

create table REMBOURSEMENT

(

NUM_REMBOURSE char (32) not null,

MONTANT_REMBOURSE char (32) ,

DATE_REMBOURSE char (32)

);

-- -----------------------------------------------------------------------------

-- TABLE : CATEGORIE

-- -----------------------------------------------------------------------------

create table CATEGORIE

(

NUM_CATEGORIE char (32) not null,

STATU char (32) ,

SALAIRBASE char (32) ,

REVERSIBILITÉ char (32)

);

-- -----------------------------------------------------------------------------

-- TABLE : COMMANDE

-- -----------------------------------------------------------------------------

create table COMMANDE

(

NUM_CMD char (32) not null,

DATE_CMD char (32)

);

-- -----------------------------------------------------------------------------

-- TABLE : FICHE_PAIE

-- -----------------------------------------------------------------------------

create table FICHE_PAIE

(

NUM_FICHEPAIE char (32) not null,

IMPOT char (32) ,

PRET_SURSALAIRE char (32) ,

AVANCE_SURSALAIRE char (32) ,

SALAIRE_FIX char (32) ,

PERIODE char (32)

);

-- -----------------------------------------------------------------------------

-- TABLE : ETRE_LIER

-- -----------------------------------------------------------------------------

create table ETRE_LIER

(

NUM_DECAIS char (32) not null,

NUM_DEM char (32) not null

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE ETRE_LIER

-- -----------------------------------------------------------------------------

create index I_FK_DECAISSEMENT

on ETRE_LIER (NUM_DECAIS);

create index I_FK_DMANDE

on ETRE_LIER (NUM_DEM);

-- -----------------------------------------------------------------------------

-- TABLE : LIER

-- -----------------------------------------------------------------------------

create table LIER

(

NUM_EMPRUNT char (32) not null,

NUM_ECHEANCE char (32)

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE LIER

-- -----------------------------------------------------------------------------

create index I_FK_EMPRUNT

on LIER (NUM_EMPRUNT);

create index I_FK_ECHEANCE

on LIER (NUM_ECHEANCE);

-- -----------------------------------------------------------------------------

-- TABLE : RECEVOIR

-- -----------------------------------------------------------------------------

create table RECEVOIR

(

COD_CLT char (32) not null,

NUM_FACT char (32)

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE RECEVOIR

-- -----------------------------------------------------------------------------

create index I_FK_CLIENT

on RECEVOIR (COD_CLT);

create index I_FK_FACTURE

on RECEVOIR (NUM_FACT);

-- -----------------------------------------------------------------------------

-- TABLE : APPARTENIR

-- -----------------------------------------------------------------------------

create table APPARTENIR

(

NUM_SVCE char (32) not null,

MATRICULE char (32) not null,

DATE_DEBUT char (32) ,

DATE_FIN char (32)

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE APPARTENIR

-- -----------------------------------------------------------------------------

create index I_FK_SERVICE

on APPARTENIR (NUM_SVCE);

create index I_FK_EMPLOYE

on APPARTENIR (MATRICULE);

-- -----------------------------------------------------------------------------

-- TABLE : RESULTER

-- -----------------------------------------------------------------------------

create table RESULTER

(

NUMERO_PAIE char (32) not null,

NUM_DECAIS char (32) not null,

DATE char (32)

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE RESULTER

-- -----------------------------------------------------------------------------

create index I_FK_PAIE

on RESULTER (NUMERO_PAIE);

create index I_FK_DECAISSEMENT_2

on RESULTER (NUM_DECAIS);

-- -----------------------------------------------------------------------------

-- TABLE : FAIRE

-- -----------------------------------------------------------------------------

create table FAIRE

(

NUM_POSTE char (32) not null,

NUM_DECAIS char (32) not null,

ORDRE char (32) ,

DATE_ORDRE char (32)

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE FAIRE

-- -----------------------------------------------------------------------------

create index I_FK_POSTE_BUDGETAIRE

on FAIRE (NUM_POSTE);

create index I_FK_DECAISSEMENT_3

on FAIRE (NUM_DECAIS);

-- -----------------------------------------------------------------------------

-- TABLE : CONTENIR

-- -----------------------------------------------------------------------------

create table CONTENIR

(

NUM_FACT char (32) not null,

NUM_REGLEMENT char (32) not null

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE CONTENIR

-- -----------------------------------------------------------------------------

create index I_FK_FACTURE_2

on CONTENIR (NUM_FACT);

create index I_FK_REGLEMENT

on CONTENIR (NUM_REGLEMENT);

-- -----------------------------------------------------------------------------

-- TABLE : ÊTRE_RELATIF

-- -----------------------------------------------------------------------------

create table ÊTRE_RELATIF

(

NUM_CMD char (32) not null,

REFERENCE_PROD char (32) not null,

QTE_CMD char (32)

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE ÊTRE_RELATIF

-- -----------------------------------------------------------------------------

create index I_FK_COMMANDE

on ÊTRE_RELATIF (NUM_CMD);

create index I_FK_PRODUIT

on ÊTRE_RELATIF (REFERENCE_PROD);

-- -----------------------------------------------------------------------------

-- TABLE : REALISER

-- -----------------------------------------------------------------------------

create table REALISER

(

COD_CLT char (32) not null,

NUM_CMD char (32)

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE REALISER

-- -----------------------------------------------------------------------------

create index I_FK_CLIENT_2

on REALISER (COD_CLT);

create index I_FK_COMMANDE_2

on REALISER (NUM_CMD);

-- -----------------------------------------------------------------------------

-- TABLE : S_APPUYER

-- -----------------------------------------------------------------------------

create table S_APPUYER

(

NUM_POSTE char (32) not null,

NUM_DEP_1 char (32) not null

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE S_APPUYER

-- -----------------------------------------------------------------------------

create index I_FK_POSTE_BUDGETAIRE_2

on S_APPUYER (NUM_POSTE);

create index I_FK_DEPENSE_2

on S_APPUYER (NUM_DEP_1);

-- -----------------------------------------------------------------------------

-- TABLE : CORRESPONDRE

-- -----------------------------------------------------------------------------

create table CORRESPONDRE

(

MATRICULE char (32) not null,

NUM_FICHEPAIE char (32) not null,

DATE_ETS char (32)

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE CORRESPONDRE

-- -----------------------------------------------------------------------------

create index I_FK_EMPLOYE_2

on CORRESPONDRE (MATRICULE);

create index I_FK_FICHE_PAIE

on CORRESPONDRE (NUM_FICHEPAIE);

-- -----------------------------------------------------------------------------

-- TABLE : CONCERNER

-- -----------------------------------------------------------------------------

create table CONCERNER

(

NUM_FICHEPAIE char (32) not null,

NUMERO_PAIE char (32) not null

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE CONCERNER

-- -----------------------------------------------------------------------------

create index I_FK_FICHE_PAIE_2

on CONCERNER (NUM_FICHEPAIE);

create index I_FK_PAIE_2

on CONCERNER (NUMERO_PAIE);

-- -----------------------------------------------------------------------------

-- TABLE : EFFECTUER

-- -----------------------------------------------------------------------------

create table EFFECTUER

(

NUM_DEM char (32) not null,

MATRICULE char (32)

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE EFFECTUER

-- -----------------------------------------------------------------------------

create index I_FK_DMANDE_2

on EFFECTUER (NUM_DEM);

create index I_FK_EMPLOYE_3

on EFFECTUER (MATRICULE);

-- -----------------------------------------------------------------------------

-- TABLE : ORDONNER

-- -----------------------------------------------------------------------------

create table ORDONNER

(

NUM_DECAIS char (32) not null,

MATRICULE char (32) not null

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE ORDONNER

-- -----------------------------------------------------------------------------

create index I_FK_DECAISSEMENT_4

on ORDONNER (NUM_DECAIS);

create index I_FK_EMPLOYE_4

on ORDONNER (MATRICULE);

-- -----------------------------------------------------------------------------

-- TABLE : EFFECTUER_EN_RAPPORT

-- -----------------------------------------------------------------------------

create table EFFECTUER_EN_RAPPORT

(

NUM_ECHEANCE char (32) not null,

NUM_REMBOURSE char (32) not null

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE EFFECTUER_EN_RAPPORT

-- -----------------------------------------------------------------------------

create index I_FK_ECHEANCE_2

on EFFECTUER_EN_RAPPORT (NUM_ECHEANCE);

create index I_FK_REMBOURSEMENT

on EFFECTUER_EN_RAPPORT (NUM_REMBOURSE);

-- -----------------------------------------------------------------------------

-- TABLE : LIER_À

-- -----------------------------------------------------------------------------

create table LIER_À

(

NUM_OPERATION char (32) not null,

NUM_FACT char (32) not null

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE LIER_À

-- -----------------------------------------------------------------------------

create index I_FK_OPERATION

on LIER_À (NUM_OPERATION);

create index I_FK_FACTURE_3

on LIER_À (NUM_FACT);

-- -----------------------------------------------------------------------------

-- TABLE : ETRE_EN_RAPPORT

-- -----------------------------------------------------------------------------

create table ETRE_EN_RAPPORT

(

NUM_EMPRUNT char (32) not null,

NUM_POSTE char (32) not null

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE ETRE_EN_RAPPORT

-- -----------------------------------------------------------------------------

create index I_FK_EMPRUNT_2

on ETRE_EN_RAPPORT (NUM_EMPRUNT);

create index I_FK_POSTE_BUDGETAIRE_3

on ETRE_EN_RAPPORT (NUM_POSTE);

-- -----------------------------------------------------------------------------

-- TABLE : DEPENDRE

-- -----------------------------------------------------------------------------

create table DEPENDRE

(

NUM_DEM char (32) not null,

NUM_OPERATION char (32)

);

-- -----------------------------------------------------------------------------

-- INDEX DE LA TABLE DEPENDRE

-- -----------------------------------------------------------------------------

create index I_FK_DMANDE_3

on DEPENDRE (NUM_DEM);

create index I_FK_OPERATION_2

on DEPENDRE (NUM_OPERATION);

-- -----------------------------------------------------------------------------

-- TABLE : DEPENSE_2

-- -----------------------------------------------------------------------------

create table DEPENSE_2

(

NUM_DEP char (32) not null,

MONTANT_DEP char (32) ,

TAUX_DEP char (32)

);

-- -----------------------------------------------------------------------------

-- FIN DE GENERATION

-- -----------------------------------------------------------------------------