create table Client (
	IdClient int not null,
	Nom TEXT not null,
	Prenom TEXT not null,
	Sexe BOOLEAN not null,
	DDN DATE not null,
	Pcode int not null
		CHECK (Pcode > 0),
	Score int not null
		CHECK (Score > 0 AND Score <= 10),
	primary key (IdClient)
);

create table Sport (
	IdSport int not null,
	Nom TEXT not null,
	NbMin int not null
		CHECK (NbMin >= 0),
	NbMax int not null
		CHECK (NbMax >= NbMin),
	primary key (IdSport)
);

create table Lieu (
	IdLieu int not null,
	Intitule TEXT not null,
	PCode int not null
		CHECK (Pcode > 0),
	Capacite int not null
		CHECK (Capacite > 0),
	IdSport int not null UNIQUE,
	primary key (IdLieu),
	constraint se_pratique
		foreign key (IdSport)
			references Sport (IdSport)
);

create table Event (
	IdEv int not null,
	Niveau int not null
		CHECK (Niveau > 0),
	Date DATE not null,
	IdClient int not null UNIQUE,
	IdLieu int not null UNIQUE,
	primary key (IdEv),
	constraint organise
		foreign key (IdClient)
			references Client (IdClient),
	constraint se_situe
		foreign key (IdLieu)
			references Lieu (IdLieu)
);

create table Terrain (
	Surface int not null
		CHECK (Surface > 0),
	Type TEXT not null,
	IdTer int not null,
	IdLieu int not null UNIQUE,
	primary key (IdTer),
	constraint est_compose
		foreign key (IdLieu)
			references Lieu (IdLieu)
);

create table participe (
	IdClient int not null,
	IdEv int not null UNIQUE,
	primary key (IdClient),
	foreign key (IdClient)
		references Client (IdClient),
	foreign key (IdEv)
		references Event (IdEv)
);