--------------------------------------------------------------------------------
--                     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
BEGIN
  -- /!\ TODO /!\
  
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(
  -- /!\ TODO /!
);

----------------------
-- #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
  -- /!\ TODO /!\
  
DECLARE
  
BEGIN
  -- /!\ TODO /!\
  
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(
  -- /!\ TODO /!\
  
);

-- 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(
  -- /!\ TODO /!\
  
);

-- 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.


  -- /!\ TODO /!\
      
----------------------
-- #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
  -- /!\ TODO /!\

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(
  -- /!\ TODO /!\

);


----------------------
-- #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
  -- /!\ TODO /!\
  
BEGIN
  -- /!\ TODO /!\

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)


------------------------
-- #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(
    -- /!\ TODO /!\
    
);

CREATE OR REPLACE VIEW TP5A_ETU_VUE AS
  -- /!\ TODO /!\
  ;


-- 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;