Université de Carthage Ecole Supérieure de la Statistique et de l’Analyse de l’Information Travaux Pratiques Module : Conception des Systèmes d’Information « MERISE » Utilisation d’un AGL Power AMC ® Interrogation d’une base de données sous Oracle 10g Préparé par : Fatma CHAKER KHARRAT ® PowerAMC est une marque déposée de SYBASE TP N°1 Initiation à POWER AMC Ce TP consiste en une remise en page des Didacticiels Power AMC 12.1 proposés par Sybase http://sybooks.sybase.com/onlinebooks/group-pd/amc0611f Description de la société d’édition Vous allez effectuer ce didacticiel à l’aide d’un MCD qui représente de façon conceptuelle le système d’informations d’une société d’édition. Au cours de ce didacticiel, vous serez amené à résoudre trois problèmes de gestion distincts : Droits d’auteur pour plusieurs auteurs Vous devez être en mesure d’établir en permanence le pourcentage de droits d’auteur revenant à chacun des auteurs d’un même ouvrage Portrait et biographie des auteurs Vous devez identifier et conserver le portrait des auteurs ainsi que leur biographie afin de les utiliser sur la couverture des ouvrages Catégories d’ouvrage Vous devez classer chaque ouvrage dans l’une des catégories disponibles (périodique et non périodique) et conserver les informations s’y rapportant Sur la base de ces besoins en gestion, vous allez enrichir le modèle du didacticiel afin de pouvoir exploiter des informations supplémentaires. Pour effectuer le reste de ce didacticiel, vous devez ouvrir le fichier fourni. Ce fichier est copié dans le sous-répertoire Exemples\Didacticiel de votre répertoire PowerAMC lors de l’installation. Créer un nouveau répertoire propre à vous et enregistrer le MCD de didacticiel dans un nouveau fichier « DIDACTICIEL.MCD ». Définition des règles de gestion, de domaines et d’informations A propos des règles de gestion Crée par F. CHAKER 2 Année Universitaire : 2010-2011 Une règle de gestion est une expression qui décrit la façon dont une société fonctionne. Il existe plusieurs types de règle de gestion : faits, définitions, formules et validations : Type de règle de gestion Exemple Besoin Le modèle est conçu de telle manière que les pertes totales ne dépassent pas 10% du volume total des ventes Contrainte La date de commercialisation doit être postérieure à la date de fin d’impression Définition Un auteur est identifié par un nom et une adresse Fait Un éditeur peut publier un ou plusieurs titres Formule Le montant des droits d’auteur constitue un pourcentage sur les ventes et augmente en fonction de celles-ci Validation Le pourcentage cumulé des droits d’auteur reversés à chaque auteur d’un ouvrage doit représenter 100% des droits d’auteur Création d’une règle de gestion Vous allez créer une règle de gestion qui définit les modalités de répartition des droits entre les auteurs. 1. Sélectionnez Modèle Règles de gestion dans la barre de menus. La boîte de dialogue Liste des règles de gestion affiche les règles existantes. 2. Saisissez Pourcentage auteur dans une nouvelle colonne Nom. Il s’agit du nom de la règle de gestion. Un code identique au nom s’affiche automatiquement dans la colonne Code. 3. Sélectionnez Validation dans la liste déroulante de la colonne Type de règle. 4- Double-cliquez sur la flèche au début de la ligne de la règle créee. La feuille de propriétés de la nouvelle règle de gestion s’affiche. Crée par F. CHAKER 3 Année Universitaire : 2010-2011 8. Cliquez sur l’onglet Notes. La page Notes s’affiche et fait apparaître la zone Description. 9. Saisissez « La somme des pourcentages de droits versés à chacun des auteurs doit être égale à 100% des droits d’auteur » dans la zone Description. Ce texte décrit la règle de gestion. 10. Cliquez sur OK dans les boîtes de dialogue successives. Vous validez ainsi la création de la règle de gestion. Création d’un domaine Vous allez créer deux domaines qui définissent un type de données standardisé pour les sommes d’argent et les pourcentages dans le domaine. 1. Sélectionnez Modèle Domaines dans la barre de menus. La boîte de dialogue Liste des domaines affiche les domaines existants. 2. Cliquez sur l’outil Ajouter une ligne . Une flèche apparaît au début de la première ligne vide. Un nom et un code de domaine s’affichent par défaut. 3. Saisissez Montant dans la colonne Nom. Il s’agit du nom du domaine. Un code identique au nom s’affiche automatiquement dans la colonne Code. 4. Cliquez sur le bouton Appliquer situé dans la partie inférieure de la boîte de dialogue. La création du nouveau domaine est validée. 5. Cliquez sur le nouveau domaine. Une flèche s’affiche au début de la ligne. 6. Cliquez sur l’outil Propriétés ou double-cliquez sur la flèche au début de la ligne. La feuille de propriétés du nouveau domaine s’affiche. 7. Cliquez sur le bouton Point d’interrogation situé en regard de la zone Type de données. Crée par F. CHAKER 4 Année Universitaire : 2010-2011 La boîte de dialogue Types de données standard s’affiche. Utilisez cette boîte de dialogue pour spécifier le format des données auxquelles le domaine est affecté. 8. Cliquez sur le bouton radio Monnaie. Ce type de données permet de stocker des nombres à décimale fixe. Par la suite, lorsque vous appliquez ce domaine aux informations utilisées pour stocker les montants, ces dernières héritent du type de données que vous avez défini. 9. Saisissez 8 dans la zone Longueur. Le nombre de chiffres d’une information à laquelle ce domaine est affecté sera 8. Saisissez 2 dans la zone Précision. Une information à laquelle ce domaine est affecté peut comporter deux décimales. 10. Cliquez sur OK. Vous revenez à la feuille de propriétés du domaine. La valeur MN8,2 apparaît dans la colonne Type de données. MN est le code du type de données monétaire. 8 indique que la somme peut comporter huit chiffres et 2 indique qu’elle peut comporter deux décimales. 12. Cliquez sur OK. Vous revenez à la Liste des domaines. 13. Vous allez procéder de la même manière pour créer un nouveau domaine « Pourcentage » de type Entier court. Création d’une information La création d’une information peut se faire de deux manières : En créant un dictionnaire des données contenant toutes les informations du MCD et ensuite affecter ces informations aux entités et associations concernées. En créant les informations directement lors de la création des entités et associations concernées. Pour gérer des ouvrages rédigés par plusieurs auteurs, vous allez créer des informations qui représentent le pourcentage de droits de chaque auteur (Pourcentage TitreAuteur ) ainsi que la place du nom de l’auteur dans la liste des auteurs de l’ouvrage (Ordre TitreAuteur). 1. Sélectionnez Modèle Informations dans la barre de menus. La boîte de dialogue Liste des informations affiche les informations existantes. 2. Saisissez Pourcentage TitreAuteur dans la colonne Nom d’une nouvelle ligne. 3. Cliquez sur le bouton Appliquer situé dans la partie inférieure de la boîte de dialogue. La création de la nouvelle information est validée. 5. Double-cliquez sur la flèche au début de la ligne créee. La feuille de propriétés de la nouvelle information s’affiche. Crée par F. CHAKER 5 Année Universitaire : 2010-2011 6. Sélectionnez Pourcentage dans la liste déroulante Domaine située dans la partie inférieure de la boîte de dialogue. 8. Vous allez procéder de la même manière pour créer l’information Ordre TitreAuteur. Vous attribuez le type Entier. Affectation d’un domaine à une information Vous allez affecter le domaine MONTANT à toutes les informations qui stockent des sommes d’argent et le domaine POURCENTAGE à toutes celles qui stockent des pourcentages. 1. Sélectionnez Modèle Informations dans la barre de menus. La boîte de dialogue Liste des informations s’affiche. 2. Cliquez sur le numéro de l’information Avance auteur. Maintenez la touche ctrl enfoncée et cliquez sur les informations Montant droits d’auteur, Montant vente et Prix titre. 3. sélectionnez MONTANT dans la liste déroulante Domaine. Montant s’affiche automatiquement dans la colonne Domaine de toutes les informations sélectionnées précédemment. 6. Cliquez sur le numéro de l’information Pourcentage remise et sélectionnez POURCENTAGE dans la liste déroulante Domaine. 7. Cliquez sur OK. Définition d’entités Création d’une entité Vous allez créer une entité qui contient des données relatives aux portrait et deux entités qui différencient les catégories d’ouvrage, à savoir périodique et non-périodique. 1. Cliquez sur l’outil Entité dans la palette d’outils. 2. Cliquez sur un emplacement vide dans le diagramme. Un symbole d’entité s’affiche à l’endroit où vous avez cliqué. 3. Cliquez sur l’outil Pointeur dans la palette d’outils. 4. Double-cliquez sur le symbole de l’entité que vous venez de créer. La feuille de propriétés de l’entité s’affiche. 5. Saisissez Portrait dans la zone Nom. Il s’agit du nom de l’entité. Un code identique au nom s’affiche automatiquement dans la zone Code 6. Cliquez sur OK. La nouvelle entité s’affiche avec le nom Portrait. Vous avez créé cette entité en commençant par son symbole, puis en l’identifiant dans sa feuille de propriétés. Vous pouvez également créer des entités dans la liste des entités. Crée par F. CHAKER 6 Année Universitaire : 2010-2011 7. Sélectionnez Modèle Entités dans la barre de menus. La boîte de dialogue Liste des entités affiche la liste des entités définies. 8. Cliquez sur l’outil Ajouter une ligne. 9. Saisissez Périodique dans la colonne Nom. Le code est automatiquement défini avec la même chaîne que le nom. 10. Cliquez sur Appliquer. La création de la nouvelle entité est validée. 11. De la même manière vous allez créer l’entité Non périodique 14. Cliquez sur OK. Les nouvelles entités s’affichent dans le MCD. Définition d’attributs Vous allez définir des attributs pour les entités PORTRAIT, PERIODIQUE, NON PERIODIQUE et l’association ECRIT en affectant plusieurs informations à chacune d’entre elles. Pour créer ces attributs d’entité, vous allez : Ajouter des informations dans l’entité ou l’association concernée Créer un nouvel attribut d’entité Ajout d’informations à une entité et à une relation Vous allez affecter des informations existantes aux entités PORTRAIT, PERIODIQUE et NON PERIODIQUE et à la relation ECRIT. 1. Double-cliquez sur la relation ECRIT. La feuille de propriétés de cette relation s’affiche. 2. Cliquez sur l’onglet Attributs. La boîte de dialogue Attributs s’affiche. La liste est vide car cette entité ne contient pas encore d’attribut. 3. Cliquez sur le bouton Ajouter des informations . Une boîte de Sélection s’affiche. Elle répertorie toutes les informations disponibles dans le modèle. 4. Cliquez sur l’en-tête de colonne Code. Les informations sont triées alphabétiquement par code. 5. Cochez la case Ordre TitreAuteur. Cochez la case Pourcentage TitreAuteur. 6. Cliquez sur OK. Les informations s’affichent dans la liste des attributs de la relation « Ecrit ». 7. Cliquez sur OK. 8. Répétez les étapes 1 à 7 en les appliquant aux entités suivantes : Entité Sélectionnez les informations PORTRAIT Photo Crée par F. CHAKER 7 Année Universitaire : 2010-2011 Référence photo PERIODIQUE Format périodique Périodicité NON PERIODIQUE Collection ouvrage Création d’un attribut d’entité Vous allez ajouter un nouvel attribut à l’entité Auteur nommé Biographie. Affecter le domaine LONG_NOTES dans la zone de liste Domaine. Le type de données Texte (TXT) apparaît dans la colonne Type de données. Saisissez BIO_AUTEUR dans la colonne Code Définition d’un identifiant Vous allez définir Référence photo comme identifiant de l’entité PORTRAIT. Application d’une règle de gestion à une entité Vous allez appliquez la règle de gestion Pourcentage auteur à l’association ECRIT pour rappeler le rôle de cette association dans le calcul des droits d’auteur. 1. Double cliquez sur l’association ECRIT. La feuille de propriétés de l’association s’affiche. 2. Cliquez sur l’onglet Règles. La page Règles s’affiche. Elle répertorie les règles de gestion attachées à l’entité. Cette liste est vide. 3. Cliquez sur l’outil Ajouter des objets Une boîte de sélection s’affiche. Elle répertorie les règles de gestion disponibles. 4. Cliquez sur Pourcentage auteur. Vous sélectionnez ainsi la règle de gestion Pourcentage auteur. 5. Cliquez sur OK. Vous revenez à la page Règles. Pourcentage auteur s’affiche dans la liste. Définition de relations Création d’une relation Vous allez créer une relation entre les entités AUTEUR et PORTRAIT nommée AVOIR. Un auteur peut avoir plusieurs portraits et un portrait est défini pour un seul auteur. Définition d’un héritage Création d’un lien d’héritage Vous allez définir un héritage entre l’entité TITRE et les entités PERIODIQUE et NON PERIODIQUE. 1. Sélectionnez l’outil Héritage dans la palette d’outils. Crée par F. CHAKER 8 Année Universitaire : 2010-2011 2. Cliquez sur l’entité NON PERIODIQUE, puis tout en maintenant le bouton gauche de la souris enfoncé, faites glisser le curseur sur l’entité TITRE. Relâchez le bouton de la souris sur l’entité TITRE. Un lien d’héritage s’affiche entre les deux entités. Ce lien est doté d’un demi-cercle en son milieu ainsi que d’une pointe de flèche qui désigne l’entité père TITRE. NON PERIODIQUE est l’entité enfant. L’entité enfant hérite de son père. 3. Cliquez sur le demi-cercle, puis tout en maintenant le bouton gauche de la souris enfoncé, faites glisser le curseur sur l’entité PERIODIQUE. Relâchez le bouton de la souris sur l’entité PERIODIQUE. Les symboles se présentent comme suit. Définition des propriétés de l’héritage Vous allez attribuer un nom à l’héritage, le rendre mutuellement exclusif et définir son mode de génération. 1. Cliquez sur l’outil Pointeur dans la palette d’outils. 2. Double-cliquez sur le demi-cercle au milieu du lien d’héritage. La feuille de propriétés de l’héritage s’affiche. 3. Saisissez Héritage titre dans la zone Nom. Il s’agit du nom de l’héritage. Un code identique au nom s’affiche automatiquement dans la zone Code. 4. Cochez la case Enfants mutuellement exclusifs. Vous indiquez ainsi que l’héritage est mutuellement exclusif et qu’un titre peut être soit périodique soit non périodique, mais jamais les deux à la fois. Crée par F. CHAKER 9 Année Universitaire : 2010-2011 5. Cliquez sur l’onglet Génération. La page Génération s’affiche. 6. Décochez la case Générer les enfants dans la zone de groupe Mode de génération. Les choix que vous effectuez dans cette zone indiquent la façon dont l’héritage sera généré dans le Modèle Physique de Données (MPD). Le fait de ne sélectionner que Générer le parent permet de faire en sorte qu’une seule table soit créée pour tous les titres. Vous devez alors spécifier un attribut discriminant pour différencier les occurrences de chaque enfant. 7. Saisissez Périodique dans la colonne Nom de la zone de groupe Attributs discriminants. Il s’agit du nom de l’attribut discriminant. Un code identique au nom s’affiche automatiquement dans la colonne Code. Cet attribut discriminant permettra de créer une colonne nommée Périodique dans la table TITRE qui résultera de la génération. Cette colonne indiquera si un titre est périodique ou non. 8. Cliquez dans la colonne Type de données. Une liste déroulante s’affiche. Crée par F. CHAKER 10 Année Universitaire : 2010-2011 9. Sélectionnez BL dans la liste déroulante. Vous définissez ainsi un type de données Booléen (BL) pour l’attribut discriminant car celui-ci n’admet que deux valeurs : périodique ou non. 10. Faites défiler la liste vers la droit jusqu’à ce que la colonne O soit visible. Lorsque cette case est cochée, elle indique que toutes les occurrences de l’attribut discriminant requièrent une valeur booléenne. 11. Cochez la case O. 12. Cliquez sur OK. Une croix apparaît dans le demi-cercle pour indiquer que l’héritage est mutuellement exclusif. Organisation de l’affichage Ajout d’un cartouche de titre Vous allez ajouter un cartouche de titre dans le modèle. Le cartouche de titre contient les informations essentielles relatives au modèle. 1. Cliquez sur l’outil Titre dans la palette. 2. Cliquez sur un espace vide dans le diagramme. Un cartouche de titre s’affiche dans le diagramme. 3. Pointez sur le cartouche de titre et cliquez le bouton droit de la souris. 4. Sélectionnez Ombre dans le menu contextuel. Une ombre apparaît sous le cartouche de titre. Vous pouvez appliquer ce type d’ombre à n’importe quel symbole du modèle. Vous allez personnaliser votre cartouche en insérant vos données personnelles (auteur, version, changer de couleur, etc.) Crée par F. CHAKER 11 Année Universitaire : 2010-2011 Génération du MPD à partir du MCD Ce qui se passe lorsque vous générez un MPD La génération d’un MPD s’effectue en fonction d’un Système de Gestion de Base de Données (SGBD) particulier. Avant de générer le MPD, vous devez sélectionner le SGBD, ou base de données cible. PowerAMC convertit les types de données spécifiés dans le MCD en types de données physiques pris en charge par la base de données cible. La correspondance entre les types de données conceptuels et physiques est définie dans un fichier DEF. Il existe un fichier DEF pour chaque type de base de données cible. Génération du MPD 1. Sélectionnez Outils Générer un modèle physique de données dans la barre de menus. La boîte de dialogue Options de génération d’un MPD s’affiche. 2. Sélectionnez Sybase AS Anywhere 8 dans la liste déroulante SGBD. La boîte de dialogue propose DIDACTICIEL comme nom par défaut pour le fichier MPD. C’est le même nom de fichier que pour le MCD, seule l’extension diffère. 3. Sélectionnez le bouton radio Partage. Vous indiquez ainsi que vous souhaitez utiliser le fichier de définition de SGBD stocké dans la bibliothèque de SGBD. 4. Cliquez sur OK. La Liste de résultats s’affiche et répertorie les messages d’avertissement concernant la réutilisation d’informations par plusieurs entités dans le MCD. Ces messages sont informatifs et n’entravent nullement la génération du MPD. 5. Fermez la fenêtre Liste de résultats une fois la génération achevée. Le MPD s’affiche dans la fenêtre du modèle. MPD trop chargé Si votre MPD est difficile à consulter car ses tables contiennent trop d’informations, vous pouvez réduire la quantité d’informations affichées. Pour ce faire, sélectionnez Outils Préférences d’affichage dans la barre de menus et décochez les cases appropriées. Enregistrement et fermeture du MPD Vous allez enregistrer le MPD généré sous un nouveau nom DIDACGEN.MPD. Reverse Engineering Le reverse engineering est le processus qui consiste à générer un MPD à partir d’une structure de base de données existante. Le MPD peut être généré à partir du script de création d’une base de données, ou bien à l’aide d’une source de données ODBC. Crée par F. CHAKER 12 Année Universitaire : 2010-2011 Le reverse engineering est fréquemment utilisé pour assurer la maintenance d’une base de données existante ou pour la modifier. Maintenant, vous allez effectuer le reverse engineering d’un script de création pour générer le MPD de didacticiel. Création d’un nouveau MPD Vous allez créer un nouveau MPD. Chaque fois que vous créez un nouveau MPD, vous devez choisir un système de gestion de base de données (SGBD). 1. Sélectionnez Fichier Nouveau dans la barre de menus La boîte de dialogue Nouveau s’affiche à la page Général. Elle contient la liste des types de modèle que vous pouvez créer dans PowerAMC. 2. Sélectionnez Modèle Physique de Données dans la liste Type de modèle. 3. Sélectionnez le bouton radio Nouveau modèle dans la partie supérieure droite de la boîte de dialogue. 4. Sélectionnez Sybase AS Anywhere 9 dans la liste déroulante SGBD, dans la page Général. 5. Sélectionnez le bouton radio Partager le SGBD. Vous allez utiliser la définition du SGBD fournie avec PowerAMC. 6. Sélectionnez Diagramme Physique dans la liste déroulante Premier diagramme. 7. Cliquez sur OK. Une fenêtre de diagramme vide s’affiche et le noeud correspondant apparaît dans l’Explorateur d’objets. Reverse engineering du MPD de didacticiel Vous allez procéder au reverse engineering du MPD de didacticiel à partir d’un fichier de script de création de base de données. Un script de création contient les instructions SQL nécessaires pour créer tous les objets d’une base de données. Le MPD présente sous forme graphique tous les objets indiqués dans le script de création. 1. Sélectionnez SGBD Reverse engineering dans la barre de menus. La boîte de dialogue Reverse engineering d’une base de données s’affiche à la page Sélection. 2. Cliquez sur le bouton radio A l’aide d’un fichier script dans la page Sélection. 3. Sélectionnez le fichier MPDAVANT.SQL dans le répertoire Exemples\Didacticiel de PowerAMC, en utilisant l’outil Ajouter un fichier de la barre d’outils si nécessaire. Si d’autres fichiers que MPDAVANT.SQL sont répertoriés dans la liste déroulante supprimez-les à l’aide de l’outil Supprimer un fichier. Crée par F. CHAKER 13 Année Universitaire : 2010-2011 4. Cliquez sur OK pour démarrer le reverse engineering. La fenêtre Résultats ancrée en bas de la fenêtre principale affiche la progression du reverse engineering. Une fois le reverse engineering terminé, le MPD généré s’affiche dans la fenêtre de diagramme. Affichage de la fenêtre Résultats La fenêtre Résultats est généralement affichée par défaut. Si tels n’est pas le cas, vous pouvez l’afficher en sélectionnant Vue Résultats dans la barre de menus. Crée par F. CHAKER 14 Année Universitaire : 2010-2011 TP N°2 Création d’un MCD et génération du MPD Exercice 1 : Soit le MCD relatif à la gestion des animaux : Travail demandé : 1- Construire ce MCD en utilisant Power AMC en se basant sur les informations suivantes : Créer un Domaine nommé « Quantité » de type numérique (longueur=8 ; précision=2). Affecter ce domaine à toutes les variables désignant des quantités. Créer la règle de gestion de type « Définition » suivante : Chaque animal est caractérisé par son numéro, son nom et son age. Affecter cette règle de gestion à l’entité « Animal ». Ajouter un titre à votre MCD 2- Générer le MPD relatif à ce SI. Crée par F. CHAKER 15 Année Universitaire : 2010-2011 Exercice 2 : Gestion d’un ZOO Synthèse des entretiens avec le directeur du zoo : Chaque animal qui est accueilli reçoit un nom de baptême qui sert à le repérer par rapport aux autres animaux de son espèce. Un nom ne peut pas être réutilisé pour un animal de la même espèce, même après le décès de l’animal qui portait ce nom. Avec l’aide d’un expert, on identifie son espèce : On aura donc : LEO le lion d’Afrique TITI le canari Mais aussi : TITI le ouistiti Quand il y a des travaux dans le zoo, on est amené à loger certains animaux d’espèces différentes dans le même enclos durant une période (on précise la date de début et de fin). Il faut donc connaître les espèces qui peuvent cohabiter ainsi que l’adéquation entre l’espèce et l’enclos. En ce qui concerne la nourriture, chaque animal reçoit un repas conformément à un menu type, spécifique de son espèce. Les menus types sont en fait un aliment composé qui est défini par une proportion de protides, glucides et de lipides. Pour chaque menu est spécifiée la quantité recommandée d’aliment composé à distribuer. On note enfin à chaque repas la date et l’heure des repas ainsi que la quantité effectivement distribuée. Travail demandé : 1- Construire le MCD relatif à ce SI 2- Générer le MPD correspondant. NOTE : Enrichir votre modélisation en ajoutant toutes les notions vues durant la première séance de TP. Crée par F. CHAKER 16 Année Universitaire : 2010-2011 TP N°3 Reverse Engeneering – Exécution de script sous Oracle 10gImportation de données –Exécution de requête SQL 1. On vous donne le script de création de votre base de données « script_App03.sql ». Par rétroconception, générer le MPD (Utiliser Oracle 10g comme SGBD) ainsi que le MCD relatif à ce script 2. Créer un compte Utilisateur de base de données Oracle 10g. Pour cela, il faut suivre les étapes suivantes : Accéder à la page d’accueil de la base de données (Démarrer Tous les programmes Oracle Database 10g Express Edition page d’accueil de la base de données). Une fenêtre s’affiche demandant le nom d’utilisateur et le mot de passe. Saisir les données suivantes : a. Username: system. b. Password: Saisir le mot de passe que vous avez crée lors de l’installation de Oracle (ESSAI) c. Cliquer Login. La page d’accueil de la BD Oracle XE apparaît. Crée par F. CHAKER 17 Année Universitaire : 2010-2011 Créer votre propre compte (un nouvel utilisateur BD). Pour cela : o Cliquer sur l’icône Administration, ensuite cliquer sur utilisateurs de bases de données. o Donner un nom à votre propre compte et cliquez sur le bouton « Créer » o Une fenêtre s’affiche. Saisir un login et un mot de passe et cocher toutes les informations nécessaires. NB : Noter ces informations quelques part pour ne pas les oublier. o Déconnecter (Bouton « Déconnexion » dans le coin supérieur droit de la page) et reconnecter avec votre propre login et mot de passe que vous venez de créer. 3. Exécuter le script SQL sous Oracle 10g : Dans la page d’accueil Oracle sélectionner sur SQL Scripts SQL. Une page s’affiche contenant tous les scripts existants dans votre Compte. Cliquez télécharger vers l’amont. Vous allez récupérer le script que vous avez créé sous PowerAMC et cliquer de nouveau le Crée par F. CHAKER 18 Année Universitaire : 2010-2011 bouton télécharger vers l’amont. le fichier .SQL s’affichera dans la fenêtre. Cliquez sur ce script pour afficher son contenu. Cliquez sur le bouton « Exécuter » pour exécuter le script de création de la base. Pour afficher le résultat de la création cliquer sur le bouton « Afficher le résultat ». Une fois le script correctement exécutée toutes les tables de la BD sont générées. 4. Insertion des données dans la base : pour cela nous allons utiliser 3 méthodes : Importation des données : On vous propose les fichiers de données relatifs à cette base de données (.csv). Importer ces données dans votre base Exécution du script : On vous propose deux fichiers contenant le script relatif à l’insertion des données dans les tables « COMMANDE » et « DETAIL_COMMANDE ». Exécuter ces scripts sous SQL. Insertion des données par les commandes SQL : En utilisant les commandes SQL, insérez les données suivantes dans les tables concernées NB : Attention au respect des contraintes d’intégrités Commande Num Com 9 Date NumCl Commande Transporteur Notes 7 17/09/2002 1 rien Urgent 0 Détail_commande Num Commande 7 8 9 9 Réference Article TE20 IN00 IN00 PH10 Remise 10 15 15 10 Quantite 2 2 1 5 5. Exprimer les requêtes suivantes en SQL : Partie I : Requêtes de Sélection Requête 1 : Afficher à l’écran la liste des clients habitant la ville de Sousse (Numéro client, Raison sociale, Rue, Code Postal, Numéro de Commande, Date de commande) Requête 2 : Afficher à l’écran la liste des clients Mademoiselles ou Docteurs (Raison sociale, Ville, Rue, Code Postal, Téléphone) Crée par F. CHAKER 19 Année Universitaire : 2010-2011 Requête 3 : Afficher à l’écran la liste des commandes entre les dates 01/06/2002 et 30/10/2002 (Numéro client, Ville, Raison sociale, Rue, Numéro de Commande, Date de commande, Urgent) Requête 4: Afficher à l’écran la liste des clients dont les noms se terminent par la chaîne INFO (Numéro client, Ville, Raison sociale, Numéro de Commande, Date de commande) Requête 5 : Afficher à l’écran la liste des clients dont la 2ième lettre du nom est E (Numéro client, Raison sociale, Rue, Ville) Requête 6 : Afficher à l’écran la liste des articles qui n’ont jamais été commandées. (Référence article, Désignation, Prix unitaire) Requête 7 : Afficher à l’écran la liste des noms des produits commandés par les clients dont le nom commence par la lettre M (Numéro Client, Raison sociale, Numéro de Commande, Référence article, Désignation) Requête 8 : Afficher à l’écran le nombre de commandes passées par chaque client. (Numéro Client, Raison Sociale, Nombre de commandes) Requête 9 : Afficher à l’écran la quantité totale commandée dans chaque commande (Numéro commande, Quantité totale) Requête 11 : Afficher à l’écran la liste des articles qui ont fait l’objet de plus de 2 commandes (numéro de l’article, désignation, nombre de fois commandées). Partie II : Requêtes de Mise a jour Requête 1 : On a augmenté la quantité de 10 pour chacun des produits de catégorie Softwa Requête 2 : Tous les produits qui ont un prix de revient < 150 leur prix unitaire sera majoré de 10%. Requête 4 : Tous les produits de la commande passée par le client MEDIAPLUS ont une remise de 2% seulement Crée par F. CHAKER 20 Année Universitaire : 2010-2011 Annexe : Données de la base « App03 » Catalogue REFERENCE AL20 AL25 AL27 CO10 CO12 IN00 IN10 PH10 PH20 TE12 TE20 CATEGORIE Softwa Softwa Softwa Hardwa Hardwa Hardwa Hardwa Hardwa Hardwa Softwa Bureau DESIGNATION PRIX_REVIENT PRIX_UNITAIRE Windows XP 175 215 Compilateur C 50 70 Windows 2000 150 185 Disquette 0,5 0,6 Graveur 70 90 Ecran 200 230 Scanner 125 150 Imprimante 210 245 Lecteur CCD 50 75 AutoCad 500 650 Table Ordinateur 120 160 Client INTERL NUMC RAISON_SO OCUTE L CIALE UR 1 MEDIAPLUS Madame SPEED Mademois 2 INFO elle Mademois 3 INFOWEB elle 4 AFAK Madame ELECTROIN Mademois 5 FO elle 6 INFOBUR Madame MEDIAINF 7 O Docteur 8 INFOPLUS Monsieur 9 INFONET Madame RUE Ibn Sina Mohame dV Ben Alaya Liberte Mongi Slim Ibn Sina Ibn El Oualid El Nour El Bahri CODE _POST TELEPH ALE ONE VILLE 4002 73222102 sousse 1002 2013 4004 71986569 tunis Ben 71365214 Arous 73235641 sousse 3000 4007 73452101 monastir 73215467 sousse 1004 4004 5005 71254101 tunis 73214509 sousse 74211413 sfax DATE_CREAT ION_DOSSIER 0 0 NBRE_E MPLOYE 0 0 0 0 0 0 0 0 0 Commande NUMCOM NUMCL DATE_COMMANDE TRANSPORTEUR NOTES URGENT 1 1 10/12/2002 1 rien 1 2 5 09/10/2002 2 rien 0 3 3 09/11/2002 3 Tr¿Urgent 1 4 6 09/12/2002 2 rien 1 8 2 10/09/2002 1 Tres Urgent 1 5 9 13/09/2002 3 rien 0 6 4 14/09/2009 2 rien 1 7 8 30/11/2009 3 rien 0 Detail_commande REFERENCE NUMCOM REMISE QUANTITE CO10 5 5 200 Crée par F. CHAKER 21 Année Universitaire : 2010-2011 0 0 0 0 0 0 0 AL27 AL20 AL25 IN00 TE20 AL25 AL27 PH10 Crée par F. CHAKER 6 1 1 1 2 3 4 5 10 10 7 15 8 10 10 10 2 2 5 10 1 5 2 5 22 Année Universitaire : 2010-2011 TP N°4 : Gestion d’un ZOO Construire le MCD suivant Modèl e Conceptuel de Données Modèle : MCD Zoo Package : Di agramme : Diagramme_1 Auteur : ESSAI Date: 08/04/2009 Version: V 1.0 est de type Animal famill e num ani mal <pi > Caractère (10) <O> nom Caractère (30) Age Numérique race <pi> Caractère (30) <O> ori gine <pi> Caractère (30) <O> ami Booléen 1,1 0,n Identifiant_1 <pi> Identi fiant_1 <pi > 1,1 0,n 0,2 parent enfant Menu mange enfant de 0,n num_M <pi> Caractère (20) <O> qté viande Numéri que qté légume Numéri que Identi fiant_1 <pi> Travail demandé : 1- Génération du MPD correspondant. Sélectionner votre SGBD Oracle 10g et lancer la génération. NB : Une fois généré, apportez les modifications suivantes à votre MPD : Dans la table ANIMAL réorganisez les attributs selon l’ordre suivant : Num_animal, Nom, Age, Race, Origine, Num_M Dans la table ENFANT_DE modifiez les attributs clés par : enfant et parent 2- Génération d’un script SQL « zoo.sql » (Générer Script). 3- Exécution du script sous Oracle 10g. 4- Importation des données : Les fichiers : « Animal.csv », « famille.csv », « enfant.csv » et « menu.csv » contiennent une instance de la base zoo (Voir contenu en annexe). Vous allez importer ces données dans votre base. 5- Insertion de nouvelles lignes : Crée par F. CHAKER 23 Année Universitaire : 2010-2011 Vous allez insérer les données suivantes en utilisant les commandes SQL (INSERT). Attention : Il faut respecter les règles d’intégrité référentielle lors de l’insertion des données !!!! Table FAMILLE FAMILLE RACE ORIGINE AMI SERPENT AFRIQUE 0 SERPENT ASIE 0 SINGE EUROPE -1 Table ANIMAL ANIMAL N_A NOM AGE RACE ORIGINE N_Menu 7A KAA 2 SERPENT AFRIQUE 6M 8A YSENGRIN 4 RENARD EUROPE 6M 9A CHANTECLER 2 COQ EUROPE 8M Table MENU MENU N_M QTE_Viande QTE_legume 6M 20 0 7M 0 10 8M 0 5 Table ENFANT_DE ENFANT enfant parent 4A 6A 6- Exécuter les requêtes suivantes en SQL NB : N’oubliez pas d’enregistrer chaque requête. 1. Liste de tous les noms d'animaux (projection). 2. N­uplet(s) ayant le NOM « KAA » dans la table « ANIMAL » (sélection). 3. Origines distinctes de toutes les familles. Attention, Access distingue les deux options mutuellement exclusives « DISTINCT » et « DISTINCTROW ». Pour pouvoir manipuler ces options, il faut ouvrir la fenêtre des propriétés de la requête : soit par l'icône de la barre d'outils, soit par le menu contextuel (bouton droit souris). Puis, il faut cliquer sur la partie haute de la fenêtre de requête. 4. Noms des lions de plus de 4 ans. 5. Noms de tous les lions, ainsi que le nom des animaux qui ont au plus 4 ans. Crée par F. CHAKER 24 Année Universitaire : 2010-2011 6. Nom des animaux qui ont plus de 4 ans et moins de 10 ans 7. Menus plus riches en viande qu'en légumes 8. Nom des animaux qui ont une attitude amicale envers l'homme. 9. Numéro de menu et quantité de viande pour les menus des animaux qui ont une attitude amicale envers l'homme. 10. La relation ENFANT définit, pour chaque animal son ou ses ascendants directs. Lister l'ensemble des numéros d'animaux qui sont grand­parents. 11. Quantité de viande totale pour tous les menus. 12. Nombre d'enfants pour chaque numéro d'animal. 13. Liste des numéros d'animaux dont on apparaît les deux parents. 14. Nombre d'origines différentes. 15. Numéros des Menus plus riches en viande que la moyenne. Annexe : ANIMAL N_A 1A 2A 3A 4A 5A 6A 7A 8A 9A NOM AGE HECTOR 3 JACKO 20 GUSTAVE 20 JOSETTE 15 MARTIN 8 HONORE 25 KAA 2 YSENGRIN 4 CHANTECLER 2 RACE ORIGINE N_Menu LION AFRIQUE 5M SINGE EUROPE 7M LION AFRIQUE 5M LION AFRIQUE 5M OURS AMERIQUE 1M LION AFRIQUE 5M SERPENT AFRIQUE 6M RENARD EUROPE 6M COQ EUROPE 8M ENFANT enfant parent 1A 3A 1A 4A 4A 6A FAMILLE RACE COQ ORIGINE AMI EUROPE -1 LION AFRIQUE OURS AMERIQUE OURS EUROPE PANDA CHINE RENARD EUROPE SERPENT AFRIQUE Crée par F. CHAKER 0 0 0 0 0 0 25 Année Universitaire : 2010-2011 FAMILLE RACE ORIGINE AMI SERPENT ASIE 0 SINGE EUROPE -1 NB : -1 : Oui 0 : Non MENU N_M QTE_Viande QTE_legume 1M 10 20 4M 20 0 5M 100 0 6M 20 0 7M 0 10 8M 0 5 Crée par F. CHAKER 26 Année Universitaire : 2010-2011 Mini projet Modélisation et Conception des Systèmes d’Information MERISE Objectif : Ce projet a pour but de familiariser l’étudiant avec les concepts de base de la méthode de conception Merise en utilisant l’outil Power AMC ainsi que l’acquisition des commandes de base du SGBD Oracle. Travail demandé : Plus précisément, les points étudiés dans ce projet sont : 1- Modélisation du Système d’Information (MCD) 2- Génération du MPD 3- Génération du script SQL de création de la base de données. 4- Mise en œuvre et utilisation des fonctionnalités suivantes : Insertion des données significatives, Formulation des requêtes, Evaluation A la date prévue pour les soutenances, chaque groupe aura a présenté son projet. L’évaluation portera sur : - Le travail réalisé (M CD, MPD, script de création de la base, requête, etc.) - Le soin apporté dans la lisibilité des modèles ainsi que les résultats obtenus, - Les travaux ne comportant pas la totalité des éléments requis n'auront pas la moyenne, Crée par F. CHAKER 27 Année Universitaire : 2010-2011 Appartements- Ecole Soit le schéma relationnel suivant : IMMEUBLE (ADI, NBETAGES, DATEC, PROP) APPIM (ADI*, NAPR, OCCUP, TYPE, SUPER, ETAGE) PERSONNE (NOM, AGE, PROF, ADI*, NAPR*) ÉCOLE (NOMEC, ADEC, NBCLASSES, DIR*) CLASSE (NOMEC*, NCL, MAITRE*, NBEL) ENFANT (NOMP*, PRENOM, AN, NOMEC*, NCL*) Avec la signification suivante : 1. Relation IMMEUBLE ADI : adresse d’immeuble, clé ; on fait l’hypothèse pour simplifier, que l’adresse identifie de manière unique un immeuble BETAGES : nombre d’étages d’un immeuble DATEC : date de construction PROP : nom du propriétaire de l’immeuble qui est une personne 2. Relation APPIM (Appartement) ADI : adresse d’immeuble NAPR : numéro d’appartement OCCUP : occupant de l’appartement (nom de la personne) TYPE : type de l’appartement (Studio, F2, . . .) SUPER : superficie de l’appartement ETAGE : étage où se situe l’appartement 3. Relation PERSONNE NOM : nom de personne, clé ; on fait l’hypothèse pour simplifier, que ce nom est unique sur l’ensemble des personnes que l’on considère dans la base AGE : âge de la personne PROF : profession de la personne (directeur d’école n’est pas une profession) ADR : adresse de la résidence d’une personne, il s’agit d’un immeuble NAPR : numéro d’appartement 4. Relation ÉCOLE Crée par F. CHAKER 28 Année Universitaire : 2010-2011 NOMEC : nom d’une école. On suppose que deux écoles ne peuvent pas avoir le même nom. ADEC : adresse d’une école NBCLASSES : nombre de classes DIR : nom du directeur 5. Relation CLASSE NOMEC : nom d’une école NCL : nom de la classe, e.g., CP1, CE2, CE3, etc... MAITRE : nom de l’instituteur NBEL : nombre d’élèves dans la classe 6. Relation ENFANT NOMP : nom de la personne responsable de l’enfant, clé e.g., père, mère etc... PRENOM : prénom de l’enfant AN : année de naissance NOMEC : nom d’une école NCL : nom de la classe La relation IMMEUBLE décrit un ensemble d’immeubles. Chaque immeuble a un propriétaire. La relation APPIM décrit pour chaque immeuble l’ensemble des appartements qui le compose. Chaque appartement peut héberger plusieurs personnes mais il y en a une qui est responsable (par exemple le locataire) et qui est désignée par le constituant OCCUP. Si l’appartement est inoccupé, ce constituant prend la valeur NULL. La relation PERSONNE décrit un ensemble de personnes. ADR et NAPR représentent l’adresse où réside une personne. Une personne peut avoir plusieurs enfants décrits par la relation ENFANT. Pour simplifier, on ne considère que les enfants allant à l’école primaire. Les écoles et les classes sont décrites dans les relations ÉCOLE et CLASSE. Toutes les relations doivent vérifier les contraintes d’intégrité de base. Par exemple, une personne répertoriée dans la relation CLASSE en tant que maître exerce bien la profession d’instituteur dans la relation PERSONNE. De même, un directeur d’école est un instituteur. Travail demandé : 1- En utilisant Power AMC, construire le MCD relatif à ce schéma relationnel. 2- Générer le MPD ainsi que le script SQL de création de la base (Utiliser Oracle 10g comme SGBD). Crée par F. CHAKER 29 Année Universitaire : 2010-2011 3- Exécuter votre script sous Oracle afin de créer les différentes tables de votre base de données. NB : Avant d’exécuter le script, vous pouvez éventuellement le modifier en cas de besoin pour qu’il soit conforme à la réalité modélisée. 4- On vous propose quelques enregistrements relatifs aux tables : Immeuble A01 A02 A03 A04 A05 A06 A07 11 13 10 8 14 4 10 Appartement A01 A01 A03 A03 A03 A05 A05 A06 1968 Ben Ayed 1970 Kilani 1995 Ben Ayed 1969 Ben Ayed 1969 Hammami 1990 Kallel 1990 Ben Abdallah 1 NULL Ben 3 Mustapha 1 Lagha 2 NULL 4 Ayadi 2 Hammami 3 NULL 1 NULL F4 200 2 Studio Studio Studio F2 F3 F3 F2 90 90 85 100 150 150 100 10 1 1 2 1 3 1 Personne Ben Ayed Kallel Kilani Hammami Ben Mustapha Lagha Ayadi 38 60 39 35 Informaticien Instituteur Instituteur Instituteur 50 Instituteur 25 Instituteur 36 Instituteur A03 A05 A03 A05 1 2 4 2 A01 A03 A03 3 1 4 Pour chaque table, créer un script SQL contenant le code SQL d’insertion des données dans les tables respectives. Exécuter ensuite chacun de ces scripts. 5- Pour les tables restantes : Ecole E01 E02 E03 Tunis Tunis Sousse 5 Lagha 6 Kallel 10 Hammami Crée par F. CHAKER 30 Année Universitaire : 2010-2011 Classe E01 E01 E02 CP1 CP2 CE1 E02 E03 CP1 CP2 Enfant Lagha Lagha Ben Ayed Ben Ayed Kilani Kilani Ali Amira Salima Omar Sami Yosr Ayadi Ayadi Kilani Ben Mustapha Hammami 1998 2000 2000 2002 2000 1998 30 25 31 29 30 E02 E02 E01 E03 E01 E01 CE1 CP1 CP2 CP2 CP2 CP1 On vous propose les fichiers « .csv » contenant les données de chaque table. Importez ces données dans votre base. NB : Veuillez respecter l’ordre d’importation des tables. 6- Exprimez les requêtes suivantes en SQL : * Requêtes de sélection : Requête 1 : Donner l’adresse des immeubles ayant plus de 10 étages et construits avant 1970. Requête 2 : Donner les noms des personnes qui habitent dans un immeuble dont ils sont propriétaires (occupants et habitants). Requête 3 : Donner les noms des personnes qui ne sont pas propriétaires. Requête 4 : Donner les adresses des immeubles possédés par des informaticiens dont l’âge est inférieur à 40 ans. Requête 5 : Donner la liste des occupants (nom, âge, profession) des immeubles possédés par Mr Ben AYED. Requête 6 : Donner le nom et la profession des propriétaires d’immeubles où il y a des appartements vides. Requête 7 : Donner les noms des maîtres qui habitent dans le même immeuble (à la même adresse) qu’au moins un de leurs élèves (on suppose que les enfants vivent sous le même toit que leur responsable). Requête 8 : Donner l’adresse de l’immeuble, la date de construction, le type d’appartement et l’étage où habitent chacun des maîtres des enfants de Mr Ben AYED. Requête 9 : Donner le nom et l’âge des maîtres qui habitent dans un immeuble dont le propriétaire est responsable d’un de leurs élèves. Crée par F. CHAKER 31 Année Universitaire : 2010-2011 Requête 10 : Donner le nom et l’âge des personnes qui sont propriétaires mais qui ne sont ni maître ni directeur d’école (des classes et écoles répertoriées dans ECOLE et CLASSE). * Requêtes de mise à jour : Requête 11 : Ajouter un enfant de nom np, de prénom e, né en a et l’inscrire à la classe c de l’école ec. Crée par F. CHAKER 32 Année Universitaire : 2010-2011