
DROP TABLE PARENT;
DROP TABLE ADRESSE;
DROP TABLE COORDONNEES;
DROP TABLE INDIVIDU;
CREATE TABLE INDIVIDU(Idind number, Nom VARCHAR2(100), Prenom VARCHAR2(100), 
SEXE CHAR(1), DDN date, Lieu VARCHAR2(100),DDM date);
CREATE TABLE COORDONNEES(Idcoord number, Idind number, Idadresse number,
Debut date, Fin date);
CREATE TABLE ADRESSE(Idadresse number, Designation VARCHAR2(200), 
CP number, Ville VARCHAR2(100));
CREATE TABLE PARENT(Idparent number, Idenfant number);

ALTER TABLE INDIVIDU ADD CONSTRAINT pk_Idind PRIMARY KEY (Idind);
ALTER TABLE COORDONNEES ADD CONSTRAINT pk_Coord PRIMARY KEY (Idind);
ALTER TABLE PARENT ADD CONSTRAINT pk_parents PRIMARY KEY (Idparent,Idenfant);
ALTER TABLE ADRESSE ADD CONSTRAINT pk_adresse PRIMARY KEY (Idadresse);

ALTER TABLE PARENT ADD (CONSTRAINT ENFANT_FK FOREIGN KEY (IDENFANT)
REFERENCES INDIVIDU (IDIND));
ALTER TABLE PARENT ADD (CONSTRAINT PARENT_FK FOREIGN KEY (IDPARENT)
REFERENCES INDIVIDU (IDIND));
ALTER TABLE COORDONNEES ADD (FOREIGN KEY (IDIND) REFERENCES INDIVIDU (IDIND));

ALTER TABLE INDIVIDU  MODIFY (DDN date NOT NULL);
ALTER TABLE COORDONNEES  ADD CONSTRAINT check_date CHECK (debut<fin);
ALTER TABLE INDIVIDU MODIFY sexe CHAR(1) CHECK (sexe IN('H','F'));
