------------------------------------------------------------------
-- Suppression des relations si elles sont déja créées 
-- (enlever les tirets de commentaires)
-- L'ordre de suppression des relations doit être respecté 
-- pour ne pas violer les contraintes d'intégrité référentielles
------------------------------------------------------------------

--DROP TABLE Stage;
--DROP TABLE Biblio;
--DROP TABLE Affectation;
--DROP TABLE Presence;
--DROP TABLE Seminaire;
--DROP TABLE Candidature;
--DROP TABLE Reservation;
--DROP TABLE Salle;
--DROP TABLE Note;
--DROP TABLE Preference;
--DROP TABLE Inscription;
--DROP TABLE Etudiant;
--DROP TABLE Enseignement;
--DROP TABLE Master;
--DROP TABLE Enseignant;
--DROP TABLE Departement;

------------------------------------------------------------------
-- Script de création des relations
------------------------------------------------------------------

CREATE TABLE Departement
(
 Departement_id     SERIAL,
 Nom_Departement   varchar(25) NOT NULL,
 CONSTRAINT UN_Nom_Departement UNIQUE (nom_departement), -- contrainte d'unicité sur le nom du Département
 CONSTRAINT PK_Departement PRIMARY KEY(Departement_ID) -- Définition de la clé primaire
);
-- Pour information l'utilisation de SERIAL entraîne la création d'une séquence
-- NOTICE:  CREATE TABLE will create implicit sequence "departement_departement_id_seq" for serial column "departement.departement_id"


CREATE TABLE Enseignant
(
 Enseignant_ID     SERIAL,
 Departement_ID    integer NOT NULL, -- departement de rattachement
 Nom        	   varchar(25) NOT NULL,
 Prenom            varchar(25) NOT NULL,
 Grade             varchar(25) 
 CONSTRAINT CK_Enseignant_Grade
 CHECK (Grade IN ('Vacataire', 'Moniteur','ATER', 'MCF', 'PROF')), -- contrainte de domaine
 Telephone         varchar(10) DEFAULT NULL,
 Fax               varchar(10) DEFAULT NULL,
 Email             varchar(100) DEFAULT NULL,
 CONSTRAINT PK_Enseignant PRIMARY KEY (Enseignant_ID), -- Définition de la clé primaire
 CONSTRAINT "FK_Enseignant_Departement_ID" FOREIGN KEY (Departement_ID) REFERENCES Departement (Departement_ID) ON UPDATE RESTRICT ON DELETE RESTRICT -- Définition d'une clé étrangère
 );
-- Pour information l'utilisation de SERIAL entraîne la création d'une séquence
-- NOTICE: CREATE TABLE will create implicit sequence "enseignant_enseignant_id_seq" for serial column "enseignant.enseignant_id"


CREATE TABLE Master
(
 Master_ID     SERIAL, -- clé artificielle
 Nom_Master   varchar(25) NOT NULL,
 Responsable_ID integer NOT NULL,
 Departement_ID    integer NOT NULL,
 CONSTRAINT UN_Nom_Master UNIQUE (Nom_Master), -- Contrainte d'unicité sur le nom du Master
 CONSTRAINT PK_Master PRIMARY KEY(Master_ID), -- Définition de la clé primaire
 CONSTRAINT "PK_Master_Responsable_ID" FOREIGN KEY (Responsable_ID) REFERENCES Enseignant (Enseignant_ID) ON UPDATE RESTRICT ON DELETE RESTRICT, -- Définition d'une clé étrangère
 CONSTRAINT "FK_Master_Departement_ID" FOREIGN KEY (Departement_ID) REFERENCES Departement (Departement_ID) ON UPDATE RESTRICT ON DELETE RESTRICT -- Définition d'une clé étrangère
 
);
-- Pour information l'utilisation de SERIAL entraîne la création d'une séquence
-- NOTICE:  CREATE TABLE will create implicit sequence "master_master_id_seq" for serial column "master.master_id"


CREATE TABLE Etudiant
(
 Etudiant_ID    SERIAL, -- clé artificielle
 Nom      	varchar(25) NOT NULL,
 Prenom   	Varchar(25) NOT NULL,
 Date_Naissance date NOT NULL,
 Adresse  	Varchar(50) DEFAULT NULL,
 Ville    	Varchar(25) DEFAULT NULL,
 Code_Postal  	Varchar(9) DEFAULT NULL,
 Telephone      Varchar(10) DEFAULT NULL,
 Fax            Varchar(10) DEFAULT NULL,
 Email          Varchar(100) DEFAULT NULL,
 Master_ID 	integer,  -- Master où est inscrit l'étudiant
 CONSTRAINT PK_Etudiant PRIMARY KEY (Etudiant_ID), -- Définition de la clé primaire
 CONSTRAINT "PK_Etudiant_Master_ID" FOREIGN KEY (Master_ID) REFERENCES Master (Master_ID) ON UPDATE RESTRICT ON DELETE RESTRICT -- Définition d'une clé étrangère

);
-- Pour information l'utilisation de SERIAL entraîne la création d'une séquence
-- NOTICE: CREATE TABLE will create implicit sequence "etudiant_etudiant_id_seq" for serial column "etudiant.etudiant_id"


CREATE TABLE Enseignement
( 
  Enseignement_ID  integer NOT NULL, 
  Master_ID   integer NOT NULL,
  Intitule         varchar(60) NOT NULL,
  Description      varchar(1000),
  Obligatoire      varchar(3) NOT NULL,
  Enseignant_ID      integer NOT NULL, -- enseignant responsable de l'enseigement
  CONSTRAINT PK_Enseignement PRIMARY KEY (Enseignement_ID, Master_ID), -- Définition de la clé primaire métier
  CONSTRAINT "PK_Enseignement_Master_ID" FOREIGN KEY (Master_ID) REFERENCES Master (Master_ID) ON UPDATE RESTRICT ON DELETE RESTRICT, -- Définition d'une clé étrangère
  CONSTRAINT "FK_Enseignement_Enseignant" FOREIGN KEY (Enseignant_ID) REFERENCES Enseignant (Enseignant_ID) ON UPDATE RESTRICT ON DELETE RESTRICT, -- Définition d'une clé étrangère
  CONSTRAINT CK_Enseignement_Obligatoire CHECK (Obligatoire IN ('OUI', 'NON')) -- contrainte de domaine
) ;
-- Pour information : un enseignement est identifié par un numéro et le numéro du master associé - clé métier!!!

CREATE TABLE Preference
(
 Preference_ID SERIAL, -- clé artificielle
 Etudiant_ID     integer,
 Enseignement_ID integer,
 Master_ID integer,
 Preference integer DEFAULT 0,
 CONSTRAINT PK_Preference PRIMARY KEY (Preference_ID), -- Définition de la clé primaire
 CONSTRAINT "FK_Preference_Etudiant" FOREIGN KEY (Etudiant_ID) REFERENCES Etudiant (Etudiant_ID) ON UPDATE RESTRICT ON DELETE RESTRICT, -- Définition d'une clé étrangère
 CONSTRAINT "FK_Preference_Enseignement" FOREIGN KEY (Enseignement_ID,Master_ID) REFERENCES Enseignement (Enseignement_ID,Master_ID) ON UPDATE RESTRICT ON DELETE RESTRICT, -- Définition d'une clé étrangère
 CONSTRAINT "UN_Preference" UNIQUE (Etudiant_ID,Enseignement_ID,Master_ID), -- pour vérifier que le triplet est unique
 CONSTRAINT CK_Preference_Preference CHECK (Preference IN (0,1,2,3,4)) -- contrainte de domaine
);
-- Pour information l'utilisation de SERIAL entraîne la création d'une séquence
-- NOTICE:  CREATE TABLE will create implicit sequence "preference_preference_id_seq" for serial column "preference.preference_id"
 
 
CREATE TABLE Inscription
(
 Inscription_ID SERIAL, -- clé artificielle
 Etudiant_ID     integer,
 Enseignement_ID integer,
 Master_ID integer,
 CONSTRAINT PK_Inscription PRIMARY KEY (Inscription_ID), -- Définition de la clé primaire
 CONSTRAINT "FK_Inscription_Etudiant" FOREIGN KEY (Etudiant_ID) REFERENCES Etudiant (Etudiant_ID) ON UPDATE RESTRICT ON DELETE RESTRICT, -- Définition d'une clé étrangère
 CONSTRAINT "FK_Inscription_Enseignement" FOREIGN KEY (Enseignement_ID,Master_ID) REFERENCES Enseignement (Enseignement_ID,Master_ID) ON UPDATE RESTRICT ON DELETE RESTRICT, -- Définition d'une clé étrangère
 CONSTRAINT "UN_Inscription" UNIQUE (Etudiant_ID,Enseignement_ID,Master_ID) -- pour vérifier que le triplet est unique
);
-- Pour information l'utilisation de SERIAL entraîne la création d'une séquence
-- NOTICE:  CREATE TABLE will create implicit sequence "inscription_inscription_id_seq" for serial column "inscription.inscription_id"
 
----------------------------------------------------------
-- Relation Note créée via le programme Java
----------------------------------------------------------
--CREATE TABLE Note
--(
-- Note_ID SERIAL,
-- Etudiant_ID     integer,
-- Enseignement_ID integer,
-- Master_ID integer,
-- Note real,
-- CONSTRAINT PK_Notes PRIMARY KEY (Note_ID),
-- CONSTRAINT "FK_Notes_Etudiant" FOREIGN KEY (Etudiant_ID) 
--  REFERENCES Etudiant (Etudiant_ID) 
--   ON UPDATE RESTRICT ON DELETE RESTRICT,
-- CONSTRAINT "FK_Notes_Enseignement" 
--  FOREIGN KEY (Enseignement_ID,Master_ID) 
--   REFERENCES Enseignement (Enseignement_ID,Master_ID) 
--    ON UPDATE RESTRICT ON DELETE RESTRICT
--);
-- Pour information l'utilisation de SERIAL entraîne la création d'une séquence
-- NOTICE:  CREATE TABLE will create implicit sequence "note_note_id_seq" for serial column "note.notes_id"

CREATE TABLE Salle
(
 Batiment     	varchar(1),
 Numero_Salle   varchar(10),
 Capacite  	integer CHECK (Capacite >1), -- contrainte de domaine
 CONSTRAINT PK_Salle PRIMARY KEY (Batiment, Numero_Salle) -- Définition de la clé primaire métier!
);
-- Pour information : une salle est identifié par le nom du Batiment (A, P, B, C, D) et par un numéro

CREATE TABLE Reservation
(
 Reservation_ID     SERIAL, -- clé artificielle
 Batiment           varchar(1) NOT NULL,
 Numero_Salle       varchar(10) NOT NULL,
 Enseignement_ID    integer NOT NULL,
 Master_ID     integer NOT NULL,
 Enseignant_ID      integer NOT NULL, 
 Date_Resa          date NOT NULL DEFAULT CURRENT_DATE,
 Heure_Debut	    time NOT NULL DEFAULT CURRENT_TIME,
 Heure_Fin	    time NOT NULL DEFAULT '23:00:00',
 Nombre_Heures	    integer NOT NULL,
 CONSTRAINT PK_Reservation PRIMARY KEY (Reservation_ID), -- Définition de la clé primaire
 CONSTRAINT "FK_Reservation_Salle" FOREIGN KEY (Batiment,Numero_Salle) REFERENCES Salle (Batiment,Numero_Salle) ON UPDATE RESTRICT ON DELETE RESTRICT, -- Définition d'une clé étrangère
 CONSTRAINT "FK_Reservation_Enseignement" FOREIGN KEY (Enseignement_ID,Master_ID) REFERENCES Enseignement (Enseignement_ID,Master_ID) ON UPDATE RESTRICT ON DELETE RESTRICT, -- Définition d'une clé étrangère
 CONSTRAINT "FK_Reservation_Enseignant" FOREIGN KEY (Enseignant_ID) REFERENCES Enseignant (Enseignant_ID) ON UPDATE RESTRICT ON DELETE RESTRICT, -- Définition d'une clé étrangère
 CONSTRAINT CK_Reservation_Nombre_Heures CHECK (Nombre_Heures >=1),
 CONSTRAINT CK_Reservation_HeureDebFin CHECK (Heure_Debut < Heure_Fin)
);
-- Pour information l'utilisation de SERIAL entraîne la création d'une séquence
-- NOTICE: CREATE TABLE will create implicit sequence "reservation_reservation_id_seq" for serial column "reservation.reservation_id"

CREATE TABLE Candidature
(
Etudiant_id integer NOT NULL,
Master_id integer NOT NULL,
Preference integer NOT NULL,
Confirmee boolean DEFAULT false,
CONSTRAINT pk_candidature PRIMARY KEY (etudiant_id, master_id), -- Définition de la clé primaire métier!
CONSTRAINT "FK_Candidature_Etudiant" FOREIGN KEY (etudiant_id) -- Définition d'une clé étrangère
REFERENCES etudiant (etudiant_id),
CONSTRAINT "FK_Candidature_Master" FOREIGN KEY (master_id) -- Définition d'une clé étrangère
REFERENCES master (master_id),
CONSTRAINT ck_candidature_preference CHECK (preference IN (1,2,3,4,0)), -- contrainte de domaine
CONSTRAINT un_candidature UNIQUE (etudiant_id,master_id) -- contrainte d’unicité
);

CREATE TABLE Seminaire
(
Seminaire_id SERIAL, -- clé artificielle,
Master_id integer NOT NULL,
Entreprise varchar(100) NOT NULL,
Confirmee boolean DEFAULT false,
CONSTRAINT pk_Seminaire PRIMARY KEY (seminaire_id), -- Définition de la clé primaire
CONSTRAINT "FK_Seminaire_Master" FOREIGN KEY (master_id) -- Définition d'une clé étrangère
  REFERENCES master (master_id)
);
--NOTICE:  CREATE TABLE créera des séquences implicites « seminaire_seminaire_id_seq » pour la colonne serial «seminaire_seminaire_id »

CREATE TABLE Presence
(
Seminaire_id integer NOT NULL,
Etudiant_id int NOT NULL,
CONSTRAINT pk_Presence PRIMARY KEY (seminaire_id, etudiant_id), -- Définition de la clé primaire métier!
CONSTRAINT "FK_Presence_Seminaire" FOREIGN KEY (seminaire_id) -- Définition d'une clé étrangère
  REFERENCES seminaire (seminaire_id),
CONSTRAINT "FK_Presence_Etudiant" FOREIGN KEY (etudiant_id) -- Définition d'une clé étrangère
  REFERENCES etudiant (etudiant_id)
);

CREATE TABLE Stage
(
Stage_id SERIAL, -- clé artificielle,
Etudiant_id int NOT NULL,
Descriptif varchar(100) NOT NULL,
Entreprise varchar(100) NOT NULL,
CONSTRAINT pk_Stage PRIMARY KEY (stage_id, etudiant_id), -- Définition de la clé primaire
CONSTRAINT "FK_Stage_Etudiant" FOREIGN KEY (etudiant_id) -- Définition d'une clé étrangère
  REFERENCES etudiant (etudiant_id)
);
--NOTICE:  CREATE TABLE créera des séquences implicites « stage_stage_id_seq » pour la colonne serial « stage.stage_id »

CREATE TABLE Affectation
(
Affectation_id SERIAL, -- clé artificielle,
batiment varchar(1) NOT NULL,
numero_salle varchar(100) NOT NULL,
master_id int NOT NULL,
CONSTRAINT pk_Affectation PRIMARY KEY (affectation_id), -- Définition de la clé primaire
CONSTRAINT "FK_Affectation_Salle" FOREIGN KEY (batiment, numero_salle) -- Définition d'une clé étrangère
  REFERENCES salle (batiment, numero_salle),
CONSTRAINT "FK_Affectation_Master" FOREIGN KEY (master_id) -- Définition d'une clé étrangère
  REFERENCES master (master_id)
);
--NOTICE:  CREATE TABLE créera des séquences implicites « affectation_affectation_id_seq » pour la colonne serial « affectation.affectation_id »

CREATE TABLE Biblio
(
Biblio_id SERIAL, -- clé artificielle,
Enseignement_ID    integer NOT NULL,
Master_ID     integer NOT NULL,
ISBN_Livre varchar(20) NOT NULL,
CONSTRAINT pk_Biblio PRIMARY KEY (biblio_id), -- Définition de la clé primaire
CONSTRAINT "FK_Biblio_Enseignement" FOREIGN KEY (Enseignement_ID,Master_ID) REFERENCES Enseignement (Enseignement_ID,Master_ID), -- Définition d'une clé étrangère
CONSTRAINT "FK_Biblio_Master" FOREIGN KEY (master_id) -- Définition d'une clé étrangère
  REFERENCES master (master_id)
);
