-- ###################################################################
-- # Application : SQL script
-- # File        : create_fill_tp_note.sql
-- # Revision    : janvier 2014 
-- # Author      : Marie Szafranski
-- # Function    : structure et alimentation de la base de données 
--                 formation
-- ###################################################################

-- *********************
-- * Creation du schema
-- *********************

-- suppression des tables si besoin


CREATE TABLE t_Formateur(
   code_formateur INTEGER,
   nom VARCHAR(50) NOT NULL,
   specialite_1 VARCHAR(50) NOT NULL,
   specialite_2 VARCHAR(50),
   CONSTRAINT pk_t_Formateur PRIMARY KEY (code_formateur));
   
CREATE TABLE t_Filiere(
   code_filiere CHAR(3),   
   libelle VARCHAR(100) NOT NULL,
   responsable INTEGER NOT NULL,
   adjoint INTEGER,
   CONSTRAINT pk_t_Filiere PRIMARY KEY (code_filiere),
   CONSTRAINT fk_t_UE_responsable FOREIGN KEY (responsable) 
      REFERENCES t_Formateur(code_formateur),
   CONSTRAINT fk_t_UE_adjoint FOREIGN KEY (adjoint) 
      REFERENCES t_Formateur(code_formateur));      

CREATE TABLE t_UE(
   code_UE CHAR(4),   
   libelle VARCHAR(100) NOT NULL,
   specialite_principale VARCHAR(50),
   responsable INTEGER NOT NULL,
   CONSTRAINT pk_t_UE PRIMARY KEY (code_UE),
   CONSTRAINT fk_t_UE_responsable FOREIGN KEY (responsable) 
      REFERENCES t_Formateur(code_formateur));
      
CREATE TABLE t_Filiere_UE(
   code_filiere CHAR(3),
   code_UE CHAR(4),
   CONSTRAINT pk_t_FiUE PRIMARY KEY (code_filiere, code_UE),
   CONSTRAINT fk_t_FiUE_code_filiere FOREIGN KEY (code_filiere)
      REFERENCES t_Filiere(code_filiere),
   CONSTRAINT fk_t_FiUE_code_UE FOREIGN KEY (code_UE)
      REFERENCES t_UE(code_UE));   

-- **************************
-- * Alimentation de la base
-- **************************

DELETE FROM t_Filiere_UE;
DELETE FROM t_Filiere;
DELETE FROM t_UE;
DELETE FROM t_Formateur;

--> table t_Formateur
INSERT INTO t_Formateur(code_formateur, nom, specialite_1, specialite_2) 
VALUES (1, 'Arthur Pendragon', 'chevalerie', 'strategie');
INSERT INTO t_Formateur(code_formateur, nom, specialite_1, specialite_2) 
VALUES (2, 'Lancelot du Lac', 'chevalerie', 'strategie');
INSERT INTO t_Formateur(code_formateur, nom, specialite_1, specialite_2) 
VALUES (3, 'Perceval de Galles', 'chevalerie', 'strategie');
INSERT INTO t_Formateur(code_formateur, nom, specialite_1, specialite_2) 
VALUES (4, 'Leodagan le sanguinaire', 'chevalerie', 'infiltration');
INSERT INTO t_Formateur(code_formateur, nom, specialite_1, specialite_2) 
VALUES (5, 'Loth d Orcanie', 'chevalerie', 'diplomatie');
INSERT INTO t_Formateur(code_formateur, nom, specialite_1, specialite_2) 
VALUES (6, 'Bohort le jeune', 'diplomatie', 'infiltration');
INSERT INTO t_Formateur(code_formateur, nom, specialite_1, specialite_2) 
VALUES (7, 'Pere Blaise', 'diplomatie', 'strategie');
INSERT INTO t_Formateur(code_formateur, nom, specialite_1, specialite_2) 
VALUES (8, 'Dame Seli', 'strategie', 'diplomatie');
INSERT INTO t_Formateur(code_formateur, nom, specialite_1, specialite_2) 
VALUES (9, 'Karadoc de Vannes', 'chevalerie', NULL);
INSERT INTO t_Formateur(code_formateur, nom, specialite_1, specialite_2) 
VALUES (10, 'Yvain', 'infiltration', 'chevalerie');
INSERT INTO t_Formateur(code_formateur, nom, specialite_1, specialite_2) 
VALUES (11, 'Gauvain', 'infiltration', 'chevalerie');
INSERT INTO t_Formateur(code_formateur, nom, specialite_1, specialite_2) 
VALUES (12, 'Merlin l enchanteur', 'druidisme', NULL);
INSERT INTO t_Formateur(code_formateur, nom, specialite_1, specialite_2) 
VALUES (13, 'Elias de Kelliwic h', 'druidisme', NULL);
INSERT INTO t_Formateur(code_formateur, nom, specialite_1, specialite_2) 
VALUES (14, 'Calogrenant de Caledonie', 'chevalerie', NULL);
INSERT INTO t_Formateur(code_formateur, nom, specialite_1, specialite_2) 
VALUES (15, 'Dagonet', 'chevalerie', NULL);
INSERT INTO t_Formateur(code_formateur, nom, specialite_1, specialite_2) 
VALUES (16, 'Galecin', 'chevalerie', NULL);

--> table t_Filiere
INSERT INTO t_Filiere(code_filiere, libelle, responsable, adjoint)
VALUES ('GSM', 'Genie des Strategies Militaires', 1, 2);
INSERT INTO t_Filiere(code_filiere, libelle, responsable, adjoint)
VALUES ('GD', 'Genie Diplomatique', 1, 6);
INSERT INTO t_Filiere(code_filiere, libelle, responsable, adjoint)
VALUES ('GB', 'Genie des Batailles', 2, 4);
INSERT INTO t_Filiere(code_filiere, libelle, responsable, adjoint)
VALUES ('GS', 'Genie des Sortileges', 12, 13);
INSERT INTO t_Filiere(code_filiere, libelle, responsable, adjoint)
VALUES ('GI', 'Genie de l Infiltration', 10, 11);

--> table t_UE
-- strategie
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('ST01', 'Strategies militaires offensives', 1);
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('ST02', 'Strategies militaires defensives', 2);
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('ST03', 'Histoire des guerres de Bretagne', 7);
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('ST04', 'Strategies de desorientation', 3);
-- diplomatie
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('DI01', 'Negociations',  7);
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('DI02', 'Injonctions et sommations',  4);
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('DI03', 'Art du chantage',  8);
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('DI04', 'Art de la traitrise',  5);
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('DI05', 'Us et coutumes de l empire romain', 1);
-- INSERT INTO t_UE(code_UE, libelle, responsable)
-- VALUES ('DI06', 'Us et coutumes du royaume de Logres',  6);
-- chevalerie
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('BT01', 'Armes de contact', 1);
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('BT02', 'Armes de jet', 2);
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('BT03', 'Armes de siege', 4);
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('BT04', 'Armes detournees', 9);
-- infiltration
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('IF01', 'Art du camouflage naturel', 10);
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('IF02', 'Art du deguisement', 11);
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('IF03', 'Art de la comedie', 6);
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('IF04', 'Art du repli', 6);
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('IF05', 'Resitance aux interrogatoires', 4);
-- druidisme
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('SO01', 'Sortileges et potions de metamorphose', 13); 
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('SO02', 'Sortileges et potions d invisibilite', 13);
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('SO03', 'Elixirs de guerison', 12);
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('SO04', 'Potions de verite', 13);
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('SO05', 'Sortileges d attaque', 13);
INSERT INTO t_UE(code_UE, libelle, responsable)
VALUES ('SO06', 'Communication avec les animaux', 12);

--> table t_Filiere_UE
INSERT INTO t_Filiere_UE VALUES ('GSM', 'ST01');
INSERT INTO t_Filiere_UE VALUES ('GSM', 'ST02');
INSERT INTO t_Filiere_UE VALUES ('GSM', 'ST03');
INSERT INTO t_Filiere_UE VALUES ('GD', 'ST03');
INSERT INTO t_Filiere_UE VALUES ('GSM', 'ST04');
INSERT INTO t_Filiere_UE VALUES ('GI', 'ST04');
INSERT INTO t_Filiere_UE VALUES ('GD', 'DI01');
INSERT INTO t_Filiere_UE VALUES ('GD', 'DI02');
INSERT INTO t_Filiere_UE VALUES ('GD', 'DI03');
INSERT INTO t_Filiere_UE VALUES ('GD', 'DI04');
INSERT INTO t_Filiere_UE VALUES ('GD', 'DI05');
INSERT INTO t_Filiere_UE VALUES ('GSM', 'DI05');
INSERT INTO t_Filiere_UE VALUES ('GB', 'BT01');
INSERT INTO t_Filiere_UE VALUES ('GB', 'BT02');
INSERT INTO t_Filiere_UE VALUES ('GB', 'BT03');
INSERT INTO t_Filiere_UE VALUES ('GB', 'BT04');
INSERT INTO t_Filiere_UE VALUES ('GI', 'IF01');
INSERT INTO t_Filiere_UE VALUES ('GI', 'IF02');
INSERT INTO t_Filiere_UE VALUES ('GI', 'IF03');
INSERT INTO t_Filiere_UE VALUES ('GD', 'IF03');
INSERT INTO t_Filiere_UE VALUES ('GI', 'IF04');
INSERT INTO t_Filiere_UE VALUES ('GD', 'IF04');
INSERT INTO t_Filiere_UE VALUES ('GI', 'IF05');
INSERT INTO t_Filiere_UE VALUES ('GB', 'IF05');
INSERT INTO t_Filiere_UE VALUES ('GS', 'SO01');
INSERT INTO t_Filiere_UE VALUES ('GI', 'SO01');
INSERT INTO t_Filiere_UE VALUES ('GS', 'SO02');
INSERT INTO t_Filiere_UE VALUES ('GI', 'SO02');
INSERT INTO t_Filiere_UE VALUES ('GS', 'SO03');
INSERT INTO t_Filiere_UE VALUES ('GS', 'SO04');
INSERT INTO t_Filiere_UE VALUES ('GS', 'SO05');
INSERT INTO t_Filiere_UE VALUES ('GS', 'SO06');
