DROP TABLE Occupation;
DROP TABLE Assistance;
DROP TABLE Cours;
DROP TABLE DecalageExterne;
DROP TABLE DecalageInterne;
DROP TABLE Repartition;
DROP TABLE Seance;
DROP TABLE Volume;
DROP TABLE Affectation;
DROP TABLE Composition;
DROP TABLE Etudiant;
DROP TABLE Groupe;
DROP TABLE ModuleEns;
DROP TABLE Specificite;
DROP TABLE Annee;
DROP TABLE Creneau;
DROP TABLE Enseignant;
DROP TABLE Salle;
DROP TABLE Semestre;
DROP TABLE TypeSeance;

CREATE TABLE Annee (
    idAnnee             VARCHAR2(8)
    , numAnnee          NUMBER(1)
    , niveau            VARCHAR2(8)     
        CHECK (niveau IN ('licence', 'master', 'doctorat'))
    , mention           VARCHAR2(32)
    , parcours          VARCHAR2(32)  
,   CONSTRAINT PK_Annee PRIMARY KEY (idAnnee)  
);

CREATE TABLE Creneau (
    idCreneau           VARCHAR2(8)
    , dateCreneau       DATE
    , heureDebut        DATE
    , JourSemaine       VARCHAR2(8)
    , CONSTRAINT PK_Creneau PRIMARY KEY (idCreneau)  
);

CREATE TABLE Enseignant (
    idEnseignant        VARCHAR2(8)
    , nomEnseignant     VARCHAR2(20)
    , prenomEnseignant  VARCHAR2(20)
    , eMailEnseignant   VARCHAR2(32)  
    , CONSTRAINT PK_Enseignant PRIMARY KEY (idEnseignant)  
);

CREATE TABLE Salle (
    nomSalle            VARCHAR2(16)
    , capacite          NUMBER(4)
    , localisation      VARCHAR2(32)  
    , CONSTRAINT PK_Salle PRIMARY KEY (nomSalle)  
);

CREATE TABLE Semestre (
    numSemestre         NUMBER(2)
    , CONSTRAINT PK_Semestre PRIMARY KEY (numSemestre)  
);

CREATE TABLE TypeSeance (
    typeSeance          VARCHAR2(8)   
        CHECK (typeSeance IN ('Cours', 'TD', 'TP', 'Examen'))
    , CONSTRAINT PK_TypeSeance PRIMARY KEY (typeSeance)  
);

CREATE TABLE Affectation (
    nomSalle            VARCHAR2(16)
    , idCreneau         VARCHAR2(8)
    , CONSTRAINT PK_Affectation PRIMARY KEY (nomSalle, idCreneau)
    , CONSTRAINT FK_Affectation_Salle
          FOREIGN KEY (nomSalle)
          REFERENCES Salle (nomSalle)
    , CONSTRAINT FK_Affectation_Creneau
          FOREIGN KEY (idCreneau)
          REFERENCES Creneau (idCreneau)
);

CREATE TABLE Composition (
    numSemestre         NUMBER(2) 
    , idAnnee           VARCHAR2(8)
    , CONSTRAINT PK_Composition PRIMARY KEY (numSemestre, idAnnee)
    , CONSTRAINT FK_Composition_Semestre
          FOREIGN KEY (numSemestre)
          REFERENCES Semestre (numSemestre)
    , CONSTRAINT FK_Composition_Annee
          FOREIGN KEY (idAnnee)
          REFERENCES Annee (idAnnee)
);

CREATE TABLE Etudiant (
    numEtudiant         NUMBER(8)
    , nomEtudiant       VARCHAR2(20)
    , prenomEtudiant    VARCHAR2(20)
    , idAnnee           VARCHAR2(8)  NOT NULL
    , CONSTRAINT PK_Etudiant PRIMARY KEY (numEtudiant)
    , CONSTRAINT FK_Etudiant_Annee
          FOREIGN KEY (idAnnee)
          REFERENCES Annee (idAnnee)
);

CREATE TABLE Groupe (
    typeSeance          VARCHAR2(8)
    , numGroupe         NUMBER(1)
    , CONSTRAINT PK_Groupe PRIMARY KEY (typeSeance, numGroupe) 
    , CONSTRAINT FK_Groupe_TypeSeance
          FOREIGN KEY (typeSeance)
          REFERENCES TypeSeance (typeSeance)
);

CREATE TABLE ModuleEns (
    idModule            VARCHAR2(8)
    , intituleModule    VARCHAR2(32)
    , numSemestre       NUMBER(2)  NOT NULL
    , CONSTRAINT PK_ModuleEns PRIMARY KEY (idModule)
    , CONSTRAINT FK_ModuleEns_Semestre
          FOREIGN KEY (numSemestre)
          REFERENCES Semestre (numSemestre)
);

CREATE TABLE Specificite (
    nomSalle            VARCHAR2(16)
    , typeSeance        VARCHAR2(8)
    , CONSTRAINT PK_Specificite PRIMARY KEY (nomSalle, typeSeance)
    , CONSTRAINT FK_Specificite_Salle
          FOREIGN KEY (nomSalle)
          REFERENCES Salle (nomSalle)
    , CONSTRAINT FK_Specificite_TypeSeance
          FOREIGN KEY (typeSeance)
          REFERENCES TypeSeance (typeSeance)
);

CREATE TABLE Assistance (
    idModule            VARCHAR2(8)  NOT NULL
    , idAnnee           VARCHAR2(8)  NOT NULL
    , CONSTRAINT PK_Assistance PRIMARY KEY (idModule, idAnnee)
    , CONSTRAINT FK_Assistance_ModuleEns
          FOREIGN KEY (idModule)
          REFERENCES ModuleEns (idModule)
    , CONSTRAINT FK_Assistance_Annee
          FOREIGN KEY (idAnnee)
          REFERENCES Annee (idAnnee)
);

CREATE TABLE Cours (
    idModule            VARCHAR2(8)
    , typeSeance        VARCHAR2(8)
    , numGroupe         NUMBER(1)
    , idEnseignant      VARCHAR2(8)  NOT NULL
    , CONSTRAINT PK_Cours 
        PRIMARY KEY (idModule, typeSeance, numGroupe)
    , CONSTRAINT FK_Cours_Enseignant
          FOREIGN KEY (idEnseignant)
          REFERENCES Enseignant (idEnseignant)
    , CONSTRAINT FK_Cours_ModuleEns
          FOREIGN KEY (idModule)
          REFERENCES ModuleEns (idModule)
    , CONSTRAINT FK_Cours_Groupe
          FOREIGN KEY (typeSeance, numGroupe)
          REFERENCES Groupe (typeSeance, numGroupe)
);

CREATE TABLE DecalageExterne (
    idModulePrec        VARCHAR2(8)
    , idModuleSucc      VARCHAR2(8)
    , typeDecalage      VARCHAR2(32)     
        CHECK (typeDecalage IN ('Avec chevauchement', 'Sans chevauchement'))
    , CONSTRAINT PK_DecalageExterne 
        PRIMARY KEY (idModulePrec, idModuleSucc)
    , CONSTRAINT FK_DecalageExterne_ModuleEns
          FOREIGN KEY (idModulePrec)
          REFERENCES ModuleEns (idModule)
    , CONSTRAINT FK_DecalageExterne_ModuleEns1
          FOREIGN KEY (idModuleSucc)
          REFERENCES ModuleEns (idModule)
);

CREATE TABLE DecalageInterne (
    idModule            VARCHAR2(8)  NOT NULL
    , typeSeanceApres   VARCHAR2(8)
    , typeSeanceAvant   VARCHAR2(8)
    , ValeurDecalage    NUMBER(2)  
    , CONSTRAINT PK_DecalageInterne 
        PRIMARY KEY (idModule, typeSeanceApres, typeSeanceAvant)
    , CONSTRAINT FK_DecalageInterne_ModuleEns
          FOREIGN KEY (idModule)
          REFERENCES ModuleEns (idModule)
    , CONSTRAINT FK_DecalageInterne_TypeSeance
          FOREIGN KEY (typeSeanceApres)
          REFERENCES TypeSeance (typeSeance)
    , CONSTRAINT FK_DecalageInterne_TypeSeance1
          FOREIGN KEY (typeSeanceAvant)
          REFERENCES TypeSeance (typeSeance)
);

CREATE TABLE Repartition (
    typeSeance          VARCHAR2(8)
    , numGroupe         NUMBER(1)
    , numEtudiant       NUMBER(8)
    , CONSTRAINT PK_Repartition 
        PRIMARY KEY (typeSeance, numGroupe, numEtudiant)
    , CONSTRAINT FK_Repartition_Groupe
          FOREIGN KEY (typeSeance, numGroupe)
          REFERENCES Groupe (typeSeance, numGroupe)
    , CONSTRAINT FK_Repartition_Etudiant
          FOREIGN KEY (numEtudiant)
               REFERENCES Etudiant (numEtudiant)
);

CREATE TABLE Seance (
    idModule            VARCHAR2(8)
    , typeSeance        VARCHAR2(8)
    , numGroupe         NUMBER(1)
    , numOrdre          NUMBER(2)
    , decalageDebut     NUMBER(2)
    , dureeEffective    NUMBER(2)
    , etatSeance        VARCHAR2(32)     
        CHECK (etatSeance IN ('A planifier', 'Planifiée', 'Annulée', 'Effectuée'))
    , nomSalle VARCHAR2(16)  
    , CONSTRAINT PK_Seance 
        PRIMARY KEY (typeSeance, idModule, numGroupe, numOrdre)
    , CONSTRAINT FK_Seance_Salle
          FOREIGN KEY (nomSalle)
          REFERENCES Salle (nomSalle)
    , CONSTRAINT FK_Seance_Groupe
          FOREIGN KEY (typeSeance, numGroupe)
          REFERENCES Groupe (typeSeance, numGroupe)
    , CONSTRAINT FK_Seance_ModuleEns
          FOREIGN KEY (idModule)
          REFERENCES ModuleEns (idModule)
);

CREATE TABLE Volume (
    idModule            VARCHAR2(8)
    , typeSeance        VARCHAR2(8)
    , volumeHoraire     NUMBER(4)  
    , CONSTRAINT PK_Volume PRIMARY KEY (idModule, typeSeance)
    , CONSTRAINT FK_Volume_ModuleEns
          FOREIGN KEY (idModule)
          REFERENCES ModuleEns (idModule)
    , CONSTRAINT FK_Volume_TypeSeance
          FOREIGN KEY (typeSeance)
          REFERENCES TypeSeance (typeSeance)
);

CREATE TABLE Occupation (
    idModule            VARCHAR2(8)
    , typeSeance        VARCHAR2(8)
    , numGroupe         NUMBER(1)
    , numOrdre          NUMBER(2)
    , idCreneau         VARCHAR2(8)
    , CONSTRAINT PK_Occupation 
        PRIMARY KEY (typeSeance, idModule, numGroupe, numOrdre, idCreneau)
    , CONSTRAINT FK_Occupation_Seance
          FOREIGN KEY (typeSeance, idModule, numGroupe, numOrdre)
          REFERENCES Seance (typeSeance, idModule, numGroupe, numOrdre)
    , CONSTRAINT FK_Occupation_Creneau
          FOREIGN KEY (idCreneau)
          REFERENCES Creneau (idCreneau)
);