-- Suppression des relations si elles sont deja creees 
-- (enlever les tirets de commentaire)
-- L'ordre de suppression des relations doit etre respecte 
-- pour ne pas violer les contraintes d'integrite referentielles
--DROP VIEW Email_Etudiant;
--DROP TABLE Etudiant;
--DROP TABLE Reservation;
--DROP FUNCTION GetSalleCapaciteSuperieurA(int);
--DROP TABLE Salle;
--DROP TABLE Enseignement;
--DROP TABLE Enseignant;
--DROP TABLE Departement;

-- Script de creation des relations

CREATE TABLE Departement
(
 Departement_id     integer,
 Nom_Departement   varchar(25) NOT NULL,
 CONSTRAINT UN_Nom_Departement UNIQUE (nom_departement),
 CONSTRAINT PK_Departement PRIMARY KEY(Departement_ID)
);

CREATE TABLE Etudiant
(
 Etudiant_ID    integer,
 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,
 CONSTRAINT PK_Etudiant PRIMARY KEY (Etudiant_ID));

CREATE TABLE Enseignement
( 
  Enseignement_ID  int4 NOT NULL,
  Departement_ID   int4 NOT NULL,
  Intitule         varchar(60) NOT NULL,
  Description      varchar(1000),
  CONSTRAINT PK_Enseignement PRIMARY KEY (Enseignement_ID, Departement_ID),
  CONSTRAINT "PK_Enseignement_Departement" FOREIGN KEY (Departement_ID) REFERENCES Departement (Departement_ID) ON UPDATE RESTRICT ON DELETE RESTRICT
) ;

CREATE TABLE Enseignant
(
 Enseignant_ID     integer,
 Departement_ID    integer NOT NULL,
 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')),
 Telephone         varchar(10) DEFAULT NULL,
 Fax               varchar(10) DEFAULT NULL,
 Email             varchar(100) DEFAULT NULL,
 CONSTRAINT PK_Enseignant PRIMARY KEY (Enseignant_ID),
 CONSTRAINT "FK_Enseignant_Departement_ID" FOREIGN KEY (Departement_ID) REFERENCES Departement (Departement_ID) ON UPDATE RESTRICT ON DELETE RESTRICT
);


CREATE TABLE Salle
(
 Batiment     	varchar(1),
 Numero_Salle   varchar(10),
 Capacite  	integer CHECK (Capacite >1),
 CONSTRAINT PK_Salle PRIMARY KEY (Batiment, Numero_Salle)
);

CREATE TABLE Reservation
(
 Reservation_ID     integer,
 Batiment           varchar(1) NOT NULL,
 Numero_Salle       varchar(10) NOT NULL,
 Enseignement_ID    integer NOT NULL,
 Departement_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),
 CONSTRAINT "FK_Reservation_Salle" FOREIGN KEY (Batiment,Numero_Salle) REFERENCES Salle (Batiment,Numero_Salle) ON UPDATE RESTRICT ON DELETE RESTRICT,
 CONSTRAINT "FK_Reservation_Enseignement" FOREIGN KEY (Enseignement_ID,Departement_ID) REFERENCES Enseignement (Enseignement_ID,Departement_ID) ON UPDATE RESTRICT ON DELETE RESTRICT,
 CONSTRAINT "FK_Reservation_Enseignant" FOREIGN KEY (Enseignant_ID) REFERENCES Enseignant (Enseignant_ID) ON UPDATE RESTRICT ON DELETE RESTRICT,
 CONSTRAINT CK_Reservation_Nombre_Heures CHECK (Nombre_Heures >=1),
 CONSTRAINT CK_Reservation_HeureDebFin CHECK (Heure_Debut < Heure_Fin)
);

CREATE OR REPLACE VIEW Email_Etudiant
AS SELECT Nom, Prenom, Email FROM Etudiant;

INSERT INTO Departement VALUES ('1','MIDO');
INSERT INTO Departement VALUES ('2','LSO');
INSERT INTO Departement VALUES ('3','MSO');


INSERT INTO Etudiant VALUES ('1','GAMOTTE', 'Albert','1979/02/18','50, Rue des alouettes','PARIS','75021','0143567890',NULL,'gamotal4@etud.dauphine.fr');
INSERT INTO Etudiant VALUES ('2','HIBULAIRE', 'Pat','1980/08/23','10, Avenue des marguerites','POUILLON','40000','0678567801',NULL,'pat@yahoo.fr');
INSERT INTO Etudiant VALUES ('3','ODENT', 'Jamal','1978/05/12','25, Boulevard des fleurs','PARIS','75022','0145678956','0145678956','odent@free.fr');
INSERT INTO Etudiant VALUES ('4','DEBECE', 'Gill','1979/07/15','56, Boulevard des fleurs','PARIS','75022','0678905645',NULL,'deby@hotmail.com');
INSERT INTO Etudiant VALUES ('5','DEBECE', 'Aude','1979/08/15','45, Avenue des abeilles','PARIS','75022',NULL,NULL,NULL);

INSERT INTO Enseignant VALUES('1','1','MANOUVRIER','Maude','MCF','4185','4091','manouvrier@lamsade.dauphine.fr');
INSERT INTO Enseignant VALUES('2','1','JOMIER','Genevieve','PROF',NULL,NULL,'Genevieve.Jomier@dauphine.fr');


INSERT INTO Salle VALUES('B','020','15');
INSERT INTO Salle VALUES('B','022','15');
INSERT INTO Salle VALUES('A','301','45');
INSERT INTO Salle VALUES('C','Amphi 8','500');
INSERT INTO Salle VALUES('C','Amphi 4','200');


INSERT INTO Enseignement VALUES ('1','1','Bases de Donnees Relationnelles','Niveau Licence (L3) : Modelisation E/A et UML, Modele relationnel, Algebre Relationnelle, Calcul relationel, SQL, dependances fonctionnelles et formes normales');
INSERT INTO Enseignement VALUES ('2','1','Langage C++','Niveau Master 1');
INSERT INTO Enseignement VALUES ('3','1','Mise a Niveau Bases de Donnees','Niveau Master 2 - Programme Licence et Master 1 en Bases de Donnees');

INSERT INTO Reservation VALUES ('1','B','022','1','1','1','2008/10/15','08:30:00','11:45:00','3');
INSERT INTO Reservation VALUES ('2','B','022','1','1','2','2008/11/04','08:30:00','11:45:00','3');
INSERT INTO Reservation VALUES ('3','B','022','1','1','2','2008/11/07','08:30:00','11:45:00','3');
INSERT INTO Reservation VALUES ('4','B','020','1','1','1','2008/10/15','08:30:00','11:45:00','3');