Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

USMBA ESTF FILIERE: GESTION LOGISTIQUE ET TRANSPORT (S2) TP SGBDR Access Créer

USMBA ESTF FILIERE: GESTION LOGISTIQUE ET TRANSPORT (S2) TP SGBDR Access Créer une base de données, Trier Filtrer et Rechercher, Requêtes, Requêtes SQL, Les formulaires et les états, Les Macros, Programmation VBA Enseignant : M.HMAMOU [Année universitaire: 2019-2020] [Travaux Pratiques et problèmes sur les Bases de Données] EST Fès Filière: Gestion Logistique et Transport (Semestre 2) 2 Sommaire Partie 1 : Créer une base de données, Créer les tables, Saisir les données, Créer des relations 3 Partie 2 : Trier, Filtrer et Rechercher. 5 Partie 3 : Requêtes SQL. 6 Partie 4 : Les requêtes 7 Partie 5 : Les formulaires et les états 8 Partie 6 : Les Macros 10 EST Fès Filière: Gestion Logistique et Transport (Semestre 2) 3 Partie 1 : Créer une base de données, Créer les tables, Saisir les données, Créer des relations On se propose de créer une base de données relationnelle concernant la gestion des employés d’une société. Supposons que l’administrateur de la base de données a construit un modèle (Schéma relationnel normalisé) à partir des informations relatives aux employés d’une société organisées en plusieurs services. Le modèle est le suivant : EMPLOYE(noemp,nomemp,preemp,poste, grade, supr, datemp, noservice) SERVICE(noservice,nomservice) SALAIRE(grade,salmin,salmax) Explication des attributs : Attribut Signification Type Longueur Remarque Noemp Numéro de l’employé numérique Octet Clé primaire emp Nomemp Nom de l’employé Texte 20 Preemp Prénom de l’employé Texte 15 Poste Poste occupé par l’employé Texte 30 Grade Grade de l’employé numérique Octet Clé primaire de salaire Supr Supérieure hiérarchique numérique Octet Peut être null Datemp Date de l’embauche Date jj/mm/aa Date Noservice Numéro de service de l’employé numérique Octet Clé primaire du service Nomservice Nom du service Texte 15 Salmin Salaire minimum pour un grade donné numérique Réel simple Salmax Salaire maximum pour un grade numérique Réel simple EST Fès Filière: Gestion Logistique et Transport (Semestre 2) 4 Travail à réaliser : 1. Créer les tables SERVICE, SALAIRE et EMPLOYE en mode création 2. Saisir les données ci-dessous dans les tables suivantes : SALAIRE : Table SERVICE : Table EMPLOYE : Table 3. Mettre en forme les données saisies dans les tables : Largeur de colonnes, Police et Quadrillage. 4. Insérer un champ Titre entre les champs noemp et nomemp. 5. Ajouter une liste déroulante pour la colonne Titre dont le contenu est : Mme;Mlle;Mr 6. Mettre en relation les tables EMPLOYE, SERVICE et SALAIRE EST Fès Filière: Gestion Logistique et Transport (Semestre 2) 5 Partie 2 : Trier, Filtrer et Rechercher. Utiliser la base de donner créée précédemment, contenant les tables : EMPLOYE, SERVICE ET SALAIRE Travail à réaliser : ➢ TRI 1. Afficher la table employée triée par ordre décroissant de poste. 2. Afficher la table employée triée par ordre croissant de titre et nom d’employé 3. Afficher la table employée triée par ordre croissant de numéro. ➢ FILTRE 4. Afficher la liste des employés de sexe féminin qui travaillent dans le service 10 5. Afficher la liste des employés de grade >=4 du service 20 ou 30 6. Afficher la liste des directeurs et ceux qui travaillent dans le service 20 7. Afficher la liste des grades pour les salaires compris entre 9000 et 16000. ➢ Recherche 8. Rechercher les employés ayant le nom « ALAMI » 9. Rechercher les employés dont le nom commence par la lettre B. 10. Remplacer le nom de l’employé « BENSLIMANE » par « SOULAIMANI » EST Fès Filière: Gestion Logistique et Transport (Semestre 2) 6 Partie 3 : Requêtes SQL. Utiliser la base de données créée précédemment, contenant les tables : EMPLOYE, SERVICE, SALAIRE. Travail à réaliser : ➢ RECHERCHE 1. Afficher le nom et poste des employés ayant un grade supérieur ou égale à 4 par ordre alphabétique croissant. 2. Afficher le nom et le salaire minimal des employés ayant un grade supérieur ou égal à 4. 3. Afficher la liste des employés qui occupe le même poste que « BENAYAD ». 4. Afficher la liste des employés qui ont au moins un supérieur. 5. Quels sont les employés dont le nom commence par B? 6. Quels sont les employés directeurs, ou commerciaux et travaillants dans le service 10? 7. Quels sont les employés directeurs ou commerciaux, et travaillants dans le service 10? 8. Donner le nombre d’employés pour chaque grade>=4 9. Donner le nombre d’employé supérieur ou égal à 4 par grade. 10. Donner le salaire moyen de chaque service ➢ Mise à jour 11. Créer une table emp2 contenant les attributs : noemp, nomemp, salmin et nomservice. 12. Augmenter de 10% le salaire de tous les employés du service ‘Informatique’ dans la table emp2. 13. Insérer dans la table employé le tuple : (17, ‘Mr’,’Ziate’,’Mohamed’,’Maintenance’,4,5,#11/11/2009#,20). 14. Insérer dans la table service le tuple :(10,’comptabilité’ ) 15. Attention, Qu’est ce qui se passe ? 16. Insérer dans la table service le tuple : (50,’Comptabilité’). 17. Supprimer de la table Service le tuple N° 50. EST Fès Filière: Gestion Logistique et Transport (Semestre 2) 7 Partie 4 : Les requêtes en mode création Travail à réaliser : 1. Afficher la liste des employés (nom et prénom) classés par ordre alphabétique croissant 2. Afficher la liste des employés directeurs (nom et prénom) classés par ordre alphabétique croissant 3. Afficher la liste des employés (nom, prénom, poste et grade) directeurs et ceux qui ont le grade <4. 4. Afficher les employés (nom, prénom, poste et date d’embauche) ayant commencé dans la société après le 01/02/2009. 5. Afficher la liste des employés (nom, prénom, nom de service et salaire minimal) ayant un salaire minimal<15000 par ordre croissant du salaire minimal. 6. Afficher la liste des employés (nom, prénom et service) par poste (paramètre saisi au clavier). 7. Afficher sous forme monétaire, le salaire moyen de chaque employé de grade >=4. 8. Calculer et afficher sous forme monétaire le salaire total moyen de tous les employés. 9. Donner pour chaque service, le nombre d’employés, le salaire minimum et le salaire maximum. 10. Donner le nombre d’employés par poste. 11. Créer une table emp(noemp,nomemp,poste, grade) contenant la liste des employés ayant le grade <=3. 12. Supprimer de la table emp les employés de poste concepteur. 13. Augmenter dans la table emp le grade des employés (grade+1) qui ont travaillé avant le 01/02/2009. 14. Supprimer la table emp. EST Fès Filière: Gestion Logistique et Transport (Semestre 2) 8 Partie 5 : Les formulaires et les états Les formulaires facilitent la saisie car ils utilisent les contrôles Windows (zone de texte, listes déroulantes, cases à cocher, groupes d'options, etc). Les états permettent d'imprimer les données. Ils autorisent des regroupements et des calculs irréalisables avec les formulaires. Travail à réaliser : 1. Créer une nouvelle Base de données Facturation contenant le schéma relationnel suivant : Produit (codep , libelle , quants , prix ) Codep : Code du produit , Numérique (entier) Libelle : Libellé du produit , texte (50) Quants : Quantité en stock , Numérique (octet) Prix : Prix unitaire hors taxe , Numérique (réel simple) Facture ( refact , refcli , nomcli , datefact) Refact : n° de facture , Numérique (entier) Refcli : n° du client , Numérique (entier) Nomcli : nom du client , Texte (50) Datefact : Date d’établissement de la facture : Date Ligne (refact , codep , quantach ) Quantach : quantité achetée , Numérique (octet) 2. créer un formulaire à l’aide de l’assistant pour saisir les enregistrements dans la table Produit (voir figure1). 3. Ecrire une requête SQL pour calculer le montant hors taxe de chaque ligne de détail facture. Afficher le code produit, le libellé, le prix, la quantité achetée et le totalht (= prix*quantach). 4. Ecrire une requête SQL pour calculer le montant total à payer par facture. 5. créer un formulaire (avec sous formulaire) pour enregistrer les informations de factures dans les tables concernées. Le formulaire comprend : le numéro de la facture, la date, la référence du client et le nom du client. Le sous formulaire comprend : le code du produit, son libellé, la quantité achetée et le prix unitaire. 6. Modifier le formulaire crée dans la question 5 pour ajouter l'entête de la société, la date du jour et le montant total à payer hors taxe et TTC (TVA = 20%) voir figure2. 7. Créer un état pour afficher une facture dont le numéro est saisi au clavier. 8. Créer un état pour afficher la liste des produits et leurs nombre. EST Fès Filière: Gestion Logistique et Transport (Semestre 2) 9 Figure 1 Figure 2 EST Fès Filière: Gestion Logistique et Transport (Semestre 2) 10 Partie 6 : Les Macros Travail à réaliser : 1. Créer une nouvelle macro (macro1) permettant d’ouvrir le formulaire contenant la liste des factures. 2. Créer une macro (macro2) contenant plusieurs actions : - ouvrir la table produit en lecture seule - déplacer/ dimensionner (15cm droite, bas, hauteur et largeur) - afficher une boîte de message pour fermer la table produit. - Fermer la table produit 3. Appel d’une macro dans un formulaire : Lors de la saisie d’un produit uploads/s1/ glt-s2-m7-3-sgbdr-access-tp-hmamou.pdf