creation (3).sql

DROP TABLE SERVICES CASCADE CONSTRAINT;
DROP TABLE PATHOLOGIES CASCADE CONSTRAINT;
DROP TABLE PATIENTS CASCADE CONSTRAINT;
DROP TABLE SALLES CASCADE CONSTRAINT;
DROP TABLE SOINS CASCADE CONSTRAINT;
DROP TABLE MEDECIN CASCADE CONSTRAINT;
DROP TABLE AFFECTATION CASCADE CONSTRAINT;
DROP TABLE AFFECTATION_inf CASCADE CONSTRAINT;
DROP TABLE Planning_SOINS_SALLE CASCADE CONSTRAINT;
DROP TABLE INFIRMIER CASCADE CONSTRAINT;
DROP TABLE TELEPHONE_MEDECIN CASCADE CONSTRAINT;
DROP TABLE TELEPHONE_INFIRMIER CASCADE CONSTRAINT;
DROP TABLE TELEPHONE_PATIENT CASCADE CONSTRAINT;


CREATE TABLE SERVICES (

    NOM_SERV VARCHAR2(50),
    Code_SERV numeric(3),
    telephone VARCHAR2(20),
    CONSTRAINT cle_prim_SERV PRIMARY KEY (NOM_SERV),
    CONSTRAINT NOM_SERVICE CHECK (NOM_SERV IS NOT NULL),
    CONSTRAINT CODE_SERVICE CHECK (Code_SERV IS NOT NULL),
    CONSTRAINT SERVICES_telephone CHECK (telephone IS NOT NULL)
);

CREATE TABLE PATHOLOGIES(
    Code numeric(4),
    NOM VARCHAR2(30),
    NOM_SERV VARCHAR2(50),
    CONSTRAINT cle_prim_path PRIMARY KEY(NOM),
    CONSTRAINT PATHOLOGIES_code CHECK (Code IS NOT NULL),
    CONSTRAINT PATHOLOGIES_NOM CHECK (NOM IS NOT NULL),
    CONSTRAINT fk_PATHOLOGIE_SERVICES FOREIGN KEY (NOM_SERV) REFERENCES SERVICES(NOM_SERV) 
);

CREATE TABLE MEDECIN (
    NOM VARCHAR2(20) CHECK (NOM IS NOT NULL),
    Prenom VARCHAR2(20) CHECK (Prenom IS NOT NULL),
    INSEE numeric(8),
    metier VARCHAR2(9) CHECK (metier in('Medecin')),
    adresse VARCHAR2(100) CHECK (adresse IS NOT NULL),
    NOM_DIRECTEUR VARCHAR2(14) CHECK (NOM_DIRECTEUR IS NOT NULL),
    INSEE_chef numeric(8),
    constraint Ck_i CHECK (NOM_DIRECTEUR in ('Gerald BARRIET')),
    CONSTRAINT cle_prim_pers23 PRIMARY KEY (INSEE),
    CONSTRAINT cle_chef FOREIGN KEY (INSEE_chef) REFERENCES MEDECIN(INSEE)
);

CREATE TABLE INFIRMIER (
    NOM_INF VARCHAR2(20) CHECK (NOM_INF IS NOT NULL),
    Prenom_inf VARCHAR2(20) CHECK (Prenom_inf IS NOT NULL),
    INSEE numeric(8),
    metier VARCHAR2(9) CHECK (metier in('Infirmier')),
    adresse VARCHAR2(100) CHECK (adresse IS NOT NULL),
    NOM_DIRECTEUR VARCHAR2(14),
    INSEE_chef numeric(8),
    constraint Ck_inf_NOM_DIRECTEUR CHECK (NOM_DIRECTEUR in ('Gerald BARRIET')),
    CONSTRAINT cle_PRIM_INF PRIMARY KEY (INSEE),
    CONSTRAINT cle_etrangere_chef FOREIGN KEY (INSEE_chef) REFERENCES MEDECIN(INSEE)
);


CREATE TABLE AFFECTATION (
    NOM_SERV VARCHAR2(50),
    INSEE numeric(8),
    CONSTRAINT cle_prim_aff PRIMARY KEY (INSEE,NOM_SERV),
    CONSTRAINT clé_aff_pers FOREIGN KEY (INSEE) REFERENCES MEDECIN(INSEE),
    CONSTRAINT NNDD FOREIGN KEY (NOM_SERV) REFERENCES SERVICES(NOM_SERV)
    );

CREATE TABLE AFFECTATION_inf (
    NOM_SERV VARCHAR2(50),
    INSEE numeric(8),
    CONSTRAINT cle_prim_aff_inf PRIMARY KEY (INSEE,NOM_SERV),
    CONSTRAINT cle_aff_inf FOREIGN KEY (INSEE) REFERENCES INFIRMIER(INSEE),
    CONSTRAINT NHNHN FOREIGN KEY (NOM_SERV) REFERENCES SERVICES (NOM_SERV)
    );


CREATE TABLE PATIENTS(

    Nom VARCHAR2(20),
    Prenom VARCHAR(20),
    INSEE numeric(8),
    adress VARCHAR2(100),
    NOM_de_la_pathologie VARCHAR2(30),
    Date_Entree DATE CHECK (Date_Entree IS NOT NULL),
    Date_Sortie DATE,
    metier VARCHAR(9),
    CONSTRAINT cle_prim PRIMARY KEY (INSEE),
    CONSTRAINT contrainte_sur_les_dates CHECK (Date_Entree <= Date_sortie),
    CONSTRAINT ck_contrainte_NOM CHECK (Nom IS NOT NULL),
    CONSTRAINT ck_contrainte_Prenom CHECK (Prenom IS NOT NULL),
    CONSTRAINT ck_contrainte_INSEE CHECK (INSEE IS NOT NULL),
    CONSTRAINT ck_PATIENTS_PATHOLOGIE FOREIGN KEY (NOM_de_la_pathologie) REFERENCES PATHOLOGIES(NOM)
);


CREATE TABLE SOINS (
    Nom_SOINS VARCHAR2(20),
    pathologie VARCHAR2(30),
    CONSTRAINT cle_prim_soins PRIMARY KEY (Nom_SOINS,pathologie),
    CONSTRAINT fk_SOINS_PATHOLOGIES FOREIGN KEY (pathologie) REFERENCES PATHOLOGIES(NOM)
);

CREATE TABLE SALLES (
    Num_S NUMBER,
    Vol NUMBER,
    Taux_occ NUMBER(3),
    NOM_SERV VARCHAR2(50) NOT NULL,
    Nom_SOINS VARCHAR2(50) NOT NULL,
    pathologie VARCHAR2(30),
    CONSTRAINT cle_prism_S PRIMARY KEY (Num_S,NOM_SERV),
    CONSTRAINT Fk_SALLE_SERVICES FOREIGN KEY (NOM_SERV) REFERENCES SERVICES (NOM_SERV),
    CONSTRAINT FK_SALLE_PATHOLOGIE FOREIGN KEY (Nom_SOINS,pathologie) REFERENCES SOINS(Nom_SOINS,pathologie)
);



CREATE TABLE Planning_SOINS_SALLE(

    Nom_SOINS VARCHAR2(20) CHECK (NOM_SOINS IS NOT NULL),
    pathologie VARCHAR2(30) CHECK (pathologie IS NOT NULL),
    INSEE numeric(8),
    Date_soins DATE CHECK (Date_soins IS NOT NULL),
    INSEE_med numeric(8),
    Num_S NUMBER,
    NOM_SERV VARCHAR2(50),
    resultat VARCHAR2(1),
    COÛT NUMBER,
    CONSTRAINT djff PRIMARY KEY (INSEE),
    CONSTRAINT NDJFDF FOREIGN KEY (INSEE) REFERENCES PATIENTS(INSEE),
    CONSTRAINT fk_AFFECTATION_MEDECIN FOREIGN KEY (INSEE_med,NOM_SERV) REFERENCES AFFECTATION(INSEE,NOM_SERV),
    CONSTRAINT fk_AFFECTATION_SALLE FOREIGN KEY (Num_S,NOM_SERV) REFERENCES SALLES(Num_S,NOM_SERV),
    CONSTRAINT fk_AFFECTATION_PATHOLOGIE FOREIGN KEY (Nom_SOINS,pathologie) REFERENCES SOINS(Nom_SOINS,pathologie)
);


CREATE TABLE TELEPHONE_MEDECIN (
    telephone VARCHAR2(14),
    type VARCHAR2(10) CHECK (type in ('port','fixe')),
    metier VARCHAR2(10),
    INSEE numeric(8),
    CONSTRAINT cle_prim_tel PRIMARY KEY (telephone),
    constraint CK_mp check (metier in ('Medecin')),
    CONSTRAINT TELEPHONE_MED_INF_MEDECIN FOREIGN KEY (INSEE) REFERENCES MEDECIN(INSEE)

);
CREATE TABLE TELEPHONE_INFIRMIER(
    telephone VARCHAR2(14),
    type VARCHAR2(4) CHECK (type in ('port','fixe')),
    metier VARCHAR2(10),
    INSEE numeric(8),
    CONSTRAINT cle_prim_tel_inf PRIMARY KEY (telephone),
    CONSTRAINT ck_metier_infirmier CHECK (metier in('Infirmier')),
    CONSTRAINT TELEPHONE_MED_INF FOREIGN KEY (INSEE) REFERENCES INFIRMIER(INSEE)
);
CREATE TABLE TELEPHONE_PATIENT (
    telephone VARCHAR2(14),
    type VARCHAR2(4) CHECK (type in ('port','fixe')),
    INSEE numeric(8),
    CONSTRAINT cle_prim_tel_patient PRIMARY KEY (telephone),
    CONSTRAINT TELEPHONE_PATIENT FOREIGN KEY (INSEE) REFERENCES PATIENTS(INSEE)
);