drop table chien cascade constraints;
drop table concours cascade constraints; 
drop table proprietaire cascade constraints;
drop table participe cascade constraints;
drop table estClasse cascade constraints;
drop table vendu cascade constraints;
drop table aPourParent cascade constraints;

               
create table proprietaire (numProp number(3) constraint PKProprietaire primary key, 
                          nomProp varchar2(12), 
                          adrProp varchar2(12));
    
create table chien (numChien varchar2(7) constraint PKChien primary key, 
                    nomChien varchar2(12), 
                    race varchar2(25), 
                    sexe varchar2(1), 
                    dateNais date, 
                    numPropCourant number(3), 
                    dateDeces date
                    );    
                          
create table concours (numConcours number(5) constraint PKConcours primary key, 
                      lieu varchar2(12), 
                      typeConcours varchar2(12), 
                      dateConcours date);
                      
create table participe (numChien varchar2(7), 
                        numConcours number(5), 
                        constraint PKParticipe primary key(numChien, numConcours));
                       
                        
create table estClasse (numChien varchar2(7), 
                        numConcours number(5), 
                        classement number(1),
                        constraint PKEstClasse primary key(numChien, numConcours));
                       
                        
create table vendu (numPropVendeur number(3), 
                    numChien varchar2(7), 
                    dateVente date,
                    constraint PKVendu primary key(numPropVendeur, numChien));
                  
                    
create table aPourParent (numChiot varchar2(7), 
                          numParent varchar2(7),
                          constraint PKAPourParent primary key(numChiot, numParent));
                          
alter table chien add(constraint FKChienProp foreign key (numPropCourant) references proprietaire (numProp));

alter table participe add( constraint FKParticipeChien foreign key (numChien) references chien (numChien),
                        constraint FKParticipeConcours foreign key (numConcours) references concours (numConcours));

alter table estClasse add( constraint FKEstClasseChien foreign key (numChien) references chien (numChien),
                        constraint FKEstClasseConcours foreign key (numConcours) references concours (numConcours));
                        
alter table vendu add(  constraint FKVenduProp foreign key (numPropVendeur) references proprietaire (numProp),
                    constraint FKVenduChien foreign key (numChien) references chien (numChien));
                    
alter table aPourParent add( constraint FKAPourParentChiot foreign key (numChiot) references chien (numChien),
                          constraint FKAPourParentParent foreign key (numParent) references chien (numChien));