CREATE TABLE DÉPARTEMENT(
NUM_D VARCHAR2(4) NOT NULL,
NOM VARCHAR2(20) NOT NULL,
MATRICULE_DIRECTEUR VARCHAR2(4) NOT NULL,
DATE_DÉBUT DATE NOT NULL,
LOCAL FTYPE3(1) DEFAULT UNKNOWN NOT NULL,
PRIMARY KEY (NUM_D)) ;


CREATE TABLE POSTE(
CODE_POSTE VARCHAR2(4) NOT NULL,
PROFESSION FTYPE3(1) NOT NULL,
ÉTUDES FTYPE3(1) NOT NULL,
EXPÉRIENCE FTYPE2(2,5) NUMBER(2) NOT NULL,
PRIMARY KEY (CODE_POSTE)) ;


CREATE TABLE EMPLOYÉ(
MATRICULE VARCHAR2(4) NOT NULL,
NUM_D VARCHAR2(4) NOT NULL,
CODE_POSTE VARCHAR2(4) NOT NULL,
NOM VARCHAR2(20) NOT NULL,
PRENOM VARCHAR2(20) NOT NULL,
SEXE VARCHAR2(1) NOT NULL,
ADRESSE VARCHAR2(40) NOT NULL,
ÂGE FTYPE2(5,10) NUMBER(3) DEFAULT UNKNOWN NOT NULL,
SALAIRE FTYPE1(10,50) NUMBER(7) NOT NULL,
ÉTUDES FTYPE3(1) DOMAIN POSTE.ÉTUDES NOT NULL,
RENDEMENT FTYPE3(1) NOT NULL,
EXPÉRIENCE FTYPE2(2,5) DEFAULT UNKNOWN NUMBER(2) NOT NULL,
MATRICULE_SUPERVISEUR VARCHAR2 (4) NOT NULL,
PRIMARY KEY (MATRICULE, NUM_D, CODE_POSTE),
FOREIGN KEY (NUM_D) REFERENCES DÉPARTEMENT ON DELETE CASCADE,
FOREIGN KEY (CODE_POSTE) REFERENCES POSTE ON DELETE CASCADE) ;


CREATE TABLE PROJET(
NUM_P VARCHAR2(4)NOT NULL,
NUM_D VARCHAR2(4) NOT NULL,
NOM VARCHAR2(15) NOT NULL,
BUDGET FTYPE1(200,1000) NUMBER(7) NOT NULL,
PRIMARY KEY (NUM_P,NUM_D)
FOREIGN KEY (NUM_D) REFERENCES DEPERTEMENT ON DELETE CASCADE) ;


CREATE TABLE DÉPENDENT(
MATRICULE VARCHAR2(4) NOT NULL,
NOM VARCHAR2(15) NOT NULL,
DATE_NAISSANCE DATE NOT NULL,
LIEN_PARENTE VARCHAR2(20) NOT NULL,
PRIMARY KEY (MATRICULE,NOM),
FOREIGN KEY (MATRICULE) REFERENCES EMPLOYÉ ON DELETE CASCADE) ;


CREATE TABLE TRAVAILLE_DANS(
MATRICULE VARCHAR2(4) NOT NULL,
NUM_P VARCHAR2(4) NOT NULL,
NBRE_HEURE FTYPE1(2,4) NUMBER(4) NOT NULL,
PRIMARY KEY (MATRICULE, NUM_P),
FOREIGN KEY (MATRICULE) REFERENCES EMPLOYÉ ON DELETE CASCADE,
FOREIGN KEY (NUM_P) REFERENCES PROJET ON DELETE CASCADE) ;