CREATE TABLE cc_carnet
(
  id_carnet serial NOT NULL,
  lib_carnet character varying(50) NOT NULL,
  id_util integer NOT NULL,
  CONSTRAINT "PK_carnet" PRIMARY KEY (id_carnet)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_carnet OWNER TO postgres;
GRANT ALL ON TABLE cc_carnet TO cpnrc;





CREATE TABLE cc_liste
(
  id_liste serial NOT NULL,
  lib_liste character varying(50) NOT NULL,
  CONSTRAINT "PK_liste" PRIMARY KEY (id_liste)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_liste OWNER TO postgres;
GRANT ALL ON TABLE cc_liste TO cpnrc;





CREATE TABLE cc_type_cotisation
(
  code_cotisation serial NOT NULL,
  lib_cotisation character varying(50) NOT NULL,
  CONSTRAINT "PK_type_cotisation" PRIMARY KEY (code_cotisation)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_type_cotisation OWNER TO postgres;
GRANT ALL ON TABLE cc_type_cotisation TO cpnrc;





CREATE TABLE cc_type_document
(
  code_type_doc serial NOT NULL,
  lib_type_doc character varying(50) NOT NULL,
  CONSTRAINT "PK_type_document" PRIMARY KEY (code_type_doc)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_type_document OWNER TO postgres;
GRANT ALL ON TABLE cc_type_document TO cpnrc;






CREATE TABLE cc_document
(
  id_document serial NOT NULL,
  lib_document character varying(50) NOT NULL,
  lien_web character varying(100),
  annee_document smallint NOT NULL,
  code_type_doc integer NOT NULL,
  stock integer,
  CONSTRAINT "PK_document" PRIMARY KEY (id_document),
  CONSTRAINT "FK1_document" FOREIGN KEY (code_type_doc)
      REFERENCES cc_type_document (code_type_doc) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_document OWNER TO postgres;
GRANT ALL ON TABLE cc_document TO cpnrc;





CREATE TABLE cc_type_civilite
(
  code_civilite serial NOT NULL,
  lib_civilite character varying(50) NOT NULL,
  CONSTRAINT "PK_type_civilite" PRIMARY KEY (code_civilite)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_type_civilite OWNER TO postgres;
GRANT ALL ON TABLE cc_type_civilite TO cpnrc;





CREATE TABLE cc_type_statut
(
  code_statut serial NOT NULL,
  lib_statut character varying(80) NOT NULL,
  CONSTRAINT "PK_type_statut" PRIMARY KEY (code_statut)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_type_statut OWNER TO postgres;
GRANT ALL ON TABLE cc_type_statut TO cpnrc;






CREATE TABLE cc_coordonnees
(
  id_coordonnees serial NOT NULL,
  tel character varying(10),
  fax character varying(10),
  mail character varying(80),
  CONSTRAINT "PK_cc_coordonnees" PRIMARY KEY (id_coordonnees)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_coordonnees OWNER TO postgres;
GRANT ALL ON TABLE cc_coordonnees TO cpnrc;





CREATE TABLE cc_composition_carnet
(
  id_carnet integer NOT NULL,
  id_coordonnees integer NOT NULL,
  CONSTRAINT "PK_composition_carnet" PRIMARY KEY (id_carnet, id_coordonnees),
  CONSTRAINT "FK1_composition_carnet" FOREIGN KEY (id_carnet)
      REFERENCES cc_carnet (id_carnet) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK2_composition_carnet" FOREIGN KEY (id_coordonnees)
      REFERENCES cc_coordonnees (id_coordonnees) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_composition_carnet OWNER TO postgres;
GRANT ALL ON TABLE cc_composition_carnet TO cpnrc;





CREATE TABLE cc_coordonnees_gen
(
  id_coordonnees_gen integer NOT NULL,
  adresse character varying(80) NOT NULL,
  cp character varying(5) NOT NULL,
  ville character varying(50) NOT NULL,
  cpt character varying(50),
  CONSTRAINT "PK_cc_coordonnees_gen" PRIMARY KEY (id_coordonnees_gen),
  CONSTRAINT "FK1_cc_coordonnees_gen" FOREIGN KEY (id_coordonnees_gen)
      REFERENCES cc_coordonnees (id_coordonnees) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_coordonnees_gen OWNER TO postgres;
GRANT ALL ON TABLE cc_coordonnees_gen TO cpnrc;





CREATE TABLE cc_contact
(
  id_contact serial NOT NULL,
  notes character varying(200),
  raison_cotisation character varying(100),
  mail_ON character(1),
  courrier_ON character(1),
  cache integer,
  type_contact character varying(10) NOT NULL,
  id_coordonnees_gen integer NOT NULL,
  CONSTRAINT "PK_contact" PRIMARY KEY (id_contact),
  CONSTRAINT "FK1_contact" FOREIGN KEY (id_coordonnees_gen)
      REFERENCES cc_coordonnees_gen (id_coordonnees_gen) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_contact OWNER TO postgres;
GRANT ALL ON TABLE cc_contact TO cpnrc;




CREATE TABLE cc_composition_liste
(
  id_liste integer NOT NULL,
  id_contact integer NOT NULL,
  CONSTRAINT "PK_composition_liste" PRIMARY KEY (id_liste, id_contact),
  CONSTRAINT "FK1_composition_liste" FOREIGN KEY (id_liste)
      REFERENCES cc_liste (id_liste) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK2_composition_liste" FOREIGN KEY (id_contact)
      REFERENCES cc_contact (id_contact) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_composition_liste OWNER TO postgres;
GRANT ALL ON TABLE cc_composition_liste TO cpnrc;





CREATE TABLE cc_type_o
(
  code_type_o serial NOT NULL,
  lib_type_o character varying(50) NOT NULL,
  CONSTRAINT "PK_type_o" PRIMARY KEY (code_type_o)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_type_o OWNER TO postgres;
GRANT ALL ON TABLE cc_type_o TO cpnrc;





CREATE TABLE cc_domaine_o
(
  code_domaine_o serial NOT NULL,
  lib_domaine_o character varying(50) NOT NULL,
  CONSTRAINT "PK_domaine_o" PRIMARY KEY (code_domaine_o)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_domaine_o OWNER TO postgres;
GRANT ALL ON TABLE cc_domaine_o TO cpnrc;






CREATE TABLE cc_personne
(
  id_personne integer NOT NULL,
  nom character varying(50) NOT NULL,
  prenom character varying(50),
  code_civilite integer NOT NULL,
  CONSTRAINT "PK_personne" PRIMARY KEY (id_personne),
  CONSTRAINT "FK1_personne" FOREIGN KEY (id_personne)
      REFERENCES cc_contact (id_contact) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK2_personne" FOREIGN KEY (code_civilite)
      REFERENCES cc_type_civilite (code_civilite) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_personne OWNER TO postgres;
GRANT ALL ON TABLE cc_personne TO cpnrc;






CREATE TABLE cc_sigle
(
  code_sigle integer NOT NULL,
  lib_sigle character varying(50) NOT NULL,
  CONSTRAINT "PK_sigle" PRIMARY KEY (code_sigle)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_sigle OWNER TO postgres;
GRANT ALL ON TABLE cc_sigle TO cpnrc;






CREATE TABLE cc_organisme
(
  id_organisme integer NOT NULL,
  intitule character varying(100) NOT NULL,
  nom_abrege character varying (30),
  titre character varying(50),
  site_web character varying(80),
  periodicite character varying(30),
  lib_sigle_perso character varying(20),
  code_sigle integer,
  code_type_o integer NOT NULL,
  code_domaine_o integer NOT NULL,
  id_representant integer,
  CONSTRAINT "PK_organisme" PRIMARY KEY (id_organisme),
  CONSTRAINT "FK1_organisme" FOREIGN KEY (id_organisme)
      REFERENCES cc_contact (id_contact) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK2_organisme" FOREIGN KEY (code_domaine_o)
      REFERENCES cc_domaine_o (code_domaine_o) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK3_organisme" FOREIGN KEY (code_type_o)
      REFERENCES cc_type_o (code_type_o) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK4_organisme" FOREIGN KEY (id_representant)
      REFERENCES cc_personne (id_personne) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK5_organisme" FOREIGN KEY (code_sigle)
      REFERENCES cc_sigle (code_sigle) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_organisme OWNER TO postgres;
GRANT ALL ON TABLE cc_organisme TO cpnrc;





CREATE TABLE cc_type_diffusion
(
  code_diffusion serial NOT NULL,
  lib_diffusion character varying(50) NOT NULL,
  CONSTRAINT "PK_type_diffusion" PRIMARY KEY (code_diffusion)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_type_diffusion OWNER TO postgres;
GRANT ALL ON TABLE cc_type_diffusion TO cpnrc;





CREATE TABLE cc_diffusion
(
  id_diffusion serial NOT NULL,
  qte integer NOT NULL,
  date_diffusion date,
  code_diffusion integer NOT NULL,
  id_document integer NOT NULL,
  id_coordonnees integer NOT NULL,
  CONSTRAINT "PK_diffusion" PRIMARY KEY (id_diffusion),
  CONSTRAINT "FK1_diffusion" FOREIGN KEY (id_coordonnees)
      REFERENCES cc_coordonnees (id_coordonnees) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK2_diffusion" FOREIGN KEY (id_document)
      REFERENCES cc_document (id_document) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK3_diffusion" FOREIGN KEY (code_diffusion)
      REFERENCES cc_type_diffusion (code_diffusion) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_diffusion OWNER TO postgres;
GRANT ALL ON TABLE cc_diffusion TO cpnrc;





CREATE TABLE cc_cotisation
(
  id_cotisation serial NOT NULL,
  annee_cotisation integer NOT NULL,
  date_cheque date,
  num_cheque character varying (20),
  date_paiement date,
  mode_versement character varying(50),
  montant double precision,
  num_recu integer,
  code_cotisation integer NOT NULL,
  id_contact integer NOT NULL,
  CONSTRAINT "PK_cotisation" PRIMARY KEY (id_cotisation),
  CONSTRAINT "FK1_cotisation" FOREIGN KEY (code_cotisation)
      REFERENCES cc_type_cotisation (code_cotisation) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK2_cotisation" FOREIGN KEY (id_contact)
      REFERENCES cc_contact (id_contact) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_cotisation OWNER TO postgres;
GRANT ALL ON TABLE cc_cotisation TO cpnrc;





CREATE TABLE cc_contact_site
(
  id_contact integer NOT NULL,
  code_site character(12) NOT NULL,
  CONSTRAINT "PK_contact_site" PRIMARY KEY (id_contact, code_site),
  CONSTRAINT "FK1_contact_site" FOREIGN KEY (id_contact)
      REFERENCES cc_contact (id_contact) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK2_contact_site" FOREIGN KEY (code_site)
      REFERENCES sites (code_site) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_contact_site OWNER TO postgres;
GRANT ALL ON TABLE cc_contact_site TO cpnrc;






CREATE TABLE cc_service
(
  id_service serial NOT NULL,
  lib_service character varying(100) NOT NULL,
  id_organisme integer NOT NULL,
  CONSTRAINT "PK_service" PRIMARY KEY (id_service),
  CONSTRAINT "FK1_service" FOREIGN KEY (id_organisme)
      REFERENCES cc_organisme (id_organisme) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_service OWNER TO postgres;
GRANT ALL ON TABLE cc_service TO cpnrc;






CREATE TABLE cc_coordonnees_spe
(
  id_coordonnees_spe integer NOT NULL,
  civilite_spe character varying(50),
  id_personne integer NOT NULL,
  id_organisme integer NOT NULL,
  code_statut integer NOT NULL,
  id_service integer NOT NULL,
  CONSTRAINT "PK_coordonnees_spe" PRIMARY KEY (id_coordonnees_spe),
  CONSTRAINT "FK1_coordonnees_spe" FOREIGN KEY (id_personne)
      REFERENCES cc_personne (id_personne) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK2_coordonnees_spe" FOREIGN KEY (id_organisme)
      REFERENCES cc_organisme (id_organisme) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK3_coordonnees_spe" FOREIGN KEY (code_statut)
      REFERENCES cc_type_statut (code_statut) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK4_cc_coordonnees_spe" FOREIGN KEY (id_coordonnees_spe)
      REFERENCES cc_coordonnees (id_coordonnees) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK5_cc_coordonnees_spe" FOREIGN KEY (id_service)
      REFERENCES cc_service (id_service) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_coordonnees_spe OWNER TO postgres;
GRANT ALL ON TABLE cc_coordonnees_spe TO cpnrc;








CREATE TABLE cc_type_commission
(
  code_commission serial NOT NULL,
  lib_commission character varying(100) NOT NULL,
  CONSTRAINT "PK_type_commission" PRIMARY KEY (code_commission)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_type_commission OWNER TO postgres;
GRANT ALL ON TABLE cc_type_commission TO cpnrc;




CREATE TABLE cc_commission
(
  id_commission integer NOT NULL,
  id_personne integer NOT NULL,
  id_organisme integer NOT NULL,
  code_statut integer NOT NULL,
  id_coordonnees integer NOT NULL,
  code_commission integer NOT NULL,
  CONSTRAINT "PK_commission" PRIMARY KEY (id_commission),
  CONSTRAINT "FK1_commission" FOREIGN KEY (id_personne)
      REFERENCES cc_personne (id_personne) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK2_commission" FOREIGN KEY (id_organisme)
      REFERENCES cc_organisme (id_organisme) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK3_commission" FOREIGN KEY (code_statut)
      REFERENCES cc_type_statut (code_statut) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK4_commission" FOREIGN KEY (id_coordonnees)
      REFERENCES cc_coordonnees (id_coordonnees) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK5_commission" FOREIGN KEY (code_commission)
      REFERENCES cc_type_commission (code_commission) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION

)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_commission OWNER TO postgres;
GRANT ALL ON TABLE cc_commission TO cpnrc;





CREATE TABLE cc_type_partenariat
(
  code_partenariat serial NOT NULL,
  lib_partenariat character varying(50) NOT NULL,
  CONSTRAINT "PK_type_partenariat" PRIMARY KEY (code_partenariat)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_type_partenariat OWNER TO postgres;
GRANT ALL ON TABLE cc_type_partenariat TO cpnrc;





CREATE TABLE cc_partenaire
(
  code_partenariat integer NOT NULL,
  id_contact integer NOT NULL,
  CONSTRAINT "PK_partenaire" PRIMARY KEY (code_partenariat, id_contact),
  CONSTRAINT "FK1_partenaire" FOREIGN KEY (code_partenariat)
      REFERENCES cc_type_partenariat (code_partenariat) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "FK2_partenaire" FOREIGN KEY (id_contact)
      REFERENCES cc_contact (id_contact) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=TRUE
);
ALTER TABLE cc_partenaire OWNER TO postgres;
GRANT ALL ON TABLE cc_partenaire TO cpnrc;













