Base de données et Requêtes depuis Excel
Présentation d'une Base de Données AS/400
ORGANISATION DE L'INFORMATION SUR UN AS/400.
L'ensemble espace de stockage (les disques magnétiques)
et espace de travail (la mémoire centrale)
est considéré dans l'AS/400 comme un tout, qui est appelé
Espace Adressable Unique.
Le système doit donc mettre en œuvre une organisation lui
permettant de retrouver chaque information :
c'est la notion d'OBJET.
|
L'OBJET
Définition : est appelé OBJET tout élément identifié, contenant
sa propre description, et manipulable par les commandes
du système.
ex: les PROGRAMMES, les FICHIERS
Identification : un OBJET est identifié par 3 éléments, dont la
combinaison sera UNIQUE dans le système
- son NOM donné par le créateur de l'OBJET
- son TYPE ex : *FILE (pour les FICHIERS)
*PGM (pour les PROGRAMMES)
*CMD (pour les COMMANDES)
etc...
- sa BIBLIOTHÈQUE de rattachement
|
LA BIBLIOTHÈQUE
Une BIBLIOTHÈQUE est un OBJET dont le contenu est une LISTE D'OBJETS.
On pourrait comparer une BIBLIOTHÈQUE au Répertoire des ouvrages
d'une bibliothèque de livres. Celui-ci répertorie tous les
ouvrages de la bibliothèque et indique l'endroit PHYSIQUE où
ils se trouvent.
TOUT OBJET EST RATTACHE A UNE BIBLIOTHÈQUE ET UNE SEULE.
|
..................................................................
: ......... :
: ......... :Prog P2:<--+ :
: +->:Prog P1: ......... :.......: ! :
: ! :.......: :Fich F3:<--+ ! :
: ......... ! :.......: ! ! :
: :Fich F2:<-----+ ! ! ! :
: :.......: ! ! ......... ! ! :
: ! ! :Fich F1:<------+ ! ! :
: ! ! :.......: ! ! ! :
: ! ! ! ! ! :
: BIBLIOTHEQUE B2 ! ! BIBLIOTHEQUE B1 ! ! ! :
: .............. ! ! .............. ! ! ! :
: :Obj Type Adr: ! ! :Obj Type Adr: ! ! ! :
: :--- ---- ---: ! ! :--- ---- ---: ! ! ! :
: :F2 FILE nnn:<-+ ! :F1 FILE nnn:<--+ ! ! :
: :P1 PGM nnn:<---+ :F3 FILE nnn:<--------+ ! :
: :............: :P2 PGM nnn:<----------------------+ :
: :............: :
:................................................................:
|
LE FICHIER
Les données sont stockées dans des Objets de type FICHIER (*FILE).
Dans l'AS/400 nous trouverons 2 types de Fichiers :
- le Fichier PHYSIQUE (aussi appelé TABLE)
- le Fichier LOGIQUE (aussi appelé VUE)
|
FICHIER PHYSIQUE
C'EST LE RÉSERVOIR DE DONNÉES
- ATTRIBUTS ----> Caractéristiques du Fichier, dont
le FORMAT des enregistrements
(plusieurs Formats peuvent être utilisés,
mais ce n'est pas le cas général)
- DONNÉES ----> Sont stockées dans des MEMBRES
(plusieurs Membres peuvent être utilisés, mais
un seul est obligatoire. En général il n'en
existe qu'un, du même nom que le Fichier)
|
Les données sont placées dans des ENREGISTREMENTS.
par ex. :
le Fichier Client contiendra autant d'enregistrements
que de Clients répertoriés.
Les Enregistrements sont composés de ZONES.
par ex. :
le Fichier Client contiendra, dans chaque Enregistrement, un
ensemble de Zones, dont au minimum :
un Code Client, un Nom, une Adresse, des Conditions de Règlement,
une Date de création, etc...
Dans la partie ATTRIBUTS de l'Objet Fichier, on trouvera le nom
d'un FORMAT contenant la description des ZONES.
|
Exemple de Fichier :
ZONES (ou Champs ou Colonnes)
|
_______________v___________________
| | | | |
ENREGISTREMENTS | | | | |
(ou Lignes) ....v........v........v........v......v.....
| : CODE : NOM : AGENCE :CATEG: DATE :
| :......:...........:........:.....:........:
| |-----> : 101 : DUBOIS : 01 : 07 : 930405 :
| |-----> : 107 : ERNST : 01 : 12 : 891215 :
| |-----> : 110 : DUPONT : 02 : 14 : 890405 :
| |-----> : 102 : MERCIER : 03 : 18 : 910302 :
+--->|-----> : 104 : BOZUFFI : 03 : 12 : 900302 :
|-----> : 121 : GARDEL : 01 : 17 : 921215 :
|-----> : 130 : FLAVARD : 02 : 09 : 920405 :
|-----> : 132 : GOUDE : 02 : 13 : 890405 :
|-----> : 103 : FORTRAN : 03 : 17 : 900302 :
|-----> : 105 : DUBROVNIK : 01 : 16 : 931215 :
:..........................................:
|
NORMALISATION DES FICHIERS
---------------------------
Il faut, tout d'abord effectuer un RECENSEMENT DES DONNEES
Ce travail a pour but d'aboutir à la rédaction d'un "dictionnaire"
où les données seront identifiées par :
+ un NOM
+ une DÉFINITION (en clair)
+ une STRUCTURE (alpha,num..)
+ un TYPE (calcul ,saisie,...)
+ une QUANTIFICATION
(nb de valeurs possibles)
+ la liste des valeurs admises
puis LA SYNTHÈSE DES DONNÉES afin d'établir un dictionnaire définitif
|
A ce stade il doit être possible de distinguer une clé permettant
d'identifier un élément (enregistrement)
CLÉ = ATTRIBUT ou ENSEMBLE PERMETTANT D'IDENTIFIER TOUS LES AUTRES
Clé PRIMAIRE = identifiant UNIQUE d'une ligne dans un fichier.
LES FORMES NORMALES (proposées par le modèle de "CODD") :
Celles ci nous imposent
==> PAS D'OCCURRENCE.
PAS DE REGROUPEMENT DE DONNÉES.
QUE TOUS LES ATTRIBUT DÉPENDENT DE LA CLÉ,
DE TOUTE LE CLÉ,UNIQUEMENT DE LA CLÉ
ceci nous amène parfois à multiplier les fichiers
|
Exemple:
--------
.impossible de placer les commandes dans le fichier client :
on ne peut pas prévoir le nombre de commandes, donc impossible de faire
| N° client |
nom du client |
commande 1 |
commande 2 |
commande 3 |
commande 4 |
etc...
|
.impossible de dupliquer les informations client
on ne peut pas faire :
(trop de risques d'erreurs si le client change de nom)
| N° client |
nom du client |
commande 1 |
| N° client |
nom du client |
commande 2 |
| N° client |
nom du client |
commande 3 |
nous sommes donc obligé de faire un fichier client,
| N° de client |
Nom du client |
Adresse |
puis un fichier commande dans lequel le n° de client (et uniquement
le n°)
nous permet d'établir un lien logique avec le fichier client
pour les mêmes raisons , le fichier commandes sera éclaté
en deux
|
Entête de commandes
(une commande par enregistrement)
| N° de commande |
N° de client |
DATE de commande |
DATE de livraison |
|
puis un fichier "détail de commandes"
| N° de commande |
N° de ligne |
Article commandé |
Quantité |
|
|
FICHIER LOGIQUE
Un Fichier Logique est un Objet de type *FILE.
Il possède une Description et (au moins) un FORMAT.
Il comprend (au moins) un MEMBRE.
MAIS CE MEMBRE NE CONTIENT PAS DE DONNÉES.
Il ne possède qu'un chemin d'accès permettant de retrouver les
données d'un ou de plusieurs Fichiers Physiques.
|
LE FICHIER LOGIQUE VA NOUS PERMETTRE :
- d'accéder dans un nouvel ordre aux Enregistrements du Fichier Physique
(ex. par Département dans le fichier Client, au lieu du Numéro Client)
- d'utiliser une autre description des Enregistrements, et notamment
de ne pas accéder à certaines Zones
(ex. ne pas voir la zone Salaire, ou Adresse, du fichier du Personnel)
- de regrouper les données de plusieurs Fichiers Physiques
(ex. voir dans un seul ensemble de données: les Commandes Clients, les
informations Clients et le détail des Articles commandés)
- de sélectionner une partie des Enregistrements d'1 ou de plusieurs
Fichiers Physiques, et donc de réduire le volume des données traitées
(ex. n'accéder qu'aux Clients de certaines Agences)
Ces fonctionnalités sont également accessibles avec des outils comme
MSQUERY ou SQL, mais l'accès aux informations par des Fichiers Logiques
améliore la confidentialité et les performances.
|
EXEMPLE:
FICHIER CLIENTS
!-- (noms de zone)
v
...........................................................
: NUMCLI : NOM : PRENOM : AGENCE : COD : DATE :
:........:.............:..........:........:.....:........:
: 101 : DUBOIS : Eric : 01 : 07 : 930405 :
: 107 : ERNST : Patrick : 01 : 12 : 891215 :
: 110 : DUPONT : Alain : 02 : 14 : 890405 :
: 102 : MERCIER : Anne : 03 : 18 : 910302 :
: 104 : BOZUFFI : Ricardo : 03 : 12 : 900302 :
: 121 : GARDEL : Sophie : 01 : 17 : 921215 :
: 130 : FLAVARD : Cecile : 02 : 09 : 920405 :
: 132 : GOUDE : Jean : 02 : 13 : 890405 :
: 103 : FORTRAN : Yves : 03 : 17 : 900302 :
: 105 : DUBROVNIK : Marie : 01 : 16 : 931215 :
:........:.............:..........:........:.....:........:
|
FICHIER AGENCES
.......................... : AGENCE : LIBAGE :
:........:...............:
: 01 : PARIS :
: 02 : TOULOUSE :
: 03 : LYON :
:........:...............: |
|
Les outils de Requête vont vous permettre de faire
------------------------------------------------
SELECTION Restriction de lignes
PROJECTION Restriction/réorganisation des colonnes
UNION Regroupement de deux relations (l'une après l'autre)
JONCTION Restriction sur le produit cartésien de X relations
SELECTION (clients de l'agence 01)
..................................................
: NUMCLI : NOM : PRENOM : AGENCE :
:........:..................:...........:........:
: 101 : DUBOIS : Eric : 01 :
: 107 : ERNST : Patrick : 01 :
: 121 : GARDEL : Sophie : 01 :
:........:..................:...........:........:
|
PROJECTION (nom,agence)
.............................
: NOM : AGENCE :
:...........................:
: DUBOIS : 01 :
: ERNST : 01 :
: DUPONT : 02 :
: MERCIER : 03 :
: BOZUFFI : 03 :
: GARDEL : 01 :
: FLAVARD : 02 :
: GOUDE : 02 :
: FORTRAN : 03 :
: DUBROVNIK : 02 :
:..................:........:
JONCTION (sur agence)
.........................................................
: NUMCLI : NOM : PRENOM : AGENCE : LIBAGE :
:........:............:..........:........:.............:
: 101 : DUBOIS : Eric : 01 : PARIS :
: 107 : ERNST : Patrick : 01 : PARIS :
: 110 : DUPONT : Alain : 02 : NANTES :
: 102 : MERCIER : Anne : 03 : LYON :
: 104 : BOZUFFI : Ricardo : 03 : LYON :
: 121 : GARDEL : Sophie : 01 : PARIS :
: 130 : FLAVARD : Cecile : 02 : NANTES :
: 132 : GOUDE : Jean : 02 : NANTES :
: 103 : FORTRAN : Yves : 03 : LYON :
: 105 : DUBROVNIK : Marie : 01 : PARIS :
:........:............:..........:........:.............:
|
Excel et MSQUERY
Accéder à des données externes
Pour accéder à des données AS/400 dans Excel :
puis choisissez une source de données ODBC :
- Si vous utilisez l'assistant :
- choisissez une table
- choisissez les colonnes de la table
- indiquez un filtre (pour ne pas voir toutes les lignes)
- puis un critère de tri :
- et retournez les données à Excel :
il vous reste à placer la plage de données dans la feuille Excel
:
- Pour des requêtes plus complexes, n'utilisez pas l'assistant :
vous verrez alors apparaître MSQUERY (le produit permettant de construire
des REQUÊTES)
celui-ci vous affiche la liste des tables
Il est FORTEMENT conseillé de désactiver l'option requête
automatique
ensuite choisissez vos colonnes en les glissant dans la partie basse
A tout moment vous pouvez exécuter la requête avec le bouton "
! "
Pour déplacer une colonne , sélectionnez la.
Puis, dans un deuxième temps, déplacez en glissant :
et voilà ! (les colonnes et les lignes peuvent aussi être redimensionnées).
Pour modifier l'entête d'une colonne :
double cliquez, puis 
si vous souhaitez sélectionner (filtre) les lignes, ajoutez un critère
:
indiquez un opérateur et une(des) valeur(s)
pour faire un ET (année 99 ET sujet = COBOL)
un OU (année 99 OU sujet = COBOL)
ATTENTION pour dire année 99 ou 97 et sujet = COBOL
ce qui s'écrit (année 99 ou 97) et (sujet = COBOL), vous devez
écrire
(année 99 et sujet = COBOL) OU (année 97 et sujet = COBOL)
Pour faire des totaux :
- nombre de cours par module
- nombre de client par département
- minima et maxima par région
- moyenne et sommes
placez d'abord les colonnes représentant un groupe
(dans notre exemple, nombre de cours par module, nous placons module)
puis placez la colonne utilisée dans les calculs (nombre de cours ==>
cours)
sélectionnez la colonne puis utilisez le bouton "Calcul automatique"
(la caractère SIGMA)
celui vous proposera successivement
- somme de (si le champs est numérique)
- moyenne de (toujours si le champs est numérique)
- nombre de (dans tous les cas)
- minima ou plus petite valeur rencontrée (dans tous les cas)
- maxima ou plus grande valeur rencontrée (dans tous les cas)
ici, nous avons le nombre de cours par module,
cette nouvelle colonne peut-être un critère :
Vous pouvez aussi demander directement des calculs, pour cela utilisez
+ , / , - , * (addition , division , soustraction , multiplication)
et (de manière facultative) les fonctions
- NOMBRE(colonne)
- SOMME(colonne)
- MOYENNE(colonne)
- MIN(colonne)
- MAX(colonne)
par exemple :
- qte * prix (si qte et prix sont des colonnes connues)
- somme(qte) * somme(prix)
- etc...
voilà :
Vous pouvez bien sur modifier l'en-tête de cette nouvelle colonne
Plusieurs fichiers, JONCTION.
---------------------------------
Choisissez table/Ajouter une table
la liste des tables apparaît , utilisez le bouton Ajouter
si les colonnes permettant de faire le lien ont le même nom, les tables
sont automatiquement jointes
sinon, faites glisser la colonne clé d'un fichier vers la colonne servant
de lien, dans le deuxième .
la type jointure est paramétrable (en cliquant sur le trait liant les
deux fichiers)
- Jointure interne
les modules ayant des cours
les clients ayant des commandes, etc ...
- Jointure externe
tous les modules de cours qu'il contiennent des cours ou non
tous les clients qu'ils aient passé commande ou non, etc...
- la même chose mais en inversant les fichiers
ici (tous les cours, même d'un module non référencé):
La jointure vous permet de voir des colonnes des deux fichiers
(elle nous permet d'aller "chercher" le texte associé à
chaque module de cours)
ce qui est fait ci-dessous
vous pouvez associer plus de deux tables
(en fait 32, mais attention aux temps de réponse de votre AS/400)
ATTENTION , plus de 2 fichiers UNIQUEMENT avec des jointures INTERNES
Copyright © 1995,2000 VOLUBIS