Download - Chapitre 3 : Le Modèle Relationnel
Chapitre 3 :
Le Modèle Relationnel
Mr. Mohamed Salah MEDDEB
http://meddeb.jimdo.com
1
2
Historique : E.F. CODD dans les années 70
• modèle basé sur la théorie des ensembles
• des langages pour manipuler les données (SQL)
• une démarche pour représenter le « monde réel »
Nombreux outils :
• MySQL (logiciel libre)
• Access (Microsoft) versions 1997 – 2003 – 2007 - 2010
• ORACLE (Oracle corp.)
• SQL Server (Microsoft)
• Ingres (Computer Associates)
Modèle relationnel - SQL
Concepts des Bases de Données Relationnelles
Relation, faite de 2 composantes:
Instance : une table, avec lignes et colonnes.
#lignes = cardinalité, #colonnes = degré / arité.
Schéma : spécifie le nom de la relation, plus le nom et le
domaine (type) de chaque colonne (attribut).
Une relation est un ensemble de lignes (tuples) distinctes;
chaque tuple a la même arité que le schéma de la relation.
Base de données relationnelles: un ensemble des relations de
la BD, chacune ayant un nom distinct.
Schéma d’une BD: ensemble de schémas des relations
dans la BD.
Instance de la BD: ensemble des instances relationnelles de
la BD.
3
Langages de Requêtes Relationnelles
Un avantage majeur du modèle relationnel est qu’ilsupporte de simples et puissantes requêtes sur lesdonnées.
Les requêtes peuvent être écrites de manière intuitive (i.e.déclarative), et le SGBD est responsable de leurévaluation efficiente.
L’utilisateur dit au SGBD quoi faire et le système cherchecomment faire ce qu’il y a à faire de manière efficiente!
La clé du succès: sémantique précise des requêtes.
Permet à l’optimisateur de réordonner les opérations touten garantissant que la réponse ne change pas.
4
5
Modèle Relationnel
SCHEMA D’UNE RELATION :
6
Modèle Relationnel
ATTRIBUT (COLONNES) : colonne d ’une relation
caractérisée par un nom et un domaine.
CLE D ’UNE RELATION : ensemble minimum d ’attributs
d ’une relation qui détermine les autres attributs.
N-UPLET (LIGNE, TUPLE) : correspond à une occurrence
(ou ligne) d ’une relation
VALEUR NULLE : Valeur inconnue d ’un attribut dans la
base de données. Différente de 0 (zéro) et de chaîne vide.
7
INTEGRITE DES DONNEES
Objectif : garantir la cohérence des données afin d ’avoir une
concordance entre la base et le monde réel qu ’elle modélise.
Intégrité de domaine : concerne le contrôle syntaxique et
sémantique d ’un attribut et fait référence au type de définition
du domaine.
Exemple : age : entier compris entre 0 et 120.
Intégrité d ’entité : concerne les valeurs prises par une clé
primaire qui doivent être unique et non nulles.
Exemple : pour la relation CLIENT, CODE_CLIENT est unique et non nul.
Intégrité de référence : Si un même attribut apparaît dans une
relation comme clé et dans une autre comme non clé, toute
valeur de l ’attribut non clé doit exister dans l ’attribut clé.
8
Règles de passage d ’un modèle E/A vers
un schéma Relationnel
REGLE n°1 : TOUTE ENTITE DEVIENT UNE RELATION
Les attributs traduisent les propriétés de l ’entité
la clé primaire traduit l'identifiant de l'entité
9
Règles (suite)
REGLE n°2 : UNE ASSOCIATION DE DIMENSION 2 AVEC
CARDINALITE 1 A PLUSIEURS SE REECRIT EN :
portant dans la relation fille la clé primaire de la
relation mère.
L'attribut ainsi ajouté s'appelle clé étrangère.
10
Règles (suite) REGLE n°3: UNE ASSOCIATION DE DIMENSION 2 AVEC
CARDINALITES PLUSIEURS A PLUSIEURS SE REECRIT EN :
créant une relation contenant comme attributs les identifiants
des 2 entités associées
ces attributs constituent à eux 2 la clé primaire de la relation
ajoutant la ou les éventuelles propriétés de l'association à
cette relation.
Règles (suite)
Règle 4: Une association binaire de type 1 :1 est traduite
comme une association binaire de type 1 :n sauf que la clé
étrangère se voit imposer une contrainte d’unicité en plus
d’une éventuelle contrainte de non vacuité (cette contrainte
d’unicité impose à la colonne correspondante de ne prendre
que des valeurs distinctes).
11
12
Règles (suite)
REGLE n°5: UNE ASSOCIATION DE DIMENSION
SUPERIEURE A 2 SE REECRIT SELON LA REGLE 3 :
exemples
1-n 1-1
13
exemples
0-1 1-1
14
exemples
0-1 0-1
15
Exemples
1-1 1-1
Si fonctionnellement, le marin est le plus important…
MARIN(numMarin , nomMarin , numVoilier ,nomVoilier)
Si fonctionnellement, le voilier est le plus important…
VOILIER(numVoilier , nomVoilier , numMarin , nomMarin)
Si le modèle peut évoluer ou si on a une distinction fonctionnelle
forte entre marin et voilier…
VOILIER(numVoilier , nomVoilier , numMarin)
MARIN(numMarin , nomMarin , #numVoilier)
une course à la voile en solitaire :
16
Exemples
0-1 1-1
APPARTEMENT (numAppartement , superficie)
PLACE_PARKING (numPlace , Etage ,
#numAppartement)
Dans un immeuble, un appartement peut bénéficier
d’une place de parking ou pas mais jamais de plusieurs.
17
Exemples
0-1 0-1Une activité culturelle peut disposer d’un animateur ou
pas mais jamais de plusieurs. Un animateur peut
s’occuper au maximum d’une activité culturelle.
ANIMATEUR (numAnimateur , nom)
ACTIVITE_CULTURELLE (idActivite , nomActivite)
ANIMER (#numAnimateur , # idActivite)
18
exemples
1-n 1-n
19
exemples
0-n 0-n 1-n
20
21
22
23
Réflexive
SALARIE (matricule, nom, prénom, fonction,…,
#matricule_chef)
PIECE (référence, libellé)
COMPOSITION(#référence_composé, #référence_composant,
nombre)
24
Transformation de l'identifiant relatif
25
Propriétaire (Numpro, Ruepro,…)
PP (Numpro#, NomPP…)
SCI (Numpro#, NomSCI, Refbanque, Fraisges…)
26
Intérêt de la normalisation Risques:
Exemple 1: Soit la relation
PRODUIT (prod_id, libellé, pu, qte, dep_id, adr, volume)
Anomalies
o -Redondance : libellé et pu apparaissent pour chaque instance d'un
produit
o -Risque d'introduction d'incohérence lors de l'insertion d'une nouvelle
instance de p1
o -Risque de perte d'information : la suppression du produit p3 entraine
la perte de son libellé, son pu et des information relatives au dépôt 4
27
Intérêt de la normalisationExemple 2: Soit la relation
Emprunt_Amis (N°DVD, Nom, Prénom, Contact, Date_emp)
Pourquoi cette relation a t-elle des anomalies?
Anomalies:
Anomalie de mise à jour (exp : Michel Valon a changé de numéro de portable :
mise à jour dans tous les tuples concernés)
Anomalie d’insertion (exp : Nouvel ami, Dupreu Jean : je ne peux l’entrer dans la
base que lorsqu’il m’empruntera un DVD)
Anomalie de suppression (exp : J’ai perdu le DVD n°230 : si je le
supprime de la base, je perds les information sur Prunier Marie)
28
Objectifs:
Supprimer les anomalies précédentes
élimination des redondances : éviter les incohérences + minimiser
l’espace de stockage
Normaliser une relation consiste à décomposer une relation ayant
des anomalies en plusieurs sous relations sans anomalies.
Normalisation s’inscrit dans la partie conception d’une base de
données -> peut être vu comme un outil théorique de vérification
Problème :
Comment décomposer une relation , sachant qu’une mauvaise
décomposition peut mener à une perte d’informations ????
La Normalisation
La Normalisation
PRINCIPE
Appliquer les règles : Formes Normales
Une forme normale: une méthode de classification de table qui
repose sur les dépendances fonctionnelles
30
Dépendance fonctionnelle (Définition)Définition :
Un attribut ou une liste d'attributs Y dépend fonctionnellement
d'un attribut ou d'une liste d'attributs X dans une relation R, Si étant
donnée une valeur de X, il ne lui est associé qu'une seule valeur de
Y dans toute instance de R.
On note x y une telle dépendance.
Exemples :
PRODUIT (prod_id, libellé, pu, qte, dep_id, adr, volume)
prod_id libellé
prod_id pu
dep_id adr, volume
prod_id, dep_id qte
31
Un attribut ou une liste d'attributs X est une clé pour la relation
R(X,Y) si Y dépend fonctionnellement de X dans R
X est une clé minimale si x y est élémentaire
Si une relation possède plusieurs clés, nous on choisissons une
qui sera
appelée clé primaire (soulignée dans les schémas de relation).
Les autres clés seront appelées clés secondaires
Exemple : la relation
Voiture(immat , chassis, type, marque, puissance)
admet « immat » et « chassis » comme clés
Dépendance fonctionnelle (clé d’une relation)
33
Les dépendance Fonctionnelles
DEPENDANCE FONCTIONNELLE (DF) SIMPLE :
Il existe une dépendance fonctionnelle entre deux attributs d ’une
relation, si à toute valeur de A on ne peut associer à tout instant
qu’une et une seule valeur de B.
A B : A détermine B
Exemple : Le numéro de salarié dans une entreprise détermine son
nom
DF DE PLUSIEURS VARIABLES:
A <-> B A détermine plusieurs B
Exemple : Le nom d’un professeur détermine la liste de ses étudiants
34
Les dépendance Fonctionnelles
DF ELEMENTAIRE :
A B est élémentaire s ’il n ’existe pas X A tel que X B
Autrement dit A,X B, X B n ’est pas une DF élémentaire
Exemple :
R1(Fournisseur,Article,Adresse,Prix)
Fournisseur,Article Prix est une DF élémentaire
Fournisseur,Article Adresse est juste mais redondante car
Fournisseur Adresse, donc dans R1, Adresse ne dépend que
d ’une partie de la clé.
R1 doit être décomposée en :
R2(Fournisseur, Adresse)
R3(Fournisseur, Article, Prix)
35
Les dépendance Fonctionnelles
DF DIRECTE :
A B est directe s ’il n ’existe pas X tel que A X et X B
Exemple :
R1(Numéro_Véhicule, Marque, Type_Véhicule, Puissance, Couleur)
Les dépendances fonctionnelles sont les suivantes :
Numero_Vehicule Type_Vehicule,Couleur
Type_Vehicule Marque,Puissance
Type_Vehicule est donc une clé pour Marque et Puissance
donc R1 doit être décomposée en :
R2(Type_Vehicule, Marque,Puissance)
R3(Numero_Vehicule,Couleur,Type_Vehicule)
1NF :
Une relation est dite en première forme normale si
chacun de ses attributs a un domaine atomique mono-valué
Exemple :
Personne(id, nom, les_diplômes) n'est pas en 1NF
où « les_diplômes » est l'ensemble des diplômes obtenus par
une personne
Personne(id, nom) est en 1NF
Diplôme(id, unDiplome) est en 1NF
Formes normales (1FN)
37
Formes normales (1FN)
1FN: exemple 2
Emprunteur LivrecodEmprunteur
NomTitre
emprunter
1,3 1,1
CodEmprunt Livre1 Livre2 Livre3
1501
1502
La relativité
Tintin et le Lotus bleu
Alice au pays..
Mobby Dick
CodEmpru
1501
1502
NOM
Durand
Leroux
Titre CodEmp
La relativité
Alice au pays ..
Tintin et le Lotus
Mobby Dick
1501
1501
1502
1502
Formes normales (2FN)Une relation R est dite en deuxième forme normale si
(i) elle est en 1NF
et
(ii) tout attribut n'appartenant pas à une clé ne dépend pas d'une partie de la clé de R
Exemple :
Stock(prod_id, dep_id, libellé, qte) n'est pas en 2NF
car : prod_id, dep_id qte
prod_id libellé
Stock(prod_id, dep_id, qte) est en 2NF
Produit(prod_id, libellé) est en 2NF
Exercices : Est-ce que ces rélations sont en 2FN
Lignes_Commandes( Num_Comm, Num_produit, Désgnation, Qté, PU)
Emprunt_Amis (N°DVD, Nom, Prénom,Titre, Réalisateur, Genre, Contact, DateEmp)
39
Formes Normales 2FN : Exemple
NumSalarié Nom NumProjet Heures
NumSalarié Nom
NumSalarié NumProjet Heures
2036
3690
4502
4502
Durand
Durand
Leroux
Frank
Frank
1
2
2
3
1
18
6.5
8.5
23
4.8
2036
3690
4502
Durand
Leroux
Frank
2036
2036
2036
3690
4502
4502
1
2
2
3
1
18
6.5
8.5
23
4.8
3NF :
Une relation est dite en troisième forme normale si
(i) elle est en 2NF
et
(ii) tout attribut n'appartenant pas à une clé ne dépend pas d'un attribut non
clé toutes les DF sont directes
Exemple :
La relation AVION
n'est pas en 3NF
car
type capacité, constructeur et type n'est pas une clé
Avion(no_avion, type, propriétaire) est en 3NF
Modèle(type, constructeur, capacité) est en 3NF
Formes normales (3FN)
41
Formes Normales
3FN: Exemple
Nom NumSalarié Date_naiss Service Nomservice NumChef
NumSalarié Nom Date_naiss Service
Service NomService NumSalarié_chef
Durand
Martin
501
502
15/01/1950
12/04/1957
5
6
Vente
Info
458
412
5
6
vente
Info
458
412
501
502
Durand
Martin
15/01/1950
12/04/1957
5
6
42
La partie opérationnelle : l'algèbre
relationnelle
Les langages de base de données relationnelle tels que SQL,
se base sur des opérateurs dérivés de la théorie des
ensembles pour pouvoir interroger et mettre à jour les tables
d’une base de données relationnelle.
4 opérations ensemblistes (union, intersection, différence,
produit cartésien)
3 opérations spécifiques des BD relationnelles (sélection,
projection, jointure)
43
Opérations ensemblistes (Union)
L'opérateur UNION renvoie les lignesissues des deux interrogations aprèsélimination des doublons.
Prof_debut Prof_fin
A B
Matricule Nom Prénom Matière
0001 Baccouch Hajer Mobiles
0002 Meddeb Mohamed Base des données
0003 Guerbouj Sonia Java
0004 Bahri Taher Admin Système
Matricule Nom Prénom Matière
0001 Baccouch Hajer Mobiles
0004 Bahri Taher Admin Système
0005 Chaabani Nizar Sécurité Informtique
0006 Ferjani Anis ProgrWeb
Matricule Nom Prénom Matière
0001 Baccouch Hajer Mobiles
0002 Meddeb Mohamed Base des données
0003 Guerbouj Sonia Java
0004 Bahri Taher Admin Système
0005 Chaabani Nizar Sécurité Informtique
0006 Ferjani Anis Programmation Web
Prof_debut Prof_fin
44
Opérations ensemblistes (Intersection)
L'opérateur INTERSECT renvoie les lignes
communes aux deux interrogations.
Matricule Nom Prénom Matière
0001 Baccouch Hajer Mobiles
0002 Meddeb Mohamed Base des données
0003 Guerbouj Sonia Java
0004 Bahri Taher Admin Système
Matricule Nom Prénom Matière
0001 Baccouch Hajer Mobiles
0004 Bahri Taher Admin Système
0005 Chaabani Nizar Sécurité Informtique
0006 Ferjani Anis ProgrWeb
Prof_debut Prof_fin
Prof_debut Prof_fin
Matricule Nom Prénom Matière
0001 Baccouch Hajer Mobiles
0004 Bahri Taher Admin Système
Opérations ensemblistes (Différence)
L'opérateur MINUS renvoie toutes les lignes
distinctes sélectionnées par la première
interrogation, mais non présentes dans
l'ensemble de résultats de la deuxième
interrogation.
45
A B
Matricule Nom Prénom Matière
0001 Baccouch Hajer Mobiles
0002 Meddeb Mohamed Base des données
0003 Guerbouj Sonia Java
0004 Bahri Taher Admin Système
Matricule Nom Prénom Matière
0001 Baccouch Hajer Mobiles
0004 Bahri Taher Admin Système
0005 Chaabani Nizar Sécurité Informtique
0006 Ferjani Anis ProgrWeb
Prof_debut Prof_fin
Matricule Nom Prénom Matière
0001 Baccouch Hajer Mobiles
0002 Meddeb Mohamed Base des données
0003 Guerbouj Sonia Java
0004 Bahri Taher Admin Système
Prof_debut - Prof_fin
46
Opérations ensemblistes
Produit cartésien
Numéro
coureur
Nom Coureur Code
équipe
Code
pays
8 ULLRICH Jan TEL ALL
31 JALABERT Laurent ONC FRA
61 ROMINGER Tony COF SUI
91 BOARDMAN Chris GAN G-B
Code
pays
Nom Pays
ALL Allemagne
FRA France
SUI Suisse
G-B Grande -
Bretagne
Numéro
coureur
Nom Coureur Code
équipe
Code
pays
Code
pays
Nom Pays
8 ULLRICH Jan TEL ALL ALL Allemagne
8 ULLRICH Jan TEL ALL FRA France
8 ULLRICH Jan TEL ALL SUI Suisse
8 ULLRICH Jan TEL ALL G-B Grande -Bretagne
31 JALABERT Laurent ONC FRA ALL Allemagne…
R
R1 R2
-
47
Opérations spécifiques
Sélection (ou restriction) : relation composée de n-upletsvérifiant une condition
Numéro
coureur
Nom Coureur Code
équipe
Code
pays
8 ULLRICH Jan TEL ALL
31 JALABERT Laurent ONC FRA
61 ROMINGER Tony COF SUI
91 BOARDMAN Chris GAN G-B
114 CIPOLLINI Mario SAE ITA
Quels sont les coureurs suisses ?
R = SELECTION(COUREUR, CodePays = ‘‘SUI’’)
NOTATION : CodePays = ‘‘SUI’’(COUREUR)
Relation
résultat
48
Opérations spécifiques
Projection : relation restreinte aux attributs spécifiés dans
la projection
Exemple : Nom et nationalité des coureurs ?
R = PROJECTION(COUREUR, NomCoureur, Nationalité)
NOTATION : {NomCoureur,Nationalité}(COUREURS)
Nom Coureur Code pays
ULLRICH Jan ALL
JALABERT Laurent FRA
ROMINGER Tony SUI
BOARDMAN Chris G-B
CIPOLLINI Mario ITA
Relation
résultat
49
Opérations spécifiques
Jointure :
Code pays Nom Pays
ALL Allemagne
FRA France
SUI Suisse
G-B Grande -Bretagne
Numéro
coureurNom Coureur
Code
équipe
Code
pays
8 ULLRICH Jan TEL ALL
31 JALABERT Laurent ONC FRA
61 ROMINGER Tony COF SUI
91 BOARDMAN Chris GAN G-B
Numéro
coureurNom Coureur
Code
équipe
Code
paysNom Pays
8 ULLRICH Jan TEL ALL Allemagne
31 JALABERT Laurent ONC FRA France
61 ROMINGER Tony COF SUI Suisse
91 BOARDMAN Chris GAN G-B Grande -
Bretagne
Relation
résultat
50
Opérations spécifiques
Jointure (suite)
Notation : Coureur Pays
-jointure : jointure sous condition autre que
l’égalité
Code
pays
Code
pays
51
Opérations spécifiques
Division : relation composée des n-uplets tels que le produit
cartésien avec le diviseur soit un sous-ensemble de la relation
dividende (…?)
Athlète Epreuve Epreuve Athlète
Dupont 200 m 200 m Dupont
Durand 400 m 400 m
Dupont 400 m 110 m H
Martin 110 m H
Dupont 110 m H
Martin 200 m
Participer Epreuve
Relation résultat de
Particier Epreuve
Quels sont les athlètes qui ont participé à toutes les épreuves ?
52
a
b
c
x
y
a
a
b
b
c
c
x
y
x
y
x
y
Produit cartésien
Sélection Projection
Union Intersection Différence