--------------------------------------------------------------------------------
--                     TP5A LIF10 - 2015 - 2016 
--------------------------------------------------------------------------------

-- NOM : 
-- PRENOM : 
-- NUMERO :

-- Ce fichier est à déposer au plus tard JEUDI 12/11/15 à 16:05 sur spiral
-- http://spiralconnect.univ-lyon1.fr/webapp/activities/activities.jsp?containerId=5119347

-- Le fichier que vous déposerez sera nommé NON_Prenom_1235678.txt avec 1235678 votre numéro

-- /!\ VOUS DEVEZ IMPÉRATIVEMENT RESPECTER LES NOMS DES TABLES, ATTRIBUTS /!\
--          /!\ FONCTIONS, ETC. TELS QUE SPÉCIFIÉS DANS LE SUJET /!\

-- /!\ LE FICHER QUE VOUS RENDEZ DOIT ÊTRE UN SCRIPT SQL VALIDE QUI S'EXÉCUTE /!\
--   /!\ SANS ERREURS (les exceptions volontaires ne sont PAS des erreurs)/!\

-- Le domaine métier du sujet de ce TP est celui de la gestion des notes d'étudiants à des
-- unités d'enseignement.

SET SERVEROUTPUT ON;

DROP TABLE TP5A_NOTE_LOG;
DROP TABLE TP5A_NOTE;
DROP TABLE TP5A_UE;
DROP TABLE TP5A_ETU;
DROP TABLE TP5A_ETU_ALT;

----------------------
-- #Q1A QUESTION 1  --
----------------------

-- On souhaite gérer des étudiants dont les identifiants sont de la forme
-- 'pYYXXXXXX' où YY est l'année en cours (ici, 15) et XXXXXX une suite de six
-- chiffres. Le code suivant permet de retrouver l'année sur deux chiffres :


SELECT TO_CHAR(SYSDATE, 'YY') AS Now
FROM DUAL;

-- Écrire la fonction PL/SQL suivante qui prend en entrée une chaîne de
-- caractères et renvoie un entier :
--     . si la chaîne est bien de la forme p15XXXXXX, alors la fonction renvoie
--       *le nombre* XXXXXX
--     . sinon, elle renvoie 0

-- Pour accéder à une sous-chaîne on utilisera la fonction SUBSTR
-- Pour tester si une chaine est un nombre, on essaie de la convertir avec
-- la fonction TO_NUMBER, si cela échoue, alors on peut attraper l'exception
-- de type VALUE_ERROR


CREATE OR REPLACE FUNCTION TP5A_is_valid_etu (pstr IN VARCHAR2)
   RETURN INT
IS
   v_num NUMBER;
   v_year NUMBER;
BEGIN
  IF ((SUBSTR(pstr,1,1) <> 'p') OR (LENGTH(pstr)<>9)) THEN
    RAISE VALUE_ERROR;   
  END IF;
  v_num := TO_NUMBER(SUBSTR(pstr,2));
   
  v_year := TO_NUMBER(SUBSTR(pstr,2,2));
  IF (TO_CHAR(SYSDATE, 'YY') <> v_year) THEN
    RAISE VALUE_ERROR;  
  END IF;
     
  RETURN v_num;
EXCEPTION
WHEN VALUE_ERROR THEN
  RETURN 0;
END is_valid_etu;
/


-- Testez votre fonction avec les exemples suivants
---------------------------------------------------

SELECT is_valid_etu('p15512231') AS OK
FROM DUAL;
SELECT is_valid_etu('p11512231') AS OK
FROM DUAL;
SELECT is_valid_etu('a15512231') AS OK
FROM DUAL;
SELECT is_valid_etu('a1551223') AS OK
FROM DUAL;


----------------------
-- #Q2A QUESTION 2  --
----------------------

-- Créer la table TP5A_ETU  des étudiants avec 4 attributs :
--   . id_etu une chaîne de 9 caractères qui sert d'identifiant unique
--   . num_etu un identifiant numérique sur 8 chiffres clef primaire
--   . nom_etu une chaîne de 64 caractères (avec contrainte NOT NULL)
--   . prenom_etu une chaîne de 64 caractères (avec contrainte NOT NULL)

CREATE TABLE TP5A_ETU(
  id_etu VARCHAR2(9) UNIQUE NOT NULL ,
  num_etu NUMBER(8) PRIMARY KEY,
  nom_etu VARCHAR2(64) NOT NULL,
  prenom_etu VARCHAR2(64) NOT NULL
);

----------------------
-- #Q3A QUESTION 3  --
----------------------

-- Créer un trigger sur la table TP5A_ETU qui, lors d'une insertion ou d'une
-- modification :
--  . vérifie que id_etu est bien de la forme spécifiée à la question 1.
--    si la vérification échoue, alors l'insertion ou la modification échouera.
--  . calcule num_etu à partir de id_etu (on écrase l'ancienne valeur)
--  . passe nom_etu en MAJUSCULE (on écrase l'ancienne valeur)
--  . passe prenom_etu ne minuscule sauf son premier caractère en MAJUSCULE
--    (on écrase l'ancienne valeur)

CREATE OR REPLACE TRIGGER TP5A_ETU_INSERT_CHECK
  BEFORE INSERT OR UPDATE ON TP5A_ETU
  FOR EACH ROW
DECLARE
   v_num NUMBER;
BEGIN
  v_num := is_valid_etu(:NEW.id_etu);
  IF (v_num = 0)  THEN
      raise_application_error(-20000,'Invalid ID Number');
  END IF;
  
  :NEW.num_etu := v_num;
  :NEW.nom_etu := UPPER(:NEW.nom_etu);
  :NEW.prenom_etu := (UPPER(SUBSTR(:NEW.prenom_etu,1,1)) || LOWER(SUBSTR(:NEW.prenom_etu,2)));
END;
/


-- Testez votre trigger avec les exemples suivants
---------------------------------------------------

INSERT INTO TP5A_ETU VALUES('p15001122', 0, 'TCioN', 'ROmuald');
UPDATE TP5A_ETU SET NOM_ETU = 'THioN' where ID_ETU = 'p15001122';
UPDATE TP5A_ETU SET ID_ETU = 'p15001123' where ID_ETU = 'p15001122';
INSERT INTO TP5A_ETU VALUES('p15112233', NULL, 'De Marchi', 'Fabien');
INSERT INTO TP5A_ETU VALUES('p15223344', NULL, 'PlanteVIT', 'MARC');

SELECT * FROM TP5A_ETU;
COMMIT;

----------------------
-- #Q4A QUESTION 4  --
----------------------

-- Créer la table TP5A_UE des unités d'enseignement avec 3 attributs :
--   . num_ue un identifiant numérique sur 4 chiffres clef primaire
--   . nom_ue une chaîne de 64 caractères identifiant unique de l'UE
--   . ects un nombre d'un seul chiffre, avec par défaut la valeur 3


CREATE TABLE TP5A_UE(
  num_ue NUMBER(4) PRIMARY KEY ,
  nom_ue VARCHAR2(64) UNIQUE NOT NULL,
  ects NUMBER(1) DEFAULT 3
);

-- Testez votre table avec les exemples suivants
------------------------------------------------

INSERT INTO TP5A_UE(num_ue,nom_ue) VALUES(0, 'LIF10');
INSERT INTO TP5A_UE(num_ue,nom_ue) VALUES(1, 'LIF11');
INSERT INTO TP5A_UE(num_ue,nom_ue) VALUES(2, 'LIF15');
SELECT * FROM TP5A_UE;
COMMIT;

----------------------
-- #Q5A QUESTION 5  --
----------------------

-- Créer la table TP5A_NOTE des notes avec 3 attributs :
--   . num_ue une référence à une UE de TP5A_UE
--   . num_etu une référence à un étudiant de TP5A_ETU
--   . moyenne un nombre de la forme xx.yy compris entre 0 et 20 (inclus)
-- Pensez à spécifier la contrainte de clef primaire.


CREATE TABLE TP5A_NOTE(
  num_ue NUMBER(4) REFERENCES TP5A_UE(num_ue) ,
  num_etu NUMBER(8) REFERENCES TP5A_ETU(num_etu),
  moyenne NUMBER(4,2) CHECK (moyenne >= 0 AND moyenne <= 20),
  CONSTRAINT pk_TP5A_NOTE PRIMARY KEY (num_ue, num_etu)
);

-- Testez votre table avec les exemples suivants
------------------------------------------------

INSERT INTO TP5A_NOTE VALUES(0, 15001123, 12.00);
INSERT INTO TP5A_NOTE VALUES(1, 15001123, 13.00);
INSERT INTO TP5A_NOTE VALUES(2, 15001123, 14.00);
INSERT INTO TP5A_NOTE VALUES(0, 15112233, 15.00);
INSERT INTO TP5A_NOTE VALUES(1, 15112233, 14.00);

SELECT * FROM TP5A_NOTE;
COMMIT;

----------------------
-- #Q6A QUESTION 6  --
----------------------

-- Écrire une requête qui, pour chaque étudiant (dont on donne le numéro, le nom
-- et le prénom) calcule le nombre d'UE pour lequel il a eu des notes ainsi
-- que la somme des crédits ECTS de ces UEs. Pour les étudiants qui n'ont aucune note, on
-- souhaite qu'ils apparaissent dans le résultat et que le nombre d'ECTS soit 0.


SELECT id_etu, nom_etu, prenom_etu, COUNT(TP5A_UE.NUM_UE) AS NBUEs, NVL(SUM(ECTS),0) as NBECTS
FROM  TP5A_ETU LEFT OUTER JOIN (TP5A_NOTE  INNER JOIN TP5A_UE ON TP5A_NOTE.NUM_UE = TP5A_UE.NUM_UE) ON TP5A_NOTE.NUM_ETU = TP5A_ETU.NUM_ETU
GROUP BY id_etu, nom_etu, prenom_etu
ORDER By id_etu ASC;
      
----------------------
-- #Q7A QUESTION 7  --
----------------------

-- Écrire une procédure qui va afficher sur la console la liste des étudiants
-- (dont on donne le numéro, le nom et le prénom) qui n'ont aucune note

CREATE OR REPLACE PROCEDURE TP5A_lister_phantomes IS
BEGIN
  DECLARE
    CURSOR c_etu IS
      SELECT id_etu, nom_etu, prenom_etu
      FROM  TP5A_ETU 
      WHERE num_etu NOT IN (SELECT num_etu FROM TP5A_NOTE);
     v_etu c_etu%ROWTYPE;
     
  BEGIN
   FOR v_etu IN c_etu LOOP
      dbms_output.put_line(v_etu.id_etu || ' : ' || v_etu.nom_etu || ' ' || v_etu.prenom_etu);
  END LOOP;    
  END;
END;
/

-- Testez votre procédure avec l'appel suivant
----------------------------------------------

CALL lister_phantomes();

----------------------
-- #Q8A QUESTION 8  --
----------------------

-- On souhaite enregistrer une trace lorsque une note de TP5A_NOTE est modifiée.
-- Créer pour cela une table TP5A_NOTE_LOG avec 6 attributs :
--   . num_ue un identifiant numérique sur 4 chiffres
--   . num_etu un identifiant numérique sur 8 chiffres
--   . moyenne_old un nombre de la forme xx.yy
--   . moyenne_old un nombre de la forme xx.yy 
--   . date_update une date non nulle avec par défaut la date du jour 
--   . user_id une chaîne de 64 caractères

CREATE TABLE TP5A_NOTE_LOG(
  num_ue NUMBER(4),
  num_etu NUMBER(8),
  moyenne_old NUMBER(4,2),
  moyenne_new NUMBER(4,2),
  date_update DATE DEFAULT SYSDATE NOT NULL,
  user_id  VARCHAR2(64)
);


----------------------
-- #Q9A QUESTION 9  --
----------------------

-- Créer un trigger, qui lors d'une mise-à-jour de l'attribut moyenne de
-- TP5A_NOTE, ajoute la modification à TP5A_NOTE_LOG.

CREATE OR REPLACE TRIGGER TP5A_NOTE_LOG
  AFTER UPDATE OF moyenne ON TP5A_NOTE
  FOR EACH ROW
BEGIN
  INSERT INTO TP5A_NOTE_LOG VALUES(
    :new.num_ue,
    :new.num_etu,
    :old.moyenne,
    :new.moyenne,
    SYSDATE,
    USER
    );
END;
/

-- Testez votre trigger avec les exemples suivants
---------------------------------------------------

UPDATE TP5A_NOTE SET MOYENNE = 19.00 WHERE num_ue = 0 and num_etu= 15001123;
SELECT * FROM TP5A_NOTE_LOG;
SELECT * FROM TP5A_NOTE;
ROLLBACK;
SELECT * FROM TP5A_NOTE_LOG;
SELECT * FROM TP5A_NOTE;


------------------------
-- #Q10A QUESTION 10  --
------------------------

-- Lors du ROLLBACK précédent, le tuple est *aussi* supprimé de TP5A_NOTE_LOG.
-- Expliquez pourquoi (en commentaire SQL)

-- Le tuple inseré par le trigger fait partie de la MEME transaction que la
-- modification sur TP5A_NOTE. En faisant un ROLLBACK, on annule la 
-- modification ET l'insertion, sauf si on met AUTONOMOUS_TRANSACTION

------------------------
-- #Q11A QUESTION 11  --
------------------------

-- Proposez une solution alternative aux questions #Q1A, #Q2A et #Q3A.
-- Pour cela, définir une table TP5A_ETU_ALT comportant les trois attributs
-- num_etu, nom_etu et prenom_etu avec une contrainte CHECK sur num_etu
-- (pour cette question, on fixera l'année en dur à 15)
-- et définir une vue TP5A_ETU_VUE sur TP5A_ETU_ALT qui calculera id_etu
-- et gérera la casse de nom_etu et prenom_etu


CREATE TABLE TP5A_ETU_ALT(
  num_etu NUMBER(8) CHECK (num_etu BETWEEN (15*1000000) AND (15*1000000+999999) ) PRIMARY KEY ,
  nom_etu VARCHAR2(64) NOT NULL,
  prenom_etu VARCHAR2(64) NOT NULL
);

CREATE OR REPLACE VIEW TP5A_ETU_VUE AS
SELECT  ('p' || TO_CHAR(num_etu)) AS id_etu, num_etu, UPPER(nom_etu) as nom_etu, (UPPER(SUBSTR(prenom_etu,1,1)) || LOWER(SUBSTR(prenom_etu,2))) as prenom_etu
FROM TP5A_ETU_ALT;


-- Testez votre vue avec les exemples suivants
------------------------------------------------

INSERT INTO TP5A_ETU_ALT
SELECT num_etu, nom_etu, prenom_etu  FROM TP5A_ETU;

SELECT * FROM TP5A_ETU_VUE V INNER JOIN TP5A_ETU R ON V.num_etu = R.num_etu
WHERE (V.nom_etu <> R.nom_etu) OR (V.prenom_etu <> R.prenom_etu) OR (V.id_etu <> R.id_etu);
-- le résultat doit être vide

INSERT INTO TP5A_ETU_ALT VALUES(15998877, 'hacid', 'said');
SELECT * FROM TP5A_ETU_VUE;
ROLLBACK;

