CREATE SCHEMA IF NOT EXISTS cafesdb;

CREATE TABLE IF NOT EXISTS cafesdb.programmeurs (
    id INT NOT NULL, 
    nom VARCHAR(32),
    prenom VARCHAR(32), 
    bureau INTEGER, 
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS cafesdb.consos_cafe (
   no_semaine INTEGER NOT NULL,
   programmeur INTEGER NOT NULL,
   nb_tasses INTEGER, 
   PRIMARY KEY (no_semaine, programmeur),
   CONSTRAINT fk_ProgConsos
   FOREIGN KEY (programmeur) REFERENCES cafesdb.programmeurs(id)
);


-- insertion des données dans la table programmeurs
INSERT INTO cafesdb.programmeurs (id, nom, prenom, bureau) 
	VALUES (1, 'FAVRE', 'Jean Marie', 412);
INSERT INTO cafesdb.programmeurs (id, nom, prenom, bureau) 
	VALUES (2, 'PARENT', 'Catherine', 201);
INSERT INTO cafesdb.programmeurs (id, nom, prenom, bureau) 
	VALUES (3, 'ENE', 'Christian', 208);
INSERT INTO cafesdb.programmeurs (id, nom, prenom, bureau) 
	VALUES (4, 'WAILLE', 'Philippe', 401);
INSERT INTO cafesdb.programmeurs (id, nom, prenom, bureau) 
	VALUES (5, 'GENOUD', 'Philippe', 381);
INSERT INTO cafesdb.programmeurs (id, nom, prenom, bureau) 
	VALUES (6, 'SICARD', 'Pascal', 404);
INSERT INTO cafesdb.programmeurs (id, nom, prenom, bureau) 
	VALUES (7, 'CAFFIAU', 'Sybille', 445);
INSERT INTO cafesdb.programmeurs (id, nom, prenom, bureau) 
	VALUES (8, 'CORTES-CORNAX', 'Mario', 395);
INSERT INTO cafesdb.programmeurs (id, nom, prenom, bureau) 
	VALUES (9, 'GARDNER', 'Virginia', 446);

-- insertions des consommations de café par semaine
-- Semaine 1 ----------
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (1, 1, 12);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (1, 2, 18);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (1, 3, 9);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (1, 4, 6);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (1, 5, 7);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (1, 6, 17);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (1, 7, 26);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (1, 8, 8);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (1, 9, 6);
-- Semaine 2 ----------
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (2, 1, 4);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (2, 2, 29);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (2, 3, 18);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (2, 4, 2);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (2, 5, 11);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (2, 6, 19);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (2, 7, 6);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (2, 8, 3);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (2, 9, 22);
-- Semaine 3 ----------
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (3, 1, 17);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (3, 2, 26);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (3, 3, 2);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (3, 4, 1);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (3, 5, 23);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (3, 6, 17);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (3, 7, 12);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (3, 8, 23);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (3, 9, 28);
-- Semaine 4 ----------
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (4, 1, 4);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (4, 2, 14);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (4, 3, 18);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (4, 4, 18);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (4, 5, 14);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (4, 6, 26);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (4, 7, 4);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (4, 8, 6);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (4, 9, 24);
-- Semaine 5 ----------
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (5, 1, 23);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (5, 2, 4);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (5, 3, 19);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (5, 4, 4);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (5, 5, 24);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (5, 6, 15);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (5, 7, 0);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (5, 8, 7);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (5, 9, 18);
-- Semaine 6 ----------
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (6, 1, 3);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (6, 2, 10);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (6, 3, 22);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (6, 4, 26);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (6, 5, 14);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (6, 6, 25);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (6, 7, 12);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (6, 8, 11);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (6, 9, 10);
-- Semaine 7 ----------
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (7, 1, 6);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (7, 2, 2);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (7, 3, 7);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (7, 4, 12);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (7, 5, 17);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (7, 6, 6);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (7, 7, 9);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (7, 8, 20);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (7, 9, 7);
-- Semaine 8 ----------
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (8, 1, 7);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (8, 2, 17);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (8, 3, 11);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (8, 4, 1);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (8, 5, 23);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (8, 6, 23);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (8, 7, 2);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (8, 8, 10);
INSERT INTO cafesdb.consos_cafe (no_semaine, programmeur, nb_tasses)
   VALUES (8, 9, 5);

