Exercices de synthèse

Exercice 1: Modélisation

On vous demande de concevoir le modèle entité-association ainsi que le schéma relationnel d'un fitness en vue de l'implémenter sous la forme d'une base de données gérée par un logiciel que votre entreprise s'apprête à développer. Après discussion avec un des responsables du fitness quant à l'organisation de ce dernier, il ressort de vos notes les informations suivantes:

• On croise différentes personnes au sein du fitness caractérisées par leur prénom, nom, adresse, ville, sexe et date de naissance. Chaque personne est soit un employé, soit un client. Un employé peut lui-même être client du fitness et inversement. En outre, d'un employé, on doit connaître son unique fonction (cadre, responsable de l'entretien ou moniteur), son taux d'activité dans le fitness ainsi que son salaire. Un cadre est caractérisé par sa position au sein du fitness, un responsable de l'entretien par son année d'engagement et un moniteur par son brevet principal. D'un client, on doit connaître le type d'abonnement auquel il souscrit.
• Chaque personne vit dans une ville et dans chaque ville, on retrouve 0, 1 ou plusieurs personnes ayant un rapport avec le fitness (employé ou client)
• Des cours (caractérisés par un nom et une description) sont données dans le cadre du fitness. Afin d'en bénéficier, les clients doivent s'inscrire au cours en indiquant leur niveau. 0, 1 ou plusieurs clients peuvent s'inscrire à un cours. Chaque client peut être inscrit à 0, 1 ou plusieurs cours.
• Chaque cours est donné par un et un seul moniteur du fitness. Chaque moniteur du fitness peut avoir à charge 0, 1 ou plusieurs cours.
• Un cours est toujours localisé dans une salle qui lui est attribuée. Dans chaque salle est donné un ou plusieurs cours. Les salles sont caractérisées par son nom et son type.
• Chaque salle est nettoyée par une et une seule personne de l'entretien. Par contre, un responsable de l'entretien peut être responsable de plusieurs salles.
• Le fitness dispose de différents appareils. Chaque appareil, qui est caractérisé par son type, son numéro de série et sa valeur, est localisé dans une et une seule salle. Dans une salle, on peut avoir plusieurs appareils tout comme il est possible d'y en avoir aucun.
• Un appareil a toujours un moniteur responsable. Un moniteur peut être responsable de 0, 1 ou plusieurs appareils.

Modélisez la situation à l'aide d'un schéma entités-associations puis traduisez-le en schéma relationnel en mettant en évidence les différentes contraintes d'intégrité.

Afficher les solutions | Masquer les solutions

Exercice 2: Requêtes SQL

Considérons une base de donnée BureauSympa qui enregistre les données concernant les heures supplémentaires et les absences des employés d'une petite entreprise. Comme le montre le schéma relationnel ci-dessous, la base de donnée comporte cinq tables:

• La table Employe stocke diverses informations concernant les employés;
• La table Departement contient les informations concernant les départements de l'entreprise;
• La table Absence indique chaque absence en jour;
• La table Motif décrit la raison de l'absence (congé, maladie, etc);
• La table Supplementaire enregistre les heures supplémentaires avec un coefficient (taux) pour les heures de nuit ou du week-end.

Les données provenant de cette base de données peuvent être importées directement à l'aide du fichier BureauSympa.sql.

Rédigez les requêtes SQL suivantes permettant d'obtenir des informations à partir de la base de données BureauSympa. Vos solutions devront engendrer les mêmes tables que celles proposées dans le lien "Résultat" situé à la suite de l'énoncé de chaque requête:

1. La requête retourne la liste téléphonique de tous les employés en affichant, pour chaque employé, son prénom, son nom, sa ville et son numéro de téléphone.
   ( Résultat | Solution)
2. La requête retourne la liste téléphonique de tous les employés, triée par ordre alphabétique du nom, puis du prénom de l'employé, en affichant, pour chaque employé, son nom, son prénom, sa ville et son numéro de téléphone.
   (Résultat | Solution)
3. La requête retourne la liste de toutes les informations des employés résidant à Duraville.
   (Résultat | Solution)
4. La requête retourne la liste de tous les employés en affichant dans l'ordre le prénom, le nom et la date de naissance. Cette liste doit afficher d'abord les employés les plus âgés.
   (Résultat | Solution)
5. La requête retourne la liste contenant le nom et le prénom des employés travaillant à temps plein, triée par le nom de famille puis le prénom.
   (Résultat | Solution)
6. La requête retourne le nombre d'employés travaillant au département des finances.
   (Résultat | Solution)
7. La requête retourne le salaire moyen et le salaire maximal de tous les techniciens (la chaîne de caractères "Technicien" doit apparaître dans l'attribut Fonction).
   (Résultat | Solution)
8. La requête retourne la liste des enregistrements de la table Supplementaire en affichant pour chacun d'eux le prénom et le nom de l'employé concerné.
   (Résultat | Solution)
9. La requête retourne la liste des employés avec le nom du département auquel ils sont affectés. Cette liste affichera dans l'ordre le département, le nom, le prénom et la date d'embauche et sera triée par département et à l'intérieur de chaque département, par nom des employés.
   (Résultat | Solution)
10. La requête retourne la liste, triée par ordre alphabétique du nom et du prénom des employés embauchés depuis le 1er janvier 1990. La liste contiendra également, pour chaque employé, le nom du département auquel il est affecté ainsi que la date d'embauche.
    (Résultat | Solution)
11. La requête retourne la liste des absences en affichant pour chacune le prénom, le nom de l'employé concerné, la date du début de l'absence, sa durée, la description du motif d'absence et finalement la remarque associée. Cette liste sera triée par ordre alphabétique du nom de famille.
    (Résultat | Solution)
12. La requête retourne une liste des absences selon le même format que celle de la requête précédente mais contenant uniquement les absences ayant la chaîne de caractères "Famille" dans l'attribut Description de la table Motif.
    (Résultat | Solution)
13. La requête retourne la liste des employés avec, pour chacun, la somme des jours d'absence durant l'année 2009.
    (Résultat | Solution)
14. La requête retourne la liste des employés qui ont eu plus de quatre jours de congé de maladie en 2009.
    (Résultat | Solution)
15. La requête retourne la liste des employés qui ont pris au moins un jour de vacances en 2009.
    (Résultat | Solution)
16. La requête retourne l'identité du (ou des) employé(s) ayant cumulé la plus longue période d'absence en 2009.
    (Résultat | Solution)

Exercice 3: Requêtes SQL

Fraîchement engagé comme informaticien dans l'entreprise Northwind, une compagnie spécialisée dans la vente par correspondance, vous héritez de votre prédécesseur de la base de données Northwind.sql dont vous trouverez le schéma relationnel ci-dessous:

Votre patron vous demande de vous fournir différentes listes d'informations. Rédigez les requêtes SQL permettant d'obtenir les informations suivantes:

1. La requête retourne la liste de toutes les entreprises clientes françaises de Northwind triées par ordre alphabétique de leur ville de provenance. Cette liste contiendra le nom de l'entreprise, la ville et le code postal.
   (Résultat | Solution)
2. La requête retourne le prix moyen des articles vendus par Northwind.
   (Résultat | Solution)
3. La requête établit la liste des produits vendus par Northwind dont le prix unitaire est supérieur à 50. La liste contiendra le nom du produit, son prix unitaire ainsi que son prix unitaire majoré de 7.6% de TVA, le tout trié de manière décroissante par prix unitaire.
   (Résultat | Solution)
4. La requête détermine le nombre d'articles différents commandés dans la commande no. 11031.
   (Résultat | Solution)
5. La requête établit la liste des employés de Northwind en précisant, pour chacun et par ordre alphabétique croissant, leur nom, leur prénom, leur date de naissance et leur âge.
   (Résultat | Solution)
6. La requête établit la liste de tous les produits contenant dans leur nom la chaîne de caractères 'ch'. La liste mentionnera pour chacun de ces produits leur nom et leur prix unitaire.
   (Résultat | Solution)
7. La requête établit la liste des employés de Northwind (nom, prénom, date de naissance) nés dans les années 1950.
   (Résultat | Solution)
8. La requête établit la liste des produits (nom et quantité en stock) dont la quantité en stock est comprise entre 2 et 5 unités.
   (Résultat | Solution)
9. La requête établit la liste des pays dans lesquels Northwind a des clients, ordonnés par ordre alphabétique.
   (Résultat | Solution)
10. La requête établit la liste des fournisseurs (nom et pays) localisés en Suisse, France, Italie et Allemagne. Cette liste sera ordonnée par pays puis par nom du fournisseur.
    (Résultat | Solution)
11. La requête établit une liste indiquant pour chaque pays dans lesquels Northwind a des clients le nombre de ces derniers. La liste sera ordonnées par ordre alphabétique des pays.
    (Résultat | Solution)
12. La requête établit une liste indiquant le nombre de clients de Northwind par ville et pays. Cette liste sera ordonnées par ordre alphabétique des pays, puis des villes.
    (Résultat | Solution)
13. La requête établit une liste indiquant le nombre de clients de Northwind par pays contenant uniquement les pays dans lesquels Northwind dispose de plus d'un client. Les pays seront classés par ordre décroissant du nombre de clients.
    (Résultat | Solution)
14. La requête établit une liste indiquant le nombre de clients de Northwind par ville et pays. Cette liste ne contiendra que les villes dans lesquelles Northwind dispose de plus d'un client et sera ordonnées par ordre alphabétique des pays, puis les villes seront classées par ordre décroissant du nombre de clients.
    (Résultat | Solution)
15. La requête établit le prix unitaire moyen de chacun des groupes de produits de type 1, 5 et 8 (boissons, céréales, produits de la mer). Le groupe de produit n'apparaît dans la liste que si son prix moyen est inférieur ou égal à 30. La liste sera ordonnée par numéro de produit et moyenne décroissante des prix moyens.
    (Résultat | Solution)
16. La requête établit une liste des produits fournis par Northwind contenant le nom du produit et le nom de sa catégorie.
    (Résultat | Solution)
17. La requête établit une liste de toutes les commandes passées avec à chaque fois le nom correspondant du client ayant passé la commande. La liste sera ordonnée par ordre alphabétique du nom des clients ayant passé une commande.
    (Résultat | Solution)
18. La requête établit une liste de tous les articles commandés dans la commande no. 10847 en indiquant pour chaque article commandé son identifiant, son nom, le nom de sa catégorie et son prix.
    (Résultat | Solution)
19. La requête établit un tableau de correspondance de tous les clients ayant passé au moins une commande et du ou des entreprises de livraison (shippers) appelée(s) afin de procéder à la livraison. La liste des résultats contiendra deux colonnes: le nom du client et le nom de la compagnie de transport ayant déjà travaillé pour le client en question.
    (Résultat | Solution)
20. La requête retourne, sans jointure, le nom, l'adresse, le code postal, la ville et le pays du client ayant passé la commande no. 10251.
    (Résultat | Solution)
21. La requête établit la liste de tous les clients ayant passé au moins une commande.
    (Résultat | Solution)
22. La requête établit la liste de tous les clients n'ayant jamais commandé de produits de la mer, c'est-à-dire de produit de la catégorie seafood.
    (Résultat | Solution)

Exercice 4: Requêtes SQL

Dans le cadre du développement d'un nouveau logiciel de gestion de l'entreprise Northwind, vous êtes appelé à écrire des requêtes SQL modifiant les informations contenues dans la base de données de l'entreprise. Vous devez notamment rédiger les requêtes permettant d'effectuer les opérations suivantes:

1. Insérez dans la base de données un nouveau client dont les attributs sont les suivants:
   CustomerID:SICF; CompanyName:Sicilia Food; ContactName:Fungi Flavio; ContactTitle:Owner; Address:Via Stomboli 2; City:Cefalu; Region:Sicily; PostalCode:90015; Country:Italy; Phone:(+39) 0921 925011; Fax:(+39) 0921 420216.
   (Solution)
2. Rajoutez un nouveau transporteur répondant aux caractéristiques suivantes:
   CompanyName:Federal Express; Phone:(503) 800-463-333.
   (Solution)
3. Introduisez dans la base de données un nouveau fournisseur ayant les caractéristiques suivantes:
   CompanyName:Lyobalpine; Address:5, rue des Préalpes; City:Bulle; PostalCode:1630; Country:Switzerland.
   (Solution)
4. Complétez l'enregistrement du fournisseur Lyobalpine en y ajoutant les informations suivantes:
   ContactName:Patois Pierre; ContactTitle:Sales Manager; Region:Gruyère; Phone:026 135 34 63; Fax:026 135 34 64; HomePage:http://www.lyobalpine.ch.
   (Solution)
5. Augmentez d'une unité le seuil de réapprovisionnement (attribut ReorderLevel de la table Products) de tous les produits.
   (Solution)
6. Supprimez tous les enregistrements de la base de données Northwind rajoutés dans le cadre des points 1 à 4 précédents.
   (Solution)

Exercice 5

Dans le cadre du développement d'un logiciel de gestion des clients d'un fournisseur de livres, vous êtes appelé à écrire une interface graphique en Python capable de gérer la table Client de la base de données Librairie.sq3 et dont le résultat doit ressembler au modèle ci-dessous:

Les zones de texte doivent permettre à l'utilisateur d'entrer ou de récupérer les informations relatives à un client enregistré dans la base de donnée Librairie.sq3. Les boutons de gestion auront les rôles suivants:

• Le bouton Rechercher permettra d'afficher dans les zones de texte les données relatives à un client particulier. Pour que cet outil fonctionne, l'utilisateur devra rentrer dans les zones de texte quelques critères permettant d'identifier un seul client. Si les critères entrés sont trop vagues, l'application retournera un message d'erreur dans une fenêtre intempestive;
• Le bouton Ajouter permettra d'ajouter un client dans la base de données. Si l'utilisateur oublie d'entrer la valeur d'un attribut dans les zones de texte prévues à cet effet, l'application retournera un message d'erreur dans une fenêtre intempestive;
• Le bouton Actualiser permettra de mettre à jour un client dans la base de données. Pour ce faire, l'utilisateur devra dans un premier temps entreprendre une recherche lui permettant de visualiser les données du client à modifier. Une fois ces données affichées, l'utilisateur pourra les modifier directement dans les zones de texte et en appuyant sur le bouton Actualiser s'assurera que les modifications ont été réalisées dans la table;
• Le bouton Effacer permettra de détruire définitivement un client de la base de données. Pour ce faire, l'utilisateur devra dans un premier temps entreprendre une recherche lui permettant de visualiser les données du client à détruire. Une fois ces données affichées, l'utilisateur pourra détruire le client en cliquant sur le bouton Effacer;
• Le bouton Mise à zéro permettra à l'utilisateur d'effacer le contenu de toutes les zones de texte;
• Le bouton Aide permet d'afficher une fenêtre intempestive informant l'utilisateur sur le rôle de chaque bouton de l'application;
• Le bouton Quitter permettra de fermer l'application;

Réalisez cette application en vous aidant du fichier de test du programme et en suivant chacune des étapes suivantes à partir du squelette de code mis à votre disposition:

1. Implémentez la méthode gérant les événements du bouton Mise à zéro: Squelette | Solution
2. Implémentez la méthode gérant les événements du bouton Ajouter: Squelette | Solution
3. Implémentez la méthode gérant les événements du bouton Rechercher: Squelette | Solution
4. Implémentez la méthode gérant les événements du bouton Actualiser: Squelette | Solution
5. Implémentez la méthode gérant les événements du bouton Détruire: Squelette | Solution

Afficher la solution | Masquer la solution