Exemple

Bases de Données Relationnelles
(à partir du cours de G.Cécé )
Samir Chouali
[email protected]
-1-
Bibliographie

« Conception et architecture des Bases de Données», R.
Elmasri, S. Navathe et D. Serain, Pearson Education, 2004.

« Des Bases de Données à l'Internet »,
P. Mathieu, Vuibert, 2000.
http://www.lifl.fr/~mathieu/bdd

« Bases de Données Objet & Relationnel »,
G. Gardarin, Eyrolles, 1999.
-2-
Plan

Généralités
- Définitions
- Propriétés des SGBD

Modélisation
- Le Modèle Conceptuel de Données (MCD)
- Le Modèle Relationnel (MR)
- Passage du MCD au Modèle Relationnel




Contraintes d’intégrité
Algèbre relationnelle
Langage de requêtes SQL
Normalisation des relations
-3-
Définitions

Base de données :
- Ensemble de données qui modélisent une partie du monde réel pour
une application informatique.

Système de Gestion de Base de Données (SGBD) :
- Outil qui permet d’insérer, modifier, retirer et rechercher des données ;
le tout de façon efficace.
- Interface entre les utilisateurs et l’information brute
- Présente les informations dans une forme exploitable
-4-
Les Trois Couches d’un SGBD

Système de gestion de fichiers :
- gère le stockage physique des informations (dépend du matériel).

SGBD interne :
- assemble et place les données, gère les liens entre les données et y
garantit un accès rapide.

SGBD externe :
- s’occupe de la présentation et de la manipulation des données. Permet
l’utilisation de langages de requêtes élaborés et d’outils de
présentation adaptés.
-5-
Propriétés des SGBD (1)

Indépendance physique
- transparence de la gestion des données au niveau physique.

Indépendance logique
- chacun possède sa propre vue des données.

Manipulable par des non informaticiens
- utilisation de langages non procéduraux (pas de programmation).
-6-
Propriétés des SGBD (2)

Accès aux données efficaces
- optimisation des accès aux données.

Administration centralisées des données
- outils de sauvegarde des données, de réplication, ...

Non redondance des données.
- évite la duplication des informations ce qui facilite la gestion.

Cohérence des données
- gestion automatique des contraintes d’intégrité.
-7-
Propriétés des SGBD (3)

Partage des données
- plusieurs personnes peuvent accéder aux données simultanément tout
en conservant l’intégrité de la base.

Sécurité des données
- protection contre les accès non autorisés.
- tolérance aux pannes.
-8-
Types de Bases de Données (1)

Les bases hiérarchiques

Les bases réseaux (CODASYL)

Les bases relationnelles
- données sous formes de tables basées sur l’algèbre relationnelle et un
langage, de manipulation, déclaratif (SQL).

Les bases déductives
- données sous formes de tables (prédicats), le langage d’interrogation
est basé sur le calcul des prédicats et la logique du premier ordre.
-9-
Types de Bases de Données (2)

Les bases objets
- données représentées sous forme d’instances de classes hiérarchisées.
75% des SGBD sont des bases relationnelles
Les bases objets gagnent du terrain
- 10 -
4. Le marché des SGBD


Marché en 2003 : 7 milliards de dollars
Aujourd’hui 3 leaders : IBM, Oracle, Microsoft
Parts de m arché 2003
IBM
Oracle
Microsoft
NCR
Inform ix
Autres
source: www.gartner.com Mai
2004
Modélisation
Modèle Conceptuel de Données
(MCD)
- 12 -
Modélisation
Réalité
perçue
Modélisation
conceptuelle
Transformation dans
un modèle supporté
par un SGBD
Définition de la
structure de données
de la base
Modèle entité
association
Modèle
relationnel
SQL
- 13 -
Modélisation

Le résultat de l’analyse est le Modèle Conceptuel de Données
(MCD) qui décrit la future base de données à l’aide
d’entités et d’associations.
Employé
Numéro d’employé
Nom
Prénom
Date d’embauche
Fonction
Rémunération
0,n
participe
Date début
Date fin
1,n
Tâche
Nom de la tâche
Coût de la tâche
- 14 -
Vocabulaire (1)

Entité :
- représentation d’un objet, matériel ou immatériel
(ex. : Étudiant, Voiture, Vin, etc...).
- une entité est composée de propriétés.

Propriété :
- donnée élémentaire et indécomposable
(ex. : age, note, nom, adresse, date de naissance, etc...).
- 15 -
Vocabulaire (2)

Association
- représentation d’un lien entre différentes entités.
- des propriétés peuvent être attachées à une association.

Dimension
- nombre d’entités intervenants dans l’association
(1 : association réflexive; 2 : association binaire;
n : association n-aire)

Cardinalité
- caractérise le lien entre une entité et une association. Elle est
constituée d’une borne minimale et d’une borne maximale.
- 16 -
Vocabulaire (3)

Cardinalité (suite)
- Nombre de fois qu’une occurrence de l’entité participe aux
occurrences de l’association.

Identifiant
- une ou plusieurs propriétés d’une entité telles qu’à chaque valeur de
l’identifiant correspond une et une seule occurrence de l’entité.
- l’identifiant d’une association est constitué de la réunion des
identifiants des entités qui participent à l’association.
- 17 -
Exemple de MCD
encadre
a pour chef
0,1
0,n
est chef de
Employé
Numéro d’employé
Nom
Prénom
Date d’embauche
Fonction
Rémunération
Projet
coordonne
0,n
1,1
Numéro du projet
Thème du projet
Titre du projet
Date de début
Date de fin
1,n
0,n
participe
Date début
Date fin
1,n
Tâche
Nom de la tâche
Coût de la tâche
Constitué_de
1,1
- 18 -
Modèle Conceptuel des Données
•
Exemple "KaafKaaf"
–
–
PARTIE 1
La société "KaafKaaf" désire
informatiser son système de facturation.
Les factures devraient se présenter de la
façon suivante
–
Créez un MCD, qui permet de modéliser
correctement le système d'information
nécessaire, sachant que:
–
Un client peut bien sûr recevoir plusieurs
factures, mais il est uniquement
considéré comme tel à partir du moment
où il reçoit sa première facture.
–
Une facture concerne un et un seul
client.
Modèle Conceptuel des Données
• Remarque:
– Bien que le numéro du client n'apparaisse pas en tant que tel sur la
facture, il est préférable d'ajouter cette propriété artificielle à l'entité
Client, et de la définir comme identifiant de cette entité. Cela nous
empêche de devoir définir un identifiant composé de trop de propriétés.
Modèle Conceptuel des Données
•
PARTIE 2
– Il s'agit d'étendre le MCD de la partie 1.
– Le responsable de la facturation de la
société désire rendre les factures plus
informatives. Comme un client peut
acheter plusieurs articles différents en
même temps, la facture devrait indiquer
pour chaque article le numéro , un libellé,
le prix unitaire, la quantité vendue et le
prix total pour ce type d'article.
– Voici l'aspect que la facture devrait avoir:
– Proposez un nouveau MCD qui reflète ces
modifications, en respectant que:
– Tous les articles disponibles sont stockés
(p.ex. No=234 Libellé="Marteau"
PU=470 Luf.). Même si un article n'est pas
encore considéré par une facture, il existe
dans le système d'information.
Modèle Conceptuel des Données
•
Remarques:
– L'entité Facture ne contient plus la propriété Montant. Il existe une règle générale de conception qui
dit:
•
Aucune propriété qui peut être calculée à partir d'autres propriétés existantes, ne
devra être stockée dans le MCD.
– Pour la même raison, on n'a pas besoin de modéliser explicitement le prix à payer pour l'achat d'une
quantité d'articles donnés. Le prix pour chaque article figurant sur la facture peut être calculé à partir
du prix unitaire et de la quantité
Modèle Conceptuel des Données
•
Exemple "Gestion d'école"
–
–
–
–
–
–
–
PARTIE 1
Dans une école, on veut informatiser le système d'information qui gère les classes.
Elaborez un MCD sachant que:
Un élève est caractérisé par son no. matricule, son nom et prénom, ainsi que sa date de naissance.
Une classe est caractérisée par le nom de la classe et par une indication du cycle.
Il faudra prévoir de connaître la fréquentation des classes des élèves sur plusieurs années
consécutives.
Un élève enregistré dans le système fréquente au moins une classe au cours des années.
Modèle Conceptuel des Données
•
PARTIE 2
•
Il s'agit maintenant de concevoir une extension
au MCD précédent qui permet de représenter
la situation suivante:
•
La direction de l'école désire également saisir
tous les professeurs dans le système
d'information. Un professeur est caractérisé par
un code interne unique , son nom et prénom et
la matière qu'il enseigne. Nous supposons que
chaque professeur enseigne une seule
matière.
•
Modélisez le fait que chaque classe est
enseignée chaque année par un ou plusieurs
enseignants. Un enseignant peut bien sûr
donner des cours dans plusieurs classes, mais
peut également ne pas donner des cours
pendant une ou plusieurs années.
Exercices

Exercice 1
Un magasin de sport a besoin de stocker ses informations
principales dans une base de données. Il s'agit de stocker
toutes les informations relatives à ses produits, ses clients et
ses fournisseurs. Il souhaite que l'organisation soit facilitée
par le regroupement des produits en différentes catégories.
Et il souhaite à partir de là pouvoir facilement consulter ses
stocks, la liste de ses meilleurs clients, le top 10 de ses
produits vendus.
Créer le MCD correspondant.
- 25 -
Exercices

Exercice 2
Une agence immobilière a besoin d'une base de données
pour gérer ses locations. Il faut entre autres qu'elle soit
capable de cibler les logements pouvant convenir à un
client donné. Elle souhaite également pouvoir facilement
dresser la facture d'un client donné, savoir ce qu'elle doit
verser à un propriétaire donné, ou calculer les primes de
ses agents.
Créer le MCD correspondant.
- 26 -
Le Modèle Relationnel
- 27 -
Modèle Relationnel

Les SGBD relationnels
- organisent les données en tables
- sont basés sur l’algèbre relationnelle
(théorie des ensembles).
- 28 -
Exemple
NoProf
NoCours
Intitule
1 Supervision
2 Base de données
3 Introduction Réseaux
Nom
1 Cécé
2 Bourgeois
NoProf
NoCours
1
1
2
Nom Prof
Cécé
Bourgeois
Cécé
2
3
1
VolumeHoraire
7.5
9
6
Intitule Cours
Base de données
Supervision
Introduction Réseaux
VolumeHoraire
7.5
6
9
- 29 -
Domaines

Exemples
-
ENTIER
REEL
CHAINES DE CARACTERES
-
SALAIRE = {4 000..100 000}
COULEUR= {BLEU, BLANC, ROUGE}
-
POINT = {(X:REEL,Y:REEL)}
TRIANGLE = {(P1:POINT, P2:POINT, P3:POINT)}
- 30 -
Produit Cartésien


Le produit cartésien D1x D2x ... x Dn est l'ensemble des
tuples (n-uplets) :
<V1,V2, …, Vn> tel que Vi Di
Exemple
-
D1 = {Bleu, Blanc, Rouge}
D2 = {Vrai, Faux}
Bleu Vrai
Bleu Faux
Blanc Vrai
Blanc Faux
Rouge Vrai
Rouge Faux
- 31 -
Relation (ou Table)

Sous-ensemble du produit cartésien d'une liste de
domaines

Une relation est caractérisée par un nom

Exemple
- D1 = COULEUR
- D2 = BOOLEEN
CoulVins
Coul
Choix
Bleu
Faux
Blanc Vrai
Rouge Vrai
- 32 -
Exemple de Relation
VINS
CRU
MILL
REGION
CHENAS
1983 BEAUJOLAIS
TOKAY
1980 ALSACE
TAVEL
1986 RHONE
CHABLIS
1986 BOURGOGNE
ST-EMILION 1987 BORDELAIS
COULEUR
ROUGE
BLANC
ROSE
BLANC
ROUGE
- 33 -
Attribut

Vision tabulaire du relationnel
- Une relation est une table à deux dimensions
- Une ligne est un tuple
- Un nom est associé à chaque colonne afin de la repérer autrement que
par sa position

Attribut
- nom donné à une colonne d'une relation
- prend ses valeurs dans un domaine
- 34 -
Clé

Groupe d'attributs minimum qui détermine un tuple
unique dans une relation

Exemples
- {CRU,MILLESIME} dans VINS
- NSS dans PERSONNE

Clé étrangère
- Groupe d'attributs formant la clé d’une autre relation
- 35 -
Schéma

D’une relation
- Nom de la relation, liste des attributs avec domaines et clés de la
relation

Exemple
- VINS(NV :entier, CRU :texte, MILL :entier, DEGRE :réel,
REGION :texte)
- Par convention, la clé primaire est soulignée

Schéma d'une bd relationnelle
- C’est l'ensemble des schémas des relations composantes
- 36 -
Exemple de Schéma

Exemple
BUVEURS (NB, NOM, PRENOM, TYPE)
VINS (NV, CRU, MILL, DEGRE)
ABUS (NB, NV, DATE, QUANTITE)

Clés étrangères
ABUS.NV fait référence à VINS.NV
ABUS.NB fait référence à BUVEURS.NB
- 37 -
Diagramme des Liens
BUVEURS
NB
NOM
PRENOM
ABUS
TYPE
NB
NV
VINS
DATE
NV
CRU
MILL.
DEGRE
QUANTITE
- 38 -
Vocabulaire comparé
MCD
Modèle Relationnel
Entité
Propriété
Identifiant
Association
Table
Attribut, Colonne
Clé
Relation
- 39 -
Du MCD vers les
Tables Relationnelles
- 40 -
Transformation

Traitement des entités
- Chaque entité devient une table.
- Chaque propriété devient une colonne de cette table.
- L’identifiant d’une entité devient la clé primaire de la table
correspondante.

Traitement des associations
- Le traitement des associations dépend des cardinalités des ces
associations.
- 41 -
Association binaire
Cardinalités
(*,n) - (*,n)
(*,*) - (*,1)
Traitement
Création d’une nouvelle table dont la clé contient
nécessairement les identifiants des deux entités.
Les propriétés de l’association migrent aussi vers
la nouvelle table.
La clé étrangère du coté (*,*) migre vers le coté
(*,1). Les propriétés de l’association migrent
aussi vers le coté (*,1).
- 42 -
Exemple : (*,1) - (*,*)
De :
Vin
1,1
NoVin
Cru
Mill.
Degré
1,n
Recolte
Producteur
NoP
Nom
Région
Quantite
À:
NoVin
1
2
Cru
Mill.
Volnay 1983
Chenay 1996
Degre
12
12,5
NoP*
2
1
Quantite
15
25
NoP
1
2
Nom
Gerardus
Toeuf
Région
Monbien
Besancit
- 43 -
Association n-aires
- création d’une nouvelle table dont la clé est au moins
composée des identifiants des différentes entités.
- les propriétés de cette association migrent aussi vers la
nouvelle table.
- 44 -
Exemple
De :
Prof
NoProf
Nom
1,n
Enseigne
1,n
VolumeHoraire
Cours
NoCours
Intitulé
À:
NoProf
Nom
1 Cece
2 Bourgeois
NoCours
Intitule
1 Supervision
2 Base de données
3 Introduction Réseaux
NoProf NoCours VolumeHoraire
1
2
7.5
1
3
9
2
1
6
- 45 -
Exercice
encadre
a pour chef
0,1
0,n
est chef de
Employé
Numéro d’employé
Nom
Prénom
Date d’embauche
Fonction
Rémunération
Projet
coordonne
0,n
1,1
Numéro du projet
Thème du projet
Titre du projet
Date de début
Date de fin
1,n
0,n
participe
Date début
Date fin
1,n
Tâche
Nom de la tâche
Coût de la tâche
Constitué_de
1,1
- 46 -
Contraintes
- 47 -
Contraintes d’Intégrité


Une contrainte d’intégrité est une assertion (c-à-d une
propriété) qui doit être vérifiée par les données de la base
Si une contrainte d’intégrité n’est pas respectée lors d’une
modification des données, la modification est rejetée et
l’utilisateur est averti
- 48 -
Contraintes de Clé


Tous les constituants d’une clé primaire doivent être
renseignés. Il ne peuvent prendre la valeur Null.
Deux enregistrements différents ne peuvent avoir de
valeurs de clé identiques.
- 49 -
Contraintes de Types de Données

Permet de spécifier le domaine de validité des valeurs des
attributs.
- Exemples :
une note doit être comprise entre 0 et 20.
la date d’emprunt d’un livre est antérieure à sa date de retour
- 50 -
Contraintes d’Intégrité Référentielle
BUVEURS
NB
NOM
PRENOM
ABUS
TYPE
NB
NV
VINS
DATE
NV
CRU
MILL.
DEGRE
QUANTITE
- 51 -
Contraintes d’Intégrité Référentielle


Un constituant d’une clé étrangère doit limiter ses valeurs à
l’ensemble des valeurs présentes dans la table d’origine de
la clé.
Si un enregistrement d’une table est supprimé, tous les
enregistrements des autres tables faisant référence à cet
enregistrement, à travers des clés étrangères, doivent
normalement être supprimés.
- 52 -
Algèbre Relationnelle
- 53 -
Concepts Manipulatoires

Un ensemble d'opérations formelles

Ces opérations permettent d'exprimer toutes les requêtes
sous forme d'expressions algébriques

Elles sont la base du langage SQL
(SQL est un paraphrasage en anglais des expressions algébriques)
- 54 -
Opérations Ensemblistes

Opération ensembliste pour des relations de même schéma
- UNION notée 
- INTERSECTION notée 
- DIFFERENCE notée — ou \
- 55 -
Projection


Élimination des attributs non désirés et suppression des
tuples en double
notée A1,A2,...Ap (R)
VINS
Cru
VOLNAY
VOLNAY
CHENAS
JULIENAS
Mill
1983
1979
1983
1986
Région
BOURGOGNE
BOURGOGNE
BEAUJOLAIS
BEAUJOLAIS
Qualité
A
B
A
C
Cru,Région
Cru,Région(VINS)
Cru
Région
VOLNAY BOURGOGNE
CHENAS
BEAUJOLAIS
JULIENAS BEAUJOLAIS
- 56 -
Restriction

Sélection des tuples de R satisfaisant un critère Q

notée Q(R)

Q est le critère de qualification de la forme :
- Ai Valeur
- avec : { =, <, >=, >, <=}

Il est possible de réaliser des "ou" (union) et des "et"
(intersection) de critères simples
- 57 -
Exemple de Restriction
VINS
Cru
VOLNAY
VOLNAY
CHENAS
JULIENAS
Mill
1983
1979
1983
1986
Région
Qualité
BOURGOGNE
A
BOURGOGNE
B
BEAUJOLAIS
A
BEAUJOLAIS
C
MILL>1983
VINS
Cru
Mill
JULIENAS 1986
Région
Qualité
BEAUJOLAIS
C
CRU="VOLNAY"
CRU="CHENAS"
- 58 -
Produit Cartésien
Soient R1 et R2 deux relations.

Le produit cartésien R3 = R1 x R2 est une relation qui a pour
ensemble d'attributs l'union de ceux de R1 et de ceux de R2
et pour tuples toutes les combinaisons possibles des lignes
de R1 et de lignes de R2.
- 59 -
Exemple de Produit Cartésien
R
A
1
4
7
B
2
5
8
RxS
C
3
6
9
S
A
1
1
4
4
7
7
B
2
2
5
5
8
8
C
3
3
6
6
9
9
D
3
6
3
6
3
6
D
3
6
E
1
2
E
1
2
1
2
1
2
- 60 -
Jointure

Soient R(A1, …, An) et S(B1, …, Bm) deux relations
et Q un critère impliquant les attributs Ai et Bj.
La jointure de R et de S suivant le critère Q est l’ensemble
des éléments du produits cartésien RxS satisfaisant le
critère Q
Elle se note : R
S
Q
- 61 -
Exemple de Jointure
R
A
1
4
7
B
2
5
8
R
C
3
6
9
S
B<D
S
A
1
1
4
B
2
2
5
C
3
3
6
D
3
6
D
3
6
6
E
1
2
E
1
2
2
- 62 -
Jointures Particulières


L’équi-jointure est une jointure avec pour critère l’égalité de
certaines colonnes.
La jointure naturelle est une equi-jointure où le critère est
l’égalité entre colonnes de même nom, suivie de la
projection qui ne conserve qu’une colonne par nom.
- 63 -
Exemple de Jointure Naturelle
VINS
Cru
VOLNAY
VOLNAY
CHABLIS
JULIENAS
LOCALISATION
VINSREG
Cru
VOLNAY
VOLNAY
CHABLIS
CHABLIS
Mill
1983
1979
1983
1983
Mill
1983
1979
1983
1986
Cru
VOLNAY
CHABLIS
CHABLIS
Qualité
A
B
A
A
Qualité
A
B
A
C
Région
Bourgogne
Bourgogne
Californie
Région
Bourgogne
Bourgogne
Bourgogne
Californie
- 64 -
Utilisation Pratique
Prof
NoProf
Nom
0,n
Enseigne
VolumeHoraire
0,n
Cours
NoCours
Intitulé
- 65 -
Utilisation Pratique (suite)
Tables de l’exemple :
NoProf
Nom
1 Cece
2 Bourgeois
NoCours
Intitule
1 Supervision
2 Base de données
3 Introduction Réseaux
NoProf NoCours VolumeHoraire
1
2
7.5
1
3
9
2
1
6
Jointure naturelle :
NoProf
Nom
1 Cece
2 Bourgeois
1 Cece
Projection, (Nom, Intitule, VolumeHoraire)
puis renommage des colonnes :
NoCours
Intitule
2 Base de données
1 Supervision
3 Introduction Réseaux
VolumeHoraire
7.5
6
9
Nom Prof
Intitule Cours
Cece
Base de données
Bourgeois Supervision
Cece
Introduction Réseaux
VolumeHoraire
7.5
6
9
- 66 -
Structured Query Language
(SQL)
- 67 -
Définition

SQL (Structured Query Language) est un langage de
définition et de manipulation de bases de données
relationnelles.
- 68 -
Les Trois Niveaux

DDL (Data Definition Language)
- permet de créer, modifier, supprimer les tables

DML (Data Manipulation Language)
- permet de manipuler les données contenues dans les tables (sélection,
ajout, modification, suppression)

DCL (Data Control Language)
- permet de gérer les accès des utilisateurs aux tables
- 69 -
Principaux ordres SQL
DDL
ALTER
CREATE
COMMENT
DROP
RENAME
DML
DELETE
INSERT
SELECT
UPDATE
DCL
GRANT
REVOKE
- 70 -
Tables des exemples
Fournisseur
fno
10
11
12
13
14
15
16
17
19
Produit
nom adresse v ille
Dupo nt
Lille
Martin
Amie ns
Jaquet
Lyon
Durand
Lyon
Martin
Nice
Durand
Lille
Dupo nt
Paris
Lefebvre
Lille
Maurice
Paris
pno
101
102
103
104
105
106
107
108
cno
1001
1003
1005
1007
1011
1013
1017
1019
1023
1029
fno
17
15
17
15
19
13
19
14
10
17
design
fauteuil
fauteuil
bureau
bureau
armoire
caison
caison
classeur
pno
103
103
102
108
107
107
105
103
102
108
qute
10
2
1
1
12
5
3
10
8
15
prix
2,000.00
1,500.00
3,500.00
4,000.00
2,500.00
1,000.00
1,000.00
1,500.00
poids
F
F
F
F
F
F
F
F
couleur
7 gris
9 rouge
30 vert
40 gris
35 rouge
12 gris
12 ja une
20 ble u
Commande
- 71 -
Sélection simple (1)
SELECT DISTINCT design
FROM Produit
;
design
armoire
bureau
caison
classeur
fauteuil
SELECT DISTINCT design
FROM Produit
WHERE prix > 2000
design
armoire
bureau
;
- 72 -
Sélection simple (2)
SELECT DISTINCT design, prix
FROM Produit
;
SELECT *
FROM Produit
;
design
armoire
bureau
bureau
caison
classeur
fauteuil
fauteuil
pno design
101 fauteuil
102 fauteuil
103 bureau
104 bureau
105 armoire
106 caison
107 caison
108 classeur
prix
2,500.00 EUR
3,500.00 EUR
4,000.00 EUR
1,000.00 EUR
1,500.00 EUR
1,500.00 EUR
2,000.00 EUR
prix
2,000.00
1,500.00
3,500.00
4,000.00
2,500.00
1,000.00
1,000.00
1,500.00
F
F
F
F
F
F
F
F
poids couleur
7 gris
9 rouge
30 vert
40 gris
35 rouge
12 gris
12 ja une
20 ble u
- 73 -
Sélection simple ordonnée

Présentation
SELECT DISTINCT design, couleur
FROM Produit
WHERE couleur IN ("rouge", "vert")
ORDER BY design DESC;
SELECT DISTINCT design AS Nom du produit
FROM Produit
WHERE couleur IN ("rouge", "vert")
ORDER BY design ASC;
design
fauteuil
bureau
armoire
couleur
rouge
vert
rouge
Nom du produit
armoire
bureau
fauteuil
- 74 -
Jointure

Produit cartésien
SELECT *
FROM Produit, Commande, Fournisseur ;

Jointure
- « Donner toutes les informations concernant les commandes »
SELECT *
FROM Produit, Commande, Fournisseur
WHERE Produit.pno = Commande.pno AND
Fournisseur.fno = Commande.fno;
- 75 -
Jointure

Jointure (résultat)
SELECT * FROM Produit, Commande, Fournisseur
WHERE Produit.pno = Commande.pno AND Fournisseur.fno = Commande.fno;
P roduits.
pno
103
103
102
108
107
107
105
103
102
108
de sign
bureau
bureau
fauteuil
c las s eur
c ais on
c ais on
arm oire
bureau
fauteuil
c las s eur
prix
3,500.00
3,500.00
1,500.00
1,500.00
1,000.00
1,000.00
2,500.00
3,500.00
1,500.00
1,500.00
poids
F
F
F
F
F
F
F
F
F
F
30
30
9
20
12
12
35
30
9
20
coule ur
vert
vert
rouge
bleu
jaune
jaune
rouge
vert
rouge
bleu
cno
1001
1003
1005
1007
1011
1013
1017
1019
1023
1029
co m m ande co m m ande qute Fournisse urs.
.fno
.pno
fno
17
103
10
17
15
103
2
15
17
102
1
17
15
108
1
15
19
107
12
19
13
107
5
13
19
105
3
19
14
103
10
14
10
102
8
10
17
108
15
17
no m
adre sse
Lefebvre
D urand
Lefebvre
D urand
Mauric e
D urand
Mauric e
Martin
D upont
Lefebvre
v ille
Lille
Lille
Lille
Lille
P aris
Lyon
P aris
Nic e
Lille
Lille
- 76 -
Jointure

Jointure - projection
SELECT cno, design, nom AS Nom fournisseur, qute
FROM Produit, Commande, Fournisseur
WHERE Produit.pno = Commande.pno AND Fournisseur.fno = Commande.fno;
cno design
1001 bureau
1003 bureau
1005 fauteuil
1007 classeur
1011 caison
1013 caison
1017 armoire
1019 bureau
1023 fauteuil
1029 classeur
Nom fournisseur qute
Lefebvre
10
Durand
2
Lefebvre
1
Durand
1
Maurice
12
Durand
5
Maurice
3
Martin
10
Dupo nt
8
Lefebvre
15
- 77 -
Calcul

Jointure - projection - calcul
SELECT no, design, nom AS Nom fournisseur, prix, qute, prix*qute AS Total
FROM Produit, Commande, Fournisseur
WHERE Produit.pno = Commande.pno AND Fournisseur.fno = Commande.fno;
cno design
1001 bureau
1003 bureau
1005 fauteuil
1007 classeur
1011 caison
1013 caison
1017 armoire
1019 bureau
1023 fauteuil
1029 classeur
Nom fournisseur
Lefebvre
Durand
Lefebvre
Durand
Maurice
Durand
Maurice
Martin
Dupo nt
Lefebvre
prix
3,500.00
3,500.00
1,500.00
1,500.00
1,000.00
1,000.00
2,500.00
3,500.00
1,500.00
1,500.00
F
F
F
F
F
F
F
F
F
F
qute
10
2
1
1
12
5
3
10
8
15
Total
35,000
7,000
1,500
1,500
12,000
5,000
7,500
35,000
12,000
22,500
F
F
F
F
F
F
F
F
F
F
- 78 -
Sous requête

Question
liste des numéros de fournisseurs livrant au moins un produit en quantité
supérieure à chacun des produits livrés par le fournisseur 19
SELECT fno FROM Commande
WHERE qute > ALL (SELECT qute FROM Commande
WHERE fno = 19)
fno
17
- 79 -
Fonctions statistiques
AVG
Moyenne
COUNT
Nombre d’éléments
MAX
Maximum
MIN
Minimum
SUM
Somme
- 80 -
Exemples d'agrégats (Regroupements)
VINS
CRU
MILL
DEGRE
CHABLIS
1977
10.9
100
CHABLIS
1987
11.9
250
VOLNAY
1977
10.8
400
VOLNAY
1986
11.2
300
MEDOC
1985
11.2
200
SELECT CRU, SUM(QUANTITE)
FROM VINS
GROUP BY CRU;
SELECT AVG(DEGRE) FROM
VINS;
AVG
QUANTITE
DEGRE
11.2
SUM
CRU
SUM(QUANTITE)
CHABLIS
350
VOLNAY
700
MEDOC
200
- 81 -
COUNT

Comptage de tuples
- compter le nombre de commandes passées
SELECT COUNT(*) FROM Commande;
COUNT(*)
10
NbRouge
compter le nombre de produits de couleur rouge
SELECT COUNT(*) AS NbRouge FROM Produit WHERE couleur = ‘ rouge ’;
2
- 82 -
SUM

Sommations
- Total des quantités commandées de produits de couleur
rouge.
SELECT SUM(qute) AS QuteCmdRouge FROM Commande, Produit
WHERE Commande.pno = Produit.pno AND
couleur = rouge;
QuteCmdRouge
12
- 83 -
SUM et agrégats

Calculs sur les tuples et regroupement
- Total des quantités commandées par numéro de produit.
SELECT SUM(qute) AS QuteCmd , pno FROM Commande
GROUP BY pno;
QuteCmd pno
9 102
22 103
3 105
17 107
16 108
- 84 -
Forme générale

Consultation de tables
SELECT [ALL | DISTINCT] <attributs>
FROM <tables>
[ WHERE <conditions>
GROUP BY <attributs>
HAVING <conditions>
ORDER BY <attributs> ] ;
- 85 -
Autres Exemples (1)

Calcul sur les tuples
- « Donner le nom des buveurs ayant consommé plus que la moyenne »
SELECT Buveurs.nom
FROM Buveurs, Abus
WHERE
AND
Buveurs.nb = Abus.nb
Abus.qte >
( SELECT AVG(Abus.qte) FROM Abus ) ;
- 86 -
Autres Exemples (2)

Calcul sur les tuples et regroupements
- « Donner le nom et la quantité de vin bue par chaque buveur ayant
consommé plus de 10 litres »
SELECT Buveurs.nom, SUM(Abus.qte)
FROM Buveurs, Abus
WHERE Buveurs.nb = Abus.nb
GROUP BY Buveurs.nom
HAVING SUM(Abus.qte) > 10 ;
- 87 -
Autres Exemples (3)

Requête d’insertion
- « Ajouter un buveur »
INSERT INTO Buveurs (nb, nom, ville, type)
VALUES (8, Dupont, Lyon, Petit)
- 88 -
Autres Exemples (4)

Requête d’insertion
- « Ajouter dans la table Petit_Buveurs, les petits buveurs contenus dans
la table Buveurs »
INSERT INTO Petit_Buveurs (nb, nom)
SELECT Buveurs.nb, Buveurs.nom FROM Buveurs
WHERE Buveurs.type = ‘ Petit ’ ;
- 89 -
Autres Exemples (5)

Requête de mise à jour
- « Modifier le type des buveurs habitant Bordeaux en gros buveurs »
UPDATE Buveurs
SET Buveurs.type = ‘ gros ’
WHERE Buveurs.ville = ‘ Bordeaux ’ ;
- 90 -
Autres Exemples (6)

Requête de suppression
- « Supprimer tous les petits buveurs »
DELETE FROM Buveurs
WHERE Buveurs.type = ‘ Petit ’ ;
- 91 -
Normalisations
- 92 -
Nécessité des Normalisations

Considérons le schéma de la relation suivante :
Article(NomFnsr, AdresseFnsr, NomArt, PrixArt).

Une table correspondante est :
NomFnsr
Dupont
AdresseFnsr
Lille
NomArt
Fauteuil
PrixArt
1500
Martin
Nice
Bureau
5600
Dupont
Lille
Bureau
6000
Dupont
Lille
Armoire
4400
- 93 -
Anomalies de Mises à Jour

Anomalie d’insertion :
- On ne peut mémoriser (insérer) les coordonnées d’un fournisseur s’il
ne fourni pas au moins un article.

Anomalie de suppression :
- La suppression d’un article qui est l’unique article fourni par un
fournisseur entraîne la perte des informations relatives à ce
fournisseur.

Anomalies de modification :
- Si un fournisseur change de coordonnées, il faudra répercuter cette
modification à tous les articles dont il est le fournisseur.
- 94 -
Normalisation de l’exemple
La relation
Article(NomFnsr, AdresseFnsr, NomArt, PrixArt)
contient certaines dépendances :
NomFnsr  AdresseFnsr
NomFnsr, NomArt  PrixArt
Elle devrait se décomposer en deux relations :
Fournisseur(NomFnsr, AdresseFnsr) et
Article(NomFnsr, NomArt, PrixArt)
- 95 -
Normalisation

Les règles de normalisation permettent de concevoir un
schéma de base de données correct :
- sans redondance d’information.
- sans anomalie de mise à jour.

Elles se basent sur
- les dépendances fonctionnelles (DF) qui traduisent les relations entre
les données.
- les formes normales qui définissent les relations bien conçues.
- 96 -
Normalisation

Il existe plusieurs niveaux de normalisation :
- Première forme normale (1FN)
- Deuxième forme normale (2FN)
- Troisième forme normale (3FN)
...

Un modèle relationnel est dit normalisé quand toutes ses
tables sont en 3FN.
- 97 -
Dépendance fonctionnelle (DF)

Soient
- R (A1, A2, …, An) un schéma de relation.
- X et Y des sous-ensembles d’attributs de la relation R.


X  Y qui se lit X détermine Y ou Y dépend
(fonctionnellement) de X signifie que si on connaît la valeur
de X alors la valeur de Y est automatiquement déduite.
PERSONNE
- N° SS --> NOM ?
- NOM --> N° SS ?
- 98 -
Normalisation

Première forme normale
- But : garantir la manipulation de données élémentaires (indivisibles)

Deuxième forme normale
- But : éliminer certaines redondances en s’assurant qu’aucun attribut
n’est déterminé par une sous partie de la clé.

Troisième forme normale
- But : Elimination des dépendances dues à la transitivité des
dépendances transitives.
- 99 -
Première forme normale

Définition
- Une relation est en 1ère forme normale si tout attribut contient une
valeur atomique (unique)

Exemple d’une relation non en 1NF
PERSONNE
NOM
DUPONT
MARTIN
PROFESSION
Ingénieur, Professeur
Géomètre
Une telle relation doit être décomposée en répétant les noms
pour chaque profession
- 100 -
Première forme normale

Décomposition :
PERSONNE
NOM
PROFESSION
DUPONT
Ingénieur
DUPONT
Professeur
MARTIN
Géomètre
- 101 -
Deuxième forme normale

une relation est en 2e forme normale ssi :
1) elle est en 1ère forme normale
2) tout attribut non clé ne dépend pas d'une partie de la clé

Schéma d’une relation non en 2NF :
R
K1 K2
X
Y
Une telle relation doit être décomposée en
R1(K1,K2,X) et R2(K2,Y)
- 102 -
Exemple
Article(NomFnsr, AdresseFnsr, NomArt, PrixArt).
NomFnsr
Dupont
AdresseFnsr
Lille
NomArt
Fauteuil
PrixArt
1500
Martin
Nice
Bureau
5600
Dupont
Lille
Bureau
6000
Dupont
Lille
Armoire
4400
NomFnsr  AdresseFnsr
NomFnsr, NomArt  PrixArt
- 103 -
Troisième forme normale

une relation est en 3e forme normale ssi :
1) elle est en 2e forme normale
2) tout attribut n'appartenant pas a une clé ne dépend pas d’attribut ne
faisant pas partie de la clé

Schéma d’une relation non en 3NF :
R
K
X
Y
Z
Une telle relation doit être décomposée en
R1(K, X, Y) et R2(X,Z)
- 104 -
Exemple 3ième Forme Normale

Exemple
Voiture (NV, marque, type, puissance, couleur)
Type --> marque
Type --> puissance

Pas en 3eme forme normale !

Devra se décomposer en :
Voiture(NV, type, couleur) et
TypeVoiture(type, marque, puissance)
- 105 -