DROP TABLE Personne CASCADE CONSTRAINTS;
DROP TABLE MereDe CASCADE CONSTRAINTS;
DROP TABLE PereDe CASCADE CONSTRAINTS;

-- -----------------------------------------------------------------------------
--       TABLE : Personne
-- -----------------------------------------------------------------------------
CREATE TABLE Personne
  ( numPers     NUMBER(4)    	NOT NULL
  , nomPers     VARCHAR2(20)
  , prenomPers  VARCHAR2(20)
  , sexePers    VARCHAR2(1)	CHECK (sexePers IN ('F','M'))
,    CONSTRAINT PK_Personne PRIMARY KEY (numPers)  
  );

-- -----------------------------------------------------------------------------
--       TABLE : MereDe
-- -----------------------------------------------------------------------------
CREATE TABLE MereDe
  ( numPersEnfant  NUMBER(4)  NOT NULL
  , numPersMere    NUMBER(4)
,    CONSTRAINT PK_MereDe PRIMARY KEY (numPersEnfant)
  ) ;

-- -----------------------------------------------------------------------------
--       TABLE : PereDe
-- -----------------------------------------------------------------------------
CREATE TABLE PereDe
  ( numPersEnfant  NUMBER(4)  NOT NULL
  , numPersPere    NUMBER(4)
,    CONSTRAINT PK_PereDe PRIMARY KEY (numPersEnfant)
  ) ;

-- -----------------------------------------------------------------------------
--       CREATION DES REFERENCES DE TABLE
-- -----------------------------------------------------------------------------
ALTER TABLE MereDe ADD (
    CONSTRAINT FK_MereDe_Personne
         FOREIGN KEY (numPersEnfant)
              REFERENCES Personne (numPers))   ;

ALTER TABLE MereDe ADD (
    CONSTRAINT FK_MereDe_Personne2
         FOREIGN KEY (numPersMere)
              REFERENCES Personne (numPers))   ;

ALTER TABLE PereDe ADD (
    CONSTRAINT FK_PereDe_Personne
         FOREIGN KEY (numPersEnfant)
              REFERENCES Personne (numPers))   ;

ALTER TABLE PereDe ADD (
    CONSTRAINT FK_PereDe_Personne1
         FOREIGN KEY (numPersPere)
              REFERENCES Personne (numPers))   ;

insert into Personne (numPers, nomPers, prenomPers, sexePers) values(1, 'Dubois','Anne','F');
insert into Personne (numPers, nomPers, prenomPers, sexePers) values(2, 'Durand','Alain','M');
insert into Personne (numPers, nomPers, prenomPers, sexePers) values(3, 'Dupont','Eric','M');
insert into Personne (numPers, nomPers, prenomPers, sexePers) values(4, 'Dunois','Marie','F');
insert into Personne (numPers, nomPers, prenomPers, sexePers) values(5, 'Durand','Pierre','M');
insert into Personne (numPers, nomPers, prenomPers, sexePers) values(6, 'Dupont','Daniel','M');
insert into Personne (numPers, nomPers, prenomPers, sexePers) values(7, 'Berthier','Aline','F');
insert into Personne (numPers, nomPers, prenomPers, sexePers) values(8, 'Durand','Paul','M');
insert into Personne (numPers, nomPers, prenomPers, sexePers) values(9, 'Durand','Odile','F');
insert into Personne (numPers, nomPers, prenomPers, sexePers) values(10, 'Dupont','Jeanne','F');
insert into Personne (numPers, nomPers, prenomPers, sexePers) values(11, 'Dupont','Nicolas','M');
insert into Personne (numPers, nomPers, prenomPers, sexePers) values(12, 'Dupont','Maxime','M');
insert into Personne (numPers, nomPers, prenomPers, sexePers) values(13, 'Durand','Luc','M');
insert into Personne (numPers, nomPers, prenomPers, sexePers) values(14, 'Durand','Monique','F');

insert into PereDe (numPersEnfant, numPersPere) values(5, 2);
insert into PereDe (numPersEnfant, numPersPere) values(8, 5);
insert into PereDe (numPersEnfant, numPersPere) values(9, 5);
insert into PereDe (numPersEnfant, numPersPere) values(13, 8);
insert into PereDe (numPersEnfant, numPersPere) values(6, 3);
insert into PereDe (numPersEnfant, numPersPere) values(10, 6);
insert into PereDe (numPersEnfant, numPersPere) values(11, 6);
insert into PereDe (numPersEnfant, numPersPere) values(12, 6);

insert into MereDe (numPersEnfant, numPersMere) values(5, 1);
insert into MereDe (numPersEnfant, numPersMere) values(8, 4);
insert into MereDe (numPersEnfant, numPersMere) values(9, 4);
insert into MereDe (numPersEnfant, numPersMere) values(10, 4);
insert into MereDe (numPersEnfant, numPersMere) values(11, 4);
insert into MereDe (numPersEnfant, numPersMere) values(12, 4);
insert into MereDe (numPersEnfant, numPersMere) values(13, 7);
insert into MereDe (numPersEnfant, numPersMere) values(14, 9);