lelel

CREATE TABLE PERSONNE (
  nopers number(6) NOT NULL,
  prenom varchar2(20) ,
  nom2fam varchar2(25) NOT NULL,
  PRIMARY KEY(nopers)
);

CREATE TABLE SERVICE (
  noservice number(4) NOT NULL,
  nomservice varchar2(30) NOT NULL,
  noloc number(6) NOT NULL,
  nochef number(6),
  PRIMARY KEY(noservice)
);

CREATE TABLE LOCALISATION (
  noloc number(6) NOT NULL,
  rue varchar2(40) NOT NULL,
  ville varchar2(30) DEFAULT 'Paris',
  PRIMARY KEY(noloc)
);

CREATE TABLE POSTE (
  noposte varchar2(10) NOT NULL,
  nomposte varchar2(30) NOT NULL,
  PRIMARY KEY(noposte)
);

CREATE TABLE SALAIRE(
  noposte varchar2(10) NOT NULL REFERENCES POSTE(noposte),
  noservice number(4,0) NOT NULL,
  salairemin number(6),
  salairemax number(6),
  PRIMARY KEY(noposte,noservice),
  FOREIGN KEY (noservice) REFERENCES SERVICE(noservice)
);

CREATE TABLE AFFECTATION (
  nopers number(6) NOT NULL REFERENCES PERSONNE(nopers),
  noposte varchar2(10) NOT NULL REFERENCES POSTE(noposte),
  noservice number(4) NOT NULL REFERENCES SERVICE(noservice),
  debservice date NOT NULL,
  finservice date,
  salaire number(8),
  CONSTRAINT datefindeb CHECK (debservice<finservice),
  UNIQUE(nopers, noposte, noservice)
);