Bases de données et programmation – Exercices UML et relationnel (1)

Ce document présente une série d'exercices et leurs corrections pour la modélisation de bases de données à l'aide de diagrammes UML et leur traduction en schémas relationnels. Il s'adresse aux étudiants en informatique, notamment ceux spécialisés en bases de données et systèmes d'information, souhaitant approfondir la conception conceptuelle et logique de bases de données dans des contextes variés : entreprise de fabrication, compagnie aérienne, institut de formation.

Exercice 1 : Modélisation d'une entreprise de fabrication et distribution

Une entreprise possède une usine et plusieurs dépôts de stockage/expédition. Les éléments à modéliser sont :

• Produit : identifié par un numéro (NOP), un libellé (LIB) et un prix unitaire (PU).
• Dépôt : identifié par un numéro (NOD). Chaque dépôt stocke plusieurs produits avec une quantité en stock (QTS) et une quantité disponible (QTD).
• Client : identifié par un numéro (NOCLI), un nom (NOM), une adresse (ADR), un chiffre d'affaires (CA) et un taux de réduction (RED). Chaque client est livré à partir d'un dépôt privilégié, avec un dépôt de secours possible.
• Commande : associée à un client, caractérisée par un numéro (NOCOM) et une date (DAC). Chaque commande comporte des lignes avec un code produit, une quantité commandée (QTC), un délai de livraison (DEL) et un code livraison (CL) indiquant si la livraison a eu lieu.
• Facture : liée à une ou plusieurs commandes dès qu'une livraison est intervenue. Identifiée par un numéro (NOF), une date (DAP) et un montant (MOF). Chaque ligne facture une quantité (QTF) et un montant partiel (MOP).

Le schéma conceptuel UML doit représenter ces entités et leurs relations, notamment la gestion des stocks par dépôt, la relation client-commande-facture, et la disponibilité des produits.

Exemple simplifié de relation Produit-Dépôt

Un produit P1 (NOP=101, LIB="Clé USB", PU=10€) est stocké dans deux dépôts D1 et D2 :

• Dépôt D1 (NOD=1) : QTS=100, QTD=80
• Dépôt D2 (NOD=2) : QTS=50, QTD=50

La différence QTS - QTD correspond à la quantité réservée pour des commandes en attente.

Exercice 2 : Gestion des vols d'une compagnie aérienne

La compagnie souhaite gérer ses vols, départs, passagers et personnels. Les éléments clés sont :

• Vol : identifié par un NUMERO, avec ville de départ, ville d'arrivée, heure de départ, heure d'arrivée, distance et fréquence.
• Départ : un vol programmé à une date précise, avec un avion affecté.
• Passager : enregistré pour un départ, caractérisé par nom, adresse et téléphone.
• Personnel : affecté à un départ, distinguant personnels navigants (dont pilotes) et non-navigants.
• Avion : identifié par un numéro, un type et une capacité.

Diagramme de classes UML (extrait simplifié)

• Vol (NUMERO, VILLE-DEPART, VILLE-ARRIVEE, HEURE-DEPART, HEURE-ARRIVEE, DISTANCE, FREQUENCE)
• Départ (NUMERO, DATE) lié à un Vol et un Avion
• Avion (NUMERO, TYPE, CAPACITE)
• Passager (NOM, ADRESSE, NO-TELEPHONE)
• Personnel (NOM, ADRESSE, NO-TELEPHONE), avec sous-classes Navigant et Non-navigant, et sous-classe Pilote pour navigants

Extension : Vols à plusieurs tronçons et gestion des villes

Pour gérer les vols composés de plusieurs tronçons, on introduit :

• Tronçon : identifié par un numéro, avec ville de départ, ville d'arrivée, distance.
• Un vol est composé d'un ou plusieurs tronçons.
• Chaque tronçon a une heure de départ et une heure d'arrivée.
• Les villes sont liées aux vols en tant que points de départ et d'arrivée.

Exemple de vol à tronçons

• Vol V1 composé de deux tronçons :
• Tronçon T1 : Lyon → Paris, distance 400 km, départ 08:00, arrivée 09:00
• Tronçon T2 : Paris → Londres, distance 350 km, départ 10:00, arrivée 11:00

Exercice 3 : Gestion des formations dans un institut privé

Le modèle doit gérer les cours, sessions, animateurs et participants :

• Cours : identifié par NOCOURS, avec libellé, durée (heures) et type.
• Session : plusieurs sessions par cours dans l'année, identifiées par NOSES, avec date de début et prix.
• Animateur : plusieurs animateurs par session, avec un animateur principal responsable. Chaque animateur a un numéro (NOANI), nom et adresse.
• On mémorise le nombre d'heures (NBH) effectuées par animateur pour chaque session.
• Participant : indépendant ou employé d'une entreprise cliente, identifié par NO-PAR, nom et adresse.
• Pour les employés, on enregistre le nom (NOMEN) et l'adresse (ADREN) de l'entreprise.
• La distinction entre participants indépendants et employés est importante pour la facturation.

Relations principales

• ANIMER (NOANI#, NOSES#, NBH) : animateurs et sessions avec heures effectuées.
• PARTICIPER (NOP_PAR#, NOSES#) : participants inscrits aux sessions.

Exemple d'inscription

Le cours C1 (NOCOURS=10, LIBELLE="Programmation Java", DUREE=30h, TYPE="Informatique") propose une session S1 (NOSES=100, DATE=01/03/2024, PRIX=500€). L'animateur A1 (NOANI=200, NOM="Dupont") anime cette session 20 heures. Le participant P1 (NOP_PAR=300, NOM="Martin") y est inscrit.

Exercice 4 : Traduction des schémas UML en schémas relationnels

Les trois modèles précédents sont traduits en schémas relationnels, avec les clés primaires en majuscules et les clés étrangères marquées par un #.

Schéma relationnel pour l'entreprise de fabrication/distribution

Relation	Attributs
PRODUIT	(NOP, LIB, PU)
DEPOT	(NOD)
CLIENT	(NOCLI, NOM, ADR, CA, RED, NOD_PRIV#, NOD_SEC#)
COMMANDE	(NOCOM, DAC, NOCLI#)
FACTURE	(NOF, DAP, MOP, NOCOM#)
STOCKAGE	(NOP#, NOD#, QTS, QTD)
LIGNE_COMMANDE	(NOP#, NOCOM#, QTC, DEL, CL)
LIGNE_FACTURE	(NOP#, NOF#, QTF, MOF)

Schéma relationnel pour la compagnie aérienne

Relation	Attributs
VOL	(NUMVOL, FREQUENCE)
TRONCON	(NUMTRONC, VILLEDEP, VILLEARR, DISTANCE)
AVION	(NUMAV, TYPE, CAPACITE)
DEPART	(NUMDEP, DTE, NUMVOL#, NUMAV#, QTECARBU)
PERSONNE	(NUMPERS, ADRESSE, TEL, CAT1)
PASSAGER	(NUMPERS_PAS#)
PERSONNEL	(NUMPERS_PER#, CAT2)
NAVIGANT	(NUMPERS_NAV#)
NON_NAV	(NUMPERS_NON#)
PILOTE	(NUMPERS_PIL#)
DECOUPER	(NUMVOL#, NUMTRONC#, HEUREDEP, HEUREARR)
RESERVER	(NUMDEP#, NUMPERS_PAS#)
AFFECTER	(NUMDEP#, NUMPERS_PER#)

Schéma relationnel pour l'institut de formation

Relation	Attributs
SESSION	(NOSES, DATE, PRIX, NOCOURS#, NOP_ANIPRINC#)
COURS	(NOCOURS, LIBELLE, DUREE, TYPE)
PERSONNE	(NOP, NOM, ADR, CAT)
ANIMATEUR	(NOP_ANI#)
PARTICIPANT	(NOP_PAR#)
EMPLOYE	(NOP_EMP#, NOMEN, ADREN)
PARTICIPER	(NOP_PAR#, NOSES#)
ANIMER	(NOP_ANI#, NOSES#, NBH)

Glossaire des termes clés

• Classe : Entité ou concept modélisé dans UML, avec attributs et relations.
• Attribut : Propriété ou caractéristique d'une classe.
• Association : Relation entre classes, pouvant être qualifiée par une cardinalité.
• Héritage : Mécanisme UML permettant de spécialiser une classe en sous-classes.
• Cardinalité : Nombre minimum et maximum d'occurrences liées dans une association.
• Schéma conceptuel : Modèle abstrait représentant les données et leurs relations sans considération technique.
• Schéma relationnel : Traduction du schéma conceptuel en tables avec clés primaires et étrangères.
• Clé primaire : Attribut ou ensemble d'attributs identifiant de manière unique une occurrence dans une table.
• Clé étrangère : Attribut référant à la clé primaire d'une autre table pour assurer l'intégrité référentielle.
• Quantité en stock (QTS) : Total de produits présents dans un dépôt.
• Quantité disponible (QTD) : Quantité de produits non réservés et donc disponibles à la vente.
• Session : Occurrence d'un cours à une date donnée, avec animateurs et participants.
• Tronçon : Segment d'un vol entre deux villes, avec horaires et distance.

Points clés à retenir

• La modélisation UML permet de représenter clairement les entités, leurs attributs et leurs relations dans un système d'information.
• Les relations d'héritage facilitent la gestion des catégories et spécialisations (ex. personnel navigant vs non-navigant).
• La traduction en schéma relationnel assure la mise en œuvre pratique dans une base de données relationnelle.
• La gestion des stocks nécessite de distinguer la quantité totale et la quantité disponible pour refléter les réservations.
• La modélisation des vols multi-tronçons permet une gestion fine des itinéraires et horaires.
• La distinction entre types de participants (indépendants, employés) est importante pour la gestion administrative et financière.