Bases de données - Geo

Bases de données
Access, Mysql et autres
Systèmes de Gestion de Base de
Données Relationnelle (=SGBDR)
(=DBMS)
GVdK = CUY
Sommaire
– Base de données : définition et utilisation
– Pourquoi pas Excel ?
– BdD : éviter la redondance
– Langage SQL et requêtes
GVdK = CUY
Définition
• Base de données
– Un ensemble de données
– Une structuration forte
– Une gestion de plusieurs utilisateurs
simultanés
– Un ensemble d’outils permettant d’extraire
rapidement des informations de l’ensemble
des données
Ensemble structuré de données enregistrées avec le minimum de
redondances pour satisfaire simultanément plusieurs utilisateurs de
façon sélective et en un temps opportun.
GVdK = CUY
Utilisation
Voir article « Quel
SGBD choisir »
– Access
– Paradox
– Sybase
– SQL Server
– Oracle
– MySQL (libre
avec PHP)
– InterBase
– PostGreSQL
– SQLite
GVdK = CUY
Bases de données omniprésentes
• Les bases de données sont omniprésentes :
– Hautes Écoles, Universités :
• Données sur les étudiants
– Id, nom, prénom, classe, section, cycle, année…
• Données sur les formations
– matière, intervenant (enseignant), public, salle
• Données sur les résultats
– matière, intervenant (enseignant), pondération, résultat
– Entreprises
• fichiers clients, fournisseurs, commandes
• facturation,
• gestion de stock, inventaire.
GVdK = CUY
Bases de données omniprésentes
• Les bases de données sont omniprésentes :
– Bibliothèques :
• Données sur les lecteurs
– Id, nom, prénom, statistiques, montant dû…
• Données sur les livres
– Titre, ISBN, auteur, genre, code, emplacement, emprunts,
collection, éditeurs
• Données sur les auteurs
– Livres, adresse, nom, prénom,
– Privé
• Collection de CD ou DVD
• Contenu de congélateur
•…
GVdK = CUY
Pourquoi pas Excel ? (1)
• Et c’est parti…
j’attends ma première commande…
GVdK = CUY
Pourquoi pas Excel ? (2)
• Premier client, première commande…
GVdK = CUY
Pourquoi pas Excel ? (3)
• Dans sa commande, mon client désire
plusieurs produits …
GVdK = CUY
Pourquoi pas Excel ? (4)
• Chiffre d’affaires pour les tomates ?
GVdK = CUY
Pourquoi pas Excel ? (5)
• Les défauts :
– Redondance :
• Données répétées (info client si plusieurs commandes, info
produit si plusieurs fois commandé…)
– Réécriture
• Orthographe, perte de temps, mise à jour difficile
– Nombre de lignes
• 65535 = 216 – 1
– Mélange de divers types de données
• Client, produit, commande, prix, …
• gestion de stock, inventaire.
GVdK = CUY
Base de données (1)
• Exemple :
un carnet d’adresses
organise les informations sur vos
interlocuteurs
en plusieurs catégories :
nom, numéro de téléphone fixe, gsm, fax,
adresse, e-mail, etc.
Une base de données est un ensemble organisé d’informations
structurées
GVdK = CUY
Base de données (2)
Nom
Prénom
Adresse Tél
Fax
E-mail
Nom1
Nom2
…
…
…
…
…
…
…
…
…
…
Une table contient
des enregistrements en lignes, correspondant
aux individus (animés ou inanimés)
et des champs en colonnes,
décrivant chaque individu
GVdK = CUY
Éviter la redondance (1)
Nom
Prénom
Classe
Matières
Durand
Pierre
1NSSE
Compta1
Durand
Pierre
2NSSE
Compta2
Dupond
Paul
1NSSE
Compta1
Dupond
Paul
1NSSE
Droit1
Durand
Pierre
1NSSE
Droit1
Dupond
Paul
1NSSE
Stat
Durand
Pierre
1NSSE
Stat
Nom
Prénom
Classe
Classe
Matières
Durand
Pierre
1NSSE
1NSSE
Compta1
Durand
Pierre
2NSSE
2NSSE
Compta2
Dupond
Paul
1NSSE
1NSSE
Compta1
Dupond
Paul
1NSSE
1NSSE
Droit1
Durand
Pierre
1NSSE
1NSSE
Droit1
Dupond
Paul
1NSSE
1NSSE
Stat
Pierre
1NSSE
1NSSE
Stat
Durand
GVdK = CUY
Éviter la redondance (2)
Nom
Prénom
Classe
Matières
Durand
Pierre
1NSSE
Compta1
Durand
Pierre
2NSSE
Compta2
Dupond
Paul
1NSSE
Compta1
Dupond
Paul
1NSSE
Droit1
Durand
Pierre
1NSSE
Droit1
Dupond
Paul
1NSSE
Stat
Durand
Pierre
1NSSE
Stat
Nom
Prénom
Classe
Durand
Pierre
1NSSE
Durand
Pierre
2NSSE
Dupond
Paul
1NSSE
n
n
Classe
Matières
1NSSE
Compta1
2NSSE
Compta2
1NSSE
Droit1
1NSSE
Stat
Attention :
Un élève peut suivre plusieurs cours
GVdK = CUY
un même cours peut être suivi par plusieurs élèves
À éviter…
Éviter la redondance (3)
Client
Produit
NoClient
RaisonSociale
Adresse1
Ref prod
Descript
Puht
C987
Dupont & Co
12 rue Par..
P1234567
Tomate
10
C986
Durand & Cie
71 cbru
B9873685
Banane
4
…
…
….
C9875986
Poire
180
…
…
…
n
n
Attention :
Un client peut acheter plusieurs produits
GVdK = CUY
un même produit peut être acheté par plusieurs clients
À éviter…
Redondance (4), relation
fonctionnelle
Client
Produit
NoClient
Ref prod
RaisonSociale
Descript
Adresse1
Puht
…
TxTVA
…
1
…
…
Acceptable…
Commande
Comprendre
.
n
NoComm
# NoComm
DateCommande
# Ref Prod
# NoClient
Quantité
…
Attention :
Un client peut passer plusieurs commandes
Mais :
Une commande ne peut être passée que par un seul client
GVdK = CUY
Redondance (4), relation
fonctionnelle
Client
Produit
NoClient
Ref prod
RaisonSociale
Descript
Adresse1
Puht
…
TxTVA
…
…
…
1
Commande
n
NoComm
Acceptable…
1
n
Comprendre
.
(ou Ligne de commande)
# NoComm
DateCommande
# Ref Prod
# NoClient
Quantité
…
Attention : Une commande peut comprendre plusieurs couples ‘commandeproduit’ (lignes de commandes) pour des quantités différentes
Mais : Un couple ‘commande-produit’ ou une ligne de commande ne
peut être compris(e) qu’une fois que dans une seule commande
GVdK = CUY
Redondance (4), relation
fonctionnelle
Client
Produit
NoClient
Ref prod
RaisonSociale
Descript
Adresse1
Puht
…
TxTVA
…
1
…
…
Acceptable…
Commande
Comprendre
.
n
NoComm
1
n
# NoComm
DateCommande
# Ref Prod
# NoClient
Quantité
…
Attention : Un produit ne peut être compris que dans un seul couple
‘commande-produit’ pour une quantité précise
Et : Un couple ‘commande-produit’ ne peut comprendre qu’un seul
produit
GVdK = CUY
1
1
Données saisies ou calculées
saisies
GVdK = CUY
calculées
Données : tables
L’analyse de la liste des données saisies
permet de mettre en évidence des
ensembles homogènes.
Exemples :
- données concernant les clients
- données concernant les produits
Chacun de ces ensembles est représenté
par une table.
Exemples : - la table « client »
- la table « produit »
GVdK = CUY
7
Table : sa structure
Une table se compose:
- de colonnes où sont indiqués les différents attributs
- de lignes où sont rangées les valeurs des différents attributs.
L’ensemble des valeurs d’une ligne est appelé un enregistrement.
DÉPENDANCE FONCTIONNELLE
La connaissance d’une valeur de l’attribut
clé primaire détermine de façon unique la
connaissance des valeurs des autres
attributs,
on dit qu’il existe entre
dépendance fonctionnelle.
eux
une
Exemple : la connaissance du numéro de
client nous permet de retrouver son nom et
son adresse.
GVdK = CUY
Un attribut principal appelé clé primaire doit permettre
d’identifier chaque enregistrement sans ambiguïté.
Les valeurs de l’attribut clé primaire doivent donc
obligatoirement être uniques.
Pour la table client, « Numcli » répond le mieux à ce
critère.
La clé primaire est généralement située en première
colonne.
8
Table : modèle relationnel (1.1)
• Entités
– attributs (caractéristiques) : description
– ex. : clients, produits, élèves, citoyens belges, motos
belges ...
– Clé primaire unique
• Relations
– relation entre entités
– ex. : commandes (clients, produits),
proprio (moto, citoyen)
conjoint (citoyen1, citoyen2)
GVdK = CUY
Table : modèle relationnel (1.2)
CLIENT ( NumCli,
CLIENT
Nomcli,
AdresseCPcli, Adressevillecli)
Adresseruecli,
Nom de la relation/entité (en majuscules) suivi de
parenthèses
Attributs séparés par des virgules
Clé primaire en premier lieu et soulignée :
unique.
GVdK = CUY
8
Base : modèle relationnel entre deux
tables (2.1)
La connaissance d’un numéro de
commande nous permet de retrouver de
façon unique un numéro de client.
Dépendance
fonctionnelle
Il existe donc une dépendance fonctionnelle
entre deux tables (commande et client).
Pour matérialiser ce lien, une colonne (numcli)
sera ajoutée à la table source de la dépendance
fonctionnelle.
Celle-ci sera appelée clé étrangère.
n
1
Attribut commun aux
deux tables
matérialisant la
dépendance
fonctionnelle.
GVdK = CUY
108
Base : modèle relationnel entre deux
tables (2.2)
n
1
Attribut commun aux
deux tables
matérialisant la
dépendance
fonctionnelle.
CLIENT ( NumCli, Nomcli, Adresseruecli, AdresseCPcli, Adressevillecli)
COMMANDE ( NumCom, datecom, # NumCli )
Si la relation comprend un attribut clé étrangère, celui-ci sera placé à la fin et
précédé du signe #.
GVdK = CUY
108
Table : modèle relationnel entre
deux tables (2.3)
• Ce qui est nécessaire :
– 2 tables (ou requêtes ou une combinaison des deux)
– 1 champ en commun dans chacune des tables
– même type de champ (texte avec texte, numérique
avec numérique ...)
– même longueur (pas un champ long de 15 caractères
avec un autre long de 50 caractères !)
– même genre d'information (Ex.: Code d'inventaire
avec des codes d'inventaires, NAS avec NAS ...)
• Et vive les clés primaires et étrangères !!!
GVdK = CUY
Table : modèle relationnel entre
deux tables (2.4)
– La table Fournisseurs
contient toutes les
informations de contact au
sujet de ces entreprises
– Quant à la table Produits, elle fait référence à
la table Fournisseurs, mais elle ne doit pas
forcément inclure toutes les informations de
contact de tous les fournisseurs.
GVdK = CUY
Base : modèle relationnel entre plusieurs
tables (1)
Dépendance
fonctionnelle
composée
La connaissance du numéro de commande et de
la référence du produit nous permet de retrouver
de façon unique la quantité commandée.
Il existe donc une dépendance fonctionnelle
multiple ou composée à partir des tables
« PRODUIT » et « COMMANDE ».
Pour matérialiser ce double lien, une table
supplémentaire « COMPRENDRE » sera créée.
Celle-ci sera composée d’une clé primaire
concaténée « Numcom+Refprod » et de
l’attribut « quantite »
GVdK = CUY
108
Base : modèle relationnel entre plusieurs
tables (2)
CLIENT ( NumCli, Nomcli, Adresseruecli, AdresseCPcli, Adressevillecli)
PRODUIT ( Refprod, Designprod, PUprod)
COMMANDE ( NumCom, datecom, # NumCli )
COMPRENDRE ( NumCom, Refprod, Quantité )
GVdK = CUY
Dans le cas d’une clé primaire concaténée, l’ensemble des attributs formant la
clé est souligné.
108
Duferco
• Duferco produit des brames qu’elle vend.
• Brame : La brame est un demi-produit sidérurgique.
C'est la matière première utilisée pour la fabrication des
tôles ou des plaques sur les trains de laminoirs.
• La brame ou slab est une masse d'acier de forme
parallélépipédique de fortes dimensions (plus de 1000
mm de largeur pour plus de 10 mètres de long et un
poids de plusieurs dizaines de tonnes), obtenue soit
directement de coulée continue soit après réduction
d'un lingot dans un slabbing lequel est un train de
laminoirs dégrossisseur.
GVdK = CUY
Duferco (2)
Fourniss
Brames
Clients
Codefourniss
Codebrame
Codeclient
Codecoulée
Coulées
Commandes
Codecoulée
Codecommande
Codechim
Codebrame
Codeequip
Codeclient
Codefourniss
Equipes
Compchim
Codeequip
Codechim
GVdK = CUY
Duferco (3)
GVdK = CUY
Bases & tables : exemples
1. Duferco
tables, relations_0,
2. Enseignement supérieur
x
3. Police d’Ixelles
x
4. Centre de documentation
x
5. Réservations théâtre
x
6. 9e art
x
GVdK = CUY
Créer une base de données (0)
Contrainte de clé primaire :
Une fois que la clé primaire sera désignée et
le champ déclaré sans doublons, les saisies
de valeurs identiques seront impossibles.
Lors de la création d’une table, la clé primaire et
l’appartenance des données à un type doivent
être obligatoirement définies.
D’autres éléments peuvent être posés pour assurer
le confort et la sécurité de la saisie.
Choix du type :
Il s’agit de préciser quel type de valeurs sera
stocké dans le champ.
Il existe trois grands types (numérique,
date, texte). Pour les champs de type texte, il
est possible d’intervenir sur la taille du
champ, de manière à optimiser la place
occupée sur le support de stockage.
Contrôle de la saisie :
Pour faciliter la saisie, un masque, une valeur
par défaut, peuvent être mis en place.
Contrainte de domaine :
Il est possible, pour un champ donné, de
limiter les valeurs autorisées à la saisie.
Celles-ci sont indiquées dans la case
« Valide si », un message d’erreur peut y être
associé.
GVdK = CUY
9
Créer une base de données (1)
• Possibilité de créer avec SQL, mais pas indispensable
• Base de données = ensemble de tables
GVdK = CUY
Créer une base de données (2)
•
• Donner un nom à la Base de données  extension .mdb
GVdK = CUY
Créer une base de données (3)
• La Base de données est créée,
• Elle ne contient aucune ‘Table’ au départ
GVdK = CUY
Objets d’une base de données (1)
• Tables, Requêtes
• Formulaires, États
• Tables,
entités/relations
• Requêtes,
interrogations
• Formulaires,
visualisation de
tous les attributs
• États,
documents à
publier
GVdK = CUY
Objets d’une base de données (2)
• Tables, Requêtes
• Formulaires, États
• Tables,
entités/relations
• Requêtes,
interrogations
• Formulaires,
visualisation de
tous les attributs
• États,
documents à
publier
GVdK = CUY
Objets d’une base de données (3)
• Requêtes,
interroger les
données
• États,
publier et mettre
en forme les
résultats
• Macros,
automatiser des
actions à exécuter
• Modules,
programmer en
VisualBasic
• Tables,
entités/relations
saisir les données
GVdK = CUY
• Formulaires,
visualiser et
faciliter la saisie
des attributs
Créer une table (0)
3
2
1
GVdK = CUY
Créer une table – données (1)
GVdK = CUY
Créer une table – assistant (2.1)
GVdK = CUY
Créer une table – assistant (2.2)
GVdK = CUY
Créer une table – création (3.1)
GVdK = CUY
Créer une table – création (3.2)
GVdK = CUY
Créer une table – SQL (4)
• CREATE TABLE
• exemple :
Créer la nouvelle table "table_test" contenant quatre champs :
un champ avec un entier qui doit toujours être saisi et un champ
contenant une chaîne de 5 caractères :
• CREATE TABLE table_test
(
champ1 int unsigned
champ2 integer CONSTRAINT champ2 NOT NULL,
champ3 char(5),
champ4 date
);
GVdK = CUY
Les types d’attributs (1)
GVdK = CUY
Les types d’attributs (2)
1. Texte
max : 255 caractères
2. Mémo
max : 65 535 caractères
3. Numérique
entier, simple précision, nbre décimales, etc.
4. Date/Heure
année 1009999, 12 ou 24 h
5. Monétaire
nombres réels de 1 à 4 décimales
6. NuméroAuto
incrémentation automatique
7. Oui/Non
Vrai/Faux , Actif/inactif
8. Objet OLE (Object Linking and Embedding)
feuilles de calcul, sons, vidéos, graphiques…
9. Lien Hypertexte
chemin complet (local ou internet)
GVdK = CUY
Les masques de saisie (1)
GVdK = CUY
Les masques de saisie (2)
GVdK = CUY
Les masques de saisie (3)
GVdK = CUY
Les masques de saisie (4)
Symbole
Signification
0
Chiffre de 0 à 9 obligatoire
9
Chiffre ou espace facultatif
#
Chiffre ou espace ou + ou -
L
Lettre de A à Z obligatoire
?
Lettre de A à Z non obligatoire
A
Lettre ou chiffre obligatoire
a
Lettre ou chiffre non obligatoire
&
Caractère quelconque obligatoire
C
Caractère quelconque non obligatoire
<
Passe tout en minuscules (bas de casse)
>
Passe tout en majuscules (haut de casse)
!
Saisie à partir de la droite
GVdK = CUY
Les masques de saisie (5)
masque
sens
exemples
00 00 00 00 00
Numéro de téléphone français
nouvelle numérotation
04 12 34 56 78
00 999
Code postal français
(département obligatoire)
75 123 ou 59
>CCCCCCCCCCCCCCC
CCCCC
>C<CCCCCCCCCCCCC
CCCCCC
Nom de maximum 20 caractères,
converti en capitales
VAN DELDER ou BOND
Prénom de maximum 20
caractères, 1re lettre capitale,
lettres suivantes bas de casse
Guy ou James 007
0 00 00 00 0000
Matricule d’enseignant sur
précisément 11 chiffres
1 53 06 07 0557
00/00/0099
Date avec année possible sur 2
ou 4 chiffres
07/06/1953 ou 22/02/53
(000) 000-0000
Téléphone avec indice régional
obligatoire
GVdK = CUY
(207)555-0199 ou
(165)345-2025
Les masques de saisie (6)
masque
sens
exemples
(999) 000-0000!
Téléphone avec indice régional
facultatif – remplissage par la
droite
Téléphone américain (derniers
chiffres peuvent être des lettres)
(207)555-0199 ou ()3452025
(206) 555-TELE
#999
Nombre positif ou négatif, composé de quatre
caractères ou moins, sans séparateur de
milliers ni séparateur décimal.
2345 ou -20
>L????L?000L0
Combinaison de lettres obligatoires (L), lettres
facultatives (?) et nombres obligatoires (0). Le
signe « > » oblige les utilisateurs à entrer
toutes les lettres en capitales.
0 00 00 00 0000
Matricule d’enseignant sur
précisément 11 chiffres
00/00/0099
Date avec année possible sur 2
ou 4 chiffres
(000) 000-0000
Téléphone avec indice régional
obligatoire
(000) AAA-AAAA
GVdK = CUY
GREENGR339M3 ou
MAY R462B7
1 53 06 07 0557
07/06/1953 ou 22/02/53
(207)555-0199 ou
(165)345-2025
Les masques de saisie (5)
GVdK = CUY
La saisie (1)
GVdK = CUY
Les listes déroulantes (1)
Monsieur, M., Mr, Dr, Me, Madame, Mme, Mlle, …
GVdK = CUY
Les listes déroulantes (2)
solution simple, par valeurs souhaitées
Dans la table, créer le champ Titre
Cliquer sur ‘Assistant Liste de choix’
Sélectionner "Je taperai
les valeurs souhaitées"
Dresser la liste dans
Contenu
GVdK = CUY
Les listes déroulantes (3)
GVdK = CUY
Les listes déroulantes (4)
GVdK = CUY
Les listes déroulantes (5)
GVdK = CUY
Les clefs (1)
GVdK = CUY
Lier des tables (1)
GVdK = CUY
Lier des tables (2)
GVdK = CUY
Lier des tables (3)
GVdK = CUY
Les filtres (1)
Les filtres permettent de limiter simplement,
de façon temporaire,
les enregistrements affichés dans une table.
On peut filtrer selon deux méthodes :
- le filtre sur un seul critère :
ex : les étudiants qui habitent Braine-le-Comte ;
- le filtre sur plusieurs critères :
ex : les étudiants qui habitent Braine-le-Comte ou ceux qui
s’appellent « Durand ».
GVdK = CUY
Les filtres (2) à un seul critère
1. on se positionne sur l'enregistrement d'un client habitant Paris,
2. on sélectionne le mot ‘Paris’ dans le champ ‘Ville’ ;
3. on clique sur l’icône de filtre ;
et voilà…
4. pour revenir à un affichage non filtré, on clique sur l’icône
et nous voilà comme avant…
GVdK = CUY
;
Les filtres (3a) à plusieurs critères
(= filtre par formulaire)
1. on clique sur l’icône de filtre
;
2. un enregistrement vide apparaît ;
3. on sélectionne « Machin » dans le champ ‘Nom’ ;
4. Puis on sélectionne « ou » dans le coin inférieur gauche, on obtient
un nouvel enregistrement vide…
GVdK = CUY
Les filtres (3b) à plusieurs critères
(= filtre par formulaire)
5. on sélectionne « Saint-Quentin » dans le champ ‘Ville’ ;
6. pour terminer (et pas pour revenir à un affichage non filtré), on clique
sur l’icône
;…
GVdK = CUY
Les requêtes
(= interroger une base de données)
1. Requête de consultation, (=sélection, interrogation…)
1. Requêtes dans une table ou plusieurs tables
2. Tri, filtres (critères), calculs, regroupement
3. Projection, sélection / restriction, jointure
2. Requête d’action (attention)
1.
2.
3.
4.
Mise à jour
Suppression
Ajout
Création de table
3. Requête d’analyse croisée
4. Requête SQL
1. Langage spécifique, permet une exécution ‘fine’ des requêtes cidessus
GVdK = CUY
Créer une requête de consultation
(1)
Interroger une base de données
(plusieurs tables éventuellement)
=
Créer une requête de consultation
GVdK = CUY
Créer une requête de gestion (1)
Gérer une base de données
(plusieurs tables éventuellement)
=
Créer une nouvelle table ;
Modifier la structure d’une table ;
Supprimer une table ;
Créer ou supprimer un index.
GVdK = CUY
Créer une requête de
manipulation (1)
Manipuler une base de données
(plusieurs tables éventuellement)
=
Ajouter des données dans une table ;
Modifier les données d’une table ;
Supprimer des données d’une table.
GVdK = CUY
Créer une requête de consultation
(1)
Interroger une base de données
(plusieurs tables éventuellement)
=
Créer une requête de consultation
les noms des clients et ceux des villes où ils sont domiciliés ?
Projection (d’une table) -> tous les enregistrements, quelques champs
les noms des clients domiciliés à Paris ?
projection et restriction/sélection (d’une table) -> quelques champs de
quelques enregistrements spécifiques
les dates des commandes passées par les clients domiciliés à Paris ?
projection, restriction/sélection et jointure (de plusieurs tables)
GVdK = CUY
Créer une requête : consultation
(2)
Requêteur graphique = langage visuel
propre à chaque logiciel,
possibilités sont limitées lors de cas
complexes.
SELECT liste des champs à afficher
Les logiciels SGBDR intègrent également
un langage textuel de programmation de
requêtes :
le SQL (Structured Query language),
universel, normalisé
et capable de traiter toutes les difficultés.
FROM liste des tables utilisées
WHERE champ et critère de condition
AND champ commun aux tables utilisées
ORDER BY champ à trier ASC ou DESC
GVdK = CUY
22
Créer une requête consultation (3)
Les requêtes de consultation en SQL
ont généralement la structure suivante:
L’instruction SELECT permet de
lister les champs que l’on souhaite
obtenir, il correspond à l ’opérateur de
PROJECTION.
SELECT liste des champs à afficher
FROM liste des tables utilisées
L’instruction FROM permet de lister
les tables utiles à la requête.
Les instructions WHERE et AND
sont
liées.
Elles
permettent
d’introduire :
WHERE champ et critère de condition
AND champ commun aux tables utilisées
ORDER BY champ à trier ASC ou DESC
les
critères de conditions
correspondant
à l’opérateur de
RESTRICTION.
- les champs communs aux tables
utilisées correspondant à l’opérateur
de JOINTURE.
GVdK = CUY
L’instruction ORDER BY permet de
demander un tri sur un champ (ASC
pour croissant et DESC pour
décroissant).
22
Langage des requêtes : projection
(présentation théorique)
Une requête de consultation est une interrogation de la base de données portant sur le contenu d’une ou
plusieurs relations.
Elle met en jeu des opérateurs (projection, restriction, jointure).
La projection consiste
en un découpage
vertical.
Requête1 : On souhaite obtenir les noms des clients (tous) et ceux
des villes où ils sont domiciliés.
Pour cela, nous allons utiliser l’opérateur de projection.
GVdK = CUY
15
Requêtes : projection
(requêteur graphique)
Les logiciels de gestion de bases de données (SGBDR) mettent à disposition des utilisateurs un requêteur graphique
(Query by example dans le logiciel Access) permettant de réaliser des requêtes de consultation facilement et
intuitivement.
Requête avec projection : VILLES DES CLIENTS
Table utilisée dans la
requête.
Projection des champs
retenus dans la requête.
Un attribut est appelé un
champ par le logiciel.
GVdK = CUY
18
Requêtes : projection
(langage
SQL[1])
Projection – Exemple
Une projection est une instruction de consultation permettant de sélectionner
un ensemble de colonnes (champs, arguments) dans une table.
Soit la table Clients qui contient les champs :
NumCli, NomCli, Adresseruecli, AdresseCPcli, Adressevillecli
++++++++++++++++++++++++++++++++++++++++++++++++
L’instruction
Select * FROM Clients
aura comme
résultat un tableau contenant l’ensemble de la table (tous les champs)
L’instruction
Select NomCli, Adressevillecli FROM Clients
aura comme résultat un tableau contenant uniquement les informations (les
champs) correspondant(e)s.
GVdK = CUY
Requêtes : projection
(langage SQL[2])
Projection – Suite
Une projection est une instruction de consultation permettant de sélectionner
un ensemble de colonnes dans une table.
Soit la table Clients qui contient les champs :
NumCli, NomCli, Adresseruecli, AdresseCPcli, Adressevillecli
++++++++++++++++++++++++++++++++++++++++++++
L’instruction
Select DISTINCT Adressevillecli FROM Clients
aura comme
résultat l’ensemble des villes où l’on a des clients (sans doublons)
L’instruction
Select NomCli, Adressevillecli FROM Clients ORDER BY Adressevillecli
aura comme
résultat la liste des clients, classés alphabétiquement par commune.
GVdK = CUY
Requêtes : projection
GVdK = CUY
Requêtes : restriction/sélection
(présentation théorique)
La restriction ou
sélection consiste en un
découpage horizontal.
La projection consiste
en un découpage
vertical.
Requête2 : On souhaite obtenir les noms des clients domiciliés à
Paris. Nous conserverons le nom de la ville dans l’affichage.
Pour cela, nous allons utiliser les opérateurs de projection et de
restriction/sélection.
GVdK = CUY
16
Requêtes : restriction sélection
(requêteur graphique)
Table utilisée dans la
requête.
Projection des champs
retenus dans la requête.
Restriction sur le champ
« adressevillecli » en
posant comme critère
« paris ».
Requête avec projection et restriction :
CLIENTS PARISIENS
GVdK = CUY
19
Requêtes : restriction sélection
(langage SQL[1])
Restriction - sélection – Exemple
Une restriction est une instruction de consultation permettant de sélectionner
un ensemble de lignes dans une table, selon un critère fixé.
Soit la table Clients qui contient les champs :
NumCli, NomCli, Adresseruecli, AdresseCPcli, Adressevillecli
++++++++++++++++++++++++++++++++++++++++
L’instruction
Select Nomcli FROM Clients WHERE Adressevillecli="paris"
aura comme
résultat la liste des clients parisiens
L’instruction
Select NomCli, Adressevillecli FROM Clients WHERE Adressevillecli="paris"
OR Adressevillecli="lille"
aura comme résultat
la liste des clients parisiens ou lillois.
GVdK = CUY
Requêtes : restriction sélection
(langage SQL[2])
Soit la table ETUDIANT (N°Etudiant, Nom, Age, CodePostal, Ville)
SELECT * FROM ETUDIANT
WHERE Age IN (19, 20, 21, 22, 23) ;
SELECT * FROM ETUDIANT
WHERE Age BETWEEN 19 AND 23 ;
SELECT * FROM ETUDIANT
WHERE CodePostal LIKE ’70*’ ;
SELECT * FROM ETUDIANT
WHERE CodePostal LIKE ’70?0’ ;
SELECT * FROM ETUDIANT
WHERE Ville IS NULL ; // Étudiants pour lesquels la ville n'est pas renseignée
SELECT * FROM ETUDIANT
WHERE Ville IS NOT NULL ; // Étudiants pour lesquels la ville est renseignée
GVdK = CUY
Requêtes : restriction sélection
(exemple 1)
Soit une base contenant plusieurs tables,
soit la table Clients
On clique sur ‘requêtes’
‘nouveau/elle’
‘mode création’
// Étudiants pour lesquels la ville est renseignée
GVdK = CUY
Requêtes : combinaison de
critères [1]
L’instruction
Select titre FROM Tableaux WHERE (expo NOT LIKE “*paris*“) AND ((année
BETWEEN 1600 And 1699) OR (année BETWEEN 1900 And 1999))
aura comme résultat la liste des titres des tableaux
 non exposés à Paris,
 et datant des XVIIe ou XXe siècles
GVdK = CUY
Requêtes : combinaison de
critères [2]
Et
Ou
GVdK = CUY
Requêtes : combinaison de
critères [3]
Et/ou [1]
Et/Ou [2]
Plusieurs Ou
=
in(x;y;z;w)
GVdK = CUY
Langage des requêtes : restriction
sélection
GVdK = CUY
Requêtes consultation : jointure
(présentation théorique)
La projection consiste
en un découpage
vertical.
La jointure permet de
lier deux tables ayant un
attribut commun.
La restriction consiste
en un découpage
horizontal.
Requête3 : On souhaite obtenir les dates des commandes
passées par les clients domiciliés à Paris
Pour cela, nous allons utiliser les opérateurs de projection,
de restriction et de jointure.
GVdK = CUY
17
Requêtes : jointure
(requêteur graphique)
Requête avec projection, restriction et jointure : DATES DE COMMANDES DES CLIENTS PARISIENS
Tables utilisées dans la
requête.
Jointure liant les deux
tables ayant un champ
commun : « numcli ».
Projection des champs
retenus dans la requête.
Restriction sur le champ
« adressevillecli » avec le
critère « paris ».
GVdK = CUY
20
Requêtes : jointure
Jointure – Exemple
(langage SQL[1])
Une jointure est une instruction permettant de sélectionner un ensemble de
lignes dans plusieurs tables (liées), selon un critère fixé.
Soit les tables Client, Commande qui contient les champs :
NumCli, NomCli, Adresseruecli, AdresseCPcli, Adressevillecli
NumCom, datecom, NumCli
+++++++++++++++++++++++++++++++++++++++++
L’instruction
Select Nomcli, Adressevillecli, datecom
FROM Clients, Commande
WHERE Adressevillecli="paris“ AND Clients.NumCli = Commande.NumCli
aura comme
résultat la liste des dates de commandes des clients parisiens
GVdK = CUY
Langage des requêtes : jointure
GVdK = CUY
Langage des requêtes : jointure
GVdK = CUY
Les requêtes paramétrées
Dans la ligne Critères du (ou des)
champs concerné(s) par le
paramétrage, entrer entre crochets [ ]
l’invite qui apparaîtra dans la boîte de
dialogue.
La requête est alors
exécutée en fonction
de la réponse de
l'opérateur.
GVdK = CUY
Requêtes : analyse croisée
Il s'agit d'un tableau interactif qui contient des données de synthèse constituées à
partir d'une BDD interne (Access) ou externe (SQL Server, etc.) que l'on peut
manipuler à l'aide de fonctions statistiques pour les analyser sous divers angles.
Le vocable dynamique découle du fait que l'on peut
faire pivoter les titres des colonnes et des lignes
pour obtenir différentes présentations analytiques des données.
Exemples,
• récapituler les ventes par service, par mois ou par vendeur ;
• subdiviser ces catégories par produit ;
• comparer les ventes réalisées et les dépenses avec les montants
budgétés par mois, par trimestre ou par année.
Par rapport à une requête Sélection,
les informations obtenues sont plus compactes
et se prêtent donc mieux à une analyse.
GVdK = CUY
Requêtes : analyse croisée
Par rapport à une requête Sélection,
les informations obtenues sont plus compactes
et se prêtent donc mieux à une analyse.
GVdK = CUY
Requêtes : fonctions de calcul
• Les fonctions suivantes peuvent être
utilisées dans une clause SELECT pour
effectuer des calculs sur le résultat de la
requête :
• COUNT : nombre d'enregistrements (Pour
éviter de compter les doublons, on peut ajouter le paramètre
DISTINCT)
•
•
•
•
SUM : somme d'une colonne
AVG : moyenne (average)
MAX : maximum d'une colonne
MIN : minimum d'une colonne
GVdK = CUY
Requêtes : fonctions de calcul
• Select AVG(tableagrégation.nombre)
as Moyennedenombre
from tableagrégation
GVdK = CUY
Requêtes : fonctions de calcul
• Select COUNT(*)
from PERSONNEL
 nombre de salariés
GVdK = CUY
Requêtes : fonctions de calcul
• Select SUM(salaire)
from PERSONNEL
where fonction=« maitre-assistant »
 Somme des salaires des «maîtreassistant »
GVdK = CUY
Requêtes : regroupement sans
fonction de calcul [1]
• Select tableagrégation.ville
from tableagrégation
GROUP BY tableagrégation.ville
GVdK = CUY
Requêtes : regroupement sans
fonction de calcul [2]
• Select
from
GROUP BY
GVdK = CUY
Requêtes : fonctions de calcul et
groupement
• Select AVG(tableagrégation.nombre)
as Moyennedenombre
from tableagrégation
GROUP BY tableagrégation.ville
GVdK = CUY
Requêtes : groupement, sélection et
fonction de calcul
On affiche les villes pour lesquelles la moyenne du champ
"nombre" est supérieure à 400.
On affiche aussi les moyennes du champ "nombre" pour
les villes retenues.
GVdK = CUY
Select tableagrégation.ville, avg(nombre)
from tableagrégation
GROUP BY tableagrégation.ville
HAVING avg(nombre)>400
Requêtes : groupement, sélection et
fonction de calcul [2]
On affiche les numéros de client et le nombre de
commandes passées.
Select numclient, count(*)
from commande
GROUP BY numclient
GVdK = CUY
Requêtes : groupement, sélection et
fonction de calcul [3]
On affiche pour chaque client ayant passé plus de 2 commandes, quel est
le montant minimum, et maximum des commandes qu’il a passées.
Select numclient, min(montant), max(montant)
from commande
GROUP BY numclient
GVdK = CUY
HAVING count(*)>2
Requêtes : groupement, sélection et
fonction de calcul [4]
On affiche pour chaque client ayant passé plus de 2 commandes, son
numéro, nom, prénom, nombre de commandes passées, moyenne et cumul
total des montants, le tout trié par nombre décroissant de commandes
passées, puis par noms, prénoms croissants
Select cl.numclient, nom, prenom, count(*), avg(montant), sum(montant)
from client cl, commande co
WHERE cl.numclient = co.numclient
GROUP BY numclient
HAVING count(*)>2
ORDER BY 4 desc, nom, prenom
GVdK = CUY
Requêtes : groupement, sélection et
fonction de calcul [5]
On affiche pour chaque élément commandé, ceux dont 90% de la quantité
commandée dépasse les 500 unités
Select *
from details
WHERE quantite – quantite * 0,1 > 500
GVdK = CUY
Requêtes : groupement, sélection et
fonction de calcul [6]
On affiche pour chaque élément commandé, ceux dont 90% de la quantité
commandée dépasse les 500 unités
Select sum(prixunitaire * quantite)
from details
WHERE numcommande = « PO467-2009 »
GVdK = CUY
Requêtes : requêtes imbriquées
(intersection) [1]
On affiche les étudiants qui se sont inscrits après être
passés par la journée ‘portes ouvertes’
Select numetud, nom, prenom
from students
WHERE numetud in
(select numetud
from JPO)
GVdK = CUY
Select numetud, nom, prenom
from students
Intersect
select numetud
from JPO)
Requêtes : requêtes imbriquées
(intersection) [2]
On affiche les étudiants qui se sont inscrits sans être
passés par la journée ‘portes ouvertes’
Select numetud, nom, prenom
from students
WHERE numetud NOT in
(select numetud
from JPO)
Select numetud, nom, prenom
Select numetud, nom, prenom
from students
from students
Minus
Except
select numetud
select numetud
from
from JPO)
GVdK
= CUYJPO)
Requêtes : exemples et exercices
Travail (présentation Powerpoint) relatif aux
requêtes (les fautes d’orthographe sont de leurs auteurs) :
• Fait par Hyacinthe Laini (59 dias) :
• Fait par Didric Sluis (21 dias) :
• Fait par Céline Stevens (21 dias) :
• Fait par Giusy Talluto (27 dias) :
• Fait par Jonathan Visage (23 dias) :
GVdK = CUY
Requêtes : exemples et exercices
Travail (présentation Powerpoint) relatif aux
requêtes, une cinquantaine d’exemples (les fautes
d’orthographe sont de leurs auteurs) :
• NET_PROVENCE,
fait par Richit Nathalie, Pouplier Thierry, Patrice Viaud, Patrick
Laupies (42 dias) :
GVdK = CUY
Formulaires : assistant (1)
GVdK = CUY
Formulaires : assistant (2.1)
GVdK = CUY
Formulaires : assistant (2.2)
GVdK = CUY
Formulaires : assistant (3)
GVdK = CUY
Formulaires : assistant (4)
GVdK = CUY
Formulaires : assistant (5)
GVdK = CUY
Formulaires : assistant (6)
GVdK = CUY
Formulaires : instantané (6)
GVdK = CUY
Formulaires : assist. Graph. (7)
GVdK = CUY
Formulaires : assist. Graph. (8)
GVdK = CUY
Formulaires : assist. tabl. dyn. (9)
GVdK = CUY
États : création (1)
GVdK = CUY
États : création (2)
GVdK = CUY
États : création (3)
GVdK = CUY
États : création (4)
GVdK = CUY
États : création (5)
GVdK = CUY
États : création (6)
GVdK = CUY
États : création (7)
GVdK = CUY
États : assistant étiquettes (8)
GVdK = CUY
États : assistant étiquettes (9)
GVdK = CUY
États : calculs (10)
GVdK = CUY
États : tris & regroupements (11)
GVdK = CUY
Cas Baronnia (énoncé [1])
Dans la haute société, Madame la Baronne Damien Fèz de V'nir
(Ukraine) est une figure incontournable. Elle organise avec un
brio inégalé des réceptions qui vont du souper simple entre amis
(24 couverts, 5 services), à la soirée de mariage princier.
Inutile de dire que ses invitations sont extrêmement prisées. Et pour
cause...
Madame la Baronne, avec beaucoup de tact, veille à inviter au
moins une fois l'an chacune de ses relations. En bonne hôtesse,
elle sait présenter ses invités les uns aux autres en rappelant les
professions respectives et, le cas échéant, à quelle réception ils
ont déjà eu l'occasion de se rencontrer.
Le nombre d'amis et relations ne faisant que croître, Madame la
Baronne envisage - non sans une certaine répugnance - de
recourir à l'ordinateur ("Vous savez très chère, cette chose pleine
de puces et de souris..."). Sur recommandation de son ami
intime, le Comte René de Saissandre, qui vous connaît très bien
(si, si ...), elle décide de vous confier la résolution de cet
angoissant problème.
GVdK = CUY
Cas Baronnia (énoncé [2])
Madame la Baronne désire :
• établir un carnet d'adresse de ses amis, relations et connaissances (en
distinguant chacune de ces catégories) ;
• savoir si telle personne était présente à la dernière réception, si elle était
malade ou excusée ;
• savoir depuis combien de temps telle personne n'a plus été invitée et
adresser des invitations à celles qui n'ont plus été invitées depuis 8 mois
;
• connaître l'âge (en toute discrétion bien sûr), la profession et le revenu
annuel (en toute discrétion aussi ça va de soi) de ses relations ;
• faire la liste des personnes qui appartiennent à une tranche d'âge
donnée ;
• vérifier le budget, c'est-à-dire connaître le coût des réceptions. On
compte un coût fixe (location du château) et un coût proportionnel par
invité (menu) ;
• savoir si une invitation a été confirmée ;
• savoir si le temps était beau, maussade ou pluvieux à chacune de ses
réceptions ;
• etc.
GVdK = CUY
Cas Baronnia (énoncé [3])
Quelques informations supplémentaires :
• On ne considère que les personnes individuelles et non les
couples ;
• Les invités apportent souvent un petit cadeau. Pour éviter tout
impair, il faut savoir qui a apporté quoi et à quelle réception ;
• De même, certains invités reçoivent un petit cadeau
(anniversaire, promotion...) ; il faut savoir qui a reçu quoi et à
quelle réception... et pour quelle valeur (budget) ;
• Plusieurs amis possèdent un titre de noblesse
GVdK = CUY
Cas Baronnia (énoncé [4])
Quelques exigences particulières, il faudrait pouvoir obtenir :
• la liste des personnes invitées à la réception du jj/mm/aaaa,
[titre, nom, prénom, profession de l'invité ; tri par nom] ;
• la liste des personnes présentes à la réception du jj/mm/aaaa
(critère) [tous les champs ; tri par nom] ;
• la liste de toutes les réceptions [Date, lieu, nombre d'invités
présents ; tri par date décroissante] ;
• le coût d'une réception donné par la formule suivante :
nombre d'invités (sauf excusés) x coût par personne + coût fixe
+ coût des cadeaux offerts
GVdK = CUY
Cas Baronnia (énoncé [5])
Quelques réceptions remarquables :
Lieu
Date
Coût fixe (Eur)
Coût / pers
(Eur)
Château de
Lamalle
21 juillet 1999
1 250
100
Château
d'Outre Meuse
15 août 1999
1 350
125
Domaine des
Prés Fleuris
14 février 1998
950
62,5
Ferme du
Marquisat
21 mars 1999
450
45
GVdK = CUY
Cas Baronnia (énoncé [6])
Quelques amis et relations (remarquables ?) :
Identité
Adresse
Ville
Omer d’Alors
Rue neuve 5
Juprelle
Dominique Nique
(marquise)
Rue Dewonck 67
Fexhe
Roland Nôset-Abond
Rue du sale 118
Mellery
Elvire Sacutti
Rue provinciale
Villers
Aubin Sahalore
(comte)
Rue Maréchal 114
Lantin
GVdK = CUY
Diviser pour régner…
Carnet d’adresse
Covert Harry, rue Minant 3, 6681 Lavacherie, 0475 98 76 54
Dalors Homère, rue Tilant 9, 1348 Louvain-la-Neuve, 010 11 12 13
John Deuf, rue Barbe 10, 7090 Dour, 065 43 21 00
Qui
Où
Tel
Covert Harry
rue Minant 3, 6681 Lavacherie,
0475 98 76 54
Dalors Homère
rue Tilant 9, 1348 Louvain-laNeuve
010 11 12 13
John Deuf
rue Barbe 10, 7090 Dour
065 43 21 00
cPrenom cNom
cAdresse
cCP
cCommune
cTel
Harry
Covert
rue Minant 3
6681
Lavacherie
0475 98 76 54
Homère
Dalors
rue Tilant 9
1348
Louvain-la-Neuve
010 11 12 13
John
Deuf
rue Barbe 10
7090
Dour
065 43 21 00
GVdK = CUY
Structure des bases de données
Base de données
mabase.mdb
Table1
Élèves
Enregistrement1
Élv Dupont
Enregistrement2
Élv Durand Pierre1
Champ1
Nom
GVdK = CUY
Table2
Classe
Champ2
Prénom
Table3
Matières
Utile dans les requêtes (1)
Like "S*"
Fournit tous les enregistrements pour lesquels le
champ commence par S.
Like "*Imports"
Fournit tous les enregistrements pour lesquels le
champ termine par le mot "Imports".
Like "[A-D]*"
Fournit tous les enregistrements pour lesquels le
champ commence par une lettre entre A et D.
Like "*ar*"
Fournit tous les enregistrements pour lesquels le
champ contient la séquence "ar".
Like "Acc?"
Fournit tous les enregistrements pour lesquels le
champ commence par "Acc" et pour lesquels la
quatrième et dernière lettre est inconnue.
Like
Fournit tous les enregistrements pour lesquels le
champ est identique au champ "NomChamp" du
[forms]![NomFor
formulaire "NomForm".
m]![NomChamp]
GVdK = CUY
Utile dans les requêtes (2)
< 100
Fournit tous les enregistrements pour lesquels le
champ contient une valeur numérique inférieure à
100.
< 1/1/2000
Fournit tous les enregistrements pour lesquels le
champ contient une date antérieure au 1/1/2000.
A And B
Fournit tous les enregistrements pour lesquels le
champs satisfait le critère A et le critère B.
A Or B
Fournit tous les enregistrements pour lesquels le
champs satisfait le critère A ou le critère B.
A Xor B
Fournit tous les enregistrements pour lesquels le
champs satisfait le critère A ou le critère B de
manière exclusive.
Null
Fournit tous les enregistrements pour lesquels le
champs est vide.
Is not null
Fournit tous les enregistrements pour lesquels le
champs n'est pas vide.
GVdK = CUY
Les cardinalités (1)
A
B
A
B
A
B
a1
b1
a1
b1
a1
b1
a2
b2
a2
b2
a2
b2
a3
b3
a3
b3
a3
b3
a4
b4
a4
b4
a4
b4
a5
b5
a5
b5
a5
b5
b6
a6
b6
a6
<1,1>
(0,1)-(1,1)
One to one
GVdK = CUY
<1,n>
(0,n)-(1,1)
One to many
<m,n>
(1,n)-(0,n)
Many to many