DROP TABLE HoraireHebdo;
DROP TABLE Employee;
DROP TABLE Diplome;
DROP TABLE ListeDiplome;
DROP TABLE Formation;
DROP TABLE Poste;
DROP TABLE Etablissement;
DROP TABLE Personne;
DROP TABLE Adresse;
DROP TABLE Ville;

--------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE Ville                   --coordonnée de toutes les viles selon leurs code postal
( n_ville integer PRIMARY KEY,       --code postal de la ville
  nom_ville varchar2(20) NOT NULL,   --nom de la ville
  nom_pays varchar2(20) NOT NULL,    --nom du pays ou ce trouve cette ville
  CONSTRAINT Ville_unique UNIQUE (nom_ville,nom_pays)
);

--------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE Adresse
( n_adre integer PRIMARY KEY, --reference adresse
  n_rue integer,             --numero de la rue
  rue varchar2(50),          --nom de la rue
  n_villeA integer,          --reference ville (code postal)
  CONSTRAINT adresse_ville FOREIGN KEY (n_villea) REFERENCES Ville (n_ville)
);

--------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE Personne           --Contient les informations sur une personne
( n_pers integer PRIMARY KEY,   --identifiant numero de personne
  Nom varchar2(20) NOT NULL,    --Nom de moin de 20 caractères
  Prenom varchar2(20) NOT NULL, --Prenom
  n_secu integer,               --numero de secu
  sexe varchar2(2) NOT NULL CONSTRAINT choix_sexe CHECK(sexe IN ('M','F')),
                                --choix du sexe de la personne
  jj integer,                   --jour de naissance
  mm integer,                   --mois de naissance
  aa integer,                   --annee de naissance
  lieu_naissance integer,       --code postal du lieu de naissance
  CONSTRAINT ville_origine FOREIGN KEY (lieu_naissance) REFERENCES Ville (n_ville),
  n_adrP integer,                --numero d'enregistrement dans la liste des adresses
  CONSTRAINT adresse_perso FOREIGN KEY (n_adrp) REFERENCES Adresse (n_adre),
  Nationnalite varchar2(20) NOT NULL CONSTRAINT choix_nationnalite CHECK(nationnalite IN('Francaise','Autre mais europe','Reste du monde')),
  phone integer
);

--------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE Etablissement     --contient les coordonnées d'un etablissement
( n_Entr integer PRIMARY KEY,  --numero de l'etablissement
  nom_entr varchar2(80),       --nom de l'entreprise
  n_adresEnt integer,          --reference de l'adresse
  CONSTRAINT adresse_entreprise FOREIGN KEY (n_adresent) REFERENCES Adresse (n_adre),
  phone integer DEFAULT 0,     --numero de telephone de l'etablissement
  fax integer DEFAULT 0        --numero de fax.....
);

--------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE Poste                      --Conserve le suivie de la carriere de tous les employes
( n_poste integer PRIMARY KEY,          --Donner le numero identifiant du poste
  n_PersP integer,                      --Donner le numero identifiant de la personne
  CONSTRAINT personne_poste FOREIGN KEY (n_persp) REFERENCES Personne,
  Titre varchar2(20),
  DateDeb varchar2(10) NOT NULL,        --Date d'entree dans le nouveau poste. Doit etre fournis
  DateFin varchar2(10) DEFAULT NULL,    --Date de depart. Peut etre NULL si la personne est toujour au meme poste
  n_EntrP integer,                      --numero identifiant de l'entreprise
  CONSTRAINT poste_entreprise FOREIGN KEY (n_entrp) REFERENCES Etablissement (n_entr)
);

--------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE Formation          --formation suivie dans ou en dehors de l'entreprise
( n_form integer PRIMARY KEY,   --numero de formation
  n_persF integer,              --numero de personne
  CONSTRAINT personne_formation FOREIGN KEY (n_persf) REFERENCES Personne (n_pers),
  Duree integer,                --duree du stage (l'unité est à preciser
  typeF varchar2(20) NOT NULL CONSTRAINT choix_formation CHECK(typeF IN('Stage','Formation','Autre')),
                                --type de formation (au choix)
  n_EntrF integer,              --numero entreprise
  CONSTRAINT formation_entreprise FOREIGN KEY (n_entrf) REFERENCES Etablissement (n_entr)
);

--------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE ListeDiplome           --liste des diplomes les plus frequents
( n_dipl integer PRIMARY KEY,       --identifiant de la base, le numero du type de diplome
  niveau varchar2(30) NOT NULL CONSTRAINT choix_niveau CHECK(niveau IN('CAP','BAC','DEUG','BTS/IUT','LICENCE','MAITRISE','MASTER','INGENIEUR','DOCTORAT')),
                                    --choix du niveau d'étude
  specialite varchar2(20) NOT NULL, --choix de la specialité suivie
  mention varchar2(20) NOT NULL CONSTRAINT choix_mention CHECK(mention IN('Passable','Bien','Assez bien','Tres bien','Exellent')),
                                    --mention obtenue pour ce diplome
  CONSTRAINT diplomes_unique UNIQUE (niveau,specialite,mention)
);

--------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE Diplome
( n_persD integer,        --numero de personne
  CONSTRAINT personne_diplome FOREIGN KEY (n_persd) REFERENCES Personne (n_pers),
  promo integer,          --annee d'obtention du diplome
  type_diplome integer,   --le diplome avec niveau
  CONSTRAINT diplome_obtenue FOREIGN KEY (type_diplome) REFERENCES ListeDiplome (n_dipl),
  n_etabD integer,        --coordonné de l'etablissement ou a ete obtenue le diplome
  CONSTRAINT etablissement_diplome FOREIGN KEY (n_etabd) REFERENCES Etablissement (n_entr),
  CONSTRAINT diplome_unique UNIQUE (n_persd,promo)
);

--------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE Employee         --fiche employee
( n_Emp integer PRIMARY KEY,  --identifiant numero employer
  n_persE integer,            --numero de personne
  CONSTRAINT personne_employee FOREIGN KEY (n_perse) REFERENCES Personne (n_pers),
  n_PosteE integer,           --numero poste en cour
  CONSTRAINT poste_tenu FOREIGN KEY (n_postee) REFERENCES Poste (n_Poste),
  Salaire integer DEFAULT 0,  --salaire de l'employee à l'heure
  Gain integer DEFAULT 0      --salaire de l'employee depuis son arrivé
);

--------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE HoraireHebdo
( n_week integer,              --numero de la semaine pour le salaire
  n_persH integer,             --numero de personne
  CONSTRAINT salaire_hebdo FOREIGN KEY (n_persh) REFERENCES Personne (n_pers),
  Heures integer DEFAULT 0,      --d'heures pour la semaine pour la personne
  CONSTRAINT Horaire_personne UNIQUE (n_week,n_persh)
);