Select

INFO 2014
Fichiers et base de données
Philippe Fournier-Viger
Département d’informatique, U.de Moncton
Bureau D216,
[email protected]
1
Calendrier
TP1 énoncé
TP2 énoncé
2
TP2 - information
Pour faire un programme qui accepte des
paramètres en ligne de commande:
#include <iostream>
int main(int argc, char* argv[]) {
cout << argv[0] << endl;
return 0;
}
3
La suite du cours sera basée sur:

Silberschatz, Korth et Sudarshan,
Database System concepts, Sixth
edition, New York, McGraw-Hill, 2010.
Chap. 1,2,3,4,5, 7,8,9 et 14
(principalement)
4
Les bases de données (BD)
5
Introduction



Base de données (BD): un ensemble de données
inter-reliées
Système de gestion de base de données
(SGBD): logiciel pour accéder et gérer une BD.
Les SGBD sont conçus pour gérer de très grands
volumes d’information.
◦ permet de définir des structures pour stocker
l’information,
◦ offre des mécanismes d’accès à l’information.

Un SGBD peut aussi protéger les données:
◦ accès autorisés seulement,
◦ assurer l’intégrité des données si plusieurs utilisateurs
simultanés (exclusion mutuelle),
◦ autre contraintes…
6
Applications des BD
Exemple:
 Gérer l’information d’une entreprise, de façon
générale:
◦
◦
◦
◦
◦
◦
◦
◦
les employés,
les clients,
les fournisseurs,
les produits,
l’inventaire,
la comptabilité (reçus, paiement, …),
les biens de l’entreprise,
et autres (ex.: le feed-back de consommateurs).
7
Applications des BD (suite)
Pour des domaines plus spécifiques:
 Le domaine de la finance:
◦ transactions bancaires,
◦ clients,
◦ information du marché boursier mise à jour en temps
réel.

Les universités:
◦ information sur les étudiants, employés, cours, notes,
livres, etc.

Les compagnies aériennes:
◦ réservations, horaire, points boni, …

Les entreprises en télécommunication:
◦ historique des appels téléphoniques, cartes prépayés,
information sur le réseau téléphonique…
8
Interaction avec les BD
Il y a une trentaine d’années, la plupart des gens
n’interagissaient pas avec les BD.
 De nos jours, cela est très courant:

◦
◦
◦
◦
◦
◦
◦
guichets automatiques,
système téléphonique intelligent,
site web de réservation,
site web de choix de cours,
site web d’achat de produits (ex.: Amazon),
site web de votre compte bancaire.
publicités personnalisées sur le Web.
9
Interaction avec les BD (suite)
Dans la vie quotidienne, les BD et les détails des
accès aux BD ne sont généralement pas visibles
à l’utilisateur.
 On peut remarquer l’importance des BD:

◦ les compagnies spécialisées telles qu’Oracle sont
parmi les plus grandes compagnies informatique,
◦ plusieurs grandes compagnies informatiques (IBM,
Microsoft…) ont des produits dans ce domaine.
10
Pourquoi des BD?
Il y a quelques décennies,
Les données étaient stockées dans des fichiers.
 Des programmes étaient développés pour gérer l’accès
aux fichiers et effectuer des opération (recherche,
modification…) (ex.: inscrire un nouvel étudiant dans un
cours, annuler un cours…)
 Si de nouveaux besoins, alors de nouveaux programmes
étaient développés, modifiés ou de nouveaux fichiers
étaient ajoutés.

produire bilan mensuel
clients
inventaire
Imprimer les factures
employés
factures
ajouter un client
enregistrer une vente11
Utilisation de fichiers
Désavantages:
 Le stockage des fichiers est géré par le système
d’exploitation.
 Incohérence et redondance: avec le temps:
◦ des fichiers avec des structures différentes,
◦ plusieurs langages de programmation,
◦ dédoublement de l’information dans plusieurs fichiers
(ex.: étudiant en informatique et philosophie),
◦ risques d’incohérences
(ex.: adresse n’est pas mise à jour partout).
12
Utilisation de fichiers (suite)

Accès difficile aux données:
◦ Les applications et structures de fichier développées ne
sont pas flexibles pour de nouveaux besoins.
◦ Ex.: un logiciel pour générer la liste de tous les
étudiants, mais la secrétaire n’a besoin que de la liste
des étudiants du département qui ont plus de 60
crédits.
◦ Solution: faire appel à un programmeur ou faire le tri
manuellement…
◦ En somme, organiser de l’information en fichiers n’est
pas flexible et pratique pour l’utilisateur.
13
Utilisation de fichiers (suite)

Isolation par les formats de données:
◦ Si les données sont stockées dans plusieurs fichiers
ayant différents formats, il peut être très difficile de
définir un programme pour extraire et manipuler
l’information appropriée.
◦ Exemples:
 Plusieurs formats de fichier pour stocker les dossiers des
étudiants dans chaque département.
 Plusieurs formats pour stocker l’inventaire dans différentes
succursales.
14
Utilisation de fichiers (suite)

Intégrité des données:
◦ Pour assurer l’intégrité des données, il est possible d’ajouter
des contraintes aux programmes
(ex.: dates de naissances > 1900, montant > 0 $).
◦ Toutefois, si de nouvelles contraintes, difficile de les ajouter à
tous les programmes sans introduire d’erreurs.
◦ Exemples:
 changement de la taxe de vente,
 changement des conditions d’amissibilité à un programme
◦ Ceci est encore plus problématique quand les données sont
réparties dans plusieurs fichiers.
15
Utilisation de fichiers (suite)

Problèmes d’atomicité:
◦ Considérons un programme de transfert d’argent:
Programme « transfert »
Paramètres : montant, compte source, compte destination
1. Lire le montant dans le compte de banque source
2. Soustraire la somme
3. Écrire le nouveau montant dans le compte de banque destination
◦ Si une panne survient entre le retrait et le dépôt,
l’argent est perdu.
◦ Les données sont alors dans un état incohérent.
◦ En utilisant des fichiers, il peut être difficile d’assurer la
cohérence des données..
16
Utilisation de fichiers (suite)

Problèmes d’atomicité (suite)
◦ L’atomicité est une propriété désirable.
 C’est la propriété de pouvoir traiter plusieurs opérations comme
un tout.
 En cas d’échec d’une opération, toutes les opérations sont
annulées.
17
Utilisation de fichiers (suite)

Anomalies causées par des accès
concurrents:
◦ Plusieurs systèmes permettent aux utilisateurs de
mettre à jour les données simultanément.
◦ Ex: une banque avec des milliers de clients.
◦ Ceci peut provoquer des incohérences.
◦ Exemple 
18
Utilisation de fichiers (suite)
Exemple:
◦ un compte avec 1000 $.
Programme « retrait »
Paramètres : montant, # de compte
1. Lire le montant dans le compte de banque
2. Soustraire la somme
3. Écrire le nouveau montant dans le compte de
banque
◦ Deux achats quasi simultané: un de 100 $ et un de 200$.
◦ Il est possible que le montant d’argent dans le compte après les transactions soit
900 $, 800 $ ou bien 700 $.....
◦ Il faut donc un système de supervision des accès concurrents.
◦ Difficile à mettre en place quand plusieurs programmes sont utilisés.
19
Cas 1 (résultat attendu):
Guichet 1
Lecture
de 1000$
Compte 1000 $
Guichet 2
1000$ - 200$
= 800 $
Écriture de
800 $
800 $
700$
Lecture
de 800$
800$ - 100$ =
700 $
Écriture de
700 $
Cas 2 (erreur):
Guichet 1
Lecture
de 1000$
1000$ - 200$
= 800 $
Écriture de
800 $
Compte 1000 $
Guichet 2
900$
800 $
Lecture
de 1000$
1000$ - 100$
= 900 $
Écriture de
900 $
Cas 3 (erreur):
Guichet 1
1000$ - 200$
= 800 $
Compte 1000 $
Guichet 2
900 $
Lecture
de 1000$
1000$ - 100$
= 900 $
Lecture
de 1000$
Écriture de
900 $
Écriture de
800 $
800$
22
Utilisation de fichiers (suite)

Problèmes de sécurité:
◦ Tous les utilisateurs ne doivent pas avoir accès
à toutes les données.
◦ Exemple: dans une université, tous ne
doivent pas avoir accès aux informations
académiques ou au système de paie.
◦ Difficile de vérifier les contraintes de
sécurité quand les accès aux données sont
faits par plusieurs programmes sur des
fichiers.
23
Vues sur les données
Les SGBD offrent des vues abstraites aux
utilisateurs pour simplifier les interactions avec
le système.
 Raison:

◦ certains utilisateurs ne sont pas des programmeurs,
◦ pour être efficient, un SGBD peut utiliser des
structures de données (ex.: B+ arbres) et
mécanismes complexes, pas pertinents pour
l’utilisateur final.
24
Vues sur les données (suite)
Trois niveaux d’abstraction pour les données:
1. niveau physique: comment les données sont
réellement stockées sur le disque.
◦ Structure de données de bas niveau, indexation… B+
arbre…
2.
niveau logique: décrit quelles sont les
information contenues dans la base de données et
leur relations.
◦ cela est décrit avec des structures simples indépendantes
de la représentation physique.
◦ les administrateurs de BD travaillent principalement à ce
niveau pour décider de ce qui est stocké dans une BD.
25
Vues sur les données (suite)
3.
niveau des vues:
◦ le niveau de l’utilisateur final.
◦ une vue présente seulement une partie de la BD à un
utilisateur.
◦ le système peut offrir plusieurs vues de la même BD à
différents utilisateurs.
◦ Les vues:
 permettent de faire abstraction de la variété d’information stockées
dans la BD.
 permettent de simplifier l’interaction avec le système.
 servent de mécanisme de sécurité pour empêcher les accès
interdits.
26
Vues sur les données (suite)

Une analogie avec la programmation:
◦ niveau physique: l’information est stockée
dans des cases mémoires.
◦ niveau logique: des structure de données
sont définies. Ex.:
 Département possède nom, édifice, budget
 Cours possède titre, dept, crédits, session…
 Étudiant possède matricule, nom, adresse …
◦ niveau des vues: les utilisateurs d’un logiciel
n’ont qu’une vue sur les données du logiciel.
27
Instances et schémas
Instances et schémas d’une BD
 Instance d’une BD: les informations
stockées dans la BD à un temps donné.
 Schéma d’une BD: la conception générale
de la BD.
Analogie avec la programmation:
instance = valeur d’une variable,
ou instance d’un objet
schéma = déclaration de variable
ou classe
28
Schémas
Les schémas peuvent être décrits selon les
trois niveaux d’abstraction
(physique, logique et vues).
 Les administrateurs utilisent les schémas
logiques pour faire la conception d’une BD.
 Des sous-schémas peuvent être utilisés pour
décrire différentes vues sur une BD.
 La séparation des niveaux d’abstraction permet
une indépendance des logiciels ou des utilisateurs

envers la représentation physique des données.
29
Modèles de données
Un autre concept important est celui de
modèle de données.
 Le modèle de données décrit

◦ les outils conceptuels pour décrire les
données,
◦ les relations,
◦ la sémantique des données,
◦ les contraintes d’intégrité.

Il y a plusieurs modèles de données:

30
Modèles de données
1.
Modèle relationnel:
◦ Le plus utilisé!
◦ Des tables stockent les données et les
relations entre elles.
◦ Une table a plusieurs colonnes avec des noms
uniques.
◦ Le modèle relationnel est basé sur le concept
d’enregistrement.
 Une ligne dans une table = un enregistrement
 Une colonne = un attribut d’un enregistrement
31
Exemple: une table « étudiants »
Matricule
1234567666
Nom
Roy
Prénom
Linda
Programme
Administration
1234567777
1234569999
1234568888
Sauvé
Pierre
Sauvé
Paul
Marc
Paul
Éducation
Biologie
Informatique
…
…
….
…
1234568668
Fournier
Jean
Informatique
enregistrements
champ
32
Modèles de données (suite)
2.
Modèle entité-relation:
◦ Les données sont représentées en termes
d’entités et de relations entre entités.
◦ Entités: un élément ou une chose du monde
réel.
◦ Relations: relations entre éléments
◦ Ce modèle est souvent utilisé pour modéliser de
façon informelle les liens entre les entités
décrites dans une base de données, pour passer
ensuite à un autre modèle de données comme le
modèle relationnel.
33
durée_contrat
date_embauche
employé
id_employé
nom
salaire
succursale
travaille
travaille
prix_vente
id_succ
adresse
téléphone
vend
produit
nom
couleur
description
34
Modèles de données (suite)
3.
Modèle orienté-objet:
◦ Une BD orientée objet stocke des objets et leurs
relations directement et permet de les récupérer.
◦ Encapsulation, méthodes, objets…
4.
Modèle semi-structuré:
◦ Permet de spécifier des éléments de données
pour lesquels les instances n’ont pas toujours
la même structure.
◦ Ex.: les bases de données XML, RDF,...
5.
Autres: graphes, spatial, temporel, etc.
35
SGBD
Système de Gestion de Bases de données.
 Ex.: Access, MySQL, MariaDB…
 Le but d’un système de gestion de BD est
de fournir un environnement pratique et
efficient pour stocker et récupérer de
l’information.
 De nos jours, les BD sont partout.

36
Langages de bases de données

Deux types de langage:
◦ langage de manipulation de données.
◦ langage de définition de données.
En pratique ces deux types de langages
sont souvent combinés en un seul
langage.
Ex.: SQL


37
Langage de manipulation de données
Langage de manipulation de données (LMD):
 permet aux utilisateurs de manipuler les données.
 Accéder, insérer, supprimer ou modifier de
l’information stockée dans la BD.
 Un LMD permet de spécifier les données à accéder et
optionnellement comment y accéder.

Ex.: SQL
 INSERT: ajouter des enregistrement(s)
 DELETE: effacer des enregistrement(s)
 SELECT: sélectionner des enregistrement(s)
38
Langage de manipulation de données (suite)
Une requête est un énoncé demandant de
l’information.
 Un langage de requêtes permet d’exprimer
des requêtes.
 Un processeur de requêtes est incorporé
aux SGBD pour traduire les requêtes LMD en
actions au niveau physique.

39
Exemples de requête
SELECT nom_employés FROM
table_employés WHERE salaire > 30000
AND age < 35.
SELECT nom_clients FROM table_clients
WHERE somme_achats > 500$ AND
dernière_visite < 2011.
40
Langage de définition de données (LDD)
permet de spécifier la structure de stockage des données
(les schémas).
 permet de spécifier des contraintes sur la cohérence
des données. Elles sont vérifiées par le SGBD à chaque
modification de la BD.
 Ex.:
type employé = record
matricule : char(5);
nom: char(20);
nom_departement: char(20);
salaire: numeric(8,2);
end;


Quatre types de contraintes 
note: numeric (nombre de chiffres avant et après la virgule)
41
Contraintes
Contraintes de domaine: associent un
domaine de valeur à chaque attribut.
◦ Ex.: l’attribut « salaire » est un entier
positif.
◦ Ces contraintes sont vérifiées à chaque
modification de la BD.
42
Contraintes
Contraintes d’intégrité référentielle:
◦ pour s’assurer qu’une valeur qui apparaît dans
une relation pour un ensemble d’attributs
apparaît aussi dans un ensemble d’attribut
d’une autre relation.
◦ Ex.: le code de programme présent dans un
enregistrement sur un étudiant est un code
de programme qui existe dans les
enregistrements décrivant les programmes.
43
Contraintes (suite)
Assertions: des conditions qui doivent être
toujours satisfaites dans la BD.
 Les deux types de contraintes précédents sont des
assertions, mais il en existe d’autres types.
 Ex.: tous les départements doivent avoir au moins 5
étudiants.
 Quand une assertion est créée, elle est vérifiée
initialement sur la BD.
 Puis l’assertion est vérifiée à chaque modification.
 Une modification est annulée si elle contredit une
assertion.
44
Contraintes (suite)
Autorisations: permettent de spécifier les
permissions d’accès pour chaque utilisateur
ou groupe d’utilisateurs:
◦
◦
◦
◦
autorisation de lecture,
autorisation d’insertion,
autorisation de modification (mise à jour),
autorisation de suppression.
45
Langage de définition de données (suite)
Les LDD sont interprétés.
 Ils génèrent des métadonnées qui sont
stockées dans la BD (dans le dictionnaire
de la BD).
 Ces métadonnées sont strictement
utilisées par le SGBD et sont invisibles
aux utilisateurs.

46
Dictionnaire de données
décrit la structure employée pour stocker
les données dans la base de données.
 les noms des champs (ex.: adresse,
téléphone),
 le type de données que les champs
contiennent (numérique, alphanumérique,
date, …),
 la taille des champs (ex.: nombre de
caractère d’un code postal).

47
Exploitation d’une bases de données
Accès direct par l’utilisateur (ex.: Access)
 Logiciels exploitant la BD (ex.: C++, Java,
C#…)
 Sites Web dynamiques (ex.: PHP, Servlets,
JSP, ASP…)
…

Code PHP
48
INTRODUCTION AUX BD
RELATIONNELLES
49
Introduction

Des bases de données basées sur
différents « modèles de données »:
◦
◦
◦
◦

orienté objet,
relationnelles,
semi-structurées (ex.: XML),
multimédia…
Le type le plus répandu est le modèle
relationnel.
50
Les bases de données relationnelles

Structure d’une BD relationnelle: des tables
représentant des données et les relations entre les
données.

Le modèle relationnel est populaire, car il
est simple et expressif.

Système de gestion de base de données
relationnelles: un SGBD spécialisé pour les BD
relationnelles.
◦ Ex.: MySQL (open-source, par Oracle), MariaDB,
SQLServer, SQLite, IBM DB2, Oracle, Microsoft Access…
51
Les bases de données relationnelles
Pour interagir avec une BD relationnelle, il faut un
langage de définition de données (LDD) et un
langage de manipulation de données (LMD).
 En général, SQL est utilisé et il rempli ces deux
fonctions.
 Des outils d’administration visuels sont aussi
souvent fournis et utilisés pour un accès simples
aux BD. (ex.: MySQLAdmin, PhpMyAdmin,
Access…)

52
Les tables
Chaque ligne représente un enregistrement.
 Chaque colonne a un nom unique et représente un
attribut pour décrire un enregistrement.
 Une ou plusieurs tables.
 Ex.:
Table « Restos »

Restaurant
Téléphone
Type
Pizza+
123-4567
Pizzeria
Subway
124-4568
Sous-marins
Vitos
125-4569
Italien
…
…
…
53
Les tables (suite)
Il est important de bien concevoir le schéma d’une BD. Un
schéma mal conçu peut introduire de la redondance
inutile et causer d’autres problèmes. Ex.:
Table « Restos »
Nom_restaurant
Téléphone
Type
Pizza+
123-4567
Pizzeria
Subway
124-4568
Fast-food
Vitos
125-4569
Italien
…
…
…
Table « Addresses_restos »
Nom_restaurant
Téléphone
Addresse
Pizza+
123-4567
1 main st.
…
…
…
54
Un autre exemple:
Nom
Prenom
Equipe
Universite
Lecompte
Paul
Aigles
U. de Moncton
Boisvert
Luc
Aigles
U. de Moncton
Charles
Marie
Verts et or
U. de Sherbrooke
Lafortune
Jean
Tigers
U. Dalhousie
Boisvert
Marc
Verts et or
U. de Sherbrooke
…
…
…
…
55
Langage de manipulation de données



SQL est un langage non procédural (une requête demande une
information, mais ne dit pas comment l’obtenir).
Une requête SQL retourne toujours une table en résultat.
Exemple de requête SQL:
select Restos.nom
from Restos
where Téléphone =‘123-4567’
Cette requête retourne une table contenant le nom de tous les restaurants
ayant le numéro de téléphone 123-4567.
Table « Restos »
nom
Téléphone
Type
Pizza+
123-4567
Pizzeria
Subway
124-4568
Fast-food
…
…
…
Résultat
Nom
Pizza+
56
Langage de manipulation de données (suite)
◦ Les requêtes peuvent porter sur plusieurs tables.
◦ Ex.:
select Restos.nom, Addresses_restos.adresse
from Restos, Addresses_restos
where Restos.Type=‘Pizzeria’ and
Restos.nom = Addresses_restos.nom
Nom
Table « Restos »
Téléphone
Type
Pizza+
123-4567
Pizzeria
Subway
124-4567
Fast-food
Vitos
125-4567
Italien
…
…
…
Table « Addresses_restos »
Nom
Téléphone
Addresse
Pizza+
123-4567
1 main st.
57
Langage de définition de données

SQL fournit un langage pour définir:
◦ tables,
◦ contraintes d’intégrités,
◦ assertions, etc.
Exemple 1 (créer une table:
create table Etudiants
(prenom
char(20),
programme char(15),
credits
tinyint);
Exemple 2 (contrainte):
ALTER TABLE Comptes_banques ADD CONSTRAINT solde
CHECK (balance >= 0)
58
Accès à partir d’un logiciel d’application
◦ Logiciel d’application: logiciel utilisateur.
Ex.: logiciel de gestion des employés.
◦ SQL ne remplace pas un langage de programmation.
◦ Certaines opérations sont impossibles en SQL.
◦ Entre autres, SQL ne gère pas l’affichage à l’utilisateur, la
communication réseau, etc.
◦ C’est pourquoi SQL est toujours utilisé avec un langage
hôte: Java, C++, C#...
◦ En général, pour envoyer des requêtes SQL au SGBD, une
interface de programmation est utilisée (ex.: JDBC en Java,
qui se conforme à la norme ODBC).
Ex.: connecteurs pour MYSQL:
http://www.mysql.com/products/connector/
59
CONCEPTION DE BD
60
Conception de bases de données
La conception d’une BD consiste principalement en la
définition de ses schémas.
Étapes:
1. Analyse des besoins: comprendre les besoins des
utilisateurs.
2. Design conceptuel: choisir un modèle de données
pour établir un schéma conceptuel de la BD décrivant
les données et leur relations.
Objectif: faire une modélisation de haut niveau des
données qui seront stockées.
◦
◦
Quels attributs? Quoi inclure dans la BD?
Modèle entité-relation,
◦
Normalisation ( )
61
Exemple de modèle entité-relation
Il existe plusieurs représentations graphiques pour
le modèle entité-relation.
instructeur
matricule
nom
salaire
département
membre
nom département
bâtiment
budget
Représentation inspirée de UML
Les entités sont représentée par des rectangles
• nom (en gris)
• attributs (en blanc)
Les relations sont représentées par des losanges.
Il est possible d’ajouter des contraintes de cardinalité aux
relations (ex.: 1 instructeur ne peut être associé qu’à 1 seul
département)
62
Conception de bases de données (suite)
Spécification des besoins fonctionnels: spécifier quels
sont les besoins en terme d’opérations sur la BD
(chercher, modifier, supprimer, insérer…).
3.
◦
4.
5.
à cette étape, on peut revenir à l’étape 2 pour réviser le modèle
conceptuel.
Conception logique: traduction du modèle conceptuel
de haut niveau en modèle utilisé par la BD. (ex.: création
de tables pour le modèle relationnel à partir d’un modèle
entité-relation)
Conception physique: un schéma logique est défini
pour prendre des décisions pour la BD au niveau physique
◦ Ex.: choix de structures de stockage interne, du mécanisme
d’indexation….
63
Normalisation


Une méthode pour créer une base de données
relationnelle (sous forme de tables) est la
normalisation.
Cela consiste a analyser les dépendances entre attributs
pour créer des schémas logiques (des tables) qui sont
dans une forme normale appropriée.
◦ 1ière forme normale, 2ième forme normale, …5ième forme normale,
6ième forme normale.
Chaque forme normale énonce des critères à respecter.
 Plus une BD est dans une forme normale élevée, moins
il y a de risque de redondance et d’erreurs.

64
Exemple
Considérez la table suivante qui se nomme “Professeurs”:
Plusieurs problèmes potentiels. 
65
Exemple
Considérez la table suivante qui se nomme “Professeurs”:
Problème 1: Les information sur le département d’histoire sont
dupliquées.
• Plus coûteux à mettre à jour.
• Risque d’incohérence si un seul enregistrement est mis à jour.
66
Exemple
Considérez la table suivante qui se nomme “Professeurs”:
Problème 2: Un autre problème: Il est impossible d’ajouter un
département sans créer un enregistrement pour le département.
• Solution? Créer un département avec des valeurs « null » ? Mais
quoi faire lorsque le dernier membre est supprimé?
67
Architecture d’une BD
Principalement deux modules:
1) Gestionnaire de stockage:



gère les données stockées sur disque,
Insertion, recherche et mise à jour de l’information
stockée,
emploi des stratégies pour minimiser le nombre d’accès
disque, telles que:
◦ Indexation, bassins de tampons, B+ arbre.

vérifie les contraintes d’intégrité et les autorisations…
68
Architecture d’une BD
2) Processeur de requêtes:
transforme des requête écrites en langage de
requêtes en opérations au niveau physique,
 l’utilisation d’un langage de requêtes simplifie et
facilite l’accès aux données,
 cela permet aux utilisateurs de travailler au
niveau des vues plutôt qu’au niveau physique.

69
Utilisateurs des BD
Utilisateurs « naïfs »: ceux qui utilisent les logiciels
d’application (ex.: formulaires, page Web).
 Programmeurs de logiciels d’applications.
 Utilisateurs « sophistiqués »: interrogent la BD en
utilisant des requêtes ou des outils d’analyse spécialisés.
 Utilisateurs « spécialisés »: utilisateurs qui écrivent
des logiciels qui exploitent les connaissances dans la BD
de façon particulière (ex.: systèmes experts…)

70
L’administrateur de BD

Il est responsable de :
◦
◦
◦
◦
◦
définir les schémas de BD,
choisir une structure de stockage,
choisir une méthode d’accès,
gérer les autorisations d’accès,
Effectuer la maintenance:
 sauvegarde périodique de la BD,
 s'assurer qu’il y a assez d’espace disque et mettre à jour le
matériel,
 faire un suivi de la performance. Ex.: pour éviter que des
tâches de certains utilisateurs accaparent toutes les
ressources.
71
LES BD
RELATIONNELLES
(SUITE)
72
Introduction
Le modèle relationnel est au niveau
logique et au niveau des vues.
 Les données sont représentées comme
un ensemble de tables.
 Chaque table a un nom unique.

73
Exemple
Table “Instructeur”
74
Table “Cours”
Table “prérequis”
75
Fondements mathématiques du modèle
relationnel
Une table est une relation.
 Une ligne dans une table est un uplet (tuple, en anglais),
c’est-à-dire une liste de valeurs (des éléments).
 Une relation est un ensemble d’uplets (non ordonnés).

◦ Donc, l’ordre des uplets dans une table n’est pas pertinent.
Nom_restaurant
Téléphone
Type
Pizza+
123-4567
Pizzeria
Subway
124-4568
Fast-food
…
…
…
76
Fondements mathématiques du modèle
relationnel
Exemple:
 Une table nommée « Restaurants »:

Nom_restaurant
Téléphone
Type
Pizza+
123-4567
Pizzeria
Subway
124-4568
Fast-food
McDO
123-8765
Fast-food
Représentation mathématique de cette relation:
R = {(Pizza+, 123-4567, Pizzeria),
(Subway, 124-4568, Fast-food),
(McDo, 123-8765, Fast-food)}
(cette relation est un ensemble d’uplets de 3 éléments).
R ⊆ Chaines_caractères × Téléphones × Types_restaurants
(la relation est sous-ensemble du produit cartésien du domaine
de chaque attribut).
77
Fondements mathématiques du
modèle relationnel (suite)

Pour chaque attribut un domaine est défini
◦ ex.: le domaine de l’attribut « salaire » sont les entiers positifs
de 0 à 100,000.

Toutes les valeurs d’attributs sont atomiques (une
valeur d’attribut ne peut pas être un ensemble de
valeurs).
◦ Ex.: On ne peut pas stocker plusieurs numéros de téléphones
dans un attribut « téléphone » (en fait, si on le faisait, le SGBD le
traiterait comme un seul)
Nom_restaurant
Téléphone
Type
Pizza+
123-4567
Pizzeria
Subway
124-4568
Fast-food
…
…
…
78
Caractéristiques (suite)

La valeur « null » pour un attribut signifie que la valeur
est inconnue.
◦ Ex.: un professeur qui n’a pas de numéro de téléphone.
Table « Professeur »

Nom_professeur
Telephone
Jean P.
514-123-4567
Paul F.
506-123-7654
Fernand G.
Null
En pratique, il est préférable de ne pas avoir de valeurs
« null » car cela peut engendrer des problèmes.
79
Schémas et instances

Le schéma d’une relation (table) est le nom de la
relation suivi du nom de ses arguments (attributs):
◦ Ex. 1: instructeur( ID, name, dept_name, salary)
◦ Ex. 2: Cours(course_id, title, dept_name, credits)
Une instance d’une relation est l’ensemble des uplets
que contient cette relation dans une instance de la BD
(lignes dans une table).
 Certains attributs (ex.: dept_name) peuvent être
communs à plusieurs relations afin de relier les uplets de
plusieurs relations.

Exemple 
80
Exemple 1
Table « Professeur »
matricule_prof
nom_professeur
telephone
nom_departement
1234789
Jean P.
514-123-4567
Informatique
9898000
Paul F.
506-123-7654
Biologie
5656987
Fernand G.
506-124-7653
Administration
Table « Departement »
nom_departement
annee_fondation
bâtiment
Informatique
1980
ABC
Biologie
1970
ABC
Administration
1960
XHY
Table « Bâtiment »
bâtiment
adresse
ABC
1234 rue des pins
XYZ
1235 connaught
XHY
1236 mountain road
81
Exemple 2
Voici quelques relations qu’on pourrait définir dans le
schéma d’une BD pour une université:
Étudiant(ID_e, nom, dépt., crédits)
Professeur(ID_s, nom, telephone)
Suit_cours(ID_e, sigle, semestre, année, ID_s)
Salle_cours(batiment, num_local, capacité
Case_horaire(id_case, jour, temps_debut, temps_fin)
82
Les clés
Une clé est un attribut permettant de distinguer les
uplets au sein d’une relation.
 Aucun uplet ne doit être entièrement identique à un
autre uplet pour un attribut qui est une clé.

Exemple:
Table « Livres »
ISBN
Titre
Auteur
Date Éditeur
145343454
Java
Jean P.
2000
AAA
345755675
Les bases de données
Marc X.
2005
BBB
809076786
SQL
Tom Y.
2003
AAA
453452333
Java
Luc W.
2003
DDD
686786786
Scala pour les nuls
Tom Y.
2003
EEE
Quelles sont le(s) clé(s)?
• ISBN
83
Les super-clés
Une super-clé est un ensemble d’un ou plusieurs
d’attributs qui collectivement identifient uniquement
chaque uplet dans une relation.
Exemple:
Table « Livres »
ISBN
Titre
Auteur
Date Éditeur
145343454
Java
Jean P.
2000
AAA
345755675
Les bases de données
Marc X.
2005
BBB
809076786
SQL
Tom Y.
2003
AAA
453452333
Java
Luc W.
2003
DDD
686786786
Scala pour les nuls
Tom Y.
2003
EEE
Quelles sont le(s) super-clé(s)?

84
Table « Livres »
ISBN
Titre
Auteur
Date Éditeur
145343454
Java
Jean P.
2000
AAA
345755675
Les bases de données
Marc X.
2005
BBB
809076786
SQL
Tom Y.
2003
AAA
453452333
Java
Luc W.
2003
DDD
686786786
Scala pour les nuls
Tom Y.
2003
EEE
Quelles sont les super-clés?
• ISBN
• ISBN + Titre
• ISBN + Auteur
• ISBN + Auteur + Titre
• …
• Auteur + Titre
• Auteur + Date + Titre
• Auteur + Titre + Éditeur
• …
85
Les clés candidates
Une clé candidate est une super-clé minimale.
 Il est possible qu’il y aille plusieurs clés candidates pour
une relation.
 Exemple 

86
Exemple 1
Table « Livres »
ISBN
Titre
Auteur
Date Éditeur
145343454
Java
Jean P.
2000
AAA
345755675
Les bases de données
Marc X.
2005
BBB
809076786
SQL
Tom Y.
2003
AAA
453452333
Java
Luc W.
2003
DDD
686786786
Scala pour les nuls
Tom Y.
2003
EEE
Parmi les clés suivantes, quelles sont les clé(s)
candidate(s)?
• ISBN
• ISBN + Titre
• ISBN + Auteur
• ISBN + Auteur + Titre
• …
• Auteur + Titre
• Auteur + Date + Titre
• Auteur + Titre + Éditeur
• …
87
Exemple 2
Table « Employé »
Nom
Prénom
Date_naiss
Code postal
Telephone
Adresse
Parent
Jean
1985
H1J2K2
555-5533
1234 rue Main
Arsenault
Marc
1987
H2P3K3
…
Charest
Tom
1990
E1C3K4
…
Torvald
Linus
1992
E2C3K5
…
Sawyer
Tom
1990
E2F9P2
…
…
…
…
…
…
Quelles sont les clé(s) candidate(s)?
• Nom + prénom + Date_naissance
• Nom + prénom + Telephone
• Nom + prénom + Adresse
• …?
88
Les clés primaires
Une clé primaire est une clé candidate choisie par le
concepteur d’une BD comme moyen principal pour
identifier les uplets d’une relation.
 Contrainte: aucun uplet ne doit avoir la même valeur
qu’un autre uplet pour la clé primaire.
 Quand on conçoit une BD, il faut bien choisir les clés
primaires pour respecter la contrainte!

Exemple:
Table « Employé »
Nom
Prénom
Date_naiss
Code postal
Telephone
Adresse
Parent
Jean
1985
H1J2K2
555-5533
1234 rue Main
Arsenault
Marc
1987
H2P3K3
…
…
…
…
…
…
Que choisiriez-vous comme clé primaire?
89
Clés étrangères

Quand une relation r1 inclus dans ses attributs, une clé
primaire d’une autre relation r2 , il s’agit d’une clé
étrangère pour r1.
Exemple.: Soit les deux schémas suivants:
departement(nom_departement, bâtiment)
instructeur( matricule, nom, nom_departement, salaire)

nom_departement est une clé primaire de departement.
nom est une clé primaire de instructeur
nom_departement est une clé étrangère de instructeur.
Ainsi pour chaque valeur pour nom_departement dans la relation
instructeur, il doit y avoir une valeur pour nom_departement dans la
relation departement(ceci est un exemple de contrainte
d’intégrité référentielle)
90
Exemple 2
Table « Departement »
nom_departement
annee_fondation
bâtiment
Informatique
1980
ABC
Biologie
1970
ABC
Administration
1960
XHY
Table « Bâtiment »
bâtiment
adresse
ABC
1234 rue des pins
XYZ
1235 connaught
XHY
1236 mountain road
L’attribut « bâtiment » est une clé étrangère pour la
relation « Département »
91
Diagrammes de schéma
Le schéma d’une BD avec les clés
primaires et étrangères peut être
représenté par un diagramme de
schéma.
 Notation:

◦
◦
◦
◦
une relation: une boîte
nom d’une relation: rectangle bleu
clé primaire: souligné
clé étrangère: au début d’une flèche
92
Exemple – BD universitaire
diagramme de schéma
une relation: une boîte
nom d’une relation: rectangle bleu
clé primaire: souligné
clé étrangère: au début d’une flèche
93
Exemple – BD universitaire (suite)
schémas
94
Diagramme de schéma (suite)
Avec certains logiciels (ex.: Microsoft Access), il est
possible de créer des diagrammes de schéma
visuellement.
95
Opérations relationnelles

Une requête
◦ est une demande d’information à une base de
données,
◦ est exprimée dans un langage de requêtes,
◦ exprime un ensemble de contraintes sur le résultat
demandé,
◦ retourne une relation comme résultat.
96
Opérations relationnelles (suite)


Les requêtes sont traduites en opérations
relationnelles par le module de traitement des
requêtes d’un SGBD.
Une opération relationnelle
◦ prend en paramètre une ou plusieurs relations,
◦ retourne une relation comme résultat.

Il est possible de combiner plusieurs opérations
relationnelles pour effectuer des requêtes complexes.
97
Algèbre relationnel
Les opérations relationnelles varient
en fonction du langage de requêtes, mais
sont généralement semblables d’un
langage à l’autre.
 Elles définissent un algèbre relationnel.

98
Exemples d’opérations relationnelles
1.
Sélection: sélectionner tous les uplets dans une relation qui
satisfont une certaine condition.
Exemple 1: sélectionner tous les employés avec une date de
naissance > 1990
Table « Employé »
Nom
Prénom
Date_naiss
Parent
Jean
1985
Nom
Prénom
Date_naiss
Arsenault
Marc
1987
Charest
Tom
1990
Charest
Tom
1990
Torvald
Linus
1992
Torvald
Linus
1992
Sawyer
Tom
1990
Sawyer
Tom
1990
Résultat
Exemple 2 
99
Exemples d’opérations relationnelles
1.
Sélection: sélectionner tous les uplets dans une relation qui
satisfont une certaine condition..
Exemple 2: sélectionner tous les employés avec prénom = «
Jean ».
Table « Employé »
Nom
Prénom
Date_naiss
Parent
Jean
1985
Nom
Prénom
Date_naiss
Arsenault
Marc
1987
Parent
Jean
1985
Charest
Tom
1990
Torvald
Linus
1992
Sawyer
Tom
1990
Résultat
Exemple 3 
100
Exemples d’opérations relationnelles
1.
Sélection: sélectionner tous les uplets dans une relation qui
satisfont une certaine condition.
Exemple 3: sélectionner tous les employés nés entre 1980 et
1988, inclusivement.
Table « Employé »
Nom
Prénom
Date_naiss
Parent
Jean
1985
Nom
Prénom
Date_naiss
Arsenault
Marc
1987
Parent
Jean
1985
Charest
Tom
1990
Arsenault
Marc
1987
Torvald
Linus
1992
Sawyer
Tom
1990
Résultat
101
Exemples d’opérations relationnelles
(suite)
2.
Projection: sélectionner un sous-ensemble d’attributs d’une
relation.
Exemple: sélectionner les attributs « Nom » et « Prénom »
de la relation « Employé »
Table « Employé »
Résultat
Nom
Prénom
Date_naiss
Nom
Prénom
Parent
Jean
1985
Parent
Jean
Arsenault
Marc
1987
Arsenault
Marc
Charest
Tom
1990
Charest
Tom
Torvald
Linus
1992
Torvald
Linus
Sawyer
Tom
1990
Sawyer
Tom
102
Exemples d’opérations relationnelles
(suite)
3.
Jointure naturelle de deux relations: combiner les uplets de
deux relations avec les mêmes valeurs pour les mêmes attributs.
(note: il existe d’autres types de jointures…)
Exemple:
Table « Bâtiment »
bâtiment
adresse
ABC
1234 rue des pins
XYZ
1235 connaught
XHY
1236 mountain road
Table « Departement »
Résultat
nom
fondation
bâtiment
adresse
Informatique
1980
ABC
1234 …
nom
fondation
bâtiment
Biologie
1970
ABC
1234…
Informatique
1980
ABC
1960
XHY
1236…
Biologie
1970
ABC
Administratio
n
Administration
1960
XHY
103
Exemples d’opérations relationnelles
(suite)
4.
Produit cartésien: combine les uplets de deux relations en
formant toutes les combinaisons possibles.
Exemple:
Coûteux! Le produit cartésien de
deux relations de 2000 uplets
contiendra 2000 x 2000 =
4 millions d’uplets
Table « Cours »
sigle
sujet
INFO2012
Prog.
INFO2014
BD
INFO3020
Réseaux
Résultat
sigle
sujet
nom
fondation
Table « Departement »
INFO2012
Prog.
Info
1980
nom
fondation
INFO2012
Prog.
Bio
1970
Info
1980
INFO2012
Prog.
Adm
1960
Bio
1970
INFO2014
BD
Info
1980
Adm
1960
….
…
…
…
104
Exemples d’opérations relationnelles
(suite)
5.
Union: applique l’opération ensembliste d’union
Exemple: union des cours d’automne et d’hiver
Table « Cours Automne »
sigle
sujet
INFO2012
Prog.
INFO2014
BD
INFO3020
Réseaux
Résultat
sigle
sujet
Table « Cours Hiver »
INFO2012
Prog.
sigle
sujet
INFO2014
BD
INFO2014
BD
INFO3020
Réseaux
INFO3020
Réseaux
INFO4021
Arch. de réseau
INFO4021
Arch. de réseau
105
Exemples d’opérations relationnelles
(suite)
6.
Différence: applique l’opération ensembliste de soustraction
d’ensemble
Exemple: cours d’hiver non offert à l’automne
Table « Cours Automne »
sigle
sujet
INFO2012
Prog.
INFO2014
BD
INFO3020
Réseaux
Résultat
Table « Cours Hiver »
sigle
sujet
INFO2014
BD
INFO3020
Réseaux
INFO4021
Arch. de réseau
sigle
sujet
INFO4021
Arch. de réseau
106
Exemples d’opérations relationnelles
(suite)
7.
Renomer: renomme un attribut d’une relation ou une relation
elle même
Exemple: cours d’hiver non offert à l’automne
Résultat (relation anonyme)
Table « Cours Hiver seulement »
sigle
sujet
sigle_cours
sujet
INFO4021
Arch. de réseau
INFO4021
Arch. de réseau
107
Exemples d’opérations relationnelles
(suite)
Autres opérations ensemblistes: intersection…
9. Opérations d’agrégation:
8.
◦
◦
ajout de fonction telles que somme, minimum, maximum, moyenne et
compte pour cumuler l’information sur un attribut donné.
Exemple: obtenir la date de naissance maximale
Table « Employé »
Nom
Prénom
Date_naiss
Parent
Jean
1985
Arsenault
Marc
1987
Charest
Tom
1990
Torvald
Linus
1992
Sawyer
Tom
1990
Résultat
1992
108
Opérations relationnelles (suite)
De plus, il est possible de combiner plusieurs opérations.
Par exemple 
109
Exemple 1 de combinaison d’opérations
Table « Bâtiment »
bâtiment
adresse
ABC
1234 …
XYZ
1235 …
XHY
1236 …
jointure naturelle
nom
Table « Departement »
Informatique
fondation
adresse
1980
ABC
1234 …
1970
ABC
1234…
1960
XHY
1236…
?
nom
fondation
bâtiment
Biologie
nformatique
1980
ABC
Biologie
1970
ABC
Administratio
n
Administration
1960
XHY
Quelles opérations
ont été effectuées?
(deux)
bâtiment
projection
nom
adresse
Informatique
1234 …
Biologie
1234…
Administration
1236…
110
Exemple 2 de combinaison d’opérations
Table « departement »
Numéro
département
1
A
2
B
Table « items_prix »
nom
prix_unitaire
Scie
20.00
nom
Code
Dep
Marteau
15.00
Scie
1
A
Éclume
5.00
Marteau
1
A
Éclume
1
A
Scie
2
B
Marteau
2
B
Éclume
2
B
Quelles opérations
ont été effectuées?
111
Opérations relationnelles (suite)
Certaines opérations relationnelles peuvent
retourner des uplets en double.
 En fonction des implémentations, ces doublons
peuvent être conservés ou supprimés,
dépendant si une implémentation respecte la
définition d’ensemble mathématique ou non.
 SQL est basé sur l’idée d’algèbre relationnel,
mais propose également d’autres éléments pour
compléter.

112
Opérations relationnelles (suite)
Pour obtenir le résultat à une requête, il existe
souvent plusieurs séquences d’opérations
relationnelles équivalentes.
 Un bon module de traitement de requête,
optimisera le choix des opérations pour une
meilleure performance.


Exemple 
113
Quelques exemples de principes
d’optimisation
effectuer l’opération de sélection en premier réduit le
nombre d’information à traiter pour les opérations
suivantes.
 certaines opérations sont sans effet (ex.: plusieurs
sélections identiques sur une même relation).
 certaines opérations sont commutatives (ex.: sélection)
 une sélection avec plusieurs conditions sous forme de
conjonction, peut être faite en deux opérations
successives.

114
Quelques exemples de principes
d’optimisation
une sélection avec plusieurs conditions sous forme de
disjonction, peut être faite en l’union du résultat de
deux opérations successives.
 les différents types de jointures sont de façon générale
les opérations les plus coûteuses
 dans certains cas, il est préférable de faire une
projection avant une sélection pour réduire le nombre
d’uplets.
 …

115
INTRODUCTION À SQL
116
Introduction à SQL







Le langage de requêtes le plus utilisé est SQL.
Langage multiplateforme.
Il en existe d’autres, expérimentaux ou commerciaux.
SQL est inspiré de Sequel un langage développé par
IBM dans les années 1970
Plusieurs versions de SQL.
La dernière spécification est SQL-2011. La
spécification peut être achetée auprès de l’ISO.
Le langage SQL peut être traité légèrement
différemment par certaines implémentations (ex.:
Access).
117
Introduction à SQL (suite)
Le langage SQL est composé de:
◦ Un LDD pour spécifier:





le schéma logique de la BD,
des contraintes d’intégrité,
des vues,
contrôler le début et la fin des transactions,
les autorisations
◦ Un LMD pour interroger la BD et la modifier.
118
Langage de définition de SQL

Il permet de définir:
◦
◦
◦
◦
◦
◦
le schéma pour chaque relation,
le domaine de chaque attribut,
les contraintes d’intégrité,
les indices à maintenir pour chaque relation,
les autorisations pour chaque relation,
la structure de stockage de chaque relation
sur le disque
119
Types de base
Plusieurs types prédéfinis:
 char(n): chaîne de caractères de taille n,
 varchar(n): chaîne de caractères à taille variable avec
maximum n caractères.
 int : entier de 4 octets
 tinyint: entier de 1 octet
 numeric(p, d): des nombres avec maximum p chiffres
avant la virgule et d après la virgule,
 smallint, real, double, float, date…. etc.
120
Création d’une table
Commande pour la création d’une table:
create table departement
(nom_dept varchar(20),
bâtiment
varchar(15),
budget
numeric(12,2)
);
titre de la relation
définition des
attributs
Il est également possible d’utiliser des contraintes
supplémentaires.
Exemple 
121
Exemple
create table departements
(nom_dept varchar(20),
bâtiment
varchar(15),
budget
numeric(12,2)
primary key(nom_dept));
indication qu’un attribut est une
clé primaire.
indication que la valeur « Null »
n’est pas acceptée pour un
attribut
create table professeurs
(matricule char(5) not null,
nom_dept varchar(20) ,
salaire
numeric(6,2)
primary key(matricule)
foreign key(nom_dept) references(departements);
Indication qu’un attribut est une clé étrangère
Note: Il est possible de déclarer plusieurs clés
étrangères pour une même table.
122
Insertion/supression
Insertion d’un uplet:
insert into professeurs
values(12345, ‘informatique’, 30000);
Suppression de tous les uplets d’une relation
(sans supprimer la relation):
delete from professeurs;
Suppression d’une relation:
drop table professeurs;
123
Ajout/suppression d’attributs

Ajout d’un attribut à une relation:
alter table professeurs add annees_experience int;

Suppression d’un attribut:
alter table professeurs drop annees_experience;
124
Structure d’une requête SQL
les attributs à sélectionner
Select A1,A2, A3…
from r1,r2, r3…
where C;
les relations à laquelle
s’applique la requête
la condition pour
déterminer les
enregistrements à
sélectionner.
125
Requête sur une relation
Exemple 1:
select nom
from employe;
Résultat
Table « employe »
nom
prenom
date_naiss
Nom
Parent
Jean
1985
Parent
Arsenault
Marc
1987
Arsenault
Charest
Tom
1990
Charest
Torvald
Linus
1992
Torvald
Sawyer
Tom
1990
Sawyer
126
Requête sur une relation
Exemple 2:
select nom, prenom
from employe;
Résultat
Table « employe »
nom
prenom
date_naiss
nom
prenom
Parent
Jean
1985
Parent
Jean
Arsenault
Marc
1987
Arsenault
Marc
Charest
Tom
1990
Charest
Tom
Torvald
Linus
1992
Torvald
Linus
Sawyer
Tom
1990
Sawyer
Tom
127
Requête sur une relation
Exemple 3:
select prenom
from employe;
Résultat
Table « employe »
nom
prenom
date_naiss
prenom
Parent
Jean
1985
Jean
Arsenault
Marc
1987
Marc
Charest
Tom
1990
Tom
Torvald
Linus
1992
Linus
Sawyer
Tom
1990
Tom
Observation:
certaines valeurs
apparaissent
plusieurs fois
dans la relation
qui est le résultat
de la requête!
Comment
éliminer? 
128
Requête sur une relation
Exemple 4:
select distinct prenom
from employe;
Résultat
Table « employe »
nom
prenom
date_naiss
prenom
Parent
Jean
1985
Jean
Arsenault
Marc
1987
Marc
Charest
Tom
1990
Tom
Torvald
Linus
1992
Linus
Sawyer
Tom
1990
Note:
Éliminer les
doublons peut
être coûteux en
terme de
traitement!
129
Requête sur une relation
Il est possible d’utiliser les opérateurs arithmétiques * / +
- dans la clause select.
Exemple 5:
select nom, salaire * 1.1
from employe;
Résultat
Table « employe »
nom
prenom
salaire
nom
Parent
Jean
20000
Parent
22000
Arsenault
Marc
30000
Arsenault
33000
Charest
Tom
50000
Charest
55000
Torvald
Linus
60000
Torvald
66000
Sawyer
Tom
90000
Sawyer
99000
130
Requête sur une relation
La clause « where » permet de sélectionner seulement les uplets qui
satisfont une condition.
Exemple 6:
select nom, salaire
from employe
where salaire >= 50000;
Table « employe »
nom
prenom
salaire
Parent
Jean
20000
nom
salaire
Arsenault
Marc
30000
Charest
50000
Charest
Tom
50000
Torvald
60000
Torvald
Linus
60000
Sawyer
90000
Sawyer
Tom
90000
Résultat
Note: Il est possible d’utiliser > >= < <= <> and or not dans la clause where.
131
Requête sur une relation
La clause « where » permet de sélectionner seulement les uplets qui
satisfont une condition.
Exemple 7:
select nom, salaire
from employe
where prenom = ‘Tom’ and salaire >= 50000;
Table « employe »
nom
prenom
salaire
Parent
Jean
20000
Arsenault
Marc
30000
Charest
Tom
50000
Torvald
Linus
60000
Sawyer
Tom
90000
Résultat
nom
salaire
Charest
50000
Sawyer
90000
Note: Il est possible d’utiliser > >= < <= <> and or not dans la clause where.
132
Requête sur une relation
La clause « where » permet de sélectionner seulement les uplets qui
satisfont une condition.
Exemple 8:
select nom, salaire
from employe
where prenom <> ‘Tom’
Table « employe »
nom
prenom
salaire
Parent
Jean
20000
Arsenault
Marc
30000
Charest
Tom
50000
Torvald
Linus
60000
Sawyer
Tom
90000
Résultat
nom
salaire
Parent
20000
Arsenault
30000
Torvald
60000
Note: Il est possible d’utiliser > >= < <= <> and or not dans la clause where.
133
Requêtes sur plusieurs relations
Il est parfois nécessaire d’accéder simultanément à de
l’information provenant de plusieurs tables.
Exemple:
select nom, adresse
Table « Bâtiments »
batiment
adresse
ABC
1234 rue des pins
XYZ
1235 connaught
XHY
1236 mountain road
Table « departements »
nom
fondation
bâtiment
Informatique
1980
ABC
Biologie
1970
ABC
Administration
1960
XHY
from batiments, departements
where batiments.batiment =
departements.batiment.
Résultat
nom
adresse
Informatique
1234 rue des pins
Biologie
1234 rue des pins
Administration
1236 mountain road
• La clause from spécifie les relations.
• La clause where spécifie la condition
d’appariement.
• Le « . » est utilisé pour éliminer l’ambiguïté
134
Interprétation
select nom, adresse
from batiments, departements
where batiments.batiment = departements.batiment.
La requête SQL peut être interprétée comme suit:
Créer une nouvelle relation résultat vide.
Pour chaque uplet x dans batiments
Pour chaque uplet y dans departements
Concatener x et y en un nouveau uplet si
x.batiment = y.batiment.
Ajouter le uplet résultant dans la relation résultat.
Retourner la relation résutat.
Note: En pratique, un processeur de requête SQL utilise une approche optimisée pour
éviter de tester toutes les possibilités.
135
Requêtes sur plusieurs relations
(suite)
select nom, adresse
from batiments, departements
where batiments.batiment = departements.batiment.
Qu’arrive t-il si on omet la clause where?
 Le résultat de la requête est alors le produit cartésien
des deux relations.
 Toutes les combinaisons possibles entre les uplets des
deux relations sont effectuées.
 Cela peut générer un très grand nombre d’uplets
comme résultat!
136
Illustration – produit cartésien
Résultat
Table « Cours »
sigle
sujet
nom
fondation
sigle
sujet
INFO2012
Prog.
Info
1980
INFO2012
Prog.
INFO2012
Prog.
Bio
1970
INFO2014
BD
INFO2012
Prog.
Adm
1960
INFO3020
Réseaux
INFO2014
BD
Info
1980
INFO2014
BD
Bio
1970
Table « Departement »
nom
fondation
INFO2014
BD
Adm
1960
Info
1980
INFO3020
Réseaux
Info
1980
Bio
1970
INFO3020
Réseaux
Bio
1970
Adm
1960
INFO3020
Réseaux
Adm
1960
select sigle, sujet, nom, fondation
from cours, departement
La jointure naturelle
Table « Bâtiments »
bâtiment
adresse
ABC
1234 …
XYZ
1235 …
XHY
1236 …
Jointure naturelle: concaténer les uplets de
deux relations qui ont les mêmes valeurs
pour les attributs ayant le même nom.
nom
Table « Departement »
Informatique
adresse
?
nom
fondation
bâtiment
Biologie
nformatique
1980
ABC
Administration
Biologie
1970
ABC
Administration
1960
XHY
1234 …
1234…
1236…
select nom, adresse
from batiments natural join departements
Note: Ceci est équivalent à:
select nom, adresse
from batiments, departements
where batiments.batiment = departements.batiment.
138
La jointure naturelle (suite)
De façon plus générale, une jointure
naturelle peut porter sur plus de deux
relations.
Syntaxe générale:
139
Un autre exemple de jointure naturelle
Considérez les trois tables suivantes:
Table « professeurs »
Table
« donne »
Table « cours »
IDProf
bâtiment
Nom
IDProf
sigle
sigle
bâtiment
Titre
123
ABC
Paul
123
INFO1000
INFO1000
ABC
Java
456
XYZ
Léo
123
INFO1002
INFO1002
XYZ
C++
678
XHY
Luc
678
INFO1003
INFO1003
XHY
SQL
Quel est la signification de la requête suivante ?
select nom, titre
from professeurs natural join donne, cours
where donne.sigle = cours.sigle;
Obtenir les uplets « nom de professeur » et « titre de cours » pour tous
les cours.
140
Table « professeurs »
Table
« donne »
Table « cours »
IDProf
bâtiment
Nom
IDProf
sigle
sigle
bâtiment
Titre
123
ABC
Paul
123
INFO1000
INFO1000
ABC
Java
456
XYZ
Léo
123
INFO1002
INFO1002
XYZ
C++
678
XHY
Luc
678
INFO1003
INFO1003
XHY
SQL
Est-ce que la requête précédente :
select nom, titre
from professeurs natural join donne, cours
where donne.sigle = cours.sigle;
est équivalente à cette requête:
select nom, titre
from professeurs natural join donne natural join cours
?
Non, cette requête ne retourne que les uplets où un cours est
donné dans le bâtiment associé au professeur qui donne le cours
(parce que « bâtiment » est présent dans deux tables).
141
Une façon de résoudre ce problème:
select nom, titre
from professeurs natural join donne natural joins cours
select nom, titre
from (professeurs natural join donne) join cours using
(cours.sigle);
Le mot-clé join indique de faire une jointure. La
différence avec natural join est que join permet de
spécifier le(s) attribut(s) à utiliser pour faire la jointure.
142
AUTRES OPÉRATIONS
143
Renommer un attribut
Dans certaines situations, il est nécessaire de
(re)nommer un attribut:
 La clause select d’une requête crée une
relation avec deux attributs ayant le même nom
provenant de deux tables différentes.
 Un attribut est créé par une opération
arithmétique ce qui résulte en un attribut sans
nom.
 On veut renommer un attribut dans le résultat
d’une requête.
144
Renommer un attribut (suite)
Comment renommer?
ancien nom
Exemple:
nouveau nom
select titre as titre_cours, sigle
from cours
where batiment = ‘ABC’;
Table « cours »
Résultat
sigle
batiment
titre
titre_cours
sigle
INFO1000
ABC
Java
Java
INFO1000
INFO1002
ABC
Cobol
INFO1003
XHY
SQL
Cobol
INFO1002
145
Renommer un attribut (suite)
Un autre exemple:
select nom, salaire * 1.1 as nouveau_salaire
from employe;
Résultat
Table « employe »
nom
prenom
salaire
nom
nouveau_salaire
Parent
Jean
20000
Parent
22000
Arsenault
Marc
30000
Arsenault
33000
Charest
Tom
50000
Charest
55000
Torvald
Linus
60000
Torvald
66000
Sawyer
Tom
90000
Sawyer
99000
146
Renommer (suite)
On peut aussi renommer une relation à
l’intérieur d’une requête pour simplifier la
requête:
select professeurs.nom, donne.sigle
from professeurs, donne
where professeurs.ID = donne.ID;
select T.nom, S.sigle
from professeurs as T, donne as S
where T.ID = S.ID;
147
Renommer (suite)
On peut aussi renommer une relation afin
d’utiliser deux fois la même relation dans la
même requête.
 Exemple:

Cette requête retourne tous les instructeurs ayant
un salaire supérieur à celui de l’instructeur le moins
bien payé du département de biologie.
148
Opération sur les chaînes de caractères
Les chaîne de caractères sont représentées avec
des guillemets simples. Ex.: ‘ordinateur’
 Opérateur de comparaison =
Ex.: ‘ordinateur’ = ‘maison’ est faux
Ex.: ‘ordinateur’ = ‘ordinateur’ est vrai
Ex.: ‘ordinateur’ = ‘Ordinateur’ est vrai ou
faux dépendant de l’implémentation.

149
Opération sur les chaînes de caractères (suite)
Opérateur de concaténation ||
Ex.: prenom || ‘ ‘ || nom
 Conversion en majuscule:
upper(s)
 Conversion en minuscule:
lower(s)
 Enlever les espaces à la fin d’une
châine:
trim(s)

150
Exemple
Concaténation avec Oracle:
SELECT 'Le nom est: ' || nom_famille
FROM employe;
Concaténation avec Access:
SELECT 'Le nom est: ' & nom_famille
FROM employe;
151
Opération sur les chaînes de caractères (suite)
Appariement de motif:
◦ un motif est une chaîne de caractère sensible à la casse.
◦ SQL permet d’énoncer qu’un motif doit apparaître dans une
chaîne de caractères.
◦ Un motif est défini en utilisant les symboles:
 % le caractère s’apparie avec n’importe quelle chaîne.
 _ le caractère s’apparie avec n’importe quel caractère.
◦ Exemples:




‘%Test’ signifie toutes les chaînes de caractères se terminant par ‘Test’.
‘Test%’ signifie toutes les chaînes de caractères débutant par ‘Test’.
‘_ _ _’ signifie les chaînes contenant 3 caractères.
‘_ _ _%’ signifie les chaînes avec au moins 3 caractères.
152
Opération sur les chaînes de caractères (suite)
Appariement de motif (suite): Les motifs sont utilisés dans les
requêtes en utilisant l’opérateur like.
Exemple 1:
select nom
from produits
where produits.nom like ‘%C++%’;
Table « produits »
nom
auteur
ISBN
Le C++
Paul
12345678
Le Java
Marc
98767575
C++ en 24h
Peter
26456456
Java en 24h
Linus
45645645
SQL
Jean
83454689
Résultat
nom
Le C++
C++ en 24h
153
Opération sur les chaînes de caractères (suite)
Exemple 2:
select auteur
from produits
where produits.auteur like ‘P_ _ _’;
Table « produits »
nom
auteur
ISBN
Le C++
Paul
12345678
Le Java
Marc
98767575
C++ en 24h
Peter
26456456
Java en 24h
Linus
45645645
SQL
Jean
83454689
Résultat
auteur
Paul
154
Opération sur les chaînes de caractères (suite)
Appariement de motif (suite):
◦ Si on veut utiliser les caractères % et _ dans un motif, il faut
utiliser \% et \_
◦ De la même façon, il faut utiliser \\ pour le caractère \
select nom
from produits
where produits.nom like ‘\%cereales%’;
155
Opération sur les chaînes de caractères (suite)
Appariement de motif (suite):
◦ SQL offre aussi un opérateur not like:
select nom
from produits
where produits.nom not like ‘%cereales%’;
◦ SQL:1999 introduit aussi l’utilisation d’expressions
régulières similaires à celles d’Unix avec l’expression
similar to.
156
Sélection de tous les attributs
Il est possible de sélectionner tous les attributs d’une
relation en utilisant « * ».
Exemple 1:
select *
from produits where produits.nom not like ‘%Java%’;
Table « produits »
nom
auteur
ISBN
Le C++
Paul
12345678
Le Java
Marc
98767575
C++ en 24h
Peter
26456456
Java en 24h
Linus
45645645
SQL
Jean
83454689
Résultat
nom
auteur
ISBN
Le C++
Paul
12345678
C++ en 24h
Peter
26456456
SQL
Jean
83454689
157
Sélection de tous les attributs
Exemple 2:
select departements.*, batiments.nom
from batiments, departements
where batiments.batiment = departements.batiment.
Table « Bâtiments »
batiment
nom
ABC
Taillon
XYZ
Jeanne
XHY
Rémi
Table « Departement »
nom
fondation
batiment
nom
nom
fondation
batiment
Informatique
1980
ABC
Taillon
Informatique
1980
ABC
Biologie
1970
ABC
Taillon
Biologie
1970
ABC
Administration
1960
XHY
Rémi
Administration
1960
XHY
Note: En général, il est préférable de ne pas utiliser « * » dans
du code de production.
158
Ordonner les uplets
La clause order by permet de trier les uplets résultant
d’une requête.
Par défaut, le tri est par ordre croissant.
select nom, prenom
from employe order by prenom;
Table « employe »
nom
prenom
date_naiss
Parent
Jean
1985
Arsenault
Marc
1987
Charest
Tom
1990
Torvald
Linus
1992
Sawyer
Tom
1990
Résultat
nom
prenom
Parent
Jean
Torvald
Linus
Arsenault
Marc
Charest
Tom
Sawyer
Tom
159
Ordonner les uplets (suite)
Il est possible d’utiliser asc ou desc pour choisir de trier en
ordre croissant ou décroissant.
 De plus, il est possible de trier avec plusieurs attributs.

select prenom, salaire
from employe order by prenom asc, salaire desc;
Table « employe »
nom
prenom
salaire
Parent
Jean
5000
Arsenault
Marc
6000
Charest
Tom
5000
Torvald
Linus
5500
Sawyer
Tom
7000
Résultat
prenom
salaire
Jean
5000
Linus
5500
Marc
6000
Tom
7000
Tom
5000
160
Intervalles dans la clause where
L’opérateur between permet de spécifier
des intervalles.
Exemple:
select nom, prix
from produits
where prix between 20 and 100;
Ceci est équivalent à:
select nom, prix
from produits
where prix <= 100and prix >= 20;
161
Comparaison d’uplets
La notation (v1, v2, … vn) peut être utilisée pour représenter un
uplet de taille n ayant les valeurs v1, v2, … vn.
 Cela peut être utilisé pour des fins de comparaisons dans une
requête.
 Par exemple:

select cours.sigle
from cours, professeur
where (cours.prof, professeur.dept) = (professeur.id, ‘info’);
est équivalent à :
select cours.sigle
from cours, professeur
where cours.prof = professeur.id and professeur.dept = ‘info’;
= est vrai si tous les attributs sont identiques.
>= , <= …
162
Opérations ensemblistes
Il est possible de réaliser l’union, intersection
et la différence de relations.
 Considérez l’exemple suivant: 

163
On désire obtenir les sigles des cours offerts à l’hiver 2011.
Table « cours »
select sigle from cours
where annee = 2011 and session =‘hiver';
sigle
session
annee
INFO1000
ete
2012
INFO1002
hiver
2011
sigle
INFO1003
ete
2012
INFO1002
INFO1000
ete
2012
INFO1003
INFO1003
hiver
2011
INFO1004
automne
2009
On désire obtenir les sigles des cours offert à l’été 2012.
select sigle from cours
where annee = 2012 and session =‘ete';
sigle
session
annee
INFO1000
ete
2012
INFO1002
hiver
2011
sigle
INFO1003
ete
2012
INFO1000
INFO1000
ete
2012
INFO1003
INFO1003
hiver
2011
INFO1000
INFO1004
automne
2009
Notez la présence de doublons
164
Maintenant, si on désire obtenir les sigles des
cours offerts à l’hiver 2011 ou à l’été 2012:
(select sigle from cours
where annee = 2011 and session =‘hiver)
union
(select sigle from cours
where annee = 2012 and session =‘ete');
Note: l’union élimine les doublons.
sigle
session
annee
INFO1000
ete
2012
INFO1002
hiver
2011
INFO1003
ete
2012
INFO1000
ete
2012
INFO1003
hiver
2011
INFO1004
automne
2009
sigle
INFO1000
INFO1002
INFO1003
165
Si on désire conserver les doublons:
(select sigle from cours
where annee = 2011 and session =‘hiver)
union all
(select sigle from cours
where annee = 2012 and session =‘ete');
sigle
session
annee
INFO1000
ete
2012
sigle
INFO1002
hiver
2011
INFO1000
INFO1003
ete
2012
INFO1000
INFO1000
ete
2012
INFO1002
INFO1003
hiver
2011
INFO1003
INFO1004
automne
2009
INFO1003
166
Si on désire obtenir les sigles des cours
offerts à l’hiver 2011 ainsi qu’à l’été 2012:
(select sigle from cours
where annee = 2011 and session =‘hiver)
intersect
(select sigle from cours
where annee = 2012 and session =‘ete');
sigle
session
annee
INFO1000
ete
2012
INFO1002
hiver
2011
INFO1003
ete
2012
sigle
INFO1000
ete
2012
INFO1003
INFO1003
hiver
2011
INFO1004
automne
2009
Note: Il est possible d’utiliser intersect all pour conserver les doublons, s’il y en a.
Ex.: Si INFO4000 apparaît 4 fois dans le résultat du premier select et 2 fois dans le
résultat du deuxième select, le résultat contiendra 2 fois INFO4000.
167
Si on désire obtenir les sigles des cours
offerts à l’hiver 2011 mais pas à l’été 2012:
(select sigle from cours
where annee = 2011 and session =‘hiver)
except
(select sigle from cours
where annee = 2012 and session =‘ete');
sigle
session
annee
INFO1000
ete
2012
INFO1002
hiver
2011
INFO1003
ete
2012
sigle
INFO1000
ete
2012
INFO1002
INFO1003
hiver
2011
INFO1004
automne
2009
Note: Il est possible d’utiliser except all pour conserver les doublons, s’il y en a.
Ex.: Si INFO4000 apparaît 5 fois dans le résultat du premier select et 3 fois dans le
résultat du deuxième select, le résultat contiendra 2 fois INFO4000.
168
Les valeurs nulles
Une valeur null dans une BD indique l’absence de valeur.
 Le résultat d’une opération arithmétique ( / * + - )
appliquée à null est null .
 Le résultat d’une comparaison avec null retourne la valeur
unknown au lieu de true ou false

◦ Ex.: 1 < null
◦ Ex.: r.a >= 5 où r.a prend la valeur null

Le résultat des opérateurs booléens (and, or not) à
unknown est unknown .
169
Les valeurs nulles (suite)

Exemple:
Quel est le résultat de not (r.a >= 5)
quand r.a prend la valeur null ?
Note: Si une clause where est évaluée à
unknown ou false pour un uplet, le uplet n’est
pas inclus dans le résultat.
170
Les valeurs nulles (suite)

Il est possible de tester si une valeur est nulle avec is
null et is not null.

Exemple:
select cours.sigle
from cours
where cours.titre is null;
Certaines implémentations de SQL offrent aussi is unknown
et is not unknown.
 Note: Si on utilise select distinct, deux uplets sont égaux, s’ils
ont les même valeurs pour les mêmes attributs, incluant les
valeurs nulles. Cette interprétation est différente de null = null
qui retourne unknown.

171
Les fonctions d’agrégation

SQL offrent cinq fonctions d’agrégation:
◦ avg : la moyenne
◦ min : le minimum
◦ max : le maximum
◦ sum : la somme
◦ count : le nombre d’uplets.
 Ces fonctions prennent en entrée un ensemble de
valeurs (entiers, chaînes de caractères, dates…).
 Exemples 
172
On désire obtenir le salaire moyen des
employés.
select avg (salaire)
from employe;
Table « employe »
nom
prenom
salaire
Parent
Jean
5000
Arsenault
Marc
6000
Charest
Tom
5000
Torvald
Linus
5500
Sawyer
Tom
7000
5700
Le résultat est une valeur
pour un attribut non
nommé.
Si on veut le nommer 
173
select avg (salaire) as moyenne
from employe;
Table « employe »
nom
prenom
salaire
Parent
Jean
5000
Arsenault
Marc
6000
Charest
Tom
5000
Torvald
Linus
5500
Sawyer
Tom
7000
moyenne
5700
174
On désire obtenir le nombre de cours offerts.
select count(sigle) as compte
from cours;
Table « cours »
sigle
batiment
titre
INFO1000
ABC
Java
INFO1002
ABC
Cobol
INFO1003
XHY
SQL
compte
3
« count » compte le nombre de valeurs dans
la colonne « sigle »
175
On désire obtenir le nombre de bâtiments
qui offrent des cours:
select count(batiment) as compte
from cours;
Table « cours »
sigle
batiment
titre
INFO1000
ABC
Java
INFO1002
ABC
Cobol
INFO1003
XHY
SQL
compte
3
Cette requête ne donne pas le
résultat escompté!
176
Une solution:
select count(distinct batiment) as
compte from cours;
Table « cours »
sigle
batiment
titre
INFO1000
ABC
Java
INFO1002
ABC
Cobol
INFO1003
XHY
SQL
compte
2
177
Et s’il y a des valeurs nulles?
select count(batiment) as compte
from cours;
Table « cours »
sigle
batiment
titre
INFO1000
ABC
Java
INFO1002
ABC
Cobol
INFO1003
null
SQL
compte
2
Les valeurs nulles ne sont pas comptées.
178
Si on veut compter le nombre de uplets dans
une relation:
select count(*) as compte
from cours;
Table « cours »
sigle
batiment
titre
INFO1000
ABC
Java
INFO1002
ABC
Cobol
INFO1003
null
SQL
compte
3
Note: distinct ne peut pas être utilisé avec count(*).
179
Pour obtenir la valeur minimum d’un attribut:
select prenom, min(salaire) as salairemin
from employe;
Table « employe »
nom
prenom
salaire
Parent
Jean
3000
Arsenault
Marc
6000
Charest
Tom
5000
Torvald
Linus
5500
Sawyer
Tom
7000
prenom
salairemin
Jean
3000
De façon similaire, on pourrait utiliser « max » pour obtenir la
valeur maximale pour un attribut.
180
Pour obtenir la somme des valeurs d’un
attribut:
select sum(salaire) as total
from employe;
Table « employe »
nom
prenom
salaire
Parent
Jean
3000
Arsenault
Marc
6000
Charest
Tom
5000
Torvald
Linus
5500
Sawyer
Tom
7000
total
26500
181
Agrégation par groupes
Le mot-clé group by permet d’agréger par groupes.
select departement, avg(salaire) as salaire_moyen
from professeurs
group by departement;
Table « professeurs »
nom
departement
salaire
departement
salaire_moyen
Jean
Chimie
5000
Chimie
4500
Paul
Chimie
4000
Biologie
2500
Luc
Biologie
3000
Math
10000
Martin
Biologie
2000
Sylvain
Math
10000
182
Agrégation par groupes (suite)
On peut appliquer une condition aux
groupes en utilisant « having ».
select departement, avg(salaire) as salaire_moyen
from professeurs
group by departement having avg(salaire) > 3000;
Table « professeurs »
nom
departement
salaire
departement
salaire_moyen
Jean
Chimie
5000
Chimie
4500
Paul
Chimie
4000
Math
10000
Luc
Biologie
3000
Martin
Biologie
2000
Sylvain
Math
10000
183
Agrégation par groupes (suite)
On peut utiliser plusieurs attributs dans un
« group by »
SELECT pays, count(*) as compte FROM
livres GROUP BY pays, année;
Table « livres »
pays
année
titre
Canada
2014
Pascal
Canada
2014
France
pays
compte
Canada
C#
2
France
2014
Java
2
Pérou
France
2014
C++
1
Pérou
1980
Cobol
184
Agrégation par groupe (suite)
L’ordre d’évaluation des requêtes SQL est
le suivant:
51234-
185
Note sur l’agrégation
Lorsque « group_by » est utilisé, les attributs dans
« select » doivent être soit présents dans « group
by » ou être le résultat d’une fonction
d’agrégation
select departement, nom, avg(salaire)
from professeurs
group by departement having avg(salaire) > 3000;
Table « professeurs »
nom
departement
salaire
Jean
Chimie
5000
Paul
Chimie
4000
Luc
Biologie
3000
Martin
Biologie
2000
Ceci est interdit!
186
Agrégation (suite)
Traitement des valeurs nulles:
Toutes les fonctions d’agrégation ignorent
les valeurs nulles sauf count(*).
187
SELECT TOP / LIMIT
Quand on manipule de très grandes tables, on peut vouloir limiter
le nombre d’uplets en résultat.
N’est pas supporté par toutes les SGBD.


SELECT TOP 2 pays, titre FROM livres;
Table « livres »
pays
année
titre
pays
titre
Canada
2014
Pascal
Canada
Pascal
Canada
2014
C#
Canada
C#
France
2014
Java
France
2014
C++
Pérou
1980
Cobol
En général, retourne les premiers uplets de la table.
188
REQUÊTE IMBRIQUÉES
189
Exemple:
SELECT nom, prenom FROM employe
WHERE salaire >
(SELECT AVG(salaire) FROM employe);
Table « employe »
nom
prenom
salaire
Parent
Jean
5000
nom
prenom
Arsenault
Marc
6000
Arsenault
Marc
Charest
Tom
5000
Sawyer
Tom
Torvald
Linus
5500
Sawyer
Tom
7000
La moyenne est 5875
190
L’appartenance à un ensemble
Les mots-clés in et not in permettent de vérifier
si un uplet appartient à une relation.
Exemple 
191
Si on désire obtenir les sigles des cours
offerts à l’hiver 2011 ainsi qu’à l’été 2012:
select sigle from cours
where annee = 2011 and session =‘hiver
and sigle in
(select sigle from cours
where annee = 2012 and session =‘ete');
sigle
session
annee
INFO1000
ete
2012
INFO1002
hiver
2011
INFO1003
ete
2012
INFO1000
ete
2012
INFO1003
hiver
2011
INFO1004
automne
2009
sigle
INFO1000
INFO1003
INFO1000
sigle
INFO1003
192
select sigle from cours
where annee = 2011 and session =‘hiver
and sigle in
(select sigle from cours
where annee = 2012 and session =‘ete');
est équivalent à:
(select sigle from cours
where annee = 2011 and session =‘hiver)
intersect
(select sigle from cours
where annee = 2012 and session =‘ete');
193
Si on désire obtenir les sigles des cours offerts
à l’hiver 2011 mais pas à l’été 2012:
select sigle from cours
where annee = 2011 and session =‘hiver
and sigle not in
(select sigle from cours
where annee = 2012 and session =‘ete');
sigle
session
annee
INFO1000
ete
2012
INFO1002
hiver
2011
INFO1003
ete
2012
sigle
INFO1000
ete
2012
INFO1002
INFO1003
hiver
2011
INFO1004
automne
2009
194
L’appartenance à un ensemble énuméré
Les mots-clés in et not in peuvent être aussi
utilisés avec des ensembles énumérés.
select distinct nom
from musiciens
where nom not in (‘Marco’, ‘Sylvain’);
Table « musicien »
nom
style
salaire
nom
Marco
rock
2000
Julie
Julie
pop
10000
Luc
Luc
rock
7000
Sarah
Sarah
jazz
2000
Sylvain
blues
10000
195
L’appartenance à un ensemble (suite)
De plus, il est possible de tester l’inclusion de uplets à plusieurs
attributs.
select count(matricule)
from inscrit_a
where (sigle, semestre, année)
in (select sigle, semestre, année
from enseigne
where prof.id = 222444);
Cette requête signifie trouver le nombre d’étudiant qui ont pris un cours enseigné par le prof.
222444’.
Table « inscrit_a »
Table « enseigne »
sigle
semestre
année
matricule
222444
INFO
1
A2013
2000
A0012
10000
222444
INFO2
A2013
10000
A0014
7000
113333
INFO3
A2014
7000
A0017
sigle
semestre
année
prof_id
INFO1
A2013
2000
INFO2
A2012
INFO3
A2014
Résultat : 1
196
Comparaison d’ensembles
Le mot-clé some signifie au moins un uplet parmi une relation.
select nom
from professeurs
where salaire > some (select salaire
from professeurs
where departement = ‘Biologie’);
Cette requête retourne tous les professeurs ayant un salaire supérieur à celui du
professeur le moins bien payé du département de biologie.
Table « professeurs »
nom
departement
salaire
nom
Jean
Chimie
5000
Jean
Paul
Chimie
4000
Luc
Luc
Biologie
7000
Sylvain
Martin
Biologie
4500
Sylvain
Math
10000
197
est équivalent à:
select nom
from professeurs
where salaire > (select min(salaire)
from professeurs
where departement = ‘Biologie’);
Note: il est aussi possible d’utiliser >= < <= = et <> avec some

198
Exemple
select nom
from professeurs
where salaire <= some (select salaire
from professeurs
where departement = ‘Biologie’);
Cette requête retourne tous les professeurs ayant un salaire inférieur ou égal à
celui du professeur le mieux payé du département de biologie.
Table « professeurs »
nom
departement
salaire
nom
Jean
Chimie
5000
Jean
Paul
Chimie
4000
Paul
Luc
Biologie
7000
Luc
Martin
Biologie
4500
Martin
Sylvain
Math
10000
199
Comparaison d’ensembles (suite)
Le mot-clé all signifie tous les uplets parmi une relation.
select nom
from professeurs
where salaire > all(select salaire
from professeurs
where departement = ‘Biologie’);
Cette requête retourne tous les instructeurs ayant un salaire supérieur à
tous les instructeurs du département de biologie.
Note: il est aussi possible d’utiliser >= < <= = et <> avec all
200
est équivalent à:
select nom
from professeurs
where salaire > (select max(salaire)
from professeurs
where departement = ‘Biologie’);
201
Exercice
Que signifie la requête suivante?
202
Comparaison d’ensembles (suite)
Les mots clés exists ou not exists permettent de vérifier si le
résultat d’une sous-requête retourne un résultat vide ou non.
(retourne vrai ou faux).
(select sigle from cours as S
where annee = 2011 and session =‘hiver’ and
exists
(select * from cours as T
where annee = 2012 and session =‘ete‘
and S.sigle = T.Sigle);
Cette requête retourne les sigles des cours offerts à l’hiver 2011 ainsi qu’ à l’été
2012.
sigle
session
annee
INFO1000
ete
2012
INFO1002
hiver
2011
INFO1003
ete
2012
sigle
INFO1000
ete
2012
INFO1003
INFO1003
hiver
2011
INFO1004
automne
2009
203
Tester la présence de doublons
Les mots clés unique et not unique permettent de vérifier si le
résultat d’une sous-requête contient des doublons ou non.
(retourne vrai ou faux)
Exemple: Trouver tous les cours offerts au plus une fois en 2012
(peut être offert zéro fois ou 1 fois).
(select sigle from cours as T
where unique
(select R.sigle from cours as R
where R.annee = 2012 and T.sigle = R.Sigle);
sigle
session
annee
INFO1000
ete
2012
INFO1002
hiver
2011
INFO1003
automne
2012
INFO1003
INFO1000
ete
2012
INFO1002
sigle
204
Tester la présence de doublons
Exemple: Trouver tous les cours offerts au moins deux
fois en 2012
(select sigle from cours as T
where not unique
(select R.sigle from cours as R
where R.annee = 2012 and T.sigle = R.Sigle);
sigle
session
annee
INFO1000
ete
2012
INFO1002
hiver
2011
INFO1003
automne
2012
INFO1000
ete
2012
sigle
INFO1000
205
Requête imbriquée dans la clause From
Il est aussi possible de placer des requêtes imbriquées dans
la clause from des requêtes.
 Exemple: Trouver le salaire moyen par département des
départements où le salaire moyen est supérieur à 42,000
$.

Cela est accepté par la plupart des implémentations SQL.
206
Requêtes scalaires
Les requêtes retournant une seule valeur
peuvent être imbriquées dans select,
where et having.
Exemple:
(select nom_dept, (select count(*)
from professeur
where departement.nom_dept =
professeur.nom_dept)
from departement;
Cette requête liste les départements avec leur nombre de professeurs.
207
REQUÊTES DE
MODIFICATION DE LA
BASE DE DONNÉES
delete, insert, update…
208
Suppression


Une requête de suppression ne peut supprimer que des uplets
au complet à partir d’une seule table (on ne peut pas
supprimer une partie d’un uplet).
Les requêtes de suppression ont la forme:
delete from r where C;


où r est une seule table et C est une condition.
Si la clause where est omise tous les uplets de la table sont
supprimés.
Exemple 
209
delete from cours where session = ‘été’;
Table « cours »
sigle
session
annee
sigle
session
annee
INFO1000
ete
2012
INFO1002
hiver
2011
INFO1002
hiver
2011
INFO1003
automne
2012
INFO1003
automne
2012
INFO1000
ete
2012
delete from professeurs where salaire between
3500 and 6000;
Table « professeurs »
nom
departement
salaire
nom
departement
salaire
Jean
Chimie
5000
Luc
Biologie
3000
Paul
Chimie
4000
Martin
Biologie
2000
Luc
Biologie
3000
Sylvain
Math
10000
Martin
Biologie
2000
Sylvain
Math
10000
211
delete from professeurs
where bâtiment in (select batiment
from batiments
where adresse = ‘1235 connaught’);
Table « professeurs »
Table « batiments »
IDProf
bâtiment
Nom
batiment
adresse
123
ABC
Paul
ABC
1234 rue des pins
456
XYZ
Léo
XYZ
1235 connaught
678
XHY
Luc
XHY
1236 mountain road
Table « professeurs »
Table « batiments »
IDProf
bâtiment
Nom
batiment
adresse
123
ABC
Paul
ABC
1234 rue des pins
678
XHY
Luc
XYZ
1235 connaught
XHY
1236 mountain road
212
delete from professeurs
where salaire < (select avg(salaire)
from professeurs);
Table « professeurs »
nom
departement
salaire
Jean
Chimie
5000
Paul
Chimie
4000
Luc
Biologie
3000
Martin
Biologie
2000
Sylvain
Math
10000
Table « professeurs »
nom
departemen
t
salaire
Jean
Chimie
5000
Sylvain
Math
10000
Dans cet exemple, le
salaire moyen est 4800.
213
Insertion

Deux cas:
◦ requête pour insérer un uplet.
◦ requête qui résulte en un ensemble
d’uplets à insérer.
214
Exemple 1 – insertion d’un uplet
insert into cours
values (‘INFO2014’, ‘hiver’, 2012);
insert into cours
values (‘INFO4018’, ‘hiver’, 2012);
Table « cours »
Table « cours »
sigle
session
annee
sigle
session
annee
INFO1000
ete
2012
INFO1000
ete
2012
INFO1002
hiver
2011
INFO1002
hiver
2011
INFO1003
automne
2012
INFO1003
automne
2012
INFO1000
ete
2012
INFO1000
ete
2012
INFO2014 hiver
2012
INFO4018 hiver
2012
215
Exemple 2 – insertion du résultat d’une requête
insert into superviseurs
(select nom_employe, 29999
from employes_reguliers
where annee_experience >= 5);
Table « superviseurs »
Table « superviseurs »
nom
salaire
nom
salaire
Jean
5000
Jean
5000
Paul
4000
Paul
4000
Luc
3000
Luc
3000
Sylvain
10000
Sylvain
10000
Bill
29999
Paul
29999
Table « employes_reguliers »
nom_employe
annee_experience
Mario
1
Simon
4
Bill
5
Paul
7
Cette requête fait la
promotion de tous les
employé réguliers avec 5 ans
ou plus d’expérience et leur
donne un salaire de 29999. 216
Exemple 3
Il est possible d’insérer un uplet
contenant des valeurs à « null »
insert into cours
values (‘INFO2014’, ‘hiver’, null);
Table « cours »
Table « cours »
sigle
session
annee
sigle
session
annee
INFO1000
ete
2012
INFO1000
ete
2012
INFO1002
hiver
2011
INFO1002
hiver
2011
INFO1003
automne
2012
INFO1003
automne
2012
INFO1000
ete
2012
INFO1000
ete
2012
INFO2014 hiver
null
217
Autres méthodes d’insertion
De nombreux SGBD offrent également la
possibilité d’insérer de grands volumes de
uplets par lot à partir de fichiers textes
formatés.
 Certains SGBD offrent une interface
graphique (ex.: Access).

218
Mise à jour des uplets
Les mises à jour sont faites avec le motclé update.
 Exemple: augmenter le coût des items
en vente de 10%.

update items
set prix_unitaire = prix_unitaire * 1.1;
Table « items »
Table « items »
nom
prix_unitaire
nom
prix_unitaire
Scie
20.00
Scie
22.00
Marteau
15.00
Marteau
16.50
Tournevis
5.00
Tournevis
5.50
Éclume
200.00
Éclume
220.00
219
Exemple 2:
update items
set prix_unitaire = prix_unitaire * 1.1;
where prix_unitaire < 18;
Table « items »
Table « items »
nom
prix_unitaire
nom
prix_unitaire
Scie
20.00
Scie
20.00
Marteau
15.00
Marteau
16.5
Tournevis
5.00
Tournevis
5.50
Éclume
200.00
Éclume
200.00
La clause where d’une requête update peut
pratiquement contenir tout ce que la clause
where d’une requête select peut contenir.
Autre exemple 
220
Exemple 3:
update items
set prix_unitaire = prix_unitaire * 1.1;
where prix_unitaire <
(select avg(prix_unitaire) from items);
Table « items »
Table « items »
nom
prix_unitaire
nom
prix_unitaire
Scie
20.00
Scie
22.00
Marteau
15.00
Marteau
16.5
Tournevis
5.00
Tournevis
5.50
Éclume
200.00
Éclume
200.00
La moyenne des prix unitaire est 60 $
221
Exemple 4:
Supposons qu’on veut augmenter de 10% les prix inférieur ou
égal à 15 $ et diminuer de 10% les prix supérieurs à 15 $.
update items
set prix_unitaire = prix_unitaire * 1.1;
where prix_unitaire <= 15;
update items
set prix_unitaire = prix_unitaire * 0.9;
where prix_unitaire > 15;
Table « items »
Table « items »
nom
prix_unitaire
nom
prix_unitaire
Scie
20.00
Scie
18
Marteau
15.00
Marteau
14.85
Tournevis
5.00
Tournevis
5.5
Éclume
200.00
Éclume
180.00
Problème! La première requête affecte la seconde! solution 
222
Solution: utiliser case
update items
set prix_unitaire =
case
when prix_unitaire <=15 then
prix_unitaire = prix_unitaire * 1.1
else prix_unitaire = prix_unitaire * 0.9
end;
Note: Il est possible d’utiliser plusieurs
when …. then….
à l’intérieur de case.
223
Exemple 5: mise à jour en utilisant le résultat
d’une requête scalaire
update items as I
set prix_unitaire =
(select avg(R.prix_unitaire) )
from items as R
where R.prix_unitaire > I.prix_unitaire);
Pour chaque item, mettre à jour son prix comme étant le
prix moyen de tous les items ayant un prix supérieur à
celui de l’item.
224
LES JOINTURES
225
Les jointures
Il existe plusieurs types de jointures.
 Nous avions vu la jointure naturelle.

◦ Rappel 

Nous verrons d’autres types de
jointures…
226
Rappel - La jointure naturelle
Table « Bâtiment »
bâtiment
adresse
ABC
1234 …
XYZ
1235 …
XHY
1236 …
Jointure naturelle: concaténer les uplets de
deux relations qui ont les mêmes valeurs
pour les attributs ayant le même nom.
nom
Table « Departement »
Informatique
adresse
?
nom
fondation
bâtiment
Biologie
nformatique
1980
ABC
Administration
Biologie
1970
ABC
Administration
1960
XHY
1234 …
1234…
1236…
select nom, adresse
from batiments natural join departements
Note: Ceci est équivalent à:
select nom, adresse
from batiments, departements
where batiments.batiment = departements.batiment.
227
Rappel – La jointure naturelle en
spécifiant les attributs à utiliser pour la
jointure
select nom, titre
from (professeurs natural join donne) join cours
using (cours.sigle);
Le mot-clé join indique de faire une jointure naturelle.
Le mot clé using indique avec quel(s) attributs faire la
jointure naturelle.
228
La jointure naturelle en spécifiant une
condition arbitraire
select *
from etudiant join inscrit_a on (etudiant.nom =
inscrit_a.nom);
Le mot-clé join indique de faire une jointure naturelle.
Le mot-clé on indique une condition à respecter pour
effectuer la jointure naturelle.
229
Les jointures externes (outer joins)
Comment écrire une requête pour obtenir la liste de tous les
étudiants avec les cours qu’ils ont suivis?
Select * from etudiants natural join inscrit_a
Non! Cette requête n’affichera pas les étudiants qui ne sont inscrit à
aucun cours dans le résultat.
Etudiants
Matricule
nom
A00123
Jean
A00555
Julie
A00777
Paul
Inscrit_a
Matricule
sigle
A00123
INFO2014
A00123
INFO4022
A00555
INFO3020
Matricule
nom
sigle
A00123
Jean
INFO2014
A00123
Jean
INFO4022
A00555
Julie
INFO3020
L’étudiant A0777 n’apparaît pas dans
les résultats!
230
Les jointures externes (outer joins)



La solution: utiliser une jointure externe.
Une jointure externe fonctionne comme une jointure
naturelle, mais préserve les uplets qui seraient perdus, en
ajoutant des valeurs « null »
Trois types de jointures externes:
◦ jointure externe à gauche (left outer join): conserve les uplets de la
relation nommée en premier.
◦ jointure externe à droite (right outer join): conserve les uplets de la
relation nommée en deuxième
◦ jointure externe complète (full outer join): conserve les uplets des
deux relations.
231
La jointure externe à gauche
Exemple:
Select * from etudiants natural left outer join inscrit_a
Etudiants
Matricule
nom
A00123
Jean
Matricule
nom
sigle
A00555
Julie
A00123
Jean
INFO2014
A00777
Paul
A00123
Jean
INFO4022
A00555
Julie
INFO3020
A00777
Paul
null
Inscrit_a
nom
sigle
A00123
INFO2014
A00123
INFO4022
A00555
INFO3020
232
La jointure externe à droite
Exemple:
Select * from employes natural right outer join projets
Employes
Matricule
nom
X123
Luc
Matricule
nom
Titre
X555
Antoine
X123
Luc
Super-projet
X888
Sophie
X888
Sophie
Mon projet
null
null
Projet secret
Projets
Matricule
Titre
X123
Super-projet
X888
Mon projet
null
Projet secret
233
La jointure complète
Exemple:
Select * from employes natural full outer join projets
Employes
Matricule
nom
X123
Luc
Matricule
nom
Titre
X555
Antoine
X123
Luc
Super-projet
X888
Sophie
X888
Sophie
Mon projet
null
null
Projet secret
X555
Antoine
null
Projets
Matricule
Titre
X123
Super-projet
X888
Mon projet
null
Projet secret
234
Les jointures externes (suite)
Pour aller un peu plus loin…
 Il est possible d’utiliser la clause « on » pour
exprimer une condition sur une jointure
externe.
 Exemple:
Select * from etudiants natural left outer join
inscrit_a on etudiant.nom = inscrit_a.nom;
Ceci équivaut au premier exemple de jointure naturelle.
235
Quelques détails supplémentaires…
Note: Si les clause on et where sont utilisées dans une
même requête où il y a jointure externe alors seul le on
est utilisé pour l’établissement de la jointure. La condition
where s’applique après la jointure.
Jointures internes
 Le terme jointure interne (« inner join ») dénote la
jointure régulière.
 Le terme « join » est équivalent à « inner join »
 Le terme « natural join » est équivalent à « natural
inner join »
236
Illustration des jointures
jointure naturelle
jointure externe à gauche
source: w3schools
jointure externe à droite
jointure complète
237
LES VUES
238
Les vues
Comment restreindre l’accès à certaines
relations pour certains utilisateurs?
 Ex.: Un commis doit avoir accès aux
noms des employés dans la relation
« Employés », mais pas aux salaires.
 Une solution: créer une vue (une
relation « virtuelle »)

239
Les vues (suite)
Pour créer une vue, il faut utiliser la syntaxe
« create view <nom> as <requête> »
Exemple:
create view noms_employes as
select nom, prenom, departement
from employes;
240
Les vues (suite)
Une vue est dynamique.
 Elle n’est pas calculée à l’avance. Elle est calculée
seulement quand il y a accès à la vue.
 Les vues peuvent être utilisées dans des
requêtes de la même façon qu’on utiliserait une
relation.

Exemple:
SELECT nom, prenom FROM noms_employes
WHERE departement = ‘Informatique’;
241
Les vues (suite)
Certaines bases de données vont
permettre de créer des « vues
matérialisées » où la vue est stockée et
mise à jour s’il y a changements aux
relations utilisées dans la définition de la
vue.
 Le mécanisme de mise à jour dépend du
SGBD.

242
CONCLUSION - SQL
243
En résumé

SQL permet de définir le schéma des relations.
◦ noms de relations, attributs, types d’attributs…
◦ renommer les attributs et relations

SQL permet d’interroger une BD.
◦
◦
◦
◦

select, from where, jointures, …
opérations ensemblistes (union, …)
requêtes imbriquées, valeurs « null »,
tri des résultats, fonctions d’agrégation,
SQL permet de mettre à jour une BD.
◦ insertion, suppression, mise à jour.
244
Microsoft Access



Microsoft Access ne supporte pas le mot clé « natural
join ». Pourquoi? La jointure naturelle est implicite, ce
qui peut générer des erreurs si la structure des tables
est changée.
Plutôt que d’utiliser « natural join », on peut utiliser
inner join avec Access, ce qui permet de spécifier les
attributs à utiliser pour la jointure.
Exemple:
SELECT Fournisseurs.Nom_fournisseur,
Regions.Nom_region
FROM Regions INNER JOIN Fournisseurs ON
Regions.code_region = Fournisseurs.code_région;
250
Microsoft Access (suite)
Access n’offre pas Except, Intersect…
 Pour augmenter la taille de la police pour les requêtes
SQL dans Access:

◦ Fichier > Options > Concepteurs d’objets > Création de
requêtes > Taille de police
Il n’y a pas de bouton « Annuler » dans Access.
 Il est préférable de ne pas utiliser d’espace dans les
noms des attributs et des relations (le souligné _ peut
être utilisé).
 Pour le devoir, attention aux accents et aux lettres
minuscules et majuscules dans les noms des attributs.

251
Bibliographie
Chap. 1, 2, 3 et 4. Silberschatz, Korth et Sudarshan,
Database System concepts, Sixth edition,
New York, McGraw-Hill, 2010.
252