Étude de cas complète sur un seul schéma : partie A en quatre exercices — lire le schéma, interpréter des requêtes, interroger, étendre

Partie A : Gestion de la base de données (12 points)

Le service municipal VéloCité de Sfax met des vélos, classiques ou électriques, à la disposition de ses abonnés dans les stations de la ville. Pour gérer les abonnements, les trajets et l'entretien du parc, il utilise la base de données Gestion_VeloCite, dont la représentation graphique est la suivante :

Les champs des tables sont décrits ci-dessous :

Exercice 1 : Interprétation de la base de données (3 points)

1. Donner la représentation textuelle de cette base de données en précisant pour chaque table son nom, ses champs, sa clé primaire et sa (ses) clé(s) étrangère(s) s'il y en a.
2. Pour chacune des propositions suivantes, répondre par « Vrai » si elle est vérifiée ou par « Faux » dans le cas contraire :
   1. Un vélo peut être rattaché en même temps à deux stations différentes.
   2. Deux vélos du même modèle ont toujours le même tarif à la minute.
   3. Un vélo peut faire l'objet de deux réparations le même jour.
   4. Un abonné peut souscrire à deux formules d'abonnement en même temps.
   5. Un abonné peut emprunter le même vélo plusieurs fois dans la même journée.
   6. Un trajet peut être enregistré avant que son montant ne soit connu.
   7. Deux abonnés peuvent avoir le même numéro de téléphone.
3. Les tables « Modele » et « Velo » sont vides et les stations d'identifiants 1 à 4 existent déjà. On insère dans l'ordre les lignes du document 1, d'abord dans la table « Modele » puis dans la table « Velo ». Le SGBD génère des erreurs au niveau de quelques lignes. Pour chaque erreur, indiquer le numéro de la ligne, le nom de la table, le nom du champ ainsi que la contrainte d'intégrité non respectée (intégrité de table (clé primaire), intégrité référentielle ou intégrité de domaine).

Document 1 — lignes à insérer dans la table « Modele » puis dans la table « Velo » :

Exercice 2 : Interprétation des requêtes (1,5 point)

Soit la liste des rôles suivante :

• a. Supprimer les stations dont le nom contient « Gare ».
• b. Ajouter une contrainte de domaine sur un champ de la table « Formule ».
• c. Afficher les vélos du modèle dont le code est « E-City ».
• d. Modifier la formule d'un abonné dont le numéro est spécifié.
• e. Supprimer les stations dont le nom commence par « Gare ».
• f. Ajouter une nouvelle formule avec des valeurs spécifiques.
• g. Modifier la valeur par défaut d'un champ de la table « Station ».
• h. Afficher les vélos du modèle dont le libellé est « E-City ».
• i. Modifier le prix mensuel d'une formule donnée.
• j. Modifier le type d'un champ de la table « Station ».

Pour chacune des requêtes R1 à R6 ci-dessous, donner la lettre correspondant à son rôle dans la liste ci-dessus.

-- R1
INSERT INTO Formule
VALUES ('F4', 'Etudiant', 10.000);
-- R2
SELECT idVelo, dateService
FROM Velo V, Modele M
WHERE V.codeModele = M.codeModele
  AND libModele = 'E-City';
-- R3
UPDATE Abonne SET codeFormule = 'F3'
WHERE numAbonne = 1002;
-- R4
DELETE FROM Station
WHERE nomStation LIKE 'Gare%';
-- R5
ALTER TABLE Formule
ADD CHECK (prixMensuel > 0);
-- R6
ALTER TABLE Station
MODIFY capacite INT DEFAULT 20;

Exercice 3 : Manipulation des données de la base (6 points)

On suppose que la base de données est déjà créée et contient des données. Écrire en SQL les requêtes permettant de répondre aux questions suivantes :

1. Quels sont les différents quartiers dans lesquels se trouvent les stations ? On affichera, sans répétition, le champ quartier.
2. Quels sont les vélos réparés moins de 365 jours après leur mise en service ? On affichera les champs idVelo, dateService, dateRep et panne.
3. Comment réduire de 20 % le tarif à la minute des modèles de type « Classique » ?
4. Quels sont les modèles dont au moins un vélo a fait l'objet d'une réparation ? On affichera, sans répétition, les champs libModele et typeModele.
5. Quels sont les trajets effectués en 2026 par l'abonné nommé « Karim Jmal » ? On affichera les champs idTrajet, dateTrajet et dureeMin, du trajet le plus long au plus court.
6. Quel est le nombre d'abonnés de chaque formule ? On affichera les champs codeFormule et libFormule ainsi que le nombre d'abonnés.
7. Quels sont les vélos dont le coût total des réparations dépasse strictement 100 dinars ? On affichera les champs idVelo et libModele ainsi que ce coût total.
8. Quels sont les vélos qui n'ont été empruntés par aucun abonné pendant l'année 2025 ? On affichera les champs idVelo et dateService.
9. Quels sont les vélos avec lesquels a été effectué le trajet le plus cher ? On affichera les champs idVelo, libModele et montant.

Exercice 4 : Extension et modification de la structure de la base de données (1,5 point)

Important : à chaque représentation textuelle demandée, indiquer la clé primaire et la (les) clé(s) étrangère(s) de chaque table.

1. Le quartier d'une station est saisi en toutes lettres, si bien qu'un même quartier apparaît sous des écritures différentes (« Sfax Jadida », « Sfax El Jadida »…). Pour éliminer cette incohérence, on propose de modifier la structure de la base en respectant les règles de gestion suivantes : R1 : une station se trouve dans un seul quartier ; R2 : un quartier peut compter plusieurs stations. Proposer une représentation textuelle de la table « Station » ainsi que de toute nouvelle table indispensable.
2. Pour suivre l'état du parc, le service inspecte régulièrement ses vélos : lors d'une inspection, on vérifie des points de contrôle (freins, pneus, éclairage, batterie…) et, pour chacun, on enregistre un résultat (« Conforme » ou « Non conforme ») et une remarque. On propose de créer les tables PointControle, Inspection et Verification en respectant les règles de gestion suivantes : R3 : une inspection porte sur plusieurs points de contrôle ; R4 : un point de contrôle est vérifié lors de plusieurs inspections ; R5 : une inspection concerne un seul vélo ; R6 : lors d'une inspection, un point de contrôle n'est vérifié qu'une seule fois, avec l'enregistrement de son résultat.
   1. Les propositions 1 à 4 ci-dessous sont quatre représentations textuelles erronées. Pour chacune, indiquer la règle de gestion (R3, R4, R5 ou R6) non respectée.
   2. En déduire une représentation textuelle correcte des tables « PointControle », « Inspection » et « Verification ».

• Proposition 1 : PointControle ( codePoint , libPoint ) ; Inspection ( idInsp , dateInsp ) ; Verification ( idInsp# , codePoint# , resultat , remarque , idVelo# )
• Proposition 2 : PointControle ( codePoint , libPoint ) ; Inspection ( idInsp , dateInsp , idVelo# ) ; Verification ( idVerif , idInsp# , codePoint# , resultat , remarque )
• Proposition 3 : PointControle ( codePoint , libPoint ) ; Inspection ( idInsp , dateInsp , idVelo# , codePoint# , resultat ) ; Verification ( idInsp# , remarque )
• Proposition 4 : PointControle ( codePoint , libPoint , idInsp# , resultat ) ; Inspection ( idInsp , dateInsp , idVelo# ) ; Verification ( idInsp# , remarque )