Actualité du cours

http://eric.univ-lyon2.fr/~jdarmont/?page_id=447
Bases de données
http://eric.univ-lyon2.fr/~jdarmont/?feed=rss2

Master 1 Monnaie Banque Finance Assurance

Année 2017-2018
Jérôme Darmont https://twitter.com/darmont_lyon2 #bdmbfa

http://eric.univ-lyon2.fr/~jdarmont/
Conception de sites Web http://eric.univ-lyon2.fr/~jdarmont/ 1

Planning des séances Plan du cours

Jour Horaire Salle Introduction
Vendredi 22/09/16 12h00-13h45 Amphi Fugier
Vendredi 29/09/16 12h00-13h45 Amphi Fugier
Modèle UML
Vendredi 06/10/16 12h00-13h45 Amphi Fugier Modèle relationnel
Vendredi 13/10/16 12h00-13h45 Amphi Fugier
Langage SQL
Vendredi 20/10/16 12h00-13h45 Amphi Fugier
Vendredi 27/10/16 12h00-13h45 Amphi Fugier

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 2 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 3

Qu’est-ce qu’une base de données (BD) ? Organisation en fichiers

Collection de données cohérentes et structurées
Saisie Traitement Fichier

≠ Fichier

Base de données Etat de

Saisie Traitement sortie
Fichiers

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 4 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 5

1
 Organisation base de données Avantages de l’organisation BD

Uniformisation de la saisie
Saisie
Base Standardisation des traitements
+ de Traitements
données
Contrôles
Contrôle immédiat de la validité des données

Partage de données entre plusieurs traitements

Etats de
sortie

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 6 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 7

Propriétés de l’organisation BD Qu’est-ce qu’un SGBD ?

Usage multiple des données Système de Gestion de Bases de Données : Logiciel(s)
Accès facile, rapide, protégé, souple, puissant assurant structuration, stockage, maintenance, mise à
jour et consultation des données d’une BD
Coût réduit de stockage, de mise à jour et de saisie
Disponibilité, exactitude, cohérence et protection des Exemples
données ; non redondance
SGBD « bureautiques » : Access, Base, Filemaker, Paradox…
Évolution aisée et protection de l’investissement de
programmation SGBD serveurs : Oracle, DB2, SQL Server, PostgreSQL,
MySQL, MariaDB…
Indépendance des données et des programmes
Conception a priori

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 8 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 9

Objectifs des SGBD Fonctions des SGBD

Indépendance physique
Description des données : Langage de Définition de
Indépendance logique
Données (LDD)
Manipulation facile des données
Recherche des données
Administration facile des données
Efficacité des accès aux données
Redondance contrôlée des données
Mise à jour des données

Transformation des données

} Langage de
Manipulation de
Données (LMD)

Cohérence des données (contraintes d’intégrité)

Partage des données
Sécurité des données
Contrôle de l’intégrité des données

Gestion de transactions et sécurité } Langage de

Contrôle de
Données (LCD)

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 10 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 11

2
Processus de conception d’une BD Sondage express
Problème du
monde réel Qu’avez-vous compris jusqu’ici ?
Cahier des charges

Spécifications Indépendant d’un SGBD

de la BD A. L’utilité des bases de données
Rédaction B. Le processus de conception des bases de données
Modèle
Spécifique C. Les deux !
conceptuel
Analyse Famille de SGBD D. Rien du tout…
Modèle
logique
SGBD particulier Répondre sur http://toreply.univ-lille1.fr
Traduction
Modèle
physique
Question n° 4598
Traduction
Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 12 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 13

Plan du cours Modèle conceptuel UML

Introduction
Modèle UML
Modèle relationnel
Langage SQL Standard de l’Object Management Group
Ensemble de formalismes graphiques
Diagramme de classes

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 14 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 15

Classes et attributs Types des attributs

Classe : Groupe d’entités du monde réel ayant les Type d’attribut :
mêmes caractéristiques et le même comportement Nombre entier (Entier)
ex. ETUDIANT Nombre réel (Réel)
Chaîne de caractères (Chaîne)
Date (Date)
Attribut : Propriété de la classe
ex. Nom et Prénom de l’étudiant·e

Représentation graphique :

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 16 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 17

3
Instances Identifiant (1/2)
Objets de la classe ETUDIANT : les étudiant·es Solution : Ajouter un attribut numéro d’étudiant !
Nom Prénom DateNaiss Etc.
Dupont Albertine 01/06/1993 ... NumEtu Nom Prénom DateNaiss
West James 03/09/1994 ... 1110 Dupont Albertin 01/06/1993
Martin Marie 05/06/1995 ... 2002 West James 03/09/1994
Durand Rachid 15/11/1995 ... 3333 Martin Marie 05/06/1995
Titgoutte Justine 28/02/1996 ... 4042 Durand Rachid 05/11/1995
Dupont Noémie 18/09/1995 ... 5552 Titgoutte Justine 28/02/1996
Dupont Albert 23/05/1990 ... 6789 Dupont Noémie 18/09/1995
7000 Dupont Albert 23/05/1990

Problème : Comment distinguer les Dupont ?

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 18 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 19

Identifiant (2/2) Sondage express

Le numéro d’étudiant est un attribut identifiant.

Un identifiant caractérise de façon unique les instances

d’une classe. La notion d’identifiant vous paraît-elle claire ?

Convention graphique :
NB : Ne pas confondre avec Répondre sur http://toreply.univ-lille1.fr
les attributs de classe UML
dont c’est la notation usuelle
Question n° 5872

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 20 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 21

Associations Associations récursives

Définition : liaison perçue entre des classes Association récursive : une même instance de classe
ex. Les étudiant·es passent des épreuves. peut jouer plusieurs rôles dans la même association.
ex. Employé·es et supérieur·es hiérarchiques

Rôle : fonction de chaque

classe participante (+).
Les classes ETUDIANT et EPREUVE peuvent être qualifiées de
participantes à l’association PASSER.

Degré ou arité d’une association : nombre de classes participantes.

En général : associations binaires (de degré 2).

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 22 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 23

4
Multiplicité (ou cardinalité) Associations 1-1
Définition : Indicateur qui montre combien d’instances de ex. Un·e étudiant·e possède une et une seule carte Izly.
la classe considérée peuvent être liées à une instance Cette dernière n’est possédée que par un·e seul·e
de l’autre classe participant à l’association étudiant·e.

1 Un et un seul
0..1 Zéro ou un
0..* ou * Zéro ou plus
1..* Un ou plus
M..N De M à N (M, N entiers)
ex. 4..10 (de 4 à 10) Lire "Un·e étudiant·e possède multiplicité (1) carte Izly".

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 24 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 25

Associations 1-N Associations 0 ou 1-N

ex. Une épreuve relève d’une et une seule matière. ex. Un·e étudiant·e peut appartenir ou non à un groupe
Une matière peut donner lieu à plusieurs épreuves. de TD. Un groupe de TD réunit plusieurs étudiant·es.

NB : La multiplicité un à plusieurs (1..*) peut aussi être

zéro à plusieurs (0..* ou *). NB : La multiplicité un à plusieurs (1..*) peut aussi être
zéro à plusieurs (0..* ou *).

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 26 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 27

Associations M-N Quizz

ex. Un étudiant·e peut passer plusieurs épreuves. Une « Les clients achètent des produits »
épreuve peut être passée par plusieurs étudiant·es. est une association :
A. 1-1
B. 1-N (1 à plusieurs)
C. 0 ou 1-N (0 ou 1 à plusieurs)
D. M-N (plusieurs à plusieurs)

NB : Les multiplicités un à plusieurs (1..*) peuvent Répondre sur http://toreply.univ-lille1.fr

aussi être zéro à plusieurs (0..* ou *).
Question n° 2924

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 28 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 29

5
Classes-associations Exemple : spécifications (1/2)
Il est possible de caractériser une association par des Les étudiant·es sont caractérisé·es par un numéro
attributs. unique, leur nom, prénom, date de naissance, rue, code
ex. Un·e étudiant·e obtient une note par épreuve passée. postal et ville.
Les étudiant·es possèdent une carte Izly caractérisée
par un numéro unique et un solde d’argent utilisable au
CROUS.
Selon qu’ils ou elles sont dispensé·es ou non
d’assiduité, les étudiant·es appartiennent à un groupe de
TD caractérisé par un code unique.
NB : Une classe-association demeure une association.
Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 30 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 31

Exemple : spécifications (2/2) Démarche de modélisation conceptuelle

Les étudiant·es passent des épreuves et obtiennent une 1. Identifier les classes
note pour chacune.
2. Identifier les associations entre les classes
Les épreuves sont caractérisées par un code, ainsi que
3. Identifier les attributs de chaque classe et de chaque
la date et le lieu auxquels elles se déroulent. classe-association
Chaque épreuve relève d'une matière unique (mais une 4. Identifier et souligner l’identifiant de chaque classe
matière donnée peut donner lieu à plusieurs épreuves).
5. Évaluer les multiplicités des associations
Les matières sont caractérisées par un code et un
intitulé.

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 32 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 33

Exemple : diagramme de classes Quizz

Dans le diagramme précédent,
PASSER est :
A. Une classe
B. Une association
C. Les deux !
D. Obiwan Kenobi

Répondre sur http://toreply.univ-lille1.fr

Question n° 3903

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 34 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 35

6
Plan du cours Modèle logique relationnel

Introduction
Modèle UML
Modèle relationnel
Modèle associé aux SGBD relationnels
Langage SQL
Objectifs du modèle relationnel :
Indépendance physique
Traitement du problème de redondance des données
LMD non procéduraux (faciles à utiliser)
Devenir un standard
Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 36 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 37

Modèle
relationnel

Caractéristiques des systèmes relationnels Relations et attributs

Langages d’interrogation puissants et déclaratifs Une relation R est un ensemble d’attributs {A1, A2, …, An}.
Accès orienté valeur ex. La relation EPREUVE est l’ensemble des attributs
{CodeEpr, DateEpr, Lieu}
Grande simplicité, absence de considérations physiques
Description du schéma très réduite Chaque attribut Ai prend ses valeurs dans un domaine
LDD intégré au LMD dom(Ai).

Grande dynamique de structure ex. Note ∈ [0, 20]

Lieu ∈ {'Amphi Aubrac', 'Amphi Say',
Optimisation de requêtes 'Salle D101', 'Salle D201', …}
Utilisation interactive ou à partir d’un langage hôte

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 38 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 39

Modèle Modèle
relationnel relationnel

N-uplets Contraintes d’intégrité (1/2)

Notation d’une relation : R (A1, A2, …, An) Clé primaire : Ensemble d’attributs dont les valeurs
permettent de distinguer les n-uplets les uns des autres.
ex. EPREUVE (CodeEpr, DateEpr, Lieu)
ex. CodeEpr est clé primaire de la relation EPREUVE.
Un n-uplet t est un ensemble de valeurs t = <V1, V2, …, Vn>
où Vi ∈ dom(Ai) ou Vi est la valeur nulle (NULL). Clé étrangère : Attribut qui est clé primaire d’une autre
relation.
ex. <'InfoS2', '30-06-2011', 'Amphi Aubrac'> est un n-uplet
ex. Connaître la matière dont relève chaque épreuve
de la relation EPREUVE.
⇒ ajout de l’attribut CodeMat à la relation EPREUVE

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 40 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 41

7
 Modèle Modèle
relationnel relationnel

Contraintes d’intégrité (2/2) Contraintes d’intégrité en pratique

Notations : Clés primaires soulignées, clés étrangères
EPREUVE MATIERE
postfixées par le caractère #.
ex. EPREUVE (CodeEpr, DateEpr, Lieu, CodeMat#) CodeEpr DateEpr Lieu Codemat#
Codemat Intitulé
Amphi
ECOS101 15/01/2011 ECO
Aubrac
Contraintes de domaine : Les attributs doivent respecter ECO Économie
une condition logique. ECOS102 16/01/2011
Amphi
Aubrac
ECO

ex. Note ≥ 0 ET Note ≤ 20 GES Gestion

GESS201 25/05/2011 Salle 201 GES

INFOS101 20/01/2011 Salle 101 INFO

INFO Informatique

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 42 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 43

Sondage express Traduction UML-relationnel (1/4)

■ Chaque classe devient une relation.

■ Les attributs de la classe deviennent attributs de la relation.

La notion de clé étrangère vous paraît-elle claire? ■ L’identifiant de la classe devient clé primaire de la relation.

Répondre sur http://toreply.univ-lille1.fr ex. ETUDIANT (NumEtu, Nom, Prénom,

DateNaiss, Rue, CP, Ville)
Question n° 1089

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 44 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 45

Traduction UML-relationnel (2/4) Traduction UML-relationnel (3/4)

Chaque association 1-1 est prise en compte en incluant la Chaque association 1-N est prise en compte en incluant la
clé primaire d’une des relations comme clé étrangère dans clé primaire de la relation dont la multiplicité maximale est 1
l’autre relation.
comme clé étrangère dans l’autre relation.

ex. CARTE_IZLY (NumCarte, SoldeCROUS) ex. EPREUVE (CodeEpr, DateEpr, Lieu, CodeMat#)
ETUDIANT (NumEtu, Nom, Prénom, DateNaiss, Rue,
CP, Ville, NumCarte#) MATIERE (CodeMat, Intitulé)

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 46 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 47

8
 Modèle
relationnel

Traduction UML-relationnel (4/4) Exemple : modèle logique relationnel

CARTE_IZLY (NumCarte, SoldeCROUS)
Chaque association M-N est prise en compte en créant
une nouvelle relation dont la clé primaire et la GROUPE_TD (CodeGroupe)
concaténation des clés primaires des relations
participantes. Les attributs de la classe-association sont ETUDIANT (NumEtu, Nom, Prénom, DateNaiss,
insérés dans cette nouvelle relation si nécessaire. Rue, CP, Ville, NumCarte#, CodeGroupe#)

MATIERE (CodeMat, Intitulé)

ex. PASSER (NumEtu#, CodeEpr#, Note)
EPREUVE (CodeEpr, DateEpr, Lieu, CodeMat#)

PASSER (NumEtu#, CodeEpr#, Note)

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 48 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 49

Modèle
relationnel

Traduction d’une association M-N Sondage express

ETUDIANT Le tableau d’équivalence suivant
NumEtu Nom Prénom vous paraît-il correct ?
1110 Dupont Albertine PASSER (table « pont »)
UML Relationnel
2002 West James NumEtu# CodeEpr# Note
Classe Relation
1110 INFOS101 15,5
EPREUVE Attribut Attribut
2002 ECOS101 8,5
CodeEpr DateEpr Lieu Identifiant Clé primaire
2002 ECOS102 13
ECOS101 15/01/2016 Aubrac Association 1-1 ou 1-N Clé étrangère
1110 GESS201 14
ECOS102 16/01/2016 Aubrac Association M-N Relation « pont »
2002 GESS201 14,5
GESS201 25/05/2016 D201
INFOS101 20/01/2016 D101 Répondre sur http://toreply.univ-lille1.fr
Question n° 6873
Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 50 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 51

Modèle Modèle
relationnel relationnel

Problèmes de la redondance Anomalies liées à la redondance

Lorsque l’on effectue directement une modélisation logique Anomalies de modification : Si l’on souhaite mettre à jour
ex. Soit la relation PASSER_EPREUVE. le lieu d’une épreuve, il faut le faire pour tous les n-uplets
concernés.
NumEtu Note CodeEpr Lieu Anomalies d’insertion : Pour ajouter une nouvelle
1110 15,5 INFO1 Amphi Aubrac
1110 14,0 ECO1 Amphi Aubrac épreuve, il faut obligatoirement fournir des valeurs pour
2002 13,0 ECO2 Salle D201 NumEtu et Note.
3333 10,5 INFO1 Amphi Aubrac
Anomalies de suppression : ex. La suppression de
l’étudiant·e n° 2002 fait perdre toutes les informations
Cette relation présente différentes anomalies. concernant l’épreuve ECOS2.

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 52 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 53

9
Éviter la redondance Quizz
Pourquoi ? La redondance, c’est :
Suppression des problèmes de mise à jour
Minimisation de l’espace de stockage
A. Vraiment Très MalTM
B. Parfois nécessaire
Comment ? C. Une figure de style littéraire
Dans le modèle conceptuel, ne spécifier que des attributs non
décomposables (première forme normale).
ex. Une adresse doit être décomposée en rue, code postal,
ville… Répondre sur http://toreply.univ-lille1.fr
C’est tout !
Question n° 6721

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 54 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 55

Modèle Modèle
relationnel relationnel

Algèbre relationnelle Opérateurs ensemblistes

Ensemble d’opérateurs qui s’appliquent aux relations Union : T=R∪S (notation algébrique)
ou T = UNION (R, S) (notation fonctionnelle)
R et S doivent avoir même schéma.
Résultat : nouvelle relation qui peut à son tour être
manipulée ex. R et S sont les relations ETUDIANT de deux formations
(ex. anciens M1 Finance et Eco-Société) fusionnées pour
constituer une liste d’émargement commune.
⇒ L’algèbre relationnelle permet d’effectuer des recherches
dans les relations. Notation graphique :
T
∪
R S
Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 56 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 57

Modèle Modèle
relationnel relationnel

Opérateurs ensemblistes Opérateurs ensemblistes

Intersection : T=R∩S Différence : T=R-S
ou T = INTERSECT (R, S) ou T = MINUS (R, S)
R et S doivent avoir même schéma. R et S doivent avoir même schéma.
ex. Permet de trouver les étudiant·es commun·es à ex. Permet de retirer les étudiant·es de la relation S
deux formations. existant dans la relation R.
Notation graphique : Notation graphique :
T T
∩ −
R S R S
Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 58 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 59

10
 Modèle Modèle
relationnel relationnel

Opérateurs ensemblistes Produit cartésien

Produit cartésien : T=RxS NumEtu Nom CodeEpr Lieu
ou T = PRODUCT (R, S) ex. 101 E1 X INFO1 Aubrac
102 E2 ECO1 Aubrac
Associe chaque n-uplet de R à chaque n-uplet de S. ECO2 D201
Notation graphique :
NumEtu Nom CodeEpr Lieu
T 101 E1 INFO1 Aubrac
102 E2 INFO1 Aubrac
101 E1 ECO1 Aubrac
x = 102 E2 ECO1 Aubrac
101 E1 ECO2 D201
R S 102 E2 ECO2 D201

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 60 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 61

Modèle Modèle
relationnel relationnel

Opérateurs ensemblistes Division

Division : T = R ÷ S ex.
ou T = DIVISION (R, S) NumEtu CodeEpr Note
R (A1, A2, …, An) S (Ap+1, …, An) 101
101 INFO1
INFO1 11
11
T (A1, A2, …, Ap) contient tous les n-uplets tels que leur 101 ECO1 15
CodeEpr Note
101
101 ECO2
ECO2 12
12
concaténation à chacun des n-uplets de S donne toujours 102 ECO1 9 ÷ INFO1 11
un n-uplet de R. ECO2 12
103 INFO1 11
Notation graphique : 103 ECO2 12

T
NumEtu
÷ = 101
103
R S
Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 62 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 63

Modèle Modèle
relationnel relationnel

Opérateurs spécifiques Opérateurs spécifiques

Projection : T = Π <A, B, C> (R) Restriction : T = σ <C> (R)
ou T = PROJECT (R / A, B, C) ou T = RESTRICT (R / C)
T ne contient que les attributs A, B et C de R. T ne contient que les attributs de R qui satisfont la
ex. Noms et prénoms des étudiant·es. condition C.
Notation graphique : ex. C = Étudiant·es qui habitent Lyon.
Notation graphique :
T
T
A, B, C
C
R
R
Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 64 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 65

11
 Modèle Modèle
relationnel relationnel

Opérateurs spécifiques Exemple de requête*

Notes des étudiant·es en précisant leurs noms
Jointure naturelle : T = R >< S (et pas seulement leurs numéros)
ou T = JOIN (R, S)
Produit cartésien R x S et restriction A = B sur les RESULTAT
attributs A ∈ R et B ∈ S.
Nom,
f
Notation graphique : CodeEpr, Note
Hh

T NumEtu NumEtu
=
A B
= ETUDIANT PASSER
R S
*Requête : enchaînement d’opérations
Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 66 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 67

Modèle Modèle
relationnel relationnel

Exemple de requête Exemple de requête

Décomposition des opérations E.NumEtu Nom P.NumEtu CodeEpr Note

101 E1 101 INFO1 10
102 E2 101 INFO1 10
ETUDIANT PASSER 103 E3 101 INFO1 10
101 E1 103 INFO1 15
NumEtu Nom NumEtu CodeEpr Note
= 102 E2 103 INFO1 15
101 E1 101 INFO1 10
102 E2 X 103 INFO1 15 103 E3 103 INFO1 15
103 E3 103 ECO1 12 101 E1 103 ECO1 12
102 E2 103 ECO1 12
103 E3 103 ECO1 12

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 68 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 69

Modèle
relationnel

Exemple de requête Quizz

ETUDIANT PASSER
E.NumEtu Nom P.NumEtu CodeEpr Note
L’algèbre relationnelle sert à faire :
101 E1 101 INFO1 10
103 E3 103 INFO1 15 A. Des opérations sur les relations
103 E3 103 ECO1 12 B. Des requêtes sur une base de données relationnelle
C. Des nœuds dans le cerveau des étudiant·es
Π <Nom, CodeEpr, Note> (ETUDIANT PASSER)
Nom CodeEpr Note
E1 INFO1 10 (Projection sur les attributs Répondre sur http://toreply.univ-lille1.fr
E3 INFO1 15 Nom, CodeEpr et Note)
Question n° 9313
E3 ECO1 12
Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 70 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 71

12
Classification des SGBD relationnels Quizz
Niveau 1 : Systèmes non relationnels. Quel est le niveau de classification
Supportent uniquement la structure tabulaire. d’un tableur ?
Niveau 2 : Systèmes relationnellement minimaux.
A. Non relationnel – Niveau 1
Permettent les opérations de restriction, projection et
jointure. B. Relationnellement minimal – Niveau 2
C. Relationnellement complet – Niveau 3
Niveau 3 : Systèmes relationnellement complets. Toutes D. Relationnellement plein – Niveau 4
les opérations de l’algèbre relationnelle.
Niveau 4 : Systèmes relationnellement pleins. Répondre sur http://toreply.univ-lille1.fr
Permettent la définition des contraintes d’intégrité.
Question n° 2469

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 72 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 73

Plan du cours Qu’est-ce que SQL ?

Introduction Structured Query Language

Modèle UML Issu de SEQUEL (Structured English as a Query Language)

Modèle relationnel LDD LMD

Permet la définition, la manipulation et le contrôle d’une
LCD

base de données relationnelle.

Langage SQL
SQL se base sur l’algèbre relationnelle.

Standard depuis 1986.

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 74 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 75

LDD LDD

Types de données principaux Contraintes d’intégrité

NUMBER(n) : nombre entier à n chiffres Mot clé CONSTRAINT

Identification par un nom de contrainte

NUMBER(n, m) : nombre réel à n chiffres au total
(virgule comprise) et m chiffres après la virgule Clé primaire :
PRIMARY KEY (clé)

VARCHAR(n) : chaîne de caractères de taille n Clé étrangère :

FOREIGN KEY (clé) REFERENCES table(attribut)

DATE : date au format ‘JJ-MM-AAAA’ Contrainte de domaine :

CHECK (condition)

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 76 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 77

13
 LDD LDD

Définition des données Définition des données

ex.
ex.
CREATE TABLE Passer ( NumEtu NUMBER(8),
CREATE TABLE Etudiant ( NumEtu NUMBER(8), CodeEpr VARCHAR(10),
Note NUMBER(5, 2),
Nom VARCHAR(255),
Prenom VARCHAR(255), CONSTRAINT PassClePri PRIMARY KEY (NumEtu, CodeEpr),
DateNaiss DATE,
Rue VARCHAR(255), CONSTRAINT PassCleEtrEtu FOREIGN KEY (NumEtu)
CP NUMBER(5), REFERENCES Etudiant (NumEtu),
Ville VARCHAR(255), CONSTRAINT PassCleEtrEpr FOREIGN KEY (CodeEpr)
REFERENCES Epreuve (CodeEpr),
CONSTRAINT EtuClePri PRIMARY KEY (NumEtu)
) CONSTRAINT NoteValide CHECK (Note >= 0 AND Note <= 20)
)
Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 78 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 79

LDD LDD

Modifications structurelles Modifications structurelles

Ajout d’attributs Ajout de contrainte
ALTER TABLE nom_table ADD (attribut TYPE, …) ALTER TABLE nom_table
ex. ALTER TABLE Etudiant ADD (tel NUMBER(8)) ADD CONSTRAINT nom_contrainte définition_contrainte
ex. ALTER TABLE Epreuve
ADD CONSTRAINT LieuValide CHECK (Lieu IN (‘Say’, ‘Aubrac’))
Modifications d’attributs
ALTER TABLE nom_table MODIFY (attribut TYPE, …)
ex. ALTER TABLE Etudiant MODIFY (tel NUMBER(10))
Suppression de contrainte
ALTER TABLE nom_table DROP CONSTRAINT nom_contrainte
ex. ALTER TABLE Epreuve
Suppression d'attributs DROP CONSTRAINT LieuValide
ALTER TABLE nom_table DROP COLUMN attribut, ...
ex. ALTER TABLE Etudiant DROP COLUMN tel

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 80 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 81

LDD LMD

Index Mise à jour des données

Ajout d’un n-uplet
Définition : Structure de données physique permettant ex. INSERT INTO Matiere
VALUES (‘BDM1MBFA’, ‘Bases de données’)
d'accélérer les accès aux données
Modification de la valeur d’un attribut
ex. UPDATE Etudiant SET Nom=‘Dudule’
Exemple :
WHERE NumEtu = 333333
CREATE INDEX IdxNomEtu ON Etudiant (Nom)
ex. UPDATE Passer SET Note = Note + 1

NB : La clé primaire d'une relation est automatiquement Suppression de n-uplets

ex. DELETE FROM Etudiant
indexée.
WHERE Ville = ‘Lyon’
ex. DELETE FROM Epreuve
Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 82 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 83

14
 LMD

Quizz Interrogation des données

Corriger l’adresse d’un·e étudiant·e Par l’exemple, sur la base ETUDIANTS
qui a déménagé est :
CARTE_IZLY (NumCarte, SoldeCROUS)
A. Une modification structurelle des données GROUPE_TD (CodeGroupe)
ETUDIANT (NumEtu, Nom, Prénom, DateNaiss, Rue, CP, Ville,
B. Une mise à jour des données NumCarte#, CodeGroupe#)
C. Ni l’une, ni l’autre MATIERE (CodeMat, Intitulé)
EPREUVE (CodeEpr, DateEpr, Lieu, CodeMat#)
PASSER (NumEtu#, CodeEpr#, Note)
Répondre sur http://toreply.univ-lille1.fr
Question n° 1294 Note : Les symboles [ ] indiquent une clause optionnelle d’une requête
dans les transparents suivants.

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 84 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 85

LMD LMD

Étoile, tri et champs calculés Projection et restriction

Tous les n-uplets d’une table : étoile (*) Projection
ex. SELECT * FROM Etudiant ex. Noms et Prénoms des étudiant·es, uniquement (pas
les autres attributs)
Tri du résultat SELECT Nom, Prénom FROM Etudiant
ex. Par ordre alphabétique [inverse] de nom
Suppression des doublons
SELECT * FROM Etudiant ex. SELECT DISTINCT Nom FROM Etudiant
ORDER BY Nom [DESC]
Restriction
Champs calculés ex. Étudiant·es qui habitent à Lyon
ex. Transformation de notes sur 20 en notes sur 10 SELECT * FROM Etudiant
SELECT Note / 2 FROM Passer WHERE Ville = ‘Lyon’

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 86 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 87

LMD LMD

Opérateurs de restriction (1/3) Opérateurs de restriction (2/3)

ex. Épreuves se déroulant après le 01/01/2016 ex. Étudiant·es habitant une ville dont le nom se
SELECT * FROM Epreuve termine par sur-Saône
WHERE DateEpr >= '01-01-2016'
SELECT * FROM Etudiant
ex. Notes comprises entre 10 et 20 WHERE Ville LIKE ‘%sur-Saône’
SELECT * FROM Passer
WHERE Note BETWEEN 10 AND 20 ‘sur-Saône%’ ⇒ commence par sur-Saône
‘%sur%’ ⇒ contient le mot sur
ex. Notes indéterminées (sans valeur)
SELECT * FROM Passer
WHERE Note IS NULL

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 88 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 89

15
 LMD LMD

Opérateurs de restriction (3/3) Opérateurs logiques

ex. Prénoms des étudiant·es dont le nom est ET. ex. Épreuves se déroulant le 15/01/2016 en salle D201
Dupont, Durand ou Martin SELECT * FROM Epreuve
SELECT Prénom FROM Etudiant WHERE DateEpr = ‘15-01-2016’ AND Lieu = ‘D201’
WHERE Nom IN (‘Dupont’, ‘Durand’, ’Martin’) OU. ex. Étudiant·es né·es avant 1990 ou habitant hors Lyon
SELECT * FROM Etudiant
WHERE DateNaiss < ‘01-01-1990’ OR Ville <> ‘Lyon’
NB : Possibilité d’utiliser la négation pour tous ces Combinaisons. ex. Étudiant·es né·es après 1990 et habitant
prédicats ⇒ NOT BETWEEN, NOT NULL, NOT LIKE, Lyon ou Vienne
NOT IN. SELECT * FROM Etudiant
WHERE DateNaiss > ‘31-12-1990’
AND (Ville = ‘Lyon’ OR Ville = ‘Vienne’)
Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 90 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 91

LMD LMD

Fonctions d’agrégat Fonction COUNT et opérateur DISTINCT

Elles opèrent sur un ensemble de valeurs et les agrègent.

ex. Nombre total de notes
AVG(), VARIANCE(), STDDEV() : moyenne, variance et
écart-type des valeurs SELECT COUNT(*) FROM Passer
SELECT COUNT(NumEtu) FROM Passer
SUM() : somme des valeurs
MIN(), MAX() : valeur minimum, valeur maximum ex. Nombre d'étudiant·es noté·es
COUNT() : nombre de valeurs
SELECT COUNT(DISTINCT NumEtu) FROM Passer
ex. Moyenne des notes
SELECT AVG(Note) FROM Passer

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 92 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 93

LMD LMD

Exemple Jointure
Table PASSER ex. Liste des notes avec le nom des étudiant·es

SELECT Nom, CodeEpr, Note

NumEtu CodeEpr Note
101 INFO1 10 FROM Etudiant, Passer
103 INFO1 15 WHERE Etudiant.NumEtu = Passer.NumEtu RESULTAT
103 ECO1 12
Nom,
f
CodeEpr, Note
Hh
COUNT(NumEtu) ⇒ Résultat = 3
NumEtu NumEtu
COUNT(DISTINCT NumEtu) ⇒ Résultat = 2 =

ETUDIANT PASSER

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 94 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 95

16
 LMD LMD

Jointure Jointure
ex. Idem avec le numéro d'étudiant en plus Jointure exprimée avec le prédicat IN
SELECT E.NumEtu, Nom, CodeEpr, Note ex. Notes des épreuves passées le 23 septembre 2013
FROM Etudiant E, Passer P
SELECT Note FROM Passer
WHERE E.NumEtu = P.NumEtu
WHERE CodeEpr IN (
ORDER BY Nom, Note DESC Sous-
SELECT CodeEpr FROM Epreuve requête
WHERE DateEpr = ‘23-09-2013’)
NB : Utilisation d’alias (E et P) pour alléger l’écriture
+ tri par nom (croissant) et note (décroissante).
NB : Il est possible d’imbriquer des requêtes.

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 96 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 97

LMD LMD

Prédicats d’existence Prédicats de dénombrement

Prédicats EXISTS / NOT EXISTS Prédicats ALL / ANY
ex. Étudiant·es qui ont passé au moins une épreuve ex. Numéros des étudiant·es qui ont obtenu au moins
[n’ont passé aucune épreuve] une note supérieure à chacune [à au moins une] des
notes obtenues par l'étudiant·e n° 1000.
SELECT * FROM Etudiant E
WHERE [NOT] EXISTS ( SELECT DISTINCT NumEtu FROM Passer
WHERE Note > ALL [ANY] (
SELECT * FROM Passer P
SELECT Note FROM Passer
WHERE E.NumEtu = P.NumEtu )
WHERE NumEtu = 101 )

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 98 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 99

LMD LMD

Groupement (1/2) Groupement (2/2)

ex. Note moyenne pour les étudiant·es ayant
ex. Moyenne de chaque étudiant·e passé moins de 5 épreuves
SELECT NumEtu, AVG(Note) SELECT NumEtu, AVG(Note)
FROM Passer FROM Passer
GROUP BY NumEtu GROUP BY NumEtu
HAVING COUNT(*) < 5
ex. Nombre de notes par étudiant·e
Attention : La clause HAVING ne s’utilise qu’avec GROUP BY.
SELECT NumEtu, COUNT(*)
FROM Passer NB : HAVING : évaluation de condition sur un résultat
de groupement (a posteriori)
GROUP BY NumEtu
≠ WHERE : évaluation de condition a priori

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 100 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 101

17
 LMD

Opérations ensemblistes Quizz

INTERSECT, MINUS, UNION

ex. Code des épreuves ayant soit lieu dans l’Amphi

Aubrac, soit ayant été passées par l'étudiant·e n° 102 Tous les opérateurs de l’algèbre
relationnelle ont-ils un équivalent en SQL ?
SELECT CodeEpr FROM Epreuve
WHERE Lieu = 'Amphi Aubrac‘
Répondre sur http://toreply.univ-lille1.fr
UNION
SELECT CodeEpr FROM Passer Question n° 7838
WHERE NumEtu = 102

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 102 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 103

Tutoriel SQL Plan du cours

Pour approfondir SQL en ligne…
Introduction
Modèle UML
Modèle relationnel
Langage SQL

http://eric.univ-lyon2.fr/~jdarmont/tutoriel-sql/

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 104 Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 105

Sondage express

Que pensez vous de ce cours ?

Répondre sur http://toreply.univ-lille1.fr

Question n° 2310

Bases de données http://eric.univ-lyon2.fr/~jdarmont/ 106

18