Exercices Merise corrigés : MCD et MLD de cas complets

1. Le MCD. Sept entités :

• CLIENT (num_client, raison_sociale, ville)
• CONTACT (num_contact, nom_contact, prenom_contact, tel_contact, email_contact)
• MATERIEL (num_serie, type_materiel, modele, date_mise_service)
• TICKET (num_ticket, date_ouverture, titre, description, priorite, statut)
• INTERVENTION (num_intervention, date_intervention, duree_min, compte_rendu), identifiée relativement à TICKET
• TECHNICIEN (matricule, nom_tech, prenom_tech)
• COMPETENCE (code_competence, libelle_competence)

Neuf associations :

• CONTACT 1,1 ── TRAVAILLER_POUR ── 1,n CLIENT (RG2 : un client a au moins un contact)
• MATERIEL 1,1 ── APPARTENIR ── 0,n CLIENT (RG3)
• TICKET 1,1 ── OUVRIR ── 0,n CONTACT (RG4)
• TICKET 0,1 ── CONCERNER ── 0,n MATERIEL (RG4 : « au plus un »)
• TICKET 0,1 ── ETRE_AFFECTE ── 0,n TECHNICIEN (RG5 : 0 tant qu'il est en attente)
• INTERVENTION (1,1) ── COMPOSER ── 0,n TICKET (RG6 : identifiant relatif, un ticket neuf n'a aucune intervention)
• INTERVENTION 1,1 ── REALISER ── 0,n TECHNICIEN (RG6)
• TECHNICIEN 0,n ── POSSEDER (niveau) ── 0,n COMPETENCE (RG7 : le niveau dépend du couple)
• TECHNICIEN (débutant) 0,1 ── SUIVRE ── 0,n TECHNICIEN (référent) (RG8, réflexive)

Deux associations relient TICKET et TECHNICIEN, directement (l'affectation) et par INTERVENTION (qui a vraiment travaillé) : elles n'ont pas le même sens, il faut les garder toutes les deux.

2. Le MLD.

• client (num_client, raison_sociale, ville)
• contact (num_contact, nom_contact, prenom_contact, tel_contact, email_contact, #num_client)
• materiel (num_serie, type_materiel, modele, date_mise_service, #num_client)
• technicien (matricule, nom_tech, prenom_tech, #matricule_referent)
• competence (code_competence, libelle_competence)
• posseder (#matricule, #code_competence, niveau)
• ticket (num_ticket, date_ouverture, titre, description, priorite, statut, #num_contact, #num_serie, #matricule_affecte)
• intervention (#num_ticket, num_intervention, date_intervention, duree_min, compte_rendu, #matricule)

8 tables (7 entités et l'association POSSEDER) et 10 clés étrangères : 1 dans contact, 1 dans materiel, 1 dans technicien (la réflexive), 2 dans posseder, 3 dans ticket, 2 dans intervention. num_serie et matricule_affecte sont facultatives dans ticket (cardinalités 0,1), comme matricule_referent dans technicien. Le niveau de compétence se contrôle par CHECK (niveau BETWEEN 1 AND 3).

3. La cohérence client. C'est une contrainte qui suit deux chemins (ticket → contact → client et ticket → matériel → client) : aucune clé étrangère ne l'exprime. On la garantit par un trigger BEFORE INSERT et BEFORE UPDATE sur ticket, qui compare les deux clients et refuse l'opération s'ils diffèrent, ou par un contrôle dans l'application. Dans une copie, nommer la contrainte et le moyen de la faire respecter rapporte des points ; c'est le genre de remarque que l'E7 attend.

1. Le MCD. Huit entités :

• ADHERENT (num_adherent, nom, prenom, date_naissance, email)
• FORMULE (code_formule, libelle_formule)
• TARIF (date_debut_tarif, prix_mensuel), identifiée relativement à FORMULE
• ABONNEMENT (num_abonnement, date_debut, date_fin)
• TYPE_COURS (code_type, libelle_type, duree_min)
• SALLE (code_salle, nom_salle, capacite)
• COACH (matricule, nom_coach, prenom_coach)
• SEANCE (num_seance, date_heure, nb_places)

Les associations :

• TARIF (1,1) ── FIXER ── 1,n FORMULE : historisation du prix (RG2) ; une formule a au moins un tarif
• ABONNEMENT 1,1 ── SOUSCRIRE ── 0,n ADHERENT et ABONNEMENT 1,1 ── PORTER_SUR ── 0,n FORMULE : ABONNEMENT est une entité, car un adhérent peut reprendre la même formule (RG3)
• SEANCE 1,1 ── ETRE_DE_TYPE ── 0,n TYPE_COURS, SEANCE 1,1 ── SE_DEROULER ── 0,n SALLE, SEANCE 1,1 ── ANIMER ── 0,n COACH (RG5)
• COACH 1,n ── ETRE_HABILITE (date_habilitation) ── 0,n TYPE_COURS (RG6)
• ADHERENT 0,n ── RESERVER (date_reservation, present) ── 0,n SEANCE (RG7)

Il existe aussi une contrainte d'inclusion : le couple (coach, type) d'une séance doit figurer dans ETRE_HABILITE.

2. Le MLD.

• adherent (num_adherent, nom, prenom, date_naissance, email)
• formule (code_formule, libelle_formule)
• tarif (#code_formule, date_debut_tarif, prix_mensuel)
• abonnement (num_abonnement, date_debut, date_fin, #num_adherent, #code_formule)
• type_cours (code_type, libelle_type, duree_min)
• salle (code_salle, nom_salle, capacite)
• coach (matricule, nom_coach, prenom_coach)
• etre_habilite (#matricule, #code_type, date_habilitation)
• seance (num_seance, date_heure, nb_places, #code_type, #code_salle, #matricule)
• reserver (#num_adherent, #num_seance, date_reservation, present)

Dix tables. present reste vide (NULL) tant que la séance n'a pas eu lieu.

3. Les contraintes. La RG6 se garantit par une clé étrangère composée : dans seance, le couple (matricule, code_type) référence la clé primaire d'etre_habilite. Une séance ne peut alors être confiée qu'à un coach habilité pour son type, sans aucun trigger. La RG8 compare des valeurs de plusieurs tables (nb_places et capacite) et compte des lignes (les réservations) : aucune contrainte CHECK ne le permet, il faut des triggers sur seance et sur reserver, ou un contrôle dans l'application.

4. Le tarif applicable. C'est la ligne de la formule dont la date d'entrée en vigueur est la plus récente sans dépasser la date voulue :

SELECT prix_mensuel
FROM tarif
WHERE code_formule = 'MENS'
  AND date_debut_tarif <= '2026-11-15'
ORDER BY date_debut_tarif DESC
LIMIT 1;

1. Les données calculées. Le montant de chaque ligne forfaitisée (quantité × montant unitaire) et le total déclaré. Vérification : 3 × 110,00 = 330,00 ; 420 × 0,62 = 260,40 ; 2 × 80,00 = 160,00 ; 5 × 25,00 = 125,00 ; soit 875,40 € de frais forfaitisés, plus 45,00 + 210,00 = 255,00 € hors forfait : 1 130,40 €. On ne le stocke pas. Le montant validé, lui, est saisi par le comptable (il peut différer du total déclaré) : c'est une donnée élémentaire.

2. Les DF.

id_visiteur              → nom_visiteur, prenom_visiteur
id_etat                  → libelle_etat
id_frais                 → libelle_frais, montant_unitaire
(id_visiteur, mois)      → nb_justificatifs, montant_valide, date_modif, id_etat
(id_visiteur, mois, id_frais) → quantite
num_ligne                → date_frais, libelle_hf, montant_hf, id_visiteur, mois

La fiche est déterminée par le couple (visiteur, mois) : c'est la règle « une seule fiche par mois ».

3. Le MCD.

• VISITEUR (id_visiteur, nom_visiteur, prenom_visiteur)

• FICHE_FRAIS (mois, nb_justificatifs, montant_valide, date_modif), identifiée relativement à VISITEUR

• ETAT (id_etat, libelle_etat)

• FRAIS_FORFAIT (id_frais, libelle_frais, montant_unitaire)

• LIGNE_HORS_FORFAIT (num_ligne, date_frais, libelle_hf, montant_hf)

• FICHE_FRAIS (1,1) ── DECLARER ── 0,n VISITEUR

• FICHE_FRAIS 1,1 ── ETRE_DANS ── 0,n ETAT

• FICHE_FRAIS 0,n ── COMPORTER (quantite) ── 0,n FRAIS_FORFAIT

• LIGNE_HORS_FORFAIT 1,1 ── FIGURER ── 0,n FICHE_FRAIS

Le MLD.

• visiteur (id_visiteur, nom_visiteur, prenom_visiteur)
• etat (id_etat, libelle_etat)
• fiche_frais (#id_visiteur, mois, nb_justificatifs, montant_valide, date_modif, #id_etat)
• frais_forfait (id_frais, libelle_frais, montant_unitaire)
• ligne_frais_forfait (#id_visiteur, #mois, #id_frais, quantite)
• ligne_hors_forfait (num_ligne, date_frais, libelle_hf, montant_hf, #id_visiteur, #mois)

Point délicat : dans ligne_frais_forfait et ligne_hors_forfait, le couple (id_visiteur, mois) forme une seule clé étrangère composée, qui référence la clé primaire de fiche_frais. En SQL : FOREIGN KEY (id_visiteur, mois) REFERENCES fiche_frais (id_visiteur, mois). Deux clés étrangères séparées ne garantiraient pas que la fiche existe.

Remarque : le montant unitaire des frais forfaitisés change parfois (le barème kilométrique, par exemple). Pour qu'une ancienne fiche garde son montant, il faudrait historiser ce montant ou le recopier dans ligne_frais_forfait au moment de la clôture (chapitre 7).

1. Le MCD.

• MODELE (code_modele, marque, designation, nb_places)

• VEHICULE (immatriculation, date_mise_circulation, kilometrage)

• AGENCE (code_agence, ville)

• CLIENT (num_client, adresse, telephone), spécialisée en PARTICULIER (nom, prenom, num_permis) et ENTREPRISE (siret, raison_sociale), avec une contrainte de partition (XT)

• LOCATION (num_location, date_depart, date_retour_prevue, date_retour, km_depart, km_retour)

• OPTION_LOCATION (code_option, libelle_option, prix_jour)

• VEHICULE 1,1 ── ETRE_DU_MODELE ── 0,n MODELE

• VEHICULE 1,1 ── RATTACHER ── 0,n AGENCE

• LOCATION 1,1 ── CONCERNER ── 0,n VEHICULE

• LOCATION 1,1 ── SOUSCRIRE ── 0,n CLIENT

• LOCATION 1,1 ── PARTIR_DE ── 0,n AGENCE

• LOCATION 0,1 ── ETRE_RENDUE_A ── 0,n AGENCE

• LOCATION 0,n ── INCLURE (quantite) ── 0,n OPTION_LOCATION

Le nombre de places dépend du modèle, pas du véhicule : il va dans MODELE. C'est le piège « modèle et exemplaire » de ce cas, le même que le document et l'exemplaire de Rivelire. Le kilométrage actuel, lui, est propre à chaque véhicule.

2. Le MLD, avec l'héritage traduit par une table par entité :

• modele (code_modele, marque, designation, nb_places)
• agence (code_agence, ville)
• vehicule (immatriculation, date_mise_circulation, kilometrage, #code_modele, #code_agence)
• client (num_client, adresse, telephone)
• particulier (#num_client, nom, prenom, num_permis)
• entreprise (#num_client, siret, raison_sociale)
• location (num_location, date_depart, date_retour_prevue, date_retour, km_depart, km_retour, #immatriculation, #num_client, #code_agence_depart, #code_agence_retour)
• option_location (code_option, libelle_option, prix_jour)
• inclure (#num_location, #code_option, quantite)

Neuf tables et dix clés étrangères. Les deux associations entre LOCATION et AGENCE donnent deux clés étrangères de noms différents. La table s'appelle option_location et non option : OPTION est un mot réservé de MySQL.

3. La table location.

CREATE TABLE location (
  num_location       INT AUTO_INCREMENT PRIMARY KEY,
  date_depart        DATE    NOT NULL,
  date_retour_prevue DATE    NOT NULL,
  date_retour        DATE,
  km_depart          INT     NOT NULL,
  km_retour          INT,
  immatriculation    CHAR(9) NOT NULL,
  num_client         INT     NOT NULL,
  code_agence_depart CHAR(4) NOT NULL,
  code_agence_retour CHAR(4),
  CONSTRAINT chk_dates_location CHECK (date_retour_prevue >= date_depart
    AND (date_retour IS NULL OR date_retour >= date_depart)),
  CONSTRAINT chk_km CHECK (km_retour IS NULL OR km_retour >= km_depart),
  CONSTRAINT chk_retour_complet CHECK ((date_retour IS NULL) = (km_retour IS NULL)
    AND (km_retour IS NULL) = (code_agence_retour IS NULL)),
  CONSTRAINT fk_location_vehicule FOREIGN KEY (immatriculation)
    REFERENCES vehicule (immatriculation),
  CONSTRAINT fk_location_client FOREIGN KEY (num_client)
    REFERENCES client (num_client),
  CONSTRAINT fk_location_agence_depart FOREIGN KEY (code_agence_depart)
    REFERENCES agence (code_agence),
  CONSTRAINT fk_location_agence_retour FOREIGN KEY (code_agence_retour)
    REFERENCES agence (code_agence)
);

Une immatriculation au format actuel (AB-123-CD) compte 9 caractères. La contrainte chk_retour_complet est une simultanéité : la date, le kilométrage et l'agence de retour sont renseignés ensemble, au retour du véhicule. La partition des clients (particulier ou entreprise, jamais les deux) ne s'exprime pas dans cette table : elle demande un trigger sur particulier et entreprise, ou une colonne de type dans client.