/* -------------------------------------------------------- 
  Structure de la table centre 
-------------------------------------------------------- */
CREATE TABLE centre (
   n_centre int4 NOT NULL,
   nomc varchar(10),
   ca float8,
   n_fp int4,
   benef float8,
   dateouv date,
   n_siege int4,
   CONSTRAINT centre_pkey PRIMARY KEY (n_centre)
);



/* -------------------------------------------------------- 
  Contenu de la table centre 
-------------------------------------------------------- */ 
INSERT INTO centre (n_centre, nomc, ca, n_fp, benef, dateouv, n_siege) VALUES(1, 'PARIS1', 500, 1, 0.2, '2000-01-01', 1);
INSERT INTO centre (n_centre, nomc, ca, n_fp, benef, dateouv, n_siege) VALUES(2, 'PARIS2', 100, 2, 0.5, '2001-01-01', 1);
INSERT INTO centre (n_centre, nomc, ca, n_fp, benef, dateouv, n_siege) VALUES(3, 'LYON1', 250, 1, 0.2, '2000-05-01', 1);
INSERT INTO centre (n_centre, nomc, ca, n_fp, benef, dateouv, n_siege) VALUES(4, 'LYON2', 120, 3, 0.1, '2001-12-01', 3);
INSERT INTO centre (n_centre, nomc, ca, n_fp, benef, dateouv, n_siege) VALUES(5, 'LILLE1', 200, 2, 0.3, '2000-04-01', 1);
INSERT INTO centre (n_centre, nomc, ca, n_fp, benef, dateouv, n_siege) VALUES(6, 'MARSEILLE1', 430, 4, 0.1, '2002-01-01', 1);
INSERT INTO centre (n_centre, nomc, ca, n_fp, benef, dateouv, n_siege) VALUES(7, 'BORDEAUX1', 300, 2, 0.6, '2001-02-02', 1);
INSERT INTO centre (n_centre, nomc, ca, n_fp, benef, dateouv, n_siege) VALUES(8, 'BORDEAUX2', NULL, NULL, NULL, '2004-01-01', 7);


/* -------------------------------------------------------- 
  Structure de la table centre_hf 
-------------------------------------------------------- */
CREATE TABLE centre_hf (
   n_centre int4 NOT NULL,
   nomc varchar(10),
   ca float8,
   n_fp int4,
   benef float8,
   dateouv date,
   n_siege int4,
   CONSTRAINT centre_hf_pkey PRIMARY KEY (n_centre)
);



/* -------------------------------------------------------- 
  Contenu de la table centre_hf 
-------------------------------------------------------- */ 
INSERT INTO centre_hf (n_centre, nomc, ca, n_fp, benef, dateouv, n_siege) VALUES(11, 'ROME', 500, 1, 0.2, '2003-01-01', 1);
INSERT INTO centre_hf (n_centre, nomc, ca, n_fp, benef, dateouv, n_siege) VALUES(12, 'LONDRES', 100, 2, 0.5, '2004-01-01', 1);
INSERT INTO centre_hf (n_centre, nomc, ca, n_fp, benef, dateouv, n_siege) VALUES(13, 'TOKYO', 250, 1, 0.2, '2003-05-01', 1);
INSERT INTO centre_hf (n_centre, nomc, ca, n_fp, benef, dateouv, n_siege) VALUES(14, 'NEWYORK1', 120, 3, 0.1, '2004-12-01', 3);
INSERT INTO centre_hf (n_centre, nomc, ca, n_fp, benef, dateouv, n_siege) VALUES(15, 'NEWYORK2', 200, 2, 0.3, '2003-04-01', 1);
INSERT INTO centre_hf (n_centre, nomc, ca, n_fp, benef, dateouv, n_siege) VALUES(16, 'BARCELONE', 430, 6, 0.1, '2005-01-01', 1);

/* -------------------------------------------------------- 
  Structure de la table fp 
-------------------------------------------------------- */
CREATE TABLE fp (
   n_fp int4 NOT NULL,
   nomfp varchar(15),
   CONSTRAINT fp_pkey PRIMARY KEY (n_fp)
);


/* -------------------------------------------------------- 
  Contenu de la table fp 
-------------------------------------------------------- */ 
INSERT INTO fp (n_fp, nomfp) VALUES(1, 'QUINCAILLERIE');
INSERT INTO fp (n_fp, nomfp) VALUES(2, 'BOIS');
INSERT INTO fp (n_fp, nomfp) VALUES(3, 'PEINTURE');
INSERT INTO fp (n_fp, nomfp) VALUES(4, 'JARDINAGE');
INSERT INTO fp (n_fp, nomfp) VALUES(5, 'HABILLEMENT');
INSERT INTO fp (n_fp, nomfp) VALUES(6, 'HIFI');

/* -------------------------------------------------------- 
  Vues 
-------------------------------------------------------- */ 



CREATE VIEW nb_centres_per_fp AS SELECT centre.n_fp, count(*) AS nb_centres FROM centre GROUP BY centre.n_fp; 