--------------------------------------------------------
--  Fichier créé - jeudi-mars-03-2011   
--------------------------------------------------------
  DROP TABLE "EM_AFFECTER" cascade constraints;
  DROP TABLE "EM_CHAMBRES" cascade constraints;
  DROP TABLE "EM_MEDECINS" cascade constraints;
  DROP TABLE "EM_OCCUPER" cascade constraints;
  DROP TABLE "EM_PATHOLOGIES" cascade constraints;
  DROP TABLE "EM_PATIENTS" cascade constraints;
  DROP TABLE "EM_SOIGNER" cascade constraints;
  
  DROP SEQUENCE s_Personne;
  DROP SEQUENCE s_Chambre;
  DROP SEQUENCE s_Pathologie;

  -- DROP TRIGGER Auto_Medecins;
  -- DROP TRIGGER Auto_Patients;
  -- DROP TRIGGER Auto_Chambres;
  -- DROP TRIGGER AutoPathologies;
  
--------------------------------------------------------
--  DDL for Sequences
--------------------------------------------------------
  CREATE SEQUENCE s_Personne;
  CREATE SEQUENCE s_Chambre;
  CREATE SEQUENCE s_Pathologie;


  
--------------------------------------------------------
--  DDL for Table EM_AFFECTER
--------------------------------------------------------

  CREATE TABLE "EM_AFFECTER" 
   (	"IDPATIENT" number(10), 
	"IDPATHOLOGIE" number(10)
   ) ;
--------------------------------------------------------
--  DDL for Table EM_CHAMBRES
--------------------------------------------------------

  CREATE TABLE "EM_CHAMBRES" 
   (	"IDCHAMBRE" number(10), 
	"DESCRIPTION" VARCHAR2(40), 
	"PRIX1" NUMBER, 
	"PRIX2" NUMBER
   ) ;
--------------------------------------------------------
--  DDL for Table EM_MEDECINS
--------------------------------------------------------

  CREATE TABLE "EM_MEDECINS" 
   (	"IDMEDECIN" number(10), 
	"NOM" VARCHAR2(30), 
	"PRENOM" VARCHAR2(30), 
	"SPECIALITE" VARCHAR2(50)
   ) ;
--------------------------------------------------------
--  DDL for Table EM_OCCUPER
--------------------------------------------------------

  CREATE TABLE "EM_OCCUPER" 
   (	"IDPATIENT" number(10), 
	"IDCHAMBRE" number(10), 
	"DATEENTREE" DATE, 
	"DATESORTIE" DATE
   ) ;
--------------------------------------------------------
--  DDL for Table EM_PATHOLOGIES
--------------------------------------------------------

  CREATE TABLE "EM_PATHOLOGIES" 
   (	"IDPATHOLOGIE" number(10), 
	"NOM" VARCHAR2(40), 
	"REFERENCE" VARCHAR2(20)
   ) ;
--------------------------------------------------------
--  DDL for Table EM_PATIENTS
--------------------------------------------------------

  CREATE TABLE "EM_PATIENTS" 
   (	"IDPATIENT" number(10), 
	"NOM" VARCHAR2(30), 
	"PRENOM" VARCHAR2(30), 
	"DATENAISSANCE" DATE, 
	"ADRESSE" VARCHAR2(80),
  "CONJOINT" VARCHAR2(30)
   ) ;
--------------------------------------------------------
--  DDL for Table EM_SOIGNER
--------------------------------------------------------

  CREATE TABLE "EM_SOIGNER" 
   (	"IDMEDECIN" number(10), 
	"IDPATIENT" number(10)
   ) ;

---------------------------------------------------
--   DATA FOR TABLE EM_MEDECINS
--   FILTER = none used
---------------------------------------------------
REM INSERTING into EM_MEDECINS

---------------------------------------------------
--   END DATA FOR TABLE EM_MEDECINS
---------------------------------------------------

---------------------------------------------------
--   DATA FOR TABLE EM_SOIGNER
--   FILTER = none used
---------------------------------------------------
REM INSERTING into EM_SOIGNER

---------------------------------------------------
--   END DATA FOR TABLE EM_SOIGNER
---------------------------------------------------

---------------------------------------------------
--   DATA FOR TABLE EM_OCCUPER
--   FILTER = none used
---------------------------------------------------
REM INSERTING into EM_OCCUPER

---------------------------------------------------
--   END DATA FOR TABLE EM_OCCUPER
---------------------------------------------------

---------------------------------------------------
--   DATA FOR TABLE EM_AFFECTER
--   FILTER = none used
---------------------------------------------------
REM INSERTING into EM_AFFECTER

---------------------------------------------------
--   END DATA FOR TABLE EM_AFFECTER
---------------------------------------------------

---------------------------------------------------
--   DATA FOR TABLE EM_PATHOLOGIES
--   FILTER = none used
---------------------------------------------------
REM INSERTING into EM_PATHOLOGIES

---------------------------------------------------
--   END DATA FOR TABLE EM_PATHOLOGIES
---------------------------------------------------

---------------------------------------------------
--   DATA FOR TABLE EM_PATIENTS
--   FILTER = none used
---------------------------------------------------
REM INSERTING into EM_PATIENTS

---------------------------------------------------
--   END DATA FOR TABLE EM_PATIENTS
---------------------------------------------------

---------------------------------------------------
--   DATA FOR TABLE EM_CHAMBRES
--   FILTER = none used
---------------------------------------------------
REM INSERTING into EM_CHAMBRES

---------------------------------------------------
--   END DATA FOR TABLE EM_CHAMBRES
---------------------------------------------------
--------------------------------------------------------
--  Constraints for Table EM_AFFECTER
--------------------------------------------------------

  ALTER TABLE "EM_AFFECTER" ADD CONSTRAINT "PK_AFFECTER" PRIMARY KEY ("IDPATIENT", "IDPATHOLOGIE") ENABLE;
 
  ALTER TABLE "EM_AFFECTER" MODIFY ("IDPATIENT" NOT NULL ENABLE);
 
  ALTER TABLE "EM_AFFECTER" MODIFY ("IDPATHOLOGIE" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table EM_CHAMBRES
--------------------------------------------------------

  ALTER TABLE "EM_CHAMBRES" ADD CONSTRAINT "PK_CHAMBRES" PRIMARY KEY ("IDCHAMBRE") ENABLE;
 
  ALTER TABLE "EM_CHAMBRES" MODIFY ("IDCHAMBRE" NOT NULL ENABLE);
 
  ALTER TABLE "EM_CHAMBRES" MODIFY ("DESCRIPTION" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table EM_MEDECINS
--------------------------------------------------------

  ALTER TABLE "EM_MEDECINS" ADD CONSTRAINT "PK_MEDECINS" PRIMARY KEY ("IDMEDECIN") ENABLE;
 
  ALTER TABLE "EM_MEDECINS" MODIFY ("IDMEDECIN" NOT NULL ENABLE);
 
  ALTER TABLE "EM_MEDECINS" MODIFY ("NOM" NOT NULL ENABLE);
 
  ALTER TABLE "EM_MEDECINS" MODIFY ("PRENOM" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table EM_OCCUPER
--------------------------------------------------------

  ALTER TABLE "EM_OCCUPER" ADD CONSTRAINT "PK_OCCUPER" PRIMARY KEY ("IDPATIENT", "IDCHAMBRE", "DATEENTREE") ENABLE;
 
  ALTER TABLE "EM_OCCUPER" MODIFY ("IDPATIENT" NOT NULL ENABLE);
 
  ALTER TABLE "EM_OCCUPER" MODIFY ("IDCHAMBRE" NOT NULL ENABLE);
 
  ALTER TABLE "EM_OCCUPER" MODIFY ("DATEENTREE" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table EM_PATHOLOGIES
--------------------------------------------------------

  ALTER TABLE "EM_PATHOLOGIES" ADD CONSTRAINT "PK_PATHOLOGIES" PRIMARY KEY ("IDPATHOLOGIE") ENABLE;
 
  ALTER TABLE "EM_PATHOLOGIES" MODIFY ("IDPATHOLOGIE" NOT NULL ENABLE);
 
  ALTER TABLE "EM_PATHOLOGIES" MODIFY ("NOM" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table EM_PATIENTS
--------------------------------------------------------

  ALTER TABLE "EM_PATIENTS" ADD CONSTRAINT "PK_PATIENTS" PRIMARY KEY ("IDPATIENT") ENABLE;
 
  ALTER TABLE "EM_PATIENTS" MODIFY ("IDPATIENT" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table EM_SOIGNER
--------------------------------------------------------

  ALTER TABLE "EM_SOIGNER" ADD CONSTRAINT "PK_SOIGNER" PRIMARY KEY ("IDMEDECIN", "IDPATIENT") ENABLE;
 
  ALTER TABLE "EM_SOIGNER" MODIFY ("IDMEDECIN" NOT NULL ENABLE);
 
  ALTER TABLE "EM_SOIGNER" MODIFY ("IDPATIENT" NOT NULL ENABLE);


BEGIN
EXECUTE IMMEDIATE 
'CREATE OR REPLACE
 TRIGGER AUTO_MEDECINS 
 BEFORE INSERT ON em_MEDECINS 
 FOR EACH ROW 
 begin
      if inserting then
        if :NEW."IDMEDECIN" is null then
           select S_PERSONNE.nextval into :NEW."IDMEDECIN" from dual;
        end if;
    end if;
end;';

EXECUTE IMMEDIATE
'CREATE OR REPLACE
TRIGGER AUTO_PATIENTS  
BEFORE INSERT ON em_PATIENTS  
FOR EACH ROW  
begin 
    if inserting then 
       if :NEW."IDPATIENT" is null then 
          select S_PERSONNE.nextval into :NEW."IDPATIENT" from dual; 
       end if; 
    end if; 
end;';

EXECUTE IMMEDIATE
'CREATE OR REPLACE
TRIGGER AUTO_PATHOLOGIES  
BEFORE INSERT ON em_PATHOLOGIES   
FOR EACH ROW  
begin 
    if inserting then 
       if :NEW."IDPATHOLOGIE" is null then 
          select S_PATHOLOGIE.nextval into :NEW."IDPATHOLOGIE" from dual; 
       end if; 
    end if;  
end;';


EXECUTE IMMEDIATE
'CREATE OR REPLACE
TRIGGER AUTO_CHAMBRES 
BEFORE INSERT ON em_CHAMBRES 
FOR EACH ROW 
begin
     if inserting then
       if :NEW."IDCHAMBRE" is null then
          select S_CHAMBRE.nextval into :NEW."IDCHAMBRE" from dual;
       end if;
     end if;
end;';
end;




