Views.sql

-- PARTIE 1:

-- IF YOU NEED TO DROP ALL TABLES IN THE DATABASE WITH ORACLE, HERE’S AN EASY WAY! RUN THIS COMMAND:

SELECT
    'drop table ',
    TABLE_NAME,
    'cascade constraints;'
FROM
    USER_TABLES;

-- DROP ONE TABLE
drop table table_name;


-- SHOW ALL TABLES OWNED BY THE CURRENT USER:

SELECT
    TABLE_NAME
FROM
    USER_TABLES;


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

-- run Creation_BD.sql
-- run Insertion_Tuples.sql

-- Q1
ALTER TABLE EQUIPE DISABLE CONSTRAINT KEY_EQUIPE CASCADE

CREATE VIEW CHERCHEUR_2400 AS
    SELECT
        CODECH,
        NOMCH,
        SALAIRE
    FROM
        CHERCHEUR
    WHERE
        SALAIRE > 2400;

INSERT INTO CHERCHEUR VALUES (15,'THIERRY',2,2600);

select * from chercheur;
SELECT * fROM CHERCHEUR_2400;

-- Q2
CREATE VIEW CHERCHEUR_EQ1 AS
    SELECT
        CODECH,
        NOMCH
    FROM
        CHERCHEUR
    WHERE
        CODEEQ = 1;

select * from chercheur;
SELECT * fROM CHERCHEUR_EQ1;

INSERT INTO CHERCHEUR VALUES (14,'RENAUD',1,1000);


SELECT * fROM CHERCHEUR_EQ1;

UPDATE CHERCHEUR
SET
    NOMCH = 'RENAUDIN'
WHERE
    NOMCH = 'RENAUD';

INSERT INTO CHERCHEUR_EQ1 VALUES (18, 'LALANDE');


DELETE FROM CHERCHEUR_EQ1 WHERE NOMCH = 'RENAUDIN';

-- c possible car c une vue mono table

--Q3
/*CREATE VIEW CHERCHEUR_EQUIPE AS
    SELECT
        CODECH,
        NOMCH,
        NOMEQ,
        NOMEQ
    FROM
        CHERCHEUR,
        EQUIPE; */

CREATE VIEW CHERCHEUR_EQUIPE AS
    SELECT
        CHERCHEUR.CODECH,
        CHERCHEUR.NOMCH,
        EQUIPE.NOMEQ,
        EQUIPE.CODERESP
    FROM
        CHERCHEUR
        JOIN EQUIPE
        ON CHERCHEUR.CODEEQ = EQUIPE.CODEEQ


select * from CHERCHEUR_EQUIPE where NOMEQ = 'FIRM';


-- il n'a pas pu mettre à jour la relation à travère une vue multitable ou bien contient une fonction d'aggrégation


-- PARTIE 2:
-- (IL EST ÉGALEMENT IMPORTANT DE SAUVEGARDER VOS DONNÉES AVANT DE PROCÉDER À TOUTE MODIFICATION DE STRUCTURE DE BASE DE DONNÉES.)

/* Q1
Faut faire les requêtes sur les vues et pas sur les tables_relations pour être flexible dans le futur en cas de changement du schéma relationnel
Les étapes :
1. création une nouvelle relation chercheur_equipe avec code_ch et code_eq clé primaire : les 2 et mettre les contraintes clé étrangère
2. peupler la relation 
3. ré-écrire les vues
4. supprimer code_eq de chercheur   */


-- Q2

/*
LES ÉTAPES EFFECTUÉES PAR L'administrateur pour réaliser la restructuration de la base de données sont les suivantes:
- CRÉER UNE NOUVELLE TABLE APPELÉE CHERCHEUR_EQUIPE POUR STOCKER LES ASSOCIATIONS ENTRE LES CHERCHEURS ET LES ÉQUIPES. CETTE TABLE AURAIT DES COLONNES CODECH, CODEEQ ET UNE CLÉ PRIMAIRE COMBINÉE SUR LES DEUX COLONNES.
- METTRE À JOUR LES DONNÉES EXISTANTES DANS LA TABLE CHERCHEUR POUR RETIRER LA COLONNE CODEEQ ET AJOUTER DES ENTRÉES CORRESPONDANTES DANS LA NOUVELLE TABLE CHERCHEUR_EQUIPE POUR CHAQUE CHERCHEUR.
- METTRE À JOUR LA VUE CHERCHEUR_EQUIPE POUR INCLURE UNE JOINTURE AVEC LA NOUVELLE TABLE CHERCHEUR_EQUIPE POUR AFFICHER LES INFORMATIONS SUR LES ÉQUIPES AUXQUELLES CHAQUE CHERCHEUR EST ASSOCIÉ.
- TESTER LES APPLICATIONS EXISTANTES POUR S'assurer qu'ELLES FONCTIONNENT TOUJOURS CORRECTEMENT AVEC LA NOUVELLE STRUCTURE DE DONNÉES.
- METTRE EN PLACE DES PROCÉDURES POUR GÉRER LES MISES À JOUR DE LA NOUVELLE TABLE CHERCHEUR_EQUIPE LORSQUE DES CHERCHEURS SONT AFFECTÉS OU DÉSAFFECTÉS DES ÉQUIPES.
*/

-- CRÉER LA NOUVELLE TABLE CHERCHEUR_EQUIPE :
CREATE TABLE CHERCHEUR_EQUIPE (
    CODECH INT NOT NULL,
    CODEEQ INT NOT NULL,
    PRIMARY KEY (CODECH, CODEEQ)
);

-- INSÉRER LES DONNÉES EXISTANTES DE LA TABLE CHERCHEUR DANS LA TABLE CHERCHEUR_EQUIPE :
INSERT INTO CHERCHEUR_EQUIPE (CodeCh, CodeEq)
SELECT CodeCh, CodeEq
FROM CHERCHEUR;

-- MODIFIER LA TABLE CHERCHEUR POUR SUPPRIMER LA COLONNE CODEEQ :
ALTER TABLE CHERCHEUR DROP COLUMN CODEEQ;

-- METTRE À JOUR LA VUE CHERCHEUR_EQUIPE POUR INCLURE UNE JOINTURE AVEC LA NOUVELLE TABLE CHERCHEUR_EQUIPE :
CREATE OR REPLACE VIEW CHERCHEUR_EQUIPE AS
SELECT CHERCHEUR.CodeCh, CHERCHEUR.NomCh, EQUIPE.NomEq, EQUIPE.CodeResp
FROM CHERCHEUR
JOIN CHERCHEUR_EQUIPE ON CHERCHEUR.CodeCh = CHERCHEUR_EQUIPE.CodeCh
JOIN EQUIPE ON CHERCHEUR_EQUIPE.CodeEq = EQUIPE.CodeEq;

-- POUR GÉRER LES MISES À JOUR DE LA NOUVELLE TABLE CHERCHEUR_EQUIPE LORSQUE DES CHERCHEURS SONT AFFECTÉS OU DÉSAFFECTÉS DES ÉQUIPES, VOUS POURRIEZ UTILISER DES REQUÊTES SQL TELLES QUE :
-- AJOUTER UN CHERCHEUR À UNE ÉQUIPE :
INSERT INTO CHERCHEUR_EQUIPE (CodeCh, CodeEq)
VALUES (15, 2);
-- RETIRER UN CHERCHEUR D'une équipe :
DELETE FROM CHERCHEUR_EQUIPE
WHERE CodeCh = 15 AND CodeEq = 2;

-- (ENFIN, IL EST IMPORTANT DE SURVEILLER LES PERFORMANCES DE LA BASE DE DONNÉES ET DE LES OPTIMISER SI NÉCESSAIRE APRÈS TOUTE MODIFICATION DE STRUCTURE DE DONNÉES.)