-- ------------------------------------------------------------------------
-- SCRIPT B.D Chevaux
-- auteur : M. Lamolle
-- date : 21-03-2011
-- ------------------------------------------------------------------------
drop table appartient;
drop table chevauxTransportes;
drop table clients;
drop table voyages;
drop table chevaux;

CREATE TABLE chevaux
    (nocheval serial NOT NULL 
         CONSTRAINT cheval_pk PRIMARY KEY, 
     nomCheval VARCHAR(32)
    );

CREATE TABLE voyages
    (noVoyage serial NOT NULL 
         CONSTRAINT voyage_pk PRIMARY KEY, 
     datVoyage date default now()
    );

CREATE TABLE clients (noclt serial NOT NULL,    
                nom VARCHAR(32) ,
                prenom VARCHAR(32) ,
                cp varchar(5),
		ville varchar(32), 
		CONSTRAINT clients_pk PRIMARY KEY (noclt)
     );

create table appartient (nocheval int, noclt int, pourcentage float,
		CONSTRAINT appartient_pk PRIMARY KEY (nocheval, noclt), 
                CONSTRAINT appartient_nocheval_fk FOREIGN KEY (nocheval) REFERENCES chevaux (nocheval),
		CONSTRAINT appartient_noclt_fk FOREIGN KEY (noclt) REFERENCES clients (noclt) 
	);

create table chevauxTransportes (nocheval int, novoyage int, 
		prixKM float, distance int, destination varchar(32),
		CONSTRAINT chevauxTransportes_pk PRIMARY KEY (nocheval, novoyage), 
                CONSTRAINT chevauxTransport_nocheval_fk FOREIGN KEY (nocheval) REFERENCES chevaux (nocheval),
		CONSTRAINT chevauxTransport_novoyage_fk FOREIGN KEY (novoyage) REFERENCES voyages (novoyage) 
	);
 
