Projet BDD - Final

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)
);