SQL

CREATE TABLE MAT(
MATC VARCHAR2(10) PRIMARY KEY,
INTC VARCHAR2(70)
);

insert into MAT(MATC, INTC)
select distinct MATC, INTC from thierry_millan.CELCAT2017
where MATC is not null;

CREATE TABLE GROUPE( GRPC VARCHAR2(20),ANNEEC CHAR(3));

CREATE TABLE CRENEAU( DEBSEMC DATE, JOURC VARCHAR2(8), HEUREDC CHAR(5), TYPEC VARCHAR2(20), HEUREFC CHAR(5), GRPC VARCHAR2(20), MATC VARCHAR2(10));

CREATE TABLE ENSEIGNER( DEBSEMC DATE, JOURC VARCHAR2(8), HEUREDC CHAR(5),GRPC varchar2(20), ENSC char(3));

CREATE TABLE AFFECTER ( DEBSEMC DATE, JOURC VARCHAR2(8), HEUREDC CHAR(5),GRPC varchar2(20), SALC varchar2(15));

alter table GROUPE add constraint PK_GROUPE primary key (GRPC);
alter table CRENEAU add constraint PK_CRENEAU primary key (DEBSEMC,JOURC,HEUREDC,GRPC);
alter table ENSEIGNER add constraint PK_ENSEIGNER primary key (DEBSEMC,JOURC,HEUREDC,GRPC,ENSC);
alter table AFFECTER add constraint PK_AFFECTER primary key (DEBSEMC,JOURC,HEUREDC,GRPC,SALC);

ALTER TABLE CRENEAU ADD CONSTRAINT FK_CRENEAU_MATC FOREIGN KEY (MATC) REFERENCES MAT(MATC);
ALTER TABLE CRENEAU ADD CONSTRAINT FK_CRENEAU_GRPC FOREIGN KEY (GRPC) REFERENCES GROUPE(GRPC);
ALTER TABLE AFFECTER ADD CONSTRAINT FK_AFFECTER_GRPC FOREIGN KEY (GRPC) REFERENCES GROUPE(GRPC);
ALTER TABLE ENSEIGNER ADD CONSTRAINT FK_ENSEIGNER_GRPC FOREIGN KEY (GRPC) REFERENCES GROUPE(GRPC);

ALTER TABLE AFFECTER ADD CONSTRAINT FK_AFFECTER_DEBSEMC_GRPC FOREIGN KEY (DEBSEMC, JOURC, HEUREDC, GRPC) REFERENCES CRENEAU ( DEBSEMC, JOURC, HEUREDC, GRPC) ;
ALTER TABLE ENSEIGNER ADD CONSTRAINT FK_ENSEIGNER_DEBSEMC_GRPC FOREIGN KEY (DEBSEMC, JOURC, HEUREDC, GRPC) REFERENCES CRENEAU ( DEBSEMC, JOURC, HEUREDC, GRPC) ;

insert into GROUPE(GRPC,ANNEEC) select distinct GRPC,ANNEEC from thierry_millan.CELCAT2017 where GRPC is not null;
insert into CRENEAU (DEBSEMC,JOURC,HEUREDC,TYPEC,HEUREFC,GRPC,MATC) select distinct DEBSEMC,JOURC,HEUREDC,TYPEC,HEUREFC,GRPC,MATC from thierry_millan.CELCAT2017 ;
insert into ENSEIGNER (DEBSEMC,JOURC,HEUREDC,GRPC,ENSC) select distinct DEBSEMC,JOURC,HEUREDC,GRPC,ENSC from thierry_millan.CELCAT2017 where ENSC is not null;
insert into AFFECTER(DEBSEMC,JOURC,HEUREDC,GRPC,SALC) select distinct DEBSEMC,JOURC,HEUREDC,GRPC,SALC from thierry_millan.CELCAT2017 where SALC is not null;

SELECT CRENEAU.DEBSEMC, CRENEAU.JOURC, CRENEAU.HEUREDC, TYPEC, HEUREFC, CRENEAU.GRPC, CRENEAU.MATC, SALC, ENSC, ANNEEC, INTC
FROM CRENEAU, MAT, GROUPE, ENSEIGNER, AFFECTER
WHERE CRENEAU.GRPC = GROUPE.GRPC
AND MAT.MATC(+) = CRENEAU.MATC
AND AFFECTER.DEBSEMC(+) = CRENEAU.DEBSEMC
AND AFFECTER.JOURC(+) = CRENEAU.JOURC
AND AFFECTER.HEUREDC(+) = CRENEAU.HEUREDC
AND AFFECTER.GRPC(+) = CRENEAU.GRPC
AND ENSEIGNER.DEBSEMC(+) = CRENEAU.DEBSEMC
AND ENSEIGNER.JOURC(+) = CRENEAU.JOURC
AND ENSEIGNER.HEUREDC(+) = CRENEAU.HEUREDC
AND ENSEIGNER.GRPC(+) = CRENEAU.GRPC
MINUS
SELECT DEBSEMC, JOURC, HEUREDC, TYPEC, HEUREFC, GRPC, MATC, SALC, ENSC, ANNEEC, INTC
FROM thierry_millan.CELCAT2017 ;


SELECT DEBSEMC, JOURC, HEUREDC, TYPEC, HEUREFC, GRPC, MATC, SALC, ENSC, ANNEEC, INTC
FROM thierry_millan.CELCAT2017
MINUS
SELECT CRENEAU.DEBSEMC, CRENEAU.JOURC, CRENEAU.HEUREDC, TYPEC, HEUREFC, CRENEAU.GRPC, CRENEAU.MATC, SALC, ENSC, ANNEEC, INTC
FROM CRENEAU, MAT, GROUPE, ENSEIGNER, AFFECTER
WHERE CRENEAU.GRPC = GROUPE.GRPC
AND MAT.MATC(+) = CRENEAU.MATC
AND AFFECTER.DEBSEMC(+) = CRENEAU.DEBSEMC
AND AFFECTER.JOURC(+) = CRENEAU.JOURC
AND AFFECTER.HEUREDC(+) = CRENEAU.HEUREDC
AND AFFECTER.GRPC(+) = CRENEAU.GRPC
AND ENSEIGNER.DEBSEMC(+) = CRENEAU.DEBSEMC
AND ENSEIGNER.JOURC(+) = CRENEAU.JOURC
AND ENSEIGNER.HEUREDC(+) = CRENEAU.HEUREDC
AND ENSEIGNER.GRPC(+) = CRENEAU.GRPC;

create table FORMATION as select * from thierry_millan.FORMATION2018;
create table SALLE as select * from thierry_millan.SALLE2018;
create table ENSEIGNANT as select * from thierry_millan.ENSEIGNANT2018;
create table STATUT as select * from thierry_millan.STATUT2018;

ALTER TABLE FORMATION add constraint PK_FORMATION primary key (IdFor);
ALTER TABLE SALLE add constraint PK_SALLE primary key (NSalle);
ALTER TABLE ENSEIGNANT add constraint PK_ENSEIGNANT primary key (IdENseign);
ALTER TABLE STATUT add constraint PK_STATUT primary key (Grade);

ALTER TABLE GROUPE ADD CONSTRAINT FK_GROUPE_ANNEEC FOREIGN KEY (ANNEEC) REFERENCES FORMATION(IdFor);
ALTER TABLE ENSEIGNANT ADD CONSTRAINT FK_ENSEIGNANT_GRADE FOREIGN KEY (Grade) REFERENCES STATUT(Grade);
ALTER TABLE ENSEIGNER ADD CONSTRAINT FK_ENSEIGNER_ENSC FOREIGN KEY (ENSC) REFERENCES ENSEIGNANT(IdEnseign);
ALTER TABLE AFFECTER ADD CONSTRAINT FK_AFFECTER_SALC FOREIGN KEY (SALC) REFERENCES SALLE(NSalle);

ALTER TABLE GROUPE ADD Eff NUMBER(3) DEFAULT 0;
UPDATE GROUPE SET Eff = (SELECT Eff FROM Thierry_millan.EFFORMATION2018 WHERE IDGRP = GROUPE.GRPC) ;

SELECT GROUPE.GRPC, GROUPE.EFF
FROM GROUPE, CRENEAU
WHERE GROUPE.GRPC = CRENEAU.GRPC
AND CRENEAU.JOURC = 'mercredi'
AND CRENEAU.DEBSEMC = '19/09/16'
AND CRENEAU.HEUREDC = '11:00'
AND CRENEAU.MATC = 'InM1101' ;

SELECT CRENEAU.MATC, SUM(CASE TYPEC WHEN 'CM' THEN ((to_date(HEUREFC,'HH24:MI')-to_date(HEUREDC,'HH24:MI'))*24*1.5) ELSE ((to_date(HEUREFC,'HH24:MI')-to_date(HEUREDC,'HH24:MI'))*24)END)
FROM CRENEAU
WHERE CRENEAU.TYPEC in('CM','TD','TP')
GROUP BY CRENEAU.MATC ;

SELECT * FROM SALLE ;
SELECT * FROM AFFECTER ;

-- 5.a

CREATE OR REPLACE TRIGGER types_cours_salles
BEFORE INSERT
ON AFFECTER FOR EACH ROW
DECLARE
    type_salle varchar2(10 BYTE);
    type_cours varchar2(10 BYTE);
    type_salle_diff_type_cours EXCEPTION ;
    PRAGMA EXCEPTION_INIT (type_salle_diff_type_cours, -00001);
BEGIN
    SELECT SALLE.TSAL INTO type_salle FROM SALLE WHERE SALLE.nsalle = :NEW.SALC ;
    SELECT CRENEAU.TYPEC INTO type_cours FROM CRENEAU
    WHERE CRENEAU.DEBSEMC = :NEW.DEBSEMC
    AND CRENEAU.JOURC = :NEW.JOURC
    AND CRENEAU.HEUREDC = :NEW.HEUREDC
    AND CRENEAU.GRPC = :NEW.GRPC ;
    IF type_cours = 'TP' THEN
        IF type_salle <> 'TP' THEN
            RAISE type_salle_diff_type_cours ;
        END IF;
    END IF;
EXCEPTION
    WHEN type_salle_diff_type_cours THEN
        RAISE_APPLICATION_ERROR(num => -20001, msg => 'La salle ne convient pas pour ce type d enseignement');
    WHEN OTHERS THEN NULL ;
END types_cours_salles ;

INSERT INTO AFFECTER VALUES ('05/06/2017','mardi','08:00','InS2F2','IN209');

DELETE FROM AFFECTER
WHERE DEBSEMC = '05/06/2017'
AND JOURC = 'mardi'
AND HEUREDC = '08:00'
AND GRPC = 'InS2F2'
AND SALC = 'IN209' ;

INSERT INTO AFFECTER VALUES ('05/06/17','mardi','08:00','InS2F2','InJade');

DELETE FROM AFFECTER
WHERE DEBSEMC = '05/06/17'
AND JOURC = 'mardi'
AND HEUREDC = '08:00'
AND GRPC = 'InS2F2'
AND SALC = 'InJade' ;

INSERT INTO AFFECTER VALUES ('05/06/17','mardi','08:00','InSxx','IN209');

-- 5.b

CREATE OR REPLACE TRIGGER types_cours_salles
BEFORE INSERT OR UPDATE -- + MàJ
ON AFFECTER FOR EACH ROW
DECLARE
    type_salle varchar2(10 BYTE);
    type_cours varchar2(10 BYTE);
    type_salle_diff_type_cours EXCEPTION ;
    PRAGMA EXCEPTION_INIT (type_salle_diff_type_cours, -00001);
BEGIN
    SELECT SALLE.TSAL INTO type_salle FROM SALLE WHERE SALLE.nsalle = :NEW.SALC ;
    SELECT CRENEAU.TYPEC INTO type_cours FROM CRENEAU
    WHERE CRENEAU.DEBSEMC = :NEW.DEBSEMC
    AND CRENEAU.JOURC = :NEW.JOURC
    AND CRENEAU.HEUREDC = :NEW.HEUREDC
    AND CRENEAU.GRPC = :NEW.GRPC ;
    IF type_cours = 'TP' THEN
        IF type_salle <> 'TP' THEN
            RAISE type_salle_diff_type_cours ;
        END IF;
    END IF;
EXCEPTION
    WHEN type_salle_diff_type_cours THEN
        RAISE_APPLICATION_ERROR(num => -20001, msg => 'La salle ne convient pas pour ce type d enseignement');
    WHEN OTHERS THEN NULL ;
END types_cours_salles ;

UPDATE AFFECTER
SET SALC = 'IN2019'
WHERE AFFECTER.DEBSEMC = '05/06/17'
AND AFFECTER.JOURC = 'vendredi'
AND AFFECTER.HEUREDC = '08:00'
AND AFFECTER.GRPC = 'InS2A1'
AND AFFECTER.SALC = 'InRubis';

commit ;

UPDATE AFFECTER
SET SALC = 'InJade'
WHERE AFFECTER.DEBSEMC = '05/06/17'
AND AFFECTER.JOURC = 'vendredi'
AND AFFECTER.HEUREDC = '08:00'
AND AFFECTER.GRPC = 'InS2A1'
AND AFFECTER.SALC = 'InRubis';

rollback ;

CREATE TRIGGER verif_effectif_capacite
BEFORE INSERT OR UPDATE
ON AFFECTER FOR EACH ROW
DECLARE
    effectif_groupe NUMBER ;
    capacite_salle NUMBER ;
    effectif_sup_capacite EXCEPTION;
    PRAGMA EXCEPTION_INIT (effectif_sup_capacite,-00002);
BEGIN
    SELECT GROUPE.EFF INTO effectif_groupe FROM GROUPE WHERE GROUPE.GRPC = :NEW.GRPC ;
    SELECT SALLE.CAPACITE INTO capacite_salle FROM SALLE WHERE SALLE.NSALL = :NEW.SALC ;
END verif_effectif_capacite ;