TD 3.01 — Rappels BDD : un problème complet

Ce TD est un problème complet : on part d'un texte en français, et on descend jusqu'aux ordres SQL. Il n'y a aucune question piège — seulement l'enchaînement normal du travail d'un concepteur de base de données. C'est aussi le cas support du TP 3.01, où le même modèle sera réellement créé dans PostgreSQL.

Objectifs du TD

1. L'énoncé : le club LaRaquette

Le club LaRaquette est un club multi-sports de raquettes : on y joue au Tennis, au Squash et au Badminton.

Le club souhaite mettre en place un système informatique pour gérer sa clientèle et les réservations. Voici les règles du métier, une par une — car chaque phrase de l'énoncé deviendra une contrainte ou une cardinalité dans le modèle.

2. La méthode de modélisation

Avant de dessiner quoi que ce soit, il faut une méthode. Sans elle, on écrit des tables au hasard et on découvre les oublis trop tard.

3. Question 1 — le MCD

Étape 1 : les entités

En parcourant l'énoncé, on relève cinq « choses » dont on parle et qui ont une existence propre :

Étape 2 : les associations

Les verbes de l'énoncé donnent les associations :

Étape 3 : les cardinalités

Voici le raisonnement, association par association :

• Sport ↔ Terrain : un sport se joue sur 0 à n terrains ; un terrain accueille 1 et un seul sport. On écrit donc 1,n côté sport et 1,1 côté terrain.
• Terrain ↔ Créneau : un terrain peut accueillir 0 à n créneaux (les créneaux sont répétés chaque jour) ; un créneau se déroule sur 1 terrain précis. → 0,n / 1,1.
• Créneau ↔ Reservation : un créneau fait l'objet de 0 ou 1 réservation — pas deux, sinon deux personnes joueraient en même temps sur le même terrain ; une réservation concerne 1 créneau. → 0,1 / 1,1.
• Abonne ↔ Reservation (responsable) : un abonné effectue 0 à n réservations ; une réservation a 1 et un seul responsable. → 0,n / 1,1.
• Abonne ↔ Reservation (partenaire) : même raisonnement, dans l'autre rôle. → 0,n / 1,1.
• Abonne ↔ Abonne (parrainage) : un abonné peut parrainer 0 à n personnes ; un abonné est parrainé par 0 ou 1 personne (il n'a qu'un parrain). → 0,n / 0,1.

Le MCD complet

Et le détail de l'association réflexive, qui est le point le plus souvent raté :

4. Question 2 — les vérifications manquantes

L'énoncé demande : « y a-t-il des vérifications classiques qui ne seraient pas prises en compte par ce modèle ? » La réponse est oui, et la bonne façon de répondre est de dire à quel niveau chacune peut être rattrapée.

Voici la liste complète, avec la solution technique de chacune :

CHECK (dateNaissance <= CURRENT_DATE - INTERVAL '18 years')   -- ❌ refusé

5. Question 3 — le MLD

On applique maintenant les trois règles de passage :

• chaque entité devient une table, son identifiant devenant la clé primaire ;
• chaque association de type (1,n) laisse une clé étrangère dans la table du côté « n » ;
• l'association réflexive se traduit par une clé étrangère dans sa propre table.

Le résultat, écrit en notation relationnelle — le souligné est la clé primaire, le # la clé étrangère :

Sport(noSport, libelle)
Terrain(noTerrain, nom, #noSport)
Creneau(noCreneau, dateCreneau, heureDebut, #noTerrain)
Abonne(noAbo, nom, prenom, dateNaissance, dateDebutAbo, dateFinAbo, #noParrain)
Reservation(noResa, utilise, #noCreneau, #noAboResp, #noAboPart)

Quelques remarques sur les décisions prises :

• Reservation n'a pas de date ni d'heure : elle les tient de son créneau. Les recopier serait une redondance — donc une source d'incohérence.
• noParrain accepte NULL : un abonné peut n'avoir aucun parrain. La colonne n'est donc pas NOT NULL, contrairement aux autres clés étrangères.
• Deux clés étrangères vers la même table dans Reservation : c'est parfaitement légal, et c'est la traduction directe des deux associations effectuer et participer.
• utilise est un booléen : c'est ce qui permet de calculer les statistiques sur les réservations non utilisées, comme le demande l'énoncé.

6. Question 4 — les ordres de création

L'énoncé impose : clés étrangères en RESTRICT pour le DELETE et en CASCADE pour le UPDATE, responsable et invité distincts, heures entières entre 10h et 20h, dateFinAbo par défaut à un an, et abonné majeur.

-- On repart toujours d'une base propre : le script peut être relancé sans erreur.
DROP TABLE IF EXISTS reservation, creneau, abonne, terrain, sport CASCADE;

CREATE TABLE sport (
    noSport   INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    libelle   VARCHAR(15) NOT NULL UNIQUE
);

CREATE TABLE terrain (
    noTerrain INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nom       VARCHAR(15) NOT NULL UNIQUE,
    noSport   INTEGER NOT NULL,
    CONSTRAINT fk_terrain_sport FOREIGN KEY (noSport)
        REFERENCES sport (noSport)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE creneau (
    noCreneau   INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    dateCreneau DATE    NOT NULL,
    heureDebut  INTEGER NOT NULL,
    noTerrain   INTEGER NOT NULL,
    -- INTEGER garantit déjà une heure entière ; on borne l'amplitude.
    CONSTRAINT ck_creneau_heure  CHECK (heureDebut >= 10 AND heureDebut <= 20),
    -- Un même terrain ne peut pas avoir deux créneaux à la même heure le même jour.
    CONSTRAINT uq_creneau        UNIQUE (noTerrain, dateCreneau, heureDebut),
    CONSTRAINT fk_creneau_terrain FOREIGN KEY (noTerrain)
        REFERENCES terrain (noTerrain)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE abonne (
    noAbo         INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nom           VARCHAR(15) NOT NULL,
    prenom        VARCHAR(15) NOT NULL,
    dateNaissance DATE        NOT NULL,
    dateDebutAbo  DATE        NOT NULL DEFAULT CURRENT_DATE,
    dateFinAbo    DATE        NOT NULL DEFAULT (CURRENT_DATE + INTERVAL '1 year'),
    noParrain     INTEGER,
    CONSTRAINT ck_abonne_dates CHECK (dateFinAbo > dateDebutAbo),
    CONSTRAINT fk_abonne_parrain FOREIGN KEY (noParrain)
        REFERENCES abonne (noAbo)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE reservation (
    noResa    INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    utilise   BOOLEAN NOT NULL DEFAULT false,
    noCreneau INTEGER NOT NULL,
    noAboResp INTEGER NOT NULL,
    noAboPart INTEGER NOT NULL,
    -- Le responsable et l'invité sont bien deux personnes différentes.
    CONSTRAINT ck_resa_distincts CHECK (noAboResp <> noAboPart),
    -- Traduit la cardinalité « 0,1 » : un créneau est réservé au plus une fois.
    CONSTRAINT uq_resa_creneau  UNIQUE (noCreneau),
    CONSTRAINT fk_resa_creneau FOREIGN KEY (noCreneau)
        REFERENCES creneau (noCreneau)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_resa_resp FOREIGN KEY (noAboResp)
        REFERENCES abonne (noAbo)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_resa_part FOREIGN KEY (noAboPart)
        REFERENCES abonne (noAbo)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

Ce qu'il faut comprendre de ce script

• L'ordre des CREATE TABLE n'est pas libre. On crée d'abord les tables sans dépendance (sport), puis celles qui en dépendent. Un CREATE TABLE terrain avant CREATE TABLE sport échouerait : la clé étrangère ne trouverait pas sa cible.
• ON DELETE RESTRICT protège les données : impossible de supprimer un terrain qui a encore des créneaux. C'est le comportement prudent, et c'est ce que demande l'énoncé.
• ON UPDATE CASCADE propage une renumérotation. Utile le jour où le club décide de renuméroter ses terrains.
• UNIQUE (noCreneau) sur Reservation est la traduction en SQL de la cardinalité 0,1. C'est le lien le plus important à savoir expliquer entre le MCD et le DDL.
• GENERATED BY DEFAULT AS IDENTITY laisse la base fabriquer les numéros toute seule, tout en autorisant une valeur explicite si besoin. L'ancienne écriture, encore très répandue, est SERIAL.

7. Partie 2 — les opérations de base

7.1 Effacer toutes les données de toutes les tables

Deux stratégies, selon ce qu'on veut dire.

TRUNCATE reservation, creneau, abonne, terrain, sport CASCADE;

TRUNCATE vide les tables listées. Le mot CASCADE est nécessaire ici : comme reservation dépend de creneau, et creneau de terrain, PostgreSQL refuse de tronquer une table tant qu'une autre la référence. CASCADE dit « tronque aussi tout ce qui en dépend ».

DELETE FROM reservation;
DELETE FROM creneau;
DELETE FROM terrain;
DELETE FROM sport;
DELETE FROM abonne;

Cette version est plus verbeuse mais plus explicite : on voit l'ordre des dépendances. Elle est aussi beaucoup plus lente sur de grosses tables, parce que chaque DELETE est journalisé.

7.2 Détruire toutes les tables

L'ordre de destruction est l'inverse de l'ordre de création : on commence par celles qui ne sont référencées par personne.

DROP TABLE IF EXISTS reservation, creneau, abonne, terrain, sport CASCADE;

Deux points :

• IF EXISTS évite l'erreur si une table n'existe pas — indispensable dans un script qu'on veut pouvoir relancer.
• CASCADE ici ne concerne pas l'ordre entre les tables listées (PostgreSQL les traite ensemble), mais les dépendances extérieures : une vue, un index ou une contrainte d'une autre table qui pointerait vers l'une d'elles.

7.3 Créer une réservation le 15/09 à 10h entre Dupont et Durand sur le terrain 18

C'est la question la plus instructive, parce qu'elle oblige à réfléchir à l'ordre des insertions et à la résolution des identifiants.

-- 1. Le terrain 18 doit exister (le club n'en décrit que 16).
INSERT INTO terrain (nom, noSport)
VALUES ('T18', (SELECT noSport FROM sport WHERE libelle = 'Tennis'));

-- 2. Le créneau correspondant : terrain 18, le 15/09, à 10h.
INSERT INTO creneau (dateCreneau, heureDebut, noTerrain)
VALUES (
    DATE '2026-09-15',
    10,
    (SELECT noTerrain FROM terrain WHERE nom = 'T18')
);

-- 3. La réservation elle-même.
INSERT INTO reservation (noCreneau, noAboResp, noAboPart)
VALUES (
    (SELECT noCreneau FROM creneau
      WHERE dateCreneau = DATE '2026-09-15'
        AND heureDebut  = 10
        AND noTerrain   = (SELECT noTerrain FROM terrain WHERE nom = 'T18')),
    (SELECT noAbo FROM abonne WHERE nom = 'Dupont' AND prenom = 'Jean'),
    (SELECT noAbo FROM abonne WHERE nom = 'Durand' AND prenom = 'Paul')
);

8. Auto-évaluation